APPS.CSM_QUERY_PKG SQL Statements

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

Line: 28

PROCEDURE UPDATE_EXE_STATUS
( p_USER_ID              IN  NUMBER,
  p_QUERY_ID             IN  NUMBER,
  p_INSTANCE_ID          IN  NUMBER,
  p_QSTART_TIME          IN  DATE,
  p_QEND_TIME            IN  DATE,
  p_STATUS               IN  VARCHAR2,
  p_ERROR                IN  VARCHAR2
)
IS
l_access_id NUMBER;

Line: 42

      ( 'Entering UPDATE_EXE_STATUS for User ID, Query Id, Instance ID : ' || p_USER_ID ||
       ':' || p_QUERY_ID || ':' || p_INSTANCE_ID,'CSM_QUERY_PKG.UPDATE_EXE_STATUS',
        FND_LOG.LEVEL_ERROR);

Line: 46

     UPDATE CSM_QUERY_INSTANCES_ACC
     SET    QUERY_START_TIME = NVL(p_QSTART_TIME,QUERY_START_TIME) ,
            QUERY_END_TIME   = NVL(p_QEND_TIME,QUERY_END_TIME) ,
            STATUS           = NVL(p_STATUS,STATUS),
            ERROR_DESCRIPTION= NVL(p_ERROR,ERROR_DESCRIPTION)
     WHERE  USER_ID     = p_USER_ID
     AND    QUERY_ID    = p_QUERY_ID
     AND    INSTANCE_ID = p_INSTANCE_ID
     RETURNING ACCESS_ID INTO l_access_id;

Line: 66

    ( 'Leaving UPDATE_EXE_STATUS for User ID, Query Id, Instance ID : ' || p_USER_ID ||
       ':' || p_QUERY_ID || ':' || p_INSTANCE_ID,'CSM_QUERY_PKG.UPDATE_EXE_STATUS',
      FND_LOG.LEVEL_ERROR);

Line: 73

  ( 'Exception occurred in UPDATE_EXE_STATUS for User ID, Query Id, Instance ID : ' || p_USER_ID ||
       ':' || p_QUERY_ID || ':' || p_INSTANCE_ID ||  SQLERRM, 'CSM_QUERY_PKG.UPDATE_EXE_STATUS',
    FND_LOG.LEVEL_EXCEPTION);

Line: 77

END UPDATE_EXE_STATUS;

Line: 80

  This procedure is called by ASG team to insert a Query
  That is created in the Mobile Admin Page
***/
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_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
CURSOR c_get_query_id --get query_id
IS
SELECT CSM_QUERY_B_S.NEXTVAL
FROM DUAL;

Line: 131

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

Line: 149

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

Line: 164

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

Line: 180

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

Line: 198

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

Line: 210

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

Line: 214

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

Line: 223

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

Line: 227

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

Line: 240

    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,           PROCEDURE_NAME,
                              EMAIL_ENABLED,        RESTRICTED_FLAG,  DISABLED_FLAG )
    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,
                              fnd_global.user_id,     sysdate,            fnd_global.user_id,
                              fnd_global.login_id,   'N',                 p_PROCEDURE,
                              p_EMAIL_ENABLED,        p_RESTRICTED_FLAG,  p_DISABLED_FLAG);

Line: 260

    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,                fnd_global.user_id,
                              sysdate,                fnd_global.user_id, fnd_global.login_id);

Line: 273

        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), l_VARIABLE_VALUE_CHAR,  p_VARIABLE_VALUE_DATE(i),
                                          NVL(p_HIDDEN_FLAG(i),'N'),   NVL(p_DEFAULT_FLAG(i),'N'),         sysdate,
                                          fnd_global.user_id,                  sysdate,                   fnd_global.user_id,
                                          fnd_global.login_id        );

Line: 288

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

Line: 296

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

Line: 299

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

Line: 302

END INSERT_QUERY;

Line: 306

  This procedure is called by ASG team to Update a Query
  that is created in the Mobile Admin Page
***/
PROCEDURE UPDATE_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_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
CURSOR c_get_existing_var_count(c_query_id NUMBER)  --get the count of existing variables
IS
SELECT count(*)
FROM CSM_QUERY_VARIABLES_B
WHERE QUERY_ID  =c_query_id ;

Line: 350

SELECT  LEVEL_VALUE
FROM    CSM_QUERY_B
WHERE   QUERY_ID  = c_query_id ;

Line: 367

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

Line: 385

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

Line: 401

          ( 'Exception occurred in VALIDATE_QUERY for Query Id ' || p_QUERY_ID ||  SQLERRM, 'CSM_QUERY_PKG.UPDATE_QUERY',
            FND_LOG.LEVEL_EXCEPTION);

Line: 417

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

Line: 433

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

Line: 448

      ( 'Responsibility Mapping for the query changed : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ,'CSM_QUERY_PKG.UPDATE_QUERY',
        FND_LOG.LEVEL_ERROR);

Line: 451

       DELETE_QUERY
        ( p_QUERY_ID         => p_QUERY_ID,
          x_return_status    => l_return_status,
          x_error_message    => l_return_message
        );

Line: 459

          ( 'ERROR occurred in DELETE_QUERY for Query Name ' || p_QUERY_NAME ||  SQLERRM, 'CSM_QUERY_PKG.UPDATE_QUERY',
            FND_LOG.LEVEL_EXCEPTION);

Line: 462

          x_error_message := 'Query Delete failed durin Update: ' || l_return_message;

Line: 464

        ELSE --Insert the query again for the new responsibility
          INSERT_QUERY
          ( p_QUERY_ID             => NULL,
            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        => l_return_status,
            x_error_message        => l_return_message
          );

Line: 500

            ( 'ERROR occurred in INSERT_QUERY for Query Name ' || p_QUERY_NAME ||  SUBSTR(SQLERRM,1,3000) , 'CSM_QUERY_PKG.UPDATE_QUERY',
              FND_LOG.LEVEL_EXCEPTION);

Line: 503

            x_error_message := 'INSERT_QUERY failed during UPDATE: ' || l_return_message;

Line: 508

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

Line: 519

      UPDATE    CSM_QUERY_B
      SET       QUERY_NAME = p_QUERY_NAME,       QUERY_TYPE = p_QUERY_TYPE,
                QUERY_TEXT1 = p_QUERY_TEXT1,     QUERY_TEXT2 = p_QUERY_TEXT2,
                LEVEL_ID  = p_LEVEL_ID,          LEVEL_VALUE  = p_LEVEL_VALUE,
                PARENT_QUERY_ID  = p_PARENT_QUERY_ID,           SAVED_QUERY  = p_SAVED_QUERY,
                QUERY_OUTPUT_FORMAT  = p_QUERY_OUTPUT_FORMAT,   MIME_TYPE  = p_MIME_TYPE,
                WORK_FLOW = p_WORK_FLOW,                        RETENTION_DAYS = p_RETENTION_DAYS,
                RETENTION_POLICY = p_RETENTION_POLICY,          TEMPLATE = p_TEMPLATE,
                TEMPLATE_FILE = p_TEMPLATE_FILE,                EXECUTION_MODE= p_EXECUTION_MODE,
                LAST_UPDATE_DATE = sysdate,                     LAST_UPDATED_BY = fnd_global.user_id,
                LAST_UPDATE_LOGIN = fnd_global.login_id,
                PROCEDURE_NAME = p_PROCEDURE,
                EMAIL_ENABLED = p_EMAIL_ENABLED,
                RESTRICTED_FLAG = p_RESTRICTED_FLAG,
                DISABLED_FLAG = p_DISABLED_FLAG
      WHERE     QUERY_ID = p_QUERY_ID;

Line: 536

      UPDATE    CSM_QUERY_TL
      SET       DESCRIPTION = P_QUERY_DESC,
                LAST_UPDATE_DATE = sysdate,
                LAST_UPDATED_BY = fnd_global.user_id,
                LAST_UPDATE_LOGIN = fnd_global.login_id
      WHERE     QUERY_ID = p_QUERY_ID
      AND       LANGUAGE = l_language;

Line: 548

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

Line: 552

            x_error_message := ' UPDATE_QUERY Failed for Query Id and Query Name : ' || p_QUERY_ID || ' : ' ||
                             p_QUERY_NAME || ' because of Variable Name and Variable Type Mismatch';

Line: 561

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

Line: 565

            x_error_message := ' UPDATE_QUERY Failed for Query Id and Query Name : ' || p_QUERY_ID || ' : ' ||
                             p_QUERY_NAME || ' because of Variable Name and Variable Type Mismatch';

Line: 589

          DELETE FROM CSM_QUERY_VARIABLES_B WHERE QUERY_ID =p_QUERY_ID;

Line: 594

          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                            ( p_QUERY_ID,         i,          p_VARIABLE_NAME(i),
                                              p_VARIABLE_TYPE(i), l_VARIABLE_VALUE_CHAR,  p_VARIABLE_VALUE_DATE(i),
                                              NVL(p_HIDDEN_FLAG(i),'N'),   NVL(p_DEFAULT_FLAG(i),'N'),         sysdate,
                                              fnd_global.user_id,                  sysdate,                   fnd_global.user_id,
                                              fnd_global.login_id        );

Line: 613

          UPDATE CSM_QUERY_VARIABLES_B
          SET    VARIABLE_NAME    = p_VARIABLE_NAME(i),
                  VARIABLE_TYPE   = p_VARIABLE_TYPE(i),
                  VARIABLE_VALUE_CHAR = l_VARIABLE_VALUE_CHAR,
                  VARIABLE_VALUE_DATE = p_VARIABLE_VALUE_DATE(i),
                  HIDDEN              = NVL(p_HIDDEN_FLAG(i),'N'),
                  DEFAULT_FLAG        = NVL(p_DEFAULT_FLAG(i),'N'),
                  LAST_UPDATE_DATE    = sysdate,
                  LAST_UPDATED_BY     = fnd_global.user_id,
                  LAST_UPDATE_LOGIN   = fnd_global.login_id
          WHERE QUERY_ID =p_QUERY_ID
          AND   VARIABLE_ID = i	;

Line: 632

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

Line: 638

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

Line: 641

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

Line: 644

END UPDATE_QUERY;

Line: 647

  This procedure is called by ASG team to Delete a Query
  That is created in the Mobile Admin Page
***/
PROCEDURE DELETE_QUERY
( 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  ,'CSM_QUERY_PKG.DELETE_QUERY',
        FND_LOG.LEVEL_ERROR);

Line: 662

      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 = p_QUERY_ID;

Line: 670

      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 PARENT_QUERY_ID = p_QUERY_ID;

Line: 680

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

Line: 686

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

Line: 689

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

Line: 690

END DELETE_QUERY;

Line: 716

 SELECT QUERY_TEXT1,QUERY_TEXT2
 FROM   CSM_QUERY_B
 WHERE  QUERY_ID = c_QUERY_ID;

Line: 810

SELECT
QUERY_TEXT1,
QUERY_TEXT2,
QUERY_TYPE,
WORK_FLOW,
PROCEDURE_NAME
FROM  CSM_QUERY_B
WHERE QUERY_ID =c_QUERY_ID;

Line: 821

SELECT INSTANCE_ID
FROM  CSM_QUERY_INSTANCES_ACC
WHERE QUERY_ID    = c_QUERY_ID
AND   USER_ID     = c_USER_ID
AND   INSTANCE_ID = c_INSTANCE_ID;

Line: 829

SELECT  vacc.VARIABLE_ID,
NVL(vacc.VARIABLE_VALUE_CHAR,vb.VARIABLE_VALUE_CHAR) as VARIABLE_VALUE_CHAR,
NVL(vacc.VARIABLE_VALUE_DATE,vb.VARIABLE_VALUE_DATE) as VARIABLE_VALUE_DATE,
vb.VARIABLE_TYPE,
vb.VARIABLE_NAME
FROM
CSM_QUERY_INSTANCES_ACC iacc,
CSM_QUERY_VARIABLE_VALUES_ACC vacc,
CSM_QUERY_VARIABLES_B     vb
WHERE iacc.USER_ID     = vacc.USER_ID
AND   iacc.QUERY_ID    = vacc.QUERY_ID
AND   iacc.INSTANCE_ID = vacc.INSTANCE_ID
AND   vacc.QUERY_ID    = vb.QUERY_ID
AND   vacc.VARIABLE_ID = vb.VARIABLE_ID
AND   iacc.USER_ID     = c_USER_ID
AND   iacc.QUERY_ID    = c_QUERY_ID
AND   iacc.INSTANCE_ID = c_INSTANCE_ID;

Line: 849

SELECT ACCESS_ID
FROM  CSM_QUERY_RESULTS_ACC
WHERE QUERY_ID    = c_QUERY_ID
AND   USER_ID     = c_USER_ID
AND   INSTANCE_ID = c_INSTANCE_ID;

Line: 857

SELECT ACTIVITY_NAME
FROM   WF_PROCESS_ACTIVITIES
WHERE  process_item_type =c_process_item_type
AND    PROCESS_NAME ='ROOT';

Line: 911

    UPDATE_EXE_STATUS( p_USER_ID,  p_QUERY_ID,  p_INSTANCE_ID,
                      SYSDATE,NULL,'RUNNING',NULL);

Line: 1008

            UPDATE_EXE_STATUS( p_USER_ID,  p_QUERY_ID,  p_INSTANCE_ID,
                      NULL,SYSDATE,'ERROR','Query Completed with Error: '||SUBSTR(l_return_message,1,3000));

Line: 1022

          UPDATE CSM_QUERY_RESULTS_ACC
          SET RESULT           = l_xml_blob,
              LAST_UPDATE_DATE = sysdate,
              LAST_UPDATED_BY  = fnd_global.user_id
          WHERE ACCESS_ID = l_rs_access_id;

Line: 1031

          INSERT INTO  CSM_QUERY_RESULTS_ACC(ACCESS_ID ,   USER_ID ,   QUERY_ID ,   INSTANCE_ID , LINE_ID,
                    RESULT ,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
          VALUES       (CSM_QUERY_RESULTS_ACC_S.NEXTVAL, p_USER_ID, p_QUERY_ID, p_INSTANCE_ID, 1,
                    l_xml_blob, fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id)
          RETURNING ACCESS_ID into l_access_id;

Line: 1040

          UPDATE CSM_QUERY_RESULTS_ACC
          SET RESULT           = EMPTY_BLOB(),
              LAST_UPDATE_DATE = sysdate,
              LAST_UPDATED_BY  = fnd_global.user_id
          WHERE ACCESS_ID = l_rs_access_id;

Line: 1048

          INSERT INTO  CSM_QUERY_RESULTS_ACC(ACCESS_ID ,   USER_ID ,   QUERY_ID ,   INSTANCE_ID , LINE_ID,
                    RESULT ,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
          VALUES       (CSM_QUERY_RESULTS_ACC_S.NEXTVAL, p_USER_ID, p_QUERY_ID, p_INSTANCE_ID, 1,
                    EMPTY_BLOB(), fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id)
          RETURNING ACCESS_ID into l_access_id;

Line: 1074

        UPDATE_EXE_STATUS( p_USER_ID,  p_QUERY_ID,  p_INSTANCE_ID,
                      NULL,SYSDATE,'EXECUTED','Query Successfully Executed');

Line: 1138

      UPDATE_EXE_STATUS( p_USER_ID,  p_QUERY_ID,  p_INSTANCE_ID,
                      NULL,SYSDATE,'RUNNING','Query Successfully Executed');

Line: 1168

    UPDATE_EXE_STATUS( p_USER_ID,  p_QUERY_ID,  p_INSTANCE_ID,
                      NULL,SYSDATE,'ERROR','Query Completed with Error'||SUBSTR(SQLERRM,1,3000) );

Line: 1178

PROCEDURE INSERT_INSTANCE
( 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,
  p_commit               IN  VARCHAR2 DEFAULT fnd_api.G_TRUE,
  x_INSTANCE_ID          OUT NOCOPY NUMBER,
  x_return_status        OUT NOCOPY VARCHAR2,
  x_error_message        OUT NOCOPY VARCHAR2
)
AS
CURSOR c_check_query(c_QUERY_ID NUMBER)
IS
SELECT QUERY_NAME,LEVEL_ID,LEVEL_VALUE,QUERY_TYPE
FROM   CSM_QUERY_B
WHERE  QUERY_ID =c_QUERY_ID
AND    NVL(DELETE_FLAG,'N') = 'N';

Line: 1201

SELECT QUERY_ID
FROM   CSM_QUERY_ACC
WHERE  QUERY_ID = c_QUERY_ID
AND    USER_ID  = c_USER_ID;

Line: 1208

SELECT  vb.VARIABLE_ID,
vb.VARIABLE_VALUE_CHAR,
vb.VARIABLE_VALUE_DATE,
vb.VARIABLE_TYPE
FROM  CSM_QUERY_VARIABLES_B     vb
WHERE vb.QUERY_ID = c_QUERY_ID;

Line: 1217

SELECT CSM_QUERY_INSTANCES_ACC_S.NEXTVAL
FROM DUAL;

Line: 1222

SELECT CSM_QUERY_VARIABLE_VAL_ACC_S.NEXTVAL
FROM DUAL;

Line: 1247

      ( 'Entering INSERT_INSTANCE for Query Id : ' || p_QUERY_ID  ,'CSM_QUERY_PKG.INSERT_INSTANCE',
        FND_LOG.LEVEL_ERROR);

Line: 1253

      CSM_UTIL_PKG.LOG( 'Invalid Query Id : ' || p_QUERY_ID  ,'CSM_QUERY_PKG.INSERT_INSTANCE',FND_LOG.LEVEL_ERROR);

Line: 1275

        ( 'Exception occurred in VALIDATE_ACCESS for Query Name ' || l_instance_name ||  SQLERRM, 'CSM_QUERY_PKG.INSERT_INSTANCE',
          FND_LOG.LEVEL_EXCEPTION);

Line: 1286

      CSM_UTIL_PKG.LOG( 'User does not have access to the Query Id : ' || p_QUERY_ID  ,'CSM_QUERY_PKG.INSERT_INSTANCE',FND_LOG.LEVEL_ERROR);

Line: 1295

    SAVEPOINT INSERT_INSTANCE;

Line: 1311

    INSERT INTO  CSM_QUERY_INSTANCES_ACC(ACCESS_ID ,   USER_ID ,   QUERY_ID ,   INSTANCE_ID , INSTANCE_NAME,STATUS,
              CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
    VALUES     (l_instance_id, p_USER_ID, p_QUERY_ID, l_instance_id,l_instance_name,'OPEN',
               fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id);

Line: 1338

      ( 'Leaving INSERT_INSTANCE for Query Id  : ' || p_QUERY_ID  ,'CSM_QUERY_PKG.INSERT_INSTANCE',
        FND_LOG.LEVEL_ERROR);

Line: 1342

      x_error_message := 'Leaving INSERT_INSTANCE for Query Id  : ' || p_QUERY_ID ;

Line: 1349

      ( 'Variable Count mismatch.Leaving INSERT_INSTANCE for Query Id : ' || p_QUERY_ID  ,'CSM_QUERY_PKG.INSERT_INSTANCE',
        FND_LOG.LEVEL_ERROR);

Line: 1352

      x_error_message := 'Leaving INSERT_INSTANCE for Query Id  : ' || p_QUERY_ID ;

Line: 1353

      ROLLBACK TO INSERT_INSTANCE;

Line: 1361

            ( 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id : ' || p_QUERY_ID  ,'CSM_QUERY_PKG.INSERT_INSTANCE',
              FND_LOG.LEVEL_ERROR);

Line: 1364

            x_error_message := 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id  : ' || p_QUERY_ID ;

Line: 1365

            ROLLBACK TO INSERT_INSTANCE;

Line: 1372

            ( 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id : ' || p_QUERY_ID  ,'CSM_QUERY_PKG.INSERT_INSTANCE',
              FND_LOG.LEVEL_ERROR);

Line: 1375

            x_error_message := 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id  : ' || p_QUERY_ID ;

Line: 1377

            ROLLBACK TO INSERT_INSTANCE;

Line: 1393

      INSERT INTO  CSM_QUERY_VARIABLE_VALUES_ACC(ACCESS_ID ,   USER_ID ,   QUERY_ID ,
                INSTANCE_ID , VARIABLE_ID,VARIABLE_VALUE_CHAR,VARIABLE_VALUE_DATE,
                CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,GEN_PK)
      VALUES     (l_qvariable_id, p_USER_ID, p_QUERY_ID,
                  l_instance_id,  i, l_VARIABLE_VALUE_CHAR, p_VARIABLE_VALUE_DATE(i),
                 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id,l_qvariable_id)
      RETURNING ACCESS_ID into l_access_id;

Line: 1416

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

Line: 1419

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

Line: 1423

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

Line: 1426

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

Line: 1428

  ROLLBACK TO INSERT_INSTANCE;

Line: 1429

END INSERT_INSTANCE;

Line: 1468

 SELECT QUERY_TEXT1,QUERY_TEXT2
 FROM   CSM_QUERY_B
 WHERE  QUERY_ID = c_QUERY_ID;

Line: 1474

 SELECT 1 FROM ALL_OBJECTS
 WHERE  OBJECT_NAME = c_object_name
 AND    OWNER       = c_schema
 AND    OBJECT_TYPE IN('TABLE','VIEW','SYNONYM');

Line: 1604

PROCEDURE DELETE_INSTANCE
( p_USER_ID              IN  NUMBER,
  p_QUERY_ID             IN  NUMBER,
  p_INSTANCE_ID          IN  NUMBER,
  p_commit               IN  VARCHAR2 DEFAULT fnd_api.G_TRUE,
  x_return_status        OUT NOCOPY VARCHAR2,
  x_error_message        OUT NOCOPY VARCHAR2
)
AS
CURSOR c_get_instance (c_USER_ID NUMBER, c_QUERY_ID NUMBER, c_INSTANCE_ID NUMBER )
IS
SELECT  ACCESS_ID
FROM    CSM_QUERY_INSTANCES_ACC
WHERE   QUERY_ID = c_QUERY_ID
AND     USER_ID  = c_USER_ID
AND     INSTANCE_ID = c_INSTANCE_ID;

Line: 1623

SELECT  ACCESS_ID
FROM    CSM_QUERY_VARIABLE_VALUES_ACC
WHERE   QUERY_ID = c_QUERY_ID
AND     USER_ID  = c_USER_ID
AND     INSTANCE_ID = c_INSTANCE_ID;

Line: 1631

SELECT  ACCESS_ID
FROM    CSM_QUERY_RESULTS_ACC
WHERE   QUERY_ID = c_QUERY_ID
AND     USER_ID  = c_USER_ID
AND     INSTANCE_ID = c_INSTANCE_ID;

Line: 1643

      ( 'Entering DELETE_INSTANCE for Query Id : ' || p_QUERY_ID  ,'CSM_QUERY_PKG.DELETE_INSTANCE',
        FND_LOG.LEVEL_ERROR);

Line: 1646

    SAVEPOINT DELETE_INSTANCE;

Line: 1654

      CSM_UTIL_PKG.LOG( 'Invalid Instance Id : ' || p_INSTANCE_ID  ,'CSM_QUERY_PKG.DELETE_INSTANCE',FND_LOG.LEVEL_ERROR);

Line: 1657

      ROLLBACK TO DELETE_INSTANCE;

Line: 1661

    DELETE FROM CSM_QUERY_INSTANCES_ACC WHERE ACCESS_ID = l_access_id;

Line: 1677

      DELETE FROM CSM_QUERY_VARIABLE_VALUES_ACC WHERE ACCESS_ID = l_access_id_list(i);

Line: 1690

      l_access_id_list.DELETE;

Line: 1698

      DELETE FROM CSM_QUERY_RESULTS_ACC WHERE ACCESS_ID = l_access_id_list(i);

Line: 1715

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

Line: 1718

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

Line: 1723

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

Line: 1726

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

Line: 1727

  ROLLBACK TO DELETE_INSTANCE;

Line: 1728

END DELETE_INSTANCE;

Line: 1747

 SELECT WORK_FLOW
 FROM  CSM_QUERY_B
 WHERE QUERY_ID = c_query_id;

Line: 1753

 SELECT NAME
 FROM  WF_ITEM_TYPES
 WHERE NAME = c_work_flow;

Line: 1759

 SELECT NAME
 FROM  WF_ITEM_ATTRIBUTES
 WHERE item_type = c_work_flow
 AND   name      = c_variable_name;

Line: 1854

 SELECT procedure_name
 FROM  CSM_QUERY_B
 WHERE QUERY_ID = c_query_id;

Line: 1860

 SELECT object_name
 FROM all_objects
 WHERE object_name = c_package_name
 AND OWNER = p_schema_name
 AND object_type = 'PACKAGE'
 AND status = 'VALID';

Line: 1888

     SELECT oracle_username INTO l_SCHEMA_NAME
      FROM fnd_oracle_userid
     WHERE read_only_flag = 'U';

Line: 1923

PROCEDURE INSERT_RESULT
( p_USER_ID              IN  NUMBER,
  p_QUERY_ID             IN  NUMBER,
  p_INSTANCE_ID          IN  VARCHAR2 DEFAULT NULL,
  p_QUERY_RESULT         IN  BLOB,
  p_commit               IN  VARCHAR2 DEFAULT fnd_api.G_TRUE,
  x_return_status        OUT NOCOPY VARCHAR2,
  x_error_message        OUT NOCOPY VARCHAR2
)
AS
CURSOR c_check_results(c_USER_ID NUMBER,c_QUERY_ID NUMBER,c_INSTANCE_ID NUMBER)
IS
SELECT ACCESS_ID
FROM  CSM_QUERY_RESULTS_ACC
WHERE QUERY_ID    = c_QUERY_ID
AND   USER_ID     = c_USER_ID
AND   INSTANCE_ID = c_INSTANCE_ID;

Line: 1951

   CSM_UTIL_PKG.LOG('Entering CSM_QUERY_PKG.INSERT_RESULT for Instance ID: ' || p_INSTANCE_ID,
                         'CSM_QUERY_PKG.INSERT_RESULT',FND_LOG.LEVEL_PROCEDURE);

Line: 1958

    SAVEPOINT INSERT_RESULT;

Line: 1962

        UPDATE CSM_QUERY_RESULTS_ACC
        SET RESULT           = p_QUERY_RESULT,
            LAST_UPDATE_DATE = sysdate,
            LAST_UPDATED_BY  = fnd_global.user_id
        WHERE ACCESS_ID = l_rs_access_id;

Line: 1971

        INSERT INTO  CSM_QUERY_RESULTS_ACC(ACCESS_ID ,   USER_ID ,   QUERY_ID ,   INSTANCE_ID , LINE_ID,
                  RESULT ,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
        VALUES       (CSM_QUERY_RESULTS_ACC_S.NEXTVAL, p_USER_ID, p_QUERY_ID, p_INSTANCE_ID, 1,
                  p_QUERY_RESULT, fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id)
        RETURNING ACCESS_ID into l_access_id;

Line: 1980

        UPDATE CSM_QUERY_RESULTS_ACC
        SET RESULT           = EMPTY_BLOB(),
            LAST_UPDATE_DATE = sysdate,
            LAST_UPDATED_BY  = fnd_global.user_id
        WHERE ACCESS_ID = l_rs_access_id;

Line: 1988

        INSERT INTO  CSM_QUERY_RESULTS_ACC(ACCESS_ID ,   USER_ID ,   QUERY_ID ,   INSTANCE_ID , LINE_ID,
                  RESULT ,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
        VALUES       (CSM_QUERY_RESULTS_ACC_S.NEXTVAL, p_USER_ID, p_QUERY_ID, p_INSTANCE_ID, 1,
                  EMPTY_BLOB(), fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id)
        RETURNING ACCESS_ID into l_access_id;

Line: 2014

    UPDATE_EXE_STATUS( p_USER_ID,  p_QUERY_ID,  p_INSTANCE_ID,
                      NULL,SYSDATE,'EXECUTED','Query Successfully Executed');

Line: 2019

   CSM_UTIL_PKG.LOG('Leaving CSM_QUERY_PKG.INSERT_RESULT for Instance ID : ' || p_INSTANCE_ID,
                         'CSM_QUERY_PKG.INSERT_RESULT',FND_LOG.LEVEL_EXCEPTION);

Line: 2023

   x_error_message := 'Query Result Successfully Inserted into the Access Table';

Line: 2033

        CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_QUERY_PKG.INSERT_RESULT',FND_LOG.LEVEL_EXCEPTION);

Line: 2034

        ROLLBACK TO INSERT_RESULT;

Line: 2035

END INSERT_RESULT;