APPS.AME_ACTIONS_API SQL Statements

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

Line: 6

  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: 11

  X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);

Line: 12

  X_LAST_UPDATE_LOGIN := 0;

Line: 22

  X_CURRENT_LAST_UPDATE_DATE out nocopy VARCHAR2,
  X_CURRENT_OVN      out nocopy NUMBER
) is
  cursor CSR_GET_ACTION_TYPE_ID
  (
    X_ACTION_TYPE_NAME in VARCHAR2
  ) is
   select ACTION_TYPE_ID
   from   AME_ACTION_TYPES
   where  NAME = X_ACTION_TYPE_NAME
	    and sysdate between START_DATE
			 and nvl(END_DATE  - (1/86400), sysdate);

Line: 39

  ) is select ACTION_ID, ROWID,
          LAST_UPDATED_BY,
          to_char(LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
          nvl(OBJECT_VERSION_NUMBER,1)
     from AME_ACTIONS
    where ACTION_TYPE_ID = X_ACTION_TYPE_ID
      and nvl(PARAMETER,'NULL')      = nvl(X_PARAMETER,'NULL')
      and nvl(PARAMETER_TWO,'NULL')  = nvl(X_PARAMETER_TWO,'NULL')
	    and sysdate between START_DATE
			 and nvl(END_DATE  - (1/86400), sysdate)
    order by LAST_UPDATE_DATE desc;

Line: 54

   select APPROVAL_GROUP_ID
   from   AME_APPROVAL_GROUPS
   where  NAME = X_APPROVAL_GROUP_NAME
   and    sysdate between START_DATE
                  and nvl(end_date - (1/86400), sysdate);

Line: 95

                      X_CURRENT_OWNER, X_CURRENT_LAST_UPDATE_DATE,X_CURRENT_OVN;

Line: 102

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 default null)
return boolean as
begin
  if X_CUSTOM_MODE = 'FORCE' then
    return true;

Line: 115

    ,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: 119

end DO_UPDATE_INSERT;

Line: 120

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 default null)
return boolean as
begin
  if X_CUSTOM_MODE = 'FORCE' then
    return true;

Line: 137

      ,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: 143

end DO_TL_UPDATE_INSERT;

Line: 144

procedure INSERT_ROW (
 X_ACTION_ID                       in NUMBER,
 X_ACTION_TYPE_ID                  in NUMBER,
 X_PARAMETER                       in VARCHAR2,
 X_PARAMETER_TWO                   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_DESCRIPTION                     in VARCHAR2,
 X_OBJECT_VERSION_NUMBER           in NUMBER
 )
 is
begin
  insert into AME_ACTIONS
  (
   ACTION_ID,
   ACTION_TYPE_ID,
   PARAMETER,
   PARAMETER_TWO,
   CREATED_BY,
   CREATION_DATE,
   LAST_UPDATED_BY,
   LAST_UPDATE_DATE,
   LAST_UPDATE_LOGIN,
   START_DATE,
   END_DATE,
   DESCRIPTION,
   OBJECT_VERSION_NUMBER
  ) values (
   X_ACTION_ID,
   X_ACTION_TYPE_ID,
   X_PARAMETER,
   X_PARAMETER_TWO,
   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_OBJECT_VERSION_NUMBER);

Line: 189

end INSERT_ROW;

Line: 191

procedure INSERT_TL_ROW (
  X_ACTION_ID in NUMBER,
  X_DESCRIPTION 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) is
 begin
    if not AME_SEED_UTILITY.MLS_ENABLED then
      return;

Line: 203

  insert into AME_ACTIONS_TL
    (ACTION_ID
    ,DESCRIPTION
    ,CREATED_BY
    ,CREATION_DATE
    ,LAST_UPDATED_BY
    ,LAST_UPDATE_DATE
    ,LAST_UPDATE_LOGIN
    ,LANGUAGE
    ,SOURCE_LANG
    ) select X_ACTION_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_ACTIONS_TL T
                          where T.ACTION_ID = X_ACTION_ID
                            and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 228

END insert_tl_row;

Line: 230

procedure UPDATE_TL_ROW (
  X_ACTION_ID in NUMBER,
  X_DESCRIPTION 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_CUSTOM_MODE in VARCHAR2) is
  X_CURRENT_OWNER  NUMBER;

Line: 240

  X_CURRENT_LAST_UPDATE_DATE DATE;

Line: 246

    select LAST_UPDATED_BY,
           LAST_UPDATE_DATE
       into X_CURRENT_OWNER,
            X_CURRENT_LAST_UPDATE_DATE
       FROM AME_ACTIONS_TL
       WHERE ACTION_ID = X_ACTION_ID
       AND LANGUAGE = USERENV('LANG');

Line: 254

   if DO_UPDATE_INSERT
     (X_LAST_UPDATED_BY
     ,X_CURRENT_OWNER
     ,AME_SEED_UTILITY.DATE_AS_STRING(X_LAST_UPDATE_DATE)
     ,AME_SEED_UTILITY.DATE_AS_STRING(X_CURRENT_LAST_UPDATE_DATE)
     ,X_CUSTOM_MODE) then
      update AME_ACTIONS_TL
         set DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
             SOURCE_LANG = userenv('LANG'),
             LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
             LAST_UPDATED_BY = X_LAST_UPDATED_BY,
             LAST_UPDATE_LOGIN = 0
       where ACTION_ID = X_ACTION_ID
         and userenv('LANG') in (LANGUAGE,SOURCE_LANG);

Line: 272

end UPDATE_TL_ROW;

Line: 274

procedure UPDATE_ROW (
 X_ACTION_ROWID             in VARCHAR2,
 X_END_DATE                 in DATE)
 is
begin
  update AME_ACTIONS set
   END_DATE            = X_END_DATE
  where ROWID          = X_ACTION_ROWID;

Line: 282

end UPDATE_ROW;

Line: 284

procedure FORCE_UPDATE_ROW (
  X_ROWID                      in VARCHAR2,
  X_DESCRIPTION                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_ACTIONS
     set DESCRIPTION = X_DESCRIPTION,
         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: 308

end FORCE_UPDATE_ROW;

Line: 310

procedure DELETE_ROW (
  X_ACTION_ID      in NUMBER
) is
begin
  if AME_SEED_UTILITY.MLS_ENABLED then
    delete from AME_ACTIONS_TL
    where ACTION_ID = X_ACTION_ID;

Line: 318

  delete from AME_ACTIONS
  where ACTION_ID =   X_ACTION_ID;

Line: 323

end DELETE_ROW;

Line: 330

          X_LAST_UPDATE_DATE in VARCHAR2,
          X_CUSTOM_MODE      in VARCHAR2
)
is
  X_ACTION_ID NUMBER;

Line: 338

  X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);

Line: 340

  X_LAST_UPDATED_BY NUMBER;

Line: 341

  X_LAST_UPDATE_LOGIN NUMBER;

Line: 355

  X_CURRENT_LAST_UPDATE_DATE,
  X_CURRENT_OVN
  );

Line: 362

    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );

Line: 370

         select ame_actions_s.nextval
         into X_ACTION_ID
         from dual;

Line: 374

       INSERT_ROW (
         X_ACTION_ID,
         X_ACTION_TYPE_ID,
         L_PARAMETER,
         X_PARAMETER_TWO,
         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,
         1
         );

Line: 388

       INSERT_TL_ROW
       (
       X_ACTION_ID,
       X_DESCRIPTION,
       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
       );

Line: 400

         FORCE_UPDATE_ROW
           (
           X_ACTION_ROWID,
           X_DESCRIPTION,
           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'),
           AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
           X_CURRENT_OVN + 1
           );

Line: 413

         UPDATE_TL_ROW
           (
           X_ACTION_ID,
           X_DESCRIPTION,
           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,
           X_CUSTOM_MODE
           );

Line: 427

         if DO_UPDATE_INSERT(AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
                      X_CURRENT_OWNER,
                      X_LAST_UPDATE_DATE,
                      X_CURRENT_LAST_UPDATE_DATE) then
           UPDATE_ROW (
             X_ACTION_ROWID,
             to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')-(1/86400));

Line: 434

           INSERT_ROW (
             X_ACTION_ID,
             X_ACTION_TYPE_ID,
             L_PARAMETER,
             X_PARAMETER_TWO,
             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_CURRENT_OVN + 1
             );

Line: 448

           UPDATE_TL_ROW
             (
             X_ACTION_ID,
             X_DESCRIPTION,
             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,
             X_CUSTOM_MODE
             );

Line: 481

          X_LAST_UPDATE_DATE in VARCHAR2,
          X_CUSTOM_MODE      in VARCHAR2
)
is
begin
null;

Line: 495

  X_LAST_UPDATE_DATE          in VARCHAR2,
  X_CUSTOM_MODE               in VARCHAR2) as
  X_CURRENT_OWNER            NUMBER;

Line: 498

  X_CURRENT_LAST_UPDATE_DATE varchar2(20);

Line: 513

          select approval_group_id
            into X_APPROVAL_GROUP_ID
            from ame_approval_groups
           where name = X_PARAMETER
             and sysdate between start_date and nvl(end_date, sysdate);

Line: 524

      select ACTTL.LAST_UPDATED_BY,
             AME_SEED_UTILITY.DATE_AS_STRING(ACTTL.LAST_UPDATE_DATE),
             AME_SEED_UTILITY.OWNER_AS_STRING(ACTTL.CREATED_BY),
             ACT.ACTION_ID
        into X_CURRENT_OWNER,
             X_CURRENT_LAST_UPDATE_DATE,
             X_CREATED_BY,
             X_ACTION_ID
        from AME_ACTIONS_TL ACTTL,
             AME_ACTIONS ACT,
             AME_ACTION_TYPES AAT
       where AAT.NAME = X_ACTION_TYPE_NAME
         and AAT.ACTION_TYPE_ID = ACT.ACTION_TYPE_ID
         and nvl(ACT.PARAMETER,'NULL') = nvl(X_TEMP_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)
         and ACTTL.ACTION_ID = ACT.ACTION_ID
         and ACTTL.LANGUAGE = userenv('LANG')
         and (X_ACTION_TYPE_NAME <> ame_util.finalAuthorityTypeName
              or (X_ACTION_TYPE_NAME = ame_util.finalAuthorityTypeName and
                  ACT.ACTION_ID = (
                                   select MIN(ACTION_ID)
                                     from ame_actions aac,ame_action_types aaty
                                    where aac.action_type_id = aaty.action_type_id
                                      and aaty.name = X_ACTION_TYPE_NAME
                                      and sysdate between aac.start_date
                                                      and nvl(aac.end_date,sysdate)
                                      and sysdate between aaty.start_date
                                                      and nvl(aaty.end_date,sysdate)
                                  )
                 )
             );

Line: 557

      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_ACTIONS_TL ACTTL
           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 ACTTL.ACTION_ID = X_ACTION_ID
           and userenv('LANG') in (ACTTL.LANGUAGE,ACTTL.SOURCE_LANG);