[Home] [Help]
PACKAGE BODY: APPS.PV_ATTRIBUTE_PKG
Source
1 PACKAGE BODY PV_ATTRIBUTE_PKG as
2 /* $Header: pvxtatsb.pls 120.1 2005/06/30 13:08:20 appldev ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_ATTRIBUTE_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_ATTRIBUTE_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxtatsb.pls';
19
20
21 ----------------------------------------------------------
22 ---- MEDIA ----
23 ----------------------------------------------------------
24
25 -- ========================================================
26 --
27 -- NAME
28 -- createInsertBody
29 --
30 -- PURPOSE
31 --
32 -- NOTES
33 --
34 -- HISTORY
35 --
36 -- ========================================================
37 PROCEDURE Insert_Row(
38 px_attribute_id IN OUT NOCOPY NUMBER,
39 p_last_update_date DATE,
40 p_last_updated_by NUMBER,
41 p_creation_date DATE,
42 p_created_by NUMBER,
43 p_last_update_login NUMBER,
44 px_object_version_number IN OUT NOCOPY NUMBER,
45 --p_security_group_id NUMBER,
46 p_enabled_flag VARCHAR2,
47 p_attribute_type VARCHAR2,
48 p_attribute_category VARCHAR2,
49 p_seeded_flag VARCHAR2,
50 p_lov_function_name VARCHAR2,
51 p_return_type VARCHAR2,
52 p_max_value_flag VARCHAR2,
53 p_name VARCHAR2,
54 p_description VARCHAR2,
55 p_short_name VARCHAR2,
56
57 --new columns added
58
59 p_display_style VARCHAR2 := FND_API.G_MISS_CHAR,
60 p_character_width NUMBER := FND_API.G_MISS_NUM,
61 p_decimal_points NUMBER := FND_API.G_MISS_NUM,
62 p_no_of_lines NUMBER := FND_API.G_MISS_NUM,
63 p_expose_to_partner_flag VARCHAR2 := FND_API.G_MISS_CHAR,
64 p_value_extn_return_type VARCHAR2 := FND_API.G_MISS_CHAR,
65 p_enable_matching_flag VARCHAR2 := FND_API.G_MISS_CHAR,
66 p_performance_flag VARCHAR2 := FND_API.G_MISS_CHAR,
67 p_additive_flag VARCHAR2 := FND_API.G_MISS_CHAR,
68 p_sequence_number NUMBER := FND_API.G_MISS_NUM
69 )
70
71 IS
72 x_rowid VARCHAR2(30);
73
74
75 BEGIN
76
77
78 px_object_version_number := 1;
79
80
81 INSERT INTO PV_ATTRIBUTES_B(
82 attribute_id,
83 last_update_date,
84 last_updated_by,
85 creation_date,
86 created_by,
87 last_update_login,
88 object_version_number,
89 --security_group_id,
90 enabled_flag,
91 attribute_type,
92 attribute_category,
93 seeded_flag,
94 lov_function_name,
95 return_type,
96 max_value_flag,
97 display_style,
98 character_width,
99 decimal_points,
100 no_of_lines,
101 expose_to_partner_flag,
102 value_extn_return_type,
103 enable_matching_flag,
104 performance_flag,
105 additive_flag,
106 sequence_number
107 ) VALUES (
108 DECODE( px_attribute_id, FND_API.g_miss_num, NULL, px_attribute_id),
109 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
110 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
111 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
112 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
113 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
114 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
115 --DECODE( p_security_group_id, FND_API.g_miss_num, NULL, p_security_group_id),
116 DECODE( p_enabled_flag, FND_API.g_miss_char, NULL, p_enabled_flag),
117 DECODE( p_attribute_type, FND_API.g_miss_char, NULL, p_attribute_type),
118 DECODE( p_attribute_category, FND_API.g_miss_char, NULL, p_attribute_category),
119 DECODE( p_seeded_flag, FND_API.g_miss_char, NULL, p_seeded_flag),
120 DECODE( p_lov_function_name, FND_API.g_miss_char, NULL, p_lov_function_name),
121 DECODE( p_return_type, FND_API.g_miss_char, NULL, p_return_type),
122 DECODE( p_max_value_flag, FND_API.g_miss_char, NULL, p_max_value_flag),
123 DECODE( p_display_style, FND_API.g_miss_char, NULL, p_display_style),
124 DECODE( p_character_width, FND_API.g_miss_num, NULL, p_character_width),
125 DECODE( p_decimal_points, FND_API.g_miss_num, NULL, p_decimal_points),
126 DECODE( p_no_of_lines, FND_API.g_miss_num, NULL, p_no_of_lines),
127 DECODE( p_expose_to_partner_flag, FND_API.g_miss_char, NULL, p_expose_to_partner_flag),
128 DECODE( p_value_extn_return_type, FND_API.g_miss_char, NULL, p_value_extn_return_type),
129 DECODE( p_enable_matching_flag, FND_API.g_miss_char, NULL, p_enable_matching_flag),
130 DECODE( p_performance_flag, FND_API.g_miss_char, NULL, p_performance_flag),
131 DECODE( p_additive_flag, FND_API.g_miss_char, NULL, p_additive_flag),
132 DECODE( p_sequence_number, FND_API.g_miss_num, NULL, p_sequence_number)
133
134 );
135
136 INSERT INTO pv_attributes_tl(
137 attribute_id,
138 language,
139 last_update_date,
140 last_updated_by,
141 creation_date,
142 created_by,
143 last_update_login,
144 source_lang,
145 name,
146 description,
147 short_name
148 )
149 SELECT
150 decode( px_attribute_ID, FND_API.G_MISS_NUM, NULL, px_attribute_ID),
151 l.language_code,
152 SYSDATE,
153 FND_GLOBAL.user_id,
154 SYSDATE,
155 FND_GLOBAL.user_id,
156 FND_GLOBAL.conc_login_id,
157 USERENV('LANG'),
158 decode( p_name, FND_API.G_MISS_CHAR, NULL, p_name),
159 decode( p_description, FND_API.G_MISS_CHAR, NULL, p_description),
160 decode( p_short_name, FND_API.G_MISS_CHAR, NULL, p_short_name)
161 FROM fnd_languages l
162 WHERE l.installed_flag in ('I', 'B')
163 AND NOT EXISTS(
164 SELECT NULL
165 FROM pv_attributes_tl t
166 WHERE t.attribute_id = decode( px_attribute_id, FND_API.G_MISS_NUM, NULL, px_attribute_id)
167 AND t.language = l.language_code );
168
169
170 END Insert_Row;
171
172
173 ----------------------------------------------------------
174 ---- MEDIA ----
175 ----------------------------------------------------------
176
177 -- ========================================================
178 --
179 -- NAME
180 -- createUpdateBody
181 --
182 -- PURPOSE
183 --
184 -- NOTES
185 --
186 -- HISTORY
187 --
188 -- ========================================================
189
190 PROCEDURE Update_Row(
191 p_attribute_id NUMBER,
192 p_last_update_date DATE,
193 p_last_updated_by NUMBER,
194 --p_creation_date DATE,
195 --p_created_by NUMBER,
196 p_last_update_login NUMBER,
197 p_object_version_number NUMBER,
198 --p_security_group_id NUMBER,
199 p_enabled_flag VARCHAR2,
200 p_attribute_type VARCHAR2,
201 p_attribute_category VARCHAR2,
202 p_seeded_flag VARCHAR2,
203 p_lov_function_name VARCHAR2,
204 p_return_type VARCHAR2,
205 p_max_value_flag VARCHAR2,
206 p_name VARCHAR2,
207 p_description VARCHAR2,
208 p_short_name VARCHAR2,
209
210 --new columns added
211
212 p_display_style VARCHAR2 := FND_API.G_MISS_CHAR,
213 p_character_width NUMBER := FND_API.G_MISS_NUM,
214 p_decimal_points NUMBER := FND_API.G_MISS_NUM,
215 p_no_of_lines NUMBER := FND_API.G_MISS_NUM,
216 p_expose_to_partner_flag VARCHAR2 := FND_API.G_MISS_CHAR,
217 p_value_extn_return_type VARCHAR2 := FND_API.G_MISS_CHAR,
218 p_enable_matching_flag VARCHAR2 := FND_API.G_MISS_CHAR,
219 p_performance_flag VARCHAR2 := FND_API.G_MISS_CHAR,
220 p_additive_flag VARCHAR2 := FND_API.G_MISS_CHAR,
221 p_sequence_number NUMBER := FND_API.G_MISS_NUM
222 )
223
224 IS
225 BEGIN
226 Update PV_ATTRIBUTES_B
227 SET
228 attribute_id = DECODE( p_attribute_id, FND_API.g_miss_num, attribute_id, p_attribute_id),
229 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
230 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
231 --creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
232 --created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
233 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
234 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number+1),
235 --security_group_id = DECODE( p_security_group_id, FND_API.g_miss_num, security_group_id, p_security_group_id),
236 enabled_flag = DECODE( p_enabled_flag, FND_API.g_miss_char, enabled_flag, p_enabled_flag),
237 attribute_type = DECODE( p_attribute_type, FND_API.g_miss_char, attribute_type, p_attribute_type),
238 attribute_category = DECODE( p_attribute_category, FND_API.g_miss_char, attribute_category, p_attribute_category),
239 seeded_flag = DECODE( p_seeded_flag, FND_API.g_miss_char, seeded_flag, p_seeded_flag),
240 lov_function_name = DECODE( p_lov_function_name, FND_API.g_miss_char, lov_function_name, p_lov_function_name),
241 return_type = DECODE( p_return_type, FND_API.g_miss_char, return_type, p_return_type),
242 max_value_flag = DECODE( p_max_value_flag, FND_API.g_miss_char, max_value_flag, p_max_value_flag),
243
244 display_style = DECODE( p_display_style, FND_API.g_miss_char, display_style, p_display_style),
245 character_width = DECODE( p_character_width, FND_API.g_miss_num, character_width, p_character_width),
246 decimal_points = DECODE( p_decimal_points, FND_API.g_miss_num, decimal_points, p_decimal_points),
247 no_of_lines = DECODE( p_no_of_lines, FND_API.g_miss_num, no_of_lines, p_no_of_lines),
248 expose_to_partner_flag = DECODE( p_expose_to_partner_flag, FND_API.g_miss_char, expose_to_partner_flag, p_expose_to_partner_flag),
249 value_extn_return_type = DECODE( p_value_extn_return_type, FND_API.g_miss_char, value_extn_return_type, p_value_extn_return_type),
250 enable_matching_flag = DECODE( p_enable_matching_flag, FND_API.g_miss_char, enable_matching_flag, p_enable_matching_flag),
251 performance_flag = DECODE( p_performance_flag, FND_API.g_miss_char, performance_flag, p_performance_flag),
252 additive_flag = DECODE( p_additive_flag, FND_API.g_miss_char, additive_flag, p_additive_flag),
253 sequence_number = DECODE( p_sequence_number, FND_API.g_miss_num, sequence_number, p_sequence_number)
254
255
256 WHERE ATTRIBUTE_ID = p_ATTRIBUTE_ID
257 AND object_version_number = p_object_version_number;
261 name = decode( p_name, FND_API.G_MISS_CHAR, name, p_name),
258
259
260 update pv_attributes_tl set
262 description = decode( p_description, FND_API.G_MISS_CHAR, description, p_description),
263 short_name = decode( p_short_name, FND_API.G_MISS_CHAR, short_name, p_short_name),
264 last_update_date = SYSDATE,
265 last_updated_by = FND_GLOBAL.user_id,
266 last_update_login = FND_GLOBAL.conc_login_id,
267 source_lang = USERENV('LANG')
268 WHERE attribute_id = p_attribute_ID
269 AND USERENV('LANG') IN (language, source_lang);
270
271 IF (SQL%NOTFOUND) THEN
272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273 END IF;
274 END Update_Row;
275
276 PROCEDURE Update_Row_Seed(
277 p_attribute_id NUMBER,
278 p_last_update_date DATE,
279 p_last_updated_by NUMBER,
280 --p_creation_date DATE,
281 --p_created_by NUMBER,
282 p_last_update_login NUMBER,
283 p_object_version_number NUMBER,
284 --p_security_group_id NUMBER,
285 p_enabled_flag VARCHAR2,
286 p_attribute_type VARCHAR2,
287 p_attribute_category VARCHAR2,
288 p_seeded_flag VARCHAR2,
289 p_lov_function_name VARCHAR2,
290 p_return_type VARCHAR2,
291 p_max_value_flag VARCHAR2,
292 p_name VARCHAR2,
293 p_description VARCHAR2,
294 p_short_name VARCHAR2,
295
296 --new columns added
297
298 p_display_style VARCHAR2 := FND_API.G_MISS_CHAR,
299 p_character_width NUMBER := FND_API.G_MISS_NUM,
300 p_decimal_points NUMBER := FND_API.G_MISS_NUM,
301 p_no_of_lines NUMBER := FND_API.G_MISS_NUM,
302 p_expose_to_partner_flag VARCHAR2 := FND_API.G_MISS_CHAR,
303 p_value_extn_return_type VARCHAR2 := FND_API.G_MISS_CHAR,
304 p_enable_matching_flag VARCHAR2 := FND_API.G_MISS_CHAR,
305 p_performance_flag VARCHAR2 := FND_API.G_MISS_CHAR,
306 p_additive_flag VARCHAR2 := FND_API.G_MISS_CHAR,
307 p_sequence_number NUMBER := FND_API.G_MISS_NUM
308 )
309
310 IS
311
312 cursor c_updated_by is
313 select last_updated_by, display_style, attribute_category
314 from PV_ATTRIBUTES_B
315 where attribute_id = p_ATTRIBUTE_ID;
316
317 l_last_updated_by number;
318
319 l_display_style VARCHAR2(30);
320 l_attribute_category VARCHAR2(30);
321
322
323 BEGIN
324
325 for x in c_updated_by
326 loop
327 l_last_updated_by := x.last_updated_by;
328 l_display_style := x.display_style;
329 l_attribute_category := x.attribute_category;
330 end loop;
331
332
333 -- Checking if some body updated seeded attributes other than SEED,
334 -- If other users updated it, We will not updated display style, attribute category.
335 -- Else we will update display style, attribute category.
336
337
338 if( l_last_updated_by = 1) then
339
340 Update PV_ATTRIBUTES_B
341 SET
342 attribute_id = DECODE( p_attribute_id, FND_API.g_miss_num, attribute_id, p_attribute_id),
343 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
344 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
345 --creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
346 --created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
347 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
348 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number+1),
349 --security_group_id = DECODE( p_security_group_id, FND_API.g_miss_num, security_group_id, p_security_group_id),
350 -- enabled_flag = DECODE( p_enabled_flag, FND_API.g_miss_char, enabled_flag, p_enabled_flag),
351 attribute_type = DECODE( p_attribute_type, FND_API.g_miss_char, attribute_type, p_attribute_type),
352 attribute_category = DECODE( p_attribute_category, FND_API.g_miss_char, attribute_category, p_attribute_category),
353 seeded_flag = DECODE( p_seeded_flag, FND_API.g_miss_char, seeded_flag, p_seeded_flag),
354 lov_function_name = DECODE( p_lov_function_name, FND_API.g_miss_char, lov_function_name, p_lov_function_name),
355 return_type = DECODE( p_return_type, FND_API.g_miss_char, return_type, p_return_type),
356 max_value_flag = DECODE( p_max_value_flag, FND_API.g_miss_char, max_value_flag, p_max_value_flag),
357 display_style = DECODE( p_display_style, FND_API.g_miss_char, display_style, p_display_style),
358 -- character_width = DECODE( p_character_width, FND_API.g_miss_num, character_width, p_character_width),
359 -- decimal_points = DECODE( p_decimal_points, FND_API.g_miss_num, decimal_points, p_decimal_points),
360 -- no_of_lines = DECODE( p_no_of_lines, FND_API.g_miss_num, no_of_lines, p_no_of_lines),
361 expose_to_partner_flag = DECODE( p_expose_to_partner_flag, FND_API.g_miss_char, expose_to_partner_flag, p_expose_to_partner_flag),
365 additive_flag = DECODE( p_additive_flag, FND_API.g_miss_char, additive_flag, p_additive_flag),
362 value_extn_return_type = DECODE( p_value_extn_return_type, FND_API.g_miss_char, value_extn_return_type, p_value_extn_return_type),
363 enable_matching_flag = DECODE( p_enable_matching_flag, FND_API.g_miss_char, enable_matching_flag, p_enable_matching_flag),
364 performance_flag = DECODE( p_performance_flag, FND_API.g_miss_char, performance_flag, p_performance_flag),
366 sequence_number = DECODE( p_sequence_number, FND_API.g_miss_num, sequence_number, p_sequence_number)
367
368 WHERE ATTRIBUTE_ID = p_ATTRIBUTE_ID
369 AND object_version_number = p_object_version_number;
370
371 IF (SQL%NOTFOUND) THEN
372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
373 END IF;
374
375 update pv_attributes_tl set
376 name = decode( p_name, FND_API.G_MISS_CHAR, name, p_name),
377 description = decode( p_description, FND_API.G_MISS_CHAR, description, p_description),
378 short_name = decode( p_short_name, FND_API.G_MISS_CHAR, short_name, p_short_name),
379 last_update_date = SYSDATE,
380 last_updated_by = FND_GLOBAL.user_id,
381 last_update_login = FND_GLOBAL.conc_login_id,
382 source_lang = USERENV('LANG')
383 WHERE attribute_id = p_attribute_ID
384 AND USERENV('LANG') IN (language, source_lang);
385
386 IF (SQL%NOTFOUND) THEN
387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
388 END IF;
389
390 else
391
392 Update PV_ATTRIBUTES_B
393 SET
394 attribute_id = DECODE( p_attribute_id, FND_API.g_miss_num, attribute_id, p_attribute_id),
395 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
396 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
397 --creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
398 --created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
399 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
400 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number+1),
401 --security_group_id = DECODE( p_security_group_id, FND_API.g_miss_num, security_group_id, p_security_group_id),
402 -- enabled_flag = DECODE( p_enabled_flag, FND_API.g_miss_char, enabled_flag, p_enabled_flag),
403 attribute_type = DECODE( p_attribute_type, FND_API.g_miss_char, attribute_type, p_attribute_type),
404 -- attribute_category = DECODE( p_attribute_category, FND_API.g_miss_char, attribute_category, p_attribute_category),
405 seeded_flag = DECODE( p_seeded_flag, FND_API.g_miss_char, seeded_flag, p_seeded_flag),
406 lov_function_name = DECODE( p_lov_function_name, FND_API.g_miss_char, lov_function_name, p_lov_function_name),
407 return_type = DECODE( p_return_type, FND_API.g_miss_char, return_type, p_return_type),
408 --max_value_flag = DECODE( p_max_value_flag, FND_API.g_miss_char, max_value_flag, p_max_value_flag),
409 -- display_style = DECODE( p_display_style, FND_API.g_miss_char, display_style, p_display_style),
410 -- character_width = DECODE( p_character_width, FND_API.g_miss_num, character_width, p_character_width),
411 -- decimal_points = DECODE( p_decimal_points, FND_API.g_miss_num, decimal_points, p_decimal_points),
412 -- no_of_lines = DECODE( p_no_of_lines, FND_API.g_miss_num, no_of_lines, p_no_of_lines),
413 --expose_to_partner_flag = DECODE( p_expose_to_partner_flag, FND_API.g_miss_char, expose_to_partner_flag, p_expose_to_partner_flag),
414 value_extn_return_type = DECODE( p_value_extn_return_type, FND_API.g_miss_char, value_extn_return_type, p_value_extn_return_type),
415 --enable_matching_flag = DECODE( p_enable_matching_flag, FND_API.g_miss_char, enable_matching_flag, p_enable_matching_flag),
416 performance_flag = DECODE( p_performance_flag, FND_API.g_miss_char, performance_flag, p_performance_flag),
417 additive_flag = DECODE( p_additive_flag, FND_API.g_miss_char, additive_flag, p_additive_flag) --,
418 --sequence_number = DECODE( p_sequence_number, FND_API.g_miss_num, sequence_number, p_sequence_number)
419
420
421 WHERE ATTRIBUTE_ID = p_ATTRIBUTE_ID
422 AND object_version_number = p_object_version_number;
423
424 IF (SQL%NOTFOUND) THEN
425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
426 END IF;
427
428 if(l_display_style is null or l_display_style = '') then
429 Update PV_ATTRIBUTES_B
430 SET
431 display_style = DECODE( p_display_style, FND_API.g_miss_char, display_style, p_display_style)
432 WHERE ATTRIBUTE_ID = p_ATTRIBUTE_ID;
433 end if;
434
435 if(l_attribute_category is null or l_attribute_category = '') then
436 Update PV_ATTRIBUTES_B
437 SET
438 attribute_category = DECODE( p_attribute_category, FND_API.g_miss_char, attribute_category, p_attribute_category)
439 WHERE ATTRIBUTE_ID = p_ATTRIBUTE_ID;
440 end if;
441
442 update pv_attributes_tl set
443 --name = decode( p_name, FND_API.G_MISS_CHAR, name, p_name),
444 --description = decode( p_description, FND_API.G_MISS_CHAR, description, p_description),
445 short_name = decode( p_short_name, FND_API.G_MISS_CHAR, short_name, p_short_name),
446 last_update_date = SYSDATE,
447 last_updated_by = FND_GLOBAL.user_id,
448 last_update_login = FND_GLOBAL.conc_login_id,
449 source_lang = USERENV('LANG')
450 WHERE attribute_id = p_attribute_ID
451 AND USERENV('LANG') IN (language, source_lang);
452
456
453 IF (SQL%NOTFOUND) THEN
454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
455 END IF;
457
458 end if;
459
460
461 END Update_Row_Seed;
462
463
464 ----------------------------------------------------------
465 ---- MEDIA ----
466 ----------------------------------------------------------
467
468 -- ========================================================
469 --
470 -- NAME
471 -- createDeleteBody
472 --
473 -- PURPOSE
474 --
475 -- NOTES
476 --
477 -- HISTORY
478 --
479 -- ========================================================
480 PROCEDURE Delete_Row(
481 p_ATTRIBUTE_ID NUMBER)
482 IS
483 BEGIN
484 DELETE FROM PV_ATTRIBUTES_B
485 WHERE ATTRIBUTE_ID = p_ATTRIBUTE_ID;
486
487 If (SQL%NOTFOUND) then
488 RAISE NO_DATA_FOUND;
489 End If;
490
491 DELETE FROM PV_ATTRIBUTES_TL
492 WHERE ATTRIBUTE_ID = p_ATTRIBUTE_ID;
493
494 If (SQL%NOTFOUND) then
495 RAISE NO_DATA_FOUND;
496 End If;
497
498
499 If (SQL%NOTFOUND) then
500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
501 End If;
502 END Delete_Row ;
503
504
505
506 ----------------------------------------------------------
507 ---- MEDIA ----
508 ----------------------------------------------------------
509
510 -- ========================================================
511 --
512 -- NAME
513 -- createLockBody
514 --
515 -- PURPOSE
516 --
517 -- NOTES
518 --
519 -- HISTORY
520 --
521 -- ========================================================
522 PROCEDURE Lock_Row(
523 p_attribute_id NUMBER,
524 p_last_update_date DATE,
525 p_last_updated_by NUMBER,
526 p_creation_date DATE,
527 p_created_by NUMBER,
528 p_last_update_login NUMBER,
529 p_object_version_number NUMBER,
530 --p_security_group_id NUMBER,
531 p_enabled_flag VARCHAR2,
532 p_attribute_type VARCHAR2,
533 p_attribute_category VARCHAR2,
534 p_seeded_flag VARCHAR2,
535 p_lov_function_name VARCHAR2,
536 p_return_type VARCHAR2,
537 p_max_value_flag VARCHAR2,
538
539 --new columns added
540
541 p_display_style VARCHAR2 := FND_API.G_MISS_CHAR,
542 p_character_width NUMBER := FND_API.G_MISS_NUM,
543 p_decimal_points NUMBER := FND_API.G_MISS_NUM,
544 p_no_of_lines NUMBER := FND_API.G_MISS_NUM,
545 p_expose_to_partner_flag VARCHAR2 := FND_API.G_MISS_CHAR,
546 p_value_extn_return_type VARCHAR2 := FND_API.G_MISS_CHAR,
547 p_enable_matching_flag VARCHAR2 := FND_API.G_MISS_CHAR,
548 p_performance_flag VARCHAR2 := FND_API.G_MISS_CHAR,
549 p_additive_flag VARCHAR2 := FND_API.G_MISS_CHAR,
550 p_sequence_number NUMBER := FND_API.G_MISS_NUM
551
552 )
553
554 IS
555 CURSOR C IS
556 SELECT *
557 FROM PV_ATTRIBUTES_B
558 WHERE ATTRIBUTE_ID = p_ATTRIBUTE_ID
559 FOR UPDATE of ATTRIBUTE_ID NOWAIT;
560 Recinfo C%ROWTYPE;
561 BEGIN
562 OPEN c;
563 FETCH c INTO Recinfo;
564 If (c%NOTFOUND) then
565 CLOSE c;
566 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
567 APP_EXCEPTION.RAISE_EXCEPTION;
568 END IF;
569 CLOSE C;
570 IF (
571 ( Recinfo.attribute_id = p_attribute_id)
572 AND ( ( Recinfo.last_update_date = p_last_update_date)
573 OR ( ( Recinfo.last_update_date IS NULL )
574 AND ( p_last_update_date IS NULL )))
575 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
576 OR ( ( Recinfo.last_updated_by IS NULL )
577 AND ( p_last_updated_by IS NULL )))
578 AND ( ( Recinfo.creation_date = p_creation_date)
579 OR ( ( Recinfo.creation_date IS NULL )
580 AND ( p_creation_date IS NULL )))
581 AND ( ( Recinfo.created_by = p_created_by)
582 OR ( ( Recinfo.created_by IS NULL )
583 AND ( p_created_by IS NULL )))
584 AND ( ( Recinfo.last_update_login = p_last_update_login)
585 OR ( ( Recinfo.last_update_login IS NULL )
586 AND ( p_last_update_login IS NULL )))
587 AND ( ( Recinfo.object_version_number = p_object_version_number)
588 OR ( ( Recinfo.object_version_number IS NULL )
589 AND ( p_object_version_number IS NULL )))
590 /*
591 AND ( ( Recinfo.security_group_id = p_security_group_id)
592 OR ( ( Recinfo.security_group_id IS NULL )
593 AND ( p_security_group_id IS NULL )))
594 */
595 AND ( ( Recinfo.enabled_flag = p_enabled_flag)
596 OR ( ( Recinfo.enabled_flag IS NULL )
597 AND ( p_enabled_flag IS NULL )))
598 AND ( ( Recinfo.attribute_type = p_attribute_type)
599 OR ( ( Recinfo.attribute_type IS NULL )
600 AND ( p_attribute_type IS NULL )))
601 AND ( ( Recinfo.attribute_category = p_attribute_category)
605 OR ( ( Recinfo.seeded_flag IS NULL )
602 OR ( ( Recinfo.attribute_category IS NULL )
603 AND ( p_attribute_category IS NULL )))
604 AND ( ( Recinfo.seeded_flag = p_seeded_flag)
606 AND ( p_seeded_flag IS NULL )))
607 AND ( ( Recinfo.lov_function_name = p_lov_function_name)
608 OR ( ( Recinfo.lov_function_name IS NULL )
609 AND ( p_lov_function_name IS NULL )))
610 AND ( ( Recinfo.return_type = p_return_type)
611 OR ( ( Recinfo.return_type IS NULL )
612 AND ( p_return_type IS NULL )))
613 AND ( ( Recinfo.max_value_flag = p_max_value_flag)
614 OR ( ( Recinfo.max_value_flag IS NULL )
615 AND ( p_max_value_flag IS NULL )))
616
617 AND ( ( Recinfo.display_style = p_display_style)
618 OR ( ( Recinfo.display_style IS NULL )
619 AND ( p_display_style IS NULL )))
620
621 AND ( ( Recinfo.character_width = p_character_width)
622 OR ( ( Recinfo.character_width IS NULL )
623 AND ( p_character_width IS NULL )))
624
625 AND ( ( Recinfo.decimal_points = p_decimal_points)
626 OR ( ( Recinfo.decimal_points IS NULL )
627 AND ( p_decimal_points IS NULL )))
628
629 AND ( ( Recinfo.no_of_lines = p_no_of_lines)
630 OR ( ( Recinfo.no_of_lines IS NULL )
631 AND ( p_no_of_lines IS NULL )))
632
633 AND ( ( Recinfo.expose_to_partner_flag = p_expose_to_partner_flag)
634 OR ( ( Recinfo.expose_to_partner_flag IS NULL )
635 AND ( p_expose_to_partner_flag IS NULL )))
636
637 AND ( ( Recinfo.value_extn_return_type = p_value_extn_return_type)
638 OR ( ( Recinfo.value_extn_return_type IS NULL )
639 AND ( p_value_extn_return_type IS NULL )))
640
641 AND ( ( Recinfo.enable_matching_flag = p_enable_matching_flag)
642 OR ( ( Recinfo.enable_matching_flag IS NULL )
643 AND ( p_enable_matching_flag IS NULL )))
644
645 AND ( ( Recinfo.performance_flag = p_performance_flag)
646 OR ( ( Recinfo.performance_flag IS NULL )
647 AND ( p_performance_flag IS NULL )))
648
649 AND ( ( Recinfo.additive_flag = p_additive_flag)
650 OR ( ( Recinfo.additive_flag IS NULL )
651 AND ( p_additive_flag IS NULL )))
652
653 AND ( ( Recinfo.sequence_number = p_sequence_number)
654 OR ( ( Recinfo.sequence_number IS NULL )
655 AND ( p_sequence_number IS NULL )))
656
657
658
659
660 ) THEN
661 RETURN;
662 ELSE
663 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
664 APP_EXCEPTION.RAISE_EXCEPTION;
665 END IF;
666 END Lock_Row;
667
668
669
670 procedure ADD_LANGUAGE
671 is
672 begin
673 delete from PV_ATTRIBUTES_TL T
674 where not exists
675 (select NULL
676 from PV_ATTRIBUTES_B B
677 where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
678 );
679
680 update PV_ATTRIBUTES_TL T set (
681 NAME,
682 DESCRIPTION
683 ) = (select
684 B.NAME,
685 B.DESCRIPTION
686 from PV_ATTRIBUTES_TL B
687 where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
688 and B.LANGUAGE = T.SOURCE_LANG)
689 where (
690 T.ATTRIBUTE_ID,
691 T.LANGUAGE
692 ) in (select
693 SUBT.ATTRIBUTE_ID,
694 SUBT.LANGUAGE
695 from PV_ATTRIBUTES_TL SUBB, PV_ATTRIBUTES_TL SUBT
696 where SUBB.ATTRIBUTE_ID = SUBT.ATTRIBUTE_ID
697 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
698 and (SUBB.NAME <> SUBT.NAME
699 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
700 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
701 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
702 ));
703
704 insert into PV_ATTRIBUTES_TL (
705 NAME,
706 DESCRIPTION,
707 ATTRIBUTE_ID,
708 SHORT_NAME,
709 LAST_UPDATE_DATE,
710 LAST_UPDATED_BY,
711 CREATION_DATE,
712 CREATED_BY,
713 LAST_UPDATE_LOGIN,
714 LANGUAGE,
715 SOURCE_LANG
716 ) select
717 B.NAME,
718 B.DESCRIPTION,
719 B.ATTRIBUTE_ID,
720 B.SHORT_NAME,
721 B.LAST_UPDATE_DATE,
722 B.LAST_UPDATED_BY,
723 B.CREATION_DATE,
724 B.CREATED_BY,
725 B.LAST_UPDATE_LOGIN,
726 L.LANGUAGE_CODE,
727 B.SOURCE_LANG
728 from PV_ATTRIBUTES_TL B, FND_LANGUAGES L
729 where L.INSTALLED_FLAG in ('I', 'B')
730 and B.LANGUAGE = userenv('LANG')
731 and not exists
732 (select NULL
733 from PV_ATTRIBUTES_TL T
734 where T.ATTRIBUTE_ID = B.ATTRIBUTE_ID
735 and T.LANGUAGE = L.LANGUAGE_CODE);
736 end ADD_LANGUAGE;
737
738
739
740 procedure TRANSLATE_ROW(
741 p_attribute_id in NUMBER
742 , p_name in VARCHAR2
743 , p_description in VARCHAR2
744 , p_owner in VARCHAR2
745 ) is
746 begin
747 update PV_ATTRIBUTES_TL set
751 last_update_date = sysdate,
748 name = nvl(p_name, name),
749 description = nvl(p_description, description),
750 source_lang = userenv('LANG'),
752 last_updated_by = decode(p_owner, 'SEED', 1, 0),
753 last_update_login = 0
754 where attribute_id = p_attribute_id
755 and userenv('LANG') in (language, source_lang);
756
757 end TRANSLATE_ROW;
758
759
760
761 procedure LOAD_ROW(
762 p_attribute_id IN NUMBER,
763 --p_creation_date IN DATE,
764 --p_created_by IN NUMBER,
765 --p_security_group_id IN NUMBER,
766 p_enabled_flag IN VARCHAR2,
767 p_attribute_type IN VARCHAR2,
768 p_attribute_category IN VARCHAR2,
769 p_seeded_flag IN VARCHAR2,
770 p_lov_function_name IN VARCHAR2,
771 p_return_type IN VARCHAR2,
772 p_max_value_flag IN VARCHAR2,
773 p_name IN VARCHAR2,
774 p_description IN VARCHAR2,
775 p_short_name IN VARCHAR2,
776 p_owner IN VARCHAR2,
777
778 --new columns added
779
780 p_display_style IN VARCHAR2 := FND_API.G_MISS_CHAR,
781 p_character_width IN NUMBER := FND_API.G_MISS_NUM,
782 p_decimal_points IN NUMBER := FND_API.G_MISS_NUM,
783 p_no_of_lines IN NUMBER := FND_API.G_MISS_NUM,
784 p_expose_to_partner_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
785 p_value_extn_return_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
786 p_enable_matching_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
787 p_performance_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
788 p_additive_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
789 p_sequence_number IN NUMBER := FND_API.G_MISS_NUM
790 ) IS
791
792 l_user_id number := 0;
793 l_obj_verno number;
794 l_dummy_char varchar2(1);
795 l_row_id varchar2(100);
796 l_attribute_id number;
797
798 cursor c_obj_verno is
799 select object_version_number
800 from PV_ATTRIBUTES_B
801 where attribute_id = p_ATTRIBUTE_ID;
802
803 cursor c_chk_attrib_exists is
804 select 'x'
805 from PV_ATTRIBUTES_B
806 where attribute_id = p_ATTRIBUTE_ID;
807
808 BEGIN
809
810 l_attribute_id := p_attribute_id;
811 if p_OWNER = 'SEED' then
812 l_user_id := 1;
813 end if;
814
815 open c_chk_attrib_exists;
816 fetch c_chk_attrib_exists into l_dummy_char;
817 if c_chk_attrib_exists%notfound
818 then
819 close c_chk_attrib_exists;
820 l_obj_verno := 1;
821 PV_ATTRIBUTE_PKG.INSERT_ROW(
822 px_attribute_id => l_attribute_id
823 ,p_last_update_date => SYSDATE
824 ,p_last_updated_by => l_user_id
825 ,p_creation_date => SYSDATE
826 ,p_created_by => l_user_id
827 ,p_last_update_login => 0
828 ,px_object_version_number => l_obj_verno
829 --,p_security_group_id => p_sercurity_group_id
830 ,p_enabled_flag => p_enabled_flag
831 ,p_attribute_type => p_attribute_type
832 ,p_attribute_category => p_attribute_category
833 ,p_seeded_flag => p_seeded_flag
834 ,p_lov_function_name => p_lov_function_name
835 ,p_return_type => p_return_type
836 ,p_max_value_flag => p_max_value_flag
837 ,p_name => p_name
838 ,p_description => p_description
839 ,p_short_name => p_short_name
840
841 ,p_display_style => p_display_style
842 ,p_character_width => p_character_width
843 ,p_decimal_points => p_decimal_points
844 ,p_no_of_lines => p_no_of_lines
845 ,p_expose_to_partner_flag => p_expose_to_partner_flag
846 ,p_value_extn_return_type => p_value_extn_return_type
847 ,p_enable_matching_flag => p_enable_matching_flag
848 ,p_performance_flag => p_performance_flag
849 ,p_additive_flag => p_additive_flag
850 ,p_sequence_number => p_sequence_number
851 );
852
853 else
854 close c_chk_attrib_exists;
855 open c_obj_verno;
856 fetch c_obj_verno into l_obj_verno;
857 close c_obj_verno;
858
859 PV_ATTRIBUTE_PKG.UPDATE_ROW_SEED(
860 p_attribute_id => l_attribute_id
861 ,p_last_update_date => SYSDATE
862 ,p_last_updated_by => l_user_id
863 --,p_creation_date => p_creation_date
864 --,p_created_by => p_created_by
865 ,p_last_update_login => 0
866 ,p_object_version_number => l_obj_verno
867 --,p_security_group_id => p_sercurity_group_id
868 ,p_enabled_flag => p_enabled_flag
869 ,p_attribute_type => p_attribute_type
870 ,p_attribute_category => p_attribute_category
871 ,p_seeded_flag => p_seeded_flag
872 ,p_lov_function_name => p_lov_function_name
876 ,p_description => p_description
873 ,p_return_type => p_return_type
874 ,p_max_value_flag => p_max_value_flag
875 ,p_name => p_name
877 ,p_short_name => p_short_name
878
879 ,p_display_style => p_display_style
880 ,p_character_width => p_character_width
881 ,p_decimal_points => p_decimal_points
882 ,p_no_of_lines => p_no_of_lines
883 ,p_expose_to_partner_flag => p_expose_to_partner_flag
884 ,p_value_extn_return_type => p_value_extn_return_type
885 ,p_enable_matching_flag => p_enable_matching_flag
886 ,p_performance_flag => p_performance_flag
887 ,p_additive_flag => p_additive_flag
888 ,p_sequence_number => p_sequence_number
889 );
890
891 end if;
892 END LOAD_ROW;
893
894 procedure LOAD_SEED_ROW(
895 p_upload_mode IN VARCHAR2,
896 p_attribute_id IN NUMBER,
897 p_enabled_flag IN VARCHAR2,
898 p_attribute_type IN VARCHAR2,
899 p_attribute_category IN VARCHAR2,
900 p_seeded_flag IN VARCHAR2,
901 p_lov_function_name IN VARCHAR2,
902 p_return_type IN VARCHAR2,
903 p_max_value_flag IN VARCHAR2,
904 p_name IN VARCHAR2,
905 p_description IN VARCHAR2,
906 p_short_name IN VARCHAR2,
907 p_owner IN VARCHAR2,
908 p_display_style IN VARCHAR2 := FND_API.G_MISS_CHAR,
909 p_character_width IN NUMBER := FND_API.G_MISS_NUM,
910 p_decimal_points IN NUMBER := FND_API.G_MISS_NUM,
911 p_no_of_lines IN NUMBER := FND_API.G_MISS_NUM,
912 p_expose_to_partner_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
913 p_value_extn_return_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
914 p_enable_matching_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
915 p_performance_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
916 p_additive_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
917 p_sequence_number IN NUMBER := FND_API.G_MISS_NUM
918 )
919 IS
920 BEGIN
921 if (P_UPLOAD_MODE = 'NLS') then
922 PV_ATTRIBUTE_PKG.TRANSLATE_ROW (
923 p_attribute_id => P_ATTRIBUTE_ID
924 , p_name => P_NAME
925 , p_description => P_DESCRIPTION
926 , p_owner => P_OWNER
927 );
928 else
929 PV_ATTRIBUTE_PKG.LOAD_ROW (
930 p_ATTRIBUTE_ID => P_ATTRIBUTE_ID,
931 p_ENABLED_FLAG => P_ENABLED_FLAG,
932 p_SHORT_NAME => P_SHORT_NAME,
933 p_NAME => P_NAME,
934 p_DESCRIPTION => P_DESCRIPTION,
935 p_Owner => P_OWNER,
936 p_ATTRIBUTE_TYPE => P_ATTRIBUTE_TYPE,
937 p_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
938 p_SEEDED_FLAG => P_SEEDED_FLAG,
939 p_LOV_FUNCTION_NAME => P_LOV_FUNCTION_NAME,
940 p_RETURN_TYPE => P_RETURN_TYPE,
941 p_MAX_VALUE_FLAG => P_MAX_VALUE_FLAG,
942 p_DISPLAY_STYLE => P_DISPLAY_STYLE,
943 p_EXPOSE_TO_PARTNER_FLAG => P_EXPOSE_TO_PARTNER_FLAG,
944 p_CHARACTER_WIDTH => P_CHARACTER_WIDTH,
945 p_DECIMAL_POINTS => P_DECIMAL_POINTS,
946 p_NO_OF_LINES => P_NO_OF_LINES,
947 p_VALUE_EXTN_RETURN_TYPE => P_VALUE_EXTN_RETURN_TYPE,
948 p_ENABLE_MATCHING_FLAG => P_ENABLE_MATCHING_FLAG,
949 p_PERFORMANCE_FLAG => P_PERFORMANCE_FLAG,
950 p_ADDITIVE_FLAG => P_ADDITIVE_FLAG
951 );
952
953 end if;
954 END LOAD_SEED_ROW;
955
956 END PV_ATTRIBUTE_PKG;