[Home] [Help]
168: nvl(WIN.org_id,-777) org_id,
169: win.resource_type,
170: ACC.COLLECTOR_ID
171: FROM JTF_TAE_1600_CUST_WINNERS WIN,
172: AR_COLLECTORS ACC,
173: JTF_RS_ROLE_RELATIONS jtrr,
174: JTF_RS_ROLES_B jtr
175: WHERE WIN.SOURCE_ID = -1600
176: AND WIN.worker_id = c_worker_id
362: nvl(WIN.org_id,-777) org_id,
363: win.resource_type,
364: ACC.COLLECTOR_ID
365: FROM JTF_TAE_1600_CUST_WINNERS WIN,
366: AR_COLLECTORS ACC,
367: JTF_RS_ROLE_RELATIONS jtrr,
368: JTF_RS_ROLES_B jtr
369: WHERE WIN.SOURCE_ID = -1600
370: AND WIN.worker_id = c_worker_id
561: ACC.COLLECTOR_ID,
562: cust_acct.cust_account_id,
563: hzp.SITE_USE_ID
564: FROM JTF_TAE_1600_CUST_WINNERS WIN,
565: AR_COLLECTORS ACC,
566: JTF_RS_ROLE_RELATIONS jtrr,
567: JTF_RS_ROLES_B jtr,
568: HZ_CUST_SITE_USES_ALL hzp,
569: HZ_CUST_ACCT_SITES_ALL acct_site,
605: win.resource_type,
606: ACC.COLLECTOR_ID,
607: acct_site.cust_account_id,
608: hzp.SITE_USE_ID
609: FROM JTF_TAE_1600_CUST_WINNERS WIN, AR_COLLECTORS ACC,
610: JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B jtr,
611: HZ_CUST_SITE_USES hzp,
612: HZ_CUST_ACCT_SITES acct_site
613: WHERE WIN.SOURCE_ID = -1600
845: AND WIN.RESOURCE_TYPE IN ('RS_EMPLOYEE', 'RS_GROUP')
846: AND jtrr.role_resource_id = WIN.RESOURCE_ID
847: AND jtr.ROLE_ID = jtrr.role_id and jtr.role_type_code = 'COLLECTIONS'
848: AND NOT EXISTS
849: (SELECT RESOURCE_ID, RESOURCE_TYPE FROM AR_COLLECTORS acc
850: WHERE ACC.RESOURCE_ID = WIN.RESOURCE_ID AND
851: ACC.RESOURCE_TYPE = DECODE(WIN.RESOURCE_TYPE, 'RS_EMPLOYEE', 'RS_RESOURCE', WIN.RESOURCE_TYPE));
852:
853: Begin
850: WHERE ACC.RESOURCE_ID = WIN.RESOURCE_ID AND
851: ACC.RESOURCE_TYPE = DECODE(WIN.RESOURCE_TYPE, 'RS_EMPLOYEE', 'RS_RESOURCE', WIN.RESOURCE_TYPE));
852:
853: Begin
854: -- Bulk Read the Non-existents in AR_COLLECTORS
855: l_loop_count := 0;
856: l_max_fetches := p_terr_globals.cursor_limit;
857: LOOP
858: if (l_limit_flag) then
858: if (l_limit_flag) then
859: EXIT;
860: End If;
861: l_loop_count := l_loop_count + 1;
862: IEX_TERR_WINNERS_PUB.Print_Debug('*** Check Resources not in AR Collectors. LOOPING Count -> :'||l_loop_count);
863:
864: --------------------------------
865: l_attempts := 1;
866: l_exceptions := FALSE;
895: if l_missSF_id.count < l_max_fetches then
896: l_limit_flag := TRUE;
897: end if;
898:
899: IEX_TERR_WINNERS_PUB.Print_Debug('--- Start INSERT OF AR_COLLECTORS = . ' || l_missSF_id.count);
900:
901: IF l_missSF_id.count > 0 THEN -- if l_SalesForce_id.count > 0
902:
903: l_attempts := 1;
922: WHERE resource_id = l_missSF_id(i);
923: END IF;
924: IEX_TERR_WINNERS_PUB.Print_Debug('After selecting Resource_name = '|| l_resource_name );
925:
926: INSERT INTO AR_COLLECTORS
927: (COLLECTOR_ID ,
928: LAST_UPDATED_BY ,
929: LAST_UPDATE_DATE ,
930: LAST_UPDATE_LOGIN ,
936: STATUS ,
937: RESOURCE_ID ,
938: RESOURCE_TYPE )
939: VALUES
940: (AR_COLLECTORS_S.NEXTVAL ,
941: p_terr_globals.user_id ,
942: sysdate ,
943: p_terr_globals.last_update_login ,
944: sysdate ,
949: 'A',
950: l_missSF_id(i),
951: decode(l_missResType(i),'RS_GROUP','RS_GROUP','RS_RESOURCE' )) ;
952:
953: FND_FILE.PUT_LINE(FND_FILE.LOG, ' Inserted to the AR_COLLECTORS. Collector_ID ');
954:
955: EXCEPTION
956: WHEN OTHERS THEN
957: FND_FILE.PUT_LINE(FND_FILE.LOG,' Error while selecting resource/groupname' );
980: FROM jtf_rs_resource_extns_vl
981: WHERE resource_id = l_missSF_id(i);
982: END IF;
983:
984: INSERT INTO AR_COLLECTORS
985: (COLLECTOR_ID ,
986: LAST_UPDATED_BY ,
987: LAST_UPDATE_DATE ,
988: LAST_UPDATE_LOGIN ,
994: STATUS ,
995: RESOURCE_ID ,
996: RESOURCE_TYPE )
997: VALUES
998: (AR_COLLECTORS_S.NEXTVAL ,
999: p_terr_globals.user_id ,
1000: sysdate ,
1001: p_terr_globals.last_update_login ,
1002: sysdate ,
1007: 'A',
1008: l_missSF_id(i),
1009: decode(l_missResType(i),'RS_GROUP','RS_GROUP','RS_RESOURCE' )) ;
1010:
1011: FND_FILE.PUT_LINE(FND_FILE.LOG, ' After Inserting in to the AR_COLLECTORS');
1012:
1013: EXCEPTION
1014: WHEN OTHERS THEN
1015: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
1078:
1079: CURSOR c_MissedProfiles(c_worker_id number) IS
1080: SELECT WIN.TRANS_OBJECT_id
1081: FROM JTF_TAE_1600_CUST_WINNERS WIN,
1082: AR_COLLECTORS ACC,
1083: JTF_RS_ROLE_RELATIONS jtrr,
1084: JTF_RS_ROLES_B jtr
1085: WHERE WIN.SOURCE_ID = -1600
1086: AND WIN.worker_id = c_worker_id
1100: GROUP BY WIN.TRANS_OBJECT_id;
1101: l_profile_id NUMBER;
1102:
1103: Begin
1104: -- Bulk Read the Non-existents in AR_COLLECTORS
1105: l_loop_count := 0;
1106: l_max_fetches := p_terr_globals.cursor_limit;
1107: LOOP
1108: if (l_limit_flag) then
1298:
1299: CURSOR c_MissedProfiles(c_worker_id number) IS
1300: SELECT win.trans_object_id, hzp.SITE_USE_ID,
1301: acct_site.cust_account_id
1302: FROM JTF_TAE_1600_CUST_WINNERS WIN, AR_COLLECTORS ACC,
1303: JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B jtr,
1304: HZ_CUST_SITE_USES hzp,
1305: HZ_CUST_ACCT_SITES acct_site
1306: WHERE WIN.SOURCE_ID = -1600