11 Replies Latest reply: Jul 29, 2011 3:30 PM by GauravSehgal RSS

    ORA-20001: FLEX-ID DOES NOT EXIST:

    828721
      hello guys,i have this problem and i need the help of an expert,im trying to update the assignment records of some employees on oracle hrms using the apps.hr_assignment_api.update_emp_asg_criteria api. after running in validate mode i get this error ORA-20001: FLEX-ID DOES NOT EXIST:

      could any of you please help me out here

      Thanks
        • 1. Re: ORA-20001: FLEX-ID DOES NOT EXIST:
          Srini Chavali-Oracle
          Pl post details of OS, database and Ebs versions, along with the complete error message and the code you are executing.

          HTH
          Srini
          • 2. Re: ORA-20001: FLEX-ID DOES NOT EXIST:
            828721
            Thanks Srini the os is IBM AIX 5.3,Database is 10.1,and the Ebs is 11.5.10.2 and the error message is the title of this post ie ORA-20001: FLEX-ID DOES NOT EXIST:

            and below is the code i was trying to run


            CREATE OR REPLACE PROCEDURE APPS.UPDATE_ASSIGN_JAN11(low_line_id IN NUMBER, hi_line_ID IN NUMBER) AS
                 l_object_version_number number;
                 l_total_records number;
                 l_success_records number:=0;
                 l_failure_records number:=0;
                 l_flag char:='Y';
                 l_mess varchar2(1000);
                 l_datetrackmode varchar2(30);
                 l_soft_coding_keyflex_id number;
                 l_concatenated_segments varchar2(100);
                 l_cagr_concatenated_segments varchar2(100);
                 l_cagr_grade_def_id number;
                 l_comment_id number;
                 l_effective_start_date date;
                 l_effective_end_date date;
                 l_no_manager_warning boolean;
            l_employee_type varchar2(80);
            l_other_manager_warning boolean;
                 l_hourly_salaried_warning boolean;
                 l_people_group_id number;
                 l_special_ceiling_step_id number;
                 l_group_name varchar2(100);
                 l_effective_start_date1 date;
                 l_effective_end_date1 date;
                 l_org_now_no_manager_warning boolean;
                 l_other_manager_warning1 boolean;
                 l_spp_delete_warning boolean;
                 l_entries_changed_warning varchar2(1000);
                 l_tax_district_changed_warning boolean;
                 l_date_of_hire date;
                 /* Cursor to get records from the temporary table */
                 CURSOR loc IS
                 SELECT *
            FROM APIREPORTS.ASSIGN_UPDATE_JAN11
            WHERE STEP_IDS_REC_STATUS = 'STEP AND PARENT IDS OK'
            AND LINE_ID BETWEEN low_line_id AND hi_line_ID;
            BEGIN
            BEGIN
            APPS.fnd_global.apps_initialize(1541,50001,800);
            END;
            SELECT COUNT(*) INTO l_total_records
            FROM APIREPORTS.ASSIGN_UPDATE_JAN11
            WHERE STEP_IDS_REC_STATUS = 'STEP AND PARENT IDS OK'
            AND LINE_ID BETWEEN low_line_id AND hi_line_ID;
                 DBMS_OUTPUT.PUT_LINE('#############################################################');
                 DBMS_OUTPUT.PUT_LINE('Update of ASIGNMENT OF Employees');
                 DBMS_OUTPUT.PUT_LINE('#############################################################');
                 DBMS_OUTPUT.PUT_LINE('Start Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
                 FOR i IN loc
                 LOOP
                      l_object_version_number:=null;
                      l_soft_coding_keyflex_id :=null;
                      l_concatenated_segments     :=null;
                      l_cagr_concatenated_segments :=null;
                      l_cagr_grade_def_id :=null;
                      l_comment_id :=null;
                      l_effective_start_date :=null;
                      l_effective_end_date :=null;
                      l_no_manager_warning :=null;
                      l_other_manager_warning :=null;
                      l_hourly_salaried_warning :=null;
                      l_people_group_id :=null;
                      l_special_ceiling_step_id :=null;
                      l_group_name :=null;
                      l_effective_start_date1 :=null;
                      l_effective_end_date1 :=null;
                      l_org_now_no_manager_warning :=null;
                      l_other_manager_warning1 :=null;
                      l_spp_delete_warning :=null;
                      l_entries_changed_warning :=null;
                      l_tax_district_changed_warning :=null;
                      l_mess := null;
                      /*For getting the OVN*/
                      /* This section is necessary since the object version number changes anytime the table is updated */
                      BEGIN
                      SELECT
            object_version_number, effective_start_date
            INTO
            l_object_version_number, l_effective_start_date
            FROM
            apps.per_all_assignments_f
                      WHERE
            ASSIGNMENT_ID =i.ASSIGNMENT_ID
                      AND
            TO_DATE(i.HIRE_DATE,'DD-MM-YYYY') BETWEEN effective_start_date AND effective_end_date;     
                      EXCEPTION WHEN OTHERS THEN
                      UPDATE APIREPORTS.ASSIGN_UPDATE_JAN11
                           SET UPDATE_ASSIGNMENT_STATUS ='Error',UPDATE_ERROR_MESSAGE='Problem with Object Version Number'
                 WHERE line_id=i.line_id;
            commit;
                      END;
                      IF l_object_version_number IS NOT NULL THEN
                 /* The API is called with the relevent parameters */
                      BEGIN
                           apps.hr_assignment_api.update_emp_asg_criteria
                           (
                           p_validate               => TRUE
                           ,p_effective_date          => TO_DATE(i.HIRE_DATE,'DD-MM-YYYY') -- to_date(to_char((to_date(i.GRADE_START_DATE,'DDMMYYYY')),'DD-MON-YYYY'))
                           ,p_datetrack_update_mode     => 'UPDATE'
                           ,p_assignment_id           => i.ASSIGNMENT_ID
                           ,p_object_version_number     => l_object_version_number
                           ,p_organization_id           => i.ORGANISATION_ID
                           ,p_grade_id               => i.GRADE_ID
                      --     ,p_pay_basis_id               => i.salary_basis_id     
                           ,p_segment1               => i.MINISTRY
                           ,p_segment2               => i.DEPARTMENT
                           ,p_segment3               => i.AGENCY
                      --     ,p_segment4               => i.SECTIONS_EDUCATIONAL_UNITS
                           ,p_segment5               => i.REGION
                           ,p_segment6               => i.DISTRICT
                      --     ,p_segment7               => i.CIRCUITS
                      --     ,p_segment8               => i.OCCUPATIONAL_GROUP
                      --     ,p_segment9               => i.EMPLOYEE_TYPE
                      --     ,p_segment10               => i.ORGANIZATION_GROUP
                      --     ,p_segment11               => i.EMPLOYEE_STATUS
                           ,p_segment12               => i.NEW_SALARY_STRUCTURE
                      -- ,p_position_id               => i.position_id
                           ,p_job_id               => i.JOB_ID
                           ,p_payroll_id               => i.PAYROLL_ID
                           ,p_location_id               => i.LOCATION_ID
                           ,p_employment_category     => i.NEW_EMP_CATEGORY     
                           ,p_people_group_id => l_people_group_id      
                           ,p_special_ceiling_step_id      => l_special_ceiling_step_id
                           ,p_group_name      => l_group_name
                           ,p_effective_start_date      => l_effective_start_date1
                           ,p_effective_end_date      => l_effective_end_date1
                           ,p_org_now_no_manager_warning     => l_org_now_no_manager_warning
                           ,p_other_manager_warning => l_other_manager_warning1
                           ,p_spp_delete_warning => l_spp_delete_warning
                           ,p_entries_changed_warning => l_entries_changed_warning
                           ,p_tax_district_changed_warning => l_tax_district_changed_warning
                           );
                      --     l_success_records:=l_success_records+1;
                           UPDATE
                                APIREPORTS.KNUST_ASSIGN_UPDATE_JAN11
                           SET
                                UPDATE_ASSIGNMENT_STATUS ='YES', UPDATE_ERROR_MESSAGE = l_mess
                 WHERE
                                line_id=i.line_id;
                           COMMIT;
                 EXCEPTION
                 WHEN OTHERS THEN
                 rollback;
                 l_flag:='N';
                 l_mess:=SQLERRM;
                 l_failure_records:=l_failure_records+1;
                 UPDATE
            APIREPORTS.ASSIGN_UPDATE_JAN11
                           SET
            UPDATE_ASSIGNMENT_STATUS ='Error',
            UPDATE_ERROR_MESSAGE=substr(l_mess,1,1000)
                 WHERE line_id=i.line_id;
            commit;
                 END;
                      END IF;
                 END LOOP;
                 /* Commiting to database only if all records are successful*/
                 IF l_flag='Y' THEN
                 dbms_output.put_line('Commit');
                 commit;
            ELSE
                 dbms_output.put_line('Rollback');
                 rollback;
                 commit;
            END IF;
                 COMMIT;
                      l_success_records := l_total_records - l_failure_records;
                 DBMS_OUTPUT.PUT_LINE('End Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
                 DBMS_OUTPUT.PUT_LINE('#############################################################');
                 DBMS_OUTPUT.PUT_LINE('Total Records To Be Loaded: ' || l_total_records);
                 DBMS_OUTPUT.PUT_LINE('Total Success Records : ' || l_success_records);
                 DBMS_OUTPUT.PUT_LINE('Total Failure Records : ' || l_failure_records);
                 DBMS_OUTPUT.PUT_LINE('#############################################################');
            END UPDATE_ASSIGN_JAN11;
            • 3. Re: ORA-20001: FLEX-ID DOES NOT EXIST:
              Srini Chavali-Oracle
              I am no expert in this area, but I believe you also need to insert a row into FND_SESSIONS table before calling the API. See this related MOS Doc

              MOS Doc 353257.1 - Hr_person_absence_api.Create_person_absence Fails with ORA-20001: FLEX-ID DOES NOT EXIST

              HTH
              Srini
              • 4. Re: ORA-20001: FLEX-ID DOES NOT EXIST:
                Hussein Sawwan-Oracle
                Please see if these docs help.

                ORA-20001 Error Using HR_ASSIGNMENT_API [ID 114670.1]
                Getting "ORA-20001: The primary key specified is invalid" Error Using HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA API [ID 737145.1]
                Error: ORA-20001 When Trying To HIRE/RE-HIRE Eemployees Using HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA API [ID 1057054.1]
                Error When Updating Assignment Information For Terminated/Ex-Employees Using 'Hr_Assignment_Api.Update_Emp_Asg_Criteria' [ID 986557.1]
                ASSIGNMENT CATEGORY: ORA-20001: An Assignment with Status TERM_ASSIGN Cannot Have Any Other Attributes Updated [ID 1077124.1]

                Thanks,
                Hussein
                • 5. Re: ORA-20001: FLEX-ID DOES NOT EXIST:
                  828721
                  thanks Hussien its given me clues on how to linvestigate the error
                  • 6. Re: ORA-20001: FLEX-ID DOES NOT EXIST:
                    828721
                    hi guys i included this line in my ma code

                    APPS.HR_UTILITY.TRACE_ON;
                    APPS.HR_UTILITY.SET_TRACE_OPTIONS(UPPER('TRACE_DEST:DBMS_OUTPUT'));

                    and run the script again and this is the results i had ,could anyone interpret what it says for me ..thanks

                    Start Time : 17-JAN-2011 10:27:52
                    Entering: HR_ASSIGNMENT_BK3.UPDATE_EMP_ASG_CRITERIA_B 10
                    hr_api.validate_commit_unit 10
                    Leaving: HR_ASSIGNMENT_BK3.UPDATE_EMP_ASG_CRITERIA_B 20
                    Entering hr_kflex_utility set_profiles 5
                    hr_kflex_utility set_profiles 10
                    hr_kflex_utility set_profiles 20
                    hr_kflex_utility set_profiles 30
                    hr_kflex_utility set_profiles 40
                    hr_kflex_utility set_profiles 50
                    Leaving hr_kflex_utility set_profiles 100
                    Entering hr_kflex_utility set_session_date 5
                    hr_kflex_utility set_session_date 30
                    Leaving hr_kflex_utility set_session_date 100
                    Entering:upd_or_sel_keyflex_comb 10
                    upd_or_sel_keyflex_comb 20
                    Entering:check_ignore_varray 10
                    Seg3 KNUST 101
                    Entering:ins_or_sel_keyflex_comb 10
                    ins_or_sel_keyflex_comb 20
                    Entering:check_ignore_varray 10
                    ins_or_sel_keyflex_comb 30
                    Entering:sort_segment_order 10
                    sort_segment_order 20
                    sort_segment_order 30
                    sort_segment_order 40
                    sort_segment_order 50
                    Leaving:sort_segment_order 60
                    ins_or_sel_keyflex_comb 40
                    ins_or_sel_keyflex_comb 50
                    Entering:check_pop_segments 10
                    Entering:get_no_segments 10
                    check_pop_segments 20
                    check_pop_segments 30
                    ins_or_sel_keyflex_comb 60
                    Entering:check_segment_combination 10
                    Entering:get_no_segments 10
                    check_segment_combination 20
                    check_segment_combination 30
                    This is the seg val21 100
                    This is the seg val4 100
                    This is the seg val5 100
                    This is the seg val 100
                    This is the seg val7 100
                    This is the seg val6 100
                    This is the seg val 100
                    This is the seg val 100
                    This is the seg val17 100
                    This is the seg val 100
                    This is the seg val 100
                    This is the seg val7 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    This is the seg val9 100
                    check_segment_combination 45
                    =>Ministry of Education.NCTE.KNUST..Ashanti.Kumasi...Established Staff..48
                    check_segment_combination 50
                    check_segment_combination 53
                    check_segment_combination 80
                    check_segment_combination 81
                    check_segment_combination 82
                    check_segment_combination 100
                    ins_or_sel_keyflex_comb 100
                    Leaving upd_or_sel_keyflex_comb 140
                    Rollback
                    End Time : 17-JAN-2011 10:27:53
                    #############################################################
                    Total Records To Be Loaded: 1
                    Total Success Records : 0
                    Total Failure Records : 1
                    #############################################################

                    PL/SQL procedure successfully completed.

                    Edited by: PVSTYKS on Jan 17, 2011 2:52 AM
                    • 7. Re: ORA-20001: FLEX-ID DOES NOT EXIST:
                      Duncan Casemore
                      This trace tells us that the code reached the Exceptions block defined in hr_kflex_utility.ins_or_sel_keyflex_comb. It also tells us exactly where the code failed, which was in the check_segment_combination procedure at location 100. In here, some other flexfield error occurred that caused the flexfield to fail.

                      The thing to do here is to check over your flexfield:

                      1) Check it's enabled and compiled
                      2) Check you can enter the same through the form
                      3) Try initializing your session before calling the API by:
                      a) calling fnd_global.apps_initialize(<user_id>, <resp_id>, <resp_appl_id>, <sg_id>); -- This will ensure any profile options used in the Flexfield Value Sets are initialized
                      b) Ensure a row in fnd_sessions is defined (as per earlier post). This will ensure that date-tracked views return a row.
                      • 8. Re: ORA-20001: FLEX-ID DOES NOT EXIST:
                        Stalin Gnanasekaran
                        Hi,

                        If you are using Profiles like $person_id or user_person_type then during API call it wont consider those validation.

                        Example i had issue. I created the DFF with the table based value set which return, employee date of birth + 60 years. I have used the person id in table value set.

                        It was not accepting it. then i removed it. it was working fine.

                        Regards
                        Stalin G
                        • 9. Re: ORA-20001: FLEX-ID DOES NOT EXIST:
                          user11995547
                          The issue might have to do with the fact the validation sets maybe of type 'table validated'. These type always cause problems with key segments. The most pragmatic way to go is to remove the flexfield, do the job and attach them.
                          • 10. Re: ORA-20001: FLEX-ID DOES NOT EXIST:
                            Ora2011
                            The best way to debug the assignment api is to comment the flexfield values one by one and check the code.
                            the flexfield Job id , prople group id , grade id .

                            start with only with field required to run the api and then go ahead to add the fields . this will take time but surely you will pin point the error object.
                            I have faced this issue maximum times with people group id not passed correctly. like if you are creating the people group and dynamic insert check box is not checked then this error is populated .
                            • 11. Re: ORA-20001: FLEX-ID DOES NOT EXIST:
                              GauravSehgal
                              As the error message states, error is because of segment values you are passing. See if all the segment values you are passing are in the value sets or not. Otherwise comments segments one by one and see for which segment you are getting the error