[Home] [Help]
PACKAGE BODY: APPS.AMS_CATEGORIES_PKG
Source
1 package body AMS_CATEGORIES_PKG as
2 /* $Header: amslctyb.pls 120.0 2005/05/31 16:52:41 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_CATEGORY_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_ARC_CATEGORY_CREATED_FOR in VARCHAR2,
8 X_ENABLED_FLAG in VARCHAR2,
9 X_PARENT_CATEGORY_ID in NUMBER,
10 X_ATTRIBUTE_CATEGORY in VARCHAR2,
11 X_ATTRIBUTE1 in VARCHAR2,
12 X_ATTRIBUTE2 in VARCHAR2,
13 X_ATTRIBUTE3 in VARCHAR2,
14 X_ATTRIBUTE4 in VARCHAR2,
15 X_ATTRIBUTE5 in VARCHAR2,
16 X_ATTRIBUTE6 in VARCHAR2,
17 X_ATTRIBUTE7 in VARCHAR2,
18 X_ATTRIBUTE8 in VARCHAR2,
19 X_ATTRIBUTE9 in VARCHAR2,
20 X_ATTRIBUTE10 in VARCHAR2,
21 X_ATTRIBUTE11 in VARCHAR2,
22 X_ATTRIBUTE12 in VARCHAR2,
23 X_ATTRIBUTE13 in VARCHAR2,
24 X_ATTRIBUTE14 in VARCHAR2,
25 X_ATTRIBUTE15 in VARCHAR2,
26 X_CATEGORY_NAME in VARCHAR2,
27 X_DESCRIPTION in VARCHAR2,
28 X_CREATION_DATE in DATE,
29 X_CREATED_BY in NUMBER,
30 X_LAST_UPDATE_DATE in DATE,
31 X_LAST_UPDATED_BY in NUMBER,
32 X_LAST_UPDATE_LOGIN in NUMBER,
33 X_ACCRUED_LIABILITY_ACCOUNT in NUMBER,
34 X_DED_ADJUSTMENT_ACCOUNT in NUMBER
35 ) is
36 cursor C is select ROWID from AMS_CATEGORIES_B
37 where CATEGORY_ID = X_CATEGORY_ID
38 ;
39 begin
40 insert into AMS_CATEGORIES_B (
41 CATEGORY_ID,
42 OBJECT_VERSION_NUMBER,
43 ARC_CATEGORY_CREATED_FOR,
44 ENABLED_FLAG,
45 PARENT_CATEGORY_ID,
46 ATTRIBUTE_CATEGORY,
47 ATTRIBUTE1,
48 ATTRIBUTE2,
49 ATTRIBUTE3,
50 ATTRIBUTE4,
51 ATTRIBUTE5,
52 ATTRIBUTE6,
53 ATTRIBUTE7,
54 ATTRIBUTE8,
55 ATTRIBUTE9,
56 ATTRIBUTE10,
57 ATTRIBUTE11,
58 ATTRIBUTE12,
59 ATTRIBUTE13,
60 ATTRIBUTE14,
61 ATTRIBUTE15,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN,
67 ACCRUED_LIABILITY_ACCOUNT,
68 DED_ADJUSTMENT_ACCOUNT
69 ) values (
70 X_CATEGORY_ID,
71 X_OBJECT_VERSION_NUMBER,
72 X_ARC_CATEGORY_CREATED_FOR,
73 X_ENABLED_FLAG,
74 X_PARENT_CATEGORY_ID,
75 X_ATTRIBUTE_CATEGORY,
76 X_ATTRIBUTE1,
77 X_ATTRIBUTE2,
78 X_ATTRIBUTE3,
79 X_ATTRIBUTE4,
80 X_ATTRIBUTE5,
81 X_ATTRIBUTE6,
82 X_ATTRIBUTE7,
83 X_ATTRIBUTE8,
84 X_ATTRIBUTE9,
85 X_ATTRIBUTE10,
86 X_ATTRIBUTE11,
87 X_ATTRIBUTE12,
88 X_ATTRIBUTE13,
89 X_ATTRIBUTE14,
90 X_ATTRIBUTE15,
91 X_CREATION_DATE,
92 X_CREATED_BY,
93 X_LAST_UPDATE_DATE,
94 X_LAST_UPDATED_BY,
95 X_LAST_UPDATE_LOGIN,
96 X_ACCRUED_LIABILITY_ACCOUNT,
97 X_DED_ADJUSTMENT_ACCOUNT
98 );
99
100 insert into AMS_CATEGORIES_TL (
101 CATEGORY_ID,
102 LAST_UPDATE_DATE,
103 LAST_UPDATED_BY,
104 CREATION_DATE,
105 CREATED_BY,
106 LAST_UPDATE_LOGIN,
107 CATEGORY_NAME,
108 DESCRIPTION,
109 LANGUAGE,
110 SOURCE_LANG
111 ) select
112 X_CATEGORY_ID,
113 X_LAST_UPDATE_DATE,
114 X_LAST_UPDATED_BY,
115 X_CREATION_DATE,
116 X_CREATED_BY,
117 X_LAST_UPDATE_LOGIN,
118 X_CATEGORY_NAME,
119 X_DESCRIPTION,
120 L.LANGUAGE_CODE,
121 userenv('LANG')
122 from FND_LANGUAGES L
123 where L.INSTALLED_FLAG in ('I', 'B')
124 and not exists
125 (select NULL
126 from AMS_CATEGORIES_TL T
127 where T.CATEGORY_ID = X_CATEGORY_ID
128 and T.LANGUAGE = L.LANGUAGE_CODE);
129
130 open c;
131 fetch c into X_ROWID;
132 if (c%notfound) then
133 close c;
134 raise no_data_found;
135 end if;
136 close c;
137
138 end INSERT_ROW;
139
140 procedure LOCK_ROW (
141 X_CATEGORY_ID in NUMBER,
142 X_OBJECT_VERSION_NUMBER in NUMBER,
143 X_ARC_CATEGORY_CREATED_FOR in VARCHAR2,
144 X_ENABLED_FLAG in VARCHAR2,
145 X_PARENT_CATEGORY_ID in NUMBER,
146 X_ATTRIBUTE_CATEGORY in VARCHAR2,
147 X_ATTRIBUTE1 in VARCHAR2,
148 X_ATTRIBUTE2 in VARCHAR2,
149 X_ATTRIBUTE3 in VARCHAR2,
150 X_ATTRIBUTE4 in VARCHAR2,
151 X_ATTRIBUTE5 in VARCHAR2,
152 X_ATTRIBUTE6 in VARCHAR2,
153 X_ATTRIBUTE7 in VARCHAR2,
154 X_ATTRIBUTE8 in VARCHAR2,
155 X_ATTRIBUTE9 in VARCHAR2,
156 X_ATTRIBUTE10 in VARCHAR2,
157 X_ATTRIBUTE11 in VARCHAR2,
158 X_ATTRIBUTE12 in VARCHAR2,
159 X_ATTRIBUTE13 in VARCHAR2,
160 X_ATTRIBUTE14 in VARCHAR2,
161 X_ATTRIBUTE15 in VARCHAR2,
162 X_CATEGORY_NAME in VARCHAR2,
163 X_DESCRIPTION in VARCHAR2,
164 X_ACCRUED_LIABILITY_ACCOUNT in NUMBER,
165 X_DED_ADJUSTMENT_ACCOUNT in NUMBER
166 ) is
167 cursor c is select
168 OBJECT_VERSION_NUMBER,
169 ARC_CATEGORY_CREATED_FOR,
170 ENABLED_FLAG,
171 PARENT_CATEGORY_ID,
172 ATTRIBUTE_CATEGORY,
173 ATTRIBUTE1,
174 ATTRIBUTE2,
175 ATTRIBUTE3,
176 ATTRIBUTE4,
177 ATTRIBUTE5,
178 ATTRIBUTE6,
179 ATTRIBUTE7,
180 ATTRIBUTE8,
181 ATTRIBUTE9,
182 ATTRIBUTE10,
183 ATTRIBUTE11,
184 ATTRIBUTE12,
185 ATTRIBUTE13,
186 ATTRIBUTE14,
187 ATTRIBUTE15,
188 ACCRUED_LIABILITY_ACCOUNT,
189 DED_ADJUSTMENT_ACCOUNT
190 from AMS_CATEGORIES_B
191 where CATEGORY_ID = X_CATEGORY_ID
192 for update of CATEGORY_ID nowait;
193 recinfo c%rowtype;
194
195 cursor c1 is select
196 CATEGORY_NAME,
197 DESCRIPTION,
198 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
199 from AMS_CATEGORIES_TL
200 where CATEGORY_ID = X_CATEGORY_ID
201 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
202 for update of CATEGORY_ID nowait;
203 begin
204 open c;
205 fetch c into recinfo;
206 if (c%notfound) then
207 close c;
208 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
209 app_exception.raise_exception;
210 end if;
211 close c;
212 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
213 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
214 AND ((recinfo.ARC_CATEGORY_CREATED_FOR = X_ARC_CATEGORY_CREATED_FOR)
215 OR ((recinfo.ARC_CATEGORY_CREATED_FOR is null) AND (X_ARC_CATEGORY_CREATED_FOR is null)))
216 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
217 AND ((recinfo.PARENT_CATEGORY_ID = X_PARENT_CATEGORY_ID)
218 OR ((recinfo.PARENT_CATEGORY_ID is null) AND (X_PARENT_CATEGORY_ID is null)))
219 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
220 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
221 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
222 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
223 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
224 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
225 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
226 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
227 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
228 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
229 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
230 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
231 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
232 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
233 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
234 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
235 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
236 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
237 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
238 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
239 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
240 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
241 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
242 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
243 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
244 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
245 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
246 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
247 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
248 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
249 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
250 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
251 AND ((recinfo.ACCRUED_LIABILITY_ACCOUNT = X_ACCRUED_LIABILITY_ACCOUNT)
252 OR ((recinfo.ACCRUED_LIABILITY_ACCOUNT is null) AND (X_ACCRUED_LIABILITY_ACCOUNT is null)))
253 AND ((recinfo.DED_ADJUSTMENT_ACCOUNT = X_DED_ADJUSTMENT_ACCOUNT)
254 OR ((recinfo.DED_ADJUSTMENT_ACCOUNT is null) AND (X_DED_ADJUSTMENT_ACCOUNT is null)))
255 ) then
256 null;
257 else
258 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
259 app_exception.raise_exception;
260 end if;
261
262 for tlinfo in c1 loop
263 if (tlinfo.BASELANG = 'Y') then
264 if ( (tlinfo.CATEGORY_NAME = X_CATEGORY_NAME)
265 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
266 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
267 ) then
268 null;
269 else
270 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
271 app_exception.raise_exception;
272 end if;
273 end if;
274 end loop;
275 return;
276 end LOCK_ROW;
277
278 procedure UPDATE_ROW (
279 X_CATEGORY_ID in NUMBER,
280 X_OBJECT_VERSION_NUMBER in NUMBER,
281 X_ARC_CATEGORY_CREATED_FOR in VARCHAR2,
282 X_ENABLED_FLAG in VARCHAR2,
283 X_PARENT_CATEGORY_ID in NUMBER,
284 X_ATTRIBUTE_CATEGORY in VARCHAR2,
285 X_ATTRIBUTE1 in VARCHAR2,
286 X_ATTRIBUTE2 in VARCHAR2,
287 X_ATTRIBUTE3 in VARCHAR2,
288 X_ATTRIBUTE4 in VARCHAR2,
289 X_ATTRIBUTE5 in VARCHAR2,
290 X_ATTRIBUTE6 in VARCHAR2,
291 X_ATTRIBUTE7 in VARCHAR2,
292 X_ATTRIBUTE8 in VARCHAR2,
293 X_ATTRIBUTE9 in VARCHAR2,
294 X_ATTRIBUTE10 in VARCHAR2,
295 X_ATTRIBUTE11 in VARCHAR2,
296 X_ATTRIBUTE12 in VARCHAR2,
297 X_ATTRIBUTE13 in VARCHAR2,
298 X_ATTRIBUTE14 in VARCHAR2,
299 X_ATTRIBUTE15 in VARCHAR2,
300 X_CATEGORY_NAME in VARCHAR2,
301 X_DESCRIPTION in VARCHAR2,
302 X_LAST_UPDATE_DATE in DATE,
303 X_LAST_UPDATED_BY in NUMBER,
304 X_LAST_UPDATE_LOGIN in NUMBER,
305 X_ACCRUED_LIABILITY_ACCOUNT in NUMBER,
306 X_DED_ADJUSTMENT_ACCOUNT in NUMBER
307 ) is
308 begin
309 update AMS_CATEGORIES_B set
310 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
311 ARC_CATEGORY_CREATED_FOR = X_ARC_CATEGORY_CREATED_FOR,
312 ENABLED_FLAG = X_ENABLED_FLAG,
313 PARENT_CATEGORY_ID = X_PARENT_CATEGORY_ID,
314 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
315 ATTRIBUTE1 = X_ATTRIBUTE1,
316 ATTRIBUTE2 = X_ATTRIBUTE2,
317 ATTRIBUTE3 = X_ATTRIBUTE3,
318 ATTRIBUTE4 = X_ATTRIBUTE4,
319 ATTRIBUTE5 = X_ATTRIBUTE5,
320 ATTRIBUTE6 = X_ATTRIBUTE6,
321 ATTRIBUTE7 = X_ATTRIBUTE7,
322 ATTRIBUTE8 = X_ATTRIBUTE8,
323 ATTRIBUTE9 = X_ATTRIBUTE9,
324 ATTRIBUTE10 = X_ATTRIBUTE10,
325 ATTRIBUTE11 = X_ATTRIBUTE11,
326 ATTRIBUTE12 = X_ATTRIBUTE12,
327 ATTRIBUTE13 = X_ATTRIBUTE13,
328 ATTRIBUTE14 = X_ATTRIBUTE14,
329 ATTRIBUTE15 = X_ATTRIBUTE15,
330 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
331 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
332 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
333 ACCRUED_LIABILITY_ACCOUNT = X_ACCRUED_LIABILITY_ACCOUNT,
334 DED_ADJUSTMENT_ACCOUNT = X_DED_ADJUSTMENT_ACCOUNT
335 where CATEGORY_ID = X_CATEGORY_ID;
336
337 if (sql%notfound) then
338 raise no_data_found;
339 end if;
340
341 update AMS_CATEGORIES_TL set
342 CATEGORY_NAME = X_CATEGORY_NAME,
343 DESCRIPTION = X_DESCRIPTION,
344 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
345 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
346 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
347 SOURCE_LANG = userenv('LANG')
348 where CATEGORY_ID = X_CATEGORY_ID
349 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
350
351 if (sql%notfound) then
352 raise no_data_found;
353 end if;
354 end UPDATE_ROW;
355
356 procedure DELETE_ROW (
357 X_CATEGORY_ID in NUMBER
358 ) is
359 begin
360 delete from AMS_CATEGORIES_TL
361 where CATEGORY_ID = X_CATEGORY_ID;
362
363 if (sql%notfound) then
364 raise no_data_found;
365 end if;
366
367 delete from AMS_CATEGORIES_B
368 where CATEGORY_ID = X_CATEGORY_ID;
369
370 if (sql%notfound) then
371 raise no_data_found;
372 end if;
373 end DELETE_ROW;
374
375 procedure ADD_LANGUAGE
376 is
377 begin
378 delete from AMS_CATEGORIES_TL T
379 where not exists
380 (select NULL
381 from AMS_CATEGORIES_B B
382 where B.CATEGORY_ID = T.CATEGORY_ID
383 );
384
385 update AMS_CATEGORIES_TL T set (
386 CATEGORY_NAME,
387 DESCRIPTION
388 ) = (select
389 B.CATEGORY_NAME,
390 B.DESCRIPTION
391 from AMS_CATEGORIES_TL B
392 where B.CATEGORY_ID = T.CATEGORY_ID
393 and B.LANGUAGE = T.SOURCE_LANG)
394 where (
395 T.CATEGORY_ID,
396 T.LANGUAGE
397 ) in (select
398 SUBT.CATEGORY_ID,
399 SUBT.LANGUAGE
400 from AMS_CATEGORIES_TL SUBB, AMS_CATEGORIES_TL SUBT
401 where SUBB.CATEGORY_ID = SUBT.CATEGORY_ID
402 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
403 and (SUBB.CATEGORY_NAME <> SUBT.CATEGORY_NAME
404 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
405 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
406 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
407 ));
408
409 insert into AMS_CATEGORIES_TL (
410 CATEGORY_ID,
411 LAST_UPDATE_DATE,
412 LAST_UPDATED_BY,
413 CREATION_DATE,
414 CREATED_BY,
415 LAST_UPDATE_LOGIN,
416 CATEGORY_NAME,
417 DESCRIPTION,
418 LANGUAGE,
419 SOURCE_LANG
420 ) select
421 B.CATEGORY_ID,
422 B.LAST_UPDATE_DATE,
423 B.LAST_UPDATED_BY,
424 B.CREATION_DATE,
425 B.CREATED_BY,
426 B.LAST_UPDATE_LOGIN,
427 B.CATEGORY_NAME,
428 B.DESCRIPTION,
429 L.LANGUAGE_CODE,
430 B.SOURCE_LANG
431 from AMS_CATEGORIES_TL B, FND_LANGUAGES L
432 where L.INSTALLED_FLAG in ('I', 'B')
433 and B.LANGUAGE = userenv('LANG')
434 and not exists
435 (select NULL
436 from AMS_CATEGORIES_TL T
437 where T.CATEGORY_ID = B.CATEGORY_ID
438 and T.LANGUAGE = L.LANGUAGE_CODE);
439 end ADD_LANGUAGE;
440
441 procedure TRANSLATE_ROW(
442 x_category_id in NUMBER
443 , x_category_name in VARCHAR2
444 , x_description in VARCHAR2
445 , x_owner in VARCHAR2
446 ) is
447 begin
448 update AMS_CATEGORIES_TL set
449 category_name = nvl(x_category_name, category_name),
450 description = nvl(x_description, description),
454 last_update_login = 0
451 source_lang = userenv('LANG'),
452 last_update_date = sysdate,
453 last_updated_by = decode(x_owner, 'SEED', 1, 0),
455 where category_id = x_category_id
456 and userenv('LANG') in (language, source_lang);
457 end TRANSLATE_ROW;
458
459 procedure LOAD_ROW(
460 X_CATEGORY_ID IN NUMBER,
461 X_ARC_CATEGORY_CREATED_FOR in VARCHAR2,
462 X_ENABLED_FLAG in VARCHAR2,
463 X_PARENT_CATEGORY_ID in NUMBER,
464 X_ATTRIBUTE_CATEGORY in VARCHAR2,
465 X_ATTRIBUTE1 in VARCHAR2,
466 X_ATTRIBUTE2 in VARCHAR2,
467 X_ATTRIBUTE3 in VARCHAR2,
468 X_ATTRIBUTE4 in VARCHAR2,
469 X_ATTRIBUTE5 in VARCHAR2,
470 X_ATTRIBUTE6 in VARCHAR2,
471 X_ATTRIBUTE7 in VARCHAR2,
472 X_ATTRIBUTE8 in VARCHAR2,
473 X_ATTRIBUTE9 in VARCHAR2,
474 X_ATTRIBUTE10 in VARCHAR2,
475 X_ATTRIBUTE11 in VARCHAR2,
476 X_ATTRIBUTE12 in VARCHAR2,
477 X_ATTRIBUTE13 in VARCHAR2,
478 X_ATTRIBUTE14 in VARCHAR2,
479 X_ATTRIBUTE15 in VARCHAR2,
480 X_CATEGORY_NAME in VARCHAR2,
481 X_DESCRIPTION in VARCHAR2,
482 X_Owner in VARCHAR2,
483 X_ACCRUED_LIABILITY_ACCOUNT in NUMBER,
484 X_DED_ADJUSTMENT_ACCOUNT in NUMBER,
485 X_CUSTOM_MODE in VARCHAR2
486 ) is
487
488 l_user_id number := 1;
489 l_obj_verno number;
490 l_dummy_char varchar2(1);
491 l_row_id varchar2(100);
492 l_category_id number;
493 l_db_luby_id number;
494
495
496 cursor c_db_data_details is
497 select last_updated_by, nvl(object_version_number,1)
498 from AMS_CATEGORIES_B
499 where category_id = X_CATEGORY_ID;
500 /*
501 cursor c_obj_verno is
502 select object_version_number
503 from AMS_CATEGORIES_B
504 where category_id = X_CATEGORY_ID;
505 */
506 cursor c_chk_cty_exists is
507 select 'x'
508 from AMS_CATEGORIES_B
509 where category_id = X_CATEGORY_ID;
510
511 cursor c_get_ctyid is
512 select AMS_CATEGORIES_B_S.nextval
513 from dual;
514
515 BEGIN
516
517 -- set the last_updated_by to be used while updating the data in customer data.
518 if X_OWNER = 'SEED' then
519 l_user_id := 1;
520 elsif X_OWNER = 'ORACLE' THEN
521 l_user_id := 2;
522 elsif X_OWNER = 'SYSADMIN' THEN
523 l_user_id := 0;
524 end if ;
525
526 open c_chk_cty_exists;
527 fetch c_chk_cty_exists into l_dummy_char;
528 if c_chk_cty_exists%notfound
529 -- data does not exist at customer site and hence create the data
530 then
531 close c_chk_cty_exists;
532 if X_CATEGORY_ID is null
533 then
534 open c_get_ctyid;
535 fetch c_get_ctyid into l_category_id;
536 close c_get_ctyid;
537 else
538 l_category_id := X_CATEGORY_ID;
539 end if;
540 l_obj_verno := 1;
541 AMS_CATEGORIES_PKG.INSERT_ROW(
542 X_ROWID => l_row_id,
543 X_CATEGORY_ID => l_category_id,
544 X_OBJECT_VERSION_NUMBER => l_obj_verno,
545 X_ARC_CATEGORY_CREATED_FOR => X_ARC_CATEGORY_CREATED_FOR ,
546 X_ENABLED_FLAG => X_ENABLED_FLAG,
547 X_PARENT_CATEGORY_ID => X_PARENT_CATEGORY_ID,
548 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
549 X_ATTRIBUTE1 => X_ATTRIBUTE1,
550 X_ATTRIBUTE2 => X_ATTRIBUTE2,
551 X_ATTRIBUTE3 => X_ATTRIBUTE3,
552 X_ATTRIBUTE4 => X_ATTRIBUTE4,
553 X_ATTRIBUTE5 => X_ATTRIBUTE5,
554 X_ATTRIBUTE6 => X_ATTRIBUTE6,
555 X_ATTRIBUTE7 => X_ATTRIBUTE7,
556 X_ATTRIBUTE8 => X_ATTRIBUTE8,
557 X_ATTRIBUTE9 => X_ATTRIBUTE9,
558 X_ATTRIBUTE10 => X_ATTRIBUTE10,
559 X_ATTRIBUTE11 => X_ATTRIBUTE11,
560 X_ATTRIBUTE12 => X_ATTRIBUTE12,
561 X_ATTRIBUTE13 => X_ATTRIBUTE13,
562 X_ATTRIBUTE14 => X_ATTRIBUTE14,
563 X_ATTRIBUTE15 => X_ATTRIBUTE15,
564 X_CATEGORY_NAME => X_CATEGORY_NAME,
565 X_DESCRIPTION => X_DESCRIPTION,
566 X_CREATION_DATE => SYSDATE,
567 X_CREATED_BY => l_user_id,
568 X_LAST_UPDATE_DATE => SYSDATE,
569 X_LAST_UPDATED_BY => l_user_id,
570 X_LAST_UPDATE_LOGIN => 0,
571 X_ACCRUED_LIABILITY_ACCOUNT => X_ACCRUED_LIABILITY_ACCOUNT,
572 X_DED_ADJUSTMENT_ACCOUNT => X_DED_ADJUSTMENT_ACCOUNT
573 );
574 else
575 -- Update the data as per above rules
576 close c_chk_cty_exists;
577 open c_db_data_details;
578 fetch c_db_data_details into l_db_luby_id, l_obj_verno;
579 close c_db_data_details;
580
581 if (l_db_luby_id IN (1,2,0)
582 OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
583
584 AMS_CATEGORIES_PKG.UPDATE_ROW(
585 X_CATEGORY_ID => X_CATEGORY_ID,
586 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
587 X_ARC_CATEGORY_CREATED_FOR => X_ARC_CATEGORY_CREATED_FOR ,
588 X_ENABLED_FLAG => X_ENABLED_FLAG,
592 X_ATTRIBUTE2 => X_ATTRIBUTE2,
589 X_PARENT_CATEGORY_ID => X_PARENT_CATEGORY_ID,
590 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
591 X_ATTRIBUTE1 => X_ATTRIBUTE1,
593 X_ATTRIBUTE3 => X_ATTRIBUTE3,
594 X_ATTRIBUTE4 => X_ATTRIBUTE4,
595 X_ATTRIBUTE5 => X_ATTRIBUTE5,
596 X_ATTRIBUTE6 => X_ATTRIBUTE6,
597 X_ATTRIBUTE7 => X_ATTRIBUTE7,
598 X_ATTRIBUTE8 => X_ATTRIBUTE8,
599 X_ATTRIBUTE9 => X_ATTRIBUTE9,
600 X_ATTRIBUTE10 => X_ATTRIBUTE10,
601 X_ATTRIBUTE11 => X_ATTRIBUTE11,
602 X_ATTRIBUTE12 => X_ATTRIBUTE12,
603 X_ATTRIBUTE13 => X_ATTRIBUTE13,
604 X_ATTRIBUTE14 => X_ATTRIBUTE14,
605 X_ATTRIBUTE15 => X_ATTRIBUTE15,
606 X_CATEGORY_NAME => X_CATEGORY_NAME,
607 X_DESCRIPTION => X_DESCRIPTION,
608 X_LAST_UPDATE_DATE => SYSDATE,
609 X_LAST_UPDATED_BY => l_user_id,
610 X_LAST_UPDATE_LOGIN => 0,
611 X_ACCRUED_LIABILITY_ACCOUNT => X_ACCRUED_LIABILITY_ACCOUNT,
612 X_DED_ADJUSTMENT_ACCOUNT => X_DED_ADJUSTMENT_ACCOUNT
613 );
614
615 end if;
619
616 end if;
617
618 END LOAD_ROW;
620 end AMS_CATEGORIES_PKG;