Home » Applications » Oracle Fusion Apps & E-Business Suite » Update API failing on 'Primary Key' error.. (ORACLE 10grel2, Sun Solaris. )
Update API failing on 'Primary Key' error.. [message #388160] Mon, 23 February 2009 09:50 Go to next message
MrDuke
Messages: 41
Registered: July 2007
Location: Coventry, UK
Member
Im trying to load into ORACLE Financials 1 employee record that includes Assignment details. The script has been working fine with the 2 insert APIs but since Ive added the Hr_Assignment_Api.update_emp_asg_criteria its failing with the error message:

ORA-20001: The primary key specified is invalid
Cause: The primary key values specified are invalid and do not exist in
the schema.
Action: Check the primary key values before attempting to carry out the
operation again.
ORA-06512: at "APPS.HR_ASSIGNMENT_API", line 16009
ORA-06512: at "APPS.HR_ASSIGNMENT_API", line 14644
ORA-06512: at "APPS.HR_ASSIGNMENT_API", line 14429
ORA-06512: at line 232

On googling error message theres a problem with either ""this is the error you get when u pass assignment_id, effective_date and object_version_number parameter wrong.
in the Row handler it checks if the above combination is valid or not. if not valid then it will throw the above error. Make sure the assignment id and object version number you are passing are valid on that effective date. "

Thing is, I cant see where/what the problem is - can anyone out there?
Many thanks
Steven

/* Formatted on 2009/02/23 15:46 (Formatter Plus v4.8.7) */

/*	Module 	: EMPLOYEE LOAD
 *
 *	File 		: TEMPLOYEE_DTLS.sql
 *
 *
 *	Description : This script extracts data from SU_TEMPLOYEE_DETAILS and 
 *			  loads it into the Oracle tables
 *			  per_all_people_f and per_addresses
 *
 *	Assumptions	: Data has been pre-loaded into SU_TEMPLOYEE_DETAILS
 *
 *	Author 	: Steven Longstaff
 *	Version	: 2.0
 *	Created 	: 18-Feb-2009
 *
 *	CHANGE HISTORY.
 *	Vers	Date		Author		Change
 *	-----------------------------------------------------------------------
 * 
 */

--whenever sqlerror exit failure

SET serveroutput ON SIZE 1000000 FORMAT WRAPPED
SET verify OFF
SET feedback OFF

DECLARE
-- *********
-- Debugging/error handling
-- *********
-- *********
-- Work variables
-- *********
   emp_number                       VARCHAR2 (14);
   employee_number                  VARCHAR2 (14);
   v_rec_cnt                        NUMBER                          := 0;
   insert_flag                      VARCHAR2 (8);
   ip_p_address_id                  per_addresses.address_id%TYPE;
   ip_p_object_version_number       NUMBER;
   ip_p_party_id                    per_addresses.party_id%TYPE;
   l_person_id                      NUMBER;
   l_employee_number                VARCHAR2 (35);
   l_validate                       BOOLEAN                     DEFAULT FALSE;
   l_assignment_id                  NUMBER;                        -- := '1';
   l_per_object_version_number      NUMBER;
   l_asg_object_version_number      NUMBER;
   l_per_effective_start_date       DATE;
   l_per_effective_end_date         DATE;
   l_full_name                      VARCHAR2 (240);
   l_per_comment_id                 NUMBER;
   l_assignment_sequence            NUMBER;
   l_assignment_number              VARCHAR2 (35);
   l_name_combination_warning       BOOLEAN                         := FALSE;
   l_assign_payroll_warning         BOOLEAN                         := FALSE;
   l_address_id                     NUMBER;
   l_object_version_number          NUMBER;
   l_date                           DATE;
   -- l_true                           BOOLEAN     := FALSE;
   l_assign_id                      NUMBER;                      -- := 29924;
   l_obj                            NUMBER;
   l_datetrack_update_mode          VARCHAR2 (30)             := 'CORRECTION';
   l_organization_id                NUMBER;
   l_location_id                    NUMBER;                        -- := 101;
   l_job_id                         NUMBER;                          -- := 8;
   l_position_id                    NUMBER;                          -- := 1;
   l_special_ceiling_step_id        NUMBER;
   l_effective_start_date           DATE;
   l_effective_end_date             DATE;
   l_people_group_id                NUMBER;
   l_group_name                     VARCHAR2 (30);
   l_org_now_no_manager_warning     BOOLEAN;
   l_other_manager_warning          BOOLEAN;
   l_spp_delete_warning             BOOLEAN;
   l_entries_changed_warning        VARCHAR2 (30);
   l_tax_district_changed_warning   BOOLEAN;

   -- ***********************************
-- Get employee details info from work table
-- ***********************************
   CURSOR get_employee_details
   IS
      SELECT std_person_id, std_assignment_id, std_validate, std_hire_date,
             std_business_group_id, std_last_name, std_sex,
             std_date_of_birth, std_email_address, std_employee_number,
             std_first_name, std_marital_status, std_middle_names,
             std_nationality, std_title, std_national_identifier,
             std_address_line1, std_address_line2, std_address_line3,
             std_address_line4, std_post_code, std_position_id, std_job_id,
             std_location_id, std_organization_id, std_payroll_id,
             std_grade_id, std_pay_basis_id, std_ass_ovn
        FROM SU_TEMPLOYEE_DETAILS;

-- *****************************************
-- checks employee details info from PER_ALL_PEOPLE_F table
-- *****************************************
   CURSOR c_check_employee (emp_number VARCHAR2)
   IS
      SELECT per.person_id, per.business_group_id, per.last_name,
             per.start_date, per.date_of_birth, per.email_address,
             per.employee_number, per.first_name, per.marital_status,
             per.middle_names, per.nationality, per.national_identifier,
             per.sex, per.title, padd.address_id, padd.primary_flag,
             padd.address_line1, padd.address_line2, padd.address_line3,
             padd.town_or_city, padd.postal_code, padd.telephone_number_1,
             padd.object_version_number per_ovn, paas.assignment_number,
             paas.object_version_number ass_ovn
        FROM per_all_people_f per,
             per_addresses padd,
             per_all_assignments_f paas
       WHERE per.employee_number = emp_number
         AND per.person_id = padd.person_id
         AND paas.person_id = per.person_id;

   emp_rec                          c_check_employee%ROWTYPE;

-- ***************************************************
-- Cursor retrieves latest Object Version Number from per_assignments_f table..
-- ***************************************************
   CURSOR csr_ovn (ovn NUMBER)
   IS
      SELECT MAX (paas.object_version_number)
        FROM per_assignments_f paas, per_all_people_f per
       WHERE paas.person_id = per.person_id
         AND paas.assignment_number = l_assignment_number
         AND per.employee_number = paas.assignment_number
         AND paas.object_version_number = ovn;
BEGIN
-- ***********************************
-- Process each record in the work table
-- ***********************************
   FOR v_emp IN get_employee_details
   LOOP
-- ************************************
-- determine whether customer already exists
-- ************************************
      OPEN c_check_employee (v_emp.std_employee_number);

      FETCH c_check_employee
       INTO emp_rec;

      IF c_check_employee%NOTFOUND
      THEN
         insert_flag := 'I';
      ELSE
         insert_flag := 'X';
      END IF;

      CLOSE c_check_employee;

      OPEN csr_ovn (v_emp.std_ass_ovn);

      FETCH csr_ovn
       INTO l_obj;

      CLOSE csr_ovn;

-- ***********************************
-- Create new PER_ALL_PEOPLE_F and PER_ADDRESSES record from
--            info in  table record
-- ***********************************
      IF insert_flag = 'I'
      THEN
         BEGIN                             -- Importing Employee Procedure --
            DBMS_OUTPUT.PUT (CHR (10));
            DBMS_OUTPUT.PUT_LINE ('Importing employees....  ');

            BEGIN
               Hr_Employee_Api.create_gb_employee
                  (p_validate                       => l_validate,
                   p_hire_date                      => v_emp.std_hire_date,
                   p_business_group_id              => v_emp.std_business_group_id,
                   p_date_of_birth                  => v_emp.std_date_of_birth,
                   p_email_address                  => v_emp.std_email_address,
                   p_first_name                     => v_emp.std_first_name,
                   p_middle_names                   => v_emp.std_middle_names,
                   p_last_name                      => v_emp.std_last_name,
                   p_sex                            => v_emp.std_sex,
                   p_ni_number                      => v_emp.std_national_identifier,
                   p_employee_number                => v_emp.std_employee_number,
                   p_person_id                      => l_person_id,
                   p_title                          => v_emp.std_title,
                   p_assignment_id                  => l_assignment_id,
                   p_per_object_version_number      => l_per_object_version_number,
                   p_asg_object_version_number      => l_asg_object_version_number,
                   p_per_effective_start_date       => l_per_effective_start_date,
                   p_per_effective_end_date         => l_per_effective_end_date,
                   p_full_name                      => l_full_name,
                   p_per_comment_id                 => l_per_comment_id,
                   p_assignment_sequence            => l_assignment_sequence,
                                                                      --IN/OUT
                   p_assignment_number              => l_assignment_number,
                                                                      --IN/OUT
                   p_name_combination_warning       => l_name_combination_warning,
                                                                      --IN/OUT
                   p_assign_payroll_warning         => l_assign_payroll_warning
                                                                      --IN/OUT
                  );
               Hr_Person_Address_Api.create_person_address
                        (p_validate                     => l_validate,
--                      p_effective_date               => v_emp.std_hire_date,
                         p_effective_date               => SYSDATE,
                         p_pradd_ovlapval_override      => NULL,
                         p_validate_county              => NULL,
                         p_person_id                    => l_person_id,
                         p_primary_flag                 => 'Y',
                         p_style                        => 'GB_GLB',
--                      p_date_from                    => v_emp.std_hire_date,
                         p_date_from                    => SYSDATE,
                         p_date_to                      => NULL,
                         p_address_type                 => NULL,
                         p_comments                     => NULL,
                         p_address_line1                => v_emp.std_address_line1,
                         p_address_line2                => v_emp.std_address_line2,
                         p_address_line3                => v_emp.std_address_line3,
                         p_town_or_city                 => v_emp.std_address_line4,
                         p_region_1                     => NULL,
                         p_region_2                     => NULL,
                         p_region_3                     => NULL,
                         p_postal_code                  => v_emp.std_post_code,
                         p_country                      => v_emp.std_nationality,
                         p_telephone_number_1           => NULL,
                         p_telephone_number_2           => NULL,
                         p_telephone_number_3           => NULL,
                         p_addr_attribute_category      => NULL,
                         p_addr_attribute1              => NULL,
                         p_addr_attribute2              => NULL,
                         p_addr_attribute3              => NULL,
                         p_addr_attribute4              => NULL,
                         p_addr_attribute5              => NULL,
                         p_addr_attribute6              => NULL,
                         p_addr_attribute7              => NULL,
                         p_addr_attribute8              => NULL,
                         p_addr_attribute9              => NULL,
                         p_addr_attribute10             => NULL,
                         p_addr_attribute11             => NULL,
                         p_addr_attribute12             => NULL,
                         p_addr_attribute13             => NULL,
                         p_addr_attribute14             => NULL,
                         p_addr_attribute15             => NULL,
                         p_addr_attribute16             => NULL,
                         p_addr_attribute17             => NULL,
                         p_addr_attribute18             => NULL,
                         p_addr_attribute19             => NULL,
                         p_addr_attribute20             => NULL,
                         p_add_information13            => NULL,
                         p_add_information14            => NULL,
                         p_add_information15            => NULL,
                         p_add_information16            => NULL,
                         p_add_information17            => NULL,
                         p_add_information18            => NULL,
                         p_add_information19            => NULL,
                         p_add_information20            => NULL,
                         p_party_id                     => ip_p_party_id,
                         p_address_id                   => ip_p_address_id,
                         p_object_version_number        => ip_p_object_version_number
                        );
               Hr_Assignment_Api.update_emp_asg_criteria
                  (p_validate                          => l_validate,
                   p_effective_date                    => l_per_effective_start_date,
                   p_datetrack_update_mode             => l_datetrack_update_mode,
                   p_assignment_id                     => l_assignment_id,
                   p_object_version_number             => l_obj,
                   p_organization_id                   => v_emp.std_organization_id,
                   p_location_id                       => v_emp.std_location_id,
                   p_job_id                            => v_emp.std_job_id,
                   p_position_id                       => v_emp.std_position_id,
                   p_special_ceiling_step_id           => l_special_ceiling_step_id,
                   p_effective_start_date              => l_per_effective_start_date,
                                                                      --IN/OUT
                   p_effective_end_date                => l_effective_end_date,
                                                                      --IN/OUT
                   p_people_group_id                   => l_people_group_id,
                   p_group_name                        => l_group_name,
                   p_org_now_no_manager_warning        => l_org_now_no_manager_warning,
                                                                      --IN/OUT
                   p_other_manager_warning             => l_other_manager_warning,
                                                                      --IN/OUT
                   p_spp_delete_warning                => l_spp_delete_warning,
                                                                      --IN/OUT
                   p_entries_changed_warning           => l_entries_changed_warning,
                                                                      --IN/OUT
                   p_tax_district_changed_warning      => l_tax_district_changed_warning
                                                                      --IN/OUT
                  );
               DBMS_OUTPUT.PUT_LINE (   'Employee No:'
                                     || v_emp.std_employee_number
                                     || '  imported successfully..'
                                    );
/*     EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.PUT (CHR (10));
                  DBMS_OUTPUT.PUT_LINE (   'Ah, Employee:'
                                        || v_emp.std_employee_number
                                        || ' failed to load.. '
                                        || SQLERRM
                                       );
                  DBMS_OUTPUT.PUT (CHR (10));
                  DBMS_OUTPUT.PUT (CHR (10));
--                  DBMS_OUTPUT.PUT_LINE ('..as its failed, its goin in the ERRORS records table..'  );
   --               DBMS_OUTPUT.PUT (CHR (10));
      --            DBMS_OUTPUT.PUT (CHR (10)); */
            END;
         END;

         DBMS_OUTPUT.PUT (CHR (10));
         v_rec_cnt := v_rec_cnt + 1;
         DBMS_OUTPUT.PUT_LINE (   'There were '
                               || v_rec_cnt
                               || '  records read in..'
                              );
         DBMS_OUTPUT.PUT (CHR (10));
-- ******************************
-- End of customer related details
-- ******************************
      END IF;
   END LOOP;

   COMMIT;
END;
/

EXIT;
Re: Update API failing on 'Primary Key' error.. [message #388266 is a reply to message #388160] Tue, 24 February 2009 01:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Moving this to E-Business Suite forum; looks like a functional error to me, not something (technically) related to an error in (PL/)SQL.
Re: Update API failing on 'Primary Key' error.. [message #388295 is a reply to message #388266] Tue, 24 February 2009 02:54 Go to previous message
MrDuke
Messages: 41
Registered: July 2007
Location: Coventry, UK
Member
well - if you think so, but its the script thats at fault, the problem lies within the script before it gets to the application.
Previous Topic: ACCOUNT PAYABLES
Next Topic: Depot Repair & Order Management
Goto Forum:
  


Current Time: Thu Dec 08 02:03:13 CST 2016

Total time taken to generate the page: 0.12655 seconds