1 PACKAGE BODY BOM_REVISIONS AS
2 /* $Header: BOMREVSB.pls 120.4 2006/03/24 02:03:11 earumuga ship $ */
3
4 /* --------------------------- RAISE_REVISION_ERROR ------------------------
5 NAME
6 RAISE_REVISION_ERROR - raises generic error message
7 DESCRIPTION
8 for sql error failures, places the SQLERRM error on the message stack
9
10 REQUIRES
11 func_name PROCEDURE_name
12 stmt_num statement number
13
14 OUTPUT
15
16 NOTES
17 ---------------------------------------------------------------------------*/
18 PROCEDURE RAISE_REVISION_ERROR (
19 func_name VARCHAR2,
20 stmt_num NUMBER
21 )
22 IS
23 err_text VARCHAR2(2000);
24 BEGIN
25 err_text := func_name || '(' || stmt_num || ')' || SQLERRM;
26 FND_MESSAGE.SET_NAME('BOM', 'BOM_SQL_ERR');
27 FND_MESSAGE.SET_TOKEN('ENTITY', err_text);
28 APP_EXCEPTION.RAISE_EXCEPTION;
29 /*EXCEPTION
30 WHEN OTHERS THEN
31 NULL;*/ -- BUG 4919190
32 END RAISE_REVISION_ERROR;
33
34 /* --------------------------- RAISE_NO_REV_ERROR ------------------------
35 NAME
36 RAISE_NO_REV_ERROR - raises generic error message
37 DESCRIPTION
38 for sql error failures, places the SQLERRM error on the message stack
39
40 REQUIRES
41 org_id organization_id
42 part_id item id
43 rev_date revision date
44
45 OUTPUT
46
47 NOTES
48 ---------------------------------------------------------------------------*/
49 PROCEDURE RAISE_NO_REV_ERROR (
50 org_id NUMBER,
51 part_id NUMBER,
52 rev_date DATE
53 ) IS
54 part_number VARCHAR2(40);
55 BEGIN
56 SELECT substrb(ITEM_NUMBER, 1, 40)
57 INTO part_number
58 FROM MTL_ITEM_FLEXFIELDS
59 WHERE ORGANIZATION_ID = org_id
60 AND INVENTORY_ITEM_ID = part_id;
61
62 /*
63 ** return message that is no valid rev for item
64 ** Name: BOM_GET_REV
65 ** EFF_DATE: rev_date
66 ** ITEM_NUMBER: select from mtl_item_flexfields
67 */
68
69 FND_MESSAGE.SET_NAME('BOM', 'BOM_GET_REV');
70 FND_MESSAGE.SET_TOKEN('ITEM_NUMBER', part_number);
71 FND_MESSAGE.SET_TOKEN('EFF_DATE', fnd_date.date_to_displaydt(rev_date));
72 APP_EXCEPTION.RAISE_EXCEPTION;
73
74 EXCEPTION
75 WHEN NO_DATA_FOUND THEN
76 RAISE_REVISION_ERROR (func_name => 'RAISE_NO_REV_ERROR',
77 stmt_num => 1);
78
79 END RAISE_NO_REV_ERROR;
80
81 /* ------------------------------ GET_REVISION_DETAILS --------------------------
82 NAME
83 GET_REVISION_DETAILS - retrieve item revision,revision label,revision id for a date
84 DESCRIPTION
85 retrieve teh current revision for teh given date
86
87 REQUIRES
88 type "PART" - item revision
89 "PROCESS" - routing revision
90 eco_status "ALL" - all ECOs
91 "EXCLUDE_HOLD" - exclude pending revisions from ECOs
92 with HOLD status
93 "EXCLUDE_OPEN_HOLD" - exclude pending revisions from ECOs
94 with HOLD or OPEN status
95 "EXCLUDE_ALL" - Exclude all revisions except the Implemented
96 examine_type "ALL" - all revisions
97 "IMPL_ONLY" - only implemented revisions
98 "PEND_ONLY" - only unimplemented revisions
99 org_id organization id
100 item_id item id
101 rev_date date for which revision desired
102 OUTPUT
103 itm_rev revision
104 itm_rev_label revision label
105 itm_rev_id revision id
106 RETURNS
107
108 NOTES
109 ---------------------------------------------------------------------------*/
110 PROCEDURE GET_REVISION_DETAILS(
111 eco_status IN VARCHAR2 DEFAULT 'ALL',
112 examine_type IN VARCHAR2 DEFAULT 'ALL',
113 org_id IN NUMBER,
114 item_id IN NUMBER,
115 rev_date IN DATE,
116 itm_rev IN OUT NOCOPY VARCHAR2,
117 itm_rev_label IN OUT NOCOPY VARCHAR2,
118 itm_rev_id IN OUT NOCOPY NUMBER
119 )
120 IS
121 stmt_num NUMBER;
122
123 CURSOR ECO_STATUS_ITEM_REV IS
124 SELECT REVISION,REVISION_LABEL,REVISION_ID
125 FROM MTL_ITEM_REVISIONS_B MIR, ENG_REVISED_ITEMS ERI
126 WHERE MIR.INVENTORY_ITEM_ID = item_id
127 AND MIR.ORGANIZATION_ID = org_id
128 AND MIR.EFFECTIVITY_DATE <= rev_date --Bug 3020310
129 AND MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
130 AND (
131 (eco_status = 'EXCLUDE_HOLD'
132 AND NVL(ERI.STATUS_TYPE,0) NOT IN (2)
133 )
134 OR
135 (eco_status = 'EXCLUDE_OPEN_HOLD'
136 AND NVL(ERI.STATUS_TYPE,0) NOT IN (1,2)
137 )
138 OR
139 (eco_status = 'EXCLUDE_ALL'
140 AND NVL(ERI.STATUS_TYPE,0) IN (0,6)
141 )
142 )
143 ORDER BY MIR.EFFECTIVITY_DATE DESC, MIR.REVISION DESC;
144
145 CURSOR NO_ECO_ITEM_REV IS
146 SELECT REVISION,REVISION_LABEL,REVISION_ID
147 FROM MTL_ITEM_REVISIONS_B MIR
148 WHERE INVENTORY_ITEM_ID = item_id
149 AND ORGANIZATION_ID = org_id
150 AND MIR.EFFECTIVITY_DATE <= rev_date --Bug 3020310
151 AND ( (examine_type = 'ALL')
152 OR
153 (examine_type = 'IMPL_ONLY'
154 AND IMPLEMENTATION_DATE IS NOT NULL
155 )
156 OR
157 (examine_type = 'PEND_ONLY'
158 AND IMPLEMENTATION_DATE IS NULL
159 )
160 )
161 ORDER BY EFFECTIVITY_DATE DESC, REVISION DESC;
162
163 BEGIN
164 IF (eco_status = 'EXCLUDE_HOLD' OR eco_status = 'EXCLUDE_OPEN_HOLD'
165 OR eco_status = 'EXCLUDE_ALL' ) THEN -- Bug #4038025
166 OPEN ECO_STATUS_ITEM_REV;
167 stmt_num := 1;
168
169 FETCH ECO_STATUS_ITEM_REV INTO itm_rev,itm_rev_label,itm_rev_id;
170
171 CLOSE ECO_STATUS_ITEM_REV;
172
173 ELSE
174 OPEN NO_ECO_ITEM_REV;
175 stmt_num := 2;
176
177 FETCH NO_ECO_ITEM_REV INTO itm_rev,itm_rev_label,itm_rev_id;
178
179 CLOSE NO_ECO_ITEM_REV;
180
181 END IF;
182 EXCEPTION
183 WHEN OTHERS THEN
184 NULL;
185
186 END GET_REVISION_DETAILS;
187
188 /* ------------------------------ GET_ITEM_REVISION_LABEL_FN --------------------------
189 NAME
190 GET_ITEM_REVISION_LABEL_FN - retrieve item revision for a date
191 DESCRIPTION
192 retrieve teh current revision for teh given date
193
194 REQUIRES
195 type "PART" - item revision
196 "PROCESS" - routing revision
197 eco_status "ALL" - all ECOs
198 "EXCLUDE_HOLD" - exclude pending revisions from ECOs
199 with HOLD status
200 "EXCLUDE_OPEN_HOLD" - exclude pending revisions from ECOs
201 with HOLD or OPEN status
202 "EXCLUDE_ALL" - Exclude all revisions except the Implemented
203 examine_type "ALL" - all revisions
204 "IMPL_ONLY" - only implemented revisions
205 "PEND_ONLY" - only unimplemented revisions
206 org_id organization id
207 item_id item id
208 rev_date date for which revision desired
209 OUTPUT
210 itm_rev_label revision label
211 RETURNS
212
213 NOTES
214 ---------------------------------------------------------------------------*/
215
216
217 FUNCTION GET_ITEM_REVISION_LABEL_FN(
218 eco_status IN VARCHAR2 DEFAULT 'ALL',
219 examine_type IN VARCHAR2 DEFAULT 'ALL',
220 org_id IN NUMBER,
221 item_id IN NUMBER,
222 rev_date IN DATE
223 )
224 RETURN VARCHAR2 IS
225 itm_rev VARCHAR2(3);
226 itm_rev_label VARCHAR2(80);
227 itm_rev_id NUMBER;
228 BEGIN
229 GET_REVISION_DETAILS(
230 eco_status => eco_status,
231 examine_type => examine_type,
232 org_id => org_id,
233 item_id => item_id,
234 rev_date => rev_date,
235 itm_rev => itm_rev,
236 itm_rev_label => itm_rev_label ,
237 itm_rev_id => itm_rev_id
238 );
239
240 RETURN itm_rev_label;
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 RETURN NULL;
245
246 END GET_ITEM_REVISION_LABEL_FN;
247
248
249
250 /* ------------------------------ GET_ITEM_REVISION --------------------------
251 NAME
252 GET_ITEM_REVISION - retrieve item revision for a date
253 DESCRIPTION
254 retrieve teh current revision for teh given date
255
256 REQUIRES
257 type "PART" - item revision
258 "PROCESS" - routing revision
259 eco_status "ALL" - all ECOs
260 "EXCLUDE_HOLD" - exclude pending revisions from ECOs
261 with HOLD status
262 "EXCLUDE_OPEN_HOLD" - exclude pending revisions from ECOs
263 with HOLD or OPEN status
264 "EXCLUDE_ALL" - Exclude all revisions except the Implemented
265 examine_type "ALL" - all revisions
266 "IMPL_ONLY" - only implemented revisions
267 "PEND_ONLY" - only unimplemented revisions
268 org_id organization id
269 item_id item id
270 rev_date date for which revision desired
271 OUTPUT
272 itm_rev revision
273 RETURNS
274
275 NOTES
276 ---------------------------------------------------------------------------*/
277 PROCEDURE GET_ITEM_REVISION(
278 eco_status IN VARCHAR2 DEFAULT 'ALL',
279 examine_type IN VARCHAR2 DEFAULT 'ALL',
280 org_id IN NUMBER,
281 item_id IN NUMBER,
282 rev_date IN DATE,
283 itm_rev IN OUT NOCOPY VARCHAR2
284 )
285 IS
286 stmt_num NUMBER;
287
288 CURSOR ECO_STATUS_ITEM_REV IS
289 SELECT REVISION
290 FROM MTL_ITEM_REVISIONS_B MIR, ENG_REVISED_ITEMS ERI
291 WHERE MIR.INVENTORY_ITEM_ID = item_id
292 AND MIR.ORGANIZATION_ID = org_id
293 AND MIR.EFFECTIVITY_DATE <= rev_date --Bug 3020310
294 AND MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
295 AND (
296 (eco_status = 'EXCLUDE_HOLD'
297 AND NVL(ERI.STATUS_TYPE,0) NOT IN (2)
298 )
299 OR
300 (eco_status = 'EXCLUDE_OPEN_HOLD'
301 AND NVL(ERI.STATUS_TYPE,0) NOT IN (1,2)
302 )
303 OR
304 (eco_status = 'EXCLUDE_ALL'
305 AND NVL(ERI.STATUS_TYPE,0) IN (0,6)
306 )
307 )
308 ORDER BY MIR.EFFECTIVITY_DATE DESC, MIR.REVISION DESC;
309
310 CURSOR NO_ECO_ITEM_REV IS
311 SELECT REVISION
312 FROM MTL_ITEM_REVISIONS_B MIR
313 WHERE INVENTORY_ITEM_ID = item_id
314 AND ORGANIZATION_ID = org_id
315 AND MIR.EFFECTIVITY_DATE <= rev_date --Bug 3020310
316 AND ( (examine_type = 'ALL')
317 OR
318 (examine_type = 'IMPL_ONLY'
319 AND IMPLEMENTATION_DATE IS NOT NULL
320 )
321 OR
322 (examine_type = 'PEND_ONLY'
323 AND IMPLEMENTATION_DATE IS NULL
324 )
325 )
326 ORDER BY EFFECTIVITY_DATE DESC, REVISION DESC;
327
328 BEGIN
329 IF (eco_status = 'EXCLUDE_HOLD' OR eco_status = 'EXCLUDE_OPEN_HOLD') THEN
330 OPEN ECO_STATUS_ITEM_REV;
331 stmt_num := 1;
332
333 FETCH ECO_STATUS_ITEM_REV INTO itm_rev;
334
335 IF ECO_STATUS_ITEM_REV%NOTFOUND THEN
336 CLOSE ECO_STATUS_ITEM_REV;
337 RAISE_NO_REV_ERROR (
338 org_id => org_id,
339 part_id => item_id,
340 rev_date => rev_date);
341 END IF;
342 CLOSE ECO_STATUS_ITEM_REV;
343
344 ELSE
345 OPEN NO_ECO_ITEM_REV;
346 stmt_num := 2;
347 FETCH NO_ECO_ITEM_REV INTO itm_rev;
348
349 IF NO_ECO_ITEM_REV%NOTFOUND THEN
350 CLOSE NO_ECO_ITEM_REV;
351 RAISE_NO_REV_ERROR (
352 org_id => org_id,
353 part_id => item_id,
354 rev_date => rev_date);
355 END IF;
356 CLOSE NO_ECO_ITEM_REV;
357
358 END IF;
359
360 END GET_ITEM_REVISION;
361
362 /* ------------------------------ GET_ITEM_REVISION_FN --------------------------
363 NAME
364 GET_ITEM_REVISION_FN - retrieve item revision for a date , if no revision defined, return null instead
365 DESCRIPTION
366 retrieve teh current revision for teh given date
367
368 REQUIRES
369 type "PART" - item revision
370 "PROCESS" - routing revision
371 eco_status "ALL" - all ECOs
372 "EXCLUDE_HOLD" - exclude pending revisions from ECOs
373 with HOLD status
374 "EXCLUDE_OPEN_HOLD" - exclude pending revisions from ECOs
375 with HOLD or OPEN status
376 examine_type "ALL" - all revisions
377 "IMPL_ONLY" - only implemented revisions
378 "PEND_ONLY" - only unimplemented revisions
379 org_id organization id
380 item_id item id
381 rev_date date for which revision desired
382 OUTPUT
383 itm_rev revision
384 RETURNS
385
386 NOTES
387 ---------------------------------------------------------------------------*/
388 FUNCTION GET_ITEM_REVISION_FN(
389 eco_status IN VARCHAR2 DEFAULT 'ALL',
390 examine_type IN VARCHAR2 DEFAULT 'ALL',
391 org_id IN NUMBER,
392 item_id IN NUMBER,
393 rev_date IN DATE
394 )
395 RETURN VARCHAR2 IS
396 stmt_num NUMBER;
397 itm_rev VARCHAR2(3);
398
399 CURSOR ECO_STATUS_ITEM_REV IS
400 SELECT REVISION
401 FROM MTL_ITEM_REVISIONS_B MIR, ENG_REVISED_ITEMS ERI
402 WHERE MIR.INVENTORY_ITEM_ID = item_id
403 AND MIR.ORGANIZATION_ID = org_id
404 AND MIR.EFFECTIVITY_DATE <= rev_date
405 AND MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
406 AND (
407 (eco_status = 'EXCLUDE_HOLD'
408 AND NVL(ERI.STATUS_TYPE,0) NOT IN (2)
409 )
410 OR
411 (eco_status = 'EXCLUDE_OPEN_HOLD'
412 AND NVL(ERI.STATUS_TYPE,0) NOT IN (1,2)
413 )
414 )
415 ORDER BY MIR.EFFECTIVITY_DATE DESC, MIR.REVISION DESC;
416
417 CURSOR NO_ECO_ITEM_REV IS
418 SELECT REVISION
419 FROM MTL_ITEM_REVISIONS_B MIR
420 WHERE INVENTORY_ITEM_ID = item_id
421 AND ORGANIZATION_ID = org_id
422 AND MIR.EFFECTIVITY_DATE <= rev_date
423 AND ( (examine_type = 'ALL')
424 OR
425 (examine_type = 'IMPL_ONLY'
426 AND IMPLEMENTATION_DATE IS NOT NULL
427 )
428 OR
429 (examine_type = 'PEND_ONLY'
430 AND IMPLEMENTATION_DATE IS NULL
431 )
432 )
433 ORDER BY EFFECTIVITY_DATE DESC, REVISION DESC;
434
435 BEGIN
436 IF (eco_status = 'EXCLUDE_HOLD' OR eco_status = 'EXCLUDE_OPEN_HOLD'
437 OR eco_status = 'EXCLUDE_ALL') THEN
438 OPEN ECO_STATUS_ITEM_REV;
439 stmt_num := 1;
440
441 FETCH ECO_STATUS_ITEM_REV INTO itm_rev;
442 IF ECO_STATUS_ITEM_REV%NOTFOUND THEN
443 CLOSE ECO_STATUS_ITEM_REV;
444 RETURN NULL;
445 END IF;
446 CLOSE ECO_STATUS_ITEM_REV;
447 RETURN itm_rev;
448 ELSE
449 OPEN NO_ECO_ITEM_REV;
450 stmt_num := 2;
451 FETCH NO_ECO_ITEM_REV INTO itm_rev;
452
453 IF NO_ECO_ITEM_REV%NOTFOUND THEN
454 CLOSE NO_ECO_ITEM_REV;
455 RETURN NULL;
456 END IF;
457 CLOSE NO_ECO_ITEM_REV;
458 RETURN itm_rev;
459 END IF;
460
461 END GET_ITEM_REVISION_FN;
462
463 /* ------------------------------ GET_ITEM_REVISION_ID_FN --------------------------
464 NAME
465 GET_ITEM_REVISION_FN - retrieve item revision for a date , if no revision defined, return null instead
466 DESCRIPTION
467 retrieve teh current revision for teh given date
468
469 REQUIRES
470 type "PART" - item revision
471 "PROCESS" - routing revision
472 eco_status "ALL" - all ECOs
473 "EXCLUDE_HOLD" - exclude pending revisions from ECOs
474 with HOLD status
475 "EXCLUDE_OPEN_HOLD" - exclude pending revisions from ECOs
476 with HOLD or OPEN status
477 examine_type "ALL" - all revisions
478 "IMPL_ONLY" - only implemented revisions
479 "PEND_ONLY" - only unimplemented revisions
480 org_id organization id
481 item_id item id
482 rev_date date for which revision desired
483 OUTPUT
484 itm_rev revision_id
485 RETURNS
486
487 NOTES
488 ---------------------------------------------------------------------------*/
489 FUNCTION GET_ITEM_REVISION_ID_FN(
490 eco_status IN VARCHAR2 DEFAULT 'ALL',
491 examine_type IN VARCHAR2 DEFAULT 'ALL',
492 org_id IN NUMBER,
493 item_id IN NUMBER,
494 rev_date IN DATE
495 )
496 RETURN NUMBER IS
497 stmt_num NUMBER;
498 itm_rev VARCHAR2(3);
499 revision_id NUMBER;
500
501 CURSOR ECO_STATUS_ITEM_REV IS
502 SELECT REVISION_ID
503 FROM MTL_ITEM_REVISIONS_B MIR, ENG_REVISED_ITEMS ERI
504 WHERE MIR.INVENTORY_ITEM_ID = item_id
505 AND MIR.ORGANIZATION_ID = org_id
506 AND MIR.EFFECTIVITY_DATE <= rev_date
507 AND MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
508 AND (
509 (eco_status = 'EXCLUDE_HOLD'
510 AND NVL(ERI.STATUS_TYPE,0) NOT IN (2)
511 )
512 OR
513 (eco_status = 'EXCLUDE_OPEN_HOLD'
514 AND NVL(ERI.STATUS_TYPE,0) NOT IN (1,2)
515 )
516 )
517 ORDER BY MIR.EFFECTIVITY_DATE DESC, MIR.REVISION DESC;
518
519 CURSOR NO_ECO_ITEM_REV IS
520 SELECT REVISION_ID
521 FROM MTL_ITEM_REVISIONS_B MIR
522 WHERE INVENTORY_ITEM_ID = item_id
523 AND ORGANIZATION_ID = org_id
524 AND MIR.EFFECTIVITY_DATE <= rev_date
525 AND ( (examine_type = 'ALL')
526 OR
527 (examine_type = 'IMPL_ONLY'
528 AND IMPLEMENTATION_DATE IS NOT NULL
529 )
530 OR
531 (examine_type = 'PEND_ONLY'
532 AND IMPLEMENTATION_DATE IS NULL
533 )
534 )
535 ORDER BY EFFECTIVITY_DATE DESC, REVISION DESC;
536
537 BEGIN
538 IF (eco_status = 'EXCLUDE_HOLD' OR eco_status = 'EXCLUDE_OPEN_HOLD') THEN
539 OPEN ECO_STATUS_ITEM_REV;
540 stmt_num := 1;
541
542 FETCH ECO_STATUS_ITEM_REV INTO revision_id;
543 IF ECO_STATUS_ITEM_REV%NOTFOUND THEN
544 CLOSE ECO_STATUS_ITEM_REV;
545 RETURN NULL;
546 END IF;
547 CLOSE ECO_STATUS_ITEM_REV;
548 RETURN revision_id;
549 ELSE
550 OPEN NO_ECO_ITEM_REV;
551 stmt_num := 2;
552 FETCH NO_ECO_ITEM_REV INTO revision_id;
553
554 IF NO_ECO_ITEM_REV%NOTFOUND THEN
555 CLOSE NO_ECO_ITEM_REV;
556 RETURN NULL;
557 END IF;
558 CLOSE NO_ECO_ITEM_REV;
559 RETURN revision_id;
560 END IF;
561
562 END GET_ITEM_REVISION_ID_FN;
563
564 /* --------------------------- GET_ROUTING_REVISION ------------------------
565 NAME
566 GET_ROUTING_REVISION - retrieve routing revision for a date
567 DESCRIPTION
568 retrieve teh current revision for teh given date
569
570 REQUIRES
571 org_id organization id
572 item_id item id
573 rev_date date for which revision desired
574 OUTPUT
575 itm_rev revision
576 RETURNS
577
578 NOTES
579 ---------------------------------------------------------------------------*/
580 PROCEDURE GET_ROUTING_REVISION(
581 eco_status IN VARCHAR2 DEFAULT 'ALL', -- BUG 3940863
582 org_id IN NUMBER,
583 item_id IN NUMBER,
584 rev_date IN DATE,
585 itm_rev IN OUT NOCOPY VARCHAR2,
586 examine_type IN VARCHAR2 DEFAULT 'ALL' -- BUG 3779027
587 )
588 IS
589
590 stmt_num NUMBER;
591
592 -- Added Cursor for BUG 3940863
593 CURSOR ECO_STATUS_RTG_REV IS
594 SELECT PROCESS_REVISION
595 FROM MTL_RTG_ITEM_REVISIONS MIR, ENG_REVISED_ITEMS ERI
596 WHERE MIR.INVENTORY_ITEM_ID = item_id
597 AND MIR.ORGANIZATION_ID = org_id
598 AND MIR.EFFECTIVITY_DATE <= rev_date --Bug 3020310
599 AND MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
600 AND (
601 (eco_status = 'EXCLUDE_HOLD'
602 AND NVL(ERI.STATUS_TYPE,0) NOT IN (2)
603 )
604 OR
605 (eco_status = 'EXCLUDE_OPEN_HOLD'
606 AND NVL(ERI.STATUS_TYPE,0) NOT IN (1,2)
607 )
608 OR -- BUG 4127493
609 (eco_status = 'EXCLUDE_ALL'
610 AND NVL(ERI.STATUS_TYPE,0) IN (0,6)
611 )
612 )
613 ORDER BY MIR.EFFECTIVITY_DATE DESC, MIR.PROCESS_REVISION DESC;
614
615
616 CURSOR RTG_REV IS
617 SELECT PROCESS_REVISION
618 FROM MTL_RTG_ITEM_REVISIONS
619 WHERE INVENTORY_ITEM_ID = item_id
620 AND ORGANIZATION_ID = org_id
621 -- AND trunc(EFFECTIVITY_DATE) <= trunc(rev_date) -- changed for bug 2631052
622 AND EFFECTIVITY_DATE <= rev_date
623 AND ( (examine_type = 'ALL') -- BUG 3779027
624 OR
625 (examine_type = 'IMPL_ONLY'
626 AND IMPLEMENTATION_DATE IS NOT NULL
627 )
628 OR
629 (examine_type = 'PEND_ONLY'
630 AND IMPLEMENTATION_DATE IS NULL
631 )
632 )
633 ORDER BY EFFECTIVITY_DATE DESC, PROCESS_REVISION DESC;
634
635 BEGIN
636
637 -- Added IF conditions for BUG 3940863
638 IF (eco_status = 'EXCLUDE_HOLD' OR eco_status = 'EXCLUDE_OPEN_HOLD'
639 OR eco_status = 'EXCLUDE_ALL') THEN -- BUG 4127493
640 OPEN ECO_STATUS_RTG_REV;
641 stmt_num := 1;
642 FETCH ECO_STATUS_RTG_REV INTO itm_rev;
643 IF ECO_STATUS_RTG_REV%NOTFOUND THEN
644 CLOSE ECO_STATUS_RTG_REV;
645 RAISE_NO_REV_ERROR (
646 org_id => org_id,
647 part_id => item_id,
648 rev_date => rev_date);
649 END IF;
650 CLOSE ECO_STATUS_RTG_REV;
651 ELSE
652 OPEN RTG_REV;
653 stmt_num := 2;
654 FETCH RTG_REV INTO itm_rev;
655 IF RTG_REV%NOTFOUND THEN
656 CLOSE RTG_REV;
657 RAISE_NO_REV_ERROR (
658 org_id => org_id,
659 part_id => item_id,
660 rev_date => rev_date);
661 END IF;
662 CLOSE RTG_REV;
663 END IF;
664
665
666 END GET_ROUTING_REVISION;
667
668 /* ------------------------------- GET_REVISION ---- ------------------------
669 NAME
670 GET_REVISION - retrieve item/routing revision for a date
671 DESCRIPTION
672 retrieve teh current revision for teh given date
673
674 REQUIRES
675 type "PART" - item revision
676 "PROCESS" - routing revision
677 eco_status "ALL" - all ECOs
678 "EXCLUDE_HOLD" - exclude pending revisions from ECOs
679 with HOLD status
680 "EXCLUDE_OPEN_HOLD" - exclude pending revisions from ECOs
681 with HOLD or OPEN status
682 "EXCLUDE_ALL" - Exclude all revisions except the Implemented
683 examine_type "ALL" - all revisions
684 "IMPL_ONLY" - only implemented revisions
685 "PEND_ONLY" - only unimplemented revisions
686 org_id organization id
687 item_id item id
688 rev_date date for which revision desired
689 OUTPUT
690 itm_rev revision
691 RETURNS
692
693 NOTES
694 ---------------------------------------------------------------------------*/
695 PROCEDURE GET_REVISION(
696 type IN VARCHAR2 DEFAULT 'PART',
697 eco_status IN VARCHAR2 DEFAULT 'ALL',
698 examine_type IN VARCHAR2 DEFAULT 'ALL',
699 org_id IN NUMBER,
700 item_id IN NUMBER,
701 rev_date IN DATE,
702 itm_rev IN OUT NOCOPY VARCHAR2
703 )
704 IS
705 BEGIN
706 IF (type = 'PART') THEN
707 GET_ITEM_REVISION (
708 eco_status,
709 examine_type,
710 org_id,
711 item_id,
712 rev_date,
713 itm_rev);
714 ELSE
715 GET_ROUTING_REVISION (
716 eco_status, -- BUG 3940863
717 org_id,
718 item_id,
719 rev_date,
720 itm_rev,
721 examine_type -- BUG 3779027
722 );
723 END IF;
724 END GET_REVISION;
725
726
727 /* ------------------------------- COMPARE_REVISION ---------------------------
728 NAME
729 COMPARE_REVISION - compare 2 revisions
730 DESCRIPTION
731 compare 2 revisions
732
733 REQUIRES
734 rev1 revision 1
735 rev2 revision 2
736 OUTPUT
737 RETURNS
738 0 if rev1 = rev2
739 1 if rev1 > rev2
740 2 if rev1 < rev2
741 NOTES
742 ---------------------------------------------------------------------------*/
743 FUNCTION COMPARE_REVISION(
744 rev1 IN VARCHAR2,
745 rev2 IN VARCHAR2
746 ) RETURN INTEGER
747 IS
748 BEGIN
749 IF ((rev1 is NULL OR rev1 = '') and
750 (rev2 is NULL OR rev2 = '')) THEN
751 return(0);
752 END IF;
753
754 IF rev1 = rev2 THEN
755 RETURN (0);
756 END IF;
757
758 IF (rev1 IS NULL OR rev1 = FND_API.G_MISS_CHAR) THEN
759 RETURN(2);
760 ELSIF (rev2 IS NULL OR rev2 = FND_API.G_MISS_CHAR) THEN
761 RETURN(1);
762 ELSIF (rev1 > rev2) THEN
763 RETURN(2);
764 ELSE
765 RETURN(1);
766 END IF;
767
768 END COMPARE_REVISION;
769
770 /* ------------------------------- GET_REV_DATE ---- ------------------------
771 NAME
772 GET_REV_DATE - retrieve date for given revision
773 DESCRIPTION
774 retrieve revision start date for given revision
775
776 REQUIRES
777 type "PART" - item revision
778 "PROCESS" - routing revision
779 org_id organization id
780 item_id item id
781 itm_rev revision
782 OUTPUT
783 rev_date effecitive date of revision
784 RETURNS
785
786 NOTES
787 ---------------------------------------------------------------------------*/
788 PROCEDURE GET_REV_DATE (
789 type IN VARCHAR2 DEFAULT 'PART',
790 org_id IN NUMBER,
791 item_id IN NUMBER,
792 itm_rev IN VARCHAR2,
793 rev_date IN OUT NOCOPY DATE
794 )
795 IS
796 CURSOR ITEM_REV IS
797 SELECT EFFECTIVITY_DATE
798 FROM MTL_ITEM_REVISIONS_B
799 WHERE INVENTORY_ITEM_ID = item_id
800 AND REVISION = itm_rev
801 AND ORGANIZATION_ID = org_id;
802
803 CURSOR RTG_REV IS
804 SELECT EFFECTIVITY_DATE
805 FROM MTL_RTG_ITEM_REVISIONS
806 WHERE INVENTORY_ITEM_ID = item_id
807 AND PROCESS_REVISION = itm_rev
808 AND ORGANIZATION_ID = org_id;
809
810 BEGIN
811 IF (type = 'PART') THEN
812 OPEN ITEM_REV;
813 FETCH ITEM_REV INTO rev_date;
814 IF (ITEM_REV%NOTFOUND) THEN
815 CLOSE ITEM_REV;
816 FND_MESSAGE.SET_NAME('BOM', 'BOM_GET_REVDATE');
817 FND_MESSAGE.SET_TOKEN('REVISION', itm_rev);
818 APP_EXCEPTION.RAISE_EXCEPTION;
819 END IF;
820 CLOSE ITEM_REV;
821 ELSE /* IF (type = PROCESS) THEN */
822 OPEN RTG_REV;
823 FETCH RTG_REV INTO rev_date;
824 IF (ITEM_REV%NOTFOUND) THEN
825 CLOSE RTG_REV;
826 FND_MESSAGE.SET_NAME('BOM', 'BOM_GET_REVDATE');
827 FND_MESSAGE.SET_TOKEN('REVISION', itm_rev);
828 APP_EXCEPTION.RAISE_EXCEPTION;
829 END IF;
830 CLOSE RTG_REV;
831 END IF;
832
833 EXCEPTION
834 WHEN OTHERS THEN
835 RAISE_REVISION_ERROR (
836 func_name => 'GET_REV_DATE',
837 stmt_num => 1);
838 END GET_REV_DATE;
839
840 /* ------------------------------- GET_HIGH_DATE ---- ------------------------
841 NAME
842 GET_HIGH_DATE - retreive the high date of the revision
843 DESCRIPTION
844 retrieve the high date of the revision. For the greatest rev, high
845 date is greater of sysdate, effective_date for the revision
846
847 REQUIRES
848 type "PART" - item revision
849 "PROCESS" - routing revision
850 org_id organization id
851 item_id item id
852 eco_status "ALL" - all ECOs
853 "EXCLUDE_HOLD" - exclude pending revisions from ECOs
854 with HOLD status
855 "EXCLUDE_OPEN_HOLD" - exclude pending revisions from ECOs
856 with HOLD or OPEN status
857 "EXCLUDE_ALL" - Exclude all revisions except the Implemented
858 OUTPUT
859 itm_rev revision
860 rev_date high date
861 RETURNS
862
863 NOTES
864 ---------------------------------------------------------------------------*/
865 PROCEDURE GET_HIGH_DATE (
866 type IN VARCHAR2 DEFAULT 'PART',
867 org_id IN NUMBER,
868 item_id IN NUMBER,
869 eco_status IN VARCHAR2,
870 itm_rev IN VARCHAR2,
871 rev_date IN OUT NOCOPY DATE
872 )
873 IS
874 stmt_num INTEGER;
875 l_rev_date DATE;
876 l_item_name MTL_SYSTEM_ITEMS_VL.CONCATENATED_SEGMENTS%TYPE;
877
878 BEGIN
879 IF (type = 'PART') THEN
880 IF (eco_status = 'EXCLUDE_HOLD' OR
881 eco_status = 'EXCLUDE_OPEN_HOLD' OR
882 eco_status = 'EXCLUDE_ALL') THEN
883 stmt_num := 1;
884 SELECT MIN(A.EFFECTIVITY_DATE - 60/(60*60*24))
885 INTO l_rev_date
886 FROM MTL_ITEM_REVISIONS_B A
887 WHERE A.INVENTORY_ITEM_ID = item_id
888 AND A.ORGANIZATION_ID = org_id
889 AND A.EFFECTIVITY_DATE >
890 (SELECT EFFECTIVITY_DATE
891 FROM MTL_ITEM_REVISIONS_B
892 WHERE INVENTORY_ITEM_ID = item_id
893 AND ORGANIZATION_ID = org_id
894 AND REVISION = itm_rev
895 )
896 AND NOT EXISTS
897 ( SELECT 'X'
898 FROM ENG_REVISED_ITEMS B
899 WHERE A.REVISED_ITEM_SEQUENCE_ID =
900 B.REVISED_ITEM_SEQUENCE_ID
901 AND
902 (
903 (eco_status = 'EXCLUDE_HOLD'
904 AND B.STATUS_TYPE = 2
905 )
906 OR
907 (eco_status = 'EXCLUDE_OPEN_HOLD'
908 AND B.STATUS_TYPE IN (1,2)
909 )
910 OR
911 (eco_status = 'EXCLUDE_ALL'
912 AND B.STATUS_TYPE = 6
913 )
914 )
915 );
916 ELSE
917 stmt_num := 2;
918 SELECT MIN(EFFECTIVITY_DATE - 60/(60*60*24))
919 INTO l_rev_date
920 FROM MTL_ITEM_REVISIONS_B
921 WHERE INVENTORY_ITEM_ID = item_id
922 AND ORGANIZATION_ID = org_id
923 AND EFFECTIVITY_DATE >
924 (SELECT EFFECTIVITY_DATE
925 FROM MTL_ITEM_REVISIONS_B
926 WHERE INVENTORY_ITEM_ID = item_id
927 AND ORGANIZATION_ID = org_id
928 AND REVISION = itm_rev
929 );
930 END IF;
931 ELSE
932 stmt_num := 3;
933 SELECT MIN(EFFECTIVITY_DATE - 1/(60*60*24))
934 INTO l_rev_date
935 FROM MTL_RTG_ITEM_REVISIONS
936 WHERE INVENTORY_ITEM_ID = item_id
937 AND ORGANIZATION_ID = org_id
938 AND EFFECTIVITY_DATE >
939 (SELECT EFFECTIVITY_DATE
940 FROM MTL_RTG_ITEM_REVISIONS
941 WHERE INVENTORY_ITEM_ID = item_id
942 AND ORGANIZATION_ID = org_id
943 AND PROCESS_REVISION = itm_rev);
944 /*
945 SELECT MIN(EFFECTIVITY_DATE - 60/(60*60*24)) -- changed for bug 2631052
946 INTO l_rev_date
947 FROM MTL_RTG_ITEM_REVISIONS
948 WHERE INVENTORY_ITEM_ID = item_id
949 AND ORGANIZATION_ID = org_id
950 AND trunc(EFFECTIVITY_DATE) >
951 (SELECT trunc(EFFECTIVITY_DATE)
952 FROM MTL_RTG_ITEM_REVISIONS
953 WHERE INVENTORY_ITEM_ID = item_id
954 AND ORGANIZATION_ID = org_id
955 AND PROCESS_REVISION = itm_rev);
956 */
957 END IF;
958
959 IF l_rev_date is NULL THEN
960 /*
961 ** implies that rev is the last rev. So, if today < eff_date, then
962 ** return eff_date, else return today
963 */
964
965 IF (type = 'PART') THEN
966 stmt_num := 4;
967 SELECT GREATEST(EFFECTIVITY_DATE,SYSDATE)
968 INTO l_rev_date
969 FROM MTL_ITEM_REVISIONS_B
970 WHERE INVENTORY_ITEM_ID = item_id
971 AND ORGANIZATION_ID = org_id
972 AND REVISION = itm_rev;
973 ELSE
974 stmt_num := 5;
975 SELECT GREATEST(EFFECTIVITY_DATE,SYSDATE)
976 INTO l_rev_date
977 FROM MTL_RTG_ITEM_REVISIONS
978 WHERE INVENTORY_ITEM_ID = item_id
979 AND ORGANIZATION_ID = org_id
980 AND PROCESS_REVISION = itm_rev;
981 END IF;
982 END IF;
983
984 rev_date := l_rev_date;
985
986 EXCEPTION
987 WHEN NO_DATA_FOUND THEN
988 /*
989 ** display message to say item rev not valid
990 ** Name: MFG_NOT_VALID
991 ** ENTITY: itm_rev
992 FND_MESSAGE.SET_NAME('INV', 'INV_NOT_VALID');
993 FND_MESSAGE.SET_TOKEN('ENTITY', itm_rev);
994 APP_EXCEPTION.RAISE_EXCEPTION;
995 */
996 -- bug:2120090 Raise meaningful error as Revision does not exist.
997 -- Get the Item Name from Id
998 SELECT msivl.CONCATENATED_SEGMENTS
999 INTO l_item_name
1000 FROM MTL_SYSTEM_ITEMS_VL msivl
1001 WHERE msivl.INVENTORY_ITEM_ID = item_id
1002 AND msivl.ORGANIZATION_ID = org_id;
1003
1004 FND_MESSAGE.SET_NAME('BOM', 'BOM_REVISION_DOESNOT_EXIST');
1005 FND_MESSAGE.SET_TOKEN('REVISION', itm_rev);
1006 FND_MESSAGE.SET_TOKEN('ASSEMBLY_ITEM_NAME', l_item_name);
1007
1008 APP_EXCEPTION.RAISE_EXCEPTION;
1009
1010 WHEN OTHERS THEN
1011 RAISE_REVISION_ERROR (
1012 func_name => 'GET_HIGH_DATE',
1013 stmt_num => stmt_num);
1014 END GET_HIGH_DATE;
1015
1016 /* ---------------------------- GET_HIGH_REV_DATE ---- ------------------------
1017 NAME
1018 GET_HIGH_REV_DATE - retrieve highest rev and its high date
1019 DESCRIPTION
1020 retrievehighest revsion adn its high date
1021
1022 REQUIRES
1023 type "PART" - item revision
1024 "PROCESS" - routing revision
1025 examine_type "ALL" - all revisions
1026 "IMPL_ONLY" - only implemented revisions
1027 "PEND_ONLY" - only unimplemented revisions
1028 org_id organization id
1029 item_id item id
1030 OUTPUT
1031 rev_date high date for revision
1032 itm_rev highest revision
1033 RETURNS
1034
1035 NOTES
1036 ---------------------------------------------------------------------------*/
1037 PROCEDURE GET_HIGH_REV_DATE(
1038 type IN VARCHAR2 DEFAULT 'PART',
1039 examine_type IN VARCHAR2 DEFAULT 'ALL',
1040 org_id IN NUMBER,
1041 item_id IN NUMBER,
1042 rev_date IN OUT NOCOPY DATE,
1043 itm_rev IN OUT NOCOPY VARCHAR2
1044 )
1045 IS
1046 l_rev MTL_ITEM_REVISIONS_B.REVISION%TYPE;
1047
1048 CURSOR ITEM_REV IS
1049 SELECT REVISION
1050 FROM MTL_ITEM_REVISIONS_B
1051 WHERE INVENTORY_ITEM_ID = item_id
1052 AND ORGANIZATION_ID = org_id
1053 AND (
1054 (examine_type = 'ALL')
1055 OR
1056 (examine_type = 'IMPL_ONLY'
1057 AND IMPLEMENTATION_DATE IS NOT NULL
1058 )
1059 OR
1060 (examine_type = 'PEND_ONLY'
1061 AND IMPLEMENTATION_DATE IS NULL
1062 )
1063 )
1064 ORDER BY EFFECTIVITY_DATE DESC, REVISION DESC;
1065
1066 CURSOR RTG_REV IS
1067 SELECT PROCESS_REVISION
1068 FROM MTL_RTG_ITEM_REVISIONS
1069 WHERE INVENTORY_ITEM_ID = item_id
1070 AND ORGANIZATION_ID = org_id
1071 AND (
1072 (examine_type = 'ALL')
1073 OR
1074 (examine_type = 'IMPL_ONLY'
1075 AND IMPLEMENTATION_DATE IS NOT NULL
1076 )
1077 OR
1078 (examine_type = 'IMPL_AND_PEND'
1079 AND IMPLEMENTATION_DATE IS NULL
1080 )
1081 )
1082 ORDER BY EFFECTIVITY_DATE DESC, PROCESS_REVISION DESC;
1083
1084 BEGIN
1085 IF (type = 'PART') THEN
1086 OPEN ITEM_REV;
1087 FETCH ITEM_REV INTO l_rev;
1088 IF ITEM_REV%NOTFOUND THEN
1089 CLOSE ITEM_REV;
1090 FND_MESSAGE.SET_NAME('BOM', 'BOM_GET_REV_DATE');
1091 APP_EXCEPTION.RAISE_EXCEPTION;
1092 END IF;
1093 CLOSE ITEM_REV;
1094 ELSE
1095 OPEN RTG_REV;
1096 FETCH RTG_REV INTO l_rev;
1097 IF RTG_REV%NOTFOUND THEN
1098 CLOSE RTG_REV;
1099 FND_MESSAGE.SET_NAME('BOM', 'BOM_GET_REV_DATE');
1100 APP_EXCEPTION.RAISE_EXCEPTION;
1101 END IF;
1102 CLOSE RTG_REV;
1103
1104 END IF;
1105
1106 itm_rev := l_rev;
1107
1108 GET_HIGH_DATE(
1109 type => type,
1110 org_id => org_id,
1111 item_id => item_id,
1112 eco_status => 'ALL',
1113 itm_rev => l_rev,
1114 rev_date => rev_date);
1115
1116 EXCEPTION
1117 WHEN OTHERS THEN
1118 RAISE_REVISION_ERROR (
1119 func_name => 'GET_HIGH_REV_DATE',
1120 stmt_num => 1);
1121 END GET_HIGH_REV_DATE;
1122
1123 FUNCTION GET_ITEM_REV_HIGHDATE(
1124 p_revision_id IN NUMBER) RETURN DATE
1125 IS
1126 l_date DATE;
1127 BEGIN
1128
1129 SELECT high_date INTO l_date FROM mtl_item_rev_highdate_v WHERE revision_id = p_revision_id;
1130 return l_date;
1131
1132 EXCEPTION WHEN OTHERS THEN
1133 return null;
1134 END;
1135
1136
1137 END BOM_REVISIONS;
[Home] [Help]