APPS.ITA_SETUP_REC_VALUES_PKG SQL Statements

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

Line: 18

	-- insert into  sam_test
	-- (id,
	-- message,
	-- creation_date
	-- )
	-- values
	-- (
	-- sam_test_s1.nextval,
	-- X_DEBUG_MSG,
	-- sysdate
	-- );

Line: 63

			select distinct
			SET_OF_BOOKS_ID into X_CONTEXT_ID
			from GL_SETS_OF_BOOKS
			where NAME = nvl(P_CONTEXT_NAME,999);

Line: 74

			select distinct
			org.ORGANIZATION_ID into X_CONTEXT_ID
			from
			HR_ALL_ORGANIZATION_UNITS org
			where
			org.NAME = nvl(P_CONTEXT_NAME,999);

Line: 112

	SELECT parameter_code
          INTO X_PARAMETER_CODE
          FROM ita_setup_parameters_vl
          WHERE parameter_name = p_parameter_name and
	  setup_group_code = p_setup_group_code;

Line: 129

procedure INSERT_ROW (
  X_REC_VALUE_ID in NUMBER,
  X_PARAMETER_CODE in VARCHAR2,
  X_CONTEXT_ORG_ID in NUMBER,
  X_CONTEXT_ORG_NAME in VARCHAR2,
  X_RECOMMENDED_VALUE in VARCHAR2,
  X_DEFAULT_FLAG 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_SECURITY_GROUP_ID in NUMBER,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_PK1_VALUE in VARCHAR2,
  X_PK2_VALUE in VARCHAR2,
  X_REC_INTERFACE_ID in NUMBER
)

is
begin

  custom_debug('In to the INSERT_ROW X_PARAMETER_CODE ' || X_PARAMETER_CODE);

Line: 153

  insert into ITA_SETUP_REC_VALUES_B (
    REC_VALUE_ID,
    PARAMETER_CODE,
    CONTEXT_ORG_ID,
    CONTEXT_ORG_NAME,
    DEFAULT_FLAG,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    SECURITY_GROUP_ID,
    OBJECT_VERSION_NUMBER,
    PK1_VALUE,
    PK2_VALUE,
    REC_INTERFACE_ID
  ) values (
    X_REC_VALUE_ID,
    X_PARAMETER_CODE,
    X_CONTEXT_ORG_ID,
    X_CONTEXT_ORG_NAME,
    X_DEFAULT_FLAG,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_SECURITY_GROUP_ID,
    X_OBJECT_VERSION_NUMBER,
    X_PK1_VALUE,
    X_PK2_VALUE,
    X_REC_INTERFACE_ID
  );

Line: 187

  custom_debug('In to the INSERT_ROW callin insert in TL X_REC_VALUE_ID :: ' || X_REC_VALUE_ID);

Line: 189

  insert into ITA_SETUP_REC_VALUES_TL (
    REC_VALUE_ID,
    PARAMETER_CODE,
    CONTEXT_ORG_NAME,
    RECOMMENDED_VALUE,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    SECURITY_GROUP_ID,
    OBJECT_VERSION_NUMBER,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_REC_VALUE_ID,
    X_PARAMETER_CODE,
    X_CONTEXT_ORG_NAME,
    X_RECOMMENDED_VALUE,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_SECURITY_GROUP_ID,
    X_OBJECT_VERSION_NUMBER,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists (
    select null
    from ITA_SETUP_REC_VALUES_TL tl
    where
      (tl.REC_VALUE_ID = X_REC_VALUE_ID or
	  (PARAMETER_CODE = X_PARAMETER_CODE and CONTEXT_ORG_NAME = X_CONTEXT_ORG_NAME)) and
      tl.LANGUAGE = L.LANGUAGE_CODE);

Line: 228

end INSERT_ROW;

Line: 231

procedure UPDATE_ROW (
  X_REC_VALUE_ID in NUMBER,
  X_PARAMETER_CODE in VARCHAR2,
  X_CONTEXT_ORG_ID in NUMBER,
  X_CONTEXT_ORG_NAME in VARCHAR2,
  X_RECOMMENDED_VALUE in VARCHAR2,
  X_DEFAULT_FLAG in VARCHAR2,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  X_SECURITY_GROUP_ID in NUMBER,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_PK1_VALUE in VARCHAR2,
  X_PK2_VALUE in VARCHAR2,
  X_REC_INTERFACE_ID NUMBER
) is
begin
  update ITA_SETUP_REC_VALUES_B set
    REC_VALUE_ID = X_REC_VALUE_ID,
    PARAMETER_CODE = X_PARAMETER_CODE,
    CONTEXT_ORG_ID = X_CONTEXT_ORG_ID,
    CONTEXT_ORG_NAME = X_CONTEXT_ORG_NAME,
    DEFAULT_FLAG = X_DEFAULT_FLAG,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
    OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
    PK1_VALUE = X_PK1_VALUE,
    PK2_VALUE = X_PK2_VALUE,
    REC_INTERFACE_ID = X_REC_INTERFACE_ID
  where REC_VALUE_ID = X_REC_VALUE_ID or
    (PARAMETER_CODE = X_PARAMETER_CODE and CONTEXT_ORG_NAME = X_CONTEXT_ORG_NAME);

Line: 269

  update ITA_SETUP_REC_VALUES_TL set
    REC_VALUE_ID = X_REC_VALUE_ID,
    PARAMETER_CODE = X_PARAMETER_CODE,
    CONTEXT_ORG_NAME = X_CONTEXT_ORG_NAME,
    RECOMMENDED_VALUE = X_RECOMMENDED_VALUE,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
    OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
    SOURCE_LANG = userenv('LANG')
  where
    (REC_VALUE_ID = X_REC_VALUE_ID or
      (PARAMETER_CODE = X_PARAMETER_CODE and CONTEXT_ORG_NAME = X_CONTEXT_ORG_NAME)) and
    userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 288

end UPDATE_ROW;

Line: 298

  X_LAST_UPDATE_DATE in VARCHAR2,
  X_PK1_VALUE in VARCHAR2,
  X_PK2_VALUE in VARCHAR2,
  X_REC_INTERFACE_ID NUMBER,
  X_OWNER in VARCHAR2,
  X_CUSTOM_MODE in VARCHAR2
) is

f_luby	number;	-- entity owner in file

Line: 307

f_ludate	date;		-- entity update date in file

Line: 309

db_ludate	date;		-- entity update date in db

Line: 312

	-- Translate owner to file_last_updated_by
	f_luby := fnd_load_util.owner_id(X_OWNER);

Line: 315

	-- Translate char last_update_date to date
	f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);

Line: 318

	select LAST_UPDATED_BY, LAST_UPDATE_DATE into db_luby, db_ludate
	from ITA_SETUP_REC_VALUES_B
	where REC_VALUE_ID = X_REC_VALUE_ID or
        (PARAMETER_CODE = X_PARAMETER_CODE and CONTEXT_ORG_NAME = X_CONTEXT_ORG_NAME);

Line: 324

	then ITA_SETUP_REC_VALUES_PKG.UPDATE_ROW (
		X_REC_VALUE_ID			=> X_REC_VALUE_ID,
		X_PARAMETER_CODE			=> X_PARAMETER_CODE,
		X_CONTEXT_ORG_ID			=> X_CONTEXT_ORG_ID,
		X_CONTEXT_ORG_NAME		=> X_CONTEXT_ORG_NAME,
		X_RECOMMENDED_VALUE		=> X_RECOMMENDED_VALUE,
		X_DEFAULT_FLAG			=> X_DEFAULT_FLAG,
		X_LAST_UPDATE_DATE		=> f_ludate,
		X_LAST_UPDATED_BY			=> f_luby,
		X_LAST_UPDATE_LOGIN		=> 0,
		X_SECURITY_GROUP_ID		=> null,
		X_OBJECT_VERSION_NUMBER		=> 1,
		X_PK1_VALUE				=> X_PK1_VALUE,
		X_PK2_VALUE				=> X_PK2_VALUE,
		X_REC_INTERFACE_ID		=> X_REC_INTERFACE_ID);

Line: 341

	then ITA_SETUP_REC_VALUES_PKG.INSERT_ROW (
		X_REC_VALUE_ID			=> X_REC_VALUE_ID,
		X_PARAMETER_CODE			=> X_PARAMETER_CODE,
		X_CONTEXT_ORG_ID			=> X_CONTEXT_ORG_ID,
		X_CONTEXT_ORG_NAME		=> X_CONTEXT_ORG_NAME,
		X_RECOMMENDED_VALUE		=> X_RECOMMENDED_VALUE,
		X_DEFAULT_FLAG			=> X_DEFAULT_FLAG,
		X_CREATION_DATE			=> f_ludate,
		X_CREATED_BY			=> f_luby,
		X_LAST_UPDATE_DATE		=> f_ludate,
		X_LAST_UPDATED_BY			=> f_luby,
		X_LAST_UPDATE_LOGIN		=> 0,
		X_SECURITY_GROUP_ID		=> null,
		X_OBJECT_VERSION_NUMBER		=> 1,
		X_PK1_VALUE				=> X_PK1_VALUE,
		X_PK2_VALUE				=> X_PK2_VALUE,
		X_REC_INTERFACE_ID		=> X_REC_INTERFACE_ID);

Line: 370

  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  X_SECURITY_GROUP_ID in NUMBER,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_PK1_VALUE in VARCHAR2,
  X_PK2_VALUE in VARCHAR2,
  X_REC_INTERFACE_ID NUMBER
) is

db_rec_value_id	number;

Line: 385

	select REC_VALUE_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE into db_rec_value_id, db_luby, db_ludate
	from ITA_SETUP_REC_VALUES_B
	where REC_VALUE_ID = X_REC_VALUE_ID or
        (PARAMETER_CODE = X_PARAMETER_CODE and CONTEXT_ORG_NAME = X_CONTEXT_ORG_NAME);

Line: 391

	then ITA_SETUP_REC_VALUES_PKG.UPDATE_ROW (
		X_REC_VALUE_ID			=> db_rec_value_id,
		X_PARAMETER_CODE			=> X_PARAMETER_CODE,
		X_CONTEXT_ORG_ID			=> X_CONTEXT_ORG_ID,
		X_CONTEXT_ORG_NAME		=> X_CONTEXT_ORG_NAME,
		X_RECOMMENDED_VALUE		=> X_RECOMMENDED_VALUE,
		X_DEFAULT_FLAG			=> X_DEFAULT_FLAG,
		X_LAST_UPDATE_DATE		=> X_LAST_UPDATE_DATE,
		X_LAST_UPDATED_BY			=> X_LAST_UPDATED_BY,
		X_LAST_UPDATE_LOGIN		=> X_LAST_UPDATE_LOGIN,
		X_SECURITY_GROUP_ID		=> X_SECURITY_GROUP_ID,
		X_OBJECT_VERSION_NUMBER		=> X_OBJECT_VERSION_NUMBER,
		X_PK1_VALUE				=> X_PK1_VALUE,
		X_PK2_VALUE				=> X_PK2_VALUE,
		X_REC_INTERFACE_ID		=> X_REC_INTERFACE_ID);

Line: 408

	then ITA_SETUP_REC_VALUES_PKG.INSERT_ROW (
		X_REC_VALUE_ID			=> X_REC_VALUE_ID,
		X_PARAMETER_CODE			=> X_PARAMETER_CODE,
		X_CONTEXT_ORG_ID			=> X_CONTEXT_ORG_ID,
		X_CONTEXT_ORG_NAME		=> X_CONTEXT_ORG_NAME,
		X_RECOMMENDED_VALUE		=> X_RECOMMENDED_VALUE,
		X_DEFAULT_FLAG			=> X_DEFAULT_FLAG,
		X_CREATION_DATE			=> X_CREATION_DATE,
		X_CREATED_BY			=> X_CREATED_BY,
		X_LAST_UPDATE_DATE		=> X_LAST_UPDATE_DATE,
		X_LAST_UPDATED_BY			=> X_LAST_UPDATED_BY,
		X_LAST_UPDATE_LOGIN		=> X_LAST_UPDATE_LOGIN,
		X_SECURITY_GROUP_ID		=> X_SECURITY_GROUP_ID,
		X_OBJECT_VERSION_NUMBER		=> X_OBJECT_VERSION_NUMBER,
		X_PK1_VALUE				=> X_PK1_VALUE,
		X_PK2_VALUE				=> X_PK2_VALUE,
		X_REC_INTERFACE_ID		=> X_REC_INTERFACE_ID);

Line: 431

  delete from ITA_SETUP_REC_VALUES_TL tl
  where not exists (
    select null
    from ITA_SETUP_REC_VALUES_B b
    where
	b.PARAMETER_CODE = tl.PARAMETER_CODE and
	b.CONTEXT_ORG_NAME = tl.CONTEXT_ORG_NAME
    );

Line: 440

  update ITA_SETUP_REC_VALUES_TL tl set (
      RECOMMENDED_VALUE
    ) = (select
      b.RECOMMENDED_VALUE
    from ITA_SETUP_REC_VALUES_TL b
    where
	b.PARAMETER_CODE = tl.PARAMETER_CODE and
	b.CONTEXT_ORG_NAME = tl.CONTEXT_ORG_NAME and
      b.LANGUAGE = tl.SOURCE_LANG)
  where (
      tl.PARAMETER_CODE,
	tl.CONTEXT_ORG_NAME,
      tl.LANGUAGE
  ) in (select
      subtl.PARAMETER_CODE,
	subtl.CONTEXT_ORG_NAME,
      subtl.LANGUAGE
    from ITA_SETUP_REC_VALUES_TL subb, ITA_SETUP_REC_VALUES_TL subtl
    where
      subb.PARAMETER_CODE = subtl.PARAMETER_CODE and
      subb.CONTEXT_ORG_NAME = subtl.CONTEXT_ORG_NAME and
      subb.LANGUAGE = subtl.SOURCE_LANG and
    	(subb.RECOMMENDED_VALUE <> subtl.RECOMMENDED_VALUE or
        (subb.RECOMMENDED_VALUE is null and subtl.RECOMMENDED_VALUE is not null) or
        (subb.RECOMMENDED_VALUE is not null and subtl.RECOMMENDED_VALUE is null)));

Line: 466

  insert into ITA_SETUP_REC_VALUES_TL (
    PARAMETER_CODE,
    CONTEXT_ORG_ID,
    CONTEXT_ORG_NAME,
    RECOMMENDED_VALUE,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    SECURITY_GROUP_ID,
    OBJECT_VERSION_NUMBER,
    LANGUAGE,
    SOURCE_LANG,
    REC_VALUE_ID,
    REQUEST_ID,
    PROGRAM_APPLICATION_ID,
    PROGRAM_ID,
    PROGRAM_UPDATE_DATE
  ) select
    b.PARAMETER_CODE,
    b.CONTEXT_ORG_ID,
    b.CONTEXT_ORG_NAME,
    b.RECOMMENDED_VALUE,
    b.CREATED_BY,
    b.CREATION_DATE,
    b.LAST_UPDATED_BY,
    b.LAST_UPDATE_DATE,
    b.LAST_UPDATE_LOGIN,
    b.SECURITY_GROUP_ID,
    b.OBJECT_VERSION_NUMBER,
    L.LANGUAGE_CODE,
    b.SOURCE_LANG,
    b.REC_VALUE_ID,
    b.REQUEST_ID,
    b.PROGRAM_APPLICATION_ID,
    b.PROGRAM_ID,
    b.PROGRAM_UPDATE_DATE
  from ITA_SETUP_REC_VALUES_TL b, FND_LANGUAGES L
  where
    L.INSTALLED_FLAG in ('I', 'B') and
    b.LANGUAGE = userenv('LANG') and
    not exists (
     select null
     from ITA_SETUP_REC_VALUES_TL tl
     where
       tl.PARAMETER_CODE = b.PARAMETER_CODE and
       tl.CONTEXT_ORG_NAME = b.CONTEXT_ORG_NAME and
       tl.LANGUAGE = L.LANGUAGE_CODE);

Line: 538

SELECT
  distinct DEFAULT_FLAG,
  PARAMETER_CODE,
  SETUP_GROUP_CODE,
  PK1_VALUE,
  PK2_VALUE,
  REC_VALUE,
  CREATED_BY,
  LAST_UPDATED_BY,
  LAST_UPDATE_LOGIN,
  SECURITY_GROUP_ID  ,
  OBJECT_VERSION_NUMBER,
  REC_INTERFACE_ID
FROM
  ITA_SETUP_REC_INTF
WHERE
  BATCH_ID = X_BATCH_ID and
  CREATED_BY = X_CREATED_BY and
  IMPORTED_FLAG <> 'Y';

Line: 564

select ITA_SETUP_REC_VALUES_S1.nextval into m_rec_value_id from dual;

Line: 565

select sysdate into m_current_date from dual;

Line: 576

  custom_debug('In to the INSERT_ROW m_parameter_code ' || m_parameter_code);

Line: 593

		X_LAST_UPDATE_DATE => m_current_date,
		X_LAST_UPDATED_BY => interface_row.LAST_UPDATED_BY,
		X_LAST_UPDATE_LOGIN => interface_row.LAST_UPDATE_LOGIN,
		X_SECURITY_GROUP_ID => interface_row.SECURITY_GROUP_ID,
		X_OBJECT_VERSION_NUMBER => interface_row.OBJECT_VERSION_NUMBER,
		X_PK1_VALUE => interface_row.PK1_VALUE,
		X_PK2_VALUE => interface_row.PK2_VALUE,
		X_REC_INTERFACE_ID => interface_row.REC_INTERFACE_ID
		);

Line: 623

		X_LAST_UPDATE_DATE => m_current_date,
		X_LAST_UPDATED_BY => interface_row.LAST_UPDATED_BY,
		X_LAST_UPDATE_LOGIN => interface_row.LAST_UPDATE_LOGIN,
		X_SECURITY_GROUP_ID => interface_row.SECURITY_GROUP_ID,
		X_OBJECT_VERSION_NUMBER => interface_row.OBJECT_VERSION_NUMBER,
		X_PK1_VALUE => m_org_id,
		X_PK2_VALUE => interface_row.PK2_VALUE,
		X_REC_INTERFACE_ID => interface_row.REC_INTERFACE_ID
		);

Line: 647

		X_LAST_UPDATE_DATE => m_current_date,
		X_LAST_UPDATED_BY => interface_row.LAST_UPDATED_BY,
		X_LAST_UPDATE_LOGIN => interface_row.LAST_UPDATE_LOGIN,
		X_SECURITY_GROUP_ID => interface_row.SECURITY_GROUP_ID,
		X_OBJECT_VERSION_NUMBER => interface_row.OBJECT_VERSION_NUMBER,
		X_PK1_VALUE => -1,
		X_PK2_VALUE => interface_row.PK2_VALUE,
		X_REC_INTERFACE_ID => interface_row.REC_INTERFACE_ID
		);

Line: 661

	--update ITA_SETUP_REC_INTF
	--set IMPORTED_FLAG = 'Y'
	--where REC_INTERFACE_ID = interface_row.REC_INTERFACE_ID;

Line: 666

	delete from ITA_SETUP_REC_INTF
	where REC_INTERFACE_ID = interface_row.REC_INTERFACE_ID;

Line: 679

	   update ITA_SETUP_REC_INTF
	   set STATUS_TXT = m_error_msg
	   where REC_INTERFACE_ID = m_rec_interface_id;

Line: 706

  select recommended_value
  into l_rec_value_code
  from ita_setup_rec_values_vl
  where parameter_code = p_parameter_code
  and context_org_id = to_number(p_context_org_id);

Line: 716

    select max(recommended_value)
    into l_rec_value_code
    from ita_setup_rec_values_vl
    where parameter_code = p_parameter_code
    and default_flag = 'Y';

Line: 747

      SELECT parameter_code
      INTO l_paramcodeorg
      FROM ita_parameter_hierarchy
      WHERE override_parameter_code = p_parameter_code
       AND override_level = p_overridelevel;

Line: 774

      SELECT override_parameter_code
      INTO l_paramcodesupps
      FROM ita_parameter_hierarchy
      WHERE parameter_code =
        (SELECT parameter_code
         FROM ita_parameter_hierarchy
         WHERE override_parameter_code = p_parameter_code
         AND override_level = 2)
      AND override_level = 1;

Line: 847

  l_select_clause         ITA_SETUP_PARAMETERS_B.SELECT_CLAUSE%TYPE;

Line: 865

  SELECT select_clause, from_clause, where_clause
  INTO l_select_clause, l_from_clause, l_where_clause
  FROM ita_setup_parameters_b
  WHERE parameter_code = p_parameter_code;

Line: 874

  IF l_select_clause IS NOT NULL THEN
       l_select_clause := RTRIM(l_select_clause);

Line: 877

       if l_select_clause is null then
         --DBMS_OUTPUT.PUT_LINE('select is null: ' || l_rec_value_code);

Line: 890

       l_curr_sql := l_select_clause || ' ' || l_from_clause || ' ' || l_where_clause;