APPS.AME_RULES_API2 SQL Statements

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

Line: 8

  X_LAST_UPDATED_BY out nocopy NUMBER,
  X_LAST_UPDATE_LOGIN out nocopy NUMBER
) is
begin
  X_CREATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);

Line: 13

  X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);

Line: 14

  X_LAST_UPDATE_LOGIN := 0;

Line: 28

   select ITEM_CLASS_ID
   from   AME_ITEM_CLASSES
   where  NAME = X_ITEM_CLASS_NAME
     and  sysdate between START_DATE
     and  nvl(END_DATE  - (1/86400), sysdate);

Line: 37

  select RULE_ID, CREATED_BY
    from AME_RULES
   where RULE_KEY = X_RULE_KEY;

Line: 79

function DO_UPDATE_INSERT(X_OWNER in NUMBER,
                   X_CURRENT_OWNER in NUMBER,
                   X_LAST_UPDATE_DATE in VARCHAR2,
                   X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
                   X_CUSTOM_MODE in VARCHAR2)
return boolean as
begin
  return AME_SEED_UTILITY.MERGE_ROW_TEST
    (X_OWNER                     => X_OWNER
    ,X_CURRENT_OWNER             => X_CURRENT_OWNER
    ,X_LAST_UPDATE_DATE          => X_LAST_UPDATE_DATE
    ,X_CURRENT_LAST_UPDATE_DATE  => X_CURRENT_LAST_UPDATE_DATE
    ,X_CUSTOM_MODE               => X_CUSTOM_MODE
    );

Line: 93

end DO_UPDATE_INSERT;

Line: 95

function DO_TL_UPDATE_INSERT(X_OWNER in NUMBER,
                   X_CURRENT_OWNER in NUMBER,
                   X_LAST_UPDATE_DATE in VARCHAR2,
                   X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
                   X_CREATED_BY in VARCHAR2,
                   X_CUSTOM_MODE in VARCHAR2)
return boolean as
begin
    return AME_SEED_UTILITY.TL_MERGE_ROW_TEST
      (X_OWNER                     => X_OWNER
      ,X_CURRENT_OWNER             => X_CURRENT_OWNER
      ,X_LAST_UPDATE_DATE          => X_LAST_UPDATE_DATE
      ,X_CURRENT_LAST_UPDATE_DATE  => X_CURRENT_LAST_UPDATE_DATE
      ,X_CUSTOM_MODE               => X_CUSTOM_MODE
      );

Line: 110

end DO_TL_UPDATE_INSERT;

Line: 127

  select RULE_ID
    from AME_RULES
   where RULE_KEY = X_RULE_KEY;

Line: 134

   select RULE_KEY
   from   AME_RULES
   where  RULE_ID = X_RULE_ID;

Line: 141

   select ITEM_CLASS_ID
   from   AME_ITEM_CLASSES
   where  NAME = X_ITEM_CLASS_NAME
            and sysdate between START_DATE
                         and nvl(END_DATE  - (1/86400), sysdate);

Line: 150

  select COUNT(*)
    from AME_RULES
   where RULE_ID = X_RULE_ID;

Line: 154

   select action_id
     from ame_actions        aa,
          ame_action_types   aat
     where aa.parameter = X_PARAMETER
            and aat.name = X_ACTION_TYPE_NAME
            and aat.action_type_id = aa.action_type_id
            and sysdate between aa.start_date and
                  nvl(aa.end_date - (1/86400),sysdate)
            and sysdate between aat.start_date and
                  nvl(aat.end_date - (1/86400),sysdate);

Line: 271

procedure INSERT_ROW (
 X_RULE_KEY                        in VARCHAR2,
 X_RULE_TYPE                       in NUMBER,
 X_ACTION_ID                       in NUMBER,
 X_CREATED_BY                      in NUMBER,
 X_CREATION_DATE                   in DATE,
 X_LAST_UPDATED_BY                 in NUMBER,
 X_LAST_UPDATE_DATE                in DATE,
 X_LAST_UPDATE_LOGIN               in NUMBER,
 X_START_DATE                      in DATE,
 X_DESCRIPTION                     in VARCHAR2,
 X_ITEM_CLASS_ID                   in NUMBER,
 X_OBJECT_VERSION_NUMBER           in NUMBER)
 is
  lockHandle varchar2(500);

Line: 293

    select ame_rules_s.nextval into X_RULE_ID from dual;

Line: 294

    insert into AME_RULES
    (
     RULE_ID,
     RULE_KEY,
     RULE_TYPE,
     ACTION_ID,
     CREATED_BY,
     CREATION_DATE,
     LAST_UPDATED_BY,
     LAST_UPDATE_DATE,
     LAST_UPDATE_LOGIN,
     START_DATE,
     END_DATE,
     DESCRIPTION,
     ITEM_CLASS_ID,
     OBJECT_VERSION_NUMBER
    ) select
     X_RULE_ID,
     x_RULE_KEY,
     X_RULE_TYPE,
     X_ACTION_ID,
     X_CREATED_BY,
     X_CREATION_DATE,
     X_LAST_UPDATED_BY,
     X_LAST_UPDATE_DATE,
     X_LAST_UPDATE_LOGIN,
     X_START_DATE,
     AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
     X_DESCRIPTION,
     X_ITEM_CLASS_ID,
     X_OBJECT_VERSION_NUMBER
     from sys.dual
     where not exists (select NULL
                         from AME_RULES
                        where RULE_KEY = X_RULE_KEY
                         and sysdate between START_DATE
                         and nvl(END_DATE - (1/86400), sysdate));

Line: 335

      insert into AME_RULES_TL
        (RULE_ID
        ,DESCRIPTION
        ,CREATED_BY
        ,CREATION_DATE
        ,LAST_UPDATED_BY
        ,LAST_UPDATE_DATE
        ,LAST_UPDATE_LOGIN
        ,LANGUAGE
        ,SOURCE_LANG
        ) select X_RULE_ID,
                 X_DESCRIPTION,
                 X_CREATED_BY,
                 X_CREATION_DATE,
                 X_LAST_UPDATED_BY,
                 X_LAST_UPDATE_DATE,
                 X_LAST_UPDATE_LOGIN,
                 L.LANGUAGE_CODE,
                 userenv('LANG')
            from FND_LANGUAGES L
           where L.INSTALLED_FLAG in ('I', 'B')
             and not exists (select null
                               from AME_RULES_TL T
                              where T.RULE_ID = X_RULE_ID
                                and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 362

end INSERT_ROW;

Line: 364

procedure INSERT_ROW_2 (
 X_RULE_ID                         in NUMBER,
 X_RULE_KEY                        in VARCHAR2,
 X_RULE_TYPE                       in NUMBER,
 X_ACTION_ID                       in NUMBER,
 X_CREATED_BY                      in NUMBER,
 X_CREATION_DATE                   in DATE,
 X_LAST_UPDATED_BY                 in NUMBER,
 X_LAST_UPDATE_DATE                in DATE,
 X_LAST_UPDATE_LOGIN               in NUMBER,
 X_START_DATE                      in DATE,
 X_DESCRIPTION                     in VARCHAR2,
 X_ITEM_CLASS_ID                   in NUMBER,
 X_OBJECT_VERSION_NUMBER           in NUMBER)
 is
  lockHandle varchar2(500);

Line: 385

    insert into AME_RULES
    (
     RULE_ID,
     RULE_KEY,
     RULE_TYPE,
     ACTION_ID,
     CREATED_BY,
     CREATION_DATE,
     LAST_UPDATED_BY,
     LAST_UPDATE_DATE,
     LAST_UPDATE_LOGIN,
     START_DATE,
     END_DATE,
     DESCRIPTION,
     ITEM_CLASS_ID,
     OBJECT_VERSION_NUMBER
    ) select
     X_RULE_ID,
     X_RULE_KEY,
     X_RULE_TYPE,
     X_ACTION_ID,
     X_CREATED_BY,
     X_CREATION_DATE,
     X_LAST_UPDATED_BY,
     X_LAST_UPDATE_DATE,
     X_LAST_UPDATE_LOGIN,
     X_START_DATE,
     AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
     X_DESCRIPTION,
     X_ITEM_CLASS_ID,
     X_OBJECT_VERSION_NUMBER
     from sys.dual
     where not exists (select NULL
                         from AME_RULES
                        where RULE_ID = X_RULE_ID
                          and ((START_DATE - (1/86400)) <= sysdate)
                          and (((END_DATE  - (1/86400)) >= sysdate)
                           or (END_DATE is null)));

Line: 428

      insert into AME_RULES_TL
        (RULE_ID
        ,DESCRIPTION
        ,CREATED_BY
        ,CREATION_DATE
        ,LAST_UPDATED_BY
        ,LAST_UPDATE_DATE
        ,LAST_UPDATE_LOGIN
        ,LANGUAGE
        ,SOURCE_LANG
        ) select X_RULE_ID,
                 X_DESCRIPTION,
                 X_CREATED_BY,
                 X_CREATION_DATE,
                 X_LAST_UPDATED_BY,
                 X_LAST_UPDATE_DATE,
                 X_LAST_UPDATE_LOGIN,
                 L.LANGUAGE_CODE,
                 userenv('LANG')
            from FND_LANGUAGES L
           where L.INSTALLED_FLAG in ('I', 'B')
             and not exists (select null
                               from AME_RULES_TL T
                              where T.RULE_ID = X_RULE_ID
                                and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 455

end INSERT_ROW_2;

Line: 457

procedure DELETE_ROW (
  X_RULE_KEY in VARCHAR2
) is
begin
  if AME_SEED_UTILITY.MLS_ENABLED then
    delete from AME_RULES_TL
     where RULE_ID in (select RULE_ID
                         from AME_RULES
                        where RULE_KEY = X_RULE_KEY);

Line: 467

  delete from AME_RULES
  where RULE_KEY = X_RULE_KEY;

Line: 472

end DELETE_ROW;

Line: 483

          X_LAST_UPDATE_DATE in VARCHAR2,
          X_CUSTOM_MODE      in VARCHAR2
)
is
  X_ACTION_ID NUMBER      :=null;

Line: 492

  X_LAST_UPDATED_BY NUMBER;

Line: 493

  X_LAST_UPDATE_LOGIN NUMBER;

Line: 521

    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );

Line: 543

         update AME_RULES
         set RULE_KEY = '@' || X_RULE_KEY
         where RULE_KEY = X_RULE_KEY;

Line: 546

         INSERT_ROW (
           X_RULE_KEY,
           X_RULE_TYPE,
           X_ACTION_ID,
           X_CREATED_BY,
           to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
           X_LAST_UPDATED_BY,
           to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
           X_LAST_UPDATE_LOGIN,
           to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
           X_DESCRIPTION,
           X_ITEM_CLASS_ID,
           1);

Line: 562

       INSERT_ROW (
         X_RULE_KEY,
         X_RULE_TYPE,
         X_ACTION_ID,
         X_CREATED_BY,
         to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
         X_LAST_UPDATED_BY,
         to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
         X_LAST_UPDATE_LOGIN,
         to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
         X_DESCRIPTION,
         X_ITEM_CLASS_ID,
         1);

Line: 596

     INSERT_ROW_2 (
       X_RULE_ID,
       X_RULE_KEY2,
       X_RULE_TYPE,
       X_ACTION_ID,
       X_CREATED_BY,
       to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
       X_LAST_UPDATED_BY,
       to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
       X_LAST_UPDATE_LOGIN,
       to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
       X_DESCRIPTION,
       X_ITEM_CLASS_ID,
       1);

Line: 617

       update AME_RULES
       set RULE_KEY = X_RULE_KEY
       where RULE_KEY = X_EXISTING_RULE_KEY
         and RULE_ID  = X_RULE_ID;

Line: 638

    ,X_LAST_UPDATE_DATE       in varchar2
    ,X_CUSTOM_MODE            in varchar2
    ) as
    X_CURRENT_OWNER            number;

Line: 642

    X_CURRENT_LAST_UPDATE_DATE varchar2(19);

Line: 650

      select ARUTL.LAST_UPDATED_BY,
             AME_SEED_UTILITY.DATE_AS_STRING(ARUTL.LAST_UPDATE_DATE),
             AME_SEED_UTILITY.OWNER_AS_STRING(ARUTL.CREATED_BY),
             ARU.RULE_ID
        into X_CURRENT_OWNER,
             X_CURRENT_LAST_UPDATE_DATE,
             X_CREATED_BY,
             X_RULE_ID
        from AME_RULES ARU,
             AME_RULES_TL ARUTL
       where ARU.RULE_KEY = X_RULE_KEY
         and sysdate between ARU.START_DATE and nvl(ARU.END_DATE - (1/86400),sysdate)
         and ARUTL.RULE_ID = ARU.RULE_ID
         and ARUTL.LANGUAGE = userenv('LANG');

Line: 664

      if  DO_TL_UPDATE_INSERT
          (X_OWNER                     => AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
           X_CURRENT_OWNER             => X_CURRENT_OWNER,
           X_LAST_UPDATE_DATE          => X_LAST_UPDATE_DATE,
           X_CURRENT_LAST_UPDATE_DATE  => X_CURRENT_LAST_UPDATE_DATE,
           X_CREATED_BY                => X_CREATED_BY,
           X_CUSTOM_MODE               => X_CUSTOM_MODE) then
        update AME_RULES_TL ARUTL
           set DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
               SOURCE_LANG = userenv('LANG'),
               LAST_UPDATE_DATE = AME_SEED_UTILITY.DATE_AS_DATE(X_LAST_UPDATE_DATE),
               LAST_UPDATED_BY = AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
               LAST_UPDATE_LOGIN = 0
         where ARUTL.RULE_ID = X_RULE_ID
           and userenv('LANG') in (ARUTL.LANGUAGE,ARUTL.SOURCE_LANG);