The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT revision_id
FROM mtl_item_revisions_b
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id
AND revision < cp_revision
AND implementation_date IS NOT NULL
AND effectivity_date <= sysdate
ORDER BY effectivity_date desc;
PROCEDURE INSERT_ROW(P_Item_Revision_Rec IN MTL_ITEM_REVISIONS_B%ROWTYPE,
X_ROWID OUT NOCOPY VARCHAR2) IS
BEGIN
INSERT INTO MTL_ITEM_REVISIONS_B (
REVISION_ID,
REVISION_LABEL,
REVISION_REASON,
LIFECYCLE_ID,
CURRENT_PHASE_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
REVISION,
CHANGE_NOTICE,
ECN_INITIATION_DATE,
IMPLEMENTATION_DATE,
IMPLEMENTED_SERIAL_NUMBER,
EFFECTIVITY_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
REVISED_ITEM_SEQUENCE_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) VALUES (
P_Item_Revision_Rec.REVISION_ID,
P_Item_Revision_Rec.REVISION_LABEL,
P_Item_Revision_Rec.REVISION_REASON,
P_Item_Revision_Rec.LIFECYCLE_ID,
P_Item_Revision_Rec.CURRENT_PHASE_ID,
P_Item_Revision_Rec.INVENTORY_ITEM_ID,
P_Item_Revision_Rec.ORGANIZATION_ID,
P_Item_Revision_Rec.REVISION,
P_Item_Revision_Rec.CHANGE_NOTICE,
P_Item_Revision_Rec.ECN_INITIATION_DATE,
P_Item_Revision_Rec.IMPLEMENTATION_DATE,
P_Item_Revision_Rec.IMPLEMENTED_SERIAL_NUMBER,
P_Item_Revision_Rec.EFFECTIVITY_DATE,
P_Item_Revision_Rec.ATTRIBUTE_CATEGORY,
P_Item_Revision_Rec.ATTRIBUTE1,
P_Item_Revision_Rec.ATTRIBUTE2,
P_Item_Revision_Rec.ATTRIBUTE3,
P_Item_Revision_Rec.ATTRIBUTE4,
P_Item_Revision_Rec.ATTRIBUTE5,
P_Item_Revision_Rec.ATTRIBUTE6,
P_Item_Revision_Rec.ATTRIBUTE7,
P_Item_Revision_Rec.ATTRIBUTE8,
P_Item_Revision_Rec.ATTRIBUTE9,
P_Item_Revision_Rec.ATTRIBUTE10,
P_Item_Revision_Rec.ATTRIBUTE11,
P_Item_Revision_Rec.ATTRIBUTE12,
P_Item_Revision_Rec.ATTRIBUTE13,
P_Item_Revision_Rec.ATTRIBUTE14,
P_Item_Revision_Rec.ATTRIBUTE15,
P_Item_Revision_Rec.REQUEST_ID,
P_Item_Revision_Rec.REVISED_ITEM_SEQUENCE_ID,
NVL(P_Item_Revision_Rec.OBJECT_VERSION_NUMBER,1),
P_Item_Revision_Rec.CREATION_DATE,
P_Item_Revision_Rec.CREATED_BY,
P_Item_Revision_Rec.LAST_UPDATE_DATE,
P_Item_Revision_Rec.LAST_UPDATED_BY,
P_Item_Revision_Rec.LAST_UPDATE_LOGIN
) RETURNING ROWID INTO X_ROWID;
INSERT INTO MTL_ITEM_REVISIONS_TL (
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
REVISION_ID,
DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) SELECT P_Item_Revision_Rec.INVENTORY_ITEM_ID,
P_Item_Revision_Rec.ORGANIZATION_ID,
P_Item_Revision_Rec.REVISION_ID,
P_Item_Revision_Rec.DESCRIPTION,
P_Item_Revision_Rec.CREATION_DATE,
P_Item_Revision_Rec.CREATED_BY,
P_Item_Revision_Rec.LAST_UPDATE_DATE,
P_Item_Revision_Rec.LAST_UPDATED_BY,
P_Item_Revision_Rec.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
USERENV('LANG')
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND NOT EXISTS (SELECT NULL
FROM MTL_ITEM_REVISIONS_TL T
WHERE T.INVENTORY_ITEM_ID = P_Item_Revision_Rec.INVENTORY_ITEM_ID
AND T.ORGANIZATION_ID = P_Item_Revision_Rec.ORGANIZATION_ID
AND T.REVISION_ID = P_Item_Revision_Rec.REVISION_ID
AND T.LANGUAGE = L.LANGUAGE_CODE);
END INSERT_ROW;
SELECT
REVISION_LABEL,
REVISION_REASON,
LIFECYCLE_ID,
CURRENT_PHASE_ID,
REVISION,
CHANGE_NOTICE,
ECN_INITIATION_DATE,
IMPLEMENTATION_DATE,
IMPLEMENTED_SERIAL_NUMBER,
EFFECTIVITY_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
REVISED_ITEM_SEQUENCE_ID,
OBJECT_VERSION_NUMBER
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = P_Item_Revision_Rec.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = P_Item_Revision_Rec.ORGANIZATION_ID
AND REVISION_ID = P_Item_Revision_Rec.REVISION_ID
FOR UPDATE OF INVENTORY_ITEM_ID NOWAIT;
SELECT
DESCRIPTION,
DECODE(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
FROM MTL_ITEM_REVISIONS_TL
WHERE INVENTORY_ITEM_ID = P_Item_Revision_Rec.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = P_Item_Revision_Rec.ORGANIZATION_ID
AND REVISION_ID = P_Item_Revision_Rec.REVISION_ID
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
FOR UPDATE OF INVENTORY_ITEM_ID NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE UPDATE_ROW (P_Item_Revision_Rec IN MTL_ITEM_REVISIONS_B%ROWTYPE) IS
BEGIN
UPDATE MTL_ITEM_REVISIONS_B
SET
REVISION = P_Item_Revision_Rec.REVISION,
REVISION_LABEL = P_Item_Revision_Rec.REVISION_LABEL,--Bug: 3017253
CHANGE_NOTICE = P_Item_Revision_Rec.CHANGE_NOTICE,
ECN_INITIATION_DATE = P_Item_Revision_Rec.ECN_INITIATION_DATE,
IMPLEMENTATION_DATE = P_Item_Revision_Rec.IMPLEMENTATION_DATE,
EFFECTIVITY_DATE = DECODE(TRUNC(P_Item_Revision_Rec.EFFECTIVITY_DATE),TRUNC(EFFECTIVITY_DATE),EFFECTIVITY_DATE,TRUNC(SYSDATE),SYSDATE,P_Item_Revision_Rec.EFFECTIVITY_DATE),
ATTRIBUTE_CATEGORY = P_Item_Revision_Rec.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = P_Item_Revision_Rec.ATTRIBUTE1,
ATTRIBUTE2 = P_Item_Revision_Rec.ATTRIBUTE2,
ATTRIBUTE3 = P_Item_Revision_Rec.ATTRIBUTE3,
ATTRIBUTE4 = P_Item_Revision_Rec.ATTRIBUTE4,
ATTRIBUTE5 = P_Item_Revision_Rec.ATTRIBUTE5,
ATTRIBUTE6 = P_Item_Revision_Rec.ATTRIBUTE6,
ATTRIBUTE7 = P_Item_Revision_Rec.ATTRIBUTE7,
ATTRIBUTE8 = P_Item_Revision_Rec.ATTRIBUTE8,
ATTRIBUTE9 = P_Item_Revision_Rec.ATTRIBUTE9,
ATTRIBUTE10 = P_Item_Revision_Rec.ATTRIBUTE10,
ATTRIBUTE11 = P_Item_Revision_Rec.ATTRIBUTE11,
ATTRIBUTE12 = P_Item_Revision_Rec.ATTRIBUTE12,
ATTRIBUTE13 = P_Item_Revision_Rec.ATTRIBUTE13,
ATTRIBUTE14 = P_Item_Revision_Rec.ATTRIBUTE14,
ATTRIBUTE15 = P_Item_Revision_Rec.ATTRIBUTE15,
LAST_UPDATE_DATE = P_Item_Revision_Rec.LAST_UPDATE_DATE,
LAST_UPDATED_BY = P_Item_Revision_Rec.LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = P_Item_Revision_Rec.LAST_UPDATE_LOGIN,
/* Bug 4224512 : Incrementing OBJECT_VERSION_NUMBER each time revision is updated - Anmurali*/
OBJECT_VERSION_NUMBER = NVL(OBJECT_VERSION_NUMBER,1)+1
WHERE INVENTORY_ITEM_ID = P_Item_Revision_Rec.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = P_Item_Revision_Rec.ORGANIZATION_ID
AND REVISION_ID = P_Item_Revision_Rec.REVISION_ID;
UPDATE MTL_ITEM_REVISIONS_TL set
DESCRIPTION = P_Item_Revision_Rec.DESCRIPTION,
LAST_UPDATE_DATE = P_Item_Revision_Rec.LAST_UPDATE_DATE,
LAST_UPDATED_BY = P_Item_Revision_Rec.LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = P_Item_Revision_Rec.LAST_UPDATE_LOGIN,
SOURCE_LANG = USERENV('LANG')
WHERE INVENTORY_ITEM_ID = P_Item_Revision_Rec.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = P_Item_Revision_Rec.ORGANIZATION_ID
AND REVISION_ID = P_Item_Revision_Rec.REVISION_ID
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
,p_dml_type => 'UPDATE'
,p_inventory_item_id => p_Item_Revision_rec.Inventory_Item_Id
,p_organization_id => p_Item_Revision_rec.Organization_Id
,p_revision_id => p_Item_Revision_rec.revision_id);
END UPDATE_ROW;
/* DELETE FROM MTL_ITEM_REVISIONS_TL T
WHERE NOT EXISTS(SELECT NULL
FROM MTL_ITEM_REVISIONS_B B
WHERE B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
AND B.REVISION_ID = T.REVISION_ID);
UPDATE MTL_ITEM_REVISIONS_TL T
SET (DESCRIPTION) = (SELECT B.DESCRIPTION
FROM MTL_ITEM_REVISIONS_TL B
WHERE B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
AND B.REVISION_ID = T.REVISION_ID
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (T.INVENTORY_ITEM_ID,
T.ORGANIZATION_ID,
T.REVISION_ID,
T.LANGUAGE) IN (SELECT SUBT.INVENTORY_ITEM_ID,
SUBT.ORGANIZATION_ID,
SUBT.REVISION_ID,
SUBT.LANGUAGE
FROM MTL_ITEM_REVISIONS_TL SUBB,
MTL_ITEM_REVISIONS_TL SUBT
WHERE SUBB.INVENTORY_ITEM_ID = SUBT.INVENTORY_ITEM_ID
AND SUBB.ORGANIZATION_ID = SUBT.ORGANIZATION_ID
AND SUBB.REVISION_ID = SUBT.REVISION_ID
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)));
INSERT /*+ append parallel(tl) */
INTO MTL_ITEM_REVISIONS_TL tl (
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
REVISION_ID,
DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) SELECT /*+ PARALLEL(B) PARALLEL(L) */
B.INVENTORY_ITEM_ID,
B.ORGANIZATION_ID,
B.REVISION_ID,
B.DESCRIPTION,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
FROM MTL_ITEM_REVISIONS_TL B,
FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
AND NOT EXISTS (SELECT /*+ parallel(T) */ NULL
FROM MTL_ITEM_REVISIONS_TL T
WHERE T.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND T.ORGANIZATION_ID = B.ORGANIZATION_ID
AND T.REVISION_ID = B.REVISION_ID
AND T.LANGUAGE = L.LANGUAGE_CODE);