APPS.ALR_ACTIONS_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_APPLICATION_ID in NUMBER,
  X_ACTION_ID in NUMBER,
  X_END_DATE_ACTIVE in DATE,
  X_NAME in VARCHAR2,
  X_ALERT_ID in NUMBER,
  X_ACTION_TYPE in VARCHAR2,
  X_ENABLED_FLAG in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_ACTION_LEVEL_TYPE in VARCHAR2,
  X_DATE_LAST_EXECUTED in DATE,
  X_FILE_NAME in VARCHAR2,
  X_ARGUMENT_STRING in VARCHAR2,
  X_PROGRAM_APPLICATION_ID in NUMBER,
  X_CONCURRENT_PROGRAM_ID in NUMBER,
  X_LIST_APPLICATION_ID in NUMBER,
  X_LIST_ID in NUMBER,
  X_TO_RECIPIENTS in VARCHAR2,
  X_CC_RECIPIENTS in VARCHAR2,
  X_BCC_RECIPIENTS in VARCHAR2,
  X_PRINT_RECIPIENTS in VARCHAR2,
  X_PRINTER in VARCHAR2,
  X_SUBJECT in VARCHAR2,
  X_REPLY_TO in VARCHAR2,
  X_RESPONSE_SET_ID in NUMBER,
  X_FOLLOW_UP_AFTER_DAYS in NUMBER,
  X_COLUMN_WRAP_FLAG in VARCHAR2,
  X_MAXIMUM_SUMMARY_MESSAGE in NUMBER,
  X_BODY in VARCHAR2,
  X_VERSION_NUMBER in NUMBER,
  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
  cursor C is select ROWID from ALR_ACTIONS
    where APPLICATION_ID = X_APPLICATION_ID
    and ACTION_ID = X_ACTION_ID
    and (((END_DATE_ACTIVE is null)
      and (X_END_DATE_ACTIVE is null))
      or ((END_DATE_ACTIVE is not null)
      and (END_DATE_ACTIVE = X_END_DATE_ACTIVE)))
    ;

Line: 49

  insert into ALR_ACTIONS (
    APPLICATION_ID,
    ACTION_ID,
    NAME,
    ALERT_ID,
    ACTION_TYPE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    END_DATE_ACTIVE,
    ENABLED_FLAG,
    DESCRIPTION,
    ACTION_LEVEL_TYPE,
    DATE_LAST_EXECUTED,
    FILE_NAME,
    ARGUMENT_STRING,
    PROGRAM_APPLICATION_ID,
    CONCURRENT_PROGRAM_ID,
    LIST_APPLICATION_ID,
    LIST_ID,
    TO_RECIPIENTS,
    CC_RECIPIENTS,
    BCC_RECIPIENTS,
    PRINT_RECIPIENTS,
    PRINTER,
    SUBJECT,
    REPLY_TO,
    RESPONSE_SET_ID,
    FOLLOW_UP_AFTER_DAYS,
    COLUMN_WRAP_FLAG,
    MAXIMUM_SUMMARY_MESSAGE_WIDTH,
    BODY,
    VERSION_NUMBER
  ) values (
    X_APPLICATION_ID,
    X_ACTION_ID,
    X_NAME,
    X_ALERT_ID,
    X_ACTION_TYPE,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_END_DATE_ACTIVE,
    X_ENABLED_FLAG,
    X_DESCRIPTION,
    X_ACTION_LEVEL_TYPE,
    X_DATE_LAST_EXECUTED,
    X_FILE_NAME,
    X_ARGUMENT_STRING,
    X_PROGRAM_APPLICATION_ID,
    X_CONCURRENT_PROGRAM_ID,
    X_LIST_APPLICATION_ID,
    X_LIST_ID,
    X_TO_RECIPIENTS,
    X_CC_RECIPIENTS,
    X_BCC_RECIPIENTS,
    X_PRINT_RECIPIENTS,
    X_PRINTER,
    X_SUBJECT,
    X_REPLY_TO,
    X_RESPONSE_SET_ID,
    X_FOLLOW_UP_AFTER_DAYS,
    X_COLUMN_WRAP_FLAG,
    X_MAXIMUM_SUMMARY_MESSAGE,
    X_BODY,
    X_VERSION_NUMBER);

Line: 128

end INSERT_ROW;

Line: 160

  X_LAST_UPDATE_DATE in VARCHAR2,
  X_CUSTOM_MODE in VARCHAR2
) is

    l_user_id number := 0;

Line: 176

    f_ludate  date;    -- entity update date in file

Line: 178

    db_ludate date;    -- entity update date in db

Line: 187

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

Line: 189

  select APPLICATION_ID into l_app_id
  from FND_APPLICATION
  where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME;

Line: 193

  select ALERT_ID into l_alert_id
  from ALR_ALERTS
  where APPLICATION_ID = l_app_id
  and ALERT_NAME = X_ALERT_NAME;

Line: 199

    select APPLICATION_ID into l_program_application_id
    from FND_APPLICATION
    where APPLICATION_SHORT_NAME = X_PROGRAM_APPLICATION_NAME;

Line: 205

    select CONCURRENT_PROGRAM_ID into l_concurrent_program_id
    from FND_CONCURRENT_PROGRAMS
    where APPLICATION_ID = l_program_application_id
    and CONCURRENT_PROGRAM_NAME = X_CONCURRENT_PROGRAM_NAME;

Line: 212

    select APPLICATION_ID into l_list_app_id
    from FND_APPLICATION
    where APPLICATION_SHORT_NAME = X_LIST_APPLICATION_NAME;

Line: 218

    select LIST_ID into l_list_id
    from ALR_DISTRIBUTION_LISTS
    where APPLICATION_ID = l_list_app_id
    and  NAME = X_LIST_NAME
    and (((END_DATE_ACTIVE  is null)
        and (X_ACTION_END_DATE_ACTIVE is null))
      or ((END_DATE_ACTIVE is not null)
        and (END_DATE_ACTIVE =
        to_date(X_ACTION_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'))));

Line: 230

    select RESPONSE_SET_ID into l_resp_set_id
    from ALR_RESPONSE_SETS
    where APPLICATION_ID = l_app_id
    and ALERT_ID = l_alert_id
    and NAME = X_RESPONSE_SET_NAME;

Line: 237

  select distinct ACTION_ID into l_action_id
  from ALR_ACTIONS
  where APPLICATION_ID = l_app_id
  and ALERT_ID = l_alert_id
  and NAME = X_ACTION_NAME
  and (((END_DATE_ACTIVE  is null)
    and (X_ACTION_END_DATE_ACTIVE is null))
    or ((END_DATE_ACTIVE is not null)
    and (END_DATE_ACTIVE =
    to_date(X_ACTION_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'))));

Line: 248

  select last_updated_by, last_update_date
  into  db_luby, db_ludate
  from ALR_ACTIONS
  where application_id = l_app_id
  and   action_id = l_action_id
  and (((END_DATE_ACTIVE  is null)
    and (X_ACTION_END_DATE_ACTIVE is null))
    or ((END_DATE_ACTIVE is not null)
    and (end_date_active =
  to_date(X_ACTION_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'))));

Line: 263

  ALR_ACTIONS_PKG.UPDATE_ROW(
    X_APPLICATION_ID => l_app_id,
    X_ACTION_ID => l_action_id,
    X_END_DATE_ACTIVE =>
      to_date(X_ACTION_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'),
    X_NAME => X_ACTION_NAME,
    X_ALERT_ID => l_alert_id,
    X_ACTION_TYPE => X_ACTION_TYPE,
    X_ENABLED_FLAG => X_ENABLED_FLAG,
    X_DESCRIPTION => X_DESCRIPTION,
    X_ACTION_LEVEL_TYPE => X_ACTION_LEVEL_TYPE,
    X_DATE_LAST_EXECUTED =>
      to_date(X_DATE_LAST_EXECUTED,'YYYY/MM/DD HH24:MI:SS'),
    X_FILE_NAME => X_FILE_NAME,
    X_ARGUMENT_STRING => X_ARGUMENT_STRING,
    X_PROGRAM_APPLICATION_ID => l_program_application_id,
    X_CONCURRENT_PROGRAM_ID => l_concurrent_program_id,
    X_LIST_APPLICATION_ID => l_list_app_id,
    X_LIST_ID => l_list_id,
    X_TO_RECIPIENTS => X_TO_RECIPIENTS,
    X_CC_RECIPIENTS => X_CC_RECIPIENTS,
    X_BCC_RECIPIENTS => X_BCC_RECIPIENTS,
    X_PRINT_RECIPIENTS => X_PRINT_RECIPIENTS,
    X_PRINTER => X_PRINTER,
    X_SUBJECT => X_SUBJECT,
    X_REPLY_TO => X_REPLY_TO,
    X_RESPONSE_SET_ID => l_resp_set_id,
    X_FOLLOW_UP_AFTER_DAYS => X_FOLLOW_UP_AFTER_DAYS,
    X_COLUMN_WRAP_FLAG => X_COLUMN_WRAP_FLAG,
    X_MAXIMUM_SUMMARY_MESSAGE =>
      to_number(X_MAXIMUM_SUMMARY_MESSAGE),
    X_BODY => X_BODY,
    X_VERSION_NUMBER => X_VERSION_NUMBER,
    X_LAST_UPDATE_DATE => f_ludate,
    X_LAST_UPDATED_BY => f_luby,
    X_LAST_UPDATE_LOGIN => 0 );

Line: 306

  select ALR_ACTIONS_S.nextval into l_action_id from dual;

Line: 308

  ALR_ACTIONS_PKG.INSERT_ROW(
    X_ROWID => l_row_id,
    X_APPLICATION_ID => l_app_id,
    X_ACTION_ID => l_action_id,
    X_END_DATE_ACTIVE =>
      to_date(X_ACTION_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'),
    X_NAME => X_ACTION_NAME,
    X_ALERT_ID => l_alert_id,
    X_ACTION_TYPE => X_ACTION_TYPE,
    X_ENABLED_FLAG => X_ENABLED_FLAG,
    X_DESCRIPTION => X_DESCRIPTION,
    X_ACTION_LEVEL_TYPE => X_ACTION_LEVEL_TYPE,
    X_DATE_LAST_EXECUTED =>
      to_date(X_DATE_LAST_EXECUTED,'YYYY/MM/DD HH24:MI:SS'),
    X_FILE_NAME => X_FILE_NAME,
    X_ARGUMENT_STRING => X_ARGUMENT_STRING,
    X_PROGRAM_APPLICATION_ID => l_program_application_id,
    X_CONCURRENT_PROGRAM_ID => l_concurrent_program_id,
    X_LIST_APPLICATION_ID => l_list_app_id,
    X_LIST_ID => l_list_id,
    X_TO_RECIPIENTS => X_TO_RECIPIENTS,
    X_CC_RECIPIENTS => X_CC_RECIPIENTS,
    X_BCC_RECIPIENTS => X_BCC_RECIPIENTS,
    X_PRINT_RECIPIENTS => X_PRINT_RECIPIENTS,
    X_PRINTER => X_PRINTER,
    X_SUBJECT => X_SUBJECT,
    X_REPLY_TO => X_REPLY_TO,
    X_RESPONSE_SET_ID => l_resp_set_id,
    X_FOLLOW_UP_AFTER_DAYS => X_FOLLOW_UP_AFTER_DAYS,
    X_COLUMN_WRAP_FLAG => X_COLUMN_WRAP_FLAG,
    X_MAXIMUM_SUMMARY_MESSAGE =>
      to_number(X_MAXIMUM_SUMMARY_MESSAGE),
    X_BODY => X_BODY,
    X_VERSION_NUMBER => X_VERSION_NUMBER,
    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: 382

  cursor c1 is select
      NAME,
      ALERT_ID,
      ACTION_TYPE,
      ENABLED_FLAG,
      DESCRIPTION,
      ACTION_LEVEL_TYPE,
      DATE_LAST_EXECUTED,
      FILE_NAME,
      ARGUMENT_STRING,
      PROGRAM_APPLICATION_ID,
      CONCURRENT_PROGRAM_ID,
      LIST_APPLICATION_ID,
      LIST_ID,
      TO_RECIPIENTS,
      CC_RECIPIENTS,
      BCC_RECIPIENTS,
      PRINT_RECIPIENTS,
      PRINTER,
      SUBJECT,
      REPLY_TO,
      RESPONSE_SET_ID,
      FOLLOW_UP_AFTER_DAYS,
      COLUMN_WRAP_FLAG,
      MAXIMUM_SUMMARY_MESSAGE_WIDTH,
      BODY,
      VERSION_NUMBER,
      APPLICATION_ID,
      ACTION_ID,
      END_DATE_ACTIVE
    from ALR_ACTIONS
    where APPLICATION_ID = X_APPLICATION_ID
    and ACTION_ID = X_ACTION_ID
    and (((END_DATE_ACTIVE is null)
      and (X_END_DATE_ACTIVE is null))
      or ((END_DATE_ACTIVE is not null)
      and (END_DATE_ACTIVE = X_END_DATE_ACTIVE)))
    for update of APPLICATION_ID nowait;

Line: 502

procedure UPDATE_ROW (
  X_APPLICATION_ID in NUMBER,
  X_ACTION_ID in NUMBER,
  X_END_DATE_ACTIVE in DATE,
  X_NAME in VARCHAR2,
  X_ALERT_ID in NUMBER,
  X_ACTION_TYPE in VARCHAR2,
  X_ENABLED_FLAG in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_ACTION_LEVEL_TYPE in VARCHAR2,
  X_DATE_LAST_EXECUTED in DATE,
  X_FILE_NAME in VARCHAR2,
  X_ARGUMENT_STRING in VARCHAR2,
  X_PROGRAM_APPLICATION_ID in NUMBER,
  X_CONCURRENT_PROGRAM_ID in NUMBER,
  X_LIST_APPLICATION_ID in NUMBER,
  X_LIST_ID in NUMBER,
  X_TO_RECIPIENTS in VARCHAR2,
  X_CC_RECIPIENTS in VARCHAR2,
  X_BCC_RECIPIENTS in VARCHAR2,
  X_PRINT_RECIPIENTS in VARCHAR2,
  X_PRINTER in VARCHAR2,
  X_SUBJECT in VARCHAR2,
  X_REPLY_TO in VARCHAR2,
  X_RESPONSE_SET_ID in NUMBER,
  X_FOLLOW_UP_AFTER_DAYS in NUMBER,
  X_COLUMN_WRAP_FLAG in VARCHAR2,
  X_MAXIMUM_SUMMARY_MESSAGE in NUMBER,
  X_BODY in VARCHAR2,
  X_VERSION_NUMBER in NUMBER,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER
) is
begin


  update ALR_ACTIONS set
    NAME = X_NAME,
    ALERT_ID = X_ALERT_ID,
    ACTION_TYPE = X_ACTION_TYPE,
    ENABLED_FLAG = X_ENABLED_FLAG,
    DESCRIPTION = X_DESCRIPTION,
    ACTION_LEVEL_TYPE = X_ACTION_LEVEL_TYPE,
    DATE_LAST_EXECUTED = X_DATE_LAST_EXECUTED,
    FILE_NAME = X_FILE_NAME,
    ARGUMENT_STRING = X_ARGUMENT_STRING,
    PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
    CONCURRENT_PROGRAM_ID = X_CONCURRENT_PROGRAM_ID,
    LIST_APPLICATION_ID = X_LIST_APPLICATION_ID,
    LIST_ID = X_LIST_ID,
    TO_RECIPIENTS = X_TO_RECIPIENTS,
    CC_RECIPIENTS = X_CC_RECIPIENTS,
    BCC_RECIPIENTS = X_BCC_RECIPIENTS,
    PRINT_RECIPIENTS = X_PRINT_RECIPIENTS,
    PRINTER = X_PRINTER,
    SUBJECT = X_SUBJECT,
    REPLY_TO = X_REPLY_TO,
    RESPONSE_SET_ID = X_RESPONSE_SET_ID,
    FOLLOW_UP_AFTER_DAYS = X_FOLLOW_UP_AFTER_DAYS,
    COLUMN_WRAP_FLAG = X_COLUMN_WRAP_FLAG,
    MAXIMUM_SUMMARY_MESSAGE_WIDTH = X_MAXIMUM_SUMMARY_MESSAGE,
    BODY = X_BODY,
    VERSION_NUMBER = X_VERSION_NUMBER,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
  where APPLICATION_ID = X_APPLICATION_ID
  and ACTION_ID = X_ACTION_ID
    and (((END_DATE_ACTIVE is null)
    and (X_END_DATE_ACTIVE is null))
      or ((END_DATE_ACTIVE is not null)
      and (END_DATE_ACTIVE = X_END_DATE_ACTIVE)));

Line: 579

end UPDATE_ROW;

Line: 581

procedure DELETE_ROW (
  X_APPLICATION_ID in NUMBER,
  X_ACTION_ID in NUMBER,
  X_END_DATE_ACTIVE in DATE
) is
begin
  delete from ALR_ACTIONS
  where APPLICATION_ID = X_APPLICATION_ID
  and ACTION_ID = X_ACTION_ID
  and (((END_DATE_ACTIVE is null)
      and (X_END_DATE_ACTIVE is null))
      or ((END_DATE_ACTIVE is not null)
      and (END_DATE_ACTIVE = X_END_DATE_ACTIVE)));

Line: 599

end DELETE_ROW;