[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
which was selected at the
parameter window.
4649954 Union Worker should be archived as
"N" when collective agreement on
assignment form is null.
115.4 14-OCT-2005 sdahiya - Removed action_status = 'C' check
- Modified range code to pick
terminated/re-hired persons too.
- Added missing join condition
for effective dates in cursor
c_get_ytd_aaid.
- Added join with
pay_action_classifications in
c_get_ytd_aaid.
115.5 18-OCT-2005 ardsouza Modified to store start and end
dates instead of months.
Collective agreement of all
assignments for the person checked
to derive Union Worker flag.
115.6 24-OCT-2005 ardsouza - Modified to stamp 31st Dec on
archive record for active EEs.
- State ID archived.
- 31st Dec always used for fetching
latest YTD aaid .
4690778 - Seniority archived as null for
Active EEs.
- Person to be picked up if any
assignment found in assignment set.
4687345 - Added date check in cursor
c_get_emp_asg_range.
4693525 - Corrected calculation for Tax
Subsidy Proportion.
115.7 26-OCT-2005 ardsouza - Modified cursors c_get_emp_asg
and c_get_emp_asg_range to create
multiple assignment actions for a
re-hired person, if archiver not
already run for previous stint.
- Relaxed date constraint on
c_get_ytd_aaid to allow terminated
EEs.
4703130 Hyphens not used for validation in
ER RFC.
115.8 02-NOV-2005 ardsouza 4712450 - Subsidy Proportion applied only
if a different one used and if
Annual Tax Adjustment is run.
- Archived "ISR Exempt by Previous
ER".
- Rounded Subsidy Proportion to 4
places instead of 2.
115.9 03-NOV-2005 ardsouza 4693525 - Reverted changes made in 115.6
for "Subsidy Proportion Applied".
The changes are needed only for
"Subsidy Proportion".
115.10 07-NOV-2005 ardsouza - Annual Tax Adj Run checked only
for "Subsidy Proportion Applied"
and not "Subsidy Proportion".
115.11 14-DEC-2005 ardsouza - Modified to allow multiple runs
of Archiver for same period of
service as long as payroll runs
exist after the last archiver was
run.
- Effective date of balance calls
to be the effective date specified
as parameter.
- The second archiver run would
always lock the first.
115.12 06-JAN-2006 vpandya Replace get_seniority function with
get_seniority_social_security to
get seniority years.
115.13 12-JAN-2006 ardsouza 4938724 - Modified to use p_effective_date
as the effective_date for all
purposes.
115.14 17-JAN-2006 ardsouza 4960302 - Termination Date would still be
used to fetch person details.
4956977 Reverted changes in 115.12.
115.15 17-JAN-2006 ardsouza Bumped version to fix arcs message.
115.16 25-JAN-2006 ardsouza 4998030 Corrected c_chk_last_archiver.
115.17 02-FEB-2006 ardsouza 5004297 ' ' to be
stored under "Names".
5002968 Seniority not archived if archiver
is run for PTU, even for ex-EEs.
115.18 06-FEB-2006 ardsouza 5019199 R,Q,B,V,I actions after the prev
archiver would be detected based on
effective date rather than action
sequence because Archiver itself
is a Non-Sequenced action.
115.19 06-FEB-2006 ardsouza 5019199 Fix in 115.18 modified to restrict
R,Q,B,V,I actions only upto the
effective date of the archiver.
115.20 13-FEB-2006 vpandya 5035094 Changed populate_balances:
When YREND Archiver run previously
and it is run again for PTU,
ISR Withheld would be
ISR Withheld YTD - ISR Withheld of
previous archived value.
115.21 15-FEB-2006 ardsouza 5002968 Seniority displayed as 0 instead
of NULL, when not needed.
115.22 10-MAR-2006 ardsouza PL-SQL table g_gre_tab made public
for use within "PAY_MX_PTU_CALC".
115.23 04-MAY-2006 ardsouza 5205255 Removed unwanted table references
in cursor c_chk_asg.
115.24 02-AUG-2006 sbairagi 5042700 Cursor c_get_emp_asg of procedure
assignment_action_code is tuned.
115.25 03-AUG-2006 vpandya same as 115.24. Arcsed in 120
version mistakenly. Got error and
corrected in 115.25.
115.26 07-AUG-2006 nragavar 5457394 Archive_code to archive 'Y' where
there exists AnnTaxAdj process run.
Pkg has been modified to take out
all un-wanted comments to make the
package more readable. Procedure
archive_code has been modifed to
consider the action_status to 'C'
ie to select the assignments that
had been processed successfully.
115.28 20-Sep-2006 nragavar 5552748 added code to archive two flags
RATE_1991_IND,RATE_FISCAL_YEAR_IND
115.29 26-Sep-2006 vmehta 5565656 Changed the logic for identifying
union member. Use the
LABOUR_UNION_MEMBER_FLAG instead
of collective agreement lookup.
115.30 06-Dec-2006 vpandya 5701000 Changed assignment_action_code.
Initializing previous archiver date
and asg act id for each assignment.
115.31 06-Dec-2006 vpandya 5701701 Changed archive_code:
Taking greatest of hire date and
archiver start date. Also taking
least of archiver end date and
p_effective_date.
115.32 03-Jan-2007 vpandya 5714195 Changed assignment_action_code:
cursors c_chk_last_archiver and
c_chk_non_arch_runs.
Also changed archive_code:
added a condition where date for
PTU is populating.
115.33 11-Sep-2007 nrgavar 5923989 Modified to archive ISR Calculated,
Creditable Subsidy and
non-creditable subsidy.
115.34 17-Sep-2007 vpandya 5002968 Changed archive_date: seniority
should not be archived for term-ee
when YREND arch is run only 4 PTU.
as mentioned in 115.17
115.36 25-Feb-2008 nragavar 6807997 modified the function archive_code
115.37 25-Feb-2008 nragavar 6807997 modified the function populate_balances
115.41 26-Feb-2009 sivanara 7529502 Changed techinal logic for performance
issue.
115.42 08-Apr-2009 sivanara 8402505 Modified cursor c_get_eff_date, to get
actual termination date also.
115.43 17-Apr-2009 sivanara 8402464 Added condition
ld_PUT_DATE > ld_arch_end_date.
for getting archive end date
and enabling only_PTU_flag.
115.44 20-Apr-2009 sivanara 8402464 Added code computation of seniority
for test case ,when emp
terminated and ran PTU before the
year end process.
115.45 04-Jan-2011 sjawid 9820914 Handling Special chars in Employee Name and
Organization Name using pay_mx_rules.strip_spl_chars
115.46 01-Feb-2011 sjawid 11677462 Modified cursor load_gre to avoid invalid number
error by adding to_char to legal_employer_id check
to_char(p_le_id)
115.47 10-Feb-2011 sjawid 11715919 Added cursor c_get_max_arch_end_date to fix the
Issue with Multiple Terminations of the employee.
115.48 14-Feb-2011 sjawid 11716646 Added Legal Employer Check to cursor
c_chk_last_archiver.
115.49 14-Feb-2011 vvijayku 11738478 Added condition to prevent archiving of Seniority
twice by the Archiver.
115.50 15-Feb-2011 vvijayku 11772866 Moved the cursor c_get_max_arch_end_date call out
of the IF clause and also moved the assigning of the
variable lb_latest_assignment out of the IF clause.
115.51 19-Oct-2011 vvijayku 13111454 Corrected the load_ye_balance procedure such that the
g_ptu_balance_value_tab pl/sql table is getting
initialized with the correct Defined Balance Ids.
115.52 28-Oct-2011 vvijayku 13093752 Added the cursor to fetch the
A_AMENDS_PER_PDS_GRE_YTD balance value and also
added condition to archive the seniority value only
if the balance A_AMENDS_PER_PDS_GRE_YTD is greater
than 0.
115.53 09-Feb-2012 vvijayku 13688040 Modified the c_emp_details cursor in the
archive_code procedure for the performance improvement
of the Year End Archiver process.
115.54 03-Apr-2012 jeisaac 13776183 Moved populate_balances call inside get_emp_details cursor
loop in archive_code
115.55 29-Aug-2012 jeisaac 14339322 Modified c_get_eff_date cursor in archive_code procedure to
fetch the latest record for an assignment.
115.56 02-Nov-2012 jeisaac 14800027 Moved the populate_balances procedure call to the beginning
of loop in archive_code procedure
115.57 24-Jan-2013 jeisaac 16076577 Modified c_emp_details cursor in archive_code procedure to exclude
assignment actions which do not fall within the archiver period.
115.58 25-Jan-2013 jeisaac 16218468 ISR Tax to Charge value will be archived as 0 if value is negative
115.59 06-Feb-2013 jeisaac 16270938 ISR Tax to Charge will take the value of ISR Withheld when Annual
Tax adjustment process is not run for an employee.
*/
--
/******************************************************************************
** Global Variables
******************************************************************************/
gv_package VARCHAR2(100);
SELECT DISTINCT
fue_live.user_entity_name,
fue_live.creator_id,
0 tmp_bal_value
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb_attr,
pay_defined_balances pdb_call,
pay_balance_dimensions pbd,
ff_user_entities fue_live
WHERE pbad.attribute_name = 'Year End Balances'
AND pbad.legislation_code = 'MX'
AND pba.attribute_id = pbad.attribute_id
AND pdb_attr.defined_balance_id = pba.defined_balance_id
AND pdb_attr.balance_type_id = pdb_call.balance_type_id
AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
AND pbd.legislation_code = pbad.legislation_code
AND fue_live.creator_id = pdb_call.defined_balance_id
AND fue_live.creator_type = 'B'
ORDER BY fue_live.user_entity_name;
SELECT DISTINCT
fue_live.user_entity_name,
fue_live.creator_id,
0 tmp_bal_value
FROM pay_defined_balances pdb_call,
pay_balance_dimensions pbd,
pay_balance_types pbt,
ff_user_entities fue_live
WHERE pbt.balance_name IN ('ISR Withheld',
'Year End ISR Subject for Profit Sharing',
'Year End ISR Exempt for Profit Sharing')
AND pbt.balance_type_id = pdb_call.balance_type_id
AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
AND pbd.legislation_code = pbt.legislation_code
AND pbt.legislation_code = 'MX'
AND fue_live.creator_id = pdb_call.defined_balance_id
AND fue_live.creator_type = 'B'
ORDER BY fue_live.user_entity_name;
g_archive_item_details.DELETE;
g_ptu_bal_details.DELETE;
g_ptu_balance_value_tab.DELETE;
g_ye_balance_value_tab.DELETE;
SELECT gre_node.entity_id
FROM per_gen_hierarchy_nodes gre_node,
per_gen_hierarchy_nodes le_node,
per_gen_hierarchy_versions hier_ver,
fnd_lookup_values lv
WHERE gre_node.node_type = 'MX GRE'
AND le_node.node_type = 'MX LEGAL EMPLOYER'
AND le_node.entity_id = to_char(p_le_id) /* bug 11677462 */
AND le_node.business_group_id = p_business_group_id
AND gre_node.hierarchy_version_id = le_node.hierarchy_version_id
AND gre_node.business_group_id = le_node.business_group_id
AND le_node.hierarchy_node_id = gre_node.parent_hierarchy_node_id
AND gre_node.hierarchy_version_id = hier_ver.hierarchy_version_id
AND status = lv.lookup_code
AND lv.meaning = 'Active'
AND lv.LANGUAGE = 'US'
AND lv.lookup_type = 'PQH_GHR_HIER_VRSN_STATUS'
AND p_effective_date BETWEEN hier_ver.date_from
AND NVL(hier_ver.date_to, hr_general.end_of_time);
g_gre_tab.delete();
SELECT effective_date,
business_group_id,
pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
legislative_parameters) Legal_Employer_ID,
pay_mx_utility.get_parameter('TRANSFER_ASSIGNMENT_SET_ID',
legislative_parameters) Assignment_SET_ID
FROM pay_payroll_actions
WHERE payroll_action_id = cp_payroll_action_id;
SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = cp_archive_item_name
AND creator_type = 'X'
AND creator_id = 0
AND legislation_code = 'MX';
SELECT fai.value
INTO ln_prev_isr_whld_value
FROM ff_archive_items fai
WHERE fai.context1 = gn_prev_asg_act_id
AND fai.user_entity_id = ln_arch_user_entity_id;
select count(1)
into ln_arc_item
from ff_archive_items fai,
ff_archive_item_contexts faic
where fai.archive_item_id = faic.archive_item_id
and fai.user_entity_id = ln_arch_user_entity_id
and fai.context1 = p_archive_action_id
and fai.value = ln_value
and faic.context = p_tax_unit_id;
SELECT ffa.archive_item_id, ffa.value
FROM ff_user_entities ffu, ff_archive_items ffa
WHERE ffu.user_entity_name = cp_archive_item_name
AND ffu.creator_type = 'X'
AND ffu.creator_id = 0
AND ffu.legislation_code = 'MX'
AND ffu.user_entity_id = ffa.user_entity_id
AND ffa.context1 = p_archive_action_id;
ltab_entity_det.DELETE;
lt_ye_bal_result_tab.DELETE;
lt_ptu_bal_result_tab.DELETE;
Purpose : This returns the select statement that is
used to create the range rows for the Year End
Archiver.
Arguments :
Notes : Calls procedure - get_payroll_action_info
******************************************************************/
PROCEDURE range_code(
p_payroll_action_id IN NUMBER
,p_sqlstr OUT NOCOPY VARCHAR2)
IS
ld_end_date DATE;
'SELECT DISTINCT paf.person_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
WHERE ppa.business_group_id = ' || ln_business_group_id || '
AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_start_date) || ''')
AND fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_end_date) || ''')
AND ppa.action_type IN (''Q'',''R'',''B'',''V'',''I'')
AND paa.action_status = ''C''
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.source_action_id IS NULL
AND paf.assignment_id = paa.assignment_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND pay_mx_yrend_arch.gre_exists (paa.tax_unit_id) = 1
AND :payroll_action_id > 0
ORDER BY paf.person_id';
'SELECT DISTINCT paf.person_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
WHERE ppa.business_group_id = ' || ln_business_group_id || '
AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_start_date) || ''')
AND fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_end_date) || ''')
AND ppa.action_type IN (''Q'',''R'',''B'',''V'',''I'')
AND paa.action_status = ''C''
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.source_action_id IS NULL
AND paf.assignment_id = paa.assignment_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND pay_mx_yrend_arch.gre_exists (paa.tax_unit_id) = 1
AND EXISTS
(SELECT ''x''
FROM hr_assignment_sets has,
hr_assignment_set_amendments hasa,
per_assignments_f paf_all
WHERE has.assignment_set_id = ' || ln_asg_set_id || '
AND has.assignment_set_id = hasa.assignment_set_id
AND hasa.assignment_id = paf_all.assignment_id
AND paf_all.person_id = paf.person_id
AND hasa.include_or_exclude = ''I'')
AND :payroll_action_id > 0
ORDER BY paf.person_id';
SELECT 'X'
FROM hr_assignment_sets has,
hr_assignment_set_amendments hasa
WHERE has.assignment_set_id = cp_asg_set_id
AND has.assignment_set_id = hasa.assignment_set_id
AND hasa.assignment_id IN (SELECT DISTINCT
paf_all.assignment_id
FROM per_assignments_f paf,
per_assignments_f paf_all
WHERE paf.person_id = paf_all.person_id
AND paf.assignment_id = cp_asg_id)
AND hasa.include_or_exclude = 'E';
SELECT /*+ index(PPA PAY_PAYROLL_ACTIONS_PK) */ paf_pri.assignment_id,
paf_pri.person_id,
paf_pri.period_of_service_id
FROM per_assignments_f paf,
per_assignments_f paf_pri,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_population_ranges ppr
WHERE paf.assignment_id = paa.assignment_id
AND paa.tax_unit_id = cp_gre_id
AND ppr.payroll_action_id = p_payroll_action_id
AND ppr.chunk_number = p_chunk
AND ppr.person_id = paf.person_id
AND paf_pri.period_of_service_id = paf.period_of_service_id
AND paf_pri.primary_flag = 'Y'
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('Q','R','B','V','I')
AND paa.action_status = 'C'
AND ppa.effective_date BETWEEN cp_start_date
AND cp_end_date
AND paf_pri.effective_start_date <= cp_end_date
AND paf_pri.effective_end_date >= cp_start_date
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
ORDER BY paf_pri.person_id,
paf_pri.effective_end_date DESC;
SELECT /*+ USE_NL(pap paf) */
paf_pri.assignment_id,
paf_pri.person_id,
paf_pri.period_of_service_id
FROM per_assignments_f paf,
per_assignments_f paf_pri,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_all_payrolls_f pap
WHERE ppa.business_group_id + 0 = cp_bg_id
AND ppa.effective_date BETWEEN cp_start_date
AND cp_end_date
AND ppa.action_type IN ('Q','R','B','V','I')
AND paa.action_status = 'C'
AND pap.business_group_id = cp_bg_id
AND ppa.payroll_id = pap.payroll_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.source_action_id IS NULL
AND paf.assignment_id = paa.assignment_id
AND paf_pri.period_of_service_id = paf.period_of_service_id
AND paf_pri.primary_flag = 'Y'
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf_pri.effective_start_date <= cp_end_date
AND paf_pri.effective_end_date >= cp_start_date
AND paa.tax_unit_id = cp_gre_id
AND paf_pri.person_id = paf.person_id
AND paf.person_id BETWEEN p_start_person_id
AND p_end_person_id
ORDER BY paf_pri.person_id,
paf_pri.effective_end_date DESC;
SELECT ppa1.effective_date,
paa1.assignment_action_id
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1,
per_assignments_f paf1
WHERE ppa1.payroll_action_id = paa1.payroll_action_id
AND paa1.assignment_id = paf1.assignment_id
AND paf1.period_of_service_id = cp_period_of_service_id
AND ppa1.report_type = 'MX_YREND_ARCHIVE'
AND ppa1.report_qualifier = 'MX'
AND ppa1.report_category = 'ARCHIVE'
AND paf1.effective_start_date <= cp_end_date
AND paf1.effective_end_date >= cp_start_date
AND TO_CHAR(ppa1.effective_date, 'YYYY')
= TO_CHAR(cp_end_date, 'YYYY')
AND pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
ppa1.legislative_parameters) = cp_legal_employer_id
ORDER BY ppa1.effective_date DESC;
SELECT 'Y'
FROM pay_payroll_actions ppa2,
pay_assignment_actions paa2,
per_assignments_f paf2
WHERE ppa2.payroll_action_id = paa2.payroll_action_id
AND ppa2.action_type IN ('R', 'Q', 'B', 'V', 'I')
AND paa2.assignment_id = paf2.assignment_id
AND paf2.period_of_service_id = cp_period_of_service_id
AND ppa2.effective_date > cp_prev_arch_eff_date
AND ppa2.effective_date <= cp_end_date
AND paf2.effective_start_date <= cp_end_date
AND paf2.effective_end_date >= cp_start_date;
SELECT pay_assignment_actions_s.NEXTVAL
INTO ln_yrend_action_id
FROM dual;
UPDATE pay_assignment_actions
SET serial_number = ln_person_id
WHERE assignment_action_id = ln_yrend_action_id;
SELECT pay_assignment_actions_s.NEXTVAL
INTO ln_yrend_action_id
FROM dual;
UPDATE pay_assignment_actions
SET serial_number = ln_person_id
WHERE assignment_action_id = ln_yrend_action_id;
SELECT hoi.org_information1 "Name",
hoi.org_information2 "Employer RFC",
ppf.full_name "Legal Representative Name",
ppf.per_information2 "Legal Representative RFC",
ppf.national_identifier "Legal Representative CURP"
FROM hr_organization_information hoi,
per_people_f ppf
WHERE hoi.organization_id = cp_legal_er_id
AND hoi.org_information_context = 'MX_TAX_REGISTRATION'
AND hoi.org_information3 = ppf.person_id
AND cp_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT TO_CHAR(ld_end_date, 'YYYY')
INTO g_fiscal_year
FROM dual;
SELECT assignment_id,
serial_number,
tax_unit_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_archive_action_id;
select per_det.*, rownum row_count
from (SELECT DISTINCT
paf.business_group_id,
ppf.person_id,
paf.assignment_id,
paa.tax_unit_id,
paa.chunk_number,
ppf.last_name "Paternal Last Name",
ppf.per_information1 "Maternal Last Name",
ppf.first_name || ' ' || ppf.middle_names,
ppf.national_identifier "CURP",
ppf.per_information2 "RFC ID",
GREATEST(fnd_date.canonical_to_date(g_fiscal_year || '/01/01'),
DECODE(TO_CHAR(pps.date_start, 'YYYY'),
TO_CHAR(cp_effective_date, 'YYYY'),
pps.date_start,
fnd_date.canonical_to_date(g_fiscal_year ||
'/01/01'))
),
hr_mx_utility.get_seniority(paf.business_group_id,
paa.tax_unit_id,
paf.payroll_id,
ppf.person_id,
cp_effective_date),
NVL(paf_all.labour_union_member_flag, 'N'),
hoi.org_information7 "Economic Zone",
ROUND(0.5 + 0.005 * hr_mx_utility.get_tax_subsidy_percent(
ppf.business_group_id,
paa.tax_unit_id,
cp_effective_date), 4),
hl.region_1 "Jurisdiction"
FROM per_people_f ppf,
per_assignments_f paf,
per_assignments_f paf_all,
pay_assignment_actions paa,
per_periods_of_service pps,
hr_organization_units hou,
hr_organization_information hoi,
hr_locations_all hl,
pay_payroll_actions ppa
WHERE paa.assignment_action_id in
(select assignment_action_id
from pay_assignment_actions paa1
where paa1.assignment_id = paf.assignment_id
and paa1.assignment_id in
(select assignment_id
from pay_assignment_actions
where assignment_action_id = p_archive_action_id
and assignment_id = paf.assignment_id)
and payroll_action_id = ppa.payroll_action_id )
and not exists
( select 1 from pay_action_information
where action_context_id in
(select assignment_action_id
from pay_assignment_actions
where assignment_id in
(select assignment_id
from pay_assignment_actions
where assignment_action_id = p_archive_action_id)
and payroll_action_id = ppa.payroll_action_id) )
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id in (SELECT DISTINCT gre_node.entity_id
FROM per_gen_hierarchy_nodes gre_node,
per_gen_hierarchy_nodes le_node,
per_gen_hierarchy_versions hier_ver,
fnd_lookup_values flv
WHERE gre_node.node_type = 'MX GRE'
AND gre_node.business_group_id = paf.business_group_id
AND gre_exists (gre_node.entity_id) = 1
AND le_node.node_type = 'MX LEGAL EMPLOYER'
AND gre_node.hierarchy_version_id = le_node.hierarchy_version_id
AND le_node.hierarchy_node_id = gre_node.parent_hierarchy_node_id
AND gre_node.hierarchy_version_id = hier_ver.hierarchy_version_id
AND status = flv.lookup_code
AND flv.meaning = 'Active'
AND flv.LANGUAGE = 'US'
AND flv.lookup_type = 'PQH_GHR_HIER_VRSN_STATUS'
AND cp_effective_date BETWEEN hier_ver.date_from
AND NVL(hier_ver.date_to,
hr_general.end_of_time))
AND cp_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND cp_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND cp_effective_date BETWEEN paf_all.effective_start_date
AND paf_all.effective_end_date
AND paf.assignment_id = paa.assignment_id
and paf_all.assignment_id = paf.assignment_id
and paf.assignment_id = paf_all.assignment_id
AND ppf.person_id = paf.person_id
AND paf.person_id = paf_all.person_id
and pps.person_id = ppf.person_id
AND pps.period_of_service_id = paf.period_of_service_id
AND hou.organization_id = paa.tax_unit_id
AND hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
AND hl.location_id = paf.location_id
AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(g_fiscal_year || '/01/01')
AND p_effective_date
AND ppa.action_type in ('Q','R','B','V','I')) per_det;
SELECT pei_information1 RFC,
fnd_number.canonical_to_number(pei_information5) ISR_Withheld,
fnd_number.canonical_to_number(pei_information6) Cr_Subsidy,
fnd_number.canonical_to_number(pei_information7) Non_Cr_Subsidy,
fnd_number.canonical_to_number(pei_information8) Total_Earnings,
fnd_number.canonical_to_number(pei_information9) Exempt_Earnings
FROM per_people_extra_info
WHERE information_type = 'MX_PREV_EMPLOYMENT_INFO'
AND person_id = cp_person_id
AND TO_CHAR(fnd_date.canonical_to_date(pei_information4), 'YYYY') =
TO_CHAR(cp_effective_date, 'YYYY')
ORDER BY pei_information4 DESC;
SELECT DISTINCT
pps.actual_termination_date, /*Bug 8402505*/
NVL(pps.actual_termination_date,
nvl(paf.effective_end_date, p_effective_date)) effective_date,
NVL(pps.actual_termination_date,
fnd_date.canonical_to_date(g_fiscal_year || '/12/31')
)
FROM per_people_f ppf,
per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_periods_of_service pps
WHERE paa.assignment_action_id = p_archive_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paf.assignment_id = paa.assignment_id
AND ppf.person_id = paf.person_id
AND pps.period_of_service_id = paf.period_of_service_id
ORDER BY effective_date DESC;
SELECT count(*)
FROM pay_action_information
WHERE action_context_id = cp_payroll_action_id
AND action_context_type = 'PA';
SELECT DISTINCT le_node.entity_id,
gre_node.entity_id
FROM per_gen_hierarchy_nodes gre_node,
per_gen_hierarchy_nodes le_node,
per_gen_hierarchy_versions hier_ver,
fnd_lookup_values flv
WHERE gre_node.node_type = 'MX GRE'
AND gre_node.business_group_id = cp_business_group_id
AND gre_exists (gre_node.entity_id) = 1
AND le_node.node_type = 'MX LEGAL EMPLOYER'
AND gre_node.hierarchy_version_id = le_node.hierarchy_version_id
AND le_node.hierarchy_node_id = gre_node.parent_hierarchy_node_id
AND gre_node.hierarchy_version_id = hier_ver.hierarchy_version_id
AND status = flv.lookup_code
AND flv.meaning = 'Active'
AND flv.LANGUAGE = 'US'
AND flv.lookup_type = 'PQH_GHR_HIER_VRSN_STATUS'
AND cp_effective_date BETWEEN hier_ver.date_from
AND NVL(hier_ver.date_to,
hr_general.end_of_time);
select /*+ ordered index(PPA PAY_PAYROLL_ACTIONS_PK)*/ paa_all.assignment_action_id
from pay_assignment_actions paa_pri ,
per_assignments_f paf_pri ,
per_assignments_f paf_all ,
pay_assignment_actions paa_all,
pay_payroll_actions ppa ,
pay_action_classifications pac
WHERE paa_pri.assignment_action_id = p_archive_action_id
AND paf_pri.assignment_id = paa_pri.assignment_id
AND paf_all.period_of_service_id = paf_pri.period_of_service_id
AND paa_all.tax_unit_id = cp_tax_unit_id
AND paa_all.assignment_id = paf_all.assignment_id
AND paa_all.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type = pac.action_type
AND pac.classification_name = 'SEQUENCED'
AND paa_all.action_status = 'C'
AND ppa.effective_date BETWEEN cp_arch_period_start_date
AND cp_arch_period_end_date
ORDER BY paa_all.action_sequence DESC;
SELECT pay_balance_pkg.get_value(pdb_cr.defined_balance_id,
cp_ytd_action_id),
pay_balance_pkg.get_value(pdb_ncr.defined_balance_id,
cp_ytd_action_id)
FROM pay_defined_balances pdb_cr,
pay_defined_balances pdb_ncr,
pay_balance_types pbt_cr,
pay_balance_types pbt_ncr,
pay_balance_dimensions pbd
WHERE pdb_cr.balance_type_id = pbt_cr.balance_type_id
AND pdb_ncr.balance_type_id = pbt_ncr.balance_type_id
AND pdb_cr.balance_dimension_id = pbd.balance_dimension_id
AND pdb_ncr.balance_dimension_id = pbd.balance_dimension_id
AND pbt_cr.balance_name = 'ISR Creditable Subsidy'
AND pbt_ncr.balance_name = 'ISR Non Creditable Subsidy'
AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
AND pbt_cr.legislation_code = 'MX'
AND pbt_ncr.legislation_code = pbt_cr.legislation_code
AND pbd.legislation_code = pbt_ncr.legislation_code; */
SELECT pay_balance_pkg.get_value(pdb_cr.defined_balance_id,
cp_ytd_action_id)
FROM pay_defined_balances pdb_cr,
pay_balance_types pbt_cr,
pay_balance_dimensions pbd
WHERE pdb_cr.balance_type_id = pbt_cr.balance_type_id
AND pdb_cr.balance_dimension_id = pbd.balance_dimension_id
AND pbt_cr.balance_name = 'ISR Creditable Subsidy'
AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
AND pbt_cr.legislation_code = 'MX';
select pay_balance_pkg.get_value(pdb_ncr.defined_balance_id,
cp_ytd_action_id)
FROM pay_defined_balances pdb_ncr,
pay_balance_types pbt_ncr,
pay_balance_dimensions pbd
WHERE pdb_ncr.balance_type_id = pbt_ncr.balance_type_id
AND pdb_ncr.balance_dimension_id = pbd.balance_dimension_id
AND pbt_ncr.balance_name = 'ISR Non Creditable Subsidy'
AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
AND pbt_ncr.legislation_code = 'MX'
AND pbd.legislation_code = pbt_ncr.legislation_code;
select pay_balance_pkg.get_value(pdb_calc.defined_balance_id,
cp_ytd_action_id)
FROM pay_defined_balances pdb_calc,
pay_balance_types pbt_calc,
pay_balance_dimensions pbd
WHERE pdb_calc.balance_type_id = pbt_calc.balance_type_id
AND pdb_calc.balance_dimension_id = pbd.balance_dimension_id
AND pbt_calc.balance_name = 'ISR Calculated'
AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
AND pbt_calc.legislation_code = 'MX'
AND pbd.legislation_code = pbt_calc.legislation_code;
SELECT /*+ index(PPA PAY_PAYROLL_ACTIONS_PK)*/ DISTINCT ppa.effective_date
FROM pay_run_results prr,
pay_run_result_values prrv,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_input_values_f piv,
pay_balance_feeds_f pbf,
pay_balance_types pbt,
per_assignments_f paf
WHERE pbt.balance_name = 'Profit Sharing'
AND pbt.legislation_code = 'MX'
AND pbf.balance_type_id = pbt.balance_type_id
AND piv.input_value_id = pbf.input_value_id
AND prr.element_type_id = piv.element_type_id
AND prrv.run_result_id = prr.run_result_id
AND prr.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = paf.assignment_id
AND paf.person_id = cp_person_id
AND paa.tax_unit_id = cp_tax_unit_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R', 'Q', 'B', 'V', 'I')
AND ppa.effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND ppa.effective_date BETWEEN pbf.effective_start_date
AND pbf.effective_end_date
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.effective_date BETWEEN cp_start_date
AND cp_end_date
ORDER BY 1 DESC;
SELECT paa1.assignment_action_id, 'Y'
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1,
per_assignments_f paf1,
per_assignments_f paf2
WHERE ppa1.payroll_action_id = paa1.payroll_action_id
AND paa1.assignment_id = paf1.assignment_id
AND paf1.period_of_service_id = paf2.period_of_service_id
AND paf2.assignment_id = cp_assignment_id
AND ppa1.report_type = 'MX_YREND_ARCHIVE'
AND ppa1.report_qualifier = 'MX'
AND ppa1.report_category = 'ARCHIVE'
AND paa1.assignment_action_id <> p_archive_action_id
AND paf1.effective_start_date <= cp_end_date
AND paf1.effective_end_date >= cp_start_date
AND paf2.effective_start_date <= cp_end_date
AND paf2.effective_end_date >= cp_start_date
AND TO_CHAR(ppa1.effective_date, 'YYYY') = TO_CHAR(cp_end_date, 'YYYY')
AND paa1.tax_unit_id = cp_tax_unit_id
ORDER BY 1 desc;
SELECT distinct 'Y'
FROM per_all_assignments_f paf
WHERE paf.person_id = cp_person_id
AND EXISTS ( SELECT 1
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type = 'B'
AND ppa.effective_date BETWEEN trunc(cd_end_date,'Y')
and cd_end_date
AND ppa.business_group_id = ln_business_group_id
AND pay_mx_utility.get_legi_param_val('PROCESS',
legislative_parameters) = 'MX_ANN_ADJ'
AND paa.assignment_id = paf.assignment_id
);
SELECT pay_mx_utility.get_legi_param_val('CALC_MODE'
,legislative_parameters)
,paa.assignment_action_id
FROM per_all_assignments_f paf
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE person_id = cp_person_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type = 'B'
AND ppa.effective_date = cp_effective_date
AND ppa.business_group_id = cp_business_group_id
AND pay_mx_utility.get_legi_param_val('PROCESS'
,legislative_parameters) = 'MX_ANN_ADJ'
AND paa.assignment_id = paf.assignment_id
ORDER BY ppa.payroll_action_id desc;
select ppa.business_group_id
,ppa.effective_date
from pay_payroll_actions ppa
,pay_assignment_actions paa
where paa.assignment_action_id = cp_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
SELECT piv.input_value_id
FROM pay_element_types_f pet
,pay_input_values_f piv
WHERE pet.legislation_code = 'MX'
AND pet.element_name = 'Annual Tax Adjustment'
AND piv.element_type_id = pet.element_type_id
AND piv.name = 'Calculation Mode';
SELECT result_value
FROM pay_run_results prr
,pay_run_result_values prrv
WHERE prr.assignment_action_id = cp_assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = cp_input_value_id;
SELECT MAX (pps.date_start)
FROM per_periods_of_service pps
WHERE pps.person_id = cp_person_id
AND pps.date_start <= cp_effective_date;
SELECT NVL(max(actual_termination_date),
fnd_date.canonical_to_date(g_fiscal_year || '/12/31'))
FROM per_periods_of_service
WHERE person_id = cp_person_id
AND actual_termination_date
BETWEEN fnd_date.canonical_to_date(g_fiscal_year || '/01/01')
AND fnd_date.canonical_to_date(g_fiscal_year || '/12/31');
SELECT nvl(fnd_number.canonical_to_number(value),0)
FROM ff_archive_items
WHERE user_entity_id = (SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = 'A_AMENDS_PER_PDS_GRE_YTD')
AND context1 = p_archive_action_id;
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_archive_action_id
,p_action_context_type => 'AAP'
,p_assignment_id => ln_assignment_id
,p_tax_unit_id => ln_tax_unit_id
,p_curr_pymt_eff_date => p_effective_date
,p_tab_rec_data => pai_tab
);
pai_tab.delete;
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => g_payroll_action_id
,p_action_context_type => 'PA'
,p_assignment_id => NULL
,p_tax_unit_id => NULL
,p_curr_pymt_eff_date => p_effective_date
,p_tab_rec_data => pai_tab);
pai_tab.delete;