APPS.HZ_TRANS_FUNCTIONS_PKG SQL Statements

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

Line: 6

procedure INSERT_ROW (
  X_FUNCTION_ID IN OUT NOCOPY NUMBER,
  X_STAGED_ATTRIBUTE_TABLE in VARCHAR2,
  X_STAGED_ATTRIBUTE_COLUMN in VARCHAR2,
  X_STAGED_FLAG in VARCHAR2,
  X_ATTRIBUTE_ID in NUMBER,
  X_PROCEDURE_NAME in VARCHAR2,
  X_ACTIVE_FLAG in VARCHAR2,
  X_PRIMARY_FLAG in VARCHAR2,
  X_INDEX_REQUIRED_FLAG in VARCHAR2,
  X_TRANSFORMATION_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,
  X_OBJECT_VERSION_NUMBER in NUMBER
) is
 CURSOR C2 IS SELECT  HZ_TRANS_FUNCTIONS_s.nextval FROM sys.dual;

Line: 38

     insert into HZ_TRANS_FUNCTIONS_B (
        STAGED_ATTRIBUTE_TABLE,
        STAGED_ATTRIBUTE_COLUMN,
        STAGED_FLAG,
        FUNCTION_ID,
        ATTRIBUTE_ID,
        PROCEDURE_NAME,
        ACTIVE_FLAG,
        PRIMARY_FLAG,
        INDEX_REQUIRED_FLAG,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER
          ) values (
        X_STAGED_ATTRIBUTE_TABLE,
        l_staged_attribute_column, --Bug No:4260144
        nvl(X_STAGED_FLAG,'N'),
        X_FUNCTION_ID,
        X_ATTRIBUTE_ID,
        X_PROCEDURE_NAME,
        X_ACTIVE_FLAG,
        X_PRIMARY_FLAG,
        X_INDEX_REQUIRED_FLAG,
        X_CREATION_DATE,
        X_CREATED_BY,
        X_LAST_UPDATE_DATE,
        X_LAST_UPDATED_BY,
        X_LAST_UPDATE_LOGIN,
        1
      );

Line: 81

                 SELECT   HZ_TRANS_FUNCTIONS_s.nextval
		  into   X_FUNCTION_ID FROM sys.dual;

Line: 84

                  SELECT 'Y' INTO l_dummy
                  FROM HZ_TRANS_FUNCTIONS_B
                  WHERE  FUNCTION_ID =   X_FUNCTION_ID;

Line: 97

   insert into HZ_TRANS_FUNCTIONS_TL (
    LAST_UPDATE_DATE,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    FUNCTION_ID,
    TRANSFORMATION_NAME,
    DESCRIPTION,
    LANGUAGE,
    SOURCE_LANG,
    OBJECT_VERSION_NUMBER
  ) select
    X_LAST_UPDATE_DATE,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_FUNCTION_ID,
    X_TRANSFORMATION_NAME,
    X_DESCRIPTION,
    L.LANGUAGE_CODE,
    userenv('LANG'),
    1
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from HZ_TRANS_FUNCTIONS_TL T
    where T.FUNCTION_ID = X_FUNCTION_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 129

end INSERT_ROW;

Line: 136

  cursor c is select
    OBJECT_VERSION_NUMBER
    from HZ_TRANS_FUNCTIONS_B
    where FUNCTION_ID = X_FUNCTION_ID
    for update of FUNCTION_ID nowait;

Line: 143

  cursor c1 is select
      OBJECT_VERSION_NUMBER,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from HZ_TRANS_FUNCTIONS_TL
    where FUNCTION_ID = X_FUNCTION_ID
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of FUNCTION_ID nowait;

Line: 155

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

Line: 189

  cursor c is select
    OBJECT_VERSION_NUMBER
    from HZ_TRANS_FUNCTIONS_B
    where FUNCTION_ID = X_FUNCTION_ID
    for update of FUNCTION_ID nowait;

Line: 196

  cursor c1 is select
      TRANSFORMATION_NAME,DESCRIPTION,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from HZ_TRANS_FUNCTIONS_TL
    where FUNCTION_ID = X_FUNCTION_ID
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of FUNCTION_ID nowait;

Line: 208

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

Line: 240

procedure UPDATE_ROW (
  X_FUNCTION_ID in NUMBER,
  X_STAGED_ATTRIBUTE_TABLE in VARCHAR2,
  X_STAGED_ATTRIBUTE_COLUMN in VARCHAR2,
  X_STAGED_FLAG in VARCHAR2,
  X_ATTRIBUTE_ID in NUMBER,
  X_PROCEDURE_NAME in VARCHAR2,
  X_ACTIVE_FLAG in VARCHAR2,
  X_PRIMARY_FLAG in VARCHAR2,
  X_INDEX_REQUIRED_FLAG in VARCHAR2,
  X_TRANSFORMATION_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,
  X_OBJECT_VERSION_NUMBER IN out NOCOPY NUMBER
) is

  l_object_version_number NUMBER;

Line: 274

  SELECT 1 INTO TMP FROM HZ_TRANS_FUNCTIONS_VL
  WHERE function_id = X_FUNCTION_ID;

Line: 277

  SELECT  nvl(ACTIVE_FLAG,'Y'), PROCEDURE_NAME,
          nvl(PRIMARY_FLAG,'N'), last_updated_by, staged_flag,
          transformation_name, description,
          nvl(INDEX_REQUIRED_FLAG, 'N'), STAGED_ATTRIBUTE_COLUMN
  into l_db_act_flag, l_db_proc_name, l_db_primary_flag, l_db_upd_by, l_db_stg_flag,
       l_db_trans_name, l_db_desc, l_db_index_req_flag, l_db_stg_atr_col
  from HZ_TRANS_FUNCTIONS_VL
  where function_id =X_FUNCTION_ID;

Line: 287

  IF (X_LAST_UPDATED_BY = 1 AND l_db_upd_by <> 1) THEN
     -- coming from seed and data modified by user
     IF (X_PROCEDURE_NAME <>l_db_proc_name) THEN
       update HZ_TRANS_FUNCTIONS_B set
         PROCEDURE_NAME = X_PROCEDURE_NAME,
         STAGED_FLAG = 'N',
         OBJECT_VERSION_NUMBER = l_object_version_number
       where FUNCTION_ID = X_FUNCTION_ID;

Line: 295

       update HZ_TRANS_FUNCTIONS_TL set
         TRANSFORMATION_NAME = X_TRANSFORMATION_NAME,
         DESCRIPTION = X_DESCRIPTION,
         OBJECT_VERSION_NUMBER = l_object_version_number
       where FUNCTION_ID = X_FUNCTION_ID
       and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 316

     update HZ_TRANS_FUNCTIONS_B set
        STAGED_ATTRIBUTE_TABLE = X_STAGED_ATTRIBUTE_TABLE,
        STAGED_ATTRIBUTE_COLUMN = L_STAGED_ATTRIBUTE_COLUMN,
        ATTRIBUTE_ID = X_ATTRIBUTE_ID,
        PROCEDURE_NAME = X_PROCEDURE_NAME,
        ACTIVE_FLAG = X_ACTIVE_FLAG,
        PRIMARY_FLAG = X_PRIMARY_FLAG,
        INDEX_REQUIRED_FLAG = X_INDEX_REQUIRED_FLAG,
        STAGED_FLAG = L_STAGED_FLAG,
        LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
        LAST_UPDATED_BY = X_LAST_UPDATED_BY,
        LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER = l_object_version_number
       where FUNCTION_ID = X_FUNCTION_ID;

Line: 330

       update HZ_TRANS_FUNCTIONS_TL set
        TRANSFORMATION_NAME = X_TRANSFORMATION_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')
--        OBJECT_VERSION_NUMBER = l_object_version_number
       where FUNCTION_ID = X_FUNCTION_ID
       and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 342

end UPDATE_ROW;

Line: 345

procedure DELETE_ROW (
  X_FUNCTION_ID in NUMBER
) is
begin
  delete from HZ_TRANS_FUNCTIONS_TL
  where FUNCTION_ID = X_FUNCTION_ID;

Line: 356

  delete from HZ_TRANS_FUNCTIONS_B
  where FUNCTION_ID = X_FUNCTION_ID;

Line: 362

end DELETE_ROW;

Line: 367

  delete from HZ_TRANS_FUNCTIONS_TL T
  where not exists
    (select NULL
    from HZ_TRANS_FUNCTIONS_B B
    where B.FUNCTION_ID = T.FUNCTION_ID
    );

Line: 374

  update HZ_TRANS_FUNCTIONS_TL T set (
      TRANSFORMATION_NAME,
      DESCRIPTION
    ) = (select
      B.TRANSFORMATION_NAME,
      B.DESCRIPTION
    from HZ_TRANS_FUNCTIONS_TL B
    where B.FUNCTION_ID = T.FUNCTION_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.FUNCTION_ID,
      T.LANGUAGE
  ) in (select
      SUBT.FUNCTION_ID,
      SUBT.LANGUAGE
    from HZ_TRANS_FUNCTIONS_TL SUBB, HZ_TRANS_FUNCTIONS_TL SUBT
    where SUBB.FUNCTION_ID = SUBT.FUNCTION_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.TRANSFORMATION_NAME <> SUBT.TRANSFORMATION_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: 398

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

Line: 443

  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_OWNER in VARCHAR2) IS

  begin

  declare
     user_id		number := 0;

Line: 465

     HZ_TRANS_FUNCTIONS_PKG.UPDATE_ROW(
     X_FUNCTION_ID =>X_FUNCTION_ID,
     X_STAGED_ATTRIBUTE_TABLE =>X_STAGED_ATTRIBUTE_TABLE,
     X_STAGED_ATTRIBUTE_COLUMN =>X_STAGED_ATTRIBUTE_COLUMN,
     X_STAGED_FLAG =>X_STAGED_FLAG,
     X_ATTRIBUTE_ID =>X_ATTRIBUTE_ID,
     X_PROCEDURE_NAME =>X_PROCEDURE_NAME,
     X_ACTIVE_FLAG =>X_ACTIVE_FLAG,
     X_PRIMARY_FLAG =>X_PRIMARY_FLAG,
     X_INDEX_REQUIRED_FLAG =>X_INDEX_REQUIRED_FLAG,
     X_TRANSFORMATION_NAME =>X_TRANSFORMATION_NAME,
     X_DESCRIPTION =>X_DESCRIPTION,
     X_LAST_UPDATE_DATE => sysdate,
     X_LAST_UPDATED_BY => user_id,
     X_LAST_UPDATE_LOGIN => 0,
     X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);

Line: 485

     HZ_TRANS_FUNCTIONS_PKG.INSERT_ROW(
     X_FUNCTION_ID =>L_FUNCTION_ID,
     X_STAGED_ATTRIBUTE_TABLE =>X_STAGED_ATTRIBUTE_TABLE,
     X_STAGED_ATTRIBUTE_COLUMN =>X_STAGED_ATTRIBUTE_COLUMN,
     X_STAGED_FLAG =>X_STAGED_FLAG,
     X_ATTRIBUTE_ID =>X_ATTRIBUTE_ID,
     X_PROCEDURE_NAME =>X_PROCEDURE_NAME,
     X_ACTIVE_FLAG =>X_ACTIVE_FLAG,
     X_PRIMARY_FLAG =>X_PRIMARY_FLAG,
     X_INDEX_REQUIRED_FLAG =>X_INDEX_REQUIRED_FLAG,
     X_TRANSFORMATION_NAME =>X_TRANSFORMATION_NAME,
     X_DESCRIPTION =>X_DESCRIPTION,
     X_CREATION_DATE=>SYSDATE  ,
     X_CREATED_BY =>USER_ID,
     X_LAST_UPDATE_DATE => sysdate,
     X_LAST_UPDATED_BY => user_id,
     X_LAST_UPDATE_LOGIN => 0,
     X_OBJECT_VERSION_NUMBER => 1);

Line: 515

    update HZ_TRANS_FUNCTIONS_TL set
    TRANSFORMATION_NAME = X_TRANSFORMATION_NAME,
    DESCRIPTION = X_DESCRIPTION,
    source_lang = userenv('LANG'),
    last_update_date = sysdate,
    last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
    last_update_login = 0
    where FUNCTION_ID = X_FUNCTION_ID
    and   userenv('LANG') in (language, source_lang);

Line: 531

CURSOR c_stg_attr_col IS  select substr(staged_attribute_column,3)-1 from hz_trans_functions_vl vl1
                          where staged_attribute_table = x_staged_attribute_table
			  and substr(staged_attribute_column,3) > 2
			  and NOT EXISTS(
			    select 'Y'from hz_trans_functions_vl vl2
			    where vl2.staged_attribute_table=vl1.staged_attribute_table
			    and   substr(vl2.staged_attribute_column,3) = substr(vl1.staged_attribute_column,3)-1
			    )
			  and rownum=1 ;

Line: 540

CURSOR c_max_stg_col IS select max(to_number(substr(staged_attribute_column,3)))+1 from hz_trans_functions_vl
                        where staged_attribute_table = X_STAGED_ATTRIBUTE_TABLE;