[Home] [Help]
PACKAGE BODY: APPS.BOM_PFI_PVT
Source
1 PACKAGE BODY BOM_PFI_PVT AS
2 /* $Header: BOMVPFIB.pls 120.2 2005/06/21 03:58:14 appldev ship $ */
3
4 /****************************************************************************/
5 -- --------------- Private constants -----------------
6 -- -----------------------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'BOM_PFI_PVT' ;
9
10 /****************************************************************************/
11
12 -- Record in PL/SQL table each product family item corresponding to a
13 -- category to be created.
14 --
15 PROCEDURE Store_Cat_Create
16 ( p_return_sts IN OUT NOCOPY NUMBER ,
17 p_return_err IN OUT NOCOPY VARCHAR2 ,
18 p_item_id IN NUMBER ,
19 p_org_id IN NUMBER ,
20 p_Cat_Create_Num IN OUT NOCOPY BINARY_INTEGER ,
21 p_Create_Cat_Tbl IN OUT NOCOPY Create_Category_Tbl_Type
22 )
23 IS
24 l_return_sts NUMBER := 0 ;
25 l_return_err VARCHAR2(2000) := NULL ;
26 BEGIN
27 p_return_sts := 0 ;
28
29 p_Cat_Create_Num := p_Cat_Create_Num + 1 ;
30 p_Create_Cat_Tbl( p_Cat_Create_Num ).item_id := p_item_id ;
31 p_Create_Cat_Tbl( p_Cat_Create_Num ).org_id := p_org_id ;
32
33
34 EXCEPTION
35 WHEN OTHERS THEN
36 p_Cat_Create_Num := 0 ;
37 p_return_sts := 1 ;
38 l_return_err := 'BOM_PFI_PVT.Store_Cat_Create: ' || SQLERRM ;
39 raise_application_error (-20000, l_return_err);
40
41 END Store_Cat_Create;
42
43 /****************************************************************************/
44
45 PROCEDURE Create_PF_Category
46 ( p_return_sts IN OUT NOCOPY NUMBER ,
47 p_return_err IN OUT NOCOPY VARCHAR2 ,
48 p_Cat_Create_Num IN OUT NOCOPY BINARY_INTEGER ,
49 p_Create_Cat_Tbl IN OUT NOCOPY Create_Category_Tbl_Type
50 )
51 IS
52 l_return_sts NUMBER := 0 ;
53 l_return_err VARCHAR2(2000) := NULL ;
54 l_item_id NUMBER ;
55 l_org_id NUMBER ;
56 l_concat_segments VARCHAR2(2000) := NULL ;
57 l_category_id NUMBER ;
58 l_stmt_num NUMBER := 0 ;
59 CAT_NOT_INSERTED EXCEPTION ;
60
61 l_New_Category_ID NUMBER ;
62 l_Source_Lang VARCHAR2(4) ;
63 l_Description VARCHAR2(240) := 'Product family' ;
64
65 v_user_id VARCHAR2(20) ;
66 BEGIN
67 v_user_id := fnd_global.user_id;
68
69 select userenv('LANG')
70 into l_Source_Lang
71 from dual ;
72
73 IF ( SQL%NOTFOUND ) THEN
74 RAISE no_data_found;
75 END IF;
76
77 p_return_sts := 0 ;
78
79 l_stmt_num := 1 ;
80
81 -- Loop through each recorded product family item
82 -- corresponding to category to be created
83 --
84 FOR l_Cat_Ind IN 1..p_Cat_Create_Num LOOP
85 -----------------------------------------
86 l_item_id := p_Create_Cat_Tbl( l_Cat_Ind ).item_id ;
87 l_org_id := p_Create_Cat_Tbl( l_Cat_Ind ).org_id ;
88
89 l_stmt_num := 2 ;
90
91 BOM_PFI_PVT.Get_Category_ID
92 ( p_return_sts => l_return_sts ,
93 p_return_err => l_return_err ,
94 p_item_id => l_item_id ,
95 p_org_id => l_org_id ,
96 p_concat_segments => l_concat_segments ,
97 p_category_id => l_category_id
98 );
99
100 IF ( l_return_sts = 0 ) THEN
101 -- Do not insert if category already exists
102 GOTO No_Insert;
103 ELSIF ( l_return_sts <> 3 ) THEN
104 p_return_sts := 2 ;
105 l_return_err := 'BOM_PFI_PVT.Create_PF_Category: ' || l_return_err ;
106 FND_MESSAGE.set_name('BOM', 'BOM_PFI_PVT_MSG');
107 FND_MESSAGE.set_token('MSG', l_return_err);
108 APP_EXCEPTION.RAISE_EXCEPTION;
109 END IF;
110
111 -- Proceed with insert if category does not exist (l_return_sts = 3)
112
113 l_stmt_num := 3 ;
114
115 select MTL_CATEGORIES_S.nextval
116 into l_New_Category_ID
117 from dual ;
118
119 INSERT INTO MTL_CATEGORIES_B
120 (
121 CATEGORY_ID ,
122 STRUCTURE_ID ,
123 DISABLE_DATE ,
124 SEGMENT1 ,
125 SEGMENT2 ,
126 SEGMENT3 ,
127 SEGMENT4 ,
128 SEGMENT5 ,
129 SEGMENT6 ,
130 SEGMENT7 ,
131 SEGMENT8 ,
132 SEGMENT9 ,
133 SEGMENT10 ,
134 SEGMENT11 ,
135 SEGMENT12 ,
136 SEGMENT13 ,
137 SEGMENT14 ,
138 SEGMENT15 ,
139 SEGMENT16 ,
140 SEGMENT17 ,
141 SEGMENT18 ,
142 SEGMENT19 ,
143 SEGMENT20 ,
144 SUMMARY_FLAG ,
145 ENABLED_FLAG ,
146 CREATION_DATE ,
147 CREATED_BY ,
148 LAST_UPDATE_DATE,
149 LAST_UPDATED_BY ,
150 LAST_UPDATE_LOGIN
151 )
152 SELECT
153 l_New_Category_ID ,
154 BOM_PFI_PVT.G_PF_Structure_ID ,
155 null ,
156 SI.SEGMENT1 ,
157 SI.SEGMENT2 ,
158 SI.SEGMENT3 ,
159 SI.SEGMENT4 ,
160 SI.SEGMENT5 ,
161 SI.SEGMENT6 ,
162 SI.SEGMENT7 ,
163 SI.SEGMENT8 ,
164 SI.SEGMENT9 ,
165 SI.SEGMENT10 ,
166 SI.SEGMENT11 ,
167 SI.SEGMENT12 ,
168 SI.SEGMENT13 ,
169 SI.SEGMENT14 ,
170 SI.SEGMENT15 ,
171 SI.SEGMENT16 ,
172 SI.SEGMENT17 ,
173 SI.SEGMENT18 ,
174 SI.SEGMENT19 ,
175 SI.SEGMENT20 ,
176 'N' ,
177 'Y' ,
178 sysdate ,
179 v_user_id ,
180 sysdate ,
181 v_user_id ,
182 null
183 FROM MTL_SYSTEM_ITEMS_B SI
184 WHERE INVENTORY_ITEM_ID = l_item_id
185 AND ORGANIZATION_ID = l_org_id ;
186
187 IF ( SQL%NOTFOUND ) THEN
188 RAISE CAT_NOT_INSERTED;
189 END IF;
190
191 insert into MTL_CATEGORIES_TL (
192 CATEGORY_ID,
193 LANGUAGE,
194 SOURCE_LANG,
195 DESCRIPTION,
196 LAST_UPDATE_DATE,
197 LAST_UPDATED_BY,
198 CREATION_DATE,
199 CREATED_BY,
200 LAST_UPDATE_LOGIN
201 ) select
202 l_New_Category_ID ,
203 L.LANGUAGE_CODE ,
204 l_Source_Lang ,
205 l_Description ,
206 sysdate ,
207 v_user_id ,
208 sysdate ,
209 v_user_id ,
210 null
211 from FND_LANGUAGES L
212 where L.INSTALLED_FLAG in ('I', 'B')
213 and not exists
214 ( select NULL
215 from MTL_CATEGORIES_TL T
216 where T.CATEGORY_ID = l_New_Category_ID
217 and T.LANGUAGE = L.LANGUAGE_CODE );
218
219 <<No_Insert>>
220 NULL;
221 ----------------------------------------
222 END LOOP; -- recorded category creation
223
224 -- Reset counter so the next execution will start new index count
225 --
226 p_Cat_Create_Num := 0 ;
227
228
229 EXCEPTION
230 WHEN CAT_NOT_INSERTED THEN
231 p_Cat_Create_Num := 0 ;
232 p_return_sts := 1 ;
233 l_return_err := 'BOM_PFI_PVT.Create_PF_Category: cannot insert category' ;
234 raise_application_error (-20000, l_return_err);
235
236 WHEN OTHERS THEN
237 p_Cat_Create_Num := 0 ;
238 IF ( SQLCODE = -20001 ) THEN
239 APP_EXCEPTION.RAISE_EXCEPTION;
240 ELSE
241 p_return_sts := 4 ;
242 l_return_err := 'BOM_PFI_PVT.Create_PF_Category: ' || SQLERRM ;
243 raise_application_error (-20000, l_return_err);
244 END IF;
245
246 END Create_PF_Category;
247
248 /****************************************************************************/
249
250 -- Record in PL/SQL table each product family item corresponding to a
251 -- category to be deleted.
252 --
253 PROCEDURE Store_Category
254 ( p_return_sts IN OUT NOCOPY NUMBER ,
255 p_return_err IN OUT NOCOPY VARCHAR2 ,
256 p_item_id IN NUMBER ,
257 p_org_id IN NUMBER ,
258 p_Cat_Num IN OUT NOCOPY BINARY_INTEGER ,
259 p_Delete_Cat_Tbl IN OUT NOCOPY Delete_Category_Tbl_Type
260 )
261 IS
262 l_return_sts NUMBER := 0 ;
263 l_return_err VARCHAR2(2000) := NULL ;
264 BEGIN
265 p_return_sts := 0 ;
266
267 p_Cat_Num := p_Cat_Num + 1 ;
268 p_Delete_Cat_Tbl( p_Cat_Num ).item_id := p_item_id ;
269 p_Delete_Cat_Tbl( p_Cat_Num ).org_id := p_org_id ;
270
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 p_Cat_Num := 0 ;
275 p_return_sts := 1 ;
276 l_return_err := 'BOM_PFI_PVT.Store_Category: ' || SQLERRM ;
277 raise_application_error (-20000, l_return_err);
278
279 END Store_Category;
280
281 /****************************************************************************/
282
283 PROCEDURE Delete_PF_Category
284 ( p_return_sts IN OUT NOCOPY NUMBER ,
285 p_return_err IN OUT NOCOPY VARCHAR2 ,
286 p_Cat_Num IN OUT NOCOPY BINARY_INTEGER ,
287 p_Delete_Cat_Tbl IN OUT NOCOPY Delete_Category_Tbl_Type
288 )
289 IS
290 l_return_sts NUMBER := 0 ;
291 l_return_err VARCHAR2(2000) := NULL ;
292 l_item_id NUMBER ;
293 l_org_id NUMBER ;
294 l_concat_segments VARCHAR2(2000) := NULL ;
295 l_category_id NUMBER ;
296 l_stmt_num NUMBER := 0 ;
297 BEGIN
298 p_return_sts := 0 ;
299
300 l_stmt_num := 1 ;
301
302 -- Loop through each recorded product family item
303 -- corresponding to category to be deleted
304 --
305 FOR l_Cat_Ind IN 1..p_Cat_Num LOOP
306 ----------------------------------
307 l_item_id := p_Delete_Cat_Tbl( l_Cat_Ind ).item_id ;
308 l_org_id := p_Delete_Cat_Tbl( l_Cat_Ind ).org_id ;
309
310 l_stmt_num := 2 ;
311
312 BOM_PFI_PVT.Get_Category_ID
313 ( p_return_sts => l_return_sts ,
314 p_return_err => l_return_err ,
315 p_item_id => l_item_id ,
316 p_org_id => l_org_id ,
317 p_concat_segments => l_concat_segments ,
318 p_category_id => l_category_id
319 );
320
321 -- Do not rise exception when category ID is not found.
322 -- The category could have not been created.
323
324 IF ( l_return_sts = 0 ) THEN
325
326 DELETE FROM MTL_CATEGORIES_TL
327 WHERE CATEGORY_ID = l_category_id ;
328
329 DELETE FROM MTL_CATEGORIES_B
330 WHERE CATEGORY_ID = l_category_id ;
331
332 ELSE
333 p_return_sts := 2 ;
334 l_return_err := 'BOM_PFI_PVT.Delete_PF_Category: ' || l_return_err ;
335 END IF;
336
337 ----------------------------------------
338 END LOOP; -- recorded category deletion
339
340 -- Reset deletion counter so the next execution will start new index count
341 --
342 p_Cat_Num := 0 ;
343
344
345 EXCEPTION
346 WHEN OTHERS THEN
347 p_Cat_Num := 0 ;
348 p_return_sts := 4 ;
349 l_return_err := 'BOM_PFI_PVT.Delete_PF_Category: ' || SQLERRM ;
350 raise_application_error (-20000, l_return_err);
351
352 END Delete_PF_Category;
353
354 /****************************************************************************/
355
356 -- Record in PL/SQL table each item assignment to category/category set.
357 --
358 PROCEDURE Store_Cat_Assign
359 ( p_return_sts IN OUT NOCOPY NUMBER ,
360 p_return_err IN OUT NOCOPY VARCHAR2 ,
361 p_item_id IN NUMBER ,
362 p_org_id IN NUMBER ,
363 p_pf_item_id IN NUMBER ,
364 p_Assign_Num IN OUT NOCOPY BINARY_INTEGER ,
365 p_Cat_Assign_Tbl IN OUT NOCOPY Category_Assign_Tbl_Type
366 )
367 IS
368 l_return_sts NUMBER := 0 ;
369 l_return_err VARCHAR2(2000) := NULL ;
370 BEGIN
371 p_return_sts := 0 ;
372
373 p_Assign_Num := p_Assign_Num + 1 ;
374 p_Cat_Assign_Tbl( p_Assign_Num ).item_id := p_item_id ;
375 p_Cat_Assign_Tbl( p_Assign_Num ).org_id := p_org_id ;
376 p_Cat_Assign_Tbl( p_Assign_Num ).pf_item_id := p_pf_item_id ;
377
378
379 EXCEPTION
380 WHEN OTHERS THEN
381 p_Assign_Num := 0 ;
382 p_return_sts := 1 ;
383 l_return_err := 'BOM_PFI_PVT.Store_Cat_Assign: ' || SQLERRM ;
384 raise_application_error (-20000, l_return_err);
385
386 END Store_Cat_Assign;
387
388 /****************************************************************************/
389
390 PROCEDURE Assign_To_Category
391 ( p_return_sts IN OUT NOCOPY NUMBER ,
392 p_return_err IN OUT NOCOPY VARCHAR2 ,
393 p_Assign_Num IN OUT NOCOPY BINARY_INTEGER ,
394 p_Cat_Assign_Tbl IN OUT NOCOPY Category_Assign_Tbl_Type
395 )
396 IS
397 l_return_sts NUMBER := 0 ;
398 l_return_err VARCHAR2(2000) := NULL ;
399 l_item_id NUMBER ;
400 l_org_id NUMBER ;
401 l_master_org_id NUMBER ;
402 l_pf_item_id NUMBER ;
403 l_concat_segments VARCHAR2(2000) := NULL ;
404 l_category_id NUMBER := -1 ;
405 l_stmt_num NUMBER := 0 ;
406 v_user_id VARCHAR2(20) ;
407 BEGIN
408 v_user_id := fnd_global.user_id;
409 p_return_sts := 0 ;
410
411 -- Loop through each recorded item assignment
412 --
413 FOR l_Assign_Ind IN 1..p_Assign_Num LOOP
414 ----------------------------------------
415 l_item_id := p_Cat_Assign_Tbl( l_Assign_Ind ).item_id ;
416 l_org_id := p_Cat_Assign_Tbl( l_Assign_Ind ).org_id ;
417 l_pf_item_id := p_Cat_Assign_Tbl( l_Assign_Ind ).pf_item_id ;
418
419 l_stmt_num := 1 ;
420
421 BOM_PFI_PVT.Get_Master_Org_ID
422 ( p_return_sts => l_return_sts ,
423 p_return_err => l_return_err ,
424 p_org_id => l_org_id ,
425 p_master_org_id => l_master_org_id
426 );
427
428 l_stmt_num := 2 ;
429
430 BOM_PFI_PVT.Get_Category_ID
431 ( p_return_sts => l_return_sts ,
432 p_return_err => l_return_err ,
433 p_item_id => l_pf_item_id ,
434 p_org_id => l_master_org_id ,
435 p_concat_segments => l_concat_segments ,
436 p_category_id => l_category_id
437 );
438
439 IF ( l_return_sts = 3 ) THEN
440 p_return_sts := 3 ;
441 FND_MESSAGE.set_name('BOM', 'BOM_PFI_PVT_CAT_ASSIGN');
442 FND_MESSAGE.set_token('CAT', l_concat_segments);
443 APP_EXCEPTION.RAISE_EXCEPTION;
444 ELSIF ( l_return_sts <> 0 ) THEN
445 p_return_sts := 2 ;
446 l_return_err := 'Assign_To_Category: ' || l_return_err ;
447 FND_MESSAGE.set_name('BOM', 'BOM_PFI_PVT_MSG');
448 FND_MESSAGE.set_token('MSG', l_return_err);
449 APP_EXCEPTION.RAISE_EXCEPTION;
450 END IF;
451
452 l_stmt_num := 3 ;
453
454 INSERT INTO MTL_ITEM_CATEGORIES
455 ( INVENTORY_ITEM_ID ,
456 ORGANIZATION_ID ,
457 CATEGORY_SET_ID ,
458 CATEGORY_ID ,
459 CREATION_DATE ,
460 CREATED_BY ,
461 LAST_UPDATE_DATE ,
462 LAST_UPDATED_BY ,
463 LAST_UPDATE_LOGIN
464 )
465 SELECT
466 l_item_id ,
467 l_org_id ,
468 G_PF_Category_Set_ID ,
469 l_category_id ,
470 sysdate ,
471 v_user_id ,
472 sysdate ,
473 v_user_id ,
474 null
475 FROM dual
476 WHERE NOT EXISTS
477 ( SELECT 'x'
478 FROM MTL_ITEM_CATEGORIES icat
479 WHERE icat.INVENTORY_ITEM_ID = l_item_id
480 AND icat.ORGANIZATION_ID = l_org_id
481 AND icat.CATEGORY_SET_ID = G_PF_Category_Set_ID
482 );
483
484 --------------------------------------
485 END LOOP; -- recorded item assignment
486
487 -- Reset assignment counter so the next execution will start new index count
488 --
489 p_Assign_Num := 0 ;
490
491
492 EXCEPTION
493 WHEN OTHERS THEN
494 p_Assign_Num := 0 ;
498 p_return_sts := 4 ;
495 IF ( SQLCODE = -20001 ) THEN
496 APP_EXCEPTION.RAISE_EXCEPTION;
497 ELSE
499 l_return_err := 'BOM_PFI_PVT.Assign_To_Category: ' || SQLERRM ;
500 raise_application_error (-20000, l_return_err);
501 END IF;
502
503 END Assign_To_Category;
504
505 /****************************************************************************/
506
507 PROCEDURE Remove_From_Category
508 ( p_return_sts IN OUT NOCOPY NUMBER ,
509 p_return_err IN OUT NOCOPY VARCHAR2 ,
510 p_item_id IN NUMBER ,
511 p_org_id IN NUMBER
512 )
513 IS
514 l_return_sts NUMBER := 0 ;
515 l_return_err VARCHAR2(2000) := NULL ;
516 ITEM_CAT_NOTFOUND EXCEPTION ;
517 BEGIN
518 p_return_sts := 0 ;
519
520 DELETE FROM MTL_ITEM_CATEGORIES
521 WHERE INVENTORY_ITEM_ID = p_item_id
522 AND ORGANIZATION_ID = p_org_id
523 AND CATEGORY_SET_ID = G_PF_Category_Set_ID ;
524
525 -- Do not rise exception when item assignment to category is not found.
526 -- Item could have been removed from the category by the user.
527 /* IF ( SQL%NOTFOUND ) THEN
528 RAISE ITEM_CAT_NOTFOUND;
529 END IF;
530 */
531
532 EXCEPTION
533 /*
534 WHEN ITEM_CAT_NOTFOUND THEN
535 p_return_sts := 1 ;
536 p_return_err := 'Remove_From_Category: Item assignment not found.' ;
537 */
538 WHEN OTHERS THEN
539 p_return_sts := 2 ;
540 l_return_err := 'BOM_PFI_PVT.Remove_From_Category: ' || SQLERRM ;
541 raise_application_error (-20000, l_return_err);
542
543 END Remove_From_Category;
544
545 /****************************************************************************/
546
547 PROCEDURE Get_Category_ID
548 ( p_return_sts IN OUT NOCOPY NUMBER ,
549 p_return_err IN OUT NOCOPY VARCHAR2 ,
550 p_item_id IN NUMBER ,
551 p_org_id IN NUMBER ,
552 p_concat_segments IN OUT NOCOPY VARCHAR2 ,
553 p_category_id IN OUT NOCOPY NUMBER
554 )
555 IS
556 l_return_sts NUMBER := 0 ;
557 l_return_err VARCHAR2(2000) ;
558 l_concat_segments VARCHAR2(2000) ;
559 l_stmt_num NUMBER := 0 ;
560 BEGIN
561 p_return_sts := 0 ;
562
563 l_stmt_num := 2 ;
564
565 IF FND_FLEX_KEYVAL.validate_ccid
566 ( appl_short_name => 'INV' ,
567 key_flex_code => 'MSTK' ,
568 structure_number => BOM_PFI_PVT.G_MSTK_Structure_ID ,
569 combination_id => p_item_id ,
570 data_set => p_org_id
571 )
572 THEN
573 l_concat_segments := FND_FLEX_KEYVAL.concatenated_values ;
574 p_concat_segments := l_concat_segments ;
575 ELSE
576 p_return_sts := 2 ;
577 p_return_err := 'Get_Category_ID: Table MTL_SYSTEM_ITEMS_B, flexfield MSTK: ' ||
578 FND_FLEX_KEYVAL.error_message ;
579 RETURN;
580 END IF;
581
582 l_stmt_num := 3 ;
583
584 IF FND_FLEX_KEYVAL.validate_segs
585 ( operation => 'FIND_COMBINATION' ,
586 appl_short_name => 'INV' ,
587 key_flex_code => 'MCAT' ,
588 structure_number => BOM_PFI_PVT.G_PF_Structure_ID ,
589 concat_segments => l_concat_segments
590 )
591 THEN
592 p_category_id := FND_FLEX_KEYVAL.combination_id ;
593 ELSE
594 p_return_sts := 3 ;
595 p_return_err := 'Get_Category_ID: Table MTL_CATEGORIES_B, flexfield MCAT: ' ||
596 FND_FLEX_KEYVAL.error_message ;
597 RETURN;
598 END IF;
599
600
601 EXCEPTION
602 WHEN OTHERS THEN
603 p_return_sts := 4 ;
604 p_return_err := 'Get_Category_ID: ' || SQLERRM ;
605
606 END Get_Category_ID;
607
608 /****************************************************************************/
609
610 PROCEDURE Get_Master_Org_ID
611 ( p_return_sts IN OUT NOCOPY NUMBER ,
612 p_return_err IN OUT NOCOPY VARCHAR2 ,
613 p_org_id IN NUMBER ,
614 p_master_org_id IN OUT NOCOPY NUMBER
615 )
616 IS
617 l_return_sts NUMBER := 0 ;
618 l_return_err VARCHAR2(2000) := NULL ;
619 l_master_org_id NUMBER ;
620 BEGIN
621
622 SELECT MASTER_ORGANIZATION_ID INTO l_master_org_id
623 FROM MTL_PARAMETERS
624 WHERE ORGANIZATION_ID = p_org_id ;
625
626 p_master_org_id := l_master_org_id ;
627
628
629 EXCEPTION
630 WHEN NO_DATA_FOUND THEN
631 l_return_err := to_char( p_org_id );
632 FND_MESSAGE.set_name('BOM', 'BOM_PFI_PVT_NOTFOUND');
633 FND_MESSAGE.set_token('PROC', 'Get_Master_Org_ID');
634 FND_MESSAGE.set_token('ENTITY', 'ORG_ID ' || l_return_err);
635 APP_EXCEPTION.RAISE_EXCEPTION;
636
637 WHEN OTHERS THEN
638 l_return_err := 'Get_Master_Org_ID:' || SQLERRM ;
639 raise_application_error (-20000, l_return_err);
640
641 END Get_Master_Org_ID;
642
643 /****************************************************************************/
644
645 FUNCTION Org_Is_Master
646 ( p_org_id IN NUMBER
647 )
648 RETURN BOOLEAN
649 IS
650 l_return_err VARCHAR2(2000) ;
651 l_master_org_id NUMBER ;
652 BEGIN
653
654 SELECT MASTER_ORGANIZATION_ID INTO l_master_org_id
655 FROM MTL_PARAMETERS
656 WHERE ORGANIZATION_ID = p_org_id ;
657
658 IF ( l_master_org_id = p_org_id ) THEN
659 RETURN TRUE;
660 ELSE
661 RETURN FALSE;
662 END IF;
663
664 EXCEPTION
665 WHEN OTHERS THEN
666 RETURN FALSE;
667
668 END Org_Is_Master;
669
670 /****************************************************************************/
671
672 PROCEDURE Check_PF_Segs
673 IS
674 l_MSI_segs VARCHAR2(40) := NULL ;
675 l_PF_segs VARCHAR2(40) := NULL ;
676
677 CURSOR MSI_Struct_Segs IS
678 select application_column_name
679 from FND_ID_FLEX_SEGMENTS
680 where application_id = 401
681 and id_flex_code = 'MSTK'
682 and id_flex_num = 101
683 and enabled_flag = 'Y'
684 order by segment_num;
685
686 CURSOR PF_Struct_Segs IS
687 select application_column_name
688 from FND_ID_FLEX_SEGMENTS
689 where application_id = 401
690 and id_flex_code = 'MCAT'
691 and id_flex_num = G_PF_Structure_ID
692 and enabled_flag = 'Y'
693 order by segment_num;
694
695 BEGIN
696 BOM_PFI_PVT.PF_Segs_Status := BOM_PFI_PVT.G_PF_Segs_Status_OK;
697
698 OPEN MSI_Struct_Segs;
699 OPEN PF_Struct_Segs;
700 LOOP
701 FETCH MSI_Struct_Segs into l_MSI_segs;
702 FETCH PF_Struct_Segs into l_PF_segs;
703
704 IF ( MSI_Struct_Segs%NOTFOUND and PF_Struct_Segs%NOTFOUND )
705 THEN
706 EXIT;
707 ELSIF ( MSI_Struct_Segs%FOUND and PF_Struct_Segs%FOUND )
708 THEN
709 IF NOT (l_PF_segs = l_MSI_segs) THEN
710 BOM_PFI_PVT.PF_Segs_Status := BOM_PFI_PVT.G_PF_Segs_Status_Mismatch;
711 EXIT;
712 END IF;
713 ELSE
714 IF ( l_PF_segs is null ) THEN
715 BOM_PFI_PVT.PF_Segs_Status := BOM_PFI_PVT.G_PF_Segs_Status_Undefined;
716 ELSE
717 BOM_PFI_PVT.PF_Segs_Status := BOM_PFI_PVT.G_PF_Segs_Status_Mismatch;
718 END IF;
719 EXIT;
720 END IF;
721 END LOOP;
722 CLOSE MSI_Struct_Segs;
723 CLOSE PF_Struct_Segs;
724
725 IF ( BOM_PFI_PVT.PF_Segs_Status = BOM_PFI_PVT.G_PF_Segs_Status_Mismatch )
726 THEN
727 BOM_PFI_PVT.PF_Segs_Status := BOM_PFI_PVT.G_PF_Segs_Status_OK;
728 FND_MESSAGE.set_name('INV', 'INV_BOM_PFI_SEGS_MISMATCH');
729 APP_EXCEPTION.RAISE_EXCEPTION;
730 END IF;
731
732 END Check_PF_Segs;
733
734 /****************************************************************************/
735
736 FUNCTION PF_Segs_Undefined
737 RETURN BOOLEAN
738 IS
739 BEGIN
740 IF ( BOM_PFI_PVT.PF_Segs_Status = BOM_PFI_PVT.G_PF_Segs_Status_Undefined )
741 THEN
742 BOM_PFI_PVT.PF_Segs_Status := BOM_PFI_PVT.G_PF_Segs_Status_OK;
743 RETURN TRUE;
744 ELSE
745 RETURN FALSE;
746 END IF;
747
748 END PF_Segs_Undefined;
749
750 /****************************************************************************/
751
752 END BOM_PFI_PVT;