DECLARE
lc_employee_number PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE;
ln_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
ln_assignment_id PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
ln_object_ver_number PER_ALL_ASSIGNMENTS_F.OBJECT_VERSION_NUMBER%TYPE;
ln_asg_ovn NUMBER;
ld_per_effective_start_date PER_ALL_PEOPLE_F.EFFECTIVE_START_DATE%TYPE;
ld_per_effective_end_date PER_ALL_PEOPLE_F.EFFECTIVE_END_DATE%TYPE;
lc_full_name PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
ln_per_comment_id PER_ALL_PEOPLE_F.COMMENT_ID%TYPE;
ln_assignment_sequence PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE%TYPE;
lc_assignment_number PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
lb_name_combination_warning BOOLEAN;
lb_assign_payroll_warning BOOLEAN;
lb_orig_hire_warning BOOLEAN;
l_count NUMBER := 0;
l_error NUMBER;
l_error_msg VARCHAR2 (4000);
CURSOR c1
IS
SELECT t.EMPLOYEE_NUMBER,
t.employee_id,
(SELECT PERSON_TYPE_ID
FROM PER_PERSON_TYPES
WHERE USER_PERSON_TYPE = employee_type
AND BUSINESS_GROUP_ID = 81)
employee_type,
(CASE
WHEN title = 'Mr' THEN 'MR.'
WHEN title = 'Ms' THEN 'MS.'
END)
title,
first_name,
last_name,
middle_name,
LEGAL_NAME,
NRIC_FIN,
COLOR,
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'SG_NRIC_COLOUR'
AND LANGUAGE = 'US'
AND MEANING = COLOR)
COLOR_CODE,
TO_CHAR (TO_DATE (date_of_birth, 'DD-MON-RRRR'),
'DD-MON-RRRR')
date_of_birth,
TO_CHAR (TO_DATE (hire_date, 'DD-MON-RRRR'), 'DD-MON-RRRR')
hire_date,
country_of_birth,
(SELECT TERRITORY_CODE
FROM FND_TERRITORIES_VL
WHERE TERRITORY_SHORT_NAME = country_of_birth)
country_of_birth_CODE,
email_id,
nationality,
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'NATIONALITY'
AND LANGUAGE = 'US'
AND MEANING = nationality)
nationality_CODE,
marital_status,
(CASE
WHEN marital_status = 'Married' THEN 'M'
WHEN marital_status = 'Single' THEN 'S'
WHEN marital_status = 'Divorced' THEN 'D'
WHEN marital_status = 'Widowed' THEN 'W'
END)
marital_status_code,
(CASE
WHEN gender = 'Male' THEN 'M'
WHEN gender = 'Female' THEN 'F'
END)
gender,
PERMIT_TYPE,
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'SG_PERMIT_TYPE'
AND LANGUAGE = 'US'
AND MEANING = PERMIT_TYPE)
PERMIT_TYPE_code,
PERMIT_NUMBER,
TO_CHAR (PERMIT_DATE_ISSUED, 'YYYY/MM/DD HH24:MI:SS') PERMIT_DATE_ISSUED,
TO_CHAR (PERMIT_EXPIRY_DATE, 'YYYY/MM/DD HH24:MI:SS') PERMIT_EXPIRY_DATE,
PERMIT_CATEGORY,
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'SG_PERMIT_CATEGORY'
AND LANGUAGE = 'US'
AND MEANING = PERMIT_CATEGORY)
PERMIT_CATEGORY_code,
ER_EE_CPF_RATE,
RACE,
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'SG_RACE'
AND LANGUAGE = 'US'
AND MEANING = RACE)
RACE_code,
COMMUNITY_FUND_CATEGORY,
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'SG_COMMUNITY_FUND'
AND LANGUAGE = 'US'
AND MEANING = COMMUNITY_FUND_CATEGORY)
COMMUNITY_FUND_CATEGORY_code,
MBMF_FUND,
RELIGION,
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'SG_RELIGION'
AND LANGUAGE = 'US'
AND MEANING = RELIGION)
RELIGION_code,
cpf.EMPLOYEE_NUMBER cpf_EMPLOYEE_NUMBER,
CPF_ACCOUNT_NUMBER,
CPF_CATEGORY,
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'SG_CPF_CATEGORY'
AND LANGUAGE = 'US'
AND MEANING = CPF_CATEGORY)
CPF_CATEGORY_code,
INCOME_TAX_NUMBER,
INCOME_TAX_NUMBER_SPOUSE,
PAYEE_ID_TYPE,
psp.EMPLOYEE_NUMBER psp_EMPLOYEE_NUMBER,
PASSPORT_NUMBER,
PASSPORT_COUNTRY_OF_ISSUE,
(SELECT TERRITORY_CODE
FROM FND_TERRITORIES_VL
WHERE TERRITORY_SHORT_NAME = PASSPORT_COUNTRY_OF_ISSUE)
PASSPORT_COUNTRY_OF_ISSUE_code,
TO_CHAR (PASSPORT_ISSUE_DATE, 'YYYY/MM/DD HH:MI:SS')--'DD-MON-RRRR')
PASSPORT_ISSUE_DATE,
TO_CHAR (PASSPORT_EXPIRY_DATE, 'YYYY/MM/DD HH:MI:SS') -- 'DD-MON-RRRR')
PASSPORT_EXPIRY_DATE,
PASSPORT_TYPE,
process_flag,
error_message
FROM XXHRL_SG_EMPLOYEE_MASTER t,
XXHRL_SG_CPF cpf,
XXHRL_SG_PASSPORT psp
WHERE cpf.EMPLOYEE_NUMBER(+) = t.EMPlOYEE_NUMBER
AND psp.EMPLOYEE_NUMBER(+) = t.EMPLOYEE_NUMBER;
--AND NVL (process_flag, 'N') = 'N';
BEGIN
FOR i IN c1
LOOP
BEGIN
DBMS_OUTPUT.put_line ('In Loop');
l_error := 0;
lc_employee_number := i.EMPLOYEE_NUMBER;
HR_SG_EMPLOYEE_API.CREATE_SG_EMPLOYEE (
-- ------------------------------
p_hire_date => i.hire_date,
p_business_group_id => 81,
p_person_type_id => i.EMPLOYEE_TYPE,
p_last_name => i.FIRST_NAME,
--p_first_name => 'XXX',
p_sex => i.GENDER,
p_national_identifier => i.NRIC_FIN,
p_legal_name => i.LEGAL_NAME,
-- p_ee_er_rate => 'ER_GR_EE_GR',
p_race => i.RACE_CODE,
p_community_fund_category => i.COMMUNITY_FUND_CATEGORY_CODE,
p_religion => i.RELIGION_CODE,
p_permit_type => 'SG', --i.PERMIT_TYPE_CODE,
p_date_of_birth => i.DATE_OF_BIRTH, ,
p_marital_status => i.MARITAL_STATUS_CODE,
p_email_address => i.EMAIL_ID,
p_nationality => i.NATIONALITY_CODE,
p_title => i.TITLE,
p_country_of_birth => i.COUNTRY_OF_BIRTH_CODE,
p_nric_colour => i.COLOR_CODE,
p_cpf_account_number => i.CPF_ACCOUNT_NUMBER,
p_cpf_category => i.CPF_CATEGORY_CODE,
p_income_tax_number => i.INCOME_TAX_NUMBER,
p_attribute_category => '81',
p_attribute1 => i.employee_id,
-- Output variables
-- --------------------------------
p_employee_number => lc_employee_number,
p_person_id => ln_person_id,
p_assignment_id => ln_assignment_id,
p_per_object_version_number => ln_object_ver_number,
p_asg_object_version_number => ln_asg_ovn,
p_per_effective_start_date => ld_per_effective_start_date,
p_per_effective_end_date => ld_per_effective_end_date,
p_full_name => lc_full_name,
p_per_comment_id => ln_per_comment_id,
p_assignment_sequence => ln_assignment_sequence,
p_assignment_number => lc_assignment_number,
p_name_combination_warning => lb_name_combination_warning,
p_assign_payroll_warning => lb_assign_payroll_warning,
p_orig_hire_warning => lb_orig_hire_warning
);
l_count := l_count + 1;
DBMS_OUTPUT.put_line ('Employee loaded # ' || l_count);
UPDATE XXHRL_SG_EMPLOYEE_MASTER
SET process_flag = 'Y', error_message = 'Loaded'
WHERE EMPLOYEE_NUMBER = i.EMPLOYEE_NUMBER;
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg := 'Employee Create' || SQLERRM;
DBMS_OUTPUT.put_line (SQLERRM);
END;
IF l_error = 1
THEN
UPDATE XXHRL_SG_EMPLOYEE_MASTER
SET process_flag = 'N', error_message = l_error_msg
WHERE EMPLOYEE_NUMBER = i.EMPLOYEE_NUMBER;
END IF;
l_error := 0;
l_error_msg := NULL;
END LOOP;
--COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
0 Comments