[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(1)
INTO l_set_count
FROM eam_failure_sets
WHERE set_name = p_failureset_rec.set_name;
SELECT set_id
INTO l_set_id
FROM eam_failure_sets
WHERE set_name = p_failureset_rec.set_name;
SELECT description, effective_end_date, last_update_date
INTO l_old_description, l_old_eff_end_date, l_old_last_upd_date
FROM eam_failure_sets
WHERE set_id = l_set_id;
SELECT efsa.inventory_item_id, msik.concatenated_segments
INTO l_inventory_item_id, l_item
FROM eam_failure_set_associations efsa,
mtl_system_items_kfv msik
WHERE efsa.set_id = l_set_id
AND msik.inventory_item_id = efsa.inventory_item_id
AND EXISTS
(SELECT 1
FROM eam_failure_set_associations efsa1,
eam_failure_sets efs
WHERE efsa1.inventory_item_id = efsa.inventory_item_id
AND efsa1.set_id <> efsa.set_id
AND efs.set_id = efsa1.set_id
AND (efs.effective_end_date IS NULL OR
efs.effective_end_date > NVL(p_failureset_rec.effective_end_date, efs.effective_end_date - 1)))
AND rownum < 2;
SELECT set_id
INTO l_set_id
FROM eam_failure_sets
WHERE set_name = p_association_rec.set_name;
SELECT effective_end_date
INTO l_set_end_date
FROM eam_failure_sets
WHERE set_id = l_set_id;
SELECT count(1)
INTO l_item_exists
FROM mtl_system_items
WHERE inventory_item_id = p_association_rec.inventory_item_id
AND eam_item_type IN (1,3)
AND rownum < 2;
SELECT concatenated_segments
INTO l_maintained_group
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_association_rec.inventory_item_id
AND ROWNUM < 2;
SELECT count(1)
INTO l_association_exists
FROM eam_failure_set_associations
WHERE set_id = l_set_id
AND inventory_item_id = p_association_rec.inventory_item_id
AND effective_end_date IS NULL;
SELECT count(1)
INTO l_association_exists
FROM eam_failure_set_associations efsa,
eam_failure_sets efs
WHERE efsa.inventory_item_id = p_association_rec.inventory_item_id
AND efsa.set_id <> l_set_id
AND efs.set_id = efsa.set_id
AND (efs.effective_end_date IS NULL OR
efs.effective_end_date >= SYSDATE)
AND (efsa.effective_end_date IS NULL OR
efsa.effective_end_date >= SYSDATE);
SELECT count(1)
INTO l_open_wo_exists
FROM wip_discrete_jobs wdj,
eam_work_order_details ewod
WHERE (wdj.asset_group_id = p_association_rec.inventory_item_id
OR
wdj.rebuild_item_id = p_association_rec.inventory_item_id)
AND wdj.status_type NOT IN (4,5,7,12,14,15)
AND ewod.organization_id = wdj.organization_id
AND ewod.wip_entity_id = wdj.wip_entity_id
AND ewod.failure_code_required = 'Y'
AND rownum < 2;
SELECT description, effective_end_date
FROM eam_failure_sets
WHERE set_id = l_failureset_id
FOR UPDATE NOWAIT;
-- Insert into eam failure sets
INSERT INTO eam_failure_sets
(set_id ,
set_name ,
description ,
effective_end_date,
created_by ,
creation_date ,
last_update_date ,
last_updated_by ,
last_update_login)
VALUES (eam_failuresets_s.nextval ,
p_failureset_rec.set_name ,
p_failureset_rec.description ,
p_failureset_rec.effective_end_date,
fnd_global.user_id,
SYSDATE,
SYSDATE,
fnd_global.user_id,
NULL)
RETURNING set_id INTO l_failureset_id ;
UPDATE eam_failure_sets
SET description = decode(p_failureset_rec.description,
NULL, description,
FND_API.G_MISS_CHAR, NULL,
p_failureset_rec.description),
effective_end_date = decode(p_failureset_rec.effective_end_date,
NULL, effective_end_date,
FND_API.G_MISS_DATE, NULL,
p_failureset_rec.effective_end_date),
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = NULL
WHERE set_id = l_failureset_id;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
SELECT failure_code_required, effective_end_date
FROM eam_failure_set_associations
WHERE set_id = p_set_id
AND inventory_item_id = p_item_id
AND effective_end_date IS NULL
FOR UPDATE NOWAIT;
IF (p_association_rec.last_update_date is null) THEN
l_created_by := fnd_global.user_id;
l_last_update_date := SYSDATE;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := NULL;
l_last_update_date := p_association_rec.last_update_date;
l_last_updated_by := p_association_rec.last_updated_by;
l_last_update_login := p_association_rec.last_update_login;
UPDATE eam_failure_set_associations
SET effective_end_date = NULL,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE set_id = l_set_id
AND inventory_item_id = p_association_rec.inventory_item_id;
-- Insert into eam failure set associations
INSERT INTO eam_failure_set_associations
(set_id ,
inventory_item_id ,
failure_code_required,
created_by ,
creation_date ,
last_update_date ,
last_updated_by ,
last_update_login)
VALUES (l_set_id ,
p_association_rec.inventory_item_id ,
NVL(p_association_rec.failure_code_required,'N'),
l_created_by ,
l_creation_date ,
l_last_update_date ,
l_last_updated_by ,
l_last_update_login);
UPDATE eam_failure_set_associations
SET failure_code_required = decode(
p_association_rec.failure_code_required,
'Y','Y',
NULL, failure_code_required,
'N'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE set_id = l_set_id
AND inventory_item_id = p_association_rec.inventory_item_id
AND effective_end_date IS NULL;
UPDATE eam_failure_set_associations
SET effective_end_date = SYSDATE,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE set_id = l_set_id
AND inventory_item_id = p_association_rec.inventory_item_id;
UPDATE eam_failure_combinations
SET set_id = x_failureset_id
WHERE set_id = p_set_id;
p_last_update_date IN DATE ,
p_last_updated_by IN NUMBER ,
p_last_update_login IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_association_rec EAM_FailureSets_PUB.eam_set_association_rec_type;
l_association_rec.last_update_date := p_last_update_date;
l_association_rec.last_updated_by := p_last_updated_by;
l_association_rec.last_update_login := p_last_update_login;
p_last_update_date IN DATE ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR lock_association(c_set_id NUMBER, c_item_id NUMBER) IS
SELECT failure_code_required, effective_end_date, last_update_date
FROM eam_failure_set_associations
WHERE set_id = c_set_id
AND inventory_item_id = c_item_id
AND effective_end_date IS NULL
FOR UPDATE NOWAIT;
l_last_update_date DATE;
INTO l_fcr, l_end_date, l_last_update_date;
IF (p_last_update_date <> l_last_update_date) THEN
FND_MESSAGE.SET_NAME ('FND', 'FND_RECORD_CHANGED_ERROR');
FND_MESSAGE.SET_NAME ('FND', 'FND_RECORD_DELETED_ERROR');