[Home] [Help]
240: PROCEDURE REMOVE_DIRTY_CACHE
241: IS
242: PRAGMA AUTONOMOUS_TRANSACTION;
243: BEGIN
244: DELETE FROM CSM_HTML5_CACHE B WHERE EXISTS(SELECT 1 FROM ASG_SYSTEM_DIRTY_QUEUE
245: WHERE CLIENT_ID=B.USER_NAME
246: AND PUB_ITEM=B.PI_NAME);
247: COMMIT;
248: END REMOVE_DIRTY_CACHE;
1205: CSM_UTIL_PKG.LOG('Processing PI :'||rec.item_id, 'CSM_HTML5_PKG.query_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1206:
1207: l_final:=l_final ||'appsdb.delete_record("DELETE FROM '||rec.item_id||'");'||g_nl_chr;
1208:
1209: DELETE FROM ASG_SYSTEM_DIRTY_QUEUE WHERE CLIENT_ID=p_user_name AND PUB_ITEM=rec.item_id; --clear SDQ records here, so that new records inserted into sdq during this process
1210: --won't be missed. Confirmed that cursor resultSet is the one picked at the time of OPEN
1211: l_cached:=GET_CACHED (p_user_name,rec.item_id,l_stmt,l_rec_count);
1212:
1213: IF NOT l_cached THEN
1395: and name=p_pi)
1396: LOOP
1397: l_final:=l_final || create_table_js(p_pi);
1398:
1399: DELETE FROM ASG_SYSTEM_DIRTY_QUEUE WHERE CLIENT_ID=p_user_name AND PUB_ITEM=p_pi; --clear records here, so that new records inserted into sdq during this process
1400: --won't be missed. Confirmed that cursor resultSet is the one at the time of OPEN
1401:
1402: l_cached:=GET_CACHED(p_user_name,p_pi,l_stmt,l_rec_count);
1403:
1619: /*****ASG SDQ***********/
1620: --remove successfully deleted DMLs from SDQ
1621: DELETE FROM asg_delete_queue
1622: WHERE qid IN (SELECT qid
1623: FROM asg_system_dirty_queue
1624: WHERE client_id = p_user_name AND
1625: (transaction_id < p_tranid OR pub_item in (SELECT NAME FROM ASG_PUB_ITEM WHERE nvl(html5_offline,'N')='N' AND PUB_NAME='SERVICEP')));
1626:
1627: --remove successful DMLs from SDQ and unwanted PI-s
1624: WHERE client_id = p_user_name AND
1625: (transaction_id < p_tranid OR pub_item in (SELECT NAME FROM ASG_PUB_ITEM WHERE nvl(html5_offline,'N')='N' AND PUB_NAME='SERVICEP')));
1626:
1627: --remove successful DMLs from SDQ and unwanted PI-s
1628: DELETE FROM asg_system_dirty_queue
1629: WHERE client_id = p_user_name AND
1630: (transaction_id < p_tranid OR pub_item in (SELECT NAME FROM ASG_PUB_ITEM WHERE nvl(html5_offline,'N')='N' AND PUB_NAME='SERVICEP'));
1631:
1632: --restore unsuccessful ones for current sync
1629: WHERE client_id = p_user_name AND
1630: (transaction_id < p_tranid OR pub_item in (SELECT NAME FROM ASG_PUB_ITEM WHERE nvl(html5_offline,'N')='N' AND PUB_NAME='SERVICEP'));
1631:
1632: --restore unsuccessful ones for current sync
1633: UPDATE asg_system_dirty_queue SET transaction_id=p_tranid
1634: WHERE client_id = p_user_name and rownum<=2000; --inc sync processes only max 2000 records(exclusive of comp ref) per sync
1635:
1636: IF SQL%ROWCOUNT=2000 THEN
1637: l_more_avl := true;
1686: /*****ASG SDQ PROCESSING***********/
1687: for rec in (select b.pub_item,b.access_id,b.dml_type,base_object_name,a.primary_key_column
1688: from asg_pub_item a,
1689: (select pub_item,access_id,client_id,min(dml_type) as dml_type
1690: from asG_system_dirty_queue
1691: where client_id=p_useR_name
1692: and transaction_id = p_tranid
1693: and nvl(download_flag,'Y')='Y' --Bug 16456574: process conflicts
1694: and pub_item in (SELECT NAME FROM ASG_PUB_ITEM WHERE nvl(html5_offline,'N')='Y')
2121: last_update_date,
2122: last_updated_by)
2123: SELECT p_user_name, p_pubitem, p_upload_tranid, access_id,b.sequence, l_client_wins,
2124: sysdate, 1, sysdate, 1
2125: FROM asg_system_dirty_queue a, CSM_HTML5_PARSED_DATA b
2126: WHERE client_id = p_user_name AND pub_item = p_pubitem AND
2127: transaction_id is NULL AND a.dml_type = 2
2128: AND b.session_id=p_upload_tranid AND b.piv_name=a.pub_item AND a.access_id=b.pk_value AND b.dml_type='U'
2129: AND NOT EXISTS(SELECT 1 FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME=p_user_name AND PRF_NAME='COMPREF' AND PRF_VALUE=p_pubitem);
2167: END IF;
2168:
2169: -- Ok, all the conflict rows are processed. We need to
2170: -- reset download flag for those rows where client wins applies
2171: UPDATE asg_system_dirty_queue
2172: SET download_flag = 'N'
2173: WHERE client_id = p_user_name AND
2174: pub_item = p_pubitem AND
2175: transaction_id is NULL AND
2531: RETURN '-1';
2532: END IF ;
2533:
2534: BEGIN
2535: SELECT '1' INTO x FROM ASG_SYSTEM_DIRTY_QUEUE sdq WHERE CLIENT_ID=p_USER_NAME AND ROWNUM<2
2536: AND EXISTS(SELECT 1 FROM ASG_PUB_ITEM pi WHERE pi.item_id=sdq.pub_item and nvl(html5_offline,'N')='Y');
2537:
2538: DELETE FROM csm_html5_clients_info WHERE USER_NAME=p_USER_NAME AND PRF_NAME like 'REUSABLE%';
2539: RETURN '-1';