APPS.JTF_IH_OUTCOMES_SEED_PKG SQL Statements

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

Line: 3

procedure INSERT_ROW (
  X_ROWID in out nocopy VARCHAR2,
  X_OUTCOME_ID in NUMBER,
  X_GENERATE_PUBLIC_CALLBACK in VARCHAR2,
  X_GENERATE_PRIVATE_CALLBACK in VARCHAR2,
  X_SCORE in NUMBER,
  X_POSITIVE_OUTCOME_FLAG in VARCHAR2,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_RESULT_REQUIRED in VARCHAR2,
  X_VERSATILITY_CODE in NUMBER,
  X_OUTCOME_CODE in VARCHAR2,
  X_MEDIA_TYPE in VARCHAR2,
  X_SHORT_DESCRIPTION in VARCHAR2,
  X_LONG_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,
  X_ACTIVE in VARCHAR2
) is
  cursor C is select ROWID from JTF_IH_OUTCOMES_B
    where OUTCOME_ID = X_OUTCOME_ID
    ;

Line: 39

  insert into JTF_IH_OUTCOMES_B (
    GENERATE_PUBLIC_CALLBACK,
    GENERATE_PRIVATE_CALLBACK,
    SCORE,
    POSITIVE_OUTCOME_FLAG,
    OUTCOME_ID,
    OBJECT_VERSION_NUMBER,
    RESULT_REQUIRED,
    VERSATILITY_CODE,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    ACTIVE
  ) values (
    X_GENERATE_PUBLIC_CALLBACK,
    X_GENERATE_PRIVATE_CALLBACK,
    X_SCORE,
    X_POSITIVE_OUTCOME_FLAG,
    X_OUTCOME_ID,
    X_OBJECT_VERSION_NUMBER,
    X_RESULT_REQUIRED,
    X_VERSATILITY_CODE,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN,
    --NVL(X_ACTIVE,'Y')
    --Added for performance issue due to literals -  27-Jul-2004
    NVL(X_ACTIVE,L_ACTIVE_FLAG)
  );

Line: 73

  insert into JTF_IH_OUTCOMES_TL (
    OUTCOME_ID,
    OBJECT_VERSION_NUMBER,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    LONG_DESCRIPTION,
    SHORT_DESCRIPTION,
    OUTCOME_CODE,
    MEDIA_TYPE,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_OUTCOME_ID,
    X_OBJECT_VERSION_NUMBER,
    X_CREATED_BY,
    X_CREATION_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATE_LOGIN,
    X_LONG_DESCRIPTION,
    X_SHORT_DESCRIPTION,
    X_OUTCOME_CODE,
    X_MEDIA_TYPE,
    L.LANGUAGE_CODE,
    --Added for performance issue due to literals -  27-Jul-2004
    --userenv('LANG')
    L_LANG
  from FND_LANGUAGES L
  --Added for performance issue due to literals -  27-Jul-2004
  --where L.INSTALLED_FLAG in ('I', 'B')
  where L.INSTALLED_FLAG in ( L_INSTALLED_FLAG1,  L_INSTALLED_FLAG2)
  and not exists
    (select NULL
    from JTF_IH_OUTCOMES_TL T
    where T.OUTCOME_ID = X_OUTCOME_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 121

end INSERT_ROW;

Line: 137

  cursor c is select
      GENERATE_PUBLIC_CALLBACK,
      GENERATE_PRIVATE_CALLBACK,
      SCORE,
      POSITIVE_OUTCOME_FLAG,
      OBJECT_VERSION_NUMBER,
      RESULT_REQUIRED,
      VERSATILITY_CODE
    from JTF_IH_OUTCOMES_B
    where OUTCOME_ID = X_OUTCOME_ID
    for update of OUTCOME_ID nowait;

Line: 155

  cursor c1 is select
      OUTCOME_CODE,
      MEDIA_TYPE,
      SHORT_DESCRIPTION,
      LONG_DESCRIPTION,
      --Added for performance issue due to literals -  28-Jul-2004
      --decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
      decode(LANGUAGE, L_LANG, L_YES, L_NO) BASELANG
    from JTF_IH_OUTCOMES_TL
    where OUTCOME_ID = X_OUTCOME_ID
    --and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    --Added for performance issue due to literals -  28-Jul-2004
    and L_LANG in (LANGUAGE, SOURCE_LANG)
    for update of OUTCOME_ID nowait;

Line: 179

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

Line: 222

procedure UPDATE_ROW (
  X_OUTCOME_ID in NUMBER,
  X_GENERATE_PUBLIC_CALLBACK in VARCHAR2,
  X_GENERATE_PRIVATE_CALLBACK in VARCHAR2,
  X_SCORE in NUMBER,
  X_POSITIVE_OUTCOME_FLAG in VARCHAR2,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_RESULT_REQUIRED in VARCHAR2,
  X_VERSATILITY_CODE in NUMBER,
  X_OUTCOME_CODE in VARCHAR2,
  X_MEDIA_TYPE in VARCHAR2,
  X_SHORT_DESCRIPTION in VARCHAR2,
  X_LONG_DESCRIPTION in VARCHAR2,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  X_ACTIVE in VARCHAR2
) is
--Added for performance issue due to literals -  27-Jul-2004
L_ACTIVE_FLAG VARCHAR2(1);

Line: 249

  update JTF_IH_OUTCOMES_B set
    GENERATE_PUBLIC_CALLBACK = X_GENERATE_PUBLIC_CALLBACK,
    GENERATE_PRIVATE_CALLBACK = X_GENERATE_PRIVATE_CALLBACK,
    SCORE = X_SCORE,
    POSITIVE_OUTCOME_FLAG = X_POSITIVE_OUTCOME_FLAG,
    OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
    RESULT_REQUIRED = X_RESULT_REQUIRED,
    VERSATILITY_CODE = X_VERSATILITY_CODE,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    --Added for performance issue due to literals -  27-Jul-2004
    --ACTIVE = NVL(X_ACTIVE,'Y')
    ACTIVE = NVL(X_ACTIVE,L_ACTIVE_FLAG)
  where OUTCOME_ID = X_OUTCOME_ID;

Line: 269

  update JTF_IH_OUTCOMES_TL set
    OUTCOME_CODE = X_OUTCOME_CODE,
    MEDIA_TYPE = X_MEDIA_TYPE,
    SHORT_DESCRIPTION = X_SHORT_DESCRIPTION,
    LONG_DESCRIPTION = X_LONG_DESCRIPTION,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    --Added for performance issue due to literals -  27-Jul-2004
    --SOURCE_LANG = userenv('LANG')
    SOURCE_LANG = L_LANG
  where OUTCOME_ID = X_OUTCOME_ID
  --Added for performance issue due to literals -  27-Jul-2004
  --and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 288

end UPDATE_ROW;

Line: 290

procedure DELETE_ROW (
  X_OUTCOME_ID in NUMBER
) is
begin
  delete from JTF_IH_OUTCOMES_TL
  where OUTCOME_ID = X_OUTCOME_ID;

Line: 301

  delete from JTF_IH_OUTCOMES_B
  where OUTCOME_ID = X_OUTCOME_ID;

Line: 307

end DELETE_ROW;

Line: 321

  delete from JTF_IH_OUTCOMES_TL T
  where not exists
    (select NULL
    from JTF_IH_OUTCOMES_B B
    where B.OUTCOME_ID = T.OUTCOME_ID
    );

Line: 328

  update JTF_IH_OUTCOMES_TL T set (
      OUTCOME_CODE,
      MEDIA_TYPE,
      SHORT_DESCRIPTION,
      LONG_DESCRIPTION
    ) = (select
      B.OUTCOME_CODE,
      B.MEDIA_TYPE,
      B.SHORT_DESCRIPTION,
      B.LONG_DESCRIPTION
    from JTF_IH_OUTCOMES_TL B
    where B.OUTCOME_ID = T.OUTCOME_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.OUTCOME_ID,
      T.LANGUAGE
  ) in (select
      SUBT.OUTCOME_ID,
      SUBT.LANGUAGE
    from JTF_IH_OUTCOMES_TL SUBB, JTF_IH_OUTCOMES_TL SUBT
    where SUBB.OUTCOME_ID = SUBT.OUTCOME_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.OUTCOME_CODE <> SUBT.OUTCOME_CODE
      or SUBB.MEDIA_TYPE <> SUBT.MEDIA_TYPE
      or (SUBB.MEDIA_TYPE is null and SUBT.MEDIA_TYPE is not null)
      or (SUBB.MEDIA_TYPE is not null and SUBT.MEDIA_TYPE is null)
      or SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
      or SUBB.LONG_DESCRIPTION <> SUBT.LONG_DESCRIPTION
      or (SUBB.LONG_DESCRIPTION is null and SUBT.LONG_DESCRIPTION is not null)
      or (SUBB.LONG_DESCRIPTION is not null and SUBT.LONG_DESCRIPTION is null)
  ));

Line: 360

  insert into JTF_IH_OUTCOMES_TL (
    OUTCOME_ID,
    OBJECT_VERSION_NUMBER,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    LONG_DESCRIPTION,
    SHORT_DESCRIPTION,
    OUTCOME_CODE,
    MEDIA_TYPE,
    LANGUAGE,
    SOURCE_LANG
  ) select
    B.OUTCOME_ID,
    B.OBJECT_VERSION_NUMBER,
    B.CREATED_BY,
    B.CREATION_DATE,
    B.LAST_UPDATED_BY,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATE_LOGIN,
    B.LONG_DESCRIPTION,
    B.SHORT_DESCRIPTION,
    B.OUTCOME_CODE,
    B.MEDIA_TYPE,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from JTF_IH_OUTCOMES_TL B, FND_LANGUAGES L
  where  L.INSTALLED_FLAG in (L_INSTALLED_FLAG1, L_INSTALLED_FLAG2)
  and B.LANGUAGE = L_LANG
  and not exists
    (select NULL
    from JTF_IH_OUTCOMES_TL T
    where T.OUTCOME_ID = B.OUTCOME_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 437

	l_last_update_date	DATE;

Line: 438

	l_last_updated_by	NUMBER;

Line: 439

	l_last_update_login	NUMBER;

Line: 461

	l_last_update_date := sysdate;

Line: 462

	l_last_updated_by := user_id;

Line: 463

	l_last_update_login := 0;

Line: 466

	UPDATE_ROW(
  			X_OUTCOME_ID => l_outcome_id,
  			X_GENERATE_PUBLIC_CALLBACK => l_generate_public_callback,
  			X_GENERATE_PRIVATE_CALLBACK => l_generate_private_callback,
  			X_SCORE => l_score,
  			X_POSITIVE_OUTCOME_FLAG => l_positive_outcome_flag,
  			X_OBJECT_VERSION_NUMBER => l_object_version_number,
  			X_RESULT_REQUIRED => l_result_required,
  			X_VERSATILITY_CODE => l_versatility_code,
  			X_OUTCOME_CODE => l_outcome_code,
  			X_MEDIA_TYPE => l_media_type,
  			X_SHORT_DESCRIPTION => l_short_description,
  			X_LONG_DESCRIPTION => l_long_description,
  			X_LAST_UPDATE_DATE => l_last_update_date,
  			X_LAST_UPDATED_BY => l_last_updated_by,
  			X_LAST_UPDATE_LOGIN => l_last_update_login,
			X_ACTIVE => l_active);

Line: 487

			INSERT_ROW(
			row_id,
  			X_OUTCOME_ID => l_outcome_id,
  			X_GENERATE_PUBLIC_CALLBACK => l_generate_public_callback,
  			X_GENERATE_PRIVATE_CALLBACK => l_generate_private_callback,
  			X_SCORE => l_score,
  			X_POSITIVE_OUTCOME_FLAG => l_positive_outcome_flag,
  			X_OBJECT_VERSION_NUMBER => l_object_version_number,
  			X_RESULT_REQUIRED => l_result_required,
  			X_VERSATILITY_CODE => l_versatility_code,
  			X_OUTCOME_CODE => l_outcome_code,
  			X_MEDIA_TYPE => l_media_type,
  			X_SHORT_DESCRIPTION => l_short_description,
  			X_LONG_DESCRIPTION => l_long_description,
			X_CREATION_DATE => l_creation_date,
			X_CREATED_BY => l_created_by,
  			X_LAST_UPDATE_DATE => l_last_update_date,
  			X_LAST_UPDATED_BY => l_last_updated_by,
  			X_LAST_UPDATE_LOGIN => l_last_update_login,
			X_ACTIVE => l_active);

Line: 520

  L_UPDATEDBY1 NUMBER;

Line: 521

  L_UPDATEDBY0 NUMBER;

Line: 526

	L_UPDATEDBY1 := 1;

Line: 527

	L_UPDATEDBY0 := 0;

Line: 529

	UPDATE jtf_ih_outcomes_tl SET
		outcome_id = X_OUTCOME_ID,
		outcome_code = X_OUTCOME_CODE,
		media_type = X_MEDIA_TYPE,
		short_description = X_SHORT_DESCRIPTION,
		long_description = X_LONG_DESCRIPTION,
		last_update_date = sysdate,
		--Added for performance issue due to literals -  28-Jul-2004
		--last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
		--last_updated_by = decode(X_OWNER, L_SEED, L_UPDATEDBY1, L_UPDATEDBY0),
                last_updated_by = fnd_load_util.owner_id(x_owner),
		last_update_login = 0,
		--Added for performance issue due to literals -  28-Jul-2004
		--source_lang = userenv('LANG')
		source_lang = L_LANG
	--WHERE userenv('LANG') in (LANGUAGE, SOURCE_LANG) AND
	WHERE L_LANG in (LANGUAGE, SOURCE_LANG) AND
		outcome_id = X_OUTCOME_ID;