[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 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.ORG_INFORMATION_CONTEXT='FI_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_f pet,
pay_element_entries_f 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 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 = 'FI'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = 'Magistrate 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 pap.per_information9
FROM
pay_assignment_actions pac,
per_all_assignments assign,
per_all_people pap
WHERE pac.assignment_action_id = p_asg_act_id
AND assign.assignment_id = pac.assignment_id
AND assign.person_id = pap.person_id
AND pap.per_information_category = 'FI';
SELECT target.segment2 , ASSIGN.business_group_id
FROM
hr_soft_coding_keyflex target,
per_all_assignments ASSIGN,
fnd_id_flex_structures fstruct,
pay_legislation_rules leg,
pay_assignment_actions pac
WHERE fstruct.id_flex_num = leg.rule_mode
AND fstruct.id_flex_code = 'SCL'
AND fstruct.application_id = 800
AND leg.legislation_code = 'FI'
AND fstruct.enabled_flag = 'Y'
AND leg.rule_type = 'S'
AND target.id_flex_num = fstruct.id_flex_num
AND ASSIGN.assignment_id = pac.assignment_id
AND pac.assignment_action_id = P_ASG_ACT_ID
AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
AND target.enabled_flag = 'Y';
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 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNITS'
AND hoi2.organization_id = hoi3.organization_id
AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER' ;
SELECT hoi2.org_information3
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = p_tax_unit_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_ACCIDENT_PROVIDERS'
AND p_effective_date between fnd_date.canonical_to_date(hoi2.org_information1) AND
nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY')) ;
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 PER_INFORMATION24
FROM per_all_assignments_f asg1
,per_all_people_f per
,pay_assignment_actions pac
WHERE per.person_id = asg1.person_id
AND pac.assignment_action_id = p_asg_act_id
AND asg1.assignment_id = pac.assignment_id
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date ;
SELECT hoi2.org_information4
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = p_tax_unit_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_PROVIDERS'
AND hoi2.org_information6 = p_pension_num
AND p_effective_date between fnd_date.canonical_to_date(hoi2.org_information1) AND
nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY'))
AND hoi2.org_information6 IN
(
SELECT NVL(hoi2.org_information1,0 )
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id = p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = l_local_unit_id
AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.org_information1 = p_pension_num
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LU_PENSION_PROVIDERS' );
SELECT nvl(target.segment8, '1')
FROM
hr_soft_coding_keyflex target,
per_all_assignments ASSIGN,
fnd_id_flex_structures fstruct,
pay_legislation_rules leg,
pay_assignment_actions pac
WHERE fstruct.id_flex_num = leg.rule_mode
AND fstruct.id_flex_code = 'SCL'
AND fstruct.application_id = 800
AND leg.legislation_code = 'FI'
AND fstruct.enabled_flag = 'Y'
AND leg.rule_type = 'S'
AND target.id_flex_num = fstruct.id_flex_num
AND ASSIGN.assignment_id = pac.assignment_id
AND pac.assignment_action_id = P_ASG_ACT_ID
AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
AND target.enabled_flag = 'Y';
SELECT eev1.screen_entry_value screen_entry_value
FROM per_all_assignments_f asg1
,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 pac.assignment_action_id = P_ASG_ACT_ID
AND ppa.payroll_action_id = pac.payroll_action_id
AND asg1.assignment_id = pac.assignment_id
AND ppa.effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND et.element_name = 'Tax'
AND et.legislation_code = 'FI'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = 'Primary Employment'
AND el.business_group_id = asg1.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg1.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND ppa.effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND ppa.effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
SELECT nvl(target.segment13,'N')
FROM
hr_soft_coding_keyflex target,
per_all_assignments ASSIGN,
fnd_id_flex_structures fstruct,
pay_legislation_rules leg,
pay_assignment_actions pac
WHERE fstruct.id_flex_num = leg.rule_mode
AND fstruct.id_flex_code = 'SCL'
AND fstruct.application_id = 800
AND leg.legislation_code = 'FI'
AND fstruct.enabled_flag = 'Y'
AND leg.rule_type = 'S'
AND target.id_flex_num = fstruct.id_flex_num
AND ASSIGN.assignment_id = pac.assignment_id
AND pac.assignment_action_id = P_ASG_ACT_ID
AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
AND target.enabled_flag = 'Y';
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 = 'FI'
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';
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
UNION
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
,pay_assignment_actions paa
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 = paa.source_action_id
AND paa.assignment_action_id = p_action_context_id
AND paa.assignment_id = pai.assignment_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
-- ,pai1.action_information4 ss_days
FROM per_time_periods ptp
,pay_payrolls_f ppf
,pay_action_information pai
-- ,pay_action_information pai1
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 pai1.action_context_type = 'AAP'
AND pai.action_information_category = p_category1
-- AND pai1.action_information_category = p_category2
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
,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(fnd_number.canonical_to_number(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
,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_information4
,pai1.action_information8
ORDER BY pai.action_information5,pai1.action_information8 DESC;