[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct * from
(
select per_information8
, to_date(per_information9, 'YYYY/MM/DD HH24:MI:SS')
, to_date(per_information10, 'YYYY/MM/DD HH24:MI:SS')
, to_date(per_information11, 'YYYY/MM/DD HH24:MI:SS')
, effective_start_date
from per_all_people_f per
where per.person_id = (select max(paf.person_id)
from per_all_assignments_f paf
where paf.assignment_id = p_assignment_id)
and per.per_information6 = 'WP'
and nvl(per.current_employee_flag,'N') = 'Y' /* Bug 6158284 */
and per.effective_start_date <= p_end_date
and per.effective_end_date >= p_start_date)
order by effective_start_date;
select CINST.value
from pay_user_tables tab
,pay_user_columns col
,pay_user_rows_f r
,pay_user_column_instances_f cinst
where tab.user_table_name = 'FWL_RATES'
and col.user_table_id = tab.user_table_id
and upper(col.user_column_name)= upper('Daily Rate')
and cinst.user_column_id = col.user_column_id
and r.user_table_id = tab.user_table_id
and r.ROW_LOW_RANGE_OR_NAME = p_permit_category
and cinst.user_row_id = r.user_row_id
and p_date_earned between cinst.effective_start_date and cinst.effective_end_date;
select distinct * from
(
select per_information6
, to_date(per_information9, 'YYYY/MM/DD HH24:MI:SS')
, to_date(per_information10, 'YYYY/MM/DD HH24:MI:SS')
, to_date(per_information11, 'YYYY/MM/DD HH24:MI:SS')
from per_all_people_f per
where per.person_id = (select max(paf.person_id)
from per_all_assignments_f paf
where paf.assignment_id = p_assignment_id)
and per.per_information6 = 'SP'
and per.effective_start_date <= p_end_date
and per.effective_end_date >= p_start_date
order by per.effective_start_date);
select min(pap.effective_start_date),max(pap.effective_end_date) /*bug 2772106 */
from per_all_people_f pap,
per_all_assignments_f target
where target.assignment_id = c_assignment_id
and pap.person_id = target.person_id
and nvl(pap.current_employee_flag,'N') = 'Y';
select target.PER_INFORMATION6,
target.EFFECTIVE_START_DATE
from per_all_people_f target
where target.person_id = (select paf.person_id
from per_all_assignments_f paf
where paf.assignment_id = c_assignment_id
and c_date between paf.effective_start_date and paf.effective_end_date )
and c_date between target.effective_start_date and target.effective_end_date;
SELECT creator_type
FROM pay_element_entries_f pee
WHERE pee.element_entry_id = c_element_entry_id
AND c_date_earned between pee.effective_start_date and pee.effective_end_date;
** Creator_type of RR (updated element entry) or EE (new element entry) indicates
** it's a retropay element.
*/
OPEN c_get_creator_type(p_element_entry_id,p_date_earned);
select pet.element_name
from pay_element_entries_f pee,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_element_sets pes,
pay_element_type_rules petr,
pay_element_types_f pet
where pee.creator_id = paa.assignment_action_id
and pee.assignment_id = c_assignment_id
and pee.creator_type in ('EE', 'RR')
and pee.element_entry_id = c_element_entry_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.element_set_id = pes.element_set_id
and pes.element_set_id = petr.element_set_id
and petr.element_type_id = pet.element_type_id
and petr.include_or_exclude = 'I'
and c_date_earned between pet.effective_start_date and pet.effective_end_date
and c_date_earned between pee.effective_start_date and pee.effective_end_date
and pet.element_name like '%CPF%'
and pet.legislation_code = 'SG'
union all
select pec.classification_name
from pay_element_entries_f pee,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_element_sets pes,
pay_ele_classification_rules pecr,
pay_element_classifications pec
where pee.creator_id = paa.assignment_action_id
and pee.assignment_id = c_assignment_id
and pee.creator_type in ('EE', 'RR')
and pee.element_entry_id = c_element_entry_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.element_set_id = pes.element_set_id
and pes.element_set_id = pecr.element_set_id
and pecr.classification_id = pec.classification_id
and c_date_earned between pee.effective_start_date and pee.effective_end_date
and pec.classification_name in ('Statutory Deductions', 'Employer Liabilities')
and pec.legislation_code = 'SG';
select decode(pbt.balance_name,
'CPF Ordinary Earnings Eligible Comp', 'O',
'CPF Additional Earnings Eligible Comp', 'A', ' ')
from pay_balance_types pbt,
pay_balance_feeds_f pbf,
pay_input_values_f pivf,
pay_element_types_f petf
where pbt.balance_type_id = pbf.balance_type_id
and pbf.input_value_id = pivf.input_value_id
and pivf.element_type_id = petf.element_type_id
and pbt.balance_name = c_balance_name
and petf.element_type_id = c_element_type_id;
select paa.tax_unit_id
from pay_assignment_actions paa
where paa.assignment_id = c_assignment_id
and paa.assignment_action_id = c_assignment_action_id;
select max(paa.action_sequence),
to_number(to_char(ppa.effective_date,'MM')),
max(pas.person_id)
from per_assignments_f pas,
pay_assignment_actions paa,
pay_payroll_actions ppa
where (pas.person_id, paa.tax_unit_id)
IN (select pas1.person_id,
hsc1.segment1
from per_assignments_f pas1,
hr_soft_coding_keyflex hsc1
where pas1.assignment_id = c_assignment_id
and pas1.soft_coding_keyflex_id = hsc1.soft_coding_keyflex_id
and c_date_earned between pas1.effective_start_date and pas1.effective_end_date) /* Bug 5298298 */
and pas.assignment_id = paa.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R','Q','B','V','I')
and ppa.effective_date between trunc(add_months(c_date_earned,-12),'Y') /* Bug 4267196 */
and trunc(c_date_earned,'Y') - 1
group by to_number(to_char(ppa.effective_date,'MM'))
order by to_number(to_char(ppa.effective_date,'MM')) desc;
select paa.assignment_action_id,
ppa.effective_date,
paa.assignment_id
from per_assignments_f pas,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pas.person_id = c_person_id /* Bug 5298298 */
and paa.assignment_id = pas.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_sequence = c_action_sequence
and ppa.effective_date between trunc(add_months(c_date_earned,-12),'Y') /* Bug 4267196 */
and trunc(c_date_earned,'Y') - 1;
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 pbt.balance_type_id = pdb.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pdb.legislation_code = 'SG';
select global_value
from ff_globals_f
where global_name = 'CPF_ORD_MONTH_CAP_AMT'
and p_date_earned between effective_start_date and effective_end_date;
g_balance_value_tab.delete;
g_context_tab.delete;
g_detailed_bal_out_tab.delete;
select paa.tax_unit_id
from pay_assignment_actions paa
where paa.assignment_id = c_assignment_id
and paa.assignment_action_id = c_assignment_action_id;
select max(paa.action_sequence),
to_number(to_char(ppa.effective_date,'MM')),
max(pas.person_id)
from per_assignments_f pas,
pay_assignment_actions paa,
pay_payroll_actions ppa
where (pas.person_id, paa.tax_unit_id)
IN (select pas1.person_id,
hsc1.segment1
from per_assignments_f pas1,
hr_soft_coding_keyflex hsc1
where pas1.assignment_id = c_assignment_id
and pas1.soft_coding_keyflex_id = hsc1.soft_coding_keyflex_id
and c_date_earned between pas1.effective_start_date and pas1.effective_end_date) /* Bug 5298298 */
and pas.assignment_id = paa.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R','Q','B','V','I')
and ppa.effective_date between trunc(c_date_earned,'Y') /* Bug 4267196 */
and last_day(add_months(c_date_earned,-1))
group by to_number(to_char(ppa.effective_date,'MM'))
order by to_number(to_char(ppa.effective_date,'MM')) desc;
select paa.assignment_action_id,
ppa.effective_date,
pas.assignment_id
from per_assignments_f pas,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pas.person_id = c_person_id /* Bug 5298298 */
and paa.assignment_id = pas.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_sequence = c_action_sequence
and ppa.effective_date between trunc(c_date_earned,'Y') /* Bug 4267196 */
and last_day(add_months(c_date_earned,-1)) ;
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 pbt.balance_type_id = pdb.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pdb.legislation_code = 'SG';
select global_value
from ff_globals_f
where global_name = 'CPF_ORD_MONTH_CAP_AMT'
and p_date_earned between effective_start_date and effective_end_date;
g_balance_value_tab.delete;
g_context_tab.delete;
g_detailed_bal_out_tab.delete;
select sum(peev.screen_entry_value)
from pay_element_entry_values_f peev,
pay_element_entries_f pee,
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_classifications pec
where pee.assignment_id = p_assignment_id
and pee.source_asg_action_id in
(select paa1.assignment_action_id
from pay_assignment_actions paa1,
pay_payroll_actions ppa1
where paa1.assignment_id = pee.assignment_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type in ('R','Q','B','V','I')
and to_char(ppa1.effective_date,'MM') = to_char(p_date_earned,'MM'))
and pee.creator_type in ('EE','RR')
and pee.element_type_id = pet.element_type_id
and pet.classification_id = pec.classification_id
and pec.classification_name = 'Ordinary Earnings'
and pec.legislation_code = 'SG'
and pee.element_entry_id = peev.element_entry_id
and peev.input_value_id = piv.input_value_id
and piv.name = 'Pay Value'
and p_date_earned between pee.source_start_date
and pee.source_end_date
and p_date_earned between pet.effective_start_date
and pet.effective_end_date
and p_date_earned between piv.effective_start_date
and piv.effective_end_date ;
select formula_id, effective_start_date
from ff_formulas_f
where formula_name = c_formula_name
and legislation_code = 'SG'
and c_effective_date between effective_start_date and effective_end_date;