[Home] [Help]
735: CURSOR merge_records(c_worker_id number) IS
736: SELECT DISTINCT
737: A.quote_number , A.resource_id
738: FROM JTF_TAE_1001_QUOTE_WINNERS WIN,
739: ASO_QUOTE_ACCESSES A
740: WHERE WIN.trans_object_id = A.quote_number
741: AND WIN.source_id = -1001
742: AND WIN.resource_id = A.RESOURCE_ID
743: AND WIN.worker_id = c_worker_id
798: END IF;
799: WHILE l_attempts < 3 LOOP
800: BEGIN
801: FORALL i IN l_first .. l_last
802: UPDATE ASO_QUOTE_ACCESSES A
803: SET (update_access_flag ,
804: resource_grp_id ,
805: role_id ,
806: territory_id )
825: IF l_attempts = 3 THEN
826: FOR i IN l_first .. l_last
827: LOOP
828: BEGIN
829: UPDATE ASO_QUOTE_ACCESSES A
830: SET (update_access_flag ,
831: resource_grp_id ,
832: role_id ,
833: territory_id )
907: CURSOR merge_records(c_worker_id number) IS
908: SELECT DISTINCT
909: A.access_id,WIN.full_access_flag,WIN.terr_id
910: FROM JTF_TAE_1001_QUOTE_WINNERS WIN,
911: ASO_QUOTE_ACCESSES A
912: WHERE WIN.trans_object_id = A.quote_number
913: AND WIN.source_id = -1001
914: AND WIN.resource_id = A.RESOURCE_ID
915: AND WIN.worker_id = c_worker_id
962: END IF;
963: WHILE l_attempts < 3 LOOP
964: BEGIN
965: FORALL i IN l_first .. l_last
966: UPDATE ASO_QUOTE_ACCESSES A
967: SET update_access_flag = l_faf(i),
968: territory_id = l_terr_id(i)
969: WHERE access_id = l_access_id(i);
970: COMMIT;
979: IF l_attempts = 3 THEN
980: FOR i IN l_first .. l_last
981: LOOP
982: BEGIN
983: UPDATE ASO_QUOTE_ACCESSES A
984: SET update_access_flag = l_faf(i),
985: territory_id = l_terr_id(i)
986: WHERE access_id = l_access_id(i);
987: EXCEPTION
1072: AND W.source_id = -1001
1073: AND W.worker_id = c_worker_id
1074: AND NOT EXISTS
1075: (SELECT 'X'
1076: FROM aso_quote_accesses A
1077: WHERE W.trans_object_id = A.quote_number
1078: AND W.resource_id = a.RESOURCE_ID)
1079: GROUP BY W.trans_object_id, W.resource_id;
1080:
1130: END IF;
1131: BEGIN
1132: 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);
1133: FORALL i IN l_first .. l_last
1134: INSERT INTO ASO_QUOTE_ACCESSES (
1135: ACCESS_ID,
1136: QUOTE_NUMBER,
1137: RESOURCE_ID,
1138: RESOURCE_GRP_ID,
1151: TERRITORY_ID,
1152: TERRITORY_SOURCE_FLAG,
1153: ROLE_ID
1154: ) VALUES (
1155: ASO_QUOTE_ACCESSES_S.nextval,
1156: l_quote_number_id(i),
1157: l_resource_id(i),
1158: l_sales_group_id(i),
1159: p_terr_globals.user_id,
1178: WHEN DUP_VAL_ON_INDEX THEN
1179: 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);
1180: FOR i IN l_first .. l_last LOOP
1181: BEGIN
1182: INSERT INTO ASO_QUOTE_ACCESSES (
1183: access_id,
1184: quote_number,
1185: resource_id,
1186: resource_grp_id,
1199: territory_id,
1200: territory_source_flag,
1201: role_id
1202: ) VALUES (
1203: aso_quote_accesses_s.NEXTVAL,
1204: l_quote_number_id(i),
1205: l_resource_id(i),
1206: l_sales_group_id(i),
1207: p_terr_globals.user_id,
1365: SYSDATE
1366: FROM
1367: (SELECT AA.acc_id
1368: FROM ( SELECT DISTINCT a.access_id acc_id
1369: FROM ASO_QUOTE_ACCESSES A
1370: WHERE A.quote_number = l_quote_number_id(i)
1371: AND A.resource_id=l_resource_id(i)
1372: ) AA
1373: WHERE NOT EXISTS
1414: SYSDATE
1415: FROM
1416: (SELECT AA.acc_id
1417: FROM ( SELECT DISTINCT a.access_id acc_id
1418: FROM ASO_QUOTE_ACCESSES A
1419: WHERE A.quote_number = l_quote_number_id(i)
1420: AND A.resource_id=l_resource_id(i)
1421: ) AA
1422: WHERE NOT EXISTS
1498: | the valid salesrep joins in the not exists clause..
1499: |-------------------------------------------------------------------------------*/
1500: CURSOR set_primary_srep_sc1_total(c_worker_id number) IS
1501: SELECT W.trans_object_id, MAX(access_id)
1502: FROM ASO_QUOTE_ACCESSES AQA1,
1503: ( SELECT DISTINCT trans_object_id -- Q8
1504: FROM JTF_TAE_1001_QUOTE_TRANS TRANS ,ASO_QUOTE_HEADERS_ALL AQH
1505: WHERE worker_id=c_worker_id
1506: AND TRANS.trans_object_id = AQH.quote_number
1506: AND TRANS.trans_object_id = AQH.quote_number
1507: AND NOT EXISTS
1508: (
1509: SELECT 'X'
1510: FROM ASO_QUOTE_ACCESSES AQA2, JTF_RS_SRP_Vl SREP1
1511: WHERE AQH.resource_Id = AQA2.resource_id
1512: AND AQH.quote_number = AQA2.quote_number
1513: AND SREP1.resource_id = AQA2.resource_id
1514: AND NVL(AQA2.update_access_flag,'N') = 'Y'
1526: GROUP BY W.trans_object_id;
1527:
1528: CURSOR set_primary_srep_sc1_nm(c_worker_id number) IS
1529: SELECT W.trans_object_id, MAX(access_id)
1530: FROM ASO_QUOTE_ACCESSES AQA1,
1531: ( SELECT DISTINCT trans_object_id -- Q8
1532: FROM JTF_TAE_1001_QUOTE_NM_TRANS TRANS ,ASO_QUOTE_HEADERS_ALL AQH
1533: WHERE worker_id=c_worker_id
1534: AND TRANS.trans_object_id = AQH.quote_number
1534: AND TRANS.trans_object_id = AQH.quote_number
1535: AND NOT EXISTS
1536: (
1537: SELECT 'X'
1538: FROM ASO_QUOTE_ACCESSES AQA2, JTF_RS_SRP_Vl SREP1
1539: WHERE AQH.resource_Id = AQA2.resource_id
1540: AND AQH.quote_number = AQA2.quote_number
1541: AND SREP1.resource_id = AQA2.resource_id
1542: AND NVL(AQA2.update_access_flag,'N') = 'Y'
1565: WHERE worker_id=c_worker_id
1566: AND TRANS.trans_object_id = AQH.quote_number
1567: AND NOT EXISTS
1568: (SELECT 'X'
1569: FROM ASO_QUOTE_ACCESSES AQA2, JTF_RS_SRP_Vl SREP
1570: WHERE SREP.resource_Id = AQA2.resource_id
1571: AND AQH.quote_number = AQA2.quote_number
1572: AND NVL(AQA2.update_access_flag,'N') = 'Y'
1573: AND NVL(SREP.status,'A') = 'A'
1582: WHERE worker_id=c_worker_id
1583: AND TRANS.trans_object_id = AQH.quote_number
1584: AND NOT EXISTS
1585: (SELECT 'X'
1586: FROM ASO_QUOTE_ACCESSES AQA2, JTF_RS_SRP_Vl SREP
1587: WHERE SREP.resource_Id = AQA2.resource_id
1588: AND AQH.quote_number = AQA2.quote_number
1589: AND NVL(AQA2.update_access_flag,'N') = 'Y'
1590: AND NVL(SREP.status,'A') = 'A'
1672: last_updated_by = FND_GLOBAL.USER_ID,
1673: last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
1674: (resource_id,resource_grp_id) =
1675: ( SELECT resource_id,resource_grp_id
1676: FROM ASO_QUOTE_ACCESSES AQA
1677: WHERE AQA.access_id = l_access_id(i)
1678: )
1679: WHERE quote_number = l_quote_number_id(I);
1680: COMMIT;
1694: last_updated_by = FND_GLOBAL.USER_ID,
1695: last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
1696: (resource_id,resource_grp_id) =
1697: ( SELECT resource_id,resource_grp_id
1698: FROM ASO_QUOTE_ACCESSES AQA
1699: WHERE AQA.access_id = l_access_id(i)
1700: )
1701: WHERE quote_number = l_quote_number_id(I);
1702: EXCEPTION
1795: WHERE quote_number = l_quote_number_id(I) RETURNING ORG_ID,RESOURCE_ID,RESOURCE_GRP_ID
1796: BULK COLLECT INTO l_org_id,l_sales_rep_id,l_sales_grp_id;
1797: COMMIT;
1798: FORALL i in l_first .. l_last
1799: INSERT INTO ASO_QUOTE_ACCESSES (
1800: ACCESS_ID,
1801: QUOTE_NUMBER,
1802: RESOURCE_ID,
1803: RESOURCE_GRP_ID,
1816: TERRITORY_ID,
1817: TERRITORY_SOURCE_FLAG,
1818: ROLE_ID
1819: )
1820: SELECT ASO_QUOTE_ACCESSES_S.nextval,
1821: l_quote_number_id(i),
1822: l_sales_rep_id(i),
1823: l_sales_grp_id(i),
1824: p_terr_globals.user_id,
1846: WHEN DUP_VAL_ON_INDEX THEN
1847: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1848: FOR i IN l_first .. l_last LOOP
1849: BEGIN
1850: INSERT INTO ASO_QUOTE_ACCESSES (
1851: ACCESS_ID,
1852: QUOTE_NUMBER,
1853: RESOURCE_ID,
1854: RESOURCE_GRP_ID,
1867: TERRITORY_ID,
1868: TERRITORY_SOURCE_FLAG,
1869: ROLE_ID
1870: )
1871: SELECT ASO_QUOTE_ACCESSES_S.nextval,
1872: l_quote_number_id(i),
1873: l_sales_rep_id(i),
1874: l_sales_grp_id(i),
1875: p_terr_globals.user_id,
1919: AND b.organization_id = AQH.org_id)
1920: WHERE quote_number = l_quote_number_id(I) RETURNING ORG_ID,RESOURCE_ID,RESOURCE_GRP_ID
1921: INTO l_ind_org_id,l_ind_sales_rep_id,l_ind_sales_grp_id;
1922:
1923: INSERT INTO ASO_QUOTE_ACCESSES (
1924: ACCESS_ID,
1925: QUOTE_NUMBER,
1926: RESOURCE_ID,
1927: RESOURCE_GRP_ID,
1940: TERRITORY_ID,
1941: TERRITORY_SOURCE_FLAG,
1942: ROLE_ID
1943: )
1944: SELECT ASO_QUOTE_ACCESSES_S.nextval,
1945: l_quote_number_id(i),
1946: l_sales_rep_id(i),
1947: l_sales_grp_id(i),
1948: p_terr_globals.user_id,
1978: ROLLBACK;
1979: FOR i IN l_first .. l_last LOOP
1980: BEGIN
1981: IF aso_utility_pvt.get_ou_attribute_value(aso_utility_pvt.G_DEFAULT_SALESREP,l_org_id(i)) IS NOT NULL THEN
1982: INSERT INTO ASO_QUOTE_ACCESSES (
1983: ACCESS_ID,
1984: QUOTE_NUMBER,
1985: RESOURCE_ID,
1986: RESOURCE_GRP_ID,
1999: TERRITORY_ID,
2000: TERRITORY_SOURCE_FLAG,
2001: ROLE_ID
2002: )
2003: SELECT ASO_QUOTE_ACCESSES_S.nextval,
2004: l_quote_number_id(i),
2005: l_sales_rep_id(i),
2006: l_sales_grp_id(i),
2007: p_terr_globals.user_id,
2151: WHILE l_attempts < 3 LOOP --{L4
2152: BEGIN
2153: 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);
2154: FORALL i in l_first..l_last
2155: DELETE FROM ASO_QUOTE_ACCESSES ACC
2156: WHERE ACC.QUOTE_NUMBER =l_quote_number_id(i)
2157: AND NVL(ACC.KEEP_FLAG,'N') <> 'Y'
2158: AND NOT EXISTS (SELECT 'X'
2159: FROM JTF_TAE_1001_QUOTE_WINNERS W
2173: IF l_attempts = 3 THEN
2174: FOR i IN l_first .. l_last LOOP --{L5
2175: BEGIN
2176: 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);
2177: DELETE FROM ASO_QUOTE_ACCESSES ACC
2178: WHERE ACC.QUOTE_NUMBER =l_quote_number_id(i)
2179: AND NVL(ACC.KEEP_FLAG,'N') <> 'Y'
2180: AND NOT EXISTS (SELECT 'X'
2181: FROM JTF_TAE_1001_QUOTE_WINNERS W