[Home] [Help]
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
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,
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
115: AND BICN.ACD_TYPE = 3
118: OR
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,
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
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'
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,
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
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,
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
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;
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'
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,
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
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,
406: OR MV2.OPERATION_SEQ_NUM = C.OPERATION_SEQ_NUM)
407: AND MV2.EFFECTIVITY_DATE <= CUTOFF_DATE2
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