APPS.CSM_REQUIREMENTS_PKG SQL Statements

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

Line: 20

  SELECT *
  FROM   CSM_REQ_LINES_INQ l
  WHERE  l.tranid$$ = b_tranid
  AND    l.clid$$cs = b_user_name
  AND    l.requirement_header_id = b_req_hdr_id;

Line: 27

  SELECT distinct l.requirement_header_id
  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: 38

  SELECT distinct reqh.requirement_header_id
  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
  AND    NOT EXISTS(SELECT 1 FROM ASG_DEFERRED_TRANINFO a   --hz_location pkg can defer hdr/lines
				    WHERE device_user_name=reqh.clid$$cs
			        AND deferred_tran_id=reqh.tranid$$
                    AND SEQUENCE=reqh.seqno$$);

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_line_table  IN OUT NOCOPY CSP_PARTS_REQUIREMENT.LINE_TBL_TYPE,
           p_user_name           IN      VARCHAR2,
           p_tranid              IN      NUMBER,
           p_get_inqueue_header  IN      BOOLEAN,
		   p_req_hid              IN      NUMBER,
           p_error_msg           OUT NOCOPY     VARCHAR2,
           x_return_status       IN OUT NOCOPY  VARCHAR2
         ) IS

  l_header_rec   CSP_PARTS_REQUIREMENT.HEADER_REC_TYPE;

Line: 78

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

    SELECT *
    FROM   CSP_REQUIREMENT_HEADERS
    WHERE  requirement_header_id = b_requirement_header_id;

Line: 105

       ( 'Entering CSM_REQUIREMENTS_PKG.APPLY_INSERT for PK '||p_req_hid,
         'CSM_REQUIREMENTS_PKG.APPLY_INSERT',
          FND_LOG.LEVEL_STATEMENT );

Line: 199

         'CSM_REQUIREMENTS_PKG.APPLY_INSERT', FND_LOG.LEVEL_STATEMENT );

Line: 219

	   , 'CSM_REQUIREMENTS_PKG.APPLY_INSERT', FND_LOG.LEVEL_PROCEDURE );

Line: 225

	SELECT user_id,responsibility_id, app_id
	INTO  l_user_id,l_resp_id,l_app_id
	FROM  asg_user WHERE user_name=p_user_name;

Line: 243

	   , 'CSM_REQUIREMENTS_PKG.APPLY_INSERT', FND_LOG.LEVEL_PROCEDURE );

Line: 252

       ( 'Leaving CSM_REQUIREMENTS_PKG.APPLY_INSERT for req_header_id: '||p_req_hid,
         'CSM_REQUIREMENTS_PKG.APPLY_INSERT',
          FND_LOG.LEVEL_STATEMENT );

Line: 260

     ( 'Exception occurred in CSM_REQUIREMENTS_PKG.APPLY_INSERT for req_header_id: '||p_req_hid || FND_GLOBAL.LOCAL_CHR(10) ||  substr(sqlerrm,0,2000),
       'CSM_REQUIREMENTS_PKG.APPLY_INSERT',
        FND_LOG.LEVEL_EXCEPTION );

Line: 265

END APPLY_INSERT;

Line: 268

   This procedure is called by APPLY_CLIENT_CHANGES 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: 282

    This one is to be executed after all requirement lines with headers have been deleted from the inqueue.
  ***/
  CURSOR c_get_req_headers_processed ( 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: 297

    SELECT *
    FROM   CSM_REQ_HEADERS_INQ h
    WHERE  h.tranid$$ = b_tranid
    AND    h.clid$$cs = b_user_name
    AND    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
                    )
    AND    NOT EXISTS(SELECT 1 FROM ASG_DEFERRED_TRANINFO a   --hz_location pkg can also defer hdr/lines
				  WHERE device_user_name=b_user_name
			      AND deferred_tran_id=b_tranid
                  AND SEQUENCE=h.seqno$$);

Line: 314

  CSM_UTIL_PKG.LOG( 'Entering CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',        'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ', FND_LOG.LEVEL_STATEMENT);

Line: 321

    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,
        g_object_name,
        g_pub_name2,
        l_error_msg,
        x_return_status
      );

Line: 333

    /*** 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 req header id: '||r_get_req_headers_from_inq.requirement_header_id,
         'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',FND_LOG.LEVEL_PROCEDURE);

Line: 350

         'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
          FND_LOG.LEVEL_PROCEDURE);

Line: 372

		||r_get_req_headers_from_inq.requirement_header_id,'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ', FND_LOG.LEVEL_PROCEDURE);

Line: 378

  CSM_UTIL_PKG.LOG( 'Leaving CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ ','CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
          FND_LOG.LEVEL_STATEMENT);

Line: 385

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

Line: 388

END DELETE_REQ_HEADERS_FROM_INQ;

Line: 466

    APPLY_INSERT
      (
	   l_line_table
      , p_user_name
      , p_tranid
      , p_header_in_inq
	  , p_req_hid
      , l_error_msg
      , x_return_status
      );

Line: 481

		  /*** Yes -> delete record from inqueue ***/
		   CSM_UTIL_PKG.LOG( 'Record successfully processed, deleting from inqueue req line id :' || r_requirements.requirement_line_id,'CSM_REQUIREMENTS_PKG.APPLY_RECORD', FND_LOG.LEVEL_PROCEDURE );

Line: 485

		  -- Delete the requirement line from the requirement line inqueue.
		  CSM_UTIL_PKG.DELETE_RECORD
			(
			  p_user_name,
			  p_tranid,
			  r_requirements.seqno$$,
			  r_requirements.requirement_line_id,
			  g_object_name,
			  g_pub_name,
			  l_error_msg,
			  l_return_status
			);

Line: 498

		  /*** was delete successful? ***/
			  IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
				/*** no -> rollback ***/
				CSM_UTIL_PKG.LOG( 'Deleting from inqueue failed, rolling back to savepoint for req_line_id ' || r_requirements.requirement_line_id,'CSM_REQUIREMENTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_PROCEDURE );

Line: 509

		  /*** 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 req_line_id:' || r_requirements.requirement_line_id,'CSM_REQUIREMENTS_PKG.APPLY_RECORD', FND_LOG.LEVEL_PROCEDURE );

Line: 553

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

  DELETE_REQ_HEADERS_FROM_INQ(p_user_name, p_tranid, l_process_status);