[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pbl.batch_line_id,
pbl.value_1,
pbl.value_2,
pbl.value_3,
pbl.value_4,
pbl.value_5,
pbl.value_6,
pbl.value_7,
pbl.value_8,
pbl.value_9,
pbl.value_10,
pbl.value_11,
pbl.value_12,
pbl.value_13,
pbl.value_14,
pbl.value_15,
pbh.business_group_id,
p_leg_code legislation_code,
pbl.effective_date,
pbl.element_type_id,
pbl.element_name,
pbl.assignment_id,
pbl.assignment_number,
pbl.batch_line_status
FROM pay_batch_lines pbl,
pay_batch_headers pbh
WHERE pbl.assignment_id is not null
AND pbl.assignment_id = p_assignment_id
AND pbh.business_group_id = p_bg_id
AND pbh.batch_id = pbl.batch_id
AND p_assignment_id is not null
UNION ALL
SELECT pbl.batch_line_id,
pbl.value_1,
pbl.value_2,
pbl.value_3,
pbl.value_4,
pbl.value_5,
pbl.value_6,
pbl.value_7,
pbl.value_8,
pbl.value_9,
pbl.value_10,
pbl.value_11,
pbl.value_12,
pbl.value_13,
pbl.value_14,
pbl.value_15,
pbh.business_group_id,
p_leg_code legislation_code,
pbl.effective_date,
pbl.element_type_id,
pbl.element_name,
asg.assignment_id,
pbl.assignment_number,
pbl.batch_line_status
FROM pay_batch_lines pbl,
pay_batch_headers pbh,
per_all_assignments_f asg
WHERE pbh.business_group_id = p_bg_id
AND pbl.assignment_id is null
AND asg.assignment_id = p_assignment_id
AND asg.business_group_id = p_bg_id
AND pbl.effective_date between asg.effective_start_date and asg.effective_end_date
AND upper(pbl.assignment_number) = upper(asg.assignment_number)
AND pbh.batch_id = pbl.batch_id
AND p_assignment_id is not null
UNION ALL
SELECT pbl.batch_line_id,
pbl.value_1,
pbl.value_2,
pbl.value_3,
pbl.value_4,
pbl.value_5,
pbl.value_6,
pbl.value_7,
pbl.value_8,
pbl.value_9,
pbl.value_10,
pbl.value_11,
pbl.value_12,
pbl.value_13,
pbl.value_14,
pbl.value_15,
pbh.business_group_id,
p_leg_code legislation_code,
pbl.effective_date,
pbl.element_type_id,
pbl.element_name,
pbl.assignment_id,
pbl.assignment_number,
pbl.batch_line_status
FROM pay_batch_lines pbl,
pay_batch_headers pbh
WHERE pbh.business_group_id = p_bg_id
AND pbl.assignment_id is null
AND (pbl.assignment_number is null or
not exists
(select null
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.business_group_id = p_bg_id
and upper(pbl.assignment_number) = upper(asg.assignment_number)
and pbl.effective_date between asg.effective_start_date and asg.effective_end_date))
AND pbh.batch_id = pbl.batch_id
AND p_assignment_id is null
ORDER BY element_type_id,element_name;
SELECT piv.name,
piv.display_sequence, piv.uom,piv.lookup_type,
piv.value_set_id,pet.input_currency_code
FROM pay_element_types_f pet,
pay_input_values_f piv
WHERE p_element_type_id is not null
AND p_element_type_id = pet.element_type_id
AND p_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.element_type_id = piv.element_type_id
AND p_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
UNION ALL
SELECT piv.name,
piv.display_sequence, piv.uom,piv.lookup_type,
piv.value_set_id,pet.input_currency_code
FROM pay_element_types_f pet,
pay_input_values_f piv
WHERE p_element_type_id is null
AND upper(p_element_name) = upper(pet.element_name)
AND (pet.business_group_id = p_business_group_id OR
(pet.business_group_id is null AND pet.legislation_code = p_legislation_code) OR
(pet.business_group_id is null AND pet.legislation_code is null))
AND p_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.element_type_id = piv.element_type_id
AND p_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
ORDER BY display_sequence, name;
g_input_values.name.delete;
g_input_values.display_sequence.delete;
g_input_values.uom.delete;
g_input_values.lookup_type.delete;
g_input_values.value_set_id.delete;
g_input_values.input_currency_code.delete;
SELECT FLV.LOOKUP_CODE
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.VIEW_APPLICATION_ID = 3
AND FLV.SECURITY_GROUP_ID = decode(substr(userenv('CLIENT_INFO'),55,1),
' ', 0, NULL, 0, '0', 0,
fnd_global.lookup_security_group(FLV.LOOKUP_TYPE,FLV.VIEW_APPLICATION_ID))
AND decode(FLV.TAG,
NULL, 'Y',
decode(substr(FLV.TAG,1,1),
'+', decode(sign(instr(FLV.TAG, HR_API.GET_LEGISLATION_CONTEXT)),
1, 'Y', 'N'),
'-', decode(sign(instr(FLV.TAG, HR_API.GET_LEGISLATION_CONTEXT)),
1, 'N', 'Y'),
'Y' )
) = 'Y'
AND flv.lookup_type = p_lookup_type
AND flv.meaning = p_meaning;
update pay_batch_lines
set value_1 = l_temp.value_1,
value_2 = l_temp.value_2,
value_3 = l_temp.value_3,
value_4 = l_temp.value_4,
value_5 = l_temp.value_5,
value_6 = l_temp.value_6,
value_7 = l_temp.value_7,
value_8 = l_temp.value_8,
value_9 = l_temp.value_9,
value_10 = l_temp.value_10,
value_11 = l_temp.value_11,
value_12 = l_temp.value_12,
value_13 = l_temp.value_13,
value_14 = l_temp.value_14,
value_15 = l_temp.value_15
where batch_line_id = p_rec.batch_line_id;
select cact.chunk_number,
pbg.business_group_id,
pbg.legislation_code,
cact.payroll_action_id
from pay_temp_object_actions cact,
pay_payroll_actions pct,
per_business_groups pbg
where cact.object_id = P_ASG_ID
and pct.payroll_action_id = cact.payroll_action_id
and pbg.business_group_id = pct.business_group_id;
select max(cact.chunk_number) last_chunk
from pay_temp_object_actions cact
where cact.payroll_action_id = p_pct_id;