[Home] [Help]
PACKAGE BODY: APPS.AS_ATA_TOTAL_PUB
Source
1 PACKAGE BODY AS_ATA_TOTAL_PUB as
2 /* $Header: asxtatab.pls 120.13.12000000.2 2007/05/05 08:33:01 annsrini ship $ */
3
4
5 /*-------------------------------------------------------------------------*
6 |
7 | PRIVATE CONSTANTS
8 |
9 *-------------------------------------------------------------------------*/
10 G_ENTITY CONSTANT VARCHAR2(20) := 'ATA TOTAL MODE : ';
11 DEADLOCK_DETECTED EXCEPTION;
12 PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
13 /*-------------------------------------------------------------------------*
14 |
15 | PRIVATE DATATYPES
16 |
17 *-------------------------------------------------------------------------*/
18
19 /*-------------------------------------------------------------------------*
20 |
21 | PRIVATE VARIABLES
22 |
23 *-------------------------------------------------------------------------*/
24 G_PR_ACCOUNT VARCHAR2(25) := 'PROCESS ACCOUNT TRANS';
25 G_PR_LEAD VARCHAR2(20) := 'PROCESS LEAD TRANS';
26 G_PR_OPPTY VARCHAR2(30) := 'PROCESS OPPORTUNITY TRANS';
27 G_PR_QUOTE VARCHAR2(25) := 'PROCESS QUOTES TRANS';
28 G_PR_PROPOSAL VARCHAR2(25) := 'PROCESS PROPOSAL TRANS';
29 G_PR_TRANS VARCHAR2(20) := 'PROCESS JTY TRANS';
30 G_WHERE VARCHAR2(20) := 'ADDL. WHERE CLAUSE';
31 G_GAR_SUBMIT VARCHAR2(20) := 'SUBMITTING GAR';
32
33 /*-------------------------------------------------------------------------*
34 |
35 | PRIVATE ROUTINES SPECIFICATION
36 |
37 *-------------------------------------------------------------------------*/
38 PROCEDURE Process_trans_data(
39 p_trans_id IN NUMBER,
40 P_addl_where IN VARCHAR2,
41 P_percent_analyzed IN NUMBER,
42 P_trace_mode IN VARCHAR2,
43 x_return_Status OUT NOCOPY VARCHAR2);
44 PROCEDURE Assign_Account_Terr_Accesses(
45 ERRBUF out NOCOPY VARCHAR2,
46 RETCODE out NOCOPY VARCHAR2,
47 P_submit_acc_tap IN VARCHAR2,
48 P_account_type IN VARCHAR2,
49 P_addl_where IN VARCHAR2,
50 P_perc_analyzed IN NUMBER,
51 P_debug IN VARCHAR2,
52 P_trace IN VARCHAR2
53 )
54 IS
55 l_addl_where VARCHAR2(2000);
56 l_return_status VARCHAR2(10);
57 l_status BOOLEAN;
58 l_errbuf VARCHAR2(4000);
59 l_retcode VARCHAR2(255);
60 l_oracle_schema VARCHAR2(32) := 'OSM';
61 BEGIN
62 AS_GAR.g_debug_flag := p_debug;
63 IF P_trace = 'Y' THEN AS_GAR.SETTRACE; END IF;
64 AS_GAR.LOG(G_ENTITY || G_PR_ACCOUNT || AS_GAR.G_START);
65 IF P_submit_acc_tap='Y' THEN
66 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_SETAREASIZE || AS_GAR.G_START);
67 AS_GAR.Set_Area_Sizes;
68 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_SETAREASIZE || AS_GAR.G_END);
69 IF P_account_type = 'ORGANIZATION' THEN
70 l_addl_where := ' Where party_type = ''ORGANIZATION''';
71 ELSIF P_account_type = 'PERSON' THEN
72 l_addl_where := ' Where party_type = ''PERSON''';
73 END IF;
74 IF p_addl_where is NOT NULL THEN
75 IF nvl(p_account_type,'ALL') = 'ALL' THEN
76 l_addl_where := ' Where ' || p_addl_Where;
77 ELSE
78 l_addl_where := l_Addl_Where || ' and ' ||p_addl_Where;
79 END IF;
80 END IF;
81 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_ACCOUNT || G_WHERE || l_addl_where);
82 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_ACCOUNT || AS_GAR.G_START);
83
84 DELETE_CHANGED_ENTITY(p_entity => 'ACCOUNT',
85 x_errbuf => l_errbuf,
86 x_retcode => l_retcode,
87 x_return_status => l_return_status);
88 PROCESS_TRANS_DATA(
89 p_trans_id => -1002
90 ,P_addl_where => l_addl_where
91 ,P_percent_analyzed =>P_perc_analyzed
92 ,P_trace_mode => p_trace
93 ,x_return_status => l_return_status);
94 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_ACCOUNT || AS_GAR.G_END ||l_return_status);
95 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_ACCOUNT || AS_GAR.G_RETURN_STATUS || l_return_status);
96 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
97 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_ACCOUNT, l_errbuf, l_retcode);
98 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
99 End If;
100
101 --fix for bug(5869095) --populating as_terr_resources_tmp table
102
103 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_oracle_schema || '.AS_TERR_RESOURCES_TMP REUSE STORAGE';
104
105 INSERT /*+ append parallel(AS_TERR_RESOURCES_TMP) */
106 INTO AS_TERR_RESOURCES_TMP
107 (RESOURCE_ID,
108 RESOURCE_TYPE,
109 PARTY_ID,
110 TERR_ID)
111 ( SELECT DISTINCT USERS.EMPLOYEE_ID,
112 VAL.PROFILE_OPTION_VALUE,-1,-1
113 FROM FND_PROFILE_OPTION_VALUES VAL,
114 FND_PROFILE_OPTIONS OPTIONS,
115 FND_USER USERS
116 WHERE VAL.LEVEL_ID = 10004
117 AND USERS.EMPLOYEE_ID is not null
118 AND VAL.PROFILE_OPTION_VALUE is not null
119 AND USERS.USER_ID = VAL.LEVEL_VALUE
120 AND VAL.PROFILE_OPTION_VALUE is not null
121 AND OPTIONS.PROFILE_OPTION_ID = VAL.PROFILE_OPTION_ID
122 AND OPTIONS.APPLICATION_ID = VAL.APPLICATION_ID
123 AND OPTIONS.PROFILE_OPTION_NAME = 'AS_DEF_CUST_ST_ROLE'
124 );
125
126 COMMIT;
127
128 dbms_stats.gather_table_stats('OSM','AS_TERR_RESOURCES_TMP',
129 estimate_percent=>10, degree=>8, granularity=>'GLOBAL', cascade=>TRUE) ;
130 COMMIT;
131
132 --fix for bug(5869095) --populating as_terr_resources_tmp table
133
134 END IF;
135 AS_GAR.LOG(G_ENTITY || G_PR_ACCOUNT || AS_GAR.G_END);
136 EXCEPTION
137 WHEN OTHERS THEN
138 AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
139 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
140 END Assign_Account_Terr_Accesses;
141
142 PROCEDURE Assign_Lead_Terr_Accesses(
143 ERRBUF OUT NOCOPY VARCHAR2,
144 RETCODE OUT NOCOPY VARCHAR2,
145 P_submit_lead_tap IN VARCHAR2,
146 P_lead_status IN VARCHAR2,
147 P_addl_where IN VARCHAR2,
148 P_perc_analyzed IN NUMBER,
149 P_debug IN VARCHAR2,
150 P_trace IN VARCHAR2
151 )
152 IS
153 l_errbuf VARCHAR2(4000);
154 l_retcode VARCHAR2(255);
155 l_addl_where VARCHAR2(2000);
156 l_return_status VARCHAR2(10);
157 l_status BOOLEAN;
158 BEGIN
159 AS_GAR.g_debug_flag := p_debug;
160 IF P_trace = 'Y' THEN AS_GAR.SETTRACE; END IF;
161 AS_GAR.LOG(G_ENTITY || G_PR_LEAD || AS_GAR.G_START);
162
163 IF P_submit_lead_tap='Y' THEN
164 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_SETAREASIZE || AS_GAR.G_START);
165 AS_GAR.Set_Area_Sizes;
166 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_SETAREASIZE || AS_GAR.G_END);
167
168 DELETE_CHANGED_ENTITY(p_entity => 'LEAD',
169 x_errbuf => l_errbuf,
170 x_retcode => l_retcode,
171 x_return_status => l_return_status);
172 IF P_lead_status = 'OPEN' THEN
173 l_addl_where := ' Where open_flag = ''Y''';
174 ELSIF P_lead_status = 'CLOSED' THEN
175 l_addl_where := ' WHere open_Flag = ''N''';
176 END IF;
177 IF p_addl_where is NOT NULL THEN
178 IF nvl(P_lead_status,'ALL') = 'ALL' THEN
179 l_addl_where := ' Where ' || p_addl_Where;
180 ELSE
181 l_addl_where := l_Addl_Where || ' and ' ||p_addl_Where;
182 END IF;
183 END IF;
184 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_LEAD || G_WHERE || l_addl_where);
185 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_LEAD || AS_GAR.G_START);
186 Process_trans_data(
187 p_trans_id => -1003
188 ,P_addl_where => l_addl_where
189 ,P_percent_analyzed =>P_perc_analyzed
190 ,P_trace_mode => p_trace
191 ,x_return_status => l_return_status);
192 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_LEAD || AS_GAR.G_END ||l_return_status);
193 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_LEAD || AS_GAR.G_RETURN_STATUS || l_return_status);
194 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
195 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_LEAD, l_errbuf, l_retcode);
196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
197 End If;
198 END IF;
199 AS_GAR.LOG(G_ENTITY || G_PR_LEAD || AS_GAR.G_END);
200 EXCEPTION
201 WHEN OTHERS THEN
202 AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
203 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
204 END Assign_Lead_Terr_Accesses;
205
206 PROCEDURE Assign_Oppty_Terr_Accesses(
207 ERRBUF OUT NOCOPY VARCHAR2,
208 RETCODE OUT NOCOPY VARCHAR2,
209 P_submit_oppty_tap IN VARCHAR2,
210 P_oppty_status IN VARCHAR2,
211 P_addl_where IN VARCHAR2,
212 P_perc_analyzed IN NUMBER,
213 P_debug IN VARCHAR2,
214 P_trace IN VARCHAR2
215 )
216 IS
217 l_errbuf VARCHAR2(4000);
218 l_retcode VARCHAR2(255);
219 l_addl_where VARCHAR2(2000);
220 l_return_status VARCHAR2(10);
221 l_status BOOLEAN;
222
223 BEGIN
224 AS_GAR.g_debug_flag := p_debug;
225 IF P_trace = 'Y' THEN AS_GAR.SETTRACE; END IF;
226 AS_GAR.LOG(G_ENTITY || G_PR_OPPTY || AS_GAR.G_START);
227 IF P_submit_oppty_tap='Y' THEN
228 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_SETAREASIZE || AS_GAR.G_START);
229 AS_GAR.Set_Area_Sizes;
230 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_SETAREASIZE || AS_GAR.G_END);
231
232 DELETE_CHANGED_ENTITY(p_entity => 'OPPTY',
233 x_errbuf => l_errbuf,
234 x_retcode => l_retcode,
235 x_return_status => l_return_status);
236 IF P_oppty_status = 'OPEN' THEN
237 l_addl_where := ' Where open_flag = ''Y''';
238 ELSIF P_oppty_status = 'CLOSED' THEN
239 l_addl_where := ' Where open_Flag = ''N''';
240 END IF;
241 IF p_addl_where is NOT NULL THEN
242 IF nvl(P_oppty_status,'ALL') = 'ALL' THEN
243 l_addl_where := ' Where ' || p_addl_Where;
244 ELSE
245 l_addl_where := l_Addl_Where || ' and ' ||p_addl_Where;
246 END IF;
247 END IF;
248 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_OPPTY || G_WHERE || l_addl_where);
249 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_OPPTY || AS_GAR.G_START);
250 Process_trans_data(
251 p_trans_id => -1004
252 ,P_addl_where => l_addl_where
253 ,P_percent_analyzed =>P_perc_analyzed
254 ,P_trace_mode => p_trace
255 ,x_return_status => l_return_status);
256 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_OPPTY || AS_GAR.G_END ||l_return_status);
257 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_OPPTY || AS_GAR.G_RETURN_STATUS || l_return_status);
258 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
259 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_OPPTY, l_errbuf, l_retcode);
260 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
261 End If;
262 END IF;
263 AS_GAR.LOG(G_ENTITY || G_PR_OPPTY || AS_GAR.G_END);
264 EXCEPTION
265 WHEN OTHERS THEN
266 AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
267 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
268 END Assign_Oppty_Terr_Accesses;
269
270
271 PROCEDURE Assign_Quote_Terr_Accesses(
272 ERRBUF OUT NOCOPY VARCHAR2,
273 RETCODE OUT NOCOPY VARCHAR2,
274 P_submit_quote_tap IN VARCHAR2,
275 P_exclude_ord_quote IN VARCHAR2,
276 P_exclude_exp_quote IN VARCHAR2,
277 P_addl_where IN VARCHAR2,
278 P_perc_analyzed IN NUMBER,
279 P_debug IN VARCHAR2,
280 P_trace IN VARCHAR2
281 )
282 IS
283 l_errbuf VARCHAR2(4000);
284 l_retcode VARCHAR2(255);
285 l_addl_where VARCHAR2(2000);
286 l_return_status VARCHAR2(10);
287 l_status BOOLEAN;
288 BEGIN
289 AS_GAR.g_debug_flag := p_debug;
290 IF P_trace = 'Y' THEN AS_GAR.SETTRACE; END IF;
291 AS_GAR.LOG(G_ENTITY || G_PR_QUOTE || AS_GAR.G_START);
292 IF P_submit_quote_tap='Y' THEN
293 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_SETAREASIZE || AS_GAR.G_START);
294 AS_GAR.Set_Area_Sizes;
295 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_SETAREASIZE || AS_GAR.G_END);
296
297 DELETE_CHANGED_ENTITY(p_entity => 'QUOTE',
298 x_errbuf => l_errbuf,
299 x_retcode => l_retcode,
300 x_return_status => l_return_status);
301 IF nvl(P_exclude_ord_quote,'N') = 'Y' THEN
302 l_addl_where := ' WHERE ORDER_ID IS NULL';
303 END IF;
304 IF nvl(P_exclude_exp_quote,'N') = 'Y' THEN
305 IF nvl(P_exclude_ord_quote,'N') ='N' THEN
306 l_addl_where := ' WHERE trunc(quote_expiration_date) >= trunc(sysdate) ';
307 ELSE
308 l_addl_where := l_addl_where || ' AND trunc(quote_expiration_date) >= trunc(sysdate) ';
309 END IF;
310 END IF;
311 IF p_addl_where is NOT NULL THEN
312 IF nvl(P_exclude_ord_quote,'N') = 'N' AND nvl(P_exclude_ord_quote,'N') ='N' THEN
313 l_addl_where := ' Where ' || p_addl_Where;
314 ELSE
315 l_addl_where := l_Addl_Where || ' and ' ||p_addl_Where;
316 END IF;
317 END IF;
318 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_QUOTE || G_WHERE || l_addl_where);
319 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_QUOTE || AS_GAR.G_START);
320 Process_trans_data(
321 p_trans_id => -1105
322 ,P_addl_where => l_addl_where
323 ,P_percent_analyzed =>P_perc_analyzed
324 ,P_trace_mode => p_trace
325 ,x_return_status => l_return_status);
326 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_QUOTE || AS_GAR.G_END ||l_return_status);
327 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_QUOTE || AS_GAR.G_RETURN_STATUS || l_return_status);
328 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
329 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_QUOTE, l_errbuf, l_retcode);
330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
331 End If;
332 END IF;
336 AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
333 AS_GAR.LOG(G_ENTITY || G_PR_QUOTE || AS_GAR.G_END);
334 EXCEPTION
335 WHEN OTHERS THEN
337 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
338 END Assign_Quote_Terr_Accesses;
339
340 PROCEDURE Assign_Proposal_Terr_Accesses(
341 ERRBUF out NOCOPY VARCHAR2,
342 RETCODE out NOCOPY VARCHAR2,
343 P_submit_prp_tap IN VARCHAR2,
344 P_addl_where IN VARCHAR2,
345 P_perc_analyzed IN NUMBER,
346 P_debug IN VARCHAR2,
347 P_trace IN VARCHAR2
348 )
349 IS
350 l_errbuf VARCHAR2(4000);
351 l_retcode VARCHAR2(255);
352 l_addl_where VARCHAR2(2000);
353 l_return_status VARCHAR2(10);
354 l_status BOOLEAN;
355 BEGIN
356 AS_GAR.g_debug_flag := p_debug;
357 IF P_trace = 'Y' THEN AS_GAR.SETTRACE; END IF;
358 AS_GAR.LOG(G_ENTITY || G_PR_PROPOSAL || AS_GAR.G_START);
359 IF P_submit_prp_tap = 'Y' THEN
360 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_SETAREASIZE || AS_GAR.G_START);
361 AS_GAR.Set_Area_Sizes;
362 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_SETAREASIZE || AS_GAR.G_END);
363
364 DELETE_CHANGED_ENTITY(p_entity => 'PROPOSAL',
365 x_errbuf => l_errbuf,
366 x_retcode => l_retcode,
367 x_return_status => l_return_status);
368
369
370 IF p_addl_where IS NOT NULL THEN
371 l_addl_where := 'Where ' || p_addl_Where;
372 END IF;
373 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_PROPOSAL || G_WHERE || l_addl_where);
374 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_PROPOSAL || AS_GAR.G_START);
375 Process_trans_data(
376 p_trans_id => -1106
377 ,P_addl_where => l_addl_where
378 ,P_percent_analyzed =>P_perc_analyzed
379 ,P_trace_mode => p_trace
380 ,x_return_status => l_return_status);
381 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_PROPOSAL || AS_GAR.G_END ||l_return_status);
382 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_PROPOSAL || AS_GAR.G_RETURN_STATUS || l_return_status);
383 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
384 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || G_PR_PROPOSAL, l_errbuf, l_retcode);
385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
386 End If;
387 END IF;
388 AS_GAR.LOG(G_ENTITY || G_PR_PROPOSAL || AS_GAR.G_END);
389 EXCEPTION
390 WHEN OTHERS THEN
391 AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
392 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
393 END Assign_Proposal_Terr_Accesses;
394
395 PROCEDURE Process_trans_data
396 (
397 p_trans_id IN NUMBER,
398 P_addl_where IN VARCHAR2,
399 P_percent_analyzed IN NUMBER,
400 P_trace_mode IN VARCHAR2,
401 x_return_Status OUT NOCOPY VARCHAR2
402 )
403 IS
404 l_return_status VARCHAR2(10);
405 l_msg_count NUMBER;
406 l_msg_data VARCHAR2(2000);
407 l_errbuf VARCHAR2(1000);
408 l_retcode VARCHAR2(1000);
409 l_con_req_name VARCHAR2(150);
410 l_program_name VARCHAR2(150);
411 l_req_id NUMBER;
412 l_number NUMBER;
413 l_status BOOLEAN;
414 l_prof_no_of_workers NUMBER;
415 l_entity VARCHAR2(25);
416 BEGIN
417 IF p_trans_id = -1002 THEN
418 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || G_PR_ACCOUNT || AS_GAR.G_START);
419 l_program_name := 'SALES/ACCOUNT PROGRAM';
420 l_con_req_name :='ASXGARAC';
421 l_prof_no_of_workers := fnd_profile.value('AS_TAP_NUM_CHILD_ACCOUNT_WORKERS');
422 l_entity := ': ACCOUNT :';
423 ELSIF p_trans_id = -1003 THEN
424 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || G_PR_LEAD || AS_GAR.G_START);
425 l_program_name := 'SALES/LEAD PROGRAM';
426 l_con_req_name :='ASXGARLD';
427 l_prof_no_of_workers := fnd_profile.value('AS_TAP_NUM_CHILD_LEAD_WORKERS');
428 l_entity := ' : LEAD : ';
429 ELSIF p_trans_id = -1004 THEN
430 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || G_PR_OPPTY || AS_GAR.G_START);
431 l_program_name := 'SALES/OPPORTUNITY PROGRAM';
432 l_con_req_name :='ASXGAROP';
433 l_prof_no_of_workers := fnd_profile.value('AS_TAP_NUM_CHILD_OPPOR_WORKERS');
434 l_entity := ' : OPPORTUNITY : ';
435 ELSIF p_trans_id = -1105 THEN
436 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || G_PR_QUOTE || AS_GAR.G_START);
437 l_program_name := 'SALES/QUOTE PROGRAM';
438 l_con_req_name :='ASXGARQT';
439 l_prof_no_of_workers := fnd_profile.value('AS_TAP_NUM_CHILD_QUOTE_WORKERS');
440 l_entity := ' : QUOTE : ';
441 ELSIF p_trans_id = -1106 THEN
442 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || G_PR_PROPOSAL || AS_GAR.G_START);
443 l_program_name := 'SALES/PROPOSAL PROGRAM';
444 l_con_req_name :='ASXGARPR';
445 l_prof_no_of_workers := fnd_profile.value('AS_TAP_NUM_CHILD_PROPOSAL_WORKERS');
446 l_entity := ' : PROPOSAL : ';
447 END IF;
448 IF l_prof_no_of_workers >10 then
449 l_prof_no_of_workers := 10;
450 END IF;
451 AS_GAR.LOG(G_ENTITY ||l_entity|| 'No of Workers :'|| l_prof_no_of_workers ||'% Analyzed:'|| P_percent_analyzed);
452 AS_GAR.LOG(G_ENTITY ||l_entity|| 'Additioal Where Clause :'|| P_addl_where);
456 P_init_msg_list => FND_API.G_FALSE,
453 AS_GAR.LOG(G_ENTITY ||l_entity|| AS_GAR.G_CALL_TO || G_PR_TRANS || AS_GAR.G_START);
454 JTY_ASSIGN_BULK_PUB.collect_trans_data (
455 P_api_version_number => 1.0,
457 P_source_id => -1001,
458 P_trans_id =>p_trans_id,
459 P_program_name => l_program_name,
460 P_mode => 'TOTAL',
461 P_where => P_addl_where,
462 P_NO_OF_WORKERS => l_prof_no_of_workers,
463 P_percent_analyzed => NVL(P_percent_analyzed,20),
464 P_request_id => FND_GLOBAL.Conc_Request_Id,
465 X_return_status => l_return_status,
466 X_msg_count => l_msg_count,
467 X_msg_data => l_msg_data,
468 Errbuf => l_errbuf,
469 Retcode => l_retcode);
470 AS_GAR.LOG(G_ENTITY ||l_entity || AS_GAR.G_CALL_TO || G_PR_TRANS||l_msg_count||l_msg_data|| AS_GAR.G_END);
471 AS_GAR.LOG(G_ENTITY ||l_entity || AS_GAR.G_CALL_TO || G_PR_TRANS || AS_GAR.G_RETURN_STATUS || l_return_status);
472
473 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
474 AS_GAR.LOG_EXCEPTION(G_ENTITY ||l_entity || AS_GAR.G_CALL_TO || G_PR_TRANS||l_msg_count||l_msg_data, l_errbuf, l_retcode);
475 RAISE FND_API.G_EXC_ERROR;
476 END IF;
477
478 FOR i in 1..l_prof_no_of_workers
479 LOOP
480 AS_GAR.LOG(G_ENTITY ||l_entity || G_GAR_SUBMIT || AS_GAR.G_START ||' Worker ID : ' || i);
481 l_req_id := FND_REQUEST.SUBMIT_REQUEST('AS',
482 l_con_req_name,
483 '',
484 '',
485 FALSE,
486 'TOTAL',
487 AS_GAR.g_debug_flag,
488 P_trace_mode ,
489 i,
490 P_percent_analyzed,
491 CHR(0));
492
493 AS_GAR.LOG(G_ENTITY ||l_entity || G_GAR_SUBMIT || AS_GAR.G_END ||' Request ID : ' || l_req_id);
494 IF l_req_id = 0
495 THEN
496 l_msg_data:=FND_MESSAGE.GET;
497 AS_GAR.LOG(G_ENTITY ||l_entity || G_GAR_SUBMIT || AS_GAR.G_END ||' ERRPR :' || l_msg_data);
498 RAISE FND_API.G_EXC_ERROR;
499 END IF;
500 END LOOP;
501 EXCEPTION
502 WHEN OTHERS THEN
503 x_return_status := FND_API.G_RET_STS_ERROR;
504 AS_GAR.LOG_EXCEPTION(G_ENTITY ||l_entity||G_GAR_SUBMIT, SQLERRM, TO_CHAR(SQLCODE));
505 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
506 END PROCESS_TRANS_DATA;
507
508 -------------Modified as per Bug#5027026-----------------
509 PROCEDURE DELETE_CHANGED_ENTITY(p_entity IN VARCHAR2,
510 x_errbuf OUT NOCOPY VARCHAR2,
511 x_retcode OUT NOCOPY VARCHAR2,
512 x_return_status OUT NOCOPY VARCHAR2)IS
513
514 TYPE entity_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
515 l_entity_id entity_id_list;
516
517 CURSOR del_acct_changed IS
518 SELECT distinct customer_id
519 FROM AS_CHANGED_ACCOUNTS_ALL
520 WHERE lead_id IS NULL
521 AND sales_lead_id IS NULL;
522
523 CURSOR del_lead_changed IS
524 SELECT distinct sales_lead_id
525 FROM AS_CHANGED_ACCOUNTS_ALL
526 WHERE lead_id IS NULL
527 AND sales_lead_id IS NOT NULL;
528
529 CURSOR del_oppty_changed IS
530 SELECT distinct lead_id
531 FROM AS_CHANGED_ACCOUNTS_ALL
532 WHERE lead_id IS NOT NULL
533 AND sales_lead_id IS NULL;
534
535 CURSOR del_proposal_changed IS
536 SELECT distinct proposal_id
537 FROM PRP_CHANGED_PROPOSALS;
538
539 CURSOR del_quote_changed IS
540 SELECT distinct quote_number
541 FROM ASO_CHANGED_QUOTES;
542
543 l_flag BOOLEAN;
544 l_first NUMBER;
545 l_last NUMBER;
546 l_var NUMBER;
547
548 l_worker_id NUMBER;
549
550 l_del_flag BOOLEAN:=FALSE;
551 l_limit_flag BOOLEAN := FALSE;
552 l_MAX_fetches NUMBER := 10000;
553 l_loop_count NUMBER := 0;
554
555 G_DEL_REC CONSTANT NUMBER:=10001;
556 l_status BOOLEAN;
557
558 BEGIN
559 l_var := nvl(to_number(fnd_profile.value('AS_BULK_COMMIT_SIZE')),10000);
560 l_MAX_fetches := nvl(to_number(fnd_profile.value('AS_TERR_RECORDS_TO_OPEN')) ,10000);
561 LOOP --{L1
562 IF (l_limit_flag) THEN EXIT; END IF;
563 l_loop_count := l_loop_count + 1;
564 BEGIN
565 AS_GAR.LOG(G_ENTITY || 'DELETE FROM CHANGED ENTITY::' || p_entity || ': LOOPCOUNT :- ' ||l_loop_count);
566 l_flag := TRUE;
567 l_first := 0;
568 l_last := 0;
569
570 IF p_entity = 'ACCOUNT' THEN
571 OPEN del_acct_changed;
572 EXIT WHEN del_acct_changed%NOTFOUND;
573 FETCH del_acct_changed BULK COLLECT INTO l_entity_id LIMIT l_MAX_fetches;
574 ELSIF p_entity = 'LEAD' THEN
575 OPEN del_lead_changed;
576 EXIT WHEN del_lead_changed%NOTFOUND;
577 FETCH del_lead_changed BULK COLLECT INTO l_entity_id LIMIT l_MAX_fetches;
578 ELSIF p_entity = 'OPPTY' THEN
579 OPEN del_oppty_changed;
580 EXIT WHEN del_oppty_changed%NOTFOUND;
581 FETCH del_oppty_changed BULK COLLECT INTO l_entity_id LIMIT l_MAX_fetches;
582 ELSIF p_entity = 'QUOTE' THEN
583 OPEN del_quote_changed;
584 EXIT WHEN del_quote_changed%NOTFOUND;
585 FETCH del_quote_changed BULK COLLECT INTO l_entity_id LIMIT l_MAX_fetches;
586 ELSIF p_entity = 'PROPOSAL' THEN
587 OPEN del_proposal_changed;
588 EXIT WHEN del_proposal_changed%NOTFOUND;
592 IF l_entity_id.COUNT < l_MAX_fetches THEN
589 FETCH del_proposal_changed BULK COLLECT INTO l_entity_id LIMIT l_MAX_fetches;
590 END IF;
591
593 l_limit_flag := TRUE;
594 END IF;
595 IF l_entity_id.count > 0 THEN --{I1
596 l_flag := TRUE;
597 l_first := l_entity_id.first;
598 l_last := l_first + l_var;
599 AS_GAR.LOG(G_ENTITY || 'DELETE FROM CHANGED ENTITY::' || p_entity || AS_GAR.G_N_ROWS_PROCESSED ||
600 l_entity_id.FIRST || '-' ||
601 l_entity_id.LAST);
602 WHILE l_flag LOOP --{L2 10K cust loop
603 IF l_last > l_entity_id.LAST THEN
604 l_last := l_entity_id.LAST;
605 END IF;
606
607 BEGIN
608 IF p_entity = 'ACCOUNT' THEN
609 FORALL i in l_first..l_last
610 DELETE FROM AS_CHANGED_ACCOUNTS_ALL ACC
611 WHERE ACC.customer_id=l_entity_id(i)
612 AND ACC.lead_id IS NULL
613 AND ACC.sales_lead_id IS NULL;
614
615 ELSIF p_entity = 'LEAD' THEN
616 FORALL i in l_first..l_last
617 DELETE FROM AS_CHANGED_ACCOUNTS_ALL ACC
618 WHERE ACC.sales_lead_id=l_entity_id(i)
619 AND ACC.lead_id IS NULL;
620
621 ELSIF p_entity = 'OPPTY' THEN
622 FORALL i in l_first..l_last
623 DELETE FROM AS_CHANGED_ACCOUNTS_ALL ACC
624 WHERE ACC.lead_id=l_entity_id(i)
625 AND ACC.change_type = 'OPPORTUNITY';
626
627 --Fix for #4891555
628 ELSIF p_entity = 'QUOTE' THEN
629 FORALL i in l_first..l_last
630 DELETE FROM ASO_CHANGED_QUOTES ACC
631 WHERE ACC.quote_number=l_entity_id(i);
632
633 ELSIF p_entity = 'PROPOSAL' THEN
634 FORALL i in l_first..l_last
635 DELETE FROM PRP_CHANGED_PROPOSALS ACC
636 WHERE ACC.proposal_id=l_entity_id(i);
637
638 END IF;
639 COMMIT;
640
641
642 EXCEPTION
643 WHEN DEADLOCK_DETECTED THEN
644 BEGIN --{I2
645
646 ROLLBACK;
647 AS_GAR.LOG('processing Individual Delete');
648 FOR i IN l_first .. l_last LOOP --{L5
649 BEGIN
650 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);
651 IF p_entity = 'ACCOUNT' THEN
652 DELETE FROM AS_CHANGED_ACCOUNTS_ALL ACC
653 WHERE ACC.customer_id=l_entity_id(i)
654 AND ACC.lead_id IS NULL
655 AND ACC.sales_lead_id IS NULL;
656 ELSIF p_entity = 'LEAD' THEN
657 DELETE FROM AS_CHANGED_ACCOUNTS_ALL ACC
658 WHERE ACC.sales_lead_id=l_entity_id(i)
659 AND ACC.lead_id IS NULL;
660 ELSIF p_entity = 'OPPTY' THEN
661 DELETE FROM AS_CHANGED_ACCOUNTS_ALL ACC
662 WHERE ACC.lead_id=l_entity_id(i)
663 AND ACC.change_type = 'OPPORTUNITY';
664 --Fix for #4891555
665
666 ELSIF p_entity = 'QUOTE' THEN
667 DELETE FROM ASO_CHANGED_QUOTES ACC
668 WHERE ACC.quote_number=l_entity_id(i);
669 ELSIF p_entity = 'PROPOSAL' THEN
670 DELETE FROM PRP_CHANGED_PROPOSALS ACC
671 WHERE ACC.proposal_id=l_entity_id(i);
672 END IF;
673 COMMIT;
674 EXCEPTION
675 WHEN OTHERS THEN
676 AS_GAR.LOG(G_ENTITY || 'DELETE FROM CHANGED ENTITY::' || p_entity || AS_GAR.G_IND_DEL || AS_GAR.G_GENERAL_EXCEPTION);
677 AS_GAR.LOG('ENTITY_ID - ' || l_entity_id(i));
678 END;
679 END LOOP; --}L5
680 COMMIT;
681
682
683 END; --}I2 end of deadlock exception
684 WHEN OTHERS THEN
685 AS_GAR.LOG_EXCEPTION(G_ENTITY || 'DELETE FROM CHANGED ENTITY::' || p_entity || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
686 IF del_acct_changed%ISOPEN THEN CLOSE del_acct_changed; END IF;
687 IF del_lead_changed%ISOPEN THEN CLOSE del_lead_changed; END IF;
688 IF del_oppty_changed%ISOPEN THEN CLOSE del_oppty_changed; END IF;
689
690 --IF del_quote_changed%ISOPEN THEN CLOSE del_quote_changed; END IF;
691 IF del_proposal_changed%ISOPEN THEN CLOSE del_proposal_changed; END IF;
692 x_errbuf := SQLERRM;
693 x_retcode := SQLCODE;
694 x_return_status := FND_API.G_RET_STS_ERROR;
695 END;
696
697 AS_GAR.LOG(G_ENTITY || 'DELETE FROM CHANGED ENTITY::' || p_entity || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-' || l_last);
698
699 l_first := l_last + 1;
700 l_last := l_first + l_var;
701 IF l_first > l_entity_id.LAST THEN
702 l_flag := FALSE;
703 END IF;
704 END LOOP; --}L2 while l_flag loop (10K cust loop)
705 END IF;--}I1
706 AS_GAR.LOG(G_ENTITY ||'DELETE FROM CHANGED ENTITY::' || AS_GAR.G_END);
707 COMMIT;
708 EXCEPTION
709 WHEN Others THEN
710 AS_GAR.LOG_EXCEPTION(G_ENTITY || 'DELETE FROM CHANGED ENTITY::' || p_entity || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
711 IF del_acct_changed%ISOPEN THEN CLOSE del_acct_changed; END IF;
712 IF del_lead_changed%ISOPEN THEN CLOSE del_lead_changed; END IF;
713 IF del_oppty_changed%ISOPEN THEN CLOSE del_oppty_changed; END IF;
714 IF del_quote_changed%ISOPEN THEN CLOSE del_quote_changed; END IF;
715 IF del_proposal_changed%ISOPEN THEN CLOSE del_proposal_changed; END IF;
716 x_errbuf := SQLERRM;
717 x_retcode := SQLCODE;
718 x_return_status := FND_API.G_RET_STS_ERROR;
719 END;
720 END LOOP;--}L1
721 IF del_acct_changed%ISOPEN THEN CLOSE del_acct_changed; END IF;
722 IF del_lead_changed%ISOPEN THEN CLOSE del_lead_changed; END IF;
723 IF del_oppty_changed%ISOPEN THEN CLOSE del_oppty_changed; END IF;
724 IF del_quote_changed%ISOPEN THEN CLOSE del_quote_changed; END IF;
725 IF del_proposal_changed%ISOPEN THEN CLOSE del_proposal_changed; END IF;
726 EXCEPTION
727 WHEN OTHERS THEN
728 x_return_status := FND_API.G_RET_STS_ERROR;
729 AS_GAR.LOG_EXCEPTION(G_ENTITY || 'DELETE FROM CHANGED ENTITY::' || p_entity , SQLERRM, TO_CHAR(SQLCODE));
730 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
731 END;
732
733
734 END AS_ATA_TOTAL_PUB;