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