DECLARE
ln_address_id PER_ADDRESSES.ADDRESS_ID%TYPE;
ln_object_version_number PER_ADDRESSES.OBJECT_VERSION_NUMBER%TYPE;
l_primary_flag PER_ADDRESSES.PRIMARY_FLAG%TYPE;
l_address_type PER_ADDRESSES.ADDRESS_TYPE%TYPE;
l_count NUMBER := 0;
l_error NUMBER;
l_error_msg VARCHAR2 (4000);
CURSOR c1
IS
SELECT TYPE,
/* (SELECT ADDRESS_ID
FROM PER_ADDRESSES
WHERE BUSINESS_GROUP_ID = 82
AND PERSON_ID =
(SELECT DISTINCT PERSON_ID
FROM PER_ALL_PEOPLE_F
WHERE EMPLOYEE_NUMBER = t.EMPLOYEE_ID
AND BUSINESS_GROUP_ID = 82)
AND PRIMARY_FLAG =
DECODE (t.TYPE, 'Primary', 'Y', 'Home', 'N'))
ADDRESS_ID,*/
EMPLOYEE_ID,
(SELECT DISTINCT PERSON_ID
FROM PER_ALL_PEOPLE_F
WHERE EMPLOYEE_NUMBER = t.EMPLOYEE_ID
AND BUSINESS_GROUP_ID = 82)
PERSON_ID,
(SELECT paf.EFFECTIVE_START_DATE
FROM PER_ALL_PEOPLE_F paf
WHERE paf.employee_number = t.EMPLOYEE_ID
AND BUSINESS_GROUP_ID = 82
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date)
effective_date,
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
POSTAL_CODE,
AREA_CODE,
CITY,
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'MY_STATE'
and language = 'US'
AND MEANING = STATE_PROVINCE)
STATE_PROVINCE,
(SELECT TERRITORY_CODE
FROM FND_TERRITORIES_VL
WHERE TERRITORY_SHORT_NAME = COUNTRY)
COUNTRY,
TELEPHONE,
MOBILE,
FAX,
PROCESS_FLAG,
process_message
FROM XXHRL_MY_ADDRESSES t
WHERE NVL (process_flag, 'N') = 'N';
BEGIN
FOR i IN c1
LOOP
BEGIN
DBMS_OUTPUT.put_line ('In Loop');
l_error := 0;
IF i.TYPE = 'Primary'
THEN
l_primary_flag := 'Y';
-- l_address_type := 'PHCA';
ELSE
l_primary_flag := 'N';
l_address_type := 'PHCA';
END IF;
hr_person_address_api.create_person_address (
p_effective_date => TO_CHAR (i.effective_date, 'DD-MON-RRRR'),
p_person_id => i.PERSON_ID,
-- p_address_id => i.ADDRESS_ID,
p_primary_flag => l_primary_flag,
p_style => 'MY_GLB', --SG
p_date_from => TO_CHAR (i.effective_date, 'DD-MON-RRRR'),
p_address_type => l_address_type,
p_address_line1 => i.address_line_1,
p_address_line2 => i.address_line_2,
p_address_line3 => i.address_line_3,
p_telephone_number_1 => i.TELEPHONE,
p_telephone_number_2 => i.MOBILE,
p_telephone_number_3 => i.FAX,
p_town_or_city => i.CITY,
p_region_1 => i.STATE_PROVINCE,
p_postal_code => i.POSTAL_CODE,
p_country => i.COUNTRY,
-- Output parameters
-- --------------------------------
p_address_id => ln_address_id,
p_object_version_number => ln_object_version_number
);
l_count := l_count + 1;
DBMS_OUTPUT.put_line ('l_count: ' || l_count);
UPDATE XXHRL_MY_ADDRESSES
SET process_flag = 'Y', process_message = 'Loaded'
WHERE employee_id = i.employee_id;
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg := 'Exception ' || SQLERRM;
DBMS_OUTPUT.put_line (SQLERRM);
END;
IF l_error = 1
THEN
UPDATE XXHRL_MY_ADDRESSES
SET process_flag = 'N', process_message = l_error_msg
WHERE employee_id = i.employee_id;
END IF;
l_error := 0;
l_error_msg := NULL;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (SQLERRM);
END;
0 Comments