APPS.MTL_ITEM_REVISIONS_UTIL SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 14

   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;

Line: 66

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;

Line: 149

   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);

Line: 201

END INSERT_ROW;

Line: 206

     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;

Line: 243

      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;

Line: 260

      fnd_message.set_name('FND', 'FORM_RECORD_DELETED');

Line: 338

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;

Line: 378

   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);

Line: 397

        ,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);

Line: 407

END UPDATE_ROW;

Line: 414

/*   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);

Line: 421

   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)));

Line: 445

   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 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 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);