[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wrapper_name
FROM asg_pub
WHERE name = p_pub_name;
SELECT nvl(custom, 'N')
FROM asg_pub
WHERE name = p_pub_name;
PROCEDURE delete_access(p_user_name IN VARCHAR2,
p_pub_name IN VARCHAR2)
IS
CURSOR c_wrapper_name(p_pub_name VARCHAR2) IS
SELECT wrapper_name
FROM asg_pub
WHERE name = p_pub_name;
SELECT nvl(custom, 'N')
FROM asg_pub
WHERE name = p_pub_name;
log('Calling delete_access_records for user: ' || p_user_name ||
' and publication: ' || p_pub_name,'asg_helper',g_stmt_level);
'.delete_access_records(:2); END;';
log('Exception in call to delete access records: ' ||
SQLERRM, 'asg_helper',g_err_level);
END delete_access;
DELETE FROM asg_sequence_partitions
WHERE clientid = p_user_name AND name = p_seq_name;
INSERT INTO asg_sequence_partitions (
CLIENTID,
NAME,
CURR_VAL,
INCR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
values
(p_user_name,
p_seq_name,
to_number(p_start_value),
to_number(p_next_value),
sysdate,
1,
sysdate,
1);
DELETE FROM asg_sequence_partitions
WHERE clientid = p_user_name AND name = p_seq_name;
PROCEDURE insert_user_pub_resp(p_user_name IN VARCHAR2,
p_pub_name IN VARCHAR2,
p_resp_id IN NUMBER,
p_app_id IN NUMBER)
IS
BEGIN
DELETE FROM asg_user_pub_resps
WHERE user_name = p_user_name AND
pub_name = p_pub_name AND
responsibility_id = p_resp_id AND
app_id = p_app_id;
INSERT INTO asg_user_pub_resps (
USER_NAME,
PUB_NAME,
SYNCH_DISABLED,
RESPONSIBILITY_ID,
APP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
(p_user_name,
p_pub_name,
'N',
p_resp_id,
p_app_id,
sysdate,
1,
sysdate,
1);
END insert_user_pub_resp;
PROCEDURE delete_user_pub(p_user_name IN VARCHAR2,
p_pub_name IN VARCHAR2)
IS
BEGIN
DELETE FROM asg_user_pub_resps
WHERE user_name = p_user_name AND
pub_name = p_pub_name;
DELETE FROM asg_purge_sdq
WHERE user_name = p_user_name AND
pub_name = p_pub_name;
DELETE FROM asg_complete_refresh
WHERE user_name = p_user_name AND
publication_item IN
( SELECT item_id FROM asg_pub_item
WHERE pub_name = p_pub_name);
log('Deleted user pub record for user: ' || p_user_name ||
' and publication: ' || p_pub_name,'asg_helper',g_stmt_level);
END delete_user_pub;
PROCEDURE delete_user_pub_resp(p_user_name IN VARCHAR2,
p_pub_name IN VARCHAR2,
p_resp_id IN NUMBER)
IS
BEGIN
DELETE FROM asg_user_pub_resps
WHERE user_name = p_user_name AND
pub_name = p_pub_name AND
responsibility_id = p_resp_id;
log('Deleted user pub responsibility record for user: ' || p_user_name ||
' and publication: ' || p_pub_name || ' and responsibility id: ' ||
p_resp_id,'asg_helper',g_stmt_level);
END delete_user_pub_resp;
SELECT pub_name
FROM asg_user_pub_resps
WHERE user_name = p_user_name;
delete_access(l_user_name, cups.pub_name);
DELETE FROM asg_user_pub_resps
WHERE user_name = p_user_name;
DELETE FROM ASG_USERS_INQARCHIVE
WHERE device_user_name = l_user_name;
DELETE FROM ASG_DEFERRED_TRANINFO
WHERE device_user_name = l_user_name;
DELETE FROM ASG_USERS_INQINFO
WHERE device_user_name = l_user_name;
DELETE FROM asg_auto_sync_tranids
WHERE user_name = l_user_name;
DELETE FROM asg_purge_sdq
WHERE user_name = l_user_name;
DELETE FROM asg_complete_refresh
WHERE user_name = l_user_name;
DELETE FROM asg_sequence_partitions
WHERE clientid = p_user_name;
DELETE FROM ASG_USER
WHERE user_name = l_user_name;
SELECT value
FROM asg_config
WHERE name = p_param_name;
UPDATE asg_pub
SET enable_synch = 'Y';
UPDATE asg_pub
SET enable_synch = 'Y'
WHERE name = upper(p_pub_name);
UPDATE asg_pub
SET enable_synch = 'N';
UPDATE asg_pub
SET enable_synch = 'N'
WHERE name = upper(p_pub_name);
SELECT count(*) into l_disabled_user
FROM asg_user
WHERE user_name = p_user_name AND
enabled = 'N';
l_query_string := 'SELECT count(*) ' ||
'FROM asg_user_pub_resps aup, asg_pub ap ' ||
'WHERE aup.user_name = :1 AND ' ||
' aup.pub_name = ap.name AND ' ||
' aup.synch_disabled = ''Y'' AND ' ||
' ap.name in ' ||
' (SELECT distinct pub_name ' ||
' FROM asg_pub_item api, ' ||
asg_base.G_OLITE_SCHEMA || '.c$pub_list_q cpq ' ||
' where api.name = cpq.name)';
l_query_string := 'SELECT count(*) ' ||
'FROM asg_user_pub_resps aup, asg_pub ap ' ||
'WHERE aup.user_name = :1 AND ' ||
' aup.pub_name = ap.name AND ' ||
' ap.enable_synch = ''N'' AND ' ||
' ap.name in ' ||
' (SELECT distinct pub_name ' ||
' FROM asg_pub_item api, ' ||
asg_base.G_OLITE_SCHEMA || '.c$pub_list_q cpq ' ||
' where api.name = cpq.name)';
SELECT user_name
FROM asg_user_pub_resps aup, asg_pub_item api
WHERE api.name = upper(pi_name) AND
api.pub_name = aup.pub_name;
SELECT count(*)
FROM asg_complete_refresh
WHERE publication_item=pi_name;
SELECT user_name
FROM asg_user_pub_resps aup, asg_pub_item api
WHERE api.name = upper(pi_name) AND
aup.pub_name = api.pub_name AND
user_name NOT IN
( SELECT user_name
FROM asg_complete_refresh
WHERE publication_item = pi_name );
UPDATE asg_complete_refresh
SET last_update_date=sysdate,synch_completed='N'
WHERE publication_item=l_pub_item;
INSERT INTO asg_complete_refresh(
USER_NAME,
PUBLICATION_ITEM,
SYNCH_COMPLETED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES(
l_recf1.user_name,
l_pub_item,
'N',
sysdate,
1,
sysdate,
1);
INSERT INTO asg_complete_refresh(
USER_NAME,
PUBLICATION_ITEM,
SYNCH_COMPLETED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES(
l_uname.user_name,
l_pub_item,
'N',
sysdate,
1,
sysdate,
1);
SELECT user_name
FROM asg_user
WHERE user_id = p_user_id;
UPDATE asg_user_pub_resps
SET SYNCH_DISABLED = 'Y'
WHERE user_name = l_user_name AND
pub_name = p_pub_name;
SELECT user_name
FROM asg_user
WHERE user_id = p_user_id;
UPDATE asg_user_pub_resps
SET SYNCH_DISABLED = 'N'
WHERE user_name = l_user_name AND
pub_name = p_pub_name;
l_query_string := 'SELECT id ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.groups grp ' ||
'WHERE grp.name = :group_name';
'INSERT INTO ' ||
asg_base.G_OLITE_SCHEMA || '.usr_grp ' ||
'(entity_id, entity_type, grp_id) ' ||
'SELECT usr.id, 0, :group_id ' ||
'FROM ' || asg_base.G_OLITE_SCHEMA || '.users usr ' ||
'WHERE usr.id not in ' ||
' (SELECT usr2.id ' ||
' FROM ' || asg_base.G_OLITE_SCHEMA || '.users usr2,' ||
asg_base.G_OLITE_SCHEMA || '.usr_grp ugrp ' ||
' WHERE ugrp.grp_id = :group_id AND ' ||
' usr2.id = ugrp.entity_id) AND ' ||
' usr.name in ' ||
' (SELECT user_name ' ||
' FROM asg_user)';
PROCEDURE update_hwm_tranid(p_user_name IN VARCHAR2,p_tranid IN NUMBER)
IS
BEGIN
UPDATE asg_user
SET hwm_tranid=p_tranid
WHERE user_name=UPPER(p_user_name);
END update_hwm_tranid;
PROCEDURE update_user_setup_errors(p_user_name IN VARCHAR2,p_mesg IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE asg_user
SET user_setup_errors = p_mesg
WHERE user_name = p_user_name;
END update_user_setup_errors;
PROCEDURE update_user_resps(p_user_name IN VARCHAR2)
IS
l_resp_id NUMBER;
SELECT responsibility_id, app_id INTO l_resp_id, l_app_id
FROM asg_user_pub_resps
WHERE user_name = p_user_name AND
pub_name = 'SERVICEP';
SELECT responsibility_id, app_id INTO l_resp_id, l_app_id
FROM asg_user_pub_resps
WHERE user_name = p_user_name AND
pub_name = 'SERVICEL';
UPDATE asg_user
SET responsibility_id = l_resp_id, app_id = l_app_id
WHERE user_name = p_user_name AND
responsibility_id <> l_resp_id AND
app_id <> l_app_id;
END update_user_resps;
PROCEDURE update_synch_errors(p_user_name IN VARCHAR2,p_mesg IN VARCHAR2)
IS
BEGIN
UPDATE asg_user
SET synch_errors = p_mesg,
last_wireless_contact_date = sysdate
WHERE user_name = p_user_name;
END update_synch_errors;
SELECT NAME FROM asg_pub WHERE device_type=l_device_type;
UPDATE asg_user
SET synch_errors = p_mesg,
cookie = nvl(p_device_type,cookie),
hwm_tranid = nvl(p_tranid,hwm_tranid),
last_wireless_contact_date = sysdate
WHERE user_name = p_user_name;
UPDATE asg_user_pub_resps
SET synch_date = sysdate
WHERE user_name = p_user_name
AND pub_name = l_pub_name;
SELECT user_name
FROM asg_user
WHERE cookie is null;
SELECT distinct ap.device_type
FROM asg_user_pub_resps aupr, asg_pub ap
WHERE aupr.pub_name = ap.name and
aupr.user_name = p_user_name and
ap.device_type is not null;
UPDATE asg_user
SET cookie = l_device_type
WHERE user_name = l_current_user;
l_sql_string := 'SELECT count(ws.os_name) ' ||
'FROM ' ||
asg_base.G_OLITE_SCHEMA || '.wtg_sites ws, ' ||
asg_base.G_OLITE_SCHEMA || '.users usr, ' ||
asg_base.G_OLITE_SCHEMA || '.c$all_clients cac ' ||
'where cac.synctime_start is not null ' ||
'and cac.clientid = usr.name and usr.id = ws.usr_id ' ||
'and abs(cac.synctime_start-ws.last_sync) <= 1/24 ' ||
'and ws.os_name is not null ' ||
'and usr.name = :1';
UPDATE asg_user
SET cookie = 'LAPTOP'
WHERE user_name = l_current_user;
l_sql_string := 'SELECT COUNT(*) ' ||
'FROM ' ||
asg_base.G_OLITE_SCHEMA || '.c$all_clients ' ||
'WHERE synctime_start is not null and ' ||
'clientid = :1';
UPDATE asg_user
SET cookie = 'PALM'
WHERE user_name = l_current_user;
SELECT nvl(custom, 'N')
FROM asg_pub
WHERE name = l_pub_name;
INSERT INTO
asg_purge_sdq(user_name,pub_name,transaction_id,CREATION_DATE,CREATED_BY,
LAST_UPDATE_DATE,LAST_UPDATED_BY)
values(p_clientid,p_pub,null,sysdate,1,sysdate,1);
UPDATE asg_purge_sdq
SET transaction_id = null,last_update_date = SYSDATE
WHERE user_name = p_clientid AND pub_name = p_pub;
SELECT object_name
FROM dba_objects
WHERE owner = 'MOBILEADMIN' AND
object_type in ('TABLE', 'VIEW') AND
object_name not like 'C__$%'
UNION
SELECT object_name
FROM dba_objects
WHERE owner = 'MOBILEADMIN' AND
object_type in ('SEQUENCE') AND
object_name not like 'M$%';
SELECT object_name
FROM dba_objects
WHERE owner = 'MOBILEADMIN' AND
object_type in ('TABLE', 'VIEW') AND
object_name not like 'C__$%'
UNION
SELECT object_name
FROM dba_objects
WHERE owner = 'MOBILEADMIN' AND
object_type in ('SEQUENCE') AND
object_name not like 'M$%';
select value into l_dec_str from asg_config
where name = p_param_name;
select oracle_username into l_schema_name
from fnd_oracle_userid
where oracle_id = l_ASG_APP_ID;
select profile_option_id, application_id
from fnd_profile_options
where ( END_dATE_ACTIVE IS NULL OR END_dATE_ACTIVE > SYSDATE )
AND profile_option_name = l_profile_name;
select profile_option_value,level_value,level_id
from fnd_profile_option_values
where application_id = l_app_id and
profile_option_id = l_profile_id and
level_id = l_level_id ;
SELECT base_object_name, inq_name
FROM asg_pub_item
WHERE enabled = 'Y';
l_sql_string := 'GRANT SELECT ON ASG_SYSTEM_DIRTY_QUEUE TO '||
l_olite_schema;
l_sql_string := 'GRANT SELECT ON ASG_DELETE_QUEUE TO '||
l_olite_schema;
l_sql_string := 'GRANT SELECT ON ASG_SEQUENCE_PARTITIONS_V TO '||
l_olite_schema;
l_sql_string := 'GRANT SELECT ON ASG_TEMP_LOB TO '||
l_olite_schema;
l_sql_string := 'GRANT SELECT ON '|| c_ioq.base_object_name ||
' TO ' || l_olite_schema;
l_sql_string := 'GRANT SELECT ON ' || c_ioq.inq_name ||
' TO ' || l_olite_schema;
l_sql_string := 'GRANT INSERT ON ' || c_ioq.inq_name ||
' TO ' || l_olite_schema;
l_sql_string := 'GRANT UPDATE ON ' || c_ioq.inq_name ||
' TO ' || l_olite_schema;
l_sql_string := 'GRANT DELETE ON ' || c_ioq.inq_name ||
' TO ' || l_olite_schema;