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.CSM_MOBILE_QUERY_PKG SQL Statements

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

Line: 14


/* Select all inq records */
CURSOR c_query( b_user_name VARCHAR2, b_tranid NUMBER)
IS
SELECT *
  FROM  CSM_QUERY_INQ
  WHERE tranid$$ = b_tranid
  AND   clid$$cs = b_user_name;

Line: 24


SELECT *
  FROM  CSM_QUERY_VARIABLES_INQ
  WHERE tranid$$ = b_tranid
  AND   clid$$cs = b_user_name
  AND   QUERY_ID = b_query_id;

Line: 31


  This procedure is called Upload to insert a Saved Queries
  That is created in the Mobile Client
***/
PROCEDURE INSERT_QUERY
( p_QUERY_ID             IN  NUMBER,
  p_QUERY_NAME           IN  VARCHAR2,
  P_QUERY_DESC           IN  VARCHAR2,
  P_QUERY_TYPE           IN  VARCHAR2,
  p_QUERY_TEXT1          IN  VARCHAR2,
  p_QUERY_TEXT2          IN  VARCHAR2,
  p_LEVEL_ID             IN  NUMBER,
  p_LEVEL_VALUE          IN  NUMBER,
  p_PARENT_QUERY_ID      IN  NUMBER,
  p_SAVED_QUERY          IN  VARCHAR2,
  p_QUERY_OUTPUT_FORMAT  IN  VARCHAR2,
  p_MIME_TYPE            IN  VARCHAR2,
  p_WORK_FLOW            IN  VARCHAR2,
  p_RETENTION_POLICY     IN  VARCHAR2,
  p_RETENTION_DAYS       IN  NUMBER,
  p_TEMPLATE             IN  VARCHAR2,
  p_TEMPLATE_FILE        IN  VARCHAR2,
  p_EXECUTION_MODE       IN  VARCHAR2,
  p_VARIABLE_NAME        IN  CSM_VARCHAR_LIST,
  p_VARIABLE_TYPE        IN  CSM_VARCHAR_LIST,
  p_VARIABLE_VALUE_CHAR  IN  CSM_VARCHAR_LIST,
  p_VARIABLE_VALUE_DATE  IN  CSM_DATE_LIST,
  p_HIDDEN_FLAG          IN  CSM_VARCHAR_LIST,
  p_DEFAULT_FLAG         IN  CSM_VARCHAR_LIST,
  x_return_status        OUT NOCOPY VARCHAR2,
  x_error_message        OUT NOCOPY VARCHAR2
)
AS
CURSOR c_get_query_id --get query_id
IS
SELECT CSM_QUERY_B_S.NEXTVAL
FROM DUAL;

Line: 70


SELECT LEVEL_ID, LEVEL_VALUE
FROM   CSM_QUERY_B
WHERE  QUERY_ID = c_parent_query_id;

Line: 85


      ( 'Entering INSERT_QUERY for Query Name :' || p_QUERY_NAME ,
        FND_LOG.LEVEL_ERROR);

Line: 108


          ( 'Exception occurred in VALIDATE_ACCESS for Query Name ' || p_QUERY_NAME ||  SQLERRM, 'INSERT_QUERY',
            FND_LOG.LEVEL_EXCEPTION);

Line: 123


          ( 'Exception occurred in VALIDATE_QUERY for Query Name ' || p_QUERY_NAME ||  SQLERRM, 'INSERT_QUERY',
            FND_LOG.LEVEL_EXCEPTION);

Line: 139


          ( 'Exception occurred in VALIDATE_WORLFLOW for Query Name ' || p_QUERY_NAME ||  SQLERRM, 'INSERT_QUERY',
            FND_LOG.LEVEL_EXCEPTION);

Line: 151


          ( 'Variable Name and Variable Type Mismatch: Leaving INSERT_QUERY for Query Name :' || p_QUERY_NAME ,
          FND_LOG.LEVEL_ERROR);

Line: 155


          x_error_message := 'Query insert failed for Query Name: ' || p_QUERY_NAME ||
                             ' because of Variable Name and Variable Type Mismatch' ;

Line: 164


          ( 'Variable Name and Variable Type Mismatch: Leaving INSERT_QUERY for Query Name :' || p_QUERY_NAME ,
          FND_LOG.LEVEL_ERROR);

Line: 168


          x_error_message := 'Query insert failed for Query Name: ' || p_QUERY_NAME ||
                             ' because of Variable Name and Variable Type Mismatch' ;

Line: 184


    INSERT INTO CSM_QUERY_B ( QUERY_ID,             QUERY_NAME,       QUERY_TYPE,
                              QUERY_TEXT1,          QUERY_TEXT2,      LEVEL_ID,
                              LEVEL_VALUE,          PARENT_QUERY_ID,  SAVED_QUERY,
                              QUERY_OUTPUT_FORMAT,  MIME_TYPE,        WORK_FLOW,
                              RETENTION_POLICY,     RETENTION_DAYS,   TEMPLATE,
                              TEMPLATE_FILE,        EXECUTION_MODE,   CREATION_DATE,
                              CREATED_BY,           LAST_UPDATE_DATE, LAST_UPDATED_BY,
                              LAST_UPDATE_LOGIN,    SEEDED  )
    VALUES                 (  l_query_id,             p_QUERY_NAME,       p_QUERY_TYPE,
                              p_QUERY_TEXT1,          p_QUERY_TEXT2,      p_LEVEL_ID,
                              p_LEVEL_VALUE,          p_PARENT_QUERY_ID,  p_SAVED_QUERY,
                              p_QUERY_OUTPUT_FORMAT,  p_MIME_TYPE,        p_WORK_FLOW,
                              p_RETENTION_POLICY,     p_RETENTION_DAYS,   p_TEMPLATE,
                              p_TEMPLATE_FILE,        p_EXECUTION_MODE,   sysdate,
                              p_LEVEL_VALUE,     sysdate,            p_LEVEL_VALUE,
                              fnd_global.login_id,   'N'  );

Line: 202


    INSERT INTO CSM_QUERY_TL ( QUERY_ID,             DESCRIPTION,      LANGUAGE,
                              CREATION_DATE,        CREATED_BY,
                              LAST_UPDATE_DATE,     LAST_UPDATED_BY,  LAST_UPDATE_LOGIN )
    VALUES                 (  l_query_id,             P_QUERY_DESC,       l_language,
                              sysdate,                p_LEVEL_VALUE,
                              sysdate,                p_LEVEL_VALUE, fnd_global.login_id);

Line: 214


        INSERT INTO CSM_QUERY_VARIABLES_B ( QUERY_ID,         VARIABLE_ID,          VARIABLE_NAME,
                                          VARIABLE_TYPE,    VARIABLE_VALUE_CHAR,  VARIABLE_VALUE_DATE,
                                          HIDDEN,           DEFAULT_FLAG,         CREATION_DATE,
                                          CREATED_BY,        LAST_UPDATE_DATE,    LAST_UPDATED_BY,
                                          LAST_UPDATE_LOGIN   )
        VALUES                            ( l_query_id,         i,          p_VARIABLE_NAME(i),
                                          p_VARIABLE_TYPE(i), p_VARIABLE_VALUE_CHAR(i),  p_VARIABLE_VALUE_DATE(i),
                                          NVL(p_HIDDEN_FLAG(i),'N'),   NVL(p_DEFAULT_FLAG(i),'N'),         sysdate,
                                          p_LEVEL_VALUE,                  sysdate,                   p_LEVEL_VALUE,
                                          fnd_global.login_id        );

Line: 228


    CSM_UTIL_PKG.LOG( 'Leaving INSERT_QUERY for Query Name :' || p_QUERY_NAME ,
      FND_LOG.LEVEL_ERROR);

Line: 230


    x_error_message := 'Query insert is Successful for Query Name: ' || p_QUERY_NAME ;

Line: 236


  ( 'Exception occurred in INSERT_QUERY for Query Name ' || p_QUERY_NAME ||  SQLERRM, 'INSERT_QUERY',
    FND_LOG.LEVEL_EXCEPTION);

Line: 239


  x_error_message := 'Query insert failed for Query Name: ' || p_QUERY_NAME ||
                    ' : ' || SUBSTR(SQLERRM,1,3000);

Line: 242


END INSERT_QUERY;

Line: 245


  This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
***/
PROCEDURE APPLY_INSERT
         (
           p_record        IN c_query%ROWTYPE,
           p_error_msg     out nocopy    VARCHAR2,
           x_return_status IN out nocopy VARCHAR2
         )
IS
CURSOR c_check_query(c_QUERY_ID NUMBER)
IS
SELECT QUERY_NAME, QUERY_TYPE, QUERY_TEXT1, QUERY_TEXT2,
       QUERY_OUTPUT_FORMAT, MIME_TYPE, WORK_FLOW, RETENTION_POLICY,
       RETENTION_DAYS, TEMPLATE, TEMPLATE_FILE, EXECUTION_MODE
FROM   CSM_QUERY_B
WHERE  QUERY_ID =c_QUERY_ID
AND    NVL(DELETE_FLAG,'N') = 'N';

Line: 265


SELECT
    vb.VARIABLE_NAME,
    vb.VARIABLE_TYPE,
    vb.VARIABLE_VALUE_CHAR,
    vb.VARIABLE_VALUE_DATE,
    vb.HIDDEN,
    vb.DEFAULT_FLAG
FROM  CSM_QUERY_VARIABLES_INQ     vb
WHERE vb.QUERY_ID = c_QUERY_ID;

Line: 277


SELECT USER_ID
FROM   ASG_USER
WHERE  USER_NAME =c_user_name;

Line: 310


  CSM_UTIL_PKG.LOG('Entering CSM_MOBILE_QUERY_PKG.APPLY_INSERT for Query ID ' || p_record.QUERY_ID ,
                         'CSM_MOBILE_QUERY_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);

Line: 363


INSERT_QUERY
( p_QUERY_ID             => l_QUERY_ID,
  p_QUERY_NAME           => l_QUERY_NAME,
  P_QUERY_DESC           => p_record.DESCRIPTION,
  P_QUERY_TYPE           => l_QUERY_TYPE,
  p_QUERY_TEXT1          => l_QUERY_TEXT1,
  p_QUERY_TEXT2          => l_QUERY_TEXT2,
  p_LEVEL_ID             => l_LEVEL_ID,
  p_LEVEL_VALUE          => l_LEVEL_VALUE,
  p_PARENT_QUERY_ID      => l_PARENT_QUERY_ID,
  p_SAVED_QUERY          => l_SAVED_QUERY,
  p_QUERY_OUTPUT_FORMAT  => l_QUERY_OUTPUT_FORMAT,
  p_MIME_TYPE            => l_MIME_TYPE,
  p_WORK_FLOW            => l_WORK_FLOW,
  p_RETENTION_POLICY     => l_RETENTION_POLICY,
  p_RETENTION_DAYS       => l_RETENTION_DAYS,
  p_TEMPLATE             => l_TEMPLATE,
  p_TEMPLATE_FILE        => l_TEMPLATE_FILE,
  p_EXECUTION_MODE       => l_EXECUTION_MODE,
  p_VARIABLE_NAME        => l_variable_name_lst,
  p_VARIABLE_TYPE        => l_var_type_lst,
  p_VARIABLE_VALUE_CHAR  => l_var_value_char_lst,
  p_VARIABLE_VALUE_DATE  => l_var_value_date_lst,
  p_HIDDEN_FLAG          => l_var_hidden_lst,
  p_DEFAULT_FLAG         => l_var_default_lst,
  x_return_status        => x_return_status,
  x_error_message        => p_error_msg);

Line: 399


  CSM_UTIL_PKG.LOG('Leaving CSM_MOBILE_QUERY_PKG.APPLY_INSERT for Query ID : ' || p_record.QUERY_ID ,
                         'CSM_MOBILE_QUERY_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);

Line: 407


     CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT: ' || SUBSTR(SQLERRM,1,3000)
               || ' for Query ID ' || p_record.QUERY_ID ,'CSM_MOBILE_QUERY_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION);

Line: 410


END APPLY_INSERT;

Line: 413


  This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
***/
PROCEDURE APPLY_DELETE
         (
           p_record        IN c_query%ROWTYPE,
           p_error_msg     out nocopy    VARCHAR2,
           x_return_status IN out nocopy VARCHAR2
         )
IS
CURSOR c_check_query(c_QUERY_ID NUMBER, c_USER_ID NUMBER)
IS
SELECT NVL(DELETE_FLAG,'N')
FROM   CSM_QUERY_B
WHERE  QUERY_ID = c_QUERY_ID
AND    LEVEL_ID = 10004 --user level
AND    LEVEL_VALUE = c_USER_ID
AND    SAVED_QUERY = 'Y'
AND    NVL(DELETE_FLAG,'N') = 'N';

Line: 435


SELECT USER_ID
FROM   ASG_USER
WHERE  USER_NAME =c_user_name;

Line: 439


l_DELETE_FLAG VARCHAR2(1) := NULL;

Line: 444


  CSM_UTIL_PKG.LOG('Entering CSM_MOBILE_QUERY_PKG.APPLY_DELETE for Query ID ' || p_record.QUERY_ID ,
                         'CSM_MOBILE_QUERY_PKG.APPLY_DELETE',FND_LOG.LEVEL_PROCEDURE);

Line: 459


  FETCH c_check_query INTO l_DELETE_FLAG;

Line: 462


  IF l_DELETE_FLAG IS NULL THEN
    p_error_msg     := 'User may not have permission to Delete the Query or The Query is not available for Delete. Query ID : '|| l_query_id;

Line: 468


  UPDATE    CSM_QUERY_B
  SET       DELETE_FLAG       = 'Y',
            LAST_UPDATE_DATE  = sysdate,
            LAST_UPDATED_BY   = fnd_global.user_id,
            LAST_UPDATE_LOGIN = fnd_global.login_id
  WHERE     QUERY_ID = l_QUERY_ID;

Line: 475


  CSM_UTIL_PKG.LOG('Leaving CSM_MOBILE_QUERY_PKG.APPLY_DELETE for Query ID : ' || p_record.QUERY_ID ,
                         'CSM_MOBILE_QUERY_PKG.APPLY_DELETE',FND_LOG.LEVEL_PROCEDURE);

Line: 477


  p_error_msg     := 'Query Delete is Successful for Query ID : '|| l_query_id;

Line: 482


     p_error_msg := 'Exeception in Query Delete for  Query Id : '|| l_query_id ||' with Error ' ||SUBSTR(SQLERRM,1,3000);

Line: 483


     CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_DELETE: ' || SUBSTR(SQLERRM,1,3000)
               || ' for Query ID ' || p_record.QUERY_ID ,'CSM_MOBILE_QUERY_PKG.APPLY_DELETE',FND_LOG.LEVEL_EXCEPTION);

Line: 486


END APPLY_DELETE;

Line: 509


    APPLY_INSERT
      (
        p_record,
        p_error_msg,
        x_return_status
      );

Line: 517


    APPLY_DELETE
      (
        p_record,
        p_error_msg,
        x_return_status
      );

Line: 524


  ELSE --Delete and update is not supported for this PI
    -- invalid dml type
      CSM_UTIL_PKG.LOG
        ( 'Invalid DML type: ' || p_record.dmltype$$ || ' is not supported for this entity'
      || ' for Query Instance ID ' || p_record.QUERY_ID ,'CSM_MOBILE_QUERY_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);

Line: 558


  a fast sync. This procedure will insert the data that came from mobile into the backend tables using
  public APIs.
***/
PROCEDURE APPLY_CLIENT_CHANGES
         (
           p_user_name     IN VARCHAR2,
           p_tranid        IN NUMBER,
           p_debug_level   IN NUMBER,
           x_return_status IN out nocopy VARCHAR2
         ) IS

  l_process_status VARCHAR2(1);

Line: 591


      /*** Yes -> delete record from inqueue ***/
      CSM_UTIL_PKG.DELETE_RECORD
        (
          p_user_name,
          p_tranid,
          r_query_rec.seqno$$,
          r_query_rec.QUERY_ID,
          g_object_name,
          g_pub_name,
          l_error_msg,
          l_process_status
        );

Line: 605


            CSM_UTIL_PKG.DELETE_RECORD
            (
              p_user_name,
              p_tranid,
              r_query_var_inq_rec.seqno$$,
              r_query_var_inq_rec.GEN_PK,
              g_object_name,
              g_query_var_pub_name,
              l_error_msg,
              l_process_status
            );

Line: 619


      /*** was delete successful? ***/
      IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
       /*** no -> rollback ***/
          CSM_UTIL_PKG.LOG
          ( 'Deleting from inqueue failed, rolling back to savepoint'
      || ' for Instance ID ' || r_query_rec.QUERY_ID ,'CSM_MOBILE_QUERY_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here