DBA Data[Home] [Help]

APPS.BOM_ALTERNATE_DESIGNATORS_PKG SQL Statements

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

Line: 15

   , LAST_UPDATE_DATE   DATE
   , LAST_UPDATED_BY    NUMBER
   , CREATION_DATE	DATE
   , CREATED_BY		NUMBER
   , LAST_UPDATE_LOGIN	NUMBER
   , DESCRIPTION	VARCHAR2(240)
   , DISABLE_DATE	DATE
   , ATTRIBUTE_CATEGORY VARCHAR2(30)
   , ATTRIBUTE1		VARCHAR2(150)
   , ATTRIBUTE2		VARCHAR2(150)
   , ATTRIBUTE3		VARCHAR2(150)
   , ATTRIBUTE4		VARCHAR2(150)
   , ATTRIBUTE5		VARCHAR2(150)
   , ATTRIBUTE6		VARCHAR2(150)
   , ATTRIBUTE7		VARCHAR2(150)
   , ATTRIBUTE8		VARCHAR2(150)
   , ATTRIBUTE9		VARCHAR2(150)
   , ATTRIBUTE10	VARCHAR2(150)
   , ATTRIBUTE11	VARCHAR2(150)
   , ATTRIBUTE12	VARCHAR2(150)
   , ATTRIBUTE13	VARCHAR2(150)
   , ATTRIBUTE14	VARCHAR2(150)
   , ATTRIBUTE15	VARCHAR2(150)
   , REQUEST_ID		NUMBER
   , PROGRAM_APPLICATION_ID NUMBER
   , PROGRAM_ID		NUMBER
   , PROGRAM_UPDATE_DATE DATE
   , STRUCTURE_TYPE_ID	NUMBER
   , IS_PREFERRED      VARCHAR2(1)
--- Extra attributes added for internal usage
   , DISPLAY_NAME	VARCHAR2(80)
   , Alt_Desig_Code_Old	VARCHAR2(10)
   , api_version	NUMBER
  );
Line: 55

PROCEDURE	Insert_Row (p_alt_desig_rec   IN Bom_Alt_Desig_Rec
       ,x_return_status                 OUT NOCOPY VARCHAR2
       ,x_errorcode                     OUT NOCOPY NUMBER
       ,x_msg_count                     OUT NOCOPY NUMBER
       ,x_msg_data                      OUT NOCOPY VARCHAR2
);
Line: 62

PROCEDURE	Update_Row (p_alt_desig_rec   IN Bom_Alt_Desig_Rec
       ,x_return_status                 OUT NOCOPY VARCHAR2
       ,x_errorcode                     OUT NOCOPY NUMBER
       ,x_msg_count                     OUT NOCOPY NUMBER
       ,x_msg_data                      OUT NOCOPY VARCHAR2
);
Line: 86

	 SELECT 1
	 FROM BOM_BILL_OF_MATERIALS
	 WHERE BOM_BILL_OF_MATERIALS.Alternate_Bom_Designator =
	       X_Alt_Desig_Code
	 UNION
	 SELECT 1
	 FROM BOM_OPERATIONAL_ROUTINGS
	 WHERE BOM_OPERATIONAL_ROUTINGS.ALTERNATE_ROUTING_DESIGNATOR =
	       X_Alt_Desig_Code
	 UNION
         SELECT 1
	 FROM CST_COST_TYPES
	 WHERE CST_COST_TYPES.ALTERNATE_BOM_DESIGNATOR =
	       X_Alt_Desig_Code;
Line: 127

    SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
      (SELECT 1 FROM BOM_ALTERNATE_DESIGNATORS
       WHERE Organization_Id = X_Organization_Id
       AND Alternate_Designator_Code  = X_Alternate_Designator_Code
       );
Line: 147

    SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
      (SELECT 1 FROM BOM_BILL_OF_MATERIALS
       WHERE BOM_BILL_OF_MATERIALS.Organization_ID = X_Organization_Id
	 AND BOM_BILL_OF_MATERIALS.Alternate_Bom_Designator =
	     X_Alternate_Designator_Code
       );
Line: 154

    SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
      (SELECT 1 FROM BOM_OPERATIONAL_ROUTINGS
       WHERE BOM_OPERATIONAL_ROUTINGS.Organization_Id = X_Organization_Id
       AND BOM_OPERATIONAL_ROUTINGS.ALTERNATE_ROUTING_DESIGNATOR =
       X_Alternate_Designator_Code
      );
Line: 161

    SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
      (SELECT 1 FROM CST_COST_TYPES
       WHERE CST_COST_TYPES.Organization_Id = X_Organization_Id
	 AND CST_COST_TYPES.ALTERNATE_BOM_DESIGNATOR =
	     X_Alternate_Designator_Code
       );
Line: 175

PROCEDURE Insert_Row ( --- not used, retaining for the moment
        p_api_version                   IN   NUMBER
       ,p_alt_desig_code	        IN   VARCHAR2
       ,p_organization_id               IN   NUMBER
       ,p_description                   IN   VARCHAR2
       ,p_disable_date                  IN   DATE
       ,p_structure_type_id             IN   NUMBER
       ,x_return_status                 OUT NOCOPY VARCHAR2
       ,x_errorcode                     OUT NOCOPY NUMBER
       ,x_msg_count                     OUT NOCOPY NUMBER
       ,x_msg_data                      OUT NOCOPY VARCHAR2
) IS
  BEGIN
    Insert_Row (
        p_api_version           => p_api_version
       ,p_alt_desig_code        => p_alt_desig_code
       ,p_organization_id       => p_organization_id
       ,p_display_name          => p_alt_desig_code
       ,p_description           => p_description
       ,p_disable_date          => p_disable_date
       ,p_structure_type_id     => p_structure_type_id
       ,p_is_preferred          => null
       ,x_return_status         => x_return_status
       ,x_errorcode             => x_errorcode
       ,x_msg_count             => x_msg_count
       ,x_msg_data              => x_msg_data
    );
Line: 203

END Insert_row;
Line: 207

PROCEDURE Insert_Row ( --- Called by OA Pages
        p_api_version                   IN   NUMBER
       ,p_alt_desig_code                IN   VARCHAR2
       ,p_organization_id               IN   NUMBER
       ,p_display_name                  IN   VARCHAR2
       ,p_description                   IN   VARCHAR2
       ,p_disable_date                  IN   DATE
       ,p_structure_type_id             IN   NUMBER
       ,p_is_preferred			IN  VARCHAR2
       ,x_return_status                 OUT NOCOPY VARCHAR2
       ,x_errorcode                     OUT NOCOPY NUMBER
       ,x_msg_count                     OUT NOCOPY NUMBER
       ,x_msg_data                      OUT NOCOPY VARCHAR2
) IS
    l_api_version            CONSTANT NUMBER := 1.0;
Line: 227

	rec.LAST_UPDATE_DATE          := l_Sysdate;
Line: 228

	rec.LAST_UPDATED_BY           := g_current_user_id;
Line: 231

	rec.LAST_UPDATE_LOGIN         := g_current_login_id;
Line: 253

	rec.PROGRAM_UPDATE_DATE       := NULL;
Line: 260

	Insert_Row (p_alt_desig_rec   => rec,
	x_return_status => x_return_status,
	x_errorcode => x_errorcode,
	x_msg_count => x_msg_count,
	x_msg_data => x_msg_data);
Line: 266

END Insert_row;
Line: 270

PROCEDURE Update_Row ( --- not used, retaining for the moment
        p_api_version                   IN   NUMBER
       ,p_alt_desig_code_old		IN   VARCHAR2
       ,p_organization_id               IN   NUMBER
       ,p_alt_desig_code_new		IN   VARCHAR2
       ,p_description                   IN   VARCHAR2
       ,p_disable_date                  IN   DATE
       ,p_structure_type_id             IN   NUMBER
       ,x_return_status                 OUT NOCOPY VARCHAR2
       ,x_errorcode                     OUT NOCOPY NUMBER
       ,x_msg_count                     OUT NOCOPY NUMBER
       ,x_msg_data                      OUT NOCOPY VARCHAR2
) IS
  BEGIN

    Update_Row (
        p_api_version           => p_api_version
       ,p_alt_desig_code_old    => p_alt_desig_code_old
       ,p_organization_id       => p_organization_id
       ,p_alt_desig_code_new    => p_alt_desig_code_new
       ,p_display_name_new      => p_alt_desig_code_new
       ,p_description           => p_description
       ,p_disable_date          => p_disable_date
       ,p_structure_type_id     => p_structure_type_id
       ,p_is_preferred		=> null
       ,x_return_status         => x_return_status
       ,x_errorcode             => x_errorcode
       ,x_msg_count             => x_msg_count
       ,x_msg_data              => x_msg_data
    );
Line: 300

END Update_Row;
Line: 304

PROCEDURE Update_Row ( --- Called by OA Pages
        p_api_version                   IN   NUMBER
       ,p_alt_desig_code_old            IN   VARCHAR2
       ,p_organization_id               IN   NUMBER
       ,p_alt_desig_code_new            IN   VARCHAR2
       ,p_display_name_new              IN   VARCHAR2
       ,p_description                   IN   VARCHAR2
       ,p_disable_date                  IN   DATE
       ,p_structure_type_id             IN   NUMBER
       ,p_is_preferred 			IN   VARCHAR2
       ,x_return_status                 OUT NOCOPY VARCHAR2
       ,x_errorcode                     OUT NOCOPY NUMBER
       ,x_msg_count                     OUT NOCOPY NUMBER
       ,x_msg_data                      OUT NOCOPY VARCHAR2
) IS
    l_api_version            CONSTANT NUMBER := 1.0;
Line: 326

	rec.LAST_UPDATE_DATE          := l_Sysdate;
Line: 327

	rec.LAST_UPDATED_BY           := g_current_user_id;
Line: 330

	rec.LAST_UPDATE_LOGIN         := g_current_login_id;
Line: 352

	rec.PROGRAM_UPDATE_DATE       := NULL;
Line: 359

	Update_Row (p_alt_desig_rec   => rec
       ,x_return_status => x_return_status
       ,x_errorcode => x_errorcode
       ,x_msg_count => x_msg_count
       ,x_msg_data => x_msg_data);
Line: 365

END Update_Row;
Line: 369

PROCEDURE Delete_Row (
        p_api_version                   IN   NUMBER
       ,p_alt_desig_code                IN   VARCHAR2
       ,p_from_struct_alt_page          IN   VARCHAR2 DEFAULT 'N'
       ,x_return_status                 OUT NOCOPY VARCHAR2
       ,x_errorcode                     OUT NOCOPY NUMBER
       ,x_msg_count                     OUT NOCOPY NUMBER
       ,x_msg_data                      OUT NOCOPY VARCHAR2
) IS
    l_api_name               CONSTANT VARCHAR2(30) := 'Delete_Alternate_In_All_Org';
Line: 383

    SAVEPOINT Delete_Alternate_PUB;
Line: 395

        DELETE FROM BOM_ALTERNATE_DESIGNATORS
          WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code;
Line: 398

	DELETE FROM BOM_ALTERNATE_DESIGNATORS_TL
	  WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code;
Line: 402

        UPDATE BOM_ALTERNATE_DESIGNATORS SET structure_type_id =
          (SELECT structure_type_id
           FROM bom_structure_types_b
           WHERE parent_structure_type_id IS NULL)
        WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code ;
Line: 421

      ROLLBACK TO Delete_Alternate_PUB;
Line: 426

END Delete_Row;
Line: 430

PROCEDURE Delete_Row (
        p_api_version                   IN   NUMBER
       ,p_alt_desig_code		IN   VARCHAR2
       ,p_organization_id		IN   NUMBER
       ,x_return_status                 OUT NOCOPY VARCHAR2
       ,x_errorcode                     OUT NOCOPY NUMBER
       ,x_msg_count                     OUT NOCOPY NUMBER
       ,x_msg_data                      OUT NOCOPY VARCHAR2
) IS
    l_api_name               CONSTANT VARCHAR2(30) := 'Delete_Alternate_For_Org';
Line: 444

    SAVEPOINT Delete_Alternate_PUB;
Line: 453

	    DELETE FROM BOM_ALTERNATE_DESIGNATORS
	    WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code
	    AND ORGANIZATION_ID = p_organization_id;
Line: 457

	    DELETE FROM BOM_ALTERNATE_DESIGNATORS_TL
	    WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code
	    and ORGANIZATION_ID = p_organization_id;
Line: 472

      ROLLBACK TO Delete_Alternate_PUB;
Line: 477

END Delete_Row;
Line: 500

   UPDATE BOM_ALTERNATE_DESIGNATORS
     SET structure_type_id = p_structure_type_id
     WHERE alternate_designator_code = p_alternate_designator_code;
Line: 511

   FND_MESSAGE.SET_NAME('BOM', 'BOM_UPDATE_FAILED');
Line: 542

  DELETE FROM BOM_ALTERNATE_DESIGNATORS_TL T
  WHERE not exists
    (SELECT NULL
    FROM BOM_ALTERNATE_DESIGNATORS B
    WHERE B.ALTERNATE_DESIGNATOR_CODE = T.ALTERNATE_DESIGNATOR_CODE
    and B.ORGANIZATION_ID = T.ORGANIZATION_ID
    );
Line: 550

  UPDATE BOM_ALTERNATE_DESIGNATORS_TL T SET (
      DISPLAY_NAME,
      DESCRIPTION
    ) = (SELECT
      B.DISPLAY_NAME,
      B.DESCRIPTION
    FROM BOM_ALTERNATE_DESIGNATORS_TL B
    WHERE B.ALTERNATE_DESIGNATOR_CODE = T.ALTERNATE_DESIGNATOR_CODE
    AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
    AND B.LANGUAGE = T.SOURCE_LANG)
  WHERE (
      T.ALTERNATE_DESIGNATOR_CODE,
      T.ORGANIZATION_ID,
      T.LANGUAGE
  ) IN (SELECT
      SUBT.ALTERNATE_DESIGNATOR_CODE,
      SUBT.ORGANIZATION_ID,
      SUBT.LANGUAGE
    FROM BOM_ALTERNATE_DESIGNATORS_TL SUBB, BOM_ALTERNATE_DESIGNATORS_TL SUBT
    WHERE SUBB.ALTERNATE_DESIGNATOR_CODE = SUBT.ALTERNATE_DESIGNATOR_CODE
    AND SUBB.ORGANIZATION_ID = SUBT.ORGANIZATION_ID
    AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
    AND (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
      OR (SUBB.DISPLAY_NAME IS NULL AND SUBT.DISPLAY_NAME IS NOT NULL)
      OR (SUBB.DISPLAY_NAME IS NOT NULL AND SUBT.DISPLAY_NAME IS NULL)
      OR 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: 580

  INSERT INTO BOM_ALTERNATE_DESIGNATORS_TL (
    ALTERNATE_DESIGNATOR_CODE,
    ORGANIZATION_ID,
    DISPLAY_NAME,
    DESCRIPTION,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) SELECT /*+ ORDERED */
    B.ALTERNATE_DESIGNATOR_CODE,
    B.ORGANIZATION_ID,
    B.DISPLAY_NAME,
    B.DESCRIPTION,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATED_BY,
    B.CREATION_DATE,
    B.CREATED_BY,
    B.LAST_UPDATE_LOGIN,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  FROM BOM_ALTERNATE_DESIGNATORS_TL B, FND_LANGUAGES L
  WHERE L.INSTALLED_FLAG IN ('I', 'B')
  AND B.LANGUAGE = USERENV('LANG')
  AND NOT EXISTS
    (SELECT NULL
    FROM BOM_ALTERNATE_DESIGNATORS_TL T
    WHERE T.ALTERNATE_DESIGNATOR_CODE = B.ALTERNATE_DESIGNATOR_CODE
    AND T.ORGANIZATION_ID = B.ORGANIZATION_ID
    AND T.LANGUAGE = L.LANGUAGE_CODE);
Line: 617

PROCEDURE Insert_Row ( --- Called by form BOMFDBAD.fmb
	P_ALTERNATE_DESIGNATOR_CODE in VARCHAR2,
	P_ORGANIZATION_ID in NUMBER,
	P_STRUCTURE_TYPE_ID in NUMBER,
	P_DISABLE_DATE in DATE,
	P_ATTRIBUTE_CATEGORY in VARCHAR2,
	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_REQUEST_ID in NUMBER,
	P_DISPLAY_NAME 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
) IS
    l_api_version            CONSTANT NUMBER := 1.0;
Line: 657

	rec.LAST_UPDATE_DATE          := P_LAST_UPDATE_DATE;
Line: 658

	rec.LAST_UPDATED_BY           := P_LAST_UPDATED_BY;
Line: 661

	rec.LAST_UPDATE_LOGIN         := P_LAST_UPDATE_LOGIN;
Line: 683

	rec.PROGRAM_UPDATE_DATE       := NULL;
Line: 689

	Insert_Row (p_alt_desig_rec   => rec,
	x_return_status               => x_return_status,
	x_errorcode                   => x_errorcode,
	x_msg_count                   => x_msg_count,
	x_msg_data                    => x_msg_data);
Line: 699

END Insert_Row;
Line: 702

PROCEDURE Insert_Row (
	p_alt_desig_rec                 IN Bom_Alt_Desig_Rec
       ,x_return_status                 OUT NOCOPY VARCHAR2
       ,x_errorcode                     OUT NOCOPY NUMBER
       ,x_msg_count                     OUT NOCOPY NUMBER
       ,x_msg_data                      OUT NOCOPY VARCHAR2
) IS
    l_api_name               CONSTANT VARCHAR2(30) := 'Create_Alternate';
Line: 745

          update bom_alternate_designators
             set is_preferred ='N'
          where
            alternate_designator_code =old_preferred_name;
Line: 753

	    INSERT INTO BOM_ALTERNATE_DESIGNATORS
	    (
		ALTERNATE_DESIGNATOR_CODE
	       ,ORGANIZATION_ID
	       ,DESCRIPTION
	       ,DISABLE_DATE
	       ,STRUCTURE_TYPE_ID
	       ,ATTRIBUTE_CATEGORY
	       ,ATTRIBUTE1
	       ,ATTRIBUTE2
	       ,ATTRIBUTE3
	       ,ATTRIBUTE4
	       ,ATTRIBUTE5
	       ,ATTRIBUTE6
	       ,ATTRIBUTE7
	       ,ATTRIBUTE8
	       ,ATTRIBUTE9
	       ,ATTRIBUTE10
	       ,ATTRIBUTE11
	       ,ATTRIBUTE12
	       ,ATTRIBUTE13
	       ,ATTRIBUTE14
	       ,ATTRIBUTE15
	       ,REQUEST_ID
	       ,CREATION_DATE
	       ,CREATED_BY
	       ,LAST_UPDATE_DATE
	       ,LAST_UPDATED_BY
	       ,LAST_UPDATE_LOGIN
	       ,PROGRAM_APPLICATION_ID
	       ,PROGRAM_ID
	       ,PROGRAM_UPDATE_DATE
               , IS_PREFERRED
	    )
	    VALUES
	    (
		p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE
	       ,p_alt_desig_rec.ORGANIZATION_ID
	       ,p_alt_desig_rec.DESCRIPTION
	       ,p_alt_desig_rec.DISABLE_DATE
	       ,l_structure_type_id
	       ,p_alt_desig_rec.ATTRIBUTE_CATEGORY
	       ,p_alt_desig_rec.ATTRIBUTE1
	       ,p_alt_desig_rec.ATTRIBUTE2
	       ,p_alt_desig_rec.ATTRIBUTE3
	       ,p_alt_desig_rec.ATTRIBUTE4
	       ,p_alt_desig_rec.ATTRIBUTE5
	       ,p_alt_desig_rec.ATTRIBUTE6
	       ,p_alt_desig_rec.ATTRIBUTE7
	       ,p_alt_desig_rec.ATTRIBUTE8
	       ,p_alt_desig_rec.ATTRIBUTE9
	       ,p_alt_desig_rec.ATTRIBUTE10
	       ,p_alt_desig_rec.ATTRIBUTE11
	       ,p_alt_desig_rec.ATTRIBUTE12
	       ,p_alt_desig_rec.ATTRIBUTE13
	       ,p_alt_desig_rec.ATTRIBUTE14
	       ,p_alt_desig_rec.ATTRIBUTE15
	       ,p_alt_desig_rec.REQUEST_ID
	       ,p_alt_desig_rec.CREATION_DATE
	       ,p_alt_desig_rec.CREATED_BY
	       ,p_alt_desig_rec.LAST_UPDATE_DATE
	       ,p_alt_desig_rec.LAST_UPDATED_BY
	       ,p_alt_desig_rec.LAST_UPDATE_LOGIN
	       ,p_alt_desig_rec.PROGRAM_APPLICATION_ID
	       ,p_alt_desig_rec.PROGRAM_ID
	       ,p_alt_desig_rec.PROGRAM_UPDATE_DATE
	       ,p_alt_desig_rec.IS_PREFERRED
	    );
Line: 823

	    insert into BOM_ALTERNATE_DESIGNATORS_TL (
	        ALTERNATE_DESIGNATOR_CODE,
		ORGANIZATION_ID,
		DISPLAY_NAME,
		DESCRIPTION,
		LAST_UPDATE_DATE,
		LAST_UPDATED_BY,
		CREATION_DATE,
		CREATED_BY,
		LAST_UPDATE_LOGIN,
		LANGUAGE,
		SOURCE_LANG
	    ) select
	        p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
		p_alt_desig_rec.ORGANIZATION_ID,
		p_alt_desig_rec.DISPLAY_NAME,
		p_alt_desig_rec.DESCRIPTION,
		p_alt_desig_rec.LAST_UPDATE_DATE,
		p_alt_desig_rec.LAST_UPDATED_BY,
		p_alt_desig_rec.CREATION_DATE,
		p_alt_desig_rec.CREATED_BY,
		p_alt_desig_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 BOM_ALTERNATE_DESIGNATORS_TL T
	     where T.ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE
	     and T.ORGANIZATION_ID = p_alt_desig_rec.ORGANIZATION_ID
	     and T.LANGUAGE = L.LANGUAGE_CODE);
Line: 864

END Insert_row;
Line: 866

procedure UPDATE_ROW ( --- Called from the form BOMFDBAD.fmb
  P_ALTERNATE_DESIGNATOR_CODE in VARCHAR2,
  P_ORGANIZATION_ID in NUMBER,
  P_STRUCTURE_TYPE_ID in NUMBER,
  P_DISABLE_DATE in DATE,
  P_ATTRIBUTE_CATEGORY in VARCHAR2,
  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_REQUEST_ID in NUMBER,
  P_DISPLAY_NAME in VARCHAR2,
  P_DESCRIPTION in VARCHAR2,
  P_LAST_UPDATE_DATE in DATE,
  P_LAST_UPDATED_BY in NUMBER,
  P_LAST_UPDATE_LOGIN in NUMBER
) IS
    l_api_version            CONSTANT NUMBER := 1.0;
Line: 904

	rec.LAST_UPDATE_DATE          := P_LAST_UPDATE_DATE;
Line: 905

	rec.LAST_UPDATED_BY           := P_LAST_UPDATED_BY;
Line: 908

	rec.LAST_UPDATE_LOGIN         := P_LAST_UPDATE_LOGIN;
Line: 930

	rec.PROGRAM_UPDATE_DATE       := NULL;
Line: 936

	Update_Row (p_alt_desig_rec   => rec
       ,x_return_status => x_return_status
       ,x_errorcode => x_errorcode
       ,x_msg_count => x_msg_count
       ,x_msg_data => x_msg_data);
Line: 949

PROCEDURE Update_Row (
        p_alt_desig_rec                 IN Bom_Alt_Desig_Rec
       ,x_return_status                 OUT NOCOPY VARCHAR2
       ,x_errorcode                     OUT NOCOPY NUMBER
       ,x_msg_count                     OUT NOCOPY NUMBER
       ,x_msg_data                      OUT NOCOPY VARCHAR2
) IS

    l_api_name               CONSTANT VARCHAR2(30) := 'Update_Alternate';
Line: 964

    SAVEPOINT Update_Alternate_PUB;
Line: 986

        UPDATE bom_alternate_designators
            SET is_preferred ='N'
        WHERE
            structure_type_id = p_alt_desig_rec.structure_type_id
            AND is_preferred ='Y';
Line: 994

     UPDATE BOM_ALTERNATE_DESIGNATORS
	SET
	   ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
	   DESCRIPTION = p_alt_desig_rec.DESCRIPTION,
	   DISABLE_DATE = p_alt_desig_rec.DISABLE_DATE,
	   STRUCTURE_TYPE_ID = p_alt_desig_rec.STRUCTURE_TYPE_ID,
	   ATTRIBUTE_CATEGORY = p_alt_desig_rec.ATTRIBUTE_CATEGORY,
	   ATTRIBUTE1 = p_alt_desig_rec.ATTRIBUTE1,
	   ATTRIBUTE2 = p_alt_desig_rec.ATTRIBUTE2,
	   ATTRIBUTE3 = p_alt_desig_rec.ATTRIBUTE3,
	   ATTRIBUTE4 = p_alt_desig_rec.ATTRIBUTE4,
	   ATTRIBUTE5 = p_alt_desig_rec.ATTRIBUTE5,
	   ATTRIBUTE6 = p_alt_desig_rec.ATTRIBUTE6,
	   ATTRIBUTE7 = p_alt_desig_rec.ATTRIBUTE7,
	   ATTRIBUTE8 = p_alt_desig_rec.ATTRIBUTE8,
	   ATTRIBUTE9 = p_alt_desig_rec.ATTRIBUTE9,
	   ATTRIBUTE10 = p_alt_desig_rec.ATTRIBUTE10,
	   ATTRIBUTE11 = p_alt_desig_rec.ATTRIBUTE11,
	   ATTRIBUTE12 = p_alt_desig_rec.ATTRIBUTE12,
	   ATTRIBUTE13 = p_alt_desig_rec.ATTRIBUTE13,
	   ATTRIBUTE14 = p_alt_desig_rec.ATTRIBUTE14,
	   ATTRIBUTE15 = p_alt_desig_rec.ATTRIBUTE15,
	   REQUEST_ID = p_alt_desig_rec.REQUEST_ID,
           LAST_UPDATE_DATE = p_alt_desig_rec.LAST_UPDATE_DATE,
           LAST_UPDATED_BY = p_alt_desig_rec.LAST_UPDATED_BY,
           LAST_UPDATE_LOGIN = p_alt_desig_rec.LAST_UPDATE_LOGIN,
           IS_PREFERRED = p_alt_desig_rec.IS_PREFERRED
	WHERE
	   ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.alt_desig_code_old
	   AND ORGANIZATION_ID = p_alt_desig_rec.ORGANIZATION_ID;
Line: 1025

     UPDATE BOM_ALTERNATE_DESIGNATORS_TL
        SET
	   ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
	   DESCRIPTION = p_alt_desig_rec.DESCRIPTION,
	   DISPLAY_NAME = p_alt_desig_rec.DISPLAY_NAME,
	   LAST_UPDATE_DATE = p_alt_desig_rec.LAST_UPDATE_DATE,
	   LAST_UPDATED_BY = p_alt_desig_rec.LAST_UPDATED_BY,
	   LAST_UPDATE_LOGIN = p_alt_desig_rec.LAST_UPDATE_LOGIN,
	   SOURCE_LANG = userenv('LANG')
	WHERE
	   ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.alt_desig_code_old
	   and ORGANIZATION_ID = p_alt_desig_rec.ORGANIZATION_ID
	   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
Line: 1042

     UPDATE BOM_ALTERNATE_DESIGNATORS
	SET
	   ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
	   DESCRIPTION = p_alt_desig_rec.description,
	   DISABLE_DATE = p_alt_desig_rec.disable_date,
	   STRUCTURE_TYPE_ID = p_alt_desig_rec.structure_type_id,
	   ATTRIBUTE_CATEGORY = p_alt_desig_rec.ATTRIBUTE_CATEGORY,
	   ATTRIBUTE1 = p_alt_desig_rec.ATTRIBUTE1,
	   ATTRIBUTE2 = p_alt_desig_rec.ATTRIBUTE2,
	   ATTRIBUTE3 = p_alt_desig_rec.ATTRIBUTE3,
	   ATTRIBUTE4 = p_alt_desig_rec.ATTRIBUTE4,
	   ATTRIBUTE5 = p_alt_desig_rec.ATTRIBUTE5,
	   ATTRIBUTE6 = p_alt_desig_rec.ATTRIBUTE6,
	   ATTRIBUTE7 = p_alt_desig_rec.ATTRIBUTE7,
	   ATTRIBUTE8 = p_alt_desig_rec.ATTRIBUTE8,
	   ATTRIBUTE9 = p_alt_desig_rec.ATTRIBUTE9,
	   ATTRIBUTE10 = p_alt_desig_rec.ATTRIBUTE10,
	   ATTRIBUTE11 = p_alt_desig_rec.ATTRIBUTE11,
	   ATTRIBUTE12 = p_alt_desig_rec.ATTRIBUTE12,
	   ATTRIBUTE13 = p_alt_desig_rec.ATTRIBUTE13,
	   ATTRIBUTE14 = p_alt_desig_rec.ATTRIBUTE14,
	   ATTRIBUTE15 = p_alt_desig_rec.ATTRIBUTE15,
	   REQUEST_ID = p_alt_desig_rec.REQUEST_ID,
           LAST_UPDATE_DATE = p_alt_desig_rec.LAST_UPDATE_DATE,
           LAST_UPDATED_BY = p_alt_desig_rec.LAST_UPDATED_BY,
           LAST_UPDATE_LOGIN = p_alt_desig_rec.LAST_UPDATE_LOGIN
	WHERE
	   ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.alt_desig_code_old -- 4054618
	   AND ORGANIZATION_ID = p_alt_desig_rec.ORGANIZATION_ID;
Line: 1072

     UPDATE BOM_ALTERNATE_DESIGNATORS_TL
        SET
	   ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
	   DESCRIPTION = p_alt_desig_rec.description,
	   DISPLAY_NAME = p_alt_desig_rec.display_name,
	   LAST_UPDATE_DATE = p_alt_desig_rec.LAST_UPDATE_DATE,
	   LAST_UPDATED_BY = p_alt_desig_rec.LAST_UPDATED_BY,
	   LAST_UPDATE_LOGIN = p_alt_desig_rec.LAST_UPDATE_LOGIN,
	   SOURCE_LANG = userenv('LANG')
	WHERE
	   ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.alt_desig_code_old
	   and ORGANIZATION_ID = p_alt_desig_rec.organization_id
	   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
Line: 1092

      ROLLBACK TO Update_Alternate_PUB;
Line: 1097

END Update_Row;
Line: 1099

procedure DELETE_ROW ( ---- Called from form BOMFDBAD.fmb
  P_ALTERNATE_DESIGNATOR_CODE in VARCHAR2,
  P_ORGANIZATION_ID in NUMBER
) IS
    l_api_version            CONSTANT NUMBER := 1.0;
Line: 1109

	Delete_Row (
        p_api_version => l_api_version
       ,p_alt_desig_code => P_ALTERNATE_DESIGNATOR_CODE
       ,p_organization_id => P_ORGANIZATION_ID
       ,x_return_status => x_return_status
       ,x_errorcode => x_errorcode
       ,x_msg_count => x_msg_count
       ,x_msg_data => x_msg_data);
Line: 1122

END DELETE_ROW;
Line: 1149

  cursor c is select
      STRUCTURE_TYPE_ID,
      DISABLE_DATE,
      ATTRIBUTE_CATEGORY,
      ATTRIBUTE1,
      ATTRIBUTE2,
      ATTRIBUTE3,
      ATTRIBUTE4,
      ATTRIBUTE5,
      ATTRIBUTE6,
      ATTRIBUTE7,
      ATTRIBUTE8,
      ATTRIBUTE9,
      ATTRIBUTE10,
      ATTRIBUTE11,
      ATTRIBUTE12,
      ATTRIBUTE13,
      ATTRIBUTE14,
      ATTRIBUTE15,
      REQUEST_ID
    from BOM_ALTERNATE_DESIGNATORS
    where ALTERNATE_DESIGNATOR_CODE = P_ALTERNATE_DESIGNATOR_CODE
    and ORGANIZATION_ID = P_ORGANIZATION_ID
    for update of ALTERNATE_DESIGNATOR_CODE nowait;
Line: 1175

  cursor c1 is select
--      DISPLAY_NAME,
      DESCRIPTION,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from BOM_ALTERNATE_DESIGNATORS_TL
    where ALTERNATE_DESIGNATOR_CODE = P_ALTERNATE_DESIGNATOR_CODE
    and ORGANIZATION_ID = P_ORGANIZATION_ID
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of ALTERNATE_DESIGNATOR_CODE nowait;
Line: 1189

    fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
Line: 1258

 select distinct alternate_designator_code from bom_alternate_designators
    where is_preferred ='Y'
    and structure_type_id = p_structure_type_id
    and alternate_designator_code <> p_alt_code;
Line: 1273

       SELECT display_name,
	          description,
			  NULL disable_date,
			  structure_type_id,
			  is_preferred
	   FROM bom_alternate_designators_vl badv
	   WHERE badv.alternate_designator_code = cp_alt_desig_code
	     AND badv.organization_id = cp_org_id;
Line: 1301

  insert_row (
        p_api_version => 1.0
       ,p_alt_desig_code => p_alt_desig_code
       ,p_organization_id => p_to_org_id
       ,p_display_name => l_display_name
       ,p_description => l_description
       ,p_disable_date => l_disable_date
       ,p_structure_type_id => l_structure_type_id
       ,p_is_preferred => l_is_preferred
       ,x_return_status => x_return_status
       ,x_errorcode => x_errorcode
       ,x_msg_count => x_msg_count
       ,x_msg_data => x_msg_data
   );
Line: 1346

  p_program_update_date IN DATE,
  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_custom_mode IN VARCHAR2,
  p_is_preferred IN VARCHAR2)
IS
  CURSOR GET_ALL_ORGS IS
  SELECT organization_id
    FROM mtl_parameters ;
Line: 1388

        p_program_update_date => p_program_update_date,
        p_creation_date       => p_creation_date,
        p_created_by          => p_created_by,
        p_last_update_date    => p_last_update_date,
        p_last_updated_by     => p_last_updated_by,
        p_last_update_login   => p_last_update_login,
        p_custom_mode         => p_custom_mode,
        p_is_preferred        => p_is_preferred
      );
Line: 1425

        p_program_update_date => p_program_update_date,
        p_creation_date       => p_creation_date,
        p_created_by          => p_created_by,
        p_last_update_date    => p_last_update_date,
        p_last_updated_by     => p_last_updated_by,
        p_last_update_login   => p_last_update_login,
        p_custom_mode         => p_custom_mode,
        p_is_preferred        => p_is_preferred
      );
Line: 1463

  p_program_update_date IN DATE,
  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_custom_mode IN VARCHAR2,
  p_is_preferred IN VARCHAR2)
IS
  db_luby   NUMBER;  -- entity owner in db
Line: 1473

  db_ludate DATE;    -- entity update date in db
Line: 1477

  SELECT
        bad.LAST_UPDATED_BY, bad.LAST_UPDATE_DATE
  INTO
        db_luby, db_ludate
  FROM
        BOM_ALTERNATE_DESIGNATORS bad
  WHERE
      (
        (
              p_alternate_designator_code IS NULL
         AND  bad.ALTERNATE_DESIGNATOR_CODE IS NULL
        )
      OR
        (
          p_alternate_designator_code = bad.ALTERNATE_DESIGNATOR_CODE
        )
      )
  AND  ( bad.ORGANIZATION_ID = p_organization_id
    or (bad.organization_id is null and p_organization_id is null));
Line: 1498

  IF ( FND_LOAD_UTIL.UPLOAD_TEST(p_last_updated_by, p_last_update_date, db_luby, db_ludate, p_custom_mode) )
  THEN
    -- When is_preferred is set for current structure name and there exists another preferred structure name already
    -- then reset that value.
    IF (p_is_preferred = 'Y' )
    THEN
      old_preferred_name := Get_Preferred_Name(p_structure_type_id,  p_alternate_designator_code);
Line: 1507

        UPDATE BOM_ALTERNATE_DESIGNATORS
          SET is_preferred ='N'
        WHERE alternate_designator_code = old_preferred_name;
Line: 1515

    UPDATE BOM_ALTERNATE_DESIGNATORS
    SET
      DESCRIPTION = NVL(p_description, DESCRIPTION),
      DISABLE_DATE = p_disable_date,
      STRUCTURE_TYPE_ID = p_structure_type_id,
      ATTRIBUTE_CATEGORY = p_attribute_category,
      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,
      REQUEST_ID = p_request_id,
      PROGRAM_APPLICATION_ID = p_program_application_id,
      PROGRAM_ID = p_program_id,
      PROGRAM_UPDATE_DATE = p_program_update_date,
      LAST_UPDATE_DATE = p_last_update_date,
      LAST_UPDATED_BY = p_last_updated_by,
      LAST_UPDATE_LOGIN = p_last_update_login,
      IS_PREFERRED = p_is_preferred
    WHERE
        (
          (
                p_alternate_designator_code IS NULL
           AND  ALTERNATE_DESIGNATOR_CODE  IS NULL
          )
         OR
          ( ALTERNATE_DESIGNATOR_CODE = p_alternate_designator_code )
        )
    AND (ORGANIZATION_ID = p_organization_id
    or (organization_id is null and p_organization_id is null));
Line: 1556

    UPDATE BOM_ALTERNATE_DESIGNATORS_TL
    SET
      DESCRIPTION = NVL(p_description, DESCRIPTION),
      DISPLAY_NAME = NVL(p_display_name, DISPLAY_NAME),
      LAST_UPDATE_DATE = p_last_update_date,
      LAST_UPDATED_BY = p_last_updated_by,
      LAST_UPDATE_LOGIN = p_last_update_login,
      SOURCE_LANG = userenv('LANG')
    WHERE
        (
          (
                p_alternate_designator_code IS NULL
           AND  ALTERNATE_DESIGNATOR_CODE  IS NULL
          )
         OR
          ( ALTERNATE_DESIGNATOR_CODE = p_alternate_designator_code )
        )
    AND ORGANIZATION_ID = p_organization_id
    AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
Line: 1582

    INSERT INTO BOM_ALTERNATE_DESIGNATORS
      (
         ALTERNATE_DESIGNATOR_CODE
        ,ORGANIZATION_ID
        ,DESCRIPTION
        ,DISABLE_DATE
        ,STRUCTURE_TYPE_ID
        ,ATTRIBUTE_CATEGORY
        ,ATTRIBUTE1
        ,ATTRIBUTE2
        ,ATTRIBUTE3
        ,ATTRIBUTE4
        ,ATTRIBUTE5
        ,ATTRIBUTE6
        ,ATTRIBUTE7
        ,ATTRIBUTE8
        ,ATTRIBUTE9
        ,ATTRIBUTE10
        ,ATTRIBUTE11
        ,ATTRIBUTE12
        ,ATTRIBUTE13
        ,ATTRIBUTE14
        ,ATTRIBUTE15
        ,REQUEST_ID
        ,CREATION_DATE
        ,CREATED_BY
        ,LAST_UPDATE_DATE
        ,LAST_UPDATED_BY
        ,LAST_UPDATE_LOGIN
        ,PROGRAM_APPLICATION_ID
        ,PROGRAM_ID
        ,PROGRAM_UPDATE_DATE
        ,IS_PREFERRED
      )
    VALUES
      (
         p_alternate_designator_code
        ,p_organization_id
        ,p_description
        ,p_disable_date
        ,p_structure_type_id
        ,p_attribute_category
        ,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_request_id
        ,p_creation_date
        ,p_created_by
        ,p_last_update_date
        ,p_last_updated_by
        ,p_last_update_login
        ,p_program_application_id
        ,p_program_id
        ,p_program_update_date
        ,p_is_preferred
      );
Line: 1651

    INSERT INTO BOM_ALTERNATE_DESIGNATORS_TL
      (
        ALTERNATE_DESIGNATOR_CODE,
        ORGANIZATION_ID,
        DISPLAY_NAME,
        DESCRIPTION,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        LANGUAGE,
        SOURCE_LANG
      )
      SELECT
        p_alternate_designator_code,
        p_organization_id,
        p_display_name,
        p_description,
        p_last_update_date,
        p_last_updated_by,
        p_creation_date,
        p_created_by,
        p_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 BOM_ALTERNATE_DESIGNATORS_TL T
                  WHERE
                    (
                      (
                            p_alternate_designator_code IS NULL
                       AND  T.ALTERNATE_DESIGNATOR_CODE  IS NULL
                      )
                     OR
                      ( T.ALTERNATE_DESIGNATOR_CODE = p_alternate_designator_code )
                    )
                  AND T.ORGANIZATION_ID = p_organization_id
                  AND T.LANGUAGE = L.LANGUAGE_CODE
                );
Line: 1705

        UPDATE BOM_ALTERNATE_DESIGNATORS
          SET is_preferred ='N'
        WHERE alternate_designator_code = old_preferred_name;
Line: 1718

  p_last_update_date IN DATE,
  p_last_updated_by IN NUMBER,
  p_last_update_login IN NUMBER,
  p_custom_mode IN VARCHAR2)
IS
  db_luby   NUMBER;  -- entity owner in db
Line: 1724

  db_ludate DATE;    -- entity update date in db
Line: 1727

  SELECT
        badtl.LAST_UPDATED_BY, badtl.LAST_UPDATE_DATE
  INTO
        db_luby, db_ludate
  FROM
        BOM_ALTERNATE_DESIGNATORS_TL badtl
  WHERE
      badtl.LANGUAGE = userenv('LANG')
  AND
      (
        (
              p_alternate_designator_code IS NULL
         AND  badtl.ALTERNATE_DESIGNATOR_CODE IS NULL
        )
      OR
        (
          p_alternate_designator_code = badtl.ALTERNATE_DESIGNATOR_CODE
        )
      )
  AND   badtl.ORGANIZATION_ID = p_organization_id;
Line: 1749

  IF ( FND_LOAD_UTIL.UPLOAD_TEST(p_last_updated_by, p_last_update_date, db_luby, db_ludate, p_custom_mode) )
  THEN
     -- Update translations for this language
     UPDATE BOM_ALTERNATE_DESIGNATORS_TL
     SET
        DISPLAY_NAME = NVL(p_display_name, DISPLAY_NAME),
        DESCRIPTION = NVL(p_description, DESCRIPTION),
        LAST_UPDATE_DATE = p_last_update_date,
        LAST_UPDATED_BY = p_last_updated_by,
        LAST_UPDATE_LOGIN = p_last_update_login,
        SOURCE_LANG = userenv('LANG')
     WHERE
        userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
      AND
          (
            (
                  p_alternate_designator_code IS NULL
             AND  ALTERNATE_DESIGNATOR_CODE IS NULL
            )
          OR
            (
              p_alternate_designator_code = ALTERNATE_DESIGNATOR_CODE
            )
          )
      AND   ORGANIZATION_ID = p_organization_id;