eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HZ_ORIG_SYSTEMS_PKG SQL Statements

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

Line: 4


procedure INSERT_ROW (
  X_ORIG_SYSTEM_ID in out nocopy NUMBER,
  X_ORIG_SYSTEM in VARCHAR2,
  X_ORIG_SYSTEM_TYPE in VARCHAR2,
  X_SST_FLAG in VARCHAR2,
  X_STATUS in VARCHAR2,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_CREATED_BY_MODULE in VARCHAR2,
  X_ATTRIBUTE_CATEGORY in VARCHAR2,
  X_ATTRIBUTE1 in VARCHAR2,
  X_ATTRIBUTE2 in VARCHAR2,
  X_ATTRIBUTE3 in VARCHAR2,
  X_ATTRIBUTE4 in VARCHAR2,
  X_ATTRIBUTE5 in VARCHAR2,
  X_ATTRIBUTE6 in VARCHAR2,
  X_ATTRIBUTE7 in VARCHAR2,
  X_ATTRIBUTE8 in VARCHAR2,
  X_ATTRIBUTE9 in VARCHAR2,
  X_ATTRIBUTE10 in VARCHAR2,
  X_ATTRIBUTE11 in VARCHAR2,
  X_ATTRIBUTE12 in VARCHAR2,
  X_ATTRIBUTE13 in VARCHAR2,
  X_ATTRIBUTE14 in VARCHAR2,
  X_ATTRIBUTE15 in VARCHAR2,
  X_ATTRIBUTE16 in VARCHAR2,
  X_ATTRIBUTE17 in VARCHAR2,
  X_ATTRIBUTE18 in VARCHAR2,
  X_ATTRIBUTE19 in VARCHAR2,
  X_ATTRIBUTE20 in VARCHAR2,
  X_ORIG_SYSTEM_NAME in VARCHAR2,
  X_DESCRIPTION in VARCHAR2--,
/*  X_CREATION_DATE in DATE,
  X_CREATED_BY in NUMBER,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER*/
) is

 CURSOR C2 IS SELECT  HZ_ORIG_SYSTEMS_S.nextval FROM sys.dual;

Line: 54


	    insert into HZ_ORIG_SYSTEMS_B (
	    ORIG_SYSTEM_ID,
	    ORIG_SYSTEM,
	    ORIG_SYSTEM_TYPE,
	    SST_FLAG,
	    STATUS,
	    START_DATE_ACTIVE,
	    END_DATE_ACTIVE,
	    OBJECT_VERSION_NUMBER,
	    CREATED_BY_MODULE,
	    ATTRIBUTE_CATEGORY,
	    ATTRIBUTE1,
	    ATTRIBUTE2,
	    ATTRIBUTE3,
	    ATTRIBUTE4,
	    ATTRIBUTE5,
	    ATTRIBUTE6,
	    ATTRIBUTE7,
	    ATTRIBUTE8,
	    ATTRIBUTE9,
	    ATTRIBUTE10,
	    ATTRIBUTE11,
	    ATTRIBUTE12,
	    ATTRIBUTE13,
	    ATTRIBUTE14,
	    ATTRIBUTE15,
	    ATTRIBUTE16,
	    ATTRIBUTE17,
	    ATTRIBUTE18,
	    ATTRIBUTE19,
	    ATTRIBUTE20,
	    CREATION_DATE,
	    CREATED_BY,
	    LAST_UPDATE_DATE,
	    LAST_UPDATED_BY,
	    LAST_UPDATE_LOGIN
	  ) values (
	    X_ORIG_SYSTEM_ID,
	    X_ORIG_SYSTEM,
	    X_ORIG_SYSTEM_TYPE,
	    X_SST_FLAG,
	    X_STATUS,
	    sysdate,
	    decode(X_STATUS,'A',NULL,'I',sysdate),
	    X_OBJECT_VERSION_NUMBER,
	    X_CREATED_BY_MODULE,
	    X_ATTRIBUTE_CATEGORY,
	    X_ATTRIBUTE1,
	    X_ATTRIBUTE2,
	    X_ATTRIBUTE3,
	    X_ATTRIBUTE4,
	    X_ATTRIBUTE5,
	    X_ATTRIBUTE6,
	    X_ATTRIBUTE7,
	    X_ATTRIBUTE8,
	    X_ATTRIBUTE9,
	    X_ATTRIBUTE10,
	    X_ATTRIBUTE11,
	    X_ATTRIBUTE12,
	    X_ATTRIBUTE13,
	    X_ATTRIBUTE14,
	    X_ATTRIBUTE15,
	    X_ATTRIBUTE16,
	    X_ATTRIBUTE17,
	    X_ATTRIBUTE18,
	    X_ATTRIBUTE19,
	    X_ATTRIBUTE20,
	    HZ_UTILITY_V2PUB.CREATION_DATE,
	    HZ_UTILITY_V2PUB.CREATED_BY,
	    HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
	    HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
	    HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
	    );

Line: 139


			     SELECT HZ_ORIG_SYSTEMS_S.NEXTVAL
			     INTO X_ORIG_SYSTEM_ID FROM dual;

Line: 142


				SELECT 'Y' INTO l_dummy
				FROM HZ_ORIG_SYSTEMS_B
				WHERE ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID;

Line: 157


  insert into HZ_ORIG_SYSTEMS_TL (
    ORIG_SYSTEM_ID,
    ORIG_SYSTEM_NAME,
    DESCRIPTION,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_ORIG_SYSTEM_ID,
    X_ORIG_SYSTEM_NAME,
    X_DESCRIPTION,
    HZ_UTILITY_V2PUB.CREATED_BY,
    HZ_UTILITY_V2PUB.CREATION_DATE,
    HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
    HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
    HZ_UTILITY_V2PUB.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 HZ_ORIG_SYSTEMS_TL T
    where T.ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 187


end INSERT_ROW;

Line: 193


  cursor c is select
      OBJECT_VERSION_NUMBER
    from HZ_ORIG_SYSTEMS_B
    where ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID
    for update of ORIG_SYSTEM_ID nowait;

Line: 200


  cursor c1 is select
      ORIG_SYSTEM_NAME,
      DESCRIPTION,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from HZ_ORIG_SYSTEMS_TL
    where ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of ORIG_SYSTEM_ID nowait;

Line: 213


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

Line: 230


procedure UPDATE_ROW (
  X_ORIG_SYSTEM_ID in NUMBER,
  X_ORIG_SYSTEM in VARCHAR2,
  X_ORIG_SYSTEM_TYPE in VARCHAR2,
  X_SST_FLAG in VARCHAR2,
  X_STATUS in VARCHAR2,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_CREATED_BY_MODULE in VARCHAR2,
  X_ATTRIBUTE_CATEGORY in VARCHAR2,
  X_ATTRIBUTE1 in VARCHAR2,
  X_ATTRIBUTE2 in VARCHAR2,
  X_ATTRIBUTE3 in VARCHAR2,
  X_ATTRIBUTE4 in VARCHAR2,
  X_ATTRIBUTE5 in VARCHAR2,
  X_ATTRIBUTE6 in VARCHAR2,
  X_ATTRIBUTE7 in VARCHAR2,
  X_ATTRIBUTE8 in VARCHAR2,
  X_ATTRIBUTE9 in VARCHAR2,
  X_ATTRIBUTE10 in VARCHAR2,
  X_ATTRIBUTE11 in VARCHAR2,
  X_ATTRIBUTE12 in VARCHAR2,
  X_ATTRIBUTE13 in VARCHAR2,
  X_ATTRIBUTE14 in VARCHAR2,
  X_ATTRIBUTE15 in VARCHAR2,
  X_ATTRIBUTE16 in VARCHAR2,
  X_ATTRIBUTE17 in VARCHAR2,
  X_ATTRIBUTE18 in VARCHAR2,
  X_ATTRIBUTE19 in VARCHAR2,
  X_ATTRIBUTE20 in VARCHAR2,
  X_ORIG_SYSTEM_NAME in VARCHAR2,
  X_DESCRIPTION in VARCHAR2--,
/*  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER*/
) is
--p_object_version_number number;

Line: 271


  update HZ_ORIG_SYSTEMS_B set
    ORIG_SYSTEM = DECODE(X_ORIG_SYSTEM, FND_API.G_MISS_CHAR, NULL,NULL,ORIG_SYSTEM, X_ORIG_SYSTEM),
    ORIG_SYSTEM_TYPE = DECODE(X_ORIG_SYSTEM_TYPE, FND_API.G_MISS_CHAR, NULL,NULL,ORIG_SYSTEM_TYPE, X_ORIG_SYSTEM_TYPE),
    SST_FLAG = DECODE(X_SST_FLAG, FND_API.G_MISS_CHAR, NULL,NULL,SST_FLAG, X_SST_FLAG),
    STATUS = DECODE(X_STATUS, FND_API.G_MISS_CHAR, NULL,NULL,STATUS, X_STATUS),
    END_DATE_ACTIVE = DECODE(X_STATUS,'I',DECODE(END_DATE_ACTIVE,NULL,SYSDATE,END_DATE_ACTIVE),'A',NULL,NULL,END_DATE_ACTIVE),
    OBJECT_VERSION_NUMBER = DECODE(X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, X_OBJECT_VERSION_NUMBER),
    CREATED_BY_MODULE = DECODE(X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL,NULL,CREATED_BY_MODULE, X_CREATED_BY_MODULE),
    ATTRIBUTE_CATEGORY = DECODE(X_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE_CATEGORY, X_ATTRIBUTE_CATEGORY),
    ATTRIBUTE1 = DECODE(X_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE1, X_ATTRIBUTE1),
    ATTRIBUTE2 = DECODE(X_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE2, X_ATTRIBUTE2),
    ATTRIBUTE3 = DECODE(X_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE3, X_ATTRIBUTE3),
    ATTRIBUTE4 = DECODE(X_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE4, X_ATTRIBUTE4),
    ATTRIBUTE5 = DECODE(X_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE5, X_ATTRIBUTE5),
    ATTRIBUTE6 = DECODE(X_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE6, X_ATTRIBUTE6),
    ATTRIBUTE7 = DECODE(X_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE7, X_ATTRIBUTE7),
    ATTRIBUTE8 = DECODE(X_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE8, X_ATTRIBUTE8),
    ATTRIBUTE9 = DECODE(X_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE9, X_ATTRIBUTE9),
    ATTRIBUTE10 = DECODE(X_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE10, X_ATTRIBUTE10),
    ATTRIBUTE11 = DECODE(X_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE11, X_ATTRIBUTE11),
    ATTRIBUTE12 = DECODE(X_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE12, X_ATTRIBUTE12),
    ATTRIBUTE13 = DECODE(X_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE13, X_ATTRIBUTE13),
    ATTRIBUTE14 = DECODE(X_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE14, X_ATTRIBUTE14),
    ATTRIBUTE15 = DECODE(X_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE15, X_ATTRIBUTE15),
    ATTRIBUTE16 = DECODE(X_ATTRIBUTE16, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE16, X_ATTRIBUTE16),
    ATTRIBUTE17 = DECODE(X_ATTRIBUTE17, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE17, X_ATTRIBUTE17),
    ATTRIBUTE18 = DECODE(X_ATTRIBUTE18, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE18, X_ATTRIBUTE18),
    ATTRIBUTE19 = DECODE(X_ATTRIBUTE19, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE19, X_ATTRIBUTE19),
    ATTRIBUTE20 = DECODE(X_ATTRIBUTE20, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE20, X_ATTRIBUTE20),
    LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
    LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
  where ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID;

Line: 309


  update HZ_ORIG_SYSTEMS_TL set
    ORIG_SYSTEM_NAME = DECODE(X_ORIG_SYSTEM_NAME, FND_API.G_MISS_CHAR, NULL,NULL,ORIG_SYSTEM_NAME, X_ORIG_SYSTEM_NAME),
    DESCRIPTION = DECODE(X_DESCRIPTION, FND_API.G_MISS_CHAR, NULL,NULL,DESCRIPTION, X_DESCRIPTION),
    LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
    LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
    SOURCE_LANG = userenv('LANG')
  where ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 322


end UPDATE_ROW;

Line: 324


procedure DELETE_ROW (
  X_ORIG_SYSTEM_ID in NUMBER
) is
begin
  delete from HZ_ORIG_SYSTEMS_TL
  where ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID;

Line: 335


  delete from HZ_ORIG_SYSTEMS_B
  where ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID;

Line: 341


end DELETE_ROW;

Line: 346


  delete from HZ_ORIG_SYSTEMS_TL T
  where not exists
    (select NULL
    from HZ_ORIG_SYSTEMS_B B
    where B.ORIG_SYSTEM_ID = T.ORIG_SYSTEM_ID
    );

Line: 353


  update HZ_ORIG_SYSTEMS_TL T set (
      ORIG_SYSTEM_NAME,
      DESCRIPTION
    ) = (select
      B.ORIG_SYSTEM_NAME,
      B.DESCRIPTION
    from HZ_ORIG_SYSTEMS_TL B
    where B.ORIG_SYSTEM_ID = T.ORIG_SYSTEM_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.ORIG_SYSTEM_ID,
      T.LANGUAGE
  ) in (select
      SUBT.ORIG_SYSTEM_ID,
      SUBT.LANGUAGE
    from HZ_ORIG_SYSTEMS_TL SUBB, HZ_ORIG_SYSTEMS_TL SUBT
    where SUBB.ORIG_SYSTEM_ID = SUBT.ORIG_SYSTEM_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.ORIG_SYSTEM_NAME <> SUBT.ORIG_SYSTEM_NAME
      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: 377


  insert into HZ_ORIG_SYSTEMS_TL (
    ORIG_SYSTEM_ID,
    ORIG_SYSTEM_NAME,
    DESCRIPTION,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select /*+ ORDERED */
    B.ORIG_SYSTEM_ID,
    B.ORIG_SYSTEM_NAME,
    B.DESCRIPTION,
    B.CREATED_BY,
    B.CREATION_DATE,
    B.LAST_UPDATED_BY,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATE_LOGIN,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from HZ_ORIG_SYSTEMS_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from HZ_ORIG_SYSTEMS_TL T
    where T.ORIG_SYSTEM_ID = B.ORIG_SYSTEM_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);