Hi all,
I have created below API to create Object to the user.When i run it data populating in the PER_OBJECTIVES table, but the same data is not showing when i try to see from the front end. I am trying to see the same data in Performance Managment - Employee responsibility.
If i created same object manually, this time also same table is updating and data is showing in the front end.
This is very urgent to..please help me on this.
create or replace procedure xx_hrms_create_object_prc(ERRBUF OUT VARCHAR2,RETCODE OUT VARCHAR2)
is
cursor c is select * from xx_hrms_stg;
V_VALIDATE BOOLEAN:=false;
V_EFFECTIVE_DATE DATE := trunc(sysdate);
V_BUSINESS_GROUP_ID NUMBER:=81;
V_NAME VARCHAR2(2000); ------objective_name ---1
V_START_DATE DATE;-----Process_start_date
V_OWNING_PERSON_ID number;
V_TARGET_DATE DATE default null; ---Target_date
V_ACHIEVEMENT_DATE DATE default null; ---procee_End_Date
V_DETAIL VARCHAR2(2000) default null;-----Objective
V_COMMENTS VARCHAR2(2000) default null;
V_SUCCESS_CRITERIA VARCHAR2(2000) default null;------Key Performance Indicators
V_APPRAISAL_ID NUMBER default null;
V_ATTRIBUTE_CATEGORY VARCHAR2(2000) default null;
V_ATTRIBUTE1 VARCHAR2(2000) default null;
V_ATTRIBUTE2 VARCHAR2(2000) default null;
V_ATTRIBUTE3 VARCHAR2(2000) default null;
V_ATTRIBUTE4 VARCHAR2(2000) default null;
V_ATTRIBUTE5 VARCHAR2(2000) default null;
V_ATTRIBUTE6 VARCHAR2(2000) default null;
V_ATTRIBUTE7 VARCHAR2(2000) default null;
V_ATTRIBUTE8 VARCHAR2(2000) default null;
V_ATTRIBUTE9 VARCHAR2(2000) default null;
V_ATTRIBUTE10 VARCHAR2(2000) default null;
V_ATTRIBUTE11 VARCHAR2(2000) default null;
V_ATTRIBUTE12 VARCHAR2(2000) default null;
V_ATTRIBUTE13 VARCHAR2(2000) default null;
V_ATTRIBUTE14 VARCHAR2(2000) default null;
V_ATTRIBUTE15 VARCHAR2(2000) default null;
V_ATTRIBUTE16 VARCHAR2(2000) default null;
V_ATTRIBUTE17 VARCHAR2(2000) default null;
V_ATTRIBUTE18 VARCHAR2(2000) default null;
V_ATTRIBUTE19 VARCHAR2(2000) default null;
V_ATTRIBUTE20 VARCHAR2(2000) default null;
V_ATTRIBUTE21 VARCHAR2(2000) default null;
V_ATTRIBUTE22 VARCHAR2(2000) default null;
V_ATTRIBUTE23 VARCHAR2(2000) default null;
V_ATTRIBUTE24 VARCHAR2(2000) default null;
V_ATTRIBUTE25 VARCHAR2(2000) default null;
V_ATTRIBUTE26 VARCHAR2(2000) default null;
V_ATTRIBUTE27 VARCHAR2(2000) default null;
V_ATTRIBUTE28 VARCHAR2(2000) default null;
V_ATTRIBUTE29 VARCHAR2(2000) default null;
V_ATTRIBUTE30 VARCHAR2(2000) default null;
V_SCORECARD_ID NUMBER default null;--:=9193;
V_COPIED_FROM_LIBRARY_ID NUMBER default null;
V_COPIED_FROM_OBJECTIVE_ID NUMBER default null;
V_ALIGNED_WITH_OBJECTIVE_ID NUMBER default null;
V_NEXT_REVIEW_DATE DATE; --Next_review_date
V_GROUP_CODE VARCHAR2(2000) :='CUS';--group
V_PRIORITY_CODE VARCHAR2(2000) :='1_L';--Priority
V_APPRAISE_FLAG VARCHAR2(2000);----Appraise
V_VERIFIED_FLAG VARCHAR2(2000) default null;
V_TARGET_VALUE NUMBER default null;
V_ACTUAL_VALUE NUMBER default null;
V_WEIGHTING_PERCENT number ;--Weighting Scale
V_COMPLETE_PERCENT NUMBER default null;--Complete
V_UOM_CODE VARCHAR2(2000) default null;
V_MEASUREMENT_STYLE_CODE VARCHAR2(2000):='N_M';
V_MEASURE_NAME VARCHAR2(2000) default null;
V_MEASURE_TYPE_CODE VARCHAR2(2000) default null;
V_MEASURE_COMMENTS VARCHAR2(2000) default null;
V_SHARING_ACCESS_CODE VARCHAR2(2000) default null;
-- Output Variables
V_WEIGHTING_OVER_100_WARNING BOOLEAN;
V_WEIGHTING_APPRAISAL_WARNING BOOLEAN;
V_OBJECTIVE_ID NUMBER default null;
V_OBJECT_VERSION_NUMBER NUMBER;
v_process_flag varchar2(10);
BEGIN
for r in c
loop
DBMS_output.enable(800000);
begin
fnd_file.put_line(fnd_file.log,'Entered into Employee Name Validation');
if r.emp_number is not null then
select employee_id into V_OWNING_PERSON_ID from hr_employees where employee_num=r.emp_number;
fnd_file.put_line(fnd_file.log,'Employee Name Validation completed');
v_process_flag:='Y';
else
fnd_file.put_line(fnd_file.log,'Employee Name should not null');
v_process_flag:='N';
end if;
end;
begin
fnd_file.put_line(fnd_file.log,'Entered into Objective Name Validation');
if r.objective_name is not null then
select r.objective_name into V_NAME from dual;
fnd_file.put_line(fnd_file.log,'Objective Name Validation completed');
v_process_flag:='Y';
else
fnd_file.put_line(fnd_file.log,'objective Name should not null');
v_process_flag:='N';
end if;
end;
begin
fnd_file.put_line(fnd_file.log,'Entered into Process Start Date Validation');
if r.process_start_date is not null then
select r.process_start_date into V_START_DATE from dual;
fnd_file.put_line(fnd_file.log,'Process Start Date Validation completed');
v_process_flag:='Y';
else
fnd_file.put_line(fnd_file.log,'Process Start Date should not null');
v_process_flag:='N';
end if;
end;
Begin
fnd_file.put_line(fnd_file.log,'Entered into Appraise Validation');
if r.appraise is not null then
select r.appraise into V_APPRAISE_FLAG from dual;
fnd_file.put_line(fnd_file.log,'Appraise Validation completed');
v_process_flag:='Y';
else
fnd_file.put_line(fnd_file.log,'Appraise should not null');
v_process_flag:='N';
end if;
end;
Begin
fnd_file.put_line(fnd_file.log,'Entered into WEIGHTING_SCALE Validation');
if r.WEIGHTING_SCALE is not null then
select replace(r.WEIGHTING_SCALE,'%','') into V_WEIGHTING_PERCENT from dual;
fnd_file.put_line(fnd_file.log,'WEIGHTING_SCALE Validation completed');
v_process_flag:='Y';
else
fnd_file.put_line(fnd_file.log,'WEIGHTING_SCALE should not null');
v_process_flag:='N';
end if;
end;
Begin
fnd_file.put_line(fnd_file.log,'Entered into TARGET_DATE Validation');
if r.TARGET_DATE is not null then
select r.TARGET_DATE into V_TARGET_DATE from dual;
fnd_file.put_line(fnd_file.log,'TARGET_DATE Validation completed');
v_process_flag:='Y';
else
fnd_file.put_line(fnd_file.log,'TARGET_DATE should not null');
v_process_flag:='N';
end if;
end;
begin
fnd_file.put_line(fnd_file.log,'Entered into Scorecard Sequence generatiom');
select APPS.XX_SEQ.nextval into V_SCORECARD_ID from dual;
fnd_file.put_line(fnd_file.log,'Sequence generated for Scorecard');
end;
begin
fnd_file.put_line(fnd_file.log,'Entered into Insertion Part');
IF v_process_flag='Y' THEN-- Calling API HR_OBJECTIVES_API.CREATE_OBJECTIVE
HR_OBJECTIVES_API.CREATE_OBJECTIVE(P_VALIDATE => V_VALIDATE
,P_EFFECTIVE_DATE => V_EFFECTIVE_DATE
,P_BUSINESS_GROUP_ID => V_BUSINESS_GROUP_ID
,P_NAME => V_NAME
,P_START_DATE => V_START_DATE
,P_OWNING_PERSON_ID => V_OWNING_PERSON_ID
,P_TARGET_DATE => V_TARGET_DATE
,P_ACHIEVEMENT_DATE => V_TARGET_DATE--V_ACHIEVEMENT_DATE
,P_DETAIL => r.objective--V_DETAIL
,P_COMMENTS => V_COMMENTS
,P_SUCCESS_CRITERIA => r.KEY_PERFORMANCE_INDICATORS--V_SUCCESS_CRITERIA
,P_APPRAISAL_ID => V_APPRAISAL_ID
,P_ATTRIBUTE_CATEGORY => V_ATTRIBUTE_CATEGORY
,P_ATTRIBUTE1 => V_ATTRIBUTE1
,P_ATTRIBUTE2 => V_ATTRIBUTE2
,P_ATTRIBUTE3 => V_ATTRIBUTE3
,P_ATTRIBUTE4 => V_ATTRIBUTE4
,P_ATTRIBUTE5 => V_ATTRIBUTE5
,P_ATTRIBUTE6 => V_ATTRIBUTE6
,P_ATTRIBUTE7 => V_ATTRIBUTE7
,P_ATTRIBUTE8 => V_ATTRIBUTE8
,P_ATTRIBUTE9 => V_ATTRIBUTE9
,P_ATTRIBUTE10 => V_ATTRIBUTE10
,P_ATTRIBUTE11 => V_ATTRIBUTE11
,P_ATTRIBUTE12 => V_ATTRIBUTE12
,P_ATTRIBUTE13 => V_ATTRIBUTE13
,P_ATTRIBUTE14 => V_ATTRIBUTE14
,P_ATTRIBUTE15 => V_ATTRIBUTE15
,P_ATTRIBUTE16 => V_ATTRIBUTE16
,P_ATTRIBUTE17 => V_ATTRIBUTE17
,P_ATTRIBUTE18 => V_ATTRIBUTE18
,P_ATTRIBUTE19 => V_ATTRIBUTE19
,P_ATTRIBUTE20 => V_ATTRIBUTE20
,P_ATTRIBUTE21 => V_ATTRIBUTE21
,P_ATTRIBUTE22 => V_ATTRIBUTE22
,P_ATTRIBUTE23 => V_ATTRIBUTE23
,P_ATTRIBUTE24 => V_ATTRIBUTE24
,P_ATTRIBUTE25 => V_ATTRIBUTE25
,P_ATTRIBUTE26 => V_ATTRIBUTE26
,P_ATTRIBUTE27 => V_ATTRIBUTE27
,P_ATTRIBUTE28 => V_ATTRIBUTE28
,P_ATTRIBUTE29 => V_ATTRIBUTE29
,P_ATTRIBUTE30 => V_ATTRIBUTE30
,P_SCORECARD_ID => V_SCORECARD_ID
,P_COPIED_FROM_LIBRARY_ID => V_COPIED_FROM_LIBRARY_ID
,P_COPIED_FROM_OBJECTIVE_ID => V_COPIED_FROM_OBJECTIVE_ID
,P_ALIGNED_WITH_OBJECTIVE_ID => V_ALIGNED_WITH_OBJECTIVE_ID
,P_NEXT_REVIEW_DATE => V_NEXT_REVIEW_DATE
,P_GROUP_CODE => V_GROUP_CODE
,P_PRIORITY_CODE => V_PRIORITY_CODE
,P_APPRAISE_FLAG => V_APPRAISE_FLAG
,P_VERIFIED_FLAG => V_VERIFIED_FLAG
,P_TARGET_VALUE => V_TARGET_VALUE
,P_ACTUAL_VALUE => V_ACTUAL_VALUE
,P_WEIGHTING_PERCENT => V_WEIGHTING_PERCENT
,P_COMPLETE_PERCENT => V_COMPLETE_PERCENT
,P_UOM_CODE => V_UOM_CODE
,P_MEASUREMENT_STYLE_CODE => V_MEASUREMENT_STYLE_CODE
,P_MEASURE_NAME => V_MEASURE_NAME
,P_MEASURE_TYPE_CODE => V_MEASURE_TYPE_CODE
,P_MEASURE_COMMENTS => V_MEASURE_COMMENTS
,P_SHARING_ACCESS_CODE => V_SHARING_ACCESS_CODE
,P_WEIGHTING_OVER_100_WARNING => V_WEIGHTING_OVER_100_WARNING
,P_WEIGHTING_APPRAISAL_WARNING => V_WEIGHTING_APPRAISAL_WARNING
,P_OBJECTIVE_ID => V_OBJECTIVE_ID
,P_OBJECT_VERSION_NUMBER => V_OBJECT_VERSION_NUMBER
);
fnd_file.put_line(fnd_file.log,'Successfully created objective: ' || v_objective_id ||
' for TARGET PERSON_ID: ' || v_owning_person_id );
UPDATE xx_hrms_stg SET ERROR_MESSAGE='Record Successfully loaded intoPER_OBJECTIVE Table'
,process_flag='S'
where EMP_NUMBER =r.EMP_NUMBER;
else
UPDATE xx_hrms_stg SET ERROR_MESSAGE='Record Not loaded intoPER_OBJECTIVE Table'
,process_flag='F'
where EMP_NUMBER =r.EMP_NUMBER;
END IF;
commit;
end;
end loop;
--commit;
exception
when others then
fnd_file.put_line(fnd_file.log,'error : ' || sqlerrm);
END;