Home » Applications » Oracle Fusion Apps & E-Business Suite » Loading Employee records via APIs - business group id failure.. (Financials 11i, ORACLE 10G...)
| Loading Employee records via APIs - business group id failure.. [message #383340] |
Wed, 28 January 2009 06:23  |
MrDuke
Messages: 41
Registered: July 2007
Location: Coventry, UK
|
Member |
|
|
hi all.
Im trying to load a set of employees information from my temporary table via 2 APIs in a PLSQL script to
update per_all_people_f and Per_addresses tables.
Record failed to load.. ORA-20001: The business group specified is invalid
Cause: You have specified an incorrect business group identifier or name.
Action: Correct the business group value or call your local support representative.
ALL our 'business group identifier' fields are set to zero in per_all_people_f table and this what I've setup
for my test records in my temp table. Metalink has suggestions of checking 2 profile options..
Have you checked the HR:Security and HR:Business Group Profile Options? – I have checked, and we have
never setup one, the entry in the profile option is 'Setup Business Group' rather than any number. At SITE
level.... When there isn’t one setup is Zero allowed I wonder for adding new records?
Has anybody else had problems with business groups when loading employee information..?
many thanks.. Steven
|
|
|
|
| Re: Loading Employee records via APIs - business group id failure.. [message #383358 is a reply to message #383340] |
Wed, 28 January 2009 08:24  |
MrDuke
Messages: 41
Registered: July 2007
Location: Coventry, UK
|
Member |
|
|
seem to have got rid of that error by doing:
p_business_group_id NUMBER := 0;
in the variables list, but this has led to:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1
..of suggestions found: "1) See if the out of your function is at the same size of the fields that are receiving it." - how do I do that?
============== code ==================
/* Formatted on 2009/01/26 15:02 (Formatter Plus v4.8.7) */
/* File : TEMPLOYEE_DTLS.sql
*
* Called By : TEMPLOYEE_DTLS.sh
*
* Description : This script extracts data from SU_TEMPLOYEE_DETAILS and
* loads it directly into the Oracle tables [via HRMS's
* API packages..]
*
* Assumptions : Data has been pre-loaded into
* SU_TEMPLOYEE_DETAILS temp table with records from CHRIS21
* Author : Steven Longstaff
* Version : 1.0
* Created : 22-Jan-2009
*
* CHANGE HISTORY.
* Vers Date Author Change
* -----------------------------------------------------------------------
*
*/
--whenever sqlerror exit failure
SET serveroutput ON SIZE 1000000
SET verify OFF
SET feedback OFF
DECLARE
-- *********
-- Debugging/error handling
-- *********
v_err_seq NUMBER := 0;
v_err_num VARCHAR2 (30);
v_err_msg VARCHAR2 (250);
v_err_line VARCHAR2 (350);
-- *********
-- Work variables
-- *********
p_business_group_id NUMBER := 0;
p_person_id NUMBER := 0;
p_address_line1 VARCHAR2 (35);
p_date_of_birth VARCHAR2 (12);
p_address_line2 VARCHAR2 (35);
employee_number VARCHAR2 (14);
p_employee_number VARCHAR2 (14);
emp_number VARCHAR2 (14);
p_email_address VARCHAR2 (25);
p_address_line3 VARCHAR2 (20);
p_first_name VARCHAR2 (25);
p_address_line4 VARCHAR2 (20);
p_middle_names VARCHAR2 (20);
p_post_code VARCHAR2 (10);
p_last_name VARCHAR2 (22);
p_nationality VARCHAR2 (10);
p_sex VARCHAR2 (2);
p_national_identifier VARCHAR2 (13);
p_title VARCHAR2 (6);
v_rec_cnt NUMBER := 0;
insert_flag VARCHAR2 (1);
-- ip_p_address_id NUMBER;
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 per_all_people_f.person_id%type;
l_employee_number VARCHAR2 (14);
l_validate BOOLEAN DEFAULT FALSE;
l_assignment_id NUMBER;
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 (40);
l_per_comment_id NUMBER;
l_assignment_sequence NUMBER;
l_assignment_number VARCHAR2 (20);
l_name_combination_warning BOOLEAN;
l_assign_payroll_warning BOOLEAN;
l_address_id NUMBER;
l_object_version_number NUMBER;
return_code NUMBER;
return_message VARCHAR2 (2000);
p_hire_date DATE;
-- ***********************************
-- Get employee details info from work table
-- ***********************************
CURSOR get_employee_details
IS
SELECT p_person_id, p_validate, p_hire_date, p_business_group_id, p_last_name,
p_sex, p_date_of_birth, p_email_address, p_employee_number,
p_first_name, p_marital_status, p_middle_names, p_nationality,
p_title, p_national_identifier, p_address_line1,
p_address_line2, p_address_line3, p_address_line4, p_post_code
FROM SU_TEMPLOYEE_DETAILS;
-- *****************************************
-- checks employee details info from PER_ALL_PEOPLE_F table
-- *****************************************
-- v_err_seq := 1;
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
FROM per_all_people_f per, per_addresses padd
WHERE per.employee_number = emp_number
AND per.person_id = padd.person_id;
emp_rec c_check_employee%ROWTYPE;
BEGIN
--v_err_seq := 2;
-- ***********************************
-- Process each record in the work table
-- ***********************************
FOR v_emp IN get_employee_details
LOOP
v_rec_cnt := v_rec_cnt + 1;
-- ************************************
-- determine whether customer already exists
-- ************************************
OPEN c_check_employee (v_emp.p_employee_number);
FETCH c_check_employee
INTO emp_rec;
IF c_check_employee%NOTFOUND
THEN
insert_flag := 'I';
ELSE
insert_flag := 'X';
END IF;
IF insert_flag = 'I'
THEN
-- RETURN 'Employee does not exist, continue import..';
DBMS_OUTPUT.PUT_LINE ('Employee does not exist, continue import..');
ELSE
-- RETURN 'Employee found - record cannot be imported.');
DBMS_OUTPUT.PUT_LINE ('Employee found - record cannot be imported.');
END IF;
CLOSE c_check_employee;
-- v_err_seq := 3;
-- ***********************************
-- 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_LINE (' ');
DBMS_OUTPUT.PUT_LINE (' ');
BEGIN
Hr_Employee_Api.create_gb_employee
(p_validate => l_validate, --FALSE,
p_hire_date => p_hire_date,
p_business_group_id => p_business_group_id,
p_date_of_birth => p_date_of_birth,
p_email_address => p_email_address,
p_first_name => p_first_name,
p_middle_names => p_middle_names,
p_last_name => p_last_name,
p_sex => p_sex,
p_ni_number => p_national_identifier,
p_employee_number => l_employee_number,
p_person_id => l_person_id,
p_title => p_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,
p_assignment_number => l_assignment_number,
p_name_combination_warning => l_name_combination_warning,
p_assign_payroll_warning => l_assign_payroll_warning
);
DBMS_OUTPUT.PUT_LINE ('..employee record updated succesfully..');
DBMS_OUTPUT.PUT_LINE (' ');
DBMS_OUTPUT.PUT_LINE (' ');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('..SQLCodeErrors:- ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE (' ');
DBMS_OUTPUT.PUT_LINE ('Record failed to load.. ' || SQLERRM);
END;
BEGIN -- Importing Associated Address Procedure --
DBMS_OUTPUT.PUT_LINE (' ');
DBMS_OUTPUT.PUT_LINE
('..and the associated employee address....');
DBMS_OUTPUT.PUT_LINE (' ');
DBMS_OUTPUT.PUT_LINE (' ');
Hr_Person_Address_Api.create_person_address
(p_validate => l_validate,
-- p_effective_date => p_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 => p_hire_date,
p_date_from => SYSDATE,
p_date_to => NULL,
p_address_type => NULL,
p_comments => NULL,
p_address_line1 => p_address_line1,
p_address_line2 => p_address_line2,
p_address_line3 => p_address_line3,
p_town_or_city => p_address_line4,
p_region_1 => NULL,
p_region_2 => NULL,
p_region_3 => NULL,
p_postal_code => p_post_code,
p_country => p_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 => NULL,
p_party_id => ip_p_party_id,
p_address_id => ip_p_address_id,
p_object_version_number => ip_p_object_version_number
);
DBMS_OUTPUT.PUT_LINE
('Address Updation/Insertion has been successful!');
DBMS_OUTPUT.PUT_LINE (' ');
DBMS_OUTPUT.PUT_LINE (' ');
DBMS_OUTPUT.PUT_LINE (' ');
END;
END;
-- v_err_seq := 4;
-- ******************************
-- End of customer related details
-- ******************************
-- ******************************
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Records read : ' || v_rec_cnt);
v_err_seq := 5;
--EXCEPTION
-- WHEN OTHERS THEN
-- ROLLBACK;
-- Output Error Message
-- v_err_num := TO_CHAR(SQLCODE);
-- v_err_msg := SUBSTR(SQLERRM,1,250);
-- v_err_line := 'Oracle error (seqno=' || v_err_seq || ') ' ||
-- v_err_num ||' occurred processing record '||
-- TO_CHAR(v_rec_cnt + 1) ||' : '||v_err_msg;
-- DBMS_OUTPUT.PUT_LINE(v_err_line);
COMMIT;
END;
--END;
/
EXIT;
====================================
thanks..
|
|
|
|
Goto Forum:
Current Time: Sat May 18 20:01:55 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
