[Home] [Help]
PACKAGE BODY: APPS.CS_SR_STATUS_GROUPS_PKG
Source
1 PACKAGE BODY CS_SR_STATUS_GROUPS_PKG AS
2 /* $Header: cststgrb.pls 120.0 2006/02/28 11:53:50 spusegao noship $ */
3
4 PROCEDURE INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2 ,
6 X_STATUS_GROUP_ID in NUMBER,
7 X_SECURITY_GROUP_ID in NUMBER,
8 X_TRANSITION_IND in VARCHAR2,
9 X_OBJECT_VERSION_NUMBER in NUMBER,
10 X_ORIG_SYSTEM_REFERENCE_ID in NUMBER,
11 X_END_DATE in DATE,
12 X_START_DATE in DATE,
13 X_DEFAULT_INCIDENT_STATUS_ID in NUMBER,
14 X_GROUP_NAME in VARCHAR2,
15 X_DESCRIPTION in VARCHAR2,
16 X_LANGUAGE in VARCHAR2,
17 X_SOURCE_LANG 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)
23
24 IS
25 cursor C is select ROWID from CS_SR_STATUS_GROUPS_B
26 where STATUS_GROUP_ID = X_STATUS_GROUP_ID;
27
28 cursor c1 is
29 select cs_sr_status_groups_b_s.nextval
30 from dual;
31
32 l_status_group_id NUMBER ;
33
34 BEGIN
35 if ( x_status_group_id IS NULL OR x_status_group_id = FND_API.G_MISS_NUM ) then
36 open c1;
37 fetch c1 into l_status_group_id;
38 close c1;
39 end if;
40
41 INSERT INTO CS_SR_STATUS_GROUPS_B (
42 STATUS_GROUP_ID,
43 SECURITY_GROUP_ID,
44 TRANSITION_IND,
45 DEFAULT_INCIDENT_STATUS_ID,
46 ORIG_SYSTEM_REFERENCE_ID,
47 END_DATE,
48 START_DATE,
49 CREATION_DATE,
50 CREATED_BY,
51 LAST_UPDATE_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_LOGIN,
54 OBJECT_VERSION_NUMBER )
55 VALUES (
56 NVL(X_STATUS_GROUP_ID,l_status_group_id ),
57 X_SECURITY_GROUP_ID,
58 X_TRANSITION_IND,
59 X_DEFAULT_INCIDENT_STATUS_ID,
60 X_ORIG_SYSTEM_REFERENCE_ID,
61 X_END_DATE,
62 X_START_DATE,
63 X_CREATION_DATE,
64 X_CREATED_BY,
65 X_LAST_UPDATE_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_LOGIN,
68 X_OBJECT_VERSION_NUMBER );
69
70
71 INSERT INTO CS_SR_STATUS_GROUPS_TL (
72 STATUS_GROUP_ID,
73 GROUP_NAME,
74 DESCRIPTION,
75 LANGUAGE,
76 SOURCE_LANG,
77 CREATION_DATE,
78 CREATED_BY,
79 LAST_UPDATE_DATE,
80 LAST_UPDATED_BY,
81 LAST_UPDATE_LOGIN )
82 SELECT
83 NVL(X_STATUS_GROUP_ID,l_status_group_id ),
84 X_GROUP_NAME,
85 X_DESCRIPTION,
86 L.LANGUAGE_CODE,
87 userenv('LANG'),
88 X_CREATION_DATE,
89 X_CREATED_BY,
90 X_LAST_UPDATE_DATE,
91 X_LAST_UPDATED_BY,
92 X_LAST_UPDATE_LOGIN
93 FROM FND_LANGUAGES L
94 WHERE L.INSTALLED_FLAG in ('I', 'B')
95 AND NOT EXISTS ( SELECT NULL
96 FROM CS_SR_STATUS_GROUPS_TL T
97 WHERE T.STATUS_GROUP_ID = X_STATUS_GROUP_ID
98 AND T.LANGUAGE = L.LANGUAGE_CODE);
99
100 open c;
101 fetch c into X_ROWID;
102 if (c%notfound) then
103 close c;
104 raise no_data_found;
105 end if;
106 close c;
107
108 END INSERT_ROW;
109
110
111 PROCEDURE LOCK_ROW (
112 X_STATUS_GROUP_ID in NUMBER,
113 X_SECURITY_GROUP_ID in NUMBER,
114 X_TRANSITION_IND in VARCHAR2,
115 X_OBJECT_VERSION_NUMBER in NUMBER,
116 X_ORIG_SYSTEM_REFERENCE_ID in NUMBER,
117 X_END_DATE in DATE,
118 X_START_DATE in DATE,
119 X_DEFAULT_INCIDENT_STATUS_ID in NUMBER,
120 X_GROUP_NAME in VARCHAR2,
121 X_DESCRIPTION in VARCHAR2,
122 X_LANGUAGE in VARCHAR2,
123 X_SOURCE_LANG in VARCHAR2
124 ) is
125 cursor c is select
126 SECURITY_GROUP_ID,
127 TRANSITION_IND,
128 OBJECT_VERSION_NUMBER,
129 ORIG_SYSTEM_REFERENCE_ID,
130 END_DATE,
131 START_DATE,
132 DEFAULT_INCIDENT_STATUS_ID
133 from CS_SR_STATUS_GROUPS_B
134 where STATUS_GROUP_ID = X_STATUS_GROUP_ID
135 for update of STATUS_GROUP_ID nowait;
136 recinfo c%rowtype;
137
138 cursor c1 is select
139 GROUP_NAME,
140 DESCRIPTION,
141 LANGUAGE,
142 SOURCE_LANG,
143 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
144 from CS_SR_STATUS_GROUPS_TL
145 where STATUS_GROUP_ID = X_STATUS_GROUP_ID
146 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
147 for update of STATUS_GROUP_ID nowait;
148 begin
149 open c;
150 fetch c into recinfo;
151 if (c%notfound) then
152 close c;
153 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
154 app_exception.raise_exception;
155 end if;
156 close c;
157 if ( ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
158 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
159 AND ((recinfo.TRANSITION_IND = X_TRANSITION_IND)
160 OR ((recinfo.TRANSITION_IND is null) AND (X_TRANSITION_IND is null)))
161 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
162 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
163 AND ((recinfo.ORIG_SYSTEM_REFERENCE_ID = X_ORIG_SYSTEM_REFERENCE_ID)
164 OR ((recinfo.ORIG_SYSTEM_REFERENCE_ID is null) AND (X_ORIG_SYSTEM_REFERENCE_ID is null)))
165 AND ((recinfo.END_DATE = X_END_DATE)
166 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
167 AND ((recinfo.START_DATE = X_START_DATE)
168 OR ((recinfo.START_DATE is null) AND (X_START_DATE is null)))
169 AND ((recinfo.DEFAULT_INCIDENT_STATUS_ID = X_DEFAULT_INCIDENT_STATUS_ID)
170 OR ((recinfo.DEFAULT_INCIDENT_STATUS_ID is null) AND (X_DEFAULT_INCIDENT_STATUS_ID is null)))
171 ) then
172 null;
173 else
174 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
175 app_exception.raise_exception;
176 end if;
177
178 for tlinfo in c1 loop
179 if (tlinfo.BASELANG = 'Y') then
180 if ( (tlinfo.GROUP_NAME = X_GROUP_NAME)
181 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
182 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
183 AND (tlinfo.LANGUAGE = X_LANGUAGE)
184 AND (tlinfo.SOURCE_LANG = X_SOURCE_LANG)
185 ) then
186 null;
187 else
188 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
189 app_exception.raise_exception;
190 end if;
191 end if;
192 end loop;
193 return;
194 end LOCK_ROW;
195
196 procedure UPDATE_ROW (
197 X_STATUS_GROUP_ID in NUMBER,
198 X_SECURITY_GROUP_ID in NUMBER,
199 X_TRANSITION_IND in VARCHAR2,
200 X_OBJECT_VERSION_NUMBER in NUMBER,
201 X_ORIG_SYSTEM_REFERENCE_ID in NUMBER,
202 X_END_DATE in DATE,
203 X_START_DATE in DATE,
204 X_DEFAULT_INCIDENT_STATUS_ID in NUMBER,
205 X_GROUP_NAME in VARCHAR2,
206 X_DESCRIPTION in VARCHAR2,
207 X_LANGUAGE in VARCHAR2,
208 X_SOURCE_LANG in VARCHAR2,
209 X_LAST_UPDATE_DATE in DATE,
210 X_LAST_UPDATED_BY in NUMBER,
211 X_LAST_UPDATE_LOGIN in NUMBER) is
212
213 begin
214
215 UPDATE CS_SR_STATUS_GROUPS_B set
216 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
217 TRANSITION_IND = X_TRANSITION_IND,
218 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
219 ORIG_SYSTEM_REFERENCE_ID = X_ORIG_SYSTEM_REFERENCE_ID,
220 END_DATE = X_END_DATE,
221 START_DATE = X_START_DATE,
222 DEFAULT_INCIDENT_STATUS_ID = X_DEFAULT_INCIDENT_STATUS_ID,
223 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
224 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
225 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
226 where STATUS_GROUP_ID = X_STATUS_GROUP_ID;
227
228 if (sql%notfound) then
229 raise no_data_found;
230 end if;
231
232 UPDATE CS_SR_STATUS_GROUPS_TL set
233 GROUP_NAME = X_GROUP_NAME,
234 DESCRIPTION = X_DESCRIPTION,
235 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
236 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
237 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
238 SOURCE_LANG = userenv('LANG')
239 where STATUS_GROUP_ID = X_STATUS_GROUP_ID
240 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
241
242 if (sql%notfound) then
243 raise no_data_found;
244 end if;
245 END UPDATE_ROW;
246
247
248 PROCEDURE DELETE_ROW (
249 X_STATUS_GROUP_ID IN NUMBER )
250 IS
251 BEGIN
252
253 delete from CS_SR_STATUS_GROUPS_TL
254 where STATUS_GROUP_ID = X_STATUS_GROUP_ID;
255
256 if (sql%notfound) then
257 raise no_data_found;
258 end if;
259
260 delete from CS_SR_STATUS_GROUPS_B
261 where STATUS_GROUP_ID = X_STATUS_GROUP_ID;
262
263 if (sql%notfound) then
264 raise no_data_found;
265 end if;
266
267 END DELETE_ROW;
268
269 PROCEDURE ADD_LANGUAGE
270 IS
271 BEGIN
272
273 delete from CS_SR_STATUS_GROUPS_TL T
274 where not exists ( select NULL
275 from CS_SR_STATUS_GROUPS_B B
276 where B.STATUS_GROUP_ID = T.STATUS_GROUP_ID );
277
278 update CS_SR_STATUS_GROUPS_TL T
279 set ( GROUP_NAME,
280 DESCRIPTION
281 ) = ( select B.GROUP_NAME,
282 B.DESCRIPTION
283 from CS_SR_STATUS_GROUPS_TL B
284 where B.STATUS_GROUP_ID = T.STATUS_GROUP_ID
285 and B.LANGUAGE = T.SOURCE_LANG)
286 where ( T.STATUS_GROUP_ID, T.LANGUAGE) in ( select SUBT.STATUS_GROUP_ID, SUBT.LANGUAGE
287 from CS_SR_STATUS_GROUPS_TL SUBB,
288 CS_SR_STATUS_GROUPS_TL SUBT
289 where SUBB.STATUS_GROUP_ID = SUBT.STATUS_GROUP_ID
290 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
291 and (SUBB.GROUP_NAME <> SUBT.GROUP_NAME
292 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
293 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
294 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
295 or SUBB.LANGUAGE <> SUBT.LANGUAGE
296 or SUBB.SOURCE_LANG <> SUBT.SOURCE_LANG
297 ));
298
299
300 INSERT INTO CS_SR_STATUS_GROUPS_TL (
301 STATUS_GROUP_ID,
302 GROUP_NAME,
303 DESCRIPTION,
304 LANGUAGE,
305 SOURCE_LANG,
306 CREATION_DATE,
307 CREATED_BY,
308 LAST_UPDATE_DATE,
309 LAST_UPDATED_BY,
310 LAST_UPDATE_LOGIN )
311 SELECT
312 B.STATUS_GROUP_ID,
313 B.GROUP_NAME,
314 B.DESCRIPTION,
315 L.LANGUAGE_CODE,
316 B.SOURCE_LANG,
317 B.CREATION_DATE,
318 B.CREATED_BY,
319 B.LAST_UPDATE_DATE,
320 B.LAST_UPDATED_BY,
321 B.LAST_UPDATE_LOGIN
322 FROM CS_SR_STATUS_GROUPS_TL B,
323 FND_LANGUAGES L
324 WHERE L.INSTALLED_FLAG in ('I', 'B')
325 AND B.LANGUAGE = userenv('LANG')
326 AND NOT EXISTS ( select NULL
327 from CS_SR_STATUS_GROUPS_TL T
328 where T.STATUS_GROUP_ID = B.STATUS_GROUP_ID
329 and T.LANGUAGE = L.LANGUAGE_CODE);
330 END ADD_LANGUAGE;
331
332 PROCEDURE LOAD_ROW (
333 P_STATUS_GROUP_ID IN NUMBER,
334 P_OWNER IN VARCHAR2,
335 P_TRANSITION_IND IN VARCHAR2,
336 P_DEFAULT_INCIDENT_STATUS_ID IN NUMBER,
337 P_ORIG_SYSTEM_REFERENCE_ID IN NUMBER,
338 P_START_DATE IN VARCHAR2,
339 P_END_DATE IN VARCHAR2,
343 P_SOURCE_LANG IN VARCHAR2,
340 P_GROUP_NAME IN VARCHAR2,
341 P_DESCRIPTION IN VARCHAR2,
342 P_LANGUAGE IN VARCHAR2,
344 P_OBJECT_VERSION_NUMBER IN NUMBER,
345 P_SECURITY_GROUP_ID IN NUMBER )
346 IS
347 l_user_id number := 0;
348 l_status_group_id number;
349 lx_object_version_number number;
350 l_rowid number;
351 BEGIN
352 if ( p_owner = 'SEED' ) then
353 l_user_id := 1;
354 end if;
355
356 l_status_group_id := p_status_group_id;
357
358 UPDATE_ROW (
359 X_STATUS_GROUP_ID => l_status_group_id,
360 X_OBJECT_VERSION_NUMBER => p_object_version_number,
361 X_TRANSITION_IND => p_transition_ind,
362 X_DEFAULT_INCIDENT_STATUS_ID => p_default_incident_status_id,
363 X_ORIG_SYSTEM_REFERENCE_ID => p_orig_system_reference_id,
364 X_START_DATE => to_date(p_start_date, 'DD-MM-YYYY'),
365 X_END_DATE => to_date(p_end_date, 'DD-MM-YYYY'),
366 X_GROUP_NAME => p_group_name,
367 X_DESCRIPTION => p_description,
368 X_LANGUAGE => p_language ,
369 X_SOURCE_LANG => p_source_lang,
370 X_LAST_UPDATE_DATE => SYSDATE,
371 X_LAST_UPDATED_BY => l_user_id,
372 X_LAST_UPDATE_LOGIN => 0,
373 X_SECURITY_GROUP_ID => p_security_group_id );
374
375 EXCEPTION
376 WHEN NO_DATA_FOUND THEN
377 INSERT_ROW (
378 X_ROWID => l_rowid,
379 X_STATUS_GROUP_ID => l_status_group_id,
380 X_TRANSITION_IND => p_transition_ind,
381 X_DEFAULT_INCIDENT_STATUS_ID => p_default_incident_status_id,
382 X_ORIG_SYSTEM_REFERENCE_ID => p_orig_system_reference_id,
383 X_START_DATE => to_date(p_start_date, 'DD-MM-YYYY'),
384 X_END_DATE => to_date(p_end_date, 'DD-MM-YYYY'),
385 X_GROUP_NAME => p_group_name,
386 X_DESCRIPTION => p_description,
387 X_LANGUAGE => p_language ,
388 X_SOURCE_LANG => p_source_lang,
389 X_CREATION_DATE => SYSDATE,
390 X_CREATED_BY => l_user_id,
391 X_LAST_UPDATE_DATE => SYSDATE,
392 X_LAST_UPDATED_BY => l_user_id,
393 X_LAST_UPDATE_LOGIN => 0,
394 X_OBJECT_VERSION_NUMBER => p_object_version_number,
395 X_SECURITY_GROUP_ID => p_security_group_id);
396
397 END LOAD_ROW;
398
399 PROCEDURE TRANSLATE_ROW (
400 P_STATUS_GROUP_ID IN NUMBER,
401 P_GROUP_NAME IN VARCHAR2,
402 P_DESCRIPTION IN VARCHAR2,
403 P_OWNER IN VARCHAR2 )
404 IS
405 BEGIN
406 UPDATE cs_sr_status_groups_tl
407 SET group_name = p_group_name,
408 description = NVL(p_description,description),
409 last_update_date = sysdate,
410 last_updated_by = DECODE(p_owner, 'SEED', 1, 0),
411 last_update_login = 0,
412 source_lang = userenv('LANG')
413 WHERE status_group_id = p_status_group_id
414 AND userenv('LANG') IN (language, source_lang) ;
415
416 END TRANSLATE_ROW ;
417
418 END CS_SR_STATUS_GROUPS_PKG;