APPS.AME_RULE_USAGES_API2 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: 28

   select ACA.APPLICATION_ID
   from   AME_CALLING_APPS ACA,
          FND_APPLICATION_VL FA
   where  FA.APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
     and  nvl(ACA.TRANSACTION_TYPE_ID,'NULL') = nvl(X_TRANSACTION_TYPE_ID,'NULL')
     and  FA.APPLICATION_ID = ACA.FND_APPLICATION_ID
     and sysdate between ACA.START_DATE
       and nvl(ACA.END_DATE - (1/86400), sysdate);

Line: 41

  select RULE_ID
    from AME_RULES
   where RULE_KEY = X_RULE_KEY;

Line: 50

  select 'FOUND'
    from AME_RULE_USAGES
   where RULE_ID  = X_RULE_ID
     and ITEM_ID  = X_ITEM_ID;

Line: 100

   select ACA.APPLICATION_ID
   from   AME_CALLING_APPS ACA,
          FND_APPLICATION_VL FA
   where  FA.APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
     and  nvl(ACA.TRANSACTION_TYPE_ID,'NULL') = nvl(X_TRANSACTION_TYPE_ID,'NULL')
     and  FA.APPLICATION_ID = ACA.FND_APPLICATION_ID
     and ((ACA.START_DATE - (1/86400)) <= sysdate)
     and (((ACA.END_DATE  - (1/86400)) >= sysdate)
      or (ACA.END_DATE is null));

Line: 115

  select COUNT(*)
    from AME_RULE_USAGES
   where RULE_ID = X_RULE_ID
     and ITEM_ID = X_ITEM_ID;

Line: 144

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

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

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

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

    select AME_CONDITIONS.ATTRIBUTE_ID
    from  AME_CONDITIONS,
      AME_CONDITION_USAGES
    where
      AME_CONDITIONS.CONDITION_TYPE in (AME_UTIL.ORDINARYCONDITIONTYPE,
                                        AME_UTIL.EXCEPTIONCONDITIONTYPE) and
      AME_CONDITION_USAGES.RULE_ID = X_RULE_ID and
      AME_CONDITION_USAGES.CONDITION_ID = AME_CONDITIONS.CONDITION_ID and
      (AME_CONDITIONS.START_DATE <= sysdate and
        (AME_CONDITIONS.END_DATE is null or sysdate < AME_CONDITIONS.END_DATE)) 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))))
      union
      select AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID
      from AME_MANDATORY_ATTRIBUTES,
       AME_ACTION_USAGES,
       AME_ACTIONS
      where
       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 = X_RULE_ID and
       (AME_MANDATORY_ATTRIBUTES.START_DATE <= sysdate and
       (AME_MANDATORY_ATTRIBUTES.END_DATE is null or sysdate < AME_MANDATORY_ATTRIBUTES.END_DATE)) 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)))) and
        (AME_ACTIONS.START_DATE <= sysdate and
        (AME_ACTIONS.END_DATE is null or sysdate < AME_ACTIONS.END_DATE))
      union
      select AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID
      from AME_MANDATORY_ATTRIBUTES,
       AME_RULES,
       AME_ACTIONS
      where
       AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID = AME_ACTIONS.ACTION_TYPE_ID and
       AME_ACTIONS.ACTION_ID = AME_RULES.ACTION_ID and
       AME_RULES.ACTION_ID is not null and
       AME_RULES.RULE_ID = X_RULE_ID and
       (AME_MANDATORY_ATTRIBUTES.START_DATE <= sysdate and
       (AME_MANDATORY_ATTRIBUTES.END_DATE is null or sysdate < AME_MANDATORY_ATTRIBUTES.END_DATE)) 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
        (AME_ACTIONS.START_DATE <= sysdate and
        (AME_ACTIONS.END_DATE is null or sysdate < AME_ACTIONS.END_DATE));

Line: 304

    update AME_ATTRIBUTE_USAGES
    set  USE_COUNT = X_USE_COUNT
    where
     ATTRIBUTE_ID = ATTRIBUTE_REC.ATTRIBUTE_ID and
     APPLICATION_ID = X_APPLICATION_ID and
     sysdate between START_DATE and
               nvl(END_DATE - (1/86400), sysdate);

Line: 319

      select aa.action_id,
             aa.action_type_id,
             aat.name,
             aa.parameter
        from ame_rule_usages aru,
             ame_action_usages aau,
             ame_actions aa,
             ame_action_types aat
       where aru.rule_id = x_rule_id
         and aru.item_id = x_application_id
         and sysdate between aat.start_date and nvl(aat.end_date,sysdate)
         and sysdate between aa.start_date and nvl(aa.end_date,sysdate)
         and (sysdate between aru.start_date and nvl(aru.end_date,sysdate) or
              aru.start_date > sysdate and nvl(aru.end_date,aru.start_date + (1/86400)) < aru.start_date)
         and (sysdate between aau.start_date and nvl(aau.end_date,sysdate) or
              aau.start_date > sysdate and nvl(aau.end_date,aau.start_date + (1/86400)) < aau.start_date)
         and aru.rule_id = aau.rule_id
         and aau.action_id = aa.action_id
         and aa.action_type_id = aat.action_type_id;

Line: 339

      select null
        from ame_action_types
       where sysdate between start_date and nvl(end_date,sysdate)
         and action_type_id = c_action_type_id
         and name in ('pre-chain-of-authority approvals'
                     ,'post-chain-of-authority approvals'
                     ,'approval-group chain of authority');

Line: 388

procedure INSERT_ROW (
 X_ITEM_ID                         in NUMBER,
 X_RULE_ID                         in NUMBER,
 X_APPROVER_CATEGORY               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_RULE_USAGES
  (
   ITEM_ID,
   RULE_ID,
   APPROVER_CATEGORY,
   CREATED_BY,
   CREATION_DATE,
   LAST_UPDATED_BY,
   LAST_UPDATE_DATE,
   LAST_UPDATE_LOGIN,
   START_DATE,
   END_DATE,
   OBJECT_VERSION_NUMBER
  ) values (
   X_ITEM_ID,
   X_RULE_ID,
   X_APPROVER_CATEGORY,
   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: 429

end INSERT_ROW;

Line: 431

procedure DELETE_ROW (
  X_ITEM_ID in NUMBER,
  X_RULE_ID in NUMBER
) is
begin
  delete from AME_RULE_USAGES
  where ITEM_ID = X_ITEM_ID
    and RULE_ID = X_RULE_ID;

Line: 442

end DELETE_ROW;

Line: 451

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

Line: 457

  X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);

Line: 459

  X_LAST_UPDATED_BY NUMBER;

Line: 460

  X_LAST_UPDATE_LOGIN NUMBER;

Line: 468

    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );

Line: 484

     INSERT_ROW (
       X_ITEM_ID,
       X_RULE_ID_LOAD,
       X_APPROVER_CATEGORY,
       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: 513

     INSERT_ROW (
       X_ITEM_ID,
       X_RULE_ID,
       X_APPROVER_CATEGORY,
       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);