1 PACKAGE BODY ENG_Validate AS
2 /* $Header: ENGSVATB.pls 120.3 2006/06/06 12:40:09 vkeerthi noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_Validate';
7 ret_code NUMBER;
8
9 -- Procedure Get_Attr_Tbl.
10 --
11 -- Used by generator to avoid overriding or duplicating existing
12 -- validation functions.
13 --
14 -- DO NOT REMOVE
15
16 PROCEDURE Get_Attr_Tbl
17 IS
18 I NUMBER:=0;
19 BEGIN
20
21 FND_API.g_attr_tbl.DELETE;
22
23 -- START GEN attributes
24
25 -- Generator will append new attributes before end generate comment.
26
27 I := I + 1;
28 FND_API.g_attr_tbl(I).name := 'Desc_Flex';
29 I := I + 1;
30 FND_API.g_attr_tbl(I).name := 'request';
31 I := I + 1;
32 FND_API.g_attr_tbl(I).name := 'program_application';
33 I := I + 1;
34 FND_API.g_attr_tbl(I).name := 'program';
35 I := I + 1;
36 FND_API.g_attr_tbl(I).name := 'program_update_date';
37 I := I + 1;
38 FND_API.g_attr_tbl(I).name := 'approval_status_type';
39 I := I + 1;
40 FND_API.g_attr_tbl(I).name := 'approval_date';
41 I := I + 1;
42 FND_API.g_attr_tbl(I).name := 'approval_list';
43 I := I + 1;
44 FND_API.g_attr_tbl(I).name := 'change_order_type';
45 I := I + 1;
46 FND_API.g_attr_tbl(I).name := 'responsible_org';
47 I := I + 1;
48 FND_API.g_attr_tbl(I).name := 'approval_request_date';
49 I := I + 1;
50 FND_API.g_attr_tbl(I).name := 'change_notice';
51 I := I + 1;
52 FND_API.g_attr_tbl(I).name := 'organization';
53 I := I + 1;
54 FND_API.g_attr_tbl(I).name := 'last_update_date';
55 I := I + 1;
56 FND_API.g_attr_tbl(I).name := 'last_updated_by';
57 I := I + 1;
58 FND_API.g_attr_tbl(I).name := 'creation_date';
59 I := I + 1;
60 FND_API.g_attr_tbl(I).name := 'created_by';
61 I := I + 1;
62 FND_API.g_attr_tbl(I).name := 'last_update_login';
63 I := I + 1;
64 FND_API.g_attr_tbl(I).name := 'description';
65 I := I + 1;
66 FND_API.g_attr_tbl(I).name := 'status_type';
67 I := I + 1;
68 FND_API.g_attr_tbl(I).name := 'initiation_date';
69 I := I + 1;
70 FND_API.g_attr_tbl(I).name := 'implementation_date';
71 I := I + 1;
72 FND_API.g_attr_tbl(I).name := 'cancellation_date';
73 I := I + 1;
74 FND_API.g_attr_tbl(I).name := 'cancellation_comments';
75 I := I + 1;
76 FND_API.g_attr_tbl(I).name := 'priority';
77 I := I + 1;
78 FND_API.g_attr_tbl(I).name := 'reason';
79 I := I + 1;
80 FND_API.g_attr_tbl(I).name := 'estimated_eng_cost';
81 I := I + 1;
82 FND_API.g_attr_tbl(I).name := 'estimated_mfg_cost';
83 I := I + 1;
84 FND_API.g_attr_tbl(I).name := 'requestor';
85
86 I := I + 1;
87 FND_API.g_attr_tbl(I).name := 'revision';
88 I := I + 1;
89 FND_API.g_attr_tbl(I).name := 'rev';
90 I := I + 1;
91 FND_API.g_attr_tbl(I).name := 'comments';
92
93 I := I + 1;
94 FND_API.g_attr_tbl(I).name := 'using_assembly';
95 I := I + 1;
96 FND_API.g_attr_tbl(I).name := 'revised_item';
97 I := I + 1;
98 FND_API.g_attr_tbl(I).name := 'cancel_comments';
99 I := I + 1;
100 FND_API.g_attr_tbl(I).name := 'disposition_type';
101 I := I + 1;
102 FND_API.g_attr_tbl(I).name := 'new_item_revision';
103 I := I + 1;
104 FND_API.g_attr_tbl(I).name := 'early_schedule_date';
105 I := I + 1;
106 FND_API.g_attr_tbl(I).name := 'scheduled_date';
107 I := I + 1;
108 FND_API.g_attr_tbl(I).name := 'bill_sequence';
109 I := I + 1;
110 FND_API.g_attr_tbl(I).name := 'mrp_active';
111 I := I + 1;
112 FND_API.g_attr_tbl(I).name := 'update_wip';
113 I := I + 1;
114 FND_API.g_attr_tbl(I).name := 'use_up';
115 I := I + 1;
116 FND_API.g_attr_tbl(I).name := 'use_up_item';
117 I := I + 1;
118 FND_API.g_attr_tbl(I).name := 'revised_item_sequence';
119 I := I + 1;
120 FND_API.g_attr_tbl(I).name := 'use_up_plan_name';
121 I := I + 1;
122 FND_API.g_attr_tbl(I).name := 'descriptive_text';
123 I := I + 1;
124 FND_API.g_attr_tbl(I).name := 'auto_implement_date';
125
126 I := I + 1;
127 FND_API.g_attr_tbl(I).name := 'supply_subinventory';
128 I := I + 1;
129 FND_API.g_attr_tbl(I).name := 'op_lead_time_percent';
130 I := I + 1;
131 FND_API.g_attr_tbl(I).name := 'cost_factor';
132 I := I + 1;
133 FND_API.g_attr_tbl(I).name := 'required_for_revenue';
134 I := I + 1;
135 FND_API.g_attr_tbl(I).name := 'high_quantity';
136 I := I + 1;
137 FND_API.g_attr_tbl(I).name := 'component_sequence';
138 I := I + 1;
139 FND_API.g_attr_tbl(I).name := 'wip_supply_type';
140 I := I + 1;
141 FND_API.g_attr_tbl(I).name := 'supply_locator';
142 I := I + 1;
143 FND_API.g_attr_tbl(I).name := 'bom_item_type';
144 I := I + 1;
145 FND_API.g_attr_tbl(I).name := 'operation_seq_num';
146 I := I + 1;
147 FND_API.g_attr_tbl(I).name := 'component_item';
148 I := I + 1;
149 FND_API.g_attr_tbl(I).name := 'item_num';
150 I := I + 1;
151 FND_API.g_attr_tbl(I).name := 'component_quantity';
152 I := I + 1;
153 FND_API.g_attr_tbl(I).name := 'component_yield_factor';
154 I := I + 1;
155 FND_API.g_attr_tbl(I).name := 'component_remarks';
156 I := I + 1;
157 FND_API.g_attr_tbl(I).name := 'effectivity_date';
158 I := I + 1;
159 FND_API.g_attr_tbl(I).name := 'disable_date';
160 I := I + 1;
161 FND_API.g_attr_tbl(I).name := 'planning_factor';
162 I := I + 1;
163 FND_API.g_attr_tbl(I).name := 'quantity_related';
164 I := I + 1;
165 FND_API.g_attr_tbl(I).name := 'so_basis';
166 I := I + 1;
167 FND_API.g_attr_tbl(I).name := 'optional';
168 I := I + 1;
169 FND_API.g_attr_tbl(I).name := 'mutually_exclusive_opt';
170 I := I + 1;
171 FND_API.g_attr_tbl(I).name := 'include_in_cost_rollup';
172 I := I + 1;
173 FND_API.g_attr_tbl(I).name := 'check_atp';
174 I := I + 1;
175 FND_API.g_attr_tbl(I).name := 'shipping_allowed';
176 I := I + 1;
177 FND_API.g_attr_tbl(I).name := 'required_to_ship';
178 I := I + 1;
179 FND_API.g_attr_tbl(I).name := 'include_on_ship_docs';
180 I := I + 1;
181 FND_API.g_attr_tbl(I).name := 'include_on_bill_docs';
182 I := I + 1;
183 FND_API.g_attr_tbl(I).name := 'low_quantity';
184 I := I + 1;
185 FND_API.g_attr_tbl(I).name := 'acd_type';
186 I := I + 1;
187 FND_API.g_attr_tbl(I).name := 'old_component_sequence';
188 I := I + 1;
189 FND_API.g_attr_tbl(I).name := 'pick_components';
190
191 I := I + 1;
192 FND_API.g_attr_tbl(I).name := 'ref_designator';
193 I := I + 1;
194 FND_API.g_attr_tbl(I).name := 'ref_designator_comment';
195
196 I := I + 1;
197 FND_API.g_attr_tbl(I).name := 'substitute_component';
198 I := I + 1;
199 FND_API.g_attr_tbl(I).name := 'substitute_item_quantity';
200
201
202 -- END GEN attributes
203
204 END Get_Attr_Tbl;
205
206 -- Prototypes for validate functions.
207
208 -- START GEN validate
209
210 -- Generator will append new prototypes before end generate
211 -- comment.
212
213
214 FUNCTION Desc_Flex ( p_flex_name IN VARCHAR2 )
215 RETURN BOOLEAN
216 IS
217 BEGIN
218
219 RETURN TRUE;
220
221 END Desc_Flex;
222
223 /***************************************************************************
224 *
225 *
226 *
227 *
228 *
229 *
230 ***************************************************************************/
231 FUNCTION Approval_Status_Type ( p_approval_status_type IN NUMBER
232 , x_err_text OUT NOCOPY VARCHAR2
233 )
234 RETURN BOOLEAN
235 IS
236 l_dummy VARCHAR2(10);
237 BEGIN
238
239 IF p_approval_status_type IS NULL OR
240 p_approval_status_type = FND_API.G_MISS_NUM
241 THEN
242 RETURN TRUE;
243 END IF;
244
245 SELECT 'VALID'
246 INTO l_dummy
247 FROM mfg_lookups
248 WHERE lookup_type = 'ENG_ECN_APPROVAL_STATUS'
249 AND lookup_code = p_approval_status_type;
250
251 RETURN TRUE;
252
253 EXCEPTION
254
255 WHEN NO_DATA_FOUND THEN
256 -- Should log error message ENG_APPROVAL_STATUS_INVALID
257 RETURN FALSE;
258
259 WHEN OTHERS THEN
260 x_err_text := 'An unexpected error occured in ' || G_PKG_NAME ||
261 ' and procedure Approval_Status_Type ' || SQLERRM;
262 RETURN FALSE;
263
264 END Approval_Status_Type;
265
266 /***************************************************************************
267 *
268 *
269 *
270 *
271 *
272 *
273 ***************************************************************************/
274 FUNCTION Approval_Date ( p_approval_date IN DATE
275 , x_err_text OUT NOCOPY VARCHAR2
276 )
277 RETURN BOOLEAN
278 IS
279 l_dummy VARCHAR2(10);
280 BEGIN
281
282 IF p_approval_date IS NULL OR
283 p_approval_date = FND_API.G_MISS_DATE
284 THEN
285 RETURN TRUE;
286 END IF;
287
288 IF p_approval_date > SYSDATE
289 THEN
290 RETURN FALSE;
291 END IF;
292
293 RETURN TRUE;
294
295 END Approval_Date;
296
297 /***************************************************************************
298 *
299 *
300 *
301 *
302 *
303 *
304 ***************************************************************************/
305 FUNCTION Approval_List ( p_approval_list_id IN NUMBER
306 , x_err_text OUT NOCOPY VARCHAR2
307 )
308 RETURN BOOLEAN
309 IS
310 l_dummy VARCHAR2(10);
311 BEGIN
312
313 IF p_approval_list_id IS NULL OR
314 p_approval_list_id = FND_API.G_MISS_NUM
315 THEN
316 RETURN TRUE;
317 END IF;
318
319 SELECT 'VALID'
320 INTO l_dummy
321 FROM eng_ecn_approval_lists
322 WHERE approval_list_id = p_approval_list_id;
323
324 RETURN TRUE;
325
326 EXCEPTION
327
328 WHEN NO_DATA_FOUND THEN
329
330 -- Should log error ENG_APPROVAL_LIST_INVALID
331 RETURN FALSE;
332
333 WHEN OTHERS THEN
334 x_err_text := 'An unexpected error occured in ' || G_PKG_NAME ||
335 ' and procedure Approval_List ' || SQLERRM;
336
337 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
338
339 END Approval_List;
340
341 /***************************************************************************
342 *
343 *
344 *
345 *
346 *
347 *
348 ***************************************************************************/
349 FUNCTION Change_Order_Type ( p_change_order_type_id IN NUMBER
350 , x_err_text OUT NOCOPY VARCHAR2
351 )
352 RETURN BOOLEAN
353 IS
354 l_dummy VARCHAR2(10);
355 BEGIN
356
357 IF p_change_order_type_id IS NULL OR
358 p_change_order_type_id = FND_API.G_MISS_NUM
359 THEN
360 RETURN TRUE;
361 END IF;
362
363 SELECT 'VALID'
364 INTO l_dummy
365 FROM eng_change_order_types
366 WHERE change_order_type_id = p_change_order_type_id
367 AND NVL(disable_date, SYSDATE + 1) > SYSDATE;
368
369 RETURN TRUE;
370
371 EXCEPTION
372
373 WHEN NO_DATA_FOUND THEN
374
375 -- Should log error message ENG_CHANGE_ORDER_TYPE_INVALID
376 RETURN FALSE;
377
378 WHEN OTHERS THEN
379 x_err_text := 'An unexpected error occured in ' || G_PKG_NAME ||
380 ' and procedure Change_Order_Type ' || SQLERRM;
381 RETURN FALSE;
382
383 END Change_Order_Type;
384
385 /***************************************************************************
386 *
387 *
388 *
389 *
390 *
391 *
392 ***************************************************************************/
393 FUNCTION Responsible_Org ( p_responsible_org_id IN NUMBER
394 , p_current_org_id IN NUMBER
395 , x_err_text OUT NOCOPY VARCHAR2
396 )
397 RETURN BOOLEAN
398 IS
399 l_dummy VARCHAR2(10);
400 BEGIN
401
402 IF p_responsible_org_id IS NULL OR
403 p_responsible_org_id = FND_API.G_MISS_NUM
404 THEN
405 RETURN TRUE;
406 END IF;
407 -- Bug 4947849
408 -- The following query has been fixed to fetch valid departments immaterial of the business group in context.
409 -- The view hr_organization_units in iteself is restricted based on the profile HR: Cross Business group
410 -- Value and per_business_group_id in context if the prior value is N.
411 -- Also , it is being assumed here that the user will login to Oracle Appliction for doing an import from
412 -- 11.5.10 onwards because Change Import concurrent pogram is used. Otherwise the query should return all
413 -- departments
414 SELECT 'VALID'
415 INTO l_dummy
416 FROM hr_organization_units hou
417 -- ,org_organization_definitions org_def
418 WHERE hou.organization_id = p_responsible_org_id
419 -- AND org_def.organization_id = p_current_org_id
420 -- AND org_def.business_group_id = hou.business_group_id
421 AND EXISTS
422 (select null
423 from hr_organization_information hoi
424 where hoi.organization_id = hou.organization_id
425 and hoi.org_information_context = 'CLASS'
426 and hoi.org_information1 = 'BOM_ECOD'
427 and hoi.org_information2 = 'Y');
428 RETURN TRUE;
429
430 EXCEPTION
431
432 WHEN NO_DATA_FOUND THEN
433
434 -- Should log an error ENG_RESP_ORG_ID_INVALID
435 RETURN FALSE;
436
437 WHEN OTHERS THEN
438 x_err_text := 'An unexpected error occured in ' || G_PKG_NAME ||
439 ' and procedure Responsible_Org ' || SQLERRM;
440
441 RETURN FALSE;
442
443 END Responsible_Org;
444
445 /***************************************************************************
446 *
447 *
448 *
449 *
450 *
451 *
452 ***************************************************************************/
453 FUNCTION Approval_Request_Date ( p_approval_request_date IN DATE
454 , x_err_text OUT NOCOPY VARCHAR2
455 )
456 RETURN BOOLEAN
457 IS
458 l_dummy VARCHAR2(10);
459 BEGIN
460
461 IF p_approval_request_date IS NULL OR
462 p_approval_request_date = FND_API.G_MISS_DATE
463 THEN
464 RETURN TRUE;
465 END IF;
466
467 IF p_approval_request_date > SYSDATE
468 THEN
469 RETURN FALSE;
470 END IF;
471
472 RETURN TRUE;
473
474 END Approval_Request_Date;
475
476 /*****************************************************************************
477 *
478 *
479 *
480 *
481 *
482 *
483 *****************************************************************************/
484 FUNCTION Status_Type ( p_status_type IN NUMBER
485 , x_err_text OUT NOCOPY VARCHAR2 )
486 RETURN BOOLEAN
487 IS
488 l_dummy VARCHAR2(10);
489 l_err_text VARCHAR2(2000) := NULL;
490 BEGIN
491
492 IF p_status_type IS NULL OR
493 p_status_type = FND_API.G_MISS_NUM
494 THEN
495 RETURN TRUE;
496 END IF;
497
498 --SELECT 'VALID'
499 --INTO l_dummy
500 --FROM mfg_lookups
501 --WHERE lookup_type = 'ECG_ECN_STATUS'
502 -- and lookup_code = p_status_type;
503
504 SELECT 'VALID'
505 INTO l_dummy
506 FROM eng_change_statuses
507 WHERE status_code = p_status_type;
508
509 RETURN TRUE;
510
511 EXCEPTION
512
513 WHEN NO_DATA_FOUND THEN
514
515 RETURN FALSE;
516
517 WHEN OTHERS THEN
518 x_err_text := 'An unexpected error occured in ' || G_PKG_NAME ||
519 ' and procedure Status_Type ' || SQLERRM ;
520
521 RETURN FALSE;
522
523 END Status_Type;
524
525 /****************************************************************************
526 * Function : End_Item_Unit_Number
527 * Parameters IN : p_From_End_Item_Unit_Number
528 * Parameters OUT: Error Text which will be pouplated in case of an
529 * unexpected error.
530 *
531 * Return : True if the from end item unit number is valid else False
532 * Purpose : Verify that the from end item unit number exists
533 * in the table PJM_MODEL_UNIT_NUMBERS.
534 ****************************************************************************/
535 FUNCTION End_Item_Unit_Number
536 ( p_from_end_item_unit_number IN VARCHAR2
537 , p_revised_item_id IN NUMBER
538 , x_err_text OUT NOCOPY VARCHAR2 )
539 RETURN BOOLEAN
540 IS
541 l_dummy VARCHAR2(10);
542 l_err_text VARCHAR2(2000) := NULL;
543 BEGIN
544
545 IF p_from_end_item_unit_number IS NULL OR
546 p_from_end_item_unit_number = FND_API.G_MISS_CHAR
547 THEN
548 RETURN TRUE;
549 END IF;
550
551 --Commented out on Oct 3, 1002 by ragreenw to match bom
552 -- equivalent package code
553 SELECT 'VALID'
554 INTO l_dummy
555 FROM pjm_unit_numbers
556 --WHERE end_item_id = p_revised_item_id
557 WHERE unit_number = p_from_end_item_unit_number;
558
559 RETURN TRUE;
560
561 EXCEPTION
562
563 WHEN NO_DATA_FOUND THEN
564
565 RETURN FALSE;
566
567 WHEN OTHERS THEN
568 x_err_text := 'An unexpected error occured in ' || G_PKG_NAME ||
569 ' and procedure From End Item Unit Number'
570 || SQLERRM ;
571
572 RETURN FALSE;
573
574 END End_Item_Unit_Number;
575
576 /****************************************************************************
577 *
578 *
579 *
580 *
581 *
582 *
583 ****************************************************************************/
584 FUNCTION Initiation_Date ( p_initiation_date IN DATE
585 , x_err_text OUT NOCOPY VARCHAR2
586 )
587 RETURN BOOLEAN
588 IS
589 l_dummy VARCHAR2(10);
590 BEGIN
591
592 IF p_initiation_date IS NULL OR
593 p_initiation_date = FND_API.G_MISS_DATE
594 THEN
595 RETURN TRUE;
596 END IF;
597
598 IF p_initiation_date > SYSDATE
599 THEN
600 RETURN FALSE;
601 END IF;
602
603 RETURN TRUE;
604
605 END Initiation_Date;
606
607 /*****************************************************************************
608 *
609 *
610 *
611 *
612 *
613 *
614 *****************************************************************************/
615 FUNCTION Implementation_Date ( p_implementation_date IN DATE
616 , x_err_text OUT NOCOPY VARCHAR2
617 )
618 RETURN BOOLEAN
619 IS
620 l_dummy VARCHAR2(10);
621 l_err_text VARCHAR2(2000) := NULL;
622 BEGIN
623
624 IF p_implementation_date IS NULL OR
625 p_implementation_date = FND_API.G_MISS_DATE
626 THEN
627 RETURN TRUE;
628 END IF;
629
630 IF p_implementation_date IS NOT NULL
631 THEN
632 RETURN FALSE;
633 END IF;
634
635 RETURN TRUE;
636
637 END Implementation_Date;
638
639 /****************************************************************************
640 *
641 *
642 *
643 *
644 *
645 *
646 *****************************************************************************/
647 FUNCTION Cancellation_Date ( p_cancellation_date IN DATE
648 , x_err_text OUT NOCOPY VARCHAR2
649 )
650 RETURN BOOLEAN
651 IS
652 l_dummy VARCHAR2(10);
653 BEGIN
654
655 IF p_cancellation_date IS NULL OR
656 p_cancellation_date = FND_API.G_MISS_DATE
657 THEN
658 RETURN TRUE;
659 END IF;
660
661 IF p_cancellation_date > SYSDATE
662 THEN
663 RETURN FALSE;
664 END IF;
665
666 RETURN TRUE;
667
668 END Cancellation_Date;
669
670 /****************************************************************************
671 *
672 *
673 *
674 *
675 *
676 *
677 *****************************************************************************/
678 FUNCTION Priority ( p_priority_code IN VARCHAR2
679 , p_organization_id IN NUMBER
680 , x_disable_date OUT NOCOPY DATE
681 , x_err_text OUT NOCOPY VARCHAR2
682 )
683 RETURN BOOLEAN
684 IS
685 l_dummy VARCHAR2(10);
686 BEGIN
687
688 IF p_priority_code IS NULL OR
689 p_priority_code = FND_API.G_MISS_CHAR
690 THEN
691 RETURN TRUE;
692 END IF;
693
694 SELECT 'VALID', disable_date
695 INTO l_dummy, x_disable_date
696 FROM eng_change_priorities
697 WHERE eng_change_priority_code = p_priority_code
698 AND organization_id = -1; --p_organization_id;
699
700 RETURN TRUE;
701
702 EXCEPTION
703
704 WHEN NO_DATA_FOUND THEN
705
706 -- Should log error message 'ENG_PRIORITY_CODE_INVALID'
707
708 RETURN FALSE;
709
710 WHEN OTHERS THEN
711 x_err_text := 'An unexpected error occured in ' || G_PKG_NAME ||
712 ' and procedure Priority' || SQLERRM ;
713
714 RETURN FALSE;
715
716 END Priority;
717
718 FUNCTION Reason ( p_reason_code IN VARCHAR2
719 , p_organization_id IN NUMBER
720 , x_disable_date OUT NOCOPY DATE
721 , x_err_text OUT NOCOPY VARCHAR2
722 )
723 RETURN BOOLEAN
724 IS
725 l_dummy VARCHAR2(10);
726 BEGIN
727
728 IF p_reason_code IS NULL OR
729 p_reason_code = FND_API.G_MISS_CHAR
730 THEN
731 RETURN TRUE;
732 END IF;
733
734 SELECT 'VALID', disable_date
735 INTO l_dummy, x_disable_date
736 FROM eng_change_reasons
737 WHERE eng_change_reason_code = p_reason_code
738 AND organization_id = -1; --p_organization_id;
739
740 RETURN TRUE;
741
742 EXCEPTION
743
744 WHEN NO_DATA_FOUND THEN
745
746 RETURN FALSE;
747
748 WHEN OTHERS THEN
749 x_err_text := 'An unexpected error occured in ' || G_PKG_NAME ||
750 ' and procedure Priority' || SQLERRM ;
751
752 RETURN FALSE;
753
754
755 END Reason;
756
757 FUNCTION Disposition_Type ( p_disposition_type IN NUMBER
758 , x_err_text OUT NOCOPY VARCHAR2
759 )
760 RETURN BOOLEAN
761 IS
762 l_dummy VARCHAR2(10);
763 l_err_text VARCHAR2(2000) := NULL;
764 BEGIN
765
766 IF p_disposition_type IS NULL OR
767 p_disposition_type = FND_API.G_MISS_NUM
768 THEN
769 RETURN TRUE;
770 END IF;
771
772 SELECT 'VALID'
773 INTO l_dummy
774 FROM mfg_lookups
775 WHERE lookup_type = 'ECG_MATERIAL_DISPOSITION'
776 and lookup_code = p_disposition_type;
777
778 RETURN TRUE;
779
780 EXCEPTION
781
782 WHEN NO_DATA_FOUND THEN
783
784 RETURN FALSE;
785
786 WHEN OTHERS THEN
787 x_err_text := 'An unexpected error occured in ' || G_PKG_NAME ||
788 ' and procedure Disposition_Type' || SQLERRM ;
789
790 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
791
792
793 END Disposition_Type;
794
795 /**************************************************************************
796 *
797 *
798 *
799 *
800 *
801 *
802 **************************************************************************/
803 FUNCTION Mrp_Active ( p_mrp_active IN NUMBER
804 , x_err_text OUT NOCOPY VARCHAR2 )
805 RETURN BOOLEAN
806 IS
807 l_dummy VARCHAR2(10);
808 l_err_text VARCHAR2(2000) := NULL;
809 BEGIN
810
811 IF p_mrp_active IS NULL OR
812 p_mrp_active = FND_API.G_MISS_NUM
813 THEN
814 RETURN TRUE;
815 END IF;
816
817 IF p_mrp_active IN (1, 2)
818 THEN
819 RETURN TRUE;
820 ELSE
821 RETURN FALSE;
822 END IF;
823
824 END Mrp_Active;
825
826 /****************************************************************************
827 *
828 *
829 *
830 *
831 *
832 ****************************************************************************/
833 FUNCTION Update_Wip ( p_update_wip IN NUMBER
834 , x_err_text OUT NOCOPY VARCHAR2
835 )
836 RETURN BOOLEAN
837 IS
838 l_dummy VARCHAR2(10);
839 BEGIN
840
841 IF p_update_wip IS NULL OR
842 p_update_wip = FND_API.G_MISS_NUM
843 THEN
844 RETURN TRUE;
845 END IF;
846
847 IF p_update_wip IN (1, 2)
848 THEN
849 RETURN TRUE;
850 ELSE
851 RETURN FALSE;
852 END IF;
853
854 END Update_Wip;
855
856 /*****************************************************************************
857 *
858 *
859 *
860 *
861 *
862 *****************************************************************************/
863 FUNCTION Use_Up ( p_use_up IN NUMBER
864 , x_err_text OUT NOCOPY VARCHAR2 )
865 RETURN BOOLEAN
866 IS
867 l_dummy VARCHAR2(10);
868 BEGIN
869
870 IF p_use_up IS NULL OR
871 p_use_up = FND_API.G_MISS_NUM
872 THEN
873 RETURN TRUE;
874 END IF;
875
876 IF p_use_up IN (1, 2)
877 THEN
878 RETURN TRUE;
879 ELSE
880 RETURN FALSE;
881 END IF;
882
883 END Use_Up;
884
885
886 /*****************************************************************************
887 * Function : Use_Up_Plan_Name
888 * Parameters IN : Plan Name
889 * Organization Id
890 * Parameters OUT: Error_Text
891 * Returns : True if plan is valid, otherwise False.
892 * Purpose : Function will validate the plan name against mrp_plans and
893 * verify if it exists. If it does then the function will check
894 * 1. If Data_completion date < Explosion_completion_date
895 * 2. If Plan completion_date < Data_completion_date
896 * If any of these conditions are violated, then the function
897 * returns with a False and the message name that should be used
898 * to get the message from the dictionary.
899 ******************************************************************************/
900 FUNCTION Use_Up_Plan_Name ( p_use_up_plan_name IN VARCHAR2
901 , p_organization_id IN NUMBER
902 , x_err_text OUT NOCOPY VARCHAR2 )
903 RETURN BOOLEAN
904 IS
905 l_explosion_completion_date DATE;
906 l_data_completion_date DATE;
907 l_plan_completion_date DATE;
908 BEGIN
909 IF p_use_up_plan_name IS NULL OR
910 p_use_up_plan_name = FND_API.G_MISS_CHAR
911 THEN
912 RETURN TRUE;
913 END IF;
914
915 Begin
916 SELECT explosion_completion_date, data_completion_date,
917 plan_completion_date
918 INTO l_explosion_completion_date,
919 l_data_completion_date,
920 l_plan_completion_date
921 FROM mrp_plans
922 WHERE compile_designator = p_use_up_plan_name
923 AND organization_id = p_organization_id;
924
925 IF l_data_completion_date < l_explosion_completion_date
926 THEN
927 x_err_text := 'ENG_DATA_COMPL_DATE_INVALID';
928 RETURN FALSE;
929
930 END IF;
931 IF l_data_completion_date > l_plan_completion_date
932 THEN
933 x_err_text := 'ENG_PLAN_COMPL_DATE_INVALID';
934 RETURN FALSE;
935 END IF;
936
937 /* Aded following for Bug 3240315 */
938 Exception
939 WHEN NO_DATA_FOUND THEN
940
941 SELECT plan_completion_date
942 INTO l_plan_completion_date
943 FROM mrp_plan_organizations_v
944 WHERE compile_designator = p_use_up_plan_name
945 AND planned_organization = p_organization_id;
946
947 End;
948 RETURN TRUE;
949
950 EXCEPTION
951
952 WHEN NO_DATA_FOUND THEN
953 x_err_text := 'ENG_USE_UP_PLAN_INVALID';
954 RETURN FALSE;
955
956 WHEN OTHERS THEN
957 x_err_text := 'An unexpected error occured in ' || G_PKG_NAME ||
958 ' and procedure Use_Up_Plan_Name' || SQLERRM ;
959
960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
961
962 END Use_Up_Plan_Name;
963
964
965 /****************************************************************************
966 * Function : Supply_Subinventory
967 * Parameters IN : Subinventory Name
968 * Organization Id
969 * Parameters OUT: Error Text which will be pouplated in case of an
970 * unexpected error.
971 *
972 * Return : True if the subinventory is valid else False
973 * Purpose : Verify that the supply subinventory is exist for the given
974 * organization in the table MTL_SECONDARY_SUBINVENTORIES.
975 ****************************************************************************/
976 FUNCTION Supply_Subinventory ( p_supply_subinventory IN VARCHAR2
977 , p_organization_id IN NUMBER
978 , x_err_text OUT NOCOPY VARCHAR2
979 )
980 RETURN BOOLEAN
981 IS
982 l_dummy VARCHAR2(10);
983 BEGIN
984
985 IF p_supply_subinventory IS NULL OR
986 p_supply_subinventory = FND_API.G_MISS_CHAR
987 THEN
988 RETURN TRUE;
989 END IF;
990
991 SELECT 'VALID'
992 INTO l_dummy
993 FROM mtl_secondary_inventories
994 WHERE secondary_inventory_name = p_supply_subinventory
995 AND organization_id = p_organization_id;
996
997 RETURN TRUE;
998
999 EXCEPTION
1000 WHEN NO_DATA_FOUND THEN
1001 RETURN FALSE;
1002
1003 WHEN OTHERS THEN
1004 x_err_text := 'An unexpected error occured in ' || G_PKG_NAME ||
1005 ' and procedure Supply_Subinventory' || SQLERRM ;
1006 RETURN FALSE;
1007
1008 END Supply_Subinventory;
1009
1010 /****************************************************************************
1011 *
1012 *
1013 *
1014 *
1015 *
1016 *
1017 ****************************************************************************/
1018 FUNCTION Required_For_Revenue ( p_required_for_revenue IN NUMBER
1019 , x_err_text OUT NOCOPY VARCHAR2
1020 )
1021 RETURN BOOLEAN
1022 IS
1023 l_dummy VARCHAR2(10);
1024 BEGIN
1025
1026 IF p_required_for_revenue IS NULL OR
1027 p_required_for_revenue = FND_API.G_MISS_NUM
1028 THEN
1029 RETURN TRUE;
1030 END IF;
1031
1032 IF p_required_for_revenue NOT IN(1, 2) THEN
1033 RETURN FALSE;
1034 ELSE
1035 RETURN TRUE;
1036 END IF;
1037
1038 END Required_For_Revenue;
1039
1040 /****************************************************************************
1041 * Function : Wip_Supply_Type
1042 * Parameters IN : Wip_Supply_Type value
1043 * Parameters OUT: Error Text which will be populated in case of an
1044 * unexpected error.
1045 * Returns : True if the Wip_supply_Type exist in the Lookup else False
1046 * Purpose : Verify that the value of Wip_Supply_Type is valid, by looking
1047 * in the Table MFG_LOOKUPS with a Lookup Type of 'WIP_SUPPLY'
1048 *****************************************************************************/
1049 FUNCTION Wip_Supply_Type ( p_wip_supply_type IN NUMBER
1050 , x_err_text OUT NOCOPY VARCHAR2 )
1051 RETURN BOOLEAN
1052 IS
1053 l_dummy VARCHAR2(10);
1054 BEGIN
1055
1056 IF p_wip_supply_type IS NULL OR
1057 p_wip_supply_type = FND_API.G_MISS_NUM
1058 THEN
1059 RETURN TRUE;
1060 END IF;
1061
1062 SELECT 'VALID'
1063 INTO l_dummy
1064 FROM mfg_lookups
1065 WHERE lookup_code = p_wip_supply_type
1066 AND lookup_type = 'WIP_SUPPLY' ;
1067
1068 RETURN TRUE;
1069
1070 EXCEPTION
1071
1072 WHEN NO_DATA_FOUND THEN
1073 RETURN FALSE;
1074
1075 WHEN OTHERS THEN
1076 x_err_text := 'An unexpected error occured in ' || G_PKG_NAME ||
1077 ' and function Wip_Supply_Type' || SQLERRM ;
1078
1079 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1080
1081 END Wip_Supply_Type;
1082
1083 /*****************************************************************************
1084 *
1085 *
1086 *
1087 *
1088 *
1089 *
1090 *****************************************************************************/
1091 FUNCTION Item_Num ( p_item_num IN NUMBER
1092 , x_err_text OUT NOCOPY VARCHAR2)
1093 RETURN BOOLEAN
1094 IS
1095 l_dummy VARCHAR2(10);
1096 BEGIN
1097
1098 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Attribute validating Item_num . . . ' ||
1099 to_char(p_item_num));
1100 END IF;
1101
1102 IF p_item_num IS NULL OR
1103 p_item_num = FND_API.G_MISS_NUM
1104 THEN
1105 RETURN TRUE;
1106 ELSIF p_item_num < 0 OR
1107 p_item_num > 9999 THEN
1108 RETURN FALSE;
1109 ELSE
1110 RETURN TRUE;
1111 END IF;
1112
1113 END Item_Num;
1114
1115 FUNCTION Component_Yield_Factor ( p_component_yield_factor IN NUMBER
1116 , x_err_text OUT NOCOPY VARCHAR2
1117 )
1118 RETURN BOOLEAN
1119 IS
1120 l_dummy VARCHAR2(10);
1121 BEGIN
1122
1123 IF p_component_yield_factor IS NULL OR
1124 p_component_yield_factor = FND_API.G_MISS_NUM
1125 THEN
1126 RETURN TRUE;
1127 ELSIF p_component_yield_factor < 0 THEN
1128 RETURN FALSE;
1129 ELSE
1130 RETURN TRUE;
1131 END IF;
1132
1133 END Component_Yield_Factor;
1134
1135 /*****************************************************************************
1136 * Function : Effectivity_Date
1137 * Parmeters IN : Effectivity Date of the component
1138 * Revised item sequence id
1139 * Parmeters OUT : Error Text which will be populated in case of an unexpected
1140 * error.
1141 * Returns : True if the effectivity date is valid else False.
1142 * Purpose : Verify that the effectivity date of the component is equal
1143 * to the schedule date of the revised item.
1144 ******************************************************************************/
1145 FUNCTION Effectivity_Date ( p_effectivity_date IN DATE
1146 , p_revised_item_sequence_id IN NUMBER
1147 , x_err_text OUT NOCOPY VARCHAR2
1148 )
1149 RETURN BOOLEAN
1150 IS
1151 l_dummy VARCHAR2(10);
1152 CURSOR c_EffectiveDate IS
1153 SELECT scheduled_date
1154 FROM eng_revised_items
1155 WHERE revised_item_sequence_id = p_revised_item_sequence_id;
1156
1157 BEGIN
1158
1159 IF p_effectivity_date IS NULL OR
1160 p_effectivity_date = FND_API.G_MISS_DATE
1161 THEN
1162 RETURN TRUE;
1163 END IF;
1164
1165 FOR l_Effective IN c_EffectiveDate LOOP
1166
1167 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Schedule Date : ' ||
1168 to_Char(l_effective.scheduled_date) || ' ' ||
1169 'Effective Date : ' || to_Char(p_effectivity_date));
1170 END IF;
1171
1172 IF p_effectivity_date = l_effective.scheduled_date
1173 THEN
1174 RETURN TRUE;
1175 ELSE
1176 -- Date's should be = Scheduled date
1177 RETURN FALSE;
1178
1179 END IF;
1180 END LOOP;
1181
1182 END Effectivity_Date;
1183
1184 /****************************************************************************
1185 *
1186 *
1187 *
1188 *
1189 *
1190 *****************************************************************************/
1191 FUNCTION Disable_Date ( p_disable_date IN DATE
1192 , p_effectivity_date IN DATE
1193 , x_err_text OUT NOCOPY VARCHAR2
1194 )
1195 RETURN BOOLEAN
1196 IS
1197 l_dummy VARCHAR2(10);
1198 BEGIN
1199
1200 IF p_disable_date IS NULL OR
1201 p_disable_date = FND_API.G_MISS_DATE OR
1202 (p_disable_date >= SYSDATE AND
1203 p_disable_date >= p_effectivity_date
1204 )
1205 THEN
1206 RETURN TRUE;
1207 ELSE
1208 RETURN FALSE;
1209 END IF;
1210
1211 END Disable_Date;
1212
1213 FUNCTION Quantity_Related ( p_quantity_related IN NUMBER ,
1214 x_err_text OUT NOCOPY VARCHAR2 )
1215 RETURN BOOLEAN
1216 IS
1217 l_dummy VARCHAR2(10);
1218 BEGIN
1219
1220 IF p_quantity_related IS NULL OR
1221 p_quantity_related = FND_API.G_MISS_NUM
1222 THEN
1223 RETURN TRUE;
1224 END IF;
1225
1226 IF p_quantity_related in (1, 2) THEN
1227 RETURN TRUE;
1228 ELSE
1229 RETURN FALSE;
1230 END IF;
1231 END Quantity_Related;
1232
1233 FUNCTION So_Basis ( p_so_basis IN NUMBER
1234 , x_err_text OUT NOCOPY VARCHAR2
1235 )
1236 RETURN BOOLEAN
1237 IS
1238 l_dummy VARCHAR2(10);
1239 BEGIN
1240
1241 IF p_so_basis IS NULL OR
1242 p_so_basis = FND_API.G_MISS_NUM
1243 THEN
1244 RETURN TRUE;
1245 END IF;
1246 IF p_so_basis in (1, 2) THEN
1247 RETURN TRUE;
1248 ELSE
1249 RETURN FALSE;
1250 END IF;
1251
1252 END So_Basis;
1253
1254 /****************************************************************************
1255 *
1256 *
1257 *
1258 *
1259 *****************************************************************************/
1260 FUNCTION Optional ( p_optional IN NUMBER ,
1261 x_err_text OUT NOCOPY VARCHAR2 )
1262 RETURN BOOLEAN
1263 IS
1264 l_dummy VARCHAR2(10);
1265 BEGIN
1266
1267 IF p_optional IS NULL OR
1268 p_optional = FND_API.G_MISS_NUM
1269 THEN
1270 RETURN TRUE;
1271 END IF;
1272
1273 IF p_optional IN (1, 2) THEN
1274 RETURN TRUE;
1275 ELSE
1276 RETURN FALSE;
1277 END IF;
1278 END Optional;
1279
1280 /****************************************************************************
1281 *
1282 *
1283 *
1284 *
1285 *****************************************************************************/
1286 FUNCTION Mutually_Exclusive_Opt ( p_mutually_exclusive_opt IN NUMBER ,
1287 x_err_text OUT NOCOPY VARCHAR2 )
1288 RETURN BOOLEAN
1289 IS
1290 l_dummy VARCHAR2(10);
1291 BEGIN
1292
1293 IF p_mutually_exclusive_opt IS NULL OR
1294 p_mutually_exclusive_opt = FND_API.G_MISS_NUM
1295 THEN
1296 RETURN TRUE;
1297 END IF;
1298
1299 IF p_mutually_exclusive_opt IN (1, 2)
1300 THEN
1301 RETURN TRUE;
1302 ELSE
1303 RETURN FALSE;
1304 END IF;
1305
1306 END Mutually_Exclusive_Opt;
1307
1308 /****************************************************************************
1309 *
1310 *
1311 *
1312 *
1313 *****************************************************************************/
1314 FUNCTION Include_In_Cost_Rollup ( p_include_in_cost_rollup IN NUMBER ,
1315 x_err_text OUT NOCOPY VARCHAR2)
1316 RETURN BOOLEAN
1317 IS
1318 l_dummy VARCHAR2(10);
1319 BEGIN
1320
1321 IF p_include_in_cost_rollup IS NULL OR
1322 p_include_in_cost_rollup = FND_API.G_MISS_NUM
1323 THEN
1324 RETURN TRUE;
1325 END IF;
1326
1327 IF p_include_in_cost_rollup IN (1, 2)
1328 THEN
1329 RETURN TRUE;
1330 ELSE
1331 RETURN FALSE;
1332 END IF;
1333
1334 END Include_In_Cost_Rollup;
1335
1336 /****************************************************************************
1337 *
1338 *
1339 *
1340 *
1341 *****************************************************************************/
1342 FUNCTION Check_Atp ( p_check_atp IN NUMBER
1343 , x_err_text OUT NOCOPY VARCHAR2)
1344 RETURN BOOLEAN
1345 IS
1346 l_dummy VARCHAR2(10);
1347 BEGIN
1348
1349 -- Validate ATP at the Entity level as it
1350 -- requires lot of additional information.
1351
1352 IF p_check_atp IS NULL OR
1353 p_check_atp = FND_API.G_MISS_NUM
1354 THEN
1355 RETURN TRUE;
1356 ELSIF p_check_atp NOT IN (1, 2, 3) THEN
1357 RETURN FALSE;
1358 ELSE
1359 RETURN TRUE;
1360 END IF;
1361
1362 END Check_Atp;
1363
1364 /****************************************************************************
1365 *
1366 *
1367 *
1368 *
1369 *****************************************************************************/
1370 FUNCTION Shipping_Allowed ( p_shipping_allowed IN NUMBER ,
1371 x_err_text OUT NOCOPY VARCHAR2 )
1372 RETURN BOOLEAN
1373 IS
1374 l_dummy VARCHAR2(10);
1375 BEGIN
1376
1377 IF p_shipping_allowed IS NULL OR
1378 p_shipping_allowed = FND_API.G_MISS_NUM
1379 THEN
1380 RETURN TRUE;
1381 END IF;
1382
1383 IF p_shipping_Allowed IN (1, 2)
1384 THEN
1385 RETURN TRUE;
1386 ELSE
1387 RETURN FALSE;
1388 END IF;
1389
1390 END Shipping_Allowed;
1391
1392 /****************************************************************************
1393 *
1394 *
1395 *
1396 *
1397 *****************************************************************************/
1398 FUNCTION Required_To_Ship ( p_required_to_ship IN NUMBER ,
1399 x_err_text OUT NOCOPY VARCHAR2 )
1400 RETURN BOOLEAN
1401 IS
1402 l_dummy VARCHAR2(10);
1403 BEGIN
1404
1405 IF p_required_to_ship IS NULL OR
1406 p_required_to_ship = FND_API.G_MISS_NUM
1407 THEN
1408 RETURN TRUE;
1409 END IF;
1410
1411 IF p_required_to_ship IN (1, 2)
1412 THEN
1413 RETURN TRUE;
1414 ELSE
1415 RETURN FALSE;
1416 END IF;
1417
1418 END Required_To_Ship;
1419
1420 /****************************************************************************
1421 *
1422 *
1423 *
1424 *
1425 *****************************************************************************/
1426 FUNCTION Include_On_Ship_Docs ( p_include_on_ship_docs IN NUMBER ,
1427 x_err_text OUT NOCOPY VARCHAR2 )
1428 RETURN BOOLEAN
1429 IS
1430 l_dummy VARCHAR2(10);
1431 BEGIN
1432
1433 IF p_include_on_ship_docs IS NULL OR
1434 p_include_on_ship_docs = FND_API.G_MISS_NUM
1435 THEN
1436 RETURN TRUE;
1437 END IF;
1438
1439 IF p_include_on_ship_docs IN (1, 2)
1440 THEN
1441 RETURN TRUE;
1442 ELSE
1443 RETURN FALSE;
1444 END IF;
1445
1446 END Include_On_Ship_Docs;
1447
1448 /****************************************************************************
1449 *
1450 *
1451 *
1452 *
1453 *****************************************************************************/
1454 FUNCTION Acd_Type ( p_acd_type IN NUMBER ,
1455 x_err_text OUT NOCOPY VARCHAR2 )
1456 RETURN BOOLEAN
1457 IS
1458 l_dummy VARCHAR2(10);
1459 BEGIN
1460
1461 IF p_acd_type IS NULL OR
1462 p_acd_type NOT IN (1,2,3)
1463 THEN
1464 RETURN FALSE;
1465 ELSE
1466 RETURN TRUE;
1467 END IF;
1468 END Acd_Type;
1469
1470
1471
1472 /****************************************************************************
1473 * Added by MK on 09/01/2000 for ECO New Effectivities
1474 *
1475 *
1476 * Function : Check_RevCmp_In_ECO_By_WO
1477 * Parameters IN : Revised Item Sequence Id, Component Item Id and Operation Seq Num
1478 * Parameters OUT: Error Text which will be populated in case of an
1479 * unexpected error.
1480 * Returns : True if All Jobs in ECO by Lot, WO, Cum Qty have the
1481 * Rev Component and Op Seq Number else False.
1482 * Purpose : Check if Component Item, Op Seq Num exists in material requirements
1483 * info of jobs and schedules Verify the user can create a revised
1484 * component record in ECO by WO
1485 *****************************************************************************/
1486 FUNCTION Check_RevCmp_In_ECO_By_WO
1487 ( p_revised_item_sequence_id IN NUMBER
1488 , p_rev_comp_item_id IN NUMBER
1489 , p_operation_seq_num IN NUMBER)
1490
1491 RETURN BOOLEAN
1492 IS
1493 l_ret_status BOOLEAN := TRUE ;
1494
1495 CURSOR l_check_rit_effectivity_csr (p_revised_item_sequence_id NUMBER)
1496 IS
1497 SELECT lot_number
1498 , from_wip_entity_id
1499 , to_wip_entity_id
1500 , from_cum_qty
1501 , organization_id
1502 FROM ENG_REVISED_ITEMS
1503 WHERE (lot_number IS NOT NULL OR
1504 from_wip_entity_id IS NOT NULL)
1505 AND revised_item_sequence_id = p_revised_item_sequence_id ;
1506
1507
1508 CURSOR l_check_lot_num_csr ( p_lot_number NUMBER
1509 , p_rev_comp_item_id NUMBER
1510 , p_operation_seq_num NUMBER
1511 , p_organization_id NUMBER)
1512 IS
1513 SELECT 'Cmp does not exist'
1514 FROM SYS.DUAL
1515 WHERE EXISTS (SELECT NULL
1516 FROM WIP_DISCRETE_JOBS wdj
1517 WHERE (wdj.status_type <> 1
1518 OR
1519 NOT EXISTS(SELECT NULL
1520 FROM WIP_REQUIREMENT_OPERATIONS wro
1521 WHERE wro.operation_seq_num = p_operation_seq_num
1522 AND wro.inventory_item_id = p_rev_comp_item_id
1523 AND wro.wip_entity_id = wdj.wip_entity_id)
1524 )
1525 AND wdj.lot_number = p_lot_number
1526 AND wdj.organization_id = p_organization_id
1527 ) ;
1528
1529 CURSOR l_check_wo_csr ( p_from_wip_entity_id NUMBER
1530 , p_to_wip_entity_id NUMBER
1531 , p_rev_comp_item_id NUMBER
1532 , p_operation_seq_num NUMBER )
1533 IS
1534 SELECT 'Cmp does not exist'
1535 FROM SYS.DUAL
1536 WHERE EXISTS (SELECT NULL
1537 FROM WIP_DISCRETE_JOBS wdj
1538 , WIP_ENTITIES we
1539 , WIP_ENTITIES we1
1540 , WIP_ENTITIES we2
1541 WHERE (wdj.status_type <> 1
1542 OR
1543 NOT EXISTS (SELECT NULL
1544 FROM WIP_REQUIREMENT_OPERATIONS wro
1545 WHERE wro.operation_seq_num = p_operation_seq_num
1546 AND wro.inventory_item_id = p_rev_comp_item_id
1547 AND wro.wip_entity_id = wdj.wip_entity_id)
1548 )
1549 AND wdj.wip_entity_id = we.wip_entity_id
1550 AND we.wip_entity_name >= we1.wip_entity_name
1551 AND we.wip_entity_name <= we2.wip_entity_name
1552 AND we1.wip_entity_id = p_from_wip_entity_id
1553 AND we2.wip_entity_id = p_to_wip_entity_id
1554 ) ;
1555
1556 CURSOR l_check_cum_csr ( p_from_wip_entity_id NUMBER
1557 , p_rev_comp_item_id NUMBER
1558 , p_operation_seq_num NUMBER)
1559 IS
1560 SELECT 'Cmp does not exist'
1561 FROM SYS.DUAL
1562 WHERE EXISTS (SELECT NULL
1563 FROM WIP_DISCRETE_JOBS wdj
1564 WHERE (wdj.status_type <> 1
1565 OR
1566 NOT EXISTS(SELECT NULL
1567 FROM WIP_REQUIREMENT_OPERATIONS wro
1568 WHERE wro.operation_seq_num = p_operation_seq_num
1569 AND wro.inventory_item_id = p_rev_comp_item_id
1570 AND wro.wip_entity_id = wdj.wip_entity_id)
1571 )
1572 AND wdj.wip_entity_id = p_from_wip_entity_id
1573 ) ;
1574
1575 BEGIN
1576
1577
1578 FOR l_eco_effect_rec IN l_check_rit_effectivity_csr
1579 (p_revised_item_sequence_id)
1580 LOOP
1581
1582
1583 -- Check if Op Seq Num is exist in ECO by Lot
1584 IF l_eco_effect_rec.lot_number IS NOT NULL
1585 AND l_eco_effect_rec.from_wip_entity_id IS NULL
1586 AND l_eco_effect_rec.to_wip_entity_id IS NULL
1587 AND l_eco_effect_rec.from_cum_qty IS NULL
1588 THEN
1589
1590 FOR l_lot_num_rec IN l_check_lot_num_csr
1591 ( p_lot_number => l_eco_effect_rec.lot_number
1592 , p_rev_comp_item_id => p_rev_comp_item_id
1593 , p_operation_seq_num => p_operation_seq_num
1594 , p_organization_id => l_eco_effect_rec.organization_id)
1595
1596 LOOP
1597 l_ret_status := FALSE ;
1598 END LOOP ;
1599
1600 -- Check if Op Seq Num is exist in ECO by Cum
1601 ELSIF l_eco_effect_rec.lot_number IS NULL
1602 AND l_eco_effect_rec.from_wip_entity_id IS NOT NULL
1603 AND l_eco_effect_rec.to_wip_entity_id IS NULL
1604 AND l_eco_effect_rec.from_cum_qty IS NOT NULL
1605 THEN
1606
1607 FOR l_lot_num_rec IN l_check_cum_csr
1608 ( p_from_wip_entity_id => l_eco_effect_rec.from_wip_entity_id
1609 , p_rev_comp_item_id => p_rev_comp_item_id
1610 , p_operation_seq_num => p_operation_seq_num )
1611 LOOP
1612 l_ret_status := FALSE ;
1613 END LOOP ;
1614
1615 -- Check if Op Seq Num is exist in ECO by WO
1616 ELSIF l_eco_effect_rec.lot_number IS NULL
1617 AND l_eco_effect_rec.from_wip_entity_id IS NOT NULL
1618 AND l_eco_effect_rec.to_wip_entity_id IS NOT NULL
1619 AND l_eco_effect_rec.from_cum_qty IS NULL
1620 THEN
1621
1622 FOR l_lot_num_rec IN l_check_wo_csr
1623 ( p_from_wip_entity_id => l_eco_effect_rec.from_wip_entity_id
1624 , p_to_wip_entity_id => l_eco_effect_rec.to_wip_entity_id
1625 , p_rev_comp_item_id => p_rev_comp_item_id
1626 , p_operation_seq_num => p_operation_seq_num )
1627 LOOP
1628 l_ret_status := FALSE ;
1629 END LOOP ;
1630
1631 ELSIF l_eco_effect_rec.lot_number IS NULL
1632 AND l_eco_effect_rec.from_wip_entity_id IS NULL
1633 AND l_eco_effect_rec.to_wip_entity_id IS NULL
1634 AND l_eco_effect_rec.from_cum_qty IS NULL
1635 THEN
1636 NULL ;
1637
1638 -- ELSE
1639 -- l_ret_status := FALSE ;
1640 --
1641
1642 END IF ;
1643 END LOOP ;
1644
1645 RETURN l_ret_status ;
1646
1647 END Check_RevCmp_In_ECO_By_WO ;
1648
1649
1650 -- Function Check_Reference_Common
1651 -- Cannot delete revised item if another bill references it as a common bill
1652
1653 FUNCTION Check_Reference_Common
1654 ( p_change_notice VARCHAR2
1655 , p_bill_sequence_id NUMBER
1656 )RETURN NUMBER
1657 IS
1658 l_count1 NUMBER := 0;
1659 l_count2 NUMBER := 0;
1660 cursor pending_on_eco is
1661 select 1
1662 from BOM_BILL_OF_MATERIALS
1663 where bill_sequence_id = p_bill_sequence_id
1664 and pending_from_ecn is not null
1665 and pending_from_ecn = p_change_notice;
1666 cursor reference_common is
1667 select 1
1668 from BOM_BILL_OF_MATERIALS
1669 where source_bill_sequence_id = p_bill_sequence_id
1670 and source_bill_sequence_id <> bill_sequence_id;
1671 BEGIN
1672
1673 l_count1 := 0;
1674
1675 for l_pending_on_eco in pending_on_eco loop
1676 l_count1 := 1;
1677 end loop;
1678
1679 if l_count1 = 1
1680 then
1681 l_count2 := 0;
1682
1683 for l_reference_common in reference_common loop
1684 l_count2 := 1;
1685 end loop;
1686 end if;
1687
1688 return (l_count2);
1689 END Check_Reference_Common;
1690
1691
1692 -- Function Check_Reference_Rtg_Common
1693 -- Cannot delete revised item if another Routing references it as a common routing
1694
1695 FUNCTION Check_Reference_Rtg_Common
1696 ( p_change_notice VARCHAR2
1697 , p_routing_sequence_id NUMBER
1698 )RETURN NUMBER
1699 IS
1700 l_count1 NUMBER := 0;
1701 l_count2 NUMBER := 0;
1702 cursor pending_on_eco is
1703 select 1
1704 from BOM_OPERATIONAL_ROUTINGS
1705 where routing_sequence_id = p_routing_sequence_id
1706 and pending_from_ecn is not null
1707 and pending_from_ecn = p_change_notice;
1708 cursor reference_common is
1709 select 1
1710 from BOM_OPERATIONAL_ROUTINGS
1711 where common_routing_sequence_id = p_routing_sequence_id
1712 and common_routing_sequence_id <> routing_sequence_id;
1713 BEGIN
1714
1715 l_count1 := 0;
1716
1717 for l_pending_on_eco in pending_on_eco loop
1718 l_count1 := 1;
1719 end loop;
1720
1721 if l_count1 = 1
1722 then
1723 l_count2 := 0;
1724
1725 for l_reference_common in reference_common loop
1726 l_count2 := 1;
1727 end loop;
1728 end if;
1729
1730 return (l_count2);
1731 END Check_Reference_Rtg_Common;
1732
1733
1734 -- Added by MK on 08/26/2000
1735 /*****************************************************************************
1736 * Procedure : Entity_Delete
1737 * Parameters IN : Revised item exposed column record
1738 * Revised item unexposed column record
1739 * Parameters OUT: Mesg Token Table
1740 * Return Status
1741 * Purpose : Entity Delete procedure will check if the given revised item
1742 * can be deleted without violating any business rules or
1743 * constraints. Revised item's cannot be deleted if there are
1744 * components on the bill or it revised item's bill is being
1745 * referenced as common by any other bills in the same org or
1746 * any other org.
1747 * (Check of revised item being implemented or cancelled is done
1748 * in the previous steps of the process flow)
1749 ******************************************************************************/
1750 PROCEDURE Check_Entity_Delete
1751 ( x_return_status OUT NOCOPY VARCHAR2
1752 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1753 , p_revised_item_rec IN ENG_Eco_PUB.Revised_Item_Rec_Type
1754 , p_rev_item_unexp_rec IN Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
1755 )
1756 IS
1757 l_err_text VARCHAR2(2000) := NULL;
1758 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1759 check_delete NUMBER := 0;
1760 l_count1 NUMBER := 0;
1761 CURSOR rev_comps IS
1762 SELECT 1
1763 FROM BOM_INVENTORY_COMPONENTS
1764 WHERE revised_item_sequence_id =
1765 p_rev_item_unexp_rec.revised_item_sequence_id;
1766
1767
1768 /******************************************************************
1769 -- Added by MK on 08/26/2000
1770 -- Enhancement for ECO Routing
1771 ******************************************************************/
1772 CURSOR rev_op_seq IS
1773 SELECT 'Rev Op Exist'
1774 FROM SYS.DUAL
1775 WHERE EXISTS ( SELECT NULL
1776 FROM BOM_OPERATION_SEQUENCES
1777 WHERE revised_item_sequence_id =
1778 p_rev_item_unexp_rec.revised_item_sequence_id) ;
1779 -- Added by MK on 08/26/2000
1780
1781 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1782 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1783 BEGIN
1784
1785
1786 --
1787 -- Set the revised item token name and value
1788 --
1789 l_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
1790 l_Token_Tbl(1).Token_Value := p_revised_item_rec.revised_item_name;
1791
1792 FOR l_rev_comps IN rev_comps
1793 LOOP
1794 --
1795 -- if loop executes, then component exist on that bill
1796 -- so it cannot be deleted.
1797 --
1798 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1799 THEN
1800 Error_Handler.Add_Error_Token
1801 ( p_Message_Name => 'ENG_CANNOT_DEL_COMP_EXIST'
1802 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1803 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1804 , p_Token_Tbl => l_Token_Tbl
1805 );
1806 END IF;
1807 l_return_status := FND_API.G_RET_STS_ERROR;
1808 END LOOP;
1809
1810
1811
1812 /******************************************************************
1813 -- Added by MK on 08/26/2000
1814 -- Enhancement for ECO Routing
1815 ******************************************************************/
1816 FOR l_rev_op_seq IN rev_op_seq
1817 LOOP
1818 --
1819 -- if loop executes, then revised operation exist on that
1820 -- routing so it cannot be deleted.
1821 --
1822 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1823 THEN
1824 Error_Handler.Add_Error_Token
1825 ( p_Message_Name => 'ENG_CANNOT_DEL_OP_EXIST'
1826 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1827 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1828 , p_Token_Tbl => l_Token_Tbl
1829 );
1830 END IF;
1831 l_return_status := FND_API.G_RET_STS_ERROR;
1832 END LOOP;
1833 -- Added by MK on 08/26/2000
1834
1835
1836
1837 /*********************************************************************
1838 --
1839 -- Check if the revised item's bill is being referenced as common
1840 --
1841 **********************************************************************/
1842 check_delete := Check_Reference_Common
1843 ( p_change_notice => p_revised_item_rec.eco_name
1844 , p_bill_sequence_id => p_rev_item_unexp_rec.bill_sequence_id
1845 );
1846
1847 IF check_delete <> 0
1848 THEN
1849 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1850 THEN
1851 Error_Handler.Add_Error_Token
1852 ( p_Message_Name => 'ENG_CANNOT_DEL_COMMON_EXIST'
1853 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1854 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1855 , p_Token_Tbl => l_Token_Tbl
1856 );
1857 END IF;
1858 l_return_status := FND_API.G_RET_STS_ERROR;
1859 END IF;
1860
1861 /*********************************************************************
1862 -- Added by MK on 08/26/2000
1863 -- Check if the revised item's routing is being referenced as common
1864 **********************************************************************/
1865 check_delete := 0 ;
1866 check_delete := Check_Reference_Rtg_Common
1867 ( p_change_notice => p_revised_item_rec.eco_name
1868 , p_routing_sequence_id => p_rev_item_unexp_rec.routing_sequence_id
1869 );
1870
1871 IF check_delete <> 0
1872 THEN
1873 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1874 THEN
1875 Error_Handler.Add_Error_Token
1876 ( p_Message_Name => 'ENG_CANNOT_DEL_RTG_COMMON_EXIST'
1877 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1878 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1879 , p_Token_Tbl => l_Token_Tbl
1880 );
1881 END IF;
1882 l_return_status := FND_API.G_RET_STS_ERROR;
1883 END IF;
1884 -- Added by MK on 08/26/2000
1885
1886 -- Done with the validations
1887 x_return_status := l_return_status;
1888 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1889
1890 EXCEPTION
1891
1892 WHEN FND_API.G_EXC_ERROR THEN
1893
1894 x_return_status := FND_API.G_RET_STS_ERROR;
1895
1896 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1897
1898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1899
1900 WHEN OTHERS THEN
1901
1902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1903
1904 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1905 THEN
1906 l_err_text := G_PKG_NAME || ' : (Entity Delete Validation) ' ||
1907 substrb(SQLERRM,1,200);
1908 Error_Handler.Add_Error_Token
1909 ( p_Message_Name => NULL
1910 , p_Message_Text => l_Err_Text
1911 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1912 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1913 );
1914 END IF;
1915
1916 END Check_Entity_Delete;
1917
1918 END ENG_Validate;
[Home] [Help]