[Home] [Help]
PACKAGE BODY: APPS.BOMPKMUD
Source
1 PACKAGE BODY BOMPKMUD AS
2 /* $Header: BOMKMUDB.pls 120.14.12020000.2 2012/07/05 09:29:21 ntungare ship $ */
3
4 --+==========================================================================
5 --|
6 --| HISTORY: ..-SEP-03 odaboval added procedure Raise_ECO_Create_Event
7 --| for raising that event after creating the ECO
8 --| 14-oct-03 odaboval made ERES calls 8i compliant.
9 --| 29-May-06 BBPATEL Performance fix
10 --|
11 --|
12 --+==========================================================================*/
13
14 --
15 -- Lookup values for acd_type (domain BOM_CO_ACTION) in
16 -- bom_inventory_comps_interface.
17 --
18 action_add CONSTANT NUMBER(1) := 1;
19 action_change CONSTANT NUMBER(1) := 4; -- new attributes
20 action_delete CONSTANT NUMBER(1) := 3;
21 action_replace CONSTANT NUMBER(1) := 2; -- old attributes
22 --
23 -- Lookup values for acd_type in bom_inventory_components
24 --
25 ecg_action_add CONSTANT NUMBER(1) := 1;
26 ecg_action_change CONSTANT NUMBER(1) := 2;
27 ecg_action_delete CONSTANT NUMBER(1) := 3;
28 --
29 -- Defaults
30 --
31 default_approval_status CONSTANT NUMBER(1) := 5; -- approved
32 default_operation_seq_num CONSTANT NUMBER(1) := 1;
33 default_component_quantity CONSTANT NUMBER(1) := 1;
34 default_component_yield_factor CONSTANT NUMBER(1) := 1;
35 default_planning_factor CONSTANT NUMBER(3) := 100;
36 default_quantity_related CONSTANT NUMBER(1) := 2;
37 default_include_in_cost_rollup CONSTANT NUMBER(1) := 2;
38 default_check_atp CONSTANT NUMBER(1) := 2;
39 default_disposition CONSTANT NUMBER(1) := 1;
40 default_status CONSTANT NUMBER(1) := 1; -- open
41
42 --
43 -- Lookup values for bom_item_type in mtl_system_items.
44 --
45 model_type CONSTANT NUMBER(1) := 1;
46 option_class_type CONSTANT NUMBER(1) := 2;
47 planning_type CONSTANT NUMBER(1) := 3;
48 standard_type CONSTANT NUMBER(1) := 4;
49
50 --
51 -- Lookup values for wip_supply_type in mtl_system_items.
52 --
53 phantom CONSTANT NUMBER(1) := 6;
54
55 --
56 -- Lookup values for assembly type
57 --
58 mfg CONSTANT NUMBER(1) := 1;
59 eng CONSTANT NUMBER(1) := 2;
60
61 --
62 -- Lookup value for MRP_ATO_FORECAST_CONTROL
63 --
64 g_consume_and_derive CONSTANT NUMBER(1) := 2;
65
66 --
67 -- Lookup value for EFFECTIVITY_CONTROL
68 --
69 date_control CONSTANT NUMBER(1) := 1;
70 unit_control CONSTANT NUMBER(1) := 2;
71
72 --
73 -- Lookup value for structure EFFECTIVITY_CONTROL
74 --
75 G_STRUCT_DATE_EFF CONSTANT NUMBER(1) := 1;
76 G_STRUCT_UNIT_EFF CONSTANT NUMBER(1) := 2;
77 G_STRUCT_SER_EFF CONSTANT NUMBER(1) := 3;
78 G_STRUCT_REV_EFF CONSTANT NUMBER(1) := 4;
79
80 --
81 -- Bug 4106826 - Added variable l_bom_lists_count for debugging
82 --
83 l_bom_lists_count NUMBER;
84
85
86 -- ERES change begins
87 PROCEDURE Raise_ECO_Create_Event( p_organization_id IN NUMBER
88 , p_organization_code IN VARCHAR2
89 , p_change_notice IN VARCHAR2
90 , x_return_status IN OUT NOCOPY VARCHAR2
91 --, x_msg_data OUT NOCOPY VARCHAR2
92 , x_msg_count IN OUT NOCOPY NUMBER)
93 IS
94
95 CURSOR Get_ECO_details( org_id IN NUMBER
96 , eco IN VARCHAR2) IS
97 SELECT change_id
98 FROM ENG_ENGINEERING_CHANGES
99 WHERE change_mgmt_type_code = 'CHANGE_ORDER'
100 AND organization_id = org_id
101 AND change_notice = eco;
102
103 l_child_record QA_EDR_STANDARD.ERECORD_ID_TBL_TYPE;
104 l_event QA_EDR_STANDARD.ERES_EVENT_REC_TYPE;
105 -- l_payload FND_WF_EVENT.PARAM_TABLE;
106 l_change_id NUMBER;
107 l_parent_record_id NUMBER;
108 l_msg_data VARCHAR2(2000);
109 l_message VARCHAR2(2000);
110 l_dummy_cnt NUMBER;
111 l_erecord_id NUMBER;
112 l_trans_status VARCHAR2(20);
113 l_event_status VARCHAR2(20);
114 l_send_ackn BOOLEAN;
115 l_ackn_by VARCHAR2(200);
116
117 RAISE_ERES_EVENT_ERROR EXCEPTION;
118 SEND_ACKN_ERROR EXCEPTION;
119 BEGIN
120
121 x_return_status := FND_API.G_FALSE;
122
123 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Beginning the ERES part for org_id='||p_organization_id||', change_notice='||p_change_notice);
124
125 -- Get Parent Event record id :
126 -- The error is not trapped, so that the execution can carry on.
127 QA_EDR_STANDARD.GET_ERECORD_ID
128 ( p_api_version => 1.0
129 , p_init_msg_list => FND_API.G_TRUE
130 , x_return_status => x_return_status
131 , x_msg_count => x_msg_count
132 , x_msg_data => l_msg_data
133 , p_event_name => 'oracle.apps.eng.massChangeBill'
134 , p_event_key => TO_CHAR(p_organization_id)||'-'||p_change_notice
135 , x_erecord_id => l_parent_record_id);
136
137
138 -- Prepare ecoCreate event
139 OPEN Get_ECO_Details(p_organization_id, p_change_notice);
140 FETCH Get_ECO_Details
141 INTO l_change_id;
142 CLOSE Get_ECO_Details;
143
144 FND_FILE.PUT_LINE(FND_FILE.LOG, 'change_id='||l_change_id||', parent_id='||l_parent_record_id);
145 IF (l_change_id IS NOT NULL)
146 THEN
147 -- First: Preparing child event #1
148
149 l_event.param_name_1 := 'DEFERRED';
150 l_event.param_value_1 := 'Y';
151
152 l_event.param_name_2 := 'POST_OPERATION_API';
153 l_event.param_value_2 := 'NONE';
154
155 l_event.param_name_3 := 'PSIG_USER_KEY_LABEL';
156 FND_MESSAGE.SET_NAME('ENG', 'ENG_ERES_ECO_USER_KEY');
157 l_event.param_value_3 := FND_MESSAGE.GET;
158
159 l_event.param_name_4 := 'PSIG_USER_KEY_VALUE';
160 l_event.param_value_4 := p_organization_code||'-'||p_change_notice;
161
162 l_event.param_name_5 := 'PSIG_TRANSACTION_AUDIT_ID';
163 l_event.param_value_5 := -1;
164
165 l_event.param_name_6 := '#WF_SOURCE_APPLICATION_TYPE';
166 l_event.param_value_6 := 'DB';
167
168 l_event.param_name_7 := '#WF_SIGN_REQUESTER';
169 l_event.param_value_7 := FND_GLOBAL.USER_NAME;
170
171 IF (l_parent_record_id > 0)
172 THEN
173 --additional parameters for the child event
174 l_event.param_name_8 := 'PARENT_EVENT_NAME';
175 l_event.param_value_8 := 'oracle.apps.eng.massChangeBill';
176 l_event.param_name_9 := 'PARENT_EVENT_KEY';
177 l_event.param_value_9 := TO_CHAR(p_organization_id)||'-'||p_change_notice;
178 l_event.param_name_10 := 'PARENT_ERECORD_ID';
179 l_event.param_value_10 := TO_CHAR(l_parent_record_id);
180 END IF;
181
182 -- Part 2 of preparation of child event :
183 l_event.event_name := 'oracle.apps.eng.ecoCreate';
184 l_event.event_key := TO_CHAR(l_change_id);
185 -- l_event.payload := l_payload;
186 l_event.erecord_id := l_erecord_id;
187 l_event.event_status := l_event_status;
188
189 QA_EDR_STANDARD.RAISE_ERES_EVENT
190 ( p_api_version => 1.0
191 , p_init_msg_list => FND_API.G_FALSE
192 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
193 , x_return_status => x_return_status
194 , x_msg_count => x_msg_count
195 , x_msg_data => l_msg_data
196 , p_child_erecords => l_child_record
197 , x_event => l_event);
198
199 IF (NVL(x_return_status, FND_API.G_FALSE) <> FND_API.G_TRUE)
200 AND (x_msg_count > 0)
201 THEN
202 RAISE RAISE_ERES_EVENT_ERROR;
203
204 END IF;
205
206 IF (l_event.event_status = 'PENDING')
207 THEN
208 l_send_ackn := TRUE;
209 l_trans_status := 'SUCCESS';
210 ELSIF (l_event.event_status = 'ERROR'
211 AND l_event.erecord_id IS NOT NULL)
212 THEN
213 l_send_ackn := TRUE;
214 l_trans_status := 'ERROR';
215 ELSIF (l_event.event_status = 'NOACTION'
216 AND l_event.erecord_id IS NOT NULL)
217 THEN
218 l_send_ackn := TRUE;
219 l_trans_status := 'SUCCESS';
220 END IF;
221 IF (l_send_ackn = TRUE )
222 THEN
223 FND_MESSAGE.SET_NAME('ENG', 'ENG_ERES_ACKN_MASS_CHANGES');
224 l_ackn_by := FND_MESSAGE.GET;
225
226 QA_EDR_STANDARD.SEND_ACKN
227 ( p_api_version => 1.0
228 , p_init_msg_list => FND_API.G_FALSE
229 , x_return_status => x_return_status
230 , x_msg_count => x_msg_count
231 , x_msg_data => l_msg_data
232 , p_event_name => l_event.event_name
233 , p_event_key => l_event.event_key
234 , p_erecord_id => l_event.erecord_id
235 , p_trans_status => l_trans_status
236 , p_ackn_by => l_ackn_by
237 , p_ackn_note => '(organization_id, change_notice)='||TO_CHAR(p_organization_id)||', '||p_change_notice||')'
238 , p_autonomous_commit => FND_API.G_FALSE);
239
240 FND_FILE.PUT_LINE(FND_FILE.LOG, 'After QA_EDR_STANDARD.SEND_ACKN msg='||x_msg_count);
241 IF (NVL(x_return_status, FND_API.G_FALSE) <> FND_API.G_TRUE)
242 AND (x_msg_count > 0)
243 THEN
244 RAISE SEND_ACKN_ERROR;
245 END IF;
246
247 END IF; -- (l_send_ackn = TRUE)
248 END IF; -- (l_change_id IS NOT NULL)
249
250 EXCEPTION
251 WHEN RAISE_ERES_EVENT_ERROR THEN
252 FND_FILE.PUT_LINE(FND_FILE.LOG,'ECO Create event, RAISE_ERES_EVENT_ERROR :');
253 -- Get the message and raise the procedure exception.
254 FND_MSG_PUB.Get(
255 p_msg_index => 1,
256 p_data => l_message,
257 p_encoded => FND_API.G_FALSE,
258 p_msg_index_out => l_dummy_cnt);
259 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error='||l_message);
260
261 WHEN SEND_ACKN_ERROR THEN
262 FND_FILE.PUT_LINE(FND_FILE.LOG,'ECO Create event, SEND_ACKN_ERROR :');
263 -- Get the message and raise the procedure exception.
264 FND_MSG_PUB.Get(
265 p_msg_index => 1,
266 p_data => l_message,
267 p_encoded => FND_API.G_FALSE,
268 p_msg_index_out => l_dummy_cnt);
269 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error='||l_message);
270
271 WHEN OTHERS THEN
272 FND_FILE.PUT_LINE(FND_FILE.LOG,'ECO Create event, OTHERS :'||SQLERRM);
273
274 END Raise_ECO_Create_Event;
275 -- ERES change ends
276
277
278 FUNCTION cnt(p_list_id NUMBER)
279 RETURN NUMBER IS
280 CURSOR lc_cnt IS
281 SELECT COUNT(*)
282 FROM bom_lists
283 WHERE sequence_id = p_list_id;
284 l_cnt NUMBER;
285 BEGIN
286 OPEN lc_cnt;
287 FETCH lc_cnt
288 INTO l_cnt;
289 CLOSE lc_cnt;
290 RETURN l_cnt;
291 END cnt;
292
293 ----------------------------- Procedure ---------------------------------
294 --
295 -- NAME
296 -- Match_Attributes
297 -- DESCRIPTION
298 -- Checks if component exists with attributes matching criteria in
299 -- component interface table.
300 -- REQUIRES
301 -- List - Sequence id of list in BOM_LISTS.
302 -- ECO - Engineering change order number of Mass Change Order.
303 -- Org Id - Organization id of Mass Change Order.
304 -- MODIFIES
305 -- Error Message - PL/SQL error.
306 -- RETURNS
307 --
308 -- NOTES
309 --
310 -- EXAMPLE
311 --
312 PROCEDURE Match_Attributes(
313 p_list_id IN bom_lists.sequence_id%TYPE,
314 p_eco IN eng_revised_items_interface.change_notice%TYPE,
315 p_org_id IN eng_revised_items_interface.organization_id%TYPE,
316 x_error_message IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
317 IS
318
319 BEGIN
320 SAVEPOINT begin_match;
321
322 -- Bug 4216428
323
324 FND_FILE.PUT_LINE(FND_FILE.LOG,'Delete rev effective structures from list');
325 DELETE FROM bom_lists l
326 WHERE l.sequence_id = p_list_id
327 AND EXISTS (
328 SELECT NULL
329 FROM bom_bill_of_materials b
330 WHERE b.assembly_item_id = l.assembly_item_id
331 AND b.organization_id = p_org_id
332 AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
333 OR (b.alternate_bom_designator = l.alternate_designator) )
334 AND b.effectivity_control =4);
335 --
336 -- Bug 4106826
337 --
338 FND_FILE.PUT_LINE(FND_FILE.LOG,'Entered Match Attributes');
339 select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
340 FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS before delete st in Match attributes='||
341 to_char(l_bom_lists_count));
342 -- end Bug 4106826
343
344 -- Removed NVL and added AND/OR condition on Alternate_bom_desigantor to improve performance
345 DELETE FROM bom_lists l
346 WHERE l.sequence_id = p_list_id
347 AND EXISTS (
348 SELECT NULL
349 FROM
350 bom_inventory_comps_interface ci,
351 eng_revised_items_interface ri
352 WHERE
353 ci.acd_type IN (action_replace, action_delete)
354 AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
355 AND ri.change_notice = p_eco
356 AND ri.organization_id = p_org_id
357 /* For bug 8550652
358 AND NOT EXISTS (
359 SELECT NULL
360 FROM eng_revised_items_interface rii,
361 bom_inventory_comps_interface cii
362 WHERE rii.change_notice = p_eco
363 AND rii.organization_id = p_org_id
364 AND rii.revised_item_sequence_id =
365 ri.revised_item_sequence_id
366 AND cii.revised_item_sequence_id =
367 ri.revised_item_sequence_id
368 AND cii.acd_type IN (action_add))
369 For bug 8550652 */
370 AND NOT EXISTS (
371 SELECT NULL
372 FROM bom_structures_b b,
373 bom_components_b c
374 WHERE
375 b.assembly_item_id = l.assembly_item_id
376 AND b.organization_id = p_org_id
377 AND ( (l.alternate_designator IS NULL AND b.alternate_bom_designator IS NULL)
378 OR (b.alternate_bom_designator = l.alternate_designator) )
379 AND (c.item_num = ci.item_num
380 OR ci.item_num IS NULL)
381 AND (Nvl(c.basis_type,4) = Decode(ci.basis_type, FND_API.G_MISS_NUM,4,ci.basis_type) -- 5214239
382 OR ci.basis_type is NULL)
383 AND (c.component_quantity = ci.component_quantity
384 OR ci.component_quantity IS NULL)
385 AND (c.component_yield_factor = ci.component_yield_factor
386 OR ci.component_yield_factor IS NULL)
387 AND (c.planning_factor = ci.planning_factor
388 OR ci.planning_factor IS NULL)
389 AND (c.quantity_related = ci.quantity_related
390 OR ci.quantity_related IS NULL)
391 AND (c.so_basis = ci.so_basis
392 OR ci.so_basis IS NULL)
393 AND (c.optional = ci.optional
394 OR ci.optional IS NULL)
395 AND (c.mutually_exclusive_options =
396 ci.mutually_exclusive_options
397 OR ci.mutually_exclusive_options IS NULL)
398 AND (c.include_in_cost_rollup = ci.include_in_cost_rollup
399 OR ci.include_in_cost_rollup IS NULL)
400 AND (c.check_atp = ci.check_atp
401 OR ci.check_atp IS NULL)
402 AND (c.shipping_allowed = ci.shipping_allowed
403 OR ci.shipping_allowed IS NULL)
404 AND (c.required_to_ship = ci.required_to_ship
405 OR ci.required_to_ship IS NULL)
406 AND (c.required_for_revenue = ci.required_for_revenue
407 OR ci.required_for_revenue IS NULL)
408 AND (c.include_on_ship_docs = ci.include_on_ship_docs
409 OR ci.include_on_ship_docs IS NULL)
410 AND (c.low_quantity = ci.low_quantity
411 OR ci.low_quantity IS NULL)
412 AND (c.high_quantity = ci.high_quantity
413 OR ci.high_quantity IS NULL)
414 AND (c.wip_supply_type = ci.wip_supply_type
415 OR ci.wip_supply_type IS NULL)
416 AND (c.supply_subinventory = ci.supply_subinventory
417 OR ci.supply_subinventory IS NULL)
418 AND (c.supply_locator_id = ci.supply_locator_id
419 OR ci.supply_locator_id IS NULL)
420 AND (c.component_remarks = ci.component_remarks
421 OR ci.component_remarks IS NULL)
422 AND (c.attribute_category = ci.attribute_category
423 OR ci.attribute_category IS NULL)
424 AND (c.attribute1 = ci.attribute1 OR ci.attribute1 IS NULL)
425 AND (c.attribute2 = ci.attribute2 OR ci.attribute2 IS NULL)
426 AND (c.attribute3 = ci.attribute3 OR ci.attribute3 IS NULL)
427 AND (c.attribute4 = ci.attribute4 OR ci.attribute4 IS NULL)
428 AND (c.attribute5 = ci.attribute5 OR ci.attribute5 IS NULL)
429 AND (c.attribute6 = ci.attribute6 OR ci.attribute6 IS NULL)
430 AND (c.attribute7 = ci.attribute7 OR ci.attribute7 IS NULL)
431 AND (c.attribute8 = ci.attribute8 OR ci.attribute8 IS NULL)
432 AND (c.attribute9 = ci.attribute9 OR ci.attribute9 IS NULL)
433 AND (c.attribute10 = ci.attribute10 OR ci.attribute10 IS NULL)
434 AND (c.attribute11 = ci.attribute11 OR ci.attribute11 IS NULL)
435 AND (c.attribute12 = ci.attribute12 OR ci.attribute12 IS NULL)
436 AND (c.attribute13 = ci.attribute13 OR ci.attribute13 IS NULL)
437 AND (c.attribute14 = ci.attribute14 OR ci.attribute14 IS NULL)
438 AND (c.attribute15 = ci.attribute15 OR ci.attribute15 IS NULL)
439 AND c.operation_seq_num =
440 NVL(ci.operation_seq_num, c.operation_seq_num)
441 AND c.component_item_id = ci.component_item_id
442 AND c.bill_sequence_id = b.bill_sequence_id
443
444 AND NVL(TRUNC(c.disable_date), NVL(ri.scheduled_date,TRUNC(SYSDATE)) + 1) >
445 NVL(ri.scheduled_date,TRUNC(SYSDATE))
446 AND TRUNC(c.effectivity_date) <= NVL(ri.scheduled_date,TRUNC(SYSDATE))
447
448 AND ( ( b.effectivity_control IN (G_STRUCT_DATE_EFF, G_STRUCT_REV_EFF) )
449 OR
450 (
451 ( b.effectivity_control IN (G_STRUCT_UNIT_EFF, G_STRUCT_SER_EFF) )
452 AND NVL(c.to_end_item_unit_number, ri.from_end_item_unit_number) >=
453 ri.from_end_item_unit_number
454 AND c.from_end_item_unit_number <= ri.from_end_item_unit_number )
455 )
456 )
457 );
458
459 x_error_message := NULL;
460 COMMIT;
461 select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
462 FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete st in match_attribute='||
463 to_char(l_bom_lists_count));
464
465 EXCEPTION
466 WHEN others THEN
467 ROLLBACK TO begin_match;
468 x_error_message := SUBSTRB(sqlerrm, 1, 150);
469 END Match_Attributes;
470
471 ----------------------------- Procedure ---------------------------------
472 --
473 -- NAME
474 -- Check_Combination
475 -- DESCRIPTION
476 -- Checks attributes of component item with those of revised item to see
477 -- if they are compatible.
478 -- REQUIRES
479 -- List id - Sequence id of list in BOM_LISTS.
480 -- ECO - Engineering Change Order number of Mass Update.
481 -- Organization - Organization id of revised item.
482 -- MODIFIES
483 -- Error Message - PL/SQL error.
484 -- RETURNS
485 --
486 -- NOTES
487 --
488 -- EXAMPLE
489 --
490
491 PROCEDURE Check_Combination(
492 p_list_id IN bom_lists.sequence_id%TYPE,
493 p_eco IN eng_revised_items_interface.change_notice%TYPE,
494 p_organization IN eng_revised_items_interface.organization_id%TYPE,
495 x_error_message IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
496 IS
497
498 BEGIN
499 SAVEPOINT begin_combo;
500
501 --
502 -- 1. Y = Allowed N = Not Allowed
503 -- P = Must be Phantom O = Must be Optional
504 -- Configured items are ATO standard items that have a base item id.
505 -- ATO items have Replenish to Order flags set to "Y".
506 -- PTO items have Pick Component flags set to "Y".
507 --
508 -- Parent
509 -- Child |Config ATO Mdl ATO Opt ATO Std PTO Mdl PTO Opt PTO Std
510 -- ---------------------------------------------------------------------------
511 -- Planning | N N N N N N N
512 -- Configured | Y Y Y Y Y Y N
513 -- ATO Model | P P P N P P N
514 -- ATO Opt Class | P P P N N N N
515 -- ATO Standard | Y Y Y Y O O N
516 -- PTO Model | N N N N P P N
517 -- PTO Opt Class | N N N N P P N
518 -- PTO Standard | N N N N Y Y Y
519 --
520 -- NOTE: Phantoms and Optional are handled by an update statement in
521 -- procedure Mass_Update below.
522 --
523 -- 2. Check Component ATP, delete bill from list if:
524 --
525 -- - Revised Item is ATO Model, ATO Option Class, ATO Standard,
526 -- PTO Model, PTO Option Class, PTO Standard or Phantom and ATP
527 -- Components Flag is set to No.
528 --
529 -- - Component Item's ATP Flag or ATP Components Flag is set to Yes.
530 --
531 select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
532 FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS before delete stmt Check_Combination ='||
533 to_char(l_bom_lists_count));
534 DELETE FROM bom_lists l
535 WHERE l.sequence_id = p_list_id
536 AND EXISTS (
537 SELECT NULL
538 FROM mtl_system_items_b ri,
539 mtl_system_items_b ci,
540 bom_inventory_comps_interface c,
541 eng_revised_items_interface r
542 WHERE ((ci.bom_item_type = planning_type AND
543 ri.bom_item_type <> planning_type)
544 OR
545 (ci.bom_item_type IN (model_type, option_class_type) AND
546 ri.bom_item_type = standard_type AND
547 ri.base_item_id IS NULL)
548 OR
549 (ci.replenish_to_order_flag = 'Y' AND
550 ci.bom_item_type = option_class_type AND
551 ri.pick_components_flag = 'Y')
552 OR
553 (ci.replenish_to_order_flag = 'Y' AND
554 ci.bom_item_type = standard_type AND
555 ri.pick_components_flag = 'Y' AND
556 ri.bom_item_type = standard_type)
557 OR
558 (ci.pick_components_flag = 'Y' AND
559 ri.replenish_to_order_flag = 'Y')
560 /* commented for bug 3548357 and 3508992
561 OR
562 (ri.bom_item_type <> planning_type AND
563 ri.atp_components_flag = 'N'
564 AND (ri.replenish_to_order_flag = 'Y' OR
565 ri.pick_components_flag = 'Y' OR
566 ri.wip_supply_type = phantom)
567 AND (ci.atp_flag = 'Y'
568 OR ci.atp_components_flag = 'Y'))*/
569 )
570 AND ri.inventory_item_id = l.assembly_item_id
571 AND ri.organization_id = p_organization
572 AND ci.inventory_item_id = c.component_item_id
573 AND ci.organization_id = p_organization
574 AND c.acd_type IN (action_add, action_change)
575 AND c.revised_item_sequence_id = r.revised_item_sequence_id
576 AND r.change_notice = p_eco
577 AND r.organization_id = p_organization);
578
579 select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
580 FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 1 Check_Combination ='||
581 to_char(l_bom_lists_count));
582
583 --
584 -- Do not create revised items if:
585 --
586 -- - Optional = Yes and Revised Item <> Model/OC
587 -- - Planning Percent <> 100% and
588 -- - Revised Item is Standard or
589 -- - Revised Items is Model/OC and
590 -- Component is mandatory and is not "Consume or Derive"
591 -- - Shippable = Yes and Revised Items is not pick-to-order
592 --
593
594 DELETE FROM bom_lists l
595 WHERE l.sequence_id = p_list_id
596 AND EXISTS (
597 SELECT NULL
598 FROM mtl_system_items_b i,
599 mtl_system_items_b ci,
600 bom_inventory_comps_interface c,
601 eng_revised_items_interface r
602 WHERE ((c.optional = yes AND i.bom_item_type
603 NOT IN (model_type, option_class_type))
604 OR
605 (c.planning_factor <> default_planning_factor
606 AND ((i.bom_item_type IN
607 (model_type, option_class_type)
608 AND c.optional = no
609 AND ci.ato_forecast_control <>
610 g_consume_and_derive)
611 OR (i.bom_item_type = standard_type)))
612 )
613 AND i.inventory_item_id = l.assembly_item_id
614 AND i.organization_id = r.organization_id
615 AND ci.inventory_item_id = c.component_item_id
616 AND ci.organization_id = r.organization_id
617 AND c.acd_type = action_add
618 AND c.revised_item_sequence_id = r.revised_item_sequence_id
619 AND r.change_notice = p_eco
620 AND r.organization_id = p_organization);
621 select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
622 FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 2 in Check_Combination='||
623 to_char(l_bom_lists_count));
624
625 DELETE FROM bom_lists bl
626 WHERE bl.sequence_id = p_list_id
627 AND EXISTS (
628 SELECT NULL
629 FROM mtl_system_items_b ri_itm, -- revised item
630 mtl_system_items_b ci_itm, -- component item
631 bom_inventory_components c,
632 bom_bill_of_materials bom,
633 bom_inventory_comps_interface o, -- old component
634 bom_inventory_comps_interface n, -- new component
635 eng_revised_items_interface ri
636 WHERE ri_itm.inventory_item_id = bom.assembly_item_id
637 AND ri_itm.organization_id = bom.organization_id
638 AND ci_itm.inventory_item_id = c.component_item_id
639 AND ci_itm.organization_id = bom.organization_id
640 AND ((NVL(n.optional, c.optional) = yes AND
641 ri_itm.bom_item_type NOT IN
642 (model_type, option_class_type))
643 OR
644 (NVL(n.planning_factor, c.planning_factor) <>
645 default_planning_factor
646 AND ((ri_itm.bom_item_type IN
647 (model_type, option_class_type)
648 AND NVL(n.optional, c.optional) = no
649 AND ci_itm.ato_forecast_control <> g_consume_and_derive)
650 OR (ri_itm.bom_item_type = standard_type)))
651 )
652 AND (c.item_num = o.item_num OR o.item_num IS NULL)
653 AND (c.component_quantity = o.component_quantity
654 OR o.component_quantity IS NULL)
655 AND (c.component_yield_factor = o.component_yield_factor
656 OR o.component_yield_factor IS NULL)
657 AND (c.planning_factor = o.planning_factor
658 OR o.planning_factor IS NULL)
659 AND (c.quantity_related = o.quantity_related
660 OR o.quantity_related IS NULL)
661 AND (c.so_basis = o.so_basis OR o.so_basis IS NULL)
662 AND (c.optional = o.optional
663 OR o.optional IS NULL)
664 AND (c.mutually_exclusive_options =
665 o.mutually_exclusive_options
666 OR o.mutually_exclusive_options IS NULL)
667 AND (c.include_in_cost_rollup = o.include_in_cost_rollup
668 OR o.include_in_cost_rollup IS NULL)
669 AND (c.check_atp = o.check_atp
670 OR o.check_atp IS NULL)
671 AND (c.shipping_allowed = o.shipping_allowed
672 OR o.shipping_allowed IS NULL)
673 AND (c.required_to_ship = o.required_to_ship
674 OR o.required_to_ship IS NULL)
675 AND (c.required_for_revenue = o.required_for_revenue
676 OR o.required_for_revenue IS NULL)
677 AND (c.include_on_ship_docs = o.include_on_ship_docs
678 OR o.include_on_ship_docs IS NULL)
679 AND (c.low_quantity = o.low_quantity
680 OR o.low_quantity IS NULL)
681 AND (c.high_quantity = o.high_quantity
682 OR o.high_quantity IS NULL)
683 AND (c.wip_supply_type = o.wip_supply_type
684 OR o.wip_supply_type IS NULL)
685 AND (c.supply_subinventory = o.supply_subinventory
686 OR o.supply_subinventory IS NULL)
687 AND (c.supply_locator_id = o.supply_locator_id
688 OR o.supply_locator_id IS NULL)
689 AND (c.component_remarks = o.component_remarks
690 OR o.component_remarks IS NULL)
691 AND (c.attribute_category = o.attribute_category
692 OR o.attribute_category IS NULL)
693 AND (c.attribute1 = o.attribute1 OR o.attribute1 IS NULL)
694 AND (c.attribute2 = o.attribute2 OR o.attribute2 IS NULL)
695 AND (c.attribute3 = o.attribute3 OR o.attribute3 IS NULL)
696 AND (c.attribute4 = o.attribute4 OR o.attribute4 IS NULL)
697 AND (c.attribute5 = o.attribute5 OR o.attribute5 IS NULL)
698 AND (c.attribute6 = o.attribute6 OR o.attribute6 IS NULL)
699 AND (c.attribute7 = o.attribute7 OR o.attribute7 IS NULL)
700 AND (c.attribute8 = o.attribute8 OR o.attribute8 IS NULL)
701 AND (c.attribute9 = o.attribute9 OR o.attribute9 IS NULL)
702 AND (c.attribute10 = o.attribute10 OR o.attribute10 IS NULL)
703 AND (c.attribute11 = o.attribute11 OR o.attribute11 IS NULL)
704 AND (c.attribute12 = o.attribute12 OR o.attribute12 IS NULL)
705 AND (c.attribute13 = o.attribute13 OR o.attribute13 IS NULL)
706 AND (c.attribute14 = o.attribute14 OR o.attribute14 IS NULL)
707 AND (c.attribute15 = o.attribute15 OR o.attribute15 IS NULL)
708 AND c.operation_seq_num =
709 NVL(o.operation_seq_num, c.operation_seq_num)
710 AND c.component_item_id = o.component_item_id
711 AND c.bill_sequence_id = bom.bill_sequence_id
712 AND ( (bom.alternate_bom_designator IS NULL AND bl.alternate_designator IS NULL)
713 OR (bom.alternate_bom_designator = bl.alternate_designator) )
714 AND bom.organization_id = ri.organization_id
715 AND bom.assembly_item_id = bl.assembly_item_id
716 AND o.component_sequence_id = n.old_component_sequence_id
717 AND n.acd_type = action_change
718 AND n.revised_item_sequence_id = ri.revised_item_sequence_id
719 AND ri.change_notice = p_eco
720 AND ri.organization_id = p_organization
721
722 AND NVL(TRUNC(c.disable_date), NVL(ri.scheduled_date,TRUNC(SYSDATE)) + 1) >
723 NVL(ri.scheduled_date,TRUNC(SYSDATE))
724 AND TRUNC(c.effectivity_date) <= NVL(ri.scheduled_date,TRUNC(SYSDATE))
725
726 AND ((NVL(c.to_end_item_unit_number, ri.from_end_item_unit_number) >=
727 ri.from_end_item_unit_number
728 AND c.from_end_item_unit_number <= ri.from_end_item_unit_number
729 AND ri_itm.effectivity_control = unit_control)
730 OR
731 ri_itm.effectivity_control = date_control)
732 );
733 select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
734 FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt3 in Check_Combination ='||
735 to_char(l_bom_lists_count));
736 --
737 -- Do not create revised items if:
738 --
739 -- - Operation Sequence Number does not exist in routing (except 1).
740 --
741
742 DELETE FROM bom_lists l
743 WHERE l.sequence_id = p_list_id
744 AND EXISTS (
745 SELECT NULL
746 FROM bom_inventory_comps_interface ci,
747 eng_revised_items_interface ri
748 WHERE ci.operation_seq_num NOT IN (
749 SELECT o.operation_seq_num
750 FROM bom_operation_sequences o,
751 bom_operational_routings r
752 WHERE NVL(TRUNC(o.disable_date), NVL(ri.scheduled_date,
753 TRUNC(SYSDATE)) + 1)
754 > NVL(ri.scheduled_date,TRUNC(SYSDATE))
755 AND r.common_routing_sequence_id =
756 o.routing_sequence_id
757 AND (NVL(r.alternate_routing_designator, 'NONE') =
758 NVL(l.alternate_designator, 'NONE')
759 OR
760 (r.alternate_routing_designator IS NULL
761 AND NOT EXISTS (
762 SELECT NULL
763 FROM bom_operational_routings rr
764 WHERE rr.alternate_routing_designator =
765 l.alternate_designator
766 AND rr.assembly_item_id =
767 l.assembly_item_id
768 AND rr.organization_id =
769 ri.organization_id))
770 )
771 AND r.organization_id = ri.organization_id
772 AND r.assembly_item_id = l.assembly_item_id)
773 AND ci.operation_seq_num <> 1
774 AND ci.acd_type IN (action_add, action_change)
775 AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
776 AND ri.change_notice = p_eco
777 AND ri.organization_id = p_organization
778 );
779
780 --
781 -- If use up is specified, only bills for the use up item or bills
782 -- whose components include the use up item are allowed.
783 --
784 DELETE FROM bom_lists l
785 WHERE l.sequence_id = p_list_id
786 AND EXISTS (
787 SELECT NULL
788 FROM eng_revised_items_interface ri,
789 mtl_system_items_b ri_itm
790 WHERE ri.use_up = yes
791 AND ri.change_notice = p_eco
792 AND ri.organization_id = p_organization
793 AND ri.use_up_item_id <> l.assembly_item_id
794 AND ri_itm.organization_id = ri.organization_id
795 AND ri_itm.inventory_item_id = l.assembly_item_id
796 AND NOT EXISTS (
797 SELECT NULL
798 FROM bom_inventory_components c,
799 bom_bill_of_materials b
800 WHERE c.component_item_id = ri.use_up_item_id
801 AND b.bill_sequence_id = c.bill_sequence_id
802 AND b.assembly_item_id = l.assembly_item_id
803 AND b.organization_id = p_organization
804 AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
805 OR (b.alternate_bom_designator = l.alternate_designator) )
806 AND c.implementation_date IS NOT NULL
807
808 AND NVL(TRUNC(c.disable_date), NVL(ri.scheduled_date,TRUNC(SYSDATE)) + 1) >
809 NVL(ri.scheduled_date,TRUNC(SYSDATE))
810 AND TRUNC(c.effectivity_date) <= NVL(ri.scheduled_date,TRUNC(SYSDATE))
811
812 AND ((NVL(c.to_end_item_unit_number, ri.from_end_item_unit_number) >=
813 ri.from_end_item_unit_number
814 AND c.from_end_item_unit_number <= ri.from_end_item_unit_number
815 AND ri_itm.effectivity_control = unit_control)
816 OR
817 ri_itm.effectivity_control = date_control)
818 )
819 );
820
821 x_error_message := NULL;
822 COMMIT;
823 select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
824 FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 4 in Check_Combination ='||
825 to_char(l_bom_lists_count));
826 EXCEPTION
827 WHEN others THEN
828 ROLLBACK TO begin_combo;
829 x_error_message := SUBSTRB(sqlerrm, 1, 150);
830 END Check_Combination;
831
832 ----------------------------- Procedure ---------------------------------
833 --
834 -- NAME
835 -- Check_Component
836 -- DESCRIPTION
837 -- Checks if listed items has valid components.
838 -- REQUIRES
839 -- List - Sequence id of list in BOM_LISTS.
840 -- Organization - Organization id of Bill to be checked.
841 -- Change Order - ECO number of Mass Change Order.
842 -- MODIFIES
843 -- Error buffer - Message if PL/SQL error encountered.
844 -- RETURNS
845 --
846 -- NOTES
847 --
848 -- EXAMPLE
849 --
850 PROCEDURE Check_Component(
851 p_list_id IN bom_lists.sequence_id%TYPE,
852 p_change_order IN eng_revised_items_interface.change_notice%TYPE,
853 p_organization IN eng_revised_items_interface.organization_id%TYPE,
854 x_error_buffer IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
855 IS
856
857 l_subroutine_error EXCEPTION;
858 l_internal_error VARCHAR2(150);
859
860 BEGIN
861 Match_Attributes(p_list_id, p_change_order, p_organization, l_internal_error);
862 IF l_internal_error IS NOT NULL THEN
863 RAISE l_subroutine_error;
864 END IF;
865 Check_Combination(p_list_id, p_change_order, p_organization, l_internal_error);
866 IF l_internal_error IS NOT NULL THEN
867 RAISE l_subroutine_error;
868 END IF;
869 SAVEPOINT begin_check;
870
871 --
872 -- Bills can not be components of itself.
873 --
874 DELETE FROM bom_lists bl
875 WHERE bl.sequence_id = p_list_id
876 AND EXISTS (
877 SELECT NULL
878 FROM bom_inventory_comps_interface c,
879 eng_revised_items_interface r
880 WHERE c.revised_item_sequence_id = r.revised_item_sequence_id
881 AND r.change_notice = p_change_order
882 AND r.organization_id = p_organization
883 AND bl.assembly_item_id = c.component_item_id);
884
885 --
886 -- Duplicate adds
887 --
888 DELETE FROM bom_lists l
889 WHERE l.sequence_id = p_list_id
890 AND EXISTS (
891 SELECT NULL
892 FROM bom_inventory_components c,
893 bom_bill_of_materials b,
894 bom_inventory_comps_interface ci,
895 eng_revised_items_interface ri
896 WHERE c.implementation_date IS NOT NULL
897 AND c.operation_seq_num =
898 NVL(ci.operation_seq_num, default_operation_seq_num)
899 AND c.component_item_id = ci.component_item_id
900 AND c.bill_sequence_id = b.bill_sequence_id
901 AND ci.acd_type = action_add
902 AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
903 AND ri.change_notice = p_change_order
904 AND ri.organization_id = p_organization
905 AND b.assembly_item_id = l.assembly_item_id
906 AND b.organization_id = p_organization
907 AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
908 OR (b.alternate_bom_designator = l.alternate_designator) )
909 AND NVL(TRUNC(c.disable_date), NVL(ri.scheduled_date,TRUNC(SYSDATE)) + 1) >
910 NVL(ri.scheduled_date,TRUNC(SYSDATE))
911 AND TRUNC(c.effectivity_date) <= NVL(ri.scheduled_date,TRUNC(SYSDATE))
912 AND ( ( b.effectivity_control IN (G_STRUCT_DATE_EFF, G_STRUCT_REV_EFF) )
913 OR
914 ( b.effectivity_control IN (G_STRUCT_UNIT_EFF, G_STRUCT_SER_EFF)
915 AND NVL(c.to_end_item_unit_number, ri.from_end_item_unit_number) >=
916 ri.from_end_item_unit_number
917 AND c.from_end_item_unit_number <= ri.from_end_item_unit_number )
918 )
919 );
920
921 --
922 -- Duplicate adds check,
923 -- if action is replace and old comp_item_id!=new comp_item_id
924 -- Included for mass replace of components.
925 --
926 DELETE FROM bom_lists l
927 WHERE l.sequence_id = p_list_id
928 AND EXISTS (
929 SELECT /*+ ORDERED USE_NL (B C CI CIR RI CO RI_ITM) */ NULL
930 FROM bom_structures_b b,
931 bom_components_b c,
932 bom_inventory_comps_interface ci,
933 bom_inventory_comps_interface cir,
934 eng_revised_items_interface ri,
935 bom_components_b co,
936 mtl_system_items_b ri_itm
937 WHERE c.implementation_date IS NOT NULL
938 AND c.operation_seq_num =
939 NVL(ci.operation_seq_num, NVL(cir.operation_seq_num,
940 co.operation_seq_num))
941 AND c.component_item_id = ci.component_item_id
942 AND c.bill_sequence_id = b.bill_sequence_id
943 AND ci.acd_type = action_change
944 AND ci.old_component_sequence_id = cir.component_sequence_id
945 AND ci.component_item_id <> cir.component_item_id
946 AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
947 AND ri.change_notice = p_change_order
948 AND ri.organization_id = p_organization
949 AND co.operation_seq_num = NVL(cir.operation_seq_num,
950 co.operation_seq_num)
951 AND co.bill_sequence_id = c.bill_sequence_id
952 AND co.component_item_id = cir.component_item_id
953 AND b.assembly_item_id = l.assembly_item_id
954 AND b.organization_id = p_organization
955 AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL) OR (b.alternate_bom_designator = l.alternate_designator))
956 AND ri_itm.inventory_item_id = b.assembly_item_id
957 AND ri_itm.organization_id = b.organization_id
958 AND NVL(TRUNC(c.disable_date), NVL(ri.scheduled_date,TRUNC(SYSDATE)) + 1) >
959 NVL(ri.scheduled_date,TRUNC(SYSDATE))
960 AND TRUNC(c.effectivity_date) <= NVL(ri.scheduled_date,TRUNC(SYSDATE))
961
962 AND ((NVL(c.to_end_item_unit_number, ri.from_end_item_unit_number) >=
963 ri.from_end_item_unit_number
964 AND c.from_end_item_unit_number <= ri.from_end_item_unit_number
965 AND ri_itm.effectivity_control = unit_control)
966 OR
967 ri_itm.effectivity_control = date_control)
968 );
969
970 --
971 -- Only manufacturing items can be added as components to manufacturing bills
972 --
973 DELETE FROM bom_lists l
974 WHERE l.sequence_id = p_list_id
975 AND EXISTS (
976 SELECT NULL
977 FROM mtl_system_items_b i,
978 bom_inventory_comps_interface ci,
979 eng_revised_items_interface ri
980 WHERE i.eng_item_flag = 'Y'
981 AND i.inventory_item_id = ci.component_item_id
982 AND i.organization_id = p_organization
983 AND ci.acd_type = action_add
984 AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
985 AND ri.change_notice = p_change_order
986 AND ri.organization_id = p_organization
987 )
988 AND EXISTS (
989 SELECT NULL
990 FROM bom_bill_of_materials b
991 WHERE b.assembly_type = mfg
992 AND b.assembly_item_id = l.assembly_item_id
993 AND b.organization_id = p_organization
994 AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
995 OR (b.alternate_bom_designator = l.alternate_designator) )
996 );
997 --
998 -- Only manufacturing items can be added as components to manufacturing
999 -- bills, if action is replace and old comp_item_id!=new comp_item_id
1000 -- Included for mass replace of components.
1001 --
1002 DELETE FROM bom_lists l
1003 WHERE l.sequence_id = p_list_id
1004 AND EXISTS (
1005 SELECT NULL
1006 FROM mtl_system_items_b i,
1007 bom_inventory_comps_interface ci,
1008 bom_inventory_comps_interface cir,
1009 eng_revised_items_interface ri
1010 WHERE i.eng_item_flag = 'Y'
1011 AND i.inventory_item_id = ci.component_item_id
1012 AND i.organization_id = p_organization
1013 AND ci.acd_type = action_change
1014 AND ci.old_component_sequence_id = cir.component_sequence_id
1015 AND ci.component_item_id <> cir.component_item_id
1016 AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
1017 AND ri.change_notice = p_change_order
1018 AND ri.organization_id = p_organization
1019 )
1020 AND EXISTS
1021 (
1022 SELECT NULL
1023 FROM bom_structures_b b
1024 WHERE
1025 b.assembly_type = mfg
1026 AND b.assembly_item_id = l.assembly_item_id
1027 AND b.organization_id = p_organization
1028 AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
1029 OR (b.alternate_bom_designator = l.alternate_designator) )
1030 );
1031
1032 --
1033 -- Duplicate components deletes and changes
1034 --
1035 DELETE FROM bom_lists l
1036 WHERE l.sequence_id = p_list_id
1037 AND EXISTS (
1038 SELECT NULL
1039 FROM bom_components_b c,
1040 bom_structures_b b,
1041 bom_inventory_comps_interface ci,
1042 eng_revised_items_interface ri,
1043 mtl_system_items_b ri_itm
1044 WHERE c.operation_seq_num =
1045 NVL(ci.operation_seq_num, c.operation_seq_num)
1046 AND c.component_item_id = ci.component_item_id
1047 AND c.bill_sequence_id = b.bill_sequence_id
1048 AND ci.acd_type IN (action_delete, action_change)
1049 AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
1050 AND ri.change_notice = p_change_order
1051 AND ri.organization_id = p_organization
1052 AND b.assembly_item_id = l.assembly_item_id
1053 AND b.organization_id = p_organization
1054 AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
1055 OR (b.alternate_bom_designator = l.alternate_designator) )
1056 AND ri_itm.organization_id = ri.organization_id
1057 AND ri_itm.inventory_item_id = ri.revised_item_id
1058
1059 AND NVL(TRUNC(c.disable_date), NVL(ri.scheduled_date,TRUNC(SYSDATE)) + 1) >
1060 NVL(ri.scheduled_date,TRUNC(SYSDATE))
1061 AND TRUNC(c.effectivity_date) <= NVL(ri.scheduled_date,TRUNC(SYSDATE))
1062
1063 AND ((NVL(c.to_end_item_unit_number, ri.from_end_item_unit_number) >=
1064 ri.from_end_item_unit_number
1065 AND c.from_end_item_unit_number <= ri.from_end_item_unit_number
1066 AND ri_itm.effectivity_control = unit_control)
1067 OR
1068 ri_itm.effectivity_control = date_control)
1069 );
1070
1071 --
1072 -- Other organizations who use our bills as common bills must have the
1073 -- component items in their organization as well.
1074 --
1075 DELETE FROM bom_lists l
1076 WHERE l.sequence_id = p_list_id
1077 AND EXISTS (
1078 SELECT NULL
1079 FROM bom_bill_of_materials cb,
1080 bom_bill_of_materials b,
1081 bom_inventory_comps_interface ci,
1082 eng_revised_items_interface ri
1083 WHERE NOT EXISTS (
1084 SELECT NULL
1085 FROM mtl_system_items_b i
1086 WHERE i.eng_item_flag = DECODE(cb.assembly_type,
1087 1, 'N',
1088 2, i.eng_item_flag)
1089 AND i.bom_enabled_flag = 'Y'
1090 AND i.organization_id = cb.organization_id
1091 AND i.inventory_item_id = ci.component_item_id)
1092 AND cb.organization_id <> b.organization_id
1093 AND cb.common_bill_sequence_id = b.bill_sequence_id
1094 AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
1095 OR (b.alternate_bom_designator = l.alternate_designator) )
1096 AND b.organization_id = p_organization
1097 AND b.assembly_item_id = l.assembly_item_id
1098 AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
1099 AND ri.change_notice = p_change_order
1100 AND ri.organization_id = p_organization);
1101
1102 COMMIT;
1103 x_error_buffer := NULL;
1104 select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
1105 FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt in Check_Component ='||
1106 to_char(l_bom_lists_count));
1107 EXCEPTION
1108 WHEN l_subroutine_error THEN
1109 x_error_buffer := l_internal_error;
1110 WHEN others THEN
1111 ROLLBACK TO begin_check;
1112 x_error_buffer := SUBSTRB(sqlerrm, 1, 150);
1113 END Check_Component;
1114
1115 --------------------------------- Procedure -------------------------------
1116 --
1117 -- NAME
1118 -- Restrict_List
1119 -- DESCRIPTION
1120 -- Deletes from BOM_LISTS according to access permissions granted by the
1121 -- profile options: planning item access, standard item access
1122 -- and model item access. Also culls out bills that would not
1123 -- qualify for a change order because it does not have
1124 -- components which match all the criteria specified in
1125 -- BOM_INVENTORY_COMPS_INTERFACE.
1126 -- REQUIRES
1127 -- Model Item Access - Yes (1) or No (2).
1128 -- Planning Item Access - Yes (1) or No (2).
1129 -- Standard Item Access - Yes (1) or No (2).
1130 -- List id - A sequence id used to identify the list in BOM_LISTS. This
1131 -- may either be a session id or a number obtained from the
1132 -- database sequence, BOM_LISTS_S.
1133 -- Organization Id - Organization stored in ENG_CHANGES_INTERFACE.
1134 -- MODIFIES
1135 -- Error message. If no error, returns NULL.
1136 -- RETURNS
1137 --
1138 -- NOTES
1139 -- Intended to be called from mass_update.
1140 -- EXAMPLE
1141 --
1142 PROCEDURE Restrict_List(
1143 p_list_id IN NUMBER,
1144 p_model_item_access IN NUMBER,
1145 p_planning_item_access IN NUMBER,
1146 p_standard_item_access IN NUMBER,
1147 p_change_order IN VARCHAR2,
1148 p_organization IN NUMBER,
1149 x_error_message IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1150 IS
1151
1152 l_internal_error EXCEPTION;
1153 l_subroutine_error_msg VARCHAR2(150);
1154
1155 BEGIN
1156
1157 SAVEPOINT begin_deletes;
1158
1159 --
1160 -- Eliminate common bills
1161 --
1162 DELETE FROM bom_lists l
1163 WHERE l.sequence_id = p_list_id
1164 AND EXISTS (
1165 SELECT NULL
1166 FROM bom_bill_of_materials b
1167 WHERE b.common_bill_sequence_id <> b.bill_sequence_id
1168 AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
1169 OR (b.alternate_bom_designator = l.alternate_designator) )
1170 AND b.organization_id = p_organization
1171 AND b.assembly_item_id = l.assembly_item_id);
1172
1173 --
1174 -- Eliminate Packaging BOMs
1175 --
1176 DELETE FROM bom_lists l
1177 WHERE l.sequence_id = p_list_id
1178 AND EXISTS (
1179 SELECT NULL
1180 FROM bom_bill_of_materials b
1181 WHERE b.assembly_item_id = l.assembly_item_id
1182 AND b.organization_id = p_organization
1183 AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
1184 OR (b.alternate_bom_designator = l.alternate_designator) )
1185 AND b.structure_type_id IN
1186 (SELECT Structure_Type_Id FROM Bom_Structure_Types_B
1187 WHERE Structure_Type_Name= Bom_Globals.G_PKG_ST_TYPE_NAME
1188 )
1189 );
1190
1191 --
1192 -- Eliminate Product Families
1193 --
1194 DELETE FROM bom_lists l
1195 WHERE l.sequence_id = p_list_id
1196 AND EXISTS (
1197 SELECT NULL
1198 FROM mtl_system_items_b msi
1199 WHERE msi.inventory_item_id = l.assembly_item_id
1200 AND msi.organization_id = p_organization
1201 AND msi.bom_item_type = 5
1202 );
1203
1204 --
1205 -- Check profile values for item access
1206 --
1207 DELETE FROM bom_lists l
1208 WHERE l.sequence_id = p_list_id
1209 AND EXISTS (
1210 SELECT NULL
1211 FROM mtl_system_items_b i
1212 WHERE i.inventory_item_id = l.assembly_item_id
1213 AND i.organization_id = p_organization
1214 AND ((i.bom_item_type = DECODE(p_model_item_access,
1215 no, model_type))
1216 OR
1217 (i.bom_item_type = DECODE(p_model_item_access,
1218 no, option_class_type))
1219 OR
1220 (i.bom_item_type = DECODE(p_planning_item_access,
1221 no, planning_type))
1222 OR
1223 (i.bom_item_type = DECODE(p_standard_item_access,
1224 no, standard_type))
1225 )
1226 );
1227
1228 --
1229 -- Check item type
1230 --
1231 DELETE FROM bom_lists l
1232 WHERE l.sequence_id = p_list_id
1233 AND EXISTS (
1234 SELECT NULL
1235 FROM eng_revised_items_interface r,
1236 mtl_system_items_b i
1237 WHERE r.item_type <> NVL(i.item_type, 'NONE')
1238 AND i.organization_id = p_organization
1239 AND i.inventory_item_id = l.assembly_item_id
1240 AND r.change_notice = p_change_order
1241 AND r.organization_id = p_organization);
1242
1243 --
1244 -- Check if configuration bills are specified
1245 --
1246 DELETE FROM bom_lists l
1247 WHERE l.sequence_id = p_list_id
1248 AND EXISTS (
1249 SELECT NULL
1250 FROM eng_revised_items_interface r,
1251 mtl_system_items_b i
1252 WHERE r.base_item_id <> NVL(i.base_item_id, -1)
1253 AND i.organization_id = r.organization_id
1254 AND i.inventory_item_id = l.assembly_item_id
1255 AND r.change_notice = p_change_order
1256 AND r.organization_id = p_organization);
1257
1258 -- delete from bom_lists those records that have Invalid or Obsolete item status codes
1259 DELETE FROM bom_lists l
1260 WHERE l.sequence_id = p_list_id
1261 AND EXISTS (SELECT NULL
1262 FROM eng_revised_items_interface r,
1263 mtl_system_items_b i,
1264 bom_parameters bp
1265 WHERE (i.inventory_item_status_code in ('Obsolete','Inactive')
1266 OR i.inventory_item_status_code = nvl(bp.bom_delete_status_code, FND_API.G_MISS_CHAR)
1267 OR i.bom_enabled_flag = 'N' )-- Modified for bug 13362684
1268 AND i.organization_id = bp.organization_id -- Added for bug 13362684
1269 AND i.organization_id = r.organization_id
1270 AND i.inventory_item_id = l.assembly_item_id
1271 AND r.change_notice = p_change_order
1272 AND r.organization_id = p_organization);
1273
1274
1275 /* removed, as this comment mentions
1276 --
1277 -- Check unit effectivity. This code may have to be removed/changed in
1278 -- the future once we start supporting unit effectivity for mass change.
1279 --
1280 DELETE FROM bom_lists l
1281 WHERE l.sequence_id = p_list_id
1282 AND EXISTS (
1283 SELECT NULL
1284 FROM mtl_system_items_b i
1285 WHERE i.effectivity_control <> 1
1286 AND i.organization_id = p_organization
1287 AND i.inventory_item_id = l.assembly_item_id);
1288 */
1289
1290 COMMIT;
1291 Check_Component(p_list_id, p_change_order, p_organization, l_subroutine_error_msg);
1292 IF l_subroutine_error_msg IS NOT NULL THEN
1293 RAISE l_internal_error;
1294 END IF;
1295 x_error_message := NULL;
1296 select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
1297 FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt in Restrict_List='||
1298 to_char(l_bom_lists_count));
1299 EXCEPTION
1300 WHEN l_internal_error THEN
1301 x_error_message := l_subroutine_error_msg;
1302 WHEN others THEN
1303 ROLLBACK TO begin_deletes;
1304 x_error_message := SUBSTRB(sqlerrm, 1, 150);
1305 END Restrict_List;
1306
1307 ----------------------------- Function ---------------------------------
1308 --
1309 -- NAME
1310 -- Get_Item_Name
1311 -- DESCRIPTION
1312 -- Given an item id and org_id, returns the item name
1313 -- REQUIRES
1314 -- Item_Id - Id of Inventory Item
1315 -- Org Id - Organization id of Item
1316 -- MODIFIES
1317 --
1318 -- RETURNS
1319 -- VARCHAR2
1320 -- NOTES
1321 --
1322 -- EXAMPLE
1323 --
1324 FUNCTION Get_Item_Name(
1325 p_item_id IN mtl_system_items_vl.inventory_item_id%TYPE,
1326 p_org_id IN mtl_system_items_vl.organization_id%TYPE)
1327 RETURN mtl_system_items_vl.concatenated_segments%TYPE
1328 IS
1329
1330 CURSOR c_item_name IS
1331 SELECT msi.concatenated_segments
1332 FROM mtl_system_items_vl msi
1333 WHERE msi.inventory_item_id = p_item_id
1334 AND msi.organization_id = p_org_id;
1335
1336 l_item_name mtl_system_items_vl.concatenated_segments%TYPE;
1337
1338 BEGIN
1339 IF (p_item_id IS NOT NULL) THEN
1340 OPEN c_item_name;
1341 FETCH c_item_name
1342 INTO l_item_name;
1343 CLOSE c_item_name;
1344 ELSE
1345 l_item_name := NULL;
1346 END IF;
1347
1348 RETURN l_item_name;
1349 END Get_Item_Name;
1350
1351 ----------------------------- Function ---------------------------------
1352 --
1353 -- NAME
1354 -- Get_Location_Name
1355 -- DESCRIPTION
1356 -- Given a Supply Locator id, returns the location name
1357 -- REQUIRES
1358 -- Supply_Locator_Id - Id of Location
1359 -- MODIFIES
1360 --
1361 -- RETURNS
1362 -- VARCHAR2
1363 -- NOTES
1364 --
1365 -- EXAMPLE
1366 --
1367 FUNCTION Get_Location_Name(
1368 p_supply_locator_id IN mtl_item_locations.inventory_location_id%TYPE)
1369 RETURN mtl_item_locations_kfv.CONCATENATED_SEGMENTS%TYPE
1370 IS
1371
1372 CURSOR c_location_name IS
1373 SELECT CONCATENATED_SEGMENTS
1374 FROM mtl_item_locations_kfv
1375 WHERE inventory_location_id = p_supply_locator_id;
1376
1377 l_location_name mtl_item_locations_kfv.CONCATENATED_SEGMENTS%TYPE;
1378
1379 BEGIN
1380
1381 IF (p_supply_locator_id IS NOT NULL) THEN
1382 OPEN c_location_name;
1383 FETCH c_location_name
1384 INTO l_location_name;
1385 CLOSE c_location_name;
1386 ELSE
1387 l_location_name := NULL;
1388 END IF;
1389 RETURN l_location_name;
1390 END Get_Location_Name;
1391
1392 ----------------------------- Procedure ---------------------------------
1393 --
1394 -- NAME
1395 -- mass_update
1396 -- DESCRIPTION
1397 -- Creates Mass Changes to Engineering Change Orders
1398 -- REQUIRES
1399 -- List Id - Id of Bom_List of Bills of Materials to update
1400 -- Change_Order - the name of the Change Order
1401 -- Org_Id - Organization id of Bill
1402 -- Delete_Mco - yes/no to delete Change Order when done processing
1403 -- MODIFIES
1404 --
1405 -- RETURNS
1406 -- Error_Message. If no error, returns NULL.
1407 -- NOTES
1408 --
1409 -- EXAMPLE
1410 --
1411 PROCEDURE mass_update(list_id IN NUMBER,
1412 profile IN ProgramInfoStruct,
1413 change_order IN VARCHAR2,
1414 org_id IN NUMBER,
1415 delete_mco IN NUMBER,
1416 error_message IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
1417
1418 -- ERES change begins :
1419 l_eres_enabled VARCHAR2(10);
1420 -- ERES change ends
1421
1422 -- Bug 1807268
1423 -- Changed the following cursor for performance issue
1424 -- Remove NVL from alternate designator.
1425 -- Also added condition l.organization_id = p_org_id
1426 -- This is done to avoid FULL Table scan on Bom_bill_of_materials Table
1427 -- and thus reduce the Cost
1428 --
1429 CURSOR c_get_bom_list(p_list_id IN bom_lists.sequence_id%TYPE,
1430 p_org_id IN bom_lists.organization_id%TYPE) IS
1431 SELECT l.assembly_item_id,
1432 l.alternate_designator,
1433 b.bill_sequence_id,
1434 itm.effectivity_control,
1435 ri.new_item_revision,
1436 ri.scheduled_date,
1437 ri.mrp_active,
1438 ri.update_wip,
1439 ri.use_up,
1440 ri.use_up_item_id,
1441 ri.revised_item_sequence_id,
1442 ri.increment_rev,
1443 ri.use_up_plan_name,
1444 ri.from_end_item_unit_number
1445 FROM bom_lists l,
1446 eng_revised_items_interface ri,
1447 bom_bill_of_materials b,
1448 mtl_system_items_b itm
1449 WHERE
1450 ((l.alternate_designator IS NULL
1451 AND b.alternate_bom_designator IS NULL)
1452 OR b.alternate_bom_designator = l.alternate_designator)
1453 AND b.organization_id = p_org_id
1454 AND b.assembly_item_id = l.assembly_item_id
1455 AND l.sequence_id = p_list_id
1456 AND l.organization_id = p_org_id
1457 AND ri.change_notice = change_order
1458 AND ri.organization_id = p_org_id
1459 AND itm.inventory_item_id = l.assembly_item_id
1460 AND itm.organization_id = p_org_id
1461 AND ((itm.effectivity_control = unit_control
1462 AND ri.from_end_item_unit_number IS NOT NULL)
1463 OR
1464 (itm.effectivity_control = date_control
1465 AND ri.scheduled_date IS NOT NULL));
1466
1467 --
1468 -- Change Order
1469 --
1470 CURSOR c_eco_rec IS
1471 SELECT i.description,
1472 i.change_order_type_id,
1473 i.responsible_organization_id,
1474 i.cancellation_comments,
1475 i.priority_code,
1476 i.reason_code,
1477 i.estimated_eng_cost,
1478 i.estimated_mfg_cost,
1479 i.approval_list_name
1480 FROM eng_eng_changes_interface i
1481 WHERE i.change_notice = change_order
1482 AND i.organization_id = org_id;
1483
1484 --
1485 -- Component deletes.
1486 --
1487 CURSOR c_comp_delete(
1488 x_scheduled_date bom_inventory_components.effectivity_date%TYPE,
1489 x_bill_sequence_id bom_inventory_components.bill_sequence_id%TYPE,
1490 x_from_unit_number eng_revised_items_interface.from_end_item_unit_number%TYPE)
1491 IS
1492 SELECT /*+ NO_EXPAND */ NVL(o.operation_seq_num,
1493 c.operation_seq_num) operation_sequence_number,
1494 o.component_item_id,
1495 NVL(o.item_num,
1496 c.item_num) item_num,
1497 NVL(o.basis_type,
1498 c.basis_type) basis_type,
1499 NVL(o.component_quantity,
1500 c.component_quantity) component_quantity,
1501 NVL(o.component_yield_factor,
1502 c.component_yield_factor) component_yield_factor,
1503 c.effectivity_date old_effectivity_date,
1504 NVL(o.planning_factor,
1505 c.planning_factor) planning_factor,
1506 NVL(o.quantity_related,
1507 c.quantity_related) quantity_related,
1508 NVL(o.so_basis,
1509 c.so_basis) so_basis,
1510 NVL(o.optional,
1511 c.optional) optional,
1512 NVL(o.mutually_exclusive_options,
1513 c.mutually_exclusive_options) mutually_exclusive_options,
1514 NVL(o.include_in_cost_rollup,
1515 c.include_in_cost_rollup) include_in_cost_rollup,
1516 NVL(o.check_atp,
1517 c.check_atp) check_atp,
1518 NVL(o.shipping_allowed,
1519 c.shipping_allowed) shipping_allowed,
1520 NVL(o.required_to_ship,
1521 c.required_to_ship) required_to_ship,
1522 NVL(o.required_for_revenue,
1523 c.required_for_revenue) required_for_revenue,
1524 NVL(o.include_on_ship_docs,
1525 c.include_on_ship_docs) include_on_ship_docs,
1526 NVL(o.low_quantity,
1527 c.low_quantity) low_quantity,
1528 NVL(o.high_quantity,
1529 c.high_quantity) high_quantity,
1530 ecg_action_delete acd_type,
1531 c.component_sequence_id old_component_sequence_id,
1532 NVL(o.wip_supply_type,
1533 c.wip_supply_type) wip_supply_type,
1534 NVL(o.supply_subinventory,
1535 c.supply_subinventory) supply_subinventory,
1536 NVL(o.supply_locator_id,
1537 c.supply_locator_id) supply_locator_id,
1538 c.from_end_item_unit_number old_from_end_item_unit_number,
1539 ci_itm.bom_item_type,
1540 ri.from_end_item_unit_number,
1541 o.to_end_item_unit_number,
1542 Nvl(o.component_remarks,c.component_remarks)
1543 component_remarks --Bug 3347094
1544 FROM mtl_system_items_b ci_itm,
1545 mtl_system_items_b ri_itm,
1546 bom_bill_of_materials b,
1547 bom_inventory_components c,
1548 bom_inventory_comps_interface o,
1549 eng_revised_items_interface ri
1550 WHERE (c.item_num = o.item_num OR o.item_num IS NULL)
1551 AND (Nvl(c.basis_type,4) = Decode(o.basis_type,FND_API.G_MISS_NUM,4,o.basis_type) OR o.basis_type is NULL) -- 5214239
1552 AND (c.component_quantity = o.component_quantity OR
1553 o.component_quantity IS NULL)
1554 AND (c.component_yield_factor = o.component_yield_factor OR
1555 o.component_yield_factor IS NULL)
1556 AND (c.planning_factor = o.planning_factor OR
1557 o.planning_factor IS NULL)
1558 AND (c.quantity_related = o.quantity_related OR
1559 o.quantity_related IS NULL)
1560 AND (c.so_basis = o.so_basis OR o.so_basis IS NULL)
1561 AND (c.optional = o.optional OR o.optional IS NULL)
1562 AND (c.mutually_exclusive_options = o.mutually_exclusive_options OR
1563 o.mutually_exclusive_options IS NULL)
1564 AND (c.include_in_cost_rollup = o.include_in_cost_rollup OR
1565 o.include_in_cost_rollup IS NULL)
1566 AND (c.check_atp = o.check_atp OR o.check_atp IS NULL)
1567 AND (c.shipping_allowed = o.shipping_allowed OR
1568 o.shipping_allowed IS NULL)
1569 AND (c.required_to_ship = o.required_to_ship OR
1570 o.required_to_ship IS NULL)
1571 AND (c.required_for_revenue = o.required_for_revenue OR
1572 o.required_for_revenue IS NULL)
1573 AND (c.include_on_ship_docs = o.include_on_ship_docs OR
1574 o.include_on_ship_docs IS NULL)
1575 AND (c.low_quantity = o.low_quantity OR o.low_quantity IS NULL)
1576 AND (c.high_quantity = o.high_quantity OR o.high_quantity IS NULL)
1577 AND (c.wip_supply_type = o.wip_supply_type OR
1578 o.wip_supply_type IS NULL)
1579 AND (c.supply_subinventory = o.supply_subinventory OR
1580 o.supply_subinventory IS NULL)
1581 AND (c.supply_locator_id = o.supply_locator_id OR
1582 o.supply_locator_id IS NULL)
1583 AND (c.component_remarks = o.component_remarks OR
1584 o.component_remarks IS NULL)
1585 AND (c.attribute_category = o.attribute_category OR
1586 o.attribute_category IS NULL)
1587 AND (c.attribute1 = o.attribute1 OR o.attribute1 IS NULL)
1588 AND (c.attribute2 = o.attribute2 OR o.attribute2 IS NULL)
1589 AND (c.attribute3 = o.attribute3 OR o.attribute3 IS NULL)
1590 AND (c.attribute4 = o.attribute4 OR o.attribute4 IS NULL)
1591 AND (c.attribute5 = o.attribute5 OR o.attribute5 IS NULL)
1592 AND (c.attribute6 = o.attribute6 OR o.attribute6 IS NULL)
1593 AND (c.attribute7 = o.attribute7 OR o.attribute7 IS NULL)
1594 AND (c.attribute8 = o.attribute8 OR o.attribute8 IS NULL)
1595 AND (c.attribute9 = o.attribute9 OR o.attribute9 IS NULL)
1596 AND (c.attribute10 = o.attribute10 OR o.attribute10 IS NULL)
1597 AND (c.attribute11 = o.attribute11 OR o.attribute11 IS NULL)
1598 AND (c.attribute12 = o.attribute12 OR o.attribute12 IS NULL)
1599 AND (c.attribute13 = o.attribute13 OR o.attribute13 IS NULL)
1600 AND (c.attribute14 = o.attribute14 OR o.attribute14 IS NULL)
1601 AND (c.attribute15 = o.attribute15 OR o.attribute15 IS NULL)
1602 AND c.operation_seq_num = NVL(o.operation_seq_num,
1603 c.operation_seq_num)
1604 AND c.bill_sequence_id = x_bill_sequence_id
1605 AND c.component_item_id = o.component_item_id
1606 AND ci_itm.inventory_item_id = c.component_item_id
1607 AND ci_itm.organization_id = org_id
1608 AND o.acd_type = action_delete
1609 AND o.revised_item_sequence_id = ri.revised_item_sequence_id
1610 AND ri.change_notice = change_order
1611 AND ri.organization_id = org_id
1612 AND b.bill_sequence_id = c.bill_sequence_id
1613 AND ri_itm.inventory_item_id = b.assembly_item_id
1614 AND ri_itm.organization_id = org_id
1615 /* Check for implemenation date is done to avoid mass changes from including
1616 unimplemented components in the mass changes list*/
1617 AND c.implementation_date IS NOT NULL
1618 AND NVL(TRUNC(c.disable_date), NVL(x_scheduled_date,TRUNC(SYSDATE)) + 1) >
1619 NVL(x_scheduled_date,TRUNC(SYSDATE))
1620 AND TRUNC(c.effectivity_date) <= NVL(x_scheduled_date,TRUNC(SYSDATE))
1621
1622 AND ((NVL(c.to_end_item_unit_number, x_from_unit_number) >=
1623 x_from_unit_number
1624 AND c.from_end_item_unit_number <= x_from_unit_number
1625 AND ri_itm.effectivity_control = unit_control)
1626 OR
1627 ri_itm.effectivity_control = date_control);
1628
1629 --
1630 -- Disable component changes, if action_type =action_replace
1631 -- and New component_item_id != old row's component_item_id
1632 -- Included mass replace of components.
1633 CURSOR c_comp_replace (
1634 x_scheduled_date bom_inventory_components.effectivity_date%TYPE,
1635 x_bill_sequence_id bom_inventory_components.bill_sequence_id%TYPE,
1636 x_from_unit_number eng_revised_items_interface.from_end_item_unit_number%TYPE)
1637 IS
1638 SELECT NVL(o.operation_seq_num,
1639 c.operation_seq_num) operation_sequence_number,
1640 n.operation_seq_num new_operation_sequence_number,
1641 o.component_item_id,
1642 NVL(o.item_num, c.item_num) item_num,
1643 NVL(o.basis_type, c.basis_type) basis_type,
1644 NVL(o.component_quantity,
1645 c.component_quantity) component_quantity,
1646 NVL(o.component_yield_factor,
1647 c.component_yield_factor) component_yield_factor,
1648 c.effectivity_date old_effectivity_date,
1649 NVL(o.planning_factor,
1650 c.planning_factor) planning_factor,
1651 NVL(o.quantity_related,
1652 c.quantity_related) quantity_related,
1653 NVL(o.so_basis, c.so_basis) so_basis,
1654 NVL(o.optional, c.optional) optional,
1655 NVL(o.mutually_exclusive_options,
1656 c.mutually_exclusive_options) mutually_exclusive_options,
1657 NVL(o.include_in_cost_rollup,
1658 c.include_in_cost_rollup) include_in_cost_rollup,
1659 NVL(o.check_atp, c.check_atp) check_atp,
1660 NVL(o.shipping_allowed,
1661 c.shipping_allowed) shipping_allowed,
1662 NVL(o.required_to_ship,
1663 c.required_to_ship) required_to_ship,
1664 NVL(o.required_for_revenue,
1665 c.required_for_revenue) required_for_revenue,
1666 NVL(o.include_on_ship_docs,
1667 c.include_on_ship_docs) include_on_ship_docs,
1668 NVL(o.low_quantity,
1669 c.low_quantity) low_quantity,
1670 NVL(o.high_quantity,
1671 c.high_quantity) high_quantity,
1672 ecg_action_delete acd_type,
1673 c.component_sequence_id old_component_sequence_id,
1674 NVL(o.wip_supply_type,
1675 c.wip_supply_type) wip_supply_type,
1676 NVL(o.supply_subinventory,
1677 c.supply_subinventory) supply_subinventory,
1678 NVL(o.supply_locator_id,
1679 c.supply_locator_id) supply_locator_id,
1680 c.from_end_item_unit_number old_from_end_item_unit_number,
1681 ci_itm.bom_item_type,
1682 ri.from_end_item_unit_number,
1683 NVL(o.to_end_item_unit_number, c.to_end_item_unit_number) to_end_item_unit_number,
1684 Nvl(o.component_remarks,c.component_remarks)
1685 component_remarks --Bug 3347094
1686 FROM mtl_system_items_b ci_itm,
1687 mtl_system_items_b ri_itm,
1688 bom_bill_of_materials b,
1689 bom_inventory_components c,
1690 bom_inventory_comps_interface n, -- new attributes
1691 bom_inventory_comps_interface o, -- old attributes
1692 eng_revised_items_interface ri
1693 WHERE n.old_component_sequence_id = o.component_sequence_id
1694 AND (n.component_item_id <> o.component_item_id)
1695 AND (c.item_num = o.item_num OR o.item_num IS NULL)
1696 AND (Nvl(c.basis_type,4) = Decode(o.basis_type,FND_API.G_MISS_NUM,4,o.basis_type) OR o.basis_type IS NULL) -- 5214239
1697 AND (c.component_quantity = o.component_quantity OR
1698 o.component_quantity IS NULL)
1699 AND (c.component_yield_factor = o.component_yield_factor OR
1700 o.component_yield_factor IS NULL)
1701 AND (c.component_remarks = o.component_remarks OR
1702 o.component_remarks IS NULL)
1703 AND (c.attribute_category = o.attribute_category OR
1704 o.attribute_category IS NULL)
1705 AND (c.attribute1 = o.attribute1 OR o.attribute1 IS NULL)
1706 AND (c.attribute2 = o.attribute2 OR o.attribute2 IS NULL)
1707 AND (c.attribute3 = o.attribute3 OR o.attribute3 IS NULL)
1708 AND (c.attribute4 = o.attribute4 OR o.attribute4 IS NULL)
1709 AND (c.attribute5 = o.attribute5 OR o.attribute5 IS NULL)
1710 AND (c.attribute6 = o.attribute6 OR o.attribute6 IS NULL)
1711 AND (c.attribute7 = o.attribute7 OR o.attribute7 IS NULL)
1712 AND (c.attribute8 = o.attribute8 OR o.attribute8 IS NULL)
1713 AND (c.attribute9 = o.attribute9 OR o.attribute9 IS NULL)
1714 AND (c.attribute10 = o.attribute10 OR o.attribute10 IS NULL)
1715 AND (c.attribute11 = o.attribute11 OR o.attribute11 IS NULL)
1716 AND (c.attribute12 = o.attribute12 OR o.attribute12 IS NULL)
1717 AND (c.attribute13 = o.attribute13 OR o.attribute13 IS NULL)
1718 AND (c.attribute14 = o.attribute14 OR o.attribute14 IS NULL)
1719 AND (c.attribute15 = o.attribute15 OR o.attribute15 IS NULL)
1720 AND (c.planning_factor = o.planning_factor OR
1721 o.planning_factor IS NULL)
1722 AND (c.quantity_related = o.quantity_related OR
1723 o.quantity_related IS NULL)
1724 AND (c.so_basis = o.so_basis OR o.so_basis IS NULL)
1725 AND (c.optional = o.optional OR o.optional IS NULL)
1726 AND (c.mutually_exclusive_options = o.mutually_exclusive_options OR
1727 o.mutually_exclusive_options IS NULL)
1728 AND (c.include_in_cost_rollup = o.include_in_cost_rollup OR
1729 o.include_in_cost_rollup IS NULL)
1730 AND (c.check_atp = o.check_atp OR o.check_atp IS NULL)
1731 AND (c.shipping_allowed = o.shipping_allowed OR
1732 o.shipping_allowed IS NULL)
1733 AND (c.required_to_ship = o.required_to_ship OR
1734 o.required_to_ship IS NULL)
1735 AND (c.required_for_revenue = o.required_for_revenue OR
1736 o.required_for_revenue IS NULL)
1737 AND (c.include_on_ship_docs = o.include_on_ship_docs OR
1738 o.include_on_ship_docs IS NULL)
1739 AND (c.low_quantity = o.low_quantity OR o.low_quantity IS NULL)
1740 AND (c.high_quantity = o.high_quantity OR o.high_quantity IS NULL)
1741 AND (c.wip_supply_type = o.wip_supply_type OR
1742 o.wip_supply_type IS NULL)
1743 AND (c.supply_subinventory = o.supply_subinventory OR
1744 o.supply_subinventory IS NULL)
1745 AND (c.supply_locator_id = o.supply_locator_id OR
1746 o.supply_locator_id IS NULL)
1747 AND c.operation_seq_num = NVL(o.operation_seq_num,
1748 c.operation_seq_num)
1749 AND c.bill_sequence_id = x_bill_sequence_id
1750 AND c.component_item_id = o.component_item_id
1751 AND o.acd_type = action_replace
1752 AND o.revised_item_sequence_id = ri.revised_item_sequence_id
1753 AND ci_itm.inventory_item_id = c.component_item_id
1754 AND ci_itm.organization_id = org_id
1755 AND ri.change_notice = change_order
1756 AND ri.organization_id = org_id
1757 AND b.bill_sequence_id = c.bill_sequence_id
1758 AND ri_itm.inventory_item_id = b.assembly_item_id
1759 AND ri_itm.organization_id = org_id
1760 /* Check for implemenation date is done to avoid mass changes from including
1761 unimplemented components in the mass changes list*/
1762 AND c.implementation_date IS NOT NULL
1763 AND NVL(TRUNC(c.disable_date), NVL(x_scheduled_date,TRUNC(SYSDATE)) + 1) >
1764 NVL(x_scheduled_date,TRUNC(SYSDATE))
1765 AND TRUNC(c.effectivity_date) <= NVL(x_scheduled_date,TRUNC(SYSDATE))
1766
1767 AND ((NVL(c.to_end_item_unit_number, x_from_unit_number) >=
1768 x_from_unit_number
1769 AND c.from_end_item_unit_number <= x_from_unit_number
1770 AND ri_itm.effectivity_control = unit_control)
1771 OR
1772 ri_itm.effectivity_control = date_control);
1773
1774 -- Insert component changes.
1775 -- Bug 568258: If replacement values for Supply Type, Subinventory or
1776 -- Locator is null and corresponding search criteria is not null, update
1777 -- component's attributes to null
1778 --
1779 CURSOR c_comp_change (
1780 x_scheduled_date bom_inventory_components.effectivity_date%TYPE,
1781 x_bill_sequence_id bom_inventory_components.bill_sequence_id%TYPE,
1782 x_from_unit_number eng_revised_items_interface.from_end_item_unit_number%TYPE)
1783 IS
1784 SELECT NVL(o.operation_seq_num,
1785 c.operation_seq_num) old_operation_sequence_number,
1786 n.operation_seq_num new_operation_sequence_number,
1787 c.operation_seq_num operation_sequence_number,
1788 n.component_item_id,
1789 NVL(n.item_num, c.item_num) item_num,
1790 NVL(n.basis_type , c.basis_type ) basis_type,
1791 NVL(n.component_quantity,
1792 c.component_quantity) component_quantity,
1793 NVL(n.component_yield_factor,
1794 c.component_yield_factor) component_yield_factor,
1795 DECODE(n.component_item_id,
1796 o.component_item_id,
1797 NVL(x_scheduled_date,TRUNC(SYSDATE)),
1798 NULL) new_effectivity_date,
1799 DECODE(n.component_item_id,
1800 o.component_item_id,
1801 GREATEST(NVL(x_scheduled_date,
1802 TRUNC(SYSDATE)),
1803 n.disable_date),
1804 NULL) disable_date,
1805 NVL(n.component_remarks,
1806 c.component_remarks) component_remarks,
1807 DECODE(n.component_item_id,
1808 o.component_item_id,
1809 c.effectivity_date,
1810 NULL) old_effectivity_date,
1811 NVL(n.planning_factor,
1812 c.planning_factor) planning_factor,
1813 NVL(n.quantity_related,
1814 c.quantity_related) quantity_related,
1815 NVL(n.so_basis, c.so_basis) so_basis,
1816 NVL(n.optional, c.optional) optional,
1817 NVL(n.mutually_exclusive_options,
1818 c.mutually_exclusive_options) mutually_exclusive_options,
1819 NVL(n.include_in_cost_rollup,
1820 c.include_in_cost_rollup) include_in_cost_rollup,
1821 NVL(n.check_atp, c.check_atp) check_atp,
1822 NVL(n.shipping_allowed,
1823 c.shipping_allowed) shipping_allowed,
1824 NVL(n.required_to_ship,
1825 c.required_to_ship) required_to_ship,
1826 NVL(n.required_for_revenue,
1827 c.required_for_revenue) required_for_revenue,
1828 NVL(n.include_on_ship_docs,
1829 c.include_on_ship_docs) include_on_ship_docs,
1830 NVL(n.low_quantity, c.low_quantity) low_quantity,
1831 NVL(n.high_quantity,
1832 c.high_quantity) high_quantity,
1833 DECODE(n.component_item_id,
1834 o.component_item_id,
1835 ecg_action_change,
1836 ecg_action_add) acd_type,
1837 NVL(n.wip_supply_type,
1838 DECODE(o.wip_supply_type, NULL,
1839 c.wip_supply_type, FND_API.G_MISS_NUM)) wip_supply_type, /* bug fix : 9019348 */
1840 NVL(n.supply_subinventory,
1841 DECODE(o.supply_subinventory,
1842 NULL,
1843 c.supply_subinventory,
1844 FND_API.G_MISS_CHAR)) supply_subinventory,
1845 NVL(n.supply_locator_id,
1846 DECODE(o.supply_locator_id,
1847 NULL,
1848 c.supply_locator_id,
1849 FND_API.G_MISS_NUM)) supply_locator_id,
1850 DECODE(n.component_item_id,
1851 o.component_item_id,
1852 c.from_end_item_unit_number,
1853 NULL) old_from_end_item_unit_number,
1854 ci_itm.bom_item_type,
1855 ri.from_end_item_unit_number,
1856 DECODE(n.component_item_id,
1857 o.component_item_id,
1858 GREATEST(x_from_unit_number,
1859 NVL(n.to_end_item_unit_number, c.to_end_item_unit_number)),
1860 NULL) to_end_item_unit_number,
1861
1862 -- added attribute information to resolve BUG# 2784395
1863 NVL(n.attribute_category, c.attribute_category) attribute_category ,
1864 NVL(n.attribute1, c.attribute1) attribute1 ,
1865 NVL(n.attribute2, c.attribute2) attribute2 ,
1866 NVL(n.attribute3, c.attribute3) attribute3 ,
1867 NVL(n.attribute4, c.attribute4) attribute4 ,
1868 NVL(n.attribute5, c.attribute5) attribute5 ,
1869 NVL(n.attribute6, c.attribute6) attribute6 ,
1870 NVL(n.attribute7, c.attribute7) attribute7 ,
1871 NVL(n.attribute8, c.attribute8) attribute8 ,
1872 NVL(n.attribute9, c.attribute9) attribute9 ,
1873 NVL(n.attribute10, c.attribute10) attribute10,
1874 NVL(n.attribute11, c.attribute11) attribute11,
1875 NVL(n.attribute12, c.attribute12) attribute12,
1876 NVL(n.attribute13, c.attribute13) attribute13,
1877 NVL(n.attribute14, c.attribute14) attribute14,
1878 NVL(n.attribute15, c.attribute15) attribute15
1879 -- added attribute information to resolve BUG# 2784395
1880
1881 FROM mtl_system_items_b ci_itm,
1882 mtl_system_items_b ri_itm,
1883 bom_bill_of_materials b,
1884 bom_inventory_components c,
1885 bom_inventory_comps_interface n, -- new attributes
1886 bom_inventory_comps_interface o, -- old attributes
1887 eng_revised_items_interface ri
1888 WHERE n.old_component_sequence_id = o.component_sequence_id
1889 AND (c.item_num = o.item_num OR o.item_num IS NULL)
1890 AND (Nvl(c.basis_type,4) = Decode(o.basis_type,FND_API.G_MISS_NUM,4,o.basis_type) OR o.basis_type is NULL) -- 5214239
1891 AND (c.component_quantity = o.component_quantity OR
1892 o.component_quantity IS NULL)
1893 AND (c.component_yield_factor = o.component_yield_factor OR
1894 o.component_yield_factor IS NULL)
1895 AND (c.component_remarks = o.component_remarks OR
1896 o.component_remarks IS NULL)
1897 AND (c.attribute_category = o.attribute_category OR
1898 o.attribute_category IS NULL)
1899 AND (c.attribute1 = o.attribute1 OR o.attribute1 IS NULL)
1900 AND (c.attribute2 = o.attribute2 OR o.attribute2 IS NULL)
1901 AND (c.attribute3 = o.attribute3 OR o.attribute3 IS NULL)
1902 AND (c.attribute4 = o.attribute4 OR o.attribute4 IS NULL)
1903 AND (c.attribute5 = o.attribute5 OR o.attribute5 IS NULL)
1904 AND (c.attribute6 = o.attribute6 OR o.attribute6 IS NULL)
1905 AND (c.attribute7 = o.attribute7 OR o.attribute7 IS NULL)
1906 AND (c.attribute8 = o.attribute8 OR o.attribute8 IS NULL)
1907 AND (c.attribute9 = o.attribute9 OR o.attribute9 IS NULL)
1908 AND (c.attribute10 = o.attribute10 OR o.attribute10 IS NULL)
1909 AND (c.attribute11 = o.attribute11 OR o.attribute11 IS NULL)
1910 AND (c.attribute12 = o.attribute12 OR o.attribute12 IS NULL)
1911 AND (c.attribute13 = o.attribute13 OR o.attribute13 IS NULL)
1912 AND (c.attribute14 = o.attribute14 OR o.attribute14 IS NULL)
1913 AND (c.attribute15 = o.attribute15 OR o.attribute15 IS NULL)
1914 AND (c.planning_factor = o.planning_factor OR
1915 o.planning_factor IS NULL)
1916 AND (c.quantity_related = o.quantity_related OR
1917 o.quantity_related IS NULL)
1918 AND (c.so_basis = o.so_basis OR o.so_basis IS NULL)
1919 AND (c.optional = o.optional OR o.optional IS NULL)
1920 AND (c.mutually_exclusive_options = o.mutually_exclusive_options OR
1921 o.mutually_exclusive_options IS NULL)
1922 AND (c.include_in_cost_rollup = o.include_in_cost_rollup OR
1923 o.include_in_cost_rollup IS NULL)
1924 AND (c.check_atp = o.check_atp OR o.check_atp IS NULL)
1925 AND (c.shipping_allowed = o.shipping_allowed OR
1926 o.shipping_allowed IS NULL)
1927 AND (c.required_to_ship = o.required_to_ship OR
1928 o.required_to_ship IS NULL)
1929 AND (c.required_for_revenue = o.required_for_revenue OR
1930 o.required_for_revenue IS NULL)
1931 AND (c.include_on_ship_docs = o.include_on_ship_docs OR
1932 o.include_on_ship_docs IS NULL)
1933 AND (c.low_quantity = o.low_quantity OR o.low_quantity IS NULL)
1934 AND (c.high_quantity = o.high_quantity OR o.high_quantity IS NULL)
1935 AND (c.wip_supply_type = o.wip_supply_type OR
1936 o.wip_supply_type IS NULL)
1937 AND (c.supply_subinventory = o.supply_subinventory OR
1938 o.supply_subinventory IS NULL)
1939 AND (c.supply_locator_id = o.supply_locator_id OR
1940 o.supply_locator_id IS NULL)
1941 AND c.operation_seq_num = NVL(o.operation_seq_num,
1942 c.operation_seq_num)
1943 AND c.bill_sequence_id = x_bill_sequence_id
1944 AND c.component_item_id = o.component_item_id
1945 AND o.acd_type = action_replace
1946 AND o.revised_item_sequence_id = ri.revised_item_sequence_id
1947 AND ci_itm.inventory_item_id = c.component_item_id
1948 AND ci_itm.organization_id = org_id
1949 AND ri.change_notice = change_order
1950 AND ri.organization_id = org_id
1951 AND b.bill_sequence_id = c.bill_sequence_id
1952 AND ri_itm.inventory_item_id = b.assembly_item_id
1953 AND ri_itm.organization_id = org_id
1954 /* Check for implemenation date is done to avoid mass changes from including
1955 unimplemented components in the mass changes list*/
1956 AND c.implementation_date IS NOT NULL
1957 AND NVL(TRUNC(c.disable_date), NVL(x_scheduled_date,TRUNC(SYSDATE)) + 1) >
1958 NVL(x_scheduled_date,TRUNC(SYSDATE))
1959 AND TRUNC(c.effectivity_date) <= NVL(x_scheduled_date,TRUNC(SYSDATE))
1960
1961 AND ((NVL(c.to_end_item_unit_number, x_from_unit_number) >=
1962 x_from_unit_number
1963 AND c.from_end_item_unit_number <= x_from_unit_number
1964 AND ri_itm.effectivity_control = unit_control)
1965 OR
1966 ri_itm.effectivity_control = date_control);
1967
1968 --
1969 -- Insert component adds. Insert defaults where mandatory columns were
1970 -- left NULL.
1971 --
1972 CURSOR c_comp_add(x_bill_sequence_id bom_inventory_components.bill_sequence_id%TYPE)
1973 IS
1974 SELECT NVL(i.operation_seq_num,
1975 default_operation_seq_num) operation_seq_num,
1976 i.component_item_id,
1977 i.item_num,
1978 i.basis_type basis_type,
1979 NVL(i.component_quantity,
1980 default_component_quantity) component_quantity,
1981 NVL(i.component_yield_factor,
1982 default_component_yield_factor) component_yield_factor,
1983 i.component_remarks,
1984 NVL(i.planning_factor,
1985 default_planning_factor) planning_factor,
1986 NVL(i.quantity_related,
1987 default_quantity_related) quantity_related,
1988 i.so_basis,
1989 i.optional,
1990 i.mutually_exclusive_options,
1991 NVL(i.include_in_cost_rollup,
1992 default_include_in_cost_rollup) include_in_cost_rollup,
1993 NVL(i.check_atp, default_check_atp) check_atp,
1994 i.shipping_allowed,
1995 i.required_to_ship,
1996 i.required_for_revenue,
1997 i.include_on_ship_docs,
1998 i.low_quantity,
1999 i.high_quantity,
2000 ecg_action_add acd_type,
2001 i.wip_supply_type,
2002 i.supply_subinventory,
2003 i.supply_locator_id,
2004 ci_itm.bom_item_type,
2005 ri.from_end_item_unit_number,
2006 i.to_end_item_unit_number,
2007 GREATEST(ri.scheduled_date, i.disable_date) disable_date,
2008 -- added Attribute information for BUG #2784395
2009 i.attribute_category,
2010 i.attribute1,
2011 i.attribute2,
2012 i.attribute3,
2013 i.attribute4,
2014 i.attribute5,
2015 i.attribute6,
2016 i.attribute7,
2017 i.attribute8,
2018 i.attribute9,
2019 i.attribute10,
2020 i.attribute11,
2021 i.attribute12,
2022 i.attribute13,
2023 i.attribute14,
2024 i.attribute15
2025 -- added Attribute information for BUG #2784395
2026 FROM mtl_system_items_b ci_itm,
2027 mtl_system_items_b bi_itm,
2028 bom_bill_of_materials b,
2029 bom_inventory_comps_interface i,
2030 eng_revised_items_interface ri
2031 WHERE i.acd_type = action_add
2032 AND ci_itm.inventory_item_id = i.component_item_id
2033 AND ci_itm.organization_id = org_id
2034 AND ri.revised_item_sequence_id = i.revised_item_sequence_id
2035 AND ri.change_notice = change_order
2036 AND ri.organization_id = org_id
2037 AND b.bill_sequence_id = x_bill_sequence_id
2038 AND bi_itm.inventory_item_id = b.assembly_item_id
2039 AND bi_itm.organization_id = org_id
2040
2041 AND ((bi_itm.effectivity_control = date_control
2042 AND ci_itm.effectivity_control = date_control)
2043 OR
2044 (bi_itm.effectivity_control = unit_control
2045 AND ci_itm.effectivity_control IN (date_control,unit_control)));
2046
2047 /* Fix for bug 5083488 - Added below cursor to get reference designator rows. */
2048
2049 CURSOR c_add_ref_desg(
2050 x_bill_sequence_id bom_inventory_components.bill_sequence_id%TYPE,
2051 x_scheduled_date bom_inventory_components.effectivity_date%TYPE,
2052 x_from_unit_number eng_revised_items_interface.from_end_item_unit_number%TYPE)
2053 IS
2054 SELECT n.component_item_id,
2055 NVL(o.operation_seq_num,
2056 c.operation_seq_num) operation_sequence_number,
2057 brd.COMPONENT_REFERENCE_DESIGNATOR reference_designator_name,
2058 --nvl(brd.acd_type,1) acd_type,
2059 1 acd_type, --modified for 10039721
2060 brd.REF_DESIGNATOR_COMMENT Ref_Designator_Comment,
2061 brd.Attribute_category,
2062 brd.Attribute1,
2063 brd.Attribute2,
2064 c.component_sequence_id old_component_sequence_id,
2065 brd.attribute3,
2066 brd.attribute4,
2067 brd.attribute5,
2068 brd.attribute7,
2069 brd.attribute6,
2070 brd.attribute8,
2071 brd.attribute9,
2072 brd.attribute11,
2073 brd.attribute10,
2074 brd.attribute12,
2075 brd.attribute13,
2076 brd.attribute14,
2077 brd.attribute15
2078 FROM bom_inventory_components c,
2079 bom_inventory_comps_interface n, -- new attributes
2080 bom_inventory_comps_interface o, -- old attributes
2081 eng_revised_items_interface ri,
2082 bom_reference_designators brd,
2083 mtl_system_items_b ri_itm,
2084 bom_bill_of_materials b
2085 WHERE n.old_component_sequence_id = o.component_sequence_id
2086 AND brd.component_sequence_id = c.component_Sequence_id
2087 AND (n.component_item_id <> o.component_item_id)
2088 AND (c.item_num = o.item_num OR o.item_num IS NULL)
2089 AND (c.component_quantity = o.component_quantity OR
2090 o.component_quantity IS NULL)
2091 AND (c.component_yield_factor = o.component_yield_factor OR
2092 o.component_yield_factor IS NULL)
2093 AND (c.component_remarks = o.component_remarks OR
2094 o.component_remarks IS NULL)
2095 AND (c.attribute_category = o.attribute_category OR
2096 o.attribute_category IS NULL)
2097 AND (c.attribute1 = o.attribute1 OR o.attribute1 IS NULL)
2098 AND (c.attribute2 = o.attribute2 OR o.attribute2 IS NULL)
2099 AND (c.attribute3 = o.attribute3 OR o.attribute3 IS NULL)
2100 AND (c.attribute4 = o.attribute4 OR o.attribute4 IS NULL)
2101 AND (c.attribute5 = o.attribute5 OR o.attribute5 IS NULL)
2102 AND (c.attribute6 = o.attribute6 OR o.attribute6 IS NULL)
2103 AND (c.attribute7 = o.attribute7 OR o.attribute7 IS NULL)
2104 AND (c.attribute8 = o.attribute8 OR o.attribute8 IS NULL)
2105 AND (c.attribute9 = o.attribute9 OR o.attribute9 IS NULL)
2106 AND (c.attribute10 = o.attribute10 OR o.attribute10 IS NULL)
2107 AND (c.attribute11 = o.attribute11 OR o.attribute11 IS NULL)
2108 AND (c.attribute12 = o.attribute12 OR o.attribute12 IS NULL)
2109 AND (c.attribute13 = o.attribute13 OR o.attribute13 IS NULL)
2110 AND (c.attribute14 = o.attribute14 OR o.attribute14 IS NULL)
2111 AND (c.attribute15 = o.attribute15 OR o.attribute15 IS NULL)
2112 AND (c.planning_factor = o.planning_factor OR
2113 o.planning_factor IS NULL)
2114 AND (c.quantity_related = o.quantity_related OR
2115 o.quantity_related IS NULL)
2116 AND (c.so_basis = o.so_basis OR o.so_basis IS NULL)
2117 AND (c.optional = o.optional OR o.optional IS NULL)
2118 AND (c.mutually_exclusive_options = o.mutually_exclusive_options OR
2119 o.mutually_exclusive_options IS NULL)
2120 AND (c.include_in_cost_rollup = o.include_in_cost_rollup OR
2121 o.include_in_cost_rollup IS NULL)
2122 AND (c.check_atp = o.check_atp OR o.check_atp IS NULL)
2123 AND (c.shipping_allowed = o.shipping_allowed OR
2124 o.shipping_allowed IS NULL)
2125 AND (c.required_to_ship = o.required_to_ship OR
2126 o.required_to_ship IS NULL)
2127 AND (c.required_for_revenue = o.required_for_revenue OR
2128 o.required_for_revenue IS NULL)
2129 AND (c.include_on_ship_docs = o.include_on_ship_docs OR
2130 o.include_on_ship_docs IS NULL)
2131 AND (c.low_quantity = o.low_quantity OR o.low_quantity IS NULL)
2132 AND (c.high_quantity = o.high_quantity OR o.high_quantity IS NULL)
2133 AND (c.wip_supply_type = o.wip_supply_type OR
2134 o.wip_supply_type IS NULL)
2135 AND (c.supply_subinventory = o.supply_subinventory OR
2136 o.supply_subinventory IS NULL)
2137 AND (c.supply_locator_id = o.supply_locator_id OR
2138 o.supply_locator_id IS NULL)
2139 AND c.operation_seq_num = NVL(o.operation_seq_num,
2140 c.operation_seq_num)
2141 AND c.bill_sequence_id = x_bill_sequence_id
2142 AND c.component_item_id = o.component_item_id
2143 AND o.acd_type = action_replace
2144 AND o.revised_item_sequence_id = ri.revised_item_sequence_id
2145 AND ri.change_notice = change_order
2146 AND ri.organization_id = org_id
2147 AND b.bill_sequence_id = x_bill_sequence_id
2148 AND ri_itm.inventory_item_id = b.assembly_item_id
2149 AND ri_itm.organization_id = org_id
2150 /* Check for implemenation date is done to avoid mass changes from including
2151 unimplemented components in the mass changes list*/
2152 AND c.implementation_date IS NOT NULL
2153 AND NVL(TRUNC(c.disable_date), NVL(x_scheduled_date,TRUNC(SYSDATE)) + 1) >
2154 NVL(x_scheduled_date,TRUNC(SYSDATE))
2155 AND TRUNC(c.effectivity_date) <= NVL(x_scheduled_date,TRUNC(SYSDATE))
2156
2157 AND ((NVL(c.to_end_item_unit_number, x_from_unit_number) >=
2158 x_from_unit_number
2159 AND c.from_end_item_unit_number <= x_from_unit_number
2160 AND ri_itm.effectivity_control = unit_control)
2161 OR
2162 ri_itm.effectivity_control = date_control)
2163 AND nvl(brd.acd_type,1) <> 3;
2164
2165
2166 /* Bug 2614633 */
2167
2168 CURSOR rev_item IS
2169 select revised_item_id,revised_item_sequence_id,
2170 new_item_revision,organization_id
2171 from eng_revised_items
2172 where change_notice = change_order
2173 and organization_id=org_id;
2174
2175
2176 list_error EXCEPTION;
2177 list_error_msg VARCHAR2(150);
2178 revision_error EXCEPTION;
2179 rev_error_msg VARCHAR2(150);
2180 process_eco_error EXCEPTION;
2181 whoami bompinrv.ProgramInfoStruct;
2182 X_Statement_Number VARCHAR2(3) := '[0]';
2183
2184 --The remaining declarations have been added as part of the change
2185 --to use the ECO Business Object
2186 l_eco_rec Eng_Eco_Pub.Eco_Rec_Type;
2187 l_item_tbl Eng_Eco_Pub.Revised_Item_Tbl_Type;
2188 l_comp_tbl Bom_Bo_Pub.Rev_Component_Tbl_Type;
2189 l_error_tbl Error_Handler.Error_Tbl_Type;
2190 i NUMBER;
2191
2192 --These tables are not used but are required parameters to
2193 --ENG_ECO_PUB.process_eco
2194 l_rev_tbl Eng_Eco_Pub.Eco_Revision_Tbl_Type;
2195 l_ref_designator_tbl Bom_Bo_Pub.Ref_Designator_Tbl_Type;
2196 l_sub_component_tbl Bom_Bo_Pub.Sub_Component_Tbl_Type;
2197 l_rev_operation_tbl Bom_Rtg_Pub.Rev_Operation_Tbl_Type;
2198 l_rev_op_resource_tbl Bom_Rtg_Pub.Rev_Op_Resource_Tbl_Type;
2199 l_rev_sub_resource_tbl Bom_Rtg_Pub.Rev_Sub_Resource_Tbl_Type;
2200
2201 l_org_code VARCHAR2(3);
2202 l_change_type_code VARCHAR2(80); -- Bug 3238295 Changed from 10 to 80
2203 l_department_name VARCHAR2(240); --Bug 2925982 Changes 60 to 240
2204 l_use_up_item_name VARCHAR2(801);
2205 l_revised_item_name VARCHAR2(801);
2206 l_component_item_name VARCHAR2(801);
2207 l_location_name VARCHAR2(81);
2208
2209 l_rev_cnt NUMBER := 0;
2210 l_comp_cnt NUMBER := 0;
2211 l_item_cnt NUMBER := 0;
2212 l_return_status VARCHAR2(1);
2213 l_msg_cnt NUMBER;
2214 l_alternate_bom_code VARCHAR2(10) := NULL; -- Bug 2353962
2215 l_ref_count NUMBER := 0; /* Added this variable to fix bug 5083488. */
2216
2217 -- l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2218 -- l_Token_Tbl Error_Handler.Token_Tbl_Type;
2219
2220 BEGIN
2221
2222 --Default error message in case the mass update fails
2223 --The error message is set to null on successful completion
2224 error_message := 'A fatal error occurred while processing mass_update.';
2225
2226 -- Bug 1807268
2227 -- Added update statement here to update the org_id in bom_lists Table
2228 --
2229 UPDATE bom_lists
2230 SET organization_id = org_id
2231 WHERE sequence_id = list_id;
2232
2233 Restrict_List(list_id,
2234 profile.model_item_access,
2235 profile.planning_item_access,
2236 profile.standard_item_access,
2237 change_order,
2238 org_id,
2239 list_error_msg);
2240
2241 IF list_error_msg IS NOT NULL THEN
2242 RAISE list_error;
2243 END IF;
2244
2245 SAVEPOINT begin_mass_update;
2246
2247 --Need the Organization Code
2248 SELECT organization_code
2249 INTO l_org_code
2250 FROM org_organization_definitions
2251 WHERE organization_id = org_id;
2252
2253 --Popuating PL/SQL record for ECO Header
2254 FOR eco_rec IN c_eco_rec LOOP
2255
2256 --reset the tables
2257 l_item_tbl.DELETE;
2258 l_comp_tbl.DELETE;
2259 l_error_tbl.DELETE;
2260 l_rev_tbl.DELETE;
2261 l_ref_designator_tbl.DELETE;
2262 l_sub_component_tbl.DELETE;
2263 l_rev_operation_tbl.DELETE;
2264 l_rev_op_resource_tbl.DELETE;
2265 l_rev_sub_resource_tbl.DELETE;
2266
2267 l_return_status := 'E';
2268
2269 /*
2270 INSERT INTO eng_engineering_changes(
2271 change_notice,
2272 organization_id,
2273 last_update_date,
2274 last_updated_by,
2275 creation_date,
2276 created_by,
2277 last_update_login,
2278 description,
2279 status_type,
2280 initiation_date,
2281 request_id,
2282 program_application_id,
2283 program_id,
2284 program_update_date,
2285 approval_status_type,
2286 change_order_type_id,
2287 responsible_organization_id,
2288 hierarchy_flag,
2289 organization_hierarchy)
2290 SELECT i.change_notice,
2291 i.organization_id,
2292 SYSDATE,
2293 profile.userid,
2294 SYSDATE,
2295 profile.userid,
2296 profile.loginid,
2297 i.description,
2298 default_status, -- open
2299 SYSDATE,
2300 profile.reqstid,
2301 profile.appid,
2302 profile.progid,
2303 SYSDATE,
2304 default_approval_status, -- approved
2305 i.change_order_type_id,
2306 i.responsible_organization_id,
2307 2,
2308 NULL
2309 FROM eng_eng_changes_interface i
2310 WHERE i.change_notice = change_order
2311 AND i.organization_id = org_id;
2312 */
2313
2314 --Need the Change Order Type Code
2315 IF (eco_rec.change_order_type_id IS NOT NULL) THEN
2316
2317 --SELECT change_order_type
2318 SELECT type_name
2319 INTO l_change_type_code
2320 --FROM eng_change_order_types
2321 FROM eng_change_order_types_vl
2322 WHERE change_order_type_id = eco_rec.change_order_type_id;
2323
2324 ELSE
2325
2326 l_change_type_code := NULL;
2327
2328 END IF;
2329
2330 --Need the Responsible Org Name
2331 IF (eco_rec.responsible_organization_id IS NOT NULL) THEN
2332
2333 SELECT name
2334 INTO l_department_name
2335 FROM hr_all_organization_units
2336 WHERE organization_id = eco_rec.responsible_organization_id;
2337
2338 ELSE
2339
2340 l_department_name := NULL;
2341
2342 END IF;
2343
2344 --Populating PL/SQL record for ECO Header
2345 l_eco_rec.eco_name := change_order;
2346 l_eco_rec.organization_code := l_org_code;
2347 l_eco_rec.change_type_code := l_change_type_code;
2348 -- l_eco_rec.status_type := default_status; --open
2349 l_eco_rec.eco_department_name := l_department_name;
2350 l_eco_rec.priority_code := eco_rec.priority_code;
2351 l_eco_rec.approval_list_name := eco_rec.approval_list_name;
2352 -- l_eco_rec.approval_status_type := default_approval_status;
2353 l_eco_rec.reason_code := eco_rec.reason_code;
2354 l_eco_rec.eng_implementation_cost := eco_rec.estimated_eng_cost;
2355 l_eco_rec.mfg_implementation_cost := eco_rec.estimated_mfg_cost;
2356 l_eco_rec.cancellation_comments := eco_rec.cancellation_comments;
2357 l_eco_rec.description := eco_rec.description;
2358 l_eco_rec.return_status := NULL;
2359 l_eco_rec.transaction_type := 'CREATE';
2360 -- hierarchy_flag not used any more
2361 -- l_eco_rec.hierarchy_flag := 2;
2362 l_eco_rec.organization_hierarchy := NULL;
2363 l_eco_rec.plm_or_erp_change :='ERP'; --Bug 3224337
2364
2365 FOR bom_list IN c_get_bom_list(list_id, org_id) LOOP
2366
2367 l_use_up_item_name := Get_Item_Name(bom_list.use_up_item_id,
2368 org_id);
2369 l_revised_item_name := Get_Item_Name(bom_list.assembly_item_id,
2370 org_id);
2371
2372 bom_list.new_item_revision := NULL;
2373 l_alternate_bom_code := bom_list.alternate_designator; -- Bug 2353962
2374
2375 IF (bom_list.increment_rev = yes AND
2376 bom_list.alternate_designator IS NULL) THEN
2377
2378 whoami.userid := profile.userid;
2379 whoami.reqstid := profile.reqstid;
2380 whoami.appid := profile.appid;
2381 whoami.progid := profile.progid;
2382 whoami.loginid := profile.loginid;
2383
2384 BOMPINRV.increment_revision(
2385 i_item_id => bom_list.assembly_item_id,
2386 i_org_id => org_id,
2387 i_date_time => NVL(bom_list.scheduled_date,
2388 SYSDATE),
2389 who => whoami,
2390 o_out_code => bom_list.new_item_revision,
2391 error_message => rev_error_msg);
2392
2393 IF rev_error_msg IS NOT NULL THEN
2394 raise revision_error;
2395 END IF;
2396
2397 END IF; --increment revision
2398
2399 /*
2400 IF bom_list.new_item_revision IS NOT NULL THEN
2401
2402 UPDATE mtl_item_revisions
2403 SET change_notice = change_order,
2404 ecn_initiation_date = SYSDATE,
2405 revised_item_sequence_id =
2406 bom_list.revised_item_sequence_id
2407 WHERE inventory_item_id = bom_list.assembly_item_id
2408 AND organization_id = org_id
2409 AND revision = bom_list.new_item_revision;
2410
2411 END IF; -- increment revision successful
2412
2413 INSERT INTO eng_revised_items(
2414 change_notice,
2415 organization_id,
2416 revised_item_id,
2417 last_update_date,
2418 last_updated_by,
2419 creation_date,
2420 created_by,
2421 last_update_login,
2422 disposition_type,
2423 new_item_revision,
2424 early_schedule_date,
2425 status_type,
2426 scheduled_date,
2427 bill_sequence_id,
2428 mrp_active,
2429 request_id,
2430 program_application_id,
2431 program_id,
2432 program_update_date,
2433 update_wip,
2434 use_up,
2435 use_up_item_id,
2436 revised_item_sequence_id,
2437 use_up_plan_name,
2438 eco_for_production, --- bug 1890000
2439 from_end_item_unit_number)
2440 VALUES (
2441 change_order,
2442 org_id,
2443 bom_list.assembly_item_id,
2444 SYSDATE,
2445 profile.userid,
2446 SYSDATE,
2447 profile.userid,
2448 profile.loginid,
2449 default_disposition, -- no change required
2450 bom_list.new_item_revision,
2451 LEAST(bom_list.scheduled_date, SYSDATE),
2452 default_status, -- open status
2453 NVL(bom_list.scheduled_date,SYSDATE),
2454 bom_list.bill_sequence_id,
2455 NVL(bom_list.mrp_active, no),
2456 profile.reqstid,
2457 profile.appid,
2458 profile.progid,
2459 SYSDATE,
2460 NVL(bom_list.update_wip, no),
2461 NVL(bom_list.use_up, no),
2462 bom_list.use_up_item_id,
2463 bom_list.revised_item_sequence_id,
2464 bom_list.use_up_plan_name,
2465 2,
2466 bom_list.from_end_item_unit_number);
2467 */
2468
2469 l_item_cnt := l_item_cnt + 1;
2470
2471 l_item_tbl(l_item_cnt).eco_name := change_order;
2472 l_item_tbl(l_item_cnt).organization_code := l_org_code;
2473 l_item_tbl(l_item_cnt).revised_item_name := l_revised_item_name;
2474 l_item_tbl(l_item_cnt).new_revised_item_revision := bom_list.new_item_revision;
2475 l_item_tbl(l_item_cnt).start_effective_date := NVL(bom_list.scheduled_date,
2476 TRUNC(SYSDATE));
2477 -- 2387927 l_item_tbl(l_item_cnt).alternate_bom_code := NULL;
2478 l_item_tbl(l_item_cnt).alternate_bom_code := bom_list.alternate_designator;
2479 l_item_tbl(l_item_cnt).status_type := default_status;
2480 l_item_tbl(l_item_cnt).mrp_active := NVL(bom_list.mrp_active, no);
2481 l_item_tbl(l_item_cnt).use_up_item_name := l_use_up_item_name;
2482 l_item_tbl(l_item_cnt).use_up_plan_name := bom_list.use_up_plan_name;
2483 l_item_tbl(l_item_cnt).disposition_type := default_disposition; -- no change
2484 l_item_tbl(l_item_cnt).update_wip := NVL(bom_list.update_wip, no);
2485
2486 IF bom_list.effectivity_control = unit_control THEN
2487 l_item_tbl(l_item_cnt).earliest_effective_date := NULL;
2488 l_item_tbl(l_item_cnt).from_end_item_unit_number := bom_list.from_end_item_unit_number;
2489 ELSE
2490 l_item_tbl(l_item_cnt).earliest_effective_date := LEAST(NVL(bom_list.scheduled_date,
2491 TRUNC(SYSDATE)),
2492 TRUNC(SYSDATE));
2493 END IF;
2494
2495 l_item_tbl(l_item_cnt).transaction_type := 'CREATE';
2496 l_item_tbl(l_item_cnt).eco_for_production := 2;
2497 --l_item_tbl(l_item_cnt).updated_revised_item_revision :=
2498 l_item_tbl(l_item_cnt).new_effective_date := NVL(bom_list.scheduled_date,
2499 TRUNC(SYSDATE));
2500 --l_item_tbl(l_item_cnt).new_from_end_item_unit_number :=
2501 l_item_tbl(l_item_cnt).return_status := NULL;
2502 l_item_tbl(l_item_cnt).new_routing_revision := NULL;
2503 --l_item_tbl(l_item_cnt).updated_routing_revision :=
2504 l_item_tbl(l_item_cnt).cancel_comments := NULL;
2505 l_item_tbl(l_item_cnt).change_description := NULL;
2506
2507 /*
2508 INSERT INTO eng_current_scheduled_dates(
2509 change_notice,
2510 organization_id,
2511 revised_item_id,
2512 scheduled_date,
2513 last_update_date,
2514 last_updated_by,
2515 creation_date,
2516 created_by,
2517 last_update_login,
2518 schedule_id,
2519 program_application_id,
2520 program_id,
2521 program_update_date,
2522 request_id,
2523 revised_item_sequence_id)
2524 VALUES(
2525 change_order,
2526 org_id,
2527 bom_list.assembly_item_id,
2528 NVL(bom_list.scheduled_date,SYSDATE),
2529 SYSDATE,
2530 profile.userid,
2531 SYSDATE,
2532 profile.userid,
2533 profile.loginid,
2534 eng_current_scheduled_dates_s.NEXTVAL,
2535 profile.appid,
2536 profile.progid,
2537 SYSDATE,
2538 profile.reqstid,
2539 bom_list.revised_item_sequence_id);
2540 */
2541 --
2542 -- Insert component deletes.
2543 --
2544 X_Statement_Number := '[1]';
2545
2546 /* replaced with cursor loop following
2547 INSERT INTO bom_inventory_components(
2548 operation_seq_num,
2549 component_item_id,
2550 last_update_date,
2551 last_updated_by,
2552 creation_date,
2553 created_by,
2554 last_update_login,
2555 item_num,
2556 component_quantity,
2557 component_yield_factor,
2558 effectivity_date,
2559 disable_date,
2560 change_notice,
2561 planning_factor,
2562 quantity_related,
2563 so_basis,
2564 optional,
2565 mutually_exclusive_options,
2566 include_in_cost_rollup,
2567 check_atp,
2568 shipping_allowed,
2569 required_to_ship,
2570 required_for_revenue,
2571 include_on_ship_docs,
2572 low_quantity,
2573 high_quantity,
2574 acd_type,
2575 old_component_sequence_id,
2576 component_sequence_id,
2577 bill_sequence_id,
2578 request_id,
2579 program_application_id,
2580 program_id,
2581 program_update_date,
2582 wip_supply_type,
2583 supply_subinventory,
2584 supply_locator_id,
2585 revised_item_sequence_id,
2586 bom_item_type)
2587 SELECT NVL(i.operation_seq_num, c.operation_seq_num),
2588 i.component_item_id,
2589 SYSDATE,
2590 r.last_updated_by,
2591 SYSDATE,
2592 r.created_by,
2593 r.last_update_login,
2594 NVL(i.item_num, c.item_num),
2595 NVL(i.component_quantity, c.component_quantity),
2596 NVL(i.component_yield_factor,
2597 c.component_yield_factor),
2598 NVL(r.scheduled_date,SYSDATE),
2599 r.scheduled_date,
2600 r.change_notice,
2601 NVL(i.planning_factor, c.planning_factor),
2602 NVL(i.quantity_related, c.quantity_related),
2603 NVL(i.so_basis, c.so_basis),
2604 NVL(i.optional, c.optional),
2605 NVL(i.mutually_exclusive_options,
2606 c.mutually_exclusive_options),
2607 NVL(i.include_in_cost_rollup, c.include_in_cost_rollup),
2608 NVL(i.check_atp, c.check_atp),
2609 NVL(i.shipping_allowed, c.shipping_allowed),
2610 NVL(i.required_to_ship, c.required_to_ship),
2611 NVL(i.required_for_revenue, c.required_for_revenue),
2612 NVL(i.include_on_ship_docs, c.include_on_ship_docs),
2613 NVL(i.low_quantity, c.low_quantity),
2614 NVL(i.high_quantity, c.high_quantity),
2615 ecg_action_delete,
2616 c.component_sequence_id,
2617 bom_inventory_components_s.NEXTVAL,
2618 r.bill_sequence_id,
2619 r.request_id,
2620 r.program_application_id,
2621 r.program_id,
2622 SYSDATE,
2623 NVL(i.wip_supply_type, c.wip_supply_type),
2624 NVL(i.supply_subinventory, c.supply_subinventory),
2625 NVL(i.supply_locator_id, c.supply_locator_id),
2626 r.revised_item_sequence_id,
2627 itm.bom_item_type
2628 FROM mtl_system_items_b itm,
2629 bom_inventory_components c,
2630 bom_inventory_comps_interface i,
2631 eng_revised_items_interface ri,
2632 eng_revised_items r
2633 WHERE (c.item_num = i.item_num OR i.item_num IS NULL)
2634 AND (c.component_quantity = i.component_quantity OR
2635 i.component_quantity IS NULL)
2636 AND (c.component_yield_factor = i.component_yield_factor OR
2637 i.component_yield_factor IS NULL)
2638 AND (c.planning_factor = i.planning_factor OR
2639 i.planning_factor IS NULL)
2640 AND (c.quantity_related = i.quantity_related OR
2641 i.quantity_related IS NULL)
2642 AND (c.so_basis = i.so_basis OR i.so_basis IS NULL)
2643 AND (c.optional = i.optional OR i.optional IS NULL)
2644 AND (c.mutually_exclusive_options = i.mutually_exclusive_options OR
2645 i.mutually_exclusive_options IS NULL)
2646 AND (c.include_in_cost_rollup = i.include_in_cost_rollup OR
2647 i.include_in_cost_rollup IS NULL)
2648 AND (c.check_atp = i.check_atp OR i.check_atp IS NULL)
2649 AND (c.shipping_allowed = i.shipping_allowed OR
2650 i.shipping_allowed IS NULL)
2651 AND (c.required_to_ship = i.required_to_ship OR
2652 i.required_to_ship IS NULL)
2653 AND (c.required_for_revenue = i.required_for_revenue OR
2654 i.required_for_revenue IS NULL)
2655 AND (c.include_on_ship_docs = i.include_on_ship_docs OR
2656 i.include_on_ship_docs IS NULL)
2657 AND (c.low_quantity = i.low_quantity OR i.low_quantity IS NULL)
2658 AND (c.high_quantity = i.high_quantity OR i.high_quantity IS NULL)
2659 AND (c.wip_supply_type = i.wip_supply_type OR
2660 i.wip_supply_type IS NULL)
2661 AND (c.supply_subinventory = i.supply_subinventory OR
2662 i.supply_subinventory IS NULL)
2663 AND (c.supply_locator_id = i.supply_locator_id OR
2664 i.supply_locator_id IS NULL)
2665 AND (c.component_remarks = i.component_remarks
2666 OR i.component_remarks IS NULL)
2667 AND (c.attribute_category = i.attribute_category
2668 OR i.attribute_category IS NULL)
2669 AND (c.attribute1 = i.attribute1 OR i.attribute1 IS NULL)
2670 AND (c.attribute2 = i.attribute2 OR i.attribute2 IS NULL)
2671 AND (c.attribute3 = i.attribute3 OR i.attribute3 IS NULL)
2672 AND (c.attribute4 = i.attribute4 OR i.attribute4 IS NULL)
2673 AND (c.attribute5 = i.attribute5 OR i.attribute5 IS NULL)
2674 AND (c.attribute6 = i.attribute6 OR i.attribute6 IS NULL)
2675 AND (c.attribute7 = i.attribute7 OR i.attribute7 IS NULL)
2676 AND (c.attribute8 = i.attribute8 OR i.attribute8 IS NULL)
2677 AND (c.attribute9 = i.attribute9 OR i.attribute9 IS NULL)
2678 AND (c.attribute10 = i.attribute10 OR i.attribute10 IS NULL)
2679 AND (c.attribute11 = i.attribute11 OR i.attribute11 IS NULL)
2680 AND (c.attribute12 = i.attribute12 OR i.attribute12 IS NULL)
2681 AND (c.attribute13 = i.attribute13 OR i.attribute13 IS NULL)
2682 AND (c.attribute14 = i.attribute14 OR i.attribute14 IS NULL)
2683 AND (c.attribute15 = i.attribute15 OR i.attribute15 IS NULL)
2684 AND NVL(TRUNC(c.disable_date),
2685 r.scheduled_date+1) > r.scheduled_date
2686 AND TRUNC(c.effectivity_date) <= r.scheduled_date
2687 AND c.operation_seq_num = NVL(i.operation_seq_num,
2688 c.operation_seq_num)
2689 AND c.bill_sequence_id = r.bill_sequence_id
2690 AND c.component_item_id = i.component_item_id
2691 AND itm.inventory_item_id = c.component_item_id
2692 AND itm.organization_id = r.organization_id
2693 AND r.revised_item_sequence_id = bom_list.revised_item_sequence_id
2694 AND i.acd_type = action_delete
2695 AND i.revised_item_sequence_id = ri.revised_item_sequence_id
2696 AND ri.change_notice = change_order
2697 AND ri.organization_id = org_id;
2698 */
2699
2700 FOR comp_delete IN c_comp_delete(bom_list.scheduled_date,
2701 bom_list.bill_sequence_id,
2702 bom_list.from_end_item_unit_number)
2703 LOOP
2704
2705 --get lookup values
2706 l_component_item_name := Get_Item_Name(comp_delete.component_item_id, org_id);
2707 l_location_name := Get_Location_Name(comp_delete.supply_locator_id);
2708
2709 FND_FILE.PUT_LINE(FND_FILE.LOG,'Preparing disable of ' ||l_component_item_name||
2710 ' on '||l_revised_item_name);
2711
2712 l_comp_cnt := l_comp_cnt + 1;
2713
2714 l_comp_tbl(l_comp_cnt).eco_name := change_order;
2715 l_comp_tbl(l_comp_cnt).organization_code := l_org_code;
2716 l_comp_tbl(l_comp_cnt).revised_item_name := l_revised_item_name;
2717 l_comp_tbl(l_comp_cnt).new_revised_item_revision := bom_list.new_item_revision;
2718 l_comp_tbl(l_comp_cnt).start_effective_date := NVL(bom_list.scheduled_date,
2719 TRUNC(SYSDATE));
2720 --l_comp_tbl(l_comp_cnt).new_effectivity_date :=
2721 l_comp_tbl(l_comp_cnt).disable_date := bom_list.scheduled_date;
2722 l_comp_tbl(l_comp_cnt).operation_sequence_number := comp_delete.operation_sequence_number;
2723 l_comp_tbl(l_comp_cnt).component_item_name := l_component_item_name;
2724 l_comp_tbl(l_comp_cnt).alternate_bom_code := l_alternate_bom_code; -- Bug 2353962
2725 l_comp_tbl(l_comp_cnt).acd_type := comp_delete.acd_type;
2726 l_comp_tbl(l_comp_cnt).old_effectivity_date := comp_delete.old_effectivity_date;
2727 l_comp_tbl(l_comp_cnt).old_operation_sequence_number := comp_delete.operation_sequence_number;
2728 l_comp_tbl(l_comp_cnt).new_operation_sequence_number := NULL;
2729 l_comp_tbl(l_comp_cnt).item_sequence_number := comp_delete.item_num;
2730 l_comp_tbl(l_comp_cnt).basis_type := comp_delete.basis_type;
2731 l_comp_tbl(l_comp_cnt).quantity_per_assembly := comp_delete.component_quantity;
2732 l_comp_tbl(l_comp_cnt).planning_percent := comp_delete.planning_factor;
2733 l_comp_tbl(l_comp_cnt).projected_yield := comp_delete.component_yield_factor;
2734 l_comp_tbl(l_comp_cnt).include_in_cost_rollup := comp_delete.include_in_cost_rollup;
2735 l_comp_tbl(l_comp_cnt).wip_supply_type := comp_delete.wip_supply_type;
2736 l_comp_tbl(l_comp_cnt).so_basis := comp_delete.so_basis;
2737 l_comp_tbl(l_comp_cnt).optional := comp_delete.optional;
2738 l_comp_tbl(l_comp_cnt).mutually_exclusive := comp_delete.mutually_exclusive_options;
2739 l_comp_tbl(l_comp_cnt).check_atp := comp_delete.check_atp;
2740 l_comp_tbl(l_comp_cnt).shipping_allowed := comp_delete.shipping_allowed;
2741 l_comp_tbl(l_comp_cnt).required_to_ship := comp_delete.required_to_ship;
2742 l_comp_tbl(l_comp_cnt).required_for_revenue := comp_delete.required_for_revenue;
2743 l_comp_tbl(l_comp_cnt).include_on_ship_docs := comp_delete.include_on_ship_docs;
2744 l_comp_tbl(l_comp_cnt).quantity_related := comp_delete.quantity_related;
2745 l_comp_tbl(l_comp_cnt).supply_subinventory := comp_delete.supply_subinventory;
2746 l_comp_tbl(l_comp_cnt).location_name := l_location_name;
2747 l_comp_tbl(l_comp_cnt).minimum_allowed_quantity := comp_delete.low_quantity;
2748 l_comp_tbl(l_comp_cnt).maximum_allowed_quantity := comp_delete.high_quantity;
2749 IF bom_list.effectivity_control = unit_control THEN
2750 l_comp_tbl(l_comp_cnt).old_from_end_item_unit_number := comp_delete.old_from_end_item_unit_number;
2751 l_comp_tbl(l_comp_cnt).from_end_item_unit_number := comp_delete.from_end_item_unit_number;
2752 l_comp_tbl(l_comp_cnt).to_end_item_unit_number := comp_delete.to_end_item_unit_number;
2753 ELSE
2754 l_comp_tbl(l_comp_cnt).old_from_end_item_unit_number := NULL;
2755 l_comp_tbl(l_comp_cnt).from_end_item_unit_number := NULL;
2756 l_comp_tbl(l_comp_cnt).to_end_item_unit_number := NULL;
2757 END IF;
2758 l_comp_tbl(l_comp_cnt).new_routing_revision := NULL;
2759 l_comp_tbl(l_comp_cnt).return_status := NULL;
2760 l_comp_tbl(l_comp_cnt).transaction_type := 'CREATE';
2761 l_comp_tbl(l_comp_cnt).comments := comp_delete.component_remarks; -- Bug 3347094
2762
2763 END LOOP; --comp_delete
2764
2765 --
2766 -- Disable component changes, if action_type =action_replace
2767 -- and New component_item_id != old row's component_item_id
2768 -- Included mass replace of components.
2769 X_Statement_Number := '[2]';
2770
2771 /* replaced with cursor loop following
2772 INSERT INTO bom_inventory_components(
2773 operation_seq_num,
2774 component_item_id,
2775 last_update_date,
2776 last_updated_by,
2777 creation_date,
2778 created_by,
2779 last_update_login,
2780 item_num,
2781 component_quantity,
2782 component_yield_factor,
2783 effectivity_date,
2784 disable_date,
2785 change_notice,
2786 planning_factor,
2787 quantity_related,
2788 so_basis,
2789 optional,
2790 mutually_exclusive_options,
2791 include_in_cost_rollup,
2792 check_atp,
2793 shipping_allowed,
2794 required_to_ship,
2795 required_for_revenue,
2796 include_on_ship_docs,
2797 low_quantity,
2798 high_quantity,
2799 acd_type,
2800 old_component_sequence_id,
2801 component_sequence_id,
2802 bill_sequence_id,
2803 request_id,
2804 program_application_id,
2805 program_id,
2806 program_update_date,
2807 wip_supply_type,
2808 supply_subinventory,
2809 supply_locator_id,
2810 revised_item_sequence_id,
2811 bom_item_type)
2812 SELECT NVL(o.operation_seq_num, c.operation_seq_num),
2813 o.component_item_id,
2814 SYSDATE,
2815 r.last_updated_by,
2816 SYSDATE,
2817 r.created_by,
2818 r.last_update_login,
2819 NVL(o.item_num, c.item_num),
2820 NVL(o.component_quantity, c.component_quantity),
2821 NVL(o.component_yield_factor,
2822 c.component_yield_factor),
2823 NVL(r.scheduled_date,SYSDATE),
2824 r.scheduled_date,
2825 r.change_notice,
2826 NVL(o.planning_factor, c.planning_factor),
2827 NVL(o.quantity_related, c.quantity_related),
2828 NVL(o.so_basis, c.so_basis),
2829 NVL(o.optional, c.optional),
2830 NVL(o.mutually_exclusive_options,
2831 c.mutually_exclusive_options),
2832 NVL(o.include_in_cost_rollup, c.include_in_cost_rollup),
2833 NVL(o.check_atp, c.check_atp),
2834 NVL(o.shipping_allowed, c.shipping_allowed),
2835 NVL(o.required_to_ship, c.required_to_ship),
2836 NVL(o.required_for_revenue, c.required_for_revenue),
2837 NVL(o.include_on_ship_docs, c.include_on_ship_docs),
2838 NVL(o.low_quantity, c.low_quantity),
2839 NVL(o.high_quantity, c.high_quantity),
2840 ecg_action_delete,
2841 c.component_sequence_id,
2842 bom_inventory_components_s.NEXTVAL,
2843 r.bill_sequence_id,
2844 r.request_id,
2845 r.program_application_id,
2846 r.program_id,
2847 SYSDATE,
2848 NVL(o.wip_supply_type, c.wip_supply_type),
2849 NVL(o.supply_subinventory, c.supply_subinventory),
2850 NVL(o.supply_locator_id, c.supply_locator_id),
2851 r.revised_item_sequence_id,
2852 itm.bom_item_type
2853 FROM mtl_system_items_b itm,
2854 bom_inventory_components c,
2855 bom_inventory_comps_interface n, -- new attributes
2856 bom_inventory_comps_interface o, -- old attributes
2857 eng_revised_items_interface ri,
2858 eng_revised_items r
2859 WHERE n.old_component_sequence_id = o.component_sequence_id
2860 AND (n.component_item_id <> o.component_item_id)
2861 AND (c.item_num = o.item_num OR o.item_num IS NULL)
2862 AND (c.component_quantity = o.component_quantity OR
2863 o.component_quantity IS NULL)
2864 AND (c.component_yield_factor = o.component_yield_factor OR
2865 o.component_yield_factor IS NULL)
2866 AND (c.component_remarks = o.component_remarks OR
2867 o.component_remarks IS NULL)
2868 AND (c.attribute_category = o.attribute_category OR
2869 o.attribute_category IS NULL)
2870 AND (c.attribute1 = o.attribute1 OR o.attribute1 IS NULL)
2871 AND (c.attribute2 = o.attribute2 OR o.attribute2 IS NULL)
2872 AND (c.attribute3 = o.attribute3 OR o.attribute3 IS NULL)
2873 AND (c.attribute4 = o.attribute4 OR o.attribute4 IS NULL)
2874 AND (c.attribute5 = o.attribute5 OR o.attribute5 IS NULL)
2875 AND (c.attribute6 = o.attribute6 OR o.attribute6 IS NULL)
2876 AND (c.attribute7 = o.attribute7 OR o.attribute7 IS NULL)
2877 AND (c.attribute8 = o.attribute8 OR o.attribute8 IS NULL)
2878 AND (c.attribute9 = o.attribute9 OR o.attribute9 IS NULL)
2879 AND (c.attribute10 = o.attribute10 OR o.attribute10 IS NULL)
2880 AND (c.attribute11 = o.attribute11 OR o.attribute11 IS NULL)
2881 AND (c.attribute12 = o.attribute12 OR o.attribute12 IS NULL)
2882 AND (c.attribute13 = o.attribute13 OR o.attribute13 IS NULL)
2883 AND (c.attribute14 = o.attribute14 OR o.attribute14 IS NULL)
2884 AND (c.attribute15 = o.attribute15 OR o.attribute15 IS NULL)
2885 AND (c.planning_factor = o.planning_factor OR
2886 o.planning_factor IS NULL)
2887 AND (c.quantity_related = o.quantity_related OR
2888 o.quantity_related IS NULL)
2889 AND (c.so_basis = o.so_basis OR o.so_basis IS NULL)
2890 AND (c.optional = o.optional OR o.optional IS NULL)
2891 AND (c.mutually_exclusive_options = o.mutually_exclusive_options OR
2892 o.mutually_exclusive_options IS NULL)
2893 AND (c.include_in_cost_rollup = o.include_in_cost_rollup OR
2894 o.include_in_cost_rollup IS NULL)
2895 AND (c.check_atp = o.check_atp OR o.check_atp IS NULL)
2896 AND (c.shipping_allowed = o.shipping_allowed OR
2897 o.shipping_allowed IS NULL)
2898 AND (c.required_to_ship = o.required_to_ship OR
2899 o.required_to_ship IS NULL)
2900 AND (c.required_for_revenue = o.required_for_revenue OR
2901 o.required_for_revenue IS NULL)
2902 AND (c.include_on_ship_docs = o.include_on_ship_docs OR
2903 o.include_on_ship_docs IS NULL)
2904 AND (c.low_quantity = o.low_quantity OR o.low_quantity IS NULL)
2905 AND (c.high_quantity = o.high_quantity OR o.high_quantity IS NULL)
2906 AND (c.wip_supply_type = o.wip_supply_type OR
2907 o.wip_supply_type IS NULL)
2908 AND (c.supply_subinventory = o.supply_subinventory OR
2909 o.supply_subinventory IS NULL)
2910 AND (c.supply_locator_id = o.supply_locator_id OR
2911 o.supply_locator_id IS NULL)
2912 AND NVL(TRUNC(c.disable_date),
2913 r.scheduled_date+1) > r.scheduled_date
2914 AND TRUNC(c.effectivity_date) <= r.scheduled_date
2915 AND c.operation_seq_num = NVL(o.operation_seq_num,
2916 c.operation_seq_num)
2917 AND c.bill_sequence_id = r.bill_sequence_id
2918 AND c.component_item_id = o.component_item_id
2919 AND o.acd_type = action_replace
2920 AND o.revised_item_sequence_id = ri.revised_item_sequence_id
2921 AND itm.inventory_item_id = c.component_item_id
2922 AND itm.organization_id = r.organization_id
2923 AND r.revised_item_sequence_id = bom_list.revised_item_sequence_id
2924 AND ri.change_notice = change_order
2925 AND ri.organization_id = org_id;
2926 */
2927
2928 FOR comp_replace IN c_comp_replace(
2929 bom_list.scheduled_date,
2930 bom_list.bill_sequence_id,
2931 bom_list.from_end_item_unit_number) LOOP
2932
2933 --get lookup values
2934 l_component_item_name := Get_Item_Name(comp_replace.component_item_id, org_id);
2935 l_location_name := Get_Location_Name(comp_replace.supply_locator_id);
2936
2937 FND_FILE.PUT_LINE(FND_FILE.LOG,'Preparing disable of ' ||l_component_item_name||
2938 ' on '||l_revised_item_name);
2939
2940 l_comp_cnt := l_comp_cnt + 1;
2941
2942 l_comp_tbl(l_comp_cnt).eco_name := change_order;
2943 l_comp_tbl(l_comp_cnt).organization_code := l_org_code;
2944 l_comp_tbl(l_comp_cnt).new_revised_item_revision := bom_list.new_item_revision;
2945 l_comp_tbl(l_comp_cnt).revised_item_name := l_revised_item_name;
2946 l_comp_tbl(l_comp_cnt).start_effective_date := NVL(bom_list.scheduled_date,
2947 TRUNC(SYSDATE));
2948 --l_comp_tbl(l_comp_cnt).new_effectivity_date :=
2949
2950 l_comp_tbl(l_comp_cnt).disable_date := bom_list.scheduled_date;
2951 l_comp_tbl(l_comp_cnt).operation_sequence_number := comp_replace.operation_sequence_number;
2952 l_comp_tbl(l_comp_cnt).component_item_name := l_component_item_name;
2953 l_comp_tbl(l_comp_cnt).alternate_bom_code := l_alternate_bom_code; -- Bug 2353962
2954 l_comp_tbl(l_comp_cnt).acd_type := comp_replace.acd_type;
2955 l_comp_tbl(l_comp_cnt).old_effectivity_date := comp_replace.old_effectivity_date;
2956 l_comp_tbl(l_comp_cnt).old_operation_sequence_number := comp_replace.operation_sequence_number;
2957 l_comp_tbl(l_comp_cnt).new_operation_sequence_number := comp_replace.new_operation_sequence_number;
2958 l_comp_tbl(l_comp_cnt).item_sequence_number := comp_replace.item_num;
2959 l_comp_tbl(l_comp_cnt).basis_type := comp_replace.basis_type;
2960 l_comp_tbl(l_comp_cnt).quantity_per_assembly := comp_replace.component_quantity;
2961 l_comp_tbl(l_comp_cnt).planning_percent := comp_replace.planning_factor;
2962 l_comp_tbl(l_comp_cnt).projected_yield := comp_replace.component_yield_factor;
2963 l_comp_tbl(l_comp_cnt).include_in_cost_rollup := comp_replace.include_in_cost_rollup;
2964 l_comp_tbl(l_comp_cnt).wip_supply_type := comp_replace.wip_supply_type;
2965 l_comp_tbl(l_comp_cnt).so_basis := comp_replace.so_basis;
2966 l_comp_tbl(l_comp_cnt).optional := comp_replace.optional;
2967 l_comp_tbl(l_comp_cnt).mutually_exclusive := comp_replace.mutually_exclusive_options;
2968 l_comp_tbl(l_comp_cnt).check_atp := comp_replace.check_atp;
2969 l_comp_tbl(l_comp_cnt).shipping_allowed := comp_replace.shipping_allowed;
2970 l_comp_tbl(l_comp_cnt).required_to_ship := comp_replace.required_to_ship;
2971 l_comp_tbl(l_comp_cnt).required_for_revenue := comp_replace.required_for_revenue;
2972 l_comp_tbl(l_comp_cnt).include_on_ship_docs := comp_replace.include_on_ship_docs;
2973 l_comp_tbl(l_comp_cnt).quantity_related := comp_replace.quantity_related;
2974 l_comp_tbl(l_comp_cnt).supply_subinventory := comp_replace.supply_subinventory;
2975 l_comp_tbl(l_comp_cnt).location_name := l_location_name;
2976 l_comp_tbl(l_comp_cnt).minimum_allowed_quantity := comp_replace.low_quantity;
2977 l_comp_tbl(l_comp_cnt).maximum_allowed_quantity := comp_replace.high_quantity;
2978 IF bom_list.effectivity_control = unit_control THEN
2979 l_comp_tbl(l_comp_cnt).old_from_end_item_unit_number := comp_replace.old_from_end_item_unit_number;
2980 l_comp_tbl(l_comp_cnt).from_end_item_unit_number := comp_replace.from_end_item_unit_number;
2981 l_comp_tbl(l_comp_cnt).to_end_item_unit_number := comp_replace.to_end_item_unit_number;
2982 ELSE
2983 l_comp_tbl(l_comp_cnt).old_from_end_item_unit_number := NULL;
2984 l_comp_tbl(l_comp_cnt).from_end_item_unit_number := NULL;
2985 l_comp_tbl(l_comp_cnt).to_end_item_unit_number := NULL;
2986 END IF;
2987 l_comp_tbl(l_comp_cnt).new_routing_revision := NULL;
2988 l_comp_tbl(l_comp_cnt).return_status := NULL;
2989 l_comp_tbl(l_comp_cnt).transaction_type := 'CREATE';
2990 l_comp_tbl(l_comp_cnt).comments := comp_replace.component_remarks; --* Modified for Bug 3347094
2991
2992 END LOOP; --comp_replace
2993
2994 --
2995 -- Insert component changes.
2996 -- Bug 568258: If replacement values for Supply Type, Subinventory or
2997 -- Locator is null and corresponding search criteria is not null, update
2998 -- component's attributes to null
2999 --
3000 X_Statement_Number := '[3]';
3001
3002 /* replaced with cursor loop following
3003 INSERT INTO bom_inventory_components(
3004 operation_seq_num,
3005 component_item_id,
3006 last_update_date,
3007 last_updated_by,
3008 creation_date,
3009 created_by,
3010 last_update_login,
3011 item_num,
3012 component_quantity,
3013 component_yield_factor,
3014 effectivity_date,
3015 disable_date,
3016 change_notice,
3017 component_remarks,
3018 attribute_category,
3019 attribute1,
3020 attribute2,
3021 attribute3,
3022 attribute4,
3023 attribute5,
3024 attribute6,
3025 attribute7,
3026 attribute8,
3027 attribute9,
3028 attribute10,
3029 attribute11,
3030 attribute12,
3031 attribute13,
3032 attribute14,
3033 attribute15,
3034 planning_factor,
3035 quantity_related,
3036 so_basis,
3037 optional,
3038 mutually_exclusive_options,
3039 include_in_cost_rollup,
3040 check_atp,
3041 shipping_allowed,
3042 required_to_ship,
3043 required_for_revenue,
3044 include_on_ship_docs,
3045 low_quantity,
3046 high_quantity,
3047 acd_type,
3048 component_sequence_id,
3049 old_component_sequence_id,
3050 bill_sequence_id,
3051 request_id,
3052 program_application_id,
3053 program_id,
3054 program_update_date,
3055 wip_supply_type,
3056 supply_subinventory,
3057 supply_locator_id,
3058 revised_item_sequence_id,
3059 bom_item_type)
3060 SELECT NVL(n.operation_seq_num, c.operation_seq_num),
3061 n.component_item_id,
3062 SYSDATE,
3063 r.last_updated_by,
3064 SYSDATE,
3065 r.created_by,
3066 r.last_update_login,
3067 NVL(n.item_num, c.item_num),
3068 NVL(n.component_quantity, c.component_quantity),
3069 NVL(n.component_yield_factor, c.component_yield_factor),
3070 NVL(r.scheduled_date,SYSDATE),
3071 GREATEST(r.scheduled_date, n.disable_date),
3072 r.change_notice,
3073 NVL(n.component_remarks, c.component_remarks),
3074 NVL(n.attribute_category, c.attribute_category),
3075 NVL(n.attribute1, c.attribute1),
3076 NVL(n.attribute2, c.attribute2),
3077 NVL(n.attribute3, c.attribute3),
3078 NVL(n.attribute4, c.attribute4),
3079 NVL(n.attribute5, c.attribute5),
3080 NVL(n.attribute6, c.attribute6),
3081 NVL(n.attribute7, c.attribute7),
3082 NVL(n.attribute8, c.attribute8),
3083 NVL(n.attribute9, c.attribute9),
3084 NVL(n.attribute10, c.attribute10),
3085 NVL(n.attribute11, c.attribute11),
3086 NVL(n.attribute12, c.attribute12),
3087 NVL(n.attribute13, c.attribute13),
3088 NVL(n.attribute14, c.attribute14),
3089 NVL(n.attribute15, c.attribute15),
3090 NVL(n.planning_factor, c.planning_factor),
3091 NVL(n.quantity_related, c.quantity_related),
3092 NVL(n.so_basis, c.so_basis),
3093 NVL(n.optional, c.optional),
3094 NVL(n.mutually_exclusive_options,
3095 c.mutually_exclusive_options),
3096 NVL(n.include_in_cost_rollup, c.include_in_cost_rollup),
3097 NVL(n.check_atp, c.check_atp),
3098 NVL(n.shipping_allowed, c.shipping_allowed),
3099 NVL(n.required_to_ship, c.required_to_ship),
3100 NVL(n.required_for_revenue, c.required_for_revenue),
3101 NVL(n.include_on_ship_docs, c.include_on_ship_docs),
3102 NVL(n.low_quantity, c.low_quantity),
3103 NVL(n.high_quantity, c.high_quantity),
3104 DECODE(n.component_item_id, o.component_item_id,
3105 ecg_action_change, ecg_action_add),
3106 bom_inventory_components_s.NEXTVAL,
3107 DECODE(n.component_item_id,
3108 o.component_item_id,
3109 c.component_sequence_id,
3110 bom_inventory_components_s.CURRVAL),
3111 r.bill_sequence_id,
3112 r.request_id,
3113 r.program_application_id,
3114 r.program_id,
3115 SYSDATE,
3116 NVL(n.wip_supply_type,
3117 DECODE(o.wip_supply_type, NULL, c.wip_supply_type, NULL)),
3118 NVL(n.supply_subinventory, DECODE(o.supply_subinventory,
3119 NULL, c.supply_subinventory, NULL)),
3120 NVL(n.supply_locator_id, DECODE(o.supply_locator_id, NULL,
3121 c.supply_locator_id, NULL)),
3122 r.revised_item_sequence_id,
3123 itm.bom_item_type
3124 FROM mtl_system_items_b itm,
3125 bom_inventory_components c,
3126 bom_inventory_comps_interface n, -- new attributes
3127 bom_inventory_comps_interface o, -- old attributes
3128 eng_revised_items_interface ri,
3129 eng_revised_items r
3130 WHERE n.old_component_sequence_id = o.component_sequence_id
3131 AND (c.item_num = o.item_num OR o.item_num IS NULL)
3132 AND (c.component_quantity = o.component_quantity OR
3133 o.component_quantity IS NULL)
3134 AND (c.component_yield_factor = o.component_yield_factor OR
3135 o.component_yield_factor IS NULL)
3136 AND (c.component_remarks = o.component_remarks OR
3137 o.component_remarks IS NULL)
3138 AND (c.attribute_category = o.attribute_category OR
3139 o.attribute_category IS NULL)
3140 AND (c.attribute1 = o.attribute1 OR o.attribute1 IS NULL)
3141 AND (c.attribute2 = o.attribute2 OR o.attribute2 IS NULL)
3142 AND (c.attribute3 = o.attribute3 OR o.attribute3 IS NULL)
3143 AND (c.attribute4 = o.attribute4 OR o.attribute4 IS NULL)
3144 AND (c.attribute5 = o.attribute5 OR o.attribute5 IS NULL)
3145 AND (c.attribute6 = o.attribute6 OR o.attribute6 IS NULL)
3146 AND (c.attribute7 = o.attribute7 OR o.attribute7 IS NULL)
3147 AND (c.attribute8 = o.attribute8 OR o.attribute8 IS NULL)
3148 AND (c.attribute9 = o.attribute9 OR o.attribute9 IS NULL)
3149 AND (c.attribute10 = o.attribute10 OR o.attribute10 IS NULL)
3150 AND (c.attribute11 = o.attribute11 OR o.attribute11 IS NULL)
3151 AND (c.attribute12 = o.attribute12 OR o.attribute12 IS NULL)
3152 AND (c.attribute13 = o.attribute13 OR o.attribute13 IS NULL)
3153 AND (c.attribute14 = o.attribute14 OR o.attribute14 IS NULL)
3154 AND (c.attribute15 = o.attribute15 OR o.attribute15 IS NULL)
3155 AND (c.planning_factor = o.planning_factor OR
3156 o.planning_factor IS NULL)
3157 AND (c.quantity_related = o.quantity_related OR
3158 o.quantity_related IS NULL)
3159 AND (c.so_basis = o.so_basis OR o.so_basis IS NULL)
3160 AND (c.optional = o.optional OR o.optional IS NULL)
3161 AND (c.mutually_exclusive_options = o.mutually_exclusive_options OR
3162 o.mutually_exclusive_options IS NULL)
3163 AND (c.include_in_cost_rollup = o.include_in_cost_rollup OR
3164 o.include_in_cost_rollup IS NULL)
3165 AND (c.check_atp = o.check_atp OR o.check_atp IS NULL)
3166 AND (c.shipping_allowed = o.shipping_allowed OR
3167 o.shipping_allowed IS NULL)
3168 AND (c.required_to_ship = o.required_to_ship OR
3169 o.required_to_ship IS NULL)
3170 AND (c.required_for_revenue = o.required_for_revenue OR
3171 o.required_for_revenue IS NULL)
3172 AND (c.include_on_ship_docs = o.include_on_ship_docs OR
3173 o.include_on_ship_docs IS NULL)
3174 AND (c.low_quantity = o.low_quantity OR o.low_quantity IS NULL)
3175 AND (c.high_quantity = o.high_quantity OR o.high_quantity IS NULL)
3176 AND (c.wip_supply_type = o.wip_supply_type OR
3177 o.wip_supply_type IS NULL)
3178 AND (c.supply_subinventory = o.supply_subinventory OR
3179 o.supply_subinventory IS NULL)
3180 AND (c.supply_locator_id = o.supply_locator_id OR
3181 o.supply_locator_id IS NULL)
3182 AND NVL(TRUNC(c.disable_date),
3183 r.scheduled_date+1) > r.scheduled_date
3184 AND TRUNC(c.effectivity_date) <= r.scheduled_date
3185 AND c.operation_seq_num = NVL(o.operation_seq_num,
3186 c.operation_seq_num)
3187 AND c.bill_sequence_id = r.bill_sequence_id
3188 AND c.component_item_id = o.component_item_id
3189 AND o.acd_type = action_replace
3190 AND o.revised_item_sequence_id = ri.revised_item_sequence_id
3191 AND itm.inventory_item_id = c.component_item_id
3192 AND itm.organization_id = r.organization_id
3193 AND r.revised_item_sequence_id = bom_list.revised_item_sequence_id
3194 AND ri.change_notice = change_order
3195 AND ri.organization_id = org_id;
3196 */
3197
3198 FOR comp_change IN c_comp_change(
3199 bom_list.scheduled_date,
3200 bom_list.bill_sequence_id,
3201 bom_list.from_end_item_unit_number) LOOP
3202
3203 --get lookup values
3204 l_component_item_name := Get_Item_Name(comp_change.component_item_id, org_id);
3205 l_location_name := Get_Location_Name(comp_change.supply_locator_id);
3206
3207 IF (comp_change.acd_type = ecg_action_change) THEN
3208 FND_FILE.PUT_LINE(FND_FILE.LOG,'Preparing update of ' ||l_component_item_name||
3209 ' on '||l_revised_item_name);
3210 ELSE --comp_change.acd_type = ecg_action_add
3211 FND_FILE.PUT_LINE(FND_FILE.LOG,'Preparing add of ' ||l_component_item_name||
3212 ' to '||l_revised_item_name);
3213 END IF;
3214
3215 l_comp_cnt := l_comp_cnt + 1;
3216
3217 l_comp_tbl(l_comp_cnt).eco_name := change_order;
3218 l_comp_tbl(l_comp_cnt).organization_code := l_org_code;
3219 l_comp_tbl(l_comp_cnt).new_revised_item_revision := bom_list.new_item_revision;
3220 l_comp_tbl(l_comp_cnt).revised_item_name := l_revised_item_name;
3221 l_comp_tbl(l_comp_cnt).start_effective_date := NVL(bom_list.scheduled_date,
3222 TRUNC(SYSDATE));
3223 --l_comp_tbl(l_comp_cnt).new_effectivity_date :=
3224 IF bom_list.effectivity_control = date_control THEN
3225 l_comp_tbl(l_comp_cnt).disable_date := comp_change.disable_date;
3226 ELSE
3227 l_comp_tbl(l_comp_cnt).disable_date := NULL;
3228 END IF;
3229 l_comp_tbl(l_comp_cnt).operation_sequence_number := comp_change.operation_sequence_number;
3230 l_comp_tbl(l_comp_cnt).component_item_name := l_component_item_name;
3231 l_comp_tbl(l_comp_cnt).alternate_bom_code := l_alternate_bom_code; -- Bug 2353962
3232 l_comp_tbl(l_comp_cnt).acd_type := comp_change.acd_type;
3233 l_comp_tbl(l_comp_cnt).old_effectivity_date := comp_change.old_effectivity_date;
3234 l_comp_tbl(l_comp_cnt).old_operation_sequence_number := comp_change.old_operation_sequence_number;
3235 l_comp_tbl(l_comp_cnt).new_operation_sequence_number := comp_change.new_operation_sequence_number;
3236 l_comp_tbl(l_comp_cnt).item_sequence_number := comp_change.item_num;
3237 l_comp_tbl(l_comp_cnt).basis_type := comp_change.basis_type;
3238 l_comp_tbl(l_comp_cnt).quantity_per_assembly := comp_change.component_quantity;
3239 l_comp_tbl(l_comp_cnt).planning_percent := comp_change.planning_factor;
3240 l_comp_tbl(l_comp_cnt).projected_yield := comp_change.component_yield_factor;
3241 l_comp_tbl(l_comp_cnt).include_in_cost_rollup := comp_change.include_in_cost_rollup;
3242 l_comp_tbl(l_comp_cnt).wip_supply_type := comp_change.wip_supply_type;
3243 l_comp_tbl(l_comp_cnt).so_basis := comp_change.so_basis;
3244 l_comp_tbl(l_comp_cnt).optional := comp_change.optional;
3245 l_comp_tbl(l_comp_cnt).mutually_exclusive := comp_change.mutually_exclusive_options;
3246 l_comp_tbl(l_comp_cnt).check_atp := comp_change.check_atp;
3247 l_comp_tbl(l_comp_cnt).shipping_allowed := comp_change.shipping_allowed;
3248 l_comp_tbl(l_comp_cnt).required_to_ship := comp_change.required_to_ship;
3249 l_comp_tbl(l_comp_cnt).required_for_revenue := comp_change.required_for_revenue;
3250 l_comp_tbl(l_comp_cnt).include_on_ship_docs := comp_change.include_on_ship_docs;
3251 l_comp_tbl(l_comp_cnt).quantity_related := comp_change.quantity_related;
3252 l_comp_tbl(l_comp_cnt).supply_subinventory := comp_change.supply_subinventory;
3253 l_comp_tbl(l_comp_cnt).location_name := l_location_name;
3254 l_comp_tbl(l_comp_cnt).minimum_allowed_quantity := comp_change.low_quantity;
3255 l_comp_tbl(l_comp_cnt).maximum_allowed_quantity := comp_change.high_quantity;
3256 IF bom_list.effectivity_control = unit_control THEN
3257 l_comp_tbl(l_comp_cnt).old_from_end_item_unit_number := comp_change.old_from_end_item_unit_number;
3258 l_comp_tbl(l_comp_cnt).from_end_item_unit_number := comp_change.from_end_item_unit_number;
3259 l_comp_tbl(l_comp_cnt).to_end_item_unit_number := comp_change.to_end_item_unit_number;
3260 ELSE
3261 l_comp_tbl(l_comp_cnt).old_from_end_item_unit_number := NULL;
3262 l_comp_tbl(l_comp_cnt).from_end_item_unit_number := NULL;
3263 l_comp_tbl(l_comp_cnt).to_end_item_unit_number := NULL;
3264 END IF;
3265 l_comp_tbl(l_comp_cnt).new_routing_revision := NULL;
3266 l_comp_tbl(l_comp_cnt).return_status := NULL;
3267 l_comp_tbl(l_comp_cnt).transaction_type := 'CREATE';
3268 l_comp_tbl(l_comp_cnt).comments := Comp_Change.Component_Remarks; --* Modified for Bug-3347094
3269
3270 -- added Attribute information to resolve BUG #2784395
3271 l_comp_tbl(l_comp_cnt).attribute_category := comp_change.attribute_category;
3272 l_comp_tbl(l_comp_cnt).attribute1 := comp_change.attribute1;
3273 l_comp_tbl(l_comp_cnt).attribute2 := comp_change.attribute2;
3274 l_comp_tbl(l_comp_cnt).attribute3 := comp_change.attribute3;
3275 l_comp_tbl(l_comp_cnt).attribute4 := comp_change.attribute4;
3276 l_comp_tbl(l_comp_cnt).attribute5 := comp_change.attribute5;
3277 l_comp_tbl(l_comp_cnt).attribute6 := comp_change.attribute6;
3278 l_comp_tbl(l_comp_cnt).attribute7 := comp_change.attribute7;
3279 l_comp_tbl(l_comp_cnt).attribute8 := comp_change.attribute8;
3280 l_comp_tbl(l_comp_cnt).attribute9 := comp_change.attribute9;
3281 l_comp_tbl(l_comp_cnt).attribute10 := comp_change.attribute10;
3282 l_comp_tbl(l_comp_cnt).attribute11 := comp_change.attribute11;
3283 l_comp_tbl(l_comp_cnt).attribute12 := comp_change.attribute12;
3284 l_comp_tbl(l_comp_cnt).attribute13 := comp_change.attribute13;
3285 l_comp_tbl(l_comp_cnt).attribute14 := comp_change.attribute14;
3286 l_comp_tbl(l_comp_cnt).attribute15 := comp_change.attribute15;
3287 -- added Attribute information to resolve BUG #2784395
3288
3289 END LOOP; --comp_change
3290
3291 --
3292 -- Insert component adds. Insert defaults where mandatory columns were
3293 -- left NULL.
3294 --
3295 X_Statement_Number := '[4]';
3296
3297 /* replaced with cursor loop following
3298 INSERT INTO bom_inventory_components(
3299 operation_seq_num,
3300 component_item_id,
3301 last_update_date,
3302 last_updated_by,
3303 creation_date,
3304 created_by,
3305 last_update_login,
3306 item_num,
3307 component_quantity,
3308 component_yield_factor,
3309 effectivity_date,
3310 disable_date,
3311 change_notice,
3312 component_remarks,
3313 attribute_category,
3314 attribute1,
3315 attribute2,
3316 attribute3,
3317 attribute4,
3318 attribute5,
3319 attribute6,
3320 attribute7,
3321 attribute8,
3322 attribute9,
3323 attribute10,
3324 attribute11,
3325 attribute12,
3326 attribute13,
3327 attribute14,
3328 attribute15,
3329 planning_factor,
3330 quantity_related,
3331 so_basis,
3332 optional,
3333 mutually_exclusive_options,
3334 include_in_cost_rollup,
3335 check_atp,
3336 shipping_allowed,
3337 required_to_ship,
3338 required_for_revenue,
3339 include_on_ship_docs,
3340 low_quantity,
3341 high_quantity,
3342 acd_type,
3343 old_component_sequence_id,
3344 component_sequence_id,
3345 bill_sequence_id,
3346 request_id,
3347 program_application_id,
3348 program_id,
3349 program_update_date,
3350 wip_supply_type,
3351 supply_subinventory,
3352 supply_locator_id,
3353 revised_item_sequence_id,
3354 bom_item_type)
3355 SELECT NVL(i.operation_seq_num, default_operation_seq_num),
3356 i.component_item_id,
3357 SYSDATE,
3358 r.last_updated_by,
3359 SYSDATE,
3360 r.created_by,
3361 r.last_update_login,
3362 i.item_num,
3363 NVL(i.component_quantity, default_component_quantity),
3364 NVL(i.component_yield_factor,
3365 default_component_yield_factor),
3366 NVL(r.scheduled_date,SYSDATE),
3367 GREATEST(r.scheduled_date, i.disable_date),
3368 r.change_notice,
3369 i.component_remarks,
3370 i.attribute_category,
3371 i.attribute1,
3372 i.attribute2,
3373 i.attribute3,
3374 i.attribute4,
3375 i.attribute5,
3376 i.attribute6,
3377 i.attribute7,
3378 i.attribute8,
3379 i.attribute9,
3380 i.attribute10,
3381 i.attribute11,
3382 i.attribute12,
3383 i.attribute13,
3384 i.attribute14,
3385 i.attribute15,
3386 NVL(i.planning_factor, default_planning_factor),
3387 NVL(i.quantity_related, default_quantity_related),
3388 i.so_basis,
3389 i.optional,
3390 i.mutually_exclusive_options,
3391 NVL(i.include_in_cost_rollup,
3392 default_include_in_cost_rollup),
3393 NVL(i.check_atp, default_check_atp),
3394 i.shipping_allowed,
3395 i.required_to_ship,
3396 i.required_for_revenue,
3397 i.include_on_ship_docs,
3398 i.low_quantity,
3399 i.high_quantity,
3400 ecg_action_add,
3401 bom_inventory_components_s.NEXTVAL,
3402 bom_inventory_components_s.CURRVAL,
3403 r.bill_sequence_id,
3404 r.request_id,
3405 r.program_application_id,
3406 r.program_id,
3407 SYSDATE,
3408 i.wip_supply_type,
3409 i.supply_subinventory,
3410 i.supply_locator_id,
3411 r.revised_item_sequence_id,
3412 itm.bom_item_type
3413 FROM mtl_system_items_b itm,
3414 bom_inventory_comps_interface i,
3415 eng_revised_items_interface ri,
3416 eng_revised_items r
3417 WHERE r.revised_item_sequence_id =
3418 bom_list.revised_item_sequence_id
3419 AND i.acd_type = action_add
3420 AND itm.inventory_item_id = i.component_item_id
3421 AND itm.organization_id = r.organization_id
3422 AND ri.revised_item_sequence_id = i.revisedeco_name
3423 AND ri.change_notice = change_order
3424 AND ri.organization_id = org_id;
3425 */
3426
3427 FOR comp_add IN c_comp_add(bom_list.bill_sequence_id) LOOP
3428
3429 --get lookup values
3430 l_component_item_name := Get_Item_Name(comp_add.component_item_id, org_id);
3431 l_location_name := Get_Location_Name(comp_add.supply_locator_id);
3432
3433 FND_FILE.PUT_LINE(FND_FILE.LOG,'Preparing add of ' ||l_component_item_name||
3434 ' to '||l_revised_item_name);
3435
3436 l_comp_cnt := l_comp_cnt + 1;
3437
3438 l_comp_tbl(l_comp_cnt).eco_name := change_order;
3439 l_comp_tbl(l_comp_cnt).organization_code := l_org_code;
3440 l_comp_tbl(l_comp_cnt).revised_item_name := l_revised_item_name;
3441 l_comp_tbl(l_comp_cnt).new_revised_item_revision := bom_list.new_item_revision;
3442 l_comp_tbl(l_comp_cnt).start_effective_date := NVL(bom_list.scheduled_date,
3443 TRUNC(SYSDATE));
3444 --l_comp_tbl(l_comp_cnt).new_effectivity_date :=
3445 IF bom_list.effectivity_control = date_control THEN
3446 l_comp_tbl(l_comp_cnt).disable_date := comp_add.disable_date;
3447 ELSE
3448 l_comp_tbl(l_comp_cnt).disable_date := NULL;
3449 END IF;
3450 l_comp_tbl(l_comp_cnt).operation_sequence_number := comp_add.operation_seq_num;
3451 l_comp_tbl(l_comp_cnt).component_item_name := l_component_item_name;
3452 l_comp_tbl(l_comp_cnt).alternate_bom_code := l_alternate_bom_code; -- Bug 2353962
3453 l_comp_tbl(l_comp_cnt).acd_type := comp_add.acd_type;
3454 l_comp_tbl(l_comp_cnt).old_effectivity_date := NULL;
3455 l_comp_tbl(l_comp_cnt).old_operation_sequence_number := NULL;
3456 l_comp_tbl(l_comp_cnt).new_operation_sequence_number := NULL;
3457 l_comp_tbl(l_comp_cnt).item_sequence_number := comp_add.item_num;
3458 l_comp_tbl(l_comp_cnt).basis_type := comp_add.basis_type;
3459 l_comp_tbl(l_comp_cnt).quantity_per_assembly := comp_add.component_quantity;
3460 l_comp_tbl(l_comp_cnt).planning_percent := comp_add.planning_factor;
3461 l_comp_tbl(l_comp_cnt).projected_yield := comp_add.component_yield_factor;
3462 l_comp_tbl(l_comp_cnt).include_in_cost_rollup := comp_add.include_in_cost_rollup;
3463 l_comp_tbl(l_comp_cnt).wip_supply_type := comp_add.wip_supply_type;
3464 l_comp_tbl(l_comp_cnt).so_basis := comp_add.so_basis;
3465 l_comp_tbl(l_comp_cnt).optional := comp_add.optional;
3466 l_comp_tbl(l_comp_cnt).mutually_exclusive := comp_add.mutually_exclusive_options;
3467 l_comp_tbl(l_comp_cnt).check_atp := comp_add.check_atp;
3468 l_comp_tbl(l_comp_cnt).shipping_allowed := comp_add.shipping_allowed;
3469 l_comp_tbl(l_comp_cnt).required_to_ship := comp_add.required_to_ship;
3470 l_comp_tbl(l_comp_cnt).required_for_revenue := comp_add.required_for_revenue;
3471 l_comp_tbl(l_comp_cnt).include_on_ship_docs := comp_add.include_on_ship_docs;
3472 l_comp_tbl(l_comp_cnt).quantity_related := comp_add.quantity_related;
3473 l_comp_tbl(l_comp_cnt).supply_subinventory := comp_add.supply_subinventory;
3474 l_comp_tbl(l_comp_cnt).location_name := l_location_name;
3475 l_comp_tbl(l_comp_cnt).minimum_allowed_quantity := comp_add.low_quantity;
3476 l_comp_tbl(l_comp_cnt).maximum_allowed_quantity := comp_add.high_quantity;
3477 l_comp_tbl(l_comp_cnt).old_from_end_item_unit_number := NULL;
3478 IF bom_list.effectivity_control = unit_control THEN
3479 l_comp_tbl(l_comp_cnt).from_end_item_unit_number := comp_add.from_end_item_unit_number;
3480 l_comp_tbl(l_comp_cnt).to_end_item_unit_number := comp_add.to_end_item_unit_number;
3481 ELSE
3482 l_comp_tbl(l_comp_cnt).from_end_item_unit_number := NULL;
3483 l_comp_tbl(l_comp_cnt).to_end_item_unit_number := NULL;
3484 END IF;
3485 l_comp_tbl(l_comp_cnt).new_routing_revision := NULL;
3486 l_comp_tbl(l_comp_cnt).return_status := NULL;
3487 l_comp_tbl(l_comp_cnt).transaction_type := 'CREATE';
3488 l_comp_tbl(l_comp_cnt).comments := Comp_Add.Component_Remarks; --* Modified for Bug-3347094
3489 -- added Attribute information for BUG #2784395
3490 l_comp_tbl(l_comp_cnt).attribute_category := comp_add.attribute_category;
3491 l_comp_tbl(l_comp_cnt).attribute1 := comp_add.attribute1;
3492 l_comp_tbl(l_comp_cnt).attribute2 := comp_add.attribute2;
3493 l_comp_tbl(l_comp_cnt).attribute3 := comp_add.attribute3;
3494 l_comp_tbl(l_comp_cnt).attribute4 := comp_add.attribute4;
3495 l_comp_tbl(l_comp_cnt).attribute5 := comp_add.attribute5;
3496 l_comp_tbl(l_comp_cnt).attribute6 := comp_add.attribute6;
3497 l_comp_tbl(l_comp_cnt).attribute7 := comp_add.attribute7;
3498 l_comp_tbl(l_comp_cnt).attribute8 := comp_add.attribute8;
3499 l_comp_tbl(l_comp_cnt).attribute9 := comp_add.attribute9;
3500 l_comp_tbl(l_comp_cnt).attribute10 := comp_add.attribute10;
3501 l_comp_tbl(l_comp_cnt).attribute11 := comp_add.attribute11;
3502 l_comp_tbl(l_comp_cnt).attribute12 := comp_add.attribute12;
3503 l_comp_tbl(l_comp_cnt).attribute13 := comp_add.attribute13;
3504 l_comp_tbl(l_comp_cnt).attribute14 := comp_add.attribute14;
3505 l_comp_tbl(l_comp_cnt).attribute15 := comp_add.attribute15;
3506 -- added Attribute information for BUG #2784395
3507 END LOOP; --comp_add
3508
3509 /* --not needed since no inserts being done
3510 EXCEPTION
3511 WHEN Dup_Val_On_Index THEN
3512 DELETE FROM bom_inventory_components
3513 WHERE revised_item_sequence_id = bom_list.revised_item_sequence_id;
3514 DELETE FROM eng_current_scheduled_dates
3515 WHERE revised_item_sequence_id = bom_list.revised_item_sequence_id;
3516 DELETE FROM eng_revised_items
3517 WHERE revised_item_sequence_id = bom_list.revised_item_sequence_id;
3518 DELETE FROM mtl_item_revisions
3519 WHERE revised_item_sequence_id = bom_list.revised_item_sequence_id;
3520 END; -- single revised item
3521 */
3522
3523 /* Fix for bug 5083488- Populate Ref Desgs records also.*/
3524 For add_ref_desg in c_add_ref_Desg(
3525 bom_list.bill_sequence_id,
3526 bom_list.scheduled_date,
3527 bom_list.from_end_item_unit_number) LOOP
3528
3529 l_component_item_name := Get_Item_Name(add_ref_Desg.component_item_id, org_id);
3530 l_ref_count := l_ref_count + 1;
3531
3532 l_ref_designator_tbl(l_ref_count).eco_name := change_order;
3533 l_ref_designator_tbl(l_ref_count).organization_code := l_org_code;
3534 l_ref_designator_tbl(l_ref_count).new_revised_item_revision := bom_list.new_item_revision;
3535 l_ref_designator_tbl(l_ref_count).revised_item_name := l_revised_item_name;
3536 l_ref_designator_tbl(l_ref_count).start_effective_date := NVL(bom_list.scheduled_date,
3537 TRUNC(SYSDATE));
3538 l_ref_designator_tbl(l_ref_count).operation_sequence_number := add_ref_Desg.operation_sequence_number;
3539 l_ref_designator_tbl(l_ref_count).Component_Item_Name := l_component_item_name;
3540 l_ref_designator_tbl(l_ref_count).alternate_bom_code := l_alternate_bom_code;
3541 l_ref_designator_tbl(l_ref_count).Reference_Designator_Name := add_ref_Desg.reference_designator_name;
3542 l_ref_designator_tbl(l_ref_count).acd_type := add_ref_Desg.acd_type;
3543 l_ref_designator_tbl(l_ref_count).Ref_Designator_Comment := add_ref_Desg.Ref_Designator_Comment;
3544 l_ref_designator_tbl(l_ref_count).Attribute_category := add_ref_Desg.Attribute_category;
3545 l_ref_designator_tbl(l_ref_count).attribute1 := add_ref_Desg.Attribute1;
3546 l_ref_designator_tbl(l_ref_count).attribute2 := add_ref_Desg.Attribute2;
3547 l_ref_designator_tbl(l_ref_count).attribute3 := add_ref_Desg.Attribute3;
3548 l_ref_designator_tbl(l_ref_count).attribute4 := add_ref_Desg.Attribute4;
3549 l_ref_designator_tbl(l_ref_count).attribute5 := add_ref_Desg.Attribute5;
3550 l_ref_designator_tbl(l_ref_count).Attribute6 := add_ref_Desg.Attribute6;
3551 l_ref_designator_tbl(l_ref_count).Attribute7 := add_ref_Desg.Attribute7;
3552 l_ref_designator_tbl(l_ref_count).Attribute8 := add_ref_Desg.Attribute8;
3553 l_ref_designator_tbl(l_ref_count).Attribute9 := add_ref_Desg.Attribute9;
3554 l_ref_designator_tbl(l_ref_count).Attribute10 := add_ref_Desg.Attribute10;
3555 l_ref_designator_tbl(l_ref_count).Attribute11 := add_ref_Desg.Attribute11;
3556 l_ref_designator_tbl(l_ref_count).Attribute12 := add_ref_Desg.Attribute12;
3557 l_ref_designator_tbl(l_ref_count).Attribute13 := add_ref_Desg.Attribute13;
3558 l_ref_designator_tbl(l_ref_count).Attribute14 := add_ref_Desg.Attribute14;
3559 l_ref_designator_tbl(l_ref_count).Attribute15 := add_ref_Desg.Attribute15;
3560 l_ref_designator_tbl(l_ref_count).New_Routing_Revision := NULL;
3561 l_ref_designator_tbl(l_ref_count).Return_Status := NULL;
3562 l_ref_designator_tbl(l_ref_count).Transaction_Type := 'CREATE';
3563 End Loop; -- add_ref_Desg
3564 /* End of fix for bug 5083488. */
3565
3566 END LOOP; -- revised items
3567
3568 --Only call business object if there are some components
3569 -- Commenting this IF condition to retain the old functionality
3570 -- in 11.5.5. Refer bug 3823876 FP fix for 3762086.
3571 -- IF (l_comp_cnt > 0) THEN
3572
3573 ENG_GLOBALS.g_who_rec.org_id := org_id;
3574 ENG_GLOBALS.g_who_rec.user_id := FND_PROFILE.value('USER_ID');
3575 ENG_GLOBALS.g_who_rec.login_id := FND_PROFILE.value('LOGIN_ID');
3576 ENG_GLOBALS.g_who_rec.prog_appid := FND_PROFILE.value('RESP_APPL_ID');
3577 ENG_GLOBALS.g_who_rec.prog_id := NULL;
3578 ENG_GLOBALS.g_who_rec.req_id := NULL;
3579
3580 FND_GLOBAL.apps_initialize(user_id => ENG_GLOBALS.g_who_rec.user_id,
3581 resp_id => FND_PROFILE.value('RESP_ID'),
3582 resp_appl_id => ENG_GLOBALS.g_who_rec.prog_appid
3583 );
3584
3585 --Initializing the Error Handler
3586 ERROR_HANDLER.Initialize;
3587
3588 -- Set Value for Caller Type
3589 BOM_GLOBALS.Set_Caller_Type(BOM_GLOBALS.G_MASS_CHANGE);
3590
3591 --Now call the Business Object to process the ECOs
3592 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing ECO');
3593 ENG_ECO_PUB.Process_Eco(p_api_version_number => 1.0
3594 , p_init_msg_list => FALSE
3595 , x_return_status => l_return_status
3596 , x_msg_count => l_msg_cnt
3597 , p_bo_identifier => 'ECO'
3598 , p_eco_rec => l_eco_rec
3599 --, p_eco_revision_tbl => l_rev_tbl --empty
3600 , p_revised_item_tbl => l_item_tbl
3601 , p_rev_component_tbl => l_comp_tbl
3602 , p_ref_designator_tbl => l_ref_designator_tbl /* Fix for bug 5083488 - Pass ref degs also */
3603 --, p_sub_component_tbl => l_sub_component_tbl --empty
3604 --, p_rev_operation_tbl => l_rev_operation_tbl --empty
3605 --, p_rev_op_resource_tbl => l_rev_op_resource_tbl --empty
3606 --, p_rev_sub_resource_tbl => l_rev_sub_resource_tbl --empty
3607 , x_ECO_rec => l_eco_rec
3608 , x_eco_revision_tbl => l_rev_tbl
3609 , x_revised_item_tbl => l_item_tbl
3610 , x_rev_component_tbl => l_comp_tbl
3611 , x_ref_designator_tbl => l_ref_designator_tbl --empty
3612 , x_sub_component_tbl => l_sub_component_tbl --empty
3613 , x_rev_operation_tbl => l_rev_operation_tbl --empty
3614 , x_rev_op_resource_tbl => l_rev_op_resource_tbl --empty
3615 , x_rev_sub_resource_tbl => l_rev_sub_resource_tbl --empty
3616 --, p_debug => 'Y'
3617 --, p_output_dir => '/sqlcom/log/v115dlyp'
3618 --, p_debug_filename => 'ECO_BO_Debug.log'
3619 );
3620
3621 IF (NVL(l_return_status,'E') <> 'S') THEN
3622 RAISE process_eco_error;
3623 END IF;
3624
3625 -- ERES change begins :
3626 l_eres_enabled := FND_PROFILE.VALUE('EDR_ERES_ENABLED');
3627 FND_FILE.PUT_LINE(FND_FILE.LOG,'Info: profile EDR_ERES_ENABLED ='||l_eres_enabled||'.');
3628 IF ( NVL( l_eres_enabled, 'N') = 'Y')
3629 THEN
3630 Raise_ECO_Create_Event
3631 ( p_organization_id => org_id
3632 , p_organization_code => l_eco_rec.organization_code
3633 , p_change_notice => l_eco_rec.eco_name
3634 , x_return_status => l_return_status
3635 , x_msg_count => l_msg_cnt);
3636 END IF; -- l_eres_enabled
3637 -- ERES change ends
3638
3639 /* Bug 2614633 - Update change notice value and revised item sequence id in
3640 MTL_ITEM_REVISIONS Table for the newly created Revision by Mass change
3641 */
3642
3643 For item in rev_item
3644 loop
3645
3646 If (item.new_item_revision is not NULL) then
3647
3648 UPDATE mtl_item_revisions_b
3649 SET change_notice = change_order,
3650 ecn_initiation_date = SYSDATE,
3651 revised_item_sequence_id = item.revised_item_sequence_id
3652 WHERE inventory_item_id = item.revised_item_id and
3653 organization_id = item.organization_id and
3654 revision = item.new_item_revision ;
3655
3656 END if;
3657
3658 End loop;
3659
3660 /*
3661 --
3662 -- If Revised Item has:
3663 -- - Check Component ATP = No
3664 -- - Replenish to Order flag = No
3665 -- - Pick Components flag = No
3666 -- - WIP Supply Type not a phantom
3667 -- set component level Check ATP to No
3668 --
3669 UPDATE bom_inventory_components
3670 SET check_atp = no
3671 WHERE revised_item_sequence_id IN (
3672 SELECT r.revised_item_sequence_id
3673 FROM eng_revised_items r,
3674 mtl_system_items_b i
3675 WHERE i.atp_components_flag = 'N'
3676 AND i.pick_components_flag = 'N'
3677 AND i.replenish_to_order_flag = 'N'
3678 AND i.wip_supply_type <> phantom
3679 AND i.inventory_item_id = r.revised_item_id
3680 AND i.organization_id = r.organization_id
3681 AND r.change_notice = change_order
3682 AND r.organization_id = org_id);
3683
3684 --
3685 -- Check ATP must be No if component quantity <= 0
3686 --
3687 UPDATE bom_inventory_components
3688 SET check_atp = no
3689 WHERE component_sequence_id IN (
3690 SELECT component_sequence_id
3691 FROM bom_inventory_components c,
3692 eng_revised_items r
3693 WHERE r.change_notice = change_order
3694 AND r.organization_id = org_id
3695 AND r.revised_item_sequence_id = c.revised_item_sequence_id
3696 AND c.component_quantity <= 0);
3697
3698 --
3699 -- Y = Allowed N = Not Allowed
3700 -- P = Must be Phantom O = Must be Optional
3701 -- Configured items are ATO standard items that have a base item id.
3702 -- ATO items have Replenish to Order flags set to "Y".
3703 -- PTO items have Pick Component flags set to "Y".
3704 --
3705 -- Parent
3706 -- Child |Config ATO Mdl ATO Opt ATO Std PTO Mdl PTO Opt PTO Std
3707 -- ---------------------------------------------------------------------------
3708 -- Planning | N N N N N N N
3709 -- Configured | Y Y Y Y Y Y N
3710 -- ATO Model | P P P N P P N
3711 -- ATO Opt Class | P P P N N N N
3712 -- ATO Standard | Y Y Y Y O O N
3713 -- PTO Model | N N N N P P N
3714 -- PTO Opt Class | N N N N P P N
3715 -- PTO Standard | N N N N Y Y Y
3716 --
3717 -- NOTE: "Not Allowed" is handled by a delete statement in procedure
3718 -- Check_Combination above.
3719 --
3720 UPDATE bom_inventory_components
3721 SET wip_supply_type = phantom
3722 WHERE component_sequence_id IN (
3723 SELECT c.component_sequence_id
3724 FROM mtl_system_items_b i,
3725 mtl_system_items_b ci,
3726 bom_inventory_components c,
3727 eng_revised_items r
3728 WHERE ci.bom_item_type IN (model_type, option_class_type)
3729 AND ci.inventory_item_id = c.component_item_id
3730 AND ci.organization_id = r.organization_id
3731 AND c.revised_item_sequence_id = r.revised_item_sequence_id
3732 AND i.inventory_item_id = r.revised_item_id
3733 AND i.organization_id = r.organization_id
3734 AND r.change_notice = change_order
3735 AND r.organization_id = org_id);
3736
3737 UPDATE bom_inventory_components
3738 SET optional = yes
3739 WHERE component_sequence_id IN (
3740 SELECT c.component_sequence_id
3741 FROM mtl_system_items_b i,
3742 mtl_system_items_b ci,
3743 bom_inventory_components c,
3744 eng_revised_items r
3745 WHERE ci.base_item_id IS NULL
3746 AND ci.replenish_to_order_flag = 'Y'
3747 AND ci.bom_item_type = standard_type
3748 AND i.pick_components_flag = 'Y'
3749 AND i.bom_item_type IN (model_type, option_class_type)
3750 AND ci.inventory_item_id = c.component_item_id
3751 AND ci.organization_id = r.organization_id
3752 AND c.revised_item_sequence_id = r.revised_item_sequence_id
3753 AND i.inventory_item_id = r.revised_item_id
3754 AND i.organization_id = r.organization_id
3755 AND r.change_notice = change_order
3756 AND r.organization_id = org_id);
3757 */
3758
3759 --END IF; --processed -- Commented IF. Bug 3823876.
3760
3761 IF delete_mco = yes THEN
3762
3763 DELETE FROM bom_inventory_comps_interface
3764 WHERE revised_item_sequence_id IN
3765 (SELECT revised_item_sequence_id
3766 FROM eng_revised_items_interface
3767 WHERE change_notice = change_order
3768 AND organization_id = org_id);
3769
3770 DELETE FROM eng_revised_items_interface
3771 WHERE change_notice = change_order
3772 AND organization_id = org_id;
3773
3774 DELETE FROM eng_eng_changes_interface
3775 WHERE change_notice = change_order
3776 AND organization_id = org_id;
3777
3778 END IF; --delete_eco
3779
3780 END LOOP; --eco_rec
3781
3782 COMMIT;
3783
3784 BEGIN
3785
3786 ENG_CHANGE_TEXT_UTIL.Sync_Index ( p_idx_name => 'ENG_CHANGE_IMTEXT_TL_CTX1' );
3787
3788 EXCEPTION
3789
3790 WHEN others THEN
3791 error_message := 'Error in ENG_CHANGE_TEXT_UTIL.Sync_index';
3792 FND_FILE.PUT_LINE(FND_FILE.LOG,error_message);
3793 END;
3794
3795 error_message := NULL;
3796
3797 EXCEPTION
3798
3799 WHEN list_error THEN
3800 error_message := list_error_msg;
3801 FND_FILE.PUT_LINE(FND_FILE.LOG,error_message);
3802
3803 WHEN revision_error THEN
3804 ROLLBACK TO begin_mass_update;
3805 error_message := rev_error_msg;
3806 FND_FILE.PUT_LINE(FND_FILE.LOG,error_message);
3807
3808 WHEN process_eco_error THEN
3809 ROLLBACK TO begin_mass_update;
3810 Error_Handler.Get_Message_List( x_message_list => l_error_tbl);
3811 i:=0;
3812 FOR i IN 1..l_error_tbl.COUNT LOOP
3813 FND_FILE.PUT_LINE(FND_FILE.LOG,'Entity Id: '||l_error_tbl(i).entity_id);
3814 FND_FILE.PUT_LINE(FND_FILE.LOG,'Index: '||l_error_tbl(i).entity_index);
3815 FND_FILE.PUT_LINE(FND_FILE.LOG,'Mesg: '||l_error_tbl(i).message_text);
3816 END LOOP;
3817 error_message := 'Error processing ECO Business Object';
3818 FND_FILE.PUT_LINE(FND_FILE.LOG,error_message);
3819
3820 WHEN others THEN
3821 error_message := X_Statement_Number||SUBSTRB(sqlerrm, 1, 150);
3822 FND_FILE.PUT_LINE(FND_FILE.LOG,error_message);
3823 ROLLBACK TO begin_mass_update;
3824
3825 END mass_update;
3826
3827 END BOMPKMUD;