APPS.EDW_SOURCE_INSTANCES_PKG SQL Statements

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

Line: 27

        DBMS_SQL.PARSE(cid, 'SELECT count(*) FROM edw_local_instance@'||P_DBLINK, dbms_sql.native);

Line: 49

procedure INSERT_ROW (
  X_ROWID in out NOCOPY VARCHAR2,
  X_INSTANCE_CODE in VARCHAR2,
  X_ENABLED_FLAG in VARCHAR2,
  X_WAREHOUSE_TO_INSTANCE_LINK in VARCHAR2,
  X_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
dummy number:= 0;

Line: 65

  cursor C is select ROWID from EDW_SOURCE_INSTANCES
    where INSTANCE_CODE = X_INSTANCE_CODE
    ;

Line: 72

  insert into EDW_SOURCE_INSTANCES (
    INSTANCE_CODE,
    ENABLED_FLAG,
    WAREHOUSE_TO_INSTANCE_LINK,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    X_INSTANCE_CODE,
    X_ENABLED_FLAG,
    X_WAREHOUSE_TO_INSTANCE_LINK,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );

Line: 92

  insert into EDW_SOURCE_INSTANCES_TL (
    INSTANCE_CODE,
    NAME,
    DESCRIPTION,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    LAST_UPDATED_BY,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_INSTANCE_CODE,
    X_NAME,
    X_DESCRIPTION,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATE_LOGIN,
    X_LAST_UPDATED_BY,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from EDW_SOURCE_INSTANCES_TL T
    where T.INSTANCE_CODE = X_INSTANCE_CODE
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 133

end INSERT_ROW;

Line: 142

  cursor c is select
      ENABLED_FLAG,
      WAREHOUSE_TO_INSTANCE_LINK,
      INSTANCE_CODE
    from EDW_SOURCE_INSTANCES
    where INSTANCE_CODE = X_INSTANCE_CODE
    for update of INSTANCE_CODE nowait;

Line: 151

  cursor c1 is select
      NAME,
      DESCRIPTION,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from EDW_SOURCE_INSTANCES_TL
    where INSTANCE_CODE = X_INSTANCE_CODE
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of INSTANCE_CODE nowait;

Line: 164

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

Line: 201

procedure UPDATE_ROW (
  X_INSTANCE_CODE in VARCHAR2,
  X_ENABLED_FLAG in VARCHAR2,
  X_WAREHOUSE_TO_INSTANCE_LINK in VARCHAR2,
  X_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
begin
  update EDW_SOURCE_INSTANCES set
    ENABLED_FLAG = X_ENABLED_FLAG,
    WAREHOUSE_TO_INSTANCE_LINK = X_WAREHOUSE_TO_INSTANCE_LINK,
    INSTANCE_CODE = X_INSTANCE_CODE,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
  where INSTANCE_CODE = X_INSTANCE_CODE;

Line: 225

  update EDW_SOURCE_INSTANCES_TL set
    NAME = X_NAME,
    DESCRIPTION = X_DESCRIPTION,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    SOURCE_LANG = userenv('LANG')
  where INSTANCE_CODE = X_INSTANCE_CODE
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 238

end UPDATE_ROW;

Line: 240

procedure DELETE_ROW (
  X_INSTANCE_CODE in VARCHAR2
) is
begin
  delete from EDW_SOURCE_INSTANCES_TL
  where INSTANCE_CODE = X_INSTANCE_CODE;

Line: 251

  delete from EDW_SOURCE_INSTANCES
  where INSTANCE_CODE = X_INSTANCE_CODE;

Line: 257

end DELETE_ROW;

Line: 264

   select WAREHOUSE_TO_INSTANCE_LINK
   from   EDW_SOURCE_INSTANCES;

Line: 268

  delete from EDW_SOURCE_INSTANCES_TL T
  where not exists
    (select NULL
    from EDW_SOURCE_INSTANCES B
    where B.INSTANCE_CODE = T.INSTANCE_CODE
    );

Line: 275

  update EDW_SOURCE_INSTANCES_TL T set (
      NAME,
      DESCRIPTION
    ) = (select
      B.NAME,
      B.DESCRIPTION
    from EDW_SOURCE_INSTANCES_TL B
    where B.INSTANCE_CODE = T.INSTANCE_CODE
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.INSTANCE_CODE,
      T.LANGUAGE
  ) in (select
      SUBT.INSTANCE_CODE,
      SUBT.LANGUAGE
    from EDW_SOURCE_INSTANCES_TL SUBB, EDW_SOURCE_INSTANCES_TL SUBT
    where SUBB.INSTANCE_CODE = SUBT.INSTANCE_CODE
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.NAME <> SUBT.NAME
      or (SUBB.NAME is null and SUBT.NAME is not null)
      or (SUBB.NAME is not null and SUBT.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: 301

  insert into EDW_SOURCE_INSTANCES_TL (
    INSTANCE_CODE,
    NAME,
    DESCRIPTION,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    LAST_UPDATED_BY,
    LANGUAGE,
    SOURCE_LANG
  ) select
    B.INSTANCE_CODE,
    B.NAME,
    B.DESCRIPTION,
    B.CREATION_DATE,
    B.CREATED_BY,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATE_LOGIN,
    B.LAST_UPDATED_BY,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from EDW_SOURCE_INSTANCES_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from EDW_SOURCE_INSTANCES_TL T
    where T.INSTANCE_CODE = B.INSTANCE_CODE
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 341

    v_sql_stmt := 'delete from EDW_LOCAL_INSTANCE_TL@' || v_db_link;

Line: 346

      v_sql_stmt := 'insert into EDW_LOCAL_INSTANCE_TL@' || v_db_link ||
                ' select * from EDW_SOURCE_INSTANCES_TL '  ||
                ' where INSTANCE_CODE = ' ||
                  '(select distinct INSTANCE_CODE ' ||
                  ' from  EDW_SOURCE_INSTANCES '    ||
                  ' where WAREHOUSE_TO_INSTANCE_LINK = '''||v_db_link||''')';

Line: 364

      delete from EDW_LOCAL_INSTANCE_TL;

Line: 366

      insert into EDW_LOCAL_INSTANCE_TL(
	    INSTANCE_CODE,
	    NAME,
	    DESCRIPTION,
	    CREATION_DATE,
	    CREATED_BY,
	    LAST_UPDATE_DATE,
	    LAST_UPDATE_LOGIN,
	    LAST_UPDATED_BY,
	    LANGUAGE,
	    SOURCE_LANG)
        select
	    INSTANCE_CODE,
	    NAME,
	    DESCRIPTION,
	    CREATION_DATE,
	    CREATED_BY,
	    LAST_UPDATE_DATE,
	    LAST_UPDATE_LOGIN,
	    LAST_UPDATED_BY,
	    LANGUAGE,
	    SOURCE_LANG
	 from EDW_SOURCE_INSTANCES_TL
         where INSTANCE_CODE =
               (select distinct INSTANCE_CODE from EDW_LOCAL_INSTANCE);

Line: 406

  SELECT COUNT(1) INTO dummy
  FROM EDW_SOURCE_INSTANCES_VL
  WHERE INSTANCE_CODE = X_INSTANCE_CODE
  AND ((X_ROWID IS NULL) OR (ROW_ID <> X_ROWID));

Line: 425

  SELECT 1 INTO dummy FROM DUAL WHERE NOT EXISTS
  (SELECT 1 FROM edw_push_detail_log
   WHERE INSTANCE_CODE = X_INSTANCE_CODE);

Line: 443

 SELECT count(*) INTO dummy
  FROM EDW_SOURCE_INSTANCES_VL
  WHERE NAME = X_INSTANCE_NAME
  AND ((X_ROWID IS NULL) OR (ROW_ID <> X_ROWID));

Line: 464

  SELECT COUNT(1) INTO dummy
  FROM EDW_SOURCE_INSTANCES_VL
  WHERE WAREHOUSE_TO_INSTANCE_LINK = X_DBLINK
  AND ((X_ROWID IS NULL) OR (ROW_ID <> X_ROWID));

Line: 478

procedure INSERT_ROW_REMOTE (
  X_INSTANCE_CODE in VARCHAR2,
  X_ENABLED_FLAG in VARCHAR2,
  X_WAREHOUSE_TO_INSTANCE_LINK in VARCHAR2,
  X_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
v_cursor_id NUMBER;

Line: 501

        DBMS_SQL.PARSE(cid, 'SELECT sysdate FROM dual@'||X_WAREHOUSE_TO_INSTANCE_LINK, dbms_sql.native);

Line: 509

	delete_row_remote(x_instance_code, X_WAREHOUSE_TO_INSTANCE_LINK);

Line: 511

  v_sql_stmt := 'INSERT INTO edw_local_instance@' ||
                x_warehouse_to_instance_link ||
    '(instance_code, enabled_flag, creation_date, created_by,
    last_update_date, last_updated_by, last_update_login,
    warehouse_to_instance_link)
    values (:x1, :x2, :x3, :x4, :x5, :x6, :x7, :x8)';

Line: 524

  dbms_sql.bind_variable(v_cursor_id, ':x6', x_last_updated_by);

Line: 525

  dbms_sql.bind_variable(v_cursor_id, ':x7', x_last_update_login);

Line: 530

  SELECT userenv('LANG') INTO v_source_lang
  FROM dual;

Line: 533

  v_sql_stmt := 'insert into EDW_LOCAL_INSTANCE_TL@' ||
    x_warehouse_to_instance_link ||
    '(INSTANCE_CODE, NAME, DESCRIPTION, CREATION_DATE, CREATED_BY,
    LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LANGUAGE,
    SOURCE_LANG) select :x1, :x2, :x3, :x4, :x5, :x6, :x7, :x8,
    L.LANGUAGE_CODE, :x9 from FND_LANGUAGES@' ||
    x_warehouse_to_instance_link ||
  ' L where L.INSTALLED_FLAG in (''I'', ''B'')
  and not exists (select NULL from EDW_LOCAL_INSTANCE_TL@' ||
    x_warehouse_to_instance_link ||
    ' T where T.INSTANCE_CODE = :x1 and T.LANGUAGE = L.LANGUAGE_CODE)';

Line: 553

  dbms_sql.bind_variable(v_cursor_id, ':x7', x_last_updated_by);

Line: 554

  dbms_sql.bind_variable(v_cursor_id, ':x8', x_last_update_login);

Line: 569

END INSERT_ROW_REMOTE;

Line: 571

procedure DELETE_ROW_REMOTE (
  X_INSTANCE_CODE in VARCHAR2,
  X_WAREHOUSE_TO_INSTANCE_LINK in VARCHAR2
) IS
v_cursor_id NUMBER;

Line: 582

  v_sql_stmt := 'delete from EDW_LOCAL_INSTANCE@' ||
  x_warehouse_to_instance_link;

Line: 590

  v_sql_stmt := 'delete from EDW_LOCAL_INSTANCE_TL@' ||
  x_warehouse_to_instance_link ;

Line: 597

END DELETE_ROW_REMOTE;