[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_qry := 'select DEVICE_USER_NAME user_name,DEFERRED_TRAN_ID tran_id ,'
||'ERROR_DESCRIPTION ,OBJECT_NAME pub_item,SEQUENCE '
||'from asg_deferred_traninfo where CREATION_DATE >= to_date('''
||to_char(p_start_time,'mm-dd-yyyy hh24:mi:ss')
||''',''mm-dd-yyyy hh24:mi:ss'') ';
select asg_events_s.nextval into l_seq from dual;
SELECT /*+ index (asg_pub_item, asg_pub_item_n1) */ name
FROM asg_pub_item
WHERE pub_name = p_pub_name
ORDER BY nvl(table_weight, 0);
l_select_sync_sqlstring VARCHAR2(512);
l_select_sync_sqlstring :=
'SELECT count(distinct clid$$cs) ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
'WHERE clid$$cs = :1 AND ' ||
'tranid$$ NOT IN ' ||
'(SELECT tranid ' ||
' FROM asg_users_inqinfo ' ||
' WHERE device_user_name = :2)';
EXECUTE IMMEDIATE l_select_sync_sqlstring
INTO p_sync_tables
USING p_user_name, p_user_name;
l_sql := 'UPDATE ' || l_cinq_table ||
'SET tranid$$ = :1 ' ||
'WHERE clid$$cs = :2 AND ' ||
' tranid$$ >= :3 AND ' ||
' tranid$$ <= :4';
log('compact_cinq: No of Records Updated : ' || SQL%ROWCOUNT);
l_sql := 'DELETE FROM ' || l_cinq_table || ' a ' ||
'WHERE a.clid$$cs = :1 AND ' ||
' a.tranid$$ = :2 AND ' ||
' rowid > (select min(rowid) ' ||
' from ' || l_cinq_table || ' b ' ||
' where b.clid$$cs = a.clid$$cs AND ' ||
' b.tranid$$ = a.tranid$$ AND ' ||
' b.store = a.store)';
log('compact_cinq: No of Records Deleted: ' || SQL%ROWCOUNT);
l_select_store_sqlstring VARCHAR2(512);
DELETE FROM asg_users_inqinfo
WHERE device_user_name = p_user_name AND
tranid >= p_start_tranid AND
tranid <= p_end_tranid AND
tranid <> p_compact_tranid;
l_select_store_sqlstring :=
'SELECT store ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
'WHERE clid$$cs = :1 AND ' ||
' tranid$$ = :2 ' ||
' ORDER BY store';
DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
UPDATE asg_users_inqarchive
SET pub_items1 = l_pubitems_1, pub_items2 = l_pubitems_2
WHERE device_user_name = p_user_name AND
tranid = p_compact_tranid;
DELETE FROM asg_users_inqarchive
WHERE device_user_name = p_user_name AND
tranid >= p_start_tranid AND
tranid <= p_end_tranid AND
tranid <> p_compact_tranid;
l_sql := 'UPDATE ' || l_inq_table ||
'SET seqno$$ = tranid$$*1000000 + seqno$$ ' ||
'WHERE clid$$cs = :1 AND ' ||
' tranid$$ >= :2 AND ' ||
' tranid$$ <= :3';
log('compact_curr_inqtable: No of Records Updated : ' || SQL%ROWCOUNT);
l_sql := 'UPDATE ' || l_inq_table ||
'SET tranid$$ = :1 ' ||
'WHERE clid$$cs = :2 AND ' ||
' tranid$$ >= :3 AND ' ||
' tranid$$ <= :4';
log('compact_curr_inqtable: No of Records Updated : ' || SQL%ROWCOUNT);
SELECT primary_key_column INTO l_pk_columns
FROM asg_pub_item
WHERE name = p_curr_pubitem;
/*to remove records that have dml types Insert and Deletes and/or Updates in INQ in one sync*/
l_sql :=
'DELETE FROM '||l_inq_table ||' WHERE ('||l_pk_columns||',clid$$cs,tranid$$) IN(' ||
'SELECT '||l_pk_columns||',clid$$cs,tranid$$ FROM '||l_inq_table ||' a '||
'WHERE clid$$cs = :1
AND tranid$$ = :2
AND dmltype$$=''D''
AND EXISTS( select 1' ||
' from ' || l_inq_table || ' b ' ||
' where b.dmltype$$ =''I'' and
b.clid$$cs = a.clid$$cs and ' ||
' b.tranid$$ = a.tranid$$ and ' ||
l_pk_clause || ' ))';
log('compact_curr_inqtable: No of Records Deleted : ' || l_sql_count);
l_sql := 'DELETE FROM '||asg_base.G_OLITE_SCHEMA||'.c$inq a '||
'WHERE STORE= '||''''||p_curr_pubitem||''' '||
'AND tranid$$=:1 '||
'AND NOT EXISTS (SELECT 1 FROM '||l_inq_table||' b WHERE a.tranid$$=b.tranid$$)' ;
log('compact_curr_inqtable: No of Records Deleted IN C$INQ : ' || l_sql_count);
l_sql:='UPDATE ASG_USERS_INQARCHIVE '||
'SET PUB_ITEMS1=replace(PUB_ITEMS1,'''||p_curr_pubitem||''',''*'||p_curr_pubitem||'''),
PUB_ITEMS2=replace(PUB_ITEMS2,'''||p_curr_pubitem||''',''*'||p_curr_pubitem||''') '||
'WHERE DEVICE_USER_NAME=:2 AND TRANID=:3';
log('compact_curr_inqtable: No of Records Updated in INQARCHIVE : ' || SQL%ROWCOUNT);
l_sql := 'UPDATE ' || l_inq_table || ' a ' ||
'SET dmltype$$ = (select min(dmltype$$) ' ||
' from ' || l_inq_table || ' b ' ||
' where b.clid$$cs = a.clid$$cs and ' ||
' b.tranid$$ = a.tranid$$ and ' ||
l_pk_clause || ' ) '||
'WHERE clid$$cs = :1 AND tranid$$ = :2';
log('compact_curr_inqtable: No of Records Updated : ' || SQL%ROWCOUNT);
l_sql := 'DELETE FROM ' || l_inq_table || ' a ' ||
'WHERE seqno$$ < (select max(seqno$$) ' ||
' from ' || l_inq_table || ' b ' ||
' where b.clid$$cs = a.clid$$cs and ' ||
' b.tranid$$ = a.tranid$$ and ' ||
l_pk_clause || ' ) '||
'AND clid$$cs = :1 AND tranid$$ = :2';
log('compact_curr_inqtable: No of Records Deleted: ' || SQL%ROWCOUNT);
l_select_store_sqlstring VARCHAR2(512);
l_select_store_sqlstring :=
'SELECT store ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
'WHERE clid$$cs = :1 AND ' ||
' tranid$$ = :2 ' ||
' ORDER BY store';
DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
l_select_tranid_sqlstring VARCHAR2(512);
l_select_tranid_sqlstring :=
'SELECT distinct tranid$$ ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
'WHERE clid$$cs = :user_name AND '||
'tranid$$ NOT IN ' ||
'(SELECT tranid ' ||
' FROM asg_users_inqinfo ' ||
' WHERE device_user_name = :user_name)';
DBMS_SQL.PARSE (l_cursor_id, l_select_tranid_sqlstring, DBMS_SQL.v7);
SELECT DISTINCT a.user_name user_name
FROM asg_user a, asg_users_inqinfo b
WHERE a.user_name = b.device_user_name AND
b.processed in ('I', 'N') AND
a.enabled = 'Y'
ORDER BY a.user_name;
SELECT DISTINCT a.user_name user_name
FROM asg_user a, asg_users_inqinfo b
WHERE a.user_name = b.device_user_name AND
b.deferred = 'N' AND b.processed <> 'Y' AND
a.enabled = 'Y'
ORDER BY a.user_name;
SELECT DISTINCT a.user_name user_name
FROM asg_user a, asg_users_inqinfo b
WHERE a.user_name = b.device_user_name AND
b.deferred <> 'N' and b.processed <> 'Y'
ORDER BY a.user_name;
SELECT tranid
FROM asg_users_inqinfo a
WHERE a.device_user_name = p_user_name
ORDER BY tranid;
l_select_store_sqlstring VARCHAR2(512);
l_select_obj_sqlstring VARCHAR2(4000);
l_select_store_sqlstring :=
'SELECT store ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
'WHERE clid$$cs = :1 AND ' ||
' tranid$$ = :2 '||
' ORDER BY store';
DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
l_select_obj_sqlstring :=
'SELECT object_name ' ||
'FROM asg_deferred_traninfo ' ||
'WHERE device_user_name = :user_name AND ' ||
' deferred_tran_id = :tranid AND ' ||
' object_name not in ' ||
' (SELECT store ' ||
' FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
' WHERE clid$$cs = :user_name AND ' ||
' tranid$$ = :tranid) ' ||
' ORDER BY object_name';
DBMS_SQL.PARSE (l_cursor_id, l_select_obj_sqlstring, DBMS_SQL.v7);
l_select_store_sqlstring VARCHAR2(512);
l_select_obj_sqlstring VARCHAR2(4000);
l_select_store_sqlstring :=
'SELECT store ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
'WHERE clid$$cs = :1 AND ' ||
' tranid$$ = :2 AND ' ||
' store in ' ||
' (SELECT name ' ||
' FROM asg_pub_item ' ||
' WHERE pub_name = :3) ' ||
' ORDER BY store';
DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
l_select_obj_sqlstring :=
'SELECT object_name ' ||
'FROM asg_deferred_traninfo ' ||
'WHERE device_user_name = :user_name AND ' ||
' deferred_tran_id = :tranid AND ' ||
' object_name IN ' ||
' (SELECT name ' ||
' FROM asg_pub_item ' ||
' WHERE pub_name = :pubname) AND ' ||
' object_name not in ' ||
' (SELECT store ' ||
' FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
' WHERE clid$$cs = :user_name AND ' ||
' tranid$$ = :tranid) ' ||
' ORDER BY object_name';
DBMS_SQL.PARSE (l_cursor_id, l_select_obj_sqlstring, DBMS_SQL.v7);
l_select_store_sqlstring VARCHAR2(512);
l_select_store_sqlstring :=
'SELECT store ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA ||'.c$inq '||
'WHERE clid$$cs = :1 AND ' ||
' tranid$$ = :2 AND ' ||
' store not in ' ||
' (SELECT object_name ' ||
' FROM asg_deferred_traninfo ' ||
' WHERE device_user_name = :3 AND ' ||
' deferred_tran_id = :4) AND ' ||
' store in ' ||
' (SELECT name ' ||
' FROM asg_pub_item ' ||
' WHERE pub_name = :5) ' ||
' ORDER BY store';
DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
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=p_user_name);
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=p_user_name);
SELECT sync_id
FROM asg_auto_sync_tranids
WHERE user_name = p_user_name
AND upload_tranid = p_tranid;
DELETE FROM asg_auto_sync_tranids
WHERE user_name = p_user_name
AND upload_tranid <= x_compacted_tranid;
SELECT sync_id
FROM asg_auto_sync_tranids
WHERE user_name = p_user_name
AND upload_tranid = p_tranid;
DELETE FROM asg_auto_sync_tranids
WHERE user_name = p_user_name
AND upload_tranid <= l_end_tranid;
PROCEDURE delete_row(p_user_name IN VARCHAR2,
p_tranid IN NUMBER,
p_pubitem IN VARCHAR2,
p_sequence IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_deferred_row VARCHAR2(1);
sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
'.' || inq_tbl_name ||
' WHERE clid$$cs = :1 AND ' ||
' tranid$$ = :2 AND ' ||
' seqno$$ = :3';
print_string('delete_row: SQL Command: ' || sql_string);
log('delete_row: Exception: ', g_err_level);
UPDATE asg_deferred_traninfo
SET status = 0
WHERE device_user_name = p_user_name AND
deferred_tran_id = p_tranid AND
object_name = p_pubitem AND
sequence = p_sequence;
log('delete_row: Exception: tranid not deferred',g_err_level);
END delete_row;
sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
'.' || inq_tbl_name ||
' WHERE clid$$cs = :1 AND ' ||
' tranid$$ = :2 AND ' ||
' seqno$$ NOT IN ' ||
' (SELECT sequence ' ||
' FROM asg_deferred_traninfo ' ||
' WHERE device_user_name = :3 AND '||
' object_name = :4)';
sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
'.' || 'c$inq ' ||
'WHERE clid$$cs = :1 AND ' ||
' tranid$$ = :2 AND ' ||
' store = :3';
SELECT resource_id
FROM asg_user
WHERE user_name = p_user_name;
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 ' || asg_base.G_OLITE_SCHEMA || '.' || 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)';
sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
'.' || 'c$inq ' ||
'WHERE clid$$cs = :1 AND ' ||
' tranid$$ = :2 AND ' ||
' store = :3';
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;
l_select_store_sqlstring VARCHAR2(512);
l_select_store_sqlstring :=
'SELECT store ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
'WHERE clid$$cs = :1 AND ' ||
' tranid$$ = :2 ' ||
' ORDER BY store';
DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
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);
SELECT tranid
FROM asg_users_inqinfo
WHERE device_user_name = p_user_name AND
tranid <= p_max_tranid;
SELECT tranid, processed, deferred, archive
FROM asg_users_inqinfo
WHERE device_user_name = p_user_name;
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;
DELETE FROM asg_users_inqinfo
WHERE device_user_name = p_user_name AND processed = 'Y';
l_select_users_sqlstring VARCHAR2(512);
SELECT count(*) def_trans
FROM asg_deferred_traninfo;
l_select_users_sqlstring :=
'SELECT distinct clid$$cs clientid ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq';
DBMS_SQL.PARSE (l_cursor_id, l_select_users_sqlstring, DBMS_SQL.v7);
l_select_seq_sqlstring VARCHAR2(4000);
l_update_seq_sqlstring VARCHAR2(4000);
l_delete_sqlstring VARCHAR2(4000);
SELECT client_number INTO l_client_num
FROM asg_user
WHERE user_name = p_user_name;
l_select_seq_sqlstring :=
'SELECT a.name name, a.curr_val curr_val '||
'FROM ' || asg_base.G_OLITE_SCHEMA ||
'.' || 'cfm$c$all_sequence_partitions a, ' ||
asg_base.G_OLITE_SCHEMA || '.' || 'c$inq b ' ||
'WHERE b.clid$$cs = :user_name AND ' ||
'b.tranid$$ = :tranid AND ' ||
'b.store = :seq_name AND ' ||
'a.clid$$cs = b.clid$$cs AND ' ||
'a.tranid$$ = b.tranid$$';
DBMS_SQL.PARSE (l_cursor_id, l_select_seq_sqlstring, DBMS_SQL.v7);
l_update_seq_sqlstring := 'UPDATE asg_sequence_partitions ' ||
'SET curr_val = :1 ' ||
'WHERE CLIENTID = :2 AND ' ||
' name = :3 AND ' ||
' curr_val < :4';
EXECUTE IMMEDIATE l_update_seq_sqlstring
USING l_curr_val, p_user_name, l_seq_name, l_curr_val;
log('No sequences need to be updated',g_stmt_level);
l_delete_sqlstring := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
'.' || 'c$inq ' ||
'WHERE CLID$$CS = :1 AND ' ||
'TRANID$$ = :2 AND ' ||
'STORE = :3';
EXECUTE IMMEDIATE l_delete_sqlstring
USING p_user_name, p_tranid, l_sequence;
l_delete_sqlstring := 'DELETE FROM ' ||
asg_base.G_OLITE_SCHEMA ||
'.' ||'cfm$c$all_sequence_partitions ' ||
'WHERE CLID$$CS = :1 AND ' ||
' TRANID$$ = :2';
EXECUTE IMMEDIATE l_delete_sqlstring
USING p_user_name, p_tranid;
l_select_store_sqlstring VARCHAR2(512);
l_select_inqcnt_sqlstring VARCHAR2(512);
l_delete_inq_sqlstring VARCHAR2(512);
SELECT resource_id
FROM asg_user
WHERE user_name = p_user_name;
l_delete_inq_sqlstring :=
'DELETE from ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
'WHERE store in ' ||
' (SELECT api.name ' ||
' FROM asg_pub ap, asg_pub_item api ' ||
' WHERE ap.custom = ''Y'' AND ' ||
' ap.name = api.pub_name)';
EXECUTE IMMEDIATE l_delete_inq_sqlstring;
l_select_inqcnt_sqlstring :=
'SELECT count(*) ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
'WHERE clid$$cs = :1 AND ' ||
' tranid$$ = :2';
EXECUTE IMMEDIATE l_select_inqcnt_sqlstring
INTO l_inq_count
USING p_user_name, p_tranid;
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);
l_select_store_sqlstring :=
'SELECT store ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
'WHERE clid$$cs = :1 AND ' ||
' tranid$$ = :2 ' ||
' ORDER BY store';
DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
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);
l_select_inqcnt_sqlstring VARCHAR2(512);
l_select_pub_sqlstring VARCHAR2(512);
l_select_resp_sqlstring VARCHAR2(512);
l_select_userid_sqlstring VARCHAR2(512);
l_select_inqcnt_sqlstring :=
'SELECT count(*) ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
'WHERE clid$$cs = :1 AND ' ||
' tranid$$ = :2';
EXECUTE IMMEDIATE l_select_inqcnt_sqlstring
INTO l_inq_count
USING p_user_name, p_tranid;
UPDATE asg_users_inqinfo
SET processed = 'U', deferred = 'Y'
WHERE device_user_name = p_user_name AND
tranid = p_tranid;
l_select_pub_sqlstring :=
'SELECT template, wrapper_name ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$all_subscriptions a, ' ||
' asg_pub b ' ||
'WHERE a.clientid = :user_name AND ' ||
' a.template = b.name';
DBMS_SQL.PARSE (l_cursor_id, l_select_pub_sqlstring, DBMS_SQL.v7);
SELECT USER_ID into l_userid
FROM asg_user
WHERE user_name = p_user_name;
SELECT pr.responsibility_id, pr.app_id
INTO l_respid, l_appid
FROM asg_user_pub_resps pr
WHERE pr.user_name = upper(p_user_name) AND
pr.pub_name = upper(curr_pub) AND
ROWNUM =1;
select sysdate into g_conc_start_time from dual;
select nvl(fnd_profile.value_specific('ASG_ENABLE_UPLOAD_EVENTS'),'N')
into l_prof_value from dual;
select count(*) into l_row_count
from asg_deferred_traninfo
where creation_date >= g_conc_start_time;