Query for Create or Update HR location in oracle apps
Introduction
This Post illustrates the steps required to Create or Update HR location in oracle application.
Script to Create or Update HR location in oracle apps
SET DEFINE OFF;
CREATE OR REPLACE PACKAGE apps.xxal_hr_location_cre_upd_api AUTHID DEFINER
IS
l_count NUMBER;
l_location_id NUMBER;
PROCEDURE create_location (
p_validate IN BOOLEAN DEFAULT FALSE,
p_effective_date IN DATE,
p_language_code IN VARCHAR2
DEFAULT hr_api.userenv_lang,
p_location_code IN VARCHAR2,
p_description IN VARCHAR2 DEFAULT NULL,
p_timezone_code IN VARCHAR2 DEFAULT NULL,
p_tp_header_id IN NUMBER DEFAULT NULL,
p_ece_tp_location_code IN VARCHAR2 DEFAULT NULL,
p_address_line_1 IN VARCHAR2 DEFAULT NULL,
p_address_line_2 IN VARCHAR2 DEFAULT NULL,
p_address_line_3 IN VARCHAR2 DEFAULT NULL,
p_bill_to_site_flag IN VARCHAR2 DEFAULT ‘Y’,
p_country IN VARCHAR2 DEFAULT NULL,
p_designated_receiver_id IN NUMBER DEFAULT NULL,
p_in_organization_flag IN VARCHAR2 DEFAULT ‘Y’,
p_inactive_date IN DATE DEFAULT NULL,
p_operating_unit_id IN NUMBER DEFAULT NULL,
p_inventory_organization_id IN NUMBER DEFAULT NULL,
p_office_site_flag IN VARCHAR2 DEFAULT ‘Y’,
p_postal_code IN VARCHAR2 DEFAULT NULL,
p_receiving_site_flag IN VARCHAR2 DEFAULT ‘Y’,
p_region_1 IN VARCHAR2 DEFAULT NULL,
p_region_2 IN VARCHAR2 DEFAULT NULL,
p_region_3 IN VARCHAR2 DEFAULT NULL,
p_ship_to_location_id IN NUMBER DEFAULT NULL,
p_ship_to_site_flag IN VARCHAR2 DEFAULT ‘Y’,
p_style IN VARCHAR2 DEFAULT NULL,
p_tax_name IN VARCHAR2 DEFAULT NULL,
p_telephone_number_1 IN VARCHAR2 DEFAULT NULL,
p_telephone_number_2 IN VARCHAR2 DEFAULT NULL,
p_telephone_number_3 IN VARCHAR2 DEFAULT NULL,
p_town_or_city IN VARCHAR2 DEFAULT NULL,
p_loc_information13 IN VARCHAR2 DEFAULT NULL,
p_loc_information14 IN VARCHAR2 DEFAULT NULL,
p_loc_information15 IN VARCHAR2 DEFAULT NULL,
p_loc_information16 IN VARCHAR2 DEFAULT NULL,
p_loc_information17 IN VARCHAR2 DEFAULT NULL,
p_loc_information18 IN VARCHAR2 DEFAULT NULL,
p_loc_information19 IN VARCHAR2 DEFAULT NULL,
p_loc_information20 IN VARCHAR2 DEFAULT NULL,
p_attribute_category IN VARCHAR2 DEFAULT NULL,
p_attribute1 IN VARCHAR2 DEFAULT NULL,
p_attribute2 IN VARCHAR2 DEFAULT NULL,
p_attribute3 IN VARCHAR2 DEFAULT NULL,
p_attribute4 IN VARCHAR2 DEFAULT NULL,
p_attribute5 IN VARCHAR2 DEFAULT NULL,
p_attribute6 IN VARCHAR2 DEFAULT NULL,
p_attribute7 IN VARCHAR2 DEFAULT NULL,
p_attribute8 IN VARCHAR2 DEFAULT NULL,
p_attribute9 IN VARCHAR2 DEFAULT NULL,
p_attribute10 IN VARCHAR2 DEFAULT NULL,
p_attribute11 IN VARCHAR2 DEFAULT NULL,
p_attribute12 IN VARCHAR2 DEFAULT NULL,
p_attribute13 IN VARCHAR2 DEFAULT NULL,
p_attribute14 IN VARCHAR2 DEFAULT NULL,
p_attribute15 IN VARCHAR2 DEFAULT NULL,
p_attribute16 IN VARCHAR2 DEFAULT NULL,
p_attribute17 IN VARCHAR2 DEFAULT NULL,
p_attribute18 IN VARCHAR2 DEFAULT NULL,
p_attribute19 IN VARCHAR2 DEFAULT NULL,
p_attribute20 IN VARCHAR2 DEFAULT NULL,
p_global_attribute_category IN VARCHAR2 DEFAULT NULL,
p_global_attribute1 IN VARCHAR2 DEFAULT NULL,
p_global_attribute2 IN VARCHAR2 DEFAULT NULL,
p_global_attribute3 IN VARCHAR2 DEFAULT NULL,
p_global_attribute4 IN VARCHAR2 DEFAULT NULL,
p_global_attribute5 IN VARCHAR2 DEFAULT NULL,
p_global_attribute6 IN VARCHAR2 DEFAULT NULL,
p_global_attribute7 IN VARCHAR2 DEFAULT NULL,
p_global_attribute8 IN VARCHAR2 DEFAULT NULL,
p_global_attribute9 IN VARCHAR2 DEFAULT NULL,
p_global_attribute10 IN VARCHAR2 DEFAULT NULL,
p_global_attribute11 IN VARCHAR2 DEFAULT NULL,
p_global_attribute12 IN VARCHAR2 DEFAULT NULL,
p_global_attribute13 IN VARCHAR2 DEFAULT NULL,
p_global_attribute14 IN VARCHAR2 DEFAULT NULL,
p_global_attribute15 IN VARCHAR2 DEFAULT NULL,
p_global_attribute16 IN VARCHAR2 DEFAULT NULL,
p_global_attribute17 IN VARCHAR2 DEFAULT NULL,
p_global_attribute18 IN VARCHAR2 DEFAULT NULL,
p_global_attribute19 IN VARCHAR2 DEFAULT NULL,
p_global_attribute20 IN VARCHAR2 DEFAULT NULL,
p_business_group_id IN NUMBER DEFAULT NULL,
p_location_id OUT NOCOPY NUMBER,
p_object_version_number OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
END xxal_hr_location_cre_upd_api;
/
SHOW ERRORS;
EXIT;
SET DEFINE OFF;
CREATE OR REPLACE PACKAGE BODY APPS.xxal_hr_location_cre_upd_api
IS
PROCEDURE create_location (
p_validate IN BOOLEAN DEFAULT FALSE,
p_effective_date IN DATE,
p_language_code IN VARCHAR2
DEFAULT hr_api.userenv_lang,
p_location_code IN VARCHAR2,
p_description IN VARCHAR2,
p_timezone_code IN VARCHAR2,
p_tp_header_id IN NUMBER,
p_ece_tp_location_code IN VARCHAR2,
p_address_line_1 IN VARCHAR2,
p_address_line_2 IN VARCHAR2,
p_address_line_3 IN VARCHAR2,
p_bill_to_site_flag IN VARCHAR2 DEFAULT ‘Y’,
p_country IN VARCHAR2,
p_designated_receiver_id IN NUMBER,
p_in_organization_flag IN VARCHAR2 DEFAULT ‘Y’,
p_inactive_date IN DATE,
p_operating_unit_id IN NUMBER,
p_inventory_organization_id IN NUMBER,
p_office_site_flag IN VARCHAR2 DEFAULT ‘Y’,
p_postal_code IN VARCHAR2,
p_receiving_site_flag IN VARCHAR2 DEFAULT ‘Y’,
p_region_1 IN VARCHAR2,
p_region_2 IN VARCHAR2,
p_region_3 IN VARCHAR2,
p_ship_to_location_id IN NUMBER,
p_ship_to_site_flag IN VARCHAR2 DEFAULT ‘Y’,
p_style IN VARCHAR2,
p_tax_name IN VARCHAR2,
p_telephone_number_1 IN VARCHAR2,
p_telephone_number_2 IN VARCHAR2,
p_telephone_number_3 IN VARCHAR2,
p_town_or_city IN VARCHAR2,
p_loc_information13 IN VARCHAR2,
p_loc_information14 IN VARCHAR2,
p_loc_information15 IN VARCHAR2,
p_loc_information16 IN VARCHAR2,
p_loc_information17 IN VARCHAR2,
p_loc_information18 IN VARCHAR2,
p_loc_information19 IN VARCHAR2,
p_loc_information20 IN VARCHAR2,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_attribute16 IN VARCHAR2,
p_attribute17 IN VARCHAR2,
p_attribute18 IN VARCHAR2,
p_attribute19 IN VARCHAR2,
p_attribute20 IN VARCHAR2,
p_global_attribute_category IN VARCHAR2,
p_global_attribute1 IN VARCHAR2,
p_global_attribute2 IN VARCHAR2,
p_global_attribute3 IN VARCHAR2,
p_global_attribute4 IN VARCHAR2,
p_global_attribute5 IN VARCHAR2,
p_global_attribute6 IN VARCHAR2,
p_global_attribute7 IN VARCHAR2,
p_global_attribute8 IN VARCHAR2,
p_global_attribute9 IN VARCHAR2,
p_global_attribute10 IN VARCHAR2,
p_global_attribute11 IN VARCHAR2,
p_global_attribute12 IN VARCHAR2,
p_global_attribute13 IN VARCHAR2,
p_global_attribute14 IN VARCHAR2,
p_global_attribute15 IN VARCHAR2,
p_global_attribute16 IN VARCHAR2,
p_global_attribute17 IN VARCHAR2,
p_global_attribute18 IN VARCHAR2,
p_global_attribute19 IN VARCHAR2,
p_global_attribute20 IN VARCHAR2,
p_business_group_id IN NUMBER,
p_location_id OUT NOCOPY NUMBER,
p_object_version_number OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_object_version_number NUMBER;
l_address_line1 VARCHAR2 (500);
l_attribute1 VARCHAR2 (100);
l_attribute2 VARCHAR2 (100);
l_attribute3 VARCHAR2 (100);
l_attribute4 VARCHAR2 (100);
l_attribute5 VARCHAR2 (100);
l_attribute6 VARCHAR2 (100);
l_attribute7 VARCHAR2 (100);
l_attribute8 VARCHAR2 (100);
l_attribute9 VARCHAR2 (100);
l_attribute10 VARCHAR2 (100);
l_attribute11 VARCHAR2 (100);
l_attribute12 VARCHAR2 (100);
l_attribute13 VARCHAR2 (100);
l_attribute14 VARCHAR2 (100);
l_attribute15 VARCHAR2 (100);
l_attribute16 VARCHAR2 (100);
l_attribute17 VARCHAR2 (100);
l_attribute18 VARCHAR2 (100);
l_attribute19 VARCHAR2 (100);
l_attribute20 VARCHAR2 (100);
l_global_attribute_category VARCHAR (100);
l_global_attribute1 VARCHAR (100);
l_global_attribute2 VARCHAR (100);
l_global_attribute3 VARCHAR (100);
l_global_attribute4 VARCHAR (100);
l_global_attribute5 VARCHAR (100);
l_global_attribute6 VARCHAR (100);
l_global_attribute7 VARCHAR (100);
l_global_attribute8 VARCHAR (100);
l_global_attribute9 VARCHAR (100);
l_global_attribute10 VARCHAR (100);
l_global_attribute11 VARCHAR (100);
l_global_attribute12 VARCHAR (100);
l_global_attribute13 VARCHAR (100);
l_global_attribute14 VARCHAR (100);
l_global_attribute15 VARCHAR (100);
l_global_attribute16 VARCHAR (100);
l_global_attribute17 VARCHAR (100);
l_global_attribute18 VARCHAR (100);
l_global_attribute19 VARCHAR (100);
l_global_attribute20 VARCHAR (100);
l_loc_information13 VARCHAR2 (100);
l_loc_information14 VARCHAR2 (100);
l_loc_information15 VARCHAR2 (100);
l_loc_information16 VARCHAR2 (100);
l_loc_information17 VARCHAR2 (100);
l_loc_information18 VARCHAR2 (100);
l_loc_information19 VARCHAR2 (100);
l_loc_information20 VARCHAR2 (100);
l_bill_to_site_flag VARCHAR2 (100);
l_in_organization_flag VARCHAR2 (100);
l_receiving_site_flag VARCHAR2 (100);
l_office_site_flag VARCHAR2 (100);
l_ship_to_site_flag VARCHAR2 (100);
l_derived_locale VARCHAR2 (100);
— l_geometry VARCHAR2 (100);
l_legal_address_flag VARCHAR2 (100);
l_timezone_code VARCHAR2 (100);
l_region_2 VARCHAR2(100) ;
l_town_or_city varchar2(100);
BEGIN
x_return_status := ‘S’;
x_msg_count := 0;
x_msg_data := NULL;
l_region_2 := NULL;
IF x_return_status = ‘S’
THEN
BEGIN
— IF p_country =’IN’
— THEN
— l_loc_information15:=p_town_or_city;
— –l_loc_information16:=p_region_2;
— else
— l_town_or_city:=p_town_or_city;
— END IF;
— DBMS_OUTPUT.put_line (‘p_location_code—->’ || p_location_code);
SELECT COUNT (*)
INTO l_count
FROM hr_locations_all
WHERE location_code = p_location_code;
— Added by Ambika to set Region 2 as null for NON-US country
IF p_country = ‘US’ then
l_region_2 := p_region_2;
END IF;
IF l_count = 1
THEN
BEGIN
SELECT location_id, object_version_number,
address_line_1
INTO l_location_id, l_object_version_number,
l_address_line1
FROM hr_locations_all
WHERE location_code = p_location_code;
— DBMS_OUTPUT.put_line (
— ‘l_object_version_number—->’
— || l_object_version_number);
— DBMS_OUTPUT.put_line (
— ‘l_address_line1_PKG—->’ || l_address_line1);
— DBMS_OUTPUT.put_line (
— ‘P_address_line1_PKG—->’ || p_address_line_1);
IF (p_address_line_1 IS NULL)
AND (l_address_line1 IS NOT NULL)
THEN
— DBMS_OUTPUT.put_line (‘the data is here —->’);
raise_application_error
(-20100,
‘Address_line1 cannot be null for the given country’
|| SQLERRM
);
END IF;
BEGIN
SELECT attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9,
attribute10, attribute11, attribute12,
attribute13, attribute14, attribute15,
attribute16, attribute17, attribute18,
attribute19, attribute20,
bill_to_site_flag, in_organization_flag,
receiving_site_flag, office_site_flag,
ship_to_site_flag,
global_attribute_category,
global_attribute1, global_attribute2,
global_attribute3, global_attribute4,
global_attribute5, global_attribute6,
global_attribute7, global_attribute8,
global_attribute9, global_attribute10,
global_attribute11, global_attribute12,
global_attribute13, global_attribute14,
global_attribute15, global_attribute16,
global_attribute17, global_attribute18,
global_attribute19, global_attribute20,
loc_information13, loc_information14,
loc_information15, loc_information16,
loc_information17, loc_information18,
loc_information19, loc_information20,
derived_locale, legal_address_flag,
timezone_code
INTO l_attribute1, l_attribute2, l_attribute3,
l_attribute4, l_attribute5, l_attribute6,
l_attribute7, l_attribute8, l_attribute9,
l_attribute10, l_attribute11, l_attribute12,
l_attribute13, l_attribute14, l_attribute15,
l_attribute16, l_attribute17, l_attribute18,
l_attribute19, l_attribute20,
l_bill_to_site_flag, l_in_organization_flag,
l_receiving_site_flag, l_office_site_flag,
l_ship_to_site_flag,
l_global_attribute_category,
l_global_attribute1, l_global_attribute2,
l_global_attribute3, l_global_attribute4,
l_global_attribute5, l_global_attribute6,
l_global_attribute7, l_global_attribute8,
l_global_attribute9, l_global_attribute10,
l_global_attribute11, l_global_attribute12,
l_global_attribute13, l_global_attribute14,
l_global_attribute15, l_global_attribute16,
l_global_attribute17, l_global_attribute18,
l_global_attribute19, l_global_attribute20,
l_loc_information13, l_loc_information14,
l_loc_information15, l_loc_information16,
l_loc_information17, l_loc_information18,
l_loc_information19, l_loc_information20,
l_derived_locale, l_legal_address_flag,
l_timezone_code
FROM hr_locations_all
WHERE location_code = p_location_code;
END;
apps.hr_location_api.update_location
(p_validate,
p_effective_date,
p_language_code,
l_location_id,
p_location_code,
p_description,
l_timezone_code,
–p_timezone_code,
p_tp_header_id,
p_ece_tp_location_code,
p_address_line_1,
p_address_line_2,
p_address_line_3,
l_bill_to_site_flag,
— p_bill_to_site_flag,
p_country,
p_designated_receiver_id,
l_in_organization_flag,
— p_in_organization_flag,
p_inactive_date,
p_operating_unit_id,
p_inventory_organization_id,
l_office_site_flag,
— p_office_site_flag,
p_postal_code,
l_receiving_site_flag,
— p_receiving_site_flag,
p_region_1,
— p_region_2,
l_region_2,
p_region_3,
p_ship_to_location_id,
l_ship_to_site_flag,
–p_ship_to_site_flag,
p_style,
p_tax_name,
p_telephone_number_1,
p_telephone_number_2,
p_telephone_number_3,
–l_town_or_city,
p_town_or_city,
l_loc_information13,
l_loc_information14,
l_loc_information15,
l_loc_information16,
l_loc_information17,
l_loc_information18,
l_loc_information19,
l_loc_information20,
p_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15,
l_attribute16,
l_attribute17,
l_attribute18,
l_attribute19,
l_attribute20,
l_global_attribute_category,
l_global_attribute1,
l_global_attribute2,
l_global_attribute3,
l_global_attribute4,
l_global_attribute5,
l_global_attribute6,
l_global_attribute7,
l_global_attribute8,
l_global_attribute9,
l_global_attribute10,
l_global_attribute11,
l_global_attribute12,
l_global_attribute13,
l_global_attribute14,
l_global_attribute15,
l_global_attribute16,
l_global_attribute17,
l_global_attribute18,
l_global_attribute19,
l_global_attribute20,
l_object_version_number
);
EXCEPTION
WHEN OTHERS
THEN
x_return_status := ‘E’;
x_msg_count := 1;
x_msg_data := SQLERRM;
raise_application_error (-20002,
‘Update Location Error’
|| x_msg_data
);
END;
ELSE
BEGIN
apps.hr_location_api.create_location
(p_validate,
p_effective_date,
p_language_code,
p_location_code,
p_description,
p_timezone_code,
p_tp_header_id,
p_ece_tp_location_code,
p_address_line_1,
p_address_line_2,
p_address_line_3,
p_bill_to_site_flag,
p_country,
p_designated_receiver_id,
p_in_organization_flag,
p_inactive_date,
p_operating_unit_id,
p_inventory_organization_id,
p_office_site_flag,
p_postal_code,
p_receiving_site_flag,
p_region_1,
— p_region_2,
l_region_2,
p_region_3,
p_ship_to_location_id,
p_ship_to_site_flag,
p_style,
p_tax_name,
p_telephone_number_1,
p_telephone_number_2,
p_telephone_number_3,
–l_town_or_city,
p_town_or_city,
p_loc_information13,
p_loc_information14,
p_loc_information15,
l_loc_information16,
p_loc_information17,
p_loc_information18,
p_loc_information19,
p_loc_information20,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
p_global_attribute_category,
p_global_attribute1,
p_global_attribute2,
p_global_attribute3,
p_global_attribute4,
p_global_attribute5,
p_global_attribute6,
p_global_attribute7,
p_global_attribute8,
p_global_attribute9,
p_global_attribute10,
p_global_attribute11,
p_global_attribute12,
p_global_attribute13,
p_global_attribute14,
p_global_attribute15,
p_global_attribute16,
p_global_attribute17,
p_global_attribute18,
p_global_attribute19,
p_global_attribute20,
p_business_group_id,
p_location_id,
p_object_version_number
);
EXCEPTION
WHEN OTHERS
THEN
x_return_status := ‘E’;
x_msg_count := 1;
x_msg_data := SQLERRM;
raise_application_error (-20001,
‘Create Location Error’
|| x_msg_data
);
COMMIT;
END;
END IF;
END;
END IF;
END create_location;
END xxal_hr_location_cre_upd_api;
/
SHOW ERRORS;
EXIT;
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
