APPS.JTF_IAPP_FAMILIES_PKG SQL Statements

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

Line: 7

procedure INSERT_ROW (
  X_APP_FAMILY_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
  X_APP_FAMILY_ACCESS_NAME IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
  X_DISPLAY_SEQUENCE in NUMBER,
  X_ENABLED_FLAG in VARCHAR2,
  X_APP_FAMILY_DISPLAY_NAME in VARCHAR2,
  X_APP_FAMILY_DESCRIPTION in VARCHAR2,
  X_OWNER in VARCHAR2
) is
  cursor C is select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
    where APP_FAMILY_ID = X_APP_FAMILY_ID
    ;

Line: 21

    select JTF_IAPP_FAMILIES_B_S.nextval from SYS.DUAL
    ;

Line: 25

   l_last_updated_by NUMBER := G_USER_ID;

Line: 41

   	l_last_updated_by := 1;

Line: 43

   	l_last_updated_by := G_USER_ID;

Line: 46

  insert into JTF_IAPP_FAMILIES_B (
    APP_FAMILY_ID,
    APP_FAMILY_ACCESS_NAME,
    DISPLAY_SEQUENCE,
    ENABLED_FLAG,
    DELETED_FLAG,
    OBJECT_VERSION_NUMBER,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    X_APP_FAMILY_ID,
    X_APP_FAMILY_ACCESS_NAME,
    X_DISPLAY_SEQUENCE,
    X_ENABLED_FLAG,
    'N',
    0,
    SYSDATE,
    G_USER_ID,
    SYSDATE,
    l_last_updated_by,
    G_LOGIN_ID
  );

Line: 72

  insert into JTF_IAPP_FAMILIES_TL (
    APP_FAMILY_ID,
    APP_FAMILY_DISPLAY_NAME,
    APP_FAMILY_DESCRIPTION,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_APP_FAMILY_ID,
    X_APP_FAMILY_DISPLAY_NAME,
    X_APP_FAMILY_DESCRIPTION,
    G_USER_ID,
    SYSDATE,
    SYSDATE,
    l_last_updated_by,
    G_LOGIN_ID,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from JTF_IAPP_FAMILIES_TL T
    where T.APP_FAMILY_ID = X_APP_FAMILY_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 110

end INSERT_ROW;

Line: 112

procedure UPDATE_ROW (
  X_APP_FAMILY_ID in NUMBER,
  X_APP_FAMILY_ACCESS_NAME in VARCHAR2,
  X_DISPLAY_SEQUENCE in NUMBER,
  X_ENABLED_FLAG in VARCHAR2,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_APP_FAMILY_DISPLAY_NAME in VARCHAR2,
  X_APP_FAMILY_DESCRIPTION in VARCHAR2,
  X_OWNER in VARCHAR2
) is

   l_last_updated_by NUMBER := G_USER_ID;

Line: 126

  update JTF_IAPP_FAMILIES_B set
    APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME,
    DISPLAY_SEQUENCE = X_DISPLAY_SEQUENCE,
    ENABLED_FLAG = X_ENABLED_FLAG,
    OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
    LAST_UPDATE_DATE = SYSDATE,
    LAST_UPDATED_BY = G_USER_ID,
    LAST_UPDATE_LOGIN = G_LOGIN_ID
  where APP_FAMILY_ID = X_APP_FAMILY_ID;

Line: 142

   	l_last_updated_by := 1;

Line: 144

   	l_last_updated_by := G_USER_ID;

Line: 147

  update JTF_IAPP_FAMILIES_TL set
    APP_FAMILY_DISPLAY_NAME = X_APP_FAMILY_DISPLAY_NAME,
    APP_FAMILY_DESCRIPTION = X_APP_FAMILY_DESCRIPTION,
    LAST_UPDATE_DATE = SYSDATE,
    LAST_UPDATED_BY = l_last_updated_by,
    LAST_UPDATE_LOGIN = G_LOGIN_ID,
    SOURCE_LANG = userenv('LANG')
  where APP_FAMILY_ID = X_APP_FAMILY_ID
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 160

end UPDATE_ROW;

Line: 162

procedure DELETE_ROW (
  X_APP_FAMILY_ID in NUMBER
) is
begin
  delete from JTF_IAPP_FAMILIES_TL
  where APP_FAMILY_ID = X_APP_FAMILY_ID;

Line: 173

  delete from JTF_IAPP_FAMILIES_B
  where APP_FAMILY_ID = X_APP_FAMILY_ID;

Line: 179

end DELETE_ROW;

Line: 188

  cursor C is select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
    where APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
    ;

Line: 192

  cursor C_2 is select f.APP_FAMILY_ID, APPLICATION_ID
  		from JTF_IAPP_FAMILIES_B f, JTF_IAPP_FAMILY_APP_MAP a
    		where f.APP_FAMILY_ID = a.APP_FAMILY_ID
    		AND f.APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
    		AND a.APPLICATION_ID = X_APP_ID
    ;

Line: 217

       insert into JTF_IAPP_FAMILY_APP_MAP (
        APP_FAMILY_ID,
        APPLICATION_ID,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_LOGIN
       ) values (
         l_app_family_id,
         X_APP_ID,
         G_USER_ID,
         SYSDATE,
         SYSDATE,
         G_USER_ID,
         G_LOGIN_ID
       );

Line: 242

procedure DELETE_ALL_APPS_IN_FAMILY (
  X_APP_FAMILY_ACCESS_NAME in VARCHAR2
) is

  cursor C is select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
    where APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
    ;

Line: 259

  delete from JTF_IAPP_FAMILY_APP_MAP
  where APP_FAMILY_ID = l_app_family_id;

Line: 266

end DELETE_ALL_APPS_IN_FAMILY;

Line: 269

procedure DELETE_FAMILY_AND_APPS (
  X_APP_FAMILY_ACCESS_NAME in VARCHAR2
) is

begin

   jtf_iapp_families_pkg.DELETE_ALL_APPS_IN_FAMILY (X_APP_FAMILY_ACCESS_NAME => X_APP_FAMILY_ACCESS_NAME);

Line: 277

   update JTF_IAPP_FAMILIES_B set DELETED_FLAG = 'Y' where APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME;

Line: 283

end DELETE_FAMILY_AND_APPS;

Line: 286

procedure DELETE_APP_IN_FAMILY (
  X_APP_FAMILY_ACCESS_NAME in VARCHAR2,
  X_APP_ID in NUMBER
) is

  cursor C is select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
    where APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
    ;

Line: 304

  delete from JTF_IAPP_FAMILY_APP_MAP
  where APP_FAMILY_ID = l_app_family_id
  	AND APPLICATION_ID = X_APP_ID;

Line: 313

end DELETE_APP_IN_FAMILY;

Line: 321

  cursor C is select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
    where APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
    ;

Line: 325

  cursor C_2 is select f.APP_FAMILY_ID, USER_ID
  		from JTF_IAPP_FAMILIES_B f, JTF_IAPP_FAMILY_USR_MAP u
    		where f.APP_FAMILY_ID = u.APP_FAMILY_ID
    		AND f.APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
    		AND u.USER_ID = X_USER_ID
    ;

Line: 349

         insert into JTF_IAPP_FAMILY_USR_MAP (
          APP_FAMILY_ID,
          USER_ID,
          DEFAULT_RESP_ID,
          CREATED_BY,
          CREATION_DATE,
          LAST_UPDATE_DATE,
          LAST_UPDATED_BY,
          LAST_UPDATE_LOGIN
         ) values (
           l_app_family_id,
           X_USER_ID,
           X_DEFAULT_RESP_ID,
           G_USER_ID,
           SYSDATE,
           SYSDATE,
           G_USER_ID,
           G_LOGIN_ID
         );

Line: 370

        update JTF_IAPP_FAMILY_USR_MAP set
         DEFAULT_RESP_ID = X_DEFAULT_RESP_ID,
         LAST_UPDATE_DATE = SYSDATE,
         LAST_UPDATED_BY = G_USER_ID,
         LAST_UPDATE_LOGIN = G_LOGIN_ID
        where APP_FAMILY_ID = l_app_family_id
        	AND USER_ID = X_USER_ID;

Line: 390

procedure DELETE_USER_DEF_RESP (
  X_APP_FAMILY_ACCESS_NAME in VARCHAR2,
  X_USER_ID in NUMBER
) is

  cursor C is select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
    where APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
    ;

Line: 409

	delete from JTF_IAPP_FAMILY_USR_MAP
	where APP_FAMILY_ID = l_app_family_id
	AND USER_ID = X_USER_ID;

Line: 419

end DELETE_USER_DEF_RESP;

Line: 421

procedure DELETE_USER_DEF_RESP_USING_ID (
  X_APP_FAMILY_ID in NUMBER,
  X_USER_ID in NUMBER
) is

begin

   If (X_APP_FAMILY_ID IS NOT NULL AND X_USER_ID IS NOT NULL) then

	delete from JTF_IAPP_FAMILY_USR_MAP
	where APP_FAMILY_ID = X_APP_FAMILY_ID
	AND USER_ID = X_USER_ID;

Line: 440

end DELETE_USER_DEF_RESP_USING_ID;

Line: 454

  cursor C is select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
    where APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
    ;

Line: 457

  cursor c_2 is select nvl(max(app_family_id), 0)
    from jtf_iapp_families_b where app_family_id < 10000
    ;

Line: 479

      jtf_iapp_families_pkg.INSERT_ROW (
      X_APP_FAMILY_ID => l_app_pseudo_seq,
      X_APP_FAMILY_ACCESS_NAME => l_app_family_name,
      X_DISPLAY_SEQUENCE => X_DISPLAY_SEQUENCE,
      X_ENABLED_FLAG => X_ENABLED_FLAG,
      X_APP_FAMILY_DISPLAY_NAME => X_APP_FAMILY_DISPLAY_NAME,
      X_APP_FAMILY_DESCRIPTION => X_APP_FAMILY_DESCRIPTION,
      X_OWNER => X_OWNER
      );

Line: 489

      jtf_iapp_families_pkg.UPDATE_ROW (
      X_APP_FAMILY_ID => l_app_family_id,
      X_APP_FAMILY_ACCESS_NAME => l_app_family_name,
      X_DISPLAY_SEQUENCE => X_DISPLAY_SEQUENCE,
      X_ENABLED_FLAG => X_ENABLED_FLAG,
      X_OBJECT_VERSION_NUMBER => null,
      X_APP_FAMILY_DISPLAY_NAME => X_APP_FAMILY_DISPLAY_NAME,
      X_APP_FAMILY_DESCRIPTION => X_APP_FAMILY_DESCRIPTION,
      X_OWNER => X_OWNER
      );

Line: 515

      update JTF_IAPP_FAMILIES_TL set
        APP_FAMILY_DISPLAY_NAME = x_APP_FAMILY_DISPLAY_NAME,
        APP_FAMILY_DESCRIPTION = x_APP_FAMILY_DESCRIPTION,
        LAST_UPDATE_DATE = sysdate,
        LAST_UPDATED_BY = decode(x_OWNER, 'SEED', 1, 0),
        LAST_UPDATE_LOGIN = 0,
        SOURCE_LANG = userenv('LANG')
      where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
      and APP_FAMILY_ID = (select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
      			where APP_FAMILY_ACCESS_NAME = x_APP_FAMILY_ACCESS_NAME);

Line: 531

  delete from JTF_IAPP_FAMILIES_TL T
  where not exists
    (select NULL
    from JTF_IAPP_FAMILIES_B B
    where B.APP_FAMILY_ID = T.APP_FAMILY_ID
    );

Line: 538

  update JTF_IAPP_FAMILIES_TL T set (
      APP_FAMILY_DISPLAY_NAME,
      APP_FAMILY_DESCRIPTION
    ) = (select
      B.APP_FAMILY_DISPLAY_NAME,
      B.APP_FAMILY_DESCRIPTION
    from JTF_IAPP_FAMILIES_TL B
    where B.APP_FAMILY_ID = T.APP_FAMILY_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.APP_FAMILY_ID,
      T.LANGUAGE
  ) in (select
      SUBT.APP_FAMILY_ID,
      SUBT.LANGUAGE
    from JTF_IAPP_FAMILIES_TL SUBB, JTF_IAPP_FAMILIES_TL SUBT
    where SUBB.APP_FAMILY_ID = SUBT.APP_FAMILY_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.APP_FAMILY_DISPLAY_NAME <> SUBT.APP_FAMILY_DISPLAY_NAME
      or SUBB.APP_FAMILY_DESCRIPTION <> SUBT.APP_FAMILY_DESCRIPTION
      or (SUBB.APP_FAMILY_DESCRIPTION is null and SUBT.APP_FAMILY_DESCRIPTION is not null)
      or (SUBB.APP_FAMILY_DESCRIPTION is not null and SUBT.APP_FAMILY_DESCRIPTION is null)
  ));

Line: 562

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