[Home] [Help]
PACKAGE BODY: APPS.CS_SR_EVENT_CODES_PKG
Source
1 package body CS_SR_EVENT_CODES_PKG as
2 /* $Header: csxtnevb.pls 115.2 2003/02/28 08:05:37 pkesani noship $ */
3 procedure INSERT_ROW (
4 PX_EVENT_CODE in out NOCOPY VARCHAR2,
5 P_RELATIONSHIP_TYPE in VARCHAR2,
6 P_START_DATE_ACTIVE in DATE,
7 P_END_DATE_ACTIVE in DATE,
8 P_WF_BUSINESS_EVENT_ID in VARCHAR2,
9 P_SEEDED_FLAG in VARCHAR2,
10 P_APPLICATION_ID in NUMBER,
11 P_FROM_TO_STATUS in VARCHAR2,
12 P_INCIDENT_STATUS in VARCHAR2,
13 P_NAME in VARCHAR2,
14 P_DESCRIPTION in VARCHAR2,
15 P_CREATION_DATE in DATE,
16 P_CREATED_BY in NUMBER,
17 P_LAST_UPDATE_DATE in DATE,
18 P_LAST_UPDATED_BY in NUMBER,
19 P_LAST_UPDATE_LOGIN in NUMBER,
20 X_OBJECT_VERSION_NUMBER out NOCOPY NUMBER
21 ) is
22 l_object_version_number NUMBER := 1;
23 begin
24 insert into CS_SR_EVENT_CODES_B (
25 RELATIONSHIP_TYPE,
26 START_DATE_ACTIVE,
27 END_DATE_ACTIVE,
28 WF_BUSINESS_EVENT_ID,
29 SEEDED_FLAG,
30 APPLICATION_ID,
31 OBJECT_VERSION_NUMBER,
32 FROM_TO_STATUS,
33 INCIDENT_STATUS,
34 EVENT_CODE,
35 CREATION_DATE,
36 CREATED_BY,
37 LAST_UPDATE_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_LOGIN
40 ) values (
41 P_RELATIONSHIP_TYPE,
42 P_START_DATE_ACTIVE,
43 P_END_DATE_ACTIVE,
44 P_WF_BUSINESS_EVENT_ID,
45 P_SEEDED_FLAG,
46 P_APPLICATION_ID,
47 l_object_version_number,
48 P_FROM_TO_STATUS,
49 P_INCIDENT_STATUS,
50 Px_EVENT_CODE,
51 Decode(P_CREATION_DATE,NULL,SYSDATE,P_CREATION_DATE),
52 P_CREATED_BY,
53 Decode(P_LAST_UPDATE_DATE,NULL,SYSDATE,P_LAST_UPDATE_DATE),
54 P_LAST_UPDATED_BY,
55 P_LAST_UPDATE_LOGIN
56 );
57
58 insert into CS_SR_EVENT_CODES_TL (
59 CREATION_DATE,
60 CREATED_BY,
61 LAST_UPDATE_DATE,
62 LAST_UPDATED_BY,
63 LAST_UPDATE_LOGIN,
64 EVENT_CODE,
65 NAME,
66 DESCRIPTION,
67 LANGUAGE,
68 SOURCE_LANG
69 ) select
70 P_CREATION_DATE,
71 P_CREATED_BY,
72 P_LAST_UPDATE_DATE,
73 P_LAST_UPDATED_BY,
74 P_LAST_UPDATE_LOGIN,
75 PX_EVENT_CODE,
76 P_NAME,
77 P_DESCRIPTION,
78 L.LANGUAGE_CODE,
79 userenv('LANG')
80 from FND_LANGUAGES L
81 where L.INSTALLED_FLAG in ('I', 'B')
82 and not exists
83 (select NULL
84 from CS_SR_EVENT_CODES_TL T
85 where T.EVENT_CODE = PX_EVENT_CODE
86 and T.LANGUAGE = L.LANGUAGE_CODE);
87
88 x_object_version_number := l_object_version_number;
89 end INSERT_ROW;
90
91 procedure LOCK_ROW (
92 P_EVENT_CODE in VARCHAR2,
93 P_OBJECT_VERSION_NUMBER in NUMBER
94 ) is
95 cursor c is select
96 OBJECT_VERSION_NUMBER
97 from CS_SR_EVENT_CODES_VL
98 where EVENT_CODE = P_EVENT_CODE
99 for update of EVENT_CODE nowait;
100 recinfo c%rowtype;
101
102 l_object_Version_number number := 0;
103 begin
104 open c;
105 fetch c into l_object_version_number;
106 if (c%notfound) then
107 close c;
108 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
109 app_exception.raise_exception;
110 end if;
111 close c;
112 if (l_OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER) then
113 null;
114 else
115 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
116 app_exception.raise_exception;
117 end if;
118
119 return;
120 end LOCK_ROW;
121
122 procedure UPDATE_ROW (
123 P_EVENT_CODE in VARCHAR2,
124 P_RELATIONSHIP_TYPE in VARCHAR2,
125 P_START_DATE_ACTIVE in DATE,
126 P_END_DATE_ACTIVE in DATE,
127 P_WF_BUSINESS_EVENT_ID in VARCHAR2,
128 P_SEEDED_FLAG in VARCHAR2,
129 P_APPLICATION_ID in NUMBER,
130 P_FROM_TO_STATUS in VARCHAR2,
131 P_INCIDENT_STATUS in VARCHAR2,
132 P_NAME in VARCHAR2,
133 P_DESCRIPTION in VARCHAR2,
134 P_LAST_UPDATE_DATE in DATE,
135 P_LAST_UPDATED_BY in NUMBER,
136 P_LAST_UPDATE_LOGIN in NUMBER,
137 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER
138 ) is
139 l_object_Version_number number;
140 begin
141 update CS_SR_EVENT_CODES_B set
142 RELATIONSHIP_TYPE = P_RELATIONSHIP_TYPE,
143 START_DATE_ACTIVE = P_START_DATE_ACTIVE,
144 END_DATE_ACTIVE = P_END_DATE_ACTIVE,
145 WF_BUSINESS_EVENT_ID = P_WF_BUSINESS_EVENT_ID,
146 SEEDED_FLAG = P_SEEDED_FLAG,
147 APPLICATION_ID = P_APPLICATION_ID,
148 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
149 FROM_TO_STATUS = P_FROM_TO_STATUS,
150 INCIDENT_STATUS = P_INCIDENT_STATUS,
151 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
152 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
153 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
154 where EVENT_CODE = P_EVENT_CODE
155 RETURNING OBJECT_VERSION_NUMBER INTO L_OBJECT_VERSION_NUMBER;
156
157 X_OBJECT_VERSION_NUMBER := l_object_version_number;
158
159 if (sql%notfound) then
160 raise no_data_found;
161 end if;
165 DESCRIPTION = P_DESCRIPTION,
162
163 update CS_SR_EVENT_CODES_TL set
164 NAME = P_NAME,
166 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
167 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
168 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
169 SOURCE_LANG = userenv('LANG')
170 where EVENT_CODE = P_EVENT_CODE
171 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
172
173 if (sql%notfound) then
174 raise no_data_found;
175 end if;
176 end UPDATE_ROW;
177
178 procedure DELETE_ROW (
179 P_EVENT_CODE in VARCHAR2
180 ) is
181 begin
182 delete from CS_SR_EVENT_CODES_TL
183 where EVENT_CODE = P_EVENT_CODE;
184
185 if (sql%notfound) then
186 raise no_data_found;
187 end if;
188
189 delete from CS_SR_EVENT_CODES_B
190 where EVENT_CODE = P_EVENT_CODE;
191
192 if (sql%notfound) then
193 raise no_data_found;
194 end if;
195 end DELETE_ROW;
196
197 procedure ADD_LANGUAGE
198 is
199 begin
200 delete from CS_SR_EVENT_CODES_TL T
201 where not exists
202 (select NULL
203 from CS_SR_EVENT_CODES_B B
204 where B.EVENT_CODE = T.EVENT_CODE
205 );
206
207 update CS_SR_EVENT_CODES_TL T set (
208 NAME,
209 DESCRIPTION
210 ) = (select
211 B.NAME,
212 B.DESCRIPTION
213 from CS_SR_EVENT_CODES_TL B
214 where B.EVENT_CODE = T.EVENT_CODE
215 and B.LANGUAGE = T.SOURCE_LANG)
216 where (
217 T.EVENT_CODE,
218 T.LANGUAGE
219 ) in (select
220 SUBT.EVENT_CODE,
221 SUBT.LANGUAGE
222 from CS_SR_EVENT_CODES_TL SUBB, CS_SR_EVENT_CODES_TL SUBT
223 where SUBB.EVENT_CODE = SUBT.EVENT_CODE
224 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
225 and (SUBB.NAME <> SUBT.NAME
226 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
227 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
228 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
229 ));
230
231 insert into CS_SR_EVENT_CODES_TL (
232 CREATION_DATE,
233 CREATED_BY,
234 LAST_UPDATE_DATE,
235 LAST_UPDATED_BY,
236 LAST_UPDATE_LOGIN,
237 EVENT_CODE,
238 NAME,
239 DESCRIPTION,
240 LANGUAGE,
241 SOURCE_LANG
242 ) select
243 B.CREATION_DATE,
244 B.CREATED_BY,
245 B.LAST_UPDATE_DATE,
246 B.LAST_UPDATED_BY,
247 B.LAST_UPDATE_LOGIN,
248 B.EVENT_CODE,
249 B.NAME,
250 B.DESCRIPTION,
251 L.LANGUAGE_CODE,
252 B.SOURCE_LANG
253 from CS_SR_EVENT_CODES_TL B, FND_LANGUAGES L
254 where L.INSTALLED_FLAG in ('I', 'B')
255 and B.LANGUAGE = userenv('LANG')
256 and not exists
257 (select NULL
258 from CS_SR_EVENT_CODES_TL T
259 where T.EVENT_CODE = B.EVENT_CODE
260 and T.LANGUAGE = L.LANGUAGE_CODE);
261 end ADD_LANGUAGE;
262
263 PROCEDURE LOAD_ROW (
264 P_EVENT_CODE IN VARCHAR2,
265 P_RELATIONSHIP_TYPE IN VARCHAR2,
266 P_START_DATE_ACTIVE IN VARCHAR2,
267 P_END_DATE_ACTIVE IN VARCHAR2,
268 P_WF_BUSINESS_EVENT_ID IN VARCHAR2,
269 P_SEEDED_FLAG IN VARCHAR2,
270 P_APPLICATION_ID IN NUMBER,
271 P_FROM_TO_STATUS IN VARCHAR2,
272 P_INCIDENT_STATUS IN VARCHAR2,
273 P_NAME IN VARCHAR2,
274 P_DESCRIPTION IN VARCHAR2,
275 P_OWNER IN VARCHAR2,
276 P_CREATION_DATE IN VARCHAR2,
277 P_CREATED_BY IN NUMBER,
278 P_LAST_UPDATE_DATE IN VARCHAR2,
279 P_LAST_UPDATED_BY IN NUMBER,
280 P_LAST_UPDATE_LOGIN IN NUMBER,
281 P_OBJECT_VERSION_NUMBER IN NUMBER )
282
283 IS
284
285 -- Out local variables for the update / insert row procedures.
286 lx_object_version_number NUMBER := 0;
287 l_user_id NUMBER := 0;
288
289 -- needed to be passed as the parameter value for the insert's in/out
290 -- parameter.
291 l_event_code VARCHAR2(30);
292
293 BEGIN
294
295 if ( p_owner = 'SEED' ) then
296 l_user_id := 1;
297 end if;
298
299 l_event_code := p_event_code;
300
301 UPDATE_ROW (
302 P_EVENT_CODE =>l_event_code,
303 P_RELATIONSHIP_TYPE =>p_relationship_type,
304 P_START_DATE_ACTIVE =>to_date(p_start_date_active,'DD-MM-YYYY'),
305 P_END_DATE_ACTIVE =>to_date(p_end_date_active,'DD-MM-YYYY'),
306 P_WF_BUSINESS_EVENT_ID =>P_WF_BUSINESS_EVENT_ID,
307 P_SEEDED_FLAG =>p_seeded_flag,
308 P_APPLICATION_ID =>p_application_id,
309 P_FROM_TO_STATUS =>p_from_to_status,
310 P_INCIDENT_STATUS =>p_incident_status,
311 P_NAME =>p_name,
312 P_DESCRIPTION =>p_description,
313 P_LAST_UPDATE_DATE =>nvl(to_date(p_last_update_date,
314 'DD-MM-YYYY'),sysdate),
315 P_LAST_UPDATED_BY =>l_user_id,
316 P_LAST_UPDATE_LOGIN =>0,
317 X_OBJECT_VERSION_NUMBER =>lx_object_version_number
318 );
319
320 EXCEPTION
321 WHEN NO_DATA_FOUND THEN
322 INSERT_ROW (
323 PX_EVENT_CODE =>l_event_code,
324 P_RELATIONSHIP_TYPE =>p_relationship_type,
325 P_START_DATE_ACTIVE =>to_date(p_start_date_active,'DD-MM-YYYY'),
326 P_END_DATE_ACTIVE =>to_date(p_end_date_active,'DD-MM-YYYY'),
327 P_WF_BUSINESS_EVENT_ID =>P_WF_BUSINESS_EVENT_ID,
328 P_SEEDED_FLAG =>p_seeded_flag,
329 P_APPLICATION_ID =>p_application_id,
330 P_FROM_TO_STATUS =>p_from_to_status,
331 P_INCIDENT_STATUS =>p_incident_status,
332 P_NAME =>p_name,
333 P_DESCRIPTION =>p_description,
334 P_CREATION_DATE =>nvl(to_date( p_creation_date,
335 'DD-MM-YYYY'),sysdate),
336 P_CREATED_BY =>l_user_id,
337 P_LAST_UPDATE_DATE =>nvl(to_date( p_last_update_date,
338 'DD-MM-YYYY'),sysdate),
339 P_LAST_UPDATED_BY =>l_user_id,
340 P_LAST_UPDATE_LOGIN =>0,
341 X_OBJECT_VERSION_NUMBER =>lx_object_version_number
342 );
343
344 END LOAD_ROW;
345
346 procedure TRANSLATE_ROW ( X_EVENT_CODE in varchar2,
347 X_NAME in varchar2,
348 X_DESCRIPTION in varchar2,
349 X_LAST_UPDATE_DATE in date,
350 X_LAST_UPDATE_LOGIN in number,
351 X_OWNER in varchar2)
352 is
353
354 l_user_id number;
355
356 begin
357
358 if X_OWNER = 'SEED' then
359 l_user_id := 1;
360 else
361 l_user_id := 0;
362 end if;
363
364 update CS_SR_EVENT_CODES_TL set
365 name = nvl(x_name,name),
366 description = nvl(x_description,name),
367 last_update_date = nvl(x_last_update_date,sysdate),
368 last_updated_by = l_user_id,
369 last_update_login = 0,
370 source_lang = userenv('LANG')
371 where event_code = x_event_code
372 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
373
374 end TRANSLATE_ROW;
375
376 end CS_SR_EVENT_CODES_PKG;