4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
1 PACKAGE BODY BOMPBCMP AS
2 /* $Header: BOMBCMPB.pls 120.1.12010000.2 2008/10/28 09:14:29 gliang ship $ */
3 /*==========================================================================+
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMBCMPB.pls |
9 | DESCRIPTION : |
10 | This file creates a packaged procedure that populates a |
11 | temporary table with the results of a bill of material |
12 | comparison. It is called from both the bill of material |
13 | comparison inquiry and report. |
14 +==========================================================================*/
15
16 PROCEDURE BOM_BILL_COMPARE
17 (SEQ_ID IN NUMBER,
18 BILL_SEQ_ID1 IN NUMBER,
19 BILL_SEQ_ID2 IN NUMBER,
20 IMPLEMENTED_CODE1 IN NUMBER,
21 IMPLEMENTED_CODE2 IN NUMBER,
22 DISPLAY_CODE1 IN NUMBER,
23 DISPLAY_CODE2 IN NUMBER,
24 CUTOFF_DATE1 IN DATE,
25 CUTOFF_DATE2 IN DATE,
26 ITEM_NUM_CODE IN NUMBER,
27 OP_SEQ_CODE IN NUMBER,
28 EFF_DATE_CODE IN NUMBER,
29 DIS_DATE_CODE IN NUMBER,
30 IMPL_CODE IN NUMBER,
31 QUANTITY_CODE IN NUMBER,
32 OPTIONAL_CODE IN NUMBER,
33 PLAN_FACT_CODE IN NUMBER,
34 DIFF_CODE IN NUMBER,
35 RETURN_CODE IN OUT NOCOPY /* file.sql.39 change */ NUMBER)
36 IS
37
38 COUNT_DUP NUMBER;
39
40 BEGIN
41
42 COUNT_DUP := 0;
43 RETURN_CODE := 0;
44
45 /* First, insert all the appropriate rows from the first bill of material,
46 according to display code and implemented code:
47 DISPLAY CODE 1 = All
48 2 = Current
49 3 = Current and future
50 IMPEMENTED CODE 1 = Implemented only
51 2 = Implemented and unimplemented
52 */
53
54 INSERT INTO BOM_COMPARISON_TEMP
55 (SEQUENCE_ID,
56 ASSEMBLY_ID1,
57 ITEM_NUM,
58 COMPONENT_QUANTITY,
59 OPERATION_SEQ_NUM,
60 PLANNING_FACTOR,
61 EFFECTIVITY_DATE,
62 DISABLE_DATE,
63 IMPLEMENTATION_DATE,
64 OPTIONAL,
65 COMPONENT_SEQUENCE_ID1)
66 SELECT SEQ_ID,
67 COMPONENT_ITEM_ID,
68 ITEM_NUM,
69 COMPONENT_QUANTITY,
70 OPERATION_SEQ_NUM,
71 PLANNING_FACTOR,
72 EFFECTIVITY_DATE,
73 DISABLE_DATE,
74 IMPLEMENTATION_DATE,
75 OPTIONAL,
76 COMPONENT_SEQUENCE_ID
77 FROM BOM_INVENTORY_COMPONENTS C1
78 WHERE C1.BILL_SEQUENCE_ID = BILL_SEQ_ID1
79 AND NVL(ECO_FOR_PRODUCTION,2) = 2
80 AND ((IMPLEMENTED_CODE1 = 2)
81 OR
82 (IMPLEMENTED_CODE1 = 1
83 AND IMPLEMENTATION_DATE IS NOT NULL)
84 )
85 AND (DISPLAY_CODE1 = 1
86 OR (DISPLAY_CODE1 = 2
87 AND CUTOFF_DATE1 BETWEEN EFFECTIVITY_DATE AND
88 NVL((DISABLE_DATE-(1/86400)), CUTOFF_DATE1 + 1)) --Added for Bug 6689734
89 OR (DISPLAY_CODE1 = 3
90 AND NVL(DISABLE_DATE, CUTOFF_DATE1 + 1) > CUTOFF_DATE1) --Changed from >= to > for Bug 6689734
91 )
92 AND ((IMPLEMENTED_CODE1 = 2
93 AND (DISPLAY_CODE1 = 1
94 OR
95 (DISPLAY_CODE1 = 2
96 AND EFFECTIVITY_DATE =
97 (SELECT MAX(EFFECTIVITY_DATE)
98 FROM BOM_INVENTORY_COMPONENTS MV2
99 WHERE MV2.BILL_SEQUENCE_ID = BILL_SEQ_ID1
100 AND NVL(MV2.ECO_FOR_PRODUCTION,2) = 2
101 AND MV2.COMPONENT_ITEM_ID = C1.COMPONENT_ITEM_ID
102 AND (DECODE(IMPLEMENTATION_DATE, NULL,
103 MV2.OLD_COMPONENT_SEQUENCE_ID,
104 MV2.COMPONENT_SEQUENCE_ID) =
105 DECODE(C1.IMPLEMENTATION_DATE, NULL,
106 C1.OLD_COMPONENT_SEQUENCE_ID, C1.COMPONENT_SEQUENCE_ID)
107 OR MV2.OPERATION_SEQ_NUM = C1.OPERATION_SEQ_NUM)
108 AND MV2.EFFECTIVITY_DATE <= CUTOFF_DATE1
109 AND NVL(DISABLE_DATE, CUTOFF_DATE1+1) > CUTOFF_DATE1
110 AND NOT EXISTS (SELECT NULL
111 FROM BOM_INVENTORY_COMPONENTS BICN
112 WHERE BICN.BILL_SEQUENCE_ID = BILL_SEQ_ID1
113 AND NVL(BICN.ECO_FOR_PRODUCTION,2) = 2
114 AND BICN.OLD_COMPONENT_SEQUENCE_ID = C1.COMPONENT_SEQUENCE_ID
118 OR
115 AND BICN.ACD_TYPE = 3
116 AND BICN.DISABLE_DATE < CUTOFF_DATE1 )) --Changed from <= to < for Bug 6689734
117 )
119 (DISPLAY_CODE1 = 3
120 AND EFFECTIVITY_DATE =
121 (SELECT MAX(EFFECTIVITY_DATE)
122 FROM BOM_INVENTORY_COMPONENTS MV2
123 WHERE MV2.BILL_SEQUENCE_ID = BILL_SEQ_ID1
124 AND NVL(MV2.ECO_FOR_PRODUCTION,2) = 2
125 AND MV2.COMPONENT_ITEM_ID = C1.COMPONENT_ITEM_ID
126 AND (DECODE(IMPLEMENTATION_DATE, NULL,
127 MV2.OLD_COMPONENT_SEQUENCE_ID,
128 MV2.COMPONENT_SEQUENCE_ID) =
129 DECODE(C1.IMPLEMENTATION_DATE, NULL,
130 C1.OLD_COMPONENT_SEQUENCE_ID, C1.COMPONENT_SEQUENCE_ID)
131 OR MV2.OPERATION_SEQ_NUM = C1.OPERATION_SEQ_NUM)
132 AND MV2.EFFECTIVITY_DATE <= CUTOFF_DATE1
133 AND NVL(DISABLE_DATE, CUTOFF_DATE1+1) > CUTOFF_DATE1
134 AND NOT EXISTS (SELECT NULL
135 FROM BOM_INVENTORY_COMPONENTS BICN
136 WHERE BICN.BILL_SEQUENCE_ID = BILL_SEQ_ID1
137 AND NVL(BICN.ECO_FOR_PRODUCTION,2) = 2
138 AND BICN.OLD_COMPONENT_SEQUENCE_ID = C1.COMPONENT_SEQUENCE_ID
139 AND BICN.ACD_TYPE = 3
140 AND BICN.DISABLE_DATE < CUTOFF_DATE1 )) --Changed from <= to < for Bug 6689734
141 OR EFFECTIVITY_DATE > CUTOFF_DATE1
142 )))
143 OR (IMPLEMENTED_CODE1 = 1
144 AND C1.IMPLEMENTATION_DATE IS NOT NULL));
145
146 LOOP
147
148 /* Now, update the rows in the temp table if there is a match with the
149 second bill of material */
150
151 UPDATE BOM_COMPARISON_TEMP T
152 SET T.COMPONENT_SEQUENCE_ID2 =
153 (SELECT MIN(C.COMPONENT_SEQUENCE_ID)
154 FROM BOM_INVENTORY_COMPONENTS C
155 WHERE C.BILL_SEQUENCE_ID = BILL_SEQ_ID2
156 AND NVL(C.ECO_FOR_PRODUCTION,2) = 2
157 AND C.COMPONENT_ITEM_ID = T.ASSEMBLY_ID1
158 AND NOT EXISTS (SELECT 'x'
159 FROM BOM_COMPARISON_TEMP T3
163 AND DECODE(ITEM_NUM_CODE, 1, C.ITEM_NUM, T.ITEM_NUM) = T.ITEM_NUM
160 WHERE T3.COMPONENT_SEQUENCE_ID2 =
161 C.COMPONENT_SEQUENCE_ID
162 AND T3.SEQUENCE_ID = T.SEQUENCE_ID)
164 AND DECODE(OP_SEQ_CODE, 1, C.OPERATION_SEQ_NUM,
165 T.OPERATION_SEQ_NUM) = T.OPERATION_SEQ_NUM
166 AND DECODE(EFF_DATE_CODE, 1, C.EFFECTIVITY_DATE,
167 T.EFFECTIVITY_DATE) = T.EFFECTIVITY_DATE
168 AND NVL(DECODE(DIS_DATE_CODE, 1, C.DISABLE_DATE, T.DISABLE_DATE),
169 SYSDATE) = NVL(T.DISABLE_DATE, SYSDATE)
170 AND ((IMPL_CODE = 1
171 AND C.IMPLEMENTATION_DATE IS NOT NULL
172 AND T.IMPLEMENTATION_DATE IS NOT NULL)
173 OR
174 (IMPL_CODE = 1
175 AND C.IMPLEMENTATION_DATE IS NULL
176 AND T.IMPLEMENTATION_DATE IS NULL)
177 OR
181 AND NVL(DECODE(PLAN_FACT_CODE, 1, C.PLANNING_FACTOR,
178 (IMPL_CODE = 2))
179 AND DECODE(QUANTITY_CODE, 1, C.COMPONENT_QUANTITY,
180 T.COMPONENT_QUANTITY) = T.COMPONENT_QUANTITY
182 T.PLANNING_FACTOR), 1) = NVL(T.PLANNING_FACTOR, 1)
183 AND NVL(DECODE(OPTIONAL_CODE, 1, C.OPTIONAL, T.OPTIONAL), 2) =
184 NVL(T.OPTIONAL, 2)
185 AND ((IMPLEMENTED_CODE2 = 2)
186 OR
187 (IMPLEMENTED_CODE2 = 1
188 AND C.IMPLEMENTATION_DATE IS NOT NULL))
189 AND (DISPLAY_CODE2 = 1
190 OR (DISPLAY_CODE2 = 2
191 AND CUTOFF_DATE2 BETWEEN EFFECTIVITY_DATE AND
192 NVL((DISABLE_DATE-(1/86400)) , CUTOFF_DATE2 + 1)) --Added for Bug 6689734
193 OR (DISPLAY_CODE2 = 3
194 AND NVL(DISABLE_DATE, CUTOFF_DATE2 + 1) > CUTOFF_DATE2)
195 )
196 AND ((IMPLEMENTED_CODE2 = 2
197 AND (DISPLAY_CODE2 = 1
198 OR
199 (DISPLAY_CODE2 = 2
200 AND EFFECTIVITY_DATE =
201 (SELECT MAX(EFFECTIVITY_DATE)
202 FROM BOM_INVENTORY_COMPONENTS MV2
203 WHERE MV2.BILL_SEQUENCE_ID = BILL_SEQ_ID2
204 AND NVL(MV2.ECO_FOR_PRODUCTION,2) = 2
205 AND MV2.COMPONENT_ITEM_ID = C.COMPONENT_ITEM_ID
206 AND (DECODE(IMPLEMENTATION_DATE, NULL,
207 MV2.OLD_COMPONENT_SEQUENCE_ID,
208 MV2.COMPONENT_SEQUENCE_ID) =
209 DECODE(C.IMPLEMENTATION_DATE, NULL,
210 C.OLD_COMPONENT_SEQUENCE_ID, C.COMPONENT_SEQUENCE_ID)
211 OR MV2.OPERATION_SEQ_NUM = C.OPERATION_SEQ_NUM)
212 AND MV2.EFFECTIVITY_DATE <= CUTOFF_DATE2
213 AND NVL(DISABLE_DATE, CUTOFF_DATE2+1) > CUTOFF_DATE2
214 AND NOT EXISTS (SELECT NULL
215 FROM BOM_INVENTORY_COMPONENTS BICN
216 WHERE BICN.BILL_SEQUENCE_ID = BILL_SEQ_ID2
217 AND NVL(BICN.ECO_FOR_PRODUCTION,2) = 2
218 AND BICN.OLD_COMPONENT_SEQUENCE_ID = C.COMPONENT_SEQUENCE_ID
219 AND BICN.ACD_TYPE = 3
220 AND BICN.DISABLE_DATE < CUTOFF_DATE2 )) --changed from <= to < for Bug 6689734
221 )
222 OR
223 (DISPLAY_CODE2 = 3
224 AND EFFECTIVITY_DATE =
225 (SELECT MAX(EFFECTIVITY_DATE)
226 FROM BOM_INVENTORY_COMPONENTS MV2
227 WHERE MV2.BILL_SEQUENCE_ID = BILL_SEQ_ID2
228 AND NVL(MV2.ECO_FOR_PRODUCTION,2) = 2
229 AND MV2.COMPONENT_ITEM_ID = C.COMPONENT_ITEM_ID
230 AND (DECODE(IMPLEMENTATION_DATE, NULL,
231 MV2.OLD_COMPONENT_SEQUENCE_ID,
232 MV2.COMPONENT_SEQUENCE_ID) =
233 DECODE(C.IMPLEMENTATION_DATE, NULL,
234 C.OLD_COMPONENT_SEQUENCE_ID, C.COMPONENT_SEQUENCE_ID)
235 OR MV2.OPERATION_SEQ_NUM = C.OPERATION_SEQ_NUM)
236 AND MV2.EFFECTIVITY_DATE <= CUTOFF_DATE2
237 AND NVL(DISABLE_DATE, CUTOFF_DATE2 + 1) > CUTOFF_DATE2
238 AND NOT EXISTS (SELECT NULL
239 FROM BOM_INVENTORY_COMPONENTS BICN
240 WHERE BICN.BILL_SEQUENCE_ID = BILL_SEQ_ID2
241 AND NVL(BICN.ECO_FOR_PRODUCTION,2) = 2
242 AND BICN.OLD_COMPONENT_SEQUENCE_ID = C.COMPONENT_SEQUENCE_ID
243 AND BICN.ACD_TYPE = 3
244 AND BICN.DISABLE_DATE < CUTOFF_DATE2 ))--changed from <= to < for Bug 6689734
245 OR EFFECTIVITY_DATE > CUTOFF_DATE2
246 )))
247 OR (IMPLEMENTED_CODE2 = 1
248 AND C.IMPLEMENTATION_DATE IS NOT NULL))
249 )
250 WHERE T.SEQUENCE_ID = SEQ_ID
251 AND T.COMPONENT_SEQUENCE_ID2 IS NULL;
252
253 /* Make sure that no rows from the second bill of material are matched
254 to two rows in the first bill of material. */
255
256 SELECT COUNT(*)
257 INTO COUNT_DUP
258 FROM BOM_COMPARISON_TEMP T
259 WHERE EXISTS
260 (SELECT NULL FROM BOM_COMPARISON_TEMP T2
261 WHERE T.ROWID < T2.ROWID
262 AND T.SEQUENCE_ID = T2.SEQUENCE_ID
263 AND T.COMPONENT_SEQUENCE_ID2 = T2.COMPONENT_SEQUENCE_ID2)
264 AND T.SEQUENCE_ID = SEQ_ID;
265
266 EXIT WHEN COUNT_DUP = 0;
267
268 /* If duplicate matches are found, null out one of them */
269
270 UPDATE BOM_COMPARISON_TEMP T
271 SET COMPONENT_SEQUENCE_ID2 = ''
272 WHERE EXISTS
273 (SELECT NULL FROM BOM_COMPARISON_TEMP T2
274 WHERE T.ROWID < T2.ROWID
275 AND T.SEQUENCE_ID = T2.SEQUENCE_ID
276 AND T.COMPONENT_SEQUENCE_ID2 = T2.COMPONENT_SEQUENCE_ID2)
277 AND T.SEQUENCE_ID = SEQ_ID;
278
279 END LOOP;
280
281 /* Update the temp table to indicate whether columns that are not
282 comparison criteria are matches */
283
284 UPDATE BOM_COMPARISON_TEMP T
285 SET (ITEM_NUM,
286 COMPONENT_QUANTITY,
287 OPERATION_SEQ_NUM,
288 EFFECTIVITY_DATE,
289 DISABLE_DATE,
290 IMPLEMENTATION_DATE,
291 PLANNING_FACTOR,
292 OPTIONAL) =
293 (SELECT DECODE(C.ITEM_NUM, T.ITEM_NUM, T.ITEM_NUM, NULL),
294 DECODE(C.COMPONENT_QUANTITY, T.COMPONENT_QUANTITY,
295 T.COMPONENT_QUANTITY, NULL),
296 DECODE(C.OPERATION_SEQ_NUM, T.OPERATION_SEQ_NUM,
297 T.OPERATION_SEQ_NUM, NULL),
298 DECODE(TRUNC(C.EFFECTIVITY_DATE), TRUNC(T.EFFECTIVITY_DATE),
299 T.EFFECTIVITY_DATE, NULL),
300 DECODE(NVL(TRUNC(C.DISABLE_DATE),TO_DATE('01-01-1901','DD-MM-YYYY')),
301 NVL(TRUNC(T.DISABLE_DATE),TO_DATE('01-01-1901','DD-MM-YYYY')),
302 T.DISABLE_DATE, NULL),
303 DECODE(DECODE(C.IMPLEMENTATION_DATE, NULL,
304 TO_DATE('01-01-1901','DD-MM-YYYY'),
308 TO_DATE('02-01-1901','DD-MM-YYYY')),
305 TO_DATE('02-01-1901','DD-MM-YYYY')),
306 DECODE(T.IMPLEMENTATION_DATE,
307 NULL, TO_DATE('01-01-1901','DD-MM-YYYY'),
309 T.IMPLEMENTATION_DATE, TO_DATE('01-01-1901','DD-MM-YYYY')),
310 DECODE(NVL(C.PLANNING_FACTOR, -99999),
311 NVL(T.PLANNING_FACTOR, -99999),
312 T.PLANNING_FACTOR, NULL),
313 DECODE(NVL(C.OPTIONAL, -9), NVL(T.OPTIONAL, -9),
314 T.OPTIONAL, NULL)
315 FROM BOM_INVENTORY_COMPONENTS C
316 WHERE C.COMPONENT_SEQUENCE_ID = T.COMPONENT_SEQUENCE_ID2)
317 WHERE COMPONENT_SEQUENCE_ID1 IS NOT NULL
318 AND COMPONENT_SEQUENCE_ID2 IS NOT NULL
319 AND SEQUENCE_ID = SEQ_ID;
320
321 /* Insert rows from the second bill of material that did not match anything
322 in the first bill of material */
323
324 INSERT INTO BOM_COMPARISON_TEMP
325 (SEQUENCE_ID,
326 ASSEMBLY_ID2,
327 ITEM_NUM,
328 COMPONENT_QUANTITY,
329 OPERATION_SEQ_NUM,
330 PLANNING_FACTOR,
331 EFFECTIVITY_DATE,
332 DISABLE_DATE,
333 IMPLEMENTATION_DATE,
334 OPTIONAL,
335 COMPONENT_SEQUENCE_ID2)
336 SELECT SEQ_ID,
337 COMPONENT_ITEM_ID,
338 ITEM_NUM,
339 COMPONENT_QUANTITY,
340 OPERATION_SEQ_NUM,
341 PLANNING_FACTOR,
342 EFFECTIVITY_DATE,
343 DISABLE_DATE,
344 IMPLEMENTATION_DATE,
345 OPTIONAL,
346 COMPONENT_SEQUENCE_ID
347 FROM BOM_INVENTORY_COMPONENTS C
348 WHERE BILL_SEQUENCE_ID = BILL_SEQ_ID2
349 AND NVL(C.ECO_FOR_PRODUCTION,2) = 2
350 AND NOT EXISTS
351 (SELECT 'x'
352 FROM BOM_COMPARISON_TEMP T2
353 WHERE T2.SEQUENCE_ID = SEQ_ID
354 AND T2.COMPONENT_SEQUENCE_ID2 = C.COMPONENT_SEQUENCE_ID)
355 AND ((IMPLEMENTED_CODE2 = 2)
356 OR
357 (IMPLEMENTED_CODE2 = 1
358 AND C.IMPLEMENTATION_DATE IS NOT NULL)
359 )
360 AND (DISPLAY_CODE2 = 1
361 OR (DISPLAY_CODE2 = 2
362 AND CUTOFF_DATE2 BETWEEN EFFECTIVITY_DATE AND
363 NVL((DISABLE_DATE-(1/86400)), CUTOFF_DATE2 + 1)) --Added for Bug 6689734
364 OR (DISPLAY_CODE2 = 3
365 AND NVL(DISABLE_DATE, CUTOFF_DATE2 + 1) > CUTOFF_DATE2) --changed from >= to > for Bug 6689734
366 )
367 AND ((IMPLEMENTED_CODE2 = 2
368 AND (DISPLAY_CODE2 = 1
369 OR
370 (DISPLAY_CODE2 = 2
371 AND EFFECTIVITY_DATE =
372 (SELECT MAX(EFFECTIVITY_DATE)
373 FROM BOM_INVENTORY_COMPONENTS MV2
374 WHERE MV2.BILL_SEQUENCE_ID = BILL_SEQ_ID2
375 AND NVL(MV2.ECO_FOR_PRODUCTION,2) = 2
376 AND MV2.COMPONENT_ITEM_ID = C.COMPONENT_ITEM_ID
377 AND (DECODE(IMPLEMENTATION_DATE, NULL,
378 MV2.OLD_COMPONENT_SEQUENCE_ID,
379 MV2.COMPONENT_SEQUENCE_ID) =
380 DECODE(C.IMPLEMENTATION_DATE, NULL,
381 C.OLD_COMPONENT_SEQUENCE_ID, C.COMPONENT_SEQUENCE_ID)
382 OR MV2.OPERATION_SEQ_NUM = C.OPERATION_SEQ_NUM)
383 AND MV2.EFFECTIVITY_DATE <= CUTOFF_DATE2
384 AND NVL(DISABLE_DATE, CUTOFF_DATE2+1) > CUTOFF_DATE2
385 AND NOT EXISTS (SELECT NULL
386 FROM BOM_INVENTORY_COMPONENTS BICN
387 WHERE BICN.BILL_SEQUENCE_ID = BILL_SEQ_ID2
388 AND NVL(BICN.ECO_FOR_PRODUCTION,2) = 2
389 AND BICN.OLD_COMPONENT_SEQUENCE_ID = C.COMPONENT_SEQUENCE_ID
390 AND BICN.ACD_TYPE = 3
391 AND BICN.DISABLE_DATE < CUTOFF_DATE2 )) --changed from <= to < for Bug 6689734
392 )
393 OR
394 (DISPLAY_CODE2 = 3
395 AND EFFECTIVITY_DATE =
396 (SELECT MAX(EFFECTIVITY_DATE)
397 FROM BOM_INVENTORY_COMPONENTS MV2
398 WHERE MV2.BILL_SEQUENCE_ID = BILL_SEQ_ID2
399 AND NVL(MV2.ECO_FOR_PRODUCTION,2) = 2
400 AND MV2.COMPONENT_ITEM_ID = C.COMPONENT_ITEM_ID
401 AND (DECODE(IMPLEMENTATION_DATE, NULL,
402 MV2.OLD_COMPONENT_SEQUENCE_ID,
403 MV2.COMPONENT_SEQUENCE_ID) =
407 AND MV2.EFFECTIVITY_DATE <= CUTOFF_DATE2
404 DECODE(C.IMPLEMENTATION_DATE, NULL,
405 C.OLD_COMPONENT_SEQUENCE_ID, C.COMPONENT_SEQUENCE_ID)
406 OR MV2.OPERATION_SEQ_NUM = C.OPERATION_SEQ_NUM)
408 AND NVL(DISABLE_DATE, CUTOFF_DATE2 + 1) > CUTOFF_DATE2
409 AND NOT EXISTS (SELECT NULL
410 FROM BOM_INVENTORY_COMPONENTS BICN
411 WHERE BICN.BILL_SEQUENCE_ID = BILL_SEQ_ID2
412 AND NVL(BICN.ECO_FOR_PRODUCTION,2) = 2
413 AND BICN.OLD_COMPONENT_SEQUENCE_ID = C.COMPONENT_SEQUENCE_ID
414 AND BICN.ACD_TYPE = 3
415 AND BICN.DISABLE_DATE < CUTOFF_DATE2 )) --changed from <= to < for Bug 6689734
416 OR EFFECTIVITY_DATE > CUTOFF_DATE2
417 )))
418 OR (IMPLEMENTED_CODE2 = 1
419 AND C.IMPLEMENTATION_DATE IS NOT NULL));
420
421 /* Make sure that no rows from the second bill of material are matched
422 /* If we are only displaying differences, then delete all matches from
423 the temporary table */
424
425 IF DIFF_CODE = 1 THEN
426 DELETE FROM BOM_COMPARISON_TEMP
427 WHERE SEQUENCE_ID = SEQ_ID
428 AND COMPONENT_SEQUENCE_ID1 IS NOT NULL
429 AND COMPONENT_SEQUENCE_ID2 IS NOT NULL;
430 END IF;
431
432 EXCEPTION
433 WHEN NO_DATA_FOUND THEN RETURN_CODE := SQLCODE;
434 WHEN OTHERS THEN RETURN_CODE := SQLCODE;
435
436 END BOM_BILL_COMPARE;
437
438 END BOMPBCMP; /* End package */
[Home] [Help]