APPS.IBW_REFERRAL_PVT SQL Statements

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

Line: 40

PROCEDURE insert_row (
	referral_category_id  OUT NOCOPY NUMBER,
	x_referral_category_name IN VARCHAR2,
	x_referral_pattern IN VARCHAR2,
	error_messages OUT NOCOPY VARCHAR2
)

IS

	x_ref_cat_id	NUMBER;

Line: 55

	last_updated_by	NUMBER;

Line: 56

	last_update_date	DATE;

Line: 57

	last_update_login	NUMBER;

Line: 64

	CURSOR c IS SELECT ibw_referral_categories_b_s1.nextval FROM dual;

Line: 66

	CURSOR c1 IS SELECT ibw_url_patterns_b_s1.nextval FROM dual;

Line: 68

	CURSOR c2 IS SELECT referral_category_id FROM ibw_referral_categories_b
	    WHERE referral_category_id = x_ref_cat_id;

Line: 87

	last_updated_by := created_by;

Line: 89

	last_update_date := SYSDATE;

Line: 91

	FND_PROFILE.GET('LOGIN_ID', last_update_login);

Line: 101

	INSERT INTO ibw_referral_categories_b (referral_category_id, status, user_defined_flag, created_by, creation_date,
							last_updated_by, last_update_date, last_update_login, object_version_number,
							program_id, program_login_id, program_application_id, request_id)
			VALUES (x_ref_cat_id, x_status, x_user_def_flag, created_by, creation_date,
						last_updated_by, last_update_date, last_update_login, obj_ver_number,
						program_id, program_login_id, program_app_id, request_id);

Line: 108

	INSERT INTO ibw_referral_categories_tl(referral_category_id, language, source_lang, referral_category_name, description, created_by, creation_date,
					last_updated_by, last_update_date, last_update_login, object_version_number,
					program_id, program_login_id, program_application_id, request_id)
			SELECT M.referral_category_id, l.language_code, b.language_code,  x_referral_category_name, NULL, M.created_by, M.creation_date,
				M.last_updated_by, M.last_update_date, M.last_update_login, M.object_version_number,
				M.program_id, M.program_login_id, M.program_application_id, M.request_id
			FROM ibw_referral_categories_b M, fnd_languages l, fnd_languages b
			WHERE l.installed_flag IN ('I','B') AND b.installed_flag='B' AND M.referral_category_id=x_ref_cat_id;

Line: 118

	INSERT INTO ibw_url_patterns_b (url_pattern_id, url_pattern, type_id, type, created_by, creation_date,
							last_updated_by, last_update_date, last_update_login, object_version_number,
							program_id, program_login_id, program_application_id, request_id)
			VALUES (x_ref_pat_id, x_referral_pattern, x_ref_cat_id, 'R', created_by, creation_date,
						last_updated_by, last_update_date, last_update_login, obj_ver_number,
						program_id, program_login_id, program_app_id, request_id);

Line: 125

	INSERT INTO ibw_url_patterns_tl(url_pattern_id, language, source_lang, description, created_by, creation_date,
					last_updated_by, last_update_date, last_update_login, object_version_number,
					program_id, program_login_id, program_application_id, request_id)
			SELECT M.url_pattern_id, l.language_code, b.language_code, NULL, M.created_by, M.creation_date,
				M.last_updated_by, M.last_update_date, M.last_update_login, M.object_version_number,
				M.program_id, M.program_login_id, M.program_application_id, M.request_id
			FROM ibw_url_patterns_b M, fnd_languages l, fnd_languages b
			WHERE l.installed_flag IN ('I','B') AND b.installed_flag='B' AND M.url_pattern_id=x_ref_pat_id;

Line: 146

END insert_row;

Line: 152

  delete from IBW_REFERRAL_CATEGORIES_TL T
  where not exists
    (select NULL
    from IBW_REFERRAL_CATEGORIES_B B
    where B.REFERRAL_CATEGORY_ID = T.REFERRAL_CATEGORY_ID
    );

Line: 159

  update IBW_REFERRAL_CATEGORIES_TL T set (
      REFERRAL_CATEGORY_NAME,
      DESCRIPTION
    ) = (select
      B.REFERRAL_CATEGORY_NAME,
      B.DESCRIPTION
    from IBW_REFERRAL_CATEGORIES_TL B
    where B.REFERRAL_CATEGORY_ID = T.REFERRAL_CATEGORY_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.REFERRAL_CATEGORY_ID,
      T.LANGUAGE
  ) in (select
      SUBT.REFERRAL_CATEGORY_ID,
      SUBT.LANGUAGE
    from IBW_REFERRAL_CATEGORIES_TL SUBB, IBW_REFERRAL_CATEGORIES_TL SUBT
    where SUBB.REFERRAL_CATEGORY_ID = SUBT.REFERRAL_CATEGORY_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.REFERRAL_CATEGORY_NAME <> SUBT.REFERRAL_CATEGORY_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: 183

  insert into IBW_REFERRAL_CATEGORIES_TL (
    REFERRAL_CATEGORY_ID,
    REFERRAL_CATEGORY_NAME,
    DESCRIPTION,
    OBJECT_VERSION_NUMBER,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    PROGRAM_ID,
    PROGRAM_LOGIN_ID,
    PROGRAM_APPLICATION_ID,
    REQUEST_ID,
    LANGUAGE,
    SOURCE_LANG
  ) select /*+ ORDERED */
    B.REFERRAL_CATEGORY_ID,
    B.REFERRAL_CATEGORY_NAME,
    B.DESCRIPTION,
    B.OBJECT_VERSION_NUMBER,
    B.CREATED_BY,
    B.CREATION_DATE,
    B.LAST_UPDATED_BY,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATE_LOGIN,
    B.PROGRAM_ID,
    B.PROGRAM_LOGIN_ID,
    B.PROGRAM_APPLICATION_ID,
    B.REQUEST_ID,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from IBW_REFERRAL_CATEGORIES_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from IBW_REFERRAL_CATEGORIES_TL T
    where T.REFERRAL_CATEGORY_ID = B.REFERRAL_CATEGORY_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);