[Home] [Help]
26:
27: g_nl_chr VARCHAR2(10) :=fnd_global.local_chr(10);
28:
29: CURSOR c_get_inq_Table(c_piv_name VARCHAR2)
30: IS SELECT INQ_NAME FROM ASG_PUB_ITEM WHERE ITEM_ID =C_PIV_NAME;
31:
32: procedure set_context(context_name in varchar2, attr_name in varchar2,attr_value in varchar2)
33: is begin
34: dbms_session.set_context(context_name, attr_name, attr_value);
605: LOOP
606: INSERT INTO CSM_HTML5_CLIENTS_INFO(USER_NAME,PRF_NAME,PRF_VALUE) VALUES(rec.USER_NAME,'INDXREF',p_idx);
607: END LOOP;
608:
609: DELETE FROM CSM_HTML5_CACHE WHERE USER_NAME='APPLICATION' AND PI_NAME=(SELECT ITEM_ID FROM ASG_PUB_ITEM_INDEX WHERE INDEX_ID=p_idx) ;
610: END set_indx_ref;
611:
612: PROCEDURE set_indx_ref(p_user_name IN VARCHAR2,p_idx IN VARCHAR2)
613: IS
620: LOOP
621: INSERT INTO CSM_HTML5_CLIENTS_INFO(USER_NAME,PRF_NAME,PRF_VALUE) VALUES(rec.USER_NAME,'INDXREF',p_idx);
622: END LOOP;
623:
624: DELETE FROM CSM_HTML5_CACHE WHERE USER_NAME='APPLICATION' AND PI_NAME=(SELECT ITEM_ID FROM ASG_PUB_ITEM_INDEX WHERE INDEX_ID=p_idx) ;
625: END set_indx_ref;
626:
627: PROCEDURE remove_indx_ref(p_USER_NAME IN VARCHAR2)
628: IS
633: PROCEDURE remove_indx_ref(p_user_name IN VARCHAR2,p_pi IN VARCHAR2)
634: IS
635: BEGIN
636: IF p_user_name is NULL THEN
637: DELETE FROM CSM_HTML5_CLIENTS_INFO WHERE PRF_NAME='INDXREF' AND PRF_VALUE IN (SELECT INDEX_ID FROM ASG_PUB_ITEM_INDEX WHERE ITEM_ID=p_pi);
638: ELSE
639: DELETE FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME=p_USER_NAME
640: AND PRF_NAME='INDXREF' AND PRF_VALUE IN (SELECT INDEX_ID FROM ASG_PUB_ITEM_INDEX WHERE ITEM_ID=p_pi);
641: END IF;
636: IF p_user_name is NULL THEN
637: DELETE FROM CSM_HTML5_CLIENTS_INFO WHERE PRF_NAME='INDXREF' AND PRF_VALUE IN (SELECT INDEX_ID FROM ASG_PUB_ITEM_INDEX WHERE ITEM_ID=p_pi);
638: ELSE
639: DELETE FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME=p_USER_NAME
640: AND PRF_NAME='INDXREF' AND PRF_VALUE IN (SELECT INDEX_ID FROM ASG_PUB_ITEM_INDEX WHERE ITEM_ID=p_pi);
641: END IF;
642: END remove_indx_ref;
643:
644: PROCEDURE mark_pi_altered(p_pi IN VARCHAR2)
738: IS
739: l_stmt CLOB:='';
740: BEGIN
741: FOR rec IN (SELECT item_id ,index_name,index_columns,enabled
742: FROM CSM_HTML5_CLIENTS_INFO a, ASG_PUB_ITEM_INDEX b
743: WHERE a.USER_NAME=p_user_name AND a.PRF_NAME='INDXREF'
744: AND a.PRF_VALUE=b.index_id)
745: LOOP
746: l_stmt:=l_stmt||'appsdb.doDDL("DROP INDEX '||rec.index_name||'",[],";",";");'||g_nl_chr;
758: FUNCTION create_index_js( p_pi IN VARCHAR2) RETURN CLOB
759: IS
760: l_stmt CLOB:='';
761: BEGIN
762: FOR rec IN (select index_name,index_columns,enabled from ASG_PUB_ITEM_INDEX where item_id =p_pi)
763: LOOP
764: l_stmt:=l_stmt||'appsdb.doDDL("DROP INDEX '||rec.index_name||'",[],";",";");'||g_nl_chr;
765: IF rec.ENABLED='Y' THEN
766: IF(SUBSTR(rec.index_name,-2,1)='U') THEN --assumes only 9 indexes per table
789: l_cached:=GET_CACHED('CSM_CACHE_PVT',p_pi,l_stmt,l_temp);
790:
791: IF NOT l_cached THEN
792: l_stmt:=g_nl_chr;
793: for rec in (select item_id,upper(base_object_name) piv,primary_key_column from asg_pub_item where item_id=p_pi and rownum=1) --only one record selected any way
794: loop
795: l_name_tab.DELETE;
796: l_dtype_tab.DELETE;
797: l_pk_cols:=rec.primary_key_column;
846: x_error_message:='Success';
847:
848: l_stmt := l_stmt ||'function create_mfs_schema(){ '||g_nl_chr;
849:
850: for rec in (select item_id,query1 from asg_pub_item where pub_name='SERVICEP' and enabled='Y'
851: and nvl(html5_offline,'N')='Y')
852: loop
853: CSM_UTIL_PKG.LOG('Processing schema of '||rec.item_id,'CSM_HTML5_PKG.query_get_schema_js',FND_LOG.LEVEL_ERROR);
854: l_stmt := l_stmt||create_table_js(rec.item_id);
1197: into l_uname,l_lang,lrid,l_uid,l_respid,l_app_id,l_synch_date from asg_user where user_name=upper(p_user_name);
1198: asg_base.init_debug(l_uname,l_lang,lrid,l_uid,l_respid,l_app_id,l_synch_date);
1199:
1200:
1201: for rec in (select item_id,base_objecT_name from asg_pub_item where pub_name='SERVICEP' and enabled='Y'
1202: and nvl(html5_offline,'N')='Y' order by item_id)
1203: LOOP
1204:
1205: CSM_UTIL_PKG.LOG('Processing PI :'||rec.item_id, 'CSM_HTML5_PKG.query_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1390: CSM_UTIL_PKG.LOG('Processing Complete refresh of PI :'||p_pi, 'CSM_HTML5_PKG.complete_refresh', FND_LOG.LEVEL_PROCEDURE);
1391:
1392:
1393: --This api processes only one pi at a time though in loop: name=p_pi picks up one record
1394: for rec in (select item_id,base_objecT_name from asg_pub_item where pub_name='SERVICEP' and enabled='Y'
1395: and name=p_pi)
1396: LOOP
1397: l_final:=l_final || create_table_js(p_pi);
1398:
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
1628: DELETE FROM asg_system_dirty_queue
1629: WHERE client_id = p_user_name AND
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
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
1643: REMOVE_DIRTY_CACHE; --here bcoz it shd be after old SDQ records are cleared
1644:
1645: IF l_refresh_all THEN
1646: CSM_UTIL_PKG.LOG('Refresh All is set', 'CSM_HTML5_PKG.q_inc_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1647: for pi in (SELECT NAME FROM ASG_PUB_ITEM WHERE nvl(html5_offline,'N')='Y')
1648: loop
1649: set_comp_ref(p_user_name,pi.name);
1650: end loop;
1651: END IF;
1657: asg_base.init_debug(l_uname,l_lang,lrid,l_uid,l_respid,l_app_id,l_synch_date);
1658:
1659:
1660: /*****COMPLETE REFRESH***********/
1661: for pi in (SELECT a.NAME FROM ASG_PUB_ITEM a,CSM_HTML5_CLIENTS_INFO b
1662: WHERE nvl(html5_offline,'N')='Y'
1663: AND PRF_NAME='COMPREF'
1664: AND PRF_VALUE=a.NAME
1665: AND b.USER_NAME=p_user_name ORDER BY a.NAME)
1684:
1685:
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
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')
1695: group by pub_item,access_id,client_id) b
1696: where b.pub_item=a.item_id
1697: order by b.pub_item)
1698: LOOP
1929:
1930: PROCEDURE enable_pi(p_pi IN VARCHAR2)
1931: IS
1932: BEGIN
1933: UPDATE ASG_PUB_ITEM SET HTML5_OFFLINE='Y' WHERE ITEM_ID=p_pi;
1934: set_comp_ref(p_pi);
1935: DELETE FROM csm_html5_clients_info WHERE PRF_NAME like 'REUSABLE%' OR PRF_NAME like 'SCHEMA%REUSE%';
1936: END enable_pi;
1937:
1937:
1938: PROCEDURE disable_pi(p_pi IN VARCHAR2)
1939: IS
1940: BEGIN
1941: UPDATE ASG_PUB_ITEM SET HTML5_OFFLINE='N' WHERE ITEM_ID=p_pi;
1942: DELETE FROM csm_html5_clients_info WHERE PRF_NAME like 'REUSABLE%';
1943: remove_comp_ref(p_pi);
1944: END disable_pi;
1945:
2103: BEGIN
2104:
2105:
2106: SELECT conflict_callout INTO l_conflict_callout
2107: FROM asg_pub_item WHERE name = p_pubitem;
2108:
2109: l_client_wins := asg_base.G_CLIENT_WINS;
2110: l_server_wins := asg_base.G_SERVER_WINS;
2111:
2189:
2190: CSM_UTIL_PKG.LOG('Entered Process Conflicts for tranid: '||p_upload_tran_id, 'CSM_HTML5_PKG.process_conflicts', FND_LOG.LEVEL_PROCEDURE);
2191:
2192: FOR rec IN (SELECT api.item_id
2193: FROM csm_html5_inq ci, asg_pub_item api
2194: WHERE ci.user_name = p_user_name
2195: AND ci.sync_id = p_upload_tran_id
2196: AND ci.pi_name = api.name
2197: AND api.detect_conflict = 'Y')
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';
2540: EXCEPTION
2910: l_select_inqcnt_sqlstring :=
2911: 'SELECT count(*) FROM CSM_HTML5_INQ ci ' ||
2912: 'WHERE user_name = :1 AND ' ||
2913: ' sync_id = :2 AND ' ||
2914: ' pi_name in (select item_id from asg_pub_item) ' ||
2915: 'AND NOT EXISTS (SELECT 1 FROM asg_users_inqinfo ' ||
2916: ' WHERE device_user_name = ci.user_name AND TRANID =ci.sync_id) ' ;
2917:
2918: EXECUTE IMMEDIATE l_select_inqcnt_sqlstring INTO l_inq_count USING p_user_name, p_tranid;
2961: l_select_store_sqlstring :=
2962: 'SELECT pi_name FROM CSM_HTML5_INQ ci '||
2963: 'WHERE user_name = :1 AND ' ||
2964: ' sync_id = :2 AND ' ||
2965: ' pi_name in (select item_id from asg_pub_item) '||
2966: ' AND NOT EXISTS (SELECT 1 FROM asg_users_inqarchive ' ||
2967: ' WHERE device_user_name = ci.user_name AND TRANID =ci.sync_id) '||
2968: ' ORDER BY pi_name';
2969:
3065: WHERE user_name = p_user_name;
3066:
3067: CURSOR c_pk(b_pi VARCHAR2)
3068: IS --currently only one pk col supported
3069: select primary_key_column from asg_pub_item where item_id=b_pi and instr(primary_key_column,',')=0;
3070:
3071: BEGIN
3072: IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
3073: ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) OR
3453: IS
3454: counter PLS_INTEGER;
3455: CURSOR l_cursor1 IS
3456: SELECT PI_NAME
3457: FROM CSM_HTML5_INQ a,ASG_PUB_ITEM b
3458: WHERE a.PI_NAME=b.ITEM_ID
3459: AND a.user_name=p_user_name
3460: AND a.SYNC_ID=p_tranid
3461: ORDER BY nvl(b.table_weight,0);
3473: l_n:=x_pubitems_tbl.count;
3474: IF l_n >0 THEN l_dummy:=true; END IF;
3475:
3476: FOR REC IN (SELECT object_name
3477: FROM asg_deferred_traninfo a,asg_pub_item b
3478: WHERE device_user_name = p_user_name
3479: AND deferred_tran_id = p_tranid
3480: AND b.item_id=a.object_name
3481: AND NOT EXISTS(SELECT 1 FROM CSM_HTML5_INQ