eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AME_APPROVER_TYPES_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_APPROVER_TYPE
  (
    X_ORIG_SYSTEM            in VARCHAR2
  ) is
   select ROWID, APPROVER_TYPE_ID,
          LAST_UPDATED_BY,
          to_char(LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
          nvl(OBJECT_VERSION_NUMBER,1)
   from   AME_APPROVER_TYPES
   where  ORIG_SYSTEM = X_ORIG_SYSTEM
	    and sysdate between START_DATE
			 and nvl(END_DATE  - (1/86400), sysdate);

Line: 44


X_CURRENT_LAST_UPDATE_DATE,
X_CURRENT_OVN;

Line: 51


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


end DO_UPDATE_INSERT;

Line: 67


procedure INSERT_ROW (
 X_APPROVER_TYPE_ID                in NUMBER,
 X_ORIG_SYSTEM                     in VARCHAR2,
 X_QUERY_VARIABLE_1_LABEL          in VARCHAR2,
 X_QUERY_VARIABLE_2_LABEL          in VARCHAR2,
 X_QUERY_VARIABLE_3_LABEL          in VARCHAR2,
 X_QUERY_VARIABLE_4_LABEL          in VARCHAR2,
 X_QUERY_VARIABLE_5_LABEL          in VARCHAR2,
 X_VARIABLE_1_LOV_QUERY            in VARCHAR2,
 X_VARIABLE_2_LOV_QUERY            in VARCHAR2,
 X_VARIABLE_3_LOV_QUERY            in VARCHAR2,
 X_VARIABLE_4_LOV_QUERY            in VARCHAR2,
 X_VARIABLE_5_LOV_QUERY            in VARCHAR2,
 X_QUERY_PROCEDURE                 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_APPROVER_TYPES
  (
   APPROVER_TYPE_ID,
   ORIG_SYSTEM,
   QUERY_VARIABLE_1_LABEL,
   QUERY_VARIABLE_2_LABEL,
   QUERY_VARIABLE_3_LABEL,
   QUERY_VARIABLE_4_LABEL,
   QUERY_VARIABLE_5_LABEL,
   VARIABLE_1_LOV_QUERY,
   VARIABLE_2_LOV_QUERY,
   VARIABLE_3_LOV_QUERY,
   VARIABLE_4_LOV_QUERY,
   VARIABLE_5_LOV_QUERY,
   QUERY_PROCEDURE,
   CREATED_BY,
   CREATION_DATE,
   LAST_UPDATED_BY,
   LAST_UPDATE_DATE,
   LAST_UPDATE_LOGIN,
   START_DATE,
   END_DATE,
   OBJECT_VERSION_NUMBER
  ) values (
   X_APPROVER_TYPE_ID,
   X_ORIG_SYSTEM,
   X_QUERY_VARIABLE_1_LABEL,
   X_QUERY_VARIABLE_2_LABEL,
   X_QUERY_VARIABLE_3_LABEL,
   X_QUERY_VARIABLE_4_LABEL,
   X_QUERY_VARIABLE_5_LABEL,
   X_VARIABLE_1_LOV_QUERY,
   X_VARIABLE_2_LOV_QUERY,
   X_VARIABLE_3_LOV_QUERY,
   X_VARIABLE_4_LOV_QUERY,
   X_VARIABLE_5_LOV_QUERY,
   X_QUERY_PROCEDURE,
   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: 136


end INSERT_ROW;

Line: 138


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

Line: 146


end UPDATE_ROW;

Line: 148


procedure FORCE_UPDATE_ROW (
  X_ROWID                      in VARCHAR2,
  X_QUERY_VARIABLE_1_LABEL     in VARCHAR2,
  X_QUERY_VARIABLE_2_LABEL     in VARCHAR2,
  X_QUERY_VARIABLE_3_LABEL     in VARCHAR2,
  X_QUERY_VARIABLE_4_LABEL     in VARCHAR2,
  X_QUERY_VARIABLE_5_LABEL     in VARCHAR2,
  X_VARIABLE_1_LOV_QUERY       in VARCHAR2,
  X_VARIABLE_2_LOV_QUERY       in VARCHAR2,
  X_VARIABLE_3_LOV_QUERY       in VARCHAR2,
  X_VARIABLE_4_LOV_QUERY       in VARCHAR2,
  X_VARIABLE_5_LOV_QUERY       in VARCHAR2,
  X_QUERY_PROCEDURE            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_APPROVER_TYPES
     set QUERY_VARIABLE_1_LABEL = X_QUERY_VARIABLE_1_LABEL,
         QUERY_VARIABLE_2_LABEL = X_QUERY_VARIABLE_2_LABEL,
         QUERY_VARIABLE_3_LABEL = X_QUERY_VARIABLE_3_LABEL,
         QUERY_VARIABLE_4_LABEL = X_QUERY_VARIABLE_4_LABEL,
         QUERY_VARIABLE_5_LABEL = X_QUERY_VARIABLE_5_LABEL,
         VARIABLE_1_LOV_QUERY = X_QUERY_VARIABLE_1_LABEL,
         VARIABLE_2_LOV_QUERY = X_QUERY_VARIABLE_2_LABEL,
         VARIABLE_3_LOV_QUERY = X_QUERY_VARIABLE_3_LABEL,
         VARIABLE_4_LOV_QUERY = X_QUERY_VARIABLE_4_LABEL,
         VARIABLE_5_LOV_QUERY = X_QUERY_VARIABLE_5_LABEL,
         QUERY_PROCEDURE = X_QUERY_PROCEDURE,
         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: 192


end FORCE_UPDATE_ROW;

Line: 194


procedure DELETE_ROW (
  X_APPROVER_TYPE_ID in NUMBER
) is
begin
  delete from AME_APPROVER_TYPES
  where APPROVER_TYPE_ID = X_APPROVER_TYPE_ID;

Line: 204


end DELETE_ROW;

Line: 219


          X_LAST_UPDATE_DATE in VARCHAR2,
          X_CUSTOM_MODE      in VARCHAR2
)
is
  X_APPROVER_TYPE_ROWID ROWID;

Line: 226


  X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);

Line: 228


  X_LAST_UPDATED_BY NUMBER;

Line: 229


  X_LAST_UPDATE_LOGIN NUMBER;

Line: 239


    X_CURRENT_LAST_UPDATE_DATE,
    X_CURRENT_OVN
  );

Line: 246


    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );

Line: 251


       select nvl(max(APPROVER_TYPE_ID)+1, 0)
         into X_APPROVER_TYPE_ID
         from AME_APPROVER_TYPES;

Line: 254


       INSERT_ROW (
         X_APPROVER_TYPE_ID,
         X_ORIG_SYSTEM,
         X_QUERY_VARIABLE_1_LABEL,
         X_QUERY_VARIABLE_2_LABEL,
         X_QUERY_VARIABLE_3_LABEL,
         X_QUERY_VARIABLE_4_LABEL,
         X_QUERY_VARIABLE_5_LABEL,
         X_VARIABLE_1_LOV_QUERY,
         X_VARIABLE_2_LOV_QUERY,
         X_VARIABLE_3_LOV_QUERY,
         X_VARIABLE_4_LOV_QUERY,
         X_VARIABLE_5_LOV_QUERY,
         X_QUERY_PROCEDURE,
         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: 280


      FORCE_UPDATE_ROW (
        X_APPROVER_TYPE_ROWID,
        X_QUERY_VARIABLE_1_LABEL,
        X_QUERY_VARIABLE_2_LABEL,
        X_QUERY_VARIABLE_3_LABEL,
        X_QUERY_VARIABLE_4_LABEL,
        X_QUERY_VARIABLE_5_LABEL,
        X_VARIABLE_1_LOV_QUERY,
        X_VARIABLE_2_LOV_QUERY,
        X_VARIABLE_3_LOV_QUERY,
        X_VARIABLE_4_LOV_QUERY,
        X_VARIABLE_5_LOV_QUERY,
        X_QUERY_PROCEDURE,
        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: 302


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

Line: 310


          INSERT_ROW (
            X_APPROVER_TYPE_ID,
            X_ORIG_SYSTEM,
            X_QUERY_VARIABLE_1_LABEL,
            X_QUERY_VARIABLE_2_LABEL,
            X_QUERY_VARIABLE_3_LABEL,
            X_QUERY_VARIABLE_4_LABEL,
            X_QUERY_VARIABLE_5_LABEL,
            X_VARIABLE_1_LOV_QUERY,
            X_VARIABLE_2_LOV_QUERY,
            X_VARIABLE_3_LOV_QUERY,
            X_VARIABLE_4_LOV_QUERY,
            X_VARIABLE_5_LOV_QUERY,
            X_QUERY_PROCEDURE,
            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);