[Home] [Help]
180: nvl(WIN.org_id,-777) org_id,
181: win.resource_type,
182: ACC.COLLECTOR_ID
183: FROM JTF_TAE_1600_CUST_WINNERS WIN,
184: AR_COLLECTORS ACC,
185: JTF_RS_ROLE_RELATIONS jtrr,
186: JTF_RS_ROLES_B jtr
187: WHERE WIN.SOURCE_ID = -1600
188: AND WIN.worker_id = c_worker_id
395: nvl(WIN.org_id,-777) org_id,
396: win.resource_type,
397: ACC.COLLECTOR_ID
398: FROM JTF_TAE_1600_CUST_WINNERS WIN,
399: AR_COLLECTORS ACC,
400: JTF_RS_ROLE_RELATIONS jtrr,
401: JTF_RS_ROLES_B jtr
402: WHERE WIN.SOURCE_ID = -1600
403: AND WIN.worker_id = c_worker_id
606: ACC.COLLECTOR_ID,
607: cust_acct.cust_account_id,
608: hzp.SITE_USE_ID
609: FROM JTF_TAE_1600_CUST_WINNERS WIN,
610: AR_COLLECTORS ACC,
611: JTF_RS_ROLE_RELATIONS jtrr,
612: JTF_RS_ROLES_B jtr,
613: HZ_CUST_SITE_USES_ALL hzp,
614: HZ_CUST_ACCT_SITES_ALL acct_site,
650: win.resource_type,
651: ACC.COLLECTOR_ID,
652: acct_site.cust_account_id,
653: hzp.SITE_USE_ID
654: FROM JTF_TAE_1600_CUST_WINNERS WIN, AR_COLLECTORS ACC,
655: JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B jtr,
656: HZ_CUST_SITE_USES hzp,
657: HZ_CUST_ACCT_SITES acct_site
658: WHERE WIN.SOURCE_ID = -1600
903: AND WIN.RESOURCE_TYPE IN ('RS_EMPLOYEE', 'RS_GROUP')
904: AND jtrr.role_resource_id = WIN.RESOURCE_ID
905: AND jtr.ROLE_ID = jtrr.role_id and jtr.role_type_code = 'COLLECTIONS'
906: AND NOT EXISTS
907: (SELECT RESOURCE_ID, RESOURCE_TYPE FROM AR_COLLECTORS acc
908: WHERE ACC.RESOURCE_ID = WIN.RESOURCE_ID AND
909: ACC.RESOURCE_TYPE = DECODE(WIN.RESOURCE_TYPE, 'RS_EMPLOYEE', 'RS_RESOURCE', WIN.RESOURCE_TYPE));
910:
911: Begin
908: WHERE ACC.RESOURCE_ID = WIN.RESOURCE_ID AND
909: ACC.RESOURCE_TYPE = DECODE(WIN.RESOURCE_TYPE, 'RS_EMPLOYEE', 'RS_RESOURCE', WIN.RESOURCE_TYPE));
910:
911: Begin
912: -- Bulk Read the Non-existents in AR_COLLECTORS
913: l_loop_count := 0;
914: l_max_fetches := p_terr_globals.cursor_limit;
915: LOOP
916: if (l_limit_flag) then
916: if (l_limit_flag) then
917: EXIT;
918: End If;
919: l_loop_count := l_loop_count + 1;
920: IEX_TERR_WINNERS_PUB.Print_Debug('*** Check Resources not in AR Collectors. LOOPING Count -> :'||l_loop_count);
921:
922: --------------------------------
923: l_attempts := 1;
924: l_exceptions := FALSE;
953: if l_missSF_id.count < l_max_fetches then
954: l_limit_flag := TRUE;
955: end if;
956:
957: IEX_TERR_WINNERS_PUB.Print_Debug('--- Start INSERT OF AR_COLLECTORS = . ' || l_missSF_id.count);
958:
959: IF l_missSF_id.count > 0 THEN -- if l_SalesForce_id.count > 0
960:
961: l_attempts := 1;
980: WHERE resource_id = l_missSF_id(i);
981: END IF;
982: IEX_TERR_WINNERS_PUB.Print_Debug('After selecting Resource_name = '|| l_resource_name );
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, ' Inserted to the AR_COLLECTORS. Collector_ID ');
1012:
1013: EXCEPTION
1014: WHEN OTHERS THEN
1015: FND_FILE.PUT_LINE(FND_FILE.LOG,' Error while selecting resource/groupname' );
1038: FROM jtf_rs_resource_extns_vl
1039: WHERE resource_id = l_missSF_id(i);
1040: END IF;
1041:
1042: INSERT INTO AR_COLLECTORS
1043: (COLLECTOR_ID ,
1044: LAST_UPDATED_BY ,
1045: LAST_UPDATE_DATE ,
1046: LAST_UPDATE_LOGIN ,
1052: STATUS ,
1053: RESOURCE_ID ,
1054: RESOURCE_TYPE )
1055: VALUES
1056: (AR_COLLECTORS_S.NEXTVAL ,
1057: p_terr_globals.user_id ,
1058: sysdate ,
1059: p_terr_globals.last_update_login ,
1060: sysdate ,
1065: 'A',
1066: l_missSF_id(i),
1067: decode(l_missResType(i),'RS_GROUP','RS_GROUP','RS_RESOURCE' )) ;
1068:
1069: FND_FILE.PUT_LINE(FND_FILE.LOG, ' After Inserting in to the AR_COLLECTORS');
1070:
1071: EXCEPTION
1072: WHEN OTHERS THEN
1073: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
1136:
1137: CURSOR c_MissedProfiles(c_worker_id number) IS
1138: SELECT WIN.TRANS_OBJECT_id
1139: FROM JTF_TAE_1600_CUST_WINNERS WIN,
1140: AR_COLLECTORS ACC,
1141: JTF_RS_ROLE_RELATIONS jtrr,
1142: JTF_RS_ROLES_B jtr
1143: WHERE WIN.SOURCE_ID = -1600
1144: AND WIN.worker_id = c_worker_id
1161:
1162: Begin
1163: FND_FILE.PUT_LINE(FND_FILE.LOG,' *** Creating Party Profiles..... ***');
1164:
1165: -- Bulk Read the Non-existents in AR_COLLECTORS
1166: l_loop_count := 0;
1167: l_max_fetches := p_terr_globals.cursor_limit;
1168: LOOP
1169: if (l_limit_flag) then
1360:
1361: CURSOR c_MissedProfiles(c_worker_id number) IS
1362: SELECT win.trans_object_id, hzp.SITE_USE_ID,
1363: acct_site.cust_account_id
1364: FROM JTF_TAE_1600_CUST_WINNERS WIN, AR_COLLECTORS ACC,
1365: JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B jtr,
1366: HZ_CUST_SITE_USES hzp,
1367: HZ_CUST_ACCT_SITES acct_site
1368: WHERE WIN.SOURCE_ID = -1600