[Home] [Help]
PACKAGE BODY: APPS.AS_STATUSES_PKG
Source
1 package body AS_STATUSES_PKG as
2 /* #$Header: asxtstab.pls 120.4 2007/03/16 08:13:57 snsarava ship $ */
3
4 G_SCHEMA_NAME VARCHAR2(32) := null;
5
6 G_INDEX_SUFFIX1 CONSTANT VARCHAR2(4) := '_MT1';
7 G_INDEX_SUFFIX2 CONSTANT VARCHAR2(4) := '_MT2';
8
9 --*****************************************************************************
10 -- Declarations
11 --
12 PROCEDURE Create_Temp_Index(p_table IN VARCHAR2,
13 p_index_columns IN VARCHAR2,
14 p_index_suffix IN VARCHAR2);
15 PROCEDURE Drop_Temp_Index(p_table IN VARCHAR2,
16 p_index_suffix IN VARCHAR2);
17 PROCEDURE Load_Schema_Name;
18
19 procedure INSERT_ROW (
20 X_ROWID in out NOCOPY VARCHAR2,
21 X_STATUS_CODE in VARCHAR2,
22 X_ENABLED_FLAG in VARCHAR2,
23 X_LEAD_FLAG in VARCHAR2,
24 X_OPP_FLAG in VARCHAR2,
25 X_OPP_OPEN_STATUS_FLAG in VARCHAR2,
26 X_OPP_DECISION_DATE_FLAG in VARCHAR2,
27 X_STATUS_RANK in NUMBER,
28 X_FORECAST_ROLLUP_FLAG in VARCHAR2,
29 X_WIN_LOSS_INDICATOR in VARCHAR2,
30 X_USAGE_INDICATOR in VARCHAR2,
31 X_ATTRIBUTE_CATEGORY in VARCHAR2,
32 X_ATTRIBUTE1 in VARCHAR2,
33 X_ATTRIBUTE2 in VARCHAR2,
34 X_ATTRIBUTE3 in VARCHAR2,
35 X_ATTRIBUTE4 in VARCHAR2,
36 X_ATTRIBUTE5 in VARCHAR2,
37 X_ATTRIBUTE6 in VARCHAR2,
38 X_ATTRIBUTE7 in VARCHAR2,
39 X_ATTRIBUTE8 in VARCHAR2,
40 X_ATTRIBUTE9 in VARCHAR2,
41 X_ATTRIBUTE10 in VARCHAR2,
42 X_ATTRIBUTE11 in VARCHAR2,
43 X_ATTRIBUTE12 in VARCHAR2,
44 X_ATTRIBUTE13 in VARCHAR2,
45 X_ATTRIBUTE14 in VARCHAR2,
46 X_ATTRIBUTE15 in VARCHAR2,
47 X_MEANING in VARCHAR2,
48 X_DESCRIPTION in VARCHAR2,
49 X_CREATION_DATE in DATE,
50 X_CREATED_BY in NUMBER,
51 X_LAST_UPDATE_DATE in DATE,
52 X_LAST_UPDATED_BY in NUMBER,
53 X_LAST_UPDATE_LOGIN in NUMBER
54 ) is
55 cursor C is select ROWID from AS_STATUSES_B
56 where STATUS_CODE = X_STATUS_CODE
57 ;
58 begin
59 insert into AS_STATUSES_B (
60 STATUS_CODE,
61 ENABLED_FLAG,
62 LEAD_FLAG,
63 OPP_FLAG,
64 OPP_OPEN_STATUS_FLAG,
65 OPP_DECISION_DATE_FLAG,
66 STATUS_RANK,
67 FORECAST_ROLLUP_FLAG,
68 WIN_LOSS_INDICATOR,
69 USAGE_INDICATOR,
70 ATTRIBUTE_CATEGORY,
71 ATTRIBUTE1,
72 ATTRIBUTE2,
73 ATTRIBUTE3,
74 ATTRIBUTE4,
75 ATTRIBUTE5,
76 ATTRIBUTE6,
77 ATTRIBUTE7,
78 ATTRIBUTE8,
79 ATTRIBUTE9,
80 ATTRIBUTE10,
81 ATTRIBUTE11,
82 ATTRIBUTE12,
83 ATTRIBUTE13,
84 ATTRIBUTE14,
85 ATTRIBUTE15,
86 CREATION_DATE,
87 CREATED_BY,
88 LAST_UPDATE_DATE,
89 LAST_UPDATED_BY,
90 LAST_UPDATE_LOGIN
91 ) values (
92 X_STATUS_CODE,
93 X_ENABLED_FLAG,
94 X_LEAD_FLAG,
95 X_OPP_FLAG,
96 X_OPP_OPEN_STATUS_FLAG,
97 X_OPP_DECISION_DATE_FLAG,
98 X_STATUS_RANK,
99 X_FORECAST_ROLLUP_FLAG,
100 X_WIN_LOSS_INDICATOR,
101 X_USAGE_INDICATOR,
102 X_ATTRIBUTE_CATEGORY,
103 X_ATTRIBUTE1,
104 X_ATTRIBUTE2,
105 X_ATTRIBUTE3,
106 X_ATTRIBUTE4,
107 X_ATTRIBUTE5,
108 X_ATTRIBUTE6,
109 X_ATTRIBUTE7,
110 X_ATTRIBUTE8,
111 X_ATTRIBUTE9,
112 X_ATTRIBUTE10,
113 X_ATTRIBUTE11,
114 X_ATTRIBUTE12,
115 X_ATTRIBUTE13,
116 X_ATTRIBUTE14,
117 X_ATTRIBUTE15,
118 X_CREATION_DATE,
119 X_CREATED_BY,
120 X_LAST_UPDATE_DATE,
121 X_LAST_UPDATED_BY,
122 X_LAST_UPDATE_LOGIN
123 );
124
125 insert into AS_STATUSES_TL (
126 STATUS_CODE,
127 LAST_UPDATE_DATE,
128 LAST_UPDATED_BY,
129 CREATION_DATE,
130 CREATED_BY,
131 LAST_UPDATE_LOGIN,
132 MEANING,
133 DESCRIPTION,
134 LANGUAGE,
135 SOURCE_LANG
136 ) select
137 X_STATUS_CODE,
138 X_LAST_UPDATE_DATE,
139 X_LAST_UPDATED_BY,
140 X_CREATION_DATE,
141 X_CREATED_BY,
142 X_LAST_UPDATE_LOGIN,
143 X_MEANING,
144 X_DESCRIPTION,
145 L.LANGUAGE_CODE,
146 userenv('LANG')
147 from FND_LANGUAGES L
148 where L.INSTALLED_FLAG in ('I', 'B')
149 and not exists
150 (select NULL
151 from AS_STATUSES_TL T
152 where T.STATUS_CODE = X_STATUS_CODE
153 and T.LANGUAGE = L.LANGUAGE_CODE);
154
155 open c;
156 fetch c into X_ROWID;
157 if (c%notfound) then
158 close c;
159 raise no_data_found;
160 end if;
161 close c;
162
163 end INSERT_ROW;
164
165 procedure LOCK_ROW (
166 X_STATUS_CODE in VARCHAR2,
167 X_ENABLED_FLAG in VARCHAR2,
168 X_LEAD_FLAG in VARCHAR2,
169 X_OPP_FLAG in VARCHAR2,
170 X_OPP_OPEN_STATUS_FLAG in VARCHAR2,
171 X_OPP_DECISION_DATE_FLAG in VARCHAR2,
172 X_STATUS_RANK in NUMBER,
173 X_FORECAST_ROLLUP_FLAG in VARCHAR2,
174 X_WIN_LOSS_INDICATOR in VARCHAR2,
175 X_USAGE_INDICATOR in VARCHAR2,
176 X_ATTRIBUTE_CATEGORY in VARCHAR2,
177 X_ATTRIBUTE1 in VARCHAR2,
178 X_ATTRIBUTE2 in VARCHAR2,
179 X_ATTRIBUTE3 in VARCHAR2,
180 X_ATTRIBUTE4 in VARCHAR2,
181 X_ATTRIBUTE5 in VARCHAR2,
182 X_ATTRIBUTE6 in VARCHAR2,
183 X_ATTRIBUTE7 in VARCHAR2,
184 X_ATTRIBUTE8 in VARCHAR2,
185 X_ATTRIBUTE9 in VARCHAR2,
186 X_ATTRIBUTE10 in VARCHAR2,
187 X_ATTRIBUTE11 in VARCHAR2,
188 X_ATTRIBUTE12 in VARCHAR2,
189 X_ATTRIBUTE13 in VARCHAR2,
190 X_ATTRIBUTE14 in VARCHAR2,
191 X_ATTRIBUTE15 in VARCHAR2,
192 X_MEANING in VARCHAR2,
193 X_DESCRIPTION in VARCHAR2
194 ) is
195 cursor c is select
196 ENABLED_FLAG,
197 LEAD_FLAG,
198 OPP_FLAG,
199 OPP_OPEN_STATUS_FLAG,
200 OPP_DECISION_DATE_FLAG,
201 STATUS_RANK,
202 FORECAST_ROLLUP_FLAG,
203 WIN_LOSS_INDICATOR,
204 USAGE_INDICATOR,
205 ATTRIBUTE_CATEGORY,
206 ATTRIBUTE1,
207 ATTRIBUTE2,
208 ATTRIBUTE3,
209 ATTRIBUTE4,
210 ATTRIBUTE5,
211 ATTRIBUTE6,
212 ATTRIBUTE7,
213 ATTRIBUTE8,
214 ATTRIBUTE9,
215 ATTRIBUTE10,
216 ATTRIBUTE11,
217 ATTRIBUTE12,
218 ATTRIBUTE13,
219 ATTRIBUTE14,
220 ATTRIBUTE15
221 from AS_STATUSES_B
222 where STATUS_CODE = X_STATUS_CODE
223 for update of STATUS_CODE nowait;
224 recinfo c%rowtype;
225
226 cursor c1 is select
227 MEANING,
228 DESCRIPTION,
229 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
230 from AS_STATUSES_TL
231 where STATUS_CODE = X_STATUS_CODE
232 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
233 for update of STATUS_CODE nowait;
234 begin
235 open c;
236 fetch c into recinfo;
237 if (c%notfound) then
238 close c;
239 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
240 app_exception.raise_exception;
241 end if;
242 close c;
243 if ( (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
244 AND ((recinfo.LEAD_FLAG = X_LEAD_FLAG)
245 OR ((recinfo.LEAD_FLAG is null) AND (X_LEAD_FLAG is null)))
246 AND ((recinfo.OPP_FLAG = X_OPP_FLAG)
247 OR ((recinfo.OPP_FLAG is null) AND (X_OPP_FLAG is null)))
248 AND ((recinfo.OPP_OPEN_STATUS_FLAG = X_OPP_OPEN_STATUS_FLAG)
249 OR ((recinfo.OPP_OPEN_STATUS_FLAG is null) AND (X_OPP_OPEN_STATUS_FLAG is null)))
250 AND ((recinfo.OPP_DECISION_DATE_FLAG = X_OPP_DECISION_DATE_FLAG)
251 OR ((recinfo.OPP_DECISION_DATE_FLAG is null) AND (X_OPP_DECISION_DATE_FLAG is null)))
252 AND ((recinfo.STATUS_RANK = X_STATUS_RANK)
253 OR ((recinfo.STATUS_RANK is null) AND (X_STATUS_RANK is null)))
254 AND ((recinfo.FORECAST_ROLLUP_FLAG = X_FORECAST_ROLLUP_FLAG)
255 OR ((recinfo.FORECAST_ROLLUP_FLAG is null) AND (X_FORECAST_ROLLUP_FLAG is null)))
256 AND ((recinfo.WIN_LOSS_INDICATOR = X_WIN_LOSS_INDICATOR)
257 OR ((recinfo.WIN_LOSS_INDICATOR is null) AND (X_WIN_LOSS_INDICATOR is null)))
258 AND ((recinfo.USAGE_INDICATOR = X_USAGE_INDICATOR)
259 OR ((recinfo.USAGE_INDICATOR is null) AND (X_USAGE_INDICATOR is null)))
260 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
261 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
262 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
263 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
264 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
265 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
266 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
267 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
268 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
269 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
270 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
271 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
272 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
273 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
274 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
275 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
276 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
277 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
278 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
279 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
280 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
281 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
282 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
283 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
284 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
285 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
286 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
287 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
288 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
289 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
290 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
291 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
292 ) then
293 null;
294 else
295 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
296 app_exception.raise_exception;
297 end if;
298
299 for tlinfo in c1 loop
300 if (tlinfo.BASELANG = 'Y') then
301 if ( ((tlinfo.MEANING = X_MEANING)
302 OR ((tlinfo.MEANING is null) AND (X_MEANING is null)))
303 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
304 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
305 ) then
306 null;
307 else
308 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
309 app_exception.raise_exception;
310 end if;
311 end if;
312 end loop;
313 return;
314 end LOCK_ROW;
315
316 procedure UPDATE_ROW (
317 X_STATUS_CODE in VARCHAR2,
318 X_ENABLED_FLAG in VARCHAR2,
319 X_LEAD_FLAG in VARCHAR2,
320 X_OPP_FLAG in VARCHAR2,
321 X_OPP_OPEN_STATUS_FLAG in VARCHAR2,
322 X_OPP_DECISION_DATE_FLAG in VARCHAR2,
323 X_STATUS_RANK in NUMBER,
324 X_FORECAST_ROLLUP_FLAG in VARCHAR2,
325 X_WIN_LOSS_INDICATOR in VARCHAR2,
326 X_USAGE_INDICATOR in VARCHAR2,
327 X_ATTRIBUTE_CATEGORY in VARCHAR2,
328 X_ATTRIBUTE1 in VARCHAR2,
329 X_ATTRIBUTE2 in VARCHAR2,
330 X_ATTRIBUTE3 in VARCHAR2,
331 X_ATTRIBUTE4 in VARCHAR2,
332 X_ATTRIBUTE5 in VARCHAR2,
333 X_ATTRIBUTE6 in VARCHAR2,
334 X_ATTRIBUTE7 in VARCHAR2,
335 X_ATTRIBUTE8 in VARCHAR2,
336 X_ATTRIBUTE9 in VARCHAR2,
337 X_ATTRIBUTE10 in VARCHAR2,
338 X_ATTRIBUTE11 in VARCHAR2,
339 X_ATTRIBUTE12 in VARCHAR2,
340 X_ATTRIBUTE13 in VARCHAR2,
341 X_ATTRIBUTE14 in VARCHAR2,
342 X_ATTRIBUTE15 in VARCHAR2,
343 X_MEANING in VARCHAR2,
344 X_DESCRIPTION in VARCHAR2,
345 X_LAST_UPDATE_DATE in DATE,
346 X_LAST_UPDATED_BY in NUMBER,
347 X_LAST_UPDATE_LOGIN in NUMBER
348 ) is
349 l_old_opp_open_status_flag VARCHAR2(1);
350 l_request_id NUMBER;
351 l_module CONSTANT VARCHAR2(255) := 'as.plsql.stapk.UPDATE_ROW';
352 begin
353
354 --Fetch the existing open_status_flag value from as_statuses_b.
355 SELECT opp_open_status_flag INTO l_old_opp_open_status_flag
356 FROM AS_STATUSES_B
357 WHERE ltrim(nls_upper(STATUS_CODE)) = nls_upper(X_STATUS_CODE);
358
359 update AS_STATUSES_B set
360 ENABLED_FLAG = X_ENABLED_FLAG,
361 LEAD_FLAG = X_LEAD_FLAG,
362 OPP_FLAG = X_OPP_FLAG,
363 OPP_OPEN_STATUS_FLAG = X_OPP_OPEN_STATUS_FLAG,
364 OPP_DECISION_DATE_FLAG = X_OPP_DECISION_DATE_FLAG,
365 STATUS_RANK = X_STATUS_RANK,
366 FORECAST_ROLLUP_FLAG = X_FORECAST_ROLLUP_FLAG,
367 WIN_LOSS_INDICATOR = X_WIN_LOSS_INDICATOR,
368 USAGE_INDICATOR = X_USAGE_INDICATOR,
369 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
370 ATTRIBUTE1 = X_ATTRIBUTE1,
371 ATTRIBUTE2 = X_ATTRIBUTE2,
372 ATTRIBUTE3 = X_ATTRIBUTE3,
373 ATTRIBUTE4 = X_ATTRIBUTE4,
374 ATTRIBUTE5 = X_ATTRIBUTE5,
375 ATTRIBUTE6 = X_ATTRIBUTE6,
376 ATTRIBUTE7 = X_ATTRIBUTE7,
377 ATTRIBUTE8 = X_ATTRIBUTE8,
378 ATTRIBUTE9 = X_ATTRIBUTE9,
379 ATTRIBUTE10 = X_ATTRIBUTE10,
380 ATTRIBUTE11 = X_ATTRIBUTE11,
381 ATTRIBUTE12 = X_ATTRIBUTE12,
382 ATTRIBUTE13 = X_ATTRIBUTE13,
383 ATTRIBUTE14 = X_ATTRIBUTE14,
384 ATTRIBUTE15 = X_ATTRIBUTE15,
385 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
386 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
387 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
388 where STATUS_CODE = X_STATUS_CODE;
389
390 if (sql%notfound) then
391 raise no_data_found;
392 end if;
393
394 update AS_STATUSES_TL set
395 MEANING = X_MEANING,
396 DESCRIPTION = X_DESCRIPTION,
397 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
398 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
399 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
400 SOURCE_LANG = userenv('LANG')
401 where STATUS_CODE = X_STATUS_CODE
402 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
403
404 if (sql%notfound) then
405 raise no_data_found;
406 end if;
407
408 end UPDATE_ROW;
409
410 procedure DELETE_ROW (
411 X_STATUS_CODE in VARCHAR2
412 ) is
413 begin
414 delete from AS_STATUSES_TL
415 where STATUS_CODE = X_STATUS_CODE;
416
417 if (sql%notfound) then
418 raise no_data_found;
419 end if;
420
421 delete from AS_STATUSES_B
422 where STATUS_CODE = X_STATUS_CODE;
423
424 if (sql%notfound) then
425 raise no_data_found;
426 end if;
427 end DELETE_ROW;
428
429 procedure LOAD_ROW (
430 X_STATUS_CODE in VARCHAR2,
431 X_ENABLED_FLAG in VARCHAR2,
432 X_LEAD_FLAG in VARCHAR2,
433 X_OPP_FLAG in VARCHAR2,
434 X_OPP_OPEN_STATUS_FLAG in VARCHAR2,
435 X_OPP_DECISION_DATE_FLAG in VARCHAR2,
436 X_STATUS_RANK in NUMBER,
437 X_FORECAST_ROLLUP_FLAG in VARCHAR2,
441 X_ATTRIBUTE1 in VARCHAR2,
438 X_WIN_LOSS_INDICATOR in VARCHAR2,
439 X_USAGE_INDICATOR in VARCHAR2,
440 X_ATTRIBUTE_CATEGORY in VARCHAR2,
442 X_ATTRIBUTE2 in VARCHAR2,
443 X_ATTRIBUTE3 in VARCHAR2,
444 X_ATTRIBUTE4 in VARCHAR2,
445 X_ATTRIBUTE5 in VARCHAR2,
446 X_ATTRIBUTE6 in VARCHAR2,
447 X_ATTRIBUTE7 in VARCHAR2,
448 X_ATTRIBUTE8 in VARCHAR2,
449 X_ATTRIBUTE9 in VARCHAR2,
450 X_ATTRIBUTE10 in VARCHAR2,
451 X_ATTRIBUTE11 in VARCHAR2,
452 X_ATTRIBUTE12 in VARCHAR2,
453 X_ATTRIBUTE13 in VARCHAR2,
454 X_ATTRIBUTE14 in VARCHAR2,
455 X_ATTRIBUTE15 in VARCHAR2,
456 X_MEANING in VARCHAR2,
457 X_DESCRIPTION in VARCHAR2,
458 X_CUSTOM in VARCHAR2,
459 X_OWNER in VARCHAR2
460 )
461 IS
462 begin
463 declare
464 user_id number := 0;
465 row_id varchar2(64);
466
467 cursor custom_exist(p_status_code VARCHAR2) is
468 select 'Y'
469 from AS_STATUSES_B
470 where last_updated_by <> 1
471 and status_code = p_STATUS_CODE;
472
473 l_custom_exist varchar2(1) := 'N';
474
475
476 begin
477 If nvl(X_CUSTOM,'NONCUSTOM') = 'FORCE'
478 then l_custom_exist := 'N';
479 else
480 OPEN custom_exist(X_STATUS_CODE);
481 FETCH custom_exist into l_custom_exist;
482 CLOSE custom_exist;
483 end if;
484 IF nvl(l_custom_exist, 'N') = 'N' THEN
485
486 if (X_OWNER = 'SEED') then
487 user_id := 1;
488 end if;
489
490 begin
491 AS_STATUSES_PKG.UPDATE_ROW(
492 X_STATUS_CODE => X_STATUS_CODE,
493 X_ENABLED_FLAG => X_ENABLED_FLAG,
494 X_LEAD_FLAG => X_LEAD_FLAG,
495 X_OPP_FLAG => X_OPP_FLAG,
496 X_OPP_OPEN_STATUS_FLAG => X_OPP_OPEN_STATUS_FLAG,
497 X_OPP_DECISION_DATE_FLAG => X_OPP_DECISION_DATE_FLAG,
498 X_STATUS_RANK => X_STATUS_RANK,
499 X_FORECAST_ROLLUP_FLAG => X_FORECAST_ROLLUP_FLAG,
500 X_WIN_LOSS_INDICATOR => X_WIN_LOSS_INDICATOR,
501 X_USAGE_INDICATOR => X_USAGE_INDICATOR,
502 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
503 X_ATTRIBUTE1 => X_ATTRIBUTE1,
504 X_ATTRIBUTE2 => X_ATTRIBUTE2,
505 X_ATTRIBUTE3 => X_ATTRIBUTE3,
506 X_ATTRIBUTE4 => X_ATTRIBUTE4,
507 X_ATTRIBUTE5 => X_ATTRIBUTE5,
508 X_ATTRIBUTE6 => X_ATTRIBUTE6,
509 X_ATTRIBUTE7 => X_ATTRIBUTE7,
510 X_ATTRIBUTE8 => X_ATTRIBUTE8,
511 X_ATTRIBUTE9 => X_ATTRIBUTE9,
512 X_ATTRIBUTE10 => X_ATTRIBUTE10,
513 X_ATTRIBUTE11 => X_ATTRIBUTE11,
514 X_ATTRIBUTE12 => X_ATTRIBUTE12,
515 X_ATTRIBUTE13 => X_ATTRIBUTE13,
516 X_ATTRIBUTE14 => X_ATTRIBUTE14,
517 X_ATTRIBUTE15 => X_ATTRIBUTE15,
518 X_MEANING => X_MEANING,
519 X_DESCRIPTION => X_DESCRIPTION,
520 X_LAST_UPDATE_DATE => sysdate,
521 X_LAST_UPDATED_BY => user_id,
522 X_LAST_UPDATE_LOGIN => 0
523 );
524
525 exception
526 when NO_DATA_FOUND then
527 AS_STATUSES_PKG.INSERT_ROW(
528 X_ROWID => row_id,
529 X_STATUS_CODE => X_STATUS_CODE,
530 X_ENABLED_FLAG => X_ENABLED_FLAG,
531 X_LEAD_FLAG => X_LEAD_FLAG,
532 X_OPP_FLAG => X_OPP_FLAG,
533 X_OPP_OPEN_STATUS_FLAG => X_OPP_OPEN_STATUS_FLAG,
534 X_OPP_DECISION_DATE_FLAG => X_OPP_DECISION_DATE_FLAG,
535 X_STATUS_RANK => X_STATUS_RANK,
536 X_FORECAST_ROLLUP_FLAG => X_FORECAST_ROLLUP_FLAG,
537 X_WIN_LOSS_INDICATOR => X_WIN_LOSS_INDICATOR,
538 X_USAGE_INDICATOR => X_USAGE_INDICATOR,
539 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
540 X_ATTRIBUTE1 => X_ATTRIBUTE1,
541 X_ATTRIBUTE2 => X_ATTRIBUTE2,
542 X_ATTRIBUTE3 => X_ATTRIBUTE3,
543 X_ATTRIBUTE4 => X_ATTRIBUTE4,
544 X_ATTRIBUTE5 => X_ATTRIBUTE5,
545 X_ATTRIBUTE6 => X_ATTRIBUTE6,
546 X_ATTRIBUTE7 => X_ATTRIBUTE7,
547 X_ATTRIBUTE8 => X_ATTRIBUTE8,
548 X_ATTRIBUTE9 => X_ATTRIBUTE9,
549 X_ATTRIBUTE10 => X_ATTRIBUTE10,
550 X_ATTRIBUTE11 => X_ATTRIBUTE11,
551 X_ATTRIBUTE12 => X_ATTRIBUTE12,
552 X_ATTRIBUTE13 => X_ATTRIBUTE13,
553 X_ATTRIBUTE14 => X_ATTRIBUTE14,
554 X_ATTRIBUTE15 => X_ATTRIBUTE15,
555 X_MEANING => X_MEANING,
556 X_DESCRIPTION => X_DESCRIPTION,
557 X_CREATION_DATE => sysdate,
558 X_CREATED_BY => 0,
559 X_LAST_UPDATE_DATE => sysdate,
560 X_LAST_UPDATED_BY => user_id,
561 X_LAST_UPDATE_LOGIN => 0
565
562 );
563
564 end;
566 END IF;
567
568 end;
569 end LOAD_ROW;
570
571 procedure ADD_LANGUAGE
572 is
573 begin
574 delete from AS_STATUSES_TL T
575 where not exists
576 (select NULL
577 from AS_STATUSES_B B
578 where B.STATUS_CODE = T.STATUS_CODE
579 );
580
581 update AS_STATUSES_TL T set (
582 MEANING,
583 DESCRIPTION
584 ) = (select
585 B.MEANING,
586 B.DESCRIPTION
587 from AS_STATUSES_TL B
588 where B.STATUS_CODE = T.STATUS_CODE
589 and B.LANGUAGE = T.SOURCE_LANG)
590 where (
591 T.STATUS_CODE,
592 T.LANGUAGE
593 ) in (select
594 SUBT.STATUS_CODE,
595 SUBT.LANGUAGE
596 from AS_STATUSES_TL SUBB, AS_STATUSES_TL SUBT
597 where SUBB.STATUS_CODE = SUBT.STATUS_CODE
598 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
599 and (SUBB.MEANING <> SUBT.MEANING
600 or (SUBB.MEANING is null and SUBT.MEANING is not null)
601 or (SUBB.MEANING is not null and SUBT.MEANING is null)
602 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
603 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
604 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
605 ));
606
607 insert into AS_STATUSES_TL (
608 STATUS_CODE,
609 LAST_UPDATE_DATE,
610 LAST_UPDATED_BY,
611 CREATION_DATE,
612 CREATED_BY,
613 LAST_UPDATE_LOGIN,
614 MEANING,
615 DESCRIPTION,
616 LANGUAGE,
617 SOURCE_LANG
618 ) select
619 B.STATUS_CODE,
620 B.LAST_UPDATE_DATE,
621 B.LAST_UPDATED_BY,
622 B.CREATION_DATE,
623 B.CREATED_BY,
624 B.LAST_UPDATE_LOGIN,
625 B.MEANING,
626 B.DESCRIPTION,
627 L.LANGUAGE_CODE,
628 B.SOURCE_LANG
629 from AS_STATUSES_TL B, FND_LANGUAGES L
630 where L.INSTALLED_FLAG in ('I', 'B')
631 and B.LANGUAGE = userenv('LANG')
632 and not exists
633 (select NULL
634 from AS_STATUSES_TL T
635 where T.STATUS_CODE = B.STATUS_CODE
636 and T.LANGUAGE = L.LANGUAGE_CODE);
637 end ADD_LANGUAGE;
638
639
640 procedure TRANSLATE_ROW (
641 X_STATUS_CODE in VARCHAR2,
642 X_DESCRIPTION in VARCHAR2,
643 X_MEANING in VARCHAR2,
644 X_OWNER in VARCHAR2)
645 IS
646 begin
647 -- only update rows that have not been altered by user
648 update AS_STATUSES_TL
649 set description = X_DESCRIPTION,
650 meaning = X_MEANING,
651 source_lang = userenv('LANG'),
652 last_update_date = sysdate,
653 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
654 last_update_login = 0
655 where status_code = X_STATUS_CODE
656 and userenv('LANG') in (language, source_lang);
657 end TRANSLATE_ROW;
658
659
660
661
662 PROCEDURE PRE_UPDATE(
663 ERRBUF OUT NOCOPY VARCHAR2,
664 RETCODE OUT NOCOPY VARCHAR2) IS
665 l_status BOOLEAN;
666 l_module CONSTANT VARCHAR2(255) := 'as.plsql.stapk.PRE_UPDATE';
667 BEGIN
668 -- Load the schema name first
669 Load_Schema_Name;
670
671 --Create temporary indexes for leads
672 Create_Temp_Index('AS_SALES_LEADS','SALES_LEAD_ID,STATUS_CODE,CLOSE_REASON',G_INDEX_SUFFIX1);
673 Create_Temp_Index('AS_ACCESSES_ALL_ALL','ACCESS_ID,DELETE_FLAG,SALES_LEAD_ID',G_INDEX_SUFFIX1);
674
675 --Create temporary indexes for opportunities
676 Create_Temp_Index('AS_LEADS_ALL','LEAD_ID,STATUS,CLOSE_REASON',G_INDEX_SUFFIX2);
677 Create_Temp_Index('AS_ACCESSES_ALL_ALL','ACCESS_ID,DELETE_FLAG,LEAD_ID',G_INDEX_SUFFIX2);
678 EXCEPTION
679 WHEN others THEN
680 ERRBUF := SQLERRM;
681 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
682 ROLLBACK;
683 Write_Log(l_module, 1, 'Exception: Problem in index creation.');
684 Write_Log(l_module, 1, 'SQLCODE ' || to_char(SQLCODE) ||
685 ' SQLERRM ' || substr(SQLERRM, 1, 100));
686 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
687 IF l_status = TRUE THEN
688 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
689 END IF ;
690 END PRE_UPDATE;
691
692 PROCEDURE POST_UPDATE(
693 ERRBUF OUT NOCOPY VARCHAR2,
694 RETCODE OUT NOCOPY VARCHAR2) IS
695 BEGIN
696 -- Drop temporary index for Leads
697 Drop_Temp_Index('AS_SALES_LEADS',G_INDEX_SUFFIX1);
698 Drop_Temp_Index('AS_ACCESSES_ALL_ALL',G_INDEX_SUFFIX1);
699
700 -- Drop temporary index for Opportunities
701 Drop_Temp_Index('AS_LEADS_ALL',G_INDEX_SUFFIX2);
702 Drop_Temp_Index('AS_ACCESSES_ALL_ALL',G_INDEX_SUFFIX2);
703 EXCEPTION when others then
704 null;
705 END POST_UPDATE;
706
707 PROCEDURE Write_Log(p_module varchar2, p_which number, p_mssg varchar2) IS
708 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
709 BEGIN
710 IF l_debug THEN
711 AS_UTILITY_PVT.Debug_Message(p_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, p_mssg);
712 ELSE
716 END Write_Log;
713 FND_FILE.put(p_which, p_mssg);
714 FND_FILE.NEW_LINE(p_which, 1);
715 END IF;
717
718
719 PROCEDURE Load_Schema_Name IS
720 l_status VARCHAR2(2);
721 l_industry VARCHAR2(2);
722 l_oracle_schema VARCHAR2(32) := 'OSM';
723 l_schema_return BOOLEAN;
724 BEGIN
725 if (G_SCHEMA_NAME is null) then
726 l_schema_return := FND_INSTALLATION.get_app_info('AS', l_status, l_industry, l_oracle_schema);
727 G_SCHEMA_NAME := l_oracle_schema;
728 end if;
729 END;
730
731 PROCEDURE Create_Temp_Index(p_table IN VARCHAR2,
732 p_index_columns IN VARCHAR2,
733 p_index_suffix IN VARCHAR2) IS
734 l_check_tspace_exist varchar2(100);
735 l_index_tablespace varchar2(100);
736 l_sql_stmt varchar2(2000);
737 l_user varchar2(2000);
738 l_index_name varchar2(100);
739 l_dop NUMBER;
740
741
742
743 begin
744
745 SELECT MIN(TO_NUMBER(v.value))
746 INTO l_dop
747 FROM v$parameter v
748 WHERE v.name = 'parallel_max_servers'
749 OR v.name = 'cpu_count';
750
751
752
753
754 --execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
755
756 -----------------
757 -- Create index--
758 -----------------
759
760 l_user := USER;
761
762 -- Name for temporary index created for migration
763 l_index_name := p_table || p_index_suffix;
764
765 AD_TSPACE_UTIL.get_tablespace_name('AS', 'TRANSACTION_INDEXES','N',l_check_tspace_exist,l_index_tablespace);
766
767 l_sql_stmt := 'create index ' || l_index_name || ' on '
768 || G_SCHEMA_NAME||'.'
769 || p_table || '(' || p_index_columns || ') '
770 ||' tablespace ' || l_index_tablespace || ' nologging '
771 ||'parallel '||l_dop;
772 execute immediate l_sql_stmt;
773
774 --------------------
775 -- convert to no||--
776 --------------------
777 l_sql_stmt := 'alter index '|| l_user ||'.' || l_index_name || ' noparallel ';
778 execute immediate l_sql_stmt;
779
780
781 -----------------
782 -- Gather Stats--
783 -----------------
784 --Code commented for performance bug#5802537-- by lester
785 --dbms_stats.gather_index_stats(l_user,l_index_name,estimate_percent => 10);
786 END Create_Temp_Index;
787
788 PROCEDURE Drop_Temp_Index(p_table IN VARCHAR2,
789 p_index_suffix IN VARCHAR2) IS
790 l_sql_stmt varchar2(2000);
791 l_index_name varchar2(100);
792 l_user varchar2(2000);
793 begin
794 -----------------
795 -- Drop index --
796 -----------------
797 l_user := USER;
798
799 -- Name for temporary index created for migration
800 l_index_name := p_table || p_index_suffix;
801
802 l_sql_stmt := 'drop index ' || l_user||'.' || l_index_name || ' ';
803
804 execute immediate l_sql_stmt;
805 END Drop_Temp_Index;
806
807 PROCEDURE update_accesses_Main
808 (
809 errbuf OUT NOCOPY VARCHAR2,
810 retcode OUT NOCOPY NUMBER,
811 x_open_flag IN VARCHAR2,
812 x_status_code IN VARCHAR2,
813 p_num_workers IN NUMBER,
814 p_batch_size IN NUMBER,
815 p_debug_flag IN VARCHAR2
816 )
817 IS
818 l_api_name CONSTANT VARCHAR2(30) :=
819 'update_accesses_Main';
820 l_module_name CONSTANT VARCHAR2(256) :=
821 'as.plsql.as_statuses_pkg.update_accesses_Main';
822 l_msg_count NUMBER;
823 l_msg_data VARCHAR2(2000);
824 l_req_id NUMBER;
825 l_request_data VARCHAR2(30);
826 l_max_num_rows NUMBER;
827 l_rows_per_worker NUMBER;
828 l_start_id NUMBER;
829 l_end_id NUMBER;
830 l_batch_size CONSTANT NUMBER := 10000;
831
832 CURSOR c1 IS
833 select AS_ACCESSES_S.nextval
834 from dual;
835
836 CURSOR Get_AC_Min_Id IS
837 select min(access_id)
838 from as_accesses_all_all;
839
840 BEGIN
841
842 --
843 -- If this is first time parent is called, then split the rows
844 -- among workers and put the parent in paused state
845 --
846 IF (fnd_conc_global.request_data IS NULL) THEN
847
848 -- Log
849 IF (p_debug_flag = 'Y' AND
850 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
851 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
852 'Start:' || 'p_num_workers=' || p_num_workers ||
853 ',p_debug_flag=' || p_debug_flag);
854 END IF;
855
856
857 --
858 -- Get maximum number of possible rows in as_leads_all
859 --
860
864 open Get_AC_Min_Id;
861 -- Initialize start ID value
862 l_start_id := 0;
863
865 fetch Get_AC_Min_Id into l_start_id;
866 close Get_AC_Min_Id;
867
868 OPEN c1;
869 FETCH c1 INTO l_max_num_rows;
870 CLOSE c1;
871
872 --
873 -- Compute row range to be assigned to each worker
874 --
875 l_rows_per_worker := ROUND((l_max_num_rows -l_start_id) /p_num_workers) + 1;
876
877 --
878 -- Assign rows to each worker
879 --
880
881
882
883
884 FOR i IN 1..p_num_workers LOOP
885
886 -- Initialize end ID value
887 l_end_id := l_start_id + l_rows_per_worker;
888
889 IF (p_debug_flag = 'Y' AND
890 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
891 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
892 'Submitting child:' || 'Worker ID=' || i ||
893 ' ,Start ID =' || l_start_id ||
894 ',End ID =' || l_end_id);
895 END IF;
896
897
898
899 -- Submit the request
900 l_req_id :=
901 fnd_request.submit_request
902 (
903 application => 'AS',
904 program => 'ASXADFLOS',
905 description => null,
906 start_time => sysdate,
907 sub_request => true,
908 argument1 => x_open_flag,
909 argument2 => x_status_code,
910 argument3 => l_start_id,
911 argument4 => l_end_id,
912 argument5 => NVL(p_batch_size,10000),
913 argument6 => p_debug_flag,
914 argument7 => CHR(0),
915 argument8 => CHR(0),
916 argument9 => CHR(0),
917 argument10 => CHR(0),
918 argument11 => CHR(0),
919 argument12 => CHR(0),
920 argument13 => CHR(0),
921 argument14 => CHR(0),
922 argument15 => CHR(0),
923 argument16 => CHR(0),
924 argument17 => CHR(0),
925 argument18 => CHR(0),
926 argument19 => CHR(0),
927 argument20 => CHR(0),
928 argument21 => CHR(0),
929 argument22 => CHR(0),
930 argument23 => CHR(0),
931 argument24 => CHR(0),
932 argument25 => CHR(0),
933 argument26 => CHR(0),
934 argument27 => CHR(0),
935 argument28 => CHR(0),
936 argument29 => CHR(0),
937 argument30 => CHR(0),
938 argument31 => CHR(0),
939 argument32 => CHR(0),
940 argument33 => CHR(0),
941 argument34 => CHR(0),
942 argument35 => CHR(0),
943 argument36 => CHR(0),
944 argument37 => CHR(0),
945 argument38 => CHR(0),
946 argument39 => CHR(0),
947 argument40 => CHR(0),
948 argument41 => CHR(0),
949 argument42 => CHR(0),
950 argument43 => CHR(0),
951 argument44 => CHR(0),
952 argument45 => CHR(0),
953 argument46 => CHR(0),
954 argument47 => CHR(0),
955 argument48 => CHR(0),
956 argument49 => CHR(0),
957 argument50 => CHR(0),
958 argument51 => CHR(0),
959 argument52 => CHR(0),
960 argument53 => CHR(0),
961 argument54 => CHR(0),
962 argument55 => CHR(0),
963 argument56 => CHR(0),
964 argument57 => CHR(0),
965 argument58 => CHR(0),
966 argument59 => CHR(0),
967 argument60 => CHR(0),
968 argument61 => CHR(0),
969 argument62 => CHR(0),
970 argument63 => CHR(0),
971 argument64 => CHR(0),
972 argument65 => CHR(0),
973 argument66 => CHR(0),
974 argument67 => CHR(0),
975 argument68 => CHR(0),
976 argument69 => CHR(0),
977 argument70 => CHR(0),
978 argument71 => CHR(0),
979 argument72 => CHR(0),
980 argument73 => CHR(0),
981 argument74 => CHR(0),
982 argument75 => CHR(0),
983 argument76 => CHR(0),
984 argument77 => CHR(0),
985 argument78 => CHR(0),
986 argument79 => CHR(0),
987 argument80 => CHR(0),
988 argument81 => CHR(0),
989 argument82 => CHR(0),
990 argument83 => CHR(0),
991 argument84 => CHR(0),
992 argument85 => CHR(0),
993 argument86 => CHR(0),
994 argument87 => CHR(0),
995 argument88 => CHR(0),
996 argument89 => CHR(0),
997 argument90 => CHR(0),
998 argument91 => CHR(0),
999 argument92 => CHR(0),
1000 argument93 => CHR(0),
1001 argument94 => CHR(0),
1002 argument95 => CHR(0),
1003 argument96 => CHR(0),
1004 argument97 => CHR(0),
1005 argument98 => CHR(0),
1006 argument99 => CHR(0),
1007 argument100 => CHR(0)
1008 );
1009
1010 --
1011 -- If request submission failed, exit with error.
1012 --
1013 IF (l_req_id = 0) THEN
1014
1018
1015 errbuf := fnd_message.get;
1016 retcode := 2;
1017 RETURN;
1019 END IF;
1020
1021 -- Set start ID value
1022 l_start_id := l_end_id ;
1023
1024 END LOOP; -- end i
1025
1026 --
1027 -- After submitting request for all workers, put the parent
1028 -- in paused state. When all children are done, the parent
1029 -- would be called again, and then it will terminate
1030 --
1031 fnd_conc_global.set_req_globals
1032 (
1033 conc_status => 'PAUSED',
1034 request_data => to_char(l_req_id)
1035 -- conc_restart_time => to_char(sysdate)
1036 -- release_sub_request => 'N'
1037 );
1038
1039 ELSE
1040
1041 -- Log
1042 IF (p_debug_flag = 'Y' AND
1043 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1044 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1045 'Re-entering:' || 'p_num_workers=' || p_num_workers ||
1046 ',p_debug_flag='||p_debug_flag);
1047 END IF;
1048
1049
1050 errbuf := 'Migration completed';
1051 retcode := 0;
1052
1053 -- Log
1054 IF (p_debug_flag = 'Y' AND
1055 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1056 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1057 'Done:' || 'p_num_workers=' || p_num_workers ||
1058 ',p_debug_flag='||p_debug_flag);
1059 END IF;
1060
1061 END IF;
1062
1063 EXCEPTION
1064
1065 WHEN OTHERS THEN
1066 ROLLBACK;
1067
1068 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1069
1070 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1071 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1072 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1073 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1074 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, l_module_name, true);
1075 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1076 l_api_name||':'||sqlcode||':'||sqlerrm);
1077 END IF;
1078
1079 END update_accesses_Main;
1080
1081
1082
1083 procedure UPDATE_ACCESSES_SUB(
1084 ERRBUF OUT NOCOPY VARCHAR2,
1085 RETCODE OUT NOCOPY VARCHAR2,
1086 x_open_flag IN VARCHAR2,
1087 x_status_code IN VARCHAR2,
1088 p_start_id IN VARCHAR2,
1089 p_end_id IN VARCHAR2,
1090 p_batch_size IN NUMBER,
1091 p_debug_flag IN VARCHAR2
1092 )
1093 IS
1094 l_count NUMBER := 0;
1095 l_min_id NUMBER := 0;
1096 l_max_id NUMBER := 0;
1097 l_debug BOOLEAN := false;
1098 l_last_updated_by NUMBER := fnd_global.user_id;
1099 l_last_update_login NUMBER := fnd_global.conc_login_id;
1100 G_BATCH_SIZE NUMBER ;
1101 l_lead_flag VARCHAR2(1);
1102 l_opp_flag VARCHAR2(1);
1103 l_status BOOLEAN;
1104
1105
1106 CURSOR Get_flag_from_Input(c_in_param_1 VARCHAR2,c_in_param_2 VARCHAR2) IS
1107 select NVL(LEAD_FLAG,'N'),NVL(OPP_FLAG,'N')
1108 from AS_STATUSES_B
1109 where status_code = c_in_param_2
1110 and UPPER(opp_open_status_flag) = UPPER(c_in_param_1);
1111
1112 l_module CONSTANT VARCHAR2(255) := 'as.plsql.stapk.UPDATE_ACCESSES_SUB';
1113
1114 BEGIN
1115
1116 G_BATCH_SIZE := NVL(p_batch_size,10000);
1117
1118 IF Upper(X_STATUS_CODE) <> 'ALL' AND UPPER(NVL(X_OPEN_FLAG,'X')) NOT IN ('Y','N') THEN
1119 Write_log (l_module, 1, 'Invalid input for Status Flag!');
1120 RAISE FND_API.G_EXC_ERROR;
1121 END IF;
1122
1123 IF Upper(X_STATUS_CODE) <> 'ALL' THEN
1124 Open Get_flag_from_Input(X_OPEN_FLAG,X_STATUS_CODE);
1125 Fetch Get_flag_from_Input INTO l_lead_flag,l_opp_flag;
1126 IF (Get_flag_from_Input%NOTFOUND) THEN
1127 Write_log (l_module, 1, 'Combination of Status Code and Open Flag is not valid! Please note that the status code is case sensitive.');
1128 Close Get_flag_from_Input;
1129 RAISE FND_API.G_EXC_ERROR;
1130 ELSE
1131 Close Get_flag_from_Input;
1132 END IF;
1133 END IF;
1134
1135 --Get Min,Max Ids
1136
1137 l_min_id := p_start_id;
1138 l_max_id := p_end_id;
1139
1140 IF (p_debug_flag = 'Y') THEN
1141 l_debug := TRUE;
1142 ELSE
1143 l_debug := FALSE;
1144 END IF;
1145
1146
1147 if (l_debug) Then
1148 FND_FILE.PUT_LINE(FND_FILE.LOG,'Minimum Id - Max id for as_accesses_all_all for this worker :' || l_min_id ||' to '||l_max_id);
1149 end if;
1150
1151
1152
1153 -- Initialize counter
1154 l_count := l_min_id;
1155
1156 while (l_count <= l_max_id)
1157 loop
1158 if (l_debug) Then
1159 FND_FILE.PUT_LINE(FND_FILE.LOG,'Current loop count:' || l_count);
1160 end if;
1161 -- update statements here
1162 IF Upper(X_STATUS_CODE) <> 'ALL' THEN
1163 UPDATE /*+ INDEX(acc AS_ACCESSES_ALL_ALL_MT1)*/ AS_ACCESSES_ALL acc
1167 acc.last_updated_by = l_last_updated_by,
1164 SET object_version_number = nvl(object_version_number,0) + 1,
1165 acc.OPEN_FLAG = Upper(X_OPEN_FLAG),
1166 acc.last_update_date = sysdate,
1168 acc.last_update_login = l_last_update_login
1169 WHERE acc.ACCESS_ID >= l_count
1170 AND acc.ACCESS_ID < l_count+G_BATCH_SIZE
1171 AND acc.ACCESS_ID < l_max_id
1172 AND (l_lead_flag = 'Y' AND EXISTS
1173 ( SELECT /*+ INDEX(slds AS_SALES_LEADS_MT1)*/ 1
1174 FROM AS_SALES_LEADS slds
1175 WHERE slds.sales_lead_id = acc.sales_lead_id
1176 AND slds.status_code = X_STATUS_CODE
1177 AND acc.sales_lead_id is not null )
1178 )
1179 AND acc.sales_lead_id is not null;
1180
1181
1182 UPDATE /*+ INDEX(acc AS_ACCESSES_ALL_ALL_MT2)*/ AS_ACCESSES_ALL acc
1183 SET object_version_number = nvl(object_version_number,0) + 1,
1184 acc.OPEN_FLAG = Upper(X_OPEN_FLAG),
1185 acc.last_update_date = sysdate,
1186 acc.last_updated_by = l_last_updated_by,
1187 acc.last_update_login = l_last_update_login
1188 WHERE acc.ACCESS_ID >= l_count
1189 AND acc.ACCESS_ID < l_count+G_BATCH_SIZE
1190 AND acc.ACCESS_ID < l_max_id
1191 AND (l_opp_flag = 'Y' AND EXISTS
1192 ( SELECT /*+ INDEX(lds AS_LEADS_ALL_MT2)*/ 1
1193 FROM AS_LEADS_ALL lds
1194 WHERE lds.lead_id = acc.lead_id
1195 AND lds.status = X_STATUS_CODE
1196 AND acc.lead_id is not null ))
1197 AND acc.lead_id is not null;
1198
1199 ELSE
1200 UPDATE /*+ INDEX(acc AS_ACCESSES_ALL_ALL_MT1)*/ AS_ACCESSES_ALL acc
1201 SET object_version_number = nvl(object_version_number,0) + 1,
1202 acc.OPEN_FLAG = (SELECT /*+ INDEX(slds AS_SALES_LEADS_MT1)*/ st.opp_open_status_flag
1203 FROM AS_STATUSES_B st,AS_SALES_LEADS slds
1204 WHERE st.status_code = slds.status_code
1205 AND slds.sales_lead_id = acc.sales_lead_id
1206 AND acc.sales_lead_id is not null
1207 AND st.lead_flag = 'Y'),
1208 acc.last_update_date = sysdate,
1209 acc.last_updated_by = l_last_updated_by,
1210 acc.last_update_login = l_last_update_login
1211 WHERE acc.ACCESS_ID >= l_count
1212 AND acc.ACCESS_ID < l_count+G_BATCH_SIZE
1213 AND acc.ACCESS_ID < l_max_id
1214 AND acc.sales_lead_id is not null;
1215
1216
1217
1218 UPDATE /*+ INDEX(acc AS_ACCESSES_ALL_ALL_MT2)*/ AS_ACCESSES_ALL acc
1219 SET object_version_number = nvl(object_version_number,0) + 1,
1220 acc.OPEN_FLAG = (SELECT /*+ INDEX(lds AS_LEADS_ALL_MT2)*/ st.opp_open_status_flag
1221 FROM AS_STATUSES_B st,AS_LEADS_ALL lds
1222 WHERE st.status_code = lds.status
1223 AND lds.lead_id = acc.lead_id
1224 AND acc.lead_id is not null
1225 AND st.opp_flag = 'Y'),
1226 acc.last_update_date = sysdate,
1227 acc.last_updated_by = l_last_updated_by,
1228 acc.last_update_login = l_last_update_login
1229 WHERE acc.ACCESS_ID >= l_count
1230 AND acc.ACCESS_ID < l_count+G_BATCH_SIZE
1231 AND acc.ACCESS_ID < l_max_id
1232 AND acc.lead_id is not null;
1233 END IF;
1234 -- end update statements here
1235 commit;
1236
1237 l_count := l_count + G_BATCH_SIZE;
1238 end loop;
1239 commit;
1240
1241
1242 if l_debug then
1243 FND_FILE.PUT_LINE(FND_FILE.LOG,'Update of denormed open flag in as_accesses_all_all (for leads and opps) finished successfully');
1244 FND_FILE.PUT_LINE(FND_FILE.LOG,'for range :'|| l_min_id ||' to '||l_max_id);
1245 FND_FILE.PUT_LINE(FND_FILE.LOG,'End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1246 end if;
1247
1248
1249 EXCEPTION
1250 WHEN FND_API.G_EXC_ERROR THEN
1251 ERRBUF := ERRBUF || sqlerrm;
1252 RETCODE := FND_API.G_RET_STS_ERROR;
1253 ROLLBACK;
1254 Write_log (l_module, 1, 'Error in as_statuses_pkg.update_oppty_accesses');
1255 Write_log (l_module, 1, 'SQLCODE ' || to_char(SQLCODE) ||
1256 ' SQLERRM ' || substr(SQLERRM, 1, 100));
1257 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
1258 IF l_status = TRUE THEN
1259 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
1260 END IF ;
1264 ROLLBACK;
1261 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1262 ERRBUF := ERRBUF||sqlerrm;
1263 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
1265 Write_Log(l_module, 1, 'Unexpected error in as_statuses_pkg.update_oppty_accesses');
1266 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
1267 IF l_status = TRUE THEN
1268 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
1269 END IF ;
1270 WHEN others THEN
1271 ERRBUF := SQLERRM;
1272 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
1273 ROLLBACK;
1274 Write_Log(l_module, 1, 'Exception: others in as_statuses_pkg.update_oppty_accesses');
1275 Write_Log(l_module, 1, 'SQLCODE ' || to_char(SQLCODE) ||
1276 ' SQLERRM ' || substr(SQLERRM, 1, 100));
1277 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
1278 IF l_status = TRUE THEN
1279 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
1280 END IF ;
1281 end UPDATE_ACCESSES_SUB;
1282
1283
1284
1285
1286 PROCEDURE update_leads_Main
1287 (
1288 errbuf OUT NOCOPY VARCHAR2,
1289 retcode OUT NOCOPY NUMBER,
1290 x_open_flag IN VARCHAR2,
1291 x_status_code IN VARCHAR2,
1292 p_num_workers IN NUMBER,
1293 p_batch_size IN NUMBER,
1294 p_debug_flag IN VARCHAR2
1295 )
1296 IS
1297 l_api_name CONSTANT VARCHAR2(30) :=
1298 'update_leads_Main';
1299 l_module_name CONSTANT VARCHAR2(256) :=
1300 'as.plsql.as_statuses_pkg.update_leads_Main';
1301 l_msg_count NUMBER;
1302 l_msg_data VARCHAR2(2000);
1303 l_req_id NUMBER;
1304 l_request_data VARCHAR2(30);
1305 l_max_num_rows NUMBER;
1306 l_rows_per_worker NUMBER;
1307 l_start_id NUMBER;
1308 l_end_id NUMBER;
1309 l_batch_size CONSTANT NUMBER := 10000;
1310
1311 CURSOR Get_SL_Next_Val IS
1312 select AS_SALES_LEADS_S.nextval
1313 from dual;
1314
1315 CURSOR Get_SL_Min_Id IS
1316 select min(sales_lead_id)
1317 from as_sales_leads;
1318
1319 BEGIN
1320
1321 --
1322 -- If this is first time parent is called, then split the rows
1323 -- among workers and put the parent in paused state
1324 --
1325 IF (fnd_conc_global.request_data IS NULL) THEN
1326
1327 -- Log
1328 IF (p_debug_flag = 'Y' AND
1329 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1330 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1331 'Start:' || 'p_num_workers=' || p_num_workers ||
1332 ',p_debug_flag=' || p_debug_flag);
1333 END IF;
1334
1335
1336 --
1337 -- Get maximum number of possible rows in as_leads_all
1338 --
1339
1340 -- Initialize start ID value
1341 l_start_id := 0;
1342
1343 open Get_SL_Min_Id;
1344 fetch Get_SL_Min_Id into l_start_id;
1345 close Get_SL_Min_Id;
1346
1347 OPEN Get_SL_Next_Val;
1348 FETCH Get_SL_Next_Val INTO l_max_num_rows;
1349 CLOSE Get_SL_Next_Val;
1350
1351 --
1352 -- Compute row range to be assigned to each worker
1353 --
1354 l_rows_per_worker := ROUND((l_max_num_rows -l_start_id) /p_num_workers) + 1;
1355
1356 --
1357 -- Assign rows to each worker
1358 --
1359
1360
1361
1362
1363 FOR i IN 1..p_num_workers LOOP
1364
1365 -- Initialize end ID value
1366 l_end_id := l_start_id + l_rows_per_worker;
1367
1368
1369 IF (p_debug_flag = 'Y' AND
1370 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1371 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1372 'Submitting child:' || 'Worker ID=' || i ||
1373 ' ,Start ID =' || l_start_id ||
1374 ',End ID =' || l_end_id);
1375 END IF;
1376
1377 -- Submit the request
1378 l_req_id :=
1379 fnd_request.submit_request
1380 (
1381 application => 'AS',
1382 program => 'ASXADFL',
1383 description => null,
1384 start_time => sysdate,
1385 sub_request => true,
1386 argument1 => x_open_flag,
1387 argument2 => x_status_code,
1388 argument3 => l_start_id,
1389 argument4 => l_end_id,
1390 argument5 => NVL(p_batch_size,10000),
1391 argument6 => p_debug_flag,
1392 argument7 => CHR(0),
1393 argument8 => CHR(0),
1394 argument9 => CHR(0),
1395 argument10 => CHR(0),
1396 argument11 => CHR(0),
1397 argument12 => CHR(0),
1398 argument13 => CHR(0),
1399 argument14 => CHR(0),
1400 argument15 => CHR(0),
1401 argument16 => CHR(0),
1402 argument17 => CHR(0),
1403 argument18 => CHR(0),
1404 argument19 => CHR(0),
1405 argument20 => CHR(0),
1406 argument21 => CHR(0),
1407 argument22 => CHR(0),
1408 argument23 => CHR(0),
1409 argument24 => CHR(0),
1410 argument25 => CHR(0),
1411 argument26 => CHR(0),
1412 argument27 => CHR(0),
1413 argument28 => CHR(0),
1414 argument29 => CHR(0),
1415 argument30 => CHR(0),
1416 argument31 => CHR(0),
1417 argument32 => CHR(0),
1418 argument33 => CHR(0),
1419 argument34 => CHR(0),
1420 argument35 => CHR(0),
1421 argument36 => CHR(0),
1422 argument37 => CHR(0),
1423 argument38 => CHR(0),
1424 argument39 => CHR(0),
1425 argument40 => CHR(0),
1426 argument41 => CHR(0),
1427 argument42 => CHR(0),
1428 argument43 => CHR(0),
1429 argument44 => CHR(0),
1430 argument45 => CHR(0),
1431 argument46 => CHR(0),
1432 argument47 => CHR(0),
1433 argument48 => CHR(0),
1434 argument49 => CHR(0),
1435 argument50 => CHR(0),
1436 argument51 => CHR(0),
1437 argument52 => CHR(0),
1438 argument53 => CHR(0),
1439 argument54 => CHR(0),
1440 argument55 => CHR(0),
1441 argument56 => CHR(0),
1442 argument57 => CHR(0),
1443 argument58 => CHR(0),
1444 argument59 => CHR(0),
1445 argument60 => CHR(0),
1446 argument61 => CHR(0),
1447 argument62 => CHR(0),
1448 argument63 => CHR(0),
1449 argument64 => CHR(0),
1450 argument65 => CHR(0),
1451 argument66 => CHR(0),
1452 argument67 => CHR(0),
1453 argument68 => CHR(0),
1454 argument69 => CHR(0),
1455 argument70 => CHR(0),
1456 argument71 => CHR(0),
1457 argument72 => CHR(0),
1458 argument73 => CHR(0),
1459 argument74 => CHR(0),
1460 argument75 => CHR(0),
1461 argument76 => CHR(0),
1462 argument77 => CHR(0),
1463 argument78 => CHR(0),
1464 argument79 => CHR(0),
1465 argument80 => CHR(0),
1466 argument81 => CHR(0),
1467 argument82 => CHR(0),
1468 argument83 => CHR(0),
1469 argument84 => CHR(0),
1470 argument85 => CHR(0),
1471 argument86 => CHR(0),
1472 argument87 => CHR(0),
1473 argument88 => CHR(0),
1474 argument89 => CHR(0),
1475 argument90 => CHR(0),
1476 argument91 => CHR(0),
1477 argument92 => CHR(0),
1478 argument93 => CHR(0),
1479 argument94 => CHR(0),
1480 argument95 => CHR(0),
1481 argument96 => CHR(0),
1482 argument97 => CHR(0),
1483 argument98 => CHR(0),
1484 argument99 => CHR(0),
1485 argument100 => CHR(0)
1486 );
1487
1488 --
1489 -- If request submission failed, exit with error.
1490 --
1491 IF (l_req_id = 0) THEN
1492
1493 errbuf := fnd_message.get;
1494 retcode := 2;
1495 RETURN;
1496
1497 END IF;
1498
1499 -- Set start ID value
1500 l_start_id := l_end_id ;
1501
1502 END LOOP; -- end i
1503
1504 --
1505 -- After submitting request for all workers, put the parent
1506 -- in paused state. When all children are done, the parent
1507 -- would be called again, and then it will terminate
1508 --
1509 fnd_conc_global.set_req_globals
1510 (
1511 conc_status => 'PAUSED',
1512 request_data => to_char(l_req_id)
1513 -- conc_restart_time => to_char(sysdate)
1514 -- release_sub_request => 'N'
1515 );
1516
1517 ELSE
1518
1519 -- Log
1520 IF (p_debug_flag = 'Y' AND
1524 ',p_debug_flag='||p_debug_flag);
1521 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1522 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1523 'Re-entering:' || 'p_num_workers=' || p_num_workers ||
1525 END IF;
1526
1527
1528 errbuf := 'Migration completed';
1529 retcode := 0;
1530
1531 -- Log
1532 IF (p_debug_flag = 'Y' AND
1533 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1534 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1535 'Done:' || 'p_num_workers=' || p_num_workers ||
1536 ',p_debug_flag='||p_debug_flag);
1537 END IF;
1538
1539 END IF;
1540
1541 EXCEPTION
1542
1543 WHEN OTHERS THEN
1544 ROLLBACK;
1545
1546 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1547
1548 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1549 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1550 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1551 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1552 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, l_module_name, true);
1553 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1554 l_api_name||':'||sqlcode||':'||sqlerrm);
1555 END IF;
1556
1557 END update_leads_Main;
1558
1559 procedure UPDATE_LEADS_ACCESSES(ERRBUF OUT NOCOPY VARCHAR2,
1560 RETCODE OUT NOCOPY VARCHAR2,
1561 x_open_flag IN VARCHAR2,
1562 x_status_code IN VARCHAR2,
1563 p_start_id IN VARCHAR2,
1564 p_end_id IN VARCHAR2,
1565 p_batch_size IN NUMBER,
1566 p_debug_flag IN VARCHAR2)
1567 IS
1568 l_count NUMBER := 0;
1569 l_min_id NUMBER := 0;
1570 l_max_id NUMBER := 0;
1571 l_debug BOOLEAN := false;
1572 l_last_updated_by NUMBER:= fnd_global.user_id;
1573 l_last_update_login NUMBER:= fnd_global.conc_login_id;
1574 G_BATCH_SIZE NUMBER := 10000;
1575 l_lead_flag VARCHAR2(1);
1576 l_status BOOLEAN;
1577
1578
1579
1580 CURSOR Get_SL_Min_Id IS
1581 select min(sales_lead_id)
1582 from as_sales_leads;
1583
1584 CURSOR Get_SL_Next_Val IS
1585 select AS_SALES_LEADS_S.nextval
1586 from dual;
1587
1588 CURSOR Get_flag_from_Input(c_in_param_1 VARCHAR2,c_in_param_2 VARCHAR2) IS
1589 select NVL(LEAD_FLAG,'N')
1590 from AS_STATUSES_B
1591 where status_code = c_in_param_2
1592 and UPPER(opp_open_status_flag) = UPPER(c_in_param_1);
1593
1594 l_module CONSTANT VARCHAR2(255) := 'as.plsql.stapk.UPDATE_LEADS_ACCESSES';
1595
1596 BEGIN
1597
1598 IF Upper(X_STATUS_CODE) <> 'ALL' AND UPPER(NVL(X_OPEN_FLAG,'X')) NOT IN ('Y','N') THEN
1599 Write_log (l_module, 1, 'Invalid input for Status Flag!');
1600 RAISE FND_API.G_EXC_ERROR;
1601 END IF;
1602
1603 IF Upper(X_STATUS_CODE) <> 'ALL' THEN
1604 Open Get_flag_from_Input(X_OPEN_FLAG,X_STATUS_CODE);
1605 Fetch Get_flag_from_Input INTO l_lead_flag;
1606 IF (Get_flag_from_Input%NOTFOUND) THEN
1607 Write_log (l_module, 1, 'Combination of Status Code and Open Flag is not valid! Please note that the status code is case sensitive.');
1608 Close Get_flag_from_Input;
1609 RAISE FND_API.G_EXC_ERROR;
1610 ELSE
1611 Close Get_flag_from_Input;
1612 END IF;
1613 END IF;
1614
1615
1616
1617 IF (p_debug_flag = 'Y') THEN
1618 l_debug := TRUE;
1619 ELSE
1620 l_debug := FALSE;
1621 END IF;
1622
1623
1624
1625
1626
1627
1628 --Start updating sales leads table
1629 IF l_lead_flag = 'Y' OR Upper(X_STATUS_CODE) = 'ALL' THEN
1630 --Get Min,Max Ids
1631 l_min_id := p_start_id;
1632 l_max_id := p_end_id;
1633 G_BATCH_SIZE := NVL(p_batch_size,10000);
1634
1635
1636
1637 if (l_debug) Then
1638 FND_FILE.PUT_LINE(FND_FILE.LOG,'Minimum Id - Max id for leads for this worker :' || l_min_id ||' to '||l_max_id);
1639 end if;
1640
1641 -- Initialize counter
1642 l_count := l_min_id;
1643
1644 while (l_count <= l_max_id)
1645 loop
1646 if (l_debug) Then
1647 FND_FILE.PUT_LINE(FND_FILE.LOG,'Current loop count:' || l_count);
1648 end if;
1649 -- update statements for sales leads here
1650 If Upper(X_STATUS_CODE) = 'ALL' THEN
1651 -- added decode for converted_to_opportunity fix for bug#3931530
1652 UPDATE /*+ INDEX(sld AS_SALES_LEADS_MT1)*/ AS_SALES_LEADS sld
1653 SET (sld.STATUS_OPEN_FLAG , sld.CLOSE_REASON) =
1654 (SELECT opp_open_status_flag,
1655 DECODE(opp_open_status_flag,'Y',NULL,'N',
1656 DECODE(st.status_code,'CONVERTED_TO_OPPORTUNITY','CONVERTED_TO_OPPORTUNITY',
1657 NVL(sld.close_reason,'NOT_SPECIFIED')
1658 )
1659 )
1660 FROM AS_STATUSES_B st
1661 WHERE st.status_code = sld.status_code
1665 , sld.last_update_login = l_last_update_login
1662 AND st.lead_flag = 'Y')
1663 , sld.last_update_date = sysdate
1664 , sld.last_updated_by = l_last_updated_by
1666 WHERE sld.sales_lead_id >= l_count
1667 AND sld.sales_lead_id < l_count+G_BATCH_SIZE
1668 AND sld.sales_lead_id < l_max_id
1669 AND sld.status_code is not null;
1670 ELSE
1671 -- added decode for converted_to_opportunity fix for bug#3931530
1672 UPDATE /*+ INDEX(sld AS_SALES_LEADS_MT1)*/ AS_SALES_LEADS sld
1673 SET sld.STATUS_OPEN_FLAG = Upper(X_OPEN_FLAG)
1674 , sld.CLOSE_REASON = DECODE(Upper(X_OPEN_FLAG),'Y',NULL,'N',
1675 DECODE(X_STATUS_CODE,'CONVERTED_TO_OPPORTUNITY','CONVERTED_TO_OPPORTUNITY',
1676 NVL(sld.close_reason,'NOT_SPECIFIED')
1677 )
1678 )
1679 , sld.last_update_date = sysdate
1680 , sld.last_updated_by = l_last_updated_by
1681 , sld.last_update_login = l_last_update_login
1682 WHERE sld.sales_lead_id >= l_count
1683 AND sld.sales_lead_id < l_count+G_BATCH_SIZE
1684 AND sld.sales_lead_id < l_max_id
1685 AND sld.STATUS_CODE = X_STATUS_CODE
1686 AND sld.status_code is not null;
1687 END IF;
1688 -- end update statements for sales leads here
1689 commit;
1690
1691 l_count := l_count + G_BATCH_SIZE;
1692 end loop;
1693 commit;
1694
1695 if l_debug then
1696 FND_FILE.PUT_LINE(FND_FILE.LOG,'Update of denormed open flag in sales leads finished successfully');
1697 FND_FILE.PUT_LINE(FND_FILE.LOG,'End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1698 end if;
1699 END IF; -- Only if l_lead_flag is Y
1700
1701
1702
1703 EXCEPTION
1704 WHEN FND_API.G_EXC_ERROR THEN
1705 ERRBUF := ERRBUF || sqlerrm;
1706 RETCODE := FND_API.G_RET_STS_ERROR;
1707 ROLLBACK;
1708 Write_log (l_module, 1, 'Error in as_statuses_pkg.update_leads_accesses');
1709 Write_log (l_module, 1, 'SQLCODE ' || to_char(SQLCODE) ||
1710 ' SQLERRM ' || substr(SQLERRM, 1, 100));
1711 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
1712 IF l_status = TRUE THEN
1713 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
1714 END IF ;
1715 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1716 ERRBUF := ERRBUF||sqlerrm;
1717 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
1718 ROLLBACK;
1719 Write_Log(l_module, 1, 'Unexpected error in as_statuses_pkg.update_leads_accesses');
1720 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
1721 IF l_status = TRUE THEN
1722 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
1723 END IF ;
1724 WHEN others THEN
1725 ERRBUF := SQLERRM;
1726 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
1727 ROLLBACK;
1728 Write_Log(l_module, 1, 'Exception: others in as_statuses_pkg.update_leads_accesses');
1729 Write_Log(l_module, 1, 'SQLCODE ' || to_char(SQLCODE) ||
1730 ' SQLERRM ' || substr(SQLERRM, 1, 100));
1731 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
1732 IF l_status = TRUE THEN
1733 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
1734 END IF ;
1735 end UPDATE_LEADS_ACCESSES;
1736
1737
1738 PROCEDURE update_oppty_Main
1739 (
1740 errbuf OUT NOCOPY VARCHAR2,
1741 retcode OUT NOCOPY NUMBER,
1742 x_open_flag IN VARCHAR2,
1743 x_status_code IN VARCHAR2,
1744 p_num_workers IN NUMBER,
1745 p_batch_size IN NUMBER,
1746 p_debug_flag IN VARCHAR2
1747 )
1748 IS
1749 l_api_name CONSTANT VARCHAR2(30) :=
1750 'update_oppty_Main';
1751 l_module_name CONSTANT VARCHAR2(256) :=
1752 'as.plsql.as_statuses_pkg.update_oppty_Main';
1753 l_msg_count NUMBER;
1754 l_msg_data VARCHAR2(2000);
1755 l_req_id NUMBER;
1756 l_request_data VARCHAR2(30);
1757 l_max_num_rows NUMBER;
1758 l_rows_per_worker NUMBER;
1759 l_start_id NUMBER;
1760 l_end_id NUMBER;
1761 l_batch_size CONSTANT NUMBER := 10000;
1762
1763 CURSOR Get_SL_Next_Val IS
1764 select AS_LEADS_S.nextval
1765 from dual;
1766
1767 CURSOR Get_SL_Min_Id IS
1768 select min(lead_id)
1769 from as_leads_all;
1770
1771 BEGIN
1772
1773 --
1774 -- If this is first time parent is called, then split the rows
1775 -- among workers and put the parent in paused state
1776 --
1780 IF (p_debug_flag = 'Y' AND
1777 IF (fnd_conc_global.request_data IS NULL) THEN
1778
1779 -- Log
1781 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1782 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1783 'Start:' || 'p_num_workers=' || p_num_workers ||
1784 ',p_debug_flag=' || p_debug_flag);
1785 END IF;
1786
1787
1788 --
1789 -- Get maximum number of possible rows in as_leads_all
1790 --
1791
1792 -- Initialize start ID value
1793 l_start_id := 0;
1794
1795 open Get_SL_Min_Id;
1796 fetch Get_SL_Min_Id into l_start_id;
1797 close Get_SL_Min_Id;
1798
1799 OPEN Get_SL_Next_Val;
1800 FETCH Get_SL_Next_Val INTO l_max_num_rows;
1801 CLOSE Get_SL_Next_Val;
1802
1803 --
1804 -- Compute row range to be assigned to each worker
1805 --
1806 l_rows_per_worker := ROUND((l_max_num_rows -l_start_id) /p_num_workers) + 1;
1807
1808 --
1809 -- Assign rows to each worker
1810 --
1811
1812
1813
1814
1815 FOR i IN 1..p_num_workers LOOP
1816
1817 -- Initialize end ID value
1818 l_end_id := l_start_id + l_rows_per_worker;
1819
1820 IF (p_debug_flag = 'Y' AND
1821 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1822 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1823 'Submitting child:' || 'Worker ID=' || i ||
1824 ' ,Start ID =' || l_start_id ||
1825 ',End ID =' || l_end_id);
1826 END IF;
1827
1828 -- Submit the request
1829 l_req_id :=
1830 fnd_request.submit_request
1831 (
1832 application => 'AS',
1833 program => 'ASXADFO',
1834 description => null,
1835 start_time => sysdate,
1836 sub_request => true,
1837 argument1 => x_open_flag,
1838 argument2 => x_status_code,
1839 argument3 => l_start_id,
1840 argument4 => l_end_id,
1841 argument5 => NVL(p_batch_size,10000),
1842 argument6 => p_debug_flag,
1843 argument7 => CHR(0),
1844 argument8 => CHR(0),
1845 argument9 => CHR(0),
1846 argument10 => CHR(0),
1847 argument11 => CHR(0),
1848 argument12 => CHR(0),
1849 argument13 => CHR(0),
1850 argument14 => CHR(0),
1851 argument15 => CHR(0),
1852 argument16 => CHR(0),
1853 argument17 => CHR(0),
1854 argument18 => CHR(0),
1855 argument19 => CHR(0),
1856 argument20 => CHR(0),
1857 argument21 => CHR(0),
1858 argument22 => CHR(0),
1859 argument23 => CHR(0),
1860 argument24 => CHR(0),
1861 argument25 => CHR(0),
1862 argument26 => CHR(0),
1863 argument27 => CHR(0),
1864 argument28 => CHR(0),
1865 argument29 => CHR(0),
1866 argument30 => CHR(0),
1867 argument31 => CHR(0),
1868 argument32 => CHR(0),
1869 argument33 => CHR(0),
1870 argument34 => CHR(0),
1871 argument35 => CHR(0),
1872 argument36 => CHR(0),
1873 argument37 => CHR(0),
1874 argument38 => CHR(0),
1875 argument39 => CHR(0),
1876 argument40 => CHR(0),
1877 argument41 => CHR(0),
1878 argument42 => CHR(0),
1879 argument43 => CHR(0),
1880 argument44 => CHR(0),
1881 argument45 => CHR(0),
1882 argument46 => CHR(0),
1883 argument47 => CHR(0),
1884 argument48 => CHR(0),
1885 argument49 => CHR(0),
1886 argument50 => CHR(0),
1887 argument51 => CHR(0),
1888 argument52 => CHR(0),
1889 argument53 => CHR(0),
1890 argument54 => CHR(0),
1891 argument55 => CHR(0),
1892 argument56 => CHR(0),
1893 argument57 => CHR(0),
1894 argument58 => CHR(0),
1895 argument59 => CHR(0),
1896 argument60 => CHR(0),
1897 argument61 => CHR(0),
1898 argument62 => CHR(0),
1899 argument63 => CHR(0),
1900 argument64 => CHR(0),
1901 argument65 => CHR(0),
1902 argument66 => CHR(0),
1903 argument67 => CHR(0),
1904 argument68 => CHR(0),
1905 argument69 => CHR(0),
1906 argument70 => CHR(0),
1907 argument71 => CHR(0),
1908 argument72 => CHR(0),
1909 argument73 => CHR(0),
1910 argument74 => CHR(0),
1911 argument75 => CHR(0),
1912 argument76 => CHR(0),
1913 argument77 => CHR(0),
1914 argument78 => CHR(0),
1915 argument79 => CHR(0),
1916 argument80 => CHR(0),
1917 argument81 => CHR(0),
1918 argument82 => CHR(0),
1919 argument83 => CHR(0),
1920 argument84 => CHR(0),
1921 argument85 => CHR(0),
1922 argument86 => CHR(0),
1923 argument87 => CHR(0),
1924 argument88 => CHR(0),
1925 argument89 => CHR(0),
1929 argument93 => CHR(0),
1926 argument90 => CHR(0),
1927 argument91 => CHR(0),
1928 argument92 => CHR(0),
1930 argument94 => CHR(0),
1931 argument95 => CHR(0),
1932 argument96 => CHR(0),
1933 argument97 => CHR(0),
1934 argument98 => CHR(0),
1935 argument99 => CHR(0),
1936 argument100 => CHR(0)
1937 );
1938
1939 --
1940 -- If request submission failed, exit with error.
1941 --
1942 IF (l_req_id = 0) THEN
1943
1944 errbuf := fnd_message.get;
1945 retcode := 2;
1946 RETURN;
1947
1948 END IF;
1949
1950 -- Set start ID value
1951 l_start_id := l_end_id ;
1952
1953 END LOOP; -- end i
1954
1955 --
1956 -- After submitting request for all workers, put the parent
1957 -- in paused state. When all children are done, the parent
1958 -- would be called again, and then it will terminate
1959 --
1960 fnd_conc_global.set_req_globals
1961 (
1962 conc_status => 'PAUSED',
1963 request_data => to_char(l_req_id)
1964 -- conc_restart_time => to_char(sysdate)
1965 -- release_sub_request => 'N'
1966 );
1967
1968 ELSE
1969
1970 -- Log
1971 IF (p_debug_flag = 'Y' AND
1972 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1973 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1974 ' Re-entering:' || 'p_num_workers=' || p_num_workers ||
1975 ',p_debug_flag='||p_debug_flag);
1976
1977 END IF;
1978
1979
1980 errbuf := 'Migration completed';
1981 retcode := 0;
1982
1983 -- Log
1984 IF (p_debug_flag = 'Y' AND
1985 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1986 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1987 'Done:' || 'p_num_workers=' || p_num_workers ||
1988 ',p_debug_flag='||p_debug_flag);
1989 END IF;
1990
1991 END IF;
1992
1993 EXCEPTION
1994
1995 WHEN OTHERS THEN
1996 ROLLBACK;
1997
1998 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1999
2000 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2001 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
2002 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2003 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2004 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, l_module_name, true);
2005 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2006 l_api_name||':'||sqlcode||':'||sqlerrm);
2007 END IF;
2008
2009 END update_oppty_Main;
2010
2011
2012 procedure UPDATE_OPPTY_ACCESSES(ERRBUF OUT NOCOPY VARCHAR2,
2013 RETCODE OUT NOCOPY VARCHAR2,
2014 x_open_flag IN VARCHAR2,
2015 x_status_code IN VARCHAR2,
2016 p_start_id IN VARCHAR2,
2017 p_end_id IN VARCHAR2,
2018 p_batch_size IN NUMBER,
2019 p_debug_flag IN VARCHAR2)
2020 IS
2021 l_count NUMBER := 0;
2022 l_min_id NUMBER := 0;
2023 l_max_id NUMBER := 0;
2024 l_debug BOOLEAN := false;
2025 l_last_updated_by NUMBER:= fnd_global.user_id;
2026 l_last_update_login NUMBER:= fnd_global.conc_login_id;
2027 G_BATCH_SIZE NUMBER := 10000;
2028 l_opp_flag VARCHAR2(1);
2029 l_status BOOLEAN;
2030
2031
2032 CURSOR Get_L_Min_Id IS
2033 select min(lead_id)
2034 from as_leads_all;
2035
2036 CURSOR Get_L_Next_Val IS
2037 select AS_LEADS_S.nextval
2038 from dual;
2039
2040
2041 CURSOR Get_flag_from_Input(c_in_param_1 VARCHAR2,c_in_param_2 VARCHAR2) IS
2042 select NVL(OPP_FLAG,'N')
2043 from AS_STATUSES_B
2044 where status_code = c_in_param_2
2045 and UPPER(opp_open_status_flag) = UPPER(c_in_param_1);
2046
2047 l_module CONSTANT VARCHAR2(255) := 'as.plsql.stapk.UPDATE_OPPTY_ACCESSES';
2048
2049 BEGIN
2050
2051 IF Upper(X_STATUS_CODE) <> 'ALL' AND UPPER(NVL(X_OPEN_FLAG,'X')) NOT IN ('Y','N') THEN
2052 Write_log (l_module, 1, 'Invalid input for Status Flag!');
2053 RAISE FND_API.G_EXC_ERROR;
2054 END IF;
2055
2056 IF Upper(X_STATUS_CODE) <> 'ALL' THEN
2057 Open Get_flag_from_Input(X_OPEN_FLAG,X_STATUS_CODE);
2058 Fetch Get_flag_from_Input INTO l_opp_flag;
2059 IF (Get_flag_from_Input%NOTFOUND) THEN
2060 Write_log (l_module, 1, 'Combination of Status Code and Open Flag is not valid!');
2061 Close Get_flag_from_Input;
2062 RAISE FND_API.G_EXC_ERROR;
2063 ELSE
2064 Close Get_flag_from_Input;
2065 END IF;
2066 END IF;
2067
2068 IF (p_debug_flag = 'Y') THEN
2069 l_debug := TRUE;
2070 ELSE
2071 l_debug := FALSE;
2072 END IF;
2073
2074
2075
2076
2077
2078 --Start updating oppty table
2079 IF l_opp_flag = 'Y' OR Upper(X_STATUS_CODE) = 'ALL' THEN
2080
2084 G_BATCH_SIZE := NVL(p_batch_size,10000);
2081 --Get Min,Max Ids
2082 l_min_id := p_start_id;
2083 l_max_id := p_end_id;
2085
2086 IF (l_debug) THEN
2087 FND_FILE.PUT_LINE(FND_FILE.LOG,'Minimum Id - Max id for opportunity for this worker :' || l_min_id ||' to '||l_max_id);
2088 END IF;
2089
2090
2091 -- Initialize counter
2092 l_count := l_min_id;
2093
2094 while (l_count <= l_max_id)
2095 loop
2096 if (l_debug) Then
2097 FND_FILE.PUT_LINE(FND_FILE.LOG,'Current loop count:' || l_count);
2098 end if;
2099 -- update statements for opptys here
2100 If Upper(X_STATUS_CODE) = 'ALL' THEN
2101 execute immediate 'UPDATE /*+ INDEX(ld AS_LEADS_ALL_MT2)*/ AS_LEADS_ALL ld
2102 SET ld.close_reason = (DECODE((SELECT opp_open_status_flag
2103 FROM AS_STATUSES_B st
2104 WHERE st.status_code = ld.status
2105 AND st.opp_flag = ''Y''),''Y'',NULL,''N'',nvl(ld.CLOSE_REASON,''NOT_SPECIFIED'')))
2106 , ld.last_update_date = sysdate
2107 , ld.last_updated_by = :l_last_updated_by
2108 , ld.last_update_login = :l_last_update_login
2109 WHERE ld.lead_id >= :l_count
2110 AND ld.lead_id < :l_count1+:G_BATCH_SIZE
2111 AND ld.lead_id < :l_max_id
2112 AND ld.status is not null' using l_last_updated_by,l_last_update_login,l_count,l_count,G_BATCH_SIZE,l_max_id ;
2113 ELSIF Upper(X_OPEN_FLAG) = 'Y' THEN
2114 execute immediate 'UPDATE /*+ INDEX(ld AS_LEADS_ALL_MT2)*/ AS_LEADS_ALL ld
2115 SET ld.close_reason = null
2116 , ld.last_update_date = sysdate
2117 , ld.last_updated_by = :l_last_updated_by
2118 , ld.last_update_login = :l_last_update_login
2119 WHERE ld.lead_id >= :l_count
2120 AND ld.lead_id < :l_count1+:G_BATCH_SIZE
2121 AND ld.lead_id < :l_max_id
2122 AND ld.status = :X_STATUS_CODE
2123 AND ld.status is not null
2124 AND ld.close_reason is not null' using l_last_updated_by,l_last_update_login,l_count,l_count,G_BATCH_SIZE,l_max_id,X_STATUS_CODE ;
2125 ELSIF Upper(X_OPEN_FLAG) = 'N' THEN
2126 execute immediate 'UPDATE /*+ INDEX(ld AS_LEADS_ALL_MT2)*/ AS_LEADS_ALL ld
2127 SET ld.close_reason = ''NOT_SPECIFIED''
2128 , ld.last_update_date = sysdate
2129 , ld.last_updated_by = :l_last_updated_by
2130 , ld.last_update_login = :l_last_update_login
2131 WHERE ld.lead_id >= :l_count
2132 AND ld.lead_id < :l_count1+:G_BATCH_SIZE
2133 AND ld.lead_id < :l_max_id
2134 AND ld.status = :X_STATUS_CODE
2135 AND ld.status is not null
2136 AND ld.close_reason is null' using l_last_updated_by,l_last_update_login,l_count,l_count,G_BATCH_SIZE,l_max_id,X_STATUS_CODE ;
2137 END IF;
2138 -- end update statements for leads here
2139 commit;
2140
2141 l_count := l_count + G_BATCH_SIZE;
2142 end loop;
2143 commit;
2144
2145 if l_debug then
2146 FND_FILE.PUT_LINE(FND_FILE.LOG,'Update of close reason in opptys finished successfully');
2147 FND_FILE.PUT_LINE(FND_FILE.LOG,'End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2148 end if;
2149 END IF; -- Only if l_opp_flag is Y
2150
2151
2152
2153 EXCEPTION
2154 WHEN FND_API.G_EXC_ERROR THEN
2155 ERRBUF := ERRBUF || sqlerrm;
2156 RETCODE := FND_API.G_RET_STS_ERROR;
2157 ROLLBACK;
2158 Write_log (l_module, 1, 'Error in as_statuses_pkg.update_oppty_accesses');
2159 Write_log (l_module, 1, 'SQLCODE ' || to_char(SQLCODE) ||
2160 ' SQLERRM ' || substr(SQLERRM, 1, 100));
2161 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
2162 IF l_status = TRUE THEN
2163 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
2164 END IF ;
2165 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2166 ERRBUF := ERRBUF||sqlerrm;
2167 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
2168 ROLLBACK;
2169 Write_Log(l_module, 1, 'Unexpected error in as_statuses_pkg.update_oppty_accesses');
2170 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
2171 IF l_status = TRUE THEN
2172 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
2173 END IF ;
2174 WHEN others THEN
2175 ERRBUF := SQLERRM;
2176 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
2177 ROLLBACK;
2178 Write_Log(l_module, 1, 'Exception: others in as_statuses_pkg.update_oppty_accesses');
2179 Write_Log(l_module, 1, 'SQLCODE ' || to_char(SQLCODE) ||
2180 ' SQLERRM ' || substr(SQLERRM, 1, 100));
2181 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
2182 IF l_status = TRUE THEN
2183 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
2184 END IF ;
2185 end UPDATE_OPPTY_ACCESSES;
2186
2187 end AS_STATUSES_PKG;