1 Reply Latest reply: Oct 30, 2012 5:11 AM by Ronald Anthony RSS

    USerhook in HR_APPRAISALS_BK2.Update_appraisal_B throwing Error oracle.jbo.

    user13277716
      Hi All,

      The USerhook in the HR_APPRAISALS_BK2.Update_appraisal_B throwing Error "oracle.jbo.RemoveWithDetailsException: JBO-26019: Attempting to remove a parent entity without removing all children entities"
      We wrote a custom logic to throw error on the application in the Update Apprisals Page.
      The custom package is placed in the HR_APPRAISALS_BK2.Update_appraisal_B procedure

      create or replace PACKAGE BODY XX_APPR_HOOK_PKG
      PROCEDURE XX_APPR_HOOK_PRC (p_appraisal_id IN NUMBER)
      AS
      CURSOR APPRAISAL_DETAIL
      IS
      SELECT person_id
      FROM per_all_assignments_f
      WHERE supervisor_id = (SELECT APPRAISEE_PERSON_ID
      FROM PER_APPRAISALS
      WHERE APPRAISAL_ID = p_appraisal_id)
      and trunc(sysdate) between effective_start_date and effective_end_date;

      l_message VARCHAR2(3000) ;
      APPID NUMBER (30);
      APPSTATUS VARCHAR2 (30);
      l_names VARCHAR2(200) ;
      l_msg varchar2(500);
      l_cntr number:=0;

      BEGIN

      l_message :='Kindly close the appraisal for the following employees ';

      debug('1Hello1');
      debug('2Hello1'||p_appraisal_id);
      FOR J IN APPRAISAL_DETAIL
      LOOP
      debug('3Hello Inside loop'||J.PERSON_ID);
      APPSTATUS := null;

      BEGIN
      SELECT MAX (APPRAISAL_ID)
      INTO APPID
      FROM PER_APPRAISALS
      WHERE APPRAISEE_PERSON_ID = J.PERSON_ID;
      EXCEPTION
      WHEN OTHERS THEN
      APPID :=null;
      END;
      debug('4Hello after APPID'||J.PERSON_ID||'_'||APPID);
      BEGIN
      SELECT APPRAISAL_SYSTEM_STATUS
      INTO APPSTATUS
      FROM PER_APPRAISALS
      WHERE appraisal_id = APPID;
      EXCEPTION
      WHEN OTHERS THEN
      APPSTATUS:=null;
      END;
      debug('5Hello after APPSTATUS_'||J.PERSON_ID||APPSTATUS);
      BEGIN
      select (last_name ||' '|| first_name) employee_name,employee_number
      into l_emp_name,l_emp_number
      from per_all_people_f where person_id = J.PERSON_ID
      and trunc(sysdate) between effective_start_date and effective_end_date ;
      EXCEPTION
      WHEN OTHERS THEN
      l_emp_name :=null;
      l_emp_number:=null;
      END;
      debug('6Hello after l_emp_name'||J.PERSON_ID||l_emp_number);
      IF (APPSTATUS <> 'COMPLETED' or APPSTATUS is null)
      THEN
      l_cntr :=l_cntr +1;
      l_msg := l_msg ||l_cntr||') '||l_emp_name||' '||l_emp_number||' ';
      END IF;
      END LOOP;

      IF (l_cntr<>0 ) THEN
      debug('9_Inside l_cntr<>0 ');
      hr_utility.set_message(800, 'XX_APPRAISAL_USER_HOOK_MSG');
      hr_utility.set_message_token( 'XX_TOKEN',l_msg);
      hr_utility.raise_error;
      END IF;
      End XX_APPR_HOOK_PRC;
      END 'XX_APPR_HOOK_PKG;
      And ran the pre-processor,but still recieving the same error.
      Using the Debug procedure,I am inserting the debugmessages into table.
      It is inserting all the debug messages correctly.
      If I delete the api hook call using hr_api_hook_call_api.delete_api_hook_call, and then do the transaction it is not dispaying any error.
      but once i create the userhook using below code, it is displaying the error "oracle.jbo.RemoveWithDetailsException: JBO-26019: Attempting to remove a parent entity without removing all children entities"
      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('01-jan-2000','dd-mon-yyyy'),
      p_api_hook_id => 2925,
      p_api_hook_call_type => 'PP',
      p_sequence => 3000,
      p_enabled_flag => 'Y',
      p_call_package =>'XX_APPR_HOOK_PKG',
      p_call_procedure => 'XX_APPR_HOOK_PRC',
      p_api_hook_call_id => l_api_hook_call_id,
      p_object_version_number => l_object_version_number);
      COMMIT;
      end;

      Please let me know how to resolves this issue.

      Thanks,
      Anuradha