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_QUERY_PUB SQL Statements

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

Line: 19


  This procedure is called to insert a Query
***/
PROCEDURE INSERT_QUERY
(
  p_API_VERSION		  	   IN  NUMBER,
  p_INIT_MSG_LIST	  	   IN  VARCHAR2 	:= FND_API.G_FALSE,
  p_COMMIT    		  	   IN  VARCHAR2 	:= FND_API.G_FALSE,
  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_PROCEDURE            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,
  p_EMAIL_ENABLED        IN  VARCHAR2,
  p_RESTRICTED_FLAG      IN  VARCHAR2,
  p_DISABLED_FLAG        IN  VARCHAR2,
  x_return_status        OUT NOCOPY VARCHAR2,
  x_error_message        OUT NOCOPY VARCHAR2
)
AS
BEGIN
     CSM_UTIL_PKG.LOG
      ( 'Entering INSERT_QUERY for Query Name :' || p_QUERY_NAME ,
        FND_LOG.LEVEL_ERROR);

Line: 63


     SAVEPOINT CSM_INSERT_QUERY_PUB;

Line: 65


       CSM_QUERY_PKG.INSERT_QUERY
    ( p_QUERY_ID             => p_QUERY_ID,
      p_QUERY_NAME           => p_QUERY_NAME,
      P_QUERY_DESC           => p_QUERY_DESC,
      P_QUERY_TYPE           => p_QUERY_TYPE,
      p_QUERY_TEXT1          => p_QUERY_TEXT1,
      p_QUERY_TEXT2          => p_QUERY_TEXT2,
      p_LEVEL_ID             => p_LEVEL_ID,
      p_LEVEL_VALUE          => p_LEVEL_VALUE,
      p_PARENT_QUERY_ID      => p_PARENT_QUERY_ID,
      p_SAVED_QUERY          => p_SAVED_QUERY,
      p_QUERY_OUTPUT_FORMAT  => p_QUERY_OUTPUT_FORMAT,
      p_MIME_TYPE            => p_MIME_TYPE,
      p_WORK_FLOW            => p_WORK_FLOW,
      p_PROCEDURE            => p_PROCEDURE,
      p_RETENTION_POLICY     => p_RETENTION_POLICY,
      p_RETENTION_DAYS       => p_RETENTION_DAYS,
      p_TEMPLATE             => p_TEMPLATE,
      p_TEMPLATE_FILE        => p_TEMPLATE_FILE,
      p_EXECUTION_MODE       => p_EXECUTION_MODE,
      p_VARIABLE_NAME        => p_VARIABLE_NAME,
      p_VARIABLE_TYPE        => p_VARIABLE_TYPE,
      p_VARIABLE_VALUE_CHAR  => p_VARIABLE_VALUE_CHAR,
      p_VARIABLE_VALUE_DATE  => p_VARIABLE_VALUE_DATE,
      p_HIDDEN_FLAG          => p_HIDDEN_FLAG,
      p_DEFAULT_FLAG         => p_DEFAULT_FLAG,
      p_EMAIL_ENABLED	     => p_EMAIL_ENABLED,
      p_RESTRICTED_FLAG      => p_RESTRICTED_FLAG,
      p_DISABLED_FLAG        => p_DISABLED_FLAG,
      x_return_status        => x_return_status,
      x_error_message        => x_error_message);

Line: 100


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

Line: 103


          x_error_message := 'Query Insert failed : ' || x_error_message;

Line: 104


          ROLLBACK TO CSM_INSERT_QUERY_PUB;

Line: 111


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

Line: 118


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

Line: 121


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

Line: 123


  ROLLBACK TO CSM_INSERT_QUERY_PUB;

Line: 124


END INSERT_QUERY;

Line: 128


  This procedure is called  to Update a Query

***/
PROCEDURE UPDATE_QUERY
(
  p_API_VERSION		  	   IN  NUMBER,
  p_INIT_MSG_LIST	  	   IN  VARCHAR2 	:= FND_API.G_FALSE,
  p_COMMIT    		  	   IN  VARCHAR2 	:= FND_API.G_FALSE,
  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_PROCEDURE            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,
  p_EMAIL_ENABLED        IN  VARCHAR2,
  p_RESTRICTED_FLAG      IN  VARCHAR2,
  p_DISABLED_FLAG        IN  VARCHAR2,
  x_return_status        OUT NOCOPY VARCHAR2,
  x_error_message        OUT NOCOPY VARCHAR2
)
AS
BEGIN
    CSM_UTIL_PKG.LOG
    ( 'Entering UPDATE_QUERY for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ,
      FND_LOG.LEVEL_ERROR);

Line: 173


     SAVEPOINT CSM_UPDATE_QUERY_PUB;

Line: 175


    CSM_QUERY_PKG.UPDATE_QUERY
    ( p_QUERY_ID             => p_QUERY_ID,
      p_QUERY_NAME           => p_QUERY_NAME,
      P_QUERY_DESC           => p_QUERY_DESC,
      P_QUERY_TYPE           => p_QUERY_TYPE,
      p_QUERY_TEXT1          => p_QUERY_TEXT1,
      p_QUERY_TEXT2          => p_QUERY_TEXT2,
      p_LEVEL_ID             => p_LEVEL_ID,
      p_LEVEL_VALUE          => p_LEVEL_VALUE,
      p_PARENT_QUERY_ID      => p_PARENT_QUERY_ID,
      p_SAVED_QUERY          => p_SAVED_QUERY,
      p_QUERY_OUTPUT_FORMAT  => p_QUERY_OUTPUT_FORMAT,
      p_MIME_TYPE            => p_MIME_TYPE,
      p_WORK_FLOW            => p_WORK_FLOW,
      p_PROCEDURE 	     => p_PROCEDURE,
      p_RETENTION_POLICY     => p_RETENTION_POLICY,
      p_RETENTION_DAYS       => p_RETENTION_DAYS,
      p_TEMPLATE             => p_TEMPLATE,
      p_TEMPLATE_FILE        => p_TEMPLATE_FILE,
      p_EXECUTION_MODE       => p_EXECUTION_MODE,
      p_VARIABLE_NAME        => p_VARIABLE_NAME,
      p_VARIABLE_TYPE        => p_VARIABLE_TYPE,
      p_VARIABLE_VALUE_CHAR  => p_VARIABLE_VALUE_CHAR,
      p_VARIABLE_VALUE_DATE  => p_VARIABLE_VALUE_DATE,
      p_HIDDEN_FLAG          => p_HIDDEN_FLAG,
      p_DEFAULT_FLAG         => p_DEFAULT_FLAG,
      p_EMAIL_ENABLED        => p_EMAIL_ENABLED,
      p_RESTRICTED_FLAG      => p_RESTRICTED_FLAG,
      p_DISABLED_FLAG        => p_DISABLED_FLAG,
      x_return_status        => x_return_status,
      x_error_message        => x_error_message);

Line: 209


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

Line: 212


        x_error_message := 'Query Update failed : ' || x_error_message;

Line: 213


        ROLLBACK TO CSM_UPDATE_QUERY_PUB;

Line: 222


    ( 'Leaving UPDATE_QUERY for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ,
      FND_LOG.LEVEL_ERROR);

Line: 229


  ( 'Exception occurred in UPDATE_QUERY for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ||  SQLERRM, 'UPDATE_QUERY',
    FND_LOG.LEVEL_EXCEPTION);

Line: 232


  x_error_message := 'Query Update Failed for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ||
                    ' : ' || SUBSTR(SQLERRM,1,3000);

Line: 234


  ROLLBACK TO CSM_UPDATE_QUERY_PUB;

Line: 235


END UPDATE_QUERY;

Line: 238


  This procedure is called to Delete a Query
***/
PROCEDURE DELETE_QUERY
(
  p_API_VERSION		  	   IN  NUMBER,
  p_INIT_MSG_LIST	  	   IN  VARCHAR2 	:= FND_API.G_FALSE,
  p_COMMIT    		  	   IN  VARCHAR2 	:= FND_API.G_FALSE,
  p_QUERY_ID             IN  NUMBER,
  x_return_status        OUT NOCOPY VARCHAR2,
  x_error_message        OUT NOCOPY VARCHAR2
)
AS
BEGIN
      CSM_UTIL_PKG.LOG
      ( 'Entering DELETE_QUERY for Query Id and Query Name : ' || p_QUERY_ID  ,
        FND_LOG.LEVEL_ERROR);

Line: 255


      SAVEPOINT CSM_DELETE_QUERY_PUB;

Line: 257


      CSM_QUERY_PKG.DELETE_QUERY
      (
        p_QUERY_ID             => p_QUERY_ID,
        x_return_status        => x_return_status,
        x_error_message        => x_error_message
      );

Line: 266


        ( 'Exception occurred in DELETE QUERY for Query Id ' || p_QUERY_ID ||  SQLERRM, 'DELETE_QUERY',
          FND_LOG.LEVEL_EXCEPTION);

Line: 269


        x_error_message := 'Query Delete failed : ' || x_error_message;

Line: 270


        ROLLBACK TO CSM_DELETE_QUERY_PUB;

Line: 279


    ( 'Leaving DELETE_QUERY for Query Id and Query Name : ' || p_QUERY_ID  ,
      FND_LOG.LEVEL_ERROR);

Line: 282


    x_error_message := 'Delete Query Call Successful for Query id '|| p_QUERY_ID;

Line: 286


  ( 'Exception occurred in DELETE_QUERY for Query Id and Query Name : ' || p_QUERY_ID  ||  SQLERRM, 'DELETE_QUERY',
    FND_LOG.LEVEL_EXCEPTION);

Line: 289


  x_error_message := 'Query Delete Failed for Query Id and Query Name : ' || p_QUERY_ID ;

Line: 290


  ROLLBACK TO CSM_DELETE_QUERY_PUB;

Line: 291


END DELETE_QUERY;

Line: 357


PROCEDURE INSERT_INSTANCE
( p_API_VERSION		  	   IN  NUMBER,
  p_INIT_MSG_LIST	  	   IN  VARCHAR2 	:= FND_API.G_FALSE,
  p_COMMIT    		  	   IN  VARCHAR2 	:= FND_API.G_FALSE,
  p_USER_ID              IN  NUMBER,
  p_QUERY_ID             IN  NUMBER,
  p_INSTANCE_ID          IN  VARCHAR2 DEFAULT NULL,
  p_INSTANCE_NAME        IN  VARCHAR2,
  p_VARIABLE_ID          IN  CSM_INTEGER_LIST,
  p_VARIABLE_VALUE_CHAR  IN  CSM_VARCHAR_LIST,
  p_VARIABLE_VALUE_DATE  IN  CSM_DATE_LIST,
  x_INSTANCE_ID          OUT NOCOPY NUMBER,
  x_return_status        OUT NOCOPY VARCHAR2,
  x_error_message        OUT NOCOPY VARCHAR2
)
AS
BEGIN
    CSM_UTIL_PKG.LOG
      ( 'Entering INSERT_INSTANCE for Query Id : ' || p_QUERY_ID  ,
        FND_LOG.LEVEL_ERROR);

Line: 378


    SAVEPOINT CSM_INSERT_INSTANCE_PUB;

Line: 380


    CSM_QUERY_PKG.INSERT_INSTANCE
    ( p_USER_ID              => p_USER_ID,
      p_QUERY_ID             => p_QUERY_ID,
      p_INSTANCE_ID          => p_INSTANCE_ID,
      p_INSTANCE_NAME        => p_INSTANCE_NAME,
      p_VARIABLE_ID          => p_VARIABLE_ID,
      p_VARIABLE_VALUE_CHAR  => p_VARIABLE_VALUE_CHAR,
      p_VARIABLE_VALUE_DATE  => p_VARIABLE_VALUE_DATE,
      p_commit               => p_COMMIT,
      x_INSTANCE_ID          => x_INSTANCE_ID,
      x_return_status        => x_return_status,
      x_error_message        => x_error_message
    );

Line: 396


        ( 'Exception occurred  for Instance ID ' || p_INSTANCE_ID ||  SUBSTR(SQLERRM,1,3000), 'INSERT_INSTANCE',
          FND_LOG.LEVEL_EXCEPTION);

Line: 399


        x_error_message := 'Insert Instance failed : ' || x_error_message;

Line: 400


        ROLLBACK TO CSM_INSERT_INSTANCE_PUB;

Line: 409


    ( 'Leaving INSERT_INSTANCE for Instance Id : ' || x_INSTANCE_ID  ,
      FND_LOG.LEVEL_ERROR);

Line: 412


    x_error_message := 'Insert Successful for  Instance Id : ' || x_INSTANCE_ID ;

Line: 417


  ( 'Exception occurred in INSERT_INSTANCE for Query Id : ' || p_QUERY_ID  ||  SQLERRM, 'EXECUTE_QUERY',
    FND_LOG.LEVEL_EXCEPTION);

Line: 420


  x_error_message := 'Exception occurred in INSERT_INSTANCE With Message : ' || SUBSTR(SQLERRM,1,3000) ;

Line: 421


  ROLLBACK TO CSM_INSERT_INSTANCE_PUB;

Line: 422


END INSERT_INSTANCE;

Line: 425


PROCEDURE DELETE_INSTANCE
( p_API_VERSION		  	   IN  NUMBER,
  p_INIT_MSG_LIST	  	   IN  VARCHAR2 	:= FND_API.G_FALSE,
  p_COMMIT    		  	   IN  VARCHAR2 	:= FND_API.G_FALSE,
  p_USER_ID              IN  NUMBER,
  p_QUERY_ID             IN  NUMBER,
  p_INSTANCE_ID          IN  NUMBER,
  x_return_status        OUT NOCOPY VARCHAR2,
  x_error_message        OUT NOCOPY VARCHAR2
)
AS
BEGIN
    CSM_UTIL_PKG.LOG
      ( 'Entering DELETE_INSTANCE for Instance Id : ' || p_INSTANCE_ID  ,
        FND_LOG.LEVEL_ERROR);

Line: 441


    SAVEPOINT CSM_DELETE_INSTANCE_PUB;

Line: 443


    CSM_QUERY_PKG.DELETE_INSTANCE
    ( p_USER_ID              => p_USER_ID,
      p_QUERY_ID             => p_QUERY_ID,
      p_INSTANCE_ID          => p_INSTANCE_ID,
      p_commit               => p_COMMIT,
      x_return_status        => x_return_status,
      x_error_message        => x_error_message
    );

Line: 454


        ( 'Exception occurred  for Instance ID ' || p_INSTANCE_ID ||  SUBSTR(SQLERRM,1,3000), 'DELETE_INSTANCE',
          FND_LOG.LEVEL_EXCEPTION);

Line: 457


        x_error_message := 'Delete Instance failed : ' || x_error_message;

Line: 458


        ROLLBACK TO CSM_DELETE_INSTANCE_PUB;

Line: 467


    ( 'Leaving DELETE_INSTANCE for Query Id and Query Name : ' || p_QUERY_ID  ,
      FND_LOG.LEVEL_ERROR);

Line: 470


    x_error_message := 'Delete call Successful for  Instance Id : ' || p_INSTANCE_ID ;

Line: 475


  ( 'Exception occurred in DELETE_INSTANCE for Query Id : ' || p_QUERY_ID  ||  SQLERRM, 'DELETE_INSTANCE',
    FND_LOG.LEVEL_EXCEPTION);

Line: 478


  x_error_message := 'Exception occurred in DELETE_INSTANCE With Message : ' || SUBSTR(SQLERRM,1,3000) ;

Line: 479


  ROLLBACK TO CSM_DELETE_INSTANCE_PUB;

Line: 480


END DELETE_INSTANCE;

Line: 485


PROCEDURE INSERT_RESULT
( p_API_VERSION		  	   IN  NUMBER,
  p_INIT_MSG_LIST	  	   IN  VARCHAR2 	:= FND_API.G_FALSE,
  p_COMMIT    		  	   IN  VARCHAR2 	:= FND_API.G_FALSE,
  p_USER_ID              IN  NUMBER,
  p_QUERY_ID             IN  NUMBER,
  p_INSTANCE_ID          IN  VARCHAR2 DEFAULT NULL,
  p_QUERY_RESULT         IN  BLOB,
  x_return_status        OUT NOCOPY VARCHAR2,
  x_error_message        OUT NOCOPY VARCHAR2
)
AS
BEGIN
    CSM_UTIL_PKG.LOG
      ( 'Entering INSERT_RESULT for Instance Id : ' || p_INSTANCE_ID  ,
        FND_LOG.LEVEL_ERROR);

Line: 502


    SAVEPOINT CSM_INSERT_RESULT_PUB;

Line: 504


    CSM_QUERY_PKG.INSERT_RESULT
    ( p_USER_ID              => p_USER_ID,
      p_QUERY_ID             => p_QUERY_ID,
      p_INSTANCE_ID          => p_INSTANCE_ID,
      p_QUERY_RESULT         => p_QUERY_RESULT,
      p_commit               => p_COMMIT,
      x_return_status        => x_return_status,
      x_error_message        => x_error_message
    );

Line: 516


        ( 'Exception occurred  for Instance ID ' || p_INSTANCE_ID ||  SUBSTR(SQLERRM,1,3000), 'INSERT_RESULT',
          FND_LOG.LEVEL_EXCEPTION);

Line: 519


        x_error_message := 'Insert Result for Instance failed : ' || x_error_message;

Line: 520


        ROLLBACK TO CSM_INSERT_RESULT_PUB;

Line: 529


    ( 'Leaving INSERT_RESULT for Instance ID : ' || p_INSTANCE_ID  ,
      FND_LOG.LEVEL_ERROR);

Line: 532


    x_error_message := 'Insert Result Successful for  Instance Id : ' || p_INSTANCE_ID ;

Line: 537


  ( 'Exception occurred in INSERT_RESULT for Instance ID : ' || p_INSTANCE_ID  ||  SQLERRM, 'INSERT_RESULT',
    FND_LOG.LEVEL_EXCEPTION);

Line: 540


  x_error_message := 'Exception occurred in INSERT_RESULT With Message : ' || SUBSTR(SQLERRM,1,3000) ;

Line: 541


  ROLLBACK TO CSM_INSERT_RESULT_PUB;

Line: 542


END INSERT_RESULT;