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