[Home] [Help]
669: Load_Schema_Name;
670:
671: --Create temporary indexes for leads
672: Create_Temp_Index('AS_SALES_LEADS','SALES_LEAD_ID,STATUS_CODE,CLOSE_REASON',G_INDEX_SUFFIX1);
673: Create_Temp_Index('AS_ACCESSES_ALL_ALL','ACCESS_ID,DELETE_FLAG,SALES_LEAD_ID',G_INDEX_SUFFIX1);
674:
675: --Create temporary indexes for opportunities
676: Create_Temp_Index('AS_LEADS_ALL','LEAD_ID,STATUS,CLOSE_REASON',G_INDEX_SUFFIX2);
677: Create_Temp_Index('AS_ACCESSES_ALL_ALL','ACCESS_ID,DELETE_FLAG,LEAD_ID',G_INDEX_SUFFIX2);
673: Create_Temp_Index('AS_ACCESSES_ALL_ALL','ACCESS_ID,DELETE_FLAG,SALES_LEAD_ID',G_INDEX_SUFFIX1);
674:
675: --Create temporary indexes for opportunities
676: Create_Temp_Index('AS_LEADS_ALL','LEAD_ID,STATUS,CLOSE_REASON',G_INDEX_SUFFIX2);
677: Create_Temp_Index('AS_ACCESSES_ALL_ALL','ACCESS_ID,DELETE_FLAG,LEAD_ID',G_INDEX_SUFFIX2);
678: EXCEPTION
679: WHEN others THEN
680: ERRBUF := SQLERRM;
681: RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
694: RETCODE OUT NOCOPY VARCHAR2) IS
695: BEGIN
696: -- Drop temporary index for Leads
697: Drop_Temp_Index('AS_SALES_LEADS',G_INDEX_SUFFIX1);
698: Drop_Temp_Index('AS_ACCESSES_ALL_ALL',G_INDEX_SUFFIX1);
699:
700: -- Drop temporary index for Opportunities
701: Drop_Temp_Index('AS_LEADS_ALL',G_INDEX_SUFFIX2);
702: Drop_Temp_Index('AS_ACCESSES_ALL_ALL',G_INDEX_SUFFIX2);
698: Drop_Temp_Index('AS_ACCESSES_ALL_ALL',G_INDEX_SUFFIX1);
699:
700: -- Drop temporary index for Opportunities
701: Drop_Temp_Index('AS_LEADS_ALL',G_INDEX_SUFFIX2);
702: Drop_Temp_Index('AS_ACCESSES_ALL_ALL',G_INDEX_SUFFIX2);
703: EXCEPTION when others then
704: null;
705: END POST_UPDATE;
706:
834: from dual;
835:
836: CURSOR Get_AC_Min_Id IS
837: select min(access_id)
838: from as_accesses_all_all;
839:
840: BEGIN
841:
842: --
1144: END IF;
1145:
1146:
1147: if (l_debug) Then
1148: FND_FILE.PUT_LINE(FND_FILE.LOG,'Minimum Id - Max id for as_accesses_all_all for this worker :' || l_min_id ||' to '||l_max_id);
1149: end if;
1150:
1151:
1152:
1159: FND_FILE.PUT_LINE(FND_FILE.LOG,'Current loop count:' || l_count);
1160: end if;
1161: -- update statements here
1162: IF Upper(X_STATUS_CODE) <> 'ALL' THEN
1163: UPDATE /*+ INDEX(acc AS_ACCESSES_ALL_ALL_MT1)*/ AS_ACCESSES_ALL acc
1164: SET object_version_number = nvl(object_version_number,0) + 1,
1165: acc.OPEN_FLAG = Upper(X_OPEN_FLAG),
1166: acc.last_update_date = sysdate,
1167: acc.last_updated_by = l_last_updated_by,
1178: )
1179: AND acc.sales_lead_id is not null;
1180:
1181:
1182: UPDATE /*+ INDEX(acc AS_ACCESSES_ALL_ALL_MT2)*/ AS_ACCESSES_ALL acc
1183: SET object_version_number = nvl(object_version_number,0) + 1,
1184: acc.OPEN_FLAG = Upper(X_OPEN_FLAG),
1185: acc.last_update_date = sysdate,
1186: acc.last_updated_by = l_last_updated_by,
1196: AND acc.lead_id is not null ))
1197: AND acc.lead_id is not null;
1198:
1199: ELSE
1200: UPDATE /*+ INDEX(acc AS_ACCESSES_ALL_ALL_MT1)*/ AS_ACCESSES_ALL acc
1201: SET object_version_number = nvl(object_version_number,0) + 1,
1202: acc.OPEN_FLAG = (SELECT /*+ INDEX(slds AS_SALES_LEADS_MT1)*/ st.opp_open_status_flag
1203: FROM AS_STATUSES_B st,AS_SALES_LEADS slds
1204: WHERE st.status_code = slds.status_code
1214: AND acc.sales_lead_id is not null;
1215:
1216:
1217:
1218: UPDATE /*+ INDEX(acc AS_ACCESSES_ALL_ALL_MT2)*/ AS_ACCESSES_ALL acc
1219: SET object_version_number = nvl(object_version_number,0) + 1,
1220: acc.OPEN_FLAG = (SELECT /*+ INDEX(lds AS_LEADS_ALL_MT2)*/ st.opp_open_status_flag
1221: FROM AS_STATUSES_B st,AS_LEADS_ALL lds
1222: WHERE st.status_code = lds.status
1239: commit;
1240:
1241:
1242: if l_debug then
1243: FND_FILE.PUT_LINE(FND_FILE.LOG,'Update of denormed open flag in as_accesses_all_all (for leads and opps) finished successfully');
1244: FND_FILE.PUT_LINE(FND_FILE.LOG,'for range :'|| l_min_id ||' to '||l_max_id);
1245: FND_FILE.PUT_LINE(FND_FILE.LOG,'End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1246: end if;
1247: