APPS.CSM_DEFERRED_TXNS_PKG SQL Statements

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

Line: 23

 SELECT nfn.DEFERRED_TRAN_ID,nfn.CLIENT_ID, nfn.SEQUENCE,nfn.DML,
        nfn.OBJECT_NAME,pi.PRIMARY_KEY_COLUMN,nfn.OBJECT_ID,instr(pi.PRIMARY_KEY_COLUMN,',')
 INTO old_tranid, old_client_id,old_seq,old_dml,inq_table,inq_pk_col,inq_pk_value,l_proceed
 FROM CSM_DEFERRED_NFN_INFO nfn, ASG_PUB_ITEM pi
 WHERE nfn.tracking_id=p_tracking_id
 AND nfn.OBJECT_NAME = pi.item_id;

Line: 38

 l_qry:='SELECT 1 FROM '||inq_table||'_INQ WHERE '||inq_pk_col||'='''||inq_pk_value||''' AND TRANID$$='
         ||p_tranid||' AND CLID$$CS='''||p_client_id||'''';

Line: 43

 l_qry:='DELETE FROM '||inq_table||'_INQ WHERE '||inq_pk_col||'='''||inq_pk_value||''' AND TRANID$$='
         ||old_tranid||' AND CLID$$CS='''||old_client_id||''' AND SEQNO$$='||old_seq;

Line: 56

 l_qry:= 'UPDATE '||inq_table||'_INQ SET TRANID$$='||old_tranid||',CLID$$CS='''||old_client_id||''' ,'
         ||' SEQNO$$='||old_seq||', DMLTYPE$$='''||old_dml||''' WHERE '||inq_pk_col||'='''||inq_pk_value||''' AND TRANID$$='
         ||p_tranid||' AND CLID$$CS='''||p_client_id||'''';

Line: 91

/* Select all inq records */
CURSOR c_inq_records( b_user_name VARCHAR2, b_tranid NUMBER)
IS
  SELECT *
  FROM  CSM_DEFERRED_TRANSACTIONS_INQ
  WHERE tranid$$ = b_tranid
  AND   clid$$cs = b_user_name;

Line: 101

 SELECT TRACKING_ID,CLIENT_ID FROM  CSM_DEFERRED_NFN_INFO
 connect by prior tracking_id=parent_id
 start with tracking_id=b_tracking_id;

Line: 107

 SELECT DISTINCT DEFERRED_TRAN_ID,CLIENT_ID
 FROM  CSM_DEFERRED_TRANSACTIONS_INQ
 WHERE tranid$$ = b_tranid  AND   clid$$cs = b_name
 AND  ACTION = 'C';

Line: 132

FOR dml_0_rec IN (SELECT TRACKING_ID,SEQNO$$ FROM CSM_DEFERRED_TRANSACTIONS_INQ inq
                  WHERE TRANID$$=p_tranid AND CLID$$CS=p_user_name  AND dmltype$$='D')
LOOP

        CSM_UTIL_PKG.LOG('Delete DML received for tracking Id: '||dml_0_rec.tracking_id||' from -'||p_user_name ||' in txn-'||p_tranid,
                      'CSM_DEFERRED_TXNS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);

Line: 140

        DELETE FROM CSM_DEFERRED_TRANSACTIONS_ACC
		WHERE tracking_id = dml_0_rec.tracking_id
		AND USER_ID=asg_base.get_user_id(p_user_name);

Line: 144

		--delete children
        FOR rec IN (SELECT tracking_id FROM CSM_DEFERRED_NFN_INFO
		            WHERE PARENT_ID=dml_0_rec.tracking_id AND CLIENT_ID=p_user_name)
        LOOP
			CSM_ACC_PKG.Delete_Acc
			 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEFERRED_TRANSACTIONS')
			  ,P_ACC_TABLE_NAME         => 'CSM_DEFERRED_TRANSACTIONS_ACC'
			  ,P_PK1_NAME               => 'TRACKING_ID'
			  ,P_PK1_NUM_VALUE          => rec.tracking_id
			  ,P_USER_ID                => asg_base.get_user_id(p_user_name)
			  );

Line: 157

        DELETE FROM CSM_DEFERRED_NFN_INFO WHERE dml_0_rec.tracking_id IN (TRACKING_ID,PARENT_ID) AND CLIENT_ID=p_user_name;

Line: 159

		CSM_UTIL_PKG.DELETE_RECORD
			(
			  p_user_name,
			  p_tranid,
			  dml_0_rec.seqno$$,
			  dml_0_rec.tracking_id,
			  g_object_name,
			  g_pub_name,
			  l_error_msg,    --OUT variable
			  l_process_status
			);

Line: 180

 FOR clear_rec IN (SELECT TRACKING_ID,SEQNO$$ FROM CSM_DEFERRED_TRANSACTIONS_INQ inq
                   WHERE TRANID$$=p_tranid AND CLID$$CS=p_user_name
                   AND
                   (
                    (ACTION IS NULL OR ACTION NOT IN ('C','D'))
                   OR
                    NOT EXISTS(SELECT 1 FROM CSM_DEFERRED_NFN_INFO b
                               WHERE b.tracking_id=inq.tracking_id)
                   OR
                    (
                    ACTION='C'
                    AND EXISTS(SELECT 1 FROM CSM_DEFERRED_NFN_INFO b
                               WHERE b.tracking_id=inq.tracking_id
                               AND PARENT_ID IS NOT NULL)  /*REAPPLY supported only at ROOT level*/
                    )
                   ))
 LOOP
     CSM_UTIL_PKG.DELETE_RECORD
        (
          p_user_name,
          p_tranid,
          clear_rec.seqno$$,
          clear_rec.tracking_id,
          g_object_name,
          g_pub_name,
          l_error_msg,    --OUT variable
          l_process_status
        );

Line: 210

      SELECT 'S' INTO l_process_status
      FROM CSM_DEFERRED_NFN_INFO
      WHERE tracking_id=clear_rec.tracking_id;

Line: 254

      FOR disc_rec IN (SELECT * FROM  CSM_DEFERRED_NFN_INFO
                       connect by prior tracking_id=parent_id
                       start with tracking_id=def_rec.tracking_id)
      LOOP
        IF(disc_rec.dml='I') THEN
          asg_defer.reject_row(disc_rec.client_id,
                               disc_rec.deferred_tran_id,
                               disc_rec.OBJECT_NAME,
                               disc_rec.sequence,
                               disc_rec.error_msg,   --IN variable of asg api
                               l_process_status);

Line: 276

        CSM_ACC_PKG.Delete_Acc
         ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEFERRED_TRANSACTIONS')
          ,P_ACC_TABLE_NAME         => 'CSM_DEFERRED_TRANSACTIONS_ACC'
          ,P_PK1_NAME               => 'TRACKING_ID'
          ,P_PK1_NUM_VALUE          => disc_rec.tracking_id
          ,P_USER_ID                => asg_base.get_user_id(disc_rec.client_id)
          );

Line: 291

       DELETE FROM CSM_DEFERRED_NFN_INFO WHERE TRACKING_ID = l_track_tab(I);

Line: 298

    /*delete entire tree to reapply so that new defer reports updated error*/
      IF(l_track_tab.count >0) THEN
        l_track_tab.DELETE;

Line: 303

        l_uname_tab.DELETE;

Line: 311

         CSM_ACC_PKG.Delete_Acc
         ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEFERRED_TRANSACTIONS')
          ,P_ACC_TABLE_NAME         => 'CSM_DEFERRED_TRANSACTIONS_ACC'
          ,P_PK1_NAME               => 'TRACKING_ID'
          ,P_PK1_NUM_VALUE          => l_track_tab(I)
          ,P_USER_ID                => asg_base.get_user_id(l_uname_tab(I))
          );

Line: 319

         DELETE FROM CSM_DEFERRED_NFN_INFO WHERE TRACKING_ID = l_track_tab(I);

Line: 325

    CSM_UTIL_PKG.DELETE_RECORD
       (
         p_user_name,
         p_tranid,
         def_rec.seqno$$,
         def_rec.tracking_id,
         g_object_name,
         g_pub_name,
         l_error_msg,    --OUT variable
         l_process_status
        );

Line: 350

l_qry := 'DELETE FROM  ' ||asg_base.G_OLITE_SCHEMA ||'.C$INQ '
     ||' WHERE STORE='''||g_pub_name||''' AND TRANID$$='||p_tranid||' AND CLID$$CS= '''||p_user_name||'''';

Line: 373

	  FOR root_rec IN (SELECT TRACKING_ID FROM  CSM_DEFERRED_NFN_INFO
	                   WHERE CLIENT_ID=p_user_name AND DEFERRED_TRAN_ID=p_tranid
	                   AND PARENT_ID IS NULL)
	  LOOP
        FOR disc_rec IN (SELECT * FROM  CSM_DEFERRED_NFN_INFO
                         connect by prior tracking_id=parent_id
                         start with tracking_id=root_rec.tracking_id)
        LOOP
          IF(disc_rec.dml='I') THEN
            asg_defer.reject_row(disc_rec.client_id,
                                 disc_rec.deferred_tran_id,
                                 disc_rec.OBJECT_NAME,
                                 disc_rec.sequence,
                                 disc_rec.error_msg,   --IN variable of asg api
                                 x_return_status);

Line: 390

          CSM_ACC_PKG.Delete_Acc
           ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEFERRED_TRANSACTIONS')
            ,P_ACC_TABLE_NAME         => 'CSM_DEFERRED_TRANSACTIONS_ACC'
            ,P_PK1_NAME               => 'TRACKING_ID'
            ,P_PK1_NUM_VALUE          => disc_rec.tracking_id
            ,P_USER_ID                => asg_base.get_user_id(disc_rec.client_id)
            );

Line: 401

      DELETE FROM CSM_DEFERRED_NFN_INFO
	  WHERE CLIENT_ID=p_user_name
	  AND   DEFERRED_TRAN_ID=p_tranid;

Line: 430

      SELECT TRACKING_ID INTO l_tracking_id
      FROM  CSM_DEFERRED_NFN_INFO
	  WHERE CLIENT_ID=p_user_name
	  AND   DEFERRED_TRAN_ID=p_tranid
	  AND   SEQUENCE = p_sequence
	  AND   OBJECT_NAME = p_pubitem;

Line: 438

      FOR disc_rec IN (SELECT * FROM  CSM_DEFERRED_NFN_INFO
                       connect by prior tracking_id=parent_id
                       start with tracking_id=l_tracking_id)
      LOOP
        IF(disc_rec.dml='I') THEN
          asg_defer.reject_row(disc_rec.client_id,
                               disc_rec.deferred_tran_id,
                               disc_rec.OBJECT_NAME,
                               disc_rec.sequence,
                               disc_rec.error_msg,   --IN variable of asg api
                               x_return_status);

Line: 451

        CSM_ACC_PKG.Delete_Acc
         ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEFERRED_TRANSACTIONS')
          ,P_ACC_TABLE_NAME         => 'CSM_DEFERRED_TRANSACTIONS_ACC'
          ,P_PK1_NAME               => 'TRACKING_ID'
          ,P_PK1_NUM_VALUE          => disc_rec.tracking_id
          ,P_USER_ID                => asg_base.get_user_id(disc_rec.client_id)
          );

Line: 465

       DELETE FROM CSM_DEFERRED_NFN_INFO WHERE TRACKING_ID = l_track_tab(I);