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