DBA Data[Home] [Help]

APPS.CSM_QUERY_INSTANCE_PKG SQL Statements

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

Line: 13

/* Select all inq records */
CURSOR c_query_instances( b_user_name VARCHAR2, b_tranid NUMBER, b_from_sync VARCHAR2) is
  SELECT inq.*
  FROM  CSM_QUERY_INSTANCES_INQ inq,
        CSM_QUERY_B b
  WHERE inq.tranid$$ = b_tranid
  AND   inq.clid$$cs = b_user_name
  AND   inq.QUERY_ID = b.QUERY_ID
  AND   ((b.EXECUTION_MODE = 'SYNCHRONOUS' AND b_from_sync ='Y') OR b_from_sync = 'N');
Line: 24

  SELECT *
  FROM  CSM_QUERY_VARIABLE_VALUES_INQ
  WHERE tranid$$ = b_tranid
  AND   clid$$cs = b_user_name
  AND   INSTANCE_ID = b_instance_id;
Line: 30

  This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
***/
PROCEDURE APPLY_INSERT
         (
           p_record        IN c_query_instances%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,LEVEL_ID,LEVEL_VALUE, QUERY_TYPE
FROM   CSM_QUERY_B
WHERE  QUERY_ID =c_QUERY_ID
AND    NVL(DELETE_FLAG,'N') = 'N';
Line: 48

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

SELECT  vb.VARIABLE_ID,
vb.VARIABLE_VALUE_CHAR,
vb.VARIABLE_VALUE_DATE,
vb.SEQNO$$
FROM  CSM_QUERY_VARIABLE_VALUES_INQ     vb
WHERE vb.CLID$$CS = c_user_name
AND   vb.TRANID$$ = c_tran_id
AND   vb.QUERY_ID = c_QUERY_ID
AND   vb.INSTANCE_ID = c_instance_id;
Line: 69

SELECT CSM_QUERY_INSTANCES_ACC_S.NEXTVAL
FROM DUAL;
Line: 74

SELECT USER_ID
FROM   ASG_USER
WHERE  USER_NAME =c_user_name;
Line: 80

SELECT CSM_QUERY_VARIABLE_VAL_ACC_S.NEXTVAL
FROM DUAL;
Line: 111

  CSM_UTIL_PKG.LOG('Entering CSM_QUERY_INSTANCE_PKG.APPLY_INSERT for Instance ID ' || p_record.INSTANCE_ID ,
                         'CSM_QUERY_INSTANCE_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
Line: 141

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

  SAVEPOINT INSERT_QUERY_INSTANCE;
Line: 168

  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, l_user_id, l_query_id, l_instance_id,l_instance_name,'OPEN',
             fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id);
Line: 202

    ROLLBACK TO INSERT_QUERY_INSTANCE;
Line: 206

   CSM_UTIL_PKG.LOG( 'Leaving INSERT_INSTANCE for Query Id  : ' || l_user_id  , FND_LOG.LEVEL_ERROR);
Line: 208

   p_error_msg     := 'Leaving INSERT_INSTANCE for Query Id  : ' || l_user_id ;
Line: 220

    ( 'Variable Count mismatch.Leaving INSERT_INSTANCE for Query Id : ' || l_query_id  ,
      FND_LOG.LEVEL_ERROR);
Line: 223

    p_error_msg     := 'Variable Count mismatch.Leaving INSERT_INSTANCE for Query Id  : ' || l_query_id ;
Line: 231

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

          p_error_msg     := 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id  : ' || l_query_id ;
Line: 241

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

          p_error_msg     := 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id  : ' || l_query_id ;
Line: 258

    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, l_user_id, l_query_id,
                l_instance_id,  i, l_var_value_char_inq_lst(i), l_var_value_date_inq_lst(i),
               l_user_id, SYSDATE, l_user_id, SYSDATE, l_user_id,l_qvariable_id)
    RETURNING ACCESS_ID into l_access_id;
Line: 289

    ROLLBACK TO INSERT_QUERY_INSTANCE;
Line: 293

  CSM_UTIL_PKG.LOG('Leaving CSM_QUERY_INSTANCE_PKG.APPLY_INSERT for Instance ID ' || p_record.INSTANCE_ID ,
                         'CSM_QUERY_INSTANCE_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
Line: 300

  fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
Line: 305

     CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT: ' || sqlerrm
               || ' for Instance ID ' || p_record.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION);
Line: 307

  p_error_msg     := 'Exception in APPLY_INSERT for Instance Id  : ' || l_instance_id || substr(SQLERRM, 1,2000);
Line: 309

  ROLLBACK TO INSERT_QUERY_INSTANCE;
Line: 310

END APPLY_INSERT;
Line: 314

  This procedure is called by APPLY_CLIENT_CHANGES when an Delete  is to be processed.
***/
PROCEDURE APPLY_DELETE
         (
           p_record        IN c_query_instances%ROWTYPE,
           p_error_msg     out nocopy    VARCHAR2,
           x_return_status IN out nocopy VARCHAR2
         )
IS
CURSOR c_get_user(c_user_name VARCHAR2)
IS
SELECT USER_ID
FROM   ASG_USER
WHERE  USER_NAME =c_user_name;
Line: 331

SELECT QUERY_ID
FROM   CSM_QUERY_INSTANCES_ACC
WHERE  INSTANCE_ID = c_instance_id;
Line: 345

  CSM_UTIL_PKG.LOG('Entering CSM_QUERY_INSTANCE_PKG.APPLY_DELETE for Instance ID ' || p_record.INSTANCE_ID ,
                         'CSM_QUERY_INSTANCE_PKG.APPLY_DELETE',FND_LOG.LEVEL_PROCEDURE);
Line: 361

    CSM_UTIL_PKG.LOG( 'Query Instance Delete Failed for  : ' || l_instance_id  ,FND_LOG.LEVEL_ERROR);
Line: 363

    p_error_msg     := 'Delete  request failed for Instance Id  : ' || l_instance_id  || ' as it does not exist.';
Line: 368

  CSM_QUERY_PKG.DELETE_INSTANCE
    ( p_USER_ID        => l_user_id,
      p_QUERY_ID       => l_query_id,
      p_INSTANCE_ID    => l_instance_id,
      p_commit         => fnd_api.G_FALSE,
      x_return_status  => x_return_status,
      x_error_message  => p_error_msg)  ;
Line: 377

    CSM_UTIL_PKG.LOG( 'Query Instance Delete Failed for  : ' || l_instance_id  ,FND_LOG.LEVEL_ERROR);
Line: 382

  CSM_UTIL_PKG.LOG('Leaving CSM_QUERY_INSTANCE_PKG.APPLY_DELETE for Instance ID ' || p_record.INSTANCE_ID ,
                         'CSM_QUERY_INSTANCE_PKG.APPLY_DELETE',FND_LOG.LEVEL_PROCEDURE);
Line: 385

  p_error_msg     := 'Delete Successful for the Instance Id  : ' || l_instance_id ;
Line: 389

     fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_DELETE', sqlerrm);
Line: 391

     CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_DELETE: ' || sqlerrm
               || ' for Instance ID ' || p_record.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_DELETE',FND_LOG.LEVEL_EXCEPTION);
Line: 393

     p_error_msg     := 'Exception in APPLY_DELETE for Instance Id  : ' || l_instance_id || substr(SQLERRM, 1,2000);
Line: 395

END APPLY_DELETE;
Line: 417

    APPLY_INSERT
      (
        p_record,
        p_error_msg,
        x_return_status
      );
Line: 425

    APPLY_DELETE
      (
        p_record,
        p_error_msg,
        x_return_status
      );
Line: 431

  ELSE -- 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.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);
Line: 465

  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,
           p_from_sync     IN VARCHAR2 DEFAULT 'N',
           x_return_status IN out nocopy VARCHAR2
         ) IS

  l_process_status VARCHAR2(1);
Line: 499

      /*** Yes -> delete record from inqueue ***/
      CSM_UTIL_PKG.REJECT_RECORD
        (
          p_user_name,
          p_tranid,
          r_qi_rec.seqno$$,
          r_qi_rec.INSTANCE_ID,
          g_object_name,
          g_pub_name,
          l_error_msg,
          l_process_status
        );
Line: 511

      /*** was Instance delete successful? ***/
      IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
        /*** Yes -> delete Variable Values record from inqueue ***/
        FOR r_qvv_rec IN c_query_variable_values( p_user_name, p_tranid, r_qi_rec.INSTANCE_ID) LOOP
          CSM_UTIL_PKG.REJECT_RECORD
            (
              p_user_name,
              p_tranid,
              r_qvv_rec.seqno$$,
              r_qvv_rec.GEN_PK,
              g_object_name,
              g_pub_qvv_name,
              l_error_msg,
              l_process_status
            );
Line: 532

      /*** 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_qi_rec.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here