PMS Objectives Mandatory R12 User Hook Implementation Steps

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;
/