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