APPS.UMX_REG_SERVICES_PKG SQL Statements

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

Line: 3

procedure INSERT_ROW (
  X_ROWID in out NOCOPY VARCHAR2,
  X_REG_SERVICE_CODE in VARCHAR2,
  X_REG_SERVICE_TYPE in VARCHAR2,
  X_WF_NOTIFICATION_EVENT_GUID in RAW,
  X_EMAIL_VERIFICATION_FLAG in VARCHAR2,
  X_APPLICATION_ID in NUMBER,
  X_START_DATE in DATE,
  X_SECURITY_GROUP_ID in NUMBER,
  X_END_DATE in DATE,
  X_WF_ROLE_NAME in VARCHAR2,
  X_REG_FUNCTION_ID in NUMBER,
  X_AME_APPLICATION_ID in NUMBER,
  X_AME_TRANSACTION_TYPE_ID in VARCHAR2,
  X_DISPLAY_NAME in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_USAGE 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_WF_BUS_LOGIC_EVENT_GUID in RAW
) is
  cursor C is select ROWID from UMX_REG_SERVICES_B
    where REG_SERVICE_CODE = X_REG_SERVICE_CODE
    ;

Line: 33

  insert into UMX_REG_SERVICES_B (
    REG_SERVICE_CODE,
    REG_SERVICE_TYPE,
    WF_NOTIFICATION_EVENT_GUID,
    EMAIL_VERIFICATION_FLAG,
    APPLICATION_ID,
    START_DATE,
    SECURITY_GROUP_ID,
    END_DATE,
    WF_ROLE_NAME,
    REG_FUNCTION_ID,
    AME_APPLICATION_ID,
    AME_TRANSACTION_TYPE_ID,
    WF_BUS_LOGIC_EVENT_GUID,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    X_REG_SERVICE_CODE,
    X_REG_SERVICE_TYPE,
    X_WF_NOTIFICATION_EVENT_GUID,
    nvl(X_EMAIL_VERIFICATION_FLAG,'N'),
    X_APPLICATION_ID,
    X_START_DATE,
    X_SECURITY_GROUP_ID,
    X_END_DATE,
    X_WF_ROLE_NAME,
    X_REG_FUNCTION_ID,
    X_AME_APPLICATION_ID,
    X_AME_TRANSACTION_TYPE_ID,
    X_WF_BUS_LOGIC_EVENT_GUID,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );

Line: 73

  insert into UMX_REG_SERVICES_TL (
    REG_SERVICE_CODE,
    DISPLAY_NAME,
    DESCRIPTION,
    USAGE,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    SECURITY_GROUP_ID,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_REG_SERVICE_CODE,
    X_DISPLAY_NAME,
    X_DESCRIPTION,
    X_USAGE,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_SECURITY_GROUP_ID,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from UMX_REG_SERVICES_TL T
    where T.REG_SERVICE_CODE = X_REG_SERVICE_CODE
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 115

end INSERT_ROW;

Line: 135

  cursor c is select
      REG_SERVICE_TYPE,
      WF_NOTIFICATION_EVENT_GUID,
      EMAIL_VERIFICATION_FLAG,
      APPLICATION_ID,
      START_DATE,
      SECURITY_GROUP_ID,
      END_DATE,
      WF_ROLE_NAME,
      REG_FUNCTION_ID,
      AME_APPLICATION_ID,
      AME_TRANSACTION_TYPE_ID,
      WF_BUS_LOGIC_EVENT_GUID
    from UMX_REG_SERVICES_B
    where REG_SERVICE_CODE = X_REG_SERVICE_CODE
    for update of REG_SERVICE_CODE nowait;

Line: 153

  cursor c1 is select
      DISPLAY_NAME,
      DESCRIPTION,
      USAGE,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from UMX_REG_SERVICES_TL
    where REG_SERVICE_CODE = X_REG_SERVICE_CODE
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of REG_SERVICE_CODE nowait;

Line: 167

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

Line: 214

procedure UPDATE_ROW (
  X_REG_SERVICE_CODE in VARCHAR2,
  X_REG_SERVICE_TYPE in VARCHAR2,
  X_WF_NOTIFICATION_EVENT_GUID in RAW,
  X_EMAIL_VERIFICATION_FLAG in VARCHAR2,
  X_APPLICATION_ID in NUMBER,
  X_START_DATE in DATE,
  X_SECURITY_GROUP_ID in NUMBER,
  X_END_DATE in DATE,
  X_WF_ROLE_NAME in VARCHAR2,
  X_REG_FUNCTION_ID in NUMBER,
  X_AME_APPLICATION_ID in NUMBER,
  X_AME_TRANSACTION_TYPE_ID in VARCHAR2,
  X_DISPLAY_NAME in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_USAGE in VARCHAR2,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  X_WF_BUS_LOGIC_EVENT_GUID in RAW
) is
begin
  update UMX_REG_SERVICES_B set
    REG_SERVICE_TYPE = X_REG_SERVICE_TYPE,
    WF_NOTIFICATION_EVENT_GUID = X_WF_NOTIFICATION_EVENT_GUID,
    EMAIL_VERIFICATION_FLAG = X_EMAIL_VERIFICATION_FLAG,
    APPLICATION_ID = X_APPLICATION_ID,
    START_DATE = X_START_DATE,
    SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
    END_DATE = X_END_DATE,
    WF_ROLE_NAME = X_WF_ROLE_NAME,
    REG_FUNCTION_ID = X_REG_FUNCTION_ID,
    AME_APPLICATION_ID = X_AME_APPLICATION_ID,
    AME_TRANSACTION_TYPE_ID = X_AME_TRANSACTION_TYPE_ID,
    WF_BUS_LOGIC_EVENT_GUID = X_WF_BUS_LOGIC_EVENT_GUID ,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
  where REG_SERVICE_CODE = X_REG_SERVICE_CODE;

Line: 258

  update UMX_REG_SERVICES_TL set
    DISPLAY_NAME = X_DISPLAY_NAME,
    DESCRIPTION = X_DESCRIPTION,
    USAGE = X_USAGE,
    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 REG_SERVICE_CODE = X_REG_SERVICE_CODE
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 272

end UPDATE_ROW;

Line: 274

procedure DELETE_ROW (
  X_REG_SERVICE_CODE in VARCHAR2
) is
begin
  delete from UMX_REG_SERVICES_TL
  where REG_SERVICE_CODE = X_REG_SERVICE_CODE;

Line: 285

  delete from UMX_REG_SERVICES_B
  where REG_SERVICE_CODE = X_REG_SERVICE_CODE;

Line: 291

end DELETE_ROW;

Line: 296

  delete from UMX_REG_SERVICES_TL T
  where not exists
    (select NULL
    from UMX_REG_SERVICES_B B
    where B.REG_SERVICE_CODE = T.REG_SERVICE_CODE
    );

Line: 303

  update UMX_REG_SERVICES_TL T set (
      DISPLAY_NAME,
      DESCRIPTION,
      USAGE
    ) = (select
      B.DISPLAY_NAME,
      B.DESCRIPTION,
      B.USAGE
    from UMX_REG_SERVICES_TL B
    where B.REG_SERVICE_CODE = T.REG_SERVICE_CODE
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.REG_SERVICE_CODE,
      T.LANGUAGE
  ) in (select
      SUBT.REG_SERVICE_CODE,
      SUBT.LANGUAGE
    from UMX_REG_SERVICES_TL SUBB, UMX_REG_SERVICES_TL SUBT
    where SUBB.REG_SERVICE_CODE = SUBT.REG_SERVICE_CODE
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
      or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
      or SUBB.USAGE <> SUBT.USAGE
  ));

Line: 328

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

Line: 377

  X_LAST_UPDATE_DATE in VARCHAR2,
  X_DISPLAY_NAME in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_USAGE in VARCHAR2,
  X_OWNER in VARCHAR2,
  X_CUSTOM_MODE in VARCHAR2,
  X_WF_BUS_LOGIC_EVENT_GUID in VARCHAR2

) IS
 app_id  number;

Line: 390

 f_ludate  date;    -- entity update date in file

Line: 392

 db_ludate date;    -- entity update date in db

Line: 405

   select function_id
   from fnd_form_functions
   where function_name = X_REG_FUNCTION_NAME;

Line: 410

   select name from WF_LOCAL_ROLES
   where name = X_WF_ROLE_NAME;

Line: 414

   select name
   from wf_events
   where  guid = HEXTORAW(x_guid_raw);

Line: 419

  select ame.TRANSACTION_TYPE_ID, fa.APPLICATION_ID
  from  AME_TRANSACTION_TYPES_V ame, fnd_application fa
  where nvl(END_DATE,SYSDATE+1) > SYSDATE
  and fa.application_short_name = X_AME_APP_SHORT_NAME
  and ame.TRANSACTION_TYPE_ID = X_AME_TRANSACTION_TYPE_ID;

Line: 426

  select application_id
  from   fnd_application
  where  application_short_name = X_APP_SHORT_NAME;

Line: 436

  f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);

Line: 540

 select last_updated_by, last_update_date
 into db_luby, db_ludate
 from umx_reg_services_b
 where reg_service_code = X_REG_SERVICE_CODE;

Line: 548

    UMX_REG_SERVICES_PKG.UPDATE_ROW(
       X_REG_SERVICE_CODE => X_REG_SERVICE_CODE,
       X_REG_SERVICE_TYPE => X_REG_SERVICE_TYPE,
       X_WF_NOTIFICATION_EVENT_GUID => l_ntf_guid_raw,
       X_EMAIL_VERIFICATION_FLAG => X_EMAIL_VERIFICATION_FLAG,
       X_APPLICATION_ID => app_id,
       X_START_DATE => l_start_date,
       X_END_DATE => l_end_date,
       X_WF_ROLE_NAME => X_WF_ROLE_NAME,
       X_REG_FUNCTION_ID => l_reg_function_id,
       X_AME_APPLICATION_ID => ame_app_id,
       X_AME_TRANSACTION_TYPE_ID => X_AME_TRANSACTION_TYPE_ID,
       X_WF_BUS_LOGIC_EVENT_GUID => l_BUS_LOGIC_guid_raw,
       X_DISPLAY_NAME => X_DISPLAY_NAME,
       X_DESCRIPTION => X_DESCRIPTION,
       X_USAGE => X_USAGE,
       X_LAST_UPDATE_DATE => f_ludate,
       X_LAST_UPDATED_BY => f_luby,
       X_LAST_UPDATE_LOGIN => 0
    );

Line: 572

  UMX_REG_SERVICES_PKG.INSERT_ROW(
    X_ROWID => row_id,
    X_REG_SERVICE_CODE => X_REG_SERVICE_CODE,
    X_REG_SERVICE_TYPE => X_REG_SERVICE_TYPE,
    X_WF_NOTIFICATION_EVENT_GUID  => l_ntf_guid_raw,
    X_EMAIL_VERIFICATION_FLAG => X_EMAIL_VERIFICATION_FLAG,
    X_APPLICATION_ID => app_id,
    X_START_DATE => l_start_date,
    X_END_DATE => l_end_date,
    X_WF_ROLE_NAME => X_WF_ROLE_NAME,
    X_REG_FUNCTION_ID => l_reg_function_id,
    X_AME_APPLICATION_ID => ame_app_id,
    X_AME_TRANSACTION_TYPE_ID => X_AME_TRANSACTION_TYPE_ID,
    X_WF_BUS_LOGIC_EVENT_GUID => l_BUS_LOGIC_guid_raw,
    X_DISPLAY_NAME =>X_DISPLAY_NAME,
    X_DESCRIPTION => X_DESCRIPTION,
    X_USAGE => X_USAGE,
    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
  );

Line: 601

  X_LAST_UPDATE_DATE in VARCHAR2,
  X_DISPLAY_NAME in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_USAGE in VARCHAR2,
  X_OWNER in VARCHAR2,
  X_CUSTOM_MODE in VARCHAR2
)IS
 f_luby number;

Line: 609

 f_ludate  date;    -- entity update date in file

Line: 611

 db_ludate date;    -- entity update date in db

Line: 618

 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);

Line: 620

 select LAST_UPDATED_BY, LAST_UPDATE_DATE
 into db_luby, db_ludate
 from umx_reg_services_tl
 where reg_service_code = X_REG_SERVICE_CODE
 and userenv('LANG') = LANGUAGE;

Line: 629

     update umx_reg_services_tl
     set
     display_name        = nvl(X_DISPLAY_NAME, display_name),
     description         = nvl(X_DESCRIPTION, description),
     usage               = nvl(X_USAGE,usage),
     source_lang         = userenv('LANG'),
     last_update_date    = f_ludate,
     last_updated_by     = f_luby,
     last_update_login   = 0
     where reg_service_code = X_REG_SERVICE_CODE
     and userenv('LANG') in (language, source_lang);

Line: 658

  X_LAST_UPDATE_DATE in VARCHAR2,
  X_DISPLAY_NAME in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_USAGE in VARCHAR2,
  X_OWNER in VARCHAR2,
  X_CUSTOM_MODE in VARCHAR2,
  X_WF_BUS_LOGIC_EVENT_GUID in VARCHAR2,
  X_WF_NOTIFICATION_EVENT_NAME in VARCHAR2,
  X_WF_BUS_LOGIC_EVENT_NAME in VARCHAR2

) IS

  cursor get_notification_guid is
  select guid from wf_events
  where name = X_WF_NOTIFICATION_EVENT_NAME;

Line: 675

  select guid from wf_events
  where name = X_WF_BUS_LOGIC_EVENT_NAME;

Line: 713

  X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
  X_DISPLAY_NAME => X_DISPLAY_NAME,
  X_DESCRIPTION => X_DESCRIPTION,
  X_USAGE => X_USAGE,
  X_OWNER => X_OWNER,
  X_CUSTOM_MODE => X_CUSTOM_MODE,
  X_WF_BUS_LOGIC_EVENT_GUID => l_wf_bus_logic_event_guid
  );