DBA Data[Home] [Help]

APPS.CSM_REQUIREMENTS_PKG SQL Statements

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

Line: 22

  This one is executed after all requirement lines with headers have been deleted from the inqueue.
  The requirement lines without header remain then.
***/
CURSOR c_requirements_no_headers ( b_user_name VARCHAR2, b_tranid NUMBER) is
  SELECT *
  FROM   CSM_REQ_LINES_INQ l
  WHERE  l.tranid$$ = b_tranid
  AND    l.clid$$cs = b_user_name
  AND EXISTS
  (SELECT 1
   FROM csp_requirement_headers h
   WHERE h.requirement_header_id = l.requirement_header_id
   );
Line: 42

  SELECT reql.*
  FROM   CSM_REQ_LINES_INQ reql, CSM_REQ_HEADERS_INQ reqh
  WHERE  reql.tranid$$ = reqh.tranid$$
  AND    reql.clid$$cs = reqh.clid$$cs
  AND    reql.tranid$$ = b_tranid
  AND    reql.clid$$cs = b_user_name
  AND    reql.requirement_header_id = reqh.requirement_header_id;
Line: 51

  This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
  If p_get_inqueue_header = TRUE  => fetch requirement header from the requirement header inqueue.
  If p_get_inqueue_header = FALSE => fetch requirement header from Apps.
***/
PROCEDURE APPLY_INSERT
         (
           p_record              IN      c_requirements%ROWTYPE,
           p_user_name           IN      VARCHAR2,
           p_tranid              IN      NUMBER,
           p_get_inqueue_header  IN      BOOLEAN,
           p_error_msg           OUT NOCOPY     VARCHAR2,
           x_return_status       IN OUT NOCOPY  VARCHAR2
         ) IS

  l_header_rec   CSP_PARTS_REQUIREMENT.HEADER_REC_TYPE;
Line: 79

    SELECT *
    FROM   CSM_REQ_HEADERS_INQ
    WHERE  tranid$$ = b_tranid
    AND    clid$$cs = b_user_name
    AND    requirement_header_id = b_requirement_header_id;
Line: 89

    SELECT *
    FROM   CSP_REQUIREMENT_HEADERS
    WHERE  requirement_header_id = b_requirement_header_id;
Line: 98

       ( 'Entering CSM_REQUIREMENTS_PKG.APPLY_INSERT'|| 'for PK '||p_record.requirement_line_id,
         'CSM_REQUIREMENTS_PKG.APPLY_INSERT',
          FND_LOG.LEVEL_STATEMENT ); -- put PK column here
Line: 230

       ( 'Leaving CSM_REQUIREMENTS_PKG.APPLY_INSERT'|| 'for PK '||p_record.requirement_line_id,
         'CSM_REQUIREMENTS_PKG.APPLY_INSERT',
          FND_LOG.LEVEL_STATEMENT ); -- put PK column here
Line: 238

     ( 'Exception occurred in CSM_REQUIREMENTS_PKG.APPLY_INSERT'|| 'for PK '||p_record.requirement_line_id || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm,
       'CSM_REQUIREMENTS_PKG.APPLY_INSERT',
        FND_LOG.LEVEL_EXCEPTION ); -- put PK column here
Line: 242

  fnd_msg_pub.Add_Exc_Msg( 'CSM_REQUIREMENTS_PKG', 'APPLY_INSERT', sqlerrm);
Line: 250

END APPLY_INSERT;
Line: 253

  This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
***/
PROCEDURE APPLY_UPDATE
         (
           p_record        IN     c_requirements%ROWTYPE,
           p_error_msg     OUT NOCOPY    VARCHAR2,
           x_return_status IN OUT NOCOPY VARCHAR2
         ) IS
BEGIN

  CSM_UTIL_PKG.LOG
     ( 'Entering CSM_REQUIREMENTS_PKG.APPLY_UPDATE'|| 'for PK '||p_record.requirement_line_id ,
       'CSM_REQUIREMENTS_PKG.APPLY_UPDATE',
        FND_LOG.LEVEL_STATEMENT ); -- put PK column here
Line: 280

     ( 'Leaving CSM_REQUIREMENTS_PKG.APPLY_UPDATE'|| 'for PK '||p_record.requirement_line_id ,
       'CSM_REQUIREMENTS_PKG.APPLY_UPDATE',
        FND_LOG.LEVEL_STATEMENT ); -- put PK column here
Line: 288

     ( 'Exception occurred in CSM_REQUIREMENTS_PKG.APPLY_UPDATE'|| 'for PK '||p_record.requirement_line_id || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm,
       'CSM_REQUIREMENTS_PKG.APPLY_UPDATE',
        FND_LOG.LEVEL_EXCEPTION); -- put PK column here
Line: 293

  fnd_msg_pub.Add_Exc_Msg( 'CSM_REQUIREMENTS_PKG', 'APPLY_UPDATE', sqlerrm);
Line: 300

END APPLY_UPDATE;
Line: 330

    APPLY_INSERT
      (
        p_record,
        p_user_name,
        p_tranid,
        p_get_inqueue_header,
        p_error_msg,
        x_return_status
      );
Line: 341

    APPLY_UPDATE
      (
       p_record,
       p_error_msg,
       x_return_status
     );
Line: 350

       ( 'Delete is not supported for this entity'|| 'for PK '||p_record.requirement_line_id,
         'CSM_REQUIREMENTS_PKG.APPLY_RECORD',
          FND_LOG.LEVEL_ERROR ); -- put PK column here
Line: 404

   This procedure is called by PROCESS_REQS and deletes all requirement headers from the inqueue,
   for a given user and transaction.
***/
PROCEDURE DELETE_REQ_HEADERS_FROM_INQ
         (
           p_user_name     IN      VARCHAR2,
           p_tranid        IN      NUMBER,
           x_return_status IN OUT NOCOPY  VARCHAR2
         ) IS

  l_error_msg VARCHAR2(4000);
Line: 418

    This one is to be executed after all requirement lines with headers have been deleted from the inqueue.
  ***/
  CURSOR c_get_req_headers_from_inq ( b_user_name VARCHAR2, b_tranid NUMBER) is
    SELECT *
    FROM   CSM_REQ_HEADERS_INQ h
    WHERE  h.tranid$$ = b_tranid
    AND    h.clid$$cs = b_user_name
    AND NOT EXISTS (SELECT 1
                    FROM csm_req_lines_inq l
                    WHERE l.tranid$$ = b_tranid
                    AND  l.clid$$cs = b_user_name
                    AND  l.requirement_header_id = h.requirement_header_id
                    );
Line: 435

       ( 'Entering CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
         'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
          FND_LOG.LEVEL_STATEMENT); -- put PK column here
Line: 444

    CSM_UTIL_PKG.DELETE_RECORD
      (
        p_user_name,
        p_tranid,
        r_get_req_headers_from_inq.seqno$$,
        r_get_req_headers_from_inq.requirement_header_id, -- put PK column here
        g_object_name,
        g_pub_name2,
        l_error_msg,
        x_return_status
      );
Line: 456

    /*** was delete successful? ***/
    IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
      /*** no -> rollback ***/

      CSM_UTIL_PKG.LOG
       ( 'Deleting from inqueue failed, rolling back to savepoinT'|| 'for PK '||r_get_req_headers_from_inq.requirement_header_id,
         'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
          FND_LOG.LEVEL_PROCEDURE); -- put PK column here
Line: 469

      /*** Record was not processed successfully or delete failed -> defer and reject record ***/

      CSM_UTIL_PKG.LOG
       ( 'Record not processed successfully, deferring and rejecting record'|| 'for PK '||r_get_req_headers_from_inq.requirement_header_id,
         'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
          FND_LOG.LEVEL_PROCEDURE); -- put PK column here
Line: 496

         'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
          FND_LOG.LEVEL_PROCEDURE); -- put PK column here
Line: 505

       ( 'Leaving CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ ',
         'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
          FND_LOG.LEVEL_STATEMENT); -- put PK column here
Line: 514

       ( 'Exception occurred in DELETE_REQ_HEADERS_FROM_INQ: '|| FND_GLOBAL.LOCAL_CHR(10) || sqlerrm,
         'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
          FND_LOG.LEVEL_EXCEPTION); -- put PK column here
Line: 520

END DELETE_REQ_HEADERS_FROM_INQ;
Line: 559

      /*** Yes -> delete record from inqueue ***/
       CSM_UTIL_PKG.LOG
         ( 'Record successfully processed, deleting from inqueue'|| ' for PK ' || r_requirements.requirement_line_id,
          'CSM_REQUIREMENTS_PKG.PROCESS_REQS',
          FND_LOG.LEVEL_PROCEDURE ); -- put PK column here
Line: 567

      CSM_UTIL_PKG.DELETE_RECORD
        (
          p_user_name,
          p_tranid,
          r_requirements.seqno$$,
          r_requirements.requirement_line_id, -- put PK column here
          g_object_name,
          g_pub_name,
          l_error_msg,
          x_return_status
        );
Line: 579

      /*** was delete successful? ***/
      IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
        /*** no -> rollback ***/
        CSM_UTIL_PKG.LOG
         ( 'Deleting from inqueue failed, rolling back to savepoint'|| ' for PK ' || r_requirements.requirement_line_id,
          'CSM_REQUIREMENTS_PKG.PROCESS_REQS',
          FND_LOG.LEVEL_PROCEDURE ); -- put PK column here
Line: 592

      /*** Record was not processed successfully or delete failed -> defer and reject record ***/
      CSM_UTIL_PKG.LOG
         ( 'Record not processed successfully, deferring and rejecting record'|| ' for PK ' || r_requirements.requirement_line_id,
          'CSM_REQUIREMENTS_PKG.PROCESS_REQS',
          FND_LOG.LEVEL_PROCEDURE ); -- put PK column here
Line: 627

  DELETE_REQ_HEADERS_FROM_INQ(p_user_name, p_tranid, x_return_status);
Line: 683

      /*** Yes -> delete record from inqueue ***/
      CSM_UTIL_PKG.LOG
         ( 'Record successfully processed, deleting from inqueue'|| ' for PK ' ||r_requirements.requirement_line_id,
          'CSM_REQUIREMENTS_PKG.PROCESS_REQS_NO_HEADERS',
          FND_LOG.LEVEL_PROCEDURE ); -- put PK column here
Line: 690

      CSM_UTIL_PKG.DELETE_RECORD
        (
          p_user_name,
          p_tranid,
          r_requirements.seqno$$,
          r_requirements.requirement_line_id, -- put PK column here
          g_object_name,
          g_pub_name,
          l_error_msg,
          x_return_status
        );
Line: 702

      /*** was delete successful? ***/
      IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
        /*** no -> rollback ***/
       CSM_UTIL_PKG.LOG
         ( 'Deleting from inqueue failed, rolling back to savepoint'|| ' for PK ' ||r_requirements.requirement_line_id,
           'CSM_REQUIREMENTS_PKG.PROCESS_REQS_NO_HEADERS',
           FND_LOG.LEVEL_PROCEDURE ); -- put PK column here
Line: 715

      /*** Record was not processed successfully or delete failed -> defer and reject record ***/

      CSM_UTIL_PKG.LOG
         ( 'Record not processed successfully, deferring and rejecting record'|| ' for PK ' ||r_requirements.requirement_line_id,
           'CSM_REQUIREMENTS_PKG.PROCESS_REQS_NO_HEADERS',
           FND_LOG.LEVEL_PROCEDURE ); -- put PK column here
Line: 771

  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);