The following lines contain the word 'select', 'insert', 'update' or 'delete':
FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_INVALID_UPDATE' );
p_x_effectivity_rec.last_update_date := SYSDATE;
p_x_effectivity_rec.last_updated_by := FND_GLOBAL.user_id;
p_x_effectivity_rec.last_update_login := FND_GLOBAL.login_id;
SELECT name,
inventory_item_id,
item_number,
relationship_id,
position_ref_meaning,
position_inventory_item_id,
position_item_number,
pc_node_id,
pc_node_name,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM AHL_MR_EFFECTIVITIES_V
WHERE mr_effectivity_id = c_mr_effectivity_id;
SELECT 'X'
FROM MTL_SYSTEM_ITEMS_KFV MTL,
FND_LOOKUP_VALUES_VL IT,
AHL_POSITION_ALTERNATES_V PA
WHERE MTL.SERVICE_ITEM_FLAG = 'N'
AND IT.LOOKUP_CODE (+) = MTL.ITEM_TYPE
AND IT.LOOKUP_TYPE (+) = 'ITEM_TYPE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(IT.START_DATE_ACTIVE,SYSDATE))
AND TRUNC(NVL(IT.END_DATE_ACTIVE,SYSDATE + 1))
AND MTL.INVENTORY_ITEM_ID = PA.INVENTORY_ITEM_ID
AND PA.INVENTORY_ORG_ID = MTL.ORGANIZATION_ID
AND PA.RELATIONSHIP_ID = c_relationship_id
AND MTL.INVENTORY_ITEM_ID = c_inventory_item_id ;
SELECT DISTINCT EFD.manufacturer_id
FROM AHL_MR_EFFECTIVITY_DTLS_APP_V EFD,
AHL_MR_EFFECTIVITIES_APP_V EF
WHERE EFD.manufacturer_id IS NOT NULL
AND EFD.mr_effectivity_id = EF.mr_effectivity_id
AND EF.mr_effectivity_id = c_mr_effectivity_id;
SELECT DISTINCT INT.counter_id
FROM AHL_MR_INTERVALS_APP_V INT,
AHL_MR_EFFECTIVITIES_APP_V EF
WHERE INT.mr_effectivity_id = EF.mr_effectivity_id
AND EF.mr_effectivity_id = c_mr_effectivity_id;
SELECT program_type_code
FROM ahl_mr_headers_b
WHERE mr_header_id = p_mr_header_id;
SELECT name
FROM AHL_MR_EFFECTIVITIES_APP_V
WHERE mr_header_id = c_mr_header_id
GROUP BY name
HAVING count(*) > 1;
SELECT inventory_item_id
FROM AHL_MR_EFFECTIVITIES_APP_V
WHERE mr_header_id = c_mr_header_id
GROUP BY inventory_item_id
HAVING count(*) > 1;
select item_number
from ahl_mr_effectivities_v
where inventory_item_id = c_inventory_item_id;
INSERT INTO AHL_MR_EFFECTIVITIES
(
MR_EFFECTIVITY_ID,
OBJECT_VERSION_NUMBER,
MR_HEADER_ID,
NAME,
INVENTORY_ITEM_ID,
RELATIONSHIP_ID,
PC_NODE_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
) VALUES
(
AHL_MR_EFFECTIVITIES_S.NEXTVAL,
p_x_effectivity_tbl(i).object_version_number,
p_mr_header_id,
p_x_effectivity_tbl(i).name,
DECODE( p_x_effectivity_tbl(i).relationship_id, NULL,
p_x_effectivity_tbl(i).inventory_item_id,
p_x_effectivity_tbl(i).position_inventory_item_id ),
p_x_effectivity_tbl(i).relationship_id,
p_x_effectivity_tbl(i).pc_node_id,
p_x_effectivity_tbl(i).attribute_category,
p_x_effectivity_tbl(i).attribute1,
p_x_effectivity_tbl(i).attribute2,
p_x_effectivity_tbl(i).attribute3,
p_x_effectivity_tbl(i).attribute4,
p_x_effectivity_tbl(i).attribute5,
p_x_effectivity_tbl(i).attribute6,
p_x_effectivity_tbl(i).attribute7,
p_x_effectivity_tbl(i).attribute8,
p_x_effectivity_tbl(i).attribute9,
p_x_effectivity_tbl(i).attribute10,
p_x_effectivity_tbl(i).attribute11,
p_x_effectivity_tbl(i).attribute12,
p_x_effectivity_tbl(i).attribute13,
p_x_effectivity_tbl(i).attribute14,
p_x_effectivity_tbl(i).attribute15,
p_x_effectivity_tbl(i).last_update_date,
p_x_effectivity_tbl(i).last_updated_by,
p_x_effectivity_tbl(i).creation_date,
p_x_effectivity_tbl(i).created_by,
p_x_effectivity_tbl(i).last_update_login
) RETURNING mr_effectivity_id INTO l_mr_effectivity_id;
UPDATE AHL_MR_EFFECTIVITIES SET
object_version_number = object_version_number + 1,
name = p_x_effectivity_tbl(i).name,
inventory_item_id = DECODE(
p_x_effectivity_tbl(i).relationship_id,
NULL,
p_x_effectivity_tbl(i).inventory_item_id,
p_x_effectivity_tbl(i).position_inventory_item_id ),
relationship_id = p_x_effectivity_tbl(i).relationship_id,
pc_node_id = p_x_effectivity_tbl(i).pc_node_id,
attribute_category = p_x_effectivity_tbl(i).attribute_category,
attribute1 = p_x_effectivity_tbl(i).attribute1,
attribute2 = p_x_effectivity_tbl(i).attribute2,
attribute3 = p_x_effectivity_tbl(i).attribute3,
attribute4 = p_x_effectivity_tbl(i).attribute4,
attribute5 = p_x_effectivity_tbl(i).attribute5,
attribute6 = p_x_effectivity_tbl(i).attribute6,
attribute7 = p_x_effectivity_tbl(i).attribute7,
attribute8 = p_x_effectivity_tbl(i).attribute8,
attribute9 = p_x_effectivity_tbl(i).attribute9,
attribute10 = p_x_effectivity_tbl(i).attribute10,
attribute11 = p_x_effectivity_tbl(i).attribute11,
attribute12 = p_x_effectivity_tbl(i).attribute12,
attribute13 = p_x_effectivity_tbl(i).attribute13,
attribute14 = p_x_effectivity_tbl(i).attribute14,
attribute15 = p_x_effectivity_tbl(i).attribute15,
last_update_date = p_x_effectivity_tbl(i).last_update_date,
last_updated_by = p_x_effectivity_tbl(i).last_updated_by,
last_update_login = p_x_effectivity_tbl(i).last_update_login
WHERE mr_effectivity_id = p_x_effectivity_tbl(i).mr_effectivity_id
AND object_version_number = p_x_effectivity_tbl(i).object_version_number;
DELETE AHL_MR_EFFECTIVITIES
WHERE mr_effectivity_id = p_x_effectivity_tbl(i).mr_effectivity_id
AND object_version_number = p_x_effectivity_tbl(i).object_version_number;
DELETE AHL_MR_EFFECTIVITY_DTLS
WHERE mr_effectivity_id = p_x_effectivity_tbl(i).mr_effectivity_id;
DELETE AHL_MR_INTERVALS
WHERE mr_effectivity_id = p_x_effectivity_tbl(i).mr_effectivity_id;