[Home] [Help]
PACKAGE BODY: APPS.PV_ATTRIBUTE_CODE_PKG
Source
1 PACKAGE BODY PV_ATTRIBUTE_CODE_PKG as
2 /* $Header: pvxtatcb.pls 120.3 2011/03/25 19:23:11 hekkiral ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_ATTRIBUTE_CODE_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_CODE_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxtatcb.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_attr_code_id IN OUT NOCOPY NUMBER,
39 p_attr_code VARCHAR2,
40 p_last_update_date DATE,
41 p_last_updated_by NUMBER,
42 p_creation_date DATE,
43 p_created_by NUMBER,
44 p_last_update_login NUMBER,
45 px_object_version_number IN OUT NOCOPY NUMBER,
46 p_attribute_id NUMBER,
47 p_enabled_flag VARCHAR2,
48 p_description VARCHAR2 )
49
50 IS
51 x_rowid VARCHAR2(30);
52
53
54 BEGIN
55
56
57 px_object_version_number := 1;
58
59
60 INSERT INTO PV_ATTRIBUTE_CODES_B(
61 attr_code_id,
62 attr_code,
63 last_update_date,
64 last_updated_by,
65 creation_date,
66 created_by,
67 last_update_login,
68 object_version_number,
69 attribute_id,
70 enabled_flag
71 ) VALUES (
72 DECODE( px_attr_code_id, FND_API.g_miss_num, NULL, px_attr_code_id),
73 DECODE( p_attr_code, FND_API.g_miss_char, NULL, p_attr_code),
74 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
75 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
76 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
77 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
78 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
79 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
80 DECODE( p_attribute_id, FND_API.g_miss_num, NULL, p_attribute_id),
81 DECODE( p_enabled_flag, FND_API.g_miss_char, NULL, p_enabled_flag)
82
83 );
84
85
86 INSERT INTO PV_ATTRIBUTE_CODES_TL(
87 attr_code_id,
88 language,
89 source_lang,
90 last_update_date,
91 last_updated_by,
92 creation_date,
93 created_by,
94 last_update_login,
95 object_version_number,
96 description
97 --security_group_id
98 ) SELECT
99 DECODE( px_attr_code_id, FND_API.g_miss_num, NULL, px_attr_code_id),
100 FNDL.language_code,
101 USERENV('LANG'),
102 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
103 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
104 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
105 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
106 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
107 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
108 DECODE( p_description, FND_API.g_miss_char, NULL, p_description)
109 --DECODE( p_security_group_id, FND_API.g_miss_num, NULL, p_security_group_id)
110 FROM fnd_languages FNDL
111 WHERE FNDL.installed_flag in ('I', 'B')
112 AND NOT EXISTS(
113 SELECT NULL
114 FROM pv_attribute_codes_tl T
115 WHERE T.attr_code_id = px_attr_code_id
116 AND T.language = FNDL.language_code );
117
118 END Insert_Row;
119
120
121 ----------------------------------------------------------
122 ---- MEDIA ----
123 ----------------------------------------------------------
124
125 -- ========================================================
126 --
127 -- NAME
128 -- createUpdateBody
129 --
130 -- PURPOSE
131 --
132 -- NOTES
133 --
134 -- HISTORY
135 --
136 -- ========================================================
137 PROCEDURE Update_Row(
138 p_attr_code_id NUMBER,
139 p_attr_code VARCHAR2,
140 p_last_update_date DATE,
141 p_last_updated_by NUMBER,
142 p_creation_date DATE := FND_API.g_miss_date ,
143 p_created_by NUMBER := FND_API.g_miss_num ,
144 p_last_update_login NUMBER,
145 p_object_version_number NUMBER,
146 p_attribute_id NUMBER,
147 p_enabled_flag VARCHAR2,
148 p_description VARCHAR2 )
149
150 IS
151 BEGIN
152 Update PV_ATTRIBUTE_CODES_B
153 SET
154 attr_code_id = DECODE( p_attr_code_id, FND_API.g_miss_num, attr_code_id, p_attr_code_id),
155 attr_code = DECODE( p_attr_code, FND_API.g_miss_char, attr_code, p_attr_code),
156 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
157 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
158 --creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
159 --created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
160 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
161 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number+1),
162 attribute_id = DECODE( p_attribute_id, FND_API.g_miss_num, attribute_id, p_attribute_id),
163 enabled_flag = DECODE( p_enabled_flag, FND_API.g_miss_char, enabled_flag, p_enabled_flag)
164
165 WHERE ATTR_CODE_ID = p_ATTR_CODE_ID
166 AND object_version_number = p_object_version_number;
167
168 IF (SQL%NOTFOUND) THEN
169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
170 END IF;
171
172 Update PV_ATTRIBUTE_CODES_TL
173 SET
174 attr_code_id = DECODE( p_attr_code_id, FND_API.g_miss_num, attr_code_id, p_attr_code_id),
175 source_lang = userenv('LANG'),
176 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
177 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
178 --creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
179 --created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
180 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
181 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number+1),
182 description = DECODE( p_description, FND_API.g_miss_char, description, p_description)
183 --security_group_id = DECODE( p_security_group_id, FND_API.g_miss_num, security_group_id, p_security_group_id)
184 WHERE ATTR_CODE_ID = p_ATTR_CODE_ID
185 --AND object_version_number = p_object_version_number
186 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
187
188
189 IF (SQL%NOTFOUND) THEN
190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
191 END IF;
192
193 END Update_Row;
194
195
196 ----------------------------------------------------------
197 ---- MEDIA ----
198 ----------------------------------------------------------
199
200 -- ========================================================
201 --
202 -- NAME
203 -- UPdate_row_seed
204 --
205 -- PURPOSE
206 --
207 -- NOTES
208 --
209 -- HISTORY
210 --
211 -- ========================================================
212 PROCEDURE Update_Row_SEED(
213 p_attr_code_id NUMBER,
214 p_attr_code VARCHAR2,
215 p_last_update_date DATE,
216 p_last_updated_by NUMBER,
217 p_creation_date DATE := FND_API.g_miss_date ,
218 p_created_by NUMBER := FND_API.g_miss_num ,
219 p_last_update_login NUMBER,
220 p_object_version_number NUMBER,
221 p_attribute_id NUMBER,
222 p_enabled_flag VARCHAR2,
223 p_description VARCHAR2 )
224
225 IS
226
227 cursor c_updated_by is
228 select last_updated_by
229 from PV_ATTRIBUTE_CODES_B
230 WHERE attr_code_id = p_ATTR_CODE_ID;
231
232 l_last_updated_by number;
233
234
235 BEGIN
236
237 for x in c_updated_by
238 loop
239 l_last_updated_by := x.last_updated_by;
240 end loop;
241
242 -- Checking if some body updated seeded attribute codes other than SEED,
243 -- If other users updated it, We will not updated enabled_flag and description.
244 -- Else we will update enabled_flag and description
245
246 if( l_last_updated_by = 1) then
247
248 Update PV_ATTRIBUTE_CODES_B
249 SET
250 attr_code_id = DECODE( p_attr_code_id, FND_API.g_miss_num, attr_code_id, p_attr_code_id),
251 attr_code = DECODE( p_attr_code, FND_API.g_miss_char, attr_code, p_attr_code),
252 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
253 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
254 --creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
255 --created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
256 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
257 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number+1),
258 attribute_id = DECODE( p_attribute_id, FND_API.g_miss_num, attribute_id, p_attribute_id),
259 enabled_flag = DECODE( p_enabled_flag, FND_API.g_miss_char, enabled_flag, p_enabled_flag)
260
261 WHERE ATTR_CODE_ID = p_ATTR_CODE_ID
262 AND object_version_number = p_object_version_number;
263
264 IF (SQL%NOTFOUND) THEN
265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
266 END IF;
267
268 Update PV_ATTRIBUTE_CODES_TL
269 SET
270 attr_code_id = DECODE( p_attr_code_id, FND_API.g_miss_num, attr_code_id, p_attr_code_id),
271 source_lang = userenv('LANG'),
272 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
273 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
274 --creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
275 --created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
276 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
277 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number+1),
278 description = DECODE( p_description, FND_API.g_miss_char, description, p_description)
279
280 WHERE ATTR_CODE_ID = p_ATTR_CODE_ID
281 --AND object_version_number = p_object_version_number
282 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
283
284
285 IF (SQL%NOTFOUND) THEN
286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
287 END IF;
288
289 ELSE
290
291
292 Update PV_ATTRIBUTE_CODES_B
293 SET
294 attr_code_id = DECODE( p_attr_code_id, FND_API.g_miss_num, attr_code_id, p_attr_code_id),
295 attr_code = DECODE( p_attr_code, FND_API.g_miss_char, attr_code, p_attr_code),
296 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
297 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
298 --creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
299 --created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
300 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
301 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number+1),
302 attribute_id = DECODE( p_attribute_id, FND_API.g_miss_num, attribute_id, p_attribute_id)
303 --enabled_flag = DECODE( p_enabled_flag, FND_API.g_miss_char, enabled_flag, p_enabled_flag)
304
305 WHERE ATTR_CODE_ID = p_ATTR_CODE_ID
306 AND object_version_number = p_object_version_number;
307
308 IF (SQL%NOTFOUND) THEN
309 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
310 END IF;
311
312 Update PV_ATTRIBUTE_CODES_TL
313 SET
314 attr_code_id = DECODE( p_attr_code_id, FND_API.g_miss_num, attr_code_id, p_attr_code_id),
315 source_lang = userenv('LANG'),
316 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
317 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
318 --creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
319 --created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
320 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
321 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number+1)
322 --description = DECODE( p_description, FND_API.g_miss_char, description, p_description)
323 --security_group_id = DECODE( p_security_group_id, FND_API.g_miss_num, security_group_id, p_security_group_id)
324 WHERE ATTR_CODE_ID = p_ATTR_CODE_ID
325 --AND object_version_number = p_object_version_number
326 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
327
328
329 IF (SQL%NOTFOUND) THEN
330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
331 END IF;
332
333
334 END IF;
335
336
337 END Update_Row_Seed;
338
339 ----------------------------------------------------------
340 ---- MEDIA ----
341 ----------------------------------------------------------
342
343 -- ========================================================
344 --
345 -- NAME
346 -- createDeleteBody
347 --
348 -- PURPOSE
349 --
350 -- NOTES
351 --
352 -- HISTORY
353 --
354 -- ========================================================
355 PROCEDURE Delete_Row(
356 p_ATTR_CODE_ID NUMBER)
357 IS
358 BEGIN
362 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
359 DELETE FROM PV_ATTRIBUTE_CODES_B
360 WHERE ATTR_CODE_ID = p_ATTR_CODE_ID;
361 If (SQL%NOTFOUND) then
363 End If;
364
365 DELETE FROM PV_ATTRIBUTE_CODES_TL
366 WHERE ATTR_CODE_ID = p_ATTR_CODE_ID;
367 If (SQL%NOTFOUND) then
368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
369 End If;
370
371 END Delete_Row ;
372
373
374
375 ----------------------------------------------------------
376 ---- MEDIA ----
377 ----------------------------------------------------------
378
379 -- ========================================================
380 --
381 -- NAME
382 -- createLockBody
383 --
384 -- PURPOSE
385 --
386 -- NOTES
387 --
388 -- HISTORY
389 --
390 -- ========================================================
391 PROCEDURE Lock_Row(
392 p_attr_code_id NUMBER,
393 p_attr_code VARCHAR2,
394 p_last_update_date DATE,
395 p_last_updated_by NUMBER,
396 p_creation_date DATE,
397 p_created_by NUMBER,
398 p_last_update_login NUMBER,
399 p_object_version_number NUMBER,
400 p_attribute_id NUMBER,
401 p_enabled_flag VARCHAR2,
402 p_description VARCHAR2 )
403
404 IS
405 CURSOR C IS
406 SELECT *
407 FROM PV_ATTRIBUTE_CODES_B
408 WHERE ATTR_CODE_ID = p_ATTR_CODE_ID
409 FOR UPDATE of ATTR_CODE_ID NOWAIT;
410 Recinfo C%ROWTYPE;
411
412 cursor C1 is select
413 DESCRIPTION,
414 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
415 from PV_ATTRIBUTE_CODES_TL
416 where ATTR_CODE_ID = p_attr_code_id
417 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
418 for update of ATTR_CODE_ID nowait;
419
420 BEGIN
421 OPEN c;
422 FETCH c INTO Recinfo;
423 If (c%NOTFOUND) then
424 CLOSE c;
425 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
426 APP_EXCEPTION.RAISE_EXCEPTION;
427 END IF;
428 CLOSE C;
429 IF (
430 ( Recinfo.attr_code_id = p_attr_code_id)
431 AND ( ( Recinfo.attr_code = p_attr_code)
432 OR ( ( Recinfo.attr_code IS NULL )
433 AND ( p_attr_code IS NULL )))
434 AND ( ( Recinfo.last_update_date = p_last_update_date)
435 OR ( ( Recinfo.last_update_date IS NULL )
436 AND ( p_last_update_date IS NULL )))
437 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
438 OR ( ( Recinfo.last_updated_by IS NULL )
439 AND ( p_last_updated_by IS NULL )))
440 AND ( ( Recinfo.creation_date = p_creation_date)
441 OR ( ( Recinfo.creation_date IS NULL )
442 AND ( p_creation_date IS NULL )))
443 AND ( ( Recinfo.created_by = p_created_by)
444 OR ( ( Recinfo.created_by IS NULL )
445 AND ( p_created_by IS NULL )))
446 AND ( ( Recinfo.last_update_login = p_last_update_login)
447 OR ( ( Recinfo.last_update_login IS NULL )
448 AND ( p_last_update_login IS NULL )))
449 AND ( ( Recinfo.object_version_number = p_object_version_number)
450 OR ( ( Recinfo.object_version_number IS NULL )
451 AND ( p_object_version_number IS NULL )))
452 AND ( ( Recinfo.attribute_id = p_attribute_id)
453 OR ( ( Recinfo.attribute_id IS NULL )
454 AND ( p_attribute_id IS NULL )))
455 AND ( ( Recinfo.enabled_flag = p_enabled_flag)
456 OR ( ( Recinfo.enabled_flag IS NULL )
457 AND ( p_enabled_flag IS NULL )))
458
459 ) THEN
460 RETURN;
461 ELSE
462 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
463 APP_EXCEPTION.RAISE_EXCEPTION;
464 END IF;
465
466 for tlinfo in c1 loop
467 if (tlinfo.BASELANG = 'Y') then
468 if ( (tlinfo.DESCRIPTION = p_description)
469 ) then
470 null;
471 else
472 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
473 app_exception.raise_exception;
474 end if;
475 end if;
476 end loop;
477 return;
478
479 END Lock_Row;
480
481 --procedure ADD_LANGUAGE
482 procedure ADD_LANGUAGE
483 is
484 begin
485 delete from PV_ATTRIBUTE_CODES_TL T
486 where not exists
487 (select NULL
488 from PV_ATTRIBUTE_CODES_B B
489 where B.ATTR_CODE_ID = T.ATTR_CODE_ID
490 );
491
492 update PV_ATTRIBUTE_CODES_TL T set (
493 DESCRIPTION
494 ) = (select
495 B.DESCRIPTION
496 from PV_ATTRIBUTE_CODES_TL B
497 where B.ATTR_CODE_ID = T.ATTR_CODE_ID
498 and B.LANGUAGE = T.SOURCE_LANG)
499 where (
500 T.ATTR_CODE_ID,
501 T.LANGUAGE
502 ) in (select
503 SUBT.ATTR_CODE_ID,
504 SUBT.LANGUAGE
505 from PV_ATTRIBUTE_CODES_TL SUBB, PV_ATTRIBUTE_CODES_TL SUBT
506 where SUBB.ATTR_CODE_ID = SUBT.ATTR_CODE_ID
507 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
508 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
509 ));
510
511 insert into PV_ATTRIBUTE_CODES_TL (
512 ATTR_CODE_ID,
513 LAST_UPDATE_DATE,
514 LAST_UPDATED_BY,
515 CREATION_DATE,
516 CREATED_BY,
517 LAST_UPDATE_LOGIN,
518 OBJECT_VERSION_NUMBER,
519 DESCRIPTION,
520 --SECURITY_GROUP_ID,
524 B.ATTR_CODE_ID,
521 LANGUAGE,
522 SOURCE_LANG
523 ) select /*+ ORDERED */
525 B.LAST_UPDATE_DATE,
526 B.LAST_UPDATED_BY,
527 B.CREATION_DATE,
528 B.CREATED_BY,
529 B.LAST_UPDATE_LOGIN,
530 B.OBJECT_VERSION_NUMBER,
531 B.DESCRIPTION,
532 --B.SECURITY_GROUP_ID,
533 L.LANGUAGE_CODE,
534 B.SOURCE_LANG
535 from PV_ATTRIBUTE_CODES_TL B, FND_LANGUAGES L
536 where L.INSTALLED_FLAG in ('I', 'B')
537 and B.LANGUAGE = userenv('LANG')
538 and not exists
539 (select NULL
540 from PV_ATTRIBUTE_CODES_TL T
541 where T.ATTR_CODE_ID = B.ATTR_CODE_ID
542 and T.LANGUAGE = L.LANGUAGE_CODE);
543 end ADD_LANGUAGE;
544
545 --procedure TRANSLATE_ROW
546 procedure TRANSLATE_ROW(
547 p_attr_code_id in VARCHAR2
548 , p_description in VARCHAR2
549 , p_owner in VARCHAR2
550 ) is
551 begin
552 update PV_ATTRIBUTE_CODES_TL set
553 description = nvl(p_description, description),
554 source_lang = userenv('LANG'),
555 last_update_date = sysdate,
556 last_updated_by = decode(p_owner, 'SEED', 1, 0),
557 last_update_login = 0
558 where attr_code_id = p_attr_code_id
559 and userenv('LANG') in (language, source_lang);
560
561 end TRANSLATE_ROW;
562
563 --procedure LOAD_ROW
564 procedure LOAD_ROW(
565 p_ATTR_CODE_ID IN VARCHAR2,
566 p_ATTRIBUTE_ID IN VARCHAR2,
567 p_ENABLED_FLAG in VARCHAR2,
568 p_ATTR_CODE IN VARCHAR2,
569 p_DESCRIPTION in VARCHAR2 DEFAULT NULL ,
570 p_Owner in VARCHAR2
571 ) is
572
573 l_user_id number := 0;
574 l_obj_verno number;
575 l_dummy_char varchar2(1);
576 l_row_id varchar2(100);
577 l_attr_code_id number := p_ATTR_CODE_ID;
578
579 cursor c_obj_verno is
580 select object_version_number
581 from PV_ATTRIBUTE_CODES_B
582 where attr_code_id = p_ATTR_CODE_ID;
583
584 cursor c_chk_attrib_exists is
585 select 'x'
586 from PV_ATTRIBUTE_CODES_B
587 where attr_code_id = p_ATTR_CODE_ID;
588
589 BEGIN
590
591 l_user_id := fnd_load_util.owner_id(p_owner);
592
593 open c_chk_attrib_exists;
594 fetch c_chk_attrib_exists into l_dummy_char;
595 if c_chk_attrib_exists%notfound
596 then
597 close c_chk_attrib_exists;
598 l_obj_verno := 1;
599
600 PV_ATTRIBUTE_CODE_PKG.INSERT_ROW(
601 px_ATTR_CODE_ID => l_attr_code_id,
602 p_ATTR_CODE => p_attr_code,
603 p_LAST_UPDATE_DATE => SYSDATE,
604 p_LAST_UPDATED_BY => l_user_id,
605 p_CREATION_DATE => SYSDATE,
606 p_CREATED_BY => l_user_id,
607 p_LAST_UPDATE_LOGIN => 0,
608 px_OBJECT_VERSION_NUMBER => l_obj_verno,
609 p_ATTRIBUTE_ID => p_attribute_id,
610 p_ENABLED_FLAG => p_enabled_flag,
611 p_DESCRIPTION => p_DESCRIPTION);
612
613 else
614 close c_chk_attrib_exists;
615 open c_obj_verno;
616 fetch c_obj_verno into l_obj_verno;
617 close c_obj_verno;
618
619 PV_ATTRIBUTE_CODE_PKG.UPDATE_ROW_SEED(
620 p_ATTR_CODE_ID => p_attr_code_id,
621 p_ATTR_CODE => p_attr_code,
622 p_LAST_UPDATE_DATE => SYSDATE,
623 p_LAST_UPDATED_BY => l_user_id,
624 p_LAST_UPDATE_LOGIN => 0,
625 p_OBJECT_VERSION_NUMBER => l_obj_verno,
626 p_ATTRIBUTE_ID => p_attribute_id,
627 p_ENABLED_FLAG => p_enabled_flag,
628 p_DESCRIPTION => p_DESCRIPTION
629 );
630
631 end if;
632 END LOAD_ROW;
633
634 PROCEDURE LOAD_SEED_ROW (
635 P_UPLOAD_MODE IN VARCHAR2,
636 p_ATTR_CODE_ID IN VARCHAR2,
637 p_ATTRIBUTE_ID IN VARCHAR2,
638 p_ENABLED_FLAG in VARCHAR2,
639 p_ATTR_CODE IN VARCHAR2,
640 p_DESCRIPTION in VARCHAR2 DEFAULT NULL ,
641 p_Owner in VARCHAR2
642 )
643 IS
644
645 BEGIN
646 IF (p_upload_mode = 'NLS') THEN
647 PV_ATTRIBUTE_CODE_PKG.TRANSLATE_ROW (
648 p_ATTR_CODE_ID => p_ATTR_CODE_ID
649 , p_description => p_DESCRIPTION
650 , p_owner => p_Owner
651 );
652 ELSE
653 PV_ATTRIBUTE_CODE_PKG.LOAD_ROW (
654 p_ATTR_CODE_ID => p_ATTR_CODE_ID ,
655 p_ATTRIBUTE_ID => p_ATTRIBUTE_ID,
656 p_ENABLED_FLAG => p_ENABLED_FLAG,
657 p_ATTR_CODE => p_ATTR_CODE,
658 p_DESCRIPTION => p_DESCRIPTION,
659 p_Owner => p_Owner
660 );
661 END IF;
662 END LOAD_SEED_ROW;
663
664
665 END PV_ATTRIBUTE_CODE_PKG;
666