[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT psn.email_address
,DECODE(psn.email_address,NULL,-1,p_assignment_id)
,DECODE(psn.person_id,NULL,-1,p_assignment_id)
,psn.person_id
,psn.full_name
,psn.correspondence_language
,psn.business_group_id
FROM per_all_people_f psn
,per_all_assignments_f asg
,per_all_assignments_f asg2
,per_assignment_status_types ast
,per_assignment_status_types ast2
WHERE asg.assignment_id = cp_assignment_id
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND asg.person_id = asg2.person_id
AND asg2.primary_flag = 'Y'
AND asg2.supervisor_id = psn.person_id
AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
/* Ensures only current primary assignment is used */
AND ((asg.effective_start_date
BETWEEN asg2.effective_start_date
AND asg2.effective_end_date ) OR
(asg2.effective_start_date
BETWEEN asg.effective_start_date
AND asg.effective_end_date))
AND ((psn.effective_start_date
BETWEEN asg2.effective_start_date
AND asg2.effective_end_date ) OR
(asg2.effective_start_date
BETWEEN psn.effective_start_date
AND psn.effective_end_date))
/* Make sure that the Recipient is a current Worker */
AND ((psn.current_employee_flag = 'Y') OR
(psn.current_npw_flag = 'Y'))
AND (
(TRUNC(SYSDATE) BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND
TRUNC(SYSDATE) BETWEEN psn.effective_start_date
AND psn.effective_end_date)
OR
(
(NOT EXISTS (SELECT 'X'
FROM per_all_assignments_f asg2
WHERE asg2.assignment_id = asg.assignment_id
AND ((asg2.assignment_type = 'E') OR
(asg2.assignment_type = 'C'))
AND TRUNC(SYSDATE) BETWEEN asg2.effective_start_date
AND asg2.effective_end_date))
AND
(asg.effective_start_date IN
(
SELECT MIN(asg3.effective_start_date)
FROM per_all_assignments_f asg3
WHERE asg3.assignment_id = asg.assignment_id
AND ((asg3.assignment_type = 'E') OR
(asg3.assignment_type = 'C'))
AND asg3.effective_start_date > TRUNC(SYSDATE)
)
)
)
)
/* Return active assignment status types only */
AND ast.per_system_status IN ('ACCEPTED','ACTIVE_APL'
,'ACTIVE_ASSIGN','ACTIVE_CWK','END'
,'INTERVIEW1','INTERVIEW2'
,'OFFER','SUSP_ASSIGN','SUSP_CWK_ASG')
AND ast2.per_system_status IN ('ACCEPTED','ACTIVE_APL'
,'ACTIVE_ASSIGN','ACTIVE_CWK','END'
,'INTERVIEW1','INTERVIEW2'
,'OFFER','SUSP_ASSIGN','SUSP_CWK_ASG')
;
SELECT psn.email_address
,DECODE(psn.email_address,NULL,-1,p_assignment_id)
,DECODE(psn.person_id,NULL,-1,p_assignment_id)
,psn.person_id
,psn.full_name
,psn.correspondence_language
,psn.business_group_id
FROM per_all_people_f psn
,per_all_assignments_f asg
,per_assignment_status_types ast
WHERE asg.assignment_id = cp_assignment_id
AND asg.supervisor_id = psn.person_id
AND asg.assignment_status_type_id = ast.assignment_status_type_id
/* Ensures only current person and assignment used */
AND ((psn.effective_start_date
BETWEEN asg.effective_start_date
AND asg.effective_end_date ) OR
(asg.effective_start_date
BETWEEN psn.effective_start_date
AND psn.effective_end_date))
/* Make sure that the Recipient is a current Worker */
AND ((psn.current_employee_flag = 'Y') OR
(psn.current_npw_flag = 'Y'))
AND (
(TRUNC(SYSDATE) BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND
TRUNC(SYSDATE) BETWEEN psn.effective_start_date
AND psn.effective_end_date)
OR
(
(NOT EXISTS (SELECT 'X'
FROM per_all_assignments_f asg2
WHERE asg2.assignment_id = asg.assignment_id
AND ((asg2.assignment_type = 'E') OR
(asg2.assignment_type = 'C'))
AND TRUNC(SYSDATE) BETWEEN asg2.effective_start_date
AND asg2.effective_end_date))
AND
(asg.effective_start_date IN
(
SELECT MIN(asg3.effective_start_date)
FROM per_all_assignments_f asg3
WHERE asg3.assignment_id = asg.assignment_id
AND ((asg3.assignment_type = 'E') OR
(asg3.assignment_type = 'C'))
AND asg3.effective_start_date > TRUNC(SYSDATE)
)
)
)
)
AND ast.per_system_status IN ('ACCEPTED','ACTIVE_APL'
,'ACTIVE_ASSIGN','ACTIVE_CWK','END'
,'INTERVIEW1','INTERVIEW2'
,'OFFER','SUSP_ASSIGN','SUSP_CWK_ASG') ;
SELECT psn.email_address
,DECODE(psn.email_address,NULL,-1,p_assignment_id)
,psn.person_id
,psn.full_name
,psn.correspondence_language
,psn.business_group_id
FROM per_all_people_f psn
,per_all_assignments_f asg
,per_assignment_status_types ast
WHERE asg.assignment_id = cp_assignment_id
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND asg.person_id = psn.person_id
/* Ensures only current person and assignment used */
AND ((psn.effective_start_date
BETWEEN asg.effective_start_date
AND asg.effective_end_date ) OR
(asg.effective_start_date
BETWEEN psn.effective_start_date
AND psn.effective_end_date))
/* Make sure that the Recipient is a current Worker */
AND ((psn.current_employee_flag = 'Y') OR
(psn.current_npw_flag = 'Y'))
AND (
(TRUNC(SYSDATE) BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND
TRUNC(SYSDATE) BETWEEN psn.effective_start_date
AND psn.effective_end_date)
OR
(
(NOT EXISTS (SELECT 'X'
FROM per_all_assignments_f asg2
WHERE asg2.assignment_id = asg.assignment_id
AND ((asg2.assignment_type = 'E') OR
(asg2.assignment_type = 'C'))
AND TRUNC(SYSDATE) BETWEEN asg2.effective_start_date
AND asg2.effective_end_date))
AND
(asg.effective_start_date IN
(
SELECT MIN(asg3.effective_start_date)
FROM per_all_assignments_f asg3
WHERE asg3.assignment_id = asg.assignment_id
AND ((asg3.assignment_type = 'E') OR
(asg3.assignment_type = 'C'))
AND asg3.effective_start_date > TRUNC(SYSDATE)
)
)
)
)
AND ast.per_system_status IN ('ACCEPTED','ACTIVE_APL'
,'ACTIVE_ASSIGN','ACTIVE_CWK','END'
,'INTERVIEW1','INTERVIEW2'
,'OFFER','SUSP_ASSIGN','SUSP_CWK_ASG') ;
SELECT email_address
,DECODE(email_address,NULL,-1,p_person_id)
,full_name
,correspondence_language
,business_group_id
FROM per_all_people_f psn
WHERE person_id = cp_person_id
AND ((psn.current_employee_flag = 'Y') OR
(psn.current_npw_flag = 'Y'))
AND (
(TRUNC(SYSDATE) BETWEEN psn.effective_start_date
AND psn.effective_end_date)
OR
(
(NOT EXISTS (SELECT 'X'
FROM per_all_people_f psn2
WHERE psn2.person_id = psn.person_id
AND ((psn2.current_employee_flag = 'Y') OR
(psn2.current_npw_flag = 'Y'))
AND TRUNC(SYSDATE) BETWEEN psn2.effective_start_date
AND psn2.effective_end_date))
AND
(psn.effective_start_date IN
(
SELECT MIN(psn3.effective_start_date)
FROM per_all_people_f psn3
WHERE psn3.person_id = psn.person_id
AND ((psn3.current_employee_flag = 'Y') OR
(psn3.current_npw_flag = 'Y'))
AND psn3.effective_start_date > TRUNC(SYSDATE)
)
)
)
);
SELECT email_address
,DECODE(email_address,NULL,-1,p_person_id)
,full_name
,correspondence_language
,business_group_id
FROM per_all_people_f psn
WHERE person_id = cp_person_id
--AND ((psn.current_employee_flag = 'Y') OR
-- (psn.current_npw_flag = 'Y'))
AND (
(TRUNC(SYSDATE) BETWEEN psn.effective_start_date
AND psn.effective_end_date)
OR
(
(NOT EXISTS (SELECT 'X'
FROM per_all_people_f psn2
WHERE psn2.person_id = psn.person_id
AND ((psn2.current_employee_flag = 'Y') OR
(psn2.current_npw_flag = 'Y'))
AND TRUNC(SYSDATE) BETWEEN psn2.effective_start_date
AND psn2.effective_end_date))
AND
(psn.effective_start_date IN
(
SELECT MIN(psn3.effective_start_date)
FROM per_all_people_f psn3
WHERE psn3.person_id = psn.person_id
AND ((psn3.current_employee_flag = 'Y') OR
(psn3.current_npw_flag = 'Y'))
AND psn3.effective_start_date > TRUNC(SYSDATE)
)
)
)
);
SELECT psn.email_address
,DECODE(psn.email_address,NULL,-1,p_person_id)
,psn.person_id
,psn.full_name
,psn.correspondence_language
,psn.business_group_id
FROM per_all_people_f psn
,per_all_assignments_f asg
WHERE asg.person_id = cp_person_id
AND asg.primary_flag = 'Y'
AND asg.supervisor_id = psn.person_id
/* Ensures only current person and assignment used */
AND ((psn.effective_start_date
BETWEEN asg.effective_start_date
AND asg.effective_end_date ) OR
(asg.effective_start_date
BETWEEN psn.effective_start_date
AND psn.effective_end_date))
/* Make sure that the Recipient is a current Worker */
AND ((psn.current_employee_flag = 'Y') OR
(psn.current_npw_flag = 'Y'))
AND (
(TRUNC(SYSDATE) BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND
TRUNC(SYSDATE) BETWEEN psn.effective_start_date
AND psn.effective_end_date)
OR
(
(NOT EXISTS (SELECT 'X'
FROM per_all_assignments_f asg2
WHERE asg2.assignment_id = asg.assignment_id
AND ((asg2.assignment_type = 'E') OR
(asg2.assignment_type = 'C'))
AND TRUNC(SYSDATE) BETWEEN asg2.effective_start_date
AND asg2.effective_end_date))
AND
(asg.effective_start_date IN
(
SELECT MIN(asg3.effective_start_date)
FROM per_all_assignments_f asg3
WHERE asg3.assignment_id = asg.assignment_id
AND ((asg3.assignment_type = 'E') OR
(asg3.assignment_type = 'C'))
AND asg3.effective_start_date > TRUNC(SYSDATE)
)
)
)
);
SELECT org_information9 bg_lang
FROM hr_organization_information
WHERE org_information_context = 'Business Group Information'
AND organization_id = cp_business_group_id;