[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ppf.full_name
from per_people_f ppf
, fnd_sessions ses
where ses.session_id = userenv('sessionid')
and person_id = p_designated_receiver_id
and ses.effective_date
between ppf.effective_start_date
and ppf.effective_end_date;
select ppf.full_name
from per_people_f ppf
where ppf.person_id = p_designated_receiver_id
and ppf.effective_start_date = (
select min(ppf2.effective_start_date)
from per_people_f ppf2
where ppf2.person_id = ppf.person_id );
select OOD.ORGANIZATION_CODE ||'-'||
OOD.ORGANIZATION_NAME
FROM ORG_ORGANIZATION_DEFINITIONS OOD
where OOD.ORGANIZATION_ID = p_inventory_orgnization_id ;
select application_name
from fnd_application_vl
where application_id = p_resp_appl_id;
SELECT FT.TERRITORY_SHORT_NAME,FT.TERRITORY_CODE
FROM FND_TERRITORIES_VL FT,
FND_DESCR_FLEX_CONTEXTS FDFC
WHERE FT.TERRITORY_CODE = p_legislation_code
AND substr(FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE,1,2) = FT.TERRITORY_CODE
AND FDFC.DESCRIPTIVE_FLEXFIELD_NAME = 'Address Location'
AND FDFC.ENABLED_FLAG = 'Y';
procedure validate_insert_location
( p_location_code varchar2,
p_location_id number,
p_inventory_organization_id number ) is
v_location_id number;
SELECT l.location_id
from hr_locations l
where upper(l.location_code) = upper(p_location_code)
and (l.location_id <> p_location_id or
p_location_id is null)
AND (l.inventory_organization_id = p_inventory_organization_id
OR p_inventory_organization_id is NULL);
hr_utility.set_location('hr_location_pkg.validate_insert_location',1);
end validate_insert_location;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Location_Id IN OUT NOCOPY NUMBER,
X_Entered_By NUMBER,
X_Location_Code VARCHAR2,
X_Address_Line_1 VARCHAR2,
X_Address_Line_2 VARCHAR2,
X_Address_Line_3 VARCHAR2,
X_Bill_To_Site_Flag VARCHAR2,
X_Country VARCHAR2,
X_Description VARCHAR2,
X_Designated_Receiver_Id NUMBER,
X_In_Organization_Flag VARCHAR2,
X_Inactive_Date DATE,
X_Inventory_Organization_Id NUMBER,
X_Office_Site_Flag VARCHAR2,
X_Postal_Code VARCHAR2,
X_Receiving_Site_Flag VARCHAR2,
X_Region_1 VARCHAR2,
X_Region_2 VARCHAR2,
X_Region_3 VARCHAR2,
X_Ship_To_Location_Id IN OUT NOCOPY NUMBER,
X_Ship_To_Site_Flag VARCHAR2,
X_Style VARCHAR2,
X_Tax_Name VARCHAR2,
X_Ece_Tp_Location_Code VARCHAR2,
X_Telephone_Number_1 VARCHAR2,
X_Telephone_Number_2 VARCHAR2,
X_Telephone_Number_3 VARCHAR2,
X_Town_Or_City VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Created_By NUMBER,
X_Creation_Date DATE ,
X_C_Purchasing_Ins VARCHAR2,
X_C_Inventory_Ins VARCHAR2,
X_Ship_To_Location VARCHAR2,
X_Global_Attribute_Category VARCHAR2,
X_Global_Attribute1 VARCHAR2,
X_Global_Attribute2 VARCHAR2,
X_Global_Attribute3 VARCHAR2,
X_Global_Attribute4 VARCHAR2,
X_Global_Attribute5 VARCHAR2,
X_Global_Attribute6 VARCHAR2,
X_Global_Attribute7 VARCHAR2,
X_Global_Attribute8 VARCHAR2,
X_Global_Attribute9 VARCHAR2,
X_Global_Attribute10 VARCHAR2,
X_Global_Attribute11 VARCHAR2,
X_Global_Attribute12 VARCHAR2,
X_Global_Attribute13 VARCHAR2,
X_Global_Attribute14 VARCHAR2,
X_Global_Attribute15 VARCHAR2,
X_Global_Attribute16 VARCHAR2,
X_Global_Attribute17 VARCHAR2,
X_Global_Attribute18 VARCHAR2,
X_Global_Attribute19 VARCHAR2,
X_Global_Attribute20 VARCHAR2,
X_Loc_Information17 VARCHAR2,
X_Loc_Information18 VARCHAR2,
X_Loc_Information19 VARCHAR2,
X_Loc_Information20 VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM hr_locations
WHERE location_id = X_Location_Id;
CURSOR C2 IS SELECT hr_locations_s.nextval FROM sys.dual;
hr_locations_pkg.validate_insert_location
(X_Location_code,
X_Location_id,
X_Inventory_Organization_Id );
INSERT INTO hr_locations(
location_id,
entered_by,
location_code,
address_line_1,
address_line_2,
address_line_3,
bill_to_site_flag,
country,
description,
designated_receiver_id,
in_organization_flag,
inactive_date,
inventory_organization_id,
office_site_flag,
postal_code,
receiving_site_flag,
region_1,
region_2,
region_3,
ship_to_location_id,
ship_to_site_flag,
style,
tax_name,
ece_tp_location_code,
telephone_number_1,
telephone_number_2,
telephone_number_3,
town_or_city,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
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_information17,
loc_information18,
loc_information19,
loc_information20
) VALUES (
X_Location_Id,
X_Entered_By,
X_Location_Code,
X_Address_Line_1,
X_Address_Line_2,
X_Address_Line_3,
X_Bill_To_Site_Flag,
X_Country,
X_Description,
X_Designated_Receiver_Id,
X_In_Organization_Flag,
X_Inactive_Date,
X_Inventory_Organization_Id,
X_Office_Site_Flag,
X_Postal_Code,
X_Receiving_Site_Flag,
X_Region_1,
X_Region_2,
X_Region_3,
X_Ship_To_Location_Id,
X_Ship_To_Site_Flag,
X_Style,
X_Tax_Name,
X_Ece_Tp_Location_Code,
X_Telephone_Number_1,
X_Telephone_Number_2,
X_Telephone_Number_3,
X_Town_Or_City,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15,
X_Attribute16,
X_Attribute17,
X_Attribute18,
X_Attribute19,
X_Attribute20,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Update_Login,
X_Created_By,
X_Creation_Date,
X_Global_Attribute_Category,
X_Global_Attribute1,
X_Global_Attribute2,
X_Global_Attribute3,
X_Global_Attribute4,
X_Global_Attribute5,
X_Global_Attribute6,
X_Global_Attribute7,
X_Global_Attribute8,
X_Global_Attribute9,
X_Global_Attribute10,
X_Global_Attribute11,
X_Global_Attribute12,
X_Global_Attribute13,
X_Global_Attribute14,
X_Global_Attribute15,
X_Global_Attribute16,
X_Global_Attribute17,
X_Global_Attribute18,
X_Global_Attribute19,
X_Global_Attribute20,
X_Loc_Information17,
X_Loc_Information18,
X_Loc_Information19,
X_Loc_Information20
);
'hr_locations_pkg.insert_row');
END Insert_Row;
SELECT *
FROM hr_locations
WHERE rowid = chartorowid(X_Rowid)
FOR UPDATE of Location_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Location_Id NUMBER,
X_Entered_By NUMBER,
X_Location_Code VARCHAR2,
X_Address_Line_1 VARCHAR2,
X_Address_Line_2 VARCHAR2,
X_Address_Line_3 VARCHAR2,
X_Bill_To_Site_Flag VARCHAR2,
X_Country VARCHAR2,
X_Description VARCHAR2,
X_Designated_Receiver_Id NUMBER,
X_In_Organization_Flag VARCHAR2,
X_Inactive_Date DATE,
X_Inventory_Organization_Id NUMBER,
X_Office_Site_Flag VARCHAR2,
X_Postal_Code VARCHAR2,
X_Receiving_Site_Flag VARCHAR2,
X_Region_1 VARCHAR2,
X_Region_2 VARCHAR2,
X_Region_3 VARCHAR2,
X_Ship_To_Location_Id IN OUT NOCOPY NUMBER,
X_Ship_To_Site_Flag VARCHAR2,
X_Style VARCHAR2,
X_Tax_Name VARCHAR2,
X_Ece_Tp_Location_Code VARCHAR2,
X_Telephone_Number_1 VARCHAR2,
X_Telephone_Number_2 VARCHAR2,
X_Telephone_Number_3 VARCHAR2,
X_Town_Or_City VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER ,
X_C_Purchasing_Ins VARCHAR2,
X_C_Inventory_Ins VARCHAR2,
X_Ship_To_Location VARCHAR2,
X_Global_Attribute_Category VARCHAR2,
X_Global_Attribute1 VARCHAR2,
X_Global_Attribute2 VARCHAR2,
X_Global_Attribute3 VARCHAR2,
X_Global_Attribute4 VARCHAR2,
X_Global_Attribute5 VARCHAR2,
X_Global_Attribute6 VARCHAR2,
X_Global_Attribute7 VARCHAR2,
X_Global_Attribute8 VARCHAR2,
X_Global_Attribute9 VARCHAR2,
X_Global_Attribute10 VARCHAR2,
X_Global_Attribute11 VARCHAR2,
X_Global_Attribute12 VARCHAR2,
X_Global_Attribute13 VARCHAR2,
X_Global_Attribute14 VARCHAR2,
X_Global_Attribute15 VARCHAR2,
X_Global_Attribute16 VARCHAR2,
X_Global_Attribute17 VARCHAR2,
X_Global_Attribute18 VARCHAR2,
X_Global_Attribute19 VARCHAR2,
X_Global_Attribute20 VARCHAR2,
X_Loc_Information17 VARCHAR2,
X_Loc_Information18 VARCHAR2,
X_Loc_Information19 VARCHAR2,
X_Loc_Information20 VARCHAR2
) IS
BEGIN
set_ship_to_location(X_C_Purchasing_Ins,
X_C_Inventory_Ins,
X_Ship_To_Location,
X_Location_Id,
X_Ship_To_Location_Id,
X_Location_Code);
UPDATE hr_locations
SET
location_id = X_Location_Id,
entered_by = X_Entered_By,
location_code = X_Location_Code,
address_line_1 = X_Address_Line_1,
address_line_2 = X_Address_Line_2,
address_line_3 = X_Address_Line_3,
bill_to_site_flag = X_Bill_To_Site_Flag,
country = X_Country,
description = X_Description,
designated_receiver_id = X_Designated_Receiver_Id,
in_organization_flag = X_In_Organization_Flag,
inactive_date = X_Inactive_Date,
inventory_organization_id = X_Inventory_Organization_Id,
office_site_flag = X_Office_Site_Flag,
postal_code = X_Postal_Code,
receiving_site_flag = X_Receiving_Site_Flag,
region_1 = X_Region_1,
region_2 = X_Region_2,
region_3 = X_Region_3,
ship_to_location_id = X_Ship_To_Location_Id,
ship_to_site_flag = X_Ship_To_Site_Flag,
style = X_Style,
tax_name = X_Tax_Name,
ece_tp_location_code = X_Ece_Tp_Location_Code,
telephone_number_1 = X_Telephone_Number_1,
telephone_number_2 = X_Telephone_Number_2,
telephone_number_3 = X_Telephone_Number_3,
town_or_city = X_Town_Or_City,
attribute_category = X_Attribute_Category,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
attribute11 = X_Attribute11,
attribute12 = X_Attribute12,
attribute13 = X_Attribute13,
attribute14 = X_Attribute14,
attribute15 = X_Attribute15,
attribute16 = X_Attribute16,
attribute17 = X_Attribute17,
attribute18 = X_Attribute18,
attribute19 = X_Attribute19,
attribute20 = X_Attribute20,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
global_attribute_category = X_Global_Attribute_Category,
global_attribute1 = X_Global_Attribute1,
global_attribute2 = X_Global_Attribute2,
global_attribute3 = X_Global_Attribute3,
global_attribute4 = X_Global_Attribute4,
global_attribute5 = X_Global_Attribute5,
global_attribute6 = X_Global_Attribute6,
global_attribute7 = X_Global_Attribute7,
global_attribute8 = X_Global_Attribute8,
global_attribute9 = X_Global_Attribute9,
global_attribute10 = X_Global_Attribute10,
global_attribute11 = X_Global_Attribute11,
global_attribute12 = X_Global_Attribute12,
global_attribute13 = X_Global_Attribute13,
global_attribute14 = X_Global_Attribute14,
global_attribute15 = X_Global_Attribute15,
global_attribute16 = X_Global_Attribute16,
global_attribute17 = X_Global_Attribute17,
global_attribute18 = X_Global_Attribute18,
global_attribute19 = X_Global_Attribute19,
global_attribute20 = X_Global_Attribute20,
loc_information17 = X_Loc_Information17,
loc_information18 = X_Loc_Information18,
loc_information19 = X_Loc_Information19,
loc_information20 = X_Loc_Information20
WHERE rowid = chartorowid(X_rowid);
'hr_locations_pkg.update_row');
END Update_Row;