[Home] [Help]
PACKAGE BODY: APPS.CS_SR_LINK_TYPES_PKG
Source
1 PACKAGE BODY CS_SR_LINK_TYPES_PKG AS
2 /* $Header: cstlntyb.pls 115.3 2002/12/11 22:53:35 dejoseph noship $ */
3
4 PROCEDURE INSERT_ROW (
5 PX_LINK_TYPE_ID IN OUT NOCOPY NUMBER,
6 P_NAME IN VARCHAR2,
7 P_DESCRIPTION IN VARCHAR2,
8 P_RECIPROCAL_LINK_TYPE_ID IN NUMBER,
9 P_START_DATE_ACTIVE IN DATE,
10 P_END_DATE_ACTIVE IN DATE,
11 P_APPLICATION_ID IN NUMBER,
12 P_SEEDED_FLAG IN VARCHAR2,
13 P_USER_ID IN NUMBER, -- used for created and updated by
14 P_LOGIN_ID IN NUMBER, -- used for last update login id.
15 P_ATTRIBUTE1 IN VARCHAR2,
16 P_ATTRIBUTE2 IN VARCHAR2,
17 P_ATTRIBUTE3 IN VARCHAR2,
18 P_ATTRIBUTE4 IN VARCHAR2,
19 P_ATTRIBUTE5 IN VARCHAR2,
20 P_ATTRIBUTE6 IN VARCHAR2,
21 P_ATTRIBUTE7 IN VARCHAR2,
22 P_ATTRIBUTE8 IN VARCHAR2,
23 P_ATTRIBUTE9 IN VARCHAR2,
24 P_ATTRIBUTE10 IN VARCHAR2,
25 P_ATTRIBUTE11 IN VARCHAR2,
26 P_ATTRIBUTE12 IN VARCHAR2,
27 P_ATTRIBUTE13 IN VARCHAR2,
28 P_ATTRIBUTE14 IN VARCHAR2,
29 P_ATTRIBUTE15 IN VARCHAR2,
30 P_CONTEXT IN VARCHAR2,
31 P_OBJECT_VERSION_NUMBER IN NUMBER,
32 P_SECURITY_GROUP_ID IN NUMBER,
33 P_ATTRIBUTE_CONTEXT IN VARCHAR2,
34 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
35 X_MSG_COUNT OUT NOCOPY NUMBER,
36 X_MSG_DATA OUT NOCOPY VARCHAR2,
37 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
38 X_RECIPROCAL_LINK_ID OUT NOCOPY NUMBER,
39 X_LINK_ID OUT NOCOPY NUMBER )
40 IS
41 cursor c1 is
42 select cs_sr_link_types_b_s.nextval
43 from dual;
44
45 BEGIN
46 if ( px_link_type_id IS NULL ) OR ( px_link_type_id = FND_API.G_MISS_NUM) THEN
47 open c1;
48 fetch c1 into px_link_type_id;
49 close c1;
50 end if;
51
52 insert into CS_SR_LINK_TYPES_B (
53 LINK_TYPE_ID, END_DATE_ACTIVE, RECIPROCAL_LINK_TYPE_ID,
54 START_DATE_ACTIVE, APPLICATION_ID, SEEDED_FLAG,
55 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
56 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
57 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
58 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
59 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
60 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
61 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
62 OBJECT_VERSION_NUMBER,SECURITY_GROUP_ID, ATTRIBUTE_CONTEXT )
63 VALUES (
64 PX_LINK_TYPE_ID, P_END_DATE_ACTIVE, P_RECIPROCAL_LINK_TYPE_ID,
65 P_START_DATE_ACTIVE, P_APPLICATION_ID, P_SEEDED_FLAG,
66 p_user_id, SYSDATE, p_user_id,
67 SYSDATE, p_login_id,
68 P_ATTRIBUTE1, P_ATTRIBUTE2, P_ATTRIBUTE3,
69 P_ATTRIBUTE4, P_ATTRIBUTE5, P_ATTRIBUTE6,
70 P_ATTRIBUTE7, P_ATTRIBUTE8, P_ATTRIBUTE9,
74
71 P_ATTRIBUTE10, P_ATTRIBUTE11, P_ATTRIBUTE12,
72 P_ATTRIBUTE13, P_ATTRIBUTE14, P_ATTRIBUTE15,
73 P_OBJECT_VERSION_NUMBER, P_SECURITY_GROUP_ID, P_ATTRIBUTE_CONTEXT );
75
76 INSERT INTO CS_SR_LINK_TYPES_TL (
77 LINK_TYPE_ID, NAME, DESCRIPTION,
78 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
79 CREATED_BY, LAST_UPDATE_LOGIN, SECURITY_GROUP_ID,
80 LANGUAGE, SOURCE_LANG)
81 SELECT
82 PX_LINK_TYPE_ID, P_NAME, P_DESCRIPTION,
83 SYSDATE, P_USER_ID, SYSDATE,
84 P_USER_ID, P_LOGIN_ID, P_SECURITY_GROUP_ID,
85 L.LANGUAGE_CODE, userenv('LANG')
86 FROM FND_LANGUAGES L
87 WHERE L.INSTALLED_FLAG in ('I', 'B')
88 AND NOT EXISTS ( SELECT NULL
89 FROM CS_SR_LINK_TYPES_TL T
90 WHERE T.LINK_TYPE_ID = PX_LINK_TYPE_ID
91 AND T.LANGUAGE = L.LANGUAGE_CODE);
92 END INSERT_ROW;
93
94
95 PROCEDURE LOCK_ROW (
96 P_LINK_TYPE_ID IN NUMBER,
97 P_OBJECT_VERSION_NUMBER IN NUMBER )
98 IS
99 cursor c is
100 select 1
101 from cs_sr_link_types_vl
102 where link_type_id = p_link_type_id
103 and object_version_number = p_object_version_number
104 for update nowait;
105
106 l_dummy number(3) := 0;
107 BEGIN
108 open c;
109 fetch c into l_dummy;
110 if (c%notfound) then
111 close c;
112 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
113 app_exception.raise_exception;
114 end if;
115 close c;
116
117 END LOCK_ROW;
118
119
120 PROCEDURE UPDATE_ROW (
121 P_LINK_TYPE_ID IN NUMBER,
122 P_RECIPROCAL_LINK_TYPE_ID IN NUMBER,
123 P_START_DATE_ACTIVE IN DATE,
124 P_END_DATE_ACTIVE IN DATE,
125 P_APPLICATION_ID IN NUMBER,
126 P_SEEDED_FLAG IN VARCHAR2,
127 P_USER_ID IN NUMBER, -- used for created and updated by
128 P_LOGIN_ID IN NUMBER, -- used for last update login id.
129 P_ATTRIBUTE1 IN VARCHAR2,
130 P_ATTRIBUTE2 IN VARCHAR2,
131 P_ATTRIBUTE3 IN VARCHAR2,
132 P_ATTRIBUTE4 IN VARCHAR2,
133 P_ATTRIBUTE5 IN VARCHAR2,
134 P_ATTRIBUTE6 IN VARCHAR2,
135 P_ATTRIBUTE7 IN VARCHAR2,
136 P_ATTRIBUTE8 IN VARCHAR2,
137 P_ATTRIBUTE9 IN VARCHAR2,
138 P_ATTRIBUTE10 IN VARCHAR2,
139 P_ATTRIBUTE11 IN VARCHAR2,
140 P_ATTRIBUTE12 IN VARCHAR2,
141 P_ATTRIBUTE13 IN VARCHAR2,
142 P_ATTRIBUTE14 IN VARCHAR2,
143 P_ATTRIBUTE15 IN VARCHAR2,
144 P_CONTEXT IN VARCHAR2,
145 P_OBJECT_VERSION_NUMBER IN NUMBER,
146 P_SECURITY_GROUP_ID IN NUMBER,
147 P_ATTRIBUTE_CONTEXT IN VARCHAR2,
148 P_NAME IN VARCHAR2,
149 P_DESCRIPTION IN VARCHAR2,
150 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
151 X_MSG_COUNT OUT NOCOPY NUMBER,
152 X_MSG_DATA OUT NOCOPY VARCHAR2,
153 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
154 X_RECIPROCAL_LINK_ID OUT NOCOPY NUMBER,
155 X_LINK_ID OUT NOCOPY NUMBER )
156 IS
157
158 BEGIN
159 update CS_SR_LINK_TYPES_B
160 set END_DATE_ACTIVE = P_END_DATE_ACTIVE,
161 RECIPROCAL_LINK_TYPE_ID = P_RECIPROCAL_LINK_TYPE_ID,
162 START_DATE_ACTIVE = P_START_DATE_ACTIVE,
163 APPLICATION_ID = P_APPLICATION_ID,
164 SEEDED_FLAG = P_SEEDED_FLAG,
165 ATTRIBUTE1 = P_ATTRIBUTE1,
166 ATTRIBUTE2 = P_ATTRIBUTE2,
167 ATTRIBUTE3 = P_ATTRIBUTE3,
168 ATTRIBUTE4 = P_ATTRIBUTE4,
169 ATTRIBUTE5 = P_ATTRIBUTE5,
170 ATTRIBUTE6 = P_ATTRIBUTE6,
171 ATTRIBUTE7 = P_ATTRIBUTE7,
172 ATTRIBUTE8 = P_ATTRIBUTE8,
173 ATTRIBUTE9 = P_ATTRIBUTE9,
174 ATTRIBUTE10 = P_ATTRIBUTE10,
175 ATTRIBUTE11 = P_ATTRIBUTE11,
176 ATTRIBUTE12 = P_ATTRIBUTE12,
177 ATTRIBUTE13 = P_ATTRIBUTE13,
178 ATTRIBUTE14 = P_ATTRIBUTE14,
179 ATTRIBUTE15 = P_ATTRIBUTE15,
180 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER,
181 SECURITY_GROUP_ID = P_SECURITY_GROUP_ID,
182 ATTRIBUTE_CONTEXT = P_ATTRIBUTE_CONTEXT,
183 LAST_UPDATE_DATE = SYSDATE,
184 LAST_UPDATED_BY = P_USER_ID,
185 LAST_UPDATE_LOGIN = P_LOGIN_ID
186 WHERE LINK_TYPE_ID = P_LINK_TYPE_ID;
187
188 if (sql%notfound) then
189 raise no_data_found;
190 end if;
191
192 update CS_SR_LINK_TYPES_TL set
193 NAME = P_NAME,
194 DESCRIPTION = P_DESCRIPTION,
195 LAST_UPDATE_DATE = SYSDATE,
196 LAST_UPDATED_BY = P_USER_ID,
197 LAST_UPDATE_LOGIN = P_LOGIN_ID,
198 SOURCE_LANG = userenv('LANG')
199 WHERE LINK_TYPE_ID = P_LINK_TYPE_ID
200 AND USERENV('LANG') in (LANGUAGE, SOURCE_LANG);
201
202 if (sql%notfound) then
203 raise no_data_found;
204 end if;
205
206 END UPDATE_ROW;
207
208 PROCEDURE DELETE_ROW (
209 P_LINK_TYPE_ID in NUMBER)
210 IS
211 BEGIN
215 if (sql%notfound) then
212 delete from cs_sr_link_types_tl
213 where link_type_id = p_link_type_id;
214
216 raise no_data_found;
217 end if;
218
219 delete from cs_sr_link_types_b
220 where link_type_id = p_link_type_id;
221
222 if (sql%notfound) then
223 raise no_data_found;
224 end if;
225
226 END DELETE_ROW;
227
228 PROCEDURE ADD_LANGUAGE
229 IS
230 BEGIN
231 delete from cs_sr_link_types_tl T
232 where not exists ( select NULL
233 from CS_SR_LINK_TYPES_B B
234 where B.LINK_TYPE_ID = T.LINK_TYPE_ID );
235
236 update CS_SR_LINK_TYPES_TL T
237 set ( NAME, DESCRIPTION ) = ( select B.NAME, B.DESCRIPTION
238 from CS_SR_LINK_TYPES_TL B
239 where B.LINK_TYPE_ID = T.LINK_TYPE_ID
240 and B.LANGUAGE = T.SOURCE_LANG)
241 where (T.LINK_TYPE_ID, T.LANGUAGE) in ( select SUBT.LINK_TYPE_ID, SUBT.LANGUAGE
242 from CS_SR_LINK_TYPES_TL SUBB,
243 CS_SR_LINK_TYPES_TL SUBT
244 where SUBB.LINK_TYPE_ID = SUBT.LINK_TYPE_ID
245 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
246 and ( SUBB.NAME <> SUBT.NAME
247 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION)
248 );
249
250 insert into CS_SR_LINK_TYPES_TL (
251 LINK_TYPE_ID, NAME, DESCRIPTION,
252 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
253 CREATED_BY, LAST_UPDATE_LOGIN, SECURITY_GROUP_ID,
254 LANGUAGE, SOURCE_LANG )
255 select
256 B.LINK_TYPE_ID, B.NAME, B.DESCRIPTION,
257 B.LAST_UPDATE_DATE, B.LAST_UPDATED_BY, B.CREATION_DATE,
258 B.CREATED_BY, B.LAST_UPDATE_LOGIN, B.SECURITY_GROUP_ID,
259 L.LANGUAGE_CODE, B.SOURCE_LANG
260 from CS_SR_LINK_TYPES_TL B,
261 FND_LANGUAGES L
262 where L.INSTALLED_FLAG in ('I', 'B')
263 and B.LANGUAGE = userenv('LANG')
264 and not exists ( select NULL
265 from CS_SR_LINK_TYPES_TL T
266 where T.LINK_TYPE_ID = B.LINK_TYPE_ID
267 and T.LANGUAGE = L.LANGUAGE_CODE);
268 END ADD_LANGUAGE;
269
270
271 PROCEDURE TRANSLATE_ROW (
272 P_LINK_TYPE_ID IN NUMBER,
273 P_NAME IN VARCHAR2,
274 P_DESCRIPTION IN VARCHAR2,
275 P_OWNER IN VARCHAR2 )
276 IS
277 BEGIN
278 UPDATE cs_sr_link_types_tl
279 SET name = p_name,
280 description = NVL(p_description,description),
281 last_update_date = sysdate,
282 last_updated_by = DECODE(p_owner, 'SEED', 1, 0),
283 last_update_login = 0,
284 source_lang = userenv('LANG')
285 WHERE link_type_id = p_link_type_id
286 AND userenv('LANG') IN (language, source_lang) ;
287 END TRANSLATE_ROW ;
288
289 PROCEDURE LOAD_ROW (
290 P_LINK_TYPE_ID IN NUMBER,
291 P_NAME IN VARCHAR2,
292 P_DESCRIPTION IN VARCHAR2,
293 P_RECIPROCAL_LINK_TYPE_ID IN NUMBER,
294 P_START_DATE_ACTIVE IN VARCHAR2,
295 P_END_DATE_ACTIVE IN VARCHAR2,
296 P_OWNER IN VARCHAR2,
297 P_APPLICATION_ID IN NUMBER,
298 P_SEEDED_FLAG IN VARCHAR2,
299 P_ATTRIBUTE1 IN VARCHAR2,
300 P_ATTRIBUTE2 IN VARCHAR2,
301 P_ATTRIBUTE3 IN VARCHAR2,
302 P_ATTRIBUTE4 IN VARCHAR2,
303 P_ATTRIBUTE5 IN VARCHAR2,
304 P_ATTRIBUTE6 IN VARCHAR2,
305 P_ATTRIBUTE7 IN VARCHAR2,
306 P_ATTRIBUTE8 IN VARCHAR2,
307 P_ATTRIBUTE9 IN VARCHAR2,
308 P_ATTRIBUTE10 IN VARCHAR2,
309 P_ATTRIBUTE11 IN VARCHAR2,
310 P_ATTRIBUTE12 IN VARCHAR2,
311 P_ATTRIBUTE13 IN VARCHAR2,
312 P_ATTRIBUTE14 IN VARCHAR2,
313 P_ATTRIBUTE15 IN VARCHAR2,
314 P_CONTEXT IN VARCHAR2,
315 P_OBJECT_VERSION_NUMBER IN NUMBER,
316 P_SECURITY_GROUP_ID IN NUMBER,
317 P_ATTRIBUTE_CONTEXT IN VARCHAR2 )
318 IS
319
320 -- Out local variables for the update / insert row procedures.
321 lx_return_status VARCHAR2(3);
322 lx_msg_count NUMBER(15);
323 lx_msg_data VARCHAR2(2000);
324 lx_reciprocal_link_id NUMBER;
325 lx_link_id NUMBER;
326 lx_object_version_number NUMBER := 0;
327
328 l_user_id NUMBER;
329
330 -- needed to be passed as the parameter value for the insert's in/out
331 -- parameter.
332 l_link_type_id NUMBER;
333
334 BEGIN
335 if ( p_owner = 'SEED' ) then
336 l_user_id := 1;
337 end if;
338
339 l_link_type_id := p_link_type_id;
340
341 UPDATE_ROW (
342 P_LINK_TYPE_ID => l_link_type_id,
343 P_RECIPROCAL_LINK_TYPE_ID => p_reciprocal_link_type_id,
344 P_START_DATE_ACTIVE => to_date(p_start_date_active,'DD-MM-YYYY'),
345 P_END_DATE_ACTIVE => to_date(p_end_date_active,'DD-MM-YYYY'),
346 P_APPLICATION_ID => p_application_id,
347 P_SEEDED_FLAG => p_seeded_flag,
348 P_USER_ID => l_user_id,
349 P_LOGIN_ID => 0,
350 P_ATTRIBUTE1 => p_attribute1,
351 P_ATTRIBUTE2 => p_attribute2,
352 P_ATTRIBUTE3 => p_attribute3,
353 P_ATTRIBUTE4 => p_attribute4,
354 P_ATTRIBUTE5 => p_attribute5,
355 P_ATTRIBUTE6 => p_attribute6,
356 P_ATTRIBUTE7 => p_attribute7,
357 P_ATTRIBUTE8 => p_attribute8,
358 P_ATTRIBUTE9 => p_attribute9,
359 P_ATTRIBUTE10 => p_attribute10,
360 P_ATTRIBUTE11 => p_attribute11,
361 P_ATTRIBUTE12 => p_attribute12,
362 P_ATTRIBUTE13 => p_attribute13,
363 P_ATTRIBUTE14 => p_attribute14,
364 P_ATTRIBUTE15 => p_attribute15,
365 P_CONTEXT => p_context,
366 P_OBJECT_VERSION_NUMBER => p_object_version_number,
367 P_SECURITY_GROUP_ID => p_security_group_id,
368 P_ATTRIBUTE_CONTEXT => p_attribute_context,
369 P_NAME => p_name,
370 P_DESCRIPTION => p_description,
371 X_RETURN_STATUS => lx_return_status,
372 X_MSG_COUNT => lx_msg_count,
373 X_MSG_DATA => lx_msg_data,
374 X_OBJECT_VERSION_NUMBER => lx_object_version_number,
375 X_RECIPROCAL_LINK_ID => lx_reciprocal_link_id,
376 X_LINK_ID => lx_link_id ) ;
377
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380 INSERT_ROW (
381 PX_LINK_TYPE_ID => l_link_type_id,
382 P_RECIPROCAL_LINK_TYPE_ID => p_reciprocal_link_type_id,
383 P_START_DATE_ACTIVE => to_date(p_start_date_active,'DD-MM-YYYY'),
384 P_END_DATE_ACTIVE => to_date(p_end_date_active,'DD-MM-YYYY'),
385 P_APPLICATION_ID => p_application_id,
386 P_SEEDED_FLAG => p_seeded_flag,
387 P_USER_ID => l_user_id,
388 P_LOGIN_ID => 0,
389 P_ATTRIBUTE1 => p_attribute1,
390 P_ATTRIBUTE2 => p_attribute2,
391 P_ATTRIBUTE3 => p_attribute3,
392 P_ATTRIBUTE4 => p_attribute4,
393 P_ATTRIBUTE5 => p_attribute5,
394 P_ATTRIBUTE6 => p_attribute6,
395 P_ATTRIBUTE7 => p_attribute7,
396 P_ATTRIBUTE8 => p_attribute8,
397 P_ATTRIBUTE9 => p_attribute9,
398 P_ATTRIBUTE10 => p_attribute10,
399 P_ATTRIBUTE11 => p_attribute11,
400 P_ATTRIBUTE12 => p_attribute12,
401 P_ATTRIBUTE13 => p_attribute13,
402 P_ATTRIBUTE14 => p_attribute14,
403 P_ATTRIBUTE15 => p_attribute15,
404 P_CONTEXT => p_context,
405 P_OBJECT_VERSION_NUMBER => p_object_version_number,
406 P_SECURITY_GROUP_ID => p_security_group_id,
407 P_ATTRIBUTE_CONTEXT => p_attribute_context,
408 P_NAME => p_name,
409 P_DESCRIPTION => p_description,
410 X_RETURN_STATUS => lx_return_status,
411 X_MSG_COUNT => lx_msg_count,
412 X_MSG_DATA => lx_msg_data,
413 X_OBJECT_VERSION_NUMBER => lx_object_version_number,
414 X_RECIPROCAL_LINK_ID => lx_reciprocal_link_id,
415 X_LINK_ID => lx_link_id ) ;
416
417 END LOAD_ROW;
418
419 END CS_SR_LINK_TYPES_PKG;