Create Person Address (Permanent) in Oracle apps HRMS:
Sample Code:
DECLARE
err_code VARCHAR2 (50);
err_msg VARCHAR2 (1000);
ln_address_id PER_ADDRESSES.ADDRESS_ID%TYPE;
ln_object_version_number PER_ADDRESSES.OBJECT_VERSION_NUMBER%TYPE;
CURSOR EMP_ADD
IS
SELECT T.ADDRESS_LINE1,
T.TOWN_OR_CITY,
T.COUNTRY,
T.ADDR_ATTRIBUTE1,
T.ADDR_ATTRIBUTE2,
T.ADDR_ATTRIBUTE3,
PERSON_ID,
EMPLOYEE_NUMBER,
SR
FROM temporary_address_table T
WHERE LINE_STATUS NOT IN ('I', 'U')
OR LINE_STATUS IS NULL
AND ADDRESS_ID IS NULL
AND PRIMARY_FLAG = 'Y';
BEGIN
FOR REC IN EMP_ADD
LOOP
BEGIN
hr_person_address_api.create_person_address (
p_effective_date => TRUNC (SYSDATE),
-- P_PRADD_OVLAPVAL_OVERRIDE => FALSE,
-- P_VALIDATE_COUNTY => TRUE,
p_person_id => rec.person_id,
p_primary_flag => 'Y',
p_style => 'PK_GLB',
p_date_from => TRUNC (SYSDATE),
p_address_line1 => REC.ADDRESS_LINE1,
p_addr_attribute1 => REC.ADDRESS_LINE1,
p_town_or_city => REC.TOWN_OR_CITY,
p_addr_attribute2 => REC.TOWN_OR_CITY,
p_addr_attribute3 => 'Pakistan',
p_country => 'PK',
p_address_id => ln_address_id,
p_object_version_number => ln_object_version_number
);
COMMIT;
UPDATE temporary_address_table
SET ADDRESS_ID = ln_address_id,
LINE_STATUS = 'I',
ERROR_TEXT = ''
WHERE EMPLOYEE_NUMBER = rec.EMPLOYEE_NUMBER AND sr = rec.sr;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
err_code := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 200);
UPDATE temporary_address_table
SET LINE_STATUS = 'E',
ERROR_TEXT = err_code || ':' || err_msg
WHERE EMPLOYEE_NUMBER = rec.EMPLOYEE_NUMBER AND sr = rec.sr;
COMMIT;
DBMS_OUTPUT.put_line (SQLERRM);
END;
END LOOP;
END;
Sample Code:
DECLARE
err_code VARCHAR2 (50);
err_msg VARCHAR2 (1000);
ln_address_id PER_ADDRESSES.ADDRESS_ID%TYPE;
ln_object_version_number PER_ADDRESSES.OBJECT_VERSION_NUMBER%TYPE;
CURSOR EMP_ADD
IS
SELECT T.ADDRESS_LINE1,
T.TOWN_OR_CITY,
T.COUNTRY,
T.ADDR_ATTRIBUTE1,
T.ADDR_ATTRIBUTE2,
T.ADDR_ATTRIBUTE3,
PERSON_ID,
EMPLOYEE_NUMBER,
SR
FROM temporary_address_table T
WHERE LINE_STATUS NOT IN ('I', 'U')
OR LINE_STATUS IS NULL
AND ADDRESS_ID IS NULL
AND PRIMARY_FLAG = 'Y';
BEGIN
FOR REC IN EMP_ADD
LOOP
BEGIN
hr_person_address_api.create_person_address (
p_effective_date => TRUNC (SYSDATE),
-- P_PRADD_OVLAPVAL_OVERRIDE => FALSE,
-- P_VALIDATE_COUNTY => TRUE,
p_person_id => rec.person_id,
p_primary_flag => 'Y',
p_style => 'PK_GLB',
p_date_from => TRUNC (SYSDATE),
p_address_line1 => REC.ADDRESS_LINE1,
p_addr_attribute1 => REC.ADDRESS_LINE1,
p_town_or_city => REC.TOWN_OR_CITY,
p_addr_attribute2 => REC.TOWN_OR_CITY,
p_addr_attribute3 => 'Pakistan',
p_country => 'PK',
p_address_id => ln_address_id,
p_object_version_number => ln_object_version_number
);
COMMIT;
UPDATE temporary_address_table
SET ADDRESS_ID = ln_address_id,
LINE_STATUS = 'I',
ERROR_TEXT = ''
WHERE EMPLOYEE_NUMBER = rec.EMPLOYEE_NUMBER AND sr = rec.sr;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
err_code := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 200);
UPDATE temporary_address_table
SET LINE_STATUS = 'E',
ERROR_TEXT = err_code || ':' || err_msg
WHERE EMPLOYEE_NUMBER = rec.EMPLOYEE_NUMBER AND sr = rec.sr;
COMMIT;
DBMS_OUTPUT.put_line (SQLERRM);
END;
END LOOP;
END;
0 comments: