HRMS Employee’s Address Maigration
Hi guys today we will explain HRMS Employee’s Address Maigration
DECLARE v_address_error VARCHAR2 (400); p_validate BOOLEAN; p_effective_date DATE; p_pradd_ovlapval_override BOOLEAN; p_validate_county BOOLEAN; p_person_id NUMBER; p_primary_flag VARCHAR2 (200); p_style VARCHAR2 (200); p_date_from DATE; p_date_to DATE; p_address_type VARCHAR2 (200); p_comments LONG; p_address_line1 VARCHAR2 (200); p_address_line2 VARCHAR2 (200); p_address_line3 VARCHAR2 (200); p_town_or_city VARCHAR2 (200); p_region_1 VARCHAR2 (200); p_region_2 VARCHAR2 (200); p_region_3 VARCHAR2 (200); p_postal_code VARCHAR2 (200); p_country VARCHAR2 (200); p_telephone_number_1 VARCHAR2 (200); p_telephone_number_2 VARCHAR2 (200); p_telephone_number_3 VARCHAR2 (200); p_addr_attribute_category VARCHAR2 (200); p_addr_attribute1 VARCHAR2 (200); p_addr_attribute2 VARCHAR2 (200); p_addr_attribute3 VARCHAR2 (200); p_addr_attribute4 VARCHAR2 (200); p_addr_attribute5 VARCHAR2 (200); p_addr_attribute6 VARCHAR2 (200); p_addr_attribute7 VARCHAR2 (200); p_addr_attribute8 VARCHAR2 (200); p_addr_attribute9 VARCHAR2 (200); p_addr_attribute10 VARCHAR2 (200); p_addr_attribute11 VARCHAR2 (200); p_addr_attribute12 VARCHAR2 (200); p_addr_attribute13 VARCHAR2 (200); p_addr_attribute14 VARCHAR2 (200); p_addr_attribute15 VARCHAR2 (200); p_addr_attribute16 VARCHAR2 (200); p_addr_attribute17 VARCHAR2 (200); p_addr_attribute18 VARCHAR2 (200); p_addr_attribute19 VARCHAR2 (200); p_addr_attribute20 VARCHAR2 (200); p_add_information13 VARCHAR2 (200); p_add_information14 VARCHAR2 (200); p_add_information15 VARCHAR2 (200); p_add_information16 VARCHAR2 (200); p_add_information17 VARCHAR2 (200); p_add_information18 VARCHAR2 (200); p_add_information19 VARCHAR2 (200); p_add_information20 VARCHAR2 (200); p_party_id NUMBER; p_address_id NUMBER; p_object_version_number NUMBER; CURSOR c1 IS SELECT * FROM XX_ADD_TEMP WHERE valid_flag is null --AND SER BETWEEN 1002 AND 4025 ; AND EMP_NO '1838'; BEGIN FOR c1_rec IN c1 LOOP p_validate := FALSE; SELECT PERSON_ID INTO p_person_id FROM PER_ALL_PEOPLE_F WHERE EMPLOYEE_NUMBER=TO_NUMBER(C1_REC.EMP_NO); SELECT effective_start_date INTO p_effective_date FROM per_all_people_f WHERE employee_number = c1_rec.emp_no; -- P_EFFECTIVE_DATE := SYSDATE; p_pradd_ovlapval_override := NULL; p_validate_county := NULL; p_primary_flag := 'Y'; p_style := 'Egypt'; SELECT effective_start_date INTO p_date_from FROM per_all_people_f WHERE employee_number = c1_rec.emp_no; p_date_to := NULL; p_address_type := NULL; p_comments := NULL; p_address_line1 := c1_rec.add_line1; --building num-- p_address_line2 := c1_rec.add_line2; -- Street -- p_address_line3 := NULL; p_town_or_city := c1_rec.city; p_region_1 := c1_rec.region; -- area--- p_region_2 := NULL; p_region_3 := NULL; p_postal_code := NULL; p_country := 'مصر'; /*SELECT territory_code INTO p_country FROM fnd_territories_vl WHERE TRIM (UPPER (territory_short_name)) = TRIM (UPPER (c1_rec.country));*/ 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_address_id := NULL; p_object_version_number := NULL; BEGIN apps.hr_person_address_api.create_person_address (p_validate, p_effective_date, p_pradd_ovlapval_override, p_validate_county, p_person_id, p_primary_flag, p_style, p_date_from, p_date_to, p_address_type, p_comments, p_address_line1, p_address_line2, p_address_line3, p_town_or_city, p_region_1, p_region_2, p_region_3, p_postal_code, p_country, p_telephone_number_1, p_telephone_number_2, p_telephone_number_3, p_addr_attribute_category, p_addr_attribute1, p_addr_attribute2, p_addr_attribute3, p_addr_attribute4, p_addr_attribute5, p_addr_attribute6, p_addr_attribute7, p_addr_attribute8, p_addr_attribute9, p_addr_attribute10, p_addr_attribute11, p_addr_attribute12, p_addr_attribute13, p_addr_attribute14, p_addr_attribute15, p_addr_attribute16, p_addr_attribute17, p_addr_attribute18, p_addr_attribute19, p_addr_attribute20, p_add_information13, p_add_information14, p_add_information15, p_add_information16, p_add_information17, p_add_information18, p_add_information19, p_add_information20, p_party_id, p_address_id, p_object_version_number ); COMMIT; UPDATE xx_add_temp SET error_msg = NULL, valid_flag = 'Y' WHERE emp_no = c1_rec.emp_no; EXCEPTION WHEN OTHERS THEN v_address_error := SQLERRM; UPDATE xx_add_temp SET error_msg = error_msg || ' ' || v_address_error, valid_flag = 'N' WHERE emp_no = c1_rec.emp_no; COMMIT; END; END LOOP; END;