[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
FROM per_person_types ppt
WHERE ppt.system_person_type IN ('EMP','APL')
AND ppt.person_type_Id = p_person_type_id;
SELECT 'Y'
FROM per_person_types ppt
WHERE ppt.system_person_type LIKE 'EMP%'
AND ppt.person_type_Id = p_person_type_id;
SELECT org_information1
FROM hr_organization_information hoi,
per_person_types pty
WHERE pty.person_type_id = p_person_type_id
AND pty.business_group_id = hoi.organization_id
AND hoi.org_information_context = 'AE_BG_DETAILS';
SELECT distinct pty.business_group_id
FROM per_person_types pty,
hr_organization_information hoi
WHERE pty.person_type_id = p_person_type_id
AND pty.business_group_id = hoi.organization_id;
SELECT 'Y'
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.business_group_id = l_bg_id AND i.legislation_code IS NULL))
AND ((r.legislation_code = 'AE' AND r.business_group_id IS NULL)
OR (r.business_group_id = l_bg_id AND r.legislation_code IS NULL))
AND c.legislation_code = 'AE'
AND t.legislation_code = 'AE'
AND UPPER(t.user_table_name) = UPPER('AE_MARITAL_STATUS')
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 = p_marital_status
AND r.user_row_id = i.user_row_id
AND UPPER(c.user_column_name) = UPPER('MARITAL STATUS')
AND c.user_column_id = i.user_column_id
--AND i.value = p_value
AND p_date BETWEEN r.effective_start_date AND r.effective_end_date
AND p_date BETWEEN i.effective_start_date AND i.effective_end_date;
SELECT person_type_id
FROM per_all_people_f
WHERE person_id = p_person_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT 'Y'
FROM per_person_types ppt
WHERE ppt.system_person_type IN ('CWK')
AND ppt.person_type_id = p_person_type_id;
SELECT 'Y'
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 = p_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 = p_business_group_id))
AND ((c.legislation_code = 'AE' and c.business_group_id is null) or (c.legislation_code is NULL and c.business_group_id = p_business_group_id))
AND ((t.legislation_code = 'AE' and t.business_group_id is null) or (t.legislation_code is NULL and t.business_group_id = p_business_group_id))
AND UPPER(t.user_table_name) = UPPER(p_user_table_name)
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 = p_row_low_name
AND r.user_row_id = i.user_row_id
AND UPPER(c.user_column_name) = UPPER(p_user_column_name)
AND c.user_column_id = i.user_column_id
AND i.value = p_value
AND p_effective_date BETWEEN r.effective_start_date AND r.effective_end_date
AND p_effective_date BETWEEN i.effective_start_date AND i.effective_end_date;
SELECT distinct paf.business_group_id
FROM per_all_people_f paf,
hr_organization_information hoi
WHERE paf.person_id = p_person_id
AND paf.business_group_id = hoi.organization_id;
PROCEDURE update_address_validate
(p_address_id IN NUMBER
,p_effective_date IN DATE
,p_address_line3 IN VARCHAR2
,p_town_or_city IN VARCHAR2
,p_region_1 IN VARCHAR2) IS
CURSOR csr_get_style(l_address_id number) is
SELECT style,person_id
FROM per_addresses
WHERE address_id = l_address_id;
SELECT distinct pad.business_group_id
FROM per_addresses pad,
hr_organization_information hoi
WHERE pad.ADDRESS_ID = p_address_id
AND pad.business_group_id = hoi.organization_id;
END update_address_validate;
PROCEDURE update_location_validate
(p_style IN VARCHAR2
,p_location_id IN NUMBER
,p_effective_date IN DATE
,p_address_line_3 IN VARCHAR2
,p_town_or_city IN VARCHAR2
,p_region_1 IN VARCHAR2) IS
--
CURSOR csr_fetch_bus_id IS
SELECT distinct paaf.business_group_id
FROM per_all_assignments_f paaf,
hr_locations_all hl,
hr_organization_information hoi
WHERE paaf.location_id = p_location_id
and paaf.location_id = hl.location_id
AND paaf.business_group_id = hoi.organization_id;
END update_location_validate;
PROCEDURE update_asg_validate
(p_effective_date IN DATE
,p_assignment_id IN NUMBER
,p_segment1 IN VARCHAR2
,p_segment2 IN VARCHAR2
,p_segment3 IN VARCHAR2
,p_segment4 IN VARCHAR2
,p_segment5 IN VARCHAR2) IS
BEGIN
/* Added for GSI Bug 5472781 */
IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
IF (p_segment2 IS NOT NULL AND p_segment2 <> hr_api.g_varchar2) AND p_segment3 IS NULL THEN
hr_utility.set_message(800, 'HR_377409_AE_SOC_SEC_REQ');
SELECT qual.person_id
FROM per_qualifications qual,
per_all_assignments_f asg
WHERE qual.person_id = asg.person_id
AND qual.qualification_id = p_segment5
AND asg.assignment_id = p_assignment_id;
END update_asg_validate ;
SELECT COUNT(*)
INTO l_count
FROM per_disabilities_f
WHERE person_id = p_person_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date
AND dis_information_category = 'AE'
AND dis_information1 = 'Y';
PROCEDURE UPDATE_DISABILITY_VALIDATE
(p_effective_date IN DATE
,p_disability_id IN NUMBER
,p_category IN VARCHAR2
,p_degree IN NUMBER DEFAULT NULL
,p_dis_information_category IN VARCHAR2 DEFAULT NULL
,p_dis_information1 IN VARCHAR2 DEFAULT NULL
,p_dis_information2 IN VARCHAR2 DEFAULT NULL) AS
l_person_id NUMBER;
SELECT person_id
INTO l_person_id
FROM per_disabilities_f
WHERE disability_id = p_disability_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT COUNT(*)
INTO l_count
FROM per_disabilities_f
WHERE person_id = l_person_id
AND disability_id <> p_disability_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date
AND dis_information_category = 'AE'
AND dis_information1 = 'Y';
END UPDATE_DISABILITY_VALIDATE;
SELECT count(*)
INTO l_count
FROM PAY_PERSONAL_PAYMENT_METHODS_F
WHERE ASSIGNMENT_ID = P_ASSIGNMENT_ID
/* AND ORG_PAYMENT_METHOD_ID = P_ORG_PAYMENT_METHOD_ID */
AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND PPM_INFORMATION_CATEGORY = 'AE_AE DIRECT DEPOSIT AED'
AND PPM_INFORMATION1 = 'Y';
PROCEDURE UPDATE_PAYMENT_METHOD_VALIDATE
(P_EFFECTIVE_DATE IN DATE
,P_PERSONAL_PAYMENT_METHOD_ID IN NUMBER
,P_PPM_INFORMATION1 IN VARCHAR2) IS
l_count number;
SELECT ASSIGNMENT_ID
INTO l_assignment_id
FROM PAY_PERSONAL_PAYMENT_METHODS_F
WHERE PERSONAL_PAYMENT_METHOD_ID = P_PERSONAL_PAYMENT_METHOD_ID
AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
SELECT COUNT(*)
INTO l_count
FROM PAY_PERSONAL_PAYMENT_METHODS_F
WHERE ASSIGNMENT_ID = l_assignment_id
AND PERSONAL_PAYMENT_METHOD_ID <> P_PERSONAL_PAYMENT_METHOD_ID
AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND PPM_INFORMATION_CATEGORY = 'AE_AE DIRECT DEPOSIT AED'
AND PPM_INFORMATION1 = 'Y';
END UPDATE_PAYMENT_METHOD_VALIDATE;
PROCEDURE VALIDATE_UPDATE_ORG_INF(
p_effective_date IN DATE
,p_org_information_id IN NUMBER
,p_org_info_type_code IN VARCHAR2
,p_org_information1 IN VARCHAR2 DEFAULT null
,p_org_information2 IN VARCHAR2 DEFAULT null
,p_org_information3 IN VARCHAR2 DEFAULT null
,p_org_information4 IN VARCHAR2 DEFAULT null
,p_org_information5 IN VARCHAR2 DEFAULT null
,p_org_information6 IN VARCHAR2 DEFAULT null
,p_org_information7 IN VARCHAR2 DEFAULT null
,p_org_information8 IN VARCHAR2 DEFAULT null
,p_org_information9 IN VARCHAR2 DEFAULT null
,p_org_information10 IN VARCHAR2 DEFAULT null
)
AS
return_value VARCHAR2(100);
END VALIDATE_UPDATE_ORG_INF;