[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_STAGES_PKG
Source
1 package body AS_SALES_STAGES_PKG as
2 /* #$Header: asxtsstb.pls 120.0 2005/06/02 17:23:44 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_SALES_STAGE_ID in NUMBER,
6 X_USAGE_INDICATOR in VARCHAR2,
7 X_INTERNAL_UPGRADE_STAGE_ID in NUMBER,
8 X_ENABLED_FLAG in VARCHAR2,
9 X_START_DATE_ACTIVE in DATE,
10 X_END_DATE_ACTIVE in DATE,
11 X_ATTRIBUTE_CATEGORY in VARCHAR2,
12 X_ATTRIBUTE1 in VARCHAR2,
13 X_ATTRIBUTE2 in VARCHAR2,
14 X_ATTRIBUTE3 in VARCHAR2,
15 X_ATTRIBUTE4 in VARCHAR2,
16 X_ATTRIBUTE5 in VARCHAR2,
17 X_ATTRIBUTE6 in VARCHAR2,
18 X_ATTRIBUTE7 in VARCHAR2,
19 X_ATTRIBUTE8 in VARCHAR2,
20 X_ATTRIBUTE9 in VARCHAR2,
21 X_ATTRIBUTE10 in VARCHAR2,
22 X_ATTRIBUTE11 in VARCHAR2,
23 X_ATTRIBUTE12 in VARCHAR2,
24 X_ATTRIBUTE13 in VARCHAR2,
25 X_ATTRIBUTE14 in VARCHAR2,
26 X_ATTRIBUTE15 in VARCHAR2,
27 X_MIN_WIN_PROBABILITY in NUMBER,
28 X_MAX_WIN_PROBABILITY in NUMBER,
29 X_APPLICABILITY in VARCHAR2,
30 X_NAME in VARCHAR2,
31 X_DESCRIPTION in VARCHAR2,
32 X_CREATION_DATE in DATE,
33 X_CREATED_BY in NUMBER,
34 X_LAST_UPDATE_DATE in DATE,
35 X_LAST_UPDATED_BY in NUMBER,
36 X_LAST_UPDATE_LOGIN in NUMBER
37 ) is
38 cursor C is select ROWID from AS_SALES_STAGES_ALL_B
39 where SALES_STAGE_ID = X_SALES_STAGE_ID
40 ;
41 begin
42 insert into AS_SALES_STAGES_ALL_B (
43 USAGE_INDICATOR,
44 INTERNAL_UPGRADE_STAGE_ID,
45 ENABLED_FLAG,
46 START_DATE_ACTIVE,
47 END_DATE_ACTIVE,
48 ATTRIBUTE_CATEGORY,
49 ATTRIBUTE1,
50 ATTRIBUTE2,
51 ATTRIBUTE3,
52 ATTRIBUTE4,
53 ATTRIBUTE5,
54 ATTRIBUTE6,
55 ATTRIBUTE7,
56 ATTRIBUTE8,
57 ATTRIBUTE9,
58 ATTRIBUTE10,
59 ATTRIBUTE11,
60 ATTRIBUTE12,
61 ATTRIBUTE13,
62 ATTRIBUTE14,
63 ATTRIBUTE15,
64 SALES_STAGE_ID,
65 MIN_WIN_PROBABILITY,
66 MAX_WIN_PROBABILITY,
67 APPLICABILITY,
68 CREATION_DATE,
69 CREATED_BY,
70 LAST_UPDATE_DATE,
71 LAST_UPDATED_BY,
72 LAST_UPDATE_LOGIN
73 ) values (
74 X_USAGE_INDICATOR,
75 X_INTERNAL_UPGRADE_STAGE_ID,
76 X_ENABLED_FLAG,
77 X_START_DATE_ACTIVE,
78 X_END_DATE_ACTIVE,
79 X_ATTRIBUTE_CATEGORY,
80 X_ATTRIBUTE1,
81 X_ATTRIBUTE2,
82 X_ATTRIBUTE3,
83 X_ATTRIBUTE4,
84 X_ATTRIBUTE5,
85 X_ATTRIBUTE6,
86 X_ATTRIBUTE7,
87 X_ATTRIBUTE8,
88 X_ATTRIBUTE9,
89 X_ATTRIBUTE10,
90 X_ATTRIBUTE11,
91 X_ATTRIBUTE12,
92 X_ATTRIBUTE13,
93 X_ATTRIBUTE14,
94 X_ATTRIBUTE15,
95 X_SALES_STAGE_ID,
96 X_MIN_WIN_PROBABILITY,
97 X_MAX_WIN_PROBABILITY,
98 X_APPLICABILITY,
99 X_CREATION_DATE,
100 X_CREATED_BY,
101 X_LAST_UPDATE_DATE,
102 X_LAST_UPDATED_BY,
103 X_LAST_UPDATE_LOGIN
104 );
105
106 insert into AS_SALES_STAGES_ALL_TL (
107 SALES_STAGE_ID,
108 LAST_UPDATE_DATE,
109 LAST_UPDATED_BY,
110 CREATION_DATE,
111 CREATED_BY,
112 LAST_UPDATE_LOGIN,
113 NAME,
114 DESCRIPTION,
115 LANGUAGE,
116 SOURCE_LANG
117 ) select
118 X_SALES_STAGE_ID,
119 X_LAST_UPDATE_DATE,
120 X_LAST_UPDATED_BY,
121 X_CREATION_DATE,
122 X_CREATED_BY,
123 X_LAST_UPDATE_LOGIN,
124 X_NAME,
125 X_DESCRIPTION,
126 L.LANGUAGE_CODE,
127 userenv('LANG')
128 from FND_LANGUAGES L
129 where L.INSTALLED_FLAG in ('I', 'B')
130 and not exists
131 (select NULL
132 from AS_SALES_STAGES_ALL_TL T
133 where T.SALES_STAGE_ID = X_SALES_STAGE_ID
134 and T.LANGUAGE = L.LANGUAGE_CODE);
135
136 open c;
137 fetch c into X_ROWID;
138 if (c%notfound) then
139 close c;
140 raise no_data_found;
141 end if;
142 close c;
143
144 end INSERT_ROW;
145
146 procedure LOCK_ROW (
147 X_SALES_STAGE_ID in NUMBER,
148 X_USAGE_INDICATOR in VARCHAR2,
149 X_INTERNAL_UPGRADE_STAGE_ID in NUMBER,
150 X_ENABLED_FLAG in VARCHAR2,
151 X_START_DATE_ACTIVE in DATE,
152 X_END_DATE_ACTIVE in DATE,
153 X_ATTRIBUTE_CATEGORY in VARCHAR2,
154 X_ATTRIBUTE1 in VARCHAR2,
155 X_ATTRIBUTE2 in VARCHAR2,
156 X_ATTRIBUTE3 in VARCHAR2,
157 X_ATTRIBUTE4 in VARCHAR2,
158 X_ATTRIBUTE5 in VARCHAR2,
159 X_ATTRIBUTE6 in VARCHAR2,
160 X_ATTRIBUTE7 in VARCHAR2,
161 X_ATTRIBUTE8 in VARCHAR2,
162 X_ATTRIBUTE9 in VARCHAR2,
163 X_ATTRIBUTE10 in VARCHAR2,
164 X_ATTRIBUTE11 in VARCHAR2,
165 X_ATTRIBUTE12 in VARCHAR2,
166 X_ATTRIBUTE13 in VARCHAR2,
167 X_ATTRIBUTE14 in VARCHAR2,
168 X_ATTRIBUTE15 in VARCHAR2,
169 X_MIN_WIN_PROBABILITY in NUMBER,
170 X_MAX_WIN_PROBABILITY in NUMBER,
171 X_APPLICABILITY in VARCHAR2,
172 X_NAME in VARCHAR2,
173 X_DESCRIPTION in VARCHAR2
174 ) is
175 cursor c is select
176 USAGE_INDICATOR,
177 INTERNAL_UPGRADE_STAGE_ID,
178 ENABLED_FLAG,
179 START_DATE_ACTIVE,
180 END_DATE_ACTIVE,
181 ATTRIBUTE_CATEGORY,
182 ATTRIBUTE1,
183 ATTRIBUTE2,
184 ATTRIBUTE3,
185 ATTRIBUTE4,
186 ATTRIBUTE5,
187 ATTRIBUTE6,
188 ATTRIBUTE7,
189 ATTRIBUTE8,
190 ATTRIBUTE9,
191 ATTRIBUTE10,
192 ATTRIBUTE11,
193 ATTRIBUTE12,
194 ATTRIBUTE13,
195 ATTRIBUTE14,
196 ATTRIBUTE15,
197 MIN_WIN_PROBABILITY,
198 MAX_WIN_PROBABILITY,
199 APPLICABILITY
200 from AS_SALES_STAGES_ALL_B
201 where SALES_STAGE_ID = X_SALES_STAGE_ID
202 for update of SALES_STAGE_ID nowait;
203 recinfo c%rowtype;
204
205 cursor c1 is select
206 NAME,
207 DESCRIPTION,
208 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
209 from AS_SALES_STAGES_ALL_TL
210 where SALES_STAGE_ID = X_SALES_STAGE_ID
211 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
212 for update of SALES_STAGE_ID nowait;
213 begin
214 open c;
215 fetch c into recinfo;
216 if (c%notfound) then
217 close c;
218 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
219 app_exception.raise_exception;
220 end if;
221 close c;
222 if ( ((recinfo.USAGE_INDICATOR = X_USAGE_INDICATOR)
223 OR ((recinfo.USAGE_INDICATOR is null) AND (X_USAGE_INDICATOR is null)))
224 AND ((recinfo.INTERNAL_UPGRADE_STAGE_ID = X_INTERNAL_UPGRADE_STAGE_ID)
225 OR ((recinfo.INTERNAL_UPGRADE_STAGE_ID is null) AND (X_INTERNAL_UPGRADE_STAGE_ID is null)))
226 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
227 AND (recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
228 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
229 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
230 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
231 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
232 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
233 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
234 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
235 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
236 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
237 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
238 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
239 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
240 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
241 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
242 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
243 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
244 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
245 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
246 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
247 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
248 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
249 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
250 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
251 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
252 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
253 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
254 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
255 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
256 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
257 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
258 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
259 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
260 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
261 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
262 AND (recinfo.MIN_WIN_PROBABILITY = X_MIN_WIN_PROBABILITY)
263 AND (recinfo.MAX_WIN_PROBABILITY = X_MAX_WIN_PROBABILITY)
264 AND (recinfo.APPLICABILITY = X_APPLICABILITY)
265 ) then
266 null;
267 else
268 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
269 app_exception.raise_exception;
270 end if;
271
272 for tlinfo in c1 loop
273 if (tlinfo.BASELANG = 'Y') then
274 if ( (tlinfo.NAME = X_NAME)
275 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
276 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
277 ) then
278 null;
279 else
280 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
281 app_exception.raise_exception;
282 end if;
283 end if;
284 end loop;
285 return;
286 end LOCK_ROW;
287
288 procedure UPDATE_ROW (
289 X_SALES_STAGE_ID in NUMBER,
290 X_USAGE_INDICATOR in VARCHAR2,
291 X_INTERNAL_UPGRADE_STAGE_ID in NUMBER,
292 X_ENABLED_FLAG in VARCHAR2,
293 X_START_DATE_ACTIVE in DATE,
294 X_END_DATE_ACTIVE in DATE,
295 X_ATTRIBUTE_CATEGORY in VARCHAR2,
296 X_ATTRIBUTE1 in VARCHAR2,
297 X_ATTRIBUTE2 in VARCHAR2,
298 X_ATTRIBUTE3 in VARCHAR2,
299 X_ATTRIBUTE4 in VARCHAR2,
300 X_ATTRIBUTE5 in VARCHAR2,
301 X_ATTRIBUTE6 in VARCHAR2,
302 X_ATTRIBUTE7 in VARCHAR2,
303 X_ATTRIBUTE8 in VARCHAR2,
304 X_ATTRIBUTE9 in VARCHAR2,
305 X_ATTRIBUTE10 in VARCHAR2,
306 X_ATTRIBUTE11 in VARCHAR2,
307 X_ATTRIBUTE12 in VARCHAR2,
308 X_ATTRIBUTE13 in VARCHAR2,
309 X_ATTRIBUTE14 in VARCHAR2,
310 X_ATTRIBUTE15 in VARCHAR2,
311 X_MIN_WIN_PROBABILITY in NUMBER,
312 X_MAX_WIN_PROBABILITY in NUMBER,
313 X_APPLICABILITY in VARCHAR2,
314 X_NAME in VARCHAR2,
315 X_DESCRIPTION in VARCHAR2,
316 X_LAST_UPDATE_DATE in DATE,
317 X_LAST_UPDATED_BY in NUMBER,
318 X_LAST_UPDATE_LOGIN in NUMBER
319 ) is
320 begin
321 update AS_SALES_STAGES_ALL_B set
322 USAGE_INDICATOR = X_USAGE_INDICATOR,
323 INTERNAL_UPGRADE_STAGE_ID = X_INTERNAL_UPGRADE_STAGE_ID,
324 ENABLED_FLAG = X_ENABLED_FLAG,
325 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
326 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
327 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
328 ATTRIBUTE1 = X_ATTRIBUTE1,
329 ATTRIBUTE2 = X_ATTRIBUTE2,
330 ATTRIBUTE3 = X_ATTRIBUTE3,
331 ATTRIBUTE4 = X_ATTRIBUTE4,
332 ATTRIBUTE5 = X_ATTRIBUTE5,
333 ATTRIBUTE6 = X_ATTRIBUTE6,
334 ATTRIBUTE7 = X_ATTRIBUTE7,
335 ATTRIBUTE8 = X_ATTRIBUTE8,
336 ATTRIBUTE9 = X_ATTRIBUTE9,
337 ATTRIBUTE10 = X_ATTRIBUTE10,
338 ATTRIBUTE11 = X_ATTRIBUTE11,
339 ATTRIBUTE12 = X_ATTRIBUTE12,
340 ATTRIBUTE13 = X_ATTRIBUTE13,
341 ATTRIBUTE14 = X_ATTRIBUTE14,
342 ATTRIBUTE15 = X_ATTRIBUTE15,
343 MIN_WIN_PROBABILITY = X_MIN_WIN_PROBABILITY,
344 MAX_WIN_PROBABILITY = X_MAX_WIN_PROBABILITY,
345 APPLICABILITY = X_APPLICABILITY,
346 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
347 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
348 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
349 where SALES_STAGE_ID = X_SALES_STAGE_ID;
350
351 if (sql%notfound) then
352 raise no_data_found;
353 end if;
354
355 update AS_SALES_STAGES_ALL_TL set
356 NAME = X_NAME,
357 DESCRIPTION = X_DESCRIPTION,
358 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
359 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
360 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
361 SOURCE_LANG = userenv('LANG')
362 where SALES_STAGE_ID = X_SALES_STAGE_ID
363 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
364
365 if (sql%notfound) then
366 raise no_data_found;
367 end if;
368 end UPDATE_ROW;
369
370 procedure DELETE_ROW (
371 X_SALES_STAGE_ID in NUMBER
372 ) is
373 begin
374 delete from AS_SALES_STAGES_ALL_TL
375 where SALES_STAGE_ID = X_SALES_STAGE_ID;
376
377 if (sql%notfound) then
378 raise no_data_found;
379 end if;
380
381 delete from AS_SALES_STAGES_ALL_B
382 where SALES_STAGE_ID = X_SALES_STAGE_ID;
383
384 if (sql%notfound) then
385 raise no_data_found;
386 end if;
387 end DELETE_ROW;
388
389 procedure LOAD_ROW (
390 X_SALES_STAGE_ID in NUMBER,
391 X_USAGE_INDICATOR in VARCHAR2,
392 X_INTERNAL_UPGRADE_STAGE_ID in NUMBER,
393 X_ENABLED_FLAG in VARCHAR2,
394 X_START_DATE_ACTIVE in DATE,
395 X_END_DATE_ACTIVE in DATE,
396 X_ATTRIBUTE_CATEGORY in VARCHAR2,
397 X_ATTRIBUTE1 in VARCHAR2,
398 X_ATTRIBUTE2 in VARCHAR2,
399 X_ATTRIBUTE3 in VARCHAR2,
400 X_ATTRIBUTE4 in VARCHAR2,
401 X_ATTRIBUTE5 in VARCHAR2,
402 X_ATTRIBUTE6 in VARCHAR2,
403 X_ATTRIBUTE7 in VARCHAR2,
404 X_ATTRIBUTE8 in VARCHAR2,
405 X_ATTRIBUTE9 in VARCHAR2,
406 X_ATTRIBUTE10 in VARCHAR2,
407 X_ATTRIBUTE11 in VARCHAR2,
408 X_ATTRIBUTE12 in VARCHAR2,
409 X_ATTRIBUTE13 in VARCHAR2,
410 X_ATTRIBUTE14 in VARCHAR2,
411 X_ATTRIBUTE15 in VARCHAR2,
412 X_MIN_WIN_PROBABILITY in NUMBER,
413 X_MAX_WIN_PROBABILITY in NUMBER,
414 X_APPLICABILITY in VARCHAR2,
415 X_NAME in VARCHAR2,
416 X_DESCRIPTION in VARCHAR2,
417 X_OWNER in VARCHAR2
418 )
419 IS
420 begin
421 declare
422 user_id number := 0;
423 row_id varchar2(64);
424
425 cursor custom_exist(p_SALES_STAGE_ID NUMBER) is
426 select 'Y'
427 from AS_SALES_STAGES_ALL_B
428 where last_updated_by <> 1
429 and SALES_STAGE_ID = p_SALES_STAGE_ID;
430
431 l_custom_exist varchar2(1) := 'N';
432
433
434 begin
435
436 OPEN custom_exist( X_SALES_STAGE_ID );
437 FETCH custom_exist into l_custom_exist;
438 CLOSE custom_exist;
439 IF nvl(l_custom_exist, 'N') = 'N' THEN
440
441 if (X_OWNER = 'SEED') then
442 user_id := 1;
443 end if;
444
445 begin
446 AS_SALES_STAGES_PKG.UPDATE_ROW(
447 X_SALES_STAGE_ID => X_SALES_STAGE_ID,
448 X_USAGE_INDICATOR => X_USAGE_INDICATOR,
449 X_INTERNAL_UPGRADE_STAGE_ID => X_INTERNAL_UPGRADE_STAGE_ID,
450 X_ENABLED_FLAG => X_ENABLED_FLAG,
451 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
452 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
453 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
454 X_ATTRIBUTE1 => X_ATTRIBUTE1,
455 X_ATTRIBUTE2 => X_ATTRIBUTE2,
456 X_ATTRIBUTE3 => X_ATTRIBUTE3,
460 X_ATTRIBUTE7 => X_ATTRIBUTE7,
457 X_ATTRIBUTE4 => X_ATTRIBUTE4,
458 X_ATTRIBUTE5 => X_ATTRIBUTE5,
459 X_ATTRIBUTE6 => X_ATTRIBUTE6,
461 X_ATTRIBUTE8 => X_ATTRIBUTE8,
462 X_ATTRIBUTE9 => X_ATTRIBUTE9,
463 X_ATTRIBUTE10 => X_ATTRIBUTE10,
464 X_ATTRIBUTE11 => X_ATTRIBUTE11,
465 X_ATTRIBUTE12 => X_ATTRIBUTE12,
466 X_ATTRIBUTE13 => X_ATTRIBUTE13,
467 X_ATTRIBUTE14 => X_ATTRIBUTE14,
468 X_ATTRIBUTE15 => X_ATTRIBUTE15,
469 X_MIN_WIN_PROBABILITY => X_MIN_WIN_PROBABILITY,
470 X_MAX_WIN_PROBABILITY => X_MAX_WIN_PROBABILITY,
471 X_APPLICABILITY => X_APPLICABILITY,
472 X_NAME => X_NAME,
473 X_DESCRIPTION => X_DESCRIPTION,
474 X_LAST_UPDATE_DATE => sysdate,
475 X_LAST_UPDATED_BY => user_id,
476 X_LAST_UPDATE_LOGIN => 0
477 );
478 exception
479 when NO_DATA_FOUND then
480 AS_SALES_STAGES_PKG.INSERT_ROW(
481 X_ROWID => row_id,
482 X_SALES_STAGE_ID => X_SALES_STAGE_ID,
483 X_USAGE_INDICATOR => X_USAGE_INDICATOR,
484 X_INTERNAL_UPGRADE_STAGE_ID => X_INTERNAL_UPGRADE_STAGE_ID,
485 X_ENABLED_FLAG => X_ENABLED_FLAG,
486 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
487 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
488 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
489 X_ATTRIBUTE1 => X_ATTRIBUTE1,
490 X_ATTRIBUTE2 => X_ATTRIBUTE2,
491 X_ATTRIBUTE3 => X_ATTRIBUTE3,
492 X_ATTRIBUTE4 => X_ATTRIBUTE4,
493 X_ATTRIBUTE5 => X_ATTRIBUTE5,
494 X_ATTRIBUTE6 => X_ATTRIBUTE6,
495 X_ATTRIBUTE7 => X_ATTRIBUTE7,
496 X_ATTRIBUTE8 => X_ATTRIBUTE8,
497 X_ATTRIBUTE9 => X_ATTRIBUTE9,
498 X_ATTRIBUTE10 => X_ATTRIBUTE10,
499 X_ATTRIBUTE11 => X_ATTRIBUTE11,
500 X_ATTRIBUTE12 => X_ATTRIBUTE12,
501 X_ATTRIBUTE13 => X_ATTRIBUTE13,
502 X_ATTRIBUTE14 => X_ATTRIBUTE14,
503 X_ATTRIBUTE15 => X_ATTRIBUTE15,
504 X_MIN_WIN_PROBABILITY => X_MIN_WIN_PROBABILITY,
505 X_MAX_WIN_PROBABILITY => X_MAX_WIN_PROBABILITY,
506 X_APPLICABILITY => X_APPLICABILITY,
507 X_NAME => X_NAME,
508 X_DESCRIPTION => X_DESCRIPTION,
509 X_CREATION_DATE => sysdate,
510 X_CREATED_BY => 0,
511 X_LAST_UPDATE_DATE => sysdate,
512 X_LAST_UPDATED_BY => user_id,
513 X_LAST_UPDATE_LOGIN => 0
514 );
515 end;
516
517 END IF;
518 end;
519 end LOAD_ROW;
520
521 procedure ADD_LANGUAGE
522 is
523 begin
524 delete from AS_SALES_STAGES_ALL_TL T
525 where not exists
526 (select NULL
527 from AS_SALES_STAGES_ALL_B B
528 where B.SALES_STAGE_ID = T.SALES_STAGE_ID
529 );
530
531 update AS_SALES_STAGES_ALL_TL T set (
532 NAME,
533 DESCRIPTION
534 ) = (select
535 B.NAME,
536 B.DESCRIPTION
537 from AS_SALES_STAGES_ALL_TL B
538 where B.SALES_STAGE_ID = T.SALES_STAGE_ID
539 and B.LANGUAGE = T.SOURCE_LANG)
540 where (
541 T.SALES_STAGE_ID,
542 T.LANGUAGE
543 ) in (select
544 SUBT.SALES_STAGE_ID,
545 SUBT.LANGUAGE
546 from AS_SALES_STAGES_ALL_TL SUBB, AS_SALES_STAGES_ALL_TL SUBT
547 where SUBB.SALES_STAGE_ID = SUBT.SALES_STAGE_ID
548 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
549 and (SUBB.NAME <> SUBT.NAME
550 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
551 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
552 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
553 ));
554
555 insert into AS_SALES_STAGES_ALL_TL (
556 SALES_STAGE_ID,
557 LAST_UPDATE_DATE,
558 LAST_UPDATED_BY,
559 CREATION_DATE,
560 CREATED_BY,
561 LAST_UPDATE_LOGIN,
562 NAME,
563 DESCRIPTION,
564 LANGUAGE,
565 SOURCE_LANG
566 ) select
567 B.SALES_STAGE_ID,
568 B.LAST_UPDATE_DATE,
569 B.LAST_UPDATED_BY,
570 B.CREATION_DATE,
571 B.CREATED_BY,
572 B.LAST_UPDATE_LOGIN,
573 B.NAME,
574 B.DESCRIPTION,
575 L.LANGUAGE_CODE,
576 B.SOURCE_LANG
577 from AS_SALES_STAGES_ALL_TL B, FND_LANGUAGES L
578 where L.INSTALLED_FLAG in ('I', 'B')
579 and B.LANGUAGE = userenv('LANG')
580 and not exists
581 (select NULL
582 from AS_SALES_STAGES_ALL_TL T
583 where T.SALES_STAGE_ID = B.SALES_STAGE_ID
584 and T.LANGUAGE = L.LANGUAGE_CODE);
585 end ADD_LANGUAGE;
586
587
588 PROCEDURE TRANSLATE_ROW (
589 X_SALES_STAGE_ID in NUMBER,
590 X_DESCRIPTION in VARCHAR2,
591 X_NAME in VARCHAR2,
592 X_OWNER in VARCHAR2)
593 IS
594 begin
595 -- only update rows that have not been altered by user
596
597 update AS_SALES_STAGES_ALL_TL
598 set description = X_DESCRIPTION,
599 name = X_NAME,
600 source_lang = userenv('LANG'),
601 last_update_date = sysdate,
602 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
603 last_update_login = 0
604 where SALES_STAGE_ID = X_SALES_STAGE_ID
605 and userenv('LANG') in (language, source_lang);
606 end TRANSLATE_ROW;
607
608 end AS_SALES_STAGES_PKG;