1 PACKAGE BODY CAC_VIEW_COLLAB_DETAILS_PKG AS
2 /* $Header: jtfcvcdb.pls 115.2 2004/06/29 20:29:59 cijang noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_COLLAB_ID in NUMBER,
7 X_TASK_ID in NUMBER,
8 X_MEETING_MODE in VARCHAR2,
9 X_MEETING_ID in NUMBER,
10 X_MEETING_URL in VARCHAR2,
11 X_JOIN_URL in VARCHAR2,
12 X_PLAYBACK_URL in VARCHAR2,
13 X_DOWNLOAD_URL in VARCHAR2,
14 X_CHAT_URL in VARCHAR2,
15 X_IS_STANDALONE_LOCATION in VARCHAR2,
16 X_LOCATION in VARCHAR2,
17 X_DIAL_IN in VARCHAR2,
18 X_CREATION_DATE in DATE,
19 X_CREATED_BY in NUMBER,
20 X_LAST_UPDATE_DATE in DATE,
21 X_LAST_UPDATED_BY in NUMBER,
22 X_LAST_UPDATE_LOGIN in NUMBER) is
23
24 cursor C is select ROWID from CAC_VIEW_COLLAB_DETAILS
25 where COLLAB_ID = X_COLLAB_ID
26 ;
27 begin
28
29 insert into CAC_VIEW_COLLAB_DETAILS (
30 COLLAB_ID,
31 TASK_ID,
32 MEETING_MODE,
33 MEETING_ID,
34 MEETING_URL,
35 JOIN_URL,
36 PLAYBACK_URL,
37 DOWNLOAD_URL,
38 CHAT_URL,
39 IS_STANDALONE_LOCATION,
40 CREATION_DATE,
41 CREATED_BY,
42 LAST_UPDATE_DATE,
43 LAST_UPDATED_BY,
44 LAST_UPDATE_LOGIN
45 ) values (
46 X_COLLAB_ID,
47 X_TASK_ID,
48 X_MEETING_MODE,
49 X_MEETING_ID,
50 X_MEETING_URL,
51 X_JOIN_URL,
52 X_PLAYBACK_URL,
53 X_DOWNLOAD_URL,
54 X_CHAT_URL,
55 X_IS_STANDALONE_LOCATION,
56 X_CREATION_DATE,
57 X_CREATED_BY,
58 X_LAST_UPDATE_DATE,
59 X_LAST_UPDATED_BY,
60 X_LAST_UPDATE_LOGIN
61 );
62
63 insert into CAC_VIEW_COLLAB_DETAILS_TL (
64 CREATED_BY,
65 CREATION_DATE,
66 LAST_UPDATED_BY,
67 LAST_UPDATE_DATE,
68 LAST_UPDATE_LOGIN,
69 COLLAB_ID,
70 LOCATION,
71 DIAL_IN,
72 LANGUAGE,
73 SOURCE_LANG
74 ) select
75 X_CREATED_BY,
76 X_CREATION_DATE,
77 X_LAST_UPDATED_BY,
78 X_LAST_UPDATE_DATE,
79 X_LAST_UPDATE_LOGIN,
80 X_COLLAB_ID,
81 X_LOCATION,
82 X_DIAL_IN,
83 L.LANGUAGE_CODE,
84 userenv('LANG')
85 from FND_LANGUAGES L
86 where L.INSTALLED_FLAG in ('I', 'B')
87 and not exists
88 (select NULL
89 from CAC_VIEW_COLLAB_DETAILS_TL T
90 where T.COLLAB_ID = X_COLLAB_ID
91 and T.LANGUAGE = L.LANGUAGE_CODE);
92
93 open c;
94 fetch c into X_ROWID;
95 if (c%notfound) then
96 close c;
97 raise no_data_found;
98 end if;
99 close c;
100
101
102 end INSERT_ROW;
103
104 procedure LOCK_ROW (
105 X_COLLAB_ID in NUMBER,
106 X_TASK_ID in NUMBER,
107 X_MEETING_MODE in VARCHAR2,
108 X_MEETING_ID in NUMBER,
109 X_MEETING_URL in VARCHAR2,
110 X_JOIN_URL in VARCHAR2,
111 X_PLAYBACK_URL in VARCHAR2,
112 X_DOWNLOAD_URL in VARCHAR2,
113 X_CHAT_URL in VARCHAR2,
114 X_IS_STANDALONE_LOCATION in VARCHAR2,
115 X_LOCATION in VARCHAR2,
116 X_DIAL_IN in VARCHAR2
117 ) is
118 cursor c is select
119 TASK_ID,
120 MEETING_MODE,
121 MEETING_ID,
122 MEETING_URL,
123 JOIN_URL,
124 PLAYBACK_URL,
125 DOWNLOAD_URL,
126 CHAT_URL,
127 IS_STANDALONE_LOCATION
128 from CAC_VIEW_COLLAB_DETAILS
129 where COLLAB_ID = X_COLLAB_ID
130 for update of COLLAB_ID nowait;
131 recinfo c%rowtype;
132
133 cursor c1 is select
134 LOCATION,
135 DIAL_IN,
136 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
137 from CAC_VIEW_COLLAB_DETAILS_TL
138 where COLLAB_ID = X_COLLAB_ID
139 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
140 for update of COLLAB_ID nowait;
141 begin
142 open c;
143 fetch c into recinfo;
144 if (c%notfound) then
145 close c;
146 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
147 app_exception.raise_exception;
148 end if;
149 close c;
150 if ( (recinfo.TASK_ID = X_TASK_ID)
151 AND ((recinfo.MEETING_MODE = X_MEETING_MODE)
152 OR ((recinfo.MEETING_MODE is null) AND (X_MEETING_MODE is null)))
153 AND ((recinfo.MEETING_ID = X_MEETING_ID)
154 OR ((recinfo.MEETING_ID is null) AND (X_MEETING_ID is null)))
155 AND ((recinfo.MEETING_URL = X_MEETING_URL)
156 OR ((recinfo.MEETING_URL is null) AND (X_MEETING_URL is null)))
157 AND ((recinfo.JOIN_URL = X_JOIN_URL)
158 OR ((recinfo.JOIN_URL is null) AND (X_JOIN_URL is null)))
159 AND ((recinfo.PLAYBACK_URL = X_PLAYBACK_URL)
160 OR ((recinfo.PLAYBACK_URL is null) AND (X_PLAYBACK_URL is null)))
161 AND ((recinfo.DOWNLOAD_URL = X_DOWNLOAD_URL)
162 OR ((recinfo.DOWNLOAD_URL is null) AND (X_DOWNLOAD_URL is null)))
163 AND ((recinfo.CHAT_URL = X_CHAT_URL)
164 OR ((recinfo.CHAT_URL is null) AND (X_CHAT_URL is null)))
165 AND ((recinfo.IS_STANDALONE_LOCATION = X_IS_STANDALONE_LOCATION)
166 OR ((recinfo.IS_STANDALONE_LOCATION is null) AND (X_IS_STANDALONE_LOCATION is null)))
167 ) then
168 null;
169 else
170 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
171 app_exception.raise_exception;
172 end if;
173
174 for tlinfo in c1 loop
175 if (tlinfo.BASELANG = 'Y') then
176 if ( ((tlinfo.LOCATION = X_LOCATION)
177 OR ((tlinfo.LOCATION is null) AND (X_LOCATION is null)))
178 AND ((tlinfo.DIAL_IN = X_DIAL_IN)
179 OR ((tlinfo.DIAL_IN is null) AND (X_DIAL_IN is null)))
180 ) then
181 null;
182 else
183 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
184 app_exception.raise_exception;
185 end if;
186 end if;
187 end loop;
188 return;
189 end LOCK_ROW;
190
191 procedure UPDATE_ROW (
192 X_COLLAB_ID in NUMBER,
193 X_TASK_ID in NUMBER,
194 X_MEETING_MODE in VARCHAR2,
195 X_MEETING_ID in NUMBER,
196 X_MEETING_URL in VARCHAR2,
197 X_JOIN_URL in VARCHAR2,
198 X_PLAYBACK_URL in VARCHAR2,
199 X_DOWNLOAD_URL in VARCHAR2,
200 X_CHAT_URL in VARCHAR2,
201 X_IS_STANDALONE_LOCATION in VARCHAR2,
202 X_LOCATION in VARCHAR2,
203 X_DIAL_IN in VARCHAR2,
204 X_LAST_UPDATE_DATE in DATE,
205 X_LAST_UPDATED_BY in NUMBER,
206 X_LAST_UPDATE_LOGIN in NUMBER
207 ) is
208 begin
209 update CAC_VIEW_COLLAB_DETAILS set
210 TASK_ID = X_TASK_ID,
211 MEETING_MODE = X_MEETING_MODE,
212 MEETING_ID = X_MEETING_ID,
213 MEETING_URL = X_MEETING_URL,
214 JOIN_URL = X_JOIN_URL,
215 PLAYBACK_URL = X_PLAYBACK_URL,
216 DOWNLOAD_URL = X_DOWNLOAD_URL,
217 CHAT_URL = X_CHAT_URL,
218 IS_STANDALONE_LOCATION = X_IS_STANDALONE_LOCATION,
219 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
220 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
221 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
222 where COLLAB_ID = X_COLLAB_ID;
223
224 if (sql%notfound) then
225 raise no_data_found;
226 end if;
227
228 update CAC_VIEW_COLLAB_DETAILS_TL set
229 LOCATION = X_LOCATION,
230 DIAL_IN = X_DIAL_IN,
231 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
232 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
233 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
234 SOURCE_LANG = userenv('LANG')
235 where COLLAB_ID = X_COLLAB_ID
236 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
237
238 if (sql%notfound) then
239 raise no_data_found;
240 end if;
241 end UPDATE_ROW;
242
243 procedure DELETE_ROW (
244 X_COLLAB_ID in NUMBER
245 ) is
246 begin
247 delete from CAC_VIEW_COLLAB_DETAILS_TL
248 where COLLAB_ID = X_COLLAB_ID;
249
250 if (sql%notfound) then
251 raise no_data_found;
252 end if;
253
254 delete from CAC_VIEW_COLLAB_DETAILS
255 where COLLAB_ID = X_COLLAB_ID;
256
257 if (sql%notfound) then
258 raise no_data_found;
259 end if;
260 end DELETE_ROW;
261
262 procedure ADD_LANGUAGE
263 is
264 begin
265 delete from CAC_VIEW_COLLAB_DETAILS_TL T
266 where not exists
267 (select NULL
268 from CAC_VIEW_COLLAB_DETAILS B
269 where B.COLLAB_ID = T.COLLAB_ID
270 );
271
272 update CAC_VIEW_COLLAB_DETAILS_TL T set (
273 LOCATION,
274 DIAL_IN
275 ) = (select
276 B.LOCATION,
277 B.DIAL_IN
278 from CAC_VIEW_COLLAB_DETAILS_TL B
279 where B.COLLAB_ID = T.COLLAB_ID
280 and B.LANGUAGE = T.SOURCE_LANG)
281 where (
282 T.COLLAB_ID,
283 T.LANGUAGE
284 ) in (select
285 SUBT.COLLAB_ID,
286 SUBT.LANGUAGE
287 from CAC_VIEW_COLLAB_DETAILS_TL SUBB, CAC_VIEW_COLLAB_DETAILS_TL SUBT
288 where SUBB.COLLAB_ID = SUBT.COLLAB_ID
289 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
290 and (SUBB.LOCATION <> SUBT.LOCATION
291 or (SUBB.LOCATION is null and SUBT.LOCATION is not null)
292 or (SUBB.LOCATION is not null and SUBT.LOCATION is null)
293 or SUBB.DIAL_IN <> SUBT.DIAL_IN
294 or (SUBB.DIAL_IN is null and SUBT.DIAL_IN is not null)
295 or (SUBB.DIAL_IN is not null and SUBT.DIAL_IN is null)
296 ));
297
298 insert into CAC_VIEW_COLLAB_DETAILS_TL (
299 CREATED_BY,
300 CREATION_DATE,
301 LAST_UPDATED_BY,
302 LAST_UPDATE_DATE,
303 LAST_UPDATE_LOGIN,
304 COLLAB_ID,
305 LOCATION,
306 DIAL_IN,
307 LANGUAGE,
308 SOURCE_LANG
309 ) select /*+ ORDERED */
310 B.CREATED_BY,
311 B.CREATION_DATE,
312 B.LAST_UPDATED_BY,
313 B.LAST_UPDATE_DATE,
314 B.LAST_UPDATE_LOGIN,
315 B.COLLAB_ID,
316 B.LOCATION,
317 B.DIAL_IN,
318 L.LANGUAGE_CODE,
319 B.SOURCE_LANG
320 from CAC_VIEW_COLLAB_DETAILS_TL B, FND_LANGUAGES L
321 where L.INSTALLED_FLAG in ('I', 'B')
322 and B.LANGUAGE = userenv('LANG')
323 and not exists
324 (select NULL
325 from CAC_VIEW_COLLAB_DETAILS_TL T
326 where T.COLLAB_ID = B.COLLAB_ID
327 and T.LANGUAGE = L.LANGUAGE_CODE);
328 end ADD_LANGUAGE;
329
330
331
332 END CAC_VIEW_COLLAB_DETAILS_PKG;
[Home] [Help]