Location in Oracle HRMS
How to upload Locations in Oracle HRMS??
Here is the solution.
TABLE
Here is the solution.
TABLE
CREATE TABLE DEV_LOCATION
(
LINE_ID NUMBER,
EFFECTIVE_START_DATE DATE,
LOCATION_NAME VARCHAR2(240 BYTE),
DESCRIPTION VARCHAR2(240 BYTE),
ADDRESS_LINE1 VARCHAR2(240 BYTE),
ADDRESS_LINE2 VARCHAR2(240 BYTE),
ADDRESS_LINE3 VARCHAR2(240 BYTE),
CITY VARCHAR2(100 BYTE),
PROVINCE VARCHAR2(100 BYTE),
COUNTRY VARCHAR2(100
BYTE),
POSTAL_CODE VARCHAR2(100 BYTE),
TELEPHONE VARCHAR2(100 BYTE),
FAX VARCHAR2(20 BYTE),
PROCESS_FLAG CHAR(1 BYTE),
ERROR_DESCRIPTION CHAR(240 BYTE),
LOCATION_ID NUMBER,
OBJ_VER_NUMBER NUMBER
)
PROCEDURE
CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_LOCATION
IS
CURSOR c_loc
IS
SELECT line_id, effective_start_date, location_name, description,
address_line1, address_line2, address_line3, city, province,country,postal_code,
telephone, fax, process_flag, error_description, location_id,
obj_ver_number
FROM dev_location;
--WHERE NVL (process_flag,
'N') <> 'Y' AND error_description IS NULL;
lc_c_loc c_loc%ROWTYPE;
l_location_id NUMBER;
l_object_version_number NUMBER;
error_desc VARCHAR2 (240);
lv_loc_flag CHAR (1);
l_city VARCHAR2 (20);
l_country VARCHAR2 (30);
l_type VARCHAR2 (20);
BEGIN
OPEN c_loc;
LOOP
FETCH c_loc
INTO lc_c_loc;
EXIT WHEN c_loc%NOTFOUND;
---------------------------
--- Country Code
---------------------------
IF lc_c_loc.country IS NOT NULL THEN
SELECT ftv.territory_code
INTO l_country
FROM fnd_territories_vl ftv
WHERE UPPER (ftv.territory_short_name) LIKE UPPER (TRIM (lc_c_loc.country));
ELSE
l_country := '';
END IF;
BEGIN
hr_location_api.create_location
(p_validate => FALSE,
p_effective_date => lc_c_loc.effective_start_date,
p_location_code => lc_c_loc.location_name,
p_description => lc_c_loc.description,
p_address_line_1 => lc_c_loc.address_line1,
p_address_line_2 => lc_c_loc.address_line2,
p_address_line_3 => lc_c_loc.address_line3,
p_postal_code => lc_c_loc.postal_code,
p_town_or_city => lc_c_loc.city,
p_region_1 => lc_c_loc.province,
p_country => l_country,
p_telephone_number_1 => lc_c_loc.telephone,
p_telephone_number_3 => lc_c_loc.fax,
p_business_group_id => 2217,
p_style => 'PK',
p_location_id => l_location_id,
p_object_version_number => l_object_version_number
);
IF l_location_id IS NOT NULL
THEN
lv_loc_flag := 'Y';
error_desc := 'No Error';
END IF;
IF lv_loc_flag = 'Y'
THEN
UPDATE dev_location
SET error_description = error_desc,
process_flag = lv_loc_flag,
location_id = l_location_id,
obj_ver_number = l_object_version_number
WHERE line_id = lc_c_loc.line_id;
ELSE
error_desc := error_desc || SQLERRM;
UPDATE dev_location
SET error_description = error_desc,
process_flag = 'N'
WHERE line_id = lc_c_loc.line_id;
END IF;
DBMS_OUTPUT.put_line
('Location ID : ' || l_location_id);
EXCEPTION
WHEN OTHERS
THEN
lv_loc_flag := 'N';
error_desc := error_desc || SQLERRM;
UPDATE dev_location
SET error_description = error_desc,
process_flag = lv_loc_flag
WHERE line_id = lc_c_loc.line_id;
DBMS_OUTPUT.put_line
(SQLERRM);
END;
lv_loc_flag := '';
l_location_id := '';
l_object_version_number := '';
error_desc := '';
END LOOP;
CLOSE c_loc;
--dbms_output.put_line('Location
ID : '||l_location_id);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
(SQLERRM);
END;
/
Leave comment for any query.
0 comments: