[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
18-Jul-2011 abellur 115.88 9788510 Updated CAL_FORMULA_BO for NY, now
only one garnishment will be processed
in a run.
07-Sep-2011 abellur 115.89 12767090 Updated CAL_FORMULA_BO to include CA
for one garnishment deduction per
run.
04-Sep-2011 abellur 115.90 12795985 Updated CAL_FORMULA_BO for MA.
31-Oct-2011 abellur 115.91 12795985 Updated CAL_FORMULA_BO for MA.
01-Jan-2012 abellur 115.92 13546097 Updated CAL_FORMULA_BO for CO
22-Feb-2012 abellur 115.93 13727976 Updated CAL_FORMULA_SS child support
fee calculation stays with in limit.
28-May-2012 rgottipa 115.94 13694776 Updated CAL_FORMULA_BO to include GA
for one garnishment deduction per
run.
27-Jun-2012 abellur 115.95 14032248 Updated BASE_FORMULA, CAL_FORMULA_SS
and CAL_FORMULA_BO for Voluntary
deduction, so state level selection
needs to work for earnings rules.
19-Jul-2012 abellur 115.96 14265307 Updated BASE_FORMULA, CAL_FORMULA_SS
to include the global variable
GLB_TOTAL_WHLD_SUPPP_FEE_MNTH
This addresses multiple deductions
fee aspect overriding max limit.
21-Aug-2012 sbachu 115.97 14324196 Initialized the local variables
so that null is not returned to
fast formula and Quick pay does not
error out.
21-Aug-2012 rgottipa 115.98 14357293 The logic for
DI_total_state_exemption is
modified in Cal_Formula_BO if two
payments happened in the same
pay period.
10-Oct-2012 abellur 115.99 14265244 Updated BASE_FORMULA, fee is not
made to zero if dedn is zero for
support. It is handled in calc
formula, to process arrears.
12-Oct-2012 rgottipa 115.100 14357621 Updated CAL_FORMULA_BO, fee should
be deducted if there is
no mulitple deductions and
Subject_DISPOSABLE_INCOME - dedn_amt
is greater than zero.
19-Oct-2012 abellur 115.101 13976486 Updated CAL_FORMULA_BO, the logic
13246850 introduced for the bug 7674615, is
and checking for federal minimum wages
when DCIA is present is updated.
Federal exemption now excludes
the categories AY,BO,CS,SS and TL.
01-Jan-2013 abellur 115.102 15844070 Updated BASE_FORMULA, the percentage
entered at element entry will be
considered against Gross Wages
for Illinois, categories CD and G.
15-Feb-2013 rgottipa 115.103 16212524 If FEE is rule is defined for 'EL'
category (either seed or custom defined)
fee should deduct for that state.
11-Mar-2013 rgottipa 115.104 14726857 Modified CAL_FORMULA_BO for CA,
caliculation for DI_state_exemption_amt
..****************************************************************************/
-- New Function added (Reference Bug# 5295813)
/****************************************************************************
Name : GET_PRORATED_DEDN_AMOUNT
Description : This Function Prorates the Deduction Amount in case one or
more Actual Deduction Amount expects less than the Divide
Equally Amount.
*****************************************************************************/
FUNCTION GET_PRORATED_DEDN_AMOUNT
(
DI_subj number,
P_CTX_ORIGINAL_ENTRY_ID number
) RETURN NUMBER IS
cntr number ;
hr_utility.trace('Original Deduction is less and Modified Deduction Table is Updated already, Counter := '||ln_counter_lwrdedn) ;
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'GARN_DEBUG_ON';
select target.MAX_FEE_AMOUNT from
PAY_US_GARN_FEE_RULES_F target,
PAY_ELEMENT_TYPES_F pet,
PAY_ELEMENT_ENTRIES_F pee
WHERE target.state_code = substr(P_CTX_JURISDICTION_CODE,1,2)
AND target.garn_category = pet.element_information1
AND P_CTX_DATE_EARNED BETWEEN target.effective_start_date
AND target.effective_end_date
AND pet.element_type_id = pee.element_type_id
AND pee.element_entry_id = P_CTX_ELEMENT_ENTRY_ID
AND P_CTX_DATE_EARNED BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT nvl(entry_information9, 'N')
FROM pay_element_entries_f
WHERE element_entry_id = P_CTX_ORIGINAL_ENTRY_ID
AND entry_information_category = 'US_INVOLUNTARY DEDUCTIONS'
AND P_CTX_DATE_EARNED BETWEEN effective_start_date and effective_end_date;
select peev.screen_entry_value
from pay_element_entries_f peef,
pay_element_entry_values_f peev,
pay_input_values_f pivf
where peef.element_entry_id = P_CTX_ELEMENT_ENTRY_ID
and peev.element_entry_id = peef.element_entry_id
and pivf.element_type_id = peef.element_type_id
and pivf.name = c_input_value_name
and peev.input_value_id = pivf.input_value_id
and P_CTX_DATE_EARNED between peev.effective_start_date
and peev.effective_end_date;
select peev_si.screen_entry_value
from pay_element_entries_f peef_base,
pay_element_entries_f peef_si,
pay_element_entry_values_f peev_si,
pay_input_values_f pivf_si,
pay_element_types_f pet_base,
pay_element_types_f pet_si
where peef_base.element_entry_id = P_CTX_ELEMENT_ENTRY_ID
and peef_base.element_type_id = pet_base.element_type_id
and pet_base.element_information18 = pet_si.element_type_id
and peef_base.assignment_id = peef_si.assignment_id
and peef_si.element_type_id = pet_si.element_type_id
and peev_si.element_entry_id = peef_si.element_entry_id
and pivf_si.element_type_id = peef_si.element_type_id
and pivf_si.name = c_input_value_name
and peev_si.input_value_id = pivf_si.input_value_id
and P_CTX_DATE_EARNED between peev_si.effective_start_date
and peev_si.effective_end_date
and P_CTX_DATE_EARNED between peef_si.effective_start_date
and peef_si.effective_end_date
and P_CTX_DATE_EARNED between pivf_si.effective_start_date
and pivf_si.effective_end_date
and P_CTX_DATE_EARNED between pet_si.effective_start_date
and pet_si.effective_end_date ;
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'GARN_DEBUG_ON';
SELECT nvl(entry_information4, default_number),
nvl(entry_information5, default_number),
nvl(entry_information6, default_number),
nvl(entry_information7, default_number)
FROM pay_element_entries_f
WHERE element_entry_id = P_CTX_ORIGINAL_ENTRY_ID
AND entry_information_category = 'US_INVOLUNTARY DEDUCTIONS'
AND c_override_date BETWEEN effective_start_date and effective_end_date;
select aei.aei_information3
from per_assignment_extra_info aei,
pay_element_entries_f pee
where aei.assignment_id = pee.assignment_id
and aei.information_type = 'US_PRORATION_RULE'
and aei.aei_information_category = 'US_PRORATION_RULE'
and aei.aei_information2 = garn_cat
and substr(aei.aei_information1, 1, 2) = substr(P_CTX_JURISDICTION_CODE, 1, 2)
and pee.element_entry_id = P_CTX_ORIGINAL_ENTRY_ID ;
SELECT nvl(entry_information9, 'N')
FROM pay_element_entries_f
WHERE element_entry_id = P_CTX_ORIGINAL_ENTRY_ID
AND entry_information_category = 'US_INVOLUNTARY DEDUCTIONS'
AND P_CTX_DATE_EARNED BETWEEN effective_start_date and effective_end_date;
select target.MAX_FEE_AMOUNT from
PAY_US_GARN_FEE_RULES_F target,
PAY_ELEMENT_TYPES_F pet
WHERE target.state_code = substr(P_CTX_JURISDICTION_CODE,1,2)
AND target.garn_category = pet.element_information1
AND P_CTX_DATE_EARNED BETWEEN target.effective_start_date
AND target.effective_end_date
AND pet.element_type_id = P_CTX_ELEMENT_TYPE_ID
AND P_CTX_DATE_EARNED BETWEEN pet.effective_start_date
AND pet.effective_end_date;
select rtrim(element_name,' Calculator' )
from pay_element_types_f
where element_type_id = p_ele_type_id;
SELECT fed_information1
FROM pay_us_federal_tax_info_f
WHERE fed_information_category = 'WAGEATTACH LIMIT'
AND P_CTX_DATE_EARNED BETWEEN effective_start_date
AND effective_end_date;
* Delete the GLOBAL tables once an employee is procesed
*/
if GLB_NUM_ELEM = 0 then
reset_global_var;
SELECT hr_general.decode_lookup('FREQUENCY', ASSIGN.frequency)
,ASSIGN.normal_hours
FROM per_all_assignments_f ASSIGN
where cp_date_earned BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and ASSIGN.assignment_id = cp_assignment_id
and UPPER(ASSIGN.frequency) = 'W';
SELECT lookup_code
INTO v_pay_basis
FROM hr_lookups lkp
WHERE lkp.application_id = 800
AND lkp.lookup_type = 'PAY_BASIS'
AND lkp.meaning = p_freq;
SELECT user_column_name
INTO v_work_sched_name
FROM pay_user_columns
WHERE user_column_id = v_ws_id
AND NVL(business_group_id, p_bg) = p_bg
AND NVL(legislation_code,'US') = 'US';
select TPT.number_per_fiscal_year
into v_periods_per_fiscal_yr
from pay_payrolls_f PPF,
per_time_period_types TPT,
fnd_sessions fs
where PPF.payroll_id = p_payroll
and fs.session_id = USERENV('SESSIONID')
and fs.effective_date between PPF.effective_start_date and PPF.effective_end_date
and TPT.period_type = PPF.period_type;
SELECT TPT.number_per_fiscal_year
INTO v_annualizing_factor
FROM pay_payrolls_f PRL,
per_time_period_types TPT,
fnd_sessions fs
WHERE TPT.period_type = PRL.period_type
and fs.session_id = USERENV('SESSIONID')
and fs.effective_date BETWEEN PRL.effective_start_date
AND PRL.effective_end_date
AND PRL.payroll_id = p_payroll
AND PRL.business_group_id + 0 = p_bg;
END; /* SELECT LOOKUP CODE */
SELECT TPT.number_per_fiscal_year
INTO v_annualizing_factor
FROM pay_payrolls_f PRL,
per_time_period_types TPT,
fnd_sessions fs
WHERE TPT.period_type = PRL.period_type
and fs.session_id = USERENV('SESSIONID')
and fs.effective_date BETWEEN PRL.effective_start_date
AND PRL.effective_end_date
AND PRL.payroll_id = p_payroll
AND PRL.business_group_id + 0 = p_bg;
hr_utility.trace(' selecting from per_time_period_types');
SELECT PT.number_per_fiscal_year
INTO v_annualizing_factor
FROM per_time_period_types PT
WHERE UPPER(PT.period_type) = UPPER(p_freq);
SELECT user_column_name
INTO v_work_sched_name
FROM pay_user_columns
WHERE user_column_id = v_ws_id
AND NVL(business_group_id, p_bg) = p_bg
AND NVL(legislation_code,'US') = 'US';
select TPT.number_per_fiscal_year
into v_periods_per_fiscal_yr
from pay_payrolls_f PPF,
per_time_period_types TPT,
fnd_sessions fs
where PPF.payroll_id = p_payroll
and fs.session_id = USERENV('SESSIONID')
and fs.effective_date between PPF.effective_start_date and PPF.effective_end_date
and TPT.period_type = PPF.period_type;
SELECT entry_information8
FROM pay_element_entries_f
WHERE element_entry_id = CTX_ORIGINAL_ENTRY_ID
AND entry_information_category = 'US_INVOLUNTARY DEDUCTIONS'
AND PAY_EARNED_START_DATE BETWEEN effective_start_date AND effective_end_date;
SELECT petei.eei_information12
FROM pay_element_entries_f peef,
pay_element_types_f petf,
pay_element_types_f petf_calc,
pay_element_type_extra_info petei
WHERE peef.element_entry_id = CTX_ORIGINAL_ENTRY_ID
AND petf.element_type_id = peef.element_type_id
AND petf_calc.element_type_id = CTX_ELEMENT_TYPE_ID
AND petf_calc.element_type_id = petf.element_information5
AND petf.element_type_id = petei.element_type_id
AND petei.information_type = 'PAY_US_GARN_PROCESSING_RULE'
AND petei.eei_information11 = substr(CTX_JURISDICTION_CODE, 1, 2) ;
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'GARN_DEBUG_ON';
SELECT entry_information4
FROM pay_element_entries_f
WHERE element_entry_id = P_CTX_ORIGINAL_ENTRY_ID
AND entry_information_category = 'US_INVOLUNTARY DEDUCTIONS'
AND c_override_date BETWEEN effective_start_date and effective_end_date;
SELECT nvl(entry_information9, 'N')
FROM pay_element_entries_f
WHERE element_entry_id = P_CTX_ORIGINAL_ENTRY_ID
AND entry_information_category = 'US_INVOLUNTARY DEDUCTIONS'
AND P_CTX_DATE_EARNED BETWEEN effective_start_date and effective_end_date;
select aei.aei_information3
from per_assignment_extra_info aei,
pay_element_entries_f pee
where aei.assignment_id = pee.assignment_id
and aei.information_type = 'US_PRORATION_RULE'
and aei.aei_information_category = 'US_PRORATION_RULE'
and aei.aei_information2 = garn_cat
and substr(aei.aei_information1, 1, 2) = substr(P_CTX_JURISDICTION_CODE, 1, 2)
and pee.element_entry_id = P_CTX_ORIGINAL_ENTRY_ID ;
select target.MAX_FEE_AMOUNT from
PAY_US_GARN_FEE_RULES_F target,
PAY_ELEMENT_TYPES_F pet
WHERE target.state_code = substr(P_CTX_JURISDICTION_CODE,1,2)
AND target.garn_category = pet.element_information1
AND P_CTX_DATE_EARNED BETWEEN target.effective_start_date
AND target.effective_end_date
AND pet.element_type_id = P_CTX_ELEMENT_TYPE_ID
AND P_CTX_DATE_EARNED BETWEEN pet.effective_start_date
AND pet.effective_end_date;
select rtrim(element_name,' Calculator' )
from pay_element_types_f
where element_type_id = p_ele_type_id;
select min(effective_start_date)
from pay_element_entries_f
where element_entry_id = P_CTX_ORIGINAL_ENTRY_ID
group by element_entry_id;
select paf.assignment_id,
pus.state_code
from pay_element_entries_f pee,
per_all_assignments_f paf,
per_all_people_f ppf,
per_addresses pa,
pay_us_states pus
where pee.element_entry_id = p_element_entry_id
and pee.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and ppf.person_id = pa.person_id
and pa.primary_flag = 'Y'
and trim(pa.region_2) = pus.state_abbrev ;
select filing_status_code
from pay_us_emp_state_tax_rules_f pestr,
per_all_assignments_f paf,
hr_locations hl,
pay_us_states pus
where pestr.assignment_id = p_assignment_id
and pestr.assignment_id = paf.assignment_id
and paf.location_id = hl.location_id
and pus.state_abbrev = nvl(loc_information17,region_2) ;
select peev.screen_entry_value
from pay_element_entries_f peef,
pay_element_entry_values_f peev,
pay_input_values_f pivf
where peef.element_entry_id = P_CTX_ELEMENT_ENTRY_ID
and peev.element_entry_id = peef.element_entry_id
and pivf.element_type_id = peef.element_type_id
and pivf.name = c_input_value_name
and peev.input_value_id = pivf.input_value_id
and P_CTX_DATE_EARNED between peev.effective_start_date
and peev.effective_end_date;
SELECT fed_information1
FROM pay_us_federal_tax_info_f
WHERE fed_information_category = 'WAGEATTACH LIMIT'
AND P_CTX_DATE_EARNED BETWEEN effective_start_date
AND effective_end_date;
select puci.value
from pay_user_column_instances_f puci,
pay_user_tables put,
pay_user_rows_f pur,
pay_user_columns puc
where puci.user_column_id = puc.user_column_id
and puci.user_row_id = pur.user_row_id
and put.user_table_id = puc.user_table_id
and put.user_table_id = pur.user_table_id
and put.user_table_name = 'Wage Attach State Exemptions Table'
and pur.row_low_range_or_name = p_row_name
and puc.user_column_name = p_column_name
and p_ctx_date_earned between puci.effective_start_date
and puci.effective_end_date;
select period_type
from pay_all_payrolls_f
where payroll_id = p_payroll_id
and p_ctx_date_earned between effective_start_date
and effective_end_date;
SELECT count(petei.eei_information13)
FROM pay_element_entries_f peef,
pay_element_types_f petf,
pay_element_types_f petf_calc,
pay_element_type_extra_info petei
WHERE peef.element_entry_id = CTX_ORIGINAL_ENTRY_ID
AND petf.element_type_id = peef.element_type_id
AND petf_calc.element_type_id = CTX_ELEMENT_TYPE_ID
AND petf_calc.element_type_id = petf.element_information5
AND petf.element_type_id = petei.element_type_id
AND petei.information_type = 'PAY_US_GARN_PROCESSING_RULE'
AND petei.eei_information11 = substr(CTX_JURISDICTION_CODE, 1, 2)
GROUP BY petei.information_type
,petei.eei_information11;
SELECT petei.eei_information13
,petei.eei_information14
FROM pay_element_entries_f peef,
pay_element_types_f petf,
pay_element_types_f petf_calc,
pay_element_type_extra_info petei
WHERE peef.element_entry_id = CTX_ORIGINAL_ENTRY_ID
AND petf.element_type_id = peef.element_type_id
AND petf_calc.element_type_id = CTX_ELEMENT_TYPE_ID
AND petf_calc.element_type_id = petf.element_information5
AND petf.element_type_id = petei.element_type_id
AND petei.information_type = 'PAY_US_GARN_PROCESSING_RULE'
AND petei.eei_information11 = substr(CTX_JURISDICTION_CODE, 1, 2) ;
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'GARN_DEBUG_ON';
select rtrim(element_name,' Calculator' )
from pay_element_types_f
where element_type_id = p_ele_type_id;
select ppei.pei_information1
from per_people_extra_info ppei,
pay_element_entries_f peef,
per_all_assignments_f paaf
where peef.element_entry_id = P_CTX_ORIGINAL_ENTRY_ID
and peef.assignment_id = paaf.assignment_id
and paaf.person_id = ppei.person_id
and ppei.information_type = 'US_FED_LEVY_FIL_STATUS_YEAR';
select min(effective_start_date)
from pay_element_entries_f
where element_entry_id = P_CTX_ORIGINAL_ENTRY_ID
group by element_entry_id;
SELECT entry_information10
FROM pay_element_entries_f
WHERE element_entry_id = P_CTX_ORIGINAL_ENTRY_ID
AND entry_information_category = 'US_INVOLUNTARY DEDUCTIONS'
AND P_CTX_DATE_EARNED BETWEEN effective_start_date and effective_end_date;
SELECT fed_information1
FROM pay_us_federal_tax_info_f
WHERE fed_information_category = 'WAGEATTACH LIMIT'
AND P_CTX_DATE_EARNED BETWEEN effective_start_date
AND effective_end_date;
select decode(GLB_FIL_STAT(P_CTX_ORIGINAL_ENTRY_ID),'01','Single','03','Head Of HouseHold')
into lv_Filing_Status_name
from dual ;
Description : This procedure deletes all Global tables created and
resets the value of other Global variables.
*****************************************************************************/
PROCEDURE RESET_GLOBAL_VAR IS
l_proc_name varchar2(50);
dedn_tab.delete;
gar_dedn_tab.delete;
fees_tab.delete;
arrears_tab.delete;
actual_dedn_tab.delete;
mod_dedn_tab.delete ; /* Bug# 5295813 */
GLB_AMT.delete;
GLB_ARREARS_OVERRIDE.delete;
GLB_ARREARS_DATE.delete;
GLB_NUM_DEPS.delete;
GLB_FIL_STAT.delete;
GLB_ALLOWS.delete;
GLB_DEDN_OVERRIDE.delete;
GLB_PCT.delete;
GLB_MTD_BAL.delete;
GLB_EXEMPT_AMT.delete;
GLB_PTD_CAP_AMT.delete;
GLB_PTD_BAL.delete;
GLB_TO_ACCRUED_FEES.delete;
GLB_MONTH_CAP_AMT.delete;
GLB_FEES_ASG_GRE_PTD.delete;
GLB_BASE_ASG_GRE_PTD.delete;
hr_utility.trace('Global Tables Deleted');