APPS.AME_ATTRIBUTE_USAGES_API SQL Statements

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

Line: 11

  select  AME_RULE_USAGES.RULE_ID, AME_RULES.ACTION_ID
    from AME_RULES, AME_RULE_USAGES
   where AME_RULES.RULE_ID =  AME_RULE_USAGES.RULE_ID
     and AME_RULE_USAGES.ITEM_ID = X_APPLICATION_ID
     and ((sysdate between AME_RULES.START_DATE
            and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
      or (sysdate < AME_RULES.START_DATE
            and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
                          AME_RULES.START_DATE + (1/86400))))
     and ((sysdate between AME_RULE_USAGES.START_DATE
     and nvl(AME_RULE_USAGES.END_DATE - (1/86400), sysdate))
      or (sysdate < AME_RULE_USAGES.START_DATE
     and AME_RULE_USAGES.START_DATE < nvl(AME_RULE_USAGES.END_DATE,
                          AME_RULE_USAGES.START_DATE + (1/86400))));

Line: 31

  select COUNT(*)
    into MANDATORY_COUNT
    from AME_MANDATORY_ATTRIBUTES
   where ATTRIBUTE_ID = X_ATTRIBUTE_ID
     and ACTION_TYPE_ID = ame_util.mandAttActionTypeId
     and SYSDATE between START_DATE
     and nvl(END_DATE  - (1/86400), sysdate);

Line: 42

      select count(*)
        into TEMP_COUNT
        from AME_CONDITIONS,
             AME_CONDITION_USAGES
       where AME_CONDITIONS.ATTRIBUTE_ID = X_ATTRIBUTE_ID
         and AME_CONDITIONS.CONDITION_ID = AME_CONDITION_USAGES.CONDITION_ID
         and AME_CONDITION_USAGES.RULE_ID = TEMPRULE.RULE_ID
         and sysdate between AME_CONDITIONS.START_DATE
         and nvl(AME_CONDITIONS.END_DATE - (1/86400), sysdate)
         and ((sysdate between AME_CONDITION_USAGES.START_DATE
         and nvl(AME_CONDITION_USAGES.END_DATE - (1/86400), sysdate))
          or (sysdate < AME_CONDITION_USAGES.START_DATE
         and AME_CONDITION_USAGES.START_DATE <
             nvl(AME_CONDITION_USAGES.END_DATE,
                 AME_CONDITION_USAGES.START_DATE + (1/86400))));

Line: 62

          select count(*)
            into TEMP_COUNT
            from AME_MANDATORY_ATTRIBUTES,
                 AME_ACTIONS,
                 AME_ACTION_USAGES
           where AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID = X_ATTRIBUTE_ID
             and AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID =
                 AME_ACTIONS.ACTION_TYPE_ID
             and AME_ACTIONS.ACTION_ID = AME_ACTION_USAGES.ACTION_ID
             and AME_ACTION_USAGES.RULE_ID = TEMPRULE.RULE_ID
             and sysdate between AME_MANDATORY_ATTRIBUTES.START_DATE
             and nvl(AME_MANDATORY_ATTRIBUTES.END_DATE - (1/86400), sysdate)
             and sysdate between AME_ACTIONS.START_DATE
             and nvl(AME_ACTIONS.END_DATE - (1/86400), sysdate)
             and ((sysdate between AME_ACTION_USAGES.START_DATE
             and nvl(AME_ACTION_USAGES.END_DATE - (1/86400), sysdate))
             or (sysdate < AME_ACTION_USAGES.START_DATE
             and AME_ACTION_USAGES.START_DATE < nvl(AME_ACTION_USAGES.END_DATE,
                 AME_ACTION_USAGES.START_DATE + (1/86400))));

Line: 83

          select count(*)
            into TEMP_COUNT
            from AME_MANDATORY_ATTRIBUTES,
                 AME_ACTIONS,
                 AME_RULES
           where AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID = X_ATTRIBUTE_ID
             and AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID =
                 AME_ACTIONS.ACTION_TYPE_ID
             and AME_ACTIONS.ACTION_ID = AME_RULES.ACTION_ID
             and AME_RULES.RULE_ID = TEMPRULE.RULE_ID
             and sysdate between AME_MANDATORY_ATTRIBUTES.START_DATE
             and nvl(AME_MANDATORY_ATTRIBUTES.END_DATE - (1/86400), sysdate)
             and sysdate between AME_ACTIONS.START_DATE
             and nvl(AME_ACTIONS.END_DATE - (1/86400), sysdate)
             and ((sysdate between AME_RULES.START_DATE
             and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
             or (sysdate < AME_RULES.START_DATE
             and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
                 AME_RULES.START_DATE + (1/86400))));

Line: 127

    select 'Y'
    from  AME_ATTRIBUTE_USAGES
    where ATTRIBUTE_ID   = X_ATTRIBUTE_ID
     and  APPLICATION_ID = X_APPLICATION_ID
     and  LAST_UPDATED_BY not in (1,120)
     and  CREATED_BY in (1,120)
     and  sysdate between START_DATE
             and nvl(END_DATE - (1/86400), sysdate)
     and exists (select null
                 from AME_ATTRIBUTE_USAGES
                 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
                   and APPLICATION_ID = X_APPLICATION_ID
                  group by ATTRIBUTE_ID, APPLICATION_ID
                  having max(USE_COUNT) > 0)
     and not exists (select null
                     from AME_ATTRIBUTES ATTR1,
                          AME_ATTRIBUTE_USAGES ATTRU1
                     where ATTR1.ATTRIBUTE_ID = X_ATTRIBUTE_ID
                       and ATTRU1.APPLICATION_ID = X_APPLICATION_ID
                       and ATTR1.ATTRIBUTE_ID = ATTRU1.ATTRIBUTE_ID
                       and ATTR1.LAST_UPDATED_BY not in (1,120)
                       and ATTR1.CREATION_DATE = ATTRU1.CREATION_DATE);

Line: 222

  select LINE_ITEM
  into   X_LINE_ITEM
    from ame_attributes
    where ATTRIBUTE_ID=X_ATTRIBUTE_ID
          and SYSDATE between START_DATE
              and nvl(END_DATE  - (1/86400), sysdate);

Line: 267

   X_LAST_UPDATE_DATE in varchar2,
   X_CURRENT_LAST_UPDATE_DATE in out nocopy varchar2 ) is
  -- get all the seeded attributes impacted by the rules, when created first time
  -- using the particular attribute
  cursor ATTRIBUTE_USAGE_DATE_CUR (startDateIn date) is
    select rowid,
           END_DATE
    from AME_ATTRIBUTE_USAGES
    where ATTRIBUTE_ID   =  X_ATTRIBUTE_ID
      and APPLICATION_ID =  X_APPLICATION_ID
      and START_DATE     >= STARTDATEIN
    order by START_DATE;

Line: 281

  lastUpdateDate date;

Line: 287

    if(to_date(X_CURRENT_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS') >=
       to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')) then
      select min(START_DATE)
      into   minStartDate
      from   AME_ATTRIBUTE_USAGES
      where  ATTRIBUTE_ID   = X_ATTRIBUTE_ID
        and APPLICATION_ID  = X_APPLICATION_ID
        and LAST_UPDATED_BY not in (1,120);

Line: 300

        lastUpdateDate := minStartDate + ((recCounter+1)*oneSecond);

Line: 303

          X_CURRENT_LAST_UPDATE_DATE := lastUpdateDate;

Line: 305

          endDate := lastUpdateDate;

Line: 307

        update ame_attribute_usages
        set start_date     = startDate,
            end_date       = endDate,
            creation_date  = creationDate,
          last_update_date = lastUpdateDate
        where rowid = rec.rowid;

Line: 316

    update AME_ATTRIBUTE_USAGES
    set    LAST_UPDATED_BY = 1
    where  ATTRIBUTE_ID    = X_ATTRIBUTE_ID
      and  APPLICATION_ID  = X_APPLICATION_ID;

Line: 331

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

  X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);

Line: 337

  X_LAST_UPDATE_LOGIN := 0;

Line: 413

  X_CURRENT_LAST_UPDATE_DATE out nocopy VARCHAR2,
  X_LINE_ITEM_ID_QUERY       out nocopy VARCHAR2,
  X_CURRENT_OVN              out nocopy NUMBER
) is
  cursor CSR_GET_ATTRIBUTE_ID
  (
    X_ATTRIBUTE_NAME in VARCHAR2
  ) is
   select ATTRIBUTE_ID
   from   AME_ATTRIBUTES
   where  NAME = X_ATTRIBUTE_NAME
      and sysdate between START_DATE
       and nvl(END_DATE  - (1/86400), sysdate);

Line: 431

   select APPLICATION_ID, LINE_ITEM_ID_QUERY
   from   AME_CALLING_APPS
   where  APPLICATION_NAME = X_APPLICATION_NAME
      and sysdate between START_DATE
       and nvl(END_DATE  - (1/86400), sysdate);

Line: 442

   select ROWID, USER_EDITABLE,
          LAST_UPDATED_BY,
          to_char(LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
          nvl(OBJECT_VERSION_NUMBER,1)
     from AME_ATTRIBUTE_USAGES
    where ATTRIBUTE_ID   = X_ATTRIBUTE_ID
      and APPLICATION_ID = X_APPLICATION_ID
      and sysdate between START_DATE
       and nvl(END_DATE  - (1/86400), sysdate);

Line: 456

   select FLEX_VALUE_SET_ID
     from FND_FLEX_VALUE_SETS
    where FLEX_VALUE_SET_NAME = X_VALUE_SET_NAME;

Line: 488

                                     X_CURRENT_LAST_UPDATE_DATE,
                                     X_CURRENT_OVN;

Line: 509

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

end DO_UPDATE_INSERT;

Line: 530

    select ICLU.ITEM_ID_QUERY
    from AME_ITEM_CLASSES ICLS,
         AME_ITEM_CLASS_USAGES ICLU
    where ICLU.APPLICATION_ID = X_APPLICATION_ID
      and ICLS.ITEM_CLASS_ID = ICLU.ITEM_CLASS_ID
      and ICLS.NAME = ame_util.lineitemitemclassname
      and sysdate between ICLS.START_DATE and nvl(ICLS.END_DATE - (1/86400), sysdate)
      and sysdate between ICLU.START_DATE and nvl(ICLU.END_DATE - (1/86400), sysdate);

Line: 584

procedure INSERT_ROW (
 X_ATTRIBUTE_ID                    in NUMBER,
 X_APPLICATION_ID                  in NUMBER,
 X_QUERY_STRING                    in VARCHAR2,
 X_USE_COUNT                       in NUMBER,
 X_IS_STATIC                       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_USER_EDITABLE                   in VARCHAR2,
 X_VALUE_SET_ID                    in NUMBER,
 X_OBJECT_VERSION_NUMBER           in NUMBER)
  is
begin
  insert into AME_ATTRIBUTE_USAGES
  (ATTRIBUTE_ID,
   APPLICATION_ID,
   QUERY_STRING,
   USE_COUNT,
   IS_STATIC,
   CREATED_BY,
   CREATION_DATE,
   LAST_UPDATED_BY,
   LAST_UPDATE_DATE,
   LAST_UPDATE_LOGIN,
   START_DATE,
   END_DATE,
   USER_EDITABLE,
   VALUE_SET_ID,
   OBJECT_VERSION_NUMBER
  ) values (
   X_ATTRIBUTE_ID,
   X_APPLICATION_ID,
   X_QUERY_STRING,
   X_USE_COUNT,
   X_IS_STATIC,
   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_USER_EDITABLE,
   X_VALUE_SET_ID,
   X_OBJECT_VERSION_NUMBER
  );

Line: 634

end INSERT_ROW;

Line: 636

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

Line: 644

end UPDATE_ROW;

Line: 646

procedure FORCE_UPDATE_ROW (
  X_ROWID                      in VARCHAR2,
  X_QUERY_STRING               in VARCHAR2,
  X_USE_COUNT                  in NUMBER,
  X_IS_STATIC                  in VARCHAR2,
  X_USER_EDITABLE              in VARCHAR2,
  X_VALUE_SET_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_END_DATE                   in DATE,
  X_OBJECT_VERSION_NUMBER      in NUMBER
) is
begin
  update AME_ATTRIBUTE_USAGES
     set QUERY_STRING = X_QUERY_STRING,
         USE_COUNT = X_USE_COUNT,
         IS_STATIC = X_IS_STATIC,
         USER_EDITABLE = X_USER_EDITABLE,
         VALUE_SET_ID = X_VALUE_SET_ID,
         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: 678

end FORCE_UPDATE_ROW;

Line: 680

procedure DELETE_ROW (
  X_ATTRIBUTE_ID in NUMBER,
  X_APPLICATION_ID in NUMBER
) is
begin
  delete from AME_ATTRIBUTE_USAGES
  where ATTRIBUTE_ID =   X_ATTRIBUTE_ID
    and APPLICATION_ID = X_APPLICATION_ID;

Line: 692

end DELETE_ROW;

Line: 703

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

Line: 710

  X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);

Line: 714

  X_LAST_UPDATED_BY NUMBER;

Line: 715

  X_LAST_UPDATE_LOGIN NUMBER;

Line: 780

    X_CURRENT_LAST_UPDATE_DATE,
    X_LINE_ITEM_ID_QUERY,
    X_CURRENT_OVN);

Line: 787

    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );

Line: 829

       INSERT_ROW (
         X_ATTRIBUTE_ID,
         X_APPLICATION_ID,
         X_QUERY_STRING_OUT,
         to_number(X_USE_COUNT),
         X_IS_STATIC,
         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_USER_EDITABLE,
         X_VALUE_SET_ID,
         1);

Line: 852

                                     X_LAST_UPDATE_DATE         => X_LAST_UPDATE_DATE,
                                     X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE);

Line: 859

         FORCE_UPDATE_ROW (
           X_USAGES_ROWID,
           X_QUERY_STRING_OUT,
           X_CALCULATED_USE_COUNT,
           X_IS_STATIC,
           X_USER_EDITABLE,
           X_VALUE_SET_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'),
           AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
           X_CURRENT_OVN + 1);

Line: 875

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

Line: 885

           INSERT_ROW (
             X_ATTRIBUTE_ID,
             X_APPLICATION_ID,
             X_QUERY_STRING_OUT,
             X_CALCULATED_USE_COUNT,
             X_IS_STATIC,
             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_USER_EDITABLE,
             X_VALUE_SET_ID,
             X_CURRENT_OVN + 1);

Line: 923

    ,X_LAST_UPDATE_DATE       in varchar2
    ,X_UPLOAD_MODE            in varchar2
    ,X_CUSTOM_MODE            in varchar2
    ) as
  begin
    if X_UPLOAD_MODE = 'NLS' then
      null;

Line: 940

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