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.AS_GAR_LEADS_PUB dependencies on AS_ACCESSES_ALL

Line 15: -- 2: Merge and insert records from winners into AS_ACCESSES_ALL_ALL

11: -- This package contains procedures to accomplish each of the following
12: -- tasks:
13: -- 1: Call the JTY API to process data from JTY trans tables and
14: -- populate JTY winners.
15: -- 2: Merge and insert records from winners into AS_ACCESSES_ALL_ALL
16: -- 3: Soft Delete unwanted records from AS_ACCESSES_ALL_ALL
17: -- 4: Lead Owner Assignment
18: --
19: ---------------------------------------------------------------------------

Line 16: -- 3: Soft Delete unwanted records from AS_ACCESSES_ALL_ALL

12: -- tasks:
13: -- 1: Call the JTY API to process data from JTY trans tables and
14: -- populate JTY winners.
15: -- 2: Merge and insert records from winners into AS_ACCESSES_ALL_ALL
16: -- 3: Soft Delete unwanted records from AS_ACCESSES_ALL_ALL
17: -- 4: Lead Owner Assignment
18: --
19: ---------------------------------------------------------------------------
20: /*-------------------------------------------------------------------------+

Line 778: FROM AS_ACCESSES_ALL_ALL A,

774: SELECT /*+ LEADING(WIN) USE_NL(A WIN) INDEX(A) */ A.sales_lead_id,
775: A.salesforce_id,
776: A.sales_group_id,
777: NVL(WIN.full_access_flag,'N')
778: FROM AS_ACCESSES_ALL_ALL A,
779: JTF_TAE_1001_LEAD_WINNERS WIN
780: WHERE A.lead_id is NULL
781: AND A.sales_lead_id is NOT NULL
782: AND A.delete_flag is NULL

Line 841: UPDATE AS_ACCESSES_ALL_ALL ACC

837: END IF;
838: WHILE l_attempts < 3 LOOP
839: BEGIN
840: FORALL i IN l_first .. l_last
841: UPDATE AS_ACCESSES_ALL_ALL ACC
842: SET object_version_number = NVL(object_version_number,0) + 1,
843: ACC.last_update_date = SYSDATE,
844: ACC.last_updated_by = p_terr_globals.user_id,
845: ACC.last_update_login = p_terr_globals.last_update_login,

Line 869: UPDATE AS_ACCESSES_ALL_ALL ACC

865: IF l_attempts = 3 THEN
866: FOR i IN l_first .. l_last
867: LOOP
868: BEGIN
869: UPDATE AS_ACCESSES_ALL_ALL ACC
870: SET object_version_number = NVL(object_version_number,0) + 1,
871: ACC.last_update_date = SYSDATE,
872: ACC.last_updated_by = p_terr_globals.user_id,
873: ACC.last_update_login = p_terr_globals.last_update_login,

Line 1029: INSERT INTO AS_ACCESSES_ALL_ALL

1025: END IF;
1026: BEGIN
1027: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' to '||l_last);
1028: FORALL i IN l_first .. l_last
1029: INSERT INTO AS_ACCESSES_ALL_ALL
1030: ( access_id
1031: ,access_type
1032: ,salesforce_id
1033: ,sales_group_id

Line 1088: FROM AS_ACCESSES_ALL_ALL AA

1084: FROM DUAL, AS_SALES_LEADS L, AS_STATUSES_B STS
1085: WHERE L.sales_lead_id = l_sales_lead_id(i)
1086: AND L.status_code = STS.status_code
1087: AND NOT EXISTS ( SELECT 'X'
1088: FROM AS_ACCESSES_ALL_ALL AA
1089: WHERE AA.sales_lead_id IS NOT NULL
1090: AND AA.lead_id IS NULL
1091: AND AA.delete_flag IS NULL
1092: AND AA.sales_lead_id = l_sales_lead_id(i)

Line 1104: INSERT INTO AS_ACCESSES_ALL_ALL

1100: WHEN DUP_VAL_ON_INDEX THEN
1101: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1102: FOR i IN l_first .. l_last LOOP
1103: BEGIN
1104: INSERT INTO AS_ACCESSES_ALL_ALL
1105: ( access_id
1106: ,access_type
1107: ,salesforce_id
1108: ,sales_group_id

Line 1163: FROM AS_ACCESSES_ALL_ALL AA

1159: FROM DUAL,AS_SALES_LEADS L, AS_STATUSES_B STS
1160: WHERE L.sales_lead_id = l_sales_lead_id(i)
1161: AND L.status_code = STS.status_code
1162: AND NOT EXISTS ( SELECT 'X'
1163: FROM AS_ACCESSES_ALL_ALL AA
1164: WHERE AA.sales_lead_id IS NOT NULL
1165: AND AA.lead_id IS NULL
1166: AND AA.delete_flag IS NULL
1167: AND AA.sales_lead_id = l_sales_lead_id(i)

Line 1328: FROM AS_ACCESSES_ALL_ALL A

1324: p_terr_globals.prog_id,
1325: SYSDATE
1326: FROM
1327: ( SELECT DISTINCT A.access_id acc_id
1328: FROM AS_ACCESSES_ALL_ALL A
1329: WHERE A.sales_lead_id=l_sales_lead_id(i)
1330: AND A.sales_group_id = l_sales_group_id(i)
1331: AND A.salesforce_id=l_salesforce_id(i)
1332: AND A.sales_lead_id is NOT NULL

Line 1379: FROM AS_ACCESSES_ALL_ALL A

1375: p_terr_globals.prog_id,
1376: SYSDATE
1377: FROM
1378: ( SELECT DISTINCT a.access_id acc_id
1379: FROM AS_ACCESSES_ALL_ALL A
1380: WHERE A.sales_lead_id=l_sales_lead_id(i)
1381: AND A.sales_group_id = l_sales_group_id(i)
1382: AND A.salesforce_id=l_salesforce_id(i)
1383: AND A.sales_lead_id is NOT NULL

Line 1543: UPDATE AS_ACCESSES_ALL_ALL ACC

1539: WHILE l_attempts < 3 LOOP --{L4
1540: BEGIN
1541: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_UPD || AS_GAR.G_START);
1542: FORALL i in l_first..l_last
1543: UPDATE AS_ACCESSES_ALL_ALL ACC
1544: SET object_version_number = NVL(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
1545: WHERE ACC.sales_lead_id=l_sales_lead_id(i)
1546: AND ACC.freeze_flag = 'N'
1547: AND ACC.lead_id IS NULL

Line 1565: DELETE FROM AS_ACCESSES_ALL_ALL ACC

1561: WHEN DUP_VAL_ON_INDEX THEN
1562: BEGIN
1563: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_DEL || AS_GAR.G_START);
1564: FORALL i in l_first..l_last
1565: DELETE FROM AS_ACCESSES_ALL_ALL ACC
1566: WHERE ACC.sales_lead_id=l_sales_lead_id(i)
1567: AND ACC.freeze_flag = 'N'
1568: AND ACC.lead_id IS NULL
1569: AND ACC.sales_lead_id IS NOT NULL

Line 1593: UPDATE AS_ACCESSES_ALL_ALL ACC

1589: IF l_attempts = 3 THEN
1590: FOR i IN l_first .. l_last LOOP --{L5
1591: BEGIN
1592: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_IND_UPD || AS_GAR.G_START);
1593: UPDATE AS_ACCESSES_ALL_ALL ACC
1594: SET object_version_number = NVL(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
1595: WHERE ACC.sales_lead_id = l_sales_lead_id(i)
1596: AND ACC.freeze_flag = 'N'
1597: AND ACC.lead_id IS NULL

Line 1611: DELETE FROM AS_ACCESSES_ALL_ALL ACC

1607: EXCEPTION
1608: WHEN DUP_VAL_ON_INDEX THEN
1609: BEGIN
1610: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_IND_DEL || AS_GAR.G_START);
1611: DELETE FROM AS_ACCESSES_ALL_ALL ACC
1612: WHERE ACC.sales_lead_id=l_sales_lead_id(i)
1613: AND ACC.freeze_flag = 'N'
1614: AND ACC.lead_id IS NULL
1615: AND ACC.sales_lead_id IS NOT NULL

Line 1691: FROM AS_ACCESSES_ALL_ALL aaa,

1687:
1688: CURSOR lead_owner_totalmode(c_worker_id number) IS
1689: SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id ,
1690: max(DECODE(aaa.created_by_tap_flag,'Y',aaa.access_id,-999)) access_id
1691: FROM AS_ACCESSES_ALL_ALL aaa,
1692: ( SELECT distinct trans_object_id
1693: FROM JTF_TAE_1001_LEAD_TRANS
1694: WHERE worker_id=c_worker_id ) w
1695: WHERE aaa.lead_id is NULL

Line 1709: FROM AS_ACCESSES_ALL aaa

1705: FROM JTF_TAE_1001_LEAD_TRANS w
1706: WHERE worker_id = c_worker_id
1707: AND NOT EXISTS
1708: (SELECT 'x'
1709: FROM AS_ACCESSES_ALL aaa
1710: WHERE aaa.sales_lead_id =w.trans_object_id
1711: AND (aaa.CREATED_BY_TAP_FLAG = 'Y'
1712: OR aaa.owner_flag='Y'));
1713:

Line 1718: FROM AS_ACCESSES_ALL_ALL aaa,

1714:
1715: CURSOR lead_owner_newmode(c_worker_id number) IS
1716: SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id ,
1717: max(DECODE(aaa.created_by_tap_flag,'Y',aaa.access_id,-999)) access_id
1718: FROM AS_ACCESSES_ALL_ALL aaa,
1719: ( SELECT distinct trans_object_id
1720: FROM JTF_TAE_1001_LEAD_NM_TRANS
1721: WHERE worker_id=c_worker_id ) w
1722: WHERE aaa.lead_id is null

Line 1735: FROM AS_ACCESSES_ALL aaa

1731: FROM JTF_TAE_1001_LEAD_NM_TRANS w
1732: WHERE worker_id = c_worker_id
1733: AND NOT EXISTS
1734: (SELECT 'x'
1735: FROM AS_ACCESSES_ALL aaa
1736: WHERE aaa.sales_lead_id =w.trans_object_id
1737: AND (aaa.CREATED_BY_TAP_FLAG = 'Y'
1738: OR aaa.owner_flag='Y'));
1739:

Line 1826: FROM as_accesses_all_all

1822: sl.assign_sales_group_id,
1823: sl.assign_to_person_id
1824: ) =
1825: ( SELECT salesforce_id,sales_group_id,person_id
1826: FROM as_accesses_all_all
1827: WHERE access_id = l_access_id(i)
1828: )
1829: WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
1830: COMMIT;

Line 1855: FROM as_accesses_all_all

1851: sl.assign_sales_group_id,
1852: sl.assign_to_person_id
1853: ) =
1854: ( SELECT salesforce_id,sales_group_id,person_id
1855: FROM as_accesses_all_all
1856: WHERE access_id = l_access_id(i)
1857: )
1858: WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
1859: EXCEPTION

Line 1881: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = NVL(object_version_number,0) + 1,

1877: l_attempts := 1;
1878: WHILE l_attempts < 3 LOOP
1879: BEGIN
1880: FORALL i in l_first .. l_last
1881: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = NVL(object_version_number,0) + 1,
1882: ACC.LAST_UPDATE_DATE = SYSDATE,
1883: ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
1884: ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
1885: ACC.REQUEST_ID = p_terr_globals.request_id,

Line 1903: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = NVL(object_version_number,0) + 1,

1899: ROLLBACK;
1900: IF l_attempts = 3 THEN
1901: FOR i IN l_first .. l_last LOOP
1902: BEGIN
1903: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = NVL(object_version_number,0) + 1,
1904: ACC.LAST_UPDATE_DATE = SYSDATE,
1905: ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
1906: ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
1907: ACC.REQUEST_ID = p_terr_globals.request_id,

Line 1968: FROM as_accesses_all_all aaa,

1964: l_msg_data VARCHAR2(2000);
1965:
1966: CURSOR tot_lead_owner_def(c_worker_id number) IS
1967: SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id
1968: FROM as_accesses_all_all aaa,
1969: ( select distinct trans_object_id
1970: from jtf_tae_1001_lead_trans
1971: where worker_id=c_worker_id ) w
1972: WHERE aaa.lead_id is null

Line 1981: (select 'x' from as_accesses_all aaa

1977: HAVING sum(decode(aaa.owner_flag,'Y',1,0)) = 0
1978: UNION -- Union added for Bug#4035168
1979: select trans_object_id from jtf_tae_1001_lead_trans w
1980: where worker_id = c_worker_id and not exists
1981: (select 'x' from as_accesses_all aaa
1982: where aaa.sales_lead_id =w.trans_object_id);
1983:
1984: CURSOR tot_lead_owner_tap(c_worker_id number) IS
1985: Select trans_object_id from jtf_tae_1001_lead_trans w

Line 1987: (select 'x' from as_accesses_all aaa

1983:
1984: CURSOR tot_lead_owner_tap(c_worker_id number) IS
1985: Select trans_object_id from jtf_tae_1001_lead_trans w
1986: where worker_id = c_worker_id and not exists
1987: (select 'x' from as_accesses_all aaa
1988: where aaa.sales_lead_id =w.trans_object_id);
1989:
1990: CURSOR new_lead_owner_def(c_worker_id number) IS
1991: SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id

Line 1992: FROM as_accesses_all_all aaa,

1988: where aaa.sales_lead_id =w.trans_object_id);
1989:
1990: CURSOR new_lead_owner_def(c_worker_id number) IS
1991: SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id
1992: FROM as_accesses_all_all aaa,
1993: ( select distinct trans_object_id
1994: from JTF_TAE_1001_LEAD_NM_TRANS
1995: where worker_id=c_worker_id ) w
1996: WHERE aaa.lead_id is null

Line 2005: (select 'x' from as_accesses_all aaa

2001: HAVING sum(decode(aaa.owner_flag,'Y',1,0)) = 0
2002: UNION -- Union added for Bug#4035168
2003: select trans_object_id from JTF_TAE_1001_LEAD_NM_TRANS w
2004: where worker_id = c_worker_id and not exists
2005: (select 'x' from as_accesses_all aaa
2006: where aaa.sales_lead_id =w.trans_object_id);
2007:
2008: CURSOR new_lead_owner_tap(c_worker_id number) IS
2009: Select trans_object_id from JTF_TAE_1001_LEAD_NM_TRANS w

Line 2011: (select 'x' from as_accesses_all aaa

2007:
2008: CURSOR new_lead_owner_tap(c_worker_id number) IS
2009: Select trans_object_id from JTF_TAE_1001_LEAD_NM_TRANS w
2010: where worker_id = c_worker_id and not exists
2011: (select 'x' from as_accesses_all aaa
2012: where aaa.sales_lead_id =w.trans_object_id);
2013:
2014: CURSOR c_get_group_id(c_resource_id NUMBER) IS
2015: SELECT grp.group_id

Line 2143: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'INSERT INTO AS_ACCESSES_ALL_ALL');

2139: l_last := l_sales_lead_id.LAST;
2140: END IF;
2141:
2142: WHILE l_attempts < 3 LOOP
2143: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'INSERT INTO AS_ACCESSES_ALL_ALL');
2144: BEGIN
2145: FORALL i in l_first .. l_last
2146: INSERT INTO AS_ACCESSES_ALL_ALL
2147: (ACCESS_ID,

Line 2146: INSERT INTO AS_ACCESSES_ALL_ALL

2142: WHILE l_attempts < 3 LOOP
2143: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'INSERT INTO AS_ACCESSES_ALL_ALL');
2144: BEGIN
2145: FORALL i in l_first .. l_last
2146: INSERT INTO AS_ACCESSES_ALL_ALL
2147: (ACCESS_ID,
2148: LAST_UPDATE_DATE,
2149: LAST_UPDATED_BY,
2150: CREATION_DATE,

Line 2200: from AS_ACCESSES_ALL_ALL aa

2196: FROM DUAL ,
2197: AS_SALES_LEADS L
2198: WHERE L.sales_lead_id = l_sales_lead_id(i)
2199: AND NOT EXISTS ( select 'X'
2200: from AS_ACCESSES_ALL_ALL aa
2201: where aa.sales_lead_id is not null
2202: and aa.lead_id is null
2203: and aa.delete_flag is null
2204: and aa.sales_lead_id = l_sales_lead_id(i)

Line 2222: INSERT INTO AS_ACCESSES_ALL_ALL

2218: l_attempts := l_attempts +1;
2219: IF l_attempts = 3 THEN
2220: FOR i IN l_first .. l_last LOOP
2221: BEGIN
2222: INSERT INTO AS_ACCESSES_ALL_ALL
2223: (ACCESS_ID,
2224: LAST_UPDATE_DATE,
2225: LAST_UPDATED_BY,
2226: CREATION_DATE,

Line 2276: from AS_ACCESSES_ALL_ALL aa

2272: FROM DUAL ,
2273: AS_SALES_LEADS L
2274: WHERE L.sales_lead_id = l_sales_lead_id(i)
2275: AND NOT EXISTS ( select 'X'
2276: from AS_ACCESSES_ALL_ALL aa
2277: where aa.sales_lead_id is not null
2278: and aa.lead_id is null
2279: and aa.delete_flag is null
2280: and aa.sales_lead_id = l_sales_lead_id(i)

Line 2287: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW INSERT INTO AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));

2283: )
2284: );
2285: EXCEPTION
2286: WHEN OTHERS THEN
2287: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW INSERT INTO AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2288: END;
2289: END LOOP;
2290: COMMIT;
2291: END IF;

Line 2294: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'INSERT INTO AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));

2290: COMMIT;
2291: END IF;
2292: END; -- end of deadlock exception
2293: WHEN OTHERS THEN
2294: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'INSERT INTO AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2295: x_errbuf := SQLERRM;
2296: x_retcode := SQLCODE;
2297: x_return_status := FND_API.G_RET_STS_ERROR;
2298: RAISE;

Line 2303: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL');

2299: END;
2300: END LOOP;
2301:
2302: WHILE l_attempts < 3 LOOP
2303: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL');
2304: BEGIN
2305: FORALL i in l_first .. l_last
2306: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,
2307: ACC.LAST_UPDATE_DATE = SYSDATE,

Line 2306: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,

2302: WHILE l_attempts < 3 LOOP
2303: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL');
2304: BEGIN
2305: FORALL i in l_first .. l_last
2306: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,
2307: ACC.LAST_UPDATE_DATE = SYSDATE,
2308: ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
2309: ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
2310: ACC.REQUEST_ID = p_terr_globals.request_id,

Line 2333: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,

2329: l_attempts := l_attempts +1;
2330: IF l_attempts = 3 THEN
2331: FOR i IN l_first .. l_last LOOP
2332: BEGIN
2333: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,
2334: ACC.LAST_UPDATE_DATE = SYSDATE,
2335: ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
2336: ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
2337: ACC.REQUEST_ID = p_terr_globals.request_id,

Line 2350: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));

2346: and ACC.salesforce_id = l_resource_id
2347: and nvl(ACC.sales_group_id,-777) = nvl(l_group_id,-777);
2348: EXCEPTION
2349: WHEN OTHERS THEN
2350: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2351: END;
2352: END LOOP;
2353: COMMIT;
2354: END IF;

Line 2357: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));

2353: COMMIT;
2354: END IF;
2355: END; -- end of deadlock exception
2356: WHEN OTHERS THEN
2357: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2358: x_errbuf := SQLERRM;
2359: x_retcode := SQLCODE;
2360: x_return_status := FND_API.G_RET_STS_ERROR;
2361: RAISE;

Line 2461: FROM as_accesses_all_all aaa,

2457: l_msg_data VARCHAR2(2000);
2458:
2459: CURSOR tot_lead_owner(c_worker_id number) IS
2460: SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id
2461: FROM as_accesses_all_all aaa,
2462: ( select distinct trans_object_id
2463: from jtf_tae_1001_lead_trans
2464: where worker_id=c_worker_id ) w
2465: WHERE aaa.lead_id is null

Line 2474: FROM as_accesses_all_all aaa,

2470: HAVING sum(decode(aaa.owner_flag,'Y',1,0)) = 1;
2471:
2472: CURSOR new_lead_owner(c_worker_id number) IS
2473: SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id
2474: FROM as_accesses_all_all aaa,
2475: ( select distinct trans_object_id
2476: from JTF_TAE_1001_LEAD_NM_TRANS
2477: where worker_id=c_worker_id ) w
2478: WHERE aaa.lead_id is null

Line 2551: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL');

2547: IF l_last > l_sales_lead_id.LAST THEN
2548: l_last := l_sales_lead_id.LAST;
2549: END IF;
2550: WHILE l_attempts < 3 LOOP
2551: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL');
2552: BEGIN
2553: FORALL i in l_first .. l_last
2554: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,
2555: ACC.LAST_UPDATE_DATE = SYSDATE,

Line 2554: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,

2550: WHILE l_attempts < 3 LOOP
2551: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL');
2552: BEGIN
2553: FORALL i in l_first .. l_last
2554: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,
2555: ACC.LAST_UPDATE_DATE = SYSDATE,
2556: ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
2557: ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
2558: ACC.REQUEST_ID = p_terr_globals.request_id,

Line 2578: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,

2574: l_attempts := l_attempts +1;
2575: IF l_attempts = 3 THEN
2576: FOR i IN l_first .. l_last LOOP
2577: BEGIN
2578: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,
2579: ACC.LAST_UPDATE_DATE = SYSDATE,
2580: ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
2581: ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
2582: ACC.REQUEST_ID = p_terr_globals.request_id,

Line 2592: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));

2588: and ACC.owner_flag = 'Y'
2589: and ACC.freeze_flag = 'N';
2590: EXCEPTION
2591: WHEN OTHERS THEN
2592: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2593: END;
2594: END LOOP;
2595: COMMIT;
2596: END IF;

Line 2599: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));

2595: COMMIT;
2596: END IF;
2597: END; -- end of deadlock exception
2598: WHEN OTHERS THEN
2599: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2600: x_errbuf := SQLERRM;
2601: x_retcode := SQLCODE;
2602: x_return_status := FND_API.G_RET_STS_ERROR;
2603: RAISE;

Line 2647: FROM as_accesses_all_all aaa,

2643:
2644: CURSOR tot_lead_owner(c_worker_id number) IS
2645: SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id ,
2646: max(decode(aaa.owner_flag,'Y',aaa.access_id,-999)) access_id
2647: FROM as_accesses_all_all aaa,
2648: ( select distinct trans_object_id
2649: from jtf_tae_1001_lead_trans
2650: where worker_id=c_worker_id ) w
2651: WHERE aaa.lead_id is null

Line 2659: (select 'x' from as_accesses_all aaa

2655: GROUP BY aaa.sales_lead_id
2656: UNION -- Union added for Bug#4035168
2657: select trans_object_id ,0 from jtf_tae_1001_lead_trans w
2658: where worker_id = c_worker_id and not exists
2659: (select 'x' from as_accesses_all aaa
2660: where aaa.sales_lead_id =w.trans_object_id);
2661:
2662:
2663: CURSOR new_lead_owner(c_worker_id number) IS

Line 2666: FROM as_accesses_all_all aaa,

2662:
2663: CURSOR new_lead_owner(c_worker_id number) IS
2664: SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id ,
2665: max(decode(aaa.owner_flag,'Y',aaa.access_id,-999)) access_id
2666: FROM as_accesses_all_all aaa,
2667: ( select distinct trans_object_id
2668: from JTF_TAE_1001_LEAD_NM_TRANS
2669: where worker_id=c_worker_id ) w
2670: WHERE aaa.lead_id is null

Line 2678: (select 'x' from as_accesses_all aaa

2674: GROUP BY aaa.sales_lead_id
2675: UNION -- Union added for Bug#4035168
2676: select trans_object_id ,0 from JTF_TAE_1001_LEAD_NM_TRANS w
2677: where worker_id = c_worker_id and not exists
2678: (select 'x' from as_accesses_all aaa
2679: where aaa.sales_lead_id =w.trans_object_id);
2680:
2681: TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
2682:

Line 2763: FROM as_accesses_all_all

2759: sl.assign_sales_group_id,
2760: sl.assign_to_person_id
2761: ) =
2762: ( SELECT salesforce_id,sales_group_id,person_id
2763: FROM as_accesses_all_all
2764: WHERE access_id = l_access_id(i)
2765: )
2766: WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
2767: COMMIT;

Line 2792: FROM as_accesses_all_all

2788: sl.assign_sales_group_id,
2789: sl.assign_to_person_id
2790: ) =
2791: ( SELECT salesforce_id,sales_group_id,person_id
2792: FROM as_accesses_all_all
2793: WHERE access_id = l_access_id(i)
2794: )
2795: WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
2796: EXCEPTION