[Home] [Help]
PACKAGE BODY: APPS.BOM_ALTERNATE_DESIGNATORS_PKG
Source
4 -- Global Variables and Constants --
1 PACKAGE BODY BOM_ALTERNATE_DESIGNATORS_PKG as
2 /* $Header: bompbadb.pls 120.6 2007/03/29 05:41:21 dikrishn ship $ */
3 ------------------------------------
5 ------------------------------------
6
7 g_pkg_name CONSTANT VARCHAR2(30) := 'BOM_ALTERNATE_DESIGNATORS_PKG';
8 g_current_user_id NUMBER := FND_GLOBAL.User_Id;
9 g_current_login_id NUMBER := FND_GLOBAL.Login_Id;
10
11 TYPE Bom_Alt_Desig_Rec IS RECORD
12 ( -- Columns from Bom_Alternate_Designators table
13 Alternate_Designator_Code VARCHAR2(10)
14 , Organization_id NUMBER
15 , LAST_UPDATE_DATE DATE
16 , LAST_UPDATED_BY NUMBER
17 , CREATION_DATE DATE
18 , CREATED_BY NUMBER
19 , LAST_UPDATE_LOGIN NUMBER
20 , DESCRIPTION VARCHAR2(240)
21 , DISABLE_DATE DATE
22 , ATTRIBUTE_CATEGORY VARCHAR2(30)
23 , ATTRIBUTE1 VARCHAR2(150)
24 , ATTRIBUTE2 VARCHAR2(150)
25 , ATTRIBUTE3 VARCHAR2(150)
26 , ATTRIBUTE4 VARCHAR2(150)
27 , ATTRIBUTE5 VARCHAR2(150)
28 , ATTRIBUTE6 VARCHAR2(150)
32 , ATTRIBUTE10 VARCHAR2(150)
29 , ATTRIBUTE7 VARCHAR2(150)
30 , ATTRIBUTE8 VARCHAR2(150)
31 , ATTRIBUTE9 VARCHAR2(150)
33 , ATTRIBUTE11 VARCHAR2(150)
34 , ATTRIBUTE12 VARCHAR2(150)
35 , ATTRIBUTE13 VARCHAR2(150)
36 , ATTRIBUTE14 VARCHAR2(150)
37 , ATTRIBUTE15 VARCHAR2(150)
38 , REQUEST_ID NUMBER
39 , PROGRAM_APPLICATION_ID NUMBER
40 , PROGRAM_ID NUMBER
41 , PROGRAM_UPDATE_DATE DATE
42 , STRUCTURE_TYPE_ID NUMBER
43 , IS_PREFERRED VARCHAR2(1)
44 --- Extra attributes added for internal usage
45 , DISPLAY_NAME VARCHAR2(80)
46 , Alt_Desig_Code_Old VARCHAR2(10)
47 , api_version NUMBER
48 );
49
50 --------------------------
51 -- Private Package APIs --
52 --------------------------
53
54 Function Get_Preferred_Name (structure_type_id Number, alt_des_code varchar2) return VARCHAR2;
55 PROCEDURE Insert_Row (p_alt_desig_rec IN Bom_Alt_Desig_Rec
56 ,x_return_status OUT NOCOPY VARCHAR2
57 ,x_errorcode OUT NOCOPY NUMBER
58 ,x_msg_count OUT NOCOPY NUMBER
59 ,x_msg_data OUT NOCOPY VARCHAR2
60 );
61
62 PROCEDURE Update_Row (p_alt_desig_rec IN Bom_Alt_Desig_Rec
63 ,x_return_status OUT NOCOPY VARCHAR2
64 ,x_errorcode OUT NOCOPY NUMBER
65 ,x_msg_count OUT NOCOPY NUMBER
66 ,x_msg_data OUT NOCOPY VARCHAR2
67 );
68
69
70 ----------------------------------------------------------------------
71 FUNCTION Check_Unique(X_Organization_Id NUMBER,
72 X_Alt_Desig_Code VARCHAR2) RETURN BOOLEAN IS
73 BEGIN
74 Check_Unique(X_Organization_Id, X_Alt_Desig_Code);
75 RETURN FALSE;
76 EXCEPTION
77 WHEN NO_DATA_FOUND THEN
78 RETURN TRUE;
79 END Check_Unique;
80
81
82 FUNCTION Check_References(X_Organization_Id NUMBER,
83 X_Alt_Desig_Code VARCHAR2) RETURN BOOLEAN IS
84 CURSOR c_check_bom_rtg_cost
85 IS
86 SELECT 1
87 FROM BOM_BILL_OF_MATERIALS
88 WHERE BOM_BILL_OF_MATERIALS.Alternate_Bom_Designator =
89 X_Alt_Desig_Code
90 UNION
91 SELECT 1
92 FROM BOM_OPERATIONAL_ROUTINGS
93 WHERE BOM_OPERATIONAL_ROUTINGS.ALTERNATE_ROUTING_DESIGNATOR =
94 X_Alt_Desig_Code
95 UNION
96 SELECT 1
97 FROM CST_COST_TYPES
98 WHERE CST_COST_TYPES.ALTERNATE_BOM_DESIGNATOR =
99 X_Alt_Desig_Code;
100 cur_bom_rtg_cost c_check_bom_rtg_cost%ROWTYPE;
101 BEGIN
102 IF X_Organization_Id IS NOT NULL THEN
103 Check_References(X_Organization_Id, X_Alt_Desig_Code);
104 RETURN FALSE;
105 ELSIF X_Organization_Id IS NULL THEN
106 OPEN c_check_bom_rtg_cost;
107 FETCH c_check_bom_rtg_cost INTO cur_bom_rtg_cost;
108 IF c_check_bom_rtg_cost%NOTFOUND THEN
109 RETURN false; --TRUE;
110 END IF;
111 IF c_check_bom_rtg_cost%ISOPEN THEN
112 close c_check_bom_rtg_cost;
113 END IF;
114 RETURN TRUE; --FALSE;
115 END IF;
116 EXCEPTION
117 WHEN OTHERS THEN
118 RETURN TRUE;
119 END Check_References;
120
121 --------------------------------------------------------------------------------
122
123 PROCEDURE Check_Unique(X_Organization_Id NUMBER,
124 X_Alternate_Designator_Code VARCHAR2) IS
125 DUMMY NUMBER;
126 BEGIN
127 SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
128 (SELECT 1 FROM BOM_ALTERNATE_DESIGNATORS
129 WHERE Organization_Id = X_Organization_Id
130 AND Alternate_Designator_Code = X_Alternate_Designator_Code
131 );
132
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 FND_MESSAGE.SET_NAME('BOM', 'BOM_ALREADY_EXISTS');
136 FND_MESSAGE.SET_TOKEN('ENTITY1', 'THIS_CAP', TRUE);
137 FND_MESSAGE.SET_TOKEN('ENTITY2', 'ALTERNATE_CAP', TRUE);
138 APP_EXCEPTION.RAISE_EXCEPTION;
139 END Check_Unique;
140
141
142 PROCEDURE Check_References(X_Organization_Id NUMBER,
143 X_Alternate_Designator_Code VARCHAR2) IS
144 DUMMY NUMBER;
145 MESSAGE_NAME VARCHAR2(80);
146 BEGIN
147 SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
148 (SELECT 1 FROM BOM_BILL_OF_MATERIALS
149 WHERE BOM_BILL_OF_MATERIALS.Organization_ID = X_Organization_Id
150 AND BOM_BILL_OF_MATERIALS.Alternate_Bom_Designator =
151 X_Alternate_Designator_Code
152 );
153
154 SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
155 (SELECT 1 FROM BOM_OPERATIONAL_ROUTINGS
156 WHERE BOM_OPERATIONAL_ROUTINGS.Organization_Id = X_Organization_Id
157 AND BOM_OPERATIONAL_ROUTINGS.ALTERNATE_ROUTING_DESIGNATOR =
158 X_Alternate_Designator_Code
159 );
160
161 SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
162 (SELECT 1 FROM CST_COST_TYPES
163 WHERE CST_COST_TYPES.Organization_Id = X_Organization_Id
164 AND CST_COST_TYPES.ALTERNATE_BOM_DESIGNATOR =
165 X_Alternate_Designator_Code
166 );
167
168 EXCEPTION
169 WHEN NO_DATA_FOUND THEN
170 FND_MESSAGE.SET_NAME('BOM', 'BOM_ALT_IN_USE');
171 FND_MESSAGE.SET_TOKEN('ENTITY', X_Alternate_Designator_Code);
172 APP_EXCEPTION.RAISE_EXCEPTION;
173 END Check_References;
174
175 PROCEDURE Insert_Row ( --- not used, retaining for the moment
176 p_api_version IN NUMBER
177 ,p_alt_desig_code IN VARCHAR2
178 ,p_organization_id IN NUMBER
179 ,p_description IN VARCHAR2
180 ,p_disable_date IN DATE
181 ,p_structure_type_id IN NUMBER
182 ,x_return_status OUT NOCOPY VARCHAR2
183 ,x_errorcode OUT NOCOPY NUMBER
184 ,x_msg_count OUT NOCOPY NUMBER
185 ,x_msg_data OUT NOCOPY VARCHAR2
186 ) IS
187 BEGIN
188 Insert_Row (
189 p_api_version => p_api_version
190 ,p_alt_desig_code => p_alt_desig_code
191 ,p_organization_id => p_organization_id
192 ,p_display_name => p_alt_desig_code
193 ,p_description => p_description
194 ,p_disable_date => p_disable_date
195 ,p_structure_type_id => p_structure_type_id
196 ,p_is_preferred => null
197 ,x_return_status => x_return_status
198 ,x_errorcode => x_errorcode
199 ,x_msg_count => x_msg_count
200 ,x_msg_data => x_msg_data
201 );
202
203 END Insert_row;
204
205 ----------------------------------------------------------------------
206
207 PROCEDURE Insert_Row ( --- Called by OA Pages
208 p_api_version IN NUMBER
209 ,p_alt_desig_code IN VARCHAR2
210 ,p_organization_id IN NUMBER
211 ,p_display_name IN VARCHAR2
212 ,p_description IN VARCHAR2
213 ,p_disable_date IN DATE
214 ,p_structure_type_id IN NUMBER
215 ,p_is_preferred IN VARCHAR2
216 ,x_return_status OUT NOCOPY VARCHAR2
217 ,x_errorcode OUT NOCOPY NUMBER
218 ,x_msg_count OUT NOCOPY NUMBER
219 ,x_msg_data OUT NOCOPY VARCHAR2
220 ) IS
221 l_api_version CONSTANT NUMBER := 1.0;
222 l_Sysdate DATE := Sysdate;
223 rec Bom_Alt_Desig_Rec;
224 BEGIN
225 rec.Alternate_Designator_Code := p_alt_desig_code;
226 rec.Organization_id := p_organization_id;
227 rec.LAST_UPDATE_DATE := l_Sysdate;
228 rec.LAST_UPDATED_BY := g_current_user_id;
229 rec.CREATION_DATE := l_Sysdate;
230 rec.CREATED_BY := g_current_user_id;
231 rec.LAST_UPDATE_LOGIN := g_current_login_id;
232 rec.DESCRIPTION := p_description;
233 rec.DISABLE_DATE := p_disable_date;
234 rec.ATTRIBUTE_CATEGORY := NULL;
235 rec.ATTRIBUTE1 := NULL;
236 rec.ATTRIBUTE2 := NULL;
237 rec.ATTRIBUTE3 := NULL;
238 rec.ATTRIBUTE4 := NULL;
239 rec.ATTRIBUTE5 := NULL;
240 rec.ATTRIBUTE6 := NULL;
241 rec.ATTRIBUTE7 := NULL;
242 rec.ATTRIBUTE8 := NULL;
243 rec.ATTRIBUTE9 := NULL;
244 rec.ATTRIBUTE10 := NULL;
245 rec.ATTRIBUTE11 := NULL;
246 rec.ATTRIBUTE12 := NULL;
247 rec.ATTRIBUTE13 := NULL;
248 rec.ATTRIBUTE14 := NULL;
249 rec.ATTRIBUTE15 := NULL;
250 rec.REQUEST_ID := NULL;
251 rec.PROGRAM_APPLICATION_ID := NULL;
252 rec.PROGRAM_ID := NULL;
253 rec.PROGRAM_UPDATE_DATE := NULL;
254 rec.STRUCTURE_TYPE_ID := p_structure_type_id;
255 rec.IS_PREFERRED := p_is_preferred;
256 rec.DISPLAY_NAME := p_display_name;
257 rec.Alt_Desig_Code_Old := NULL;
261 x_return_status => x_return_status,
258 rec.api_version := l_api_version;
259
260 Insert_Row (p_alt_desig_rec => rec,
262 x_errorcode => x_errorcode,
263 x_msg_count => x_msg_count,
264 x_msg_data => x_msg_data);
265
266 END Insert_row;
267
268 ----------------------------------------------------------------------
269
270 PROCEDURE Update_Row ( --- not used, retaining for the moment
271 p_api_version IN NUMBER
272 ,p_alt_desig_code_old IN VARCHAR2
273 ,p_organization_id IN NUMBER
274 ,p_alt_desig_code_new IN VARCHAR2
275 ,p_description IN VARCHAR2
276 ,p_disable_date IN DATE
277 ,p_structure_type_id IN NUMBER
278 ,x_return_status OUT NOCOPY VARCHAR2
279 ,x_errorcode OUT NOCOPY NUMBER
280 ,x_msg_count OUT NOCOPY NUMBER
281 ,x_msg_data OUT NOCOPY VARCHAR2
282 ) IS
283 BEGIN
284
285 Update_Row (
286 p_api_version => p_api_version
287 ,p_alt_desig_code_old => p_alt_desig_code_old
288 ,p_organization_id => p_organization_id
289 ,p_alt_desig_code_new => p_alt_desig_code_new
290 ,p_display_name_new => p_alt_desig_code_new
291 ,p_description => p_description
292 ,p_disable_date => p_disable_date
293 ,p_structure_type_id => p_structure_type_id
294 ,p_is_preferred => null
295 ,x_return_status => x_return_status
296 ,x_errorcode => x_errorcode
297 ,x_msg_count => x_msg_count
298 ,x_msg_data => x_msg_data
299 );
300 END Update_Row;
301
302 ----------------------------------------------------------------------
303
304 PROCEDURE Update_Row ( --- Called by OA Pages
305 p_api_version IN NUMBER
306 ,p_alt_desig_code_old IN VARCHAR2
307 ,p_organization_id IN NUMBER
308 ,p_alt_desig_code_new IN VARCHAR2
309 ,p_display_name_new IN VARCHAR2
310 ,p_description IN VARCHAR2
311 ,p_disable_date IN DATE
312 ,p_structure_type_id IN NUMBER
313 ,p_is_preferred IN VARCHAR2
314 ,x_return_status OUT NOCOPY VARCHAR2
315 ,x_errorcode OUT NOCOPY NUMBER
316 ,x_msg_count OUT NOCOPY NUMBER
317 ,x_msg_data OUT NOCOPY VARCHAR2
318 ) IS
319 l_api_version CONSTANT NUMBER := 1.0;
320 l_Sysdate DATE := Sysdate;
321 rec Bom_Alt_Desig_Rec;
322 BEGIN
323
324 rec.Alternate_Designator_Code := p_alt_desig_code_new;
325 rec.Organization_id := p_organization_id;
326 rec.LAST_UPDATE_DATE := l_Sysdate;
327 rec.LAST_UPDATED_BY := g_current_user_id;
328 rec.CREATION_DATE := NULL;
329 rec.CREATED_BY := NULL;
330 rec.LAST_UPDATE_LOGIN := g_current_login_id;
331 rec.DESCRIPTION := p_description;
332 rec.DISABLE_DATE := p_disable_date;
333 rec.ATTRIBUTE_CATEGORY := NULL;
334 rec.ATTRIBUTE1 := NULL;
335 rec.ATTRIBUTE2 := NULL;
336 rec.ATTRIBUTE3 := NULL;
337 rec.ATTRIBUTE4 := NULL;
338 rec.ATTRIBUTE5 := NULL;
339 rec.ATTRIBUTE6 := NULL;
340 rec.ATTRIBUTE7 := NULL;
341 rec.ATTRIBUTE8 := NULL;
342 rec.ATTRIBUTE9 := NULL;
343 rec.ATTRIBUTE10 := NULL;
344 rec.ATTRIBUTE11 := NULL;
345 rec.ATTRIBUTE12 := NULL;
346 rec.ATTRIBUTE13 := NULL;
347 rec.ATTRIBUTE14 := NULL;
348 rec.ATTRIBUTE15 := NULL;
349 rec.REQUEST_ID := NULL;
350 rec.PROGRAM_APPLICATION_ID := NULL;
351 rec.PROGRAM_ID := NULL;
352 rec.PROGRAM_UPDATE_DATE := NULL;
353 rec.STRUCTURE_TYPE_ID := p_structure_type_id;
354 rec.IS_PREFERRED := p_is_preferred;
355 rec.DISPLAY_NAME := p_display_name_new;
356 rec.Alt_Desig_Code_Old := p_alt_desig_code_old;
357 rec.api_version := l_api_version;
358
359 Update_Row (p_alt_desig_rec => rec
360 ,x_return_status => x_return_status
361 ,x_errorcode => x_errorcode
362 ,x_msg_count => x_msg_count
363 ,x_msg_data => x_msg_data);
364
365 END Update_Row;
366
367 ----------------------------------------------------------------------
368
369 PROCEDURE Delete_Row (
370 p_api_version IN NUMBER
371 ,p_alt_desig_code IN VARCHAR2
372 ,p_from_struct_alt_page IN VARCHAR2 DEFAULT 'N'
373 ,x_return_status OUT NOCOPY VARCHAR2
374 ,x_errorcode OUT NOCOPY NUMBER
375 ,x_msg_count OUT NOCOPY NUMBER
376 ,x_msg_data OUT NOCOPY VARCHAR2
377 ) IS
378 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Alternate_In_All_Org';
379 l_api_version CONSTANT NUMBER := 1.0;
380 BEGIN
381
382 -- Standard start of API savepoint
383 SAVEPOINT Delete_Alternate_PUB;
384
385 -- Check for call compatibility
386 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
387 l_api_name, G_PKG_NAME)
388 THEN
389 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
393 -- Added for Bug Fix : 3045566
390 END IF;
391
392 IF NOT Check_References(NULL, p_alt_desig_code) THEN
394 IF p_from_struct_alt_page = 'N' THEN
395 DELETE FROM BOM_ALTERNATE_DESIGNATORS
396 WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code;
397
398 DELETE FROM BOM_ALTERNATE_DESIGNATORS_TL
399 WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code;
400
401 ELSE
402 UPDATE BOM_ALTERNATE_DESIGNATORS SET structure_type_id =
403 (SELECT structure_type_id
404 FROM bom_structure_types_b
405 WHERE parent_structure_type_id IS NULL)
406 WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code ;
407 END IF;
408
409 -- End of bug fix
410
411 x_return_status := FND_API.G_RET_STS_SUCCESS;
412 ELSE
413 FND_MESSAGE.SET_NAME('BOM', 'BOM_ALT_IN_USE');
414 FND_MESSAGE.SET_TOKEN('ENTITY', p_alt_desig_code);
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
416 x_msg_data := FND_MESSAGE.GET;
417 END IF;
418
419 EXCEPTION
420 WHEN OTHERS THEN
421 ROLLBACK TO Delete_Alternate_PUB;
422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
423 -- x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
424 x_msg_data := FND_MESSAGE.GET;
425
426 END Delete_Row;
427
428 ----------------------------------------------------------------------
429
430 PROCEDURE Delete_Row (
431 p_api_version IN NUMBER
432 ,p_alt_desig_code IN VARCHAR2
433 ,p_organization_id IN NUMBER
434 ,x_return_status OUT NOCOPY VARCHAR2
435 ,x_errorcode OUT NOCOPY NUMBER
436 ,x_msg_count OUT NOCOPY NUMBER
437 ,x_msg_data OUT NOCOPY VARCHAR2
438 ) IS
439 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Alternate_For_Org';
440 l_api_version CONSTANT NUMBER := 1.0;
441 BEGIN
442
443 -- Standard start of API savepoint
444 SAVEPOINT Delete_Alternate_PUB;
445
446 -- Check for call compatibility
447 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
448 l_api_name, G_PKG_NAME) THEN
449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
450 END IF;
451
452 IF NOT Check_References(p_organization_id, p_alt_desig_code) THEN
453 DELETE FROM BOM_ALTERNATE_DESIGNATORS
454 WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code
455 AND ORGANIZATION_ID = p_organization_id;
456
457 DELETE FROM BOM_ALTERNATE_DESIGNATORS_TL
458 WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code
459 and ORGANIZATION_ID = p_organization_id;
460
461 commit;
462 x_return_status := FND_API.G_RET_STS_SUCCESS;
463 ELSE
464 FND_MESSAGE.SET_NAME('BOM', 'BOM_ALT_IN_USE');
465 FND_MESSAGE.SET_TOKEN('ENTITY', p_alt_desig_code);
466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
467 x_msg_data := FND_MESSAGE.GET;
468 END IF;
469
470 EXCEPTION
471 WHEN OTHERS THEN
472 ROLLBACK TO Delete_Alternate_PUB;
473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
474 -- x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
475 x_msg_data := FND_MESSAGE.GET;
476
477 END Delete_Row;
478
479 PROCEDURE Create_Association(
480 p_api_version IN NUMBER
481 -- ,p_organization_id IN NUMBER
482 ,p_alternate_designator_code IN VARCHAR2
483 ,p_structure_type_id IN NUMBER
484 ,x_return_status OUT NOCOPY VARCHAR2
485 ,x_errorcode OUT NOCOPY NUMBER
486 ,x_msg_count OUT NOCOPY NUMBER
487 ,x_msg_data OUT NOCOPY VARCHAR2
488 ) IS
489 l_api_name CONSTANT VARCHAR2(30) := 'Create_Association';
490 l_api_version CONSTANT NUMBER := 1.0;
491 BEGIN
492 -- Standard start of API Savepoint
493 SAVEPOINT Create_Association;
494
495 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
496 l_api_name, G_PKG_NAME) THEN
497 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
498 END IF;
499
500 UPDATE BOM_ALTERNATE_DESIGNATORS
501 SET structure_type_id = p_structure_type_id
502 WHERE alternate_designator_code = p_alternate_designator_code;
503 -- AND organization_id = p_organization_id;
504 x_return_status := FND_API.G_RET_STS_SUCCESS;
505
506 EXCEPTION
507 WHEN OTHERS
508 THEN
509 ROLLBACK TO Create_Association;
510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
511 FND_MESSAGE.SET_NAME('BOM', 'BOM_UPDATE_FAILED');
512 x_msg_data := FND_MESSAGE.GET;
513 -- x_msg_data := 'Executing - '||G_PKG_NAME ||'.'||l_api_name||' '||SQLERRM;
514
515 END Create_Association;
516 -- -------------------------------
517 -- Description : Added the following wrapper function
518 -- for the Original Check_References Function
519 -- since due to limitation of CallableStatement not
520 -- able to return values of BOOLEAN type
521 -- Bug No : 2826480
522 -- ------
523 FUNCTION Check_References_wrapper(X_Organization_Id NUMBER,
524 X_Alternate_Designator_Code VARCHAR2)
525 RETURN VARCHAR2 IS
526 l_result BOOLEAN;
527 BEGIN
528 l_result := Check_References(x_Organization_id => null,
529 X_Alt_Desig_Code => x_Alternate_Designator_code);
530 IF l_result THEN
531 RETURN 'T';
535 END Check_References_wrapper;
532 ELSE
533 RETURN 'F';
534 END IF;
536
537 -- ----------------------------
538
539 PROCEDURE ADD_LANGUAGE
540 IS
541 BEGIN
542 DELETE FROM BOM_ALTERNATE_DESIGNATORS_TL T
543 WHERE not exists
544 (SELECT NULL
545 FROM BOM_ALTERNATE_DESIGNATORS B
546 WHERE B.ALTERNATE_DESIGNATOR_CODE = T.ALTERNATE_DESIGNATOR_CODE
547 and B.ORGANIZATION_ID = T.ORGANIZATION_ID
548 );
549
550 UPDATE BOM_ALTERNATE_DESIGNATORS_TL T SET (
551 DISPLAY_NAME,
552 DESCRIPTION
553 ) = (SELECT
554 B.DISPLAY_NAME,
555 B.DESCRIPTION
556 FROM BOM_ALTERNATE_DESIGNATORS_TL B
557 WHERE B.ALTERNATE_DESIGNATOR_CODE = T.ALTERNATE_DESIGNATOR_CODE
558 AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
559 AND B.LANGUAGE = T.SOURCE_LANG)
560 WHERE (
561 T.ALTERNATE_DESIGNATOR_CODE,
562 T.ORGANIZATION_ID,
563 T.LANGUAGE
564 ) IN (SELECT
565 SUBT.ALTERNATE_DESIGNATOR_CODE,
566 SUBT.ORGANIZATION_ID,
567 SUBT.LANGUAGE
568 FROM BOM_ALTERNATE_DESIGNATORS_TL SUBB, BOM_ALTERNATE_DESIGNATORS_TL SUBT
569 WHERE SUBB.ALTERNATE_DESIGNATOR_CODE = SUBT.ALTERNATE_DESIGNATOR_CODE
570 AND SUBB.ORGANIZATION_ID = SUBT.ORGANIZATION_ID
571 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
572 AND (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
573 OR (SUBB.DISPLAY_NAME IS NULL AND SUBT.DISPLAY_NAME IS NOT NULL)
574 OR (SUBB.DISPLAY_NAME IS NOT NULL AND SUBT.DISPLAY_NAME IS NULL)
575 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
576 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
577 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
578 ));
579
580 INSERT INTO BOM_ALTERNATE_DESIGNATORS_TL (
581 ALTERNATE_DESIGNATOR_CODE,
582 ORGANIZATION_ID,
583 DISPLAY_NAME,
584 DESCRIPTION,
585 LAST_UPDATE_DATE,
586 LAST_UPDATED_BY,
587 CREATION_DATE,
588 CREATED_BY,
589 LAST_UPDATE_LOGIN,
590 LANGUAGE,
591 SOURCE_LANG
592 ) SELECT /*+ ORDERED */
593 B.ALTERNATE_DESIGNATOR_CODE,
594 B.ORGANIZATION_ID,
595 B.DISPLAY_NAME,
596 B.DESCRIPTION,
597 B.LAST_UPDATE_DATE,
598 B.LAST_UPDATED_BY,
599 B.CREATION_DATE,
600 B.CREATED_BY,
601 B.LAST_UPDATE_LOGIN,
602 L.LANGUAGE_CODE,
603 B.SOURCE_LANG
604 FROM BOM_ALTERNATE_DESIGNATORS_TL B, FND_LANGUAGES L
605 WHERE L.INSTALLED_FLAG IN ('I', 'B')
606 AND B.LANGUAGE = USERENV('LANG')
607 AND NOT EXISTS
608 (SELECT NULL
609 FROM BOM_ALTERNATE_DESIGNATORS_TL T
610 WHERE T.ALTERNATE_DESIGNATOR_CODE = B.ALTERNATE_DESIGNATOR_CODE
611 AND T.ORGANIZATION_ID = B.ORGANIZATION_ID
612 AND T.LANGUAGE = L.LANGUAGE_CODE);
613
614 END ADD_LANGUAGE;
615
616
617 PROCEDURE Insert_Row ( --- Called by form BOMFDBAD.fmb
618 P_ALTERNATE_DESIGNATOR_CODE in VARCHAR2,
619 P_ORGANIZATION_ID in NUMBER,
620 P_STRUCTURE_TYPE_ID in NUMBER,
621 P_DISABLE_DATE in DATE,
622 P_ATTRIBUTE_CATEGORY in VARCHAR2,
623 P_ATTRIBUTE1 in VARCHAR2,
624 P_ATTRIBUTE2 in VARCHAR2,
625 P_ATTRIBUTE3 in VARCHAR2,
626 P_ATTRIBUTE4 in VARCHAR2,
627 P_ATTRIBUTE5 in VARCHAR2,
628 P_ATTRIBUTE6 in VARCHAR2,
629 P_ATTRIBUTE7 in VARCHAR2,
630 P_ATTRIBUTE8 in VARCHAR2,
631 P_ATTRIBUTE9 in VARCHAR2,
632 P_ATTRIBUTE10 in VARCHAR2,
633 P_ATTRIBUTE11 in VARCHAR2,
634 P_ATTRIBUTE12 in VARCHAR2,
635 P_ATTRIBUTE13 in VARCHAR2,
636 P_ATTRIBUTE14 in VARCHAR2,
637 P_ATTRIBUTE15 in VARCHAR2,
638 P_REQUEST_ID in NUMBER,
639 P_DISPLAY_NAME in VARCHAR2,
640 P_DESCRIPTION in VARCHAR2,
641 P_CREATION_DATE in DATE,
642 P_CREATED_BY in NUMBER,
643 P_LAST_UPDATE_DATE in DATE,
644 P_LAST_UPDATED_BY in NUMBER,
645 P_LAST_UPDATE_LOGIN in NUMBER
646 ) IS
647 l_api_version CONSTANT NUMBER := 1.0;
648 x_return_status VARCHAR2(10);
649 x_errorcode NUMBER;
650 x_msg_count NUMBER;
651 x_msg_data VARCHAR2(1000);
652 rec Bom_Alt_Desig_Rec;
653 BEGIN
654
655 rec.Alternate_Designator_Code := P_ALTERNATE_DESIGNATOR_CODE;
656 rec.Organization_id := P_ORGANIZATION_ID;
657 rec.LAST_UPDATE_DATE := P_LAST_UPDATE_DATE;
658 rec.LAST_UPDATED_BY := P_LAST_UPDATED_BY;
659 rec.CREATION_DATE := P_CREATION_DATE;
660 rec.CREATED_BY := P_CREATED_BY;
661 rec.LAST_UPDATE_LOGIN := P_LAST_UPDATE_LOGIN;
662 rec.DESCRIPTION := P_DESCRIPTION;
663 rec.DISABLE_DATE := P_DISABLE_DATE;
664 rec.ATTRIBUTE_CATEGORY := P_ATTRIBUTE_CATEGORY;
665 rec.ATTRIBUTE1 := P_ATTRIBUTE1;
666 rec.ATTRIBUTE2 := P_ATTRIBUTE2;
667 rec.ATTRIBUTE3 := P_ATTRIBUTE3;
668 rec.ATTRIBUTE4 := P_ATTRIBUTE4;
669 rec.ATTRIBUTE5 := P_ATTRIBUTE5;
670 rec.ATTRIBUTE6 := P_ATTRIBUTE6;
671 rec.ATTRIBUTE7 := P_ATTRIBUTE7;
672 rec.ATTRIBUTE8 := P_ATTRIBUTE8;
673 rec.ATTRIBUTE9 := P_ATTRIBUTE9;
674 rec.ATTRIBUTE10 := P_ATTRIBUTE10;
675 rec.ATTRIBUTE11 := P_ATTRIBUTE11;
676 rec.ATTRIBUTE12 := P_ATTRIBUTE12;
677 rec.ATTRIBUTE13 := P_ATTRIBUTE13;
678 rec.ATTRIBUTE14 := P_ATTRIBUTE14;
679 rec.ATTRIBUTE15 := P_ATTRIBUTE15;
680 rec.REQUEST_ID := P_REQUEST_ID;
681 rec.PROGRAM_APPLICATION_ID := NULL;
682 rec.PROGRAM_ID := NULL;
683 rec.PROGRAM_UPDATE_DATE := NULL;
687 rec.api_version := l_api_version;
684 rec.STRUCTURE_TYPE_ID := P_STRUCTURE_TYPE_ID;
685 rec.DISPLAY_NAME := P_DISPLAY_NAME;
686 rec.Alt_Desig_Code_Old := NULL;
688
689 Insert_Row (p_alt_desig_rec => rec,
690 x_return_status => x_return_status,
691 x_errorcode => x_errorcode,
692 x_msg_count => x_msg_count,
693 x_msg_data => x_msg_data);
694
695 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
696 -- Write exception handling specific to forms using the returned error messages
697 NULL;
698 END IF;
699 END Insert_Row;
700
701
702 PROCEDURE Insert_Row (
703 p_alt_desig_rec IN Bom_Alt_Desig_Rec
704 ,x_return_status OUT NOCOPY VARCHAR2
705 ,x_errorcode OUT NOCOPY NUMBER
706 ,x_msg_count OUT NOCOPY NUMBER
707 ,x_msg_data OUT NOCOPY VARCHAR2
708 ) IS
709 l_api_name CONSTANT VARCHAR2(30) := 'Create_Alternate';
710 l_api_version CONSTANT NUMBER := 1.0;
711 -- l_object_id NUMBER;
712 -- l_Sysdate DATE := Sysdate;
713 l_structure_type_id NUMBER;
714 old_preferred_name varchar2(10);
715
716 BEGIN
717
718 -- Standard start of API savepoint
719 SAVEPOINT Create_Alternate_PUB;
720
721 IF NOT FND_API.Compatible_API_Call (l_api_version, p_alt_desig_rec.api_version,
722 l_api_name, G_PKG_NAME)
723 THEN
724 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
725 END IF;
726 IF p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE IS NULL THEN
727 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
728 END IF;
729 IF p_alt_desig_rec.ORGANIZATION_ID IS NULL THEN
730 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
731 END IF;
732 IF p_alt_desig_rec.STRUCTURE_TYPE_ID = -1 THEN
733 l_structure_type_id := NULL;
734 ELSE
735 l_structure_type_id := p_alt_desig_rec.structure_type_id;
736 END IF;
737
738 -- when the preferred structure name is set as current structure name
739 -- and there exists another preferred structure name already
740 -- reset that value
741 old_preferred_name := Get_Preferred_Name(p_alt_desig_rec.structure_type_id, p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE);
742 if(p_alt_desig_rec.is_preferred = 'Y' and
743 old_preferred_name <> p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE)
744 then
745 update bom_alternate_designators
746 set is_preferred ='N'
747 where
748 alternate_designator_code =old_preferred_name;
749 end if;
750
751
752 IF NOT Check_Unique(p_alt_desig_rec.ORGANIZATION_ID, p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE) THEN
753 INSERT INTO BOM_ALTERNATE_DESIGNATORS
754 (
755 ALTERNATE_DESIGNATOR_CODE
756 ,ORGANIZATION_ID
757 ,DESCRIPTION
758 ,DISABLE_DATE
759 ,STRUCTURE_TYPE_ID
760 ,ATTRIBUTE_CATEGORY
761 ,ATTRIBUTE1
762 ,ATTRIBUTE2
763 ,ATTRIBUTE3
764 ,ATTRIBUTE4
765 ,ATTRIBUTE5
766 ,ATTRIBUTE6
767 ,ATTRIBUTE7
768 ,ATTRIBUTE8
769 ,ATTRIBUTE9
770 ,ATTRIBUTE10
771 ,ATTRIBUTE11
772 ,ATTRIBUTE12
773 ,ATTRIBUTE13
774 ,ATTRIBUTE14
775 ,ATTRIBUTE15
776 ,REQUEST_ID
777 ,CREATION_DATE
778 ,CREATED_BY
779 ,LAST_UPDATE_DATE
780 ,LAST_UPDATED_BY
781 ,LAST_UPDATE_LOGIN
782 ,PROGRAM_APPLICATION_ID
783 ,PROGRAM_ID
784 ,PROGRAM_UPDATE_DATE
785 , IS_PREFERRED
786 )
787 VALUES
788 (
789 p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE
790 ,p_alt_desig_rec.ORGANIZATION_ID
791 ,p_alt_desig_rec.DESCRIPTION
792 ,p_alt_desig_rec.DISABLE_DATE
793 ,l_structure_type_id
794 ,p_alt_desig_rec.ATTRIBUTE_CATEGORY
795 ,p_alt_desig_rec.ATTRIBUTE1
796 ,p_alt_desig_rec.ATTRIBUTE2
797 ,p_alt_desig_rec.ATTRIBUTE3
798 ,p_alt_desig_rec.ATTRIBUTE4
799 ,p_alt_desig_rec.ATTRIBUTE5
800 ,p_alt_desig_rec.ATTRIBUTE6
801 ,p_alt_desig_rec.ATTRIBUTE7
802 ,p_alt_desig_rec.ATTRIBUTE8
803 ,p_alt_desig_rec.ATTRIBUTE9
804 ,p_alt_desig_rec.ATTRIBUTE10
805 ,p_alt_desig_rec.ATTRIBUTE11
806 ,p_alt_desig_rec.ATTRIBUTE12
807 ,p_alt_desig_rec.ATTRIBUTE13
808 ,p_alt_desig_rec.ATTRIBUTE14
809 ,p_alt_desig_rec.ATTRIBUTE15
810 ,p_alt_desig_rec.REQUEST_ID
811 ,p_alt_desig_rec.CREATION_DATE
812 ,p_alt_desig_rec.CREATED_BY
813 ,p_alt_desig_rec.LAST_UPDATE_DATE
814 ,p_alt_desig_rec.LAST_UPDATED_BY
815 ,p_alt_desig_rec.LAST_UPDATE_LOGIN
816 ,p_alt_desig_rec.PROGRAM_APPLICATION_ID
817 ,p_alt_desig_rec.PROGRAM_ID
818 ,p_alt_desig_rec.PROGRAM_UPDATE_DATE
819 ,p_alt_desig_rec.IS_PREFERRED
820 );
821
822 --- Added for MLS enabling of Bom_Alternate_Designators table
823 insert into BOM_ALTERNATE_DESIGNATORS_TL (
824 ALTERNATE_DESIGNATOR_CODE,
825 ORGANIZATION_ID,
826 DISPLAY_NAME,
827 DESCRIPTION,
828 LAST_UPDATE_DATE,
829 LAST_UPDATED_BY,
830 CREATION_DATE,
831 CREATED_BY,
832 LAST_UPDATE_LOGIN,
833 LANGUAGE,
834 SOURCE_LANG
835 ) select
836 p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
840 p_alt_desig_rec.LAST_UPDATE_DATE,
841 p_alt_desig_rec.LAST_UPDATED_BY,
842 p_alt_desig_rec.CREATION_DATE,
843 p_alt_desig_rec.CREATED_BY,
844 p_alt_desig_rec.LAST_UPDATE_LOGIN,
845 L.LANGUAGE_CODE,
846 userenv('LANG')
847 from FND_LANGUAGES L
848 where L.INSTALLED_FLAG in ('I', 'B')
849 and not exists
850 (select NULL
851 from BOM_ALTERNATE_DESIGNATORS_TL T
852 where T.ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE
853 and T.ORGANIZATION_ID = p_alt_desig_rec.ORGANIZATION_ID
854 and T.LANGUAGE = L.LANGUAGE_CODE);
855 x_return_status := FND_API.G_RET_STS_SUCCESS;
856 END IF;
857
858 EXCEPTION
859 WHEN OTHERS THEN
860 ROLLBACK TO Create_Alternate_PUB;
861 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
862 -- x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
863 x_msg_data := FND_MESSAGE.GET;
864 END Insert_row;
865
866 procedure UPDATE_ROW ( --- Called from the form BOMFDBAD.fmb
867 P_ALTERNATE_DESIGNATOR_CODE in VARCHAR2,
868 P_ORGANIZATION_ID in NUMBER,
869 P_STRUCTURE_TYPE_ID in NUMBER,
870 P_DISABLE_DATE in DATE,
871 P_ATTRIBUTE_CATEGORY in VARCHAR2,
872 P_ATTRIBUTE1 in VARCHAR2,
873 P_ATTRIBUTE2 in VARCHAR2,
874 P_ATTRIBUTE3 in VARCHAR2,
875 P_ATTRIBUTE4 in VARCHAR2,
876 P_ATTRIBUTE5 in VARCHAR2,
877 P_ATTRIBUTE6 in VARCHAR2,
878 P_ATTRIBUTE7 in VARCHAR2,
879 P_ATTRIBUTE8 in VARCHAR2,
880 P_ATTRIBUTE9 in VARCHAR2,
881 P_ATTRIBUTE10 in VARCHAR2,
882 P_ATTRIBUTE11 in VARCHAR2,
883 P_ATTRIBUTE12 in VARCHAR2,
884 P_ATTRIBUTE13 in VARCHAR2,
885 P_ATTRIBUTE14 in VARCHAR2,
886 P_ATTRIBUTE15 in VARCHAR2,
887 P_REQUEST_ID in NUMBER,
888 P_DISPLAY_NAME in VARCHAR2,
889 P_DESCRIPTION in VARCHAR2,
890 P_LAST_UPDATE_DATE in DATE,
891 P_LAST_UPDATED_BY in NUMBER,
892 P_LAST_UPDATE_LOGIN in NUMBER
893 ) IS
894 l_api_version CONSTANT NUMBER := 1.0;
895 x_return_status VARCHAR2(1);
896 x_errorcode NUMBER;
897 x_msg_count NUMBER;
898 x_msg_data VARCHAR2(1000);
899 rec Bom_Alt_Desig_Rec;
900 BEGIN
901
902 rec.Alternate_Designator_Code := P_ALTERNATE_DESIGNATOR_CODE;
903 rec.Organization_id := P_ORGANIZATION_ID;
904 rec.LAST_UPDATE_DATE := P_LAST_UPDATE_DATE;
905 rec.LAST_UPDATED_BY := P_LAST_UPDATED_BY;
906 rec.CREATION_DATE := NULL;
907 rec.CREATED_BY := NULL;
908 rec.LAST_UPDATE_LOGIN := P_LAST_UPDATE_LOGIN;
909 rec.DESCRIPTION := P_DESCRIPTION;
910 rec.DISABLE_DATE := P_DISABLE_DATE;
911 rec.ATTRIBUTE_CATEGORY := P_ATTRIBUTE_CATEGORY;
912 rec.ATTRIBUTE1 := P_ATTRIBUTE1;
913 rec.ATTRIBUTE2 := P_ATTRIBUTE2;
914 rec.ATTRIBUTE3 := P_ATTRIBUTE3;
915 rec.ATTRIBUTE4 := P_ATTRIBUTE4;
916 rec.ATTRIBUTE5 := P_ATTRIBUTE5;
917 rec.ATTRIBUTE6 := P_ATTRIBUTE6;
918 rec.ATTRIBUTE7 := P_ATTRIBUTE7;
919 rec.ATTRIBUTE8 := P_ATTRIBUTE8;
920 rec.ATTRIBUTE9 := P_ATTRIBUTE9;
921 rec.ATTRIBUTE10 := P_ATTRIBUTE10;
922 rec.ATTRIBUTE11 := P_ATTRIBUTE11;
923 rec.ATTRIBUTE12 := P_ATTRIBUTE12;
924 rec.ATTRIBUTE13 := P_ATTRIBUTE13;
925 rec.ATTRIBUTE14 := P_ATTRIBUTE14;
926 rec.ATTRIBUTE15 := P_ATTRIBUTE15;
927 rec.REQUEST_ID := P_REQUEST_ID;
928 rec.PROGRAM_APPLICATION_ID := NULL;
929 rec.PROGRAM_ID := NULL;
930 rec.PROGRAM_UPDATE_DATE := NULL;
931 rec.STRUCTURE_TYPE_ID := P_STRUCTURE_TYPE_ID;
932 rec.DISPLAY_NAME := P_DISPLAY_NAME;
933 rec.Alt_Desig_Code_Old := P_ALTERNATE_DESIGNATOR_CODE;
934 rec.api_version := l_api_version;
935
936 Update_Row (p_alt_desig_rec => rec
937 ,x_return_status => x_return_status
938 ,x_errorcode => x_errorcode
939 ,x_msg_count => x_msg_count
940 ,x_msg_data => x_msg_data);
941
942 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
943 /* Write exception handling specific to forms using the returned error messages */
944 NULL;
945 END IF;
946 END;
947
948
949 PROCEDURE Update_Row (
950 p_alt_desig_rec IN Bom_Alt_Desig_Rec
951 ,x_return_status OUT NOCOPY VARCHAR2
952 ,x_errorcode OUT NOCOPY NUMBER
953 ,x_msg_count OUT NOCOPY NUMBER
954 ,x_msg_data OUT NOCOPY VARCHAR2
955 ) IS
956
957 l_api_name CONSTANT VARCHAR2(30) := 'Update_Alternate';
958 l_api_version CONSTANT NUMBER := 1.0;
959 old_preferred_name VARCHAR2(30);
960
961 BEGIN
962
963 -- Standard start of API savepoint
964 SAVEPOINT Update_Alternate_PUB;
965
966
967 IF NOT FND_API.Compatible_API_Call (l_api_version, p_alt_desig_rec.api_version,
968 l_api_name, G_PKG_NAME)
969 THEN
970 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
971 END IF;
972 IF p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE IS NULL THEN
973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
974 END IF;
975 IF p_alt_desig_rec.ORGANIZATION_ID IS NULL THEN
976 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
977 END IF;
978
979 -- If the current structure is set as preferred structure name
980 -- and there exists another preferred structure name for this structure type then
981 -- reset that is_preferred value for the old structure name.
982 old_preferred_name := Get_Preferred_Name(p_alt_desig_rec.structure_type_id, p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE);
983 IF(p_alt_desig_rec.is_preferred = 'Y' AND
984 old_preferred_name <> p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE)
985 THEN
986 UPDATE bom_alternate_designators
987 SET is_preferred ='N'
988 WHERE
989 structure_type_id = p_alt_desig_rec.structure_type_id
990 AND is_preferred ='Y';
991 END IF;
992
993 IF( p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.Alt_Desig_Code_Old ) THEN
994 UPDATE BOM_ALTERNATE_DESIGNATORS
995 SET
996 ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
997 DESCRIPTION = p_alt_desig_rec.DESCRIPTION,
998 DISABLE_DATE = p_alt_desig_rec.DISABLE_DATE,
999 STRUCTURE_TYPE_ID = p_alt_desig_rec.STRUCTURE_TYPE_ID,
1000 ATTRIBUTE_CATEGORY = p_alt_desig_rec.ATTRIBUTE_CATEGORY,
1001 ATTRIBUTE1 = p_alt_desig_rec.ATTRIBUTE1,
1002 ATTRIBUTE2 = p_alt_desig_rec.ATTRIBUTE2,
1003 ATTRIBUTE3 = p_alt_desig_rec.ATTRIBUTE3,
1004 ATTRIBUTE4 = p_alt_desig_rec.ATTRIBUTE4,
1005 ATTRIBUTE5 = p_alt_desig_rec.ATTRIBUTE5,
1006 ATTRIBUTE6 = p_alt_desig_rec.ATTRIBUTE6,
1007 ATTRIBUTE7 = p_alt_desig_rec.ATTRIBUTE7,
1008 ATTRIBUTE8 = p_alt_desig_rec.ATTRIBUTE8,
1009 ATTRIBUTE9 = p_alt_desig_rec.ATTRIBUTE9,
1010 ATTRIBUTE10 = p_alt_desig_rec.ATTRIBUTE10,
1011 ATTRIBUTE11 = p_alt_desig_rec.ATTRIBUTE11,
1012 ATTRIBUTE12 = p_alt_desig_rec.ATTRIBUTE12,
1013 ATTRIBUTE13 = p_alt_desig_rec.ATTRIBUTE13,
1014 ATTRIBUTE14 = p_alt_desig_rec.ATTRIBUTE14,
1015 ATTRIBUTE15 = p_alt_desig_rec.ATTRIBUTE15,
1016 REQUEST_ID = p_alt_desig_rec.REQUEST_ID,
1017 LAST_UPDATE_DATE = p_alt_desig_rec.LAST_UPDATE_DATE,
1020 IS_PREFERRED = p_alt_desig_rec.IS_PREFERRED
837 p_alt_desig_rec.ORGANIZATION_ID,
838 p_alt_desig_rec.DISPLAY_NAME,
839 p_alt_desig_rec.DESCRIPTION,
1018 LAST_UPDATED_BY = p_alt_desig_rec.LAST_UPDATED_BY,
1019 LAST_UPDATE_LOGIN = p_alt_desig_rec.LAST_UPDATE_LOGIN,
1021 WHERE
1022 ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.alt_desig_code_old
1023 AND ORGANIZATION_ID = p_alt_desig_rec.ORGANIZATION_ID;
1024
1025 UPDATE BOM_ALTERNATE_DESIGNATORS_TL
1026 SET
1027 ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
1028 DESCRIPTION = p_alt_desig_rec.DESCRIPTION,
1029 DISPLAY_NAME = p_alt_desig_rec.DISPLAY_NAME,
1030 LAST_UPDATE_DATE = p_alt_desig_rec.LAST_UPDATE_DATE,
1031 LAST_UPDATED_BY = p_alt_desig_rec.LAST_UPDATED_BY,
1032 LAST_UPDATE_LOGIN = p_alt_desig_rec.LAST_UPDATE_LOGIN,
1033 SOURCE_LANG = userenv('LANG')
1034 WHERE
1035 ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.alt_desig_code_old
1036 and ORGANIZATION_ID = p_alt_desig_rec.ORGANIZATION_ID
1037 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1038
1039 x_return_status := FND_API.G_RET_STS_SUCCESS;
1040 ELSIF( p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE <> p_alt_desig_rec.alt_desig_code_old ) THEN
1041 IF NOT Check_Unique(p_alt_desig_rec.ORGANIZATION_ID, p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE) THEN
1042 UPDATE BOM_ALTERNATE_DESIGNATORS
1043 SET
1044 ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
1045 DESCRIPTION = p_alt_desig_rec.description,
1046 DISABLE_DATE = p_alt_desig_rec.disable_date,
1047 STRUCTURE_TYPE_ID = p_alt_desig_rec.structure_type_id,
1048 ATTRIBUTE_CATEGORY = p_alt_desig_rec.ATTRIBUTE_CATEGORY,
1049 ATTRIBUTE1 = p_alt_desig_rec.ATTRIBUTE1,
1050 ATTRIBUTE2 = p_alt_desig_rec.ATTRIBUTE2,
1051 ATTRIBUTE3 = p_alt_desig_rec.ATTRIBUTE3,
1052 ATTRIBUTE4 = p_alt_desig_rec.ATTRIBUTE4,
1053 ATTRIBUTE5 = p_alt_desig_rec.ATTRIBUTE5,
1054 ATTRIBUTE6 = p_alt_desig_rec.ATTRIBUTE6,
1055 ATTRIBUTE7 = p_alt_desig_rec.ATTRIBUTE7,
1056 ATTRIBUTE8 = p_alt_desig_rec.ATTRIBUTE8,
1057 ATTRIBUTE9 = p_alt_desig_rec.ATTRIBUTE9,
1058 ATTRIBUTE10 = p_alt_desig_rec.ATTRIBUTE10,
1059 ATTRIBUTE11 = p_alt_desig_rec.ATTRIBUTE11,
1060 ATTRIBUTE12 = p_alt_desig_rec.ATTRIBUTE12,
1061 ATTRIBUTE13 = p_alt_desig_rec.ATTRIBUTE13,
1062 ATTRIBUTE14 = p_alt_desig_rec.ATTRIBUTE14,
1063 ATTRIBUTE15 = p_alt_desig_rec.ATTRIBUTE15,
1064 REQUEST_ID = p_alt_desig_rec.REQUEST_ID,
1065 LAST_UPDATE_DATE = p_alt_desig_rec.LAST_UPDATE_DATE,
1066 LAST_UPDATED_BY = p_alt_desig_rec.LAST_UPDATED_BY,
1067 LAST_UPDATE_LOGIN = p_alt_desig_rec.LAST_UPDATE_LOGIN
1068 WHERE
1069 ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.alt_desig_code_old -- 4054618
1070 AND ORGANIZATION_ID = p_alt_desig_rec.ORGANIZATION_ID;
1071
1072 UPDATE BOM_ALTERNATE_DESIGNATORS_TL
1073 SET
1074 ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
1075 DESCRIPTION = p_alt_desig_rec.description,
1076 DISPLAY_NAME = p_alt_desig_rec.display_name,
1077 LAST_UPDATE_DATE = p_alt_desig_rec.LAST_UPDATE_DATE,
1078 LAST_UPDATED_BY = p_alt_desig_rec.LAST_UPDATED_BY,
1079 LAST_UPDATE_LOGIN = p_alt_desig_rec.LAST_UPDATE_LOGIN,
1080 SOURCE_LANG = userenv('LANG')
1081 WHERE
1082 ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.alt_desig_code_old
1083 and ORGANIZATION_ID = p_alt_desig_rec.organization_id
1084 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1085
1086 x_return_status := FND_API.G_RET_STS_SUCCESS;
1087 END IF;
1088 END IF;
1089
1090 EXCEPTION
1091 WHEN OTHERS THEN
1092 ROLLBACK TO Update_Alternate_PUB;
1093 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1094 -- x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
1095 x_msg_data := FND_MESSAGE.GET;
1096
1097 END Update_Row;
1098
1099 procedure DELETE_ROW ( ---- Called from form BOMFDBAD.fmb
1100 P_ALTERNATE_DESIGNATOR_CODE in VARCHAR2,
1101 P_ORGANIZATION_ID in NUMBER
1102 ) IS
1103 l_api_version CONSTANT NUMBER := 1.0;
1104 x_return_status VARCHAR2(1);
1105 x_errorcode NUMBER;
1106 x_msg_count NUMBER;
1107 x_msg_data VARCHAR2(1000);
1108 BEGIN
1109 Delete_Row (
1110 p_api_version => l_api_version
1111 ,p_alt_desig_code => P_ALTERNATE_DESIGNATOR_CODE
1112 ,p_organization_id => P_ORGANIZATION_ID
1113 ,x_return_status => x_return_status
1114 ,x_errorcode => x_errorcode
1115 ,x_msg_count => x_msg_count
1116 ,x_msg_data => x_msg_data);
1117
1118 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1119 /* Write exception handling specific to forms using the returned error messages */
1120 NULL;
1121 END IF;
1122 END DELETE_ROW;
1123
1124 procedure LOCK_ROW (
1125 P_ALTERNATE_DESIGNATOR_CODE in VARCHAR2,
1126 P_ORGANIZATION_ID in NUMBER,
1127 P_STRUCTURE_TYPE_ID in NUMBER,
1128 P_DISABLE_DATE in DATE,
1129 P_ATTRIBUTE_CATEGORY in VARCHAR2,
1130 P_ATTRIBUTE1 in VARCHAR2,
1131 P_ATTRIBUTE2 in VARCHAR2,
1132 P_ATTRIBUTE3 in VARCHAR2,
1133 P_ATTRIBUTE4 in VARCHAR2,
1134 P_ATTRIBUTE5 in VARCHAR2,
1135 P_ATTRIBUTE6 in VARCHAR2,
1136 P_ATTRIBUTE7 in VARCHAR2,
1137 P_ATTRIBUTE8 in VARCHAR2,
1138 P_ATTRIBUTE9 in VARCHAR2,
1139 P_ATTRIBUTE10 in VARCHAR2,
1140 P_ATTRIBUTE11 in VARCHAR2,
1141 P_ATTRIBUTE12 in VARCHAR2,
1142 P_ATTRIBUTE13 in VARCHAR2,
1143 P_ATTRIBUTE14 in VARCHAR2,
1144 P_ATTRIBUTE15 in VARCHAR2,
1145 P_REQUEST_ID in NUMBER,
1146 -- P_DISPLAY_NAME in VARCHAR2,
1147 P_DESCRIPTION in VARCHAR2
1148 ) is
1149 cursor c is select
1150 STRUCTURE_TYPE_ID,
1151 DISABLE_DATE,
1152 ATTRIBUTE_CATEGORY,
1153 ATTRIBUTE1,
1154 ATTRIBUTE2,
1155 ATTRIBUTE3,
1159 ATTRIBUTE7,
1156 ATTRIBUTE4,
1157 ATTRIBUTE5,
1158 ATTRIBUTE6,
1160 ATTRIBUTE8,
1161 ATTRIBUTE9,
1162 ATTRIBUTE10,
1163 ATTRIBUTE11,
1164 ATTRIBUTE12,
1165 ATTRIBUTE13,
1166 ATTRIBUTE14,
1167 ATTRIBUTE15,
1168 REQUEST_ID
1169 from BOM_ALTERNATE_DESIGNATORS
1170 where ALTERNATE_DESIGNATOR_CODE = P_ALTERNATE_DESIGNATOR_CODE
1171 and ORGANIZATION_ID = P_ORGANIZATION_ID
1172 for update of ALTERNATE_DESIGNATOR_CODE nowait;
1173 recinfo c%rowtype;
1174
1175 cursor c1 is select
1176 -- DISPLAY_NAME,
1177 DESCRIPTION,
1178 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
1179 from BOM_ALTERNATE_DESIGNATORS_TL
1180 where ALTERNATE_DESIGNATOR_CODE = P_ALTERNATE_DESIGNATOR_CODE
1181 and ORGANIZATION_ID = P_ORGANIZATION_ID
1182 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
1183 for update of ALTERNATE_DESIGNATOR_CODE nowait;
1184 begin
1185 open c;
1186 fetch c into recinfo;
1187 if (c%notfound) then
1188 close c;
1189 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1190 app_exception.raise_exception;
1191 end if;
1192 close c;
1193 if ( ((recinfo.STRUCTURE_TYPE_ID = P_STRUCTURE_TYPE_ID)
1194 OR ((recinfo.STRUCTURE_TYPE_ID is null) AND (P_STRUCTURE_TYPE_ID is null)))
1195 AND ((recinfo.DISABLE_DATE = P_DISABLE_DATE)
1196 OR ((recinfo.DISABLE_DATE is null) AND (P_DISABLE_DATE is null)))
1197 AND ((recinfo.ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY)
1198 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (P_ATTRIBUTE_CATEGORY is null)))
1199 AND ((recinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
1200 OR ((recinfo.ATTRIBUTE1 is null) AND (P_ATTRIBUTE1 is null)))
1201 AND ((recinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
1202 OR ((recinfo.ATTRIBUTE2 is null) AND (P_ATTRIBUTE2 is null)))
1203 AND ((recinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
1204 OR ((recinfo.ATTRIBUTE3 is null) AND (P_ATTRIBUTE3 is null)))
1205 AND ((recinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
1206 OR ((recinfo.ATTRIBUTE4 is null) AND (P_ATTRIBUTE4 is null)))
1207 AND ((recinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
1208 OR ((recinfo.ATTRIBUTE5 is null) AND (P_ATTRIBUTE5 is null)))
1209 AND ((recinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
1210 OR ((recinfo.ATTRIBUTE6 is null) AND (P_ATTRIBUTE6 is null)))
1211 AND ((recinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
1212 OR ((recinfo.ATTRIBUTE7 is null) AND (P_ATTRIBUTE7 is null)))
1213 AND ((recinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
1214 OR ((recinfo.ATTRIBUTE8 is null) AND (P_ATTRIBUTE8 is null)))
1215 AND ((recinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
1216 OR ((recinfo.ATTRIBUTE9 is null) AND (P_ATTRIBUTE9 is null)))
1217 AND ((recinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
1218 OR ((recinfo.ATTRIBUTE10 is null) AND (P_ATTRIBUTE10 is null)))
1219 AND ((recinfo.ATTRIBUTE11 = P_ATTRIBUTE11)
1220 OR ((recinfo.ATTRIBUTE11 is null) AND (P_ATTRIBUTE11 is null)))
1221 AND ((recinfo.ATTRIBUTE12 = P_ATTRIBUTE12)
1222 OR ((recinfo.ATTRIBUTE12 is null) AND (P_ATTRIBUTE12 is null)))
1223 AND ((recinfo.ATTRIBUTE13 = P_ATTRIBUTE13)
1224 OR ((recinfo.ATTRIBUTE13 is null) AND (P_ATTRIBUTE13 is null)))
1225 AND ((recinfo.ATTRIBUTE14 = P_ATTRIBUTE14)
1226 OR ((recinfo.ATTRIBUTE14 is null) AND (P_ATTRIBUTE14 is null)))
1227 AND ((recinfo.ATTRIBUTE15 = P_ATTRIBUTE15)
1228 OR ((recinfo.ATTRIBUTE15 is null) AND (P_ATTRIBUTE15 is null)))
1229 AND ((recinfo.REQUEST_ID = P_REQUEST_ID)
1230 OR ((recinfo.REQUEST_ID is null) AND (P_REQUEST_ID is null)))
1231 ) then
1232 null;
1233 else
1234 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1235 app_exception.raise_exception;
1236 end if;
1237
1238 for tlinfo in c1 loop
1239 if (tlinfo.BASELANG = 'Y') then
1240 if ( -- (tlinfo.DISPLAY_NAME = P_DISPLAY_NAME) AND
1241 ((tlinfo.DESCRIPTION = P_DESCRIPTION)
1242 OR ((tlinfo.DESCRIPTION is null) AND (P_DESCRIPTION is null)))
1243 ) then
1244 null;
1245 else
1246 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1247 app_exception.raise_exception;
1248 end if;
1249 end if;
1250 end loop;
1251 return;
1252 end LOCK_ROW;
1253
1254 Function Get_Preferred_Name ( structure_type_id Number, alt_des_code varchar2) return VARCHAR2
1255 is
1256 return_value varchar2(10);
1257 cursor get_preferred (p_structure_type_id Number,p_alt_code varchar2) is
1258 select distinct alternate_designator_code from bom_alternate_designators
1259 where is_preferred ='Y'
1260 and structure_type_id = p_structure_type_id
1261 and alternate_designator_code <> p_alt_code;
1262 begin
1263
1264 for c1 in get_preferred(structure_type_id,alt_des_code) loop
1265 return_value := c1.alternate_designator_code;
1266 end loop;
1267 return return_value;
1268
1269 end Get_Preferred_Name;
1270
1271 PROCEDURE copy_to_org( p_alt_desig_code IN VARCHAR2, p_from_org_id IN NUMBER, p_to_org_id IN NUMBER) IS
1272 CURSOR c_from_structure_name_csr(cp_alt_desig_code IN VARCHAR2, cp_org_id IN NUMBER) IS
1273 SELECT display_name,
1274 description,
1275 NULL disable_date,
1276 structure_type_id,
1277 is_preferred
1278 FROM bom_alternate_designators_vl badv
1279 WHERE badv.alternate_designator_code = cp_alt_desig_code
1280 AND badv.organization_id = cp_org_id;
1281 l_display_name bom_alternate_designators_tl.display_name%TYPE;
1282 l_description bom_alternate_designators_tl.description%TYPE;
1283 l_disable_date bom_alternate_designators.disable_date%TYPE;
1284 l_structure_type_id bom_alternate_designators.structure_type_id%TYPE;
1288 x_msg_count NUMBER;
1285 l_is_preferred bom_alternate_designators.is_preferred%TYPE;
1286 x_return_status VARCHAR2(1);
1287 x_errorcode NUMBER;
1289 x_msg_data VARCHAR2(4000);
1290 BEGIN
1291
1292 OPEN c_from_structure_name_csr(p_alt_desig_code, p_from_org_id);
1293 FETCH c_from_structure_name_csr INTO l_display_name, l_description, l_disable_date, l_structure_type_id, l_is_preferred;
1294 IF (c_from_structure_name_csr%NOTFOUND) THEN
1295 CLOSE c_from_structure_name_csr;
1296 fnd_message.set_name('BOM', 'BOM_NO_SOURCE_ALT_DESIG_EXISTS');
1297 fnd_message.set_token('ALT_DESIG', p_alt_desig_code, FALSE);
1298 app_exception.raise_exception;
1299 end if;
1300 close c_from_structure_name_csr;
1301 insert_row (
1302 p_api_version => 1.0
1303 ,p_alt_desig_code => p_alt_desig_code
1304 ,p_organization_id => p_to_org_id
1305 ,p_display_name => l_display_name
1306 ,p_description => l_description
1307 ,p_disable_date => l_disable_date
1308 ,p_structure_type_id => l_structure_type_id
1309 ,p_is_preferred => l_is_preferred
1310 ,x_return_status => x_return_status
1311 ,x_errorcode => x_errorcode
1312 ,x_msg_count => x_msg_count
1313 ,x_msg_data => x_msg_data
1314 );
1315 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1316 app_exception.raise_exception;
1317 END IF;
1318 END copy_to_org;
1319
1320 PROCEDURE LOAD_ROW( --called from bomalt.lct
1321 p_alternate_designator_code IN VARCHAR2,
1322 p_organization_id IN NUMBER,
1323 p_description IN VARCHAR2,
1324 p_display_name IN VARCHAR2,
1325 p_disable_date IN DATE,
1326 p_structure_type_id IN NUMBER,
1327 p_attribute_category IN VARCHAR2,
1328 p_attribute1 IN VARCHAR2,
1329 p_attribute2 IN VARCHAR2,
1330 p_attribute3 IN VARCHAR2,
1331 p_attribute4 IN VARCHAR2,
1332 p_attribute5 IN VARCHAR2,
1333 p_attribute6 IN VARCHAR2,
1334 p_attribute7 IN VARCHAR2,
1335 p_attribute8 IN VARCHAR2,
1336 p_attribute9 IN VARCHAR2,
1337 p_attribute10 IN VARCHAR2,
1338 p_attribute11 IN VARCHAR2,
1339 p_attribute12 IN VARCHAR2,
1340 p_attribute13 IN VARCHAR2,
1341 p_attribute14 IN VARCHAR2,
1342 p_attribute15 IN VARCHAR2,
1343 p_request_id IN NUMBER,
1344 p_program_application_id IN NUMBER,
1345 p_program_id IN NUMBER,
1346 p_program_update_date IN DATE,
1347 p_creation_date IN DATE,
1348 p_created_by IN NUMBER,
1349 p_last_update_date IN DATE,
1350 p_last_updated_by IN NUMBER,
1351 p_last_update_login IN NUMBER,
1352 p_custom_mode IN VARCHAR2,
1353 p_is_preferred IN VARCHAR2)
1354 IS
1355 CURSOR GET_ALL_ORGS IS
1356 SELECT organization_id
1357 FROM mtl_parameters ;
1358 BEGIN
1359 /* special logic for seeded packbom to propagate to all orgs */
1360 IF p_alternate_designator_code ='PIM_PBOM_S' THEN
1361 FOR c1 IN GET_ALL_ORGS LOOP
1362 LOAD_ALTERNATE_DESIGNATOR (
1363 p_alternate_designator_code => p_alternate_designator_code,
1364 p_organization_id => c1.organization_id,
1365 p_description => p_description,
1366 p_display_name => p_display_name,
1367 p_disable_date => p_disable_date,
1368 p_structure_type_id => p_structure_type_id,
1369 p_attribute_category => p_attribute_category,
1370 p_attribute1 => p_attribute1,
1371 p_attribute2 => p_attribute2,
1372 p_attribute3 => p_attribute3,
1373 p_attribute4 => p_attribute4,
1374 p_attribute5 => p_attribute5,
1375 p_attribute6 => p_attribute6,
1376 p_attribute7 => p_attribute7,
1377 p_attribute8 => p_attribute8,
1378 p_attribute9 => p_attribute9,
1379 p_attribute10 => p_attribute10,
1380 p_attribute11 => p_attribute11,
1381 p_attribute12 => p_attribute12,
1382 p_attribute13 => p_attribute13,
1383 p_attribute14 => p_attribute14,
1384 p_attribute15 => p_attribute15,
1385 p_request_id => p_request_id,
1386 p_program_application_id => p_program_application_id,
1387 p_program_id => p_program_id,
1388 p_program_update_date => p_program_update_date,
1389 p_creation_date => p_creation_date,
1390 p_created_by => p_created_by,
1391 p_last_update_date => p_last_update_date,
1392 p_last_updated_by => p_last_updated_by,
1393 p_last_update_login => p_last_update_login,
1394 p_custom_mode => p_custom_mode,
1395 p_is_preferred => p_is_preferred
1396 );
1397 END LOOP; -- GET_ALL_ORGS
1398 ELSE
1399 LOAD_ALTERNATE_DESIGNATOR (
1400 p_alternate_designator_code => p_alternate_designator_code,
1401 p_organization_id => p_organization_id ,
1402 p_description => p_description,
1403 p_display_name => p_display_name,
1404 p_disable_date => p_disable_date,
1405 p_structure_type_id => p_structure_type_id,
1406 p_attribute_category => p_attribute_category,
1407 p_attribute1 => p_attribute1,
1408 p_attribute2 => p_attribute2,
1409 p_attribute3 => p_attribute3,
1410 p_attribute4 => p_attribute4,
1411 p_attribute5 => p_attribute5,
1412 p_attribute6 => p_attribute6,
1413 p_attribute7 => p_attribute7,
1414 p_attribute8 => p_attribute8,
1415 p_attribute9 => p_attribute9,
1416 p_attribute10 => p_attribute10,
1417 p_attribute11 => p_attribute11,
1418 p_attribute12 => p_attribute12,
1422 p_request_id => p_request_id,
1419 p_attribute13 => p_attribute13,
1420 p_attribute14 => p_attribute14,
1421 p_attribute15 => p_attribute15,
1423 p_program_application_id => p_program_application_id,
1424 p_program_id => p_program_id,
1425 p_program_update_date => p_program_update_date,
1426 p_creation_date => p_creation_date,
1427 p_created_by => p_created_by,
1428 p_last_update_date => p_last_update_date,
1429 p_last_updated_by => p_last_updated_by,
1430 p_last_update_login => p_last_update_login,
1431 p_custom_mode => p_custom_mode,
1432 p_is_preferred => p_is_preferred
1433 );
1434 END IF; --p_alternate_designator_code PIM_PBOM_S
1435 END LOAD_ROW;
1436
1437 PROCEDURE LOAD_ALTERNATE_DESIGNATOR ( --- called from load_row
1438 p_alternate_designator_code IN VARCHAR2,
1439 p_organization_id IN NUMBER,
1440 p_description IN VARCHAR2,
1441 p_display_name IN VARCHAR2,
1442 p_disable_date IN DATE,
1443 p_structure_type_id IN NUMBER,
1444 p_attribute_category IN VARCHAR2,
1445 p_attribute1 IN VARCHAR2,
1446 p_attribute2 IN VARCHAR2,
1447 p_attribute3 IN VARCHAR2,
1448 p_attribute4 IN VARCHAR2,
1449 p_attribute5 IN VARCHAR2,
1450 p_attribute6 IN VARCHAR2,
1451 p_attribute7 IN VARCHAR2,
1452 p_attribute8 IN VARCHAR2,
1453 p_attribute9 IN VARCHAR2,
1454 p_attribute10 IN VARCHAR2,
1455 p_attribute11 IN VARCHAR2,
1456 p_attribute12 IN VARCHAR2,
1457 p_attribute13 IN VARCHAR2,
1458 p_attribute14 IN VARCHAR2,
1459 p_attribute15 IN VARCHAR2,
1460 p_request_id IN NUMBER,
1461 p_program_application_id IN NUMBER,
1462 p_program_id IN NUMBER,
1463 p_program_update_date IN DATE,
1464 p_creation_date IN DATE,
1465 p_created_by IN NUMBER,
1466 p_last_update_date IN DATE,
1467 p_last_updated_by IN NUMBER,
1468 p_last_update_login IN NUMBER,
1469 p_custom_mode IN VARCHAR2,
1470 p_is_preferred IN VARCHAR2)
1471 IS
1472 db_luby NUMBER; -- entity owner in db
1473 db_ludate DATE; -- entity update date in db
1474 old_preferred_name VARCHAR2(30);
1475 BEGIN
1476
1477 SELECT
1478 bad.LAST_UPDATED_BY, bad.LAST_UPDATE_DATE
1479 INTO
1480 db_luby, db_ludate
1481 FROM
1482 BOM_ALTERNATE_DESIGNATORS bad
1483 WHERE
1484 (
1485 (
1486 p_alternate_designator_code IS NULL
1487 AND bad.ALTERNATE_DESIGNATOR_CODE IS NULL
1488 )
1489 OR
1490 (
1491 p_alternate_designator_code = bad.ALTERNATE_DESIGNATOR_CODE
1492 )
1493 )
1494 AND ( bad.ORGANIZATION_ID = p_organization_id
1495 or (bad.organization_id is null and p_organization_id is null));
1496
1497 -- Test for customization and version
1498 IF ( FND_LOAD_UTIL.UPLOAD_TEST(p_last_updated_by, p_last_update_date, db_luby, db_ludate, p_custom_mode) )
1499 THEN
1500 -- When is_preferred is set for current structure name and there exists another preferred structure name already
1501 -- then reset that value.
1502 IF (p_is_preferred = 'Y' )
1503 THEN
1504 old_preferred_name := Get_Preferred_Name(p_structure_type_id, p_alternate_designator_code);
1505 IF(old_preferred_name <> p_alternate_designator_code)
1506 THEN
1507 UPDATE BOM_ALTERNATE_DESIGNATORS
1508 SET is_preferred ='N'
1509 WHERE alternate_designator_code = old_preferred_name;
1510 END IF;
1511 END IF;
1512
1513 -- Update existing row
1514 -- Since update_row is not taking care of NULL alternate designator, updating row directly.
1515 UPDATE BOM_ALTERNATE_DESIGNATORS
1516 SET
1517 DESCRIPTION = NVL(p_description, DESCRIPTION),
1518 DISABLE_DATE = p_disable_date,
1519 STRUCTURE_TYPE_ID = p_structure_type_id,
1520 ATTRIBUTE_CATEGORY = p_attribute_category,
1521 ATTRIBUTE1 = p_attribute1,
1522 ATTRIBUTE2 = p_attribute2,
1523 ATTRIBUTE3 = p_attribute3,
1524 ATTRIBUTE4 = p_attribute4,
1525 ATTRIBUTE5 = p_attribute5,
1526 ATTRIBUTE6 = p_attribute6,
1527 ATTRIBUTE7 = p_attribute7,
1528 ATTRIBUTE8 = p_attribute8,
1529 ATTRIBUTE9 = p_attribute9,
1530 ATTRIBUTE10 = p_attribute10,
1531 ATTRIBUTE11 = p_attribute11,
1532 ATTRIBUTE12 = p_attribute12,
1533 ATTRIBUTE13 = p_attribute13,
1534 ATTRIBUTE14 = p_attribute14,
1535 ATTRIBUTE15 = p_attribute15,
1536 REQUEST_ID = p_request_id,
1537 PROGRAM_APPLICATION_ID = p_program_application_id,
1538 PROGRAM_ID = p_program_id,
1539 PROGRAM_UPDATE_DATE = p_program_update_date,
1540 LAST_UPDATE_DATE = p_last_update_date,
1541 LAST_UPDATED_BY = p_last_updated_by,
1542 LAST_UPDATE_LOGIN = p_last_update_login,
1543 IS_PREFERRED = p_is_preferred
1544 WHERE
1545 (
1546 (
1547 p_alternate_designator_code IS NULL
1548 AND ALTERNATE_DESIGNATOR_CODE IS NULL
1549 )
1550 OR
1551 ( ALTERNATE_DESIGNATOR_CODE = p_alternate_designator_code )
1552 )
1553 AND (ORGANIZATION_ID = p_organization_id
1554 or (organization_id is null and p_organization_id is null));
1555
1556 UPDATE BOM_ALTERNATE_DESIGNATORS_TL
1557 SET
1558 DESCRIPTION = NVL(p_description, DESCRIPTION),
1559 DISPLAY_NAME = NVL(p_display_name, DISPLAY_NAME),
1560 LAST_UPDATE_DATE = p_last_update_date,
1561 LAST_UPDATED_BY = p_last_updated_by,
1562 LAST_UPDATE_LOGIN = p_last_update_login,
1563 SOURCE_LANG = userenv('LANG')
1564 WHERE
1565 (
1566 (
1570 OR
1567 p_alternate_designator_code IS NULL
1568 AND ALTERNATE_DESIGNATOR_CODE IS NULL
1569 )
1571 ( ALTERNATE_DESIGNATOR_CODE = p_alternate_designator_code )
1572 )
1573 AND ORGANIZATION_ID = p_organization_id
1574 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1575
1576 END IF; -- end of IF FND_LOAD_UTIL
1577
1578 EXCEPTION
1579 WHEN NO_DATA_FOUND THEN
1580 -- Record doesn't exist - insert in all cases
1581 -- Since insert_row is not taking care of NULL alternate designator, inserting row directly.
1582 INSERT INTO BOM_ALTERNATE_DESIGNATORS
1583 (
1584 ALTERNATE_DESIGNATOR_CODE
1585 ,ORGANIZATION_ID
1586 ,DESCRIPTION
1587 ,DISABLE_DATE
1588 ,STRUCTURE_TYPE_ID
1589 ,ATTRIBUTE_CATEGORY
1590 ,ATTRIBUTE1
1591 ,ATTRIBUTE2
1592 ,ATTRIBUTE3
1593 ,ATTRIBUTE4
1594 ,ATTRIBUTE5
1595 ,ATTRIBUTE6
1596 ,ATTRIBUTE7
1597 ,ATTRIBUTE8
1598 ,ATTRIBUTE9
1599 ,ATTRIBUTE10
1600 ,ATTRIBUTE11
1601 ,ATTRIBUTE12
1602 ,ATTRIBUTE13
1603 ,ATTRIBUTE14
1604 ,ATTRIBUTE15
1605 ,REQUEST_ID
1606 ,CREATION_DATE
1607 ,CREATED_BY
1608 ,LAST_UPDATE_DATE
1609 ,LAST_UPDATED_BY
1610 ,LAST_UPDATE_LOGIN
1611 ,PROGRAM_APPLICATION_ID
1612 ,PROGRAM_ID
1613 ,PROGRAM_UPDATE_DATE
1614 ,IS_PREFERRED
1615 )
1616 VALUES
1617 (
1618 p_alternate_designator_code
1619 ,p_organization_id
1620 ,p_description
1621 ,p_disable_date
1622 ,p_structure_type_id
1623 ,p_attribute_category
1624 ,p_attribute1
1625 ,p_attribute2
1626 ,p_attribute3
1627 ,p_attribute4
1628 ,p_attribute5
1629 ,p_attribute6
1630 ,p_attribute7
1631 ,p_attribute8
1632 ,p_attribute9
1633 ,p_attribute10
1634 ,p_attribute11
1635 ,p_attribute12
1636 ,p_attribute13
1637 ,p_attribute14
1638 ,p_attribute15
1639 ,p_request_id
1640 ,p_creation_date
1641 ,p_created_by
1642 ,p_last_update_date
1643 ,p_last_updated_by
1644 ,p_last_update_login
1645 ,p_program_application_id
1646 ,p_program_id
1647 ,p_program_update_date
1648 ,p_is_preferred
1649 );
1650
1651 INSERT INTO BOM_ALTERNATE_DESIGNATORS_TL
1652 (
1653 ALTERNATE_DESIGNATOR_CODE,
1654 ORGANIZATION_ID,
1655 DISPLAY_NAME,
1656 DESCRIPTION,
1657 LAST_UPDATE_DATE,
1658 LAST_UPDATED_BY,
1659 CREATION_DATE,
1660 CREATED_BY,
1661 LAST_UPDATE_LOGIN,
1662 LANGUAGE,
1663 SOURCE_LANG
1664 )
1665 SELECT
1666 p_alternate_designator_code,
1667 p_organization_id,
1668 p_display_name,
1669 p_description,
1670 p_last_update_date,
1671 p_last_updated_by,
1672 p_creation_date,
1673 p_created_by,
1674 p_last_update_login,
1675 L.LANGUAGE_CODE,
1676 userenv('LANG')
1677 FROM
1678 FND_LANGUAGES L
1679 WHERE
1680 L.INSTALLED_FLAG IN ('I', 'B')
1681 AND NOT EXISTS
1682 (
1683 SELECT NULL
1684 FROM BOM_ALTERNATE_DESIGNATORS_TL T
1685 WHERE
1686 (
1687 (
1688 p_alternate_designator_code IS NULL
1689 AND T.ALTERNATE_DESIGNATOR_CODE IS NULL
1690 )
1691 OR
1692 ( T.ALTERNATE_DESIGNATOR_CODE = p_alternate_designator_code )
1693 )
1694 AND T.ORGANIZATION_ID = p_organization_id
1695 AND T.LANGUAGE = L.LANGUAGE_CODE
1696 );
1697
1698 -- When is_preferred is set for current structure name and there exists another preferred structure name already
1699 -- then reset that value.
1700 IF (p_is_preferred = 'Y' )
1701 THEN
1702 old_preferred_name := Get_Preferred_Name(p_structure_type_id, p_alternate_designator_code);
1703 IF(old_preferred_name <> p_alternate_designator_code)
1704 THEN
1705 UPDATE BOM_ALTERNATE_DESIGNATORS
1706 SET is_preferred ='N'
1707 WHERE alternate_designator_code = old_preferred_name;
1708 END IF;
1709 END IF;
1710
1711 END LOAD_ALTERNATE_DESIGNATOR;
1712
1713 PROCEDURE TRANSLATE_ROW ( --- called from bomalt.lct
1714 p_alternate_designator_code IN VARCHAR2,
1715 p_organization_id IN NUMBER,
1716 p_description IN VARCHAR2,
1717 p_display_name IN VARCHAR2,
1718 p_last_update_date IN DATE,
1719 p_last_updated_by IN NUMBER,
1720 p_last_update_login IN NUMBER,
1721 p_custom_mode IN VARCHAR2)
1722 IS
1723 db_luby NUMBER; -- entity owner in db
1724 db_ludate DATE; -- entity update date in db
1725 BEGIN
1726
1727 SELECT
1728 badtl.LAST_UPDATED_BY, badtl.LAST_UPDATE_DATE
1729 INTO
1730 db_luby, db_ludate
1731 FROM
1732 BOM_ALTERNATE_DESIGNATORS_TL badtl
1733 WHERE
1734 badtl.LANGUAGE = userenv('LANG')
1735 AND
1736 (
1737 (
1738 p_alternate_designator_code IS NULL
1739 AND badtl.ALTERNATE_DESIGNATOR_CODE IS NULL
1740 )
1741 OR
1742 (
1743 p_alternate_designator_code = badtl.ALTERNATE_DESIGNATOR_CODE
1744 )
1745 )
1746 AND badtl.ORGANIZATION_ID = p_organization_id;
1747
1748 -- Test for customization and version
1749 IF ( FND_LOAD_UTIL.UPLOAD_TEST(p_last_updated_by, p_last_update_date, db_luby, db_ludate, p_custom_mode) )
1750 THEN
1751 -- Update translations for this language
1752 UPDATE BOM_ALTERNATE_DESIGNATORS_TL
1753 SET
1754 DISPLAY_NAME = NVL(p_display_name, DISPLAY_NAME),
1755 DESCRIPTION = NVL(p_description, DESCRIPTION),
1756 LAST_UPDATE_DATE = p_last_update_date,
1757 LAST_UPDATED_BY = p_last_updated_by,
1758 LAST_UPDATE_LOGIN = p_last_update_login,
1759 SOURCE_LANG = userenv('LANG')
1760 WHERE
1761 userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
1762 AND
1763 (
1764 (
1765 p_alternate_designator_code IS NULL
1766 AND ALTERNATE_DESIGNATOR_CODE IS NULL
1767 )
1768 OR
1769 (
1770 p_alternate_designator_code = ALTERNATE_DESIGNATOR_CODE
1771 )
1772 )
1773 AND ORGANIZATION_ID = p_organization_id;
1774
1775 END IF; -- end of IF FND_LOAD_UTIL
1776
1777 EXCEPTION
1778 WHEN NO_DATA_FOUND THEN
1779 -- Do not insert missing translations, skip this row
1780 NULL;
1781 END TRANSLATE_ROW;
1782
1783 END BOM_ALTERNATE_DESIGNATORS_PKG;