[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT business_group_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT legislation_code
FROM per_business_groups
WHERE business_group_id = p_business_group_id;
g_tab_asg_set_amnds.DELETE;
SELECT legislative_parameters
FROM pay_payroll_actions
WHERE payroll_action_id = p_pay_action_id;
SELECT formula_id
FROM hr_assignment_sets ags
WHERE assignment_set_id = c_asg_set_id
AND EXISTS(SELECT 1
FROM hr_assignment_set_criteria agsc
WHERE agsc.assignment_set_id = ags.assignment_set_id);
SELECT assignment_id, NVL(include_or_exclude
,'I') include_or_exclude
FROM hr_assignment_set_amendments
WHERE assignment_set_id = c_asg_set_id;
SELECT 'X'
FROM hr_assignment_set_amendments
WHERE assignment_set_id = c_asg_set_id
AND NVL(include_or_exclude, 'I') =
'I' -- hard coded as it's from lookup code
AND ROWNUM < 2;
g_tab_asg_set_amnds.DELETE;
'SELECT DISTINCT person_id FROM per_people_f ppf
,pay_payroll_actions ppa
WHERE ppf.business_group_id = ppa.business_group_id
AND ppa.payroll_action_id = :payroll_action_id
ORDER BY ppf.person_id';
'SELECT DISTINCT person_id FROM per_people_f ppf
,pay_payroll_actions ppa
WHERE ppf.business_group_id = ppa.business_group_id
AND ppa.payroll_action_id = :payroll_action_id
AND ppf.person_id = '
|| l_person_id
|| ' ORDER BY ppf.person_id';
'SELECT DISTINCT person_id
FROM per_all_assignments_f paa
,hr_assignment_set_amendments hasa
,pay_payroll_actions ppa
WHERE paa.business_group_id = ppa.business_group_id
AND ppa.payroll_action_id = :payroll_action_id
AND paa.assignment_id = hasa.assignment_id
AND NVL(hasa.include_or_exclude,'
|| '''I'''
|| ') = '
|| '''I'''
|| ' AND hasa.assignment_set_id = '
|| l_assignment_set_id;
SELECT NVL(effective_date, SYSDATE)
FROM fnd_sessions
WHERE session_id = USERENV('SESSIONID');
SELECT asg.assignment_id assignment_id, asg.payroll_id
FROM per_all_assignments_f asg
WHERE asg.person_id BETWEEN p_start_person AND p_end_person
AND asg.assignment_id = NVL(c_assignment_id, asg.assignment_id)
AND asg.business_group_id = c_business_group_id
AND ( c_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
OR ( asg.effective_start_date > c_effective_date
AND asg.effective_end_date =
(SELECT MIN(asg2.effective_end_date)
FROM per_all_assignments_f asg2
WHERE asg2.assignment_id = asg.assignment_id)
)
)
ORDER BY asg.assignment_id;
SELECT pay_assignment_actions_s.NEXTVAL
FROM DUAL;
SELECT asg.assignment_id
FROM per_all_assignments_f asg
,hr_assignment_set_amendments hasa
WHERE asg.assignment_id = hasa.assignment_id
AND hasa.assignment_set_id = c_assignment_set_id
AND NVL(hasa.include_or_exclude, 'I') = 'I'
AND asg.person_id BETWEEN p_start_person AND p_end_person
AND asg.effective_end_date < c_effective_date
AND NOT EXISTS(
SELECT 1
FROM per_all_assignments_f asg2
WHERE asg2.assignment_id = asg.assignment_id
AND ( c_effective_date
BETWEEN asg2.effective_start_date
AND asg2.effective_end_date
OR asg2.effective_start_date > c_effective_date
))
ORDER BY asg.assignment_id;
SELECT 'X'
FROM pqp_assignment_attributes_f
WHERE assignment_id = c_assignment_id
AND contract_type = c_contract
AND ( c_effective_date BETWEEN effective_start_date
AND effective_end_date
OR effective_start_date > c_effective_date
);
SELECT assignment_id, payroll_action_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_assignment_action_id;
g_output_file_records.DELETE;
SELECT MIN(aat.effective_start_date)
FROM pqp_assignment_attributes_f aat
WHERE aat.assignment_id = p_assignment_id
AND aat.contract_type IS NOT NULL;
SELECT MIN(asg.effective_start_date)
FROM per_all_assignments_f asg
WHERE asg.assignment_id = p_assignment_id
AND asg.normal_hours IS NOT NULL;
SELECT NAME
INTO l_log_string
FROM per_business_groups_perf
WHERE business_group_id = g_business_group_id;
SELECT event_group_name
INTO l_log_string
FROM pay_event_groups
WHERE event_group_id = g_configuration_data.pcv_information3;
SELECT formula_name
INTO l_log_string
FROM ff_formulas_f
WHERE formula_id = g_configuration_data.pcv_information5;
SELECT employee_number
INTO g_output_file_records(g_output_file_records.FIRST).employee_number
FROM per_all_people_f a
WHERE a.person_id =
(SELECT asg.person_id
FROM per_all_assignments_f asg
WHERE asg.assignment_id = p_assignment_id AND ROWNUM < 2)
AND effective_start_date = (SELECT MAX(b.effective_start_date)
FROM per_all_people_f b
WHERE b.person_id = a.person_id);
SELECT assignment_number
INTO g_output_file_records(g_output_file_records.FIRST).assignment_number
FROM per_all_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND a.effective_start_date =
(SELECT MAX(b.effective_start_date)
FROM per_all_assignments_f b
WHERE b.assignment_id = a.assignment_id);
t_impact_dates.DELETE;
update_value_for_event_dates(p_uom => p_uom
,p_assignment_id => p_assignment_id
,p_business_group_id => g_business_group_id
,p_formula_id => l_budget_fast_formula_id
,p_action => p_action
,p_effective_date => l_effective_date
);
update_value_for_event_dates(p_uom => p_uom
,p_assignment_id => p_assignment_id
,p_business_group_id => g_business_group_id
,p_formula_id => l_budget_fast_formula_id
,p_action => p_action
,p_effective_date => t_impact_dates(l_current
)
);
g_output_file_records.DELETE;
g_output_file_records.DELETE; -- do not include in clear cache
SELECT event_group_name
FROM pay_event_groups
WHERE event_group_id = p_event_group_id;
p_impact_dates.DELETE;
SELECT user_table_id
FROM pay_user_tables
WHERE user_table_name = 'PQP_CONTRACT_TYPES'
AND legislation_code = p_legislation_code;
SELECT pur.user_row_id
FROM pqp_assignment_attributes_f aat, pay_user_rows_f pur
WHERE aat.assignment_id = p_assignment_id
AND p_effective_date BETWEEN aat.effective_start_date
AND aat.effective_end_date
AND pur.user_table_id = p_pqp_contract_table_id
AND pur.business_group_id = aat.business_group_id
AND pur.row_low_range_or_name = aat.contract_type
AND aat.effective_start_date BETWEEN pur.effective_start_date
AND pur.effective_end_date;
SELECT inst2.effective_start_date
FROM pay_user_column_instances_f inst1
,pay_user_column_instances_f inst2
WHERE ( inst1.effective_start_date >= p_effective_start_date
OR p_effective_start_date BETWEEN inst1.effective_start_date
AND inst1.effective_end_date
)
AND inst1.user_row_id = p_user_row_id
AND inst2.user_column_instance_id =
inst1.user_column_instance_id
AND inst2.effective_start_date = inst1.effective_end_date + 1
AND NVL(inst2.VALUE, '{null}') <> NVL(inst1.VALUE, '~NULL~')
ORDER BY 1;
SELECT asg2.effective_start_date
FROM per_all_assignments_f asg1, per_all_assignments_f asg2
WHERE asg1.assignment_id = p_assignment_id
AND ( asg1.effective_start_date >= p_effective_start_date
OR p_effective_start_date BETWEEN asg1.effective_start_date
AND asg1.effective_end_date
)
AND asg2.assignment_id = asg1.assignment_id
AND asg2.effective_start_date = asg1.effective_end_date + 1
AND NVL(asg2.normal_hours, -1) <> NVL(asg1.normal_hours, -2)
UNION ALL
SELECT aat2.effective_start_date
FROM pqp_assignment_attributes_f aat1
,pqp_assignment_attributes_f aat2
WHERE aat1.assignment_id = p_assignment_id
AND ( aat1.effective_start_date >= p_effective_start_date
OR p_effective_start_date BETWEEN aat1.effective_start_date
AND aat1.effective_end_date
)
AND aat1.assignment_id = aat2.assignment_id
AND aat2.effective_start_date = aat1.effective_end_date + 1
AND NVL(aat2.contract_type, '{null}') <>
NVL(aat1.contract_type, '[NULL]')
UNION ALL
SELECT inst2.effective_start_date
FROM pay_user_column_instances_f inst1
,pay_user_column_instances_f inst2
WHERE ( inst1.effective_start_date >= p_effective_start_date
OR p_effective_start_date BETWEEN inst1.effective_start_date
AND inst1.effective_end_date
)
AND inst1.user_row_id = p_user_row_id
AND inst2.user_column_instance_id =
inst1.user_column_instance_id
AND inst2.effective_start_date = inst1.effective_end_date + 1
AND NVL(inst2.VALUE, '{null}') <> NVL(inst1.VALUE, '~NULL~')
ORDER BY 1;
PROCEDURE update_value_for_event_dates(
p_uom IN VARCHAR2
,p_assignment_id IN NUMBER
,p_business_group_id IN NUMBER
,p_formula_id IN NUMBER
,p_action IN VARCHAR2
,p_effective_date IN DATE
)
IS
CURSOR csr_formula_name(p_formula_id NUMBER)
IS
SELECT formula_name
FROM ff_formulas_f
WHERE formula_id = p_formula_id;
:= g_package_name || 'update_value_for_event_dates';
update_and_store_abv(p_uom => p_uom
,p_assignment_id => p_assignment_id
,p_business_group_id => p_business_group_id
,p_abv_value => fnd_number.canonical_to_number(l_outputs(l_out_cnt
).VALUE) -- bug 4372165
,p_action => p_action
,p_effective_date => p_effective_date
);
END update_value_for_event_dates;
PROCEDURE update_and_store_abv(
p_uom IN VARCHAR2
,p_assignment_id IN NUMBER
,p_business_group_id IN NUMBER
,p_abv_value IN NUMBER
,p_action IN VARCHAR2
,p_effective_date IN DATE
)
IS
CURSOR csr_abv_exists(p_assignment_id NUMBER, p_uom VARCHAR2)
IS
SELECT 1
FROM per_assignment_budget_values_f
WHERE assignment_id = p_assignment_id AND unit = p_uom
AND ROWNUM < 2;
SELECT assignment_budget_value_id, VALUE, effective_start_date
,effective_end_date, object_version_number
FROM per_assignment_budget_values_f
WHERE assignment_id = p_assignment_id
AND unit = p_uom
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT effective_end_date
FROM per_assignment_budget_values_f
WHERE assignment_budget_value_id = p_assignment_budget_value_id
AND effective_start_date > p_effective_date
AND ROWNUM < 2;
:= g_package_name || 'update_and_store_abv';
,p_program_update_date => NULL
,p_assignment_budget_value_id => l_effective_abv_row.assignment_budget_value_id
,p_object_version_number => l_effective_abv_row.object_version_number
,p_effective_start_date => l_effective_abv_row.effective_start_date
,p_effective_end_date => l_effective_abv_row.effective_end_date
);
'INSERT';
l_datetrack_mode := 'UPDATE_OVERRIDE';
l_datetrack_mode := 'UPDATE';
IF l_datetrack_mode <> 'UPDATE_OVERRIDE'
THEN
--
-- only do a datetrack UPDATE or correction if the value is different
--
l_proc_step := 45;
,p_program_update_date => NULL
,p_effective_start_date => l_effective_abv_row.effective_start_date
,p_effective_end_date => l_effective_abv_row.effective_end_date
);
ELSE -- l_datetrack_mode = 'UPDATE_OVERRIDE' THEN
IF g_debug
THEN
IF g_is_concurrent_program_run
THEN
debug('g_is_concurrent_program_run:TRUE');
,p_program_update_date => NULL
,p_effective_start_date => l_effective_abv_row.effective_start_date
,p_effective_end_date => l_effective_abv_row.effective_end_date
);
,p_program_update_date => NULL
,p_effective_start_date => l_effective_abv_row.effective_start_date
,p_effective_end_date => l_effective_abv_row.effective_end_date
);
'UPDATE_OVERRIDE';
'UPDATE_OVERRIDE';
END IF; -- IF l_datetrack_mode <> 'UPDATE_OVERRIDE' THEN
END update_and_store_abv;
SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_pay_action_id AND action_status = 'C';
SELECT COUNT(*)
FROM pay_assignment_actions
WHERE payroll_action_id = p_pay_action_id;
delete assignment actions
l_proc_step := 10;
DELETE FROM pay_action_interlocks
WHERE locking_action_id = l_asg_action_id;
debug(SQL%ROWCOUNT || ' pay_action_interlocks rows deleted');
DELETE FROM pay_message_lines
WHERE source_id = l_asg_action_id AND source_type = 'A';
debug(SQL%ROWCOUNT || ' pay_message_lines rows deleted');
DELETE FROM pay_assignment_actions
WHERE assignment_action_id = l_asg_action_id;
debug(SQL%ROWCOUNT || ' pay_assignment_action rows deleted');
DELETE FROM pay_message_lines
WHERE source_id = p_pay_action_id AND source_type = 'P';
debug(SQL%ROWCOUNT || ' pay_message_lines rows deleted');
DELETE FROM pay_population_ranges
WHERE payroll_action_id = p_pay_action_id;
debug(SQL%ROWCOUNT || ' pay_population_ranges rows deleted');
DELETE FROM pay_payroll_actions
WHERE payroll_action_id = p_pay_action_id;
debug(SQL%ROWCOUNT || ' pay_payroll_actions rows deleted');