1 PACKAGE BODY BOMPECOD AS
2 /* $Header: BOMECODB.pls 115.5 2004/05/18 01:17:42 rfarook ship $ */
3
4 PROCEDURE BOM_DELETE_ECO
5 (P_CHANGE_NOTICE IN VARCHAR2,
6 P_ORGANIZATION_ID IN NUMBER)
7 IS
8
9 BEGIN
10
11 DELETE FROM ENG_CHANGE_ORDER_REVISIONS
12 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
13 AND CHANGE_NOTICE = P_CHANGE_NOTICE;
14
15 DELETE FROM ENG_CURRENT_SCHEDULED_DATES
16 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
17 AND CHANGE_NOTICE = P_CHANGE_NOTICE;
18
19 DELETE FROM BOM_REFERENCE_DESIGNATORS R
20 WHERE R.COMPONENT_SEQUENCE_ID IN
21 (SELECT C.COMPONENT_SEQUENCE_ID
22 FROM BOM_INVENTORY_COMPONENTS C,
23 ENG_REVISED_ITEMS RI
24 WHERE RI.REVISED_ITEM_SEQUENCE_ID = C.REVISED_ITEM_SEQUENCE_ID
25 AND RI.ORGANIZATION_ID = P_ORGANIZATION_ID
26 AND RI.CHANGE_NOTICE = P_CHANGE_NOTICE
27 AND C.IMPLEMENTATION_DATE IS NULL);
28
29 DELETE FROM BOM_SUBSTITUTE_COMPONENTS S
30 WHERE S.COMPONENT_SEQUENCE_ID IN
31 (SELECT C.COMPONENT_SEQUENCE_ID
32 FROM BOM_INVENTORY_COMPONENTS C,
33 ENG_REVISED_ITEMS RI
34 WHERE RI.REVISED_ITEM_SEQUENCE_ID = C.REVISED_ITEM_SEQUENCE_ID
35 AND RI.ORGANIZATION_ID = P_ORGANIZATION_ID
36 AND RI.CHANGE_NOTICE = P_CHANGE_NOTICE
37 AND C.IMPLEMENTATION_DATE IS NULL);
38
39
40 DELETE FROM BOM_INVENTORY_COMPONENTS C
41 WHERE C.IMPLEMENTATION_DATE IS NULL
42 AND C.REVISED_ITEM_SEQUENCE_ID IN
43 (SELECT R.REVISED_ITEM_SEQUENCE_ID
44 FROM ENG_REVISED_ITEMS R
45 WHERE R.ORGANIZATION_ID = P_ORGANIZATION_ID
46 AND R.CHANGE_NOTICE = P_CHANGE_NOTICE);
47
48 UPDATE BOM_INVENTORY_COMPONENTS C
49 SET C.REVISED_ITEM_SEQUENCE_ID = NULL
50 -- C.CHANGE_NOTICE = NULL -- Bug2627917
51 WHERE C.IMPLEMENTATION_DATE IS NOT NULL
52 AND C.REVISED_ITEM_SEQUENCE_ID IN
53 (SELECT R.REVISED_ITEM_SEQUENCE_ID
54 FROM ENG_REVISED_ITEMS R
55 WHERE R.ORGANIZATION_ID = P_ORGANIZATION_ID
56 AND R.CHANGE_NOTICE = P_CHANGE_NOTICE);
57
58 DELETE FROM ENG_REVISED_COMPONENTS C
59 WHERE EXISTS
60 (SELECT NULL
61 FROM ENG_REVISED_ITEMS R
62 WHERE R.ORGANIZATION_ID = P_ORGANIZATION_ID
63 AND R.CHANGE_NOTICE = P_CHANGE_NOTICE
64 AND R.REVISED_ITEM_SEQUENCE_ID = C.REVISED_ITEM_SEQUENCE_ID);
65
66 DELETE FROM MTL_ITEM_REVISIONS_TL
67 WHERE REVISION_ID IN (SELECT REVISION_ID
68 FROM MTL_ITEM_REVISIONS_B
69 WHERE CHANGE_NOTICE = P_CHANGE_NOTICE
70 AND ORGANIZATION_ID = P_ORGANIZATION_ID
71 AND IMPLEMENTATION_DATE IS NULL);
72
73 DELETE FROM MTL_ITEM_REVISIONS_B
74 WHERE CHANGE_NOTICE = P_CHANGE_NOTICE
75 AND ORGANIZATION_ID = P_ORGANIZATION_ID
76 AND IMPLEMENTATION_DATE IS NULL;
77
78
79 UPDATE MTL_ITEM_REVISIONS_B
80 SET CHANGE_NOTICE = NULL,
81 REVISED_ITEM_SEQUENCE_ID = NULL
82 WHERE CHANGE_NOTICE = P_CHANGE_NOTICE
83 AND ORGANIZATION_ID = P_ORGANIZATION_ID
84 AND IMPLEMENTATION_DATE IS NOT NULL;
85
86 DELETE FROM ENG_REVISED_ITEMS
87 WHERE CHANGE_NOTICE = P_CHANGE_NOTICE
88 AND ORGANIZATION_ID = P_ORGANIZATION_ID;
89
90 DELETE FROM ENG_ENGINEERING_CHANGES
91 WHERE CHANGE_NOTICE = P_CHANGE_NOTICE
92 AND ORGANIZATION_ID = P_ORGANIZATION_ID;
93
94 DELETE FROM BOM_BILL_OF_MATERIALS B
95 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
96 AND PENDING_FROM_ECN = P_CHANGE_NOTICE
97 AND NOT EXISTS (SELECT NULL
98 FROM BOM_INVENTORY_COMPONENTS C
99 WHERE C.BILL_SEQUENCE_ID = B.BILL_SEQUENCE_ID)
100 AND NOT EXISTS (SELECT NULL
101 FROM ENG_REVISED_ITEMS R
102 WHERE R.BILL_SEQUENCE_ID = B.BILL_SEQUENCE_ID);
103
104 EXCEPTION
105 WHEN others THEN rollback;
106
107 END BOM_DELETE_ECO;
108
109 END BOMPECOD; /* End of package */
[Home] [Help]