eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ASG_DOWNLOAD dependencies on ASG_SYSTEM_DIRTY_QUEUE

Line 61: ' from asg_system_dirty_queue '||

57: begin
58: log('Start raise_data_downloaded');
59: l_qry := 'select client_id,pub_item,access_id,dml_type, '||
60: ' transaction_id,null pk_val,sysdate synch_time '||
61: ' from asg_system_dirty_queue '||
62: ' where download_flag=''Y'' and client_id=asg_base.get_user_name'||
63: ' and transaction_id = asg_base.get_current_tranid '||
64: ' and dml_type <> 0 '||
65: ' and pub_item in ( select item_id from asg_pub_item '||

Line 71: ' from asg_system_dirty_queue sdq,asg_delete_queue dq '||

67: ' UNION ALL '||
68: ' select client_id,pub_item,access_id,dml_type, '||
69: ' transaction_id , asg_download.get_pk(pub_item,sdq.qid) pk_val, '||
70: ' sysdate synch_time '||
71: ' from asg_system_dirty_queue sdq,asg_delete_queue dq '||
72: ' where download_flag=''Y'' and client_id=asg_base.get_user_name'||
73: ' and transaction_id = asg_base.get_current_tranid '||
74: ' and dml_type = 0 '||
75: ' and pub_item in ( select item_id from asg_pub_item '||

Line 79: from asg_system_dirty_queue

75: ' and pub_item in ( select item_id from asg_pub_item '||
76: ' where nvl(enable_download_events,''N'') = ''Y'' ) '||
77: ' and sdq.qid=dq.qid ';
78: select count(*) into l_ct
79: from asg_system_dirty_queue
80: where download_flag='Y'
81: and client_id = asg_base.get_user_name
82: and transaction_id = asg_base.get_current_tranid
83: and pub_item in

Line 125: ' from asg_system_dirty_queue '||

121: log('Start raise_data_download_confirmed');
122: l_qry := 'select client_id,pub_item,access_id,dml_type, '||
123: ' transaction_id last_tran_id,asg_base.get_current_tranid '||
124: ' curr_tran_id ,null pk_val,sysdate synch_time '||
125: ' from asg_system_dirty_queue '||
126: ' where download_flag=''Y'' and client_id=asg_base.get_user_name'||
127: ' and transaction_id <= asg_base.get_last_tranid '||
128: ' and dml_type <> 0 '||
129: ' and pub_item in ( select item_id from asg_pub_item '||

Line 136: ' from asg_system_dirty_queue sdq,asg_delete_queue dq '||

132: ' select client_id,pub_item,access_id,dml_type, '||
133: ' transaction_id last_tran_id,asg_base.get_current_tranid '||
134: ' curr_tran_id, asg_download.get_pk(pub_item,sdq.qid) pk_val, '||
135: ' sysdate synch_time '||
136: ' from asg_system_dirty_queue sdq,asg_delete_queue dq '||
137: ' where download_flag=''Y'' and client_id=asg_base.get_user_name'||
138: ' and transaction_id <= asg_base.get_last_tranid '||
139: ' and dml_type = 0 '||
140: ' and pub_item in ( select item_id from asg_pub_item '||

Line 144: from asg_system_dirty_queue

140: ' and pub_item in ( select item_id from asg_pub_item '||
141: ' where nvl(enable_download_events,''N'') = ''Y'' )'||
142: ' and sdq.qid=dq.qid ';
143: select count(*) into l_ct
144: from asg_system_dirty_queue
145: where download_flag='Y'
146: and client_id = asg_base.get_user_name
147: and transaction_id <= asg_base.get_last_tranid
148: and pub_item in

Line 495: INSERT INTO asg_system_dirty_queue (

491: -- Mark Publication Item Dirty
492: IF (insert_sdq(p_pub_item,p_user_name) AND
493: is_exists(p_user_name,p_pub_item,p_seq_no,'D'))
494: THEN
495: INSERT INTO asg_system_dirty_queue (
496: qid, creation_date, created_by, last_update_date, last_updated_by,
497: pub_item, access_id, client_id, transaction_id
498: , dml_type, download_flag)
499: VALUES (

Line 500: asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,

496: qid, creation_date, created_by, last_update_date, last_updated_by,
497: pub_item, access_id, client_id, transaction_id
498: , dml_type, download_flag)
499: VALUES (
500: asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
501: p_pub_item, p_seq_no, p_user_name, NULL , 0, NULL)
502: RETURNING qid INTO l_qid;
503:
504: l_rc := storeDeletedPK(p_pub_item, p_user_name, p_tran_id,

Line 539: INSERT INTO asg_system_dirty_queue (

535: -- Mark Publication Item Dirty
536: IF (insert_sdq(p_pub_item,p_username) AND
537: is_exists(p_username,p_pub_item,p_accessid,p_dml))
538: THEN
539: INSERT INTO asg_system_dirty_queue (
540: qid, creation_date, created_by, last_update_date, last_updated_by,
541: pub_item, access_id, client_id, transaction_id
542: , dml_type, download_flag)
543: VALUES (

Line 544: asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,

540: qid, creation_date, created_by, last_update_date, last_updated_by,
541: pub_item, access_id, client_id, transaction_id
542: , dml_type, download_flag)
543: VALUES (
544: asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
545: p_pub_item, p_accessid, p_username, NULL ,
546: DECODE(p_dml,'D',0,'I',1,'U',2), NULL)
547: RETURNING qid INTO l_qid;
548:

Line 613: INSERT INTO asg_system_dirty_queue (

609: -- Mark Publication Item Dirty
610: IF (insert_sdq(p_pub_item,p_username) AND
611: is_exists(p_username,p_pub_item,p_accessid,p_dml))
612: THEN
613: INSERT INTO asg_system_dirty_queue (
614: qid, creation_date, created_by, last_update_date, last_updated_by,
615: pub_item, access_id, client_id, transaction_id
616: , dml_type, download_flag)
617: VALUES (

Line 618: asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,

614: qid, creation_date, created_by, last_update_date, last_updated_by,
615: pub_item, access_id, client_id, transaction_id
616: , dml_type, download_flag)
617: VALUES (
618: asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
619: p_pub_item, p_accessid, p_username, NULL ,
620: DECODE(p_dml,'D',0,'I',1,'U',2), NULL)
621: RETURNING qid INTO l_qid;
622: IF (p_dml = 'D') THEN

Line 711: INSERT INTO asg_system_dirty_queue (

707: -- Mark Records Dirty for a Mobile User
708: IF( nvl(l_tmp_access_list.COUNT,0) <> 0 )
709: THEN
710: FORALL i IN l_tmp_access_list.FIRST..l_tmp_access_list.LAST
711: INSERT INTO asg_system_dirty_queue (
712: qid, creation_date, created_by, last_update_date, last_updated_by,
713: pub_item, access_id, client_id, transaction_id
714: , dml_type, download_flag)
715: VALUES (

Line 716: asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,

712: qid, creation_date, created_by, last_update_date, last_updated_by,
713: pub_item, access_id, client_id, transaction_id
714: , dml_type, download_flag)
715: VALUES (
716: asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
717: p_pub_item, l_tmp_access_list(i), l_tmp_username_list(i),
718: NULL , DECODE(l_tmp_dml_list(i),'D',0,'I',1,'U',2), NULL)
719: RETURNING qid BULK COLLECT INTO l_qid_comp_list;
720:

Line 844: INSERT INTO asg_system_dirty_queue (

840: -- Mark Records Dirty for a Mobile User
841: IF( nvl(l_tmp_access_list.COUNT,0) <> 0 )
842: THEN
843: FORALL i IN l_tmp_access_list.FIRST..l_tmp_access_list.LAST
844: INSERT INTO asg_system_dirty_queue (
845: qid, creation_date, created_by, last_update_date, last_updated_by,
846: pub_item, access_id, client_id, transaction_id
847: , dml_type, download_flag)
848: VALUES (

Line 849: asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,

845: qid, creation_date, created_by, last_update_date, last_updated_by,
846: pub_item, access_id, client_id, transaction_id
847: , dml_type, download_flag)
848: VALUES (
849: asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
850: p_pub_item, l_tmp_access_list(i), l_tmp_username_list(i),
851: NULL , DECODE(p_dml_type,'D',0,'I',1,'U',2), NULL)
852: RETURNING qid BULK COLLECT INTO l_qid_list;
853: IF (p_dml_type = 'D') THEN

Line 970: INSERT INTO asg_system_dirty_queue (

966: IF( nvl(l_tmp_access_list.COUNT,0) <> 0 )
967: THEN
968: FORALL j IN 1..l_tmp_access_list.COUNT
969: -- Mark Publication Item Dirty
970: INSERT INTO asg_system_dirty_queue (
971: qid, creation_date, created_by, last_update_date, last_updated_by,
972: pub_item, access_id, client_id, transaction_id
973: , dml_type, download_flag)
974: VALUES (

Line 975: asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,

971: qid, creation_date, created_by, last_update_date, last_updated_by,
972: pub_item, access_id, client_id, transaction_id
973: , dml_type, download_flag)
974: VALUES (
975: asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
976: p_pub_item, l_tmp_access_list(j), l_mobile_user, NULL ,
977: DECODE(p_dml_type,'D',0,'I',1,'U',2), NULL)
978: RETURNING qid BULK COLLECT INTO l_qid_list;
979: IF (p_dml_type = 'D') THEN

Line 1229: l_user_name asg_system_dirty_queue.client_id%TYPE;

1225: l_changed_pub_items VARCHAR2(32767) := NULL;
1226: l_webtogo_list VARCHAR2(32767) := NULL;
1227: l_online_item_list VARCHAR2(32767) := NULL;
1228: l_compref_list pk_list;
1229: l_user_name asg_system_dirty_queue.client_id%TYPE;
1230: l_c_client_id asg_system_dirty_queue.client_id%TYPE;
1231: l_c_pub_item asg_system_dirty_queue.pub_item%TYPE;
1232: l_c_access_id asg_system_dirty_queue.access_id%TYPE;
1233: l_c_dml_type asg_system_dirty_queue.dml_type%TYPE;

Line 1230: l_c_client_id asg_system_dirty_queue.client_id%TYPE;

1226: l_webtogo_list VARCHAR2(32767) := NULL;
1227: l_online_item_list VARCHAR2(32767) := NULL;
1228: l_compref_list pk_list;
1229: l_user_name asg_system_dirty_queue.client_id%TYPE;
1230: l_c_client_id asg_system_dirty_queue.client_id%TYPE;
1231: l_c_pub_item asg_system_dirty_queue.pub_item%TYPE;
1232: l_c_access_id asg_system_dirty_queue.access_id%TYPE;
1233: l_c_dml_type asg_system_dirty_queue.dml_type%TYPE;
1234: l_cur_rc NUMBER;

Line 1231: l_c_pub_item asg_system_dirty_queue.pub_item%TYPE;

1227: l_online_item_list VARCHAR2(32767) := NULL;
1228: l_compref_list pk_list;
1229: l_user_name asg_system_dirty_queue.client_id%TYPE;
1230: l_c_client_id asg_system_dirty_queue.client_id%TYPE;
1231: l_c_pub_item asg_system_dirty_queue.pub_item%TYPE;
1232: l_c_access_id asg_system_dirty_queue.access_id%TYPE;
1233: l_c_dml_type asg_system_dirty_queue.dml_type%TYPE;
1234: l_cur_rc NUMBER;
1235: l_rc NUMBER;

Line 1232: l_c_access_id asg_system_dirty_queue.access_id%TYPE;

1228: l_compref_list pk_list;
1229: l_user_name asg_system_dirty_queue.client_id%TYPE;
1230: l_c_client_id asg_system_dirty_queue.client_id%TYPE;
1231: l_c_pub_item asg_system_dirty_queue.pub_item%TYPE;
1232: l_c_access_id asg_system_dirty_queue.access_id%TYPE;
1233: l_c_dml_type asg_system_dirty_queue.dml_type%TYPE;
1234: l_cur_rc NUMBER;
1235: l_rc NUMBER;
1236: l_qid_tmp_list DBMS_SQL.NUMBER_TABLE;

Line 1233: l_c_dml_type asg_system_dirty_queue.dml_type%TYPE;

1229: l_user_name asg_system_dirty_queue.client_id%TYPE;
1230: l_c_client_id asg_system_dirty_queue.client_id%TYPE;
1231: l_c_pub_item asg_system_dirty_queue.pub_item%TYPE;
1232: l_c_access_id asg_system_dirty_queue.access_id%TYPE;
1233: l_c_dml_type asg_system_dirty_queue.dml_type%TYPE;
1234: l_cur_rc NUMBER;
1235: l_rc NUMBER;
1236: l_qid_tmp_list DBMS_SQL.NUMBER_TABLE;
1237: l_tmpqry VARCHAR2(32767);

Line 1289: l_tmpqry := 'select qid from asg_system_dirty_queue where client_id='''||

1285:
1286: --Fix For Bug 3075299
1287: l_counter:=1;
1288: /*
1289: l_tmpqry := 'select qid from asg_system_dirty_queue where client_id='''||
1290: p_clientid|| ''' and dml_type=0 and ' ||
1291: ' pub_item in ('||l_complete_ref_pub_items||')';
1292: */
1293: l_tmpqry := 'select qid from asg_system_dirty_queue where '||

Line 1293: l_tmpqry := 'select qid from asg_system_dirty_queue where '||

1289: l_tmpqry := 'select qid from asg_system_dirty_queue where client_id='''||
1290: p_clientid|| ''' and dml_type=0 and ' ||
1291: ' pub_item in ('||l_complete_ref_pub_items||')';
1292: */
1293: l_tmpqry := 'select qid from asg_system_dirty_queue where '||
1294: 'client_id = :1 and dml_type=0 and ' ||
1295: ' pub_item in ('||l_complete_ref_pub_items||')';
1296: l_curid := DBMS_SQL.OPEN_CURSOR;
1297: DBMS_SQL.PARSE(l_curid,l_tmpqry, DBMS_SQL.v7);

Line 1308: l_tmpqry := 'delete from asg_system_dirty_queue where client_id= :1 ' ||

1304: l_counter := l_counter+1;
1305: END LOOP;
1306:
1307: /*
1308: l_tmpqry := 'delete from asg_system_dirty_queue where client_id= :1 ' ||
1309: ' and pub_item in ('||l_complete_ref_pub_items||')';
1310: EXECUTE IMMEDIATE l_tmpqry
1311: USING p_clientid;
1312: */

Line 1316: delete from asg_system_dirty_queue

1312: */
1313: l_complete_ref_pub_items_lst := get_listfrom_string(
1314: l_complete_ref_pub_items);
1315: forall i in 1..l_complete_ref_pub_items_lst.count
1316: delete from asg_system_dirty_queue
1317: where client_id = p_clientid
1318: and pub_item = l_complete_ref_pub_items_lst(i);
1319:
1320:

Line 1345: l_dml := 'SELECT DISTINCT pub_item FROM asg_system_dirty_queue '

1341: log (' Changed Pub Items List ');
1342:
1343: l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1344: /*
1345: l_dml := 'SELECT DISTINCT pub_item FROM asg_system_dirty_queue '
1346: || ' WHERE client_id='''||p_clientid
1347: ||''' AND (transaction_id IS NULL '
1348: || ' OR transaction_id > '||p_last_tranid
1349: ||') AND pub_item IN ('||l_incr_ref_pub_items||')';

Line 1351: l_dml := 'SELECT DISTINCT pub_item FROM asg_system_dirty_queue '

1347: ||''' AND (transaction_id IS NULL '
1348: || ' OR transaction_id > '||p_last_tranid
1349: ||') AND pub_item IN ('||l_incr_ref_pub_items||')';
1350: */
1351: l_dml := 'SELECT DISTINCT pub_item FROM asg_system_dirty_queue '
1352: || ' WHERE client_id= :1 AND (transaction_id IS NULL '
1353: || ' OR transaction_id > :2 ) '
1354: || ' AND pub_item IN ('||l_incr_ref_pub_items||')';
1355:

Line 1393: l_dml := 'UPDATE asg_system_dirty_queue SET '

1389: * with Curr Tran ID */
1390: log (' Before marking records with Tran id '||p_curr_tranid
1391: ||' '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1392: /*
1393: l_dml := 'UPDATE asg_system_dirty_queue SET '
1394: || ' transaction_id = :1, download_flag=NULL, '
1395: || ' last_update_date = sysdate '
1396: || ' WHERE client_id = :2 AND ( transaction_id IS NULL '
1397: || ' OR transaction_id > :3 ) AND '

Line 1405: update asg_system_dirty_queue

1401: replace(l_changed_pub_items, '''', '');
1402: */
1403: l_changed_pub_items_lst := get_listfrom_string(l_changed_pub_items);
1404: forall j in 1..l_changed_pub_items_lst.count
1405: update asg_system_dirty_queue
1406: set transaction_id = p_curr_tranid,
1407: download_flag = null,
1408: last_update_date = sysdate
1409: where client_id = p_clientid

Line 1421: UPDATE ASG_SYSTEM_DIRTY_QUEUE

1417:
1418: /** Mark RECORDS with the right DML Operation to Send to client */
1419: log (' Before setting download_flag '
1420: ||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1421: UPDATE ASG_SYSTEM_DIRTY_QUEUE
1422: SET download_flag = 'Y'
1423: WHERE qid IN (select qid from ASG_SDQ_UPDATE_V );
1424: log (' After setting download_flag '
1425: ||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));

Line 1446: l_dml := 'INSERT INTO asg_system_dirty_queue ( ' ||

1442: /* The second condition actually implies an error in seed data but we
1443: tolerate it */
1444: IF ((l_query_access = 'N') OR
1445: (l_access_owner IS NULL OR l_access_name IS NULL)) THEN
1446: l_dml := 'INSERT INTO asg_system_dirty_queue ( ' ||
1447: 'qid, creation_date, created_by, last_update_date,' ||
1448: 'last_updated_by, pub_item, access_id, client_id, ' ||
1449: 'transaction_id, dml_type, download_flag) '||
1450: ' SELECT asg_system_dirty_queue_s.nextval, SYSDATE, 1, ' ||

Line 1450: ' SELECT asg_system_dirty_queue_s.nextval, SYSDATE, 1, ' ||

1446: l_dml := 'INSERT INTO asg_system_dirty_queue ( ' ||
1447: 'qid, creation_date, created_by, last_update_date,' ||
1448: 'last_updated_by, pub_item, access_id, client_id, ' ||
1449: 'transaction_id, dml_type, download_flag) '||
1450: ' SELECT asg_system_dirty_queue_s.nextval, SYSDATE, 1, ' ||
1451: ' SYSDATE, 1, :1, ' ||
1452: ' uniqpiv.access_id, ' ||
1453: ' :2, :3, ' ||
1454: ' 1, ''Y'' FROM (SELECT DISTINCT ACCESS_ID FROM ' ||

Line 1460: l_dml := 'INSERT INTO asg_system_dirty_queue ( ' ||

1456: ' ) uniqpiv';
1457:
1458: ELSE
1459: l_predicate_clause := get_predicate_clause(l_predicate_list);
1460: l_dml := 'INSERT INTO asg_system_dirty_queue ( ' ||
1461: 'qid, creation_date, created_by, last_update_date,' ||
1462: 'last_updated_by, pub_item, access_id, client_id, ' ||
1463: 'transaction_id, dml_type, download_flag) '||
1464: ' SELECT asg_system_dirty_queue_s.nextval, SYSDATE, 1, ' ||

Line 1464: ' SELECT asg_system_dirty_queue_s.nextval, SYSDATE, 1, ' ||

1460: l_dml := 'INSERT INTO asg_system_dirty_queue ( ' ||
1461: 'qid, creation_date, created_by, last_update_date,' ||
1462: 'last_updated_by, pub_item, access_id, client_id, ' ||
1463: 'transaction_id, dml_type, download_flag) '||
1464: ' SELECT asg_system_dirty_queue_s.nextval, SYSDATE, 1, ' ||
1465: ' SYSDATE, 1, :1, ' ||
1466: ' uniqacc.access_id, ' ||
1467: ' :2, :3, ' ||
1468: ' 1, ''Y'' FROM (SELECT DISTINCT ACCESS_ID FROM ' ||

Line 1530: DELETE FROM asg_system_dirty_queue

1526:
1527: /* Clean the System Dirty Queue */
1528: IF (isFirstSync) THEN
1529: /*First synch. So no need to raise events*/
1530: DELETE FROM asg_system_dirty_queue
1531: WHERE client_id = g_clientid AND
1532: transaction_id IS NOT NULL;
1533: ELSE
1534: select nvl(fnd_profile.value_specific('ASG_ENABLE_DELIVERY_EVENTS'),'N')

Line 1554: FROM asg_system_dirty_queue

1550: end if;
1551: /* Clean the Delete Queue */
1552: DELETE FROM asg_delete_queue
1553: WHERE qid IN (SELECT qid
1554: FROM asg_system_dirty_queue
1555: WHERE client_id = g_clientid AND
1556: transaction_id <= g_last_tranid );
1557:
1558: DELETE FROM asg_system_dirty_queue

Line 1558: DELETE FROM asg_system_dirty_queue

1554: FROM asg_system_dirty_queue
1555: WHERE client_id = g_clientid AND
1556: transaction_id <= g_last_tranid );
1557:
1558: DELETE FROM asg_system_dirty_queue
1559: WHERE client_id = g_clientid AND
1560: transaction_id <= g_last_tranid;
1561: END IF;
1562:

Line 1582: FROM asg_system_dirty_queue

1578: /* Clean the Delete Queue */
1579: log('PurgeSDQ for '||p_clientid);
1580: DELETE FROM asg_delete_queue
1581: WHERE qid IN (SELECT qid
1582: FROM asg_system_dirty_queue
1583: WHERE client_id = p_clientid);
1584:
1585: /* Clean the System Dirty Queue */
1586: DELETE FROM asg_system_dirty_queue

Line 1586: DELETE FROM asg_system_dirty_queue

1582: FROM asg_system_dirty_queue
1583: WHERE client_id = p_clientid);
1584:
1585: /* Clean the System Dirty Queue */
1586: DELETE FROM asg_system_dirty_queue
1587: WHERE client_id = p_clientid;
1588:
1589: log('PurgeSDQ done for '||p_clientid);
1590:

Line 1841: ' from asg_system_dirty_queue where client_id = :1 AND '||

1837: ' count(*) over (partition by pub_item, access_id, dml_type)'||
1838: ' as total_rows, '||
1839: ' min(qid) over (partition by pub_item, access_id, dml_type)'||
1840: ' as min_qid '||
1841: ' from asg_system_dirty_queue where client_id = :1 AND '||
1842: ' TRANSACTION_ID IS NULL and download_flag is null '||
1843: ' ) '||
1844: ' where qid <> min_qid and total_rows >1 )';
1845: EXECUTE IMMEDIATE l_dml USING l_user_name;

Line 1851: -- delete from asg_system_dirty_queue

1847: /* log_concprogram('Removed '||l_count||' duplicate rows from Delete queue '||
1848: ' for user : '||l_user_name,
1849: 'asg_download',
1850: FND_LOG.LEVEL_STATEMENT);*/
1851: -- delete from asg_system_dirty_queue
1852: l_dml := 'Delete /*+ INDEX(asg_system_dirty_queue ASG_SYSTEM_DIRTY_QUEUE_U1) */ from asg_system_dirty_queue where qid in '||
1853: ' ( Select qid from '||
1854: ' ( select qid, pub_item, access_id, dml_type, '||
1855: ' count(*) over (partition by pub_item, access_id, dml_type)'||

Line 1852: l_dml := 'Delete /+ INDEX(asg_system_dirty_queue ASG_SYSTEM_DIRTY_QUEUE_U1) / from asg_system_dirty_queue where qid in '||

1848: ' for user : '||l_user_name,
1849: 'asg_download',
1850: FND_LOG.LEVEL_STATEMENT);*/
1851: -- delete from asg_system_dirty_queue
1852: l_dml := 'Delete /*+ INDEX(asg_system_dirty_queue ASG_SYSTEM_DIRTY_QUEUE_U1) */ from asg_system_dirty_queue where qid in '||
1853: ' ( Select qid from '||
1854: ' ( select qid, pub_item, access_id, dml_type, '||
1855: ' count(*) over (partition by pub_item, access_id, dml_type)'||
1856: ' as total_rows, '||

Line 1859: ' from asg_system_dirty_queue where client_id = :1 AND '||

1855: ' count(*) over (partition by pub_item, access_id, dml_type)'||
1856: ' as total_rows, '||
1857: ' min(qid) over (partition by pub_item, access_id, dml_type)'||
1858: ' as min_qid '||
1859: ' from asg_system_dirty_queue where client_id = :1 AND '||
1860: ' TRANSACTION_ID IS NULL and download_flag is null '||
1861: ' ) '||
1862: ' where qid <> min_qid and total_rows >1 )';
1863: EXECUTE IMMEDIATE l_dml USING l_user_name;

Line 1949: -- Deletes the rows in asg_system_dirty_queue for all the dormant users.

1945: END set_user_first_synch_pub;
1946:
1947:
1948: -- to be used by the JTM master concurrent program.
1949: -- Deletes the rows in asg_system_dirty_queue for all the dormant users.
1950: -- Forces these users to do a complete refresh.
1951: PROCEDURE delete_Sdq( P_status OUT NOCOPY VARCHAR2,
1952: P_message OUT NOCOPY VARCHAR2)
1953: IS

Line 2208: FROM asg_system_dirty_queue

2204: DELETE /*+ INDEX(asg_delete_queue ASG_DELETE_QUEUE_U1) */ FROM
2205: asg_delete_queue
2206: WHERE qid IN
2207: ( SELECT qid
2208: FROM asg_system_dirty_queue
2209: WHERE client_id = l_dormant_rec.user_name
2210: AND pub_item in
2211: (SELECT item_id FROM asg_pub_item
2212: WHERE pub_name=l_dormant_rec.pub_name));

Line 2219: DELETE FROM asg_system_dirty_queue

2215: SQL%ROWCOUNT||' rows',
2216: 'asg_download',FND_LOG.LEVEL_STATEMENT);
2217:
2218:
2219: DELETE FROM asg_system_dirty_queue
2220: WHERE client_id = l_dormant_rec.user_name
2221: AND pub_item in
2222: (SELECT item_id FROM asg_pub_item
2223: WHERE pub_name=l_dormant_rec.pub_name);

Line 2264: p_message := 'Purging asg_system_dirty_queue and asg_delete_queue completed successfully.';

2260:
2261: log_concprogram('End SDQ purge ','asg_download',
2262: FND_LOG.LEVEL_STATEMENT);
2263: p_status := 'Fine';
2264: p_message := 'Purging asg_system_dirty_queue and asg_delete_queue completed successfully.';
2265:
2266: EXCEPTION
2267: WHEN OTHERS THEN
2268: p_status := 'Error';

Line 2344: SELECT client_id FROM asg_system_dirty_queue

2340: IS
2341: CURSOR c_is_exists_in_sdq(p_clientid varchar2, p_pub_item varchar2,
2342: p_access_id number,p_dml_type varchar2)
2343: IS
2344: SELECT client_id FROM asg_system_dirty_queue
2345: WHERE client_id = p_clientid AND pub_item = p_pub_item
2346: AND access_id = p_access_id
2347: AND dml_type = DECODE(p_dml_type,'D',0,'I',1,'U',2)
2348: AND transaction_id IS NULL AND download_flag IS null;