[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 = 'NO_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNITS'
AND hoi2.organization_id = hoi3.organization_id
AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER' ;
SELECT distinct eev.screen_entry_value Tax_Municipality
FROM pay_element_entries_f pee
,pay_element_entry_values_f eev
,pay_input_values_f piv
,pay_element_types_f pet
,per_all_assignments_f paaf
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND pee.element_entry_id = eev.element_entry_id
AND eev.input_value_id + 0 = piv.input_value_id
AND piv.name = 'Tax Municipality'
AND piv.element_type_id = pet.element_type_id
AND ppa.effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND pee.assignment_id = paa.assignment_id
AND pet.element_name = 'Tax Card'
AND pet.legislation_code = 'NO'
AND ppa.effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND ppa.effective_date BETWEEN eev.effective_start_date
AND eev.effective_end_date
AND ppa.effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND ppa.effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT pet.element_name
FROM pay_element_types pet
, pay_element_entries_f 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
,fnd_sessions fs
,pay_input_values_f_tl ivtl
WHERE per.person_id = asg1.person_id
AND asg2.person_id = per.person_id
AND asg2.primary_flag = 'Y'
AND et.element_name = 'Wage Attachment Support Order'
AND et.legislation_code = 'NO'
AND iv1.element_type_id = et.element_type_id
-- BUG fix 4777716
/*start-conditions added for performance tuning*/
AND fs.session_id = USERENV('sessionid')
AND fs.effective_date BETWEEN per.effective_start_date AND per.effective_end_date
AND fs.effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND fs.effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
AND fs.effective_date BETWEEN el.effective_start_date AND el.effective_end_date
AND fs.effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND iv1.input_value_id = ivtl.input_value_id
AND ivtl.language = USERENV('LANG')
AND fs.effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
AND fs.effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
/*End-conditions added for performance tuning*/
-- Modified for bug fix 4372257
AND iv1.name = 'Third Party Payee'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
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 eev1.input_value_id = iv1.input_value_id
AND pac.assignment_action_id = p_asg_act_id
AND asg1.assignment_id = pac.assignment_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
,fnd_sessions fs
,pay_input_values_f_tl ivtl
WHERE per.person_id = asg1.person_id
AND asg2.person_id = per.person_id
AND asg2.primary_flag = 'Y'
AND et.element_name = 'Wage Attachment Tax Levy'
AND et.legislation_code = 'NO'
AND iv1.element_type_id = et.element_type_id
-- BUG fix 4777716
/*start-conditions added for performance tuning*/
AND fs.session_id = USERENV('sessionid')
AND fs.effective_date BETWEEN per.effective_start_date AND per.effective_end_date
AND fs.effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND fs.effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
AND fs.effective_date BETWEEN el.effective_start_date AND el.effective_end_date
AND fs.effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND iv1.input_value_id = ivtl.input_value_id
AND ivtl.language = USERENV('LANG')
AND fs.effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
AND fs.effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
/*End-conditions added for performance tuning*/
-- Modified for bug fix 4372257
AND iv1.name = 'Third Party Payee'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
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 eev1.input_value_id = iv1.input_value_id
AND pac.assignment_action_id = p_asg_act_id
AND asg1.assignment_id = pac.assignment_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
,fnd_sessions fs
,pay_input_values_f_tl ivtl
WHERE per.person_id = asg1.person_id
AND asg2.person_id = per.person_id
AND asg2.primary_flag = 'Y'
AND et.element_name = 'Union Dues'
AND et.legislation_code = 'NO'
AND iv1.element_type_id = et.element_type_id
-- BUG fix 4777716
/*start-conditions added for performance tuning*/
AND fs.session_id = USERENV('sessionid')
AND fs.effective_date BETWEEN per.effective_start_date AND per.effective_end_date
AND fs.effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND fs.effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
AND fs.effective_date BETWEEN el.effective_start_date AND el.effective_end_date
AND fs.effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND iv1.input_value_id = ivtl.input_value_id
AND ivtl.language = USERENV('LANG')
AND fs.effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
AND fs.effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
/*End-conditions added for performance tuning*/
AND iv1.name = 'Third Party Payee'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
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 eev1.input_value_id = iv1.input_value_id
AND pac.assignment_action_id = p_asg_act_id
AND asg1.assignment_id = pac.assignment_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
,fnd_sessions fs
,pay_input_values_f_tl ivtl
WHERE per.person_id = asg1.person_id
AND asg2.person_id = per.person_id
AND asg2.primary_flag = 'Y'
AND et.element_name = p_element_name
AND et.legislation_code = 'NO'
AND iv1.element_type_id = et.element_type_id
AND fs.session_id = USERENV('sessionid')
AND fs.effective_date BETWEEN per.effective_start_date AND per.effective_end_date
AND fs.effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND fs.effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
AND fs.effective_date BETWEEN el.effective_start_date AND el.effective_end_date
AND fs.effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND iv1.input_value_id = ivtl.input_value_id
AND ivtl.language = USERENV('LANG')
AND fs.effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
AND fs.effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
AND iv1.name = 'Third Party Payee'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
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 eev1.input_value_id = iv1.input_value_id
AND pac.assignment_action_id = p_asg_act_id
AND asg1.assignment_id = pac.assignment_id;
select scl.segment2
from hr_soft_coding_keyflex scl,
pay_assignment_actions pac,
per_all_assignments_f ASSIGN,
pay_legislation_rules LEG,
fnd_id_flex_structures fstruct
Where pac.assignment_action_id = p_assignment_action_id
and pac.assignment_id = ASSIGN.assignment_id
and ASSIGN.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and LEG.rule_type = 'S'
and LEG.rule_mode = scl.id_flex_num
and scl.enabled_flag = 'Y'
and LEG.legislation_code = 'NO'
and fstruct.id_flex_num = leg.rule_mode
AND fstruct.id_flex_code = 'SCL'
AND fstruct.application_id = 800
AND fstruct.enabled_flag = 'Y';
select scl.segment2
from hr_soft_coding_keyflex scl,
per_all_assignments_f ASSIGN,
pay_legislation_rules LEG,
fnd_id_flex_structures fstruct
Where ASSIGN.assignment_id = p_assignment_id
and p_effective_date between ASSIGN.effective_start_date and ASSIGN.effective_end_date
and ASSIGN.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and LEG.rule_type = 'S'
and LEG.rule_mode = scl.id_flex_num
and scl.enabled_flag = 'Y'
and LEG.legislation_code = 'NO'
and fstruct.id_flex_num = leg.rule_mode
AND fstruct.id_flex_code = 'SCL'
AND fstruct.application_id = 800
AND fstruct.enabled_flag = 'Y';
SELECT distinct eev.screen_entry_value Tax_Municipality
FROM pay_element_entries_f pee
,pay_element_entry_values_f eev
,pay_input_values_f piv
,pay_element_types_f pet
,per_all_assignments_f paaf
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND pee.element_entry_id = eev.element_entry_id
AND eev.input_value_id + 0 = piv.input_value_id
AND piv.name = 'Tax Municipality'
AND piv.element_type_id = pet.element_type_id
AND ppa.effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND pee.assignment_id = paa.assignment_id
AND pet.element_name = 'Tax Card'
AND pet.legislation_code = 'NO'
AND ppa.effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND ppa.effective_date BETWEEN eev.effective_start_date
AND eev.effective_end_date
AND ppa.effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND ppa.effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT asg2.assignment_id
,assact.payroll_action_id
FROM per_all_assignments_f asg1
,per_all_assignments_f asg2
,pay_assignment_actions assact
,per_all_people_f pap
,pay_payroll_actions ppa
WHERE assact.assignment_action_id = p_assignment_action_id
AND asg1.assignment_id = assact.assignment_id
AND pap.person_id = asg1.person_id
AND asg2.person_id = pap.person_id
AND asg2.primary_flag = 'Y'
AND ppa.payroll_action_id = assact.payroll_action_id
AND ppa.effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND ppa.effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
AND ppa.effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date;
SELECT distinct eev.screen_entry_value Tax_Municipality
FROM pay_element_entries_f pee
,pay_element_entry_values_f eev
,pay_input_values_f piv
,pay_element_types_f pet
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pay_act_id
AND pee.element_entry_id = eev.element_entry_id
AND eev.input_value_id + 0 = piv.input_value_id
AND piv.name = 'Tax Municipality'
AND piv.element_type_id = pet.element_type_id
AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND pee.assignment_id = prim_asg_id
AND pet.element_name = 'Tax Card'
AND pet.legislation_code = 'NO'
AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND ppa.effective_date BETWEEN eev.effective_start_date AND eev.effective_end_date
AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
SELECT ppa.effective_date , ppa.payroll_action_id , assact.assignment_id
FROM pay_assignment_actions assact
,pay_payroll_actions ppa
WHERE assact.assignment_action_id = p_assignment_action_id
AND ppa.payroll_action_id = assact.payroll_action_id ;
SELECT ORG_INFORMATION6 lu_tax_mun
FROM pay_assignment_actions assact ,
per_all_assignments_f paa ,
pay_payroll_actions ppa ,
hr_soft_coding_keyflex scl ,
hr_organization_information hoi
WHERE assact.assignment_action_id = p_assignment_action_id
AND ppa.payroll_action_id = assact.payroll_action_id
AND paa.assignment_id = assact.assignment_id
AND ppa.effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date
AND paa.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND hoi.organization_id = scl.segment2
AND hoi.org_information_context = 'NO_LOCAL_UNIT_DETAILS' ;
SELECT distinct eev.screen_entry_value Tax_Municipality
FROM pay_element_entries_f pee
,pay_element_entry_values_f eev
,pay_input_values_f piv
,pay_element_types_f pet
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pay_act_id
AND pee.assignment_id = p_asg_id
AND pet.element_name = 'Employer Contribution Information'
AND pet.legislation_code = 'NO'
AND piv.name = 'Tax Municipality'
AND pee.element_entry_id = eev.element_entry_id
AND eev.input_value_id + 0 = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND ppa.effective_date BETWEEN eev.effective_start_date AND eev.effective_end_date
AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date ;
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
,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_information8
,pai1.action_information4
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
,pai1.action_information13 element_code
,pai1.action_information14 payslip_info
,pai1.action_information12 bal_val_ytd
,pai.action_information10 hol_basis_text
,pai.action_information11 tax_basis_text
,pai.action_information12 ele_class
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
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;
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
,pai1.action_information13 element_code
,pai1.action_information14 payslip_info
,pai1.action_information12 bal_val_ytd
,pai.action_information10 hol_basis_text
,pai.action_information11 tax_basis_text
,pai.action_information12 ele_class
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
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 row_low_range_or_name
from pay_user_tables put,
pay_user_rows_f pur,
fnd_sessions fs
where
put.user_table_name ='NO_SSB_CODE_RULES'
and put.user_table_id = pur.user_table_id
and fs.session_id = userenv('sessionid')
and fs.effective_date between pur.effective_start_date
and pur.effective_end_date
order by row_low_range_or_name;
SELECT asg2.assignment_id
,assact.payroll_action_id
FROM per_all_assignments_f asg1
,per_all_assignments_f asg2
,pay_assignment_actions assact
,per_all_people_f pap
,pay_payroll_actions ppa
WHERE assact.assignment_action_id = p_assignment_action_id
AND asg1.assignment_id = assact.assignment_id
AND pap.person_id = asg1.person_id
AND asg2.person_id = pap.person_id
AND asg2.primary_flag = 'Y'
AND ppa.payroll_action_id = assact.payroll_action_id
AND ppa.effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND ppa.effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
AND ppa.effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date;
SELECT distinct eev.screen_entry_value Tax_Municipality
FROM pay_element_entries_f pee
,pay_element_entry_values_f eev
,pay_input_values_f piv
,pay_element_types_f pet
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pay_act_id
AND pee.element_entry_id = eev.element_entry_id
AND eev.input_value_id + 0 = piv.input_value_id
AND piv.name = 'Tax Municipality'
AND piv.element_type_id = pet.element_type_id
AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND pee.assignment_id = prim_asg_id
AND pet.element_name = 'Tax Card'
AND pet.legislation_code = 'NO'
AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND ppa.effective_date BETWEEN eev.effective_start_date AND eev.effective_end_date
AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
SELECT org_information1 --Local Unit ID flag
INTO l_sort_flag
FROM hr_organization_information
WHERE organization_id = to_number(l_bg_id)
AND org_information_context = 'NO_PAYSLIP_SORT_DETAILS'
AND rownum <= 1;
SELECT org_information2 --Org ID flag
INTO l_sort_flag
FROM hr_organization_information
WHERE organization_id = to_number(l_bg_id)
AND org_information_context = 'NO_PAYSLIP_SORT_DETAILS'
AND rownum <= 1;