Monday, October 26, 2020

API to load Employee Address in R12 (HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS)

 

 

 

 

 

 

In this post, we have given a sample script to associate an address to existing employee in R12.

Tested Instance: R12.2

API: HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS

TABLES:

  1. per_all_people_f
  2. per_addresses

 Query to Get list of defined Address Styles: 

SELECT * FROM fnd_descr_flex_contexts WHERE descriptive_flexfield_name = 'Address Structure' AND descriptive_flex_context_code = :style AND enabled_flag = 'Y' AND application_id = 800;
Script:
SET SERVEROUTPUT ON; DECLARE lrec_person_details per_all_people_f%ROWTYPE; ln_object_version_num NUMBER; ln_address_id NUMBER; ln_exists NUMBER; lv_employee_num VARCHAR2(200) DEFAULT '123456'; BEGIN SELECT papf.* INTO lrec_person_details FROM per_all_people_f papf WHERE papf.employee_number = lv_employee_num AND current_employee_flag = 'Y' AND SYSDATE BETWEEN effective_start_date AND effective_end_date; HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS (p_validate => FALSE, p_effective_date => lrec_person_details.effective_start_date, p_person_id => lrec_person_details.person_id, p_primary_flag => 'Y', p_style => 'GENERIC', p_date_from => SYSDATE, p_address_line1 => '11, TEST ADDRESS LINE', p_address_line2 => NULL, p_address_line3 => NULL, p_country => 'MY', p_postal_code => '40400', p_region_1 => 'SELANGOR', p_address_id => ln_address_id, p_object_version_number => ln_object_version_num ); DBMS_OUTPUT.PUT_LINE('Address id: '||ln_address_id); SELECT COUNT(1) INTO ln_exists FROM per_addresses WHERE address_id = ln_address_id AND person_id = lrec_person_details.person_id ; IF ln_exists > 0 THEN DBMS_OUTPUT.PUT_LINE('Address successfully attached to person'); ELSE DBMS_OUTPUT.PUT_LINE('Address assignment failed'); END IF; END;

1 Responses to “API to load Employee Address in R12 (HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS)”

OracleTech said...
January 8, 2024 at 5:58 AM

Than you....Interesting Post


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.