[Home] [Help]
1: PACKAGE BODY AS_GAR_LEADS_PUB AS
2: /* $Header: asxgrldb.pls 120.8.12010000.2 2010/03/01 10:38:13 sariff ship $ */
3:
4: ---------------------------------------------------------------------------
5: -- Start of Comments
3:
4: ---------------------------------------------------------------------------
5: -- Start of Comments
6: ---------------------------------------------------------------------------
7: -- PACKAGE NAME: AS_GAR_LEADS_PUB
8: -- ---------------------------------------------------------------------
9: -- PURPOSE
10: -- --------
11: -- This package contains procedures to accomplish each of the following
43: p_trace_mode IN VARCHAR2,
44: p_worker_id IN VARCHAR2,
45: P_percent_analyzed IN NUMBER )
46: IS
47: l_terr_globals AS_GAR.TERR_GLOBALS;
48: l_msg_count NUMBER;
49: l_msg_data VARCHAR2(2000);
50: l_errbuf VARCHAR2(4000);
51: l_retcode VARCHAR2(255);
57: l_assign_manual_flag VARCHAR2(1);
58: l_resource_id NUMBER;
59:
60: BEGIN
61: AS_GAR.g_debug_flag := p_debug_mode;
62: IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
63: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_START);
64:
65: IF p_run_mode = AS_GAR.G_TOTAL_MODE THEN
58: l_resource_id NUMBER;
59:
60: BEGIN
61: AS_GAR.g_debug_flag := p_debug_mode;
62: IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
63: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_START);
64:
65: IF p_run_mode = AS_GAR.G_TOTAL_MODE THEN
66: l_target_type := 'TOTAL';
59:
60: BEGIN
61: AS_GAR.g_debug_flag := p_debug_mode;
62: IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
63: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_START);
64:
65: IF p_run_mode = AS_GAR.G_TOTAL_MODE THEN
66: l_target_type := 'TOTAL';
67: ELSIF p_run_mode = AS_GAR.G_NEW_MODE THEN
61: AS_GAR.g_debug_flag := p_debug_mode;
62: IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
63: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_START);
64:
65: IF p_run_mode = AS_GAR.G_TOTAL_MODE THEN
66: l_target_type := 'TOTAL';
67: ELSIF p_run_mode = AS_GAR.G_NEW_MODE THEN
68: l_target_type := 'INCREMENTAL';
69: END If;
63: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_START);
64:
65: IF p_run_mode = AS_GAR.G_TOTAL_MODE THEN
66: l_target_type := 'TOTAL';
67: ELSIF p_run_mode = AS_GAR.G_NEW_MODE THEN
68: l_target_type := 'INCREMENTAL';
69: END If;
70:
71: -- Set the Global variables
68: l_target_type := 'INCREMENTAL';
69: END If;
70:
71: -- Set the Global variables
72: AS_GAR.INIT(
73: p_run_mode,
74: p_worker_id,
75: l_terr_globals);
76:
74: p_worker_id,
75: l_terr_globals);
76:
77: /* This inserts into Lead winners */
78: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_START);
79: JTY_ASSIGN_BULK_PUB.GET_WINNERS
80: ( p_api_version_number => 1.0,
81: p_init_msg_list => FND_API.G_TRUE,
82: p_source_id => -1001,
91: ERRBUF => l_errbuf,
92: RETCODE => l_retcode);
93:
94:
95: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_END);
96: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_RETURN_STATUS || l_return_status);
97:
98: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
99: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW, l_errbuf, l_retcode);
92: RETCODE => l_retcode);
93:
94:
95: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_END);
96: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_RETURN_STATUS || l_return_status);
97:
98: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
99: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW, l_errbuf, l_retcode);
100: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
95: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_END);
96: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_RETURN_STATUS || l_return_status);
97:
98: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
99: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW, l_errbuf, l_retcode);
100: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101: End If;
102:
103: COMMIT;
102:
103: COMMIT;
104:
105: -- Explode GROUPS if any inside winners
106: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_START);
107: AS_GAR_LEADS_PUB.EXPLODE_GROUPS_LEADS(
108: x_errbuf => l_errbuf,
109: x_retcode => l_retcode,
110: p_terr_globals => l_terr_globals,
103: COMMIT;
104:
105: -- Explode GROUPS if any inside winners
106: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_START);
107: AS_GAR_LEADS_PUB.EXPLODE_GROUPS_LEADS(
108: x_errbuf => l_errbuf,
109: x_retcode => l_retcode,
110: p_terr_globals => l_terr_globals,
111: x_return_status => l_return_status);
109: x_retcode => l_retcode,
110: p_terr_globals => l_terr_globals,
111: x_return_status => l_return_status);
112:
113: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_END);
114: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_RETURN_STATUS || l_return_status);
115:
116: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
117: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS, l_errbuf, l_retcode);
110: p_terr_globals => l_terr_globals,
111: x_return_status => l_return_status);
112:
113: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_END);
114: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_RETURN_STATUS || l_return_status);
115:
116: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
117: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS, l_errbuf, l_retcode);
118: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
113: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_END);
114: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_RETURN_STATUS || l_return_status);
115:
116: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
117: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS, l_errbuf, l_retcode);
118: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
119: End If;
120:
121: COMMIT;
120:
121: COMMIT;
122:
123: -- Explode TEAMS if any inside winners
124: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
125: AS_GAR_LEADS_PUB.EXPLODE_TEAMS_LEADS(
126: x_errbuf => l_errbuf,
127: x_retcode => l_retcode,
128: p_terr_globals => l_terr_globals,
121: COMMIT;
122:
123: -- Explode TEAMS if any inside winners
124: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
125: AS_GAR_LEADS_PUB.EXPLODE_TEAMS_LEADS(
126: x_errbuf => l_errbuf,
127: x_retcode => l_retcode,
128: p_terr_globals => l_terr_globals,
129: x_return_status => l_return_status);
127: x_retcode => l_retcode,
128: p_terr_globals => l_terr_globals,
129: x_return_status => l_return_status);
130:
131: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_END);
132: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_RETURN_STATUS || l_return_status);
133:
134: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
135: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS, l_errbuf, l_retcode);
128: p_terr_globals => l_terr_globals,
129: x_return_status => l_return_status);
130:
131: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_END);
132: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_RETURN_STATUS || l_return_status);
133:
134: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
135: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS, l_errbuf, l_retcode);
136: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
131: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_END);
132: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_RETURN_STATUS || l_return_status);
133:
134: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
135: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS, l_errbuf, l_retcode);
136: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
137: End If;
138:
139: COMMIT;
138:
139: COMMIT;
140:
141: -- Set team leader for Leads
142: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_START);
143: AS_GAR_LEADS_PUB.SET_TEAM_LEAD_LEADS(
144: x_errbuf => l_errbuf,
145: x_retcode => l_retcode,
146: p_terr_globals => l_terr_globals,
139: COMMIT;
140:
141: -- Set team leader for Leads
142: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_START);
143: AS_GAR_LEADS_PUB.SET_TEAM_LEAD_LEADS(
144: x_errbuf => l_errbuf,
145: x_retcode => l_retcode,
146: p_terr_globals => l_terr_globals,
147: x_return_status => l_return_status);
145: x_retcode => l_retcode,
146: p_terr_globals => l_terr_globals,
147: x_return_status => l_return_status);
148:
149: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_END);
150: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_RETURN_STATUS || l_return_status);
151:
152: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
153: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD, l_errbuf, l_retcode);
146: p_terr_globals => l_terr_globals,
147: x_return_status => l_return_status);
148:
149: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_END);
150: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_RETURN_STATUS || l_return_status);
151:
152: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
153: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD, l_errbuf, l_retcode);
154: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
149: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_END);
150: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_RETURN_STATUS || l_return_status);
151:
152: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
153: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD, l_errbuf, l_retcode);
154: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155: End If;
156:
157: -- Insert into Lead Accesses from Winners
154: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155: End If;
156:
157: -- Insert into Lead Accesses from Winners
158: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
159: AS_GAR_LEADS_PUB.INSERT_ACCESSES_LEADS(
160: x_errbuf => l_errbuf,
161: x_retcode => l_retcode,
162: p_terr_globals => l_terr_globals,
155: End If;
156:
157: -- Insert into Lead Accesses from Winners
158: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
159: AS_GAR_LEADS_PUB.INSERT_ACCESSES_LEADS(
160: x_errbuf => l_errbuf,
161: x_retcode => l_retcode,
162: p_terr_globals => l_terr_globals,
163: x_return_status => l_return_status);
161: x_retcode => l_retcode,
162: p_terr_globals => l_terr_globals,
163: x_return_status => l_return_status);
164:
165: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_END);
166: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_RETURN_STATUS || l_return_status);
167:
168: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
169: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC, l_errbuf, l_retcode);
162: p_terr_globals => l_terr_globals,
163: x_return_status => l_return_status);
164:
165: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_END);
166: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_RETURN_STATUS || l_return_status);
167:
168: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
169: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC, l_errbuf, l_retcode);
170: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
165: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_END);
166: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_RETURN_STATUS || l_return_status);
167:
168: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
169: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC, l_errbuf, l_retcode);
170: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
171: End If;
172:
173: -- Insert into territory Accesses
170: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
171: End If;
172:
173: -- Insert into territory Accesses
174: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
175: AS_GAR_LEADS_PUB.INSERT_TERR_ACCESSES_LEADS(
176: x_errbuf => l_errbuf,
177: x_retcode => l_retcode,
178: p_terr_globals => l_terr_globals,
171: End If;
172:
173: -- Insert into territory Accesses
174: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
175: AS_GAR_LEADS_PUB.INSERT_TERR_ACCESSES_LEADS(
176: x_errbuf => l_errbuf,
177: x_retcode => l_retcode,
178: p_terr_globals => l_terr_globals,
179: x_return_status => l_return_status);
177: x_retcode => l_retcode,
178: p_terr_globals => l_terr_globals,
179: x_return_status => l_return_status);
180:
181: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
182: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_RETURN_STATUS || l_return_status);
183:
184: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
185: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC, l_errbuf, l_retcode);
178: p_terr_globals => l_terr_globals,
179: x_return_status => l_return_status);
180:
181: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
182: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_RETURN_STATUS || l_return_status);
183:
184: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
185: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC, l_errbuf, l_retcode);
186: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
182: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_RETURN_STATUS || l_return_status);
183:
184: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
185: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC, l_errbuf, l_retcode);
186: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
187: End If;
188:
189: -- Remove (soft delete) records in access table that are not qualified
186: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
187: End If;
188:
189: -- Remove (soft delete) records in access table that are not qualified
190: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_START);
191: AS_GAR_LEADS_PUB.PERFORM_LEAD_CLEANUP(
192: x_errbuf => l_errbuf,
193: x_retcode => l_retcode,
194: p_terr_globals => l_terr_globals,
187: End If;
188:
189: -- Remove (soft delete) records in access table that are not qualified
190: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_START);
191: AS_GAR_LEADS_PUB.PERFORM_LEAD_CLEANUP(
192: x_errbuf => l_errbuf,
193: x_retcode => l_retcode,
194: p_terr_globals => l_terr_globals,
195: x_return_status => l_return_status);
193: x_retcode => l_retcode,
194: p_terr_globals => l_terr_globals,
195: x_return_status => l_return_status);
196:
197: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_END);
198: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_RETURN_STATUS || l_return_status);
199:
200: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
201: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC, l_errbuf, l_retcode);
194: p_terr_globals => l_terr_globals,
195: x_return_status => l_return_status);
196:
197: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_END);
198: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_RETURN_STATUS || l_return_status);
199:
200: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
201: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC, l_errbuf, l_retcode);
202: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
197: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_END);
198: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_RETURN_STATUS || l_return_status);
199:
200: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
201: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC, l_errbuf, l_retcode);
202: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
203: End If;
204:
205: -- LEAD Owner assignment
213:
214: l_assign_manual_flag := nvl(FND_PROFILE.Value('AS_LEAD_ASSIGN_MANUAL'),'N');
215: l_resource_id := fnd_profile.value('AS_DEFAULT_RESOURCE_ID');
216:
217: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_START);
218: IF l_assign_manual_flag = 'N' THEN
219: AS_GAR_LEADS_PUB.ASSIGN_LEAD_OWNER(
220: x_errbuf => l_errbuf,
221: x_retcode => l_retcode,
215: l_resource_id := fnd_profile.value('AS_DEFAULT_RESOURCE_ID');
216:
217: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_START);
218: IF l_assign_manual_flag = 'N' THEN
219: AS_GAR_LEADS_PUB.ASSIGN_LEAD_OWNER(
220: x_errbuf => l_errbuf,
221: x_retcode => l_retcode,
222: p_terr_globals => l_terr_globals,
223: x_return_status => l_return_status);
221: x_retcode => l_retcode,
222: p_terr_globals => l_terr_globals,
223: x_return_status => l_return_status);
224: IF l_resource_id IS NOT NULL THEN
225: AS_GAR_LEADS_PUB.ASSIGN_DEFAULT_LEAD_OWNER(
226: x_errbuf => l_errbuf,
227: x_retcode => l_retcode,
228: p_terr_globals => l_terr_globals,
229: x_return_status => l_return_status);
229: x_return_status => l_return_status);
230: END IF;
231: ELSE
232: IF l_resource_id IS NOT NULL THEN
233: AS_GAR_LEADS_PUB.UNCHECK_LEAD_OWNER(
234: x_errbuf => l_errbuf,
235: x_retcode => l_retcode,
236: p_terr_globals => l_terr_globals,
237: x_return_status => l_return_status);
234: x_errbuf => l_errbuf,
235: x_retcode => l_retcode,
236: p_terr_globals => l_terr_globals,
237: x_return_status => l_return_status);
238: AS_GAR_LEADS_PUB.ASSIGN_DEFAULT_LEAD_OWNER(
239: x_errbuf => l_errbuf,
240: x_retcode => l_retcode,
241: p_terr_globals => l_terr_globals,
242: x_return_status => l_return_status);
240: x_retcode => l_retcode,
241: p_terr_globals => l_terr_globals,
242: x_return_status => l_return_status);
243: ELSE
244: AS_GAR_LEADS_PUB.UNCHECK_ASSIGN_SALESFORCE(
245: x_errbuf => l_errbuf,
246: x_retcode => l_retcode,
247: p_terr_globals => l_terr_globals,
248: x_return_status => l_return_status);
247: p_terr_globals => l_terr_globals,
248: x_return_status => l_return_status);
249: END IF;
250: END IF;
251: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_END);
252: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_RETURN_STATUS || l_return_status);
253:
254: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
255: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO, l_errbuf, l_retcode);
248: x_return_status => l_return_status);
249: END IF;
250: END IF;
251: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_END);
252: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_RETURN_STATUS || l_return_status);
253:
254: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
255: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO, l_errbuf, l_retcode);
256: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
251: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_END);
252: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_RETURN_STATUS || l_return_status);
253:
254: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
255: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO, l_errbuf, l_retcode);
256: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257: End If;
258:
259: -- BES enhancement
257: End If;
258:
259: -- BES enhancement
260:
261: l_sub_exist := AS_GAR.exist_subscription(G_BUSINESS_EVENT);
262: IF l_sub_exist = 'Y' THEN
263: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_EXISTS);
264: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_RAISE);
265: AS_GAR.Raise_BE(l_terr_globals);
259: -- BES enhancement
260:
261: l_sub_exist := AS_GAR.exist_subscription(G_BUSINESS_EVENT);
262: IF l_sub_exist = 'Y' THEN
263: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_EXISTS);
264: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_RAISE);
265: AS_GAR.Raise_BE(l_terr_globals);
266: END If;
267:
260:
261: l_sub_exist := AS_GAR.exist_subscription(G_BUSINESS_EVENT);
262: IF l_sub_exist = 'Y' THEN
263: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_EXISTS);
264: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_RAISE);
265: AS_GAR.Raise_BE(l_terr_globals);
266: END If;
267:
268: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_END);
261: l_sub_exist := AS_GAR.exist_subscription(G_BUSINESS_EVENT);
262: IF l_sub_exist = 'Y' THEN
263: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_EXISTS);
264: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_RAISE);
265: AS_GAR.Raise_BE(l_terr_globals);
266: END If;
267:
268: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_END);
269: EXCEPTION
264: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_RAISE);
265: AS_GAR.Raise_BE(l_terr_globals);
266: END If;
267:
268: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_END);
269: EXCEPTION
270: WHEN OTHERS THEN
271: AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
272: l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
267:
268: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_END);
269: EXCEPTION
270: WHEN OTHERS THEN
271: AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
272: l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
273: END GAR_WRAPPER;
274:
275: /************************** End GAR Wrapper *****************************/
277: /************************** Start Explode Teams Leads ******************/
278: PROCEDURE EXPLODE_TEAMS_LEADS(
279: x_errbuf OUT NOCOPY VARCHAR2,
280: x_retcode OUT NOCOPY VARCHAR2,
281: p_terr_globals IN AS_GAR.TERR_GLOBALS,
282: x_return_status OUT NOCOPY VARCHAR2)
283: IS
284:
285: /*-------------------------------------------------------------------------+
325: AND rules.source_id = -1001
326: AND rsc.terr_rsc_id = acc.terr_rsc_id;
327:
328: BEGIN
329: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
330: x_return_status := FND_API.G_RET_STS_SUCCESS;
331: l_request_id := p_terr_globals.request_id;
332: l_worker_id := p_terr_globals.worker_id;
333: l_resource_type := 'RS_TEAM';
342: FETCH c_get_res_type_count INTO l_res_type_count;
343: CLOSE c_get_res_type_count;
344: END IF;
345:
346: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
347: IF l_res_type_count > 0 THEN
348: /* Get resources within a resource team */
349: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
350: /** Note
345:
346: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
347: IF l_res_type_count > 0 THEN
348: /* Get resources within a resource team */
349: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
350: /** Note
351: Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
352: because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
353: **/
529: AND rt1.worker_id = t.worker_id
530: AND rt1.trans_object_id = t.trans_object_id
531: AND NVL(rt1.trans_detail_object_id,-1) = NVL(t.trans_detail_object_id,-1));
532:
533: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
534: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
535:
536:
537: COMMIT;
530: AND rt1.trans_object_id = t.trans_object_id
531: AND NVL(rt1.trans_detail_object_id,-1) = NVL(t.trans_detail_object_id,-1));
532:
533: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
534: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
535:
536:
537: COMMIT;
538:
538:
539: END IF; /* if l_res_type_count > 0 */
540: EXCEPTION
541: WHEN others THEN
542: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS, SQLERRM, TO_CHAR(SQLCODE));
543: x_errbuf := SQLERRM;
544: x_retcode := SQLCODE;
545: x_return_status := FND_API.G_RET_STS_ERROR;
546: RAISE;
550: /************************** Start Explode Groups Leads ******************/
551: PROCEDURE EXPLODE_GROUPS_LEADS(
552: x_errbuf OUT NOCOPY VARCHAR2,
553: x_retcode OUT NOCOPY VARCHAR2,
554: p_terr_globals IN AS_GAR.TERR_GLOBALS,
555: x_return_status OUT NOCOPY VARCHAR2)
556: IS
557: -------------RS_GROUP---------
558: /*-------------------------------------------------------------------------+
606: FETCH c_get_res_type_count INTO l_res_type_count;
607: CLOSE c_get_res_type_count;
608: END IF;
609:
610: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
611: IF l_res_type_count > 0 THEN
612: /* Get resources within a resource group */
613: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
614: /** Note
609:
610: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
611: IF l_res_type_count > 0 THEN
612: /* Get resources within a resource group */
613: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
614: /** Note
615: Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
616: because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
617: **/
711: AND rt1.worker_id = t.worker_id
712: AND rt1.trans_object_id = t.trans_object_id
713: AND NVL(rt1.trans_detail_object_id,-1) = NVL(t.trans_detail_object_id,-1));
714:
715: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
716: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
717:
718: COMMIT;
719: END IF; /* if l_res_type_count > 0 */
712: AND rt1.trans_object_id = t.trans_object_id
713: AND NVL(rt1.trans_detail_object_id,-1) = NVL(t.trans_detail_object_id,-1));
714:
715: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
716: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
717:
718: COMMIT;
719: END IF; /* if l_res_type_count > 0 */
720:
719: END IF; /* if l_res_type_count > 0 */
720:
721: EXCEPTION
722: WHEN OTHERS THEN
723: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS, SQLERRM, TO_CHAR(SQLCODE));
724: x_errbuf := SQLERRM;
725: x_retcode := SQLCODE;
726: x_return_status := FND_API.G_RET_STS_ERROR;
727: RAISE;
748:
749: PROCEDURE SET_TEAM_LEAD_LEADS(
750: x_errbuf OUT NOCOPY VARCHAR2,
751: x_retcode OUT NOCOPY VARCHAR2,
752: p_terr_globals IN AS_GAR.TERR_GLOBALS,
753: x_return_status OUT NOCOPY VARCHAR2)
754: IS
755:
756: TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
793: WIN.full_access_flag;
794:
795:
796: BEGIN
797: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_START);
798: x_return_status := FND_API.G_RET_STS_SUCCESS;
799: l_worker_id:=p_terr_globals.worker_id;
800: l_var :=p_terr_globals.bulk_size;
801: l_max_fetches := p_terr_globals.cursor_limit;
806: l_salesforce_id.DELETE;
807: l_sales_group_id.DELETE;
808: l_faf.DELETE;
809: l_loop_count := l_loop_count + 1;
810: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'LOOPCOUNT :- ' || l_loop_count);
811:
812: --------------------------------
813: OPEN team_leader(l_worker_id);
814: FETCH team_leader BULK COLLECT INTO
824:
825: IF l_sales_lead_id.COUNT < l_max_fetches THEN
826: l_limit_flag := TRUE;
827: END IF;
828: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD || AS_GAR.G_START);
829:
830: IF l_sales_lead_id.COUNT > 0 THEN
831: l_flag := TRUE;
832: l_first := l_sales_lead_id.FIRST;
854: AND ACC.salesforce_id = l_salesforce_id(i)
855: AND ACC.sales_group_id = l_sales_group_id(i);
856: COMMIT;
857: l_attempts := 3;
858: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS ||AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-'|| l_last);
859: EXCEPTION
860: WHEN DEADLOCK_DETECTED THEN
861: BEGIN
862: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_DEADLOCK ||l_attempts);
858: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS ||AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-'|| l_last);
859: EXCEPTION
860: WHEN DEADLOCK_DETECTED THEN
861: BEGIN
862: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_DEADLOCK ||l_attempts);
863: ROLLBACK;
864: l_attempts := l_attempts +1;
865: IF l_attempts = 3 THEN
866: FOR i IN l_first .. l_last
882: AND ACC.salesforce_id = l_salesforce_id(i)
883: AND ACC.sales_group_id = l_sales_group_id(i);
884: EXCEPTION
885: WHEN OTHERS THEN
886: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_IND_UPD || AS_GAR.G_GENERAL_EXCEPTION);
887: AS_GAR.LOG('SALES_LEAD_ID/SALESFORCE_ID/SALESGROUP_ID/ORG_ID - ' || l_sales_lead_id(i) || '/' || l_salesforce_id(i) || '/' || l_sales_group_id(i) );
888: END;
889: END LOOP; -- for each record individually
890: COMMIT;
883: AND ACC.sales_group_id = l_sales_group_id(i);
884: EXCEPTION
885: WHEN OTHERS THEN
886: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_IND_UPD || AS_GAR.G_GENERAL_EXCEPTION);
887: AS_GAR.LOG('SALES_LEAD_ID/SALESFORCE_ID/SALESGROUP_ID/ORG_ID - ' || l_sales_lead_id(i) || '/' || l_salesforce_id(i) || '/' || l_sales_group_id(i) );
888: END;
889: END LOOP; -- for each record individually
890: COMMIT;
891: END IF;
890: COMMIT;
891: END IF;
892: END; -- end of deadlock exception
893: WHEN OTHERS THEN
894: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD, SQLERRM, TO_CHAR(SQLCODE));
895: x_errbuf := SQLERRM;
896: x_retcode := SQLCODE;
897: x_return_status := FND_API.G_RET_STS_ERROR;
898: RAISE;
905: l_flag := FALSE;
906: END IF;
907: END LOOP; -- loop for more records within the bulk_size
908: END IF; --l_sales_lead_id.count > 0
909: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_END);
910: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_N_ROWS_PROCESSED || l_sales_lead_id.COUNT);
911: END LOOP; -- loop for more bulk_size fetches
912: l_sales_lead_id.DELETE;
913: l_faf.DELETE;
906: END IF;
907: END LOOP; -- loop for more records within the bulk_size
908: END IF; --l_sales_lead_id.count > 0
909: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_END);
910: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_N_ROWS_PROCESSED || l_sales_lead_id.COUNT);
911: END LOOP; -- loop for more bulk_size fetches
912: l_sales_lead_id.DELETE;
913: l_faf.DELETE;
914: l_salesforce_id.DELETE;
914: l_salesforce_id.DELETE;
915: l_sales_group_id.DELETE;
916: EXCEPTION
917: WHEN OTHERS THEN
918: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD, SQLERRM, TO_CHAR(SQLCODE));
919: x_errbuf := SQLERRM;
920: x_retcode := SQLCODE;
921: x_return_status := FND_API.G_RET_STS_ERROR;
922: RAISE;
928:
929: PROCEDURE INSERT_ACCESSES_LEADS(
930: x_errbuf OUT NOCOPY VARCHAR2,
931: x_retcode OUT NOCOPY VARCHAR2,
932: p_terr_globals IN AS_GAR.TERR_GLOBALS,
933: x_return_status OUT NOCOPY VARCHAR2)
934: IS
935: TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
936: TYPE faf_list is TABLE of VARCHAR2(1) INDEX BY BINARY_INTEGER;
980: | Re-Initialize variables and null out if necessary.
981: | Try bulk inserting into accesses. If this fails, insert records one by one.
982: |
983: +-------------------------------------------------------------------------*/
984: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_START);
985: x_return_status := FND_API.G_RET_STS_SUCCESS;
986: l_worker_id:=p_terr_globals.worker_id;
987: l_var :=p_terr_globals.bulk_size;
988: OPEN ins_acc(l_worker_id);
998:
999: EXIT WHEN ins_acc%NOTFOUND;
1000:
1001: l_loop_count := l_loop_count + 1;
1002: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'LOOPCOUNT :- ' || l_loop_count);
1003:
1004: FETCH ins_acc BULK COLLECT INTO
1005: l_salesforce_id, l_sales_group_id, l_person_id,
1006: l_sales_lead_id,l_faf,l_org_id
1023: IF l_last > l_sales_lead_id.LAST THEN
1024: l_last := l_sales_lead_id.LAST;
1025: END IF;
1026: BEGIN
1027: 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);
1028: FORALL i IN l_first .. l_last
1029: INSERT INTO AS_ACCESSES_ALL_ALL
1030: ( access_id
1031: ,access_type
1093: AND AA.salesforce_id = l_salesforce_id(i)
1094: AND AA.sales_group_id = l_sales_group_id(i)
1095: )
1096: );
1097: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1098: COMMIT;
1099: EXCEPTION
1100: WHEN DUP_VAL_ON_INDEX THEN
1101: 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);
1097: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1098: COMMIT;
1099: EXCEPTION
1100: WHEN DUP_VAL_ON_INDEX THEN
1101: 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);
1102: FOR i IN l_first .. l_last LOOP
1103: BEGIN
1104: INSERT INTO AS_ACCESSES_ALL_ALL
1105: ( access_id
1175: END;
1176: END LOOP; /* loop for DUP_VAL_ON_INDEX individual insert */
1177: COMMIT;
1178: WHEN OTHERS THEN
1179: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1180: x_errbuf := SQLERRM;
1181: x_retcode := SQLCODE;
1182: x_return_status := FND_API.G_RET_STS_ERROR;
1183: RAISE;
1199: IF ins_acc%ISOPEN THEN CLOSE ins_acc; END IF;
1200:
1201: EXCEPTION
1202: WHEN others THEN
1203: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC, SQLERRM, TO_CHAR(SQLCODE));
1204: x_errbuf := SQLERRM;
1205: x_retcode := SQLCODE;
1206: x_return_status := FND_API.G_RET_STS_ERROR;
1207: IF ins_acc%ISOPEN THEN CLOSE ins_acc; END IF;
1214:
1215: PROCEDURE INSERT_TERR_ACCESSES_LEADS(
1216: x_errbuf OUT NOCOPY VARCHAR2,
1217: x_retcode OUT NOCOPY VARCHAR2,
1218: p_terr_globals IN AS_GAR.TERR_GLOBALS,
1219: x_return_status OUT NOCOPY VARCHAR2)
1220: IS
1221: TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
1222:
1258: | Almost the same as accesses, except the insertion is into as_territory_accesses
1259: | and there is no involvement of role.
1260: |
1261: +-------------------------------------------------------------------------*/
1262: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
1263: x_return_status := FND_API.G_RET_STS_SUCCESS;
1264: l_worker_id := p_terr_globals.worker_id;
1265: l_var := p_terr_globals.bulk_size;
1266: OPEN ins_tacc(l_worker_id);
1271: l_sales_lead_id.DELETE;
1272: l_salesforce_id.DELETE;
1273: l_sales_group_id.DELETE;
1274: l_terr_id.DELETE;
1275: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'LOOPCOUNT :- ' || l_loop_count);
1276: BEGIN
1277:
1278: FETCH ins_tacc BULK COLLECT INTO l_terr_id,
1279: l_sales_lead_id, l_salesforce_id, l_sales_group_id
1292: IF l_last > l_sales_lead_id.last THEN
1293: l_last := l_sales_lead_id.last;
1294: END IF;
1295: BEGIN
1296: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1297: FORALL i IN l_first .. l_last
1298: INSERT INTO AS_TERRITORY_ACCESSES
1299: ( access_id,
1300: territory_id,
1338: WHERE AST.access_id = A.access_id
1339: AND AST.territory_id = l_terr_id(i))
1340: ) V
1341: );
1342: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1343: COMMIT;
1344: EXCEPTION
1345: WHEN DUP_VAL_ON_INDEX THEN
1346: 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);
1342: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1343: COMMIT;
1344: EXCEPTION
1345: WHEN DUP_VAL_ON_INDEX THEN
1346: 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);
1347: FOR i IN l_first .. l_last LOOP
1348: BEGIN
1349: INSERT INTO AS_TERRITORY_ACCESSES
1350: ( access_id,
1394: WHEN Others THEN
1395: NULL;
1396: END;
1397: END LOOP;
1398: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1399: COMMIT;
1400: WHEN Others THEN
1401: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1402: x_errbuf := SQLERRM;
1397: END LOOP;
1398: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1399: COMMIT;
1400: WHEN Others THEN
1401: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1402: x_errbuf := SQLERRM;
1403: x_retcode := SQLCODE;
1404: x_return_status := FND_API.G_RET_STS_ERROR;
1405: IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1413: END LOOP;
1414: END IF; --l_sales_lead_id.count > 0
1415: EXCEPTION
1416: WHEN Others THEN
1417: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC , SQLERRM, TO_CHAR(SQLCODE));
1418: IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1419: x_errbuf := SQLERRM;
1420: x_retcode := SQLCODE;
1421: x_return_status := FND_API.G_RET_STS_ERROR;
1426: l_salesforce_id.DELETE;
1427: l_sales_group_id.DELETE;
1428: l_terr_id.DELETE;
1429: IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1430: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
1431: EXCEPTION
1432: WHEN others THEN
1433: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC, SQLERRM, TO_CHAR(SQLCODE));
1434: x_errbuf := SQLERRM;
1429: IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1430: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
1431: EXCEPTION
1432: WHEN others THEN
1433: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC, SQLERRM, TO_CHAR(SQLCODE));
1434: x_errbuf := SQLERRM;
1435: x_retcode := SQLCODE;
1436: x_return_status := FND_API.G_RET_STS_ERROR;
1437: IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1445:
1446: PROCEDURE Perform_Lead_Cleanup(
1447: x_errbuf OUT NOCOPY VARCHAR2,
1448: x_retcode OUT NOCOPY VARCHAR2,
1449: p_terr_globals IN AS_GAR.TERR_GLOBALS,
1450: x_return_status OUT NOCOPY VARCHAR2)
1451: IS
1452:
1453: TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
1482: FROM JTF_TAE_1001_LEAD_NM_TRANS
1483: WHERE worker_id=c_worker_id;
1484:
1485: BEGIN
1486: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_START);
1487: x_return_status := FND_API.G_RET_STS_SUCCESS;
1488: l_worker_id := p_terr_globals.worker_id;
1489: l_var := p_terr_globals.bulk_size;
1490: l_max_fetches := p_terr_globals.cursor_limit;
1488: l_worker_id := p_terr_globals.worker_id;
1489: l_var := p_terr_globals.bulk_size;
1490: l_max_fetches := p_terr_globals.cursor_limit;
1491:
1492: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1493: OPEN del_lead_totalmode(l_worker_id);
1494: ELSE
1495: OPEN del_lead_newmode(l_worker_id);
1496: END IF;
1497: LOOP --{L1
1498: IF (l_limit_flag) THEN EXIT; END IF;
1499:
1500: l_loop_count := l_loop_count + 1;
1501: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || 'LOOPCOUNT :- ' ||l_loop_count);
1502: BEGIN
1503: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1504: EXIT WHEN del_lead_totalmode%NOTFOUND;
1505: FETCH del_lead_totalmode BULK COLLECT INTO l_sales_lead_id
1499:
1500: l_loop_count := l_loop_count + 1;
1501: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || 'LOOPCOUNT :- ' ||l_loop_count);
1502: BEGIN
1503: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1504: EXIT WHEN del_lead_totalmode%NOTFOUND;
1505: FETCH del_lead_totalmode BULK COLLECT INTO l_sales_lead_id
1506: LIMIT l_max_fetches;
1507: ELSE
1518: IF l_sales_lead_id.COUNT < l_max_fetches THEN
1519: l_limit_flag := TRUE;
1520: END IF;
1521:
1522: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_START);
1523: IF l_sales_lead_id.count > 0 THEN --{I1
1524: l_flag := TRUE;
1525: l_first := l_sales_lead_id.first;
1526: l_last := l_first + l_var;
1523: IF l_sales_lead_id.count > 0 THEN --{I1
1524: l_flag := TRUE;
1525: l_first := l_sales_lead_id.first;
1526: l_last := l_first + l_var;
1527: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_N_ROWS_PROCESSED ||
1528: l_sales_lead_id.FIRST || '-' ||
1529: l_sales_lead_id.LAST);
1530: WHILE l_flag LOOP --{L2 10K cust loop
1531: IF l_last > l_sales_lead_id.LAST THEN
1537: IF (l_del_flag) THEN EXIT; END IF;
1538: l_del_flag := FALSE;
1539: WHILE l_attempts < 3 LOOP --{L4
1540: BEGIN
1541: 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);
1542: FORALL i in l_first..l_last
1543: UPDATE AS_ACCESSES_ALL_ALL ACC
1544: SET object_version_number = NVL(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
1545: WHERE ACC.sales_lead_id=l_sales_lead_id(i)
1559: IF l_access_id.COUNT < G_NUM_REC THEN l_del_flag := TRUE; END IF;
1560: EXCEPTION
1561: WHEN DUP_VAL_ON_INDEX THEN
1562: BEGIN
1563: 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);
1564: FORALL i in l_first..l_last
1565: DELETE FROM AS_ACCESSES_ALL_ALL ACC
1566: WHERE ACC.sales_lead_id=l_sales_lead_id(i)
1567: AND ACC.freeze_flag = 'N'
1578: l_attempts := 3;
1579: IF l_access_id.COUNT < G_NUM_REC THEN l_del_flag := TRUE; END IF;
1580: EXCEPTION
1581: WHEN OTHERS THEN
1582: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_DEL, SQLERRM, TO_CHAR(SQLCODE));
1583: END;
1584: WHEN deadlock_detected THEN
1585: BEGIN --{I2
1586: 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_DEADLOCK || l_attempts);
1582: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_DEL, SQLERRM, TO_CHAR(SQLCODE));
1583: END;
1584: WHEN deadlock_detected THEN
1585: BEGIN --{I2
1586: 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_DEADLOCK || l_attempts);
1587: ROLLBACK;
1588: l_attempts := l_attempts +1;
1589: IF l_attempts = 3 THEN
1590: FOR i IN l_first .. l_last LOOP --{L5
1588: l_attempts := l_attempts +1;
1589: IF l_attempts = 3 THEN
1590: FOR i IN l_first .. l_last LOOP --{L5
1591: BEGIN
1592: 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);
1593: UPDATE AS_ACCESSES_ALL_ALL ACC
1594: SET object_version_number = NVL(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
1595: WHERE ACC.sales_lead_id = l_sales_lead_id(i)
1596: AND ACC.freeze_flag = 'N'
1606: COMMIT;
1607: EXCEPTION
1608: WHEN DUP_VAL_ON_INDEX THEN
1609: BEGIN
1610: 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);
1611: DELETE FROM AS_ACCESSES_ALL_ALL ACC
1612: WHERE ACC.sales_lead_id=l_sales_lead_id(i)
1613: AND ACC.freeze_flag = 'N'
1614: AND ACC.lead_id IS NULL
1620: AND W.worker_id = l_worker_id
1621: AND W.group_id = ACC.sales_group_id);
1622: EXCEPTION
1623: WHEN OTHERS THEN
1624: 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_GENERAL_EXCEPTION);
1625: AS_GAR.LOG('SALES_LEAD_ID - ' || l_sales_lead_id(i));
1626: END;
1627: END;
1628: END LOOP; --}L5
1621: AND W.group_id = ACC.sales_group_id);
1622: EXCEPTION
1623: WHEN OTHERS THEN
1624: 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_GENERAL_EXCEPTION);
1625: AS_GAR.LOG('SALES_LEAD_ID - ' || l_sales_lead_id(i));
1626: END;
1627: END;
1628: END LOOP; --}L5
1629: COMMIT;
1630: l_del_flag := TRUE;
1631: END IF;
1632: END; --}I2 end of deadlock exception
1633: WHEN OTHERS THEN
1634: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1635: IF del_lead_totalmode%ISOPEN THEN CLOSE del_lead_totalmode; END IF;
1636: IF del_lead_newmode%ISOPEN THEN CLOSE del_lead_newmode; END IF;
1637: x_errbuf := SQLERRM;
1638: x_retcode := SQLCODE;
1639: x_return_status := FND_API.G_RET_STS_ERROR;
1640: RAISE;
1641: END;
1642: END LOOP; --}L4 l_attempts loop 3 trys
1643: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-' || l_last);
1644: END LOOP; --}L3 -- to update only 10k record at a time on accesses
1645: l_first := l_last + 1;
1646: l_last := l_first + l_var;
1647: IF l_first > l_sales_lead_id.LAST THEN
1648: l_flag := FALSE;
1649: END IF;
1650: END LOOP; --}L2 while l_flag loop (10K cust loop)
1651: END IF;--}I1
1652: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_END);
1653: COMMIT;
1654: EXCEPTION
1655: WHEN Others THEN
1656: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1652: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_END);
1653: COMMIT;
1654: EXCEPTION
1655: WHEN Others THEN
1656: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1657: IF del_lead_totalmode%ISOPEN THEN CLOSE del_lead_totalmode; END IF;
1658: IF del_lead_newmode%ISOPEN THEN CLOSE del_lead_newmode; END IF;
1659: x_errbuf := SQLERRM;
1660: x_retcode := SQLCODE;
1665: IF del_lead_totalmode%ISOPEN THEN CLOSE del_lead_totalmode; END IF;
1666: IF del_lead_newmode%ISOPEN THEN CLOSE del_lead_newmode; END IF;
1667: EXCEPTION
1668: WHEN OTHERS THEN
1669: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1670: x_errbuf := SQLERRM;
1671: x_retcode := SQLCODE;
1672: x_return_status := FND_API.G_RET_STS_ERROR;
1673: RAISE;
1677: /**************************** Start Assign Lead Owner ********************/
1678: PROCEDURE ASSIGN_LEAD_OWNER(
1679: x_errbuf OUT NOCOPY VARCHAR2,
1680: x_retcode OUT NOCOPY VARCHAR2,
1681: p_terr_globals IN AS_GAR.TERR_GLOBALS,
1682: x_return_status OUT NOCOPY VARCHAR2)
1683: IS
1684: l_return_status VARCHAR2(1);
1685: l_msg_count NUMBER;
1759:
1760:
1761:
1762: BEGIN
1763: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_START);
1764:
1765: x_return_status := FND_API.G_RET_STS_SUCCESS;
1766: l_worker_id:=p_terr_globals.worker_id;
1767: l_var :=p_terr_globals.bulk_size;
1767: l_var :=p_terr_globals.bulk_size;
1768: l_max_fetches := p_terr_globals.cursor_limit;
1769: IF fnd_profile.value('AS_LEAD_ASSIGNMENT_UHK') = 'Y'
1770: THEN
1771: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'CUSTOM AS PER PROFILE---AS_LEAD_ASSIGNMENT_UHK:- Y---');
1772: AS_CUSTOM_HOOKS_UHK.Lead_TOTTAP_Owner_Assignment(
1773: p_request_id => p_terr_globals.request_id,
1774: p_worker_id => p_terr_globals.worker_id,
1775: x_return_status => l_return_status,
1778: ELSE
1779: LOOP
1780: IF (l_limit_flag) THEN EXIT; END IF;
1781: l_loop_count := l_loop_count + 1;
1782: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
1783: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1784: OPEN lead_owner_totalmode(l_worker_id);
1785: FETCH lead_owner_totalmode BULK COLLECT INTO l_sales_lead_id,l_access_id LIMIT l_max_fetches;
1786: CLOSE lead_owner_totalmode;
1779: LOOP
1780: IF (l_limit_flag) THEN EXIT; END IF;
1781: l_loop_count := l_loop_count + 1;
1782: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
1783: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1784: OPEN lead_owner_totalmode(l_worker_id);
1785: FETCH lead_owner_totalmode BULK COLLECT INTO l_sales_lead_id,l_access_id LIMIT l_max_fetches;
1786: CLOSE lead_owner_totalmode;
1787: ELSE
1806: IF l_last > l_sales_lead_id.LAST THEN
1807: l_last := l_sales_lead_id.LAST;
1808: END IF;
1809: WHILE l_attempts < 3 LOOP
1810: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS');
1811: BEGIN
1812: FORALL i in l_first .. l_last
1813: UPDATE AS_SALES_LEADS sl
1814: SET sl.last_update_date = SYSDATE,
1828: )
1829: WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
1830: COMMIT;
1831: l_attempts := 3;
1832: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
1833: EXCEPTION
1834: WHEN deadlock_detected THEN
1835: BEGIN
1836: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
1832: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
1833: EXCEPTION
1834: WHEN deadlock_detected THEN
1835: BEGIN
1836: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
1837: ROLLBACK;
1838: l_attempts := l_attempts +1;
1839: IF l_attempts = 3 THEN
1840: FOR i IN l_first .. l_last LOOP
1857: )
1858: WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
1859: EXCEPTION
1860: WHEN OTHERS THEN
1861: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE OF SALES LEADS', SQLERRM, TO_CHAR(SQLCODE));
1862: END;
1863: END LOOP;
1864: COMMIT;
1865: END IF;
1864: COMMIT;
1865: END IF;
1866: END; -- end of deadlock exception
1867: WHEN OTHERS THEN
1868: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS', SQLERRM, TO_CHAR(SQLCODE));
1869: x_errbuf := SQLERRM;
1870: x_retcode := SQLCODE;
1871: x_return_status := FND_API.G_RET_STS_ERROR;
1872: RAISE;
1872: RAISE;
1873: END;
1874: END LOOP;
1875:
1876: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES');
1877: l_attempts := 1;
1878: WHILE l_attempts < 3 LOOP
1879: BEGIN
1880: FORALL i in l_first .. l_last
1889: ACC.owner_flag = 'Y'
1890: WHERE ACC.access_id = l_access_id(i);
1891: COMMIT;
1892: l_attempts := 3;
1893: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES::' || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
1894: EXCEPTION
1895: WHEN deadlock_detected THEN
1896: BEGIN
1897: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES::' || AS_GAR.G_DEADLOCK ||l_attempts );
1893: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES::' || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
1894: EXCEPTION
1895: WHEN deadlock_detected THEN
1896: BEGIN
1897: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES::' || AS_GAR.G_DEADLOCK ||l_attempts );
1898: l_attempts := l_attempts +1;
1899: ROLLBACK;
1900: IF l_attempts = 3 THEN
1901: FOR i IN l_first .. l_last LOOP
1911: ACC.owner_flag = 'Y'
1912: WHERE ACC.access_id = l_access_id(i);
1913: EXCEPTION
1914: WHEN OTHERS THEN
1915: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE OF SALES LEADS ACCESSES', SQLERRM, TO_CHAR(SQLCODE));
1916: END;
1917: END LOOP;
1918: COMMIT;
1919: END IF;
1918: COMMIT;
1919: END IF;
1920: END; -- end of deadlock exception
1921: WHEN OTHERS THEN
1922: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE SALES_LEADS ACCESSES', SQLERRM, TO_CHAR(SQLCODE));
1923: x_errbuf := SQLERRM;
1924: x_retcode := SQLCODE;
1925: x_return_status := FND_API.G_RET_STS_ERROR;
1926: RAISE;
1932: l_flag := FALSE;
1933: END IF;
1934: END LOOP;
1935: END IF; --l_sales_lead_id.count > 0
1936: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_sales_lead_id.COUNT);
1937: END LOOP;
1938: l_limit_flag := FALSE;
1939: l_loop_count := 0;
1940: l_access_id.delete;
1943: END IF; -- (Custom or Non Custom)
1944:
1945: EXCEPTION
1946: WHEN OTHERS THEN
1947: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1948: x_errbuf := SQLERRM;
1949: x_retcode := SQLCODE;
1950: x_return_status := FND_API.G_RET_STS_ERROR;
1951: RAISE;
1955: /**************************** Start Assign_Default_Lead_Owner ********************/
1956: PROCEDURE ASSIGN_DEFAULT_LEAD_OWNER(
1957: x_errbuf OUT NOCOPY VARCHAR2,
1958: x_retcode OUT NOCOPY VARCHAR2,
1959: p_terr_globals IN AS_GAR.TERR_GLOBALS,
1960: x_return_status OUT NOCOPY VARCHAR2)
1961: IS
1962: l_return_status VARCHAR2(1);
1963: l_msg_count NUMBER;
2065: l_group_id NUMBER;
2066: l_person_id NUMBER;
2067:
2068: BEGIN
2069: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_START);
2070:
2071: x_return_status := FND_API.G_RET_STS_SUCCESS;
2072: l_worker_id:=p_terr_globals.worker_id;
2073: l_var :=p_terr_globals.bulk_size;
2076: l_assign_manual_flag := nvl(FND_PROFILE.Value('AS_LEAD_ASSIGN_MANUAL'),'N');
2077:
2078: IF fnd_profile.value('AS_LEAD_ASSIGNMENT_UHK') = 'Y'
2079: THEN
2080: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'CUSTOM AS PER PROFILE---AS_LEAD_ASSIGNMENT_UHK:- Y---');
2081: AS_CUSTOM_HOOKS_UHK.Lead_TOTTAP_Owner_Assignment(
2082: p_request_id => p_terr_globals.request_id,
2083: p_worker_id => p_terr_globals.worker_id,
2084: x_return_status => l_return_status,
2096: CLOSE c_get_person_id;
2097: LOOP
2098: IF (l_limit_flag) THEN EXIT; END IF;
2099: l_loop_count := l_loop_count + 1;
2100: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
2101:
2102: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
2103: IF l_assign_manual_flag = 'N' THEN
2104: OPEN tot_lead_owner_tap(l_worker_id);
2098: IF (l_limit_flag) THEN EXIT; END IF;
2099: l_loop_count := l_loop_count + 1;
2100: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
2101:
2102: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
2103: IF l_assign_manual_flag = 'N' THEN
2104: OPEN tot_lead_owner_tap(l_worker_id);
2105: FETCH tot_lead_owner_tap BULK COLLECT INTO l_sales_lead_id LIMIT l_max_fetches;
2106: CLOSE tot_lead_owner_tap;
2139: l_last := l_sales_lead_id.LAST;
2140: END IF;
2141:
2142: WHILE l_attempts < 3 LOOP
2143: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'INSERT INTO AS_ACCESSES_ALL_ALL');
2144: BEGIN
2145: FORALL i in l_first .. l_last
2146: INSERT INTO AS_ACCESSES_ALL_ALL
2147: (ACCESS_ID,
2208: );
2209:
2210: COMMIT;
2211: l_attempts := 3;
2212: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2213: EXCEPTION
2214: WHEN deadlock_detected THEN
2215: BEGIN
2216: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
2212: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2213: EXCEPTION
2214: WHEN deadlock_detected THEN
2215: BEGIN
2216: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
2217: ROLLBACK;
2218: l_attempts := l_attempts +1;
2219: IF l_attempts = 3 THEN
2220: FOR i IN l_first .. l_last LOOP
2283: )
2284: );
2285: EXCEPTION
2286: WHEN OTHERS THEN
2287: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW INSERT INTO AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2288: END;
2289: END LOOP;
2290: COMMIT;
2291: END IF;
2290: COMMIT;
2291: END IF;
2292: END; -- end of deadlock exception
2293: WHEN OTHERS THEN
2294: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'INSERT INTO AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2295: x_errbuf := SQLERRM;
2296: x_retcode := SQLCODE;
2297: x_return_status := FND_API.G_RET_STS_ERROR;
2298: RAISE;
2299: END;
2300: END LOOP;
2301:
2302: WHILE l_attempts < 3 LOOP
2303: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL');
2304: BEGIN
2305: FORALL i in l_first .. l_last
2306: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,
2307: ACC.LAST_UPDATE_DATE = SYSDATE,
2319: and ACC.salesforce_id = l_resource_id
2320: and nvl(ACC.sales_group_id,-777) = nvl(l_group_id,-777);
2321: COMMIT;
2322: l_attempts := 3;
2323: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2324: EXCEPTION
2325: WHEN deadlock_detected THEN
2326: BEGIN
2327: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
2323: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2324: EXCEPTION
2325: WHEN deadlock_detected THEN
2326: BEGIN
2327: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
2328: ROLLBACK;
2329: l_attempts := l_attempts +1;
2330: IF l_attempts = 3 THEN
2331: FOR i IN l_first .. l_last LOOP
2346: and ACC.salesforce_id = l_resource_id
2347: and nvl(ACC.sales_group_id,-777) = nvl(l_group_id,-777);
2348: EXCEPTION
2349: WHEN OTHERS THEN
2350: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2351: END;
2352: END LOOP;
2353: COMMIT;
2354: END IF;
2353: COMMIT;
2354: END IF;
2355: END; -- end of deadlock exception
2356: WHEN OTHERS THEN
2357: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2358: x_errbuf := SQLERRM;
2359: x_retcode := SQLCODE;
2360: x_return_status := FND_API.G_RET_STS_ERROR;
2361: RAISE;
2361: RAISE;
2362: END;
2363: END LOOP;
2364:
2365: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS');
2366: l_attempts := 1;
2367: WHILE l_attempts < 3 LOOP
2368: BEGIN
2369: FORALL i in l_first .. l_last
2380: sl.assign_to_person_id = l_person_id
2381: WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
2382: COMMIT;
2383: l_attempts := 3;
2384: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS::' || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2385: EXCEPTION
2386: WHEN deadlock_detected THEN
2387: BEGIN
2388: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS::' || AS_GAR.G_DEADLOCK ||l_attempts );
2384: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS::' || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2385: EXCEPTION
2386: WHEN deadlock_detected THEN
2387: BEGIN
2388: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS::' || AS_GAR.G_DEADLOCK ||l_attempts );
2389: l_attempts := l_attempts +1;
2390: ROLLBACK;
2391: IF l_attempts = 3 THEN
2392: FOR i IN l_first .. l_last LOOP
2404: sl.assign_to_person_id = l_person_id
2405: WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
2406: EXCEPTION
2407: WHEN OTHERS THEN
2408: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE AS_SALES_LEADS', SQLERRM, TO_CHAR(SQLCODE));
2409: END;
2410: END LOOP;
2411: COMMIT;
2412: END IF;
2411: COMMIT;
2412: END IF;
2413: END; -- end of deadlock exception
2414: WHEN OTHERS THEN
2415: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS', SQLERRM, TO_CHAR(SQLCODE));
2416: x_errbuf := SQLERRM;
2417: x_retcode := SQLCODE;
2418: x_return_status := FND_API.G_RET_STS_ERROR;
2419: RAISE;
2425: l_flag := FALSE;
2426: END IF;
2427: END LOOP;
2428: END IF; --l_sales_lead_id.count > 0
2429: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_sales_lead_id.COUNT);
2430: END LOOP;
2431: l_limit_flag := FALSE;
2432: l_loop_count := 0;
2433: l_access_id.delete;
2436: END IF; -- (Custom or Non Custom)
2437:
2438: EXCEPTION
2439: WHEN OTHERS THEN
2440: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
2441: x_errbuf := SQLERRM;
2442: x_retcode := SQLCODE;
2443: x_return_status := FND_API.G_RET_STS_ERROR;
2444: RAISE;
2448: /**************************** Start Uncheck_Lead_Owner ********************/
2449: PROCEDURE UNCHECK_LEAD_OWNER(
2450: x_errbuf OUT NOCOPY VARCHAR2,
2451: x_retcode OUT NOCOPY VARCHAR2,
2452: p_terr_globals IN AS_GAR.TERR_GLOBALS,
2453: x_return_status OUT NOCOPY VARCHAR2)
2454: IS
2455: l_return_status VARCHAR2(1);
2456: l_msg_count NUMBER;
2500: l_worker_id NUMBER;
2501: l_var NUMBER;
2502:
2503: BEGIN
2504: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_START);
2505:
2506: x_return_status := FND_API.G_RET_STS_SUCCESS;
2507: l_worker_id:=p_terr_globals.worker_id;
2508: l_var :=p_terr_globals.bulk_size;
2508: l_var :=p_terr_globals.bulk_size;
2509: l_max_fetches := p_terr_globals.cursor_limit;
2510: IF fnd_profile.value('AS_LEAD_ASSIGNMENT_UHK') = 'Y'
2511: THEN
2512: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'CUSTOM AS PER PROFILE---AS_LEAD_ASSIGNMENT_UHK:- Y---');
2513: AS_CUSTOM_HOOKS_UHK.Lead_TOTTAP_Owner_Assignment(
2514: p_request_id => p_terr_globals.request_id,
2515: p_worker_id => p_terr_globals.worker_id,
2516: x_return_status => l_return_status,
2519: ELSE
2520: LOOP
2521: IF (l_limit_flag) THEN EXIT; END IF;
2522: l_loop_count := l_loop_count + 1;
2523: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
2524: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
2525: OPEN tot_lead_owner(l_worker_id);
2526: FETCH tot_lead_owner BULK COLLECT INTO l_sales_lead_id LIMIT l_max_fetches;
2527: CLOSE tot_lead_owner;
2520: LOOP
2521: IF (l_limit_flag) THEN EXIT; END IF;
2522: l_loop_count := l_loop_count + 1;
2523: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
2524: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
2525: OPEN tot_lead_owner(l_worker_id);
2526: FETCH tot_lead_owner BULK COLLECT INTO l_sales_lead_id LIMIT l_max_fetches;
2527: CLOSE tot_lead_owner;
2528: ELSE
2547: IF l_last > l_sales_lead_id.LAST THEN
2548: l_last := l_sales_lead_id.LAST;
2549: END IF;
2550: WHILE l_attempts < 3 LOOP
2551: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL');
2552: BEGIN
2553: FORALL i in l_first .. l_last
2554: UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,
2555: ACC.LAST_UPDATE_DATE = SYSDATE,
2564: and ACC.owner_flag = 'Y'
2565: and ACC.freeze_flag = 'N';
2566: COMMIT;
2567: l_attempts := 3;
2568: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2569: EXCEPTION
2570: WHEN deadlock_detected THEN
2571: BEGIN
2572: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
2568: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2569: EXCEPTION
2570: WHEN deadlock_detected THEN
2571: BEGIN
2572: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
2573: ROLLBACK;
2574: l_attempts := l_attempts +1;
2575: IF l_attempts = 3 THEN
2576: FOR i IN l_first .. l_last LOOP
2588: and ACC.owner_flag = 'Y'
2589: and ACC.freeze_flag = 'N';
2590: EXCEPTION
2591: WHEN OTHERS THEN
2592: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2593: END;
2594: END LOOP;
2595: COMMIT;
2596: END IF;
2595: COMMIT;
2596: END IF;
2597: END; -- end of deadlock exception
2598: WHEN OTHERS THEN
2599: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2600: x_errbuf := SQLERRM;
2601: x_retcode := SQLCODE;
2602: x_return_status := FND_API.G_RET_STS_ERROR;
2603: RAISE;
2610: l_flag := FALSE;
2611: END IF;
2612: END LOOP;
2613: END IF; --l_sales_lead_id.count > 0
2614: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_sales_lead_id.COUNT);
2615: END LOOP;
2616: l_limit_flag := FALSE;
2617: l_loop_count := 0;
2618: l_access_id.delete;
2621: END IF; -- (Custom or Non Custom)
2622:
2623: EXCEPTION
2624: WHEN OTHERS THEN
2625: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
2626: x_errbuf := SQLERRM;
2627: x_retcode := SQLCODE;
2628: x_return_status := FND_API.G_RET_STS_ERROR;
2629: RAISE;
2633: /**************************** Start Uncheck_Assign_Salesforce ********************/
2634: PROCEDURE UNCHECK_ASSIGN_SALESFORCE(
2635: x_errbuf OUT NOCOPY VARCHAR2,
2636: x_retcode OUT NOCOPY VARCHAR2,
2637: p_terr_globals IN AS_GAR.TERR_GLOBALS,
2638: x_return_status OUT NOCOPY VARCHAR2)
2639: IS
2640: l_return_status VARCHAR2(1);
2641: l_msg_count NUMBER;
2696: l_worker_id NUMBER;
2697: l_var NUMBER;
2698:
2699: BEGIN
2700: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_START);
2701:
2702: x_return_status := FND_API.G_RET_STS_SUCCESS;
2703: l_worker_id:=p_terr_globals.worker_id;
2704: l_var :=p_terr_globals.bulk_size;
2704: l_var :=p_terr_globals.bulk_size;
2705: l_max_fetches := p_terr_globals.cursor_limit;
2706: IF fnd_profile.value('AS_LEAD_ASSIGNMENT_UHK') = 'Y'
2707: THEN
2708: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'CUSTOM AS PER PROFILE---AS_LEAD_ASSIGNMENT_UHK:- Y---');
2709: AS_CUSTOM_HOOKS_UHK.Lead_TOTTAP_Owner_Assignment(
2710: p_request_id => p_terr_globals.request_id,
2711: p_worker_id => p_terr_globals.worker_id,
2712: x_return_status => l_return_status,
2715: ELSE
2716: LOOP
2717: IF (l_limit_flag) THEN EXIT; END IF;
2718: l_loop_count := l_loop_count + 1;
2719: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
2720: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
2721: OPEN tot_lead_owner(l_worker_id);
2722: FETCH tot_lead_owner BULK COLLECT INTO l_sales_lead_id,l_access_id LIMIT l_max_fetches;
2723: CLOSE tot_lead_owner;
2716: LOOP
2717: IF (l_limit_flag) THEN EXIT; END IF;
2718: l_loop_count := l_loop_count + 1;
2719: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
2720: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
2721: OPEN tot_lead_owner(l_worker_id);
2722: FETCH tot_lead_owner BULK COLLECT INTO l_sales_lead_id,l_access_id LIMIT l_max_fetches;
2723: CLOSE tot_lead_owner;
2724: ELSE
2743: IF l_last > l_sales_lead_id.LAST THEN
2744: l_last := l_sales_lead_id.LAST;
2745: END IF;
2746: WHILE l_attempts < 3 LOOP
2747: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS');
2748: BEGIN
2749: FORALL i in l_first .. l_last
2750: UPDATE AS_SALES_LEADS sl SET
2751: sl.last_update_date = SYSDATE,
2765: )
2766: WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
2767: COMMIT;
2768: l_attempts := 3;
2769: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2770: EXCEPTION
2771: WHEN deadlock_detected THEN
2772: BEGIN
2773: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
2769: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2770: EXCEPTION
2771: WHEN deadlock_detected THEN
2772: BEGIN
2773: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
2774: ROLLBACK;
2775: l_attempts := l_attempts +1;
2776: IF l_attempts = 3 THEN
2777: FOR i IN l_first .. l_last LOOP
2794: )
2795: WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
2796: EXCEPTION
2797: WHEN OTHERS THEN
2798: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE OF SALES LEADS', SQLERRM, TO_CHAR(SQLCODE));
2799: END;
2800: END LOOP;
2801: COMMIT;
2802: END IF;
2801: COMMIT;
2802: END IF;
2803: END; -- end of deadlock exception
2804: WHEN OTHERS THEN
2805: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS', SQLERRM, TO_CHAR(SQLCODE));
2806: x_errbuf := SQLERRM;
2807: x_retcode := SQLCODE;
2808: x_return_status := FND_API.G_RET_STS_ERROR;
2809: RAISE;
2816: l_flag := FALSE;
2817: END IF;
2818: END LOOP;
2819: END IF; --l_sales_lead_id.count > 0
2820: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_sales_lead_id.COUNT);
2821: END LOOP;
2822: l_limit_flag := FALSE;
2823: l_loop_count := 0;
2824: l_access_id.delete;
2827: END IF; -- (Custom or Non Custom)
2828:
2829: EXCEPTION
2830: WHEN OTHERS THEN
2831: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
2832: x_errbuf := SQLERRM;
2833: x_retcode := SQLCODE;
2834: x_return_status := FND_API.G_RET_STS_ERROR;
2835: RAISE;
2836: END UNCHECK_ASSIGN_SALESFORCE;
2837:
2838: /**************************** End Uncheck_Assign_Salesforce ********************/
2839:
2840: END AS_GAR_LEADS_PUB;
2841: