APPS.CSM_HTML5_PKG SQL Statements

Line: 1615

	 UPDATE CSM_HTML5_CLIENTS_INFO SET PRF_NAME='INDXREF' WHERE USER_NAME=p_user_name
	 AND PRF_NAME LIKE 'INDXREF-TID-%';

Line: 1620

	 --remove successfully deleted DMLs from SDQ
     DELETE FROM asg_delete_queue
	 WHERE qid IN (SELECT qid
	  			   FROM asg_system_dirty_queue
				   WHERE client_id =  p_user_name AND
				   (transaction_id < p_tranid OR pub_item in (SELECT NAME FROM ASG_PUB_ITEM WHERE nvl(html5_offline,'N')='N' AND PUB_NAME='SERVICEP')));

Line: 1628

	 DELETE FROM asg_system_dirty_queue
	 WHERE client_id = p_user_name  AND
	 (transaction_id < p_tranid OR pub_item in (SELECT NAME FROM ASG_PUB_ITEM WHERE nvl(html5_offline,'N')='N' AND PUB_NAME='SERVICEP'));

Line: 1633

	 UPDATE asg_system_dirty_queue SET transaction_id=p_tranid
	 WHERE client_id = p_user_name and rownum<=2000;		   --inc sync processes only max 2000 records(exclusive of comp ref)  per sync

Line: 1647

    for pi in (SELECT NAME FROM ASG_PUB_ITEM WHERE nvl(html5_offline,'N')='Y')
    loop
     set_comp_ref(p_user_name,pi.name);

Line: 1655

  select useR_name,language,resource_id,useR_id,responsibility_id,app_id,lasT_synch_date_end
  into l_uname,l_lang,lrid,l_uid,l_respid,l_app_id,l_synch_date from asg_user where user_name=upper(p_user_name);

Line: 1661

	  for pi in (SELECT a.NAME FROM ASG_PUB_ITEM a,CSM_HTML5_CLIENTS_INFO b
				WHERE nvl(html5_offline,'N')='Y'
				AND PRF_NAME='COMPREF'
				AND PRF_VALUE=a.NAME
				AND b.USER_NAME=p_user_name ORDER BY a.NAME)
	  loop
		l_final:=l_final||g_nl_chr||complete_refresh(l_session_id,p_user_name,pi.name,l_total_r_count,l_warning,l_dummy)||g_nl_chr;

Line: 1672

		UPDATE CSM_HTML5_CLIENTS_INFO SET PRF_NAME='COMPREF-TID-'||p_tranid
		WHERE USER_NAME=p_user_name AND PRF_NAME='COMPREF'	AND PRF_VALUE= pi.name; --check and clear in next sync

Line: 1681

 UPDATE CSM_HTML5_CLIENTS_INFO
 SET PRF_NAME='INDXREF-TID-'||p_tranid
 WHERE USER_NAME=p_user_name AND PRF_NAME='INDXREF';  --check and  clear in next sync

Line: 1687

 for rec in (select b.pub_item,b.access_id,b.dml_type,base_object_name,a.primary_key_column
  		     from asg_pub_item a,
		          (select pub_item,access_id,client_id,min(dml_type) as dml_type
			       from asG_system_dirty_queue
                   where client_id=p_useR_name
				   and  transaction_id = p_tranid
				   and  nvl(download_flag,'Y')='Y'  --Bug 16456574: process conflicts
                   and pub_item in (SELECT NAME FROM ASG_PUB_ITEM WHERE nvl(html5_offline,'N')='Y')
                   group by pub_item,access_id,client_id)  b
             where b.pub_item=a.item_id
			 order by b.pub_item)
 LOOP

   CSM_UTIL_PKG.LOG('Found/Processing PI -'||rec.pub_item||' with access_id:'||rec.access_id, 'CSM_HTML5_PKG.q_inc_sync_data_js', FND_LOG.LEVEL_PROCEDURE);

Line: 1704

     UPDATE_SESSION_DETAILS(l_session_id,l_last_pi,l_rec_count,l_dummy);

Line: 1719

     l_final:=l_final ||'appsdb.delete_record("DELETE FROM '||rec.pub_item||' WHERE ACCESS_ID='||to_char(rec.access_id)||'",[],"doProgress(++sqlR,'''||rec.pub_item||''');");'||g_nl_chr;

Line: 1735

  	    l_COL_NAME_IDX.DELETE;l_COL_CLOB_LIST.DELETE;

Line: 1738

 	   IF (rec.dml_type = 1)  THEN   --INSERT dml

	     l_stmt:='';

Line: 1742

	     IF NOT l_insertDML_pk_list.exists(rec.pub_item) THEN
  		   l_insertDML_pk_list(rec.pub_item):=rec.primary_key_column;

Line: 1745

		   l_insertDML_pi_list(l_pp_cnt):=rec.pub_item;

Line: 1750

	               ||'"INSERT INTO '||rec.pub_item||'('||l_COL_NAME_LIST(1);

Line: 1789

       l_stmt := 'appsdb.update_record("UPDATE '||rec.pub_item||' SET ';

Line: 1844

      FOR I IN 1..l_insertDML_pi_list.COUNT
		LOOP
		  l_final:=l_final||'sync.clearOldInserts("'||l_insertDML_pi_list(I)||'","'||l_insertDML_pk_list(l_insertDML_pi_list(I))||'");'||g_nl_chr;

Line: 1850

		 UPDATE_SESSION_DETAILS(l_session_id,l_last_pi,l_rec_count,l_dummy);

Line: 1865

	 DELETE FROM csm_html5_clients_info WHERE USER_NAME=p_user_name AND PRF_NAME like 'REUSABLE%';

Line: 1886

      delete from csm_html5_clients_info where user_name=p_user_name and prf_name='REUSE_AUTH_9';

Line: 1893

		   || ' if(d) { head.removeChild(d); delete(d);} delete_sync_element(); }); '||g_nl_chr||g_nl_chr

Line: 1917

      delete from csm_html5_clients_info where user_name=p_user_name and prf_name='REUSE_AUTH_9';

Line: 1933

 UPDATE ASG_PUB_ITEM SET HTML5_OFFLINE='Y' WHERE ITEM_ID=p_pi;

Line: 1935

 DELETE FROM csm_html5_clients_info WHERE PRF_NAME like 'REUSABLE%' OR  PRF_NAME like 'SCHEMA%REUSE%';

Line: 1941

 UPDATE ASG_PUB_ITEM SET HTML5_OFFLINE='N' WHERE ITEM_ID=p_pi;

Line: 1942

 DELETE FROM csm_html5_clients_info WHERE PRF_NAME like 'REUSABLE%';

Line: 1959

SELECT 1
FROM jtm_con_request_data
WHERE product_code = 'CSM'
AND package_name = 'CSM_HTML5_PKG'
AND procedure_name = 'PURGE_HTML5_DATA'
FOR UPDATE OF last_run_date NOWAIT;

Line: 1967

select profile_option_value from fnd_profile_option_values where profile_option_id in
(select profile_option_id from fnd_profile_options where profile_option_name='CSM_PURGE_INTERVAL')
and level_id=10001;

Line: 1977

  DELETE FROM CSM_HTML5_CLIENTS_INFO a WHERE  USER_NAME<>'APPLICATION' AND NOT EXISTS (SELECT 1 FROM ASG_USER au WHERE au.USER_NAME=a.USER_NAME);

Line: 1982

		 delete from csm_html5_ajax_info where creation_date < (sysdate-l_days);

Line: 1983

		 delete from csm_html5_sync_info where start_date < (sysdate-l_days);

Line: 1984

		 delete from csm_html5_sync_details a where not exists(select 1 from csm_html5_sync_info b where a.session_id=b.session_id);

Line: 1985

         delete from csm_query_instances_acc
					 where creation_date < (sysdate-l_days)
					 and query_id between 1 and 99
					 and instance_id not in (select to_number(prf_value) from csm_html5_clients_info WHERE PRF_NAME='SCHEMA_REUSE' OR PRF_NAME like 'REUSABLE%' OR PRF_NAME LIKE 'USER_CREAT%')
					 and not exists (select 1 from csm_html5_sync_info where instance_id=q_instance_id);

Line: 1990

    	 delete from csm_query_results_acc where instance_id not in ( select instance_id from csm_query_instances_acc where query_id between 1 and 99 );

Line: 1999

     UPDATE jtm_con_request_data
     SET last_run_date = l_last_run_date
     WHERE CURRENT OF l_upd_last_run_date_csr;

Line: 2024

 SELECT USER_ID INTO x FROM ASG_USER
 WHERE USER_NAME=p_user_name
 AND ENABLED='Y'
 AND MULTI_PLATFORM='Y';

Line: 2060

  SELECT client_number INTO l_client_num
  FROM asg_user
  WHERE user_name = p_user_name;

Line: 2070

	UPDATE asg_sequence_partitions SET curr_val = l_curr_val
	WHERE CLIENTID = p_user_name
	AND name = l_seq_name
	AND curr_val < l_curr_val;

Line: 2075

	CSM_UTIL_PKG.log ('Updated sequence for user: ' || p_user_name ||'\n sequence: ' || l_seq_name || '\n Seq value: ' || l_curr_val, 'CSM_HTML5_PKG.process_sequences',FND_LOG.LEVEL_PROCEDURE);

Line: 2090

    SELECT sequence
    FROM asg_conf_info
    WHERE user_name = p_user_name AND
          transaction_id = p_upload_tranid AND
          pub_item = p_pubitem AND
          sequence IS NOT NULL;

Line: 2106

    SELECT conflict_callout  INTO l_conflict_callout
    FROM asg_pub_item  WHERE name = p_pubitem;

Line: 2113

    insert into asg_conf_info (user_name,
                               pub_item,
                               transaction_id,
                               access_id,
							   sequence,
                               resolution,
                               creation_date,
                               created_by,
                               last_update_date,
                               last_updated_by)
    SELECT p_user_name, p_pubitem, p_upload_tranid, access_id,b.sequence, l_client_wins,
           sysdate, 1, sysdate, 1
    FROM asg_system_dirty_queue a, CSM_HTML5_PARSED_DATA b
    WHERE client_id = p_user_name AND  pub_item = p_pubitem AND
          transaction_id is NULL AND a.dml_type = 2
		  AND b.session_id=p_upload_tranid AND b.piv_name=a.pub_item AND a.access_id=b.pk_value AND b.dml_type='U'
		  AND NOT EXISTS(SELECT 1 FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME=p_user_name AND PRF_NAME='COMPREF' AND PRF_VALUE=p_pubitem);

Line: 2147

        l_query_string := 'SELECT ' || l_conflict_callout ||'(:1,:2,:3) from dual';

Line: 2159

          UPDATE asg_conf_info
          SET resolution = l_conf_resolution
          WHERE user_name = p_user_name AND
                transaction_id = p_upload_tranid AND
                pub_item = p_pubitem AND
                sequence = l_sequence;

Line: 2171

    UPDATE asg_system_dirty_queue
    SET download_flag = 'N'
    WHERE client_id = p_user_name AND
          pub_item = p_pubitem AND
          transaction_id is NULL AND
          dml_type = 2 AND
          access_id in (select access_id
                        FROM asg_conf_info
                        WHERE user_name = p_user_name AND
                              transaction_id = p_upload_tranid AND
                              pub_item = p_pubitem AND
                              sequence IS NOT NULL AND
                              resolution = l_client_wins);

Line: 2192

	FOR rec IN (SELECT api.item_id
				FROM  csm_html5_inq ci, asg_pub_item api
				WHERE ci.user_name = p_user_name
				AND   ci.sync_id = p_upload_tran_id
				AND   ci.pi_name = api.name
				AND   api.detect_conflict = 'Y')
	LOOP
	  CSM_UTIL_PKG.LOG('Processing ' ||rec.item_id || ' for conflicts.', 'CSM_HTML5_PKG.process_conflicts', FND_LOG.LEVEL_PROCEDURE);

Line: 2222

 For rec IN (select a.session_id from csm_html5_upload_data a, csm_html5_sync_info c
             where a.status<>'PARSED' and a.session_id=c.session_id
             and c.useR_name=p_user_name order by session_id asc)
 loop
   CSM_HTML5_PKG.HTML5_PARSE_XML(rec.SESSION_ID,x_return_status,x_error_message);

Line: 2231

		  UPDATE CSM_HTML5_UPLOAD_DATA
		  SET   LAST_UPDATE_DATE  = SYSDATE,
				LAST_UPDATED_BY    = 1,
				STATUS            = 'PARSE_ERROR',
				COMMENTS          =  x_error_message
		  WHERE SESSION_ID     = rec.SESSION_ID;

Line: 2306

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

	INSERT INTO CSM_HTML5_UPLOAD_DATA(SESSION_ID,STATUS,COMMENTS,UPLOADED_PAYLOAD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
	VALUES(l_session_id,'RECEIVED','Sync successfully dumped the data',p_sync_data,l_user_id,SYSDATE,l_user_id,SYSDATE);

Line: 2326

		  UPDATE CSM_HTML5_UPLOAD_DATA
		  SET   LAST_UPDATE_DATE  = SYSDATE,
				LAST_UPDATED_BY    = 1,
				STATUS            = 'PARSE_ERROR',
				COMMENTS          =  l_err
		  WHERE SESSION_ID     = l_session_id;

Line: 2335

	    FOR rec IN (SELECT PIV_NAME,COUNT(1) cnt FROM CSM_HTML5_PARSED_DATA
		            WHERE SESSION_ID=l_session_id GROUP BY PIV_NAME ORDER BY PIV_NAME)
		LOOP
		  UPDATE_SESSION_DETAILS(l_session_id,rec.PIV_NAME,rec.cnt,l_dummy);

Line: 2341

	    SELECT COUNT(*) INTO req_id FROM CSM_HTML5_PARSED_DATA
		WHERE SESSION_ID=l_session_id AND STATUS='PARSE_ERROR';

Line: 2366

	  CSM_QUERY_PKG.INSERT_INSTANCE(asg_base.get_user_id(p_user_name), 16, NULL, 'SYNCHRONOUS_UPLOAD',  l_id, l_ch, l_dt,NULL, instId, ret, m);

Line: 2371

			UPDATE csm_html5_sync_info SET Q_INSTANCE_ID=instId WHERE SESSION_ID=l_session_id;

Line: 2422

  select responsibility_id ,application_id into l_resp_id ,l_app_id
  from fnd_responsibility a, fnd_oracle_userid b
  where responsibility_key='OMFS_PALM'  and a.application_id=b.oracle_id
  and b.oracle_username=l_app_name ;

Line: 2445

PROCEDURE set_app_revision(p_version IN VARCHAR2,p_force_update IN VARCHAR2 :='N')
IS
l_cur_version VARCHAR2(100):=get_app_revision;

Line: 2451

   INSERT INTO csm_html5_clients_info(USER_NAME,PRF_NAME,PRF_VALUE)
   VALUES('APPLICATION','REVISION',p_version);

Line: 2454

   UPDATE csm_html5_clients_info  SET PRF_VALUE=p_version
   WHERE USER_NAME='APPLICATION' AND PRF_NAME='REVISION';

Line: 2457

   UPDATE csm_html5_clients_info  SET PRF_VALUE=p_force_update
   WHERE USER_NAME='APPLICATION' AND PRF_NAME='FORCE_UPDATE';

Line: 2460

	   INSERT INTO csm_html5_clients_info(USER_NAME,PRF_NAME,PRF_VALUE)
	   VALUES('APPLICATION','FORCE_UPDATE',p_force_update);

Line: 2471

 SELECT MAX(PRF_VALUE) into x FROM csm_html5_clients_info
 WHERE USER_NAME='APPLICATION' AND PRF_NAME='REVISION';

Line: 2475

   UPDATE csm_html5_clients_info  SET PRF_VALUE=p_v
   WHERE USER_NAME=P_U AND PRF_NAME='LAST_SYNC_APP_VERSION';

Line: 2478

	   INSERT INTO csm_html5_clients_info(USER_NAME,PRF_NAME,PRF_VALUE)
	   VALUES(P_U,'LAST_SYNC_APP_VERSION',p_v);

Line: 2491

 SELECT PRF_VALUE into x FROM csm_html5_clients_info
 WHERE USER_NAME=p_user_name AND PRF_NAME='APP_REVISION';

Line: 2494

 DELETE FROM csm_html5_clients_info
 WHERE USER_NAME=p_user_name AND PRF_NAME='APP_REVISION';

Line: 2499

 SELECT PRF_VALUE into x FROM csm_html5_clients_info
 WHERE USER_NAME='APPLICATION' AND PRF_NAME='REVISION';

Line: 2535

  SELECT '1' INTO x FROM ASG_SYSTEM_DIRTY_QUEUE sdq WHERE CLIENT_ID=p_USER_NAME AND ROWNUM<2
  AND EXISTS(SELECT 1 FROM ASG_PUB_ITEM pi WHERE pi.item_id=sdq.pub_item and nvl(html5_offline,'N')='Y');

Line: 2538

  DELETE FROM csm_html5_clients_info WHERE USER_NAME=p_USER_NAME AND PRF_NAME like 'REUSABLE%';

Line: 2546

    SELECT PRF_VALUE into x FROM csm_html5_clients_info
    WHERE USER_NAME=p_USER_NAME AND PRF_NAME='USER_CREATION_DATA';

Line: 2548

    DELETE FROM csm_html5_clients_info WHERE USER_NAME=p_USER_NAME AND PRF_NAME='USER_CREATION_DATA';

Line: 2556

   SELECT PRF_VALUE into x FROM csm_html5_clients_info
   WHERE USER_NAME=p_USER_NAME AND PRF_NAME='REUSABLE_'||p_QUERY_ID;

Line: 2560

  select '-2' into x from csm_html5_clients_info where user_name=p_user_name and prf_name='REUSE_AUTH_'||p_QUERY_ID;

Line: 2576

  select 1 into x from csm_html5_clients_info where user_name=p_user_name and prf_name='REUSE_AUTH_'||p_QUERY_ID;

Line: 2577

  delete from csm_html5_clients_info where user_name=p_user_name and prf_name='REUSE_AUTH_'||p_QUERY_ID;

Line: 2597

  CSM_QUERY_PKG.INSERT_INSTANCE(asg_base.get_user_id(p_user_name), 12, NULL, 'GENERATE_USER_DATA',  l_id, l_ch, l_dt,NULL, instId, ret, m);

Line: 2628

    L_STMT := 'INSERT INTO '||P_TABLE_NAME||'(';

Line: 2673

SELECT UPLOADED_PAYLOAD
FROM   CSM_HTML5_UPLOAD_DATA
WHERE  SESSION_ID = sync_id
AND    STATUS<>'PARSED';

Line: 2680

SELECT au.USER_NAME, USER_ID, RESPONSIBILITY_ID, APP_ID
FROM  CSM_HTML5_SYNC_INFO si, ASG_USER au
WHERE SESSION_ID =SYNC_ID
AND   au.USER_NAME =si.USER_NAME;

Line: 2722

 SELECT upper(VALUE)
 FROM NLS_SESSION_PARAMETERS
 WHERE PARAMETER='NLS_DATE_FORMAT';

Line: 2758

  l_first_lst := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(L_XML_DOC),'/SYNC_DATA/PIV');

Line: 2761

  l_sec_lst   := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(L_XML_DOC),'/SYNC_DATA/PIV/DATA');

Line: 2797

	   l_COL_NAME_IDX.DELETE;l_COL_CLOB_LIST.DELETE;

Line: 2824

		   SELECT 1 INTO dummy FROM csm_html5_inq
		   WHERE USER_NAME=L_SYNC_USER_NAME AND pi_name=L_PIV_NAME AND SYNC_ID=p_SYNC_ID;

Line: 2828

		   INSERT INTO csm_html5_inq (USER_NAME,PI_NAME,SYNC_ID) VALUES(L_SYNC_USER_NAME,L_PIV_NAME,p_SYNC_ID);

Line: 2831

		  INSERT INTO CSM_HTML5_PARSED_DATA(SESSION_ID,SEQUENCE, PIV_NAME, PK_VALUE,
					  DML_TYPE, STATUS, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
					  VALUES(P_SYNC_ID,l_seq,L_PIV_NAME,L_ACCESS_ID,L_DML_TYPE,'MOVED_TO_INQ',SYSDATE,L_USER_ID,SYSDATE,L_USER_ID);

Line: 2840

	  INSERT INTO CSM_HTML5_PARSED_DATA(SESSION_ID,SEQUENCE, PIV_NAME, PK_VALUE,
			DML_TYPE,PAYLOAD, STATUS,COMMENTS, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
	  VALUES(P_SYNC_ID,l_seq,L_PIV_NAME,L_ACCESS_ID,L_DML_TYPE,PART_XML,'PARSE_ERROR',X_ERROR_MESSAGE,SYSDATE,L_USER_ID,SYSDATE,L_USER_ID);

Line: 2848

  UPDATE CSM_HTML5_UPLOAD_DATA
  SET   LAST_UPDATE_DATE  = SYSDATE,
        LAST_UPDATED_BY    = 1,
        STATUS            = 'PARSED',
        COMMENTS          = 'Uploaded Data Parsed Successfully'
  WHERE SESSION_ID     = P_SYNC_ID;

Line: 2856

  UPDATE asg_user  SET hwm_tranid = P_SYNC_ID WHERE user_name = L_SYNC_USER_NAME;

Line: 2892

  l_select_store_sqlstring VARCHAR2(512);

Line: 2893

  l_select_inqcnt_sqlstring VARCHAR2(512);

Line: 2894

  l_delete_inq_sqlstring VARCHAR2(512);

Line: 2903

    SELECT resource_id
    FROM asg_user
    WHERE user_name = p_user_name;

Line: 2910

    l_select_inqcnt_sqlstring :=
      'SELECT count(*) FROM CSM_HTML5_INQ ci ' ||
      'WHERE user_name = :1 AND ' ||
      '      sync_id = :2 AND ' ||
      '	     pi_name in (select item_id from asg_pub_item) ' ||
      'AND NOT EXISTS (SELECT 1 FROM asg_users_inqinfo  ' ||
      ' WHERE device_user_name = ci.user_name AND TRANID =ci.sync_id) ' ;

Line: 2918

    EXECUTE IMMEDIATE l_select_inqcnt_sqlstring INTO l_inq_count USING p_user_name, p_tranid;

Line: 2931

      INSERT INTO asg_users_inqinfo (device_user_name,
                                     resource_id,
                                     tranid,
                                     sync_date,
                                     processed,
                                     deferred,
                                     archive,
                                     last_update_date,
                                     last_updated_by,
                                     creation_date,
                                     created_by)
              VALUES (p_user_name,
                      l_resource_id,
                      p_tranid,
                      sysdate,
                      'N',
                      'N',
                      'Y',
                      SYSDATE,
                      1,
                      SYSDATE,
                      1);

Line: 2961

      l_select_store_sqlstring :=
        'SELECT pi_name FROM CSM_HTML5_INQ ci '||
        'WHERE user_name = :1 AND ' ||
        '      sync_id = :2 AND ' ||
	'      pi_name in (select item_id from asg_pub_item) '||
        ' AND NOT EXISTS (SELECT 1 FROM asg_users_inqarchive  ' ||
        ' WHERE device_user_name = ci.user_name AND TRANID =ci.sync_id) '||
        ' ORDER BY pi_name';

Line: 2971

      DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);

Line: 3012

      INSERT INTO asg_users_inqarchive (device_user_name,
                                        resource_id,
                                        tranid,
                                        sync_date,
                                        processed,
                                        deferred,
                                        pub_items1,
                                        pub_items2,
                                        last_update_date,
                                        last_updated_by,
                                        creation_date,
                                        created_by)
               VALUES (p_user_name,
                       l_resource_id,
                       p_tranid,
                       sysdate,
                       'N',
                       'N',
                       l_pubitems_1,
                       l_pubitems_2,
                       SYSDATE,
                       1,
                       SYSDATE,
                       1);

Line: 3063

    SELECT resource_id
    FROM asg_user
    WHERE user_name = p_user_name;

Line: 3069

  select primary_key_column from asg_pub_item where item_id=b_pi  and instr(primary_key_column,',')=0;

Line: 3108

		  OPEN c_cur FOR 'SELECT DMLTYPE$$,SEQNO$$ ,to_char('||l_pk||') FROM '||inq_tbl_name||' a WHERE clid$$cs='''||p_user_name||''' and TRANID$$='||p_tranid
		                   ||'and not exists (SELECT 1 FROM asg_deferred_traninfo WHERE device_user_name = a.clid$$cs AND  deferred_tran_id = a.tranid$$ and sequence=a.seqno$$ AND object_name ='''
						   ||curr_pubitem||''')';

Line: 3120

      sql_string := 'INSERT INTO asg_deferred_traninfo ('||
                                         'DEVICE_USER_NAME, ' ||
                                         'RESOURCE_ID, ' ||
                                         'DEFERRED_TRAN_ID, ' ||
                                         'MOBILE_ERROR_ID, ' ||
                                         'ERROR_DESCRIPTION, ' ||
                                         'OBJECT_NAME, ' ||
                                         'SEQUENCE, ' ||
                                         'STATUS, ' ||
                                         'SYNC_TIME, ' ||
                                         'FAILURES, ' ||
                                         'LAST_UPDATE_DATE, ' ||
                                         'LAST_UPDATED_BY, ' ||
                                         'CREATION_DATE, ' ||
                                         'CREATED_BY) ' ||
             'SELECT :1, :2, :3, NULL,''Row deferred because it was left unprocessed'', :4,seqno$$, 1,NULL,1,SYSDATE,1,SYSDATE,1 ' ||
             ' FROM  '|| inq_tbl_name ||
             ' b WHERE b.clid$$cs = :5 AND ' ||
             ' tranid$$ = :6 AND ' ||
             ' b.seqno$$ not in (SELECT sequence ' ||
                                 'FROM asg_deferred_traninfo ' ||
                                 'WHERE device_user_name = :7 AND ' ||
                                 '  deferred_tran_id = :8 '||
                                 ' AND object_name = :9)';

Line: 3159

	  DELETE FROM CSM_HTML5_INQ WHERE USER_NAME=p_user_name AND SYNC_ID=p_tranid AND PI_NAME=curr_pubitem;

Line: 3164

      UPDATE asg_users_inqinfo
      SET deferred = 'Y', processed = 'I',
        last_update_date = SYSDATE, last_updated_by = 1
      WHERE device_user_name = p_user_name AND
        tranid = p_tranid;

Line: 3180

   CURSOR l_c IS SELECT PI_NAME FROM CSM_HTML5_INQ WHERE USER_NAME=p_user_name AND SYNC_ID=p_tranid;

Line: 3201

    UPDATE asg_users_inqinfo
    SET processed = 'Y', last_update_date=SYSDATE, last_updated_by=1
    WHERE device_user_name = p_user_name AND
          tranid = p_tranid AND
          tranid not IN
          (SELECT distinct deferred_tran_id
           FROM asg_deferred_traninfo
           WHERE device_user_name = p_user_name AND
                 deferred_tran_id = p_tranid);

Line: 3223

    SELECT tranid FROM asg_users_inqinfo
    WHERE device_user_name = p_user_name AND
          tranid <= p_max_tranid;

Line: 3248

		DELETE FROM CSM_HTML5_PARSED_DATA a
		WHERE A.SESSION_ID=curr_tranid
		AND a.status='MOVED_TO_INQ'
		AND NOT EXISTS (SELECT 1 FROM  asg_deferred_traninfo b
						WHERE a.session_id=b.DEFERRED_TRAN_ID
						AND a.sequence=b.sequence);  --reject record requires the data if deferred

Line: 3257

	DELETE FROM CSM_HTML5_UPLOAD_DATA b
	WHERE SESSION_ID <= g_current_tranid
	AND NOT EXISTS(SELECT 1 FROM CSM_HTML5_PARSED_DATA a WHERE a.SESSION_ID=b.SESSION_ID);

Line: 3270

    SELECT tranid, processed, deferred, archive
    FROM asg_users_inqinfo
    WHERE device_user_name = p_user_name;

Line: 3290

        UPDATE asg_users_inqarchive
        SET processed = 'Y', deferred = curr_tran_deferred,
          last_update_date = SYSDATE, last_updated_by = 1
        WHERE device_user_name = p_user_name AND
          tranid = curr_tranid;

Line: 3298

    DELETE FROM asg_users_inqinfo
    WHERE device_user_name = p_user_name AND processed = 'Y';

Line: 3310

    SELECT min(tranid) tran_id
    FROM asg_users_inqinfo a
    WHERE a.device_user_name = p_user_name AND
    a.deferred='N'
    AND a.tranid <=
    (SELECT  nvl(hwm_tranid,1000000000000)
     FROM asg_user
     WHERE user_name=a.device_user_name);

Line: 3343

    SELECT min(tranid) tran_id
    FROM asg_users_inqinfo a
    WHERE tranid > p_tranid
	AND  a.device_user_name = p_user_name AND a.deferred='N'
	AND a.tranid <= (SELECT nvl(hwm_tranid,1000000000000)
	                   FROM asg_user WHERE user_name=a.device_user_name);

Line: 3400

	SELECT au.user_id,pr.responsibility_id, pr.app_id
	INTO  l_userid,l_respid,l_appid
	FROM asg_user_pub_resps pr ,asg_user au
	WHERE  pr.user_name = upper(p_user_name)
	AND   au.user_name=pr.user_name
	AND pr.pub_name = upper('SERVICEP') AND ROWNUM=1;

Line: 3456

  SELECT PI_NAME
  FROM CSM_HTML5_INQ a,ASG_PUB_ITEM b
  WHERE a.PI_NAME=b.ITEM_ID
  AND a.user_name=p_user_name
  AND a.SYNC_ID=p_tranid
  ORDER BY nvl(b.table_weight,0);

Line: 3476

	FOR REC IN (SELECT object_name
                FROM asg_deferred_traninfo a,asg_pub_item b
				WHERE device_user_name = p_user_name
                AND deferred_tran_id = p_tranid
                AND b.item_id=a.object_name
                AND NOT EXISTS(SELECT 1 FROM CSM_HTML5_INQ
                               WHERE PI_NAME= object_name
                               AND USER_NAME=device_user_name
                               AND SYNC_ID=deferred_tran_id)
                ORDER BY nvl(b.table_weight,0))
    LOOP
	  l_n:=l_n+1;

Line: 3516

    SELECT count(*) count
    FROM asg_deferred_traninfo
    WHERE device_user_name = p_user_name AND
          deferred_tran_id = p_tranid AND
          status <> 0;

Line: 3539

	SELECT au.user_id,pr.responsibility_id, pr.app_id
	INTO  l_user_id,l_resp_id,l_app_id
	FROM asg_user_pub_resps pr ,asg_user au
	WHERE  pr.user_name = upper(p_user_name)
	AND   au.user_name=pr.user_name
	AND pr.pub_name = upper('SERVICEP') AND ROWNUM=1;

Line: 3562

      UPDATE asg_users_inqinfo
      SET deferred = 'S'
      WHERE device_user_name = p_user_name AND
            tranid = p_tranid;

Line: 3580

   FOR rec IN (select a.session_id,c.user_name from csm_html5_upload_data a,asg_user b, csm_html5_sync_info c
               where a.status<>'PARSED' and a.session_id=c.session_id
			   and nvl(fnd_profile.value_specific('ASG_SYNCHRONOUS_UPLOAD',b.user_id,b.responsibility_id,b.app_id),'N')='N'
               and b.user_name=c.user_name and b.multi_platform='Y' and b.enabled='Y' ORDER BY c.USER_NAME,a.SESSION_ID ASC)
   LOOP
     IF(l_uname IS NULL OR l_uname<>rec.USER_NAME)  THEN
      CSM_HTML5_PKG.HTML5_PARSE_XML(rec.SESSION_ID,RETCODE,errbuf);

Line: 3590

		  UPDATE CSM_HTML5_UPLOAD_DATA
		  SET   LAST_UPDATE_DATE  = SYSDATE,
				LAST_UPDATED_BY    = 1,
				STATUS            = 'PARSE_ERROR',
				COMMENTS          =  errbuf
		  WHERE SESSION_ID     = rec.SESSION_ID;

Line: 3605

   FOR rec IN (SELECT DISTINCT USER_NAME FROM CSM_HTML5_INQ)
   LOOP
     CSM_HTML5_PKG.process_upload(rec.USER_NAME);

Line: 3631

    DELETE FROM csm_html5_clients_info WHERE PRF_NAME='FORCE_LOCAL_LOGIN' AND USER_NAME=p_user_name;

Line: 3649

	   SELECT PRF_VALUE INTO l_local
	   FROM CSM_HTML5_CLIENTS_INFO
	   WHERE USER_NAME=p_user_name
	   AND PRF_NAME='FORCE_LOCAL_LOGIN'
	   AND PRF_VALUE='Y';

Line: 3663

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

procedure delete_user_vault(p_user_id IN NUMBER)
is
begin
  set_context('CSM5_VAULT_NS', 'VAULT', 'Y');

Line: 3686

 CSM_UTIL_PKG.LOG('Exception:'||' for user_id:'||p_user_id||' with msg:'||substr(sqlerrm,1,2000), 'CSM_HTML5_PKG.delete_user_vault', FND_LOG.LEVEL_PROCEDURE);

Line: 3687

end delete_user_vault;

Line: 3691

  DELETE FROM csm_html5_clients_info WHERE USER_NAME=(SELECT USER_NAME
  FROM ASG_USER WHERE USER_ID=p_user_id) AND PRF_NAME NOT IN ('REVISION','FORCE_UPDATE'); --if any user has name as APPLICATION

Line: 3694

  DELETE FROM CSM_HTML5_CACHE WHERE USER_NAME=(SELECT USER_NAME
  FROM ASG_USER WHERE USER_ID=p_user_id);

Line: 3697

  CSM_HTML5_PKG.DELETE_USER_VAULT(p_user_id);

Line: 3704

    SELECT 1 INTO l_t
    FROM asg_purge_sdq
    WHERE user_name = p_user_name
    AND   pub_name  = 'SERVICEP'
	AND   TRANSACTION_ID IS NULL;

Line: 3719

 SELECT (SELECT NVL(MAX(PRF_VALUE),'N') FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME='APPLICATION' AND PRF_NAME='FORCE_UPDATE')
        ||';'|| fnd_profile.value_specific('CSM_MULTI_APP_STORE_URL',USER_ID,RESPONSIBILITY_ID)