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_APPLY dependencies on ASG_BASE

Line 189: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||

185: l_select_sync_sqlstring VARCHAR2(512);
186: BEGIN
187: l_select_sync_sqlstring :=
188: 'SELECT count(distinct clid$$cs) ' ||
189: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
190: 'WHERE clid$$cs = :1 AND ' ||
191: 'tranid$$ NOT IN ' ||
192: '(SELECT tranid ' ||
193: ' FROM asg_users_inqinfo ' ||

Line 214: l_cinq_table := asg_base.G_OLITE_SCHEMA || '.c$inq ';

210: ' Start TranID: ' || p_start_tranid ||
211: ' End TranID: ' || p_end_tranid ||
212: ' Compact TranID: ' || p_compact_tranid);
213:
214: l_cinq_table := asg_base.G_OLITE_SCHEMA || '.c$inq ';
215: l_sql := 'UPDATE ' || l_cinq_table ||
216: 'SET tranid$$ = :1 ' ||
217: 'WHERE clid$$cs = :2 AND ' ||
218: ' tranid$$ >= :3 AND ' ||

Line 284: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||

280: counter2:= 1;
281: curr_pubitem_length := 0;
282: l_select_store_sqlstring :=
283: 'SELECT store ' ||
284: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
285: 'WHERE clid$$cs = :1 AND ' ||
286: ' tranid$$ = :2 ' ||
287: ' ORDER BY store';
288:

Line 360: l_inq_table := asg_base.G_OLITE_SCHEMA || '.cfm$' || p_curr_pubitem || ' ' ;

356: l_sql VARCHAR2(1024);
357: l_pk_clause VARCHAR2(1024) := NULL;
358: l_sql_count NUMBER;
359: BEGIN
360: l_inq_table := asg_base.G_OLITE_SCHEMA || '.cfm$' || p_curr_pubitem || ' ' ;
361: log('compact_curr_inqtable: Compacting inq table for User: ' || p_user_name ||
362: ' Start TranID: ' || p_start_tranid ||
363: ' End TranID: ' || p_end_tranid ||
364: ' Compact TranID: ' || p_compact_tranid ||

Line 427: l_sql := 'DELETE FROM '||asg_base.G_OLITE_SCHEMA||'.c$inq a '||

423: log('compact_curr_inqtable: No of Records Deleted : ' || l_sql_count);
424:
425: IF (l_sql_count > 0) THEN
426: /*remove C_inq record if INQ table curr_tranid records are purged by above query */
427: l_sql := 'DELETE FROM '||asg_base.G_OLITE_SCHEMA||'.c$inq a '||
428: 'WHERE STORE= '||''''||p_curr_pubitem||''' '||
429: 'AND tranid$$=:1 '||
430: 'AND NOT EXISTS (SELECT 1 FROM '||l_inq_table||' b WHERE a.tranid$$=b.tranid$$)' ;
431:

Line 507: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||

503:
504: counter := 1;
505: l_select_store_sqlstring :=
506: 'SELECT store ' ||
507: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
508: 'WHERE clid$$cs = :1 AND ' ||
509: ' tranid$$ = :2 AND ' ||
510: ' store in (select item_id from asg_pub_item) ' ||
511: ' ORDER BY store';

Line 550: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||

546: l_tranid NUMBER;
547: BEGIN
548: l_select_tranid_sqlstring :=
549: 'SELECT distinct tranid$$ ' ||
550: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
551: 'WHERE clid$$cs = :user_name AND '||
552: 'tranid$$ NOT IN ' ||
553: '(SELECT tranid ' ||
554: ' FROM asg_users_inqinfo ' ||

Line 694: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||

690:
691: x_return_status := FND_API.G_RET_STS_SUCCESS;
692: l_select_store_sqlstring :=
693: 'SELECT store ' ||
694: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
695: 'WHERE clid$$cs = :1 AND ' ||
696: ' tranid$$ = :2 AND '||
697: ' store in (select item_id from asg_pub_item) ' ||
698: ' ORDER BY store';

Line 729: ' FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||

725: 'WHERE device_user_name = :user_name AND ' ||
726: ' deferred_tran_id = :tranid AND ' ||
727: ' object_name not in ' ||
728: ' (SELECT store ' ||
729: ' FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
730: ' WHERE clid$$cs = :user_name AND ' ||
731: ' tranid$$ = :tranid) ' ||
732: ' ORDER BY object_name';
733:

Line 780: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||

776:
777: x_return_status := FND_API.G_RET_STS_SUCCESS;
778: l_select_store_sqlstring :=
779: 'SELECT store ' ||
780: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
781: 'WHERE clid$$cs = :1 AND ' ||
782: ' tranid$$ = :2 AND ' ||
783: ' store in ' ||
784: ' (SELECT name ' ||

Line 823: ' FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||

819: ' FROM asg_pub_item ' ||
820: ' WHERE pub_name = :pubname) AND ' ||
821: ' object_name not in ' ||
822: ' (SELECT store ' ||
823: ' FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
824: ' WHERE clid$$cs = :user_name AND ' ||
825: ' tranid$$ = :tranid) ' ||
826: ' ORDER BY object_name';
827:

Line 873: 'FROM ' || asg_base.G_OLITE_SCHEMA ||'.c$inq '||

869:
870: x_return_status := FND_API.G_RET_STS_SUCCESS;
871: l_select_store_sqlstring :=
872: 'SELECT store ' ||
873: 'FROM ' || asg_base.G_OLITE_SCHEMA ||'.c$inq '||
874: 'WHERE clid$$cs = :1 AND ' ||
875: ' tranid$$ = :2 AND ' ||
876: ' store not in ' ||
877: ' (SELECT object_name ' ||

Line 1141: sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||

1137: sql_string := 'DELETE FROM '|| csm_util_pkg.get_inq_tab(p_pubitem) ||
1138: ' WHERE clid$$cs = :1 AND ' ||' tranid$$ = :2 AND ' ||' seqno$$ = :3';
1139: ELSE
1140: inq_tbl_name := 'CFM$' || p_pubitem;
1141: sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
1142: '.' || inq_tbl_name ||
1143: ' WHERE clid$$cs = :1 AND ' ||
1144: ' tranid$$ = :2 AND ' ||
1145: ' seqno$$ = :3';

Line 1206: sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||

1202: FOR curr_index in 1..num_pubitems LOOP
1203: curr_pubitem := p_pubitems_tbl(curr_index);
1204: inq_tbl_name := 'CFM$' || curr_pubitem;
1205: -- Should change this statement to use bind-variables
1206: sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
1207: '.' || inq_tbl_name ||
1208: ' WHERE clid$$cs = :1 AND ' ||
1209: ' tranid$$ = :2 AND ' ||
1210: ' seqno$$ NOT IN ' ||

Line 1220: sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||

1216: EXECUTE IMMEDIATE sql_string
1217: USING p_user_name, p_tranid,
1218: p_user_name, curr_pubitem;
1219:
1220: sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
1221: '.' || 'c$inq ' ||
1222: 'WHERE clid$$cs = :1 AND ' ||
1223: ' tranid$$ = :2 AND ' ||
1224: ' store = :3';

Line 1308: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.' || inq_tbl_name ||

1304: 'SYSDATE,' ||
1305: '1,' ||
1306: 'SYSDATE,'||
1307: '1 ' ||
1308: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.' || inq_tbl_name ||
1309: ' b WHERE b.clid$$cs = :5 AND ' ||
1310: ' tranid$$ = :6 AND ' ||
1311: ' b.seqno$$ not in (SELECT sequence ' ||
1312: 'FROM asg_deferred_traninfo ' ||

Line 1333: sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||

1329: log('purge_pubitems: Exception executing the SQL Command ' ||
1330: SQLERRM,g_err_level);
1331: END;
1332:
1333: sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
1334: '.' || 'c$inq ' ||
1335: 'WHERE clid$$cs = :1 AND ' ||
1336: ' tranid$$ = :2 AND ' ||
1337: ' store = :3';

Line 1377: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||

1373:
1374: x_return_status := FND_API.G_RET_STS_SUCCESS;
1375: l_select_store_sqlstring :=
1376: 'SELECT store ' ||
1377: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
1378: 'WHERE clid$$cs = :1 AND ' ||
1379: ' tranid$$ = :2 AND ' ||
1380: ' store in (select item_id from asg_pub_item) ' ||
1381: ' ORDER BY store';

Line 1557: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||

1553:
1554: -- Get the list of users with dirty data
1555: l_select_users_sqlstring :=
1556: 'SELECT distinct clid$$cs clientid ' ||
1557: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
1558: ' WHERE store in (select item_id from asg_pub_item) ';
1559:
1560: l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1561: DBMS_SQL.PARSE (l_cursor_id, l_select_users_sqlstring, DBMS_SQL.v7);

Line 1632: l_sql := 'SELECT 1 FROM '|| asg_base.G_OLITE_SCHEMA

1628: END IF;
1629:
1630: x_return_status := FND_API.G_RET_STS_SUCCESS;
1631:
1632: l_sql := 'SELECT 1 FROM '|| asg_base.G_OLITE_SCHEMA
1633: ||'.C$INQ c_inq WHERE CLID$$CS=:1 AND TRANID$$=:2 AND STORE = :3'
1634: ||' AND exists (select 1 from '||asg_base.G_OLITE_SCHEMA||'.cfm$'||l_pi_name||' inq, CSM_QUERY_B b '
1635: ||' WHERE tranid$$ = c_inq.tranid$$ '
1636: ||' AND clid$$cs = c_inq.clid$$cs '

Line 1634: ||' AND exists (select 1 from '||asg_base.G_OLITE_SCHEMA||'.cfm$'||l_pi_name||' inq, CSM_QUERY_B b '

1630: x_return_status := FND_API.G_RET_STS_SUCCESS;
1631:
1632: l_sql := 'SELECT 1 FROM '|| asg_base.G_OLITE_SCHEMA
1633: ||'.C$INQ c_inq WHERE CLID$$CS=:1 AND TRANID$$=:2 AND STORE = :3'
1634: ||' AND exists (select 1 from '||asg_base.G_OLITE_SCHEMA||'.cfm$'||l_pi_name||' inq, CSM_QUERY_B b '
1635: ||' WHERE tranid$$ = c_inq.tranid$$ '
1636: ||' AND clid$$cs = c_inq.clid$$cs '
1637: ||' AND inq.QUERY_ID=b.QUERY_ID '
1638: ||' AND b.EXECUTION_MODE=''SYNCHRONOUS'')';

Line 1652: l_sql := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA

1648: log('process_mobile_queries: status returned by CSM api - '||x_return_status);
1649:
1650: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1651:
1652: l_sql := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA
1653: ||'.c$inq c_inq WHERE CLID$$CS = :1 AND TRANID$$ = :2 AND STORE = :3'
1654: ||' AND not exists (select 1 from '||asg_base.G_OLITE_SCHEMA||'.cfm$'||l_pi_name||' inq '
1655: ||' WHERE tranid$$ = c_inq.tranid$$ '
1656: ||' AND clid$$cs = c_inq.clid$$cs )';

Line 1654: ||' AND not exists (select 1 from '||asg_base.G_OLITE_SCHEMA||'.cfm$'||l_pi_name||' inq '

1650: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1651:
1652: l_sql := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA
1653: ||'.c$inq c_inq WHERE CLID$$CS = :1 AND TRANID$$ = :2 AND STORE = :3'
1654: ||' AND not exists (select 1 from '||asg_base.G_OLITE_SCHEMA||'.cfm$'||l_pi_name||' inq '
1655: ||' WHERE tranid$$ = c_inq.tranid$$ '
1656: ||' AND clid$$cs = c_inq.clid$$cs )';
1657:
1658: EXECUTE IMMEDIATE l_sql USING p_user_name, p_tranid, l_pi_name;

Line 1660: l_sql := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA

1656: ||' AND clid$$cs = c_inq.clid$$cs )';
1657:
1658: EXECUTE IMMEDIATE l_sql USING p_user_name, p_tranid, l_pi_name;
1659:
1660: l_sql := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA
1661: ||'.c$inq c_inq WHERE CLID$$CS = :1 AND TRANID$$ = :2 AND STORE = :3'
1662: ||' AND not exists (select 1 from '||asg_base.G_OLITE_SCHEMA||'.cfm$CSM_QUERY_VARIABLE_VALUES inq '
1663: ||' WHERE tranid$$ = c_inq.tranid$$ '
1664: ||' AND clid$$cs = c_inq.clid$$cs )';

Line 1662: ||' AND not exists (select 1 from '||asg_base.G_OLITE_SCHEMA||'.cfm$CSM_QUERY_VARIABLE_VALUES inq '

1658: EXECUTE IMMEDIATE l_sql USING p_user_name, p_tranid, l_pi_name;
1659:
1660: l_sql := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA
1661: ||'.c$inq c_inq WHERE CLID$$CS = :1 AND TRANID$$ = :2 AND STORE = :3'
1662: ||' AND not exists (select 1 from '||asg_base.G_OLITE_SCHEMA||'.cfm$CSM_QUERY_VARIABLE_VALUES inq '
1663: ||' WHERE tranid$$ = c_inq.tranid$$ '
1664: ||' AND clid$$cs = c_inq.clid$$cs )';
1665:
1666: EXECUTE IMMEDIATE l_sql USING p_user_name, p_tranid, 'CSM_QUERY_VARIABLE_VALUES';

Line 1705: 'FROM ' || asg_base.G_OLITE_SCHEMA ||

1701: WHERE user_name = p_user_name;
1702:
1703: l_select_seq_sqlstring :=
1704: 'SELECT a.name name, a.curr_val curr_val '||
1705: 'FROM ' || asg_base.G_OLITE_SCHEMA ||
1706: '.' || 'cfm$c$all_sequence_partitions a, ' ||
1707: asg_base.G_OLITE_SCHEMA || '.' || 'c$inq b ' ||
1708: 'WHERE b.clid$$cs = :user_name AND ' ||
1709: 'b.tranid$$ = :tranid AND ' ||

Line 1707: asg_base.G_OLITE_SCHEMA || '.' || 'c$inq b ' ||

1703: l_select_seq_sqlstring :=
1704: 'SELECT a.name name, a.curr_val curr_val '||
1705: 'FROM ' || asg_base.G_OLITE_SCHEMA ||
1706: '.' || 'cfm$c$all_sequence_partitions a, ' ||
1707: asg_base.G_OLITE_SCHEMA || '.' || 'c$inq b ' ||
1708: 'WHERE b.clid$$cs = :user_name AND ' ||
1709: 'b.tranid$$ = :tranid AND ' ||
1710: 'b.store = :seq_name AND ' ||
1711: 'a.clid$$cs = b.clid$$cs AND ' ||

Line 1756: l_delete_sqlstring := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||

1752: END IF;
1753:
1754: IF counter >1 THEN
1755: -- Updated sequences
1756: l_delete_sqlstring := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
1757: '.' || 'c$inq ' ||
1758: 'WHERE CLID$$CS = :1 AND ' ||
1759: 'TRANID$$ = :2 AND ' ||
1760: 'STORE = :3';

Line 1765: asg_base.G_OLITE_SCHEMA ||

1761: EXECUTE IMMEDIATE l_delete_sqlstring
1762: USING p_user_name, p_tranid, l_sequence;
1763:
1764: l_delete_sqlstring := 'DELETE FROM ' ||
1765: asg_base.G_OLITE_SCHEMA ||
1766: '.' ||'cfm$c$all_sequence_partitions ' ||
1767: 'WHERE CLID$$CS = :1 AND ' ||
1768: ' TRANID$$ = :2';
1769: EXECUTE IMMEDIATE l_delete_sqlstring

Line 1812: 'FROM ' || asg_base.G_OLITE_SCHEMA ||

1808: WHERE user_name = p_user_name;
1809:
1810: l_select_seq_sqlstring :=
1811: 'SELECT a.name name, a.curr_val curr_val '||
1812: 'FROM ' || asg_base.G_OLITE_SCHEMA ||
1813: '.' || 'cfm$CSM_SEQUENCES a, ' ||
1814: asg_base.G_OLITE_SCHEMA || '.' || 'c$inq b ' ||
1815: 'WHERE b.clid$$cs = :user_name AND ' ||
1816: 'b.tranid$$ = :tranid AND ' ||

Line 1814: asg_base.G_OLITE_SCHEMA || '.' || 'c$inq b ' ||

1810: l_select_seq_sqlstring :=
1811: 'SELECT a.name name, a.curr_val curr_val '||
1812: 'FROM ' || asg_base.G_OLITE_SCHEMA ||
1813: '.' || 'cfm$CSM_SEQUENCES a, ' ||
1814: asg_base.G_OLITE_SCHEMA || '.' || 'c$inq b ' ||
1815: 'WHERE b.clid$$cs = :user_name AND ' ||
1816: 'b.tranid$$ = :tranid AND ' ||
1817: 'b.store = :seq_name AND ' ||
1818: 'a.clid$$cs = b.clid$$cs AND ' ||

Line 1863: l_delete_sqlstring := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||

1859: END IF;
1860:
1861: IF counter >1 THEN
1862: -- Updated sequences
1863: l_delete_sqlstring := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
1864: '.' || 'c$inq ' ||
1865: 'WHERE CLID$$CS = :1 AND ' ||
1866: 'TRANID$$ = :2 AND ' ||
1867: 'STORE = :3';

Line 1872: asg_base.G_OLITE_SCHEMA ||

1868: EXECUTE IMMEDIATE l_delete_sqlstring
1869: USING p_user_name, p_tranid, l_sequence;
1870:
1871: l_delete_sqlstring := 'DELETE FROM ' ||
1872: asg_base.G_OLITE_SCHEMA ||
1873: '.' ||'cfm$CSM_SEQUENCES ' ||
1874: 'WHERE CLID$$CS = :1 AND ' ||
1875: ' TRANID$$ = :2';
1876: EXECUTE IMMEDIATE l_delete_sqlstring

Line 1916: 'DELETE from ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||

1912:
1913: -- Remove any custom pub items from c$inq
1914: -- Custom pub items are processed directly by custom implementor.
1915: l_delete_inq_sqlstring :=
1916: 'DELETE from ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
1917: 'WHERE store in ' ||
1918: ' (SELECT api.name ' ||
1919: ' FROM asg_pub ap, asg_pub_item api ' ||
1920: ' WHERE ap.custom = ''Y'' AND ' ||

Line 1926: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci ' ||

1922: EXECUTE IMMEDIATE l_delete_inq_sqlstring;
1923:
1924: l_select_inqcnt_sqlstring :=
1925: 'SELECT count(*) ' ||
1926: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci ' ||
1927: 'WHERE clid$$cs = :1 AND ' ||
1928: ' tranid$$ = :2 AND ' ||
1929: ' store in (select item_id from asg_pub_item) ' ||
1930: 'AND NOT EXISTS (SELECT 1 FROM asg_users_inqinfo ' ||

Line 1980: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci '||

1976: counter2:= 1;
1977: curr_pubitem_length := 0;
1978: l_select_store_sqlstring :=
1979: 'SELECT store ' ||
1980: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci '||
1981: 'WHERE clid$$cs = :1 AND ' ||
1982: ' tranid$$ = :2 AND ' ||
1983: ' store in (select item_id from asg_pub_item) '||
1984: ' AND NOT EXISTS (SELECT 1 FROM asg_users_inqarchive ' ||

Line 2102: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||

2098: -- will mark this transaction as not to be processed further
2099: -- Users can later rectify/purge through the def txn UI.
2100: l_select_inqcnt_sqlstring :=
2101: 'SELECT count(*) ' ||
2102: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
2103: 'WHERE clid$$cs = :1 AND ' ||
2104: ' tranid$$ = :2';
2105: EXECUTE IMMEDIATE l_select_inqcnt_sqlstring
2106: INTO l_inq_count

Line 2123: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$all_subscriptions a, ' ||

2119: END IF;
2120:
2121: l_select_pub_sqlstring :=
2122: 'SELECT template, wrapper_name ' ||
2123: 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$all_subscriptions a, ' ||
2124: ' asg_pub b ' ||
2125: 'WHERE a.clientid = :user_name AND ' ||
2126: ' a.template = b.name';
2127: