[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pet.element_name , pet.element_type_id
FROM pay_element_types pet
, pay_element_entries pee
WHERE pee.element_entry_id = p_ee_id
AND pee.element_type_id = pet.element_type_id
AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
SELECT scl.segment2
FROM hr_soft_coding_keyflex scl
,per_all_assignments paa
,pay_assignment_actions pac
WHERE paa.assignment_id = pac.assignment_id
AND pac.assignment_action_id = p_asg_act_id
AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
AND scl.enabled_flag = 'Y'
AND p_effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date;*/
SELECT screen_entry_value
FROM pay_element_entry_values_f peev
WHERE element_entry_id = p_ee_id
AND input_value_id IN
( select input_value_id
from pay_input_values_f
where element_type_id = p_ele_type_id
and name = 'Third Party Payee')
and p_effective_date between peev.effective_start_date and peev.effective_end_date;
SELECT effective_date
FROM pay_payroll_actions ppa, pay_assignment_actions paa
WHERE paa.assignment_action_id = p_asg_act_id
AND paa.payroll_action_id = ppa.payroll_action_id ;
SELECT TRANSLATE (UPPER(end_user_column_name), ' /','__') tag_name
FROM fnd_descr_flex_col_usage_vl
WHERE descriptive_flexfield_name = 'Action Information DF'
AND descriptive_flex_context_code = p_context_code
AND application_column_name = UPPER (p_node);
SELECT 'Y'
FROM fnd_descr_flex_col_usage_vl
WHERE descriptive_flexfield_name LIKE 'Action Information DF'
AND descriptive_flex_context_code = p_context_code
AND application_column_name LIKE p_application_column_name
AND enabled_flag = 'Y';
SELECT pai.action_information2 element_type_id
,pai.action_information3 input_value_id
/* Change the decode to get the element name with pension provider */
,decode(pai1.action_information8,NULL,decode(pai1.action_information3,'PP',
SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1)||' ('||
SUBSTR(pai1.action_information9,1,INSTR(pai1.action_information9,':')-1)||')',
SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1)),
decode(pai1.action_information3,'PP',SUBSTR(pai.action_information4,
INSTR(pai.action_information4,',')+1)||' ('||SUBSTR(pai1.action_information9,1,
INSTR(pai1.action_information9,':')-1)||')',SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1))) Name
,SUBSTR(pai.action_information4,1,INSTR(pai.action_information4,',')-1) CODE -- Changes for Bug 7229247
,pai.action_information5 type
,pai.action_information6 uom
,fnd_number.canonical_to_number(pai1.action_information8) record_count -- Format Changes for Payslip Bug - 7229247 /* 9358829 */
--,sum(pai1.action_information4) value
,substr(pai1.action_information9,instr(pai1.action_information9,':',-1)+1) unit_price -- Format Changes in Payslip Bug - 7229247
,sum(fnd_number.canonical_to_number(pai1.action_information4)) value -- Format Changes for Payslip Bug - 7229247
FROM pay_action_information pai
,pay_action_information pai1
,pay_assignment_actions paa
WHERE pai.action_context_type = 'PA'
AND pai.action_information_category = p_pa_category
AND pai1.action_context_type = 'AAP'
AND pai.action_information5 <> 'F'
AND pai1.action_information3 <> 'F'
---- Commented for performance fix
/* AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
FROM pay_assignment_actions paa
WHERE paa.source_action_id = p_action_context_id
AND paa.assignment_id = pai1.assignment_id
)
OR pai1.action_context_id = p_action_context_id)*/
and pai1.action_information_category = p_aap_category
and pai.action_information2 = pai1.action_information1
and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
and pai.action_context_id = paa.payroll_action_id
and pai1.action_context_id = paa.assignment_action_id
and paa.assignment_action_id = p_action_context_id
group by pai.action_information2
,pai.action_information3
,pai.action_information4
,pai.action_information5
,pai.action_information6
,pai.action_information9 -- Format Changes for Payslip Bug - 7229247
--Information 3 and 9 added in group by clause to get the sum based on pension provider
,pai1.action_information3
,pai1.action_information9 -- Format Changes for Payslip Bug - 7229247
,pai1.action_information10 /* 9358829 */
,pai1.action_information8 -- Format Changes for Payslip Bug - 7229247
ORDER BY pai.action_information5 DESC,fnd_number.canonical_to_number(pai1.action_information10); /* 9358829 */
SELECT pai.action_information2 element_type_id
,pai.action_information3 input_value_id
-- Changes for Payslip Format
-- Start
-- ,decode(pai1.action_information8,NULL,pai.action_information4,
-- pai.action_information4||'('||pai1.action_information8||')') Name
,SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1) Name
,SUBSTR(pai.action_information4,1,INSTR(pai.action_information4,',')-1) CODE
,fnd_number.canonical_to_number(pai1.action_information8) record_count
,substr(pai1.action_information9,instr(pai1.action_information9,':',-1)+1) unit_price
-- End
,pai.action_information5 type
,pai.action_information6 uom
--,sum(pai1.action_information4) value
,pai1.action_information4 value
FROM pay_action_information pai
,pay_action_information pai1
,pay_assignment_actions paa
WHERE pai.action_context_type = 'PA'
AND pai.action_information_category = p_pa_category
AND pai1.action_context_type = 'AAP'
AND pai.action_information5 = 'F'
AND pai1.action_information3 = 'F'
-- Commented for performance fix
/* AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
FROM pay_assignment_actions paa
WHERE paa.source_action_id = p_action_context_id
AND paa.assignment_id = pai1.assignment_id
)
OR pai1.action_context_id = p_action_context_id) */
and pai1.action_information_category = p_aap_category
and pai.action_information2 = pai1.action_information1
and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
and pai.action_context_id = paa.payroll_action_id
and pai1.action_context_id = paa.assignment_action_id
and paa.assignment_action_id = p_action_context_id
group by pai.action_information2
,pai.action_information3
,pai.action_information4 -- Format Changes for Payslip Bug - 7229247
,pai.action_information5
,pai.action_information6
,pai1.action_information4
,pai1.action_information8 -- Format Changes for Payslip Bug - 7229247
,pai1.action_information9 -- Format Changes for Payslip Bug - 7229247
-- ORDER BY pai.action_information5,pai1.action_information8 DESC;
SELECT ppf.payroll_name payroll_name
,ptp.period_name period_name
,ptp.period_type period_type
,ptp.start_date start_date
,ptp.end_date end_date
--,pai.effective_date payment_date
,ptp.default_dd_date payment_date
FROM per_time_periods ptp
,pay_payrolls_f ppf
,pay_action_information pai
WHERE ppf.payroll_id = ptp.payroll_id
AND pai.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ptp.time_period_id = pai.action_information16
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = p_category
AND pai.action_context_id=p_action_context_id;
OR pai.action_context_id = ( SELECT paa.source_action_id
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = p_action_context_id
AND paa.assignment_id = pai.Assignment_ID
));*/
SELECT legislative_parameters,payroll_id
INTO leg_param,l_ppa_payroll_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id ;
UPDATE pay_payroll_actions
SET payroll_id = l_payroll_id
WHERE payroll_action_id = p_payroll_action_id;
hr_utility.set_location('After Update', 2);