[Home] [Help]
PACKAGE BODY: APPS.BOM_STRUCTURE_TYPES_PKG
Source
1 PACKAGE BODY BOM_STRUCTURE_TYPES_PKG as
2 /* $Header: BOMPSTYPB.pls 120.2 2006/08/29 08:19:26 hgelli noship $ */
3
4 PROCEDURE Insert_Row(X_Structure_Type_Name VARCHAR2,
5 X_Last_Update_Date DATE,
6 X_Last_Updated_By NUMBER,
7 X_Creation_Date DATE,
8 X_Created_By NUMBER,
9 X_Last_Update_Login NUMBER,
10 X_Item_Catalog_Group_Id NUMBER,
11 X_Effective_Date DATE,
12 X_Structure_Creation_Allowed VARCHAR2,
13 X_Allow_Subtypes VARCHAR2,
14 X_Attribute_Category VARCHAR2,
15 X_Attribute1 VARCHAR2,
16 X_Attribute2 VARCHAR2,
17 X_Attribute3 VARCHAR2,
18 X_Attribute4 VARCHAR2,
19 X_Attribute5 VARCHAR2,
20 X_Attribute6 VARCHAR2,
21 X_Attribute7 VARCHAR2,
22 X_Attribute8 VARCHAR2,
23 X_Attribute9 VARCHAR2,
24 X_Attribute10 VARCHAR2,
25 X_Attribute11 VARCHAR2,
26 X_Attribute12 VARCHAR2,
27 X_Attribute13 VARCHAR2,
28 X_Attribute14 VARCHAR2,
29 X_Attribute15 VARCHAR2,
30 X_Disable_Date VARCHAR2,
31 X_Parent_Structure_Type_Id NUMBER,
32 X_Enable_Attachments_Flag VARCHAR2,
33 X_Display_Name VARCHAR2,
34 X_Description VARCHAR2,
35 X_Upload_mode VARCHAR2,
36 X_Custom_mode VARCHAR2,
37 X_Owner VARCHAR2
38 )
39 IS
40 BEGIN
41 declare
42 srv_id number;
43 str_id number;
44 f_luby number; -- entity owner in file
45 f_ludate date; -- entity update date in file
46 db_luby number; -- entity owner in db
47 db_ludate date; -- entity update date in db
48 stype_exists number;
49
50 BEGIN
51 IF (X_Upload_mode = 'NLS')
52 THEN
53
54 UPDATE BOM_STRUCTURE_TYPES_TL
55 SET
56 DISPLAY_NAME = X_Display_Name,
57 DESCRIPTION = X_Description,
58 LAST_UPDATE_DATE = X_Last_Update_Date,
59 LAST_UPDATED_BY = X_Last_Updated_By,
60 LAST_UPDATE_LOGIN = X_Last_Update_Login,
61 SOURCE_LANG = USERENV('LANG')
62 WHERE STRUCTURE_TYPE_ID = (SELECT STRUCTURE_TYPE_ID FROM BOM_STRUCTURE_TYPES_B
63 WHERE STRUCTURE_TYPE_NAME = X_Structure_Type_Name)
64 AND USERENV('LANG') IN (LANGUAGE,SOURCE_LANG);
65 ELSE
66
67 -- Translate owner to file_last_updated_by
68 f_luby := fnd_load_util.owner_id(X_Owner);
69
70 -- Translate char last_update_date to date
71 f_ludate := nvl(X_Last_Update_Date, sysdate);
72
73 -- Get current owner of row in database
74
75 BEGIN
76
77 SELECT LAST_UPDATED_BY,LAST_UPDATE_DATE
78 INTO db_luby, db_ludate
79 FROM BOM_STRUCTURE_TYPES_B
80 WHERE STRUCTURE_TYPE_NAME = X_Structure_Type_Name;
81
82 stype_exists := 1;
83
84 EXCEPTION WHEN NO_DATA_FOUND
85 THEN
86 db_luby := f_luby;
87 db_ludate := f_ludate;
88 stype_exists := 0;
89 END;
90
91 IF (stype_exists = 0)
92 THEN
93
94 SELECT BOM_STRUCTURE_TYPES_B_S.nextval INTO str_id FROM dual;
95
96 INSERT INTO BOM_STRUCTURE_TYPES_B
97 (
98 STRUCTURE_TYPE_ID,
99 STRUCTURE_TYPE_NAME,
100 LAST_UPDATE_DATE,
101 LAST_UPDATED_BY,
102 CREATION_DATE,
103 CREATED_BY,
104 LAST_UPDATE_LOGIN,
105 ITEM_CATALOG_GROUP_ID,
106 EFFECTIVE_DATE,
107 STRUCTURE_CREATION_ALLOWED,
108 ATTRIBUTE_CATEGORY,
109 ATTRIBUTE1,
110 ATTRIBUTE2,
111 ATTRIBUTE3,
112 ATTRIBUTE4,
113 ATTRIBUTE5,
114 ATTRIBUTE6,
115 ATTRIBUTE7,
116 ATTRIBUTE8,
117 ATTRIBUTE9,
118 ATTRIBUTE10,
119 ATTRIBUTE11,
120 ATTRIBUTE12,
121 ATTRIBUTE13,
122 ATTRIBUTE14,
123 ATTRIBUTE15,
124 DISABLE_DATE,
125 PARENT_STRUCTURE_TYPE_ID,
126 ENABLE_ATTACHMENTS_FLAG,
127 ALLOW_SUBTYPES
128 )
129 SELECT
130 str_id,
131 X_Structure_Type_Name,
132 SYSDATE,
133 X_Last_Updated_By,
134 SYSDATE,
135 X_Created_By,
136 X_Last_Update_Login,
137 X_Item_Catalog_Group_Id,
138 SYSDATE,
139 X_Structure_Creation_Allowed,
140 X_Attribute_Category,
141 X_Attribute1,
142 X_Attribute2,
143 X_Attribute3,
144 X_Attribute4,
145 X_Attribute5,
146 X_Attribute6,
147 X_Attribute7,
148 X_Attribute8,
149 X_Attribute9,
150 X_Attribute10,
151 X_Attribute11,
152 X_Attribute12,
153 X_Attribute13,
154 X_Attribute14,
155 X_Attribute15,
156 X_Disable_Date,
157 X_Parent_Structure_Type_Id,
158 X_Enable_Attachments_Flag,
159 X_Allow_Subtypes
160 FROM DUAL
161 WHERE NOT EXISTS (SELECT 1 from BOM_STRUCTURE_TYPES_B
162 WHERE STRUCTURE_TYPE_NAME = X_Structure_Type_Name );
163
164
165 INSERT INTO BOM_STRUCTURE_TYPES_TL
166 ( STRUCTURE_TYPE_ID,
167 LANGUAGE,
168 SOURCE_LANG,
169 DISPLAY_NAME,
170 DESCRIPTION,
171 LAST_UPDATE_DATE,
172 LAST_UPDATED_BY,
173 CREATION_DATE ,
174 CREATED_BY,
175 LAST_UPDATE_LOGIN
176 )
177 SELECT
178 str_id,
179 L.LANGUAGE_CODE,
180 USERENV('LANG'),
181 X_Display_Name,
182 X_Description,
183 SYSDATE,
184 X_Last_Updated_By,
185 SYSDATE,
186 X_Created_By,
187 X_Last_Update_Login
188 FROM FND_LANGUAGES L
189 WHERE L.INSTALLED_FLAG IN ('I','B')
190 AND NOT EXISTS
191 (SELECT NULL FROM BOM_STRUCTURE_TYPES_TL TL,BOM_STRUCTURE_TYPES_B B
192 WHERE B.STRUCTURE_TYPE_ID = TL.STRUCTURE_TYPE_ID
193 AND B.STRUCTURE_TYPE_NAME = X_Structure_Type_Name
194 AND TL.LANGUAGE = L.LANGUAGE_CODE);
195
196 END IF; -- End of IF for Insert into B and TL
197
198 -- Row exists, test if it should be over-written.
199 IF (stype_exists = 1) AND (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_Custom_mode))
200 THEN
201
202 UPDATE BOM_STRUCTURE_TYPES_B
203 SET
204 ITEM_CATALOG_GROUP_ID = X_Item_Catalog_Group_Id,
205 EFFECTIVE_DATE = X_Effective_Date,
206 STRUCTURE_CREATION_ALLOWED = X_Structure_Creation_Allowed,
207 ALLOW_SUBTYPES = X_Allow_Subtypes,
208 LAST_UPDATE_DATE = X_Last_Update_Date,
209 LAST_UPDATED_BY = X_Last_Updated_By,
210 LAST_UPDATE_LOGIN = X_Last_Update_Login,
211 ATTRIBUTE_CATEGORY = X_Attribute_Category ,
212 ATTRIBUTE1 = X_Attribute1,
213 ATTRIBUTE2 = X_Attribute2,
214 ATTRIBUTE3 = X_Attribute3,
215 ATTRIBUTE4 = X_Attribute4,
216 ATTRIBUTE5 = X_Attribute5,
217 ATTRIBUTE6 = X_Attribute6,
218 ATTRIBUTE7 = X_Attribute7,
219 ATTRIBUTE8 = X_Attribute8,
220 ATTRIBUTE9 = X_Attribute9,
221 ATTRIBUTE10 = X_Attribute10,
222 ATTRIBUTE11 = X_Attribute11,
223 ATTRIBUTE12 = X_Attribute12,
224 ATTRIBUTE13 = X_Attribute13,
225 ATTRIBUTE14 = X_Attribute14,
226 ATTRIBUTE15 = X_Attribute15,
227 DISABLE_DATE = X_Disable_Date,
228 PARENT_STRUCTURE_TYPE_ID = X_Parent_Structure_Type_Id,
229 ENABLE_ATTACHMENTS_FLAG = X_Enable_Attachments_Flag
230 WHERE
231 STRUCTURE_TYPE_NAME = X_Structure_Type_Name;
232
233
234 UPDATE BOM_STRUCTURE_TYPES_TL
235 SET
236 DISPLAY_NAME = X_Display_Name,
237 DESCRIPTION = X_Description,
238 LAST_UPDATE_DATE = X_Last_Update_Date,
239 LAST_UPDATED_BY = X_Last_Updated_By,
240 LAST_UPDATE_LOGIN = X_Last_Update_Login,
241 SOURCE_LANG = USERENV('LANG')
242 WHERE STRUCTURE_TYPE_ID =
243 (SELECT STRUCTURE_TYPE_ID FROM BOM_STRUCTURE_TYPES_B
244 WHERE STRUCTURE_TYPE_NAME = X_Structure_Type_Name)
245 AND USERENV('LANG') IN (LANGUAGE,SOURCE_LANG);
246
247 END IF; -- End of Test for Updates
248 END IF; -- End of NLS MODE
249 END;
250 END Insert_Row;
251
252 /*
253
254 -- Will be using Add_Language generated by FND table handler
255
256 PROCEDURE ADD_LANGUAGE
257 IS
258 BEGIN
259 INSERT INTO BOM_STRUCTURE_TYPES_TL
260 (
261 STRUCTURE_TYPE_ID,
262 LANGUAGE,
263 SOURCE_LANG,
264 DISPLAY_NAME,
265 DESCRIPTION,
266 LAST_UPDATE_DATE,
267 LAST_UPDATED_BY,
268 CREATION_DATE ,
269 CREATED_BY,
270 LAST_UPDATE_LOGIN
271 )
272 SELECT
273 B.structure_type_id,
274 L.LANGUAGE_CODE,
275 userenv('LANG'),
276 B.DISPLAY_NAME,
277 B.DESCRIPTION,
278 SYSDATE,
279 B.LAST_UPDATED_BY,
280 SYSDATE,
281 B.CREATED_BY,
282 B.LAST_UPDATE_LOGIN
283 FROM FND_LANGUAGES L,
284 BOM_STRUCTURE_TYPES_TL B
285 WHERE L.INSTALLED_FLAG IN ('I','B')
286 AND NOT EXISTS
287 (SELECT NULL FROM BOM_STRUCTURE_TYPES_TL TL
288 WHERE TL.STRUCTURE_TYPE_ID = B.STRUCTURE_TYPE_ID
289 AND TL.LANGUAGE = L.LANGUAGE_CODE);
290
291 END ADD_LANGUAGE;
292
293 */
294 -- -------------- START OF CODE FOR TABLE HANDLERS FOR BOM_STRUCTURE_TYPES_VL --------------
295 procedure INSERT_ROW (
296 X_ROWID in out nocopy VARCHAR2,
297 X_STRUCTURE_TYPE_ID in NUMBER,
298 X_ATTRIBUTE12 in VARCHAR2,
299 X_ATTRIBUTE13 in VARCHAR2,
300 X_DISABLE_DATE in DATE,
301 X_STRUCTURE_TYPE_NAME in VARCHAR2,
302 X_ITEM_CATALOG_GROUP_ID in NUMBER,
303 X_EFFECTIVE_DATE in DATE,
304 X_STRUCTURE_CREATION_ALLOWED in VARCHAR2,
305 X_ATTRIBUTE_CATEGORY in VARCHAR2,
306 X_ATTRIBUTE1 in VARCHAR2,
307 X_ATTRIBUTE2 in VARCHAR2,
308 X_ATTRIBUTE3 in VARCHAR2,
309 X_ATTRIBUTE4 in VARCHAR2,
310 X_ATTRIBUTE5 in VARCHAR2,
311 X_ATTRIBUTE6 in VARCHAR2,
312 X_ATTRIBUTE7 in VARCHAR2,
313 X_ATTRIBUTE8 in VARCHAR2,
314 X_ATTRIBUTE9 in VARCHAR2,
315 X_ATTRIBUTE10 in VARCHAR2,
316 X_ATTRIBUTE11 in VARCHAR2,
317 X_ATTRIBUTE14 in VARCHAR2,
318 X_ATTRIBUTE15 in VARCHAR2,
319 X_PARENT_STRUCTURE_TYPE_ID in NUMBER,
320 X_ENABLE_ATTACHMENTS_FLAG in VARCHAR2,
321 X_DISPLAY_NAME in VARCHAR2,
322 X_DESCRIPTION in VARCHAR2,
323 X_CREATION_DATE in DATE,
324 X_CREATED_BY in NUMBER,
325 X_LAST_UPDATE_DATE in DATE,
326 X_LAST_UPDATED_BY in NUMBER,
327 X_LAST_UPDATE_LOGIN in NUMBER,
328 X_ENABLE_UNIMPLEMENTED_BOMS IN VARCHAR2,
329 X_ALLOW_SUBTYPES IN VARCHAR2
330
331 ) is
332 cursor C is select ROWID from BOM_STRUCTURE_TYPES_B
333 where STRUCTURE_TYPE_ID = X_STRUCTURE_TYPE_ID
334 ;
335 begin
336 insert into BOM_STRUCTURE_TYPES_B (
337 ATTRIBUTE12,
338 ATTRIBUTE13,
339 DISABLE_DATE,
340 STRUCTURE_TYPE_ID,
341 STRUCTURE_TYPE_NAME,
342 ITEM_CATALOG_GROUP_ID,
343 EFFECTIVE_DATE,
344 STRUCTURE_CREATION_ALLOWED,
345 ATTRIBUTE_CATEGORY,
346 ATTRIBUTE1,
347 ATTRIBUTE2,
348 ATTRIBUTE3,
349 ATTRIBUTE4,
350 ATTRIBUTE5,
351 ATTRIBUTE6,
352 ATTRIBUTE7,
353 ATTRIBUTE8,
354 ATTRIBUTE9,
355 ATTRIBUTE10,
356 ATTRIBUTE11,
357 ATTRIBUTE14,
358 ATTRIBUTE15,
359 PARENT_STRUCTURE_TYPE_ID,
360 ENABLE_ATTACHMENTS_FLAG,
361 CREATION_DATE,
362 CREATED_BY,
363 LAST_UPDATE_DATE,
364 LAST_UPDATED_BY,
365 LAST_UPDATE_LOGIN,
366 ENABLE_UNIMPLEMENTED_BOMS,
367 ALLOW_SUBTYPES
368 ) values (
369 X_ATTRIBUTE12,
370 X_ATTRIBUTE13,
371 X_DISABLE_DATE,
372 X_STRUCTURE_TYPE_ID,
373 X_STRUCTURE_TYPE_NAME,
374 X_ITEM_CATALOG_GROUP_ID,
375 X_EFFECTIVE_DATE,
376 X_STRUCTURE_CREATION_ALLOWED,
377 X_ATTRIBUTE_CATEGORY,
378 X_ATTRIBUTE1,
379 X_ATTRIBUTE2,
380 X_ATTRIBUTE3,
381 X_ATTRIBUTE4,
382 X_ATTRIBUTE5,
383 X_ATTRIBUTE6,
384 X_ATTRIBUTE7,
385 X_ATTRIBUTE8,
386 X_ATTRIBUTE9,
387 X_ATTRIBUTE10,
388 X_ATTRIBUTE11,
389 X_ATTRIBUTE14,
390 X_ATTRIBUTE15,
391 X_PARENT_STRUCTURE_TYPE_ID,
395 X_LAST_UPDATE_DATE,
392 X_ENABLE_ATTACHMENTS_FLAG,
393 X_CREATION_DATE,
394 X_CREATED_BY,
396 X_LAST_UPDATED_BY,
397 X_LAST_UPDATE_LOGIN,
398 X_ENABLE_UNIMPLEMENTED_BOMS,
399 X_ALLOW_SUBTYPES
400 );
401
402 insert into BOM_STRUCTURE_TYPES_TL (
403 LAST_UPDATE_LOGIN,
404 STRUCTURE_TYPE_ID,
405 CREATED_BY,
406 LAST_UPDATED_BY,
407 CREATION_DATE,
408 LAST_UPDATE_DATE,
409 DISPLAY_NAME,
410 DESCRIPTION,
411 LANGUAGE,
412 SOURCE_LANG
413 ) select
414 X_LAST_UPDATE_LOGIN,
415 X_STRUCTURE_TYPE_ID,
416 X_CREATED_BY,
417 X_LAST_UPDATED_BY,
418 X_CREATION_DATE,
419 X_LAST_UPDATE_DATE,
420 X_DISPLAY_NAME,
421 X_DESCRIPTION,
422 L.LANGUAGE_CODE,
423 userenv('LANG')
424 from FND_LANGUAGES L
425 where L.INSTALLED_FLAG in ('I', 'B')
426 and not exists
427 (select NULL
428 from BOM_STRUCTURE_TYPES_TL T
429 where T.STRUCTURE_TYPE_ID = X_STRUCTURE_TYPE_ID
430 and T.LANGUAGE = L.LANGUAGE_CODE);
431
432 open c;
433 fetch c into X_ROWID;
434 if (c%notfound) then
435 close c;
436 raise no_data_found;
437 end if;
438 close c;
439
440 end INSERT_ROW;
441
442 procedure LOCK_ROW (
443 X_STRUCTURE_TYPE_ID in NUMBER,
444 X_ATTRIBUTE12 in VARCHAR2,
445 X_ATTRIBUTE13 in VARCHAR2,
446 X_DISABLE_DATE in DATE,
447 X_STRUCTURE_TYPE_NAME in VARCHAR2,
448 X_ITEM_CATALOG_GROUP_ID in NUMBER,
449 X_EFFECTIVE_DATE in DATE,
450 X_STRUCTURE_CREATION_ALLOWED in VARCHAR2,
451 X_ATTRIBUTE_CATEGORY in VARCHAR2,
452 X_ATTRIBUTE1 in VARCHAR2,
453 X_ATTRIBUTE2 in VARCHAR2,
454 X_ATTRIBUTE3 in VARCHAR2,
455 X_ATTRIBUTE4 in VARCHAR2,
456 X_ATTRIBUTE5 in VARCHAR2,
457 X_ATTRIBUTE6 in VARCHAR2,
458 X_ATTRIBUTE7 in VARCHAR2,
459 X_ATTRIBUTE8 in VARCHAR2,
460 X_ATTRIBUTE9 in VARCHAR2,
461 X_ATTRIBUTE10 in VARCHAR2,
462 X_ATTRIBUTE11 in VARCHAR2,
463 X_ATTRIBUTE14 in VARCHAR2,
464 X_ATTRIBUTE15 in VARCHAR2,
465 X_PARENT_STRUCTURE_TYPE_ID in NUMBER,
466 X_ENABLE_ATTACHMENTS_FLAG in VARCHAR2,
467 X_DISPLAY_NAME in VARCHAR2,
468 X_DESCRIPTION in VARCHAR2,
469 X_ENABLE_UNIMPLEMENTED_BOMS in VARCHAR2,
470 X_ALLOW_SUBTYPES in VARCHAR2
471 ) is
472 cursor c is select
473 ATTRIBUTE12,
474 ATTRIBUTE13,
475 DISABLE_DATE,
476 STRUCTURE_TYPE_NAME,
477 ITEM_CATALOG_GROUP_ID,
478 EFFECTIVE_DATE,
479 STRUCTURE_CREATION_ALLOWED,
480 ATTRIBUTE_CATEGORY,
481 ATTRIBUTE1,
482 ATTRIBUTE2,
483 ATTRIBUTE3,
484 ATTRIBUTE4,
485 ATTRIBUTE5,
486 ATTRIBUTE6,
487 ATTRIBUTE7,
488 ATTRIBUTE8,
489 ATTRIBUTE9,
490 ATTRIBUTE10,
491 ATTRIBUTE11,
492 ATTRIBUTE14,
493 ATTRIBUTE15,
494 PARENT_STRUCTURE_TYPE_ID,
495 ENABLE_ATTACHMENTS_FLAG,
496 ENABLE_UNIMPLEMENTED_BOMS,
497 ALLOW_SUBTYPES
498 from BOM_STRUCTURE_TYPES_B
499 where STRUCTURE_TYPE_ID = X_STRUCTURE_TYPE_ID
500 for update of STRUCTURE_TYPE_ID nowait;
501 recinfo c%rowtype;
502
503 cursor c1 is select
504 DISPLAY_NAME,
505 DESCRIPTION,
506 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
507 from BOM_STRUCTURE_TYPES_TL
508 where STRUCTURE_TYPE_ID = X_STRUCTURE_TYPE_ID
509 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
510 for update of STRUCTURE_TYPE_ID nowait;
511 begin
512 open c;
513 fetch c into recinfo;
514 if (c%notfound) then
515 close c;
516 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
517 app_exception.raise_exception;
518 end if;
519 close c;
520 if ( ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
521 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
522 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
523 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
524 AND ((recinfo.DISABLE_DATE = X_DISABLE_DATE)
525 OR ((recinfo.DISABLE_DATE is null) AND (X_DISABLE_DATE is null)))
526 AND (recinfo.STRUCTURE_TYPE_NAME = X_STRUCTURE_TYPE_NAME)
527 AND ((recinfo.ITEM_CATALOG_GROUP_ID = X_ITEM_CATALOG_GROUP_ID)
528 OR ((recinfo.ITEM_CATALOG_GROUP_ID is null) AND (X_ITEM_CATALOG_GROUP_ID is null)))
529 AND (recinfo.EFFECTIVE_DATE = X_EFFECTIVE_DATE)
530 AND (recinfo.STRUCTURE_CREATION_ALLOWED = X_STRUCTURE_CREATION_ALLOWED)
531 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
532 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
533 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
534 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
535 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
536 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
537 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
538 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
539 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
540 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
541 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
542 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
543 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
544 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
545 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
546 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
547 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
551 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
548 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
549 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
550 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
552 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
553 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
554 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
555 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
556 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
557 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
558 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
559 AND ((recinfo.PARENT_STRUCTURE_TYPE_ID = X_PARENT_STRUCTURE_TYPE_ID)
560 OR ((recinfo.PARENT_STRUCTURE_TYPE_ID is null) AND (X_PARENT_STRUCTURE_TYPE_ID is null)))
561 AND ((recinfo.ENABLE_ATTACHMENTS_FLAG = X_ENABLE_ATTACHMENTS_FLAG)
562 OR ((recinfo.ENABLE_ATTACHMENTS_FLAG is null) AND (X_ENABLE_ATTACHMENTS_FLAG is null)))
563 AND ((recinfo.ENABLE_UNIMPLEMENTED_BOMS = X_ENABLE_UNIMPLEMENTED_BOMS)
564 OR ((recinfo.ENABLE_UNIMPLEMENTED_BOMS is null) AND (X_ENABLE_UNIMPLEMENTED_BOMS is null)))
565 AND ((recinfo.ALLOW_SUBTYPES = X_ALLOW_SUBTYPES)
566 OR ((recinfo.ALLOW_SUBTYPES is null) AND (X_ALLOW_SUBTYPES is null)))
567
568 ) then
569 null;
570 else
571 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
572 app_exception.raise_exception;
573 end if;
574
575 for tlinfo in c1 loop
576 if (tlinfo.BASELANG = 'Y') then
577 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
578 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
579 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
580 ) then
581 null;
582 else
583 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
584 app_exception.raise_exception;
585 end if;
586 end if;
587 end loop;
588 return;
589 end LOCK_ROW;
590
591 procedure UPDATE_ROW (
592 X_STRUCTURE_TYPE_ID in NUMBER,
593 X_ATTRIBUTE12 in VARCHAR2,
594 X_ATTRIBUTE13 in VARCHAR2,
595 X_DISABLE_DATE in DATE,
596 X_STRUCTURE_TYPE_NAME in VARCHAR2,
597 X_ITEM_CATALOG_GROUP_ID in NUMBER,
598 X_EFFECTIVE_DATE in DATE,
599 X_STRUCTURE_CREATION_ALLOWED in VARCHAR2,
600 X_ATTRIBUTE_CATEGORY in VARCHAR2,
601 X_ATTRIBUTE1 in VARCHAR2,
602 X_ATTRIBUTE2 in VARCHAR2,
603 X_ATTRIBUTE3 in VARCHAR2,
604 X_ATTRIBUTE4 in VARCHAR2,
605 X_ATTRIBUTE5 in VARCHAR2,
606 X_ATTRIBUTE6 in VARCHAR2,
607 X_ATTRIBUTE7 in VARCHAR2,
608 X_ATTRIBUTE8 in VARCHAR2,
609 X_ATTRIBUTE9 in VARCHAR2,
610 X_ATTRIBUTE10 in VARCHAR2,
611 X_ATTRIBUTE11 in VARCHAR2,
612 X_ATTRIBUTE14 in VARCHAR2,
613 X_ATTRIBUTE15 in VARCHAR2,
614 X_PARENT_STRUCTURE_TYPE_ID in NUMBER,
615 X_ENABLE_ATTACHMENTS_FLAG in VARCHAR2,
616 X_DISPLAY_NAME in VARCHAR2,
617 X_DESCRIPTION in VARCHAR2,
618 X_LAST_UPDATE_DATE in DATE,
619 X_LAST_UPDATED_BY in NUMBER,
620 X_LAST_UPDATE_LOGIN in NUMBER,
621 X_ENABLE_UNIMPLEMENTED_BOMS in VARCHAR2,
622 X_ALLOW_SUBTYPES in VARCHAR2
623 ) is
624 begin
625 update BOM_STRUCTURE_TYPES_B set
626 ATTRIBUTE12 = X_ATTRIBUTE12,
627 ATTRIBUTE13 = X_ATTRIBUTE13,
628 DISABLE_DATE = X_DISABLE_DATE,
629 STRUCTURE_TYPE_NAME = X_STRUCTURE_TYPE_NAME,
630 ITEM_CATALOG_GROUP_ID = X_ITEM_CATALOG_GROUP_ID,
631 EFFECTIVE_DATE = X_EFFECTIVE_DATE,
632 STRUCTURE_CREATION_ALLOWED = X_STRUCTURE_CREATION_ALLOWED,
633 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
634 ATTRIBUTE1 = X_ATTRIBUTE1,
635 ATTRIBUTE2 = X_ATTRIBUTE2,
636 ATTRIBUTE3 = X_ATTRIBUTE3,
637 ATTRIBUTE4 = X_ATTRIBUTE4,
638 ATTRIBUTE5 = X_ATTRIBUTE5,
639 ATTRIBUTE6 = X_ATTRIBUTE6,
640 ATTRIBUTE7 = X_ATTRIBUTE7,
641 ATTRIBUTE8 = X_ATTRIBUTE8,
642 ATTRIBUTE9 = X_ATTRIBUTE9,
643 ATTRIBUTE10 = X_ATTRIBUTE10,
644 ATTRIBUTE11 = X_ATTRIBUTE11,
645 ATTRIBUTE14 = X_ATTRIBUTE14,
646 ATTRIBUTE15 = X_ATTRIBUTE15,
647 PARENT_STRUCTURE_TYPE_ID = X_PARENT_STRUCTURE_TYPE_ID,
648 ENABLE_ATTACHMENTS_FLAG = X_ENABLE_ATTACHMENTS_FLAG,
649 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
650 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
651 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
652 ENABLE_UNIMPLEMENTED_BOMS = X_ENABLE_UNIMPLEMENTED_BOMS,
653 ALLOW_SUBTYPES = X_ALLOW_SUBTYPES
654 where STRUCTURE_TYPE_ID = X_STRUCTURE_TYPE_ID;
655
656 if (sql%notfound) then
657 raise no_data_found;
658 end if;
659
660 update BOM_STRUCTURE_TYPES_TL set
661 DISPLAY_NAME = X_DISPLAY_NAME,
662 DESCRIPTION = X_DESCRIPTION,
663 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
664 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
665 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
666 SOURCE_LANG = userenv('LANG')
667 where STRUCTURE_TYPE_ID = X_STRUCTURE_TYPE_ID
668 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
669
670 if (sql%notfound) then
671 raise no_data_found;
672 end if;
673 end UPDATE_ROW;
674
675 procedure DELETE_ROW (
676 X_STRUCTURE_TYPE_ID in NUMBER
677 ) is
678 begin
679 delete from BOM_STRUCTURE_TYPES_TL
680 where STRUCTURE_TYPE_ID = X_STRUCTURE_TYPE_ID;
681
682 if (sql%notfound) then
683 raise no_data_found;
684 end if;
685
686 delete from BOM_STRUCTURE_TYPES_B
687 where STRUCTURE_TYPE_ID = X_STRUCTURE_TYPE_ID;
688
689 if (sql%notfound) then
690 raise no_data_found;
691 end if;
692 end DELETE_ROW;
693
694 procedure ADD_LANGUAGE
695 is
696 begin
700 from BOM_STRUCTURE_TYPES_B B
697 delete from BOM_STRUCTURE_TYPES_TL T
698 where not exists
699 (select NULL
701 where B.STRUCTURE_TYPE_ID = T.STRUCTURE_TYPE_ID
702 );
703
704 update BOM_STRUCTURE_TYPES_TL T set (
705 DISPLAY_NAME,
706 DESCRIPTION
707 ) = (select
708 B.DISPLAY_NAME,
709 B.DESCRIPTION
710 from BOM_STRUCTURE_TYPES_TL B
711 where B.STRUCTURE_TYPE_ID = T.STRUCTURE_TYPE_ID
712 and B.LANGUAGE = T.SOURCE_LANG)
713 where (
714 T.STRUCTURE_TYPE_ID,
715 T.LANGUAGE
716 ) in (select
717 SUBT.STRUCTURE_TYPE_ID,
718 SUBT.LANGUAGE
719 from BOM_STRUCTURE_TYPES_TL SUBB, BOM_STRUCTURE_TYPES_TL SUBT
720 where SUBB.STRUCTURE_TYPE_ID = SUBT.STRUCTURE_TYPE_ID
721 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
722 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
723 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
724 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
725 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
726 ));
727
728 insert into BOM_STRUCTURE_TYPES_TL (
729 CREATED_BY,
730 LAST_UPDATED_BY,
731 CREATION_DATE,
732 LAST_UPDATE_DATE,
733 DISPLAY_NAME,
734 DESCRIPTION,
735 LAST_UPDATE_LOGIN,
736 STRUCTURE_TYPE_ID,
737 LANGUAGE,
738 SOURCE_LANG
739 ) select
740 B.CREATED_BY,
741 B.LAST_UPDATED_BY,
742 B.CREATION_DATE,
743 B.LAST_UPDATE_DATE,
744 B.DISPLAY_NAME,
745 B.DESCRIPTION,
746 B.LAST_UPDATE_LOGIN,
747 B.STRUCTURE_TYPE_ID,
748 L.LANGUAGE_CODE,
749 B.SOURCE_LANG
750 from BOM_STRUCTURE_TYPES_TL B, FND_LANGUAGES L
751 where L.INSTALLED_FLAG in ('I', 'B')
752 and B.LANGUAGE = userenv('LANG')
753 and not exists
754 (select NULL
755 from BOM_STRUCTURE_TYPES_TL T
756 where T.STRUCTURE_TYPE_ID = B.STRUCTURE_TYPE_ID
757 and T.LANGUAGE = L.LANGUAGE_CODE);
758 end ADD_LANGUAGE;
759
760 -- --------------------------------
761 PROCEDURE Check_If_Connected(
762 p_parent_structure_type_id IN NUMBER,
763 p_structure_type_id IN NUMBER,
764 -- p_parent_structure_type_id_new IN NUMBER,
765 x_return_status OUT NOCOPY VARCHAR2
766 ) IS
767 CURSOR c_tree_hierarchy(c_p_parent_structure_type_id IN NUMBER) IS
768 /* SELECT structure_type_id
769 FROM bom_structure_types_b
770 CONNECT BY PRIOR structure_type_id = parent_structure_type_id
771 START WITH parent_structure_Type_id = c_p_parent_structure_type_id ;
772 */
773 SELECT structure_type_id
774 FROM bom_structure_types_b
775 CONNECT BY PRIOR parent_structure_type_id = structure_type_id
776 START WITH structure_type_id = c_p_parent_structure_type_id;
777 l_structure_type_id NUMBER ;
778 l_struct_type_id_orig NUMBER;
779 BEGIN
780
781
782 SELECT parent_structure_type_id INTO l_struct_type_id_orig
783 FROM bom_structure_types_b
784 WHERE structure_type_id = p_structure_type_id;
785
786 IF l_struct_type_id_orig IS null THEN
787 x_return_status := 'T'; -- If parent structure type id is null
788 RETURN;
789 END IF;
790
791 IF p_parent_structure_type_id = l_struct_type_id_orig THEN
792 -- Occurs in Edit Mode
793 x_return_status := 'T';
794 RETURN;
795 END IF;
796
797 --dbms_output.put_line('l struct type id '||to_char(l_struct_type_id_orig));
798
799 OPEN c_tree_hierarchy(l_struct_type_id_orig);
800 LOOP
801 FETCH c_tree_hierarchy INTO l_structure_type_id;
802 EXIT WHEN c_tree_hierarchy%NOTFOUND;
803 -- Bug : 2991692
804 -- Changed p_structure_type_id -> p_parent_structure_type_id
805 IF l_structure_type_id = p_parent_structure_type_id THEN
806 CLOSE c_tree_hierarchy;
807 x_return_status := 'T';
808 return;
809 END IF;
810 END LOOP;
811 CLOSE c_tree_hierarchy;
812 x_return_status := 'F';
813 EXCEPTION
814 WHEN NO_DATA_FOUND
815 THEN
816 x_return_status := 'T';
817 END Check_If_Connected;
818 -- -------------- END OF CODE FOR TABLE HANDLERS FOR BOM_STRUCTURE_TYPES_VL --------------
819
820 END BOM_STRUCTURE_TYPES_PKG;