[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_information2 -- Y/N
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_employer_id
AND hoi.org_information_context = 'PER_PHF_STAT_INFO_CN'
AND hoi.org_information1 = p_contribution_area;
SELECT pri.classification_id
FROM pay_element_classifications pri,
pay_element_classifications sec
WHERE sec.legislation_code = 'CN'
AND sec.classification_name = 'Special Payments'
AND pri.parent_classification_id = sec.classification_id
AND pri.classification_name = p_special_payment_type;
SELECT hoi.org_information3
FROM hr_organization_information hoi
,per_all_assignments paf
,hr_soft_coding_keyflex hsc
,hr_all_organization_units hou
WHERE paf.assignment_id = p_assignment_id
AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND hsc.segment1 = hou.organization_id
AND hou.business_group_id = hoi.organization_id
AND hoi.org_information_context = 'PER_SPECIAL_TAX_METHODS_CN'
AND hoi.org_information1 = p_tax_area
AND hoi.org_information2 = p_special_payment_type_id
AND p_date_earned BETWEEN to_date(substr(hoi.org_information4,1,10),'YYYY/MM/DD')
AND to_date(NVL(substr(hoi.org_information5,1,10),'4712/12/31'),'YYYY/MM/DD');
SELECT pap.accrual_plan_id
FROM pay_accrual_plans pap,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet
WHERE pee.assignment_id = p_assignment_id
AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND pel.element_link_id = pee.element_link_id
AND pel.element_type_id = pet.element_type_id
AND pap.accrual_plan_element_type_id = pet.element_type_id
AND pap.accrual_category = p_plan_category ;
SELECT org_information4 -- EE Contribution Base
,org_information5 -- ER Contribution Base
,org_information6 -- Low Limit Method
,fnd_number.canonical_to_number(org_information7) -- Low Limit Amount
,org_information8 -- High Limit Method
,fnd_number.canonical_to_number(org_information9) -- High Limit Amount
,org_information10 -- Switch Period Periodicity
,org_information11 -- Switch Period Month
,org_information12 -- Rounding Method
,fnd_number.canonical_to_number(org_information13) -- Lowest Average Salary
,fnd_number.canonical_to_number(org_information14) -- Average Salary
,fnd_number.canonical_to_number(org_information17) -- EE Fixed Amount
,fnd_number.canonical_to_number(org_information18) -- ER Fixed Amount
,fnd_number.canonical_to_number(org_information19) -- EE/ER Tax Threshold amount for bug 6828199
FROM hr_organization_information
WHERE org_information_context = 'PER_CONT_AREA_CONT_BASE_CN'
AND organization_id = p_business_group_id
AND p_effective_date BETWEEN to_date(org_information15,'YYYY/MM/DD HH24:MI:SS')
AND to_date(nvl(org_information16,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS')
AND org_information1 = p_contribution_area
AND org_information2 = p_phf_si_type
AND org_information3 = p_hukou_type;
SELECT org_information4 -- EE Contribution Base
,org_information5 -- ER Contribution Base
,org_information6 -- Low Limit Method
,fnd_number.canonical_to_number(org_information7) -- Low Limit Amount
,org_information8 -- High Limit Method
,fnd_number.canonical_to_number(org_information9) -- High Limit Amount
,org_information10 -- Switch Period Periodicity
,org_information11 -- Switch Period Month
,org_information12 -- Rounding Method
,fnd_number.canonical_to_number(org_information13) -- Lowest Average Salary
,fnd_number.canonical_to_number(org_information14) -- Average Salary
--
-- Bug 3017511 changes. Added these two missed columns
--
,fnd_number.canonical_to_number(org_information17) -- EE Fixed Amount
,fnd_number.canonical_to_number(org_information18) -- ER Fixed Amount
,fnd_number.canonical_to_number(org_information19) -- EE/ER Tax Threshold amount for bug 6828199
FROM hr_organization_information
WHERE org_information_context = 'PER_CONT_AREA_CONT_BASE_CN'
AND organization_id = p_business_group_id
AND p_effective_date BETWEEN to_date(org_information15,'YYYY/MM/DD HH24:MI:SS')
AND to_date(nvl(org_information16,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS')
AND org_information1 = p_contribution_area
AND org_information2 = p_phf_si_type
AND org_information3 IS NULL;
SELECT fnd_number.canonical_to_number(org_information4) -- EE Rate
,org_information5 -- EE Percent or Fixed
,fnd_number.canonical_to_number(org_information6) -- EE Rate
,org_information7 -- EE Percent or Fixed
,org_information8 -- EE Rounding Method
,org_information12 -- ER Rounding Method
,fnd_number.canonical_to_number(org_information13) -- EE Tax Threshold rate for bug 6828199
,fnd_number.canonical_to_number(org_information14) -- ER Tax thershold rate for bug 6828199
FROM hr_organization_information
WHERE org_information_context = 'PER_CONT_AREA_PHF_SI_RATES_CN'
AND organization_id = p_business_group_id
AND p_effective_date BETWEEN to_date(org_information10,'YYYY/MM/DD HH24:MI:SS')
AND to_date(nvl(org_information11,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS')
AND org_information1 = p_contribution_area
AND org_information3 = p_phf_si_type
AND org_information2 = p_employer_id
AND org_information9 = p_hukou_type;
SELECT fnd_number.canonical_to_number(org_information4) -- EE Rate
,org_information5 -- EE Percent or Fixed
,fnd_number.canonical_to_number(org_information6) -- EE Rate
,org_information7 -- EE Percent or Fixed
,org_information8 -- EE Rounding Method
,org_information12 -- ER Rounding Method
,fnd_number.canonical_to_number(org_information13) -- EE Tax Threshold rate for bug 6828199
,fnd_number.canonical_to_number(org_information14) -- ER Tax thershold rate for bug 6828199
FROM hr_organization_information
WHERE org_information_context = 'PER_CONT_AREA_PHF_SI_RATES_CN'
AND organization_id = p_business_group_id
AND p_effective_date BETWEEN to_date(org_information10,'YYYY/MM/DD HH24:MI:SS')
AND to_date(nvl(org_information11,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS')
AND org_information1 = p_contribution_area
AND org_information3 = p_phf_si_type
AND org_information2 = p_employer_id
AND org_information9 IS NULL;
SELECT fnd_number.canonical_to_number(org_information4) -- EE Rate
,org_information5 -- EE Percent or Fixed
,fnd_number.canonical_to_number(org_information6) -- EE Rate
,org_information7 -- EE Percent or Fixed
,org_information8 -- EE Rounding Method
,org_information12 -- ER Rounding Method
,fnd_number.canonical_to_number(org_information13) -- EE Tax Threshold rate for bug 6828199
,fnd_number.canonical_to_number(org_information14) -- ER Tax thershold rate for bug 6828199
FROM hr_organization_information
WHERE org_information_context = 'PER_CONT_AREA_PHF_SI_RATES_CN'
AND organization_id = p_business_group_id
AND p_effective_date BETWEEN to_date(org_information10,'YYYY/MM/DD HH24:MI:SS')
AND to_date(nvl(org_information11,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS')
AND org_information1 = p_contribution_area
AND org_information3 = p_phf_si_type
AND org_information2 IS NULL
AND org_information9 IS NULL;
SELECT fnd_number.canonical_to_number(org_information4) -- EE Rate
,org_information5 -- EE Percent or Fixed
,fnd_number.canonical_to_number(org_information6) -- EE Rate
,org_information7 -- EE Percent or Fixed
,org_information8 -- EE Rounding Method
,org_information12 -- ER Rounding Method
,fnd_number.canonical_to_number(org_information13) -- EE Tax Threshold rate for bug 6828199
,fnd_number.canonical_to_number(org_information14) -- ER Tax thershold rate for bug 6828199
FROM hr_organization_information
WHERE org_information_context = 'PER_CONT_AREA_PHF_SI_RATES_CN'
AND organization_id = p_business_group_id
AND p_effective_date BETWEEN to_date(org_information10,'YYYY/MM/DD HH24:MI:SS')
AND to_date(nvl(org_information11,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS')
AND org_information1 = p_contribution_area
AND org_information3 = p_phf_si_type
AND org_information9 = p_hukou_type
AND org_information2 IS NULL ;
SELECT fnd_number.canonical_to_number(round(months_between(c_effective_date,min(effective_start_date))/12,2)) mths_of_service
FROM per_all_assignments_f
WHERE assignment_id = c_assignment_id; --
SELECT count(inst.user_column_instance_id)
FROM pay_user_column_instances_f inst
,pay_user_columns user_col
,pay_user_tables user_tab
WHERE user_tab.user_table_name = c_table_name
AND user_tab.legislation_code = 'CN'
AND user_tab.user_table_id = user_col.user_table_id
AND user_col.user_column_name = c_col_name
AND user_col.legislation_code = 'CN'
AND user_col.user_column_id = inst.user_column_id
AND c_effective_date BETWEEN inst.effective_start_date
AND inst.effective_end_date;
SELECT org_information1 -- EE Rounding Method
, org_information2 -- ER Rounding Method
FROM hr_organization_information
WHERE org_information_context = 'PER_ORG_ANNUITY_ROUND_CN'
AND organization_id = p_business_group_id;
FUNCTION update_element_entry
(p_business_group_id IN NUMBER
,p_element_entry_id IN NUMBER
,p_calculation_date IN DATE
)
RETURN VARCHAR2
IS
--
CURSOR c_ovn (p_calculation_date IN DATE) IS
SELECT object_version_number
FROM pay_element_entries_f
WHERE element_entry_id = p_element_entry_id
AND p_calculation_date BETWEEN effective_start_date
AND effective_end_date;
SELECT piv.input_value_id
FROM pay_element_entries_f pee,
pay_element_links_f pel,
pay_input_values_f piv
WHERE pee.element_entry_id = p_element_entry_id
AND pee.element_link_id = pel.element_link_id
AND pel.element_type_id = piv.element_type_id
AND piv.name = 'Calculation Date'
AND p_calculation_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND p_calculation_date BETWEEN pel.effective_start_date AND pel.effective_end_date
AND p_calculation_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
SELECT effective_start_date
FROM pay_element_entry_values_f
WHERE element_entry_id = p_element_entry_id
AND input_value_id = p_input_value_id
AND effective_start_date >= p_calculation_date + 1
ORDER by effective_start_date;
SELECT past.per_system_status
FROM pay_element_entries_f pee,
per_assignments_f paf,
per_assignment_status_types past
WHERE pee.element_entry_id = p_element_entry_id
AND pee.assignment_id = paf.assignment_id
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND l_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date --Bug 3456162
AND l_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date; --Bug 3456162
SELECT 'exists'
FROM pay_element_entries_f
WHERE element_entry_id = p_element_entry_id
AND LAST_DAY(effective_end_date) = p_calculation_date;
g_procedure_name := g_package_name||'update_element_entry';
l_upd_mode := 'UPDATE';
l_upd_mode := 'UPDATE_CHANGE_INSERT';
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => l_upd_mode
,p_effective_date => p_calculation_date + 1
,p_business_group_id => p_business_group_id
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_ovn
,p_input_value_id1 => l_iv_id
,p_entry_value1 => fnd_date.date_to_chardate(p_calculation_date) -- Bug 3127638
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_warning
);
END update_element_entry;
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
WHERE pbt.balance_name = p_balance_name
AND pbd.dimension_name = p_dimension_name
AND pdb.balance_type_id = pbt.balance_type_id
AND pbt.legislation_code = 'CN'
AND pbd.legislation_code = 'CN'
AND pdb.legislation_code = 'CN'
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
SELECT decode(p_phf_si_type
,'PHF','PHF'
,'MEDICAL','Medical'
,'PENSION','Pension'
,'SUPPMED','Supp Medical'
,'MATERNITY','Maternity'
,'UNEMPLOYMENT','Unemployment'
,'INJURY','Injury'
,'ENTANN','Enterprise Annuity'
)
,decode(p_phf_si_type
,'PHF','PHF'
,'MEDICAL','Medical'
,'PENSION','Pension'
,'SUPPMED','Supplementary Medical'
,'MATERNITY','Maternity Insurance'
,'UNEMPLOYMENT','Unemploy Insurance'
,'INJURY','Injury Insurance'
,'ENTANN','Enterprise Annuity'
)
FROM dual;
SELECT fnd_number.canonical_to_number(global_value)
FROM ff_globals_f
WHERE legislation_code= 'CN'
AND global_name = p_global_name
AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
SELECT fnd_number.canonical_to_number(global_value)
FROM ff_globals_f
WHERE legislation_code = 'CN'
AND global_name = p_global_name
AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
/* l_message:= update_element_entry
(p_business_group_id => p_business_group_id
,p_element_entry_id => p_element_entry_id
,p_calculation_date => l_calculation_date
);
select /*+ORDERED*/ to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
from pay_assignment_actions paa
, pay_payroll_actions ppa
where paa.assignment_id = c_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date <= trunc(c_date,'Y') - 1
and ppa.effective_date >= trunc(add_months(c_date,-12),'Y')
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
and paa.action_status='C'
and ppa.action_status='C'
and paa.tax_unit_id = p_employer_id;
select /*+ORDERED*/ to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
from pay_assignment_actions paa
, pay_payroll_actions ppa
where paa.assignment_id = c_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date <= c_date - 1
and ppa.effective_date >= add_months(c_date,-1)
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
and paa.action_status='C'
and ppa.action_status='C'
and paa.tax_unit_id = p_employer_id;
SELECT hoi.org_information2
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'PER_SEVERANCE_PAY_TAX_RULE_CN'
AND hoi.org_information1 = p_tax_area
AND hoi.organization_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND p_date_earned BETWEEN to_date(substr(hoi.org_information3,1,10),'YYYY/MM/DD')
AND to_date(NVL(substr(hoi.org_information4,1,10),'4712/12/31'),'YYYY/MM/DD');
SELECT hoi.org_information1,
hoi.org_information2,
hoi.org_information3,
hoi.org_information4
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'PER_ENN_ANN_TAX_CN'
AND hoi.org_information1 = p_tax_area
AND hoi.organization_id = p_le;
SELECT hoi.org_information2
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'PER_ENN_ANN_TAX_CN'
AND hoi.org_information1 = p_tax_area
AND hoi.organization_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND p_date_paid BETWEEN fnd_date.canonical_to_date(hoi.org_information3)
AND nvl(fnd_date.canonical_to_date(hoi.org_information4), to_date( '31/12/4712','DD/MM/YYYY'));