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_OPPTYS_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 741: FROM AS_ACCESSES_ALL_ALL A,

737: SELECT /*+ LEADING(WIN) USE_NL(A WIN) INDEX(A) */ A.lead_id,
738: A.salesforce_id,
739: A.sales_group_id,
740: NVL(WIN.full_access_flag,'N')
741: FROM AS_ACCESSES_ALL_ALL A,
742: JTF_TAE_1001_OPPOR_WINNERS WIN
743: WHERE A.lead_id is NOT NULL
744: AND A.sales_lead_id is NULL
745: AND A.delete_flag is NULL

Line 804: UPDATE AS_ACCESSES_ALL_ALL ACC

800: END IF;
801: WHILE l_attempts < 3 LOOP
802: BEGIN
803: FORALL i IN l_first .. l_last
804: UPDATE AS_ACCESSES_ALL_ALL ACC
805: SET object_version_number = NVL(object_version_number,0) + 1,
806: ACC.last_update_date = SYSDATE,
807: ACC.last_updated_by = p_terr_globals.user_id,
808: ACC.last_update_login = p_terr_globals.last_update_login,

Line 832: UPDATE AS_ACCESSES_ALL_ALL ACC

828: IF l_attempts = 3 THEN
829: FOR i IN l_first .. l_last
830: LOOP
831: BEGIN
832: UPDATE AS_ACCESSES_ALL_ALL ACC
833: SET object_version_number = nvl(object_version_number,0) + 1,
834: ACC.last_update_date = SYSDATE,
835: ACC.last_updated_by = p_terr_globals.user_id,
836: ACC.last_update_login = p_terr_globals.last_update_login,

Line 990: INSERT INTO AS_ACCESSES_ALL_ALL

986: END IF;
987: BEGIN
988: 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);
989: FORALL i IN l_first .. l_last
990: INSERT INTO AS_ACCESSES_ALL_ALL
991: ( access_id
992: ,access_type
993: ,salesforce_id
994: ,sales_group_id

Line 1045: FROM AS_ACCESSES_ALL_ALL AA

1041: FROM DUAL, AS_LEADS_ALL L, AS_STATUSES_B STS
1042: WHERE L.lead_id = l_lead_id(i)
1043: AND L.status = STS.status_code
1044: AND NOT EXISTS ( SELECT 'X'
1045: FROM AS_ACCESSES_ALL_ALL AA
1046: WHERE AA.sales_lead_id IS NULL
1047: AND AA.lead_id IS NOT NULL
1048: AND AA.delete_flag IS NULL
1049: AND AA.lead_id = l_lead_id(i)

Line 1061: INSERT INTO AS_ACCESSES_ALL_ALL

1057: WHEN DUP_VAL_ON_INDEX THEN
1058: 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);
1059: FOR i IN l_first .. l_last LOOP
1060: BEGIN
1061: INSERT INTO AS_ACCESSES_ALL_ALL
1062: ( access_id
1063: ,access_type
1064: ,salesforce_id
1065: ,sales_group_id

Line 1116: FROM AS_ACCESSES_ALL_ALL AA

1112: FROM DUAL,AS_LEADS_ALL L, AS_STATUSES_B STS
1113: WHERE L.lead_id = l_lead_id(i)
1114: AND L.status = STS.status_code
1115: AND NOT EXISTS ( SELECT 'X'
1116: FROM AS_ACCESSES_ALL_ALL AA
1117: WHERE AA.sales_lead_id IS NULL
1118: AND AA.lead_id IS NOT NULL
1119: AND AA.delete_flag IS NULL
1120: AND AA.lead_id = l_lead_id(i)

Line 1282: FROM AS_ACCESSES_ALL_ALL A

1278: p_terr_globals.prog_id,
1279: SYSDATE
1280: FROM
1281: ( SELECT DISTINCT a.access_id acc_id
1282: FROM AS_ACCESSES_ALL_ALL A
1283: WHERE A.lead_id=l_lead_id(i)
1284: AND A.sales_group_id = l_sales_group_id(i)
1285: AND A.salesforce_id=l_salesforce_id(i)
1286: AND A.sales_lead_id is NULL

Line 1333: FROM AS_ACCESSES_ALL_ALL A

1329: p_terr_globals.prog_id,
1330: SYSDATE
1331: FROM
1332: ( SELECT DISTINCT a.access_id acc_id
1333: FROM AS_ACCESSES_ALL_ALL A
1334: WHERE A.lead_id=l_lead_id(i)
1335: AND A.sales_group_id = l_sales_group_id(i)
1336: AND A.salesforce_id=l_salesforce_id(i)
1337: AND A.sales_lead_id is NULL

Line 1497: UPDATE AS_ACCESSES_ALL_ALL ACC

1493: WHILE l_attempts < 3 LOOP --{L4
1494: BEGIN
1495: 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);
1496: FORALL i in l_first..l_last
1497: UPDATE AS_ACCESSES_ALL_ALL ACC
1498: SET object_version_number = NVL(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
1499: WHERE ACC.lead_id=l_lead_id(i)
1500: AND ACC.freeze_flag = 'N'
1501: AND ACC.lead_id IS NOT NULL

Line 1523: DELETE FROM AS_ACCESSES_ALL_ALL ACC

1519: WHEN DUP_VAL_ON_INDEX THEN
1520: BEGIN
1521: 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);
1522: FORALL i in l_first..l_last
1523: DELETE FROM AS_ACCESSES_ALL_ALL ACC
1524: WHERE ACC.lead_id=l_lead_id(i)
1525: AND ACC.freeze_flag = 'N'
1526: AND ACC.lead_id IS NOT NULL
1527: AND ACC.sales_lead_id IS NULL

Line 1555: UPDATE AS_ACCESSES_ALL_ALL ACC

1551: IF l_attempts = 3 THEN
1552: FOR i IN l_first .. l_last LOOP --{L5
1553: BEGIN
1554: 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);
1555: UPDATE AS_ACCESSES_ALL_ALL ACC
1556: SET object_version_number = nvl(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
1557: WHERE ACC.lead_id = l_lead_id(i)
1558: AND ACC.freeze_flag = 'N'
1559: AND ACC.lead_id IS NOT NULL

Line 1577: DELETE FROM AS_ACCESSES_ALL_ALL ACC

1573: EXCEPTION
1574: WHEN DUP_VAL_ON_INDEX THEN
1575: BEGIN
1576: 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);
1577: DELETE FROM AS_ACCESSES_ALL_ALL ACC
1578: WHERE ACC.lead_id=l_lead_id(i)
1579: AND ACC.freeze_flag = 'N'
1580: AND ACC.lead_id IS NOT NULL
1581: AND ACC.sales_lead_id IS NULL

Line 1661: FROM AS_ACCESSES_ALL_ALL aaa,

1657:
1658: CURSOR oppty_owner_totalmode(c_worker_id number) IS
1659: SELECT /*+ index(aaa as_accesses_n3) */ aaa.lead_id ,
1660: max(decode(aaa.created_by_tap_flag,'Y',aaa.access_id,-999)) access_id
1661: FROM AS_ACCESSES_ALL_ALL aaa,
1662: ( SELECT distinct trans_object_id
1663: FROM JTF_TAE_1001_OPPOR_TRANS
1664: WHERE worker_id=c_worker_id ) w
1665: WHERE aaa.lead_id IS NOT NULL

Line 1679: FROM AS_ACCESSES_ALL aaa

1675: FROM JTF_TAE_1001_OPPOR_TRANS w
1676: WHERE worker_id = c_worker_id
1677: AND NOT EXISTS
1678: (SELECT 'x'
1679: FROM AS_ACCESSES_ALL aaa
1680: WHERE aaa.lead_id =w.trans_object_id
1681: AND (aaa.CREATED_BY_TAP_FLAG = 'Y'
1682: OR aaa.owner_flag='Y'));
1683:

Line 1688: FROM AS_ACCESSES_ALL_ALL aaa,

1684:
1685: CURSOR oppty_owner_newmode(c_worker_id number) IS
1686: SELECT /*+ index(aaa as_accesses_n3) */ aaa.lead_id ,
1687: max(decode(aaa.created_by_tap_flag,'Y',aaa.access_id,-999)) access_id
1688: FROM AS_ACCESSES_ALL_ALL aaa,
1689: ( SELECT distinct trans_object_id
1690: FROM JTF_TAE_1001_OPPOR_NM_TRANS
1691: WHERE worker_id=c_worker_id ) w
1692: WHERE aaa.lead_id IS NOT null

Line 1706: FROM AS_ACCESSES_ALL aaa

1702: FROM JTF_TAE_1001_OPPOR_NM_TRANS w
1703: WHERE worker_id = c_worker_id
1704: AND NOT EXISTS
1705: (SELECT 'x'
1706: FROM AS_ACCESSES_ALL aaa
1707: WHERE aaa.lead_id =w.trans_object_id
1708: AND (aaa.CREATED_BY_TAP_FLAG = 'Y'
1709: OR aaa.owner_flag='Y'));
1710:

Line 1804: FROM as_accesses_all_all

1800: ( sl.owner_salesforce_id,
1801: sl.owner_sales_group_id
1802: ) =
1803: ( SELECT salesforce_id,sales_group_id
1804: FROM as_accesses_all_all
1805: WHERE access_id = l_access_id(i)
1806: )
1807: WHERE sl.lead_id = l_lead_id(i) ;
1808: COMMIT;

Line 1833: FROM as_accesses_all_all

1829: ( sl.owner_salesforce_id,
1830: sl.owner_sales_group_id
1831: ) =
1832: ( SELECT salesforce_id,sales_group_id
1833: FROM as_accesses_all_all
1834: WHERE access_id = l_access_id(i)
1835: )
1836: WHERE sl.lead_id = l_lead_id(i) ;
1837: EXCEPTION

Line 1870: FROM as_accesses_all_all

1866: ( scd.owner_salesforce_id,
1867: scd.owner_sales_group_id
1868: ) =
1869: ( SELECT salesforce_id,sales_group_id
1870: FROM as_accesses_all_all
1871: WHERE access_id = l_access_id(i)
1872: )
1873: WHERE scd.lead_id = l_lead_id(i) ;
1874: COMMIT;

Line 1899: FROM as_accesses_all_all

1895: ( scd.owner_salesforce_id,
1896: scd.owner_sales_group_id
1897: ) =
1898: ( SELECT salesforce_id,sales_group_id
1899: FROM as_accesses_all_all
1900: WHERE access_id = l_access_id(i)
1901: )
1902: WHERE scd.lead_id = l_lead_id(i) ;
1903: EXCEPTION

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

1920: l_attempts := 1;
1921: WHILE l_attempts < 3 LOOP
1922: BEGIN
1923: FORALL i in l_first .. l_last
1924: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,
1925: ACC.LAST_UPDATE_DATE = SYSDATE,
1926: ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
1927: ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
1928: ACC.REQUEST_ID = p_terr_globals.request_id,

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

1942: ROLLBACK;
1943: IF l_attempts = 3 THEN
1944: FOR i IN l_first .. l_last LOOP
1945: BEGIN
1946: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,
1947: ACC.LAST_UPDATE_DATE = SYSDATE,
1948: ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
1949: ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
1950: ACC.REQUEST_ID = p_terr_globals.request_id,