[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 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');