[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
table updates.
sdahiya 28-Jan-2006 115.21 5002283 Modified cursor c_IDW_events to
fetch element entry updates done
in "Update" mode in addition to
"Correction" mode.
sdahiya 01-Feb-2006 115.22 Modified cursor c_IDW_events to
use events' effective date
(instead of creation date) for IDW
calculation.
sdahiya 01-Feb-2006 115.23 5002283 Modified cursor c_IDW_events to
use events' effective date
(instead of creation date) to
date effectively identify element
entries and element types.
sdahiya 02-Feb-2006 115.24 Modified action_creation and
archinit to stamp appropriate date
in pay_recorded_requests when
archiver is run in retry mode.
sdahiya 10-Apr-2006 115.25 5146225 Modified function get_idw to call
pay_mx_ff_udfs.get_idw in
BIMONTH_REPORT mode only if
gv_variable_idw is 'Y'.
sdahiya 17-Apr-2006 115.26 5005254 Archiver should archive
termination date instead of a day
prior to the date stamped in
pay_process_events.
sdahiya 19-Apr-2006 115.27 Calculate IDW on
LEAST(assignment's end date,
process end date)
sdahiya 11-May-2006 115.28 5033056 Modified cursor c_person_detail
to select future dated hires for
archival.
vpandya 18-May-2006 115.29 5234584 Modified cursor c_person_detail
to select future dated hires and
for any UPDATE or CORRECTION in
employee name.
sdahiya 19-Jun-2006 115.30 SUA 2006 changes.
vpandya 22-Jun-2006 115.31 5353025 Changed c_abs_info cursor.
Now passing eff start and end date
of element entry instead of the
archiver.
sdahiya 27-Jun-2006 115.32 5354858 Modified all references involving
INFONAVIT element entries to
consider INFONAVIT transactions
occuring in future with respect to
archiver's end date.
sdahiya 28-Jun-2006 115.33 5355325 Removed undesired join with
pay_element_entry_values_f in
cursor c_abs_info.
vpandya 25-Aug-2006 115.34 Initializing gn_implementation to
zero when there is no payroll
action before the current one so
that if retry is run for the
very first SS Archiver process
after running SS Archiver multiple
times, it should archive Person
Information.
Also Changed value for rww.
Added logic for separation.
sdahiya 20-Sep-2006 115.35 5552692 'S' should be archived if
Reduction Table Applies input
value is 'Y'.
sdahiya 23-Sep-2006 115.36 5558838 INFONAVIT transactions effective in
past with respect to archiver
start date should be considered
for transactions 18, 19 and 20.
vmehta 26-Sep-2006 115.37 5568202 modified load_infonavit_trans to
call load_infonavit_info with
effective_start_date + 1
and effective_end_date + 1
This to ensure that we load the
structure with current values and
not old values.
sdahiya 24-Jan-2007 115.38 Modified the archiver so that
transaction date is now archived
in action_information2 and employer
SS identifier in action_information5.
Data upgrade will be carried out
using the generic upgrade mechanism.
Function arch_exists_without_upgrade
created to restrict running of
archiver without upgrading existing
archived data.
sdahiya 13-Feb-2007 115.39 5875096 Fixed get_person_information so that
it uses correct dates to fetch person
data for future dated events.
sdahiya 06-Mar-2007 115.40 5908010 Only those assignments which belong
to the current GRE should be
considered to fetch person data.
sdahiya 13-Mar-2007 115.41 5921945, Each event should be checked for its
5899264, existence under GRE for which
5922046 archiver is run.
sdahiya 14-Mar-2007 115.42 5888285 Events for EFFECTIVE_END_DATE should
be ignored if future asg records
exist.
vpandya 20-Mar-2007 115.43 5944540 Leapfrog ver 115.37 to resolve R12
Branch Line issue.
vpandya 20-Mar-2007 115.44 This is the same as 115.42.
sdahiya 21-Mar-2007 115.45 Modified seniority_changed to check
seniority on MAX(hire date, previous
archiver run date).
sdahiya 22-Mar-2007 115.46 5885473 Modified chk_person_rec_chng to
identify changes in IMSS medical
center (PER_INFORMATION4).
sdahiya 20-Apr-2007 115.47 6005922 Fixed seniority calculation for
future-dated hires.
sdahiya 22-Apr-2007 115.48 08 and 02 transactions should not be
archived if person-GRE relation
exists due to assignments other than
the current one.
sdahiya 24-Apr-2007 115.49 6013218 Employee social security number
should be fetched from person
record effective on transaction
date.
sdahiya 25-Apr-2007 115.50 6005853 Terminations due to SCL changes
should be checked for qualification
under current GRE.
sdahiya 25-Apr-2007 115.51 Modified cursor csr_per_gre so that
it checks for person-GRE association
for the current assignment in
addition to others.
sdahiya 26-Apr-2007 115.52 6019466 Modified cursor csr_per_gre to ensure
that only active assignments are
checked to establish person's
relation with GRE.
sdahiya 27-Apr-2007 115.53 6020160 Added NVL check in
chk_person_rec_chng procedure.
vpandya 10-May-2007 115.54 6019849 Changed cursor c_person_detail:
removing trailing blank if second
name is not entered.(rtrim)
sdahiya 15-May-2007 115.55 6021768 Modified arch_other_transactions
so that 07 is archived only if
person is not a new hire.
sdahiya 16-May-2007 115.56 07 transactions effective on a date
different from the hire date should
be archived. Modified cache_idw_date
for this.
sdahiya 18-May-2007 115.57 6060052 Run through transactions only if
transactions' cache is not empty.
nragavar 12-Jul-2007 115.58 6198089 modified to log absences correctly.
vpandya 16-Jul-2007 115.59 6238481 Changed: arch_other_transactions
Added end if for event_qualified
for INFONAVIT.
vpandya 18-Jul-2007 115.60 6198089 Changed: range_cursor and removed
6130744 condition for ppe.effective_date.
For bimonthly period, the date
for 07 trans would be first day
of next bimonthly period.
vpandya 20-Jul-2007 115.62 6264202 Changed: archive_data
Filter transaction if trn date is
4712/12/31. Call an API if pl/sql
table count is greater than 0.
vpandya 20-Jul-2007 115.63 Changed: archive_data
vpandya 20-Aug-2007 115.64 Changed: get_idw, truncating
effective_date while calling get_idw
of udfs.
vpandya 21-Aug-2007 115.65 6353167 Changed: get_idw, using mode to
REPORT now on as bimonthly IDW gets
when first day of next bimonth period
.
prechand 21-Feb-2008 115.66 6820541 Start date is replaced by effective_
start_date in the get person information
query for getting the latest hire date
sivanara 07-Mar-2008 115.67 6862116 Added cursor c_check_active_employee
to archive_data cursor, so that
archive data is only for "Employee"
sivanara 25-Apr-2008 115.68 6960481 Added new parameter to event_qualified
to filter out applicant event.
sivanara 17-Jun-2008 115.70 7185703 Removed fnd_date function from the cursor
csr_get_asg_end_date and csr_per_gre.
sivanara 20-Aug-2008 115.71 7341327 For the cursor csr_per_gre added condition
for applicant.
swamukhi 01-Oct-2008 115.72 6451017 For the cursor csr_per_gre added a condition
to check the effective_start_date.
vvijayku 07-Nov-2008 115.73 6451017 Added a new cursor c_get_report_term_rehire
to retrieve the value of the reporting option
and later archiving it.
vvijayku 10-Nov-2008 115.74 7342321 Added a new cursor c_first_sal_date which retrieves
the date on which the first salary was attached to
the assignment.
vvijayku 21-Nov-2008 115.75 7342321 The complete fix did not go into the earlier version
115.74. This version has the complete fix.
vvijayku 21-Nov-2008 115.76 7342321 Had to remove some compilation errors,which was arcsed
in by mistake.
sjawid 30-Jul-2009 115.77 6933682 Added extra parameters p_payroll_action_id,
p_execute_old_idw_code to
function call pay_mx_ff_udfs.get_idw.
Added new cursor c_salary_type in
procedure arch_hire_separation
to correct the salary_type for newhire employees.
sjawid 30-Jul-2009 115.78 6933682 Corrected pay_mx_ff_udfs.get_idw function call
vvijayku 20-Nov-2009 115.79 8988585 Corrected the to_char idw conversion to the correct
format.
sjawid 09-Nov-2009 115.80 8912736 Modified cursor c_disabilities_info, added decode function
to disability_control to get the correct codes as per
statutory requirement.
sjawid 19-Nov-2009 115.81 9128410 Changed the get_idw function call for the person info .
Passing assignment_start_date to get_idw function
when the person is processing first time.
sjawid 04-Jan-2011 115.82 9820914 Handling the Special Chars in Employee Name and GRE Name
using function pay_mx_rules.strip_spl_chars.
vvijayku 03-Sep-2011 115.84 8438074 Added code to enable the selective reporting of SS
transactions.
vvijayku 10-Oct-2011 115.85 8438074 Modified the function GET_SS_EXCLUSION_DATES and
added the NOCOPY hint.
vvijayku 11-Oct-2011 115.86 8438074 Corrected the function call GET_SS_EXCLUSION_DATES
and replaced the duplicate parameter
lv_exclude_start_date_from_cur with
lv_exclude_end_date_from_cur
vvijayku 08-Nov-2011 115.87 13357684 Added Message statements to be displayed in the
Payroll Processes Messages form which will help
in easily identifying the SS Archiver runs.
jeisaac 11-Jun-2012 115.88 14179408 Moved the function call to GET_SS_EXCLUSION_DATES,
to procedure get_transactions.
sjawid 11-Nov-2012 115.89 15839415 Modified Range Cursor query
Introduced new function validate_person_id.
The validations in the range cursor has been
moved to the function validate_person_id
jeisaac 17-Nov-2012 115.90 15881643 Modified Range cursor query to pick employees when variable
IDW option is set as No while submitting the process.
============================================================================*/
--
-- Global Variables
--
TYPE gre_rec_type IS RECORD(
assignment_id NUMBER,
effective_start_date DATE,
effective_end_date DATE,
gre_id NUMBER);
SELECT assignment_id,
effective_start_date,
effective_end_date,
per_mx_ssaffl_archive.derive_gre_from_loc_scl(
location_id,
business_group_id,
soft_coding_keyflex_id,
effective_end_date) gre_id
FROM per_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT per.current_applicant_flag
FROM per_all_people_f per
WHERE per.person_id = p_person_id
AND fnd_date.canonical_to_date(p_effective_date)
BETWEEN per.effective_start_date AND per.effective_end_date;
SELECT PEI_INFORMATION1,
PEI_INFORMATION2
FROM PER_PEOPLE_EXTRA_INFO
WHERE PERSON_ID = P_PERSON_ID
AND PEI_INFORMATION_CATEGORY = 'MX_EMP_EXCLUDE_SS_REPORTING';
SELECT business_group_id
FROM hr_all_organization_units
WHERE organization_id = cp_organization_id;
SELECT pay_mx_utility.get_legi_param_val('END_DATE',LEGISLATIVE_PARAMETERS)
FROM pay_payroll_actions ppa
WHERE ppa.report_type = 'SS_ARCHIVE'
AND ppa.report_qualifier = 'SS_ARCHIVE'
AND ppa.report_category = 'RT'
AND pay_mx_utility.get_legi_param_val('GRE',LEGISLATIVE_PARAMETERS) =
cp_tax_unit_id
ORDER BY ppa.payroll_action_id desc ;
SELECT fnd_date.canonical_to_date(org_information6)
FROM hr_organization_information
WHERE org_information_context = 'MX_TAX_REGISTRATION'
AND organization_id = cp_organization_id ;
SELECT count(*)
INTO ln_count
FROM fnd_sessions
WHERE session_id = USERENV('sessionid');
SELECT fnd_date.date_to_canonical(sysdate)
INTO lv_start_date
FROM DUAL;
SELECT fnd_date.date_to_displaydate( ADD_MONTHS (
fnd_date.canonical_to_date( p_date ), -1 ) ) || ' - ' ||
fnd_date.date_to_displaydate( ADD_MONTHS (
fnd_date.canonical_to_date( p_date ), 1 ) -1 )
INTO lv_dates
FROM dual;
SELECT business_group_id
,pay_mx_utility.get_legi_param_val('START_DATE',
LEGISLATIVE_PARAMETERS) start_date
,pay_mx_utility.get_legi_param_val('END_DATE',
LEGISLATIVE_PARAMETERS) end_date
,pay_mx_utility.get_legi_param_val('GRE',LEGISLATIVE_PARAMETERS) GRE
,pay_mx_utility.get_legi_param_val('MODE',
LEGISLATIVE_PARAMETERS) REPORT_MODE
,pay_mx_utility.get_legi_param_val('PERIOD_ENDING_DATE',
LEGISLATIVE_PARAMETERS) PERIOD_ENDING_DATE
FROM pay_payroll_actions
WHERE payroll_action_id = cp_payroll_action_id;
SELECT sum(decode(to_number(puci.value),0,0,1)) total_days
,sum(to_number(puci.value)) total_hours
FROM pay_user_column_instances_f puci,
pay_user_columns puc
WHERE puc.user_column_name = cp_workschedule
AND ( puc.legislation_code = 'MX' OR
puc.business_group_id = cp_business_group_id )
AND puc.user_column_id = puci.user_column_id;
SELECT fnd_number.canonical_to_number(legislation_info2)
FROM pay_mx_legislation_info_f
WHERE legislation_info_type = 'MX Minimum Wage Information'
AND legislation_info1 = 'MWA'
AND cp_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT replace(org_information1,'-','') Social_Security_ID
,org_information3 Transmitter_Yes_No
,org_information5 WayBill_Number
,org_information6 Transmitter_GRE_ID
FROM hr_organization_information
WHERE org_information_context = 'MX_SOC_SEC_DETAILS'
AND organization_id = cp_organization_id ;
SELECT name
FROM hr_organization_units
WHERE organization_id = cp_organization_id;
SELECT org_information5 WayBill_Number
FROM hr_organization_information
WHERE org_information_context = 'MX_SOC_SEC_DETAILS'
AND organization_id = cp_organization_id ;
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_payroll_action_id
,p_action_context_type => 'PA'
,p_assignment_id => null
,p_tax_unit_id => p_tax_unit_id
,p_curr_pymt_eff_date => p_effective_date
,p_tab_rec_data => pay_mx_soc_sec_archive.lrr_act_tab
);
pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
SELECT ppe.effective_date
FROM pay_process_events ppe
,pay_event_updates peu
,pay_dated_tables pdt
,pay_element_entry_values_f peev
,pay_input_values_f piv
,pay_element_types_f pet
WHERE ppe.business_group_id = p_business_group_id
AND ppe.assignment_id = p_assignment_id
AND ppe.change_type = 'DATE_EARNED'
AND ppe.creation_date BETWEEN p_start_date
AND p_end_date
AND peu.event_update_id = ppe.event_update_id
AND pdt.dated_table_id = peu.dated_table_id
AND pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
AND ppe.surrogate_key = peev.element_entry_value_id
AND peev.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND pet.element_name = 'Integrated Daily Wage'
AND piv.name = 'IDW Factor Table'
AND pet.legislation_code = 'MX'
AND ppe.effective_date BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND ppe.effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND ppe.effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
UNION
-- Bug 5002283
SELECT ppe.effective_date
FROM pay_process_events ppe
,pay_event_updates peu
,pay_dated_tables pdt
,pay_element_entries_f pee
,pay_element_types_f pet
WHERE ppe.business_group_id = p_business_group_id
AND ppe.assignment_id = p_assignment_id
AND ppe.change_type = 'DATE_EARNED'
AND ppe.creation_date BETWEEN p_start_date
AND p_end_date
AND peu.event_update_id = ppe.event_update_id
AND pdt.dated_table_id = peu.dated_table_id
AND pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
AND ppe.surrogate_key = pee.element_entry_id
AND pee.element_type_id = pet.element_type_id
AND pet.element_name = 'Integrated Daily Wage'
AND pet.legislation_code = 'MX'
AND ppe.effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND ppe.effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT element_entry_id
FROM pay_element_entries_f
WHERE assignment_id = p_assignment_id
AND cp_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT DISTINCT ppe.effective_date
,ppe.description change_values
,ppe.surrogate_key element_entry_id
,ppe.calculation_date
,peu.event_type
FROM pay_process_events ppe
,pay_event_updates peu
,pay_dated_tables pdt
WHERE ppe.business_group_id = cp_business_group_id
AND ppe.assignment_id = cp_assignment_id
AND ppe.change_type = 'DATE_EARNED'
AND ppe.creation_date BETWEEN cp_start_date
AND cp_end_date
AND peu.event_update_id = ppe.event_update_id
AND pdt.dated_table_id = peu.dated_table_id
AND pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
ORDER BY ppe.effective_date;
SELECT element_type_id
,creator_type
,effective_start_date
,effective_end_date
FROM pay_element_entries_f
WHERE element_entry_id = cp_element_entry_id
AND cp_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT 'MX_IDWF' eei_information1
FROM pay_element_type_extra_info
WHERE element_type_id = cp_element_type_id
AND information_type = 'PQP_UK_RATE_TYPE'
AND eei_information_category = 'PQP_UK_RATE_TYPE'
AND ((eei_information1 = 'MX_BASE' AND gv_IDW_calc_method = 'B')
OR (eei_information1 = 'MX_IDWF' AND gv_IDW_calc_method <> 'B'))
UNION ALL
SELECT 'MX_IDWV'
FROM pay_element_types_f pet
,pay_element_classifications pec
,pay_sub_classification_rules_f psr
WHERE pet.element_type_id = cp_element_type_id
AND cp_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND psr.element_type_id = pet.element_type_id
AND cp_effective_date BETWEEN psr.effective_start_date
AND psr.effective_end_date
AND pec.classification_id = psr.classification_id
AND psr.legislation_code = 'MX'
AND INSTR(pec.classification_name,
'Eligible Compensation for IDW (Variable Basis)') > 0;
SELECT replace(org_information1,'-','') Social_Security_ID
FROM hr_organization_information
WHERE org_information_context = 'MX_SOC_SEC_DETAILS'
AND organization_id = cp_organization_id ;
SELECT replace(ppf.per_information3,'-','') emp_ssnumber
FROM per_all_people_f ppf
WHERE ppf.person_id = cp_person_id
-- Bug 6013218
AND cp_effective_date BETWEEN ppf.effective_start_date AND
ppf.effective_end_date;
( SELECT max(ppf_in.effective_start_date)
FROM per_all_people_f ppf_in
WHERE ppf_in.person_id = ppf.person_id
AND trunc(cp_end_date) >= ppf_in.effective_start_date
AND trunc(cp_start_date) <= ppf_in.effective_end_date);*/
SELECT paat.absence_attendance_type_id
,paa.absence_attendance_id
,paa.absence_days
,paa.date_start
,paa.date_end
,paa.abs_information_category
--,paa.abs_information1 disability_type
,paa.abs_information2 disability_id
FROM per_absence_attendance_types paat
,pay_input_values_f piv
,pay_element_entries_f pee
--,pay_element_entry_values_f peev (Bug 5355325)
,per_absence_attendances paa
WHERE paat.business_group_id = cp_business_group_id
AND NVL(paat.information1, 'N')= 'Y'
/*
information1 for MX specifies if absence should be
reported to Social Security
*/
AND piv.input_value_id = paat.input_value_id
AND piv.effective_start_date <= cp_end_date
AND piv.effective_end_date >= cp_start_date
AND piv.element_type_id = cp_element_type_id
AND pee.element_entry_id = cp_element_entry_id
AND pee.assignment_id = cp_assignment_id
AND pee.element_type_id = piv.element_type_id
AND pee.effective_start_date <= cp_end_date
AND pee.effective_end_date >= cp_start_date
--AND peev.element_entry_id = pee.element_entry_id
--AND peev.effective_start_date <= cp_end_date
--AND peev.effective_end_date >= cp_start_date
AND paa.absence_attendance_id = pee.creator_id
AND paa.person_id = cp_person_id
AND paa.absence_attendance_type_id = paat.absence_attendance_type_id
AND paa.date_start BETWEEN cp_start_date
AND cp_end_date;
SELECT eei_information1
FROM pay_element_type_extra_info
WHERE element_type_id = cp_element_type_id
AND information_type = 'MX_DEDUCTION_PROCESSING'
AND eei_information_category = 'MX_DEDUCTION_PROCESSING'
AND eei_information1 = 'INFONAVIT';
SELECT piv.name
,piv.input_value_id
,pee.element_entry_id
,pee.assignment_id
,peev.screen_entry_value
,pee.effective_start_date
,pee.effective_end_date
FROM pay_input_values_f piv
,pay_element_entries_f pee
,pay_element_entry_values_f peev
WHERE piv.effective_start_date <= cp_end_date
AND piv.effective_end_date >= cp_start_date
AND piv.element_type_id = cp_element_type_id
AND pee.element_entry_id = cp_element_entry_id
AND pee.assignment_id = cp_assignment_id
AND pee.element_type_id = piv.element_type_id
AND pee.effective_start_date <= cp_end_date
AND pee.effective_end_date >= cp_start_date
AND peev.element_entry_id = pee.element_entry_id
AND peev.effective_start_date <= cp_end_date
AND peev.effective_end_date >= cp_start_date
AND pee.effective_start_date = peev.effective_start_date
AND pee.effective_end_date = peev.effective_end_date
AND peev.input_value_id = piv.input_value_id
ORDER BY piv.display_sequence;
SELECT element_entry_id
FROM pay_element_entries_f
WHERE assignment_id = p_assignment_id
AND element_type_id = cp_element_type_id
AND effective_start_date BETWEEN cp_start_date AND cp_end_date;
SELECT pdf.degree,
pdf.dis_information2 subsidized_days,
pdf.dis_information3 disability_type,
pdf.dis_information4 consequence,
DECODE(pdf.dis_information5,'6','7','7','8','8','9',pdf.dis_information5) disability_control,
pdf.incident_id
FROM per_disabilities_f pdf
WHERE pdf.person_id = p_person_id
AND pdf.registration_id = cp_registration_id
AND p_effective_date BETWEEN pdf.effective_start_date
AND pdf.effective_end_date;
SELECT pwi.inc_information1 risk_type
FROM per_work_incidents pwi
WHERE pwi.person_id = p_person_id
AND pwi.incident_id = cp_incident_id;
SELECT max(ppe.effective_date)
FROM pay_process_events ppe ,
pay_event_updates peu ,
pay_dated_tables pdt
WHERE ppe.business_group_id = p_business_group_id
AND ppe.assignment_id = p_assignment_id
AND ppe.change_type = 'DATE_EARNED'
AND peu.event_update_id = ppe.event_update_id
AND pdt.dated_table_id = peu.dated_table_id
AND ((pdt.table_name = 'PAY_ELEMENT_ENTRIES_F')
OR (pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'))
AND peu.event_type = 'INSERT';
SELECT piv.name
,piv.input_value_id
,pee.element_entry_id
,pee.assignment_id
,peev.screen_entry_value
,pee.effective_start_date
,pee.effective_end_date
FROM pay_input_values_f piv
,pay_element_entries_f pee
,pay_element_entry_values_f peev
WHERE piv.effective_start_date <= p_end_date
AND piv.effective_end_date >= p_start_date
AND piv.element_type_id = p_element_type_id
AND pee.element_entry_id = p_element_entry_id
AND pee.assignment_id = p_assignment_id
AND pee.element_type_id = piv.element_type_id
AND pee.effective_start_date <= p_end_date
AND pee.effective_end_date >= p_start_date
AND peev.element_entry_id = pee.element_entry_id
AND peev.effective_start_date <= p_end_date
AND peev.effective_end_date >= p_start_date
AND pee.effective_start_date = peev.effective_start_date
AND pee.effective_end_date = peev.effective_end_date
AND peev.input_value_id = piv.input_value_id
ORDER BY piv.display_sequence;
SELECT DECODE (c_infonavit_info_rec.screen_entry_value,
'Y', 'S',
'N') INTO trn(p_index).redxn_table_applies
FROM DUAL;
SELECT piv.name
,piv.input_value_id
,pee.element_entry_id
,pee.assignment_id
,peev.screen_entry_value
,pee.effective_start_date
,pee.effective_end_date
FROM pay_input_values_f piv
,pay_element_entries_f pee
,pay_element_entry_values_f peev
WHERE piv.effective_start_date <= cp_end_date
AND piv.effective_end_date >= cp_start_date
AND piv.element_type_id = cp_element_type_id
AND pee.element_entry_id = cp_element_entry_id
AND pee.assignment_id = cp_assignment_id
AND pee.element_type_id = piv.element_type_id
AND pee.effective_start_date <= cp_end_date
AND pee.effective_end_date >= cp_start_date
AND peev.element_entry_id = pee.element_entry_id
AND peev.effective_start_date <= cp_end_date
AND peev.effective_end_date >= cp_start_date
AND pee.effective_start_date = peev.effective_start_date
AND pee.effective_end_date = peev.effective_end_date
AND peev.input_value_id = piv.input_value_id
ORDER BY piv.display_sequence;
fix_var_idw.DELETE;
fix_var_idw_uniq.DELETE;
hr_utility_trace('Timestamp of IDW table update event = '||
fnd_date.date_to_canonical(ld_effective_date));
SELECT ADD_MONTHS (TRUNC (p_end_date, 'Y'),
MONTHS_BETWEEN (ld_hire_anniversary,
TRUNC (ld_hire_anniversary, 'Y'))
) +
(ld_hire_anniversary - TRUNC (ld_hire_anniversary, 'MM'))
INTO ld_anniversary_date
FROM dual;
fix_var_idw_uniq.DELETE;
fix_var_idw.DELETE;
fix_var_idw_uniq.DELETE;
SELECT segment10
FROM per_all_assignments_f paf
,hr_soft_coding_keyflex hck
WHERE paf.assignment_id = cp_asg_id
AND paf.soft_coding_keyflex_id = hck.soft_coding_keyflex_id;
SELECT segment1
,assignment_number
FROM per_all_assignments_f paf
,hr_soft_coding_keyflex hck
WHERE paf.assignment_id = cp_asg_id
AND cp_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.soft_coding_keyflex_id = hck.soft_coding_keyflex_id;
SELECT location_id
,assignment_number
FROM per_all_assignments_f
WHERE assignment_id = cp_asg_id
AND cp_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT replace(org_information1,'-','') Social_Security_ID
FROM hr_organization_information
WHERE org_information_context = 'MX_SOC_SEC_DETAILS'
AND organization_id = cp_organization_id ;
SELECT replace(ppf.per_information3,'-','') emp_ssnumber
FROM per_all_people_f ppf
WHERE ppf.person_id = cp_person_id
-- Bug 6013218
AND cp_effective_date BETWEEN ppf.effective_start_date AND
ppf.effective_end_date;
( SELECT max(ppf_in.effective_start_date)
FROM per_all_people_f ppf_in
WHERE ppf_in.person_id = ppf.person_id
AND trunc(cp_end_date) >= ppf_in.effective_start_date
AND trunc(cp_start_date) <= ppf_in.effective_end_date);*/
SELECT aei_information3
FROM per_assignment_extra_info pae
WHERE pae.assignment_id = cp_assignment_id
AND information_type = 'MX_SS_EMP_TRANS_REASON'
AND fnd_date.canonical_to_date(aei_information1) = cp_effective_date
AND aei_information2 = cp_gre_id ;
SELECT pds_information1
,actual_termination_date
FROM per_periods_of_service ppos,
per_all_assignments_f paf
WHERE paf.assignment_id = cp_assignment_id
AND paf.person_id = ppos.person_id
AND cp_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND pds_information_category='MX';
SELECT per_system_status
FROM per_assignment_status_types
WHERE assignment_status_type_id = cp_asg_status_type_id;
SELECT 'X'
FROM per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND cp_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf.location_id
,paf.business_group_id
,paf.soft_coding_keyflex_id
,cp_effective_date) = p_gre_id;
SELECT 'Y'
FROM per_assignments_f paf,
per_assignment_status_types pst
WHERE paf.person_id = p_person_id
--AND paf.assignment_id <> p_assignment_id
AND paf.assignment_status_type_id = pst.assignment_status_type_id
AND ((cp_effective_date < paf.effective_end_date AND
cp_tran_type = '02' AND
-- Bug 6019466
pst.per_system_status = 'ACTIVE_ASSIGN') OR
(cp_effective_date > paf.effective_start_date AND
cp_tran_type = '08'))
AND paf.assignment_type = 'E'
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf.location_id
,paf.business_group_id
,paf.soft_coding_keyflex_id
,cp_effective_date) = p_gre_id
AND EXISTS (SELECT 1
FROM per_all_people_f per
WHERE per.person_id = paf.person_id
AND cp_effective_date
BETWEEN per.effective_start_date AND per.effective_end_date
AND NVL(per.current_applicant_flag,'N') <> 'Y'
AND paf.effective_start_date BETWEEN per.effective_start_date AND per.effective_end_date);
SELECT per.current_applicant_flag
FROM per_all_people_f per
WHERE per.person_id = p_person_id
AND fnd_date.canonical_to_date(p_effective_date)
BETWEEN per.effective_start_date AND per.effective_end_date;
SELECT hck.segment6
FROM per_all_assignments_f paf
,hr_soft_coding_keyflex hck
WHERE paf.assignment_id = cp_assignment_id
AND paf.soft_coding_keyflex_id = hck.soft_coding_keyflex_id
AND cp_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date ;
hire_sep.DELETE;
hr_utility_trace('Update_type : '||
p_asg_events(i).update_type );
IF p_asg_events(i).update_type = 'I' THEN
hr_utility.set_location(gv_package || lv_procedure_name, 30);
/*IF ( p_asg_events(1).update_type <> 'I' AND
p_asg_events(i).update_type <> 'I' ) THEN*/
IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
THEN
hr_utility.set_location(gv_package||lv_procedure_name,100);
SELECT COUNT(*)
INTO ln_asg_count
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND effective_start_date = p_asg_events(i).effective_date;
date-tracked update to the asg record. We need to ignore
EFFECTIVE_END_DATE events if assignment is not terminated.
(Bug 5888285)*/
lv_old_asg_status := NULL;
hire_sep_uniq.DELETE;
hire_sep.DELETE;
hire_sep_uniq.DELETE;
SELECT event_group_id
FROM pay_event_groups
WHERE event_group_name = cp_event_group_name;
SELECT paf.assignment_id
,paf.location_id
,paf.soft_coding_keyflex_id
,paf.effective_start_date
,paf.effective_end_date
FROM per_all_assignments_f paf
WHERE paf.assignment_id = cp_assignment_id
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf.location_id
,paf.business_group_id
,paf.soft_coding_keyflex_id
,trunc(cp_end_date)) = cp_gre_id
ORDER BY paf.assignment_id
,paf.effective_start_date desc
,paf.effective_end_date desc;
SELECT effective_end_date
FROM per_assignments_f pa
WHERE pa.assignment_id = p_assignment_id
AND pa.effective_end_date = cp_effective_date
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
location_id
,business_group_id
,soft_coding_keyflex_id
,cp_effective_date) = p_gre_id
AND EXISTS (SELECT 1
FROM per_all_people_f per
WHERE per.person_id = pa.person_id
AND cp_effective_date
BETWEEN per.effective_start_date AND per.effective_end_date
AND NVL(per.current_applicant_flag,'N') <> 'Y');
asg_events.DELETE;
per_events.DELETE;
ele_events.DELETE;
eev_events.DELETE;
SELECT ppf.person_id person_id
,replace(ppf.per_information3,'-','') emp_ssnumber
,ppf.last_name paternal_last_name
,ppf.per_information1 maternal_last_name
,rtrim(ppf.first_name || ' ' || ppf.middle_names) emp_name
,ppf.per_information4 medical_center
,ppf.employee_number worker_id
,ppf.national_identifier curp
,ppf.per_information2 tax_rfc_id
,fnd_date.date_to_canonical(ppf.effective_start_date) hire_date
FROM per_all_people_f ppf
WHERE ppf.person_id = cp_person_id
AND ppf.effective_start_date =
( SELECT max(ppf_in.effective_start_date)
FROM per_all_people_f ppf_in
WHERE ppf_in.person_id = ppf.person_id
AND ppf_in.effective_start_date <= cp_effective_date);
SELECT paf.location_id
,paf.soft_coding_keyflex_id
,substr(paf.employment_category,3,1) worker_type
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 max(paf_in.effective_start_date)
FROM per_all_assignments_f paf_in
WHERE paf_in.assignment_id = paf.assignment_id
AND trunc(cp_end_date) >= paf_in.effective_start_date
AND trunc(cp_start_date) <= paf_in.effective_end_date);*/
SELECT hsc.segment6 salary_type
,puc.user_column_name work_schedule
FROM hr_soft_coding_keyflex hsc,
pay_user_columns puc
WHERE hsc.soft_coding_keyflex_id = cp_soft_cod_kflx_id
AND hsc.segment4 = puc.user_column_id(+);
SELECT location_code
FROM hr_locations_all
WHERE location_id = cp_location_id;
SELECT paf.effective_start_date,
paf.effective_end_date
FROM per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.effective_start_date = (SELECT max(paf_in.effective_start_date)
FROM per_assignments_f paf_in
WHERE paf_in.assignment_id =
paf.assignment_id
-- Bug 5908010
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf_in.location_id,
paf_in.business_group_id,
paf_in.soft_coding_keyflex_id,
paf_in.effective_start_date) = p_gre_id);
SELECT pee.element_entry_id
,pee.element_type_id
FROM pay_element_entries_f pee
,per_all_assignments_f paf
WHERE paf.person_id = cp_person_id
AND cp_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND pee.assignment_id = paf.assignment_id + 0
AND cp_effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND EXISTS ( SELECT 1
FROM pay_element_type_extra_info petei
WHERE petei.information_type = 'MX_DEDUCTION_PROCESSING'
AND petei.eei_information_category =
'MX_DEDUCTION_PROCESSING'
AND petei.eei_information1 = 'INFONAVIT'
AND petei.element_type_id = pee.element_type_id )
ORDER BY pee.effective_start_date desc;
SELECT piv.name, peev.screen_entry_value
FROM pay_element_entry_values_f peev
,pay_input_values_f piv
WHERE piv.element_type_id = cp_element_type_id
AND peev.element_entry_id = cp_element_entry_id
AND piv.input_value_id = peev.input_value_id
AND cp_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date;
SELECT count(*) into ln_check_person_info_exist
FROM pay_action_information
WHERE action_context_type='AAP'
AND assignment_id = p_assignment_id
AND tax_unit_id = p_gre_id
AND action_information_category = 'MX SS PERSON INFORMATION'
AND ld_asg_start_date >=fnd_date.canonical_to_date(action_information10);
SELECT hoi.org_information10
FROM hr_organization_units hou,
hr_organization_information hoi
WHERE hou.organization_id = p_org_id
AND hoi.org_information_context ='MX_SOC_SEC_DETAILS'
AND hou.organization_id = hoi.organization_id
AND p_effective_date BETWEEN hou.date_from
AND nvl(hou.date_to,p_effective_date);
SELECT 'Y'
FROM pay_process_events ppe
WHERE ppe.assignment_id = p_assignment_id
AND ppe.creation_date BETWEEN p_start_date AND p_end_date
AND rownum = 1;
select 'Y'
from pay_element_entries_f pee
,pay_sub_classification_rules_f psc
,pay_element_classifications pec
,pay_assignment_actions paa
,pay_payroll_actions ppa2
WHERE pee.assignment_id = p_assignment_id
AND pee.effective_start_date <= p_periodic_end_date
AND pee.effective_end_date >= p_periodic_start_date
AND psc.business_group_id = p_business_group_id
AND psc.element_type_id = pee.element_type_id
AND psc.effective_start_date <= p_periodic_end_date
AND psc.effective_end_date >= p_periodic_start_date
AND pec.classification_id = psc.classification_id
AND pec.classification_name LIKE '%Eligible Compensation for IDW (Variable Basis)'
AND paa.assignment_id = p_assignment_id
AND ppa2.payroll_action_id =paa.payroll_action_id
AND ppa2.effective_date BETWEEN p_periodic_start_date AND p_periodic_end_date
AND ppa2.action_type in ('R', 'Q', 'B', 'V' )
AND EXISTS ( SELECT 1
FROM pay_run_results prr
WHERE prr.assignment_action_id = paa.assignment_action_id
AND prr.element_type_id = pee.element_type_id)
AND ROWNUM = 1;
SELECT 'Y'
FROM pay_payroll_actions ppa_prev
,pay_assignment_actions paa_prev
WHERE ppa_prev.report_type = 'SS_ARCHIVE'
AND ppa_prev.report_qualifier = 'SS_ARCHIVE'
AND ppa_prev.report_category = 'RT'
AND pay_mx_utility.get_legi_param_val('GRE',
ppa_prev.legislative_parameters) = p_gre_id
AND TRUNC( fnd_date.canonical_to_date (
pay_mx_utility.get_legi_param_val(
'PERIOD_ENDING_DATE',
ppa_prev.legislative_parameters) ) ) =
TRUNC(p_periodic_end_date)
AND paa_prev.payroll_action_id = ppa_prev.payroll_action_id
AND paa_prev.assignment_id = p_assignment_id
AND pay_mx_utility.get_legi_param_val('MX_IDWV',
paa_prev.serial_number) = 'Y';
SELECT COUNT(*)
INTO gn_implementation
FROM pay_payroll_actions
WHERE report_type = 'SS_ARCHIVE'
AND report_qualifier = 'SS_ARCHIVE'
AND report_category = 'RT'
AND pay_mx_utility.get_legi_param_val('GRE', legislative_parameters )
= ln_gre_id
AND payroll_action_id + 0 < p_payroll_action_id;
'SELECT DISTINCT paf.person_id
FROM per_assignments_f paf,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :p_payroll_action_id
AND paf.business_group_id = ppa.business_group_id
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf.location_id
,paf.business_group_id
,paf.soft_coding_keyflex_id
,ppa.effective_date) = '||ln_gre_id|| ' '||
'AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
ORDER BY paf.person_id';
'SELECT DISTINCT person_id FROM ( '||
'SELECT paf.person_id
FROM per_assignments_f paf
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :p_payroll_action_id
AND paf.business_group_id = ppa.business_group_id
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf.location_id
,paf.business_group_id
,paf.soft_coding_keyflex_id
,ppa.effective_date) = '||ln_gre_id|| ') '||
/*'AND '''|| gv_mode || ''' = ''P'' ) ' ||*/
'ORDER BY person_id';
update pay_payroll_actions
set effective_date = fnd_date.canonical_to_date('4712/12/31')
where payroll_action_id = p_payroll_action_id;
select name
into lv_gre_name
from hr_all_organization_units
where organization_id = ln_gre_id;
SELECT paf.person_id
,decode(paf.primary_flag, 'Y', 'Y', 'Z')
,paf.assignment_id
,'N' variable_idw
FROM pay_payroll_actions ppa
,per_assignments_f paf
WHERE ppa.payroll_action_id = cp_payroll_action_id
AND ppa.business_group_id = cp_business_group_id
AND paf.business_group_id = ppa.business_group_id
AND paf.person_id BETWEEN cp_start_person_id
AND cp_end_person_id
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf.location_id
,paf.business_group_id
,paf.soft_coding_keyflex_id
,ppa.effective_date) = cp_gre_id
UNION ALL
SELECT paf.person_id
,decode(paf.primary_flag, 'Y', 'Y', 'Z')
,paf.assignment_id
,'Y' variable_idw
FROM per_assignments_f paf
,pay_payroll_actions ppa
,pay_element_entries_f pee
,pay_sub_classification_rules_f psc
,pay_element_classifications pec
,pay_assignment_actions paa
,pay_payroll_actions ppa2
WHERE ppa.payroll_action_id = cp_payroll_action_id
AND paf.business_group_id = ppa.business_group_id
AND paf.person_id BETWEEN cp_start_person_id
AND cp_end_person_id
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf.location_id
,paf.business_group_id
,paf.soft_coding_keyflex_id
,ppa.effective_date) = cp_gre_id
AND pee.assignment_id = paf.assignment_id
AND pee.effective_start_date <= cp_periodic_end_date
AND pee.effective_end_date >= cp_periodic_start_date
AND psc.business_group_id = ppa.business_group_id
AND psc.element_type_id = pee.element_type_id
AND psc.effective_start_date <= cp_periodic_end_date
AND psc.effective_end_date >= cp_periodic_start_date
AND pec.classification_id = psc.classification_id
AND pec.classification_name LIKE
'%Eligible Compensation for IDW (Variable Basis)'
AND paa.assignment_id = paf.assignment_id
AND ppa2.payroll_action_id = paa.payroll_action_id
AND ppa2.effective_date BETWEEN cp_periodic_start_date
AND cp_periodic_end_date
AND ppa2.action_type in ( 'R', 'Q', 'B', 'V' )
AND EXISTS (SELECT 1 FROM pay_run_results prr
WHERE prr.assignment_action_id = paa.assignment_action_id
AND prr.element_type_id = pee.element_type_id )
AND NOT EXISTS (
SELECT 1
FROM pay_payroll_actions ppa_prev
,pay_assignment_actions paa_prev
WHERE ppa_prev.report_type = 'SS_ARCHIVE'
AND ppa_prev.report_qualifier = 'SS_ARCHIVE'
AND ppa_prev.report_category = 'RT'
AND pay_mx_utility.get_legi_param_val('GRE',
ppa_prev.legislative_parameters) = cp_gre_id
AND TRUNC( fnd_date.canonical_to_date (
pay_mx_utility.get_legi_param_val( 'PERIOD_ENDING_DATE',
ppa_prev.legislative_parameters) ) ) =
TRUNC(fnd_date.canonical_to_date(cp_periodic_end_date))
AND paa_prev.payroll_action_id = ppa_prev.payroll_action_id
AND paa_prev.assignment_id = paf.assignment_id
AND pay_mx_utility.get_legi_param_val('MX_IDWV',
paa_prev.serial_number) = 'Y' )
AND gv_mode = 'P'
ORDER BY 1, 2, 3, 4 desc;
SELECT paf.person_id
,decode(paf.primary_flag, 'Y', 'Y', 'Z')
,paf.assignment_id
,'N' variable_idw
FROM pay_payroll_actions ppa
,per_assignments_f paf
,pay_population_ranges ppr
WHERE ppa.payroll_action_id = cp_payroll_action_id
AND ppr.payroll_action_id = ppa.payroll_action_id
AND ppr.chunk_number = cp_chunk_number
AND paf.person_id = ppr.person_id
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf.location_id
,paf.business_group_id
,paf.soft_coding_keyflex_id
,ppa.effective_date) = cp_gre_id
UNION ALL
SELECT paf.person_id
,decode(paf.primary_flag, 'Y', 'Y', 'Z')
,paf.assignment_id
,'Y' variable_idw
FROM pay_payroll_actions ppa
,per_assignments_f paf
,pay_population_ranges ppr
,pay_element_entries_f pee
,pay_sub_classification_rules_f psc
,pay_element_classifications pec
,pay_assignment_actions paa
,pay_payroll_actions ppa2
WHERE ppa.payroll_action_id = cp_payroll_action_id
AND ppr.payroll_action_id = ppa.payroll_action_id
AND ppr.chunk_number = cp_chunk_number
AND paf.person_id = ppr.person_id
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf.location_id
,paf.business_group_id
,paf.soft_coding_keyflex_id
,ppa.effective_date) = cp_gre_id
AND pee.assignment_id = paf.assignment_id
AND pee.effective_start_date <= cp_periodic_end_date
AND pee.effective_end_date >= cp_periodic_start_date
AND psc.business_group_id = ppa.business_group_id
AND psc.element_type_id = pee.element_type_id
AND psc.effective_start_date <= cp_periodic_end_date
AND psc.effective_end_date >= cp_periodic_start_date
AND pec.classification_id = psc.classification_id
AND pec.classification_name LIKE
'%Eligible Compensation for IDW (Variable Basis)'
AND paa.assignment_id = paf.assignment_id
AND ppa2.payroll_action_id = paa.payroll_action_id
AND ppa2.effective_date BETWEEN cp_periodic_start_date
AND cp_periodic_end_date
AND ppa2.action_type in ( 'R', 'Q', 'B', 'V' )
AND EXISTS (SELECT 1 FROM pay_run_results prr
WHERE prr.assignment_action_id = paa.assignment_action_id
AND prr.element_type_id = pee.element_type_id )
AND NOT EXISTS (
SELECT 1
FROM pay_payroll_actions ppa_prev
,pay_assignment_actions paa_prev
WHERE ppa_prev.report_type = 'SS_ARCHIVE'
AND ppa_prev.report_qualifier = 'SS_ARCHIVE'
AND ppa_prev.report_category = 'RT'
AND pay_mx_utility.get_legi_param_val('GRE',
ppa_prev.legislative_parameters) = cp_gre_id
AND TRUNC( fnd_date.canonical_to_date (
pay_mx_utility.get_legi_param_val( 'PERIOD_ENDING_DATE',
ppa_prev.legislative_parameters) ) ) =
TRUNC(fnd_date.canonical_to_date(cp_periodic_end_date))
AND paa_prev.payroll_action_id = ppa_prev.payroll_action_id
AND paa_prev.assignment_id = paf.assignment_id
AND pay_mx_utility.get_legi_param_val('MX_IDWV',
paa_prev.serial_number) = 'Y' )
AND gv_mode = 'P'
ORDER BY 1, 2, 3, 4 desc;*/
SELECT paf.person_id
,decode(paf.primary_flag, 'Y', 'Y', 'Z')
,paf.assignment_id
,'N' variable_idw
FROM pay_payroll_actions ppa
,per_assignments_f paf
WHERE ppa.payroll_action_id = cp_payroll_action_id
AND ppa.business_group_id = cp_business_group_id
AND paf.business_group_id = ppa.business_group_id
AND paf.person_id BETWEEN cp_start_person_id
AND cp_end_person_id
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf.location_id
,paf.business_group_id
,paf.soft_coding_keyflex_id
,ppa.effective_date) = cp_gre_id
AND validate_person_id(paf.person_id
,paf.assignment_id
,cp_start_date
,cp_end_date
,cp_periodic_start_date
,cp_periodic_end_date
,cp_payroll_action_id
,cp_gre_id
,cp_business_group_id
,'N') = 'Y'
UNION ALL
SELECT paf.person_id
,decode(paf.primary_flag, 'Y', 'Y', 'Z')
,paf.assignment_id
,'Y' variable_idw
FROM per_assignments_f paf
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = cp_payroll_action_id
AND ppa.business_group_id = cp_business_group_id
AND paf.business_group_id = ppa.business_group_id
AND paf.person_id BETWEEN cp_start_person_id
AND cp_end_person_id
AND gv_mode = 'P'
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf.location_id
,paf.business_group_id
,paf.soft_coding_keyflex_id
,ppa.effective_date) = cp_gre_id
AND validate_person_id(
paf.person_id
,paf.assignment_id
,cp_start_date
,cp_end_date
,cp_periodic_start_date
,cp_periodic_end_date
,cp_payroll_action_id
,cp_gre_id
,cp_business_group_id
,'Y') = 'Y'
ORDER BY 1, 2, 3, 4 desc;
SELECT paf.person_id
,decode(paf.primary_flag, 'Y', 'Y', 'Z')
,paf.assignment_id
,'N' variable_idw
FROM pay_payroll_actions ppa
,per_assignments_f paf
,pay_population_ranges ppr
WHERE ppa.payroll_action_id = cp_payroll_action_id
AND ppr.payroll_action_id = ppa.payroll_action_id
AND ppr.chunk_number = cp_chunk_number
AND paf.person_id = ppr.person_id
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf.location_id
,paf.business_group_id
,paf.soft_coding_keyflex_id
,ppa.effective_date) = cp_gre_id
AND validate_person_id(
paf.person_id
,paf.assignment_id
,cp_start_date
,cp_end_date
,cp_periodic_start_date
,cp_periodic_end_date
,cp_payroll_action_id
,cp_gre_id
,cp_business_group_id
,'N') = 'Y'
UNION ALL
SELECT paf.person_id
,decode(paf.primary_flag, 'Y', 'Y', 'Z')
,paf.assignment_id
,'Y' variable_idw
FROM pay_payroll_actions ppa
,per_assignments_f paf
,pay_population_ranges ppr
WHERE ppa.payroll_action_id = cp_payroll_action_id
AND ppr.payroll_action_id = ppa.payroll_action_id
AND ppr.chunk_number = cp_chunk_number
AND paf.person_id = ppr.person_id
AND gv_mode = 'P'
AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf.location_id
,paf.business_group_id
,paf.soft_coding_keyflex_id
,ppa.effective_date) = cp_gre_id
AND validate_person_id(
paf.person_id
,paf.assignment_id
,cp_start_date
,cp_end_date
,cp_periodic_start_date
,cp_periodic_end_date
,cp_payroll_action_id
,cp_gre_id
,cp_business_group_id
,'Y') = 'Y'
ORDER BY 1, 2, 3, 4 desc;
SELECT pay_assignment_actions_s.nextval
INTO ln_asg_act_id
FROM dual;
hr_utility_trace('--> Inserting into pay_assignment_actions' );
UPDATE pay_assignment_actions
SET serial_number = to_char(ln_person_id) || '|' ||
'MX_IDWV=' || lv_report_mode || '|'
WHERE assignment_action_id = ln_asg_act_id;
SELECT count(*)
INTO ln_events_found
FROM pay_process_events
WHERE assignment_id = ln_assignment_id
AND last_update_date
BETWEEN fnd_date.canonical_to_date(lv_start_date)
AND fnd_date.canonical_to_date(lv_end_date);
SELECT pay_assignment_actions_s.nextval
INTO ln_asg_act_id
FROM dual;
hr_utility_trace('--> insert into pay_assignment_actions.' );
UPDATE pay_assignment_actions
SET serial_number = to_char(ln_person_id) || '|' ||
'MX_IDWV=' || lv_report_mode || '|'
WHERE assignment_action_id = ln_asg_act_id;
pay_mx_soc_sec_archive.per_asg.delete;
SELECT paa.payroll_action_id
,paa.assignment_id
,paa.tax_unit_id
,paa.chunk_number
,paa.serial_number
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = cp_assignment_action;
SELECT count(*)
FROM pay_action_information
WHERE action_context_id = cp_payroll_action_id
AND action_context_type = 'PA';
SELECT 'Y'
FROM per_all_people_f per
WHERE per.person_id = p_person_id
AND fnd_date.canonical_to_date(p_effective_date)
BETWEEN per.effective_start_date AND per.effective_end_date
AND per.current_employee_flag = 'Y';
pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
l_act_tab.DELETE;
pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
l_act_tab.DELETE;
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_asg_action_id
,p_action_context_type => 'AAP'
,p_assignment_id => ln_assignment_id
,p_tax_unit_id => ln_gre_id
,p_curr_pymt_eff_date => ld_end_date
,p_tab_rec_data => pay_mx_soc_sec_archive.lrr_act_tab
);
pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
SELECT dated_table_id
,table_name
FROM pay_dated_tables;
SELECT pay_mx_utility.get_legi_param_val('GRE',
LEGISLATIVE_PARAMETERS) GRE,
fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val(
'END_DATE',
LEGISLATIVE_PARAMETERS)) END_DATE
FROM pay_payroll_actions
WHERE payroll_action_id = cp_payroll_action_id;
SELECT COUNT(*)
INTO gn_implementation
FROM pay_payroll_actions
WHERE report_type = 'SS_ARCHIVE'
AND report_qualifier = 'SS_ARCHIVE'
AND report_category = 'RT'
AND pay_mx_utility.get_legi_param_val('GRE', legislative_parameters )
= ln_gre_id
AND payroll_action_id + 0 < p_payroll_action_id;
advanced further; it cannot be updated by a potential retry attempt. */
hr_utility_trace ('pay_recorded_requests not updated.');
gt_gre_cache.delete();
SELECT 'Y'
FROM pay_upgrade_status pus,
pay_upgrade_definitions pud
WHERE pud.upgrade_definition_id = pus.upgrade_definition_id
AND pus.business_group_id = p_business_group_id
AND pud.short_name = 'MX_SS_ARCH_TRAN_DATE'
AND pus.status = 'C';
SELECT 'Y'
FROM pay_payroll_actions ppa
WHERE ppa.report_type = 'SS_ARCHIVE'
AND ppa.report_qualifier = 'SS_ARCHIVE'
AND ppa.report_category = 'RT'
AND ppa.business_group_id = p_business_group_id;