[Home] [Help]
PACKAGE BODY: APPS.CAC_SR_PERIOD_CATS_PKG
Source
1 package body CAC_SR_PERIOD_CATS_PKG as
2 /* $Header: cacsrperiodcatb.pls 120.2 2006/03/01 02:03:04 sbarat noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_PERIOD_CATEGORY_ID in NUMBER,
6 X_FREE_BUSY_TYPE in VARCHAR2,
7 X_DISPLAY_COLOR in VARCHAR2,
8 X_SHOW_PERIOD_DETAILS in VARCHAR2,
9 X_PERIOD_CATEGORY_NAME in VARCHAR2,
10 X_PERIOD_CATEGORY_DESC in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from CAC_SR_PERIOD_CATS_B
18 where PERIOD_CATEGORY_ID = X_PERIOD_CATEGORY_ID
19 ;
20 begin
21 insert into CAC_SR_PERIOD_CATS_B (
22 PERIOD_CATEGORY_ID,
23 FREE_BUSY_TYPE,
24 DISPLAY_COLOR,
25 SHOW_PERIOD_DETAILS,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN
31 ) values (
32 X_PERIOD_CATEGORY_ID,
33 X_FREE_BUSY_TYPE,
34 X_DISPLAY_COLOR,
35 X_SHOW_PERIOD_DETAILS,
36 X_CREATION_DATE,
37 X_CREATED_BY,
38 X_LAST_UPDATE_DATE,
39 X_LAST_UPDATED_BY,
40 X_LAST_UPDATE_LOGIN
41 );
42
43 insert into CAC_SR_PERIOD_CATS_TL (
44 PERIOD_CATEGORY_ID,
45 PERIOD_CATEGORY_NAME,
46 PERIOD_CATEGORY_DESC,
47 CREATED_BY,
48 CREATION_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATE_LOGIN,
52 LANGUAGE,
53 SOURCE_LANG
54 ) select
55 X_PERIOD_CATEGORY_ID,
56 X_PERIOD_CATEGORY_NAME,
57 X_PERIOD_CATEGORY_DESC,
58 X_CREATED_BY,
59 X_CREATION_DATE,
60 X_LAST_UPDATED_BY,
61 X_LAST_UPDATE_DATE,
62 X_LAST_UPDATE_LOGIN,
63 L.LANGUAGE_CODE,
64 userenv('LANG')
65 from FND_LANGUAGES L
66 where L.INSTALLED_FLAG in ('I', 'B')
67 and not exists
68 (select NULL
69 from CAC_SR_PERIOD_CATS_TL T
70 where T.PERIOD_CATEGORY_ID = X_PERIOD_CATEGORY_ID
71 and T.LANGUAGE = L.LANGUAGE_CODE);
72
73 open c;
74 fetch c into X_ROWID;
75 if (c%notfound) then
76 close c;
77 raise no_data_found;
78 end if;
79 close c;
80
81 end INSERT_ROW;
82
83 procedure LOCK_ROW (
84 X_PERIOD_CATEGORY_ID in NUMBER,
85 X_FREE_BUSY_TYPE in VARCHAR2,
86 X_DISPLAY_COLOR in VARCHAR2,
87 X_SHOW_PERIOD_DETAILS in VARCHAR2,
88 X_PERIOD_CATEGORY_NAME in VARCHAR2,
89 X_PERIOD_CATEGORY_DESC in VARCHAR2
90 ) is
91 cursor c is select
92 FREE_BUSY_TYPE,
93 DISPLAY_COLOR,
94 SHOW_PERIOD_DETAILS
95 from CAC_SR_PERIOD_CATS_B
96 where PERIOD_CATEGORY_ID = X_PERIOD_CATEGORY_ID
97 for update of PERIOD_CATEGORY_ID nowait;
98 recinfo c%rowtype;
99
100 cursor c1 is select
101 PERIOD_CATEGORY_NAME,
102 PERIOD_CATEGORY_DESC,
103 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
104 from CAC_SR_PERIOD_CATS_TL
105 where PERIOD_CATEGORY_ID = X_PERIOD_CATEGORY_ID
106 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
107 for update of PERIOD_CATEGORY_ID nowait;
108 begin
109 open c;
110 fetch c into recinfo;
111 if (c%notfound) then
112 close c;
113 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
114 app_exception.raise_exception;
115 end if;
116 close c;
117 if ( (recinfo.FREE_BUSY_TYPE = X_FREE_BUSY_TYPE)
118 AND (recinfo.DISPLAY_COLOR = X_DISPLAY_COLOR)
119 AND (recinfo.SHOW_PERIOD_DETAILS = X_SHOW_PERIOD_DETAILS)
120 ) then
121 null;
122 else
123 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
124 app_exception.raise_exception;
125 end if;
126
127 for tlinfo in c1 loop
128 if (tlinfo.BASELANG = 'Y') then
129 if ( (tlinfo.PERIOD_CATEGORY_NAME = X_PERIOD_CATEGORY_NAME)
130 AND ((tlinfo.PERIOD_CATEGORY_DESC = X_PERIOD_CATEGORY_DESC)
131 OR ((tlinfo.PERIOD_CATEGORY_DESC is null) AND (X_PERIOD_CATEGORY_DESC is null)))
132 ) then
133 null;
134 else
135 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
136 app_exception.raise_exception;
137 end if;
138 end if;
139 end loop;
140 return;
141 end LOCK_ROW;
142
143 procedure UPDATE_ROW (
144 X_PERIOD_CATEGORY_ID in NUMBER,
145 X_FREE_BUSY_TYPE in VARCHAR2,
146 X_DISPLAY_COLOR in VARCHAR2,
147 X_SHOW_PERIOD_DETAILS in VARCHAR2,
148 X_PERIOD_CATEGORY_NAME in VARCHAR2,
149 X_PERIOD_CATEGORY_DESC in VARCHAR2,
150 X_LAST_UPDATE_DATE in DATE,
151 X_LAST_UPDATED_BY in NUMBER,
152 X_LAST_UPDATE_LOGIN in NUMBER
153 ) is
154 begin
155 update CAC_SR_PERIOD_CATS_B set
156 FREE_BUSY_TYPE = X_FREE_BUSY_TYPE,
157 DISPLAY_COLOR = X_DISPLAY_COLOR,
158 SHOW_PERIOD_DETAILS = X_SHOW_PERIOD_DETAILS,
159 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
160 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
161 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
162 where PERIOD_CATEGORY_ID = X_PERIOD_CATEGORY_ID;
163
164 if (sql%notfound) then
165 raise no_data_found;
166 end if;
167
168 update CAC_SR_PERIOD_CATS_TL set
169 PERIOD_CATEGORY_NAME = X_PERIOD_CATEGORY_NAME,
170 PERIOD_CATEGORY_DESC = X_PERIOD_CATEGORY_DESC,
171 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
172 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
173 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
174 SOURCE_LANG = userenv('LANG')
175 where PERIOD_CATEGORY_ID = X_PERIOD_CATEGORY_ID
176 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
177
178 if (sql%notfound) then
179 raise no_data_found;
180 end if;
181 end UPDATE_ROW;
182
183 procedure DELETE_ROW (
184 X_PERIOD_CATEGORY_ID in NUMBER
185 ) is
186 begin
187 delete from CAC_SR_PERIOD_CATS_TL
188 where PERIOD_CATEGORY_ID = X_PERIOD_CATEGORY_ID;
189
190 if (sql%notfound) then
191 raise no_data_found;
192 end if;
193
194 delete from CAC_SR_PERIOD_CATS_B
195 where PERIOD_CATEGORY_ID = X_PERIOD_CATEGORY_ID;
196
197 if (sql%notfound) then
198 raise no_data_found;
199 end if;
200 end DELETE_ROW;
201
202 procedure ADD_LANGUAGE
203 is
204 begin
205 delete from CAC_SR_PERIOD_CATS_TL T
206 where not exists
207 (select NULL
208 from CAC_SR_PERIOD_CATS_B B
209 where B.PERIOD_CATEGORY_ID = T.PERIOD_CATEGORY_ID
210 );
211
212 update CAC_SR_PERIOD_CATS_TL T set (
213 PERIOD_CATEGORY_NAME,
214 PERIOD_CATEGORY_DESC
215 ) = (select
216 B.PERIOD_CATEGORY_NAME,
217 B.PERIOD_CATEGORY_DESC
218 from CAC_SR_PERIOD_CATS_TL B
219 where B.PERIOD_CATEGORY_ID = T.PERIOD_CATEGORY_ID
220 and B.LANGUAGE = T.SOURCE_LANG)
221 where (
222 T.PERIOD_CATEGORY_ID,
223 T.LANGUAGE
224 ) in (select
225 SUBT.PERIOD_CATEGORY_ID,
226 SUBT.LANGUAGE
227 from CAC_SR_PERIOD_CATS_TL SUBB, CAC_SR_PERIOD_CATS_TL SUBT
228 where SUBB.PERIOD_CATEGORY_ID = SUBT.PERIOD_CATEGORY_ID
229 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
230 and (SUBB.PERIOD_CATEGORY_NAME <> SUBT.PERIOD_CATEGORY_NAME
231 or SUBB.PERIOD_CATEGORY_DESC <> SUBT.PERIOD_CATEGORY_DESC
232 or (SUBB.PERIOD_CATEGORY_DESC is null and SUBT.PERIOD_CATEGORY_DESC is not null)
233 or (SUBB.PERIOD_CATEGORY_DESC is not null and SUBT.PERIOD_CATEGORY_DESC is null)
234 ));
235
236 insert into CAC_SR_PERIOD_CATS_TL (
237 PERIOD_CATEGORY_ID,
238 PERIOD_CATEGORY_NAME,
239 PERIOD_CATEGORY_DESC,
240 CREATED_BY,
241 CREATION_DATE,
242 LAST_UPDATED_BY,
243 LAST_UPDATE_DATE,
244 LAST_UPDATE_LOGIN,
245 LANGUAGE,
246 SOURCE_LANG
247 ) select /*+ ORDERED */
248 B.PERIOD_CATEGORY_ID,
249 B.PERIOD_CATEGORY_NAME,
250 B.PERIOD_CATEGORY_DESC,
251 B.CREATED_BY,
252 B.CREATION_DATE,
253 B.LAST_UPDATED_BY,
254 B.LAST_UPDATE_DATE,
255 B.LAST_UPDATE_LOGIN,
256 L.LANGUAGE_CODE,
257 B.SOURCE_LANG
258 from CAC_SR_PERIOD_CATS_TL B, FND_LANGUAGES L
259 where L.INSTALLED_FLAG in ('I', 'B')
260 and B.LANGUAGE = userenv('LANG')
261 and not exists
262 (select NULL
263 from CAC_SR_PERIOD_CATS_TL T
264 where T.PERIOD_CATEGORY_ID = B.PERIOD_CATEGORY_ID
265 and T.LANGUAGE = L.LANGUAGE_CODE);
266 end ADD_LANGUAGE;
267
268 /************ Start of addition by SBARAT on 01/03/2006 for bug# 5031486 ***********/
269 PROCEDURE TRANSLATE_ROW(
270 X_PERIOD_CATEGORY_ID IN NUMBER,
271 X_PERIOD_CATEGORY_NAME IN VARCHAR2,
272 X_PERIOD_CATEGORY_DESC IN VARCHAR2,
273 X_OWNER IN VARCHAR2)
274 IS
275 l_user_id NUMBER := 0;
276 BEGIN
277 IF x_owner = 'SEED'
278 THEN
279 l_user_id := 1;
280 END IF;
281
282 UPDATE cac_sr_period_cats_tl
283 SET period_category_name = NVL(X_PERIOD_CATEGORY_NAME, period_category_name ) ,
284 period_category_desc = NVL(X_PERIOD_CATEGORY_DESC, period_category_desc),
285 last_updated_by = l_user_id,
286 last_update_date = sysdate,
287 last_update_login = 0,
288 source_lang = USERENV ('LANG')
289 WHERE period_category_id = x_period_category_id
290 And USERENV('LANG') In (LANGUAGE, SOURCE_LANG);
291
292 IF (SQL%NOTFOUND)
293 THEN
294 Raise NO_DATA_FOUND;
295 END IF;
296
297 END TRANSLATE_ROW;
298
299
300 PROCEDURE LOAD_ROW (
301 X_PERIOD_CATEGORY_ID IN NUMBER,
302 X_FREE_BUSY_TYPE IN VARCHAR2,
303 X_DISPLAY_COLOR IN VARCHAR2,
304 X_SHOW_PERIOD_DETAILS IN VARCHAR2,
305 X_PERIOD_CATEGORY_NAME IN VARCHAR2,
306 X_PERIOD_CATEGORY_DESC IN VARCHAR2,
307 X_OWNER IN VARCHAR2
308 )
309 IS
310 l_user_id NUMBER := 0;
311 l_period_category_id NUMBER;
312 l_rowid ROWID;
313 BEGIN
314
315 IF x_owner = 'SEED'
316 THEN
317 l_user_id := 1;
318 END IF;
319
320
321 SELECT period_category_id
322 INTO l_period_category_id
323 FROM cac_sr_period_cats_b
324 WHERE period_category_id = x_period_category_id;
325
326
327 UPDATE cac_sr_period_cats_b
328 SET free_busy_type = x_free_busy_type,
329 display_color = x_display_color,
330 show_period_details = x_show_period_details,
331 last_updated_by = l_user_id,
332 last_update_date = sysdate,
333 last_update_login = 0
334 WHERE period_category_id = l_period_category_id;
335
336 UPDATE cac_sr_period_cats_tl
337 SET period_category_name = x_period_category_name,
338 period_category_desc = x_period_category_desc,
339 last_updated_by = l_user_id,
340 last_update_date = sysdate,
341 last_update_login = 0,
342 source_lang = USERENV ('LANG')
343 WHERE period_category_id = l_period_category_id
344 And USERENV ('LANG') In (LANGUAGE, SOURCE_LANG);
345
346 EXCEPTION
347 WHEN no_data_found THEN
348 CAC_SR_PERIOD_CATS_PKG.INSERT_ROW
349 (
350 x_rowid => l_rowid ,
351 x_period_category_id => x_period_category_id,
352 x_free_busy_type => x_free_busy_type,
353 x_display_color => x_display_color,
354 x_show_period_details => x_show_period_details,
355 x_period_category_name => x_period_category_name,
356 x_period_category_desc => x_period_category_desc,
357 x_creation_date => SYSDATE,
358 x_created_by => l_user_id,
359 x_last_update_date => SYSDATE,
360 x_last_updated_by => l_user_id,
361 x_last_update_login => 0
362 );
363
364 END LOAD_ROW ;
365 /************ End of addition by SBARAT on 01/03/2006 for bug# 5031486 ***********/
366
367 end CAC_SR_PERIOD_CATS_PKG;