eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CS_TP_CHOICES_PKG

Source


1 package body CS_TP_CHOICES_PKG as
2 /* $Header: cstpchb.pls 115.7 2002/12/04 01:26:00 wzli noship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_CHOICE_ID in NUMBER,
6   X_LOOKUP_ID in NUMBER,
7   X_SEQUENCE_NUMBER in NUMBER,
8   X_START_DATE_ACTIVE in DATE,
9   X_END_DATE_ACTIVE in DATE,
10   X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
11   X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
12   X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
13   X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
14   X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
15   X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
16   X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
17   X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
18   X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
19   X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
20   X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
21   X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
22   X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
23   X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
24   X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
25   X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
26   X_SCORE in NUMBER,
27   X_VALUE in VARCHAR2,
28   X_CREATION_DATE in DATE,
29   X_CREATED_BY in NUMBER,
30   X_LAST_UPDATE_DATE in DATE,
31   X_LAST_UPDATED_BY in NUMBER,
32   X_LAST_UPDATE_LOGIN in NUMBER
33  ,X_DEFAULT_FLAG in VARCHAR2 DEFAULT NULL
34 ) is
35   cursor C is select ROWID from CS_TP_CHOICES_B
36     where CHOICE_ID = X_CHOICE_ID
37     ;
38 begin
39   insert into CS_TP_CHOICES_B (
40     CHOICE_ID,
41     LOOKUP_ID,
42     SEQUENCE_NUMBER,
43     START_DATE_ACTIVE,
44     END_DATE_ACTIVE,
45     ATTRIBUTE_CATEGORY,
46     ATTRIBUTE1,
47     ATTRIBUTE2,
48     ATTRIBUTE3,
49     ATTRIBUTE4,
50     ATTRIBUTE5,
51     ATTRIBUTE6,
52     ATTRIBUTE7,
53     ATTRIBUTE8,
54     ATTRIBUTE9,
55     ATTRIBUTE10,
56     ATTRIBUTE11,
57     ATTRIBUTE12,
58     ATTRIBUTE13,
59     ATTRIBUTE14,
60     ATTRIBUTE15,
61     SCORE,
62     CREATION_DATE,
63     CREATED_BY,
64     LAST_UPDATE_DATE,
65     LAST_UPDATED_BY,
66     LAST_UPDATE_LOGIN
67     ,DEFAULT_CHOICE_FLAG
68   ) values (
69     X_CHOICE_ID,
70     X_LOOKUP_ID,
71     X_SEQUENCE_NUMBER,
72     X_START_DATE_ACTIVE,
73     X_END_DATE_ACTIVE,
74     X_ATTRIBUTE_CATEGORY,
75     X_ATTRIBUTE1,
76     X_ATTRIBUTE2,
77     X_ATTRIBUTE3,
78     X_ATTRIBUTE4,
79     X_ATTRIBUTE5,
80     X_ATTRIBUTE6,
81     X_ATTRIBUTE7,
82     X_ATTRIBUTE8,
83     X_ATTRIBUTE9,
84     X_ATTRIBUTE10,
85     X_ATTRIBUTE11,
86     X_ATTRIBUTE12,
87     X_ATTRIBUTE13,
88     X_ATTRIBUTE14,
89     X_ATTRIBUTE15,
90     X_SCORE,
91     X_CREATION_DATE,
92     X_CREATED_BY,
93     X_LAST_UPDATE_DATE,
94     X_LAST_UPDATED_BY,
95     X_LAST_UPDATE_LOGIN
96     ,X_DEFAULT_FLAG
97   );
98 
99   insert into CS_TP_CHOICES_TL (
100     CHOICE_ID,
101     VALUE,
102     CREATION_DATE,
103     CREATED_BY,
104     LAST_UPDATE_DATE,
105     LAST_UPDATED_BY,
106     LAST_UPDATE_LOGIN,
107     LANGUAGE,
108     SOURCE_LANG
109   ) select
110     X_CHOICE_ID,
111     X_VALUE,
112     X_CREATION_DATE,
113     X_CREATED_BY,
114     X_LAST_UPDATE_DATE,
115     X_LAST_UPDATED_BY,
116     X_LAST_UPDATE_LOGIN,
117     L.LANGUAGE_CODE,
118     userenv('LANG')
119   from FND_LANGUAGES L
120   where L.INSTALLED_FLAG in ('I', 'B')
121   and not exists
122     (select NULL
123     from CS_TP_CHOICES_TL T
124     where T.CHOICE_ID = X_CHOICE_ID
125     and T.LANGUAGE = L.LANGUAGE_CODE);
126 
127   open c;
128   fetch c into X_ROWID;
129   if (c%notfound) then
130     close c;
131     raise no_data_found;
132   end if;
133   close c;
134 
135 end INSERT_ROW;
136 
137 procedure LOCK_ROW (
138   X_CHOICE_ID in NUMBER,
139   X_LOOKUP_ID in NUMBER,
140   X_SEQUENCE_NUMBER in NUMBER,
141   X_START_DATE_ACTIVE in DATE,
142   X_END_DATE_ACTIVE in DATE,
143   X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
144   X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
145   X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
146   X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
147   X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
148   X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
149   X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
150   X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
151   X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
152   X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
153   X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
154   X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
155   X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
156   X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
157   X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
158   X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
159   X_SCORE in NUMBER,
160   X_VALUE in VARCHAR2
161 
162   ,X_DEFAULT_FLAG in VARCHAR2 DEFAULT NULL
163 ) is
164   cursor c is select
165       LOOKUP_ID,
166       SEQUENCE_NUMBER,
167       START_DATE_ACTIVE,
168       END_DATE_ACTIVE,
169       ATTRIBUTE_CATEGORY,
170       ATTRIBUTE1,
171       ATTRIBUTE2,
172       ATTRIBUTE3,
173       ATTRIBUTE4,
174       ATTRIBUTE5,
175       ATTRIBUTE6,
176       ATTRIBUTE7,
177       ATTRIBUTE8,
178       ATTRIBUTE9,
179       ATTRIBUTE10,
180       ATTRIBUTE11,
181       ATTRIBUTE12,
182       ATTRIBUTE13,
183       ATTRIBUTE14,
184       ATTRIBUTE15,
185       SCORE
186 
187       ,default_choice_flag
188     from CS_TP_CHOICES_B
189     where CHOICE_ID = X_CHOICE_ID
190     for update of CHOICE_ID nowait;
191   recinfo c%rowtype;
192 
193   cursor c1 is select
194       VALUE,
195       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
196     from CS_TP_CHOICES_TL
197     where CHOICE_ID = X_CHOICE_ID
198     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
199     for update of CHOICE_ID nowait;
200 begin
201   open c;
202   fetch c into recinfo;
203   if (c%notfound) then
204     close c;
205     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
206     app_exception.raise_exception;
207   end if;
208   close c;
209   if (    (recinfo.LOOKUP_ID = X_LOOKUP_ID)
210       AND ((recinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
211            OR ((recinfo.SEQUENCE_NUMBER is null) AND (X_SEQUENCE_NUMBER is null)))
212       AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
213            OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
214       AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
215            OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
216       AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
217            OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
218       AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
219            OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
220       AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
221            OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
222       AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
223            OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
224       AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
225            OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
226       AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
227            OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
228       AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
229            OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
230       AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
231            OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
232       AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
233            OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
234       AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
235            OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
236       AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
237            OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
238       AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
239            OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
240       AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
241            OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
242       AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
243            OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
244       AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
245            OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
246       AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
247            OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
248       AND ((recinfo.SCORE = X_SCORE)
249            OR ((recinfo.SCORE is null) AND (X_SCORE is null)))
250 
251       AND ((recinfo.DEFAULT_CHOICE_FLAG = X_DEFAULT_FLAG)
252 	   OR ((recinfo.DEFAULT_CHOICE_FLAG is null) AND (X_DEFAULT_FLAG is null)))
253   ) then
254     null;
255   else
256     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
257     app_exception.raise_exception;
258   end if;
259 
260   for tlinfo in c1 loop
261     if (tlinfo.BASELANG = 'Y') then
262       if (    (tlinfo.VALUE = X_VALUE)
263       ) then
264         null;
265       else
266         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
267         app_exception.raise_exception;
268       end if;
269     end if;
270   end loop;
271   return;
272 end LOCK_ROW;
273 
274 procedure UPDATE_ROW (
275   X_CHOICE_ID in NUMBER,
276   X_LOOKUP_ID in NUMBER,
277   X_SEQUENCE_NUMBER in NUMBER,
278   X_START_DATE_ACTIVE in DATE,
279   X_END_DATE_ACTIVE in DATE,
280   X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
281   X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
282   X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
283   X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
284   X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
285   X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
286   X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
287   X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
288   X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
289   X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
290   X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
291   X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
292   X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
293   X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
294   X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
295   X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
296   X_SCORE in NUMBER,
297   X_VALUE in VARCHAR2,
298   X_LAST_UPDATE_DATE in DATE,
299   X_LAST_UPDATED_BY in NUMBER,
300   X_LAST_UPDATE_LOGIN in NUMBER
301 
302   ,X_DEFAULT_FLAG in VARCHAR2 DEFAULT NULL
303 ) is
304 begin
305   update CS_TP_CHOICES_B set
306     LOOKUP_ID = X_LOOKUP_ID,
307     SEQUENCE_NUMBER = X_SEQUENCE_NUMBER,
308     START_DATE_ACTIVE = X_START_DATE_ACTIVE,
309     END_DATE_ACTIVE = X_END_DATE_ACTIVE,
310     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
311     ATTRIBUTE1 = X_ATTRIBUTE1,
312     ATTRIBUTE2 = X_ATTRIBUTE2,
313     ATTRIBUTE3 = X_ATTRIBUTE3,
314     ATTRIBUTE4 = X_ATTRIBUTE4,
315     ATTRIBUTE5 = X_ATTRIBUTE5,
316     ATTRIBUTE6 = X_ATTRIBUTE6,
317     ATTRIBUTE7 = X_ATTRIBUTE7,
318     ATTRIBUTE8 = X_ATTRIBUTE8,
319     ATTRIBUTE9 = X_ATTRIBUTE9,
320     ATTRIBUTE10 = X_ATTRIBUTE10,
321     ATTRIBUTE11 = X_ATTRIBUTE11,
322     ATTRIBUTE12 = X_ATTRIBUTE12,
323     ATTRIBUTE13 = X_ATTRIBUTE13,
324     ATTRIBUTE14 = X_ATTRIBUTE14,
325     ATTRIBUTE15 = X_ATTRIBUTE15,
326     SCORE = X_SCORE,
327     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
328     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
329     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
330 
331     ,DEFAULT_CHOICE_FLAG = X_DEFAULT_FLAG
332   where CHOICE_ID = X_CHOICE_ID;
333 
334   if (sql%notfound) then
335     raise no_data_found;
336   end if;
337 
338   update CS_TP_CHOICES_TL set
339     VALUE = X_VALUE,
340     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
341     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
342     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
343     SOURCE_LANG = userenv('LANG')
344   where CHOICE_ID = X_CHOICE_ID
345   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
346 
347   if (sql%notfound) then
348     raise no_data_found;
349   end if;
350 end UPDATE_ROW;
351 
352 procedure DELETE_ROW (
353   X_CHOICE_ID in NUMBER
354 ) is
355 begin
356   delete from CS_TP_CHOICES_TL
357   where CHOICE_ID = X_CHOICE_ID;
358 
359   if (sql%notfound) then
360     raise no_data_found;
361   end if;
362 
363   delete from CS_TP_CHOICES_B
364   where CHOICE_ID = X_CHOICE_ID;
365 
366   if (sql%notfound) then
367     raise no_data_found;
368   end if;
369 end DELETE_ROW;
370 
371 procedure ADD_LANGUAGE
372 is
373 begin
374   delete from CS_TP_CHOICES_TL T
375   where not exists
376     (select NULL
377     from CS_TP_CHOICES_B B
378     where B.CHOICE_ID = T.CHOICE_ID
379     );
380 
381   update CS_TP_CHOICES_TL T set (
382       VALUE
383     ) = (select
384       B.VALUE
385     from CS_TP_CHOICES_TL B
386     where B.CHOICE_ID = T.CHOICE_ID
387     and B.LANGUAGE = T.SOURCE_LANG)
388   where (
389       T.CHOICE_ID,
390       T.LANGUAGE
391   ) in (select
392       SUBT.CHOICE_ID,
393       SUBT.LANGUAGE
394     from CS_TP_CHOICES_TL SUBB, CS_TP_CHOICES_TL SUBT
395     where SUBB.CHOICE_ID = SUBT.CHOICE_ID
396     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
397     and (SUBB.VALUE <> SUBT.VALUE
398   ));
399 
400   insert into CS_TP_CHOICES_TL (
401     CHOICE_ID,
402     VALUE,
403     CREATION_DATE,
407     LAST_UPDATE_LOGIN,
404     CREATED_BY,
405     LAST_UPDATE_DATE,
406     LAST_UPDATED_BY,
408     LANGUAGE,
409     SOURCE_LANG
410   ) select
411     B.CHOICE_ID,
412     B.VALUE,
413     B.CREATION_DATE,
414     B.CREATED_BY,
415     B.LAST_UPDATE_DATE,
416     B.LAST_UPDATED_BY,
417     B.LAST_UPDATE_LOGIN,
418     L.LANGUAGE_CODE,
419     B.SOURCE_LANG
420   from CS_TP_CHOICES_TL B, FND_LANGUAGES L
421   where L.INSTALLED_FLAG in ('I', 'B')
422   and B.LANGUAGE = userenv('LANG')
423   and not exists
424     (select NULL
425     from CS_TP_CHOICES_TL T
426     where T.CHOICE_ID = B.CHOICE_ID
427     and T.LANGUAGE = L.LANGUAGE_CODE);
428 end ADD_LANGUAGE;
429 
430 
431 PROCEDURE LOAD_ROW(
432         x_choice_id in number,
433         x_owner in varchar2,
434         x_value in varchar2,
435         x_lookup_id in number,
436         x_sequence_number in number,
437         x_start_date_active in date,
438         x_end_date_active in date,
439         x_score in number ) is
440 
441        l_user_id number;
442        l_rowid varchar(30);
443        l_value varchar(240);
444 begin
445 
446      if (x_owner = 'SEED') then
447             l_user_id := 1;
448      else
449             l_user_id := 0;
450      end if;
451 
452      l_value := x_value;
453 
454      CS_TP_CHOICES_PKG.Update_Row(
455      X_CHOICE_ID => x_choice_id,
456      X_LOOKUP_ID => x_lookup_id,
457      X_SEQUENCE_NUMBER => x_sequence_number,
458      X_START_DATE_ACTIVE => x_start_date_active,
459      X_END_DATE_ACTIVE => x_end_date_active,
460      x_score => x_score,
461      x_value => l_value,
462      X_Last_Update_Date => sysdate,
463      X_Last_Updated_By => l_user_id,
464      X_Last_Update_Login => 0);
465 
466      exception
467       when no_data_found then
468             CS_TP_CHOICES_PKG.Insert_Row(
469              X_ROWID => l_rowid,
470              X_CHOICE_ID => x_choice_id,
471              X_LOOKUP_ID => x_lookup_id,
472              X_SEQUENCE_NUMBER => x_sequence_number,
473              X_START_DATE_ACTIVE => x_start_date_active,
474              X_END_DATE_ACTIVE => x_end_date_active,
475              X_SCORE => x_score,
476              X_VALUE => l_value,
477              X_CREATION_DATE => sysdate,
478              X_CREATED_BY => 0,
479              X_Last_Update_Date => sysdate,
480              X_Last_Updated_By => l_user_id,
481              X_Last_Update_Login => 0);
482 
483 
484 end;
485 
486 
487 PROCEDURE TRANSLATE_ROW(
488         x_choice_id in number,
489     	x_owner in varchar2,
490         x_value in varchar2) is
491 
492 begin
493 
494      update CS_TP_CHOICES_TL set
495 	last_update_date  = sysdate,
496         last_updated_by   = decode(X_OWNER, 'SEED', 1, 0),
497         last_update_login = 0,
498         source_lang       = userenv('LANG')
499       where CHOICE_ID = to_number(X_CHOICE_ID)
500       and userenv('LANG') in (language, source_lang);
501 
502       exception
503        when no_data_found then null;
504 
505 end;
506 
507 end CS_TP_CHOICES_PKG;