PMS Objectives Mandatory R12 User Hook Implementation Steps
1) Create package with the required procedure XXEAA_PMS_USERHOOK_PKG.XXUPDATE_APPRAISAL_B
--The parameters must be same with same sequence (as it is with seeded hook API pkg.procedure
--here seeded pkg procedure is HR_APPRAISALS_BK2.UPDATE_APPRAISAL_B)
2)--Get API_MODULE_ID
-- Choose the exact Module
SELECT *
FROM hr_api_modules
WHERE api_module_type = 'BP'
AND module_name LIKE '%UPDATE_APPRAISAL%'; --1297
3)-- get API_HOOK_ID
-- Choose w.r.t. the exact API_Module_ID
select * from hr_api_hooks ahk where API_HOOK_TYPE='BP' AND API_MODULE_ID=1297--2925
4) --create_api_hook_call
DECLARE
l_api_hook_call_id NUMBER;
l_object_version_number NUMBER;
BEGIN
hr_api_hook_call_api.create_api_hook_call (
p_validate => FALSE,
p_effective_date => TO_DATE (SYSDATE),
p_api_hook_id => 2925,
p_api_hook_call_type => 'PP',--PKG-Procedure
p_sequence => 3000,--always keep this 3000
p_enabled_flag => 'Y',
p_call_package => 'XXEAA_PMS_USERHOOK_PKG',
p_call_procedure => 'XXUPDATE_APPRAISAL_B',
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number);
COMMIT;
dbms_output.put_line('l_api_hook_call_id:-'||l_api_hook_call_id);
dbms_output.put_line(' l_object_version_number:-'|| l_object_version_number);
END;
/* Output
l_api_hook_call_id:-1180
l_object_version_number:-1
*/
5) --Check Wheather Attached Or Not:
--ENABLED_FLAG must be Y and Status =
--------------------------------------------------------------------------------
select * from hr_api_hook_calls where call_package like '%XXEAA_PMS_USERHOOK_PKG%'
6)
-- Run Pre-Processor:
--------------------------------------------------------------------------------
DECLARE
BEGIN
hr_api_user_hooks_utility.create_hooks_one_module(1297); -- (Pass Application Module ID ) API_MODULE_ID==1297
dbms_output.put_line('Pre-Processor Run Successfully');
commit;
exception
when others then
dbms_output.put_line(sqlerrm);
END;
/*Output
Pre-Processor Run Successfully
*/
7)
--Check status
--Preproceddor_date must not be null after run pre-processor
--and Status must be V & ENCODED_ERROR must be null
select * from hr_api_hook_calls where call_package like '%XXEAA_PMS_USERHOOK_PKG%'
=================================================================
/* Formatted on 2020/07/14 23:35 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PACKAGE xxeaa_pms_userhook_pkg
IS
PROCEDURE xxupdate_appraisal_b (
p_effective_date IN DATE,
p_appraisal_id IN NUMBER,
p_object_version_number IN NUMBER,
p_appraiser_person_id IN NUMBER,
p_appraisal_date IN DATE,
p_appraisal_period_end_date IN DATE,
p_appraisal_period_start_date IN DATE,
p_type IN VARCHAR2,
p_next_appraisal_date IN DATE,
p_status IN VARCHAR2,
p_comments IN VARCHAR2,
p_overall_performance_level_id IN NUMBER,
p_open IN VARCHAR2,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_attribute16 IN VARCHAR2,
p_attribute17 IN VARCHAR2,
p_attribute18 IN VARCHAR2,
p_attribute19 IN VARCHAR2,
p_attribute20 IN VARCHAR2,
p_system_type IN VARCHAR2,
p_system_params IN VARCHAR2,
p_appraisee_access IN VARCHAR2,
p_main_appraiser_id IN NUMBER,
p_assignment_id IN NUMBER,
p_assignment_start_date IN DATE,
p_asg_business_group_id IN NUMBER,
p_assignment_organization_id IN NUMBER,
p_assignment_job_id IN NUMBER,
p_assignment_position_id IN NUMBER,
p_assignment_grade_id IN NUMBER,
p_appraisal_system_status IN VARCHAR2,
p_potential_readiness_level IN VARCHAR2,
p_potential_short_term_workopp IN VARCHAR2,
p_potential_long_term_workopp IN VARCHAR2,
p_potential_details IN VARCHAR2,
p_event_id IN NUMBER,
p_show_competency_ratings IN VARCHAR2,
p_show_objective_ratings IN VARCHAR2,
p_show_questionnaire_info IN VARCHAR2,
p_show_participant_details IN VARCHAR2,
p_show_participant_ratings IN VARCHAR2,
p_show_participant_names IN VARCHAR2,
p_show_overall_ratings IN VARCHAR2,
p_show_overall_comments IN VARCHAR2,
p_update_appraisal IN VARCHAR2,
p_provide_overall_feedback IN VARCHAR2,
p_appraisee_comments IN VARCHAR2,
p_plan_id IN NUMBER,
p_offline_status IN VARCHAR2,
p_retention_potential IN VARCHAR2,
p_show_participant_comments IN VARCHAR2
);
END xxeaa_pms_userhook_pkg;
/
CREATE OR REPLACE PACKAGE BODY xxeaa_pms_userhook_pkg
IS
PROCEDURE xxupdate_appraisal_b
--HR_APPRAISALS_BK2.UPDATE_APPRAISAL_B (1297,2925)
(
p_effective_date IN DATE,
p_appraisal_id IN NUMBER,
p_object_version_number IN NUMBER,
p_appraiser_person_id IN NUMBER,
p_appraisal_date IN DATE,
p_appraisal_period_end_date IN DATE,
p_appraisal_period_start_date IN DATE,
p_type IN VARCHAR2,
p_next_appraisal_date IN DATE,
p_status IN VARCHAR2,
p_comments IN VARCHAR2,
p_overall_performance_level_id IN NUMBER,
p_open IN VARCHAR2,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_attribute16 IN VARCHAR2,
p_attribute17 IN VARCHAR2,
p_attribute18 IN VARCHAR2,
p_attribute19 IN VARCHAR2,
p_attribute20 IN VARCHAR2,
p_system_type IN VARCHAR2,
p_system_params IN VARCHAR2,
p_appraisee_access IN VARCHAR2,
p_main_appraiser_id IN NUMBER,
p_assignment_id IN NUMBER,
p_assignment_start_date IN DATE,
p_asg_business_group_id IN NUMBER,
p_assignment_organization_id IN NUMBER,
p_assignment_job_id IN NUMBER,
p_assignment_position_id IN NUMBER,
p_assignment_grade_id IN NUMBER,
p_appraisal_system_status IN VARCHAR2,
p_potential_readiness_level IN VARCHAR2,
p_potential_short_term_workopp IN VARCHAR2,
p_potential_long_term_workopp IN VARCHAR2,
p_potential_details IN VARCHAR2,
p_event_id IN NUMBER,
p_show_competency_ratings IN VARCHAR2,
p_show_objective_ratings IN VARCHAR2,
p_show_questionnaire_info IN VARCHAR2,
p_show_participant_details IN VARCHAR2,
p_show_participant_ratings IN VARCHAR2,
p_show_participant_names IN VARCHAR2,
p_show_overall_ratings IN VARCHAR2,
p_show_overall_comments IN VARCHAR2,
p_update_appraisal IN VARCHAR2,
p_provide_overall_feedback IN VARCHAR2,
p_appraisee_comments IN VARCHAR2,
p_plan_id IN NUMBER,
p_offline_status IN VARCHAR2,
p_retention_potential IN VARCHAR2,
p_show_participant_comments IN VARCHAR2
)
IS
lv_objectives_cnt NUMBER := 0;
BEGIN
xxpk_debug_prc ('XXEAA_PMS_USERHOOK_PKG.XXUPDATE_APPRAISAL_B',
'Enterred',
NULL,
'00',
SUBSTR (SQLERRM, 1, 399)
);
BEGIN
SELECT COUNT (1)
INTO lv_objectives_cnt
FROM per_objectives poe
WHERE poe.appraisal_id = p_appraisal_id;
EXCEPTION
WHEN OTHERS
THEN
lv_objectives_cnt := 0;
END;
xxpk_debug_prc ('XXEAA_PMS_USERHOOK_PKG.XXUPDATE_APPRAISAL_B',
'P_APPRAISAL_ID - P_ASSIGNMENT_ID :-> '
|| TO_CHAR (p_appraisal_id)
|| ' - '
|| TO_CHAR (p_assignment_id),
'lv_objectives_cnt - ' || TO_CHAR (lv_objectives_cnt),
'01',
SUBSTR (SQLERRM, 1, 399)
);
IF NVL (lv_objectives_cnt, 0) = 0
THEN
fnd_message.set_name ('PER', 'Objectives are mandatory !!');
fnd_message.raise_error;
END IF;
END xxupdate_appraisal_b;
END xxeaa_pms_userhook_pkg;
/
1) Create package with the required procedure XXEAA_PMS_USERHOOK_PKG.XXUPDATE_APPRAISAL_B
--The parameters must be same with same sequence (as it is with seeded hook API pkg.procedure
--here seeded pkg procedure is HR_APPRAISALS_BK2.UPDATE_APPRAISAL_B)
2)--Get API_MODULE_ID
-- Choose the exact Module
SELECT *
FROM hr_api_modules
WHERE api_module_type = 'BP'
AND module_name LIKE '%UPDATE_APPRAISAL%'; --1297
3)-- get API_HOOK_ID
-- Choose w.r.t. the exact API_Module_ID
select * from hr_api_hooks ahk where API_HOOK_TYPE='BP' AND API_MODULE_ID=1297--2925
4) --create_api_hook_call
DECLARE
l_api_hook_call_id NUMBER;
l_object_version_number NUMBER;
BEGIN
hr_api_hook_call_api.create_api_hook_call (
p_validate => FALSE,
p_effective_date => TO_DATE (SYSDATE),
p_api_hook_id => 2925,
p_api_hook_call_type => 'PP',--PKG-Procedure
p_sequence => 3000,--always keep this 3000
p_enabled_flag => 'Y',
p_call_package => 'XXEAA_PMS_USERHOOK_PKG',
p_call_procedure => 'XXUPDATE_APPRAISAL_B',
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number);
COMMIT;
dbms_output.put_line('l_api_hook_call_id:-'||l_api_hook_call_id);
dbms_output.put_line(' l_object_version_number:-'|| l_object_version_number);
END;
/* Output
l_api_hook_call_id:-1180
l_object_version_number:-1
*/
5) --Check Wheather Attached Or Not:
--ENABLED_FLAG must be Y and Status =
--------------------------------------------------------------------------------
select * from hr_api_hook_calls where call_package like '%XXEAA_PMS_USERHOOK_PKG%'
6)
-- Run Pre-Processor:
--------------------------------------------------------------------------------
DECLARE
BEGIN
hr_api_user_hooks_utility.create_hooks_one_module(1297); -- (Pass Application Module ID ) API_MODULE_ID==1297
dbms_output.put_line('Pre-Processor Run Successfully');
commit;
exception
when others then
dbms_output.put_line(sqlerrm);
END;
/*Output
Pre-Processor Run Successfully
*/
7)
--Check status
--Preproceddor_date must not be null after run pre-processor
--and Status must be V & ENCODED_ERROR must be null
select * from hr_api_hook_calls where call_package like '%XXEAA_PMS_USERHOOK_PKG%'
=================================================================
/* Formatted on 2020/07/14 23:35 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PACKAGE xxeaa_pms_userhook_pkg
IS
PROCEDURE xxupdate_appraisal_b (
p_effective_date IN DATE,
p_appraisal_id IN NUMBER,
p_object_version_number IN NUMBER,
p_appraiser_person_id IN NUMBER,
p_appraisal_date IN DATE,
p_appraisal_period_end_date IN DATE,
p_appraisal_period_start_date IN DATE,
p_type IN VARCHAR2,
p_next_appraisal_date IN DATE,
p_status IN VARCHAR2,
p_comments IN VARCHAR2,
p_overall_performance_level_id IN NUMBER,
p_open IN VARCHAR2,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_attribute16 IN VARCHAR2,
p_attribute17 IN VARCHAR2,
p_attribute18 IN VARCHAR2,
p_attribute19 IN VARCHAR2,
p_attribute20 IN VARCHAR2,
p_system_type IN VARCHAR2,
p_system_params IN VARCHAR2,
p_appraisee_access IN VARCHAR2,
p_main_appraiser_id IN NUMBER,
p_assignment_id IN NUMBER,
p_assignment_start_date IN DATE,
p_asg_business_group_id IN NUMBER,
p_assignment_organization_id IN NUMBER,
p_assignment_job_id IN NUMBER,
p_assignment_position_id IN NUMBER,
p_assignment_grade_id IN NUMBER,
p_appraisal_system_status IN VARCHAR2,
p_potential_readiness_level IN VARCHAR2,
p_potential_short_term_workopp IN VARCHAR2,
p_potential_long_term_workopp IN VARCHAR2,
p_potential_details IN VARCHAR2,
p_event_id IN NUMBER,
p_show_competency_ratings IN VARCHAR2,
p_show_objective_ratings IN VARCHAR2,
p_show_questionnaire_info IN VARCHAR2,
p_show_participant_details IN VARCHAR2,
p_show_participant_ratings IN VARCHAR2,
p_show_participant_names IN VARCHAR2,
p_show_overall_ratings IN VARCHAR2,
p_show_overall_comments IN VARCHAR2,
p_update_appraisal IN VARCHAR2,
p_provide_overall_feedback IN VARCHAR2,
p_appraisee_comments IN VARCHAR2,
p_plan_id IN NUMBER,
p_offline_status IN VARCHAR2,
p_retention_potential IN VARCHAR2,
p_show_participant_comments IN VARCHAR2
);
END xxeaa_pms_userhook_pkg;
/
CREATE OR REPLACE PACKAGE BODY xxeaa_pms_userhook_pkg
IS
PROCEDURE xxupdate_appraisal_b
--HR_APPRAISALS_BK2.UPDATE_APPRAISAL_B (1297,2925)
(
p_effective_date IN DATE,
p_appraisal_id IN NUMBER,
p_object_version_number IN NUMBER,
p_appraiser_person_id IN NUMBER,
p_appraisal_date IN DATE,
p_appraisal_period_end_date IN DATE,
p_appraisal_period_start_date IN DATE,
p_type IN VARCHAR2,
p_next_appraisal_date IN DATE,
p_status IN VARCHAR2,
p_comments IN VARCHAR2,
p_overall_performance_level_id IN NUMBER,
p_open IN VARCHAR2,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_attribute16 IN VARCHAR2,
p_attribute17 IN VARCHAR2,
p_attribute18 IN VARCHAR2,
p_attribute19 IN VARCHAR2,
p_attribute20 IN VARCHAR2,
p_system_type IN VARCHAR2,
p_system_params IN VARCHAR2,
p_appraisee_access IN VARCHAR2,
p_main_appraiser_id IN NUMBER,
p_assignment_id IN NUMBER,
p_assignment_start_date IN DATE,
p_asg_business_group_id IN NUMBER,
p_assignment_organization_id IN NUMBER,
p_assignment_job_id IN NUMBER,
p_assignment_position_id IN NUMBER,
p_assignment_grade_id IN NUMBER,
p_appraisal_system_status IN VARCHAR2,
p_potential_readiness_level IN VARCHAR2,
p_potential_short_term_workopp IN VARCHAR2,
p_potential_long_term_workopp IN VARCHAR2,
p_potential_details IN VARCHAR2,
p_event_id IN NUMBER,
p_show_competency_ratings IN VARCHAR2,
p_show_objective_ratings IN VARCHAR2,
p_show_questionnaire_info IN VARCHAR2,
p_show_participant_details IN VARCHAR2,
p_show_participant_ratings IN VARCHAR2,
p_show_participant_names IN VARCHAR2,
p_show_overall_ratings IN VARCHAR2,
p_show_overall_comments IN VARCHAR2,
p_update_appraisal IN VARCHAR2,
p_provide_overall_feedback IN VARCHAR2,
p_appraisee_comments IN VARCHAR2,
p_plan_id IN NUMBER,
p_offline_status IN VARCHAR2,
p_retention_potential IN VARCHAR2,
p_show_participant_comments IN VARCHAR2
)
IS
lv_objectives_cnt NUMBER := 0;
BEGIN
xxpk_debug_prc ('XXEAA_PMS_USERHOOK_PKG.XXUPDATE_APPRAISAL_B',
'Enterred',
NULL,
'00',
SUBSTR (SQLERRM, 1, 399)
);
BEGIN
SELECT COUNT (1)
INTO lv_objectives_cnt
FROM per_objectives poe
WHERE poe.appraisal_id = p_appraisal_id;
EXCEPTION
WHEN OTHERS
THEN
lv_objectives_cnt := 0;
END;
xxpk_debug_prc ('XXEAA_PMS_USERHOOK_PKG.XXUPDATE_APPRAISAL_B',
'P_APPRAISAL_ID - P_ASSIGNMENT_ID :-> '
|| TO_CHAR (p_appraisal_id)
|| ' - '
|| TO_CHAR (p_assignment_id),
'lv_objectives_cnt - ' || TO_CHAR (lv_objectives_cnt),
'01',
SUBSTR (SQLERRM, 1, 399)
);
IF NVL (lv_objectives_cnt, 0) = 0
THEN
fnd_message.set_name ('PER', 'Objectives are mandatory !!');
fnd_message.raise_error;
END IF;
END xxupdate_appraisal_b;
END xxeaa_pms_userhook_pkg;
/
