The following lines contain the word 'select', 'insert', 'update' or 'delete':
This procedure inserts a record in the eam_pm_set_names table
-- Parameters :
-- IN : P_API_VERSION IN NUMBER REQUIRED
-- P_INIT_MSG_LIST IN VARCHAR2 OPTIONAL
-- DEFAULT = FND_API.G_FALSE
-- P_COMMIT IN VARCHAR2 OPTIONAL
-- DEFAULT = FND_API.G_FALSE
-- P_VALIDATION_LEVEL IN NUMBER OPTIONAL
-- DEFAULT = FND_API.G_VALID_LEVEL_FULL
-- p_set_name IN varchar2 ,
-- p_description IN varchar2 default null,
-- p_end_date IN date 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_end_date_val_req IN BOOLEAN default true ,
--
-- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
-- x_msg_count OUT NOCOPY NUMBER
-- x_msg_data OUT NOCOPY VARCHAR2 (2000)
-- x_new_set_name_id OUT NOCOPY NUMBER
-- Version : Current version: 1.0
-- Initial version: 1.0
--
-- NOTE: p_end_date_validate flag will be false in case of migration, meaning no end date validation required for
-- migration. If the flag is true, only in that case the Validate_FutureEndDate function will be called.
*/
PROCEDURE Insert_PMSetName
(
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_set_name IN varchar2 ,
p_description IN varchar2 default null,
p_end_date IN date 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_organization_id IN number default null,
p_local_flag IN VARCHAR2 default 'N' ,
x_new_set_name_id OUT NOCOPY NUMBER ,
--p_end_date_val_req IN BOOLEAN default true
p_end_date_val_req IN varchar2 default 'true'
)
IS
l_api_name CONSTANT VARCHAR2(30) :='insert set name';
SAVEPOINT Insert_PMSetName_PUB;
SELECT EAM_PM_SET_NAMES_S.NEXTVAL INTO L_SET_NAME_ID FROM DUAL;
INSERT INTO eam_pm_set_names
(
set_name_id ,
set_name ,
description ,
end_date ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
owning_organization_id,
local_flag
)
VALUES
(
l_set_name_id ,
p_set_name ,
p_description ,
p_end_date ,
SYSDATE ,
FND_GLOBAL.USER_ID ,
SYSDATE ,
FND_GLOBAL.LOGIN_ID ,
FND_GLOBAL.USER_ID ,
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_organization_id ,
p_local_flag
);
ROLLBACK TO Insert_PMSetName_PUB;
ROLLBACK TO Insert_PMSetName_PUB;
ROLLBACK TO Insert_PMSetName_PUB;
END Insert_PMSetName;
This procedure updates a record in the eam_pm_set_names table
-- Parameters :
-- IN : P_API_VERSION IN NUMBER REQUIRED
-- P_INIT_MSG_LIST IN VARCHAR2 OPTIONAL
-- DEFAULT = FND_API.G_FALSE
-- P_COMMIT IN VARCHAR2 OPTIONAL
-- DEFAULT = FND_API.G_FALSE
-- P_VALIDATION_LEVEL IN NUMBER OPTIONAL
-- DEFAULT = FND_API.G_VALID_LEVEL_FULL
--
--
-- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
-- x_msg_count OUT NOCOPY NUMBER
-- x_msg_data OUT NOCOPY VARCHAR2 (2000)
-- Version : Current version: 1.0
-- Initial version: 1.0
--
-- NOTE: p_end_date_validate flag will be false in case of migration, meaning no end date validation required for
-- migration. If the flag is true, only in that case the Validate_FutureEndDate function will be called.
*/
PROCEDURE Update_PMSetName
(
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_set_name_id IN NUMBER ,
p_set_name IN VARCHAR2 ,
p_description IN VARCHAR2 default null,
p_end_date IN DATE 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_organization_id IN number default null,
p_local_flag IN VARCHAR2 default 'N',
p_end_date_val_req IN varchar2 default 'true'
)
IS
l_api_name CONSTANT VARCHAR2(30) :='update set name';
SAVEPOINT Update_PMSetName_PUB;
select set_name_id into l_set_name_id
from eam_pm_set_names
where set_name = p_set_name;
SELECT end_date, local_flag
INTO l_end_date, l_local_flag
FROM eam_pm_set_names
WHERE set_name_id = l_set_name_id;
select 'Y' into l_exists from dual
where exists
(
select eps.*
from eam_pm_schedulings eps, csi_item_instances cii, mtl_parameters mp
where eps.set_name_id = l_set_name_id
and eps.maintenance_object_id = cii.instance_id
and cii.last_vld_organization_id = mp.organization_id
and mp.maint_organization_id <> p_organization_id
);
UPDATE
eam_pm_set_names
SET
set_name = p_set_name ,
description = p_description ,
end_date = p_end_date ,
attribute_category = p_attribute_category ,
attribute1 = p_attribute1 ,
attribute2 = p_attribute2 ,
attribute3 = p_attribute3 ,
attribute4 = p_attribute4 ,
attribute5 = p_attribute5 ,
attribute6 = p_attribute6 ,
attribute7 = p_attribute7 ,
attribute8 = p_attribute8 ,
attribute9 = p_attribute9 ,
attribute10 = p_attribute10 ,
attribute11 = p_attribute11 ,
attribute12 = p_attribute12 ,
attribute13 = p_attribute13 ,
attribute14 = p_attribute14 ,
attribute15 = p_attribute15 ,
owning_organization_id = p_organization_id ,
local_flag = p_local_flag
WHERE
set_name_id = l_set_name_id;
ROLLBACK TO Update_PMSetName_PUB;
ROLLBACK TO Update_PMSetName_PUB;
ROLLBACK TO Update_PMSetName_PUB;
END Update_PMSetName;
/* validate set name exists at the time of update */
FUNCTION Validate_SetName
(p_set_name_id NUMBER, p_set_name varchar2)
return boolean
IS
l_status number;
SELECT
count (*) into l_status
FROM
eam_pm_set_names
WHERE
set_name_id = p_set_name_id
and set_name=p_set_name;
/* Validate set name is unique at the time of insert */
FUNCTION Validate_SetNameUnique
(p_set_name VARCHAR2)
return boolean
IS
l_status number;
SELECT
count(*) into l_status
FROM
eam_pm_set_names
WHERE
set_name = p_set_name ;
SELECT
'PASS' into l_status
FROM
dual
WHERE
p_end_date > sysdate;
INSERT into temp_isetup(msg,sr_no)
VALUES (info,g_sr_no);