[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_qry := 'select '||l_pkstr||' from asg_delete_queue where qid = :1';
l_qry := 'select client_id,pub_item,access_id,dml_type, '||
' transaction_id,null pk_val,sysdate synch_time '||
' from asg_system_dirty_queue '||
' where download_flag=''Y'' and client_id=asg_base.get_user_name'||
' and transaction_id = asg_base.get_current_tranid '||
' and dml_type <> 0 '||
' and pub_item in ( select item_id from asg_pub_item '||
' where nvl(enable_download_events,''N'') = ''Y'' )'||
' UNION ALL '||
' select client_id,pub_item,access_id,dml_type, '||
' transaction_id , asg_download.get_pk(pub_item,sdq.qid) pk_val, '||
' sysdate synch_time '||
' from asg_system_dirty_queue sdq,asg_delete_queue dq '||
' where download_flag=''Y'' and client_id=asg_base.get_user_name'||
' and transaction_id = asg_base.get_current_tranid '||
' and dml_type = 0 '||
' and pub_item in ( select item_id from asg_pub_item '||
' where nvl(enable_download_events,''N'') = ''Y'' ) '||
' and sdq.qid=dq.qid ';
select count(*) into l_ct
from asg_system_dirty_queue
where download_flag='Y'
and client_id = asg_base.get_user_name
and transaction_id = asg_base.get_current_tranid
and pub_item in
( select item_id from asg_pub_item
where nvl(enable_download_events,'N') = 'Y');
select asg_events_s.nextval into l_seq from dual;
l_qry := 'select client_id,pub_item,access_id,dml_type, '||
' transaction_id last_tran_id,asg_base.get_current_tranid '||
' curr_tran_id ,null pk_val,sysdate synch_time '||
' from asg_system_dirty_queue '||
' where download_flag=''Y'' and client_id=asg_base.get_user_name'||
' and transaction_id <= asg_base.get_last_tranid '||
' and dml_type <> 0 '||
' and pub_item in ( select item_id from asg_pub_item '||
' where nvl(enable_download_events,''N'') = ''Y'' )'||
' UNION ALL '||
' select client_id,pub_item,access_id,dml_type, '||
' transaction_id last_tran_id,asg_base.get_current_tranid '||
' curr_tran_id, asg_download.get_pk(pub_item,sdq.qid) pk_val, '||
' sysdate synch_time '||
' from asg_system_dirty_queue sdq,asg_delete_queue dq '||
' where download_flag=''Y'' and client_id=asg_base.get_user_name'||
' and transaction_id <= asg_base.get_last_tranid '||
' and dml_type = 0 '||
' and pub_item in ( select item_id from asg_pub_item '||
' where nvl(enable_download_events,''N'') = ''Y'' )'||
' and sdq.qid=dq.qid ';
select count(*) into l_ct
from asg_system_dirty_queue
where download_flag='Y'
and client_id = asg_base.get_user_name
and transaction_id <= asg_base.get_last_tranid
and pub_item in
( select item_id from asg_pub_item
where nvl(enable_download_events,'N') = 'Y');
select asg_events_s.nextval into l_seq from dual;
l_query := 'SELECT COUNT(*) FROM '||CONS_SCHEMA||'.'||'c$pub_list_q '
||' WHERE comp_ref <> ''Y''';
/** Function to Capture the PK of a Deleted Record
* in asg_delete_queue */
FUNCTION storeDeletedPK ( p_pub_item IN VARCHAR2,
p_accessList IN access_list,
p_qidList IN qid_list
) RETURN BOOLEAN IS
l_pk_list VARCHAR2(500);
log ('Function storeDeletedPK');
l_dml := 'SELECT base_owner, base_object_name, access_owner, access_name '||
' FROM asg_pub_item WHERE name=:1';
DBMS_SQL.PARSE (l_cur_id, 'INSERT INTO asg_delete_queue '
|| '(qid, creation_date, created_by, '
|| 'last_update_date, last_updated_by '
|| l_att_col_list
|| ') SELECT :1, sysdate, '
|| '1, sysdate, 1, '||l_pk_list
|| ' FROM '||l_base_owner||'.'||l_base_object
|| ' WHERE access_id = :2', DBMS_SQL.v7);
DBMS_SQL.PARSE (l_cur_id, 'INSERT INTO asg_delete_queue '
|| ' (qid, creation_date, '
|| 'created_by, last_update_date, '
|| 'last_updated_by '||l_att_col_list
|| ') SELECT :1, sysdate, '
|| '1, sysdate, 1, '||l_pk_list
|| ' FROM '||l_access_owner||'.'||l_access_name
|| ' WHERE access_id = :2', DBMS_SQL.v7);
log ('END Function storeDeletedPK');
END storeDeletedPK;
/** Function to Capture the PK of a Deleted Record
* in asg_delete_queue given the PK List */
FUNCTION storeDeletedPK ( p_pub_item IN VARCHAR2,
p_qid IN NUMBER,
p_pkvalList IN pk_list
) RETURN BOOLEAN IS
l_att_col_list VARCHAR2(500);
log ('Function storeDeletedPK - with PK Values Given');
l_dml := 'INSERT INTO asg_delete_queue (qid, creation_date, '
|| 'created_by, last_update_date, last_updated_by '
|| l_att_col_list || ') VALUES '
|| ' ( ' || p_qid || ', sysdate, 1, sysdate, 1 '||l_pk_val_list
|| ')';
log ('END Function storeDeletedPK - with PK Values Given');
END storeDeletedPK;
/** Function to store the PK of a Deleted Record
* in asg_delete_queue - For Reject Record */
FUNCTION storeDeletedPK ( p_pub_item IN VARCHAR2,
p_client_name IN VARCHAR2,
p_tran_id IN NUMBER,
p_seq_no IN NUMBER,
p_qid IN NUMBER
) RETURN BOOLEAN IS
l_pk_list VARCHAR2(500);
log ('Function storeDeletedPK - Reject Record');
l_dml := 'SELECT inq_owner, inq_name FROM asg_pub_item '||
' WHERE name=:1';
l_dml := 'INSERT INTO asg_delete_queue (qid, creation_date, '
|| 'created_by, last_update_date, last_updated_by '||l_att_col_list
|| ') SELECT :1, sysdate, '
|| '1, sysdate, 1, '||l_pk_list
|| ' FROM '||l_inq_owner||'.'||l_inq_name
|| ' WHERE clid$$cs = :2 AND TRANID$$ = :3 AND seqno$$ = :4';
log ('END Function storeDeletedPK - Reject Record');
END storeDeletedPK;
IF (insert_sdq(p_pub_item,p_user_name) AND
is_exists(p_user_name,p_pub_item,p_seq_no,'D'))
THEN
INSERT INTO asg_system_dirty_queue (
qid, creation_date, created_by, last_update_date, last_updated_by,
pub_item, access_id, client_id, transaction_id
, dml_type, download_flag)
VALUES (
asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
p_pub_item, p_seq_no, p_user_name, NULL , 0, NULL)
RETURNING qid INTO l_qid;
l_rc := storeDeletedPK(p_pub_item, p_user_name, p_tran_id,
p_seq_no, l_qid);
IF (insert_sdq(p_pub_item,p_username) AND
is_exists(p_username,p_pub_item,p_accessid,p_dml))
THEN
INSERT INTO asg_system_dirty_queue (
qid, creation_date, created_by, last_update_date, last_updated_by,
pub_item, access_id, client_id, transaction_id
, dml_type, download_flag)
VALUES (
asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
p_pub_item, p_accessid, p_username, NULL ,
DECODE(p_dml,'D',0,'I',1,'U',2), NULL)
RETURNING qid INTO l_qid;
l_rc := storeDeletedPK(p_pub_item, l_accesslist, l_qidlist);
IF (insert_sdq(p_pub_item,p_username) AND
is_exists(p_username,p_pub_item,p_accessid,p_dml))
THEN
INSERT INTO asg_system_dirty_queue (
qid, creation_date, created_by, last_update_date, last_updated_by,
pub_item, access_id, client_id, transaction_id
, dml_type, download_flag)
VALUES (
asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
p_pub_item, p_accessid, p_username, NULL ,
DECODE(p_dml,'D',0,'I',1,'U',2), NULL)
RETURNING qid INTO l_qid;
l_rc := storeDeletedPK(p_pub_item, l_qid, p_pkvalues);
IF (insert_sdq(p_pub_item,p_username_list(i))
AND is_exists(p_username_list(i),p_pub_item,
p_accessList(i),p_dmlList(i)))
THEN
l_tmp_access_list(l_ctr) := p_accessList(i);
INSERT INTO asg_system_dirty_queue (
qid, creation_date, created_by, last_update_date, last_updated_by,
pub_item, access_id, client_id, transaction_id
, dml_type, download_flag)
VALUES (
asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
p_pub_item, l_tmp_access_list(i), l_tmp_username_list(i),
NULL , DECODE(l_tmp_dml_list(i),'D',0,'I',1,'U',2), NULL)
RETURNING qid BULK COLLECT INTO l_qid_comp_list;
l_rc := storeDeletedPK(p_pub_item, l_accesslist, l_qid_pruned_list);
IF ( insert_sdq(p_pub_item,p_username_list(i)) AND
is_exists(p_username_list(i),p_pub_item,p_accessList(i),p_dml_type))
THEN
l_tmp_access_list(l_ctr) := p_accessList(i);
INSERT INTO asg_system_dirty_queue (
qid, creation_date, created_by, last_update_date, last_updated_by,
pub_item, access_id, client_id, transaction_id
, dml_type, download_flag)
VALUES (
asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
p_pub_item, l_tmp_access_list(i), l_tmp_username_list(i),
NULL , DECODE(p_dml_type,'D',0,'I',1,'U',2), NULL)
RETURNING qid BULK COLLECT INTO l_qid_list;
l_rc := storeDeletedPK(p_pub_item, l_tmp_access_list, l_qid_list);
IF( insert_sdq(p_pub_item,l_mobile_user) AND
is_exists(l_mobile_user,p_pub_item,p_accessList(k),p_dml_type))
THEN
l_tmp_access_list(l_ctr) := p_accessList(k);
INSERT INTO asg_system_dirty_queue (
qid, creation_date, created_by, last_update_date, last_updated_by,
pub_item, access_id, client_id, transaction_id
, dml_type, download_flag)
VALUES (
asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
p_pub_item, l_tmp_access_list(j), l_mobile_user, NULL ,
DECODE(p_dml_type,'D',0,'I',1,'U',2), NULL)
RETURNING qid BULK COLLECT INTO l_qid_list;
l_rc := storeDeletedPK(p_pub_item, l_tmp_access_list, l_qid_list);
EXECUTE IMMEDIATE 'SELECT primary_key_column, base_owner, base_object_name '
|| ' FROM ASG_PUB_ITEM WHERE name = :pi '
INTO l_pk_list, l_base_owner, l_base_object_name
USING upper(p_pub_item);
l_dml := 'SELECT column_name, data_type FROM all_tab_columns '
|| ' WHERE owner = :1 AND table_name = :2 '
|| ' AND COLUMN_NAME = :3';
l_dml := 'SELECT a.name, a.comp_ref, b.online_query ' ||
'FROM ' || CONS_SCHEMA || '.c$pub_list_q a, asg_pub_item b ' ||
'WHERE a.name = b.name ' ||
'ORDER by online_query desc, comp_ref desc';
l_tmpqry := 'select qid from asg_system_dirty_queue where client_id='''||
p_clientid|| ''' and dml_type=0 and ' ||
' pub_item in ('||l_complete_ref_pub_items||')';
l_tmpqry := 'select qid from asg_system_dirty_queue where '||
'client_id = :1 and dml_type=0 and ' ||
' pub_item in ('||l_complete_ref_pub_items||')';
l_tmpqry := 'delete from asg_system_dirty_queue where client_id= :1 ' ||
' and pub_item in ('||l_complete_ref_pub_items||')';
delete from asg_system_dirty_queue
where client_id = p_clientid
and pub_item = l_complete_ref_pub_items_lst(i);
log(' After Delete SDQ : '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
DBMS_SQL.PARSE (l_cur, 'DELETE FROM asg_delete_queue '
|| ' WHERE qid in (:2)', DBMS_SQL.v7);
log(' After Delete delQ : '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
l_dml := 'SELECT DISTINCT pub_item FROM asg_system_dirty_queue '
|| ' WHERE client_id='''||p_clientid
||''' AND (transaction_id IS NULL '
|| ' OR transaction_id > '||p_last_tranid
||') AND pub_item IN ('||l_incr_ref_pub_items||')';
l_dml := 'SELECT DISTINCT pub_item FROM asg_system_dirty_queue '
|| ' WHERE client_id= :1 AND (transaction_id IS NULL '
|| ' OR transaction_id > :2 ) '
|| ' AND pub_item IN ('||l_incr_ref_pub_items||')';
l_dml := 'UPDATE asg_system_dirty_queue SET '
|| ' transaction_id = :1, download_flag=NULL, '
|| ' last_update_date = sysdate '
|| ' WHERE client_id = :2 AND ( transaction_id IS NULL '
|| ' OR transaction_id > :3 ) AND '
|| ' pub_item IN ( :4 )';
update asg_system_dirty_queue
set transaction_id = p_curr_tranid,
download_flag = null,
last_update_date = sysdate
where client_id = p_clientid
and ( transaction_id is null or transaction_id > p_last_tranid )
and pub_item = l_changed_pub_items_lst(j);
UPDATE ASG_SYSTEM_DIRTY_QUEUE
SET download_flag = 'Y'
WHERE qid IN (select qid from ASG_SDQ_UPDATE_V );
/** Insert the complete refresh pubitems into the dirty queue */
IF ( l_compref_list.COUNT > 0 ) THEN
log(' Before Complete Refresh Insert: '||
to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
SELECT base_owner, base_object_name,
NVL(QUERY_ACCESS_TABLE, 'N'), ACCESS_TABLE_PREDICATE_LIST,
access_owner, access_name
INTO l_base_owner, l_base_object, l_query_access, l_predicate_list,
l_access_owner, l_access_name
FROM asg_pub_item WHERE name=l_compref_list(i);
l_dml := 'INSERT INTO asg_system_dirty_queue ( ' ||
'qid, creation_date, created_by, last_update_date,' ||
'last_updated_by, pub_item, access_id, client_id, ' ||
'transaction_id, dml_type, download_flag) '||
' SELECT asg_system_dirty_queue_s.nextval, SYSDATE, 1, ' ||
' SYSDATE, 1, :1, ' ||
' uniqpiv.access_id, ' ||
' :2, :3, ' ||
' 1, ''Y'' FROM (SELECT DISTINCT ACCESS_ID FROM ' ||
l_base_owner ||'.' ||l_base_object ||
' ) uniqpiv';
l_dml := 'INSERT INTO asg_system_dirty_queue ( ' ||
'qid, creation_date, created_by, last_update_date,' ||
'last_updated_by, pub_item, access_id, client_id, ' ||
'transaction_id, dml_type, download_flag) '||
' SELECT asg_system_dirty_queue_s.nextval, SYSDATE, 1, ' ||
' SYSDATE, 1, :1, ' ||
' uniqacc.access_id, ' ||
' :2, :3, ' ||
' 1, ''Y'' FROM (SELECT DISTINCT ACCESS_ID FROM ' ||
l_access_owner || '.' || l_access_name;
log(' After Complete Refresh Insert: '||
to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
/** Delete pub items which are not in l_changed_pub_items
* , l_complete_ref_pub_items AND l_online_item_list */
IF ( l_webtogo_list IS NULL ) THEN
l_dml := 'DELETE FROM '||CONS_SCHEMA||'.c$pub_list_q ' ||
'WHERE name in (select name from asg_pub_item)';
l_dml := 'DELETE FROM '||CONS_SCHEMA||'.c$pub_list_q '
|| ' WHERE name NOT IN (' || l_webtogo_list || ') AND ' ||
' name in (select name from asg_pub_item)';
DELETE FROM asg_system_dirty_queue
WHERE client_id = g_clientid AND
transaction_id IS NOT NULL;
select nvl(fnd_profile.value_specific('ASG_ENABLE_DELIVERY_EVENTS'),'N')
into l_prof_value from dual;
/* Clean the Delete Queue */
DELETE FROM asg_delete_queue
WHERE qid IN (SELECT qid
FROM asg_system_dirty_queue
WHERE client_id = g_clientid AND
transaction_id <= g_last_tranid );
DELETE FROM asg_system_dirty_queue
WHERE client_id = g_clientid AND
transaction_id <= g_last_tranid;
/* Clean the Delete Queue */
log('PurgeSDQ for '||p_clientid);
DELETE FROM asg_delete_queue
WHERE qid IN (SELECT qid
FROM asg_system_dirty_queue
WHERE client_id = p_clientid);
DELETE FROM asg_system_dirty_queue
WHERE client_id = p_clientid;
SELECT user_name
FROM asg_user
WHERE user_id = p_userid
AND ENABLED ='Y';
SELECT nvl(value,'N') INTO g_purge_log_enabled
FROM asg_config WHERE name='ENABLE_PURGE_LOGGING';
takes a user name and deletes all duplicate records for that user
from SDQ and DQ
*/
PROCEDURE delete_duplicate_records(l_user_name varchar2)
is
l_dml VARCHAR2(1000);
l_dml := 'Delete /*+ INDEX(asg_delete_queue ASG_DELETE_QUEUE_U1) */ from '||
' asg_delete_queue where qid in '||
' ( Select qid from '||
' ( select qid, pub_item, access_id, dml_type, '||
' count(*) over (partition by pub_item, access_id, dml_type)'||
' as total_rows, '||
' min(qid) over (partition by pub_item, access_id, dml_type)'||
' as min_qid '||
' from asg_system_dirty_queue where client_id = :1 AND '||
' TRANSACTION_ID IS NULL and download_flag is null '||
' ) '||
' where qid <> min_qid and total_rows >1 )';
/* log_concprogram('Removed '||l_count||' duplicate rows from Delete queue '||
' for user : '||l_user_name,
'asg_download',
FND_LOG.LEVEL_STATEMENT);*/
l_dml := 'Delete /*+ INDEX(asg_system_dirty_queue ASG_SYSTEM_DIRTY_QUEUE_U1) */ from asg_system_dirty_queue where qid in '||
' ( Select qid from '||
' ( select qid, pub_item, access_id, dml_type, '||
' count(*) over (partition by pub_item, access_id, dml_type)'||
' as total_rows, '||
' min(qid) over (partition by pub_item, access_id, dml_type)'||
' as min_qid '||
' from asg_system_dirty_queue where client_id = :1 AND '||
' TRANSACTION_ID IS NULL and download_flag is null '||
' ) '||
' where qid <> min_qid and total_rows >1 )';
END delete_duplicate_records;
takes a user name and inserts records for each publication subscribed
by the user into asg_purge_sdq
*/
PROCEDURE set_user_first_synch(l_user_name varchar2)
is
PRAGMA autonomous_transaction;
INSERT INTO asg_purge_sdq(user_name,pub_name,creation_date,created_by,
last_update_date,last_updated_by )
( SELECT user_name,pub_name ,sysdate,1,sysdate,1
FROM asg_user_pub_resps
WHERE user_name = l_user_name
AND pub_name IN
(select name from asg_pub where nvl(custom,'N') = 'N' )
);
takes a user name and inserts records for each publication subscribed
by the user into asg_purge_sdq
*/
PROCEDURE set_user_first_synch_pub(l_user_name varchar2,l_pub_name varchar2)
is
PRAGMA autonomous_transaction;
SELECT COUNT(*) INTO l_count
FROM asg_purge_sdq
WHERE user_name = l_user_name
AND pub_name = l_pub_name;
INSERT INTO asg_purge_sdq(user_name,pub_name,creation_date,created_by,
last_update_date,last_updated_by)
VALUES (l_user_name,l_pub_name,sysdate,1,sysdate,1);
PROCEDURE delete_Sdq( P_status OUT NOCOPY VARCHAR2,
P_message OUT NOCOPY VARCHAR2)
IS
l_user_id NUMBER;
SELECT user_name,pub_name
FROM asg_user_pub_resps
WHERE trunc( sysdate - NVL(synch_date,to_date('1', 'J')) )
> l_dormancy_period
AND pub_name IN ( SELECT NAME FROM asg_pub WHERE nvl(custom,'N') = 'N' )
and user_name > l_last_processed
and user_name <=l_last_user
ORDER BY user_name;
SELECT value FROM asg_config
WHERE name='ASG_SDQ_PURGE_LAST_USER';
SELECT value FROM asg_config
WHERE NAME='ASG_SDQ_PURGE_LAST_DUPDEL';
SELECT user_name FROM
(
SELECT user_name FROM asg_user
WHERE user_name > l_last_processed
and user_name not in
( select distinct user_name
from asg_purge_sdq where TRANSACTION_ID IS NULL )
ORDER BY user_name
) WHERE ROWNUM <= l_max_num;
SELECT user_name FROM (
SELECT ROWNUM pos,user_name FROM
(
SELECT DISTINCT user_name
FROM asg_user_pub_resps
WHERE user_name > l_last_processed
AND TRUNC( SYSDATE - NVL(synch_date,TO_DATE('1', 'J')) )
> l_dormancy_period
ORDER BY user_name
)
) WHERE pos = l_num_users;
SELECT COUNT(*) FROM (
SELECT ROWNUM pos,user_name FROM
(
SELECT DISTINCT user_name
FROM asg_user_pub_resps
WHERE user_name > l_last_processed
AND TRUNC( SYSDATE - NVL(synch_date,TO_DATE('1', 'J')) )
> l_dormancy_period
ORDER BY user_name
) );
delete from asg_conf_info
where (sysdate-creation_date) >l_purge_conf_interval;
SELECT SYSDATE INTO l_date FROM dual;
log_concprogram('Starting to delete duplicate records : '||
to_char(l_date,'dd-mon-yyyy hh24:mi:ss'),
'asg_download',FND_LOG.LEVEL_STATEMENT);
SELECT SYSDATE INTO l_date FROM dual;
delete_duplicate_records(l_user_name);
UPDATE asg_config SET value=nvl(l_last_user,value)
WHERE NAME='ASG_SDQ_PURGE_LAST_DUPDEL';
SELECT SYSDATE INTO l_date FROM dual;
DELETE /*+ INDEX(asg_delete_queue ASG_DELETE_QUEUE_U1) */ FROM
asg_delete_queue
WHERE qid IN
( SELECT qid
FROM asg_system_dirty_queue
WHERE client_id = l_dormant_rec.user_name
AND pub_item in
(SELECT item_id FROM asg_pub_item
WHERE pub_name=l_dormant_rec.pub_name));
DELETE FROM asg_system_dirty_queue
WHERE client_id = l_dormant_rec.user_name
AND pub_item in
(SELECT item_id FROM asg_pub_item
WHERE pub_name=l_dormant_rec.pub_name);
DELETE FROM asg_complete_refresh
WHERE user_name = l_dormant_rec.user_name
AND publication_item IN
(SELECT item_id FROM asg_pub_item
WHERE pub_name = l_dormant_rec.pub_name);
DELETE FROM asg_purge_sdq
WHERE user_name = l_dormant_rec.user_name
AND pub_name = l_dormant_rec.pub_name;
/* change this .. has to insert for current publicatino..*/
set_user_first_synch_pub(l_dormant_rec.user_name,
l_dormant_rec.pub_name);
UPDATE asg_config
SET value = nvl(l_last_user,value)
WHERE NAME ='ASG_SDQ_PURGE_LAST_USER';
UPDATE jtm_con_request_data
SET last_run_date = SYSDATE
WHERE package_name = 'ASG_DOWNLOAD'
AND procedure_name = 'DELETE_SDQ';
p_message := 'Purging asg_system_dirty_queue and asg_delete_queue completed successfully.';
END delete_Sdq;
function to verify whether record shd be inserted into SDQ or not
return values :
If it returns "false" then the record need not be inserted into SDQ ..
it may be that:
1. records for the pub item or the corresponding publication
exists in asg_complete_refresh or asg_purge_sdq
If the return value is "true" then record is inserted.
--
*/
FUNCTION insert_sdq(p_pub_item varchar2,p_user_name varchar2) RETURN boolean
IS
CURSOR c_pub_name(l_pub_item varchar2)
IS
SELECT pub_name FROM asg_pub_item WHERE item_id = p_pub_item;
SELECT user_name FROM asg_complete_refresh
WHERE user_name = l_un AND publication_item = l_pi
AND synch_completed = 'N';
SELECT user_name FROM asg_purge_Sdq
WHERE user_name = l_un
AND pub_name = l_pub
AND transaction_id IS null;
END insert_sdq;
if record exists then "false" - so need not be inserted again
if record doesn't exist then "true" - insert into SDQ
*/
FUNCTION is_exists(p_clientid varchar2, p_pub_item varchar2,
p_access_id number,p_dml_type char)
RETURN boolean
IS
CURSOR c_is_exists_in_sdq(p_clientid varchar2, p_pub_item varchar2,
p_access_id number,p_dml_type varchar2)
IS
SELECT client_id FROM asg_system_dirty_queue
WHERE client_id = p_clientid AND pub_item = p_pub_item
AND access_id = p_access_id
AND dml_type = DECODE(p_dml_type,'D',0,'I',1,'U',2)
AND transaction_id IS NULL AND download_flag IS null;
PROCEDURE delete_synch_history( P_status OUT NOCOPY VARCHAR2,
P_message OUT NOCOPY VARCHAR2)
IS
l_purge_interval NUMBER ;
/*l_qry := 'delete from '||CONS_SCHEMA||'.'||'c$sync_history where '
||' (sysdate-start_time) > '||l_purge_interval||' ';
log_concprogram('Deleted '||l_row_count||' row(s)',
'asg_download',
FND_LOG.LEVEL_STATEMENT);
l_qry := 'SELECT session_id ' ||
'FROM ' || CONS_SCHEMA || '.' || 'c$sync_history ' ||
'WHERE start_time < (trunc(sysdate) - ' || l_purge_interval || ')';
l_purge_session_data.delete;
l_qry := 'delete from '||CONS_SCHEMA||'.'||'c$sync_history where ' || ' session_id = :1 ';
log_concprogram('Deleted '||l_row_count||' row(s)',
'asg_download',
FND_LOG.LEVEL_STATEMENT);
UPDATE jtm_con_request_data
SET last_run_date = SYSDATE
WHERE package_name = 'ASG_DOWNLOAD'
AND procedure_name = 'DELETE_SYNCH_HISTORY';
END delete_synch_history;
select user_name,user_id,resource_id from asg_user where
enabled='Y' and nvl(DISABLE_USER_SYNCH,'N') = 'N';
select user_name from fnd_user where user_id = p_user_id;
select user_id from fnd_user where user_name = p_user_name;
select user_name from jtf_rs_resource_extns
where resource_id = p_res_id
and ( trunc(END_DATE_ACTIVE) is null
or trunc(END_DATE_ACTIVE) > trunc(sysdate) );
update asg_user
set DISABLE_USER_SYNCH='Y',DISABLE_SYNCH_ERROR = l_err_msg
where user_name = l_asg_user_rec.user_name;
update asg_user
set DISABLE_USER_SYNCH='Y',DISABLE_SYNCH_ERROR = l_err_msg
where user_name = l_asg_user_rec.user_name;
update asg_user set DISABLE_USER_SYNCH='Y',DISABLE_SYNCH_ERROR = l_err_msg
where user_name = l_asg_user_rec.user_name;
update asg_user
set DISABLE_USER_SYNCH='Y',DISABLE_SYNCH_ERROR = l_err_msg
where user_name = l_asg_user_rec.user_name;
update asg_user
set DISABLE_USER_SYNCH='Y',DISABLE_SYNCH_ERROR = l_err_msg
where user_name = l_asg_user_rec.user_name;