[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
into l_no_of_candidates
FROM pa_candidates
where assignment_id = g_assignment_id
AND status_code = p_project_status_code;
SELECT count(*)
into l_no_of_candidates
FROM pa_candidates
where assignment_id = g_assignment_id
and status_code in ('UNDER_REVIEW','SUITABLE');
SELECT 'Y'
into l_exists
FROM pa_candidates
WHERE resource_id=p_resource_id
AND assignment_id = p_assignment_id;
SELECT 'Y'
INTO l_exists
FROM pa_candidates pc,
pa_project_statuses pps
WHERE pc.resource_id = p_resource_id
AND pc.assignment_id <> p_assignment_id
AND pc.status_code = pps.project_status_code
AND pps.project_system_status_code not in ('CANDIDATE_DECLINED','CANDIDATE_WITHDRAWN','CANDIDATE_ASSIGNED')
AND ROWNUM = 1;
SELECT 'Y'
INTO l_exists
FROM PA_PROJECT_ASSIGNMENTS
WHERE resource_id = p_resource_id
AND assignment_id <> p_assignment_id
AND nvl(start_date,sysdate) <= nvl(p_assignment_end_date,sysdate)
AND nvl(end_date,sysdate) >= nvl(p_assignment_start_date,sysdate)
AND ROWNUM = 1;
status will be updated.
-------------------------------------------------------------------- */
FUNCTION IS_CAND_ON_ASSIGNMENT(p_resource_id IN NUMBER,
p_assignment_id IN NUMBER,
p_status_code IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS
l_exists VARCHAR2(1) := 'Y';
SELECT 'Y'
INTO l_exists
FROM pa_candidates
WHERE resource_id = p_resource_id
AND assignment_id = p_assignment_id;
SELECT project_system_status_code
INTO l_old_project_system_status
FROM pa_candidates, pa_project_statuses
WHERE resource_id = p_resource_id
AND assignment_id = p_assignment_id
AND status_code = project_status_code
AND status_type = 'CANDIDATE';
SELECT project_system_status_code
INTO l_new_project_system_status
FROM pa_project_statuses
WHERE project_status_code = p_status_code
AND status_type = 'CANDIDATE';
SELECT start_date
INTO l_asmt_start_date
FROM pa_project_assignments
WHERE assignment_id=p_assignment_id;
SELECT fnd_profile.value('PA_DEF_START_CAND_STATUS')
INTO l_status_code
FROM dual;
SELECT project_system_status_code
INTO l_system_status_code
FROM pa_project_statuses
WHERE project_status_code = l_status_code;
INSERT INTO PA_CANDIDATES
(CANDIDATE_ID,
ASSIGNMENT_ID,
RESOURCE_ID,
RECORD_VERSION_NUMBER,
STATUS_CODE,
NOMINATED_BY_PERSON_ID,
NOMINATION_DATE,
NOMINATION_COMMENTS,
CANDIDATE_RANKING,
CREATION_DATE,
-- Added for bug 9187892
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES
(PA_CANDIDATES_S.nextval,
p_ASSIGNMENT_ID,
l_RESOURCE_ID,
1,
l_status_code,
l_nominated_by_person_id,
sysdate,
p_nomination_comments,
null,
SYSDATE,
-- Added for bug 9187892
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
l_fnd_user_id,
SYSDATE,
l_fnd_user_id)
RETURNING
CANDIDATE_ID into l_candidate_id;
UPDATE PA_CANDIDATES
SET STATUS_CODE = l_status_code,
NOMINATION_COMMENTS = p_nomination_comments,
RECORD_VERSION_NUMBER = record_version_number + 1,
NOMINATED_BY_PERSON_ID = l_nominated_by_person_id,
NOMINATION_DATE = SYSDATE,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_fnd_user_id
WHERE assignment_id = p_assignment_id
AND resource_id = l_resource_id;
SELECT ENABLE_WF_FLAG, WORKFLOW_ITEM_TYPE, WORKFLOW_PROCESS, PROJECT_STATUS_NAME
INTO l_enable_wf_flag, l_wf_item_type, l_wf_process, l_status_name
FROM PA_PROJECT_STATUSES
WHERE status_type = 'CANDIDATE'
AND project_status_code = l_status_code;
Update_No_Of_Active_Candidates(
p_assignment_id => p_assignment_id,
p_old_system_status_code => NULL,
p_new_system_status_code => l_system_status_code,
x_return_status => l_return_status);
PROCEDURE: update_no_of_active_candidates
PURPOSE: This procedure will update no_of_active_candidates column of
pa_project_assignments by calling from other procedures which
update candidate status like Add_Candidate, Add_Candidate_Log,
Update_Candidate.
-------------------------------------------------------------------- */
PROCEDURE Update_No_Of_Active_Candidates(
p_assignment_id IN NUMBER,
p_old_system_status_code IN VARCHAR2,
p_new_system_status_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2 ) -- 4537865
IS
l_no_of_active_candidates NUMBER;
SELECT no_of_active_candidates, record_version_number
INTO l_no_of_active_candidates, l_record_version_number
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
pa_project_assignments_pkg.Update_row(
p_assignment_id => p_assignment_id,
p_no_of_active_candidates => l_no_of_active_candidates+1,
p_record_version_number => l_record_version_number,
x_return_status => l_return_status );
pa_project_assignments_pkg.Update_row(
p_assignment_id => p_assignment_id,
p_no_of_active_candidates => l_no_of_active_candidates-1,
p_record_version_number => l_record_version_number,
x_return_status => l_return_status );
pa_project_assignments_pkg.Update_row(
p_assignment_id => p_assignment_id,
p_no_of_active_candidates => l_no_of_active_candidates+1,
p_record_version_number => l_record_version_number,
x_return_status => l_return_status );
p_procedure_name => 'Update_No_Of_Active_Candidates');
END Update_No_Of_Active_Candidates;
PROCEDURE: Update_Remaining_Candidates
PURPOSE: This procedure will update all the candidates (except p_resource_id)
on the assignment p_assignment_id to status p_status_code.
Currently, the only acceptable status value for p_status_code is DECLINED.
This API is called from the assignment page, when an resource is assigned
to an assignment. The page has a region "Candidates", where the user
can select to update the status of remaing candidates to DECLINED.
-------------------------------------------------------------------- */
PROCEDURE Update_Remaining_Candidates
(p_assignment_id IN NUMBER,
p_resource_id IN NUMBER,
p_status_code IN VARCHAR2,
p_change_reason_code IN VARCHAR2,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
-- Added for bug 9187892
-- start for bug#9468526 , Added default null values
p_attribute_category IN pa_candidates.attribute_category%TYPE :=NULL ,
p_attribute1 IN pa_candidates.attribute1%TYPE :=NULL ,
p_attribute2 IN pa_candidates.attribute2%TYPE :=NULL ,
p_attribute3 IN pa_candidates.attribute3%TYPE :=NULL ,
p_attribute4 IN pa_candidates.attribute4%TYPE :=NULL ,
p_attribute5 IN pa_candidates.attribute5%TYPE :=NULL ,
p_attribute6 IN pa_candidates.attribute6%TYPE :=NULL ,
p_attribute7 IN pa_candidates.attribute7%TYPE :=NULL ,
p_attribute8 IN pa_candidates.attribute8%TYPE :=NULL ,
p_attribute9 IN pa_candidates.attribute9%TYPE :=NULL ,
p_attribute10 IN pa_candidates.attribute10%TYPE :=NULL ,
p_attribute11 IN pa_candidates.attribute11%TYPE :=NULL ,
p_attribute12 IN pa_candidates.attribute12%TYPE :=NULL ,
p_attribute13 IN pa_candidates.attribute13%TYPE :=NULL ,
p_attribute14 IN pa_candidates.attribute14%TYPE :=NULL ,
p_attribute15 IN pa_candidates.attribute15%TYPE :=NULL ,
-- start for bug#9468526 , Added default null values
x_return_status OUT NOCOPY VARCHAR2, -- 4537865 : Added nocopy hint
x_msg_data OUT NOCOPY VARCHAR2, -- 4537865 : Added nocopy hint
x_msg_count OUT NOCOPY NUMBER) -- 4537865 : Added nocopy hint
IS
cursor remain_candidates_csr is
SELECT candidate_id, resource_id, record_version_number,
candidate_ranking, status_code
FROM pa_candidates
WHERE assignment_id = p_assignment_id
and resource_id <> p_resource_id;
SELECT status_code
FROM pa_candidates
WHERE assignment_id = p_assignment_id
and resource_id = p_resource_id;
PA_DEBUG.init_err_stack('PA_CANDIDATE_PUB.Update_Remaining_Candidates');
SELECT 'Y'
INTO l_exists
FROM PA_PROJECT_STATUSES
WHERE project_status_code = p_status_code
AND project_system_status_code = 'CANDIDATE_DECLINED';
SELECT candidate_id,
record_version_number,
candidate_ranking
INTO l_asgned_candidate_id,
l_asgned_record_version_number,
l_candidate_ranking
FROM pa_candidates
WHERE assignment_id = p_assignment_id
AND resource_id = p_resource_id;
SELECT project_status_code
INTO l_project_status_code
FROM PA_PROJECT_STATUSES
WHERE project_system_status_code = 'CANDIDATE_ASSIGNED'
AND status_type = 'CANDIDATE'; -- Bug 4773033 CANDIDATE ROUTINES JOIN FOR PROJECT STATUSES
Update_Candidate
(p_candidate_id => l_asgned_candidate_id,
p_status_code => l_project_status_code,
p_ranking => l_candidate_ranking,
p_change_reason_code => null,
p_record_version_number => l_asgned_record_version_number,
p_init_msg_list => p_init_msg_list,
x_record_version_number => l_cand_record_version_number,
-- Added for bug 9187892
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
SELECT project_system_status_code
INTO l_project_system_status_code
FROM PA_PROJECT_STATUSES
WHERE project_status_code = c2.status_code;
Update_Candidate
(p_candidate_id => c2.candidate_id,
p_status_code => p_status_code,
p_ranking => c2.candidate_ranking,
p_change_reason_code => p_change_reason_code,
p_record_version_number => c2.record_version_number,
p_init_msg_list => p_init_msg_list,
x_record_version_number => l_cand_record_version_number,
-- Added for bug 9187892
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
SELECT record_version_number
INTO l_record_version_number
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
pa_project_assignments_pkg.Update_row(
p_assignment_id => p_assignment_id,
p_no_of_active_candidates => 0,
p_record_version_number => l_record_version_number,
x_return_status => l_return_status );
SELECT ps.project_system_status_code
INTO l_old_system_status_code
FROM pa_candidates cand, pa_project_statuses ps
WHERE cand.assignment_id = p_assignment_id
AND cand.resource_id = p_resource_id
AND ps.project_status_code = cand.status_code;
Update_No_Of_Active_Candidates(
p_assignment_id => p_assignment_id,
p_old_system_status_code => l_old_system_status_code,
p_new_system_status_code => 'CANDIDATE_ASSIGNED',
x_return_status => l_return_status);
p_procedure_name => 'Update_Remaining_Candidates' );
END Update_Remaining_Candidates;
in the pa_candidate_reviews table. It will also update
the status in pa_candidates table.
A change reason can be associated with every status change.
A review comment can be associated with every status change.
This API will error out if:
1. The status change from p_old_status_code to p_new_status_code
is not an acceptable change. (e.g: Status cannot change from
Declined to Under Review).
This API will return an unexpected error if:
1. p_candidate_id is not found
2. p_old_status_code or p_new_status_code are not valid status codes.
PARAMETERS:
p_candidate_id : Candidate Id of the candidate for whom
a log is being created
p_status_code : New Status Code entered for the log.
Pass null, if no value is entered in this field.
p_review_comments : Review Comments
p_change_reason_code : Change Reason for Status change.
p_record_version_number : Record Version of the Candidate Record
p_cand_rec_version_number : Record Version Number of the Candidate
(from pa_candidates). We do not
need the record version number for
the review_comments table, since we only
insert in this table, we do not update it.
-------------------------------------------------------------------- */
PROCEDURE Add_Candidate_Log
(p_candidate_id IN NUMBER,
p_status_code IN VARCHAR2,
p_change_reason_code IN VARCHAR2,
p_review_comments IN VARCHAR2,
p_cand_record_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE, -- Added for Bug 5130421: PJR Enhancements for Public APIs
x_cand_record_version_number OUT NOCOPY NUMBER, -- 4537865
-- Added for bug 9187892
-- start for bug#9468526 , Added default null values
p_attribute_category IN pa_candidates.attribute_category%TYPE :=NULL ,
p_attribute1 IN pa_candidates.attribute1%TYPE :=NULL ,
p_attribute2 IN pa_candidates.attribute2%TYPE :=NULL ,
p_attribute3 IN pa_candidates.attribute3%TYPE :=NULL ,
p_attribute4 IN pa_candidates.attribute4%TYPE :=NULL ,
p_attribute5 IN pa_candidates.attribute5%TYPE :=NULL ,
p_attribute6 IN pa_candidates.attribute6%TYPE :=NULL ,
p_attribute7 IN pa_candidates.attribute7%TYPE :=NULL ,
p_attribute8 IN pa_candidates.attribute8%TYPE :=NULL ,
p_attribute9 IN pa_candidates.attribute9%TYPE :=NULL ,
p_attribute10 IN pa_candidates.attribute10%TYPE :=NULL ,
p_attribute11 IN pa_candidates.attribute11%TYPE :=NULL ,
p_attribute12 IN pa_candidates.attribute12%TYPE :=NULL ,
p_attribute13 IN pa_candidates.attribute13%TYPE :=NULL ,
p_attribute14 IN pa_candidates.attribute14%TYPE :=NULL ,
p_attribute15 IN pa_candidates.attribute15%TYPE :=NULL ,
-- start for bug#9468526 , Added default null values
x_return_status OUT NOCOPY VARCHAR2, -- 4537865
x_msg_count OUT NOCOPY NUMBER, -- 4537865
x_msg_data OUT NOCOPY VARCHAR2) -- 4537865
IS
l_old_status_code VARCHAR2(30);
SELECT record_version_number
INTO l_old_record_version_number
FROM pa_candidates
WHERE candidate_id = p_candidate_id;
SELECT status_code
INTO l_old_status_code
FROM pa_candidates
WHERE candidate_id=p_candidate_id;
SELECT assignment_id,resource_id
INTO l_assignment_id,l_resource_id
FROM pa_candidates
where candidate_id = p_candidate_id;
SELECT project_system_status_code
INTO l_system_status_code
FROM pa_project_statuses
WHERE project_status_code = l_status_code
AND status_type = 'CANDIDATE';
UPDATE pa_candidates
SET status_code = l_status_code,
record_version_number = record_version_number + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
-- start for bug#9468526, Added nvl such that, if the passed value is null, existing one is retained.
attribute_category = nvl(p_attribute_category,attribute_category),
attribute1 = nvl(p_attribute1,attribute1),
attribute2 = nvl(p_attribute2,attribute2),
attribute3 = nvl(p_attribute3,attribute3),
attribute4 = nvl(p_attribute4,attribute4),
attribute5 = nvl(p_attribute5,attribute5),
attribute6 = nvl(p_attribute6,attribute6),
attribute7 = nvl(p_attribute7,attribute7),
attribute8 = nvl(p_attribute8,attribute8),
attribute9 = nvl(p_attribute9,attribute9),
attribute10 = nvl(p_attribute10,attribute10),
attribute11 = nvl(p_attribute11,attribute11),
attribute12 = nvl(p_attribute12,attribute12),
attribute13 = nvl(p_attribute13,attribute13),
attribute14 = nvl(p_attribute14,attribute14),
attribute15 = nvl(p_attribute15,attribute15)
-- end for bug#9468526
WHERE candidate_id = p_candidate_id AND
record_version_number=p_cand_record_version_number;
SELECT project_system_status_code
INTO l_system_status_code
FROM pa_project_statuses
WHERE project_status_code = l_status_code
AND status_type = 'CANDIDATE';
SELECT project_system_status_code
INTO l_old_system_status_code
FROM pa_project_statuses
WHERE project_status_code = l_old_status_code
AND status_type = 'CANDIDATE';
Update_No_Of_Active_Candidates(
p_assignment_id => l_assignment_id,
p_old_system_status_code => l_old_system_status_code,
p_new_system_status_code => l_system_status_code,
x_return_status => x_return_status);
INSERT INTO PA_CANDIDATE_REVIEWS
(CANDIDATE_REVIEW_ID,
CANDIDATE_ID,
RECORD_VERSION_NUMBER,
STATUS_CODE,
REVIEWER_PERSON_ID,
REVIEW_DATE,
CHANGE_REASON_CODE,
REVIEW_COMMENTS,
CREATION_DATE,
-- Added for bug 9187892
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES
(
PA_CANDIDATE_REVIEWS_S.nextval,
p_candidate_id,
1,
p_status_code,
l_reviewer_person_id,
sysdate,
l_change_reason_code,
l_review_comments,
sysdate,
-- Added for bug 9187892
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id
);
SELECT ENABLE_WF_FLAG, WORKFLOW_ITEM_TYPE, WORKFLOW_PROCESS, PROJECT_STATUS_NAME
INTO l_enable_wf_flag, l_wf_item_type, l_wf_process, l_status_name
FROM PA_PROJECT_STATUSES
WHERE status_type = 'CANDIDATE'
AND project_status_code = l_status_code;
PROCEDURE: Update_Candidate
PURPOSE: This Procedure will update candidate p_candidate_id's
status or ranking.
If the status changes, we will create a log entry in the
PA_CANDIDATES_LOG table. we will also update the
NO_OF_ACTIVE_CANDIDATES column in PA_PROJECT_STATUSES, if an
active candidate goes inactive or vice versa
Since no comment is passed, we will create the log with an empty
log message. This API will be called from the Candidate List Page,
where the status,change reason or ranking can be updated.
PARAMETERS: p_candidate_id : Candidate Id of the candidate being
updated
p_status_code : Status Code for the candidate record.
If the status is not changed, this
field will hold the old status value.
p_ranking : Ranking for the candidate
If the ranking is not changed, this
field will hold the old ranking value.
p_change_reason_code : Change Reason for Status change.
p_record_version_number : Record Version of the Candidate Record
-------------------------------------------------------------------- */
PROCEDURE Update_Candidate
(p_candidate_id IN NUMBER,
p_status_code IN VARCHAR2,
p_ranking IN NUMBER,
p_change_reason_code IN VARCHAR2,
p_record_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_validate_status IN VARCHAR2 := FND_API.G_TRUE,
-- Added for bug 9187892
-- start for bug#9468526 , Added default null values
p_attribute_category IN pa_candidates.attribute_category%TYPE :=NULL ,
p_attribute1 IN pa_candidates.attribute1%TYPE :=NULL ,
p_attribute2 IN pa_candidates.attribute2%TYPE :=NULL ,
p_attribute3 IN pa_candidates.attribute3%TYPE :=NULL ,
p_attribute4 IN pa_candidates.attribute4%TYPE :=NULL ,
p_attribute5 IN pa_candidates.attribute5%TYPE :=NULL ,
p_attribute6 IN pa_candidates.attribute6%TYPE :=NULL ,
p_attribute7 IN pa_candidates.attribute7%TYPE :=NULL ,
p_attribute8 IN pa_candidates.attribute8%TYPE :=NULL ,
p_attribute9 IN pa_candidates.attribute9%TYPE :=NULL ,
p_attribute10 IN pa_candidates.attribute10%TYPE :=NULL ,
p_attribute11 IN pa_candidates.attribute11%TYPE :=NULL ,
p_attribute12 IN pa_candidates.attribute12%TYPE :=NULL ,
p_attribute13 IN pa_candidates.attribute13%TYPE :=NULL ,
p_attribute14 IN pa_candidates.attribute14%TYPE :=NULL ,
p_attribute15 IN pa_candidates.attribute15%TYPE :=NULL ,
-- End for bug#9468526 , Added default null values
x_record_version_number OUT NOCOPY NUMBER, -- 4537865 Added nocopy hint
x_msg_count OUT NOCOPY NUMBER, -- 4537865 Added nocopy hint
x_msg_data OUT NOCOPY VARCHAR2, -- 4537865 Added nocopy hint
x_return_status OUT NOCOPY VARCHAR2) -- 4537865 Added nocopy hint
IS
l_assignment_id NUMBER;
SELECT assignment_id,
status_code,
record_version_number,
resource_id,
candidate_ranking
INTO l_assignment_id,
l_old_status_code,
l_old_record_version_number,
l_resource_id,
l_old_candidate_ranking
FROM pa_candidates
WHERE candidate_id = p_candidate_id;
SELECT project_system_status_code
INTO l_system_status_code
FROM pa_project_statuses
WHERE project_status_code = l_status_code
AND status_type = 'CANDIDATE';
SELECT project_system_status_code
INTO l_old_system_status_code
FROM pa_project_statuses
WHERE project_status_code = l_old_status_code
AND status_type = 'CANDIDATE';
UPDATE pa_candidates
SET
status_code = l_status_code,
candidate_ranking = p_ranking,
record_version_number = p_record_version_number+1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
-- start for bug#9468526, Added nvl such that, if the passed value is null, existing one is retained.
attribute_category = nvl(p_attribute_category,attribute_category),
attribute1 = nvl(p_attribute1,attribute1),
attribute2 = nvl(p_attribute2,attribute2),
attribute3 = nvl(p_attribute3,attribute3),
attribute4 = nvl(p_attribute4,attribute4),
attribute5 = nvl(p_attribute5,attribute5),
attribute6 = nvl(p_attribute6,attribute6),
attribute7 = nvl(p_attribute7,attribute7),
attribute8 = nvl(p_attribute8,attribute8),
attribute9 = nvl(p_attribute9,attribute9),
attribute10 = nvl(p_attribute10,attribute10),
attribute11 = nvl(p_attribute11,attribute11),
attribute12 = nvl(p_attribute12,attribute12),
attribute13 = nvl(p_attribute13,attribute13),
attribute14 = nvl(p_attribute14,attribute14),
attribute15 = nvl(p_attribute15,attribute15)
-- end for bug#9468526
WHERE
candidate_id = p_candidate_id AND
record_version_number = p_record_version_number;
INSERT INTO PA_CANDIDATE_REVIEWS
(CANDIDATE_REVIEW_ID,
CANDIDATE_ID,
RECORD_VERSION_NUMBER,
STATUS_CODE,
REVIEWER_PERSON_ID,
REVIEW_DATE,
REVIEW_COMMENTS,
CHANGE_REASON_CODE,
CREATION_DATE,
-- Added for bug 9187892
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES
(
PA_CANDIDATE_REVIEWS_S.nextval,
p_candidate_id,
1,
l_status_code,
l_reviewer_person_id,
sysdate,
null,
l_change_reason_code,
sysdate,
-- Added for bug 9187892
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id
);
Update_No_Of_Active_Candidates(
p_assignment_id => l_assignment_id,
p_old_system_status_code => l_old_system_status_code,
p_new_system_status_code => l_system_status_code,
x_return_status => l_return_status);
SELECT ENABLE_WF_FLAG, WORKFLOW_ITEM_TYPE, WORKFLOW_PROCESS, PROJECT_STATUS_NAME
INTO l_enable_wf_flag, l_wf_item_type, l_wf_process, l_status_name
FROM PA_PROJECT_STATUSES
WHERE status_type = 'CANDIDATE'
AND project_status_code = l_status_code;
p_procedure_name => 'Update_Candidate' );
END Update_Candidate;
SELECT ppc.customer_id,rac.customer_name
FROM pa_project_customers ppc,
ra_customers rac
WHERE ppc.project_id = p_project_id
AND rac.customer_id = ppc.customer_id ;
SELECT ppc.customer_id, substrb(party.party_name,1,50) customer_name
FROM pa_project_customers ppc,
hz_parties party,
hz_cust_accounts cust_acct
WHERE ppc.project_id = p_project_id
AND cust_acct.cust_account_id = ppc.customer_id
and party.party_id = cust_acct.party_id;
resources could be selected to create as candidates)
-------------------------------------------------------------------- */
PROCEDURE Check_Candidacy
(p_assignment_id IN NUMBER,
p_resource_count IN NUMBER,
p_resource_list IN VARCHAR2,
x_resource_list OUT NOCOPY VARCHAR2, -- 4537865 Added nocopy hint
x_msg_count OUT NOCOPY NUMBER, -- 4537865 Added nocopy hint
x_invalid_candidates OUT NOCOPY VARCHAR2, -- 4537865 Added nocopy hint
x_return_status OUT NOCOPY VARCHAR2) -- 4537865 Added nocopy hint
IS
l_in_resource_list VARCHAR2(1000);
SELECT ppa.assignment_id,
ppa.assignment_name,
ppa.assignment_effort,
ppa.additional_information,
ppa.description,
ppa.note_to_approver,
ppa.project_id,
ppa.resource_id,
ppa.start_date,
ppa.end_date,
ppa.status_code,
ppa.apprvl_status_code,
ppa.pending_approval_flag,
ppa.assignment_type
FROM pa_project_assignments ppa
WHERE assignment_id = p_assignment_id;
SELECT ps.wf_success_status_code,
ps.wf_failure_status_code,
ps.project_status_name
FROM pa_project_statuses ps
WHERE project_status_code = c_status_code;
SELECT res.resource_name,
res.person_id resource_person_id,
res.resource_id,
hou.name resource_organization_name,
res.manager_id
FROM pa_resources_denorm res,
hr_all_organization_units hou
WHERE res.resource_id = l_resource_id
AND hou.organization_id = res.resource_organization_id
AND p_start_date BETWEEN resource_effective_start_date
AND resource_effective_end_date
AND res.schedulable_flag = 'Y';
SELECT pap.project_id project_id,
pap.name name,
pap.segment1 segment1,
pap.carrying_out_organization_id carrying_out_organization_id,
pap.location_id,
hr.name organization_name,
NVL(pt.administrative_flag,'N') admin_flag
FROM pa_projects_all pap,
hr_all_organization_units hr,
pa_project_types_all pt
WHERE pap.project_id = l_project_id
AND pap.carrying_out_organization_id =
hr.organization_id
AND pap.org_id = pt.org_id -- Added for Bug 5389093
AND pt.project_type = pap.project_type;
SELECT pa_prm_wf_item_key_s.nextval
INTO l_itemkey
FROM dual;
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'CANDIDATE'
,p_item_type => p_wf_item_type
,p_item_key => l_itemkey
,p_entity_key1 => to_char(l_projects_rec.project_id)
,p_entity_key2 => to_char(p_assignment_id)
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
SELECT employee_id
INTO l_employee_id
FROM fnd_user
where user_id = FND_GLOBAL.USER_ID;
SELECT distinct(resource_name)
INTO l_resource_name
FROM pa_resources_denorm
WHERE resource_id = p_resource_id
AND rownum=1 -- 5345135
;
PROCEDURE: Delete_Candidates
PURPOSE: This procedure is called by the Assignment module, once an
assignment is deleted, the candidates in that assignment
should be also deleted accordingly
---------------------------------------------------------------------*/
PROCEDURE Delete_Candidates(p_assignment_id IN NUMBER,
p_status_code IN VARCHAR2 DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2, -- 4537865
x_msg_count OUT NOCOPY NUMBER, -- 4537865
x_msg_data OUT NOCOPY VARCHAR2) -- 4537865
IS
TYPE number_tbl IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
SELECT candidate_id
BULK COLLECT INTO l_candidates_tbl
FROM pa_candidates
WHERE assignment_id = p_assignment_id;
DELETE FROM pa_candidates
WHERE assignment_id = p_assignment_id;
SELECT candidate_id
BULK COLLECT INTO l_candidates_tbl
FROM pa_candidates
WHERE assignment_id = p_assignment_id
AND status_code = p_status_code;
DELETE FROM pa_candidates
WHERE assignment_id = p_assignment_id
AND status_code = p_status_code;
DELETE FROM pa_candidate_reviews
WHERE candidate_id = l_candidates_tbl(i);
p_procedure_name => 'Delete_Candidates',
p_error_text => x_msg_data);
End Delete_Candidates;
SELECT asmt.no_of_active_candidates,
asmt.record_version_number,
ps.project_system_status_code,
asmt.assignment_id
INTO l_no_of_active_candidates,
l_record_version_number,
l_system_status_code,
l_assignment_id
FROM pa_project_assignments asmt,
pa_candidates cand,
pa_project_statuses ps
WHERE asmt.assignment_id = cand.assignment_id
AND candidate_id = p_candidate_id
AND cand.status_code = ps.project_status_code
AND ps.status_type = 'CANDIDATE';
DELETE FROM pa_candidate_reviews
WHERE candidate_id = p_candidate_id;
DELETE FROM pa_candidates
WHERE candidate_id = p_candidate_id;
pa_project_assignments_pkg.Update_row(
p_assignment_id => l_assignment_id,
p_no_of_active_candidates => l_no_of_active_candidates-1,
p_record_version_number => l_record_version_number,
x_return_status => l_return_status );
SELECT cand.candidate_id,
cand.resource_id,
cand.status_code,
cand.nominated_by_person_id,
cand.nomination_date,
cand.nomination_comments,
cand.candidate_ranking
FROM pa_candidates cand,
pa_resources_denorm res
WHERE assignment_id = p_old_requirement_id
AND p_new_start_date BETWEEN
res.resource_effective_start_date AND
NVL(res.resource_effective_end_date, sysdate+1)
AND res.resource_id = cand.resource_id
AND res.schedulable_flag = 'Y';
SELECT status_code,
reviewer_person_id,
review_date,
change_reason_code,
review_comments
FROM pa_candidate_reviews
WHERE candidate_id = l_old_candidate_id;
INSERT INTO PA_CANDIDATES
(CANDIDATE_ID,
ASSIGNMENT_ID,
RESOURCE_ID,
RECORD_VERSION_NUMBER,
STATUS_CODE,
NOMINATED_BY_PERSON_ID,
NOMINATION_DATE,
NOMINATION_COMMENTS,
CANDIDATE_RANKING,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES
(PA_CANDIDATES_S.nextval,
p_new_requirement_id,
c2.resource_id,
1,
c2.status_code,
c2.nominated_by_person_id,
c2.nomination_date,
c2.nomination_comments,
c2.candidate_ranking,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID)
RETURNING
CANDIDATE_ID into l_new_candidate_id;
INSERT INTO PA_CANDIDATE_REVIEWS
(CANDIDATE_REVIEW_ID,
CANDIDATE_ID,
RECORD_VERSION_NUMBER,
STATUS_CODE,
REVIEWER_PERSON_ID,
REVIEW_DATE,
CHANGE_REASON_CODE,
REVIEW_COMMENTS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES
(
PA_CANDIDATE_REVIEWS_S.nextval,
l_new_candidate_id,
1,
c4.status_code,
c4.reviewer_person_id,
c4.review_date,
c4.change_reason_code,
c4.review_comments,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id
);
SELECT no_of_active_candidates
INTO l_no_of_active_candidates
FROM pa_project_assignments
WHERE assignment_id = p_old_requirement_id;
SELECT record_version_number
INTO l_record_version_number
FROM pa_project_assignments
WHERE assignment_id = p_new_requirement_id;
pa_project_assignments_pkg.Update_row(
p_assignment_id => p_new_requirement_id,
p_no_of_active_candidates => l_no_of_active_candidates,
p_record_version_number => l_record_version_number,
x_return_status => l_return_status );
SELECT candidate_id,
resource_id,
record_version_number
BULK COLLECT INTO
l_candidate_id_tbl,
l_resource_id_tbl,
l_record_version_number_tbl
FROM pa_candidates cand,
pa_project_statuses status
WHERE cand.assignment_id = p_assignment_id
AND cand.status_code = status.project_status_code
AND status.project_system_status_code IN ('CANDIDATE_PENDING_REVIEW',
'CANDIDATE_UNDER_REVIEW', 'CANDIDATE_SYSTEM_NOMINATED', 'CANDIDATE_SUITABLE')
AND status.status_type = 'CANDIDATE';
SELECT project_status_name,
enable_wf_flag,
workflow_item_type,
workflow_process
INTO l_decline_status_name,
l_enable_wf_flag,
l_wf_item_type,
l_wf_process
FROM pa_project_statuses
WHERE project_status_code = l_decline_status_code
AND status_type = 'CANDIDATE';
UPDATE pa_candidates SET
status_code = l_decline_status_code,
record_version_number = l_record_version_number_tbl(i) + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID
WHERE candidate_id = l_candidate_id_tbl(i);
INSERT INTO PA_CANDIDATE_REVIEWS
(CANDIDATE_REVIEW_ID,
CANDIDATE_ID,
RECORD_VERSION_NUMBER,
STATUS_CODE,
REVIEWER_PERSON_ID,
REVIEW_DATE,
REVIEW_COMMENTS,
CHANGE_REASON_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES
(PA_CANDIDATE_REVIEWS_S.nextval,
l_candidate_id_tbl(i),
1,
l_decline_status_code,
l_reviewer_person_id,
sysdate,
null,
null,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id
);
SELECT record_version_number
INTO l_asmt_record_version_number
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
pa_project_assignments_pkg.Update_row(p_assignment_id => p_assignment_id,
p_no_of_active_candidates => 0,
p_record_version_number => l_asmt_record_version_number,
x_return_status => l_return_status );
SELECT ROLE_NAME
INTO l_role_name_temp
FROM WF_USER_ROLES
WHERE USER_NAME = user_table(l_nf_loop_counter)
AND USER_ORIG_SYSTEM = 'PER'
AND ROLE_ORIG_SYSTEM = 'PER'
AND ROWNUM = 1;
SELECT change_reason_code
INTO l_change_reason_code
FROM (SELECT change_reason_code
FROM PA_CANDIDATE_REVIEWS
WHERE candidate_id =p_candidate_id
ORDER BY review_date DESC)
WHERE rownum = 1;