[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SCL.segment2 , business_group_id
FROM
per_all_assignments_f PAA,
hr_soft_coding_keyflex SCL
WHERE ASSIGNMENT_ID = p_assignment_id
AND PAA.soft_coding_keyflex_id = SCL.soft_coding_keyflex_id
AND p_effective_date BETWEEN PAA.effective_start_date AND PAA.effective_end_date ;
SELECT hoi3.organization_id
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
, hr_organization_information hoi3
WHERE o1.business_group_id =p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = p_organization_id
AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
AND hoi2.organization_id = hoi3.organization_id
AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER' ;
SELECT pet.element_name
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;
SELECT eev1.screen_entry_value screen_entry_value
FROM per_all_assignments_F asg1
,per_all_assignments_F asg2
,per_all_people_F per
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_F ee
,pay_element_entry_values_F eev1
,pay_assignment_actions pac
,pay_payroll_actions ppa
WHERE per.person_id = asg1.person_id
AND ppa.BUSINESS_GROUP_ID = per.BUSINESS_GROUP_ID
and ppa.effective_date BETWEEN per.effective_start_date and per.effective_end_date
AND asg2.person_id = per.person_id
and ppa.BUSINESS_GROUP_ID = asg1.BUSINESS_GROUP_ID
and ppa.BUSINESS_GROUP_ID = asg2.BUSINESS_GROUP_ID
and ppa.effective_date BETWEEN asg1.effective_start_date and asg1.effective_end_date
and ppa.effective_date BETWEEN asg2.effective_start_date and asg2.effective_end_date
AND asg2.primary_flag = 'Y'
AND pac.assignment_action_id = p_asg_act_id
AND pac.payroll_action_id = ppa.payroll_action_id
AND asg1.assignment_id = pac.assignment_id
AND et.element_name = 'Court Order Information'
and ppa.effective_date BETWEEN et.effective_start_date and et.effective_end_date
AND et.legislation_code = 'SE'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = 'Enforcement Office'
and ppa.effective_date BETWEEN iv1.effective_start_date and iv1.effective_end_date
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
and ppa.effective_date BETWEEN el.effective_start_date and el.effective_end_date
AND ee.assignment_id = asg2.assignment_id
AND ee.element_link_id = el.element_link_id
and ppa.effective_date BETWEEN ee.effective_start_date and ee.effective_end_date
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
and ppa.effective_date BETWEEN eev1.effective_start_date and eev1.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 '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 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 paa_chk.serial_number
FROM pay_assignment_actions paa_xfr,
pay_action_interlocks pai_xfr,
pay_action_interlocks pai_chk,
pay_assignment_actions paa_chk,
pay_payroll_actions ppa_chk
WHERE paa_xfr.assignment_action_id = pai_xfr.locking_action_id
AND pai_xfr.locked_action_id = pai_chk.locked_action_id
AND pai_chk.locking_action_id = paa_chk.assignment_action_id
AND paa_chk.payroll_action_id = ppa_chk.payroll_action_id
AND ppa_chk.action_type = 'H'
AND paa_xfr.assignment_action_id = g_action_ctx_id;
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
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_category1
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 pai.action_information2 element_type_id
,pai.action_information3 input_value_id
,SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1) Name
-- ,decode(pai1.action_information8,NULL,SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1),
-- SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1) ||'('||pai1.action_information8||')') Name
,pai.action_information5 type
,pai.action_information6 uom
,SUBSTR(pai.action_information4,1,INSTR(pai.action_information4,',')-1) CODE
,pai1.action_information8 record_count
,substr(pai1.action_information9,instr(pai1.action_information9,':',-1)+1) unit_price
,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
ORDER BY pai.action_information2;
SELECT pai.action_information2 element_type_id
,pai.action_information3 input_value_id
,SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1) Name
-- ,decode(pai1.action_information8,NULL,SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1),
-- SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1) ||'('||pai1.action_information8||')') Name
,pai.action_information5 type
,pai.action_information6 uom
,SUBSTR(pai.action_information4,1,INSTR(pai.action_information4,',')-1) CODE
--,pai1.action_information8 record_count
--,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'
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
--group by pai.action_information2
-- ,pai.action_information3
-- ,pai.action_information4
-- ,pai.action_information5
-- ,pai.action_information6
-- ,pai1.action_information8
ORDER BY pai.action_information5,pai1.action_information8 DESC;*/
/*SELECT pai.action_information2 element_type_id
,pai.action_information3 input_value_id
,decode(pai1.action_information8,NULL,pai.action_information4,
pai.action_information4||'('||pai1.action_information8||')') Name
,pai.action_information5 type
,pai.action_information6 uom
--,pai1.action_information8 record_count
,sum(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'
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
group by pai.action_information2
,pai.action_information3
,pai.action_information4
,pai.action_information5
,pai.action_information6
,pai1.action_information8
ORDER BY pai.action_information5,pai1.action_information8 DESC;*/
SELECT pai.action_information2 element_type_id
,pai.action_information3 input_value_id
,SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1) Name
--,decode(pai1.action_information8,NULL,SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1),
--SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1) ||'('||pai1.action_information8||')') Name
,pai.action_information5 type
,pai.action_information6 uom
,SUBSTR(pai.action_information4,1,INSTR(pai.action_information4,',')-1) CODE
,pai1.action_information8 record_count
,substr(pai1.action_information9,instr(pai1.action_information9,':',-1)+1) unit_price
,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
ORDER BY pai.action_information2;
--insert into clobtable(a,ID) values (l_xml,'PPS');
SELECT eev1.screen_entry_value screen_entry_value
FROM per_all_assignments_F asg1
,per_all_assignments_F asg2
,per_all_people_F per
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_F ee
,pay_element_entry_values_F eev1
,pay_assignment_actions pac
,pay_payroll_actions ppa
WHERE per.person_id = asg1.person_id
AND ppa.BUSINESS_GROUP_ID = per.BUSINESS_GROUP_ID
and ppa.effective_date BETWEEN per.effective_start_date and per.effective_end_date
AND asg2.person_id = per.person_id
and ppa.BUSINESS_GROUP_ID = asg1.BUSINESS_GROUP_ID
and ppa.BUSINESS_GROUP_ID = asg2.BUSINESS_GROUP_ID
and ppa.effective_date BETWEEN asg1.effective_start_date and asg1.effective_end_date
and ppa.effective_date BETWEEN asg2.effective_start_date and asg2.effective_end_date
AND pac.assignment_action_id = p_asg_act_id
AND pac.payroll_action_id = ppa.payroll_action_id
AND asg1.assignment_id = pac.assignment_id
-- AND et.element_name = 'Absence Details'
and ppa.effective_date BETWEEN et.effective_start_date and et.effective_end_date
AND et.legislation_code = 'SE'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = 'Absence Category'
and ppa.effective_date BETWEEN iv1.effective_start_date and iv1.effective_end_date
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
and ppa.effective_date BETWEEN el.effective_start_date and el.effective_end_date
AND ee.assignment_id = asg2.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
and ee.element_entry_id =p_ee_id
AND eev1.input_value_id = iv1.input_value_id;
SELECT target.segment2
FROM
hr_soft_coding_keyflex target,
per_all_assignments_f ASSIGN,
fnd_id_flex_structures fstruct,
pay_legislation_rules leg
WHERE fstruct.id_flex_num = leg.rule_mode
AND fstruct.id_flex_code = 'SCL'
AND fstruct.application_id = 800
AND leg.legislation_code = 'SE'
AND fstruct.enabled_flag = 'Y'
AND leg.rule_type = 'S'
AND target.id_flex_num = fstruct.id_flex_num
AND ASSIGN.assignment_id = p_assignment_id
AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
AND p_effective_date BETWEEN ASSIGN.effective_start_date AND ASSIGN.effective_end_date
AND target.enabled_flag = 'Y';