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.JTF_RS_TEAMS_PKG

Source


1 package body JTF_RS_TEAMS_PKG as
2 /* $Header: jtfrsttb.pls 120.0 2005/05/11 08:22:36 appldev ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_TEAM_ID in NUMBER,
6   X_TEAM_NUMBER in VARCHAR2,
7   X_EMAIL_ADDRESS in VARCHAR2,
8   X_EXCLUSIVE_FLAG in VARCHAR2,
9   X_START_DATE_ACTIVE in DATE,
10   X_END_DATE_ACTIVE in DATE,
11   X_ATTRIBUTE1 in VARCHAR2,
12   X_ATTRIBUTE2 in VARCHAR2,
13   X_ATTRIBUTE3 in VARCHAR2,
14   X_ATTRIBUTE4 in VARCHAR2,
15   X_ATTRIBUTE5 in VARCHAR2,
16   X_ATTRIBUTE6 in VARCHAR2,
17   X_ATTRIBUTE7 in VARCHAR2,
18   X_ATTRIBUTE8 in VARCHAR2,
19   X_ATTRIBUTE9 in VARCHAR2,
20   X_ATTRIBUTE10 in VARCHAR2,
21   X_ATTRIBUTE11 in VARCHAR2,
22   X_ATTRIBUTE12 in VARCHAR2,
23   X_ATTRIBUTE13 in VARCHAR2,
24   X_ATTRIBUTE14 in VARCHAR2,
25   X_ATTRIBUTE15 in VARCHAR2,
26   X_ATTRIBUTE_CATEGORY in VARCHAR2,
27   X_TEAM_NAME in VARCHAR2,
28   X_TEAM_DESC in VARCHAR2,
29   X_CREATION_DATE in DATE,
30   X_CREATED_BY in NUMBER,
31   X_LAST_UPDATE_DATE in DATE,
32   X_LAST_UPDATED_BY in NUMBER,
33   X_LAST_UPDATE_LOGIN in NUMBER
34 ) is
35   cursor C is select ROWID from JTF_RS_TEAMS_B
36     where TEAM_ID = X_TEAM_ID
37     ;
38 begin
39   insert into JTF_RS_TEAMS_B (
40     TEAM_ID,
41     TEAM_NUMBER,
42     EMAIL_ADDRESS,
43     EXCLUSIVE_FLAG,
44     START_DATE_ACTIVE,
45     END_DATE_ACTIVE,
46     OBJECT_VERSION_NUMBER,
47     ATTRIBUTE1,
48     ATTRIBUTE2,
49     ATTRIBUTE3,
50     ATTRIBUTE4,
51     ATTRIBUTE5,
52     ATTRIBUTE6,
53     ATTRIBUTE7,
54     ATTRIBUTE8,
55     ATTRIBUTE9,
56     ATTRIBUTE10,
57     ATTRIBUTE11,
58     ATTRIBUTE12,
59     ATTRIBUTE13,
60     ATTRIBUTE14,
61     ATTRIBUTE15,
62     ATTRIBUTE_CATEGORY,
63     CREATION_DATE,
64     CREATED_BY,
65     LAST_UPDATE_DATE,
66     LAST_UPDATED_BY,
67     LAST_UPDATE_LOGIN
68   ) values (
69     X_TEAM_ID,
70     X_TEAM_NUMBER,
71     X_EMAIL_ADDRESS,
72     X_EXCLUSIVE_FLAG,
73     X_START_DATE_ACTIVE,
74     X_END_DATE_ACTIVE,
75     1,
76     X_ATTRIBUTE1,
77     X_ATTRIBUTE2,
78     X_ATTRIBUTE3,
79     X_ATTRIBUTE4,
80     X_ATTRIBUTE5,
81     X_ATTRIBUTE6,
82     X_ATTRIBUTE7,
83     X_ATTRIBUTE8,
84     X_ATTRIBUTE9,
85     X_ATTRIBUTE10,
86     X_ATTRIBUTE11,
87     X_ATTRIBUTE12,
88     X_ATTRIBUTE13,
89     X_ATTRIBUTE14,
90     X_ATTRIBUTE15,
91     X_ATTRIBUTE_CATEGORY,
92     X_CREATION_DATE,
93     X_CREATED_BY,
94     X_LAST_UPDATE_DATE,
95     X_LAST_UPDATED_BY,
96     X_LAST_UPDATE_LOGIN
97   );
98 
99   insert into JTF_RS_TEAMS_TL (
100     TEAM_ID,
101     CREATED_BY,
102     CREATION_DATE,
103     LAST_UPDATED_BY,
104     LAST_UPDATE_DATE,
105     LAST_UPDATE_LOGIN,
106     TEAM_NAME,
107     TEAM_DESC,
108     LANGUAGE,
109     SOURCE_LANG
110   ) select
111     X_TEAM_ID,
112     X_CREATED_BY,
113     X_CREATION_DATE,
114     X_LAST_UPDATED_BY,
115     X_LAST_UPDATE_DATE,
116     X_LAST_UPDATE_LOGIN,
117     X_TEAM_NAME,
118     X_TEAM_DESC,
119     L.LANGUAGE_CODE,
120     userenv('LANG')
121   from FND_LANGUAGES L
122   where L.INSTALLED_FLAG in ('I', 'B')
123   and not exists
124     (select NULL
125     from JTF_RS_TEAMS_TL T
126     where T.TEAM_ID = X_TEAM_ID
127     and T.LANGUAGE = L.LANGUAGE_CODE);
128 
129   open c;
130   fetch c into X_ROWID;
131   if (c%notfound) then
132     close c;
133     raise no_data_found;
134   end if;
135   close c;
136 
137 end INSERT_ROW;
138 
139 procedure LOCK_ROW (
140   X_TEAM_ID in NUMBER,
141   X_OBJECT_VERSION_NUMBER in NUMBER
142 ) is
143   cursor c is select
144       OBJECT_VERSION_NUMBER
145     from JTF_RS_TEAMS_VL
146     where TEAM_ID = X_TEAM_ID
147     and OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
148     for update of TEAM_ID nowait;
149   recinfo c%rowtype;
150 
151 begin
152   open c;
153   fetch c into recinfo;
154   if (c%notfound) then
155     close c;
156     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
157     app_exception.raise_exception;
158   end if;
159 
160   close c;
161 
162   if recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER then
163     null;
164   else
165     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
166     app_exception.raise_exception;
167   end if;
168 
169 end LOCK_ROW;
170 
171 procedure UPDATE_ROW (
172   X_TEAM_ID in NUMBER,
173   X_TEAM_NUMBER in VARCHAR2,
174   X_EMAIL_ADDRESS in VARCHAR2,
175   X_EXCLUSIVE_FLAG in VARCHAR2,
176   X_START_DATE_ACTIVE in DATE,
177   X_END_DATE_ACTIVE in DATE,
178   X_OBJECT_VERSION_NUMBER in NUMBER,
179   X_ATTRIBUTE1 in VARCHAR2,
180   X_ATTRIBUTE2 in VARCHAR2,
181   X_ATTRIBUTE3 in VARCHAR2,
182   X_ATTRIBUTE4 in VARCHAR2,
183   X_ATTRIBUTE5 in VARCHAR2,
184   X_ATTRIBUTE6 in VARCHAR2,
185   X_ATTRIBUTE7 in VARCHAR2,
186   X_ATTRIBUTE8 in VARCHAR2,
187   X_ATTRIBUTE9 in VARCHAR2,
188   X_ATTRIBUTE10 in VARCHAR2,
189   X_ATTRIBUTE11 in VARCHAR2,
190   X_ATTRIBUTE12 in VARCHAR2,
191   X_ATTRIBUTE13 in VARCHAR2,
192   X_ATTRIBUTE14 in VARCHAR2,
193   X_ATTRIBUTE15 in VARCHAR2,
194   X_ATTRIBUTE_CATEGORY in VARCHAR2,
195   X_TEAM_NAME in VARCHAR2,
196   X_TEAM_DESC in VARCHAR2,
197   X_LAST_UPDATE_DATE in DATE,
198   X_LAST_UPDATED_BY in NUMBER,
199   X_LAST_UPDATE_LOGIN in NUMBER
200 ) is
201 begin
202   update JTF_RS_TEAMS_B set
203     TEAM_NUMBER = X_TEAM_NUMBER,
204     EMAIL_ADDRESS = X_EMAIL_ADDRESS,
205     EXCLUSIVE_FLAG = X_EXCLUSIVE_FLAG,
206     START_DATE_ACTIVE = X_START_DATE_ACTIVE,
207     END_DATE_ACTIVE = X_END_DATE_ACTIVE,
208     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
209     ATTRIBUTE1 = X_ATTRIBUTE1,
210     ATTRIBUTE2 = X_ATTRIBUTE2,
211     ATTRIBUTE3 = X_ATTRIBUTE3,
212     ATTRIBUTE4 = X_ATTRIBUTE4,
213     ATTRIBUTE5 = X_ATTRIBUTE5,
214     ATTRIBUTE6 = X_ATTRIBUTE6,
215     ATTRIBUTE7 = X_ATTRIBUTE7,
216     ATTRIBUTE8 = X_ATTRIBUTE8,
217     ATTRIBUTE9 = X_ATTRIBUTE9,
218     ATTRIBUTE10 = X_ATTRIBUTE10,
219     ATTRIBUTE11 = X_ATTRIBUTE11,
220     ATTRIBUTE12 = X_ATTRIBUTE12,
221     ATTRIBUTE13 = X_ATTRIBUTE13,
222     ATTRIBUTE14 = X_ATTRIBUTE14,
223     ATTRIBUTE15 = X_ATTRIBUTE15,
224     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
225     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
226     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
227     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
228   where TEAM_ID = X_TEAM_ID;
229 
230   if (sql%notfound) then
231     raise no_data_found;
232   end if;
233 
234   update JTF_RS_TEAMS_TL set
235     TEAM_NAME = X_TEAM_NAME,
236     TEAM_DESC = X_TEAM_DESC,
237     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
238     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
239     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
240     SOURCE_LANG = userenv('LANG')
241   where TEAM_ID = X_TEAM_ID
242   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
243 
244   if (sql%notfound) then
245     raise no_data_found;
246   end if;
247 end UPDATE_ROW;
248 
249 procedure DELETE_ROW (
250   X_TEAM_ID in NUMBER
251 ) is
252 begin
253   delete from JTF_RS_TEAMS_TL
254   where TEAM_ID = X_TEAM_ID;
255 
256   if (sql%notfound) then
257     raise no_data_found;
258   end if;
259 
260   delete from JTF_RS_TEAMS_B
261   where TEAM_ID = X_TEAM_ID;
262 
263   if (sql%notfound) then
264     raise no_data_found;
265   end if;
266 end DELETE_ROW;
267 
268 procedure ADD_LANGUAGE
269 is
270 begin
271   delete from JTF_RS_TEAMS_TL T
272   where not exists
273     (select NULL
274     from JTF_RS_TEAMS_B B
275     where B.TEAM_ID = T.TEAM_ID
276     );
277 
278   update JTF_RS_TEAMS_TL T set (
279       TEAM_NAME,
280       TEAM_DESC
281     ) = (select
282       B.TEAM_NAME,
283       B.TEAM_DESC
284     from JTF_RS_TEAMS_TL B
285     where B.TEAM_ID = T.TEAM_ID
286     and B.LANGUAGE = T.SOURCE_LANG)
287   where (
288       T.TEAM_ID,
289       T.LANGUAGE
290   ) in (select
291       SUBT.TEAM_ID,
292       SUBT.LANGUAGE
293     from JTF_RS_TEAMS_TL SUBB, JTF_RS_TEAMS_TL SUBT
294     where SUBB.TEAM_ID = SUBT.TEAM_ID
295     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
296     and (SUBB.TEAM_NAME <> SUBT.TEAM_NAME
297       or SUBB.TEAM_DESC <> SUBT.TEAM_DESC
298       or (SUBB.TEAM_DESC is null and SUBT.TEAM_DESC is not null)
299       or (SUBB.TEAM_DESC is not null and SUBT.TEAM_DESC is null)
300   ));
301 
302   insert into JTF_RS_TEAMS_TL (
303     TEAM_ID,
304     CREATED_BY,
305     CREATION_DATE,
306     LAST_UPDATED_BY,
307     LAST_UPDATE_DATE,
308     LAST_UPDATE_LOGIN,
309     TEAM_NAME,
310     TEAM_DESC,
311     LANGUAGE,
312     SOURCE_LANG
313   ) select
314     B.TEAM_ID,
315     B.CREATED_BY,
316     B.CREATION_DATE,
317     B.LAST_UPDATED_BY,
318     B.LAST_UPDATE_DATE,
319     B.LAST_UPDATE_LOGIN,
320     B.TEAM_NAME,
321     B.TEAM_DESC,
322     L.LANGUAGE_CODE,
323     B.SOURCE_LANG
324   from JTF_RS_TEAMS_TL B, FND_LANGUAGES L
325   where L.INSTALLED_FLAG in ('I', 'B')
326   and B.LANGUAGE = userenv('LANG')
327   and not exists
328     (select NULL
329     from JTF_RS_TEAMS_TL T
330     where T.TEAM_ID = B.TEAM_ID
331     and T.LANGUAGE = L.LANGUAGE_CODE);
332 end ADD_LANGUAGE;
333 
334 
335 Procedure TRANSLATE_ROW
336 (x_team_id  in number,
337  x_team_name in varchar2,
338  x_team_desc in varchar2,
339  x_Last_update_date in date,
340  x_last_updated_by in number,
341  x_last_update_login in number)
342 is
343 begin
344 
345 Update jtf_rs_teams_tl set
346 team_name		= nvl(x_team_name,team_name),
347 team_desc		= nvl(x_team_desc,team_desc),
348 last_update_date	= nvl(x_last_update_date,sysdate),
349 last_updated_by		= x_last_updated_by,
350 last_update_login	= 0,
351 source_lang		= userenv('LANG')
352 where team_id		= x_team_id
353 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
354 
355   if (sql%notfound) then
356     raise no_data_found;
357   end if;
358 end TRANSLATE_ROW;
359 
360 end JTF_RS_TEAMS_PKG;