[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select Org_Information1
Into l_nationality_cd
From HR_ORGANIZATION_INFORMATION
Where ORG_INFORMATION_CONTEXT = 'AE_BG_DETAILS'
And ORGANIZATION_ID = l_organization_id;
Select person_id
Into l_person_id
From PER_ALL_ASSIGNMENTS_F
Where ASSIGNMENT_ID = p_assignment_id
AND p_date_earned between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
Select per_information18
Into l_nationality_person
From PER_ALL_PEOPLE_F
Where PERSON_ID = l_person_id
AND p_date_earned between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
Select person_id
Into l_person_id
From PER_ALL_ASSIGNMENTS_F
Where ASSIGNMENT_ID = p_assignment_id
AND p_date_earned between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
Select PER_INFORMATION18
Into l_nationality_person
From PER_ALL_PEOPLE_F
Where PERSON_ID = l_person_id
AND p_date_earned between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
SELECT org_information6
FROM hr_organization_information
WHERE organization_id = l_tax_unit_id
AND org_information_context = 'AE_LEGAL_EMPLOYER_DETAILS';
SELECT user_table_id
FROM pay_user_tables
WHERE legislation_code='AE'
AND UPPER(user_table_name) = UPPER(l_table_name);
SELECT MIN(to_number(row_low_range_or_name))
FROM pay_user_rows_f
WHERE user_table_id = l_user_table_id
AND legislation_code = 'AE'
AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT MIN(to_number(row_high_range))
FROM pay_user_rows_f
WHERE user_table_id = l_user_table_id
AND legislation_code = 'AE'
AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT MAX(to_number(row_high_range))
FROM pay_user_rows_f
WHERE user_table_id = l_user_table_id
AND legislation_code = 'AE'
AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT count(DISTINCT paf.assignment_id)
FROM per_all_assignments_f paf
,per_assignment_status_types pas
WHERE paf.assignment_type = 'E'
AND paf.PERSON_ID = p_person_id
AND p_effective_date between effective_start_date and effective_end_date
AND paf.assignment_status_type_id = pas.assignment_status_type_id
AND pas.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
SELECT COUNT(*)
FROM per_assignments_f paf
,per_people_f pef
,pay_pre_payments ppp
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.payroll_action_id =
pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
AND paa.pre_payment_id = ppp.pre_payment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = paf.assignment_id
AND paf.person_id = pef.person_id
AND ppp.value <> 0
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.effective_date BETWEEN pef.effective_start_date
AND pef.effective_end_date;
SELECT SUM(ppp.value)
FROM per_assignments_f paf
,per_people_f pef
,pay_pre_payments ppp
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.payroll_action_id =
pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
AND paa.pre_payment_id = ppp.pre_payment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = paf.assignment_id
AND paf.person_id = pef.person_id
AND ppp.value <> 0
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.effective_date BETWEEN pef.effective_start_date
AND pef.effective_end_date;
SELECT SUM(ppp.value)
FROM per_assignments_f paf
,per_people_f pef
,pay_pre_payments ppp
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.payroll_action_id =
pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
AND paa.pre_payment_id = ppp.pre_payment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = paf.assignment_id
AND paf.person_id = pef.person_id
AND ppp.value > 0
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.effective_date BETWEEN pef.effective_start_date
AND pef.effective_end_date;
SELECT SUM(ppp.value)
FROM per_assignments_f paf
,per_people_f pef
,pay_pre_payments ppp
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.payroll_action_id =
pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
AND paa.pre_payment_id = ppp.pre_payment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = paf.assignment_id
AND paf.person_id = pef.person_id
AND ppp.value < 0
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.effective_date BETWEEN pef.effective_start_date
AND pef.effective_end_date;
SELECT cont.type
INTO l_contract
FROM per_contracts_f cont
,per_all_assignments_f asg
WHERE asg.assignment_id = p_assignment_id
AND asg.contract_id = cont.contract_id
AND p_date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_date_earned BETWEEN cont.effective_start_date AND cont.effective_end_date;
SELECT DATE_PROBATION_END
INTO l_date_probation_end
FROM per_all_assignments_f asg
WHERE asg.assignment_id = p_assignment_id
AND p_date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT cont.type, fnd_date.canonical_to_date(cont.ctr_information2)
INTO l_contract, l_expiry_date
FROM per_contracts_f cont
,per_all_assignments_f asg
WHERE asg.assignment_id = p_assignment_id
AND asg.contract_id = cont.contract_id
AND p_date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_date_earned BETWEEN cont.effective_start_date AND cont.effective_end_date;
SELECT pos.leaving_reason
INTO l_leav_reason
FROM per_all_assignments_f assign
,per_periods_of_service pos
WHERE p_date_earned BETWEEN assign.effective_start_date AND assign.effective_end_date
AND assign.assignment_id = p_assignment_id
AND assign.period_of_service_id = pos.period_of_service_id;
SELECT NVL(i.value,'EE')
INTO l_initiator
FROM pay_user_column_instances_f i
,pay_user_rows_f r
,pay_user_columns c
,pay_user_tables t
WHERE ((i.legislation_code = 'AE' AND i.business_group_id IS NULL) OR
(i.legislation_code IS NULL AND i.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')))
AND ((r.legislation_code = 'AE' AND r.business_group_id IS NULL) OR
(r.legislation_code IS NULL AND r.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')))
AND c.legislation_code = 'AE'
AND t.legislation_code = 'AE'
AND UPPER(t.user_table_name) = UPPER('AE_TERMINATION_INITIATOR')
AND t.user_table_id = r.user_table_id
AND t.user_table_id = c.user_table_id
AND r.row_low_range_or_name = l_leav_reason
AND r.user_row_id = i.user_row_id
AND UPPER(c.user_column_name) = UPPER('INITIATOR')
AND c.user_column_id = i.user_column_id
AND p_date_earned BETWEEN r.effective_start_date AND r.effective_end_date
AND p_date_earned BETWEEN i.effective_start_date AND i.effective_end_date;
select HOI2.org_information1
from hr_organization_units HOU
,hr_organization_information HOI1
,hr_organization_information HOI2
,hr_soft_coding_keyflex HSCK
,per_all_assignments_f PAA
where HOU.business_group_id = PAA.business_group_id
and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
to_date('4712/12/31','YYYY/MM/DD'))
and HOU.organization_id = HOI1.organization_id
and HOI1.org_information_context = 'CLASS'
and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
and HOI1.organization_id = HOI2.organization_id
and PAA.assignment_id = p_assignment_id
and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
/*and HSCK.id_flex_num = 20
and decode(HSCK.id_flex_num,20,to_number(HSCK.segment1),-9999) = HOU.organization_id*/
and hsck.segment1 = hou.organization_id
and HOI2.org_information_context = 'AE_GRATUITY_REF_FORMULA';
select HOI2.org_information2
from hr_organization_units HOU
,hr_organization_information HOI1
,hr_organization_information HOI2
,hr_soft_coding_keyflex HSCK
,per_all_assignments_f PAA
where HOU.business_group_id = PAA.business_group_id
and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
to_date('4712/12/31','YYYY/MM/DD'))
and HOU.organization_id = HOI1.organization_id
and HOI1.org_information_context = 'CLASS'
and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
and HOI1.organization_id = HOI2.organization_id
and PAA.assignment_id = p_assignment_id
and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
/*and HSCK.id_flex_num = 20
and decode(HSCK.id_flex_num,20,to_number(HSCK.segment1),-9999) = HOU.organization_id*/
and hsck.segment1 = hou.organization_id
and HOI2.org_information_context = 'AE_REFERENCE_FF';
select HOI2.org_information3
from hr_organization_units HOU
,hr_organization_information HOI1
,hr_organization_information HOI2
,hr_soft_coding_keyflex HSCK
,per_all_assignments_f PAA
where HOU.business_group_id = PAA.business_group_id
and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
to_date('4712/12/31','YYYY/MM/DD'))
and HOU.organization_id = HOI1.organization_id
and HOI1.org_information_context = 'CLASS'
and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
and HOI1.organization_id = HOI2.organization_id
and PAA.assignment_id = p_assignment_id
and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
/*and HSCK.id_flex_num = 20
and decode(HSCK.id_flex_num,20,to_number(HSCK.segment1),-9999) = HOU.organization_id*/
and hsck.segment1 = hou.organization_id
and HOI2.org_information_context = 'AE_REFERENCE_FF';
SELECT SUM(paa.absence_days) --(NVL(paa.absence_days, (paa.DATE_END - paa.DATE_START))
INTO l_days
FROM per_absence_attendances paa
,per_absence_attendance_types paat
,per_all_assignments_f asg
WHERE paat.absence_category ='UL'
AND paat.business_group_id = paa.business_group_id
AND paat.business_group_id = p_business_group_id
AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
AND paa.person_id = asg.person_id
AND asg.assignment_id = p_assignment_id
AND TRUNC(p_date_earned) BETWEEN asg.effective_start_date AND asg.effective_end_date
AND TRUNC(p_date_earned) >= TRUNC(paa.date_end,'MM') ;
select NVL(HOI2.org_information1,'X')
from hr_organization_units HOU
,hr_organization_information HOI1
,hr_organization_information HOI2
,hr_soft_coding_keyflex HSCK
,per_all_assignments_f PAA
where HOU.business_group_id = PAA.business_group_id
and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
to_date('4712/12/31','YYYY/MM/DD'))
and HOU.organization_id = HOI1.organization_id
and HOI1.org_information_context = 'CLASS'
and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
and HOI1.organization_id = HOI2.organization_id
and PAA.assignment_id = p_assignment_id
and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
and hsck.segment1 = hou.organization_id
and HOI2.org_information_context = 'AE_GRATUITY_DETAILS';
SELECT u.creator_id
FROM ff_user_entities u,
ff_database_items d
WHERE d.user_name = p_def_bal_name
AND u.user_entity_id = d.user_entity_id
AND u.legislation_code = 'AE'
AND u.business_group_id is null
AND u.creator_type = 'B';
SELECT paa.assignment_action_id
,ppa.date_earned
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
,pay_element_types_f pet
WHERE paa.assignment_id = p_assignment_id
AND paa.assignment_action_id = prr.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND ppa.action_status = 'C'
AND ppa.action_type in ('R','Q')
AND prr.element_type_id = pet.element_type_id
AND pet.element_name ='GOSI'
AND p_date_earned between pet.effective_start_date and pet.effective_end_date
AND prr.status = 'P'
AND paa.assignment_action_id < p_assignment_action_id
--AND ppa.date_earned >= ADD_MONTHS(TRUNC(p_date_earned,'YYYY'),-12)
ORDER BY paa.assignment_action_id DESC;