APPS.AME_ACTION_USAGES_API2 SQL Statements

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

Line: 14

      select ARU.RULE_ID
        into X_RULE_ID
        from AME_RULES ARU
       where ARU.RULE_KEY = X_RULE_KEY
         and sysdate between ARU.START_DATE and nvl(ARU.END_DATE - (1/86400),sysdate);

Line: 25

      select ACT.ACTION_ID,
             ACT.ACTION_TYPE_ID
        into X_ACTION_ID,
             X_ACTION_TYPE_ID
        from AME_ACTIONS ACT,
             AME_ACTION_TYPES AAT
       where ACT.ACTION_TYPE_ID = AAT.ACTION_TYPE_ID
         and AAT.NAME = X_ACTION_TYPE_NAME
         and nvl(ACT.PARAMETER,'NULL') = nvl(X_PARAMETER,'NULL')
         and nvl(ACT.PARAMETER_TWO,'NULL') = nvl(X_PARAMETER_TWO,'NULL')
         and sysdate between AAT.START_DATE and nvl(AAT.END_DATE - (1/86400),sysdate)
         and sysdate between ACT.START_DATE and nvl(ACT.END_DATE - (1/86400),sysdate);

Line: 49

      select ARU.RULE_TYPE
        into X_RULE_TYPE
        from AME_RULES ARU
       where ARU.RULE_KEY = X_RULE_KEY
         and sysdate between ARU.START_DATE and nvl(ARU.END_DATE - (1/86400),sysdate);

Line: 76

        select AAG.APPROVAL_GROUP_ID
          into L_APPROVAL_GROUP_ID
          from AME_APPROVAL_GROUPS AAG
         where AAG.NAME = X_PARAMETER
           and sysdate between AAG.START_DATE and nvl(AAG.END_DATE - (1/86400),sysdate);

Line: 96

     select ACA.APPLICATION_ID
     from   AME_CALLING_APPS ACA,
            AME_RULE_USAGES ARU
     where  ACA.APPLICATION_ID = ARU.ITEM_ID
       and  ARU.RULE_ID = X_RULE_ID
       and  sysdate between ARU.START_DATE
         and nvl(ARU.END_DATE - (1/86400), sysdate);

Line: 111

  procedure INSERT_ROW
    (X_ACTION_ID             in number
    ,X_RULE_ID               in number
    ,X_START_DATE            in date
    ,X_END_DATE              in date
    ,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_OBJECT_VERSION_NUMBER in number
    ) as
    X_LOCK_HANDLE             varchar2(500);

Line: 136

      insert into AME_ACTION_USAGES
        (ACTION_ID
        ,RULE_ID
        ,START_DATE
        ,END_DATE
        ,CREATED_BY
        ,CREATION_DATE
        ,LAST_UPDATED_BY
        ,LAST_UPDATE_DATE
        ,LAST_UPDATE_LOGIN
        ,OBJECT_VERSION_NUMBER
        ) select X_ACTION_ID,
                 X_RULE_ID,
                 X_START_DATE,
                 X_END_DATE,
                 X_CREATED_BY,
                 X_CREATION_DATE,
                 X_LAST_UPDATED_BY,
                 X_LAST_UPDATE_DATE,
                 X_LAST_UPDATE_LOGIN,
                 X_OBJECT_VERSION_NUMBER
            from dual
           where not exists (select null
                               from AME_ACTION_USAGES
                              where RULE_ID = X_RULE_ID
                                and ACTION_ID = X_ACTION_ID
                                and sysdate between START_DATE and nvl(END_DATE - (1/86400), sysdate));

Line: 165

  end INSERT_ROW;

Line: 167

  procedure UPDATE_ROW
    (X_ACTION_ID             in number
    ,X_RULE_ID               in number
    ,X_START_DATE            in date
    ,X_END_DATE              in date
    ,X_CREATED_BY            in number
    ,X_CREATION_DATE         in date
    ,X_LAST_UPDATE_DATE      in date
    ,X_LAST_UPDATED_BY       in number
    ,X_LAST_UPDATE_LOGIN     in number
    ,X_OBJECT_VERSION_NUMBER in number
    ) as
    X_LOCK_HANDLE             varchar2(500);

Line: 193

      update AME_ACTION_USAGES AAU
         set AAU.END_DATE = X_START_DATE
       where AAU.ACTION_ID = X_ACTION_ID
         and AAU.RULE_ID = X_RULE_ID
         and sysdate between AAU.START_DATE and nvl(AAU.END_DATE - (1/86400),sysdate);

Line: 199

      insert into AME_ACTION_USAGES
        (ACTION_ID
        ,RULE_ID
        ,START_DATE
        ,END_DATE
        ,CREATED_BY
        ,CREATION_DATE
        ,LAST_UPDATED_BY
        ,LAST_UPDATE_DATE
        ,LAST_UPDATE_LOGIN
        ,OBJECT_VERSION_NUMBER
        ) values
        (X_ACTION_ID
        ,X_RULE_ID
        ,X_START_DATE
        ,X_END_DATE
        ,X_CREATED_BY
        ,X_CREATION_DATE
        ,X_LAST_UPDATED_BY
        ,X_LAST_UPDATE_DATE
        ,X_LAST_UPDATE_LOGIN
        ,X_OBJECT_VERSION_NUMBER
        );

Line: 224

  end UPDATE_ROW;

Line: 226

  procedure FORCE_UPDATE_ROW (
    X_ROWID                      in VARCHAR2,
    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_END_DATE                   in DATE,
    X_OBJECT_VERSION_NUMBER      in NUMBER
  ) is
  begin
    update AME_ACTION_USAGES
       set CREATED_BY = X_CREATED_BY,
           CREATION_DATE = X_CREATION_DATE,
           LAST_UPDATED_BY = X_LAST_UPDATED_BY,
           LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
           LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
           START_DATE = X_START_DATE,
           END_DATE = X_END_DATE,
           OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
     where ROWID = X_ROWID;

Line: 248

  end FORCE_UPDATE_ROW;

Line: 256

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

Line: 268

    L_LAST_UPDATE_DATE      varchar2(19);

Line: 280

    L_LAST_UPDATE_DATE := X_LAST_UPDATE_DATE;

Line: 303

      select nvl(AAU.OBJECT_VERSION_NUMBER,1),
             ROWID
        into L_OBJECT_VERSION_NUMBER,
             L_ROWID
        from AME_ACTION_USAGES AAU
       where AAU.RULE_ID = L_RULE_ID
         and AAU.ACTION_ID = L_ACTION_ID
         and sysdate between AAU.START_DATE and nvl(AAU.END_DATE - (1/86400),sysdate);

Line: 313

        FORCE_UPDATE_ROW
          (X_ROWID                 => L_ROWID
          ,X_CREATED_BY            => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
          ,X_CREATION_DATE         => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
          ,X_LAST_UPDATE_DATE      => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
          ,X_LAST_UPDATED_BY       => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
          ,X_LAST_UPDATE_LOGIN     => 0
          ,X_START_DATE            => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
          ,X_END_DATE              => L_END_DATE
          ,X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER + 1
          );

Line: 327

        select count(*)
          into L_ACTION_USAGES_COUNT
          from ame_rules
         where RULE_ID = L_RULE_ID
           and ACTION_ID = L_ACTION_ID
           and sysdate between START_DATE
                         and nvl(END_DATE  - (1/86400), sysdate);

Line: 336

          INSERT_ROW
            (X_ACTION_ID             => L_ACTION_ID
            ,X_RULE_ID               => L_RULE_ID
            ,X_START_DATE            => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
            ,X_END_DATE              => L_END_DATE
            ,X_CREATED_BY            => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
            ,X_CREATION_DATE         => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
            ,X_LAST_UPDATED_BY       => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
            ,X_LAST_UPDATE_DATE      => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
            ,X_LAST_UPDATE_LOGIN     => 0
            ,X_OBJECT_VERSION_NUMBER => 1
            );

Line: 364

    ,X_LAST_UPDATE_DATE   in varchar2
    ,X_UPLOAD_MODE        in varchar2
    ,X_CUSTOM_MODE        in varchar2
    ) return boolean as
    X_CURRENT_OWNER              NUMBER;

Line: 369

    X_CURRENT_LAST_UPDATE_DATE   varchar2(19);

Line: 375

        select CUST.OWNER,
               CUST.LAST_UPDATE_DATE
          into X_CURRENT_OWNER,
               X_CURRENT_LAST_UPDATE_DATE
          from (select AAU.LAST_UPDATED_BY OWNER,
                       AME_SEED_UTILITY.DATE_AS_STRING(AAU.LAST_UPDATE_DATE) LAST_UPDATE_DATE
                  from AME_ACTIONS ACT,
                       AME_ACTION_TYPES AAT,
                       AME_RULES ARU,
                       AME_ACTION_USAGES AAU
                 where ACT.ACTION_TYPE_ID = AAT.ACTION_TYPE_ID
                   and ACT.ACTION_ID = AAU.ACTION_ID
                   and AAU.RULE_ID = ARU.RULE_ID
                   and AAT.NAME not in
                         ('approval-group chain of authority')
                   and ARU.RULE_TYPE in (1,2,7)
                   and ARU.RULE_KEY = X_RULE_KEY
                   and AAT.NAME = X_ACTION_TYPE_NAME
                   and nvl(ACT.PARAMETER,'NULL') = nvl(X_PARAMETER,'NULL')
                   and nvl(ACT.PARAMETER_TWO,'NULL') = nvl(X_PARAMETER_TWO,'NULL')
                   and sysdate between ACT.START_DATE and nvl(ACT.END_DATE - (1/86400),sysdate)
                   and sysdate between AAT.START_DATE and nvl(AAT.END_DATE - (1/86400),sysdate)
                   and sysdate between AAU.START_DATE and nvl(AAU.END_DATE - (1/86400),sysdate)
                   and sysdate between ARU.START_DATE and nvl(ARU.END_DATE - (1/86400),sysdate)
                union
                select AAU.LAST_UPDATED_BY OWNER,
                       AME_SEED_UTILITY.DATE_AS_STRING(AAU.LAST_UPDATE_DATE) LAST_UPDATE_DATE
                  from AME_ACTIONS ACT,
                       AME_ACTION_TYPES AAT,
                       AME_RULES ARU,
                       AME_ACTION_USAGES AAU,
                       AME_APPROVAL_GROUPS AAG
                 where ACT.ACTION_TYPE_ID = AAT.ACTION_TYPE_ID
                   and ACT.ACTION_ID = AAU.ACTION_ID
                   and AAU.RULE_ID = ARU.RULE_ID
                   and ACT.PARAMETER = to_char(AAG.APPROVAL_GROUP_ID)
                   and AAT.NAME in
                         ('pre-chain-of-authority approvals'
                         ,'post-chain-of-authority approvals'
                         ,'approval-group chain of authority')
                   and ARU.RULE_TYPE in (1,2,5,6,7)
                   and AAG.IS_STATIC = 'N'
                   and ARU.RULE_KEY = X_RULE_KEY
                   and AAT.NAME = X_ACTION_TYPE_NAME
                   and AAG.NAME = X_PARAMETER
                   and sysdate between ACT.START_DATE and nvl(ACT.END_DATE - (1/86400),sysdate)
                   and sysdate between AAT.START_DATE and nvl(AAT.END_DATE - (1/86400),sysdate)
                   and sysdate between AAU.START_DATE and nvl(AAU.END_DATE - (1/86400),sysdate)
                   and sysdate between ARU.START_DATE and nvl(ARU.END_DATE - (1/86400),sysdate)
                   and sysdate between AAG.START_DATE and nvl(AAG.END_DATE - (1/86400),sysdate)) CUST;

Line: 432

             ,X_CURRENT_LAST_UPDATE_DATE  => X_CURRENT_LAST_UPDATE_DATE
             ,X_OWNER                     => AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER)
             ,X_LAST_UPDATE_DATE          => X_LAST_UPDATE_DATE
             ,X_CUSTOM_MODE               => X_CUSTOM_MODE
             );

Line: 445

    ,X_LAST_UPDATE_DATE   in varchar2
    ,X_UPLOAD_MODE        in varchar2
    ,X_CUSTOM_MODE        in varchar2
    ) as
  begin
    AME_SEED_UTILITY.INIT_AME_INSTALLATION_LEVEL;

Line: 465

           ,X_LAST_UPDATE_DATE   => X_LAST_UPDATE_DATE
           ,X_CUSTOM_MODE        => X_CUSTOM_MODE
           );