Home » Applications » Oracle Fusion Apps & E-Business Suite » applicant assignmetn in hrms (r12)
| applicant assignmetn in hrms [message #413547] |
Thu, 16 July 2009 05:50  |
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
|
Senior Member |
|
|
Hi All,
Please guide me what i am missing.
CREATE OR REPLACE PACKAGE BODY SON_UPDATE_APPLICANT_PKG
AS
/***************************************************************
**
**
** Package : SON_UPDATE_APPLICANT_PKG.pkb(package body)
** $Logfile : $
** $Creation Date : 08/07/2009
** $Author : Monika Bhakuni
**
** (C) Copyright:
**
** ------------------------------------------------------------
**
** Description : The package body contains procedure for
** updating the applicant assignment data to Apps
** tables using the API procedure
**
** EQHDOK
** ------------------------------------------------------------
**
** $Log: $
**
**************************************************************/
procedure Update_Applicant(errbuf out varchar2,retcode out varchar2)
IS
v_application_id NUMBER;
v_business_group_id NUMBER;
v_current_employer VARCHAR(200);
v_hire_date Date;
v_object_version_number NUMBER;
v_assignment_id NUMBER;
v_effective_start_date DATE;
v_effective_end_date DATE;
v_ASSIGNMENT_STATUS_TYPE_ID per_all_assignments_f.ASSIGNMENT_STATUS_TYPE_ID%TYPE;
v_organization_id NUMBER;
l_recruiter_id NUMBER;
l_grade_id NUMBER;
l_position_id NUMBER;
l_job_id NUMBER;
l_location_id NUMBER;
l_person_referred_by_id NUMBER;
l_special_ceiling_step_id NUMBER;
l_SOURCE_ORGANIZATION_ID NUMBER;
l_vacancy_id NUMBER;
l_pay_basis_id NUMBER;
l_comments VARCHAR2(100);
l_default_code_comb_id NUMBER;
l_contract_id NUMBER;
l_establishment_id NUMBER;
l_collective_agreement_id NUMBER;
l_cagr_id_flex_num NUMBER;
l_posting_content_id NUMBER;
l_applicant_rank NUMBER;
l_cagr_grade_def_id NUMBER;
l_cagr_concatenated_segments VARCHAR2(100);
l_group_name VARCHAR2(100);
l_comment_id NUMBER;
l_people_group_id NUMBER;
l_soft_coding_keyflex_id NUMBER;
--l_RECRUITMENT_ACTIVITY_ID NUMBER;
--l_payroll_id NUMBER;
l_supervisor_id NUMBER;
l_concatenated_segments VARCHAR2(100);
lt_recruiter_id NUMBER;
l_recruitment_activity_id per_recruitment_activities.RECRUITMENT_ACTIVITY_ID%TYPE;
l_payroll_id pay_payrolls.payroll_id%TYPE;
CURSOR appl
IS
SELECT Distinct effective_date,Applicant_number,Recruiter_Id,Grade,Position,Job,Location,Person_Referred_By_Id,Special_Ceiling_Step_Id
,Recruitment_Activity,Source_Organization_id,Vacancy,Pay_Basis,Comments,Default_Code_Comb_Id,Contract_id,Establishment_id
,Collective_Agreement_Id,Cagr_Id_Flex_Num,Posting_Content_Id,Applicant_Rank,Cagr_Grade_Def_Id,Supervisor_Id
,Cagr_Concatenated_Segments,Group_Name,Comment_Id,Payroll,Concatenated_Segments,People_Group_Id,Soft_Coding_Keyflex_Id
,Change_Reason,Set_Of_Book_Id,Concat_Segments
FROM SONATA_APPLCNT_ASSGMT_API;
BEGIN
FOR appl_loop IN appl
LOOP
dbms_output.put_line('BEGIN');
SELECT paaf.assignment_id
,paaf.effective_start_date
,paaf.effective_end_date
,paaf.ASSIGNMENT_STATUS_TYPE_ID
,paaf.object_version_number
,paaf.organization_id
,paaf.application_id
INTO v_assignment_id
,v_effective_start_date
,v_effective_end_date
,v_ASSIGNMENT_STATUS_TYPE_ID
,v_object_version_number
,v_organization_id
,v_application_id
FROM per_all_assignments_f paaf
,per_all_people_f papf
WHERE papf.person_id=paaf.person_id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND paaf.object_version_number = (SELECT MAX(paaf.object_version_number)
FROM per_all_assignments_f paaf
,per_all_people_f papf
WHERE paaf.person_id = papf.person_id
AND papf.applicant_number=appl_loop.applicant_number)
AND papf.applicant_number = appl_loop.Applicant_number;
dbms_output.put_line('BEGIN1');
/* Finding job id */
IF appl_loop.job IS NULL THEN
l_job_id :=NULL;
ELSE
SELECT job_id
INTO l_job_id
FROM per_jobs
WHERE name =appl_loop.job
AND business_group_id=205;
END IF;
DBMS_OUTPUT.put_line ('Check Point : 4...........');
/* Finding location id */
IF appl_loop.location IS NULL THEN
l_location_id :=NULL;
ELSE
SELECT location_id
INTO l_location_id
FROM hr_locations
WHERE location_code = appl_loop.location;
END IF;
DBMS_OUTPUT.put_line ('Check Point : 5...........');
/* Findign salary basis */
IF appl_loop.pay_basis IS NULL THEN
l_pay_basis_id :=NULL;
ELSE
SELECT pay_basis_id
INTO l_pay_basis_id
FROM per_pay_bases
WHERE business_group_id=205
AND name=appl_loop.pay_basis;
END IF;
DBMS_OUTPUT.put_line ('Check Point : 7...........');
/* Finding grade id */
IF appl_loop.grade IS NULL THEN
l_grade_id :=NULL;
ELSE
SELECT grade_id
INTO l_grade_id
FROM per_grades
WHERE name =appl_loop.grade
AND business_group_id=205;
END IF;
DBMS_OUTPUT.put_line ('Check Point : 8...........');
/* FInding Position id */
IF appl_loop.position IS NULL THEN
l_position_id :=NULL;
ELSE
SELECT position_id
INTO l_position_id
FROM HR_ALL_POSITIONS_F
WHERE name = appl_loop.position
AND business_group_id=205;
END IF;
/* Finding Vacancy Id */
IF appl_loop.VACANCY IS NULL THEN
l_vacancy_id :=NULL;
ELSE
SELECT VACANCY_ID
INTO l_vacancy_id
FROM per_vacancies
WHERE name =appl_loop.vacancy
AND business_group_id=205;
END IF;
/* Finding Payroll id */
Insert into fnd_sessions
Values ( userenv('sessionid'),sysdate);
IF appl_loop.payroll IS NULL THEN
l_payroll_id :=NULL;
ELSE
SELECT payroll_id
INTO l_payroll_id
FROM pay_payrolls
WHERE payroll_name = appl_loop.payroll
AND business_group_id=205;
END IF;
/* Finding Recruitmetn activity id */
IF appl_loop.recruitment_activity IS NULL THEN
l_recruitment_activity_id :=NULL;
ELSE
SELECT recruitment_activity_id
INTO l_recruitment_activity_id
FROM per_recruitment_activities
WHERE name =appl_loop.recruitment_activity
AND business_group_id=205;
END IF;
/*
l_recruiter_id :=719;
l_grade_id :=2072;
l_position_id :=NULL;
l_job_id :=2112;
l_location_id :=485;
l_person_referred_by_id :=NULL;
l_special_ceiling_step_id :=NULL;
l_RECRUITMENT_ACTIVITY_ID :=NULL;
l_SOURCE_ORGANIZATION_ID :=NULL;
l_vacancy_id :=108;
l_pay_basis_id :=1062;
l_comments :=NULL;
l_default_code_comb_id :=NULL;
l_contract_id :=NULL;
l_establishment_id :=NULL;
l_collective_agreement_id :=NULL;
l_cagr_id_flex_num :=NULL;
l_posting_content_id :=NULL;
l_applicant_rank :=NULL;
l_cagr_grade_def_id :=NULL;
l_supervisor_id :=NULL;
l_cagr_concatenated_segments :=NULL;
l_group_name :=NULL;
l_comment_id :=NULL;
l_payroll_id :=NULL;
l_concatenated_segments :=NULL;
l_people_group_id :=227;
l_soft_coding_keyflex_id :=3061;
*/
dbms_output.put_line('BEGIN12');
/*
select papf.employee_number into lt_recruiter_id
from per_all_people_f papf
,per_all_assignments_f paaf
where --applicant_number=98
employee_number=l_recruiter_id
AND paaf.person_id=papf.person_id
AND trunc(sysdate) < papf.effective_end_date
AND trunc(sysdate) < paaf.effective_end_date;
*/
hr_assignment_api.update_apl_asg(
p_validate =>FALSE
,p_effective_date =>appl_loop.effective_date
,p_datetrack_update_mode =>'CORRECTION'
,p_assignment_id =>v_assignment_id
,p_object_version_number =>v_object_version_number
,p_recruiter_id =>appl_loop.recruiter_id
,p_grade_id =>l_grade_id
,p_position_id =>l_position_id
,p_job_id =>l_job_id
,p_payroll_id =>l_payroll_id
,p_location_id =>l_location_id
,p_person_referred_by_id =>appl_loop.person_referred_by_id
,p_supervisor_id =>appl_loop.supervisor_id
,p_special_ceiling_step_id =>appl_loop.special_ceiling_step_id
,p_recruitment_activity_id =>l_RECRUITMENT_ACTIVITY_ID
,p_source_organization_id =>appl_loop.SOURCE_ORGANIZATION_ID
,p_organization_id =>v_organization_id
,p_vacancy_id =>l_vacancy_id
,p_pay_basis_id =>l_pay_basis_id
,p_application_id =>v_application_id
,p_change_reason =>appl_loop.change_reason
,p_assignment_status_type_id =>v_ASSIGNMENT_STATUS_TYPE_ID
,p_comments =>appl_loop.comments
--,p_date_probation_end =>trunc(sysdate +60)
,p_default_code_comb_id =>appl_loop.default_code_comb_id
--,p_employment_category =>NULL
-- ,p_frequency =>'W'
--,p_normal_hours =>40
--,p_perf_review_period =>8
-- ,p_probation_period =>2
--,p_sal_review_period =>12
,p_set_of_books_id =>appl_loop.set_of_book_id
,p_concatenated_segments =>appl_loop.concatenated_segments
,p_concat_segments =>appl_loop.concat_segments
,p_contract_id =>appl_loop.contract_id
,p_establishment_id =>appl_loop.establishment_id
,p_collective_agreement_id =>appl_loop.collective_agreement_id
,p_cagr_id_flex_num =>appl_loop.cagr_id_flex_num
,p_posting_content_id =>appl_loop.posting_content_id
,p_applicant_rank =>appl_loop.applicant_rank
,p_cagr_grade_def_id =>appl_loop.cagr_grade_def_id
,p_cagr_concatenated_segments =>appl_loop.cagr_concatenated_segments
,p_group_name =>appl_loop.group_name
,p_comment_id =>appl_loop.comment_id
,p_people_group_id =>appl_loop.people_group_id
,p_soft_coding_keyflex_id =>appl_loop.soft_coding_keyflex_id
,p_effective_start_date =>v_effective_start_date
,p_effective_end_date =>v_effective_end_date
);
commit;
END LOOP;
dbms_output.put_line('BEGIN END');
END Update_Applicant;
/*--End of package--------*/
END SON_UPDATE_APPLICANT_PKG;
/
while executing i am getting error
-----------
ERROR at line 1:
ORA-20001: The primary key specified is invalid
Cause: The primary key values specified are invalid and do not exist in
the schema.
Action: Check the primary key values before attempting to carry out the
operation again.
ORA-06512: at "APPS.HR_ASSIGNMENT_API", line 17856
ORA-06512: at "APPS.SON_UPDATE_APPLICANT_PKG", line 232
ORA-06512: at line 1
-------------
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon May 20 16:38:04 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
