[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select_pi_sqlstring VARCHAR2(4000);
l_select_pi_sqlstring :=
'SELECT name, comp_ref ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA ||'.' ||'c$pub_list_q';
DBMS_SQL.PARSE (l_cursor_id, l_select_pi_sqlstring, DBMS_SQL.v7);
SELECT nvl(last_tranid, 0) into l_stored_last_tranid
FROM asg_user
WHERE user_name = p_user_name;
PROCEDURE insert_auto_sync_tranids(p_user_name IN VARCHAR2,
p_upload_tranid IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
insert into asg_auto_sync_tranids
(user_name, upload_tranid, sync_id,
creation_date, created_by, last_update_date, last_updated_by )
values
(p_user_name, p_upload_tranid, NULL, sysdate,1, sysdate,1);
END insert_auto_sync_tranids;
SELECT MAX(upload_tranid)
FROM asg_auto_sync_tranids
WHERE USER_NAME=b_user_name
AND SYNC_ID IS NULL;
UPDATE asg_auto_sync_tranids SET SYNC_ID= l_sync_id
WHERE USER_NAME=p_user_name
AND SYNC_ID IS NULL;
insert_auto_sync_tranids(p_clientid, p_tranid);
UPDATE asg_user SET hwm_tranid = nvl(l_sync_id,hwm_tranid) WHERE user_name = p_clientid;
SELECT nvl(password_expired, 'N') into l_pwd_expired
FROM asg_user
WHERE user_name = p_clientid;
delete_row(p_clientid);
asg_helper.log('First synch..deleted all rows from asg_complete_refresh'
,'asg_cons_qpkg',g_stmt_level);
UPDATE asg_user
SET last_tranid = l_last_tranid,
last_synch_date_end = sysdate,
prior_synch_date_end = null
WHERE user_name = p_clientid;
UPDATE asg_user
SET last_tranid = l_last_tranid,
prior_synch_date_end = asg_base.get_last_synch_date(),
last_synch_date_end = sysdate
WHERE user_name = p_clientid;
UPDATE asg_user
SET last_tranid = l_last_tranid,
last_synch_date_end = sysdate
WHERE user_name = p_clientid;
l_qry_string1:='select count(*) from '||asg_base.G_OLITE_SCHEMA
||'.c$pub_list_q';
l_qry_string1:='select count(*) from '||asg_base.G_OLITE_SCHEMA
||'.c$pub_list_q where comp_ref=''Y''';
PROCEDURE update_rec_count(p_pubitem_tbl IN asg_base.pub_item_tbl_type,
p_clientid IN VARCHAR2)
IS
l_loopvar NUMBER;
asg_helper.log('Performing batch update for: '||p_clientid,
'asg_cons_qpkg',g_stmt_level);
l_qry_string:='update '||asg_base.G_OLITE_SCHEMA||'.c$pub_list_q '||
' set rec_count= :1 ' ||
' where name = :2';
/* asg_helper.log('Update: '||p_pubitem_tbl(l_loopvar).name||' count: '
||p_pubitem_tbl(l_loopvar).rec_count,
'asg.asg_cons_qpkg');
END update_rec_count;
select base_object_name,base_owner into l_view_name,l_owner_name
from asg_pub_item where item_id=l_curr_pubitem;
l_qry_string2:='select count(*) from '||l_owner_name||'.'||l_view_name;
update_rec_count(l_pubitem_tbl,p_clientid);
l_qry_string := 'update '||asg_base.G_OLITE_SCHEMA||'.c$pub_list_q '
||'set comp_ref = ''Y'' '||' where name IN ('
||'SELECT publication_item FROM asg_complete_refresh '
||' WHERE synch_completed=''N'' AND user_name = :1 '
||' AND publication_item IN '
||' ( SELECT name FROM '||asg_base.G_OLITE_SCHEMA
||'.c$pub_list_q ))' ;
UPDATE asg_complete_refresh
SET synch_completed='Y' , last_update_date = sysdate
WHERE user_name=p_user_name
AND publication_item=p_pub_item;
l_qry_string := ' UPDATE asg_complete_refresh SET ' ||
' synch_completed=''Y'',last_update_date=sysdate ' ||
' WHERE user_name= :1 ' ||
' AND ' ||
' publication_item IN ' ||
' (SELECT name FROM '||asg_base.G_OLITE_SCHEMA||
'.c$pub_list_q)';
PROCEDURE delete_row(p_user_name VARCHAR2,p_pub_item VARCHAR2)
IS
BEGIN
DELETE FROM asg_complete_refresh
WHERE user_name = p_user_name AND
publication_item = p_pub_item;
END delete_row;
PROCEDURE delete_row(p_user_name VARCHAR2)
IS
l_qry_string VARCHAR2(1024);
l_qry_string:= ' DELETE FROM asg_complete_refresh '||
' WHERE user_name = :1 ' ||
' AND synch_completed = ''Y'' AND '||
' publication_item IN ' ||
'(SELECT name FROM '||asg_base.G_OLITE_SCHEMA||
'.c$pub_list_q)';
END delete_row;
delete_row(p_user_name);
l_str:= 'UPDATE asg_complete_refresh SET synch_completed=''N'' , '
||' last_update_date = sysdate WHERE user_name = :1 '
||' AND publication_item in '
||'(SELECT name FROM '||asg_base.G_OLITE_SCHEMA
||'.c$pub_list_q)';
l_dml := 'DELETE FROM '||asg_base.G_OLITE_SCHEMA||'.c$pub_list_q ' ||
' WHERE name IN ' ||
' (select a.name from asg_pub_item a,asg_pub b' ||
' where a.pub_name=b.name and b.custom=''Y'')';
/* l_query_string := 'SELECT distinct api.pub_name ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci, ' ||
' asg_pub_item api ' ||
'WHERE ci.clid$$cs = ''' || p_user_name || ''' AND ' ||
' ci.tranid$$ = ' || p_upload_tranid || ' AND ' ||
' ci.store = api.name';*/
l_query_string := 'SELECT distinct api.pub_name ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci, ' ||
' asg_pub_item api ' ||
'WHERE ci.clid$$cs = :1 AND ' ||
' ci.tranid$$ = :2 AND ' ||
' ci.store = api.name';
SELECT wrapper_name into l_pub_callback
FROM asg_pub
WHERE name = l_pub_name;
l_query_string2 := 'SELECT ' || l_pub_callback ||
'.detect_conflict( :1 ) from dual';
l_query_string := 'SELECT count(*) ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci, ' ||
asg_base.G_OLITE_SCHEMA || '.c$pub_list_q cpq, ' ||
' asg_pub_item api ' ||
'WHERE ci.clid$$cs = :1 AND ' ||
' ci.tranid$$ = :2 AND ' ||
' ci.store = api.name AND ' ||
' ci.store = cpq.name AND ' ||
' cpq.comp_ref <> ''Y'' AND ' ||
' api.detect_conflict = ''Y''';
/* l_query_string := 'SELECT ci.store ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci , ' ||
asg_base.G_OLITE_SCHEMA || '.c$pub_list_q cpq, ' ||
' asg_pub_item api ' ||
'WHERE ci.clid$$cs = ''' || p_user_name || ''' AND ' ||
' ci.tranid$$ = ' || p_upload_tranid || ' AND ' ||
' ci.store = api.name AND ' ||
' ci.store = cpq.name AND ' ||
' cpq.comp_ref <> ''Y'' AND ' ||
' api.detect_conflict = ''Y'' AND ' ||
' api.pub_name in (' || l_conf_pubs || ')';*/
l_query_string := 'SELECT ci.store ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci , ' ||
asg_base.G_OLITE_SCHEMA || '.c$pub_list_q cpq, ' ||
' asg_pub_item api ' ||
'WHERE ci.clid$$cs = :1 AND ' ||
' ci.tranid$$ = :2 AND ' ||
' ci.store = api.name AND ' ||
' ci.store = cpq.name AND ' ||
' cpq.comp_ref <> ''Y'' AND ' ||
' api.detect_conflict = ''Y'' AND ' ||
' api.pub_name in (' || l_conf_pubs || ')';
SELECT NVL(transaction_id,-100)
FROM asg_purge_sdq
WHERE user_name = c_username AND pub_name = c_pub_name;
l_qry_string := 'select distinct pub_name from asg_pub_item where item_id in '||
' ( select name from '||asg_base.G_OLITE_SCHEMA||'.c$pub_list_q )';
l_qry_string1 := 'update '||asg_base.G_OLITE_SCHEMA||'.c$pub_list_q '||
' set comp_ref = ''Y'' where name in '||
' ( select item_id from asg_pub_item where '||
' pub_name = :1 )';
UPDATE asg_purge_sdq
SET transaction_id=p_curr_tranid,last_update_date=sysdate
WHERE user_name=p_clientid AND pub_name = l_pub_name;
l_qry_string1 := 'update '||asg_base.G_OLITE_SCHEMA||'.c$pub_list_q '||
' set comp_ref = ''Y'' where name in '||
' ( select item_id from asg_pub_item where '||
' pub_name = :1 )';
UPDATE asg_purge_sdq
SET transaction_id=p_curr_tranid,last_update_date=sysdate
WHERE user_name=p_clientid AND pub_name = l_pub_name;
DELETE FROM asg_purge_sdq
WHERE user_name = p_clientid AND pub_name = l_pub_name;
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;
l_client_update_count NUMBER;
l_server_update_count NUMBER;
l_query_string := 'SELECT count(*) ' ||
'FROM ' || l_inqtable_name ||
'WHERE clid$$cs = :1 AND ' ||
' tranid$$ = :2 AND ' ||
' dmltype$$ = ''U''';
INTO l_client_update_count
USING p_user_name, p_upload_tranid;
IF (l_client_update_count =0) THEN
IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
asg_helper.log('No conflicts exist.',
'asg_cons_qpkg',g_stmt_level);
SELECT base_object_name, primary_key_column, conflict_callout
INTO l_piv, l_primary_key_columns, l_conflict_callout
FROM asg_pub_item
WHERE name = p_pubitem;
insert into asg_conf_info (user_name,
pub_item,
transaction_id,
access_id,
resolution,
creation_date,
created_by,
last_update_date,
last_updated_by)
SELECT p_user_name, p_pubitem, p_upload_tranid, access_id, l_client_wins,
sysdate, 1, sysdate, 1
FROM asg_system_dirty_queue
WHERE client_id = p_user_name AND
pub_item = p_pubitem AND
transaction_id = l_download_tranid AND
download_flag = 'Y' AND
dml_type = 2;
l_server_update_count := SQL%ROWCOUNT;
asg_helper.log('Number of updates in server: ' || l_server_update_count,
'asg_cons_qpkg',g_stmt_level);
IF (l_server_update_count = 0) THEN
IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
asg_helper.log('No conflicts exist.',
'asg_cons_qpkg',g_stmt_level);
l_query_string := 'UPDATE asg_conf_info ' ||
'SET (sequence, access_id) = ' ||
'(SELECT seqno$$, access_id ' ||
' FROM ' || l_inqtable_name || ' inq, ' ||
l_piv || ' piv ' ||
' WHERE inq.clid$$cs = :1 AND ' ||
' inq.tranid$$ = :2 AND ' ||
' inq.dmltype$$ = ''U'' AND ' ||
l_pk_predicate || ' AND ' ||
' piv.access_id in ' ||
'(SELECT access_id ' ||
' FROM asg_conf_info ' ||
' WHERE user_name = :3 AND ' ||
' transaction_id = :4 AND ' ||
' pub_item = :5)) ' ||
' WHERE user_name = :6 AND ' ||
' transaction_id = :7 AND ' ||
' pub_item = :8';
/* l_query_string := 'SELECT ' || l_conflict_callout ||
'(''' || p_user_name || ''', ' || p_upload_tranid ||
', ' || l_sequence || ') from dual';*/
l_query_string := 'SELECT ' || l_conflict_callout ||
'(:1,:2,:3) from dual';
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;
UPDATE asg_system_dirty_queue
SET download_flag = NULL
WHERE client_id = p_user_name AND
pub_item = p_pubitem AND
transaction_id = l_download_tranid 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);
asg_helper.update_hwm_tranid(p_user_name, p_upload_tranid);
UPDATE asg_user
SET password_expired = p_pwd_expired
WHERE user_name = p_user_name;
SELECT count(*) into l_first_synch
FROM asg_user
WHERE user_name = p_user_name AND
hwm_tranid IS NULL AND
NOT EXISTS (SELECT 1
FROM asg_purge_sdq
WHERE user_name = p_user_name and
transaction_id is NOT null);
SELECT last_synch_date_end into l_last_synch_date
FROM asg_user
WHERE user_name = p_user_name;
SELECT prior_synch_date_end into l_last_synch_date
FROM asg_user
WHERE user_name = p_user_name;