[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
25-Oct-2007 vmehta 115.27 6519803 Delete table for balances in
GET_PARTIAL_SUBJ_EARNINGS before
calculating taxes other than ISR
21-Nov-2007 prechand 115.28 6606767 Changed the function to_number
in the function CALCULATE_ISR_TAX to
Fnd_Number.Canonical_to_number
12-Dec-2007 nragavar 115.29 6487007 ISR 2008 changes
29-Jan-2008 nragavar 115.33 6779706 Subsidy for Empl paid was getting added
where, not required.
30-Jan-2008 nragavar 115.34 6782264 changes to get_table_value
21-Feb-2008 sivanara 115.35 6821377 Changes made to calculte_ISR_TAX to
considered if emp is hired in mid of
pay period. Also changes to function
CONVERT_INTO_MONTHLY_AVG_SAL
24-Mar-2008 sivanara 115.36 6852627 Included ISR proration logic
03-Apr-2008 sivanara 115.38 6926777 Included error message
PAY_MX_INVALID_ISR_NON_WRK_DAY for
ISR proration.
05-May-2008 sivanara 115.39 7027010 Incldued logic for ISR Subject proration
06-May-2008 sivanara 115.40 7116850 Revert the proration logic as we get
the prorated subject amount for p_subject_amount.
6933775 Included logic for projection of prorated
ISR subject amount
Added code in procedure CALCULATE_ISR_TAX
to consider the first paid period to the
employee
13-Jun-2008 nragavar 115.42 7047220 7047357- leapfroged from 115.40 to 115.42.
this includes changes in 115.41. changes
to cursor csr_get_min_wage.
03-Jul-2008 sivanara 115.43 7208623 leapfroged again 115.36 to115.43.
For this version the package header
version is pymxtxfn.pkh 115.19.
This version does not include any
part of isr proration fixes.
Version 115.42 to 115.44(whih has
ISR proration fix) arcsed
on top of this will be done.
03-Jul-2008 sivanara 115.44 leapfroged from Version 115.42 to 115.44
which has the ISR proration fix that was
included in version 115.43
15-jul-2008 sivanara 115.45 7260970 For ISR Proration added logic to consider the
day factor for calculating the total subject
amount from the given prorated amount.
115.46 7242481 ISR proration should be considered only for
ARTICLE 113 calculation method.
04-Aug-2008 nragavar 115.47 7042174 Done changes as part of 10 day payroll frequency.
04-Aug-2008 sjawid 115.50 7445486 No need to calculate 'credit to salary
for ISR Tax calculation as per Article142.
02-Dec-2008 sivanara 115.51 7602236 Added logic to CHECK_EE_EMPLOYMENT_CRITERIA
7604298 to consider test case for RE-HIREed employee
in the next day immediately after termination.
02-Dec-2008 sjawid 115.53 7677805 Fixed the issue of incorrect isr tax on weekly
payroll. Modified Subsidy calculation logic.
27-Jan-2009 vvijayku 115.54 6785206 Periodic tax adjustment changes
added new overloaded funtion isr_tax_calculation
20-Feb-2009 vvijayku 115.55 8271515 Changed the code so that the Non Working days is
considered correctly while doing Tax Adjustment.
25-Feb-2009 vvijayku 115.56 8286044 Changed the balance dimensions for the Balances
called for Tax Adjustment from ASG_GRE_YTD/MTD to
PER_YTD/MTD.
8283620 Modified the ISR Subject balance call for Tax
Adjustment
26-Feb-2009 vvijayku 115.57 8286044 Added a new cursor to take the first hire date of
the employee so that the number of worked days is
calculated correctly.
30-Apr-2009 vvijayku 15.58 8291738 Changed the balance calls from PER_YTD AND PER_MTD
to ASG_MTD and ASG_YTD respectively for the Tax
Adjustment process and ASG_GRE_MTD to ASG_MTD for
subsidy calculation.
27-Oct-2009 sjawid 15.59 8438155 Added new cursor csr_get_compute_subsidy_flag in
function CALCULATE_ISR_TAX and added logic to
skip the subsidy for employment calculation when
user has a value 'No' in 'Compute Subsidy for Employment'
at 'MX Statutory Info' in assignment screen.
27-Oct-2009 sjawid 15.60 8438155 Added nvl function to the cursor
csr_get_compute_subsidy_flag in function
CALCULATE_ISR_TAX.
29-Oct-2009 sjawid 15.61 8438155 Multiple style comments found on one line
are removed to remove check_patch errors.
01-Nov-2009 sjawid 15.62 8932102 The cursor csr_get_payroll_id in Calculate_isr_tax function
has been modified to fetch only payroll_id and
removed hire_date reference and a new cursor
csr_get_hire_date has been added to fetch hire date.
21-Jul-2010 vvijayku 115.63 9753792 Added new code in the function GET_RANGE_BASIS_VALUE and
GET_PARTIAL_SUBJ_EARNINGS for handling the PTU calculation.
06-Jun-2011 vvijayku 115.64 12553943 Added fnd_number.canonical_to_number in the cursor
csr_get_table_value.
23-Apr-2012 sjawid 115.65 13905014 Modified ISR tax and Subsidy for employement calculation
for Monthly and Periodic Tax adjustments.
11-May-2012 jeisaac 115.67 13998818 Modified ISR Calculated for Monthly and Periodic Tax Adj.
28-May-2012 vvijayku 115.68 14094610 Added a new function get_prorated_rates to correctly fetch
and use the prorated table rates for the user table
ISR Subsidy for Empl_Month.
27-Dec-2012 rmugloo 115.69 16032260 Modified ISR calculated for Monthly and Periodic Tax
adjustments to take care of non periodic runs.
01-Feb-2012 jeisaac 115.70 15976084 Min Wage amount while calculating ISR Exempt for sub classification
'Overtime and Holidays Worked' should be taken as weekly limit.
*****************************************************************************/
TYPE g_leg_record IS RECORD (
effective_start_date pay_mx_legislation_info_f.effective_start_date%TYPE,
effective_end_date pay_mx_legislation_info_f.effective_end_date%TYPE,
jurisdiction_code pay_mx_legislation_info_f.jurisdiction_code%TYPE,
legislation_info_type pay_mx_legislation_info_f.legislation_info_type%TYPE,
legislation_info1 pay_mx_legislation_info_f.legislation_info1%TYPE,
legislation_info2 pay_mx_legislation_info_f.legislation_info2%TYPE,
legislation_info3 pay_mx_legislation_info_f.legislation_info3%TYPE,
legislation_info4 pay_mx_legislation_info_f.legislation_info4%TYPE,
legislation_info5 pay_mx_legislation_info_f.legislation_info5%TYPE,
legislation_info6 pay_mx_legislation_info_f.legislation_info6%TYPE);
SELECT creator_id
INTO l_defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = p_entity_name
AND legislation_code = 'MX'
AND creator_type = 'B';
SELECT paf.soft_coding_keyflex_id,
paf.location_id,
ppa.effective_date,
paf.business_group_id
FROM per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paf.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = p_asact_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT paf.soft_coding_keyflex_id,
paf.location_id,
ppa.effective_date,
paf.business_group_id
FROM per_assignments_f paf,
pay_temp_object_actions ptoa,
pay_payroll_actions ppa
WHERE paf.assignment_id = ptoa.object_id
AND ptoa.payroll_action_id = ppa.payroll_action_id
AND ptoa.object_action_id = p_asact_id
AND ptoa.object_type = 'ASG'
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT paf.soft_coding_keyflex_id,
paf.location_id,
ppa.effective_date,
paf.business_group_id
FROM per_assignments_f paf,
pay_temp_object_actions ptoa,
pay_payroll_actions ppa
WHERE paf.assignment_id = ptoa.object_id
AND ptoa.payroll_action_id = ppa.payroll_action_id
AND ptoa.object_action_id = p_asact_id
AND ptoa.object_type = 'ASG'
AND paf.effective_start_date <= g_ptu_end_date
AND paf.effective_end_date >= g_ptu_start_date;
SELECT fnd_number.canonical_to_number(plif.legislation_info2)
FROM pay_mx_legislation_info_f plif,
hr_organization_units hou,
hr_organization_information hoi
WHERE hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
AND (DECODE (UPPER(p_range_basis),
'MW', 'MW'||hoi.org_information7) = plif.legislation_info1 OR
p_range_basis = plif.legislation_info1)
AND hou.organization_id = p_gre_id
AND plif.legislation_info_type = 'MX Minimum Wage Information'
AND p_effective_date BETWEEN hou.date_from
AND NVL(hou.date_to, hr_general.end_of_time)
AND p_effective_date BETWEEN plif.effective_start_date
AND plif.effective_end_date;
SELECT pec1.classification_id,
pec1.classification_name
FROM pay_element_classifications pec,
pay_element_classifications pec1, -- Secondary classification
pay_element_types_f pet,
pay_sub_classification_rules_f psr
WHERE pet.classification_id = pec.classification_id
AND pec.classification_id = pec1.parent_classification_id
AND pet.element_type_id = psr.element_type_id
AND psr.classification_id = pec1.classification_id
AND p_ctx_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_ctx_effective_date BETWEEN psr.effective_start_date
AND psr.effective_end_date
AND pet.element_type_id = p_ctx_element_type_id
AND pec.legislation_code = 'MX'
AND pec.classification_name <> 'Employer Liabilities'
AND pec.business_group_id IS NULL
AND pec1.legislation_code = 'MX'
AND pec1.business_group_id IS NULL
AND p_tax_type = 'ISR'
UNION
SELECT pec.classification_id,
pec.classification_name
FROM pay_element_classifications pec
WHERE UPPER(pec.classification_name) = UPPER(p_classification_name)
AND pec.legislation_code = 'MX'
AND pec.business_group_id IS NULL
AND pec.parent_classification_id IS NOT NULL
AND p_tax_type = 'STATE';
g_isr_balances.delete();
SELECT ''
INTO l_dummy
FROM pay_mx_earn_exemption_rules_f pmex
WHERE pmex.tax_type = p_tax_type
AND DECODE(p_tax_type, 'ISR', p_ctx_jurisdiction_code,
pmex.state_code) = p_ctx_jurisdiction_code
AND pmex.element_classification_id = l_classification_id
AND p_ctx_effective_date BETWEEN pmex.effective_start_date
AND pmex.effective_end_date;
SELECT pec1.classification_id,
pec1.classification_name
FROM pay_element_classifications pec,
pay_element_classifications pec1, -- Secondary classification
pay_element_types_f pet,
pay_sub_classification_rules_f psr
WHERE pet.classification_id = pec.classification_id
AND pec.classification_id = pec1.parent_classification_id
AND pet.element_type_id = psr.element_type_id
AND psr.classification_id = pec1.classification_id
AND p_ctx_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_ctx_effective_date BETWEEN psr.effective_start_date
AND psr.effective_end_date
AND pet.element_type_id = p_ctx_element_type_id
AND pec.legislation_code = 'MX'
AND pec.classification_name <> 'Employer Liabilities'
AND pec.business_group_id IS NULL
AND pec1.legislation_code = 'MX'
AND pec1.business_group_id IS NULL
AND p_tax_type = 'ISR'
UNION
SELECT pec.classification_id,
pec.classification_name
FROM pay_element_classifications pec
WHERE UPPER(pec.classification_name) = UPPER(p_classification_name)
AND pec.legislation_code = 'MX'
AND pec.business_group_id IS NULL
AND pec.parent_classification_id IS NOT NULL
AND p_tax_type = 'STATE';
g_isr_balances.delete();
SELECT ''
INTO l_dummy
FROM pay_mx_earn_exemption_rules_f pmex
WHERE pmex.tax_type = p_tax_type
AND DECODE(p_tax_type, 'ISR', p_ctx_jurisdiction_code,
pmex.state_code) = p_ctx_jurisdiction_code
AND pmex.element_classification_id = l_classification_id
AND p_ctx_effective_date BETWEEN pmex.effective_start_date
AND pmex.effective_end_date;
SELECT pec1.classification_id,
pec1.classification_name
FROM pay_element_classifications pec,
pay_element_classifications pec1, -- Secondary classification
pay_element_types_f pet,
pay_sub_classification_rules_f psr
WHERE pet.classification_id = pec.classification_id
AND pec.classification_id = pec1.parent_classification_id
AND pet.element_type_id = psr.element_type_id
AND psr.classification_id = pec1.classification_id
AND p_ctx_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_ctx_effective_date BETWEEN psr.effective_start_date
AND psr.effective_end_date
AND pet.element_type_id = p_ctx_element_type_id
AND pec.legislation_code = 'MX'
AND pec.classification_name <> 'Employer Liabilities'
AND pec.business_group_id IS NULL
AND pec1.legislation_code = 'MX'
AND pec1.business_group_id IS NULL
AND p_tax_type = 'ISR'
UNION
SELECT pec.classification_id,
pec.classification_name
FROM pay_element_classifications pec
WHERE UPPER(pec.classification_name) = UPPER(p_classification_name)
AND pec.legislation_code = 'MX'
AND pec.business_group_id IS NULL
AND pec.parent_classification_id IS NOT NULL
AND p_tax_type = 'STATE';
g_isr_balances.delete();
SELECT ''
INTO l_dummy
FROM pay_mx_earn_exemption_rules_f pmex
WHERE pmex.tax_type = p_tax_type
AND DECODE(p_tax_type, 'ISR', p_ctx_jurisdiction_code,
pmex.state_code) = p_ctx_jurisdiction_code
AND pmex.element_classification_id = l_classification_id
AND p_ctx_effective_date BETWEEN pmex.effective_start_date
AND pmex.effective_end_date;
SELECT pmex.calc_rule,
pmex.low_exempt_factor,
pmex.low_range_factor,
pmex.low_range_basis,
pmex.high_exempt_factor,
pmex.high_range_factor,
pmex.high_range_basis
FROM pay_mx_earn_exemption_rules_f pmex
WHERE pmex.tax_type = p_tax_type
AND DECODE(p_tax_type, 'ISR',
p_ctx_jurisdiction_code,
pmex.state_code) = p_ctx_jurisdiction_code
AND pmex.element_classification_id = p_classification_id
AND p_ctx_effective_date BETWEEN pmex.effective_start_date
AND pmex.effective_end_date;
SELECT TRUNC(ptp.end_date - ptp.start_date) + 1 days,
ppf.payroll_id,
paa.tax_unit_id,
paa.assignment_id
FROM pay_payrolls_f ppf,
per_time_periods ptp,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ptp.payroll_id = ppf.payroll_id
AND ppf.payroll_id = ppa.payroll_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = p_ctx_assignment_action_id
AND ppa.effective_date BETWEEN ptp.start_date
AND ptp.end_date
AND ppa.effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT TRUNC(ptp.end_date - ptp.start_date) + 1 days,
paf.payroll_id,
-- paa.tax_unit_id,
paf.assignment_id
FROM per_assignments_f paf,
per_time_periods ptp,
pay_temp_object_actions ptoa,
pay_payroll_actions ppa
WHERE ptp.payroll_id = paf.payroll_id
AND ppa.payroll_action_id = ptoa.payroll_action_id
AND ptoa.object_id = paf.assignment_id
AND ptoa.object_type = 'ASG'
AND ptoa.object_action_id = p_ctx_assignment_action_id
AND ppa.effective_date BETWEEN ptp.start_date
AND ptp.end_date
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT TRUNC(ptp.end_date - ptp.start_date) + 1 days,
paf.payroll_id,
-- paa.tax_unit_id,
paf.assignment_id
FROM per_assignments_f paf,
per_time_periods ptp,
pay_temp_object_actions ptoa,
pay_payroll_actions ppa
WHERE ptp.payroll_id = paf.payroll_id
AND ppa.payroll_action_id = ptoa.payroll_action_id
AND ptoa.object_id = paf.assignment_id
AND ptoa.object_type = 'ASG'
AND ptoa.object_action_id = p_ctx_assignment_action_id
AND ppa.effective_date BETWEEN ptp.start_date
AND ptp.end_date
AND paf.effective_start_date <= cp_end_date
AND paf.effective_end_date >= cp_start_date;
SELECT person_id
FROM per_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 paa.assignment_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = p_ctx_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_sequence =
(SELECT max(paa_prev.action_sequence)
FROM per_time_periods ptp
, pay_payroll_actions ppa1
, pay_assignment_actions paa1
, per_time_periods ptp_prev
, pay_payroll_actions ppa_prev
, pay_assignment_actions paa_prev
WHERE paa1.assignment_action_id = p_ctx_assignment_action_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.effective_date BETWEEN ptp.start_date
AND ptp.end_date
AND ptp.payroll_id = ppa1.payroll_id
AND ptp_prev.payroll_id = ppa1.payroll_id
AND (ptp.start_date - 1) BETWEEN ptp_prev.start_date
AND ptp_prev.end_date
AND paa_prev.assignment_id = paa1.assignment_id
AND paa_prev.payroll_action_id = ppa_prev.payroll_action_id
AND ppa_prev.action_type IN ('R', 'Q', 'B')
AND ppa_prev.effective_date BETWEEN ptp_prev.start_date
AND ptp_prev.end_date);
SELECT effective_start_date,
effective_end_date,
jurisdiction_code,
legislation_info_type,
legislation_info1,
legislation_info2,
legislation_info3,
legislation_info4,
legislation_info5,
legislation_info6
FROM pay_mx_legislation_info_f
WHERE legislation_info_type = p_legislation_info_type
AND DECODE(p_legislation_info1,
'$Sys_Def$', legislation_info1,
p_legislation_info1) = legislation_info1
AND NVL(jurisdiction_code,
p_ctx_jurisdiction_code) = p_ctx_jurisdiction_code
AND p_ctx_effective_date BETWEEN effective_start_date
AND effective_end_date;
g_pay_mx_legislation_info_f.DELETE();
SELECT DECODE(pml.legislation_info1,
'RANGE', org_information5,
'FLAT_RATE', org_information3)
FROM hr_organization_information hoi,
pay_mx_legislation_info_f pml
WHERE hoi.organization_id = cp_legal_er_id
AND hoi.org_information_context = 'MX_STATE_TAX_RULES'
AND hoi.org_information1 = p_ctx_jurisdiction_code
AND pml.jurisdiction_code = hoi.org_information1
AND pml.legislation_info_type = 'MX State Tax Rate'
AND p_ctx_effective_date BETWEEN pml.effective_start_date
AND pml.effective_end_date
AND DECODE(pml.legislation_info1,
'RANGE', org_information5,
'FLAT_RATE', org_information3) IS NOT NULL;
SELECT action_type,
pay_mx_utility.get_legi_param_val('CALC_MODE',
legislative_parameters),
pay_mx_utility.get_legi_param_val('PROCESS',
legislative_parameters)
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;*/
SELECT payroll_id
FROM per_assignments
WHERE assignment_id = P_ASSIGNMENT_ID;
SELECT min(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = P_ASSIGNMENT_ID
AND assignment_type='E';
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
WHERE pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.database_item_suffix = p_db_item_suffix -- '_ASG_GRE_MTD'
AND pbt.balance_name = p_balance_name; --'ISR Subsidy for Employment'
SELECT ppa.effective_date l_date_paid,
ptp.end_date l_date_earned
FROM per_time_periods ptp,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id
AND ppa.time_period_id = ptp.time_period_id;
SELECT MIN(ptp.start_date)
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_id = p_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ptp.time_period_id = ppa.time_period_id
AND ppa.action_type IN ('R', 'Q', 'B')
AND (to_char(l_date_paid,'yyyymm') = to_char(ptp.end_date,'yyyymm')
OR
l_date_paid BETWEEN ptp.start_date AND ptp.end_date
);
SELECT ROWNUM mult_num
,end_date period_end_mtd
,start_date period_start_mtd
,period_type
FROM PER_TIME_PERIODS ptp
WHERE payroll_id = ln_payroll_id
AND (TO_CHAR(l_date_paid,'yyyymm') = TO_CHAR(end_date,'yyyymm')
OR
l_date_paid BETWEEN start_date AND end_date
)
AND ld_hire_date <= end_date
AND start_date >= NVL(ld_first_pay_date,start_date)
ORDER BY end_date;
SELECT end_date - start_date +1 period_days
FROM PER_TIME_PERIODS ptp
WHERE payroll_id = p_payroll_id
AND TO_CHAR(l_date_earned,'yyyymmdd') = TO_CHAR(end_date,'yyyymmdd');
SELECT nvl(hsck.SEGMENT11,'Y')
FROM per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
hr_soft_coding_keyflex hsck
WHERE paf.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id
AND hsck.soft_coding_keyflex_id= paf.soft_coding_keyflex_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
select min(period_type)
into lv_period_type
from pay_payrolls_f
where payroll_id = ln_payroll_id;
select least(count(*),1)
into ln_pre_date_paid
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
where ppa.payroll_action_id = paa.payroll_action_id
and ptp.time_period_id = ppa.time_period_id
and paa.assignment_id = p_assignment_id
and to_char(ptp.end_date,'yyyymm') = to_char(l_date_paid,'yyyymm')
and to_char(ppa.effective_date,'yyyymm') < to_char(l_date_paid,'yyyymm')
AND ld_hire_date <= ptp.end_date;
select count(*) max_row
into ln_max_row
from PER_TIME_PERIODS ptp1
where payroll_id = ln_payroll_id
and to_char(l_date_paid,'yyyymm') = to_char(end_date,'yyyymm');
SELECT action_type,
pay_mx_utility.get_legi_param_val('CALC_MODE',
legislative_parameters),
pay_mx_utility.get_legi_param_val('PROCESS',
legislative_parameters)
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;*/
SELECT payroll_id
FROM per_assignments
WHERE assignment_id = P_ASSIGNMENT_ID;
SELECT min(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = P_ASSIGNMENT_ID
AND assignment_type='E';
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
WHERE pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.database_item_suffix = p_db_item_suffix -- '_ASG_GRE_MTD'
AND pbt.balance_name = p_balance_name; --'ISR Subsidy for Employment'
SELECT ppa.effective_date l_date_paid,
ptp.end_date l_date_earned
FROM per_time_periods ptp,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id
AND ppa.time_period_id = ptp.time_period_id;
SELECT MIN(ptp.start_date)
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_id = p_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ptp.time_period_id = ppa.time_period_id
AND ppa.action_type IN ('R', 'Q', 'B')
AND (to_char(l_date_paid,'yyyymm') = to_char(ptp.end_date,'yyyymm')
OR
l_date_paid BETWEEN ptp.start_date AND ptp.end_date
);
SELECT ROWNUM mult_num
,end_date period_end_mtd
,start_date period_start_mtd
,period_type
FROM PER_TIME_PERIODS ptp
WHERE payroll_id = ln_payroll_id
AND (TO_CHAR(l_date_paid,'yyyymm') = TO_CHAR(end_date,'yyyymm')
OR
l_date_paid BETWEEN start_date AND end_date
)
AND ld_hire_date <= end_date
AND start_date >= NVL(ld_first_pay_date,start_date)
ORDER BY end_date;
SELECT end_date - start_date +1 period_days
FROM PER_TIME_PERIODS ptp
WHERE payroll_id = p_payroll_id
AND TO_CHAR(l_date_earned,'yyyymmdd') = TO_CHAR(end_date,'yyyymmdd');
SELECT ptp1.end_date-TRUNC(to_date(ptp.start_date),'YEAR') + 1 period_days , TRUNC(to_date(ptp.start_date),'YEAR'), ptp1.end_date
FROM PER_TIME_PERIODS ptp, PER_TIME_PERIODS ptp1
WHERE ptp.period_num = '1'
AND l_date_earned BETWEEN ptp1.start_date AND ptp1.end_date
AND ptp.payroll_id = p_payroll_id
AND ptp1.payroll_id = p_payroll_id
AND (ptp1.end_date-TRUNC(to_date(ptp.start_date),'YEAR') + 1) BETWEEN '1' AND '379';
SELECT user_table_id
FROM pay_user_tables
WHERE upper(user_table_name) = upper(p_table_name)
AND nvl (business_group_id,
p_business_group_id) = p_business_group_id
AND nvl(legislation_code, 'MX') = 'MX';
SELECT fnd_number.canonical_to_number(CINST.value)* fnd_number.canonical_to_number(p_proration_fac)
FROM pay_user_tables TAB
, pay_user_columns C
, pay_user_rows_f R
, pay_user_column_instances_f CINST
WHERE TAB.user_table_id = p_table_id
AND C.user_table_id = TAB.user_table_id
AND nvl (C.business_group_id,
p_business_group_id) = p_business_group_id
AND nvl (C.legislation_code,
'MX') = 'MX'
AND upper (C.user_column_name) = upper (p_col_name)
AND CINST.user_column_id = C.user_column_id
AND R.user_table_id = TAB.user_table_id
AND p_date_earned BETWEEN R.effective_start_date
AND R.effective_end_date
AND nvl (R.business_group_id,
p_business_group_id) = p_business_group_id
AND nvl (R.legislation_code,
'MX') = 'MX'
AND fnd_number.canonical_to_number (p_row_value)
BETWEEN fnd_number.canonical_to_number (R.row_low_range_or_name)* fnd_number.canonical_to_number(p_proration_fac)
AND fnd_number.canonical_to_number (R.row_high_range)* fnd_number.canonical_to_number(p_proration_fac)
AND TAB.user_key_units = 'N'
AND CINST.user_row_id = R.user_row_id
AND p_date_earned BETWEEN CINST.effective_start_date
AND CINST.effective_end_date
AND nvl (CINST.business_group_id,
p_business_group_id) = p_business_group_id
AND nvl (CINST.legislation_code,
'MX') = 'MX';
SELECT end_date-TRUNC(start_date, 'MONTH')+1, end_date, TRUNC(start_date, 'MONTH')
FROM per_time_periods
WHERE payroll_id = p_payroll_id
AND l_date_paid BETWEEN start_date AND end_date;
SELECT MIN(pps.date_start)
FROM per_periods_of_service pps , per_assignments_f paf
WHERE paf.assignment_id = P_ASSIGNMENT_ID
AND pps.person_id = paf.person_id;
SELECT nvl(hsck.SEGMENT11,'Y')
FROM per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
hr_soft_coding_keyflex hsck
WHERE paf.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id
AND hsck.soft_coding_keyflex_id= paf.soft_coding_keyflex_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
select min(period_type)
into lv_period_type
from pay_payrolls_f
where payroll_id = ln_payroll_id;
select least(count(*),1)
into ln_pre_date_paid
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
where ppa.payroll_action_id = paa.payroll_action_id
and ptp.time_period_id = ppa.time_period_id
and paa.assignment_id = p_assignment_id
and to_char(ptp.end_date,'yyyymm') = to_char(l_date_paid,'yyyymm')
and to_char(ppa.effective_date,'yyyymm') < to_char(l_date_paid,'yyyymm')
AND ld_hire_date <= ptp.end_date;
select count(*) max_row
into ln_max_row
from PER_TIME_PERIODS ptp1
where payroll_id = ln_payroll_id
and to_char(l_date_paid,'yyyymm') = to_char(end_date,'yyyymm');
select end_date - start_date +1 period_days
from PER_TIME_PERIODS ptp
where payroll_id = p_payroll_id
and to_char(p_date_earned,'yyyymmdd') = to_char(end_date,'yyyymmdd');
SELECT period_type
INTO lv_period_type
FROM pay_all_payrolls_f ppf,
fnd_sessions fs
WHERE payroll_id = p_payroll_id
AND fs.effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND fs.session_id = USERENV('sessionid');
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name=p_balance_name
AND pbd.database_item_suffix =p_dimension
AND pbt.legislation_code = 'MX'
AND pbd.legislation_code = 'MX'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id;
SELECT MAX(paa.assignment_action_id)
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id =P_CTX_ASSIGNMENT_ID
AND paa.payroll_action_id=ppa.payroll_action_id
AND ppa.action_type in ('R','Q','I')
AND ppa.date_earned <=P_CTX_DATE_EARNED;
SELECT fnd_date.canonical_to_date(TO_CHAR(P_CTX_DATE_EARNED,'YYYY')
||'/'||hoi.org_information7),
fnd_date.canonical_to_date(TO_CHAR(P_CTX_DATE_EARNED,'YYYY')
||'/'||hoi.org_information8),
paf.person_id
FROM hr_organization_information hoi
,per_assignments_f paf
WHERE hoi.organization_id =
hr_mx_utility.get_legal_employer(paf.business_group_id,
per_mx_ssaffl_archive.derive_gre_from_loc_scl(
paf.location_id
,paf.business_group_id
,paf.soft_coding_keyflex_id
,p_ctx_date_earned),
p_ctx_date_earned)
AND hoi.org_information_context = 'MX_TAX_REGISTRATION'
AND paf.assignment_id = P_CTX_ASSIGNMENT_ID
AND P_CTX_DATE_EARNED BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT 'N'
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.payroll_action_id=ppa.payroll_action_id
AND paa.assignment_id =P_CTX_ASSIGNMENT_ID
AND ppa.report_type='ISR_TAX_FORMAT37'
AND ppa.date_earned BETWEEN p_start_date AND p_end_date;
SELECT MAX (pps.date_start), MAX(nvl(actual_termination_date,
fnd_date.canonical_to_date('4712/12/31')))
FROM per_periods_of_service pps
WHERE pps.person_id = cp_person_id
AND pps.date_start <= cp_effective_date;
SELECT MAX(actual_termination_date)
FROM per_periods_of_service pps
WHERE pps.person_id = cp_person_id
AND pps.date_start <= cp_effective_date
AND pps.actual_termination_date IS NOT NULL;
SELECT 'Y'
FROM pay_element_entries_f pee
,pay_element_types_f pet
,pay_element_entry_values_f pev
,pay_input_values_f piv
WHERE assignment_id=P_CTX_ASSIGNMENT_ID
AND pee.element_type_id=pet.element_type_id
AND pev.element_entry_id=pee.element_entry_id
AND piv.input_value_id = pev.input_value_id
AND pet.element_name = 'Mexico Tax'
AND piv.name ='Exempt ISR Tax'
AND pev.screen_entry_value='Y'
AND P_CTX_DATE_EARNED BETWEEN pee.effective_start_date AND pee.effective_end_date
AND P_CTX_DATE_EARNED BETWEEN pev.effective_start_date AND pev.effective_end_date;
SELECT person_id
FROM per_all_assignments_f
WHERE assignment_id=P_CTX_ASSIGNMENT_ID
AND P_CTX_DATE_EARNED BETWEEN effective_start_date AND effective_end_date;
SELECT pei_information1
FROM per_people_extra_info
WHERE person_id = p_person_id
AND information_type='MX_EMP_TAX_SIGNUP'
AND P_CTX_DATE_EARNED BETWEEN fnd_date.canonical_to_date(pei_information2)
AND fnd_date.canonical_to_date(pei_information3);
SELECT hoi.org_information7
FROM hr_organization_units hou,
hr_organization_information hoi
WHERE hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
AND hou.organization_id = P_CTX_TAX_UNIT_ID
AND P_CTX_DATE_EARNED BETWEEN hou.date_from
AND NVL(hou.date_to, hr_general.end_of_time);
SELECT legislation_info2 FROM PAY_MX_LEGISLATION_INFO_F WHERE
legislation_info1=
DECODE(P_ECON_ZONE,'NONE','GMW','MW'||P_ECON_ZONE) AND
legislation_info_type = 'MX Minimum Wage Information'
AND P_CTX_DATE_EARNED BETWEEN effective_start_date AND effective_end_date;
select rownum mult_num,
to_char(end_date,'yyyymmdd') period_end_mtd,
to_char(start_date,'yyyymmdd') period_start_mtd,
time_period_id
from PER_TIME_PERIODS ptp
where payroll_id = p_payroll_id
and (to_char(p_date_earned,'yyyymm') = to_char(end_date,'yyyymm')
or
p_date_earned between start_date and end_date
)
AND p_hire_date <= end_date
AND start_date >= NVL(p_first_pay_date,start_date)
order by end_date;
The prorated lower range values in the pl/sql table is updated as
prorated high range values of the immediately previous range + 0.01.
This is done to ensure there are no gaps between the prorated high range
values and the succeeding prorated low range values. Once the table rates
are corrected then table rate pertaining to the range in which p_row_value
is identified and returned back.*/
function get_prorated_rates (p_bus_grp_id in number,
p_table_id in number,
p_column_name in varchar2,
p_eff_date in date,
p_row_value in number,
p_period_days in number,
p_table_name in varchar2)
return varchar2 is
CURSOR csr_get_pro_table_val IS
select /*+ INDEX(C PAY_USER_COLUMNS_FK1)
INDEX(R PAY_USER_ROWS_F_FK1)
INDEX(CINST PAY_USER_COLUMN_INSTANCES_N1)
ORDERED */
round((fnd_number.canonical_to_number (R.row_low_range_or_name)/30.4) * p_period_days,2),
round((fnd_number.canonical_to_number (R.row_high_range)/30.4) * p_period_days,2),
CINST.value
from pay_user_tables TAB
, pay_user_columns C
, pay_user_rows_f R
, pay_user_column_instances_f CINST
where TAB.user_table_id = p_table_id
and C.user_table_id = TAB.user_table_id
and nvl (C.business_group_id,
p_bus_grp_id) = p_bus_grp_id
and nvl (C.legislation_code,
'MX') = 'MX'
and upper (C.user_column_name) = upper (p_column_name)
and CINST.user_column_id = C.user_column_id
and R.user_table_id = TAB.user_table_id
and p_eff_date between R.effective_start_date
and R.effective_end_date
and nvl (R.business_group_id,
p_bus_grp_id) = p_bus_grp_id
and nvl (R.legislation_code,
'MX') = 'MX'
and TAB.user_key_units = 'N'
and CINST.user_row_id = R.user_row_id
and p_eff_date between CINST.effective_start_date
and CINST.effective_end_date
and nvl (CINST.business_group_id,
p_bus_grp_id) = p_bus_grp_id
and nvl (CINST.legislation_code,
'MX') = 'MX'
order by R.display_sequence asc;
t_pro_table_values.delete;
select range_or_match, user_table_id
into l_range_or_match, l_table_id
from pay_user_tables
where upper(user_table_name) = upper(p_table_name)
and nvl (business_group_id,
p_bus_group_id) = p_bus_group_id
and nvl(legislation_code, 'MX') = 'MX';
select /*+ INDEX(C PAY_USER_COLUMNS_FK1)
INDEX(R PAY_USER_ROWS_F_FK1)
INDEX(CINST PAY_USER_COLUMN_INSTANCES_N1)
ORDERED */
CINST.value
into l_value
from pay_user_tables TAB
, pay_user_columns C
, pay_user_rows_f R
, pay_user_column_instances_f CINST
where TAB.user_table_id = l_table_id
and C.user_table_id = TAB.user_table_id
and nvl (C.business_group_id,
p_bus_group_id) = p_bus_group_id
and nvl (C.legislation_code,
'MX') = 'MX'
and upper (C.user_column_name) = upper (p_col_name)
and CINST.user_column_id = C.user_column_id
and R.user_table_id = TAB.user_table_id
and l_effective_date between R.effective_start_date
and R.effective_end_date
and nvl (R.business_group_id,
p_bus_group_id) = p_bus_group_id
and nvl (R.legislation_code,
'MX') = 'MX'
and fnd_number.canonical_to_number (p_row_value)
between (fnd_number.canonical_to_number (R.row_low_range_or_name)/30.4) * ln_period_days
and (fnd_number.canonical_to_number (R.row_high_range)/30.4) * ln_period_days
and TAB.user_key_units = 'N'
and CINST.user_row_id = R.user_row_id
and l_effective_date between CINST.effective_start_date
and CINST.effective_end_date
and nvl (CINST.business_group_id,
p_bus_group_id) = p_bus_group_id
and nvl (CINST.legislation_code,
'MX') = 'MX';