ORACLE MASTERMINDS: Create or Update HR location in oracle apps

Wednesday, September 26, 2018

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;

Wednesday, September 26, 2018

Create or Update HR location in oracle apps

No comments:

Post a Comment