APPS.AME_ITEM_CLASSES_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: 20

  X_CURRENT_LAST_UPDATE_DATE out nocopy VARCHAR2,
  X_CURRENT_OVN              out nocopy NUMBER
) is
  cursor CSR_GET_CURRENT_ITEM_CLASS
  (
    X_ITEM_CLASS_NAME in VARCHAR2
  ) is
   select ROWID,
          ITEM_CLASS_ID,
          LAST_UPDATED_BY,
          to_char(LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
          nvl(OBJECT_VERSION_NUMBER,1)
   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 nvl(max(ITEM_CLASS_ID),4)
   from   AME_ITEM_CLASSES
   where  ITEM_CLASS_ID > 4;

Line: 49

                                        X_CURRENT_LAST_UPDATE_DATE,
                                        X_CURRENT_OVN;

Line: 53

    select decode(X_ITEM_CLASS_NAME
                 ,'header',1
                 ,'line item',2
                 ,'cost center',3
                 ,'project code',4
                 ,ame_item_classes_s.nextVal)
      into X_ITEM_CLASS_ID
      from dual;

Line: 66

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

end DO_UPDATE_INSERT;

Line: 81

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

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

end DO_TL_UPDATE_INSERT;

Line: 105

procedure INSERT_ROW (
 X_ITEM_CLASS_ID                   in NUMBER,
 X_ITEM_CLASS_NAME                 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_OBJECT_VERSION_NUMBER           in NUMBER)
 is
begin
  insert into AME_ITEM_CLASSES
  (
   ITEM_CLASS_ID,
   NAME,
   CREATED_BY,
   CREATION_DATE,
   LAST_UPDATED_BY,
   LAST_UPDATE_DATE,
   LAST_UPDATE_LOGIN,
   START_DATE,
   END_DATE,
   OBJECT_VERSION_NUMBER)
   values (
   X_ITEM_CLASS_ID,
   X_ITEM_CLASS_NAME,
   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_OBJECT_VERSION_NUMBER);

Line: 140

end INSERT_ROW;

Line: 142

procedure INSERT_TL_ROW
  (X_ITEM_CLASS_ID  IN NUMBER
  ,X_USER_ITEM_CLASS_NAME 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
  ) AS
BEGIN
  if not AME_SEED_UTILITY.MLS_ENABLED then
    return;

Line: 156

  insert into AME_ITEM_CLASSES_TL
    (ITEM_CLASS_ID
    ,USER_ITEM_CLASS_NAME
    ,CREATED_BY
    ,CREATION_DATE
    ,LAST_UPDATED_BY
    ,LAST_UPDATE_DATE
    ,LAST_UPDATE_LOGIN
    ,LANGUAGE
    ,SOURCE_LANG
    ) select X_ITEM_CLASS_ID,
             X_USER_ITEM_CLASS_NAME,
             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_ITEM_CLASSES_TL T
                          where T.ITEM_CLASS_ID = X_ITEM_CLASS_ID
                            and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 181

END INSERT_TL_ROW;

Line: 183

procedure UPDATE_TL_ROW (
  X_ITEM_CLASS_ID in NUMBER,
  X_USER_ITEM_CLASS_NAME 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: 193

  X_CURRENT_LAST_UPDATE_DATE DATE;

Line: 199

    select LAST_UPDATED_BY,
           LAST_UPDATE_DATE
       into X_CURRENT_OWNER,
            X_CURRENT_LAST_UPDATE_DATE
       FROM AME_ITEM_CLASSES_TL
       WHERE ITEM_CLASS_ID = X_ITEM_CLASS_ID
       AND LANGUAGE = USERENV('LANG');

Line: 207

   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_ITEM_CLASSES_TL
         set USER_ITEM_CLASS_NAME = nvl(X_USER_ITEM_CLASS_NAME,USER_ITEM_CLASS_NAME),
             SOURCE_LANG = userenv('LANG'),
             LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
             LAST_UPDATED_BY = X_LAST_UPDATED_BY,
             LAST_UPDATE_LOGIN = 0
       where ITEM_CLASS_ID = X_ITEM_CLASS_ID
         and userenv('LANG') in (LANGUAGE,SOURCE_LANG);

Line: 225

end UPDATE_TL_ROW;

Line: 227

procedure FORCE_UPDATE_ROW (
  X_ROWID                      in VARCHAR2,
  X_ITEM_CLASS_NAME            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_ITEM_CLASSES
     set NAME = X_ITEM_CLASS_NAME,
         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: 251

end FORCE_UPDATE_ROW;

Line: 253

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

Line: 261

end UPDATE_ROW;

Line: 262

procedure DELETE_ROW (
  X_ITEM_CLASS_ID in NUMBER
) is
begin
  if AME_SEED_UTILITY.MLS_ENABLED then
    delete from AME_ITEM_CLASSES_TL
      where ITEM_CLASS_ID = X_ITEM_CLASS_ID;

Line: 270

  delete from AME_ITEM_CLASSES
  where ITEM_CLASS_ID = X_ITEM_CLASS_ID;

Line: 275

end DELETE_ROW;

Line: 281

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

Line: 286

  X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);

Line: 290

  X_LAST_UPDATED_BY NUMBER;

Line: 291

  X_LAST_UPDATE_LOGIN NUMBER;

Line: 313

      X_CURRENT_LAST_UPDATE_DATE,
      X_CURRENT_OVN
    );

Line: 320

      X_LAST_UPDATED_BY,
      X_LAST_UPDATE_LOGIN
    );

Line: 326

       INSERT_ROW (
         L_ITEM_CLASS_ID,
         X_ITEM_CLASS_NAME,
         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'),
         1);

Line: 336

       INSERT_TL_ROW
         (L_ITEM_CLASS_ID,
         nvl(X_USER_ITEM_CLASS_NAME,X_ITEM_CLASS_NAME),
         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: 349

         FORCE_UPDATE_ROW
           (X_ITEM_CLASS_ROWID,
            X_ITEM_CLASS_NAME,
            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: 361

          UPDATE_TL_ROW
            (L_ITEM_CLASS_ID,
             nvl(X_USER_ITEM_CLASS_NAME,X_ITEM_CLASS_NAME),
             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: 372

         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_ITEM_CLASS_ROWID,
              to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')-(1/86400));

Line: 380

            INSERT_ROW (
              L_ITEM_CLASS_ID,
              X_ITEM_CLASS_NAME,
              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_CURRENT_OVN + 1);

Line: 390

            UPDATE_TL_ROW
            (L_ITEM_CLASS_ID,
             nvl(X_USER_ITEM_CLASS_NAME,X_ITEM_CLASS_NAME),
             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: 418

    ,X_LAST_UPDATE_DATE       in varchar2
    ,X_CUSTOM_MODE            in varchar2
    ) as
    L_ITEM_CLASS_ID           integer;

Line: 423

    X_CURRENT_LAST_UPDATE_DATE varchar2(20);

Line: 431

      select AICTL.LAST_UPDATED_BY,
             AME_SEED_UTILITY.DATE_AS_STRING(AICTL.LAST_UPDATE_DATE),
             AME_SEED_UTILITY.OWNER_AS_STRING(AICTL.CREATED_BY),
             AICTL.ITEM_CLASS_ID
        into X_CURRENT_OWNER,
             X_CURRENT_LAST_UPDATE_DATE,
             X_CREATED_BY,
             L_ITEM_CLASS_ID
        from AME_ITEM_CLASSES_TL AICTL,
             AME_ITEM_CLASSES AIC
       where AICTL.ITEM_CLASS_ID = AIC.ITEM_CLASS_ID
         and AIC.NAME = X_ITEM_CLASS_NAME
         and sysdate between AIC.START_DATE and nvl(AIC.END_DATE - (1/86400),sysdate)
         and AICTL.LANGUAGE = userenv('LANG');

Line: 446

      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_ITEM_CLASSES_TL AICTL
           set USER_ITEM_CLASS_NAME = nvl(X_USER_ITEM_CLASS_NAME,AICTL.USER_ITEM_CLASS_NAME),
               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 AICTL.ITEM_CLASS_ID = L_ITEM_CLASS_ID
           and userenv('LANG') in (AICTL.LANGUAGE,AICTL.SOURCE_LANG);