DBA Data[Home] [Help]

APPS.MTL_CROSS_REFERENCES_PKG SQL Statements

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

Line: 4

   PROCEDURE INSERT_ROW (
         P_SOURCE_SYSTEM_ID       IN NUMBER
        ,P_START_DATE_ACTIVE      IN DATE
        ,P_END_DATE_ACTIVE        IN DATE
        ,P_OBJECT_VERSION_NUMBER  IN NUMBER
        ,P_UOM_CODE               IN VARCHAR2
        ,P_REVISION_ID            IN NUMBER
        ,P_EPC_GTIN_SERIAL        IN NUMBER
        ,P_INVENTORY_ITEM_ID      IN NUMBER
        ,P_ORGANIZATION_ID        IN NUMBER
        ,P_CROSS_REFERENCE_TYPE   IN VARCHAR2
        ,P_CROSS_REFERENCE        IN VARCHAR2
        ,P_ORG_INDEPENDENT_FLAG   IN VARCHAR2
        ,P_REQUEST_ID             IN NUMBER
        ,P_ATTRIBUTE1             IN VARCHAR2
        ,P_ATTRIBUTE2             IN VARCHAR2
        ,P_ATTRIBUTE3             IN VARCHAR2
        ,P_ATTRIBUTE4             IN VARCHAR2
        ,P_ATTRIBUTE5             IN VARCHAR2
        ,P_ATTRIBUTE6             IN VARCHAR2
        ,P_ATTRIBUTE7             IN VARCHAR2
        ,P_ATTRIBUTE8             IN VARCHAR2
        ,P_ATTRIBUTE9             IN VARCHAR2
        ,P_ATTRIBUTE10            IN VARCHAR2
        ,P_ATTRIBUTE11            IN VARCHAR2
        ,P_ATTRIBUTE12            IN VARCHAR2
        ,P_ATTRIBUTE13            IN VARCHAR2
        ,P_ATTRIBUTE14            IN VARCHAR2
        ,P_ATTRIBUTE15            IN VARCHAR2
        ,P_ATTRIBUTE_CATEGORY     IN VARCHAR2
        ,P_DESCRIPTION            IN VARCHAR2
        ,P_CREATION_DATE          IN DATE
        ,P_CREATED_BY             IN NUMBER
        ,P_LAST_UPDATE_DATE       IN DATE
        ,P_LAST_UPDATED_BY        IN NUMBER
        ,P_LAST_UPDATE_LOGIN      IN NUMBER
        ,P_PROGRAM_APPLICATION_ID IN NUMBER
        ,P_PROGRAM_ID             IN NUMBER
        ,P_PROGRAM_UPDATE_DATE    IN DATE
        ,X_CROSS_REFERENCE_ID     OUT NOCOPY NUMBER) IS

    CURSOR C_CHECK_INSERT IS
       SELECT 'Y'
       FROM   MTL_CROSS_REFERENCES_B
       WHERE  CROSS_REFERENCE_ID = X_CROSS_REFERENCE_ID;
Line: 54

      INSERT INTO MTL_CROSS_REFERENCES_B (
             SOURCE_SYSTEM_ID
            ,START_DATE_ACTIVE
            ,END_DATE_ACTIVE
            ,OBJECT_VERSION_NUMBER
            ,UOM_CODE
            ,REVISION_ID
            ,CROSS_REFERENCE_ID
            ,EPC_GTIN_SERIAL
            ,INVENTORY_ITEM_ID
            ,ORGANIZATION_ID
            ,CROSS_REFERENCE_TYPE
            ,CROSS_REFERENCE
            ,ORG_INDEPENDENT_FLAG
            ,REQUEST_ID
            ,ATTRIBUTE1
            ,ATTRIBUTE2
            ,ATTRIBUTE3
            ,ATTRIBUTE4
            ,ATTRIBUTE5
            ,ATTRIBUTE6
            ,ATTRIBUTE7
            ,ATTRIBUTE8
            ,ATTRIBUTE9
            ,ATTRIBUTE10
            ,ATTRIBUTE11
            ,ATTRIBUTE12
            ,ATTRIBUTE13
            ,ATTRIBUTE14
            ,ATTRIBUTE15
            ,ATTRIBUTE_CATEGORY
            ,CREATION_DATE
            ,CREATED_BY
            ,LAST_UPDATE_DATE
            ,LAST_UPDATED_BY
            ,LAST_UPDATE_LOGIN
            ,PROGRAM_APPLICATION_ID
            ,PROGRAM_ID
            ,PROGRAM_UPDATE_DATE)
      VALUES(
             P_SOURCE_SYSTEM_ID
            ,P_START_DATE_ACTIVE
            ,P_END_DATE_ACTIVE
            ,NVL(P_OBJECT_VERSION_NUMBER,1)
            ,P_UOM_CODE
            ,P_REVISION_ID
            ,MTL_CROSS_REFERENCES_B_S.NEXTVAL
            ,NVL(P_EPC_GTIN_SERIAL,0)
            ,P_INVENTORY_ITEM_ID
            ,P_ORGANIZATION_ID
            ,P_CROSS_REFERENCE_TYPE
            ,P_CROSS_REFERENCE
            ,P_ORG_INDEPENDENT_FLAG
            ,P_REQUEST_ID
            ,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_ATTRIBUTE_CATEGORY
            ,NVL(P_CREATION_DATE,SYSDATE)
            ,NVL(P_CREATED_BY,FND_GLOBAL.USER_ID)
            ,NVL(P_LAST_UPDATE_DATE,SYSDATE)
            ,NVL(P_LAST_UPDATED_BY,FND_GLOBAL.USER_ID)
            ,NVL(P_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID)
            ,P_PROGRAM_APPLICATION_ID
            ,P_PROGRAM_ID
            ,P_PROGRAM_UPDATE_DATE)
      RETURNING CROSS_REFERENCE_ID INTO X_CROSS_REFERENCE_ID ;
Line: 134

      INSERT INTO MTL_CROSS_REFERENCES_TL (
             LAST_UPDATE_LOGIN
            ,DESCRIPTION
            ,CREATION_DATE
            ,CREATED_BY
            ,LAST_UPDATE_DATE
            ,LAST_UPDATED_BY
            ,CROSS_REFERENCE_ID
            ,LANGUAGE
            ,SOURCE_LANG)
      SELECT
            NVL(P_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID),
            P_DESCRIPTION,
            NVL(P_CREATION_DATE,SYSDATE),
            NVL(P_CREATED_BY,FND_GLOBAL.USER_ID),
            NVL(P_LAST_UPDATE_DATE,SYSDATE),
            NVL(P_LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
            X_CROSS_REFERENCE_ID,
            L.LANGUAGE_CODE,
            USERENV('LANG')
      FROM  FND_LANGUAGES L
      WHERE L.INSTALLED_FLAG in ('I', 'B')
      AND   NOT EXISTS  (SELECT NULL
                         FROM   MTL_CROSS_REFERENCES_TL T
                        WHERE   T.CROSS_REFERENCE_ID = X_CROSS_REFERENCE_ID
                        AND     T.LANGUAGE = L.LANGUAGE_CODE);
Line: 161

      OPEN C_CHECK_INSERT;
Line: 162

      FETCH C_CHECK_INSERT INTO l_exists;
Line: 163

      IF (C_CHECK_INSERT%NOTFOUND) THEN
         CLOSE C_CHECK_INSERT;
Line: 167

      CLOSE C_CHECK_INSERT;
Line: 168

   END INSERT_ROW;
Line: 203

      SELECT
          SOURCE_SYSTEM_ID
         ,START_DATE_ACTIVE
         ,END_DATE_ACTIVE
         ,OBJECT_VERSION_NUMBER
         ,UOM_CODE
         ,REVISION_ID
         ,EPC_GTIN_SERIAL
         ,INVENTORY_ITEM_ID
         ,ORGANIZATION_ID
         ,CROSS_REFERENCE_TYPE
         ,CROSS_REFERENCE
         ,ORG_INDEPENDENT_FLAG
         ,REQUEST_ID
         ,ATTRIBUTE1
         ,ATTRIBUTE2
         ,ATTRIBUTE3
         ,ATTRIBUTE4
         ,ATTRIBUTE5
         ,ATTRIBUTE6
         ,ATTRIBUTE7
         ,ATTRIBUTE8
         ,ATTRIBUTE9
         ,ATTRIBUTE10
         ,ATTRIBUTE11
         ,ATTRIBUTE12
         ,ATTRIBUTE13
         ,ATTRIBUTE14
         ,ATTRIBUTE15
         ,ATTRIBUTE_CATEGORY
      FROM MTL_CROSS_REFERENCES_B
      WHERE CROSS_REFERENCE_ID = P_CROSS_REFERENCE_ID
      FOR UPDATE OF CROSS_REFERENCE_ID NOWAIT;
Line: 238

      SELECT
          DESCRIPTION
         ,DECODE(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
      FROM MTL_CROSS_REFERENCES_TL
      WHERE CROSS_REFERENCE_ID = P_CROSS_REFERENCE_ID
      AND   USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
      FOR UPDATE OF CROSS_REFERENCE_ID NOWAIT;
Line: 254

         FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
Line: 329

   PROCEDURE UPDATE_ROW (
         P_CROSS_REFERENCE_ID     IN NUMBER
        ,P_SOURCE_SYSTEM_ID       IN NUMBER
        ,P_START_DATE_ACTIVE      IN DATE
        ,P_END_DATE_ACTIVE        IN DATE
        ,P_UOM_CODE               IN VARCHAR2
        ,P_REVISION_ID            IN NUMBER
        ,P_EPC_GTIN_SERIAL        IN NUMBER
        ,P_INVENTORY_ITEM_ID      IN NUMBER
        ,P_ORGANIZATION_ID        IN NUMBER
        ,P_CROSS_REFERENCE_TYPE   IN VARCHAR2
        ,P_CROSS_REFERENCE        IN VARCHAR2
        ,P_ORG_INDEPENDENT_FLAG   IN VARCHAR2
        ,P_REQUEST_ID             IN NUMBER
        ,P_ATTRIBUTE1             IN VARCHAR2
        ,P_ATTRIBUTE2             IN VARCHAR2
        ,P_ATTRIBUTE3             IN VARCHAR2
        ,P_ATTRIBUTE4             IN VARCHAR2
        ,P_ATTRIBUTE5             IN VARCHAR2
        ,P_ATTRIBUTE6             IN VARCHAR2
        ,P_ATTRIBUTE7             IN VARCHAR2
        ,P_ATTRIBUTE8             IN VARCHAR2
        ,P_ATTRIBUTE9             IN VARCHAR2
        ,P_ATTRIBUTE10            IN VARCHAR2
        ,P_ATTRIBUTE11            IN VARCHAR2
        ,P_ATTRIBUTE12            IN VARCHAR2
        ,P_ATTRIBUTE13            IN VARCHAR2
        ,P_ATTRIBUTE14            IN VARCHAR2
        ,P_ATTRIBUTE15            IN VARCHAR2
        ,P_ATTRIBUTE_CATEGORY     IN VARCHAR2
        ,P_DESCRIPTION            IN VARCHAR2
        ,P_LAST_UPDATE_DATE       IN DATE
        ,P_LAST_UPDATED_BY        IN NUMBER
        ,P_LAST_UPDATE_LOGIN      IN NUMBER
        ,X_OBJECT_VERSION_NUMBER  OUT NOCOPY NUMBER) IS
   BEGIN
      UPDATE MTL_CROSS_REFERENCES_B
      SET
             SOURCE_SYSTEM_ID      = P_SOURCE_SYSTEM_ID
            ,START_DATE_ACTIVE     = P_START_DATE_ACTIVE
            ,END_DATE_ACTIVE       = P_END_DATE_ACTIVE
            ,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
            ,UOM_CODE              = P_UOM_CODE
            ,REVISION_ID           = P_REVISION_ID
            ,EPC_GTIN_SERIAL       = NVL(P_EPC_GTIN_SERIAL,EPC_GTIN_SERIAL)
            ,INVENTORY_ITEM_ID     = P_INVENTORY_ITEM_ID
            ,ORGANIZATION_ID       = P_ORGANIZATION_ID
            ,CROSS_REFERENCE_TYPE  = P_CROSS_REFERENCE_TYPE
            ,CROSS_REFERENCE       = P_CROSS_REFERENCE
            ,ORG_INDEPENDENT_FLAG  = P_ORG_INDEPENDENT_FLAG
            ,REQUEST_ID            = P_REQUEST_ID
            ,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
            ,ATTRIBUTE_CATEGORY    = P_ATTRIBUTE_CATEGORY
            ,LAST_UPDATE_DATE      = NVL(P_LAST_UPDATE_DATE,SYSDATE)
            ,LAST_UPDATED_BY       = NVL(P_LAST_UPDATED_BY,FND_GLOBAL.USER_ID)
            ,LAST_UPDATE_LOGIN     = NVL(P_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID)
      WHERE CROSS_REFERENCE_ID = P_CROSS_REFERENCE_ID
      RETURNING OBJECT_VERSION_NUMBER INTO X_OBJECT_VERSION_NUMBER;
Line: 406

      UPDATE MTL_CROSS_REFERENCES_TL
      SET    DESCRIPTION       = P_DESCRIPTION,
             LAST_UPDATE_DATE  = NVL(P_LAST_UPDATE_DATE,SYSDATE),
             LAST_UPDATED_BY   = NVL(P_LAST_UPDATE_LOGIN,FND_GLOBAL.USER_ID),
             LAST_UPDATE_LOGIN = NVL(P_LAST_UPDATED_BY,FND_GLOBAL.LOGIN_ID),
             SOURCE_LANG       = USERENV('LANG')
      WHERE CROSS_REFERENCE_ID = P_CROSS_REFERENCE_ID
      AND   USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
Line: 419

   END UPDATE_ROW;
Line: 421

   PROCEDURE DELETE_ROW (P_CROSS_REFERENCE_ID IN NUMBER) IS
   BEGIN

      DELETE FROM MTL_CROSS_REFERENCES_TL
      WHERE CROSS_REFERENCE_ID = P_CROSS_REFERENCE_ID;
Line: 431

      DELETE FROM MTL_CROSS_REFERENCES_B
      WHERE CROSS_REFERENCE_ID = P_CROSS_REFERENCE_ID;
Line: 438

   END DELETE_ROW;
Line: 442

      DELETE MTL_CROSS_REFERENCES_TL T
      WHERE NOT EXISTS (SELECT NULL
                        FROM   MTL_CROSS_REFERENCES_B B
                        WHERE  B.CROSS_REFERENCE_ID = T.CROSS_REFERENCE_ID);
Line: 447

      UPDATE MTL_CROSS_REFERENCES_TL T
      SET (DESCRIPTION) = (SELECT B.DESCRIPTION
                           FROM   MTL_CROSS_REFERENCES_TL B
                           WHERE  B.CROSS_REFERENCE_ID = T.CROSS_REFERENCE_ID
                           AND    B.LANGUAGE = T.SOURCE_LANG)
      WHERE (T.CROSS_REFERENCE_ID,T.LANGUAGE) IN
            (SELECT SUBT.CROSS_REFERENCE_ID
                   ,SUBT.LANGUAGE
             FROM MTL_CROSS_REFERENCES_TL SUBB,
                  MTL_CROSS_REFERENCES_TL SUBT
             WHERE SUBB.CROSS_REFERENCE_ID = SUBT.CROSS_REFERENCE_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: 463

      INSERT INTO MTL_CROSS_REFERENCES_TL (
             LAST_UPDATE_LOGIN
            ,DESCRIPTION
            ,CREATION_DATE
            ,CREATED_BY
            ,LAST_UPDATE_DATE
            ,LAST_UPDATED_BY
            ,CROSS_REFERENCE_ID
            ,LANGUAGE
            ,SOURCE_LANG)
      SELECT /*+ ORDERED */
             B.LAST_UPDATE_LOGIN
            ,B.DESCRIPTION
            ,B.CREATION_DATE
            ,B.CREATED_BY
            ,B.LAST_UPDATE_DATE
            ,B.LAST_UPDATED_BY
            ,B.CROSS_REFERENCE_ID
            ,L.LANGUAGE_CODE
            ,B.SOURCE_LANG
      FROM  MTL_CROSS_REFERENCES_TL B,
            FND_LANGUAGES L
      WHERE L.INSTALLED_FLAG in ('I', 'B')
      AND   B.LANGUAGE = USERENV('LANG')
      AND   NOT EXISTS (SELECT NULL
                        FROM MTL_CROSS_REFERENCES_TL T
                        WHERE T.CROSS_REFERENCE_ID = B.CROSS_REFERENCE_ID
                        AND   T.LANGUAGE = L.LANGUAGE_CODE);