[Home] [Help]
PACKAGE BODY: APPS.EGO_ICC_STRUCTURE_PVT
Source
1 PACKAGE BODY EGO_ICC_STRUCTURE_PVT AS
2 /* $Header: egoistpb.pls 120.0.12010000.13 2009/09/10 19:45:00 sisankar ship $ */
3
4 date_fmt varchar2(25) := 'yyyy/mm/dd hh24:mi:ss';
5 type item_revision_record is record
6 (revision_id Number,
7 revision Varchar2(10),
8 start_date Date,
9 end_date Date);
10
11 /* This PL/SQL table is used storing and retrieving for item revision details. */
12 type revision_rec is table of item_revision_record index by binary_integer;
13 v_item_revisions_tbl revision_rec;
14
15 type rev_index is table of number index by binary_integer;
16 v_rev_index rev_index;
17
18 G_COMP_ITEM_NAME VARCHAR2(1000);
19 G_ASSY_ITEM_NAME VARCHAR2(1000);
20 G_ALTCODE VARCHAR2(10);
21 G_EFF_FROM VARCHAR2(80);
22
23 /* UT Fix : Added to store assembly_item_id for procedures create_structure_inherit and inherit_icc_components. */
24 G_INV_ITEM_ID NUMBER;
25
26 /*
27 * This Procedure will delete the user attributes for components.
28 */
29 Procedure Delete_Comp_User_Attrs(p_comp_seq_id IN NUMBER)
30 IS
31 BEGIN
32
33 delete from BOM_COMPONENTS_EXT_B
34 where component_sequence_id = p_comp_seq_id;
35
36 delete from BOM_COMPONENTS_EXT_TL
37 where component_sequence_id = p_comp_seq_id;
38
39 END Delete_Comp_User_Attrs;
40
41 Procedure Create_Default_Header(p_item_catalog_grp_id IN NUMBER)
42 IS
43 l_create_header Number := 0;
44 l_bill_sequence_id Number;
45
46 cursor Get_str_catalog_hierarchy
47 IS
48 select structures.bill_sequence_id
49 from ( select item_catalog_group_id
50 from mtl_item_catalog_groups_b
51 connect by prior parent_catalog_group_id = item_catalog_group_id
52 start with item_catalog_group_id = p_item_catalog_grp_id ) icc,
53 BOM_STRUCTURES_B structures
54 where structures.pk1_value = icc.item_catalog_group_id
55 and structures.obj_name = 'EGO_CATALOG_GROUP'
56 and rownum = 1;
57
58 Begin
59
60 /* We need to create structure header only for versioned ICCs which doesn't have structure header already. */
61
62 select 1
63 into l_create_header
64 from dual
65 where exists (select 1
66 from EGO_MTL_CATALOG_GRP_VERS_B
67 where item_catalog_group_id = p_item_catalog_grp_id)
68 and not exists (select 1
69 from BOM_STRUCTURES_B
70 where pk1_value = p_item_catalog_grp_id
71 and obj_name = 'EGO_CATALOG_GROUP');
72
73 if l_create_header = 1 then
74
75 for icc_structure in Get_str_catalog_hierarchy loop
76
77 select bom_inventory_components_s.nextval
78 into l_bill_sequence_id
79 from dual;
80
81 insert into BOM_STRUCTURES_B
82 (BILL_SEQUENCE_ID,
83 SOURCE_BILL_SEQUENCE_ID,
84 COMMON_BILL_SEQUENCE_ID,
85 ORGANIZATION_ID,
86 ALTERNATE_BOM_DESIGNATOR,
87 ASSEMBLY_TYPE,
88 STRUCTURE_TYPE_ID,
89 EFFECTIVITY_CONTROL,
90 IS_PREFERRED,
91 OBJ_NAME,
92 PK1_VALUE,
93 PK2_VALUE,
94 LAST_UPDATE_DATE,
95 LAST_UPDATED_BY,
96 CREATION_DATE,
97 CREATED_BY,
98 LAST_UPDATE_LOGIN)
99 select
100 l_bill_sequence_id,
101 l_bill_sequence_id,
102 l_bill_sequence_id,
103 ORGANIZATION_ID,
104 ALTERNATE_BOM_DESIGNATOR,
105 ASSEMBLY_TYPE,
106 STRUCTURE_TYPE_ID,
107 EFFECTIVITY_CONTROL,
108 IS_PREFERRED,
109 OBJ_NAME,
110 p_item_catalog_grp_id,
111 PK2_VALUE,
112 LAST_UPDATE_DATE,
113 LAST_UPDATED_BY,
114 CREATION_DATE,
115 CREATED_BY,
116 LAST_UPDATE_LOGIN
117 from BOM_STRUCTURES_B
118 where BILL_SEQUENCE_ID = icc_structure.bill_sequence_id ;
119
120 end loop;
121
122 end if;
123
124 Exception
125 when others then
126 null;
127 End Create_Default_Header;
128
129 /*
130 * This Procedure will revert the components for the Draft version of the ICC.
131 */
132 PROCEDURE Revert_draft_components (p_item_catalog_grp_id IN NUMBER,
133 p_version_seq_id IN NUMBER,
134 x_Return_Status OUT NOCOPY NUMBER,
135 x_Error_Message OUT NOCOPY VARCHAR2)
136 IS
137 Cursor get_draft_components(p_bill_seq_id NUMBER)
138 IS
139 SELECT COMPONENT_SEQUENCE_ID
140 from BOM_COMPONENTS_B
141 where bill_sequence_id = p_bill_seq_id
142 and nvl(from_object_revision_id,0) = 0;
143
144 Cursor get_version_components(p_bill_seq_id NUMBER)
145 IS
146 SELECT COMPONENT_SEQUENCE_ID
147 from BOM_COMPONENTS_B
148 where bill_sequence_id = p_bill_seq_id
149 and from_object_revision_id = p_version_seq_id
150 and nvl(parent_bill_seq_id,p_bill_seq_id) = p_bill_seq_id;
151
152 l_bill_seq_id Number;
153 l_new_component_seq_id Number;
154 l_structure_type_id Number;
155
156 l_dest_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
157 l_src_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
158 l_str_type EGO_COL_NAME_VALUE_PAIR_ARRAY;
159 l_data_level_pks EGO_COL_NAME_VALUE_PAIR_ARRAY;
160 l_data_level_id Number;
161
162 l_errorcode Number;
163 l_msg_count Number;
164 l_msg_data Varchar2(2000);
165 l_Return_Status Varchar2(1) := 'S';
166
167 BEGIN
168 x_Return_Status := 0;
169 x_Error_Message := null;
170
171 Begin
172 SELECT bill_sequence_id,
173 structure_type_id
174 INTO l_bill_seq_id,
175 l_structure_type_id
176 FROM BOM_STRUCTURES_B
177 WHERE pk1_value = p_item_catalog_grp_id
178 AND obj_name = 'EGO_CATALOG_GROUP';
179 Exception
180 when others then
181 null;
182 End;
183
184 if l_bill_seq_id is not null then
185
186 for component in get_draft_components(l_bill_seq_id) loop
187 delete_comp_user_attrs(component.component_sequence_id);
188 end loop;
189
190 delete from bom_components_b
191 where bill_sequence_id = l_bill_seq_id
192 and nvl(from_object_revision_id,0) = 0;
193
194 select data_level_id
195 into l_data_level_id
196 from ego_data_level_b
197 where data_level_name = 'COMPONENTS_LEVEL'
198 and attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
199 and application_id = 702;
200
201 for component in get_version_components(l_bill_seq_id) loop
202
203 select BOM_INVENTORY_COMPONENTS_S.NEXTVAL
204 into l_new_component_seq_id
205 from dual;
206
207 Insert into BOM_COMPONENTS_B
208 (OPERATION_SEQ_NUM,
209 COMPONENT_ITEM_ID,
210 LAST_UPDATE_DATE,
211 LAST_UPDATED_BY,
212 CREATION_DATE,
213 CREATED_BY,
214 LAST_UPDATE_LOGIN,
215 ITEM_NUM,
216 COMPONENT_QUANTITY,
217 COMPONENT_YIELD_FACTOR,
218 EFFECTIVITY_DATE,
219 IMPLEMENTATION_DATE,
220 PLANNING_FACTOR,
221 QUANTITY_RELATED,
222 SO_BASIS,
223 OPTIONAL,
224 MUTUALLY_EXCLUSIVE_OPTIONS,
225 INCLUDE_IN_COST_ROLLUP,
226 CHECK_ATP,
227 SHIPPING_ALLOWED,
228 REQUIRED_TO_SHIP,
229 REQUIRED_FOR_REVENUE,
230 INCLUDE_ON_SHIP_DOCS,
231 COMPONENT_SEQUENCE_ID,
232 BILL_SEQUENCE_ID,
233 WIP_SUPPLY_TYPE,
234 PICK_COMPONENTS,
235 SUPPLY_SUBINVENTORY,
236 SUPPLY_LOCATOR_ID,
237 BOM_ITEM_TYPE,
238 ENFORCE_INT_REQUIREMENTS,
239 COMPONENT_ITEM_REVISION_ID,
240 PARENT_BILL_SEQ_ID,
241 AUTO_REQUEST_MATERIAL,
242 PK1_VALUE,
243 PK2_VALUE,
244 PK3_VALUE,
245 PK4_VALUE,
246 PK5_VALUE,
247 FROM_OBJECT_REVISION_ID,
248 COMPONENT_REMARKS,
249 CHANGE_NOTICE,
250 BASIS_TYPE,
251 LOW_QUANTITY,
252 HIGH_QUANTITY)
253 select
254 BCB.OPERATION_SEQ_NUM,
255 BCB.COMPONENT_ITEM_ID,
256 sysdate,
257 fnd_global.user_id,
258 sysdate,
259 fnd_global.user_id,
260 fnd_global.login_id,
261 BCB.ITEM_NUM,
262 BCB.COMPONENT_QUANTITY,
263 BCB.COMPONENT_YIELD_FACTOR,
264 BCB.EFFECTIVITY_DATE,
265 BCB.IMPLEMENTATION_DATE,
266 BCB.PLANNING_FACTOR,
267 BCB.QUANTITY_RELATED,
268 BCB.SO_BASIS,
269 BCB.OPTIONAL,
270 BCB.MUTUALLY_EXCLUSIVE_OPTIONS,
271 BCB.INCLUDE_IN_COST_ROLLUP,
272 BCB.CHECK_ATP,
273 BCB.SHIPPING_ALLOWED,
274 BCB.REQUIRED_TO_SHIP,
275 BCB.REQUIRED_FOR_REVENUE,
276 BCB.INCLUDE_ON_SHIP_DOCS,
277 l_new_component_seq_id,
278 l_bill_seq_id,
279 BCB.WIP_SUPPLY_TYPE,
280 BCB.PICK_COMPONENTS,
281 BCB.SUPPLY_SUBINVENTORY,
282 BCB.SUPPLY_LOCATOR_ID,
283 BCB.BOM_ITEM_TYPE,
284 BCB.ENFORCE_INT_REQUIREMENTS,
285 BCB.COMPONENT_ITEM_REVISION_ID,
286 BCB.PARENT_BILL_SEQ_ID,
287 BCB.AUTO_REQUEST_MATERIAL,
288 BCB.PK1_VALUE,
289 BCB.PK2_VALUE,
290 BCB.PK3_VALUE,
291 BCB.PK4_VALUE,
292 BCB.PK5_VALUE,
293 0,
294 BCB.COMPONENT_REMARKS,
295 BCB.CHANGE_NOTICE,
296 BCB.BASIS_TYPE,
297 BCB.LOW_QUANTITY,
298 BCB.HIGH_QUANTITY
299 from BOM_COMPONENTS_B BCB
300 where BCB.COMPONENT_SEQUENCE_ID = component.component_sequence_id
301 and BCB.BILL_SEQUENCE_ID = l_bill_seq_id;
302
303 l_src_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'COMPONENT_SEQUENCE_ID' ,
304 to_char(component.component_sequence_id)),
305 EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' ,
306 to_char(l_bill_seq_id)));
307 l_dest_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'COMPONENT_SEQUENCE_ID' ,
308 to_char(l_new_component_seq_id)),
309 EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' ,
310 to_char(l_bill_seq_id)));
311 l_str_type := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'STRUCTURE_TYPE_ID',
312 TO_CHAR(l_structure_type_id)));
313
314 l_data_level_pks := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'CONTEXT_ID' , null));
315
316
317 EGO_USER_ATTRS_DATA_PVT.Copy_User_Attrs_Data
318 (
319 p_api_version => 1.0
320 ,p_application_id => 702
321 ,p_object_name => 'BOM_COMPONENTS'
322 ,p_old_pk_col_value_pairs => l_src_pk_col_name_val_pairs
323 ,p_new_pk_col_value_pairs => l_dest_pk_col_name_val_pairs
324 ,p_old_dtlevel_col_value_pairs => l_data_level_pks
325 ,p_new_dtlevel_col_value_pairs => l_data_level_pks
326 ,p_old_data_level_id => l_data_level_id
327 ,p_new_data_level_id => l_data_level_id
328 ,p_new_cc_col_value_pairs => l_str_type
329 ,x_return_status => l_Return_Status
330 ,x_errorcode => l_errorcode
331 ,x_msg_count => l_msg_count
332 ,x_msg_data => l_msg_data
333 );
334
335 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
336 x_Return_Status := 1;
337 x_Error_Message := l_msg_data;
338 exit;
339 END IF;
340 end loop;
341 end if;
342 EXCEPTION
343 WHEN others THEN
344 x_Return_Status := 1;
345 x_Error_Message := 'UnHandled exception while reverting structure components'||sqlerrm(sqlcode);
346 END Revert_draft_components;
347
348 /*
349 * This Procedure will create the components for the newly released version of the ICC.
350 */
351 PROCEDURE Release_Components (p_item_catalog_grp_id IN NUMBER,
352 p_version_seq_id IN NUMBER,
353 p_start_date IN DATE,
354 x_Return_Status OUT NOCOPY NUMBER,
355 x_Error_Message OUT NOCOPY VARCHAR2)
356 IS
357 Cursor get_components(p_bill_seq_id NUMBER,
358 p_ver_seq_id NUMBER)
359 IS
360 SELECT component_sequence_id,
361 component_item_id,
362 item_num,
363 component_quantity,
364 component_item_revision_id,
365 parent_bill_seq_id,
366 pk1_value,
367 pk2_value,
368 pk3_value,
369 component_remarks,
370 change_notice,
371 quantity_related,
372 component_yield_factor,
373 enforce_int_requirements,
374 include_in_cost_rollup,
375 basis_type,
376 bom_item_type,
377 planning_factor,
378 supply_locator_id,
379 supply_subinventory,
380 auto_request_material,
381 wip_supply_type,
382 check_atp,
383 optional,
384 mutually_exclusive_options,
385 low_quantity,
386 high_quantity,
387 so_basis,
388 shipping_allowed,
389 include_on_ship_docs,
390 required_for_revenue,
391 required_to_ship,
392 pick_components
393 from BOM_COMPONENTS_B
394 where bill_sequence_id = p_bill_seq_id
395 and nvl(parent_bill_seq_id,bill_sequence_id) = bill_sequence_id
396 and from_object_revision_id = p_ver_seq_id;
397
398 Cursor parent_catalog is
399 select item_catalog_group_id,
400 parent_catalog_group_id
401 from mtl_item_catalog_groups_b
402 connect by prior parent_catalog_group_id = item_catalog_group_id
403 start with item_catalog_group_id = p_item_catalog_grp_id;
404
405 type rec_component IS record(
406 component_sequence_id NUMBER,
407 component_item_id NUMBER,
408 item_num NUMBER,
409 component_quantity NUMBER,
410 component_item_revision_id NUMBER,
411 parent_bill_seq_id NUMBER,
412 pk1_value VARCHAR2(240),
413 pk2_value VARCHAR2(240),
414 pk3_value VARCHAR2(240),
415 component_remarks VARCHAR2(240),
416 change_notice VARCHAR2(10),
417 quantity_related NUMBER,
418 component_yield_factor NUMBER,
419 enforce_int_requirements NUMBER,
420 include_in_cost_rollup NUMBER,
421 basis_type NUMBER,
422 bom_item_type NUMBER,
423 planning_factor NUMBER,
424 supply_locator_id NUMBER,
425 supply_subinventory VARCHAR2(10),
426 auto_request_material VARCHAR2(1),
427 wip_supply_type NUMBER,
428 check_atp NUMBER,
429 optional NUMBER,
430 mutually_exclusive_options NUMBER,
431 low_quantity NUMBER,
432 high_quantity NUMBER,
433 so_basis NUMBER,
434 shipping_allowed NUMBER,
435 include_on_ship_docs NUMBER,
436 required_for_revenue NUMBER,
437 required_to_ship NUMBER,
438 pick_components NUMBER);
439
440 type t_struct_comp is table of rec_component index by binary_integer;
441 v_struct_comp t_struct_comp;
442
443 type t_component_item is table of number index by binary_integer;
444 v_component_item t_component_item;
445
446 l_counter number;
447
448 l_duplicate_component Number := 0;
449 l_default_wip_params Number;
450 l_bill_seq_id Number;
451 l_parent_catalog_grp_id Number;
452 l_parent_bill_seq_id Number;
453 l_new_component_seq_id Number;
454 l_assembly_type Number;
455 l_pk2_value NUMBER;
456 l_effectivity_control NUMBER;
457 l_alternate_bom_designator VARCHAR2(10);
458 l_structure_type_id NUMBER;
459 l_par_assembly_type Number;
460 l_par_pk2_value NUMBER;
461 l_par_effectivity_control NUMBER;
462 l_par_alternate_bom_designator VARCHAR2(10);
463 l_par_structure_type_id NUMBER;
464 l_parent_ver_seq_id Number;
465
466 l_dest_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
467 l_src_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
468 l_str_type EGO_COL_NAME_VALUE_PAIR_ARRAY;
469 l_data_level_pks EGO_COL_NAME_VALUE_PAIR_ARRAY;
470 l_data_level_id Number;
471
472 l_errorcode Number;
473 l_msg_count Number;
474 l_msg_data Varchar2(2000);
475 l_Return_Status Varchar2(1) := 'S';
476
477 BEGIN
478
479 x_Return_Status := 0;
480 x_Error_Message := null;
481 l_counter := 0;
482
483 /* We will create default structure if any ICC in the hierarchy has some structure.*/
484 Create_Default_Header(p_item_catalog_grp_id);
485
486 Begin
487 SELECT bill_sequence_id,
488 assembly_type,
489 pk2_value,
490 effectivity_control,
491 alternate_bom_designator,
492 structure_type_id
493 INTO l_bill_seq_id,
494 l_assembly_type,
495 l_pk2_value,
496 l_effectivity_control,
497 l_alternate_bom_designator,
498 l_structure_type_id
499 FROM BOM_STRUCTURES_B
500 WHERE pk1_value = p_item_catalog_grp_id
501 and obj_name = 'EGO_CATALOG_GROUP';
502 Exception
503 when others then
504 null;
505 End;
506
507 If l_bill_seq_id is not null then
508 for component in get_components(l_bill_seq_id,0) loop
509 l_counter := l_counter+1;
510 if v_component_item.exists(component.component_item_id) then
511 l_duplicate_component := 1;
512 exit;
513 end if;
514 v_component_item(component.component_item_id) := component.component_item_id;
515 v_struct_comp(l_counter).component_sequence_id := component.component_sequence_id;
516 v_struct_comp(l_counter).component_item_id := component.component_item_id;
517 v_struct_comp(l_counter).item_num := component.item_num;
518 v_struct_comp(l_counter).component_quantity := component.component_quantity;
519 v_struct_comp(l_counter).component_item_revision_id := component.component_item_revision_id;
520 v_struct_comp(l_counter).parent_bill_seq_id := component.parent_bill_seq_id;
521 v_struct_comp(l_counter).pk1_value := component.pk1_value;
522 v_struct_comp(l_counter).pk2_value := component.pk2_value;
523 v_struct_comp(l_counter).pk3_value := component.pk3_value;
524 v_struct_comp(l_counter).component_remarks := component.component_remarks;
525 v_struct_comp(l_counter).change_notice := component.change_notice;
526 v_struct_comp(l_counter).quantity_related := component.quantity_related;
527 v_struct_comp(l_counter).component_yield_factor := component.component_yield_factor;
528 v_struct_comp(l_counter).enforce_int_requirements := component.enforce_int_requirements;
529 v_struct_comp(l_counter).include_in_cost_rollup := component.include_in_cost_rollup;
530 v_struct_comp(l_counter).basis_type := component.basis_type;
531 v_struct_comp(l_counter).bom_item_type := component.bom_item_type;
532 v_struct_comp(l_counter).planning_factor := component.planning_factor;
533 v_struct_comp(l_counter).supply_locator_id := component.supply_locator_id;
534 v_struct_comp(l_counter).supply_subinventory := component.supply_subinventory;
535 v_struct_comp(l_counter).auto_request_material := component.auto_request_material;
536 v_struct_comp(l_counter).wip_supply_type := component.wip_supply_type;
537 v_struct_comp(l_counter).check_atp := component.check_atp;
538 v_struct_comp(l_counter).optional := component.optional;
539 v_struct_comp(l_counter).mutually_exclusive_options := component.mutually_exclusive_options;
540 v_struct_comp(l_counter).low_quantity := component.low_quantity;
541 v_struct_comp(l_counter).high_quantity := component.high_quantity;
542 v_struct_comp(l_counter).so_basis := component.so_basis;
543 v_struct_comp(l_counter).shipping_allowed := component.shipping_allowed;
544 v_struct_comp(l_counter).include_on_ship_docs := component.include_on_ship_docs;
545 v_struct_comp(l_counter).required_for_revenue := component.required_for_revenue;
546 v_struct_comp(l_counter).required_to_ship := component.required_to_ship;
547 v_struct_comp(l_counter).pick_components := component.pick_components;
548 end loop;
549 if l_duplicate_component = 0 then
550 for catalog in parent_catalog loop
551 l_parent_catalog_grp_id := catalog.parent_catalog_group_id;
552 if l_parent_catalog_grp_id is not null then
553 Begin
554 SELECT bill_sequence_id,
555 assembly_type,
556 pk2_value,
557 effectivity_control,
558 alternate_bom_designator,
559 structure_type_id
560 into l_parent_bill_seq_id,
561 l_par_assembly_type,
562 l_par_pk2_value,
563 l_par_effectivity_control,
564 l_par_alternate_bom_designator,
565 l_par_structure_type_id
566 FROM BOM_STRUCTURES_B
567 WHERE pk1_value = l_parent_catalog_grp_id
568 and obj_name = 'EGO_CATALOG_GROUP';
569 Exception
570 when others then
571 null;
572 end;
573 l_parent_ver_seq_id := 0;
574 if l_parent_bill_seq_id is not null and
575 l_par_assembly_type = l_assembly_type and
576 l_par_pk2_value = l_pk2_value and
577 l_par_effectivity_control = l_effectivity_control and
578 l_par_alternate_bom_designator = l_alternate_bom_designator and
579 l_par_structure_type_id = l_structure_type_id then
580 l_parent_ver_seq_id := get_effective_version(l_parent_catalog_grp_id,p_start_date);
581 if nvl(l_parent_ver_seq_id,0) <> 0 then
582 for component in get_components(l_parent_bill_seq_id,l_parent_ver_seq_id) loop
583 l_counter := l_counter+1;
584 if v_component_item.exists(component.component_item_id) then
585 l_duplicate_component := 1;
586 exit;
587 end if;
588 v_component_item(component.component_item_id) := component.component_item_id;
589 v_struct_comp(l_counter).component_sequence_id := component.component_sequence_id;
590 v_struct_comp(l_counter).component_item_id := component.component_item_id;
591 v_struct_comp(l_counter).item_num := component.item_num;
592 v_struct_comp(l_counter).component_quantity := component.component_quantity;
593 v_struct_comp(l_counter).component_item_revision_id := component.component_item_revision_id;
594 v_struct_comp(l_counter).parent_bill_seq_id := component.parent_bill_seq_id;
595 v_struct_comp(l_counter).pk1_value := component.pk1_value;
596 v_struct_comp(l_counter).pk2_value := component.pk2_value;
597 v_struct_comp(l_counter).pk3_value := component.pk3_value;
598 v_struct_comp(l_counter).component_remarks := component.component_remarks;
599 v_struct_comp(l_counter).change_notice := component.change_notice;
600 v_struct_comp(l_counter).quantity_related := component.quantity_related;
601 v_struct_comp(l_counter).component_yield_factor := component.component_yield_factor;
602 v_struct_comp(l_counter).enforce_int_requirements := component.enforce_int_requirements;
603 v_struct_comp(l_counter).include_in_cost_rollup := component.include_in_cost_rollup;
604 v_struct_comp(l_counter).basis_type := component.basis_type;
605 v_struct_comp(l_counter).bom_item_type := component.bom_item_type;
606 v_struct_comp(l_counter).planning_factor := component.planning_factor;
607 v_struct_comp(l_counter).supply_locator_id := component.supply_locator_id;
608 v_struct_comp(l_counter).supply_subinventory := component.supply_subinventory;
609 v_struct_comp(l_counter).auto_request_material := component.auto_request_material;
610 v_struct_comp(l_counter).wip_supply_type := component.wip_supply_type;
611 v_struct_comp(l_counter).check_atp := component.check_atp;
612 v_struct_comp(l_counter).optional := component.optional;
613 v_struct_comp(l_counter).mutually_exclusive_options := component.mutually_exclusive_options;
614 v_struct_comp(l_counter).low_quantity := component.low_quantity;
615 v_struct_comp(l_counter).high_quantity := component.high_quantity;
616 v_struct_comp(l_counter).so_basis := component.so_basis;
617 v_struct_comp(l_counter).shipping_allowed := component.shipping_allowed;
618 v_struct_comp(l_counter).include_on_ship_docs := component.include_on_ship_docs;
619 v_struct_comp(l_counter).required_for_revenue := component.required_for_revenue;
620 v_struct_comp(l_counter).required_to_ship := component.required_to_ship;
621 v_struct_comp(l_counter).pick_components := component.pick_components;
622 end loop;
623 end if;
624 end if;
625 end if;
626 end loop;
627 end if;
628 if l_duplicate_component = 0 then
629 l_default_wip_params := fnd_profile.value('BOM:DEFAULT_WIP_VALUES');
630
631 select data_level_id
632 into l_data_level_id
633 from ego_data_level_b
634 where data_level_name = 'COMPONENTS_LEVEL'
635 and attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
636 and application_id = 702;
637 for cntr IN 1..v_struct_comp.COUNT LOOP
638
639 select BOM_INVENTORY_COMPONENTS_S.NEXTVAL
640 into l_new_component_seq_id
641 from dual;
642
643 Insert into BOM_COMPONENTS_B
644 (OPERATION_SEQ_NUM,
645 COMPONENT_ITEM_ID,
646 LAST_UPDATE_DATE,
647 LAST_UPDATED_BY,
648 CREATION_DATE,
649 CREATED_BY,
650 LAST_UPDATE_LOGIN,
651 ITEM_NUM,
652 COMPONENT_QUANTITY,
653 COMPONENT_YIELD_FACTOR,
654 EFFECTIVITY_DATE,
655 IMPLEMENTATION_DATE,
656 PLANNING_FACTOR,
657 QUANTITY_RELATED,
658 SO_BASIS,
659 OPTIONAL,
660 MUTUALLY_EXCLUSIVE_OPTIONS,
661 INCLUDE_IN_COST_ROLLUP,
662 CHECK_ATP,
663 SHIPPING_ALLOWED,
664 REQUIRED_TO_SHIP,
665 REQUIRED_FOR_REVENUE,
666 INCLUDE_ON_SHIP_DOCS,
667 COMPONENT_SEQUENCE_ID,
668 BILL_SEQUENCE_ID,
669 WIP_SUPPLY_TYPE,
670 PICK_COMPONENTS,
671 SUPPLY_SUBINVENTORY,
672 SUPPLY_LOCATOR_ID,
673 BOM_ITEM_TYPE,
674 ENFORCE_INT_REQUIREMENTS,
675 COMPONENT_ITEM_REVISION_ID,
676 PARENT_BILL_SEQ_ID,
677 AUTO_REQUEST_MATERIAL,
678 PK1_VALUE,
679 PK2_VALUE,
680 PK3_VALUE,
681 FROM_OBJECT_REVISION_ID,
682 COMPONENT_REMARKS,
683 CHANGE_NOTICE,
684 BASIS_TYPE,
685 LOW_QUANTITY,
686 HIGH_QUANTITY)
687 values(
688 1,
689 v_struct_comp(cntr).component_item_id,
690 sysdate,
691 fnd_global.user_id,
692 sysdate,
693 fnd_global.user_id,
694 fnd_global.login_id,
695 v_struct_comp(cntr).item_num,
696 v_struct_comp(cntr).component_quantity,
697 v_struct_comp(cntr).component_yield_factor,
698 sysdate,
699 sysdate,
700 v_struct_comp(cntr).planning_factor,
701 v_struct_comp(cntr).quantity_related,
702 v_struct_comp(cntr).so_basis,
703 v_struct_comp(cntr).optional,
704 v_struct_comp(cntr).mutually_exclusive_options,
705 v_struct_comp(cntr).include_in_cost_rollup,
706 v_struct_comp(cntr).check_atp,
707 v_struct_comp(cntr).shipping_allowed,
708 v_struct_comp(cntr).required_to_ship,
709 v_struct_comp(cntr).required_for_revenue,
710 v_struct_comp(cntr).include_on_ship_docs,
711 l_new_component_seq_id,
712 l_bill_seq_id,
713 v_struct_comp(cntr).wip_supply_type,
714 v_struct_comp(cntr).pick_components,
715 v_struct_comp(cntr).supply_subinventory,
716 v_struct_comp(cntr).supply_locator_id,
717 v_struct_comp(cntr).bom_item_type,
718 v_struct_comp(cntr).enforce_int_requirements,
719 v_struct_comp(cntr).component_item_revision_id,
720 decode(v_struct_comp(cntr).parent_bill_seq_id,null,l_bill_seq_id,v_struct_comp(cntr).parent_bill_seq_id),
721 v_struct_comp(cntr).auto_request_material,
722 v_struct_comp(cntr).pk1_value,
723 v_struct_comp(cntr).pk2_value,
724 v_struct_comp(cntr).component_item_revision_id,
725 p_version_seq_id,
726 v_struct_comp(cntr).component_remarks,
727 v_struct_comp(cntr).change_notice,
728 v_struct_comp(cntr).basis_type,
729 v_struct_comp(cntr).low_quantity,
730 v_struct_comp(cntr).high_quantity);
731 /* from mtl_system_items msi
732 where inventory_item_id = v_struct_comp(cntr).component_item_id
733 and organization_id = v_struct_comp(cntr).pk2_value; */
734
735 l_src_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'COMPONENT_SEQUENCE_ID' ,
736 to_char(v_struct_comp(cntr).component_sequence_id)),
737 EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' ,
738 to_char(nvl(v_struct_comp(cntr).parent_bill_seq_id,l_bill_seq_id))));
739 l_dest_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'COMPONENT_SEQUENCE_ID' ,
740 to_char(l_new_component_seq_id)),
741 EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' ,
742 to_char(l_bill_seq_id)));
743 l_str_type := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'STRUCTURE_TYPE_ID',
744 TO_CHAR(l_structure_type_id)));
745 l_data_level_pks := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'CONTEXT_ID' , null));
746
747 EGO_USER_ATTRS_DATA_PVT.Copy_User_Attrs_Data
748 (
749 p_api_version => 1.0
750 ,p_application_id => 702
751 ,p_object_name => 'BOM_COMPONENTS'
752 ,p_old_pk_col_value_pairs => l_src_pk_col_name_val_pairs
753 ,p_new_pk_col_value_pairs => l_dest_pk_col_name_val_pairs
754 ,p_old_dtlevel_col_value_pairs => l_data_level_pks
755 ,p_new_dtlevel_col_value_pairs => l_data_level_pks
756 ,p_old_data_level_id => l_data_level_id
757 ,p_new_data_level_id => l_data_level_id
758 ,p_new_cc_col_value_pairs => l_str_type
759 ,x_return_status => l_Return_Status
760 ,x_errorcode => l_errorcode
761 ,x_msg_count => l_msg_count
762 ,x_msg_data => l_msg_data
763 );
764
765 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
766 x_Return_Status := 1;
767 x_Error_Message := l_msg_data;
768 exit;
769 END IF;
770 end loop;
771 else
772 x_Return_Status := 1;
773 fnd_message.set_name('EGO','EGO_DUP_ICC_COMPONENTS_REL');
774 x_Error_Message := fnd_message.get;
775 end if;
776 end if;
777 EXCEPTION
778 WHEN others THEN
779 x_Return_Status := 1;
780 x_Error_Message := 'UnHandled Exception during Structure Processing'||sqlerrm(sqlcode);
781 END Release_Components;
782
783 /*
784 * This Procedure will get the ICC Name for a given Bill Seq Id.
785 */
786 Function getIccName(p_item_catalog_grp_id IN NUMBER,
787 p_parent_bill_seq_id IN NUMBER)
788 RETURN VARCHAR2
789 IS
790 l_item_catalog_grp_name VARCHAR2(819) := NULL;
791 l_item_catalog_grp_id Number ;
792 BEGIN
793
794 if p_parent_bill_seq_id is null then
795 select concatenated_segments
796 into l_item_catalog_grp_name
797 from MTL_ITEM_CATALOG_GROUPS_B_KFV
798 where item_catalog_group_id = p_item_catalog_grp_id;
799 else
800 select pk1_value
801 into l_item_catalog_grp_id
802 from BOM_STRUCTURES_B
803 where bill_sequence_id = p_parent_bill_seq_id
804 and obj_name= 'EGO_CATALOG_GROUP';
805
806 select concatenated_segments
807 into l_item_catalog_grp_name
808 from MTL_ITEM_CATALOG_GROUPS_B_KFV
809 where item_catalog_group_id = l_item_catalog_grp_id;
810 end if;
811
812 return l_item_catalog_grp_name;
813 EXCEPTION
814 WHEN others THEN
815 return null;
816 END getIccName;
817
818 /*
819 * This Procedure will get the effective version of a ICC for a given date.
820 */
821 Function Get_Effective_Version(p_item_catalog_grp_id IN NUMBER,
822 p_start_date IN DATE)
823 RETURN NUMBER
824 IS
825 l_effective_version NUMBER := NULL;
826 BEGIN
827 select version_seq_id
828 into l_effective_version
829 from EGO_MTL_CATALOG_GRP_VERS_B
830 where item_catalog_group_id = p_item_catalog_grp_id
831 and version_seq_id <> 0
832 and nvl(p_start_date,sysdate) between start_active_date and nvl(end_active_date,nvl(p_start_date,sysdate+1));
833
834 return l_effective_version;
835 EXCEPTION
836 WHEN OTHERS THEN
837 return null;
838 END Get_Effective_Version;
839
840 /*
841 * This Procedure will get the effective version of a Parent ICC for a given date.
842 */
843 Function Get_Parent_Version(p_item_catalog_grp_id IN NUMBER,
844 p_start_date IN DATE)
845 RETURN NUMBER
846 IS
847 l_effective_version NUMBER := NULL;
848 l_parent_catalog_grp_id NUMBER;
849 BEGIN
850 SELECT parent_catalog_group_id
851 into l_parent_catalog_grp_id
852 FROM MTL_ITEM_CATALOG_GROUPS_B
853 WHERE item_catalog_group_id = p_item_catalog_grp_id;
854
855 if l_parent_catalog_grp_id is not null then
856 l_effective_version := Get_Effective_Version(l_parent_catalog_grp_id,p_start_date);
857 end if;
858 return l_effective_version;
859 EXCEPTION
860 WHEN OTHERS THEN
861 return null;
862 END Get_Parent_Version;
863
864 /*
865 * This Procedure will give whether Draft version has been updated or not.
866 */
867 Function Is_Structure_Updated(p_item_catalog_grp_id IN NUMBER,
868 p_start_date IN DATE)
869 RETURN NUMBER
870 IS
871
872 Cursor get_components(p_bill_seq_id NUMBER,
873 p_ver_seq_id NUMBER)
874 IS
875 SELECT component_sequence_id,
876 component_item_id,
877 item_num,
878 component_quantity,
879 component_item_revision_id,
880 parent_bill_seq_id,
881 pk1_value,
882 pk2_value,
883 pk3_value,
884 component_remarks,
885 change_notice,
886 quantity_related,
887 component_yield_factor,
888 enforce_int_requirements,
889 include_in_cost_rollup,
890 basis_type,
891 bom_item_type,
892 planning_factor,
893 supply_locator_id,
894 supply_subinventory,
895 auto_request_material,
896 wip_supply_type,
897 check_atp,
898 optional,
899 mutually_exclusive_options,
900 low_quantity,
901 high_quantity,
902 so_basis,
903 shipping_allowed,
904 include_on_ship_docs,
905 required_for_revenue,
906 required_to_ship
907 from BOM_COMPONENTS_B
908 where bill_sequence_id = p_bill_seq_id
909 and nvl(parent_bill_seq_id,bill_sequence_id) = bill_sequence_id
910 and from_object_revision_id = p_ver_seq_id;
911
912 Cursor get_released_components(p_bill_seq_id NUMBER,
913 p_ver_seq_id NUMBER)
914 IS
915 SELECT component_sequence_id,
916 component_item_id,
917 item_num,
918 component_quantity,
919 component_item_revision_id,
920 parent_bill_seq_id,
921 pk1_value,
922 pk2_value,
923 pk3_value,
924 component_remarks,
925 change_notice,
926 quantity_related,
927 component_yield_factor,
928 enforce_int_requirements,
929 include_in_cost_rollup,
930 basis_type,
931 bom_item_type,
932 planning_factor,
933 supply_locator_id,
934 supply_subinventory,
935 auto_request_material,
936 wip_supply_type,
937 check_atp,
938 optional,
939 mutually_exclusive_options,
940 low_quantity,
941 high_quantity,
942 so_basis,
943 shipping_allowed,
944 include_on_ship_docs,
945 required_for_revenue,
946 required_to_ship
947 from BOM_COMPONENTS_B
948 where bill_sequence_id = p_bill_seq_id
949 and from_object_revision_id = p_ver_seq_id;
950
951 Cursor parent_catalog
952 is
953 select item_catalog_group_id,
954 parent_catalog_group_id
955 from mtl_item_catalog_groups_b
956 connect by prior parent_catalog_group_id = item_catalog_group_id
957 start with item_catalog_group_id = p_item_catalog_grp_id;
958
959 type rec_component IS record(
960 component_sequence_id NUMBER,
961 component_item_id NUMBER,
962 item_num NUMBER,
963 component_quantity NUMBER,
964 component_item_revision_id NUMBER,
965 parent_bill_seq_id NUMBER,
966 pk1_value VARCHAR2(240),
967 pk2_value VARCHAR2(240),
968 pk3_value VARCHAR2(240),
969 component_remarks VARCHAR2(240),
970 change_notice VARCHAR2(10),
971 quantity_related NUMBER,
972 component_yield_factor NUMBER,
973 enforce_int_requirements NUMBER,
974 include_in_cost_rollup NUMBER,
975 basis_type NUMBER,
976 bom_item_type NUMBER,
977 planning_factor NUMBER,
978 supply_locator_id NUMBER,
979 supply_subinventory VARCHAR2(10),
980 auto_request_material VARCHAR2(1),
981 wip_supply_type NUMBER,
982 check_atp NUMBER,
983 optional NUMBER,
984 mutually_exclusive_options NUMBER,
985 low_quantity NUMBER,
986 high_quantity NUMBER,
987 so_basis NUMBER,
988 shipping_allowed NUMBER,
989 include_on_ship_docs NUMBER,
990 required_for_revenue NUMBER,
991 required_to_ship NUMBER);
992
993 type t_struct_comp is table of rec_component index by binary_integer;
994 v_draft_struct_comp t_struct_comp;
995 v_released_struct_comp t_struct_comp;
996
997 type t_component_item is table of number index by binary_integer;
998 v_component_item t_component_item;
999
1000 l_counter number;
1001
1002 l_updated Number;
1003 l_bill_seq_id Number;
1004 l_effective_version Number;
1005 l_parent_ver_seq_id Number;
1006 l_parent_catalog_grp_id Number;
1007 l_assembly_type Number;
1008 l_pk2_value NUMBER;
1009 l_effectivity_control NUMBER;
1010 l_alternate_bom_designator VARCHAR2(10);
1011 l_structure_type_id NUMBER;
1012 l_parent_bill_seq_id Number;
1013 l_par_assembly_type Number;
1014 l_par_pk2_value Number;
1015 l_par_effectivity_control Number;
1016 l_par_alternate_bom_designator Varchar2(10);
1017 l_par_structure_type_id Number;
1018 l_draft_index Number;
1019
1020 BEGIN
1021
1022 l_updated := 0;
1023 l_effective_version := Get_Effective_Version(p_item_catalog_grp_id,p_start_date);
1024 l_counter := 0;
1025
1026 /* Allow releasing empty initial version for ICC. */
1027 if nvl(l_effective_version,0) = 0 then
1028 l_updated := 1;
1029 else
1030 Begin
1031 SELECT bill_sequence_id,
1032 assembly_type,
1033 pk2_value,
1034 effectivity_control,
1035 alternate_bom_designator,
1036 structure_type_id
1037 INTO l_bill_seq_id,
1038 l_assembly_type,
1039 l_pk2_value,
1040 l_effectivity_control,
1041 l_alternate_bom_designator,
1042 l_structure_type_id
1043 FROM BOM_STRUCTURES_B
1044 WHERE pk1_value = p_item_catalog_grp_id
1045 and obj_name = 'EGO_CATALOG_GROUP';
1046 Exception
1047 when others then
1048 null;
1049 End;
1050 If l_bill_seq_id is not null then
1051 for component in get_components(l_bill_seq_id,0) loop
1052 l_counter := l_counter+1;
1053 v_component_item(component.component_item_id) := l_counter;
1054 v_draft_struct_comp(l_counter).component_sequence_id := component.component_sequence_id;
1055 v_draft_struct_comp(l_counter).component_item_id := component.component_item_id;
1056 v_draft_struct_comp(l_counter).item_num := component.item_num;
1057 v_draft_struct_comp(l_counter).component_quantity := component.component_quantity;
1058 v_draft_struct_comp(l_counter).component_item_revision_id := component.component_item_revision_id;
1059 v_draft_struct_comp(l_counter).parent_bill_seq_id := component.parent_bill_seq_id;
1060 v_draft_struct_comp(l_counter).pk1_value := component.pk1_value;
1061 v_draft_struct_comp(l_counter).pk2_value := component.pk2_value;
1062 v_draft_struct_comp(l_counter).pk3_value := component.pk3_value;
1063 v_draft_struct_comp(l_counter).component_remarks := component.component_remarks;
1064 v_draft_struct_comp(l_counter).change_notice := component.change_notice;
1065 v_draft_struct_comp(l_counter).quantity_related := component.quantity_related;
1066 v_draft_struct_comp(l_counter).component_yield_factor := component.component_yield_factor;
1067 v_draft_struct_comp(l_counter).enforce_int_requirements := component.enforce_int_requirements;
1068 v_draft_struct_comp(l_counter).include_in_cost_rollup := component.include_in_cost_rollup;
1069 v_draft_struct_comp(l_counter).basis_type := component.basis_type;
1070 v_draft_struct_comp(l_counter).bom_item_type := component.bom_item_type;
1071 v_draft_struct_comp(l_counter).planning_factor := component.planning_factor;
1072 v_draft_struct_comp(l_counter).supply_locator_id := component.supply_locator_id;
1073 v_draft_struct_comp(l_counter).supply_subinventory := component.supply_subinventory;
1074 v_draft_struct_comp(l_counter).auto_request_material := component.auto_request_material;
1075 v_draft_struct_comp(l_counter).wip_supply_type := component.wip_supply_type;
1076 v_draft_struct_comp(l_counter).check_atp := component.check_atp;
1077 v_draft_struct_comp(l_counter).optional := component.optional;
1078 v_draft_struct_comp(l_counter).mutually_exclusive_options := component.mutually_exclusive_options;
1079 v_draft_struct_comp(l_counter).low_quantity := component.low_quantity;
1080 v_draft_struct_comp(l_counter).high_quantity := component.high_quantity;
1081 v_draft_struct_comp(l_counter).so_basis := component.so_basis;
1082 v_draft_struct_comp(l_counter).shipping_allowed := component.shipping_allowed;
1083 v_draft_struct_comp(l_counter).include_on_ship_docs := component.include_on_ship_docs;
1084 v_draft_struct_comp(l_counter).required_for_revenue := component.required_for_revenue;
1085 v_draft_struct_comp(l_counter).required_to_ship := component.required_to_ship;
1086 end loop;
1087 for catalog in parent_catalog loop
1088 l_parent_catalog_grp_id := catalog.parent_catalog_group_id;
1089 if l_parent_catalog_grp_id is not null then
1090 Begin
1091 SELECT bill_sequence_id,
1092 assembly_type,
1093 pk2_value,
1094 effectivity_control,
1095 alternate_bom_designator,
1096 structure_type_id
1097 into l_parent_bill_seq_id,
1098 l_par_assembly_type,
1099 l_par_pk2_value,
1100 l_par_effectivity_control,
1101 l_par_alternate_bom_designator,
1102 l_par_structure_type_id
1103 FROM BOM_STRUCTURES_B
1104 WHERE pk1_value = l_parent_catalog_grp_id
1105 and obj_name = 'EGO_CATALOG_GROUP';
1106 Exception
1107 when others then
1108 null;
1109 end;
1110 l_parent_ver_seq_id := 0;
1111 if l_parent_bill_seq_id is not null and
1112 l_par_assembly_type = l_assembly_type and
1113 l_par_pk2_value = l_pk2_value and
1114 l_par_effectivity_control = l_effectivity_control and
1115 l_par_alternate_bom_designator = l_alternate_bom_designator and
1116 l_par_structure_type_id = l_structure_type_id then
1117 l_parent_ver_seq_id := get_effective_version(l_parent_catalog_grp_id,p_start_date);
1118 if nvl(l_parent_ver_seq_id,0) <> 0 then
1119 for component in get_components(l_parent_bill_seq_id,l_parent_ver_seq_id) loop
1120 l_counter := l_counter+1;
1121 v_component_item(component.component_item_id) := l_counter;
1122 v_draft_struct_comp(l_counter).component_sequence_id := component.component_sequence_id;
1123 v_draft_struct_comp(l_counter).component_item_id := component.component_item_id;
1124 v_draft_struct_comp(l_counter).item_num := component.item_num;
1125 v_draft_struct_comp(l_counter).component_quantity := component.component_quantity;
1126 v_draft_struct_comp(l_counter).component_item_revision_id := component.component_item_revision_id;
1127 v_draft_struct_comp(l_counter).parent_bill_seq_id := component.parent_bill_seq_id;
1128 v_draft_struct_comp(l_counter).pk1_value := component.pk1_value;
1129 v_draft_struct_comp(l_counter).pk2_value := component.pk2_value;
1130 v_draft_struct_comp(l_counter).pk3_value := component.pk3_value;
1131 v_draft_struct_comp(l_counter).component_remarks := component.component_remarks;
1132 v_draft_struct_comp(l_counter).change_notice := component.change_notice;
1133 v_draft_struct_comp(l_counter).quantity_related := component.quantity_related;
1134 v_draft_struct_comp(l_counter).component_yield_factor := component.component_yield_factor;
1135 v_draft_struct_comp(l_counter).enforce_int_requirements := component.enforce_int_requirements;
1136 v_draft_struct_comp(l_counter).include_in_cost_rollup := component.include_in_cost_rollup;
1137 v_draft_struct_comp(l_counter).basis_type := component.basis_type;
1138 v_draft_struct_comp(l_counter).bom_item_type := component.bom_item_type;
1139 v_draft_struct_comp(l_counter).planning_factor := component.planning_factor;
1140 v_draft_struct_comp(l_counter).supply_locator_id := component.supply_locator_id;
1141 v_draft_struct_comp(l_counter).supply_subinventory := component.supply_subinventory;
1142 v_draft_struct_comp(l_counter).auto_request_material := component.auto_request_material;
1143 v_draft_struct_comp(l_counter).wip_supply_type := component.wip_supply_type;
1144 v_draft_struct_comp(l_counter).check_atp := component.check_atp;
1145 v_draft_struct_comp(l_counter).optional := component.optional;
1146 v_draft_struct_comp(l_counter).mutually_exclusive_options := component.mutually_exclusive_options;
1147 v_draft_struct_comp(l_counter).low_quantity := component.low_quantity;
1148 v_draft_struct_comp(l_counter).high_quantity := component.high_quantity;
1149 v_draft_struct_comp(l_counter).so_basis := component.so_basis;
1150 v_draft_struct_comp(l_counter).shipping_allowed := component.shipping_allowed;
1151 v_draft_struct_comp(l_counter).include_on_ship_docs := component.include_on_ship_docs;
1152 v_draft_struct_comp(l_counter).required_for_revenue := component.required_for_revenue;
1153 v_draft_struct_comp(l_counter).required_to_ship := component.required_to_ship;
1154 end loop;
1155 end if;
1156 end if;
1157 end if;
1158 end loop;
1159 l_counter := 0;
1160 for component in get_released_components(l_bill_seq_id,l_effective_version) loop
1161 l_counter := l_counter+1;
1162 v_released_struct_comp(l_counter).component_sequence_id := component.component_sequence_id;
1163 v_released_struct_comp(l_counter).component_item_id := component.component_item_id;
1164 v_released_struct_comp(l_counter).item_num := component.item_num;
1165 v_released_struct_comp(l_counter).component_quantity := component.component_quantity;
1166 v_released_struct_comp(l_counter).component_item_revision_id := component.component_item_revision_id;
1167 v_released_struct_comp(l_counter).parent_bill_seq_id := component.parent_bill_seq_id;
1168 v_released_struct_comp(l_counter).pk1_value := component.pk1_value;
1169 v_released_struct_comp(l_counter).pk2_value := component.pk2_value;
1170 v_released_struct_comp(l_counter).pk3_value := component.pk3_value;
1171 v_released_struct_comp(l_counter).component_remarks := component.component_remarks;
1172 v_released_struct_comp(l_counter).change_notice := component.change_notice;
1173 v_released_struct_comp(l_counter).quantity_related := component.quantity_related;
1174 v_released_struct_comp(l_counter).component_yield_factor := component.component_yield_factor;
1175 v_released_struct_comp(l_counter).enforce_int_requirements := component.enforce_int_requirements;
1176 v_released_struct_comp(l_counter).include_in_cost_rollup := component.include_in_cost_rollup;
1177 v_released_struct_comp(l_counter).basis_type := component.basis_type;
1178 v_released_struct_comp(l_counter).bom_item_type := component.bom_item_type;
1179 v_released_struct_comp(l_counter).planning_factor := component.planning_factor;
1180 v_released_struct_comp(l_counter).supply_locator_id := component.supply_locator_id;
1181 v_released_struct_comp(l_counter).supply_subinventory := component.supply_subinventory;
1182 v_released_struct_comp(l_counter).auto_request_material := component.auto_request_material;
1183 v_released_struct_comp(l_counter).wip_supply_type := component.wip_supply_type;
1184 v_released_struct_comp(l_counter).check_atp := component.check_atp;
1185 v_released_struct_comp(l_counter).optional := component.optional;
1186 v_released_struct_comp(l_counter).mutually_exclusive_options := component.mutually_exclusive_options;
1187 v_released_struct_comp(l_counter).low_quantity := component.low_quantity;
1188 v_released_struct_comp(l_counter).high_quantity := component.high_quantity;
1189 v_released_struct_comp(l_counter).so_basis := component.so_basis;
1190 v_released_struct_comp(l_counter).shipping_allowed := component.shipping_allowed;
1191 v_released_struct_comp(l_counter).include_on_ship_docs := component.include_on_ship_docs;
1192 v_released_struct_comp(l_counter).required_for_revenue := component.required_for_revenue;
1193 v_released_struct_comp(l_counter).required_to_ship := component.required_to_ship;
1194 end loop;
1195 if v_released_struct_comp.COUNT <> v_draft_struct_comp.COUNT and
1196 v_released_struct_comp.COUNT <> v_component_item.COUNT then
1197 l_updated := 1;
1198 else
1199 for cntr IN 1..v_released_struct_comp.COUNT LOOP
1200 begin
1201 l_draft_index := v_component_item(v_released_struct_comp(cntr).component_item_id);
1202 if v_released_struct_comp(cntr).component_item_id <> v_draft_struct_comp(l_draft_index).component_item_id or
1203 v_released_struct_comp(cntr).item_num <> v_draft_struct_comp(l_draft_index).item_num or
1204 v_released_struct_comp(cntr).component_item_revision_id <> v_draft_struct_comp(l_draft_index).component_item_revision_id or
1205 v_released_struct_comp(cntr).component_quantity <> v_draft_struct_comp(l_draft_index).component_quantity or
1206 v_released_struct_comp(cntr).component_remarks <> v_draft_struct_comp(l_draft_index).component_remarks or
1207 v_released_struct_comp(cntr).change_notice <> v_draft_struct_comp(l_draft_index).change_notice or
1208 v_released_struct_comp(cntr).quantity_related <> v_draft_struct_comp(l_draft_index).quantity_related or
1209 v_released_struct_comp(cntr).component_yield_factor <> v_draft_struct_comp(l_draft_index).component_yield_factor or
1210 v_released_struct_comp(cntr).enforce_int_requirements <> v_draft_struct_comp(l_draft_index).enforce_int_requirements or
1211 v_released_struct_comp(cntr).include_in_cost_rollup <> v_draft_struct_comp(l_draft_index).include_in_cost_rollup or
1212 v_released_struct_comp(cntr).basis_type <> v_draft_struct_comp(l_draft_index).basis_type or
1213 v_released_struct_comp(cntr).bom_item_type <> v_draft_struct_comp(l_draft_index).bom_item_type or
1214 v_released_struct_comp(cntr).planning_factor <> v_draft_struct_comp(l_draft_index).planning_factor or
1215 v_released_struct_comp(cntr).supply_locator_id <> v_draft_struct_comp(l_draft_index).supply_locator_id or
1216 v_released_struct_comp(cntr).supply_subinventory <> v_draft_struct_comp(l_draft_index).supply_subinventory or
1217 v_released_struct_comp(cntr).auto_request_material <> v_draft_struct_comp(l_draft_index).auto_request_material or
1218 v_released_struct_comp(cntr).wip_supply_type <> v_draft_struct_comp(l_draft_index).wip_supply_type or
1219 v_released_struct_comp(cntr).check_atp <> v_draft_struct_comp(l_draft_index).check_atp or
1220 v_released_struct_comp(cntr).optional <> v_draft_struct_comp(l_draft_index).optional or
1221 v_released_struct_comp(cntr).mutually_exclusive_options <> v_draft_struct_comp(l_draft_index).mutually_exclusive_options or
1222 v_released_struct_comp(cntr).low_quantity <> v_draft_struct_comp(l_draft_index).low_quantity or
1223 v_released_struct_comp(cntr).high_quantity <> v_draft_struct_comp(l_draft_index).high_quantity or
1224 v_released_struct_comp(cntr).so_basis <> v_draft_struct_comp(l_draft_index).so_basis or
1225 v_released_struct_comp(cntr).shipping_allowed <> v_draft_struct_comp(l_draft_index).shipping_allowed or
1226 v_released_struct_comp(cntr).include_on_ship_docs <> v_draft_struct_comp(l_draft_index).include_on_ship_docs or
1227 v_released_struct_comp(cntr).required_for_revenue <> v_draft_struct_comp(l_draft_index).required_for_revenue or
1228 v_released_struct_comp(cntr).required_to_ship <> v_draft_struct_comp(l_draft_index).required_to_ship
1229 then
1230 l_updated := 1;
1231 exit;
1232 end if;
1233 l_updated := compare_uda_values(v_released_struct_comp(cntr).component_sequence_id,
1234 v_draft_struct_comp(l_draft_index).component_sequence_id);
1235 if l_updated <> 0 then
1236 exit;
1237 end if;
1238 exception
1239 when others then
1240 l_updated := 1;
1241 exit;
1242 end;
1243 end loop;
1244 end if;
1245 end if;
1246 end if;
1247 return l_updated;
1248 EXCEPTION
1249 WHEN OTHERS THEN
1250 return 0;
1251 END Is_Structure_Updated;
1252
1253 /*
1254 * This Procedure will compare UDA values for two different components and gives whether they are same or different.
1255 */
1256
1257 Function Compare_UDA_Values(p_draft_comp_seq_id IN NUMBER,
1258 p_released_comp_seq_id IN NUMBER)
1259 RETURN NUMBER
1260 IS
1261 l_updated Number;
1262 l_data_level_id Number;
1263 BEGIN
1264 l_updated := 0;
1265
1266 /* UT Fix: Should not consider Component Override attributes. */
1267 select data_level_id
1268 into l_data_level_id
1269 from ego_data_level_b
1270 where data_level_name = 'COMPONENTS_LEVEL'
1271 and attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
1272 and application_id = 702;
1273
1274 select 1
1275 into l_updated
1276 from dual
1277 where exists(
1278 ((select
1279 ATTR_GROUP_ID,
1280 C_EXT_ATTR1,C_EXT_ATTR2,C_EXT_ATTR3,C_EXT_ATTR4,C_EXT_ATTR5,C_EXT_ATTR6,C_EXT_ATTR7,C_EXT_ATTR8,C_EXT_ATTR9,C_EXT_ATTR10,
1281 C_EXT_ATTR11,C_EXT_ATTR12,C_EXT_ATTR13,C_EXT_ATTR14,C_EXT_ATTR15,C_EXT_ATTR16,C_EXT_ATTR17,C_EXT_ATTR18,C_EXT_ATTR19,C_EXT_ATTR20,
1282 C_EXT_ATTR21,C_EXT_ATTR22,C_EXT_ATTR23,C_EXT_ATTR24,C_EXT_ATTR25,C_EXT_ATTR26,C_EXT_ATTR27,C_EXT_ATTR28,C_EXT_ATTR29,C_EXT_ATTR30,
1283 C_EXT_ATTR31,C_EXT_ATTR32,C_EXT_ATTR33,C_EXT_ATTR34,C_EXT_ATTR35,C_EXT_ATTR36,C_EXT_ATTR37,C_EXT_ATTR38,C_EXT_ATTR39,C_EXT_ATTR40,
1284 N_EXT_ATTR1,N_EXT_ATTR2,N_EXT_ATTR3,N_EXT_ATTR4,N_EXT_ATTR5,N_EXT_ATTR6,N_EXT_ATTR7,N_EXT_ATTR8,N_EXT_ATTR9,N_EXT_ATTR10,
1285 N_EXT_ATTR11,N_EXT_ATTR12,N_EXT_ATTR13,N_EXT_ATTR14,N_EXT_ATTR15,N_EXT_ATTR16,N_EXT_ATTR17,N_EXT_ATTR18,N_EXT_ATTR19,N_EXT_ATTR20,
1286 D_EXT_ATTR1,D_EXT_ATTR2,D_EXT_ATTR3,D_EXT_ATTR4,D_EXT_ATTR5,D_EXT_ATTR6,D_EXT_ATTR7,D_EXT_ATTR8,D_EXT_ATTR9,D_EXT_ATTR10,
1287 UOM_EXT_ATTR1,UOM_EXT_ATTR2,UOM_EXT_ATTR3,UOM_EXT_ATTR4,UOM_EXT_ATTR5,UOM_EXT_ATTR6,UOM_EXT_ATTR7,UOM_EXT_ATTR8,UOM_EXT_ATTR9,UOM_EXT_ATTR10,
1288 UOM_EXT_ATTR11,UOM_EXT_ATTR12,UOM_EXT_ATTR13,UOM_EXT_ATTR14,UOM_EXT_ATTR15,UOM_EXT_ATTR16,UOM_EXT_ATTR17,UOM_EXT_ATTR18,UOM_EXT_ATTR19,UOM_EXT_ATTR20
1289 from bom_components_ext_b
1290 where data_level_id = l_data_level_id and COMPONENT_SEQUENCE_ID = p_draft_comp_seq_id)
1291 minus
1292 (select
1293 ATTR_GROUP_ID,
1294 C_EXT_ATTR1,C_EXT_ATTR2,C_EXT_ATTR3,C_EXT_ATTR4,C_EXT_ATTR5,C_EXT_ATTR6,C_EXT_ATTR7,C_EXT_ATTR8,C_EXT_ATTR9,C_EXT_ATTR10,
1295 C_EXT_ATTR11,C_EXT_ATTR12,C_EXT_ATTR13,C_EXT_ATTR14,C_EXT_ATTR15,C_EXT_ATTR16,C_EXT_ATTR17,C_EXT_ATTR18,C_EXT_ATTR19,C_EXT_ATTR20,
1296 C_EXT_ATTR21,C_EXT_ATTR22,C_EXT_ATTR23,C_EXT_ATTR24,C_EXT_ATTR25,C_EXT_ATTR26,C_EXT_ATTR27,C_EXT_ATTR28,C_EXT_ATTR29,C_EXT_ATTR30,
1297 C_EXT_ATTR31,C_EXT_ATTR32,C_EXT_ATTR33,C_EXT_ATTR34,C_EXT_ATTR35,C_EXT_ATTR36,C_EXT_ATTR37,C_EXT_ATTR38,C_EXT_ATTR39,C_EXT_ATTR40,
1298 N_EXT_ATTR1,N_EXT_ATTR2,N_EXT_ATTR3,N_EXT_ATTR4,N_EXT_ATTR5,N_EXT_ATTR6,N_EXT_ATTR7,N_EXT_ATTR8,N_EXT_ATTR9,N_EXT_ATTR10,
1299 N_EXT_ATTR11,N_EXT_ATTR12,N_EXT_ATTR13,N_EXT_ATTR14,N_EXT_ATTR15,N_EXT_ATTR16,N_EXT_ATTR17,N_EXT_ATTR18,N_EXT_ATTR19,N_EXT_ATTR20,
1300 D_EXT_ATTR1,D_EXT_ATTR2,D_EXT_ATTR3,D_EXT_ATTR4,D_EXT_ATTR5,D_EXT_ATTR6,D_EXT_ATTR7,D_EXT_ATTR8,D_EXT_ATTR9,D_EXT_ATTR10,
1301 UOM_EXT_ATTR1,UOM_EXT_ATTR2,UOM_EXT_ATTR3,UOM_EXT_ATTR4,UOM_EXT_ATTR5,UOM_EXT_ATTR6,UOM_EXT_ATTR7,UOM_EXT_ATTR8,UOM_EXT_ATTR9,UOM_EXT_ATTR10,
1302 UOM_EXT_ATTR11,UOM_EXT_ATTR12,UOM_EXT_ATTR13,UOM_EXT_ATTR14,UOM_EXT_ATTR15,UOM_EXT_ATTR16,UOM_EXT_ATTR17,UOM_EXT_ATTR18,UOM_EXT_ATTR19,UOM_EXT_ATTR20
1303 from bom_components_ext_b
1304 where data_level_id = l_data_level_id and COMPONENT_SEQUENCE_ID = p_released_comp_seq_id))
1305 union
1306 ((select
1307 ATTR_GROUP_ID,
1308 C_EXT_ATTR1,C_EXT_ATTR2,C_EXT_ATTR3,C_EXT_ATTR4,C_EXT_ATTR5,C_EXT_ATTR6,C_EXT_ATTR7,C_EXT_ATTR8,C_EXT_ATTR9,C_EXT_ATTR10,
1309 C_EXT_ATTR11,C_EXT_ATTR12,C_EXT_ATTR13,C_EXT_ATTR14,C_EXT_ATTR15,C_EXT_ATTR16,C_EXT_ATTR17,C_EXT_ATTR18,C_EXT_ATTR19,C_EXT_ATTR20,
1310 C_EXT_ATTR21,C_EXT_ATTR22,C_EXT_ATTR23,C_EXT_ATTR24,C_EXT_ATTR25,C_EXT_ATTR26,C_EXT_ATTR27,C_EXT_ATTR28,C_EXT_ATTR29,C_EXT_ATTR30,
1311 C_EXT_ATTR31,C_EXT_ATTR32,C_EXT_ATTR33,C_EXT_ATTR34,C_EXT_ATTR35,C_EXT_ATTR36,C_EXT_ATTR37,C_EXT_ATTR38,C_EXT_ATTR39,C_EXT_ATTR40,
1312 N_EXT_ATTR1,N_EXT_ATTR2,N_EXT_ATTR3,N_EXT_ATTR4,N_EXT_ATTR5,N_EXT_ATTR6,N_EXT_ATTR7,N_EXT_ATTR8,N_EXT_ATTR9,N_EXT_ATTR10,
1313 N_EXT_ATTR11,N_EXT_ATTR12,N_EXT_ATTR13,N_EXT_ATTR14,N_EXT_ATTR15,N_EXT_ATTR16,N_EXT_ATTR17,N_EXT_ATTR18,N_EXT_ATTR19,N_EXT_ATTR20,
1314 D_EXT_ATTR1,D_EXT_ATTR2,D_EXT_ATTR3,D_EXT_ATTR4,D_EXT_ATTR5,D_EXT_ATTR6,D_EXT_ATTR7,D_EXT_ATTR8,D_EXT_ATTR9,D_EXT_ATTR10,
1315 UOM_EXT_ATTR1,UOM_EXT_ATTR2,UOM_EXT_ATTR3,UOM_EXT_ATTR4,UOM_EXT_ATTR5,UOM_EXT_ATTR6,UOM_EXT_ATTR7,UOM_EXT_ATTR8,UOM_EXT_ATTR9,UOM_EXT_ATTR10,
1316 UOM_EXT_ATTR11,UOM_EXT_ATTR12,UOM_EXT_ATTR13,UOM_EXT_ATTR14,UOM_EXT_ATTR15,UOM_EXT_ATTR16,UOM_EXT_ATTR17,UOM_EXT_ATTR18,UOM_EXT_ATTR19,UOM_EXT_ATTR20
1317 from bom_components_ext_b
1318 where data_level_id = l_data_level_id and COMPONENT_SEQUENCE_ID = p_released_comp_seq_id)
1319 minus
1320 (select
1321 ATTR_GROUP_ID,
1322 C_EXT_ATTR1,C_EXT_ATTR2,C_EXT_ATTR3,C_EXT_ATTR4,C_EXT_ATTR5,C_EXT_ATTR6,C_EXT_ATTR7,C_EXT_ATTR8,C_EXT_ATTR9,C_EXT_ATTR10,
1323 C_EXT_ATTR11,C_EXT_ATTR12,C_EXT_ATTR13,C_EXT_ATTR14,C_EXT_ATTR15,C_EXT_ATTR16,C_EXT_ATTR17,C_EXT_ATTR18,C_EXT_ATTR19,C_EXT_ATTR20,
1324 C_EXT_ATTR21,C_EXT_ATTR22,C_EXT_ATTR23,C_EXT_ATTR24,C_EXT_ATTR25,C_EXT_ATTR26,C_EXT_ATTR27,C_EXT_ATTR28,C_EXT_ATTR29,C_EXT_ATTR30,
1325 C_EXT_ATTR31,C_EXT_ATTR32,C_EXT_ATTR33,C_EXT_ATTR34,C_EXT_ATTR35,C_EXT_ATTR36,C_EXT_ATTR37,C_EXT_ATTR38,C_EXT_ATTR39,C_EXT_ATTR40,
1326 N_EXT_ATTR1,N_EXT_ATTR2,N_EXT_ATTR3,N_EXT_ATTR4,N_EXT_ATTR5,N_EXT_ATTR6,N_EXT_ATTR7,N_EXT_ATTR8,N_EXT_ATTR9,N_EXT_ATTR10,
1327 N_EXT_ATTR11,N_EXT_ATTR12,N_EXT_ATTR13,N_EXT_ATTR14,N_EXT_ATTR15,N_EXT_ATTR16,N_EXT_ATTR17,N_EXT_ATTR18,N_EXT_ATTR19,N_EXT_ATTR20,
1328 D_EXT_ATTR1,D_EXT_ATTR2,D_EXT_ATTR3,D_EXT_ATTR4,D_EXT_ATTR5,D_EXT_ATTR6,D_EXT_ATTR7,D_EXT_ATTR8,D_EXT_ATTR9,D_EXT_ATTR10,
1329 UOM_EXT_ATTR1,UOM_EXT_ATTR2,UOM_EXT_ATTR3,UOM_EXT_ATTR4,UOM_EXT_ATTR5,UOM_EXT_ATTR6,UOM_EXT_ATTR7,UOM_EXT_ATTR8,UOM_EXT_ATTR9,UOM_EXT_ATTR10,
1330 UOM_EXT_ATTR11,UOM_EXT_ATTR12,UOM_EXT_ATTR13,UOM_EXT_ATTR14,UOM_EXT_ATTR15,UOM_EXT_ATTR16,UOM_EXT_ATTR17,UOM_EXT_ATTR18,UOM_EXT_ATTR19,UOM_EXT_ATTR20
1331 from bom_components_ext_b
1332 where data_level_id = l_data_level_id and COMPONENT_SEQUENCE_ID = p_draft_comp_seq_id)));
1333
1334 return l_updated;
1335 EXCEPTION
1336 WHEN OTHERS THEN
1337 return 0;
1338 END Compare_UDA_Values;
1339
1340 /*
1341 * This Procedure will compare two different components and its UDA and gives whether they are same or different.
1342 */
1343
1344 Function Compare_components(p_draft_comp_seq_id IN NUMBER,
1345 p_released_comp_seq_id IN NUMBER)
1346 RETURN NUMBER
1347 IS
1348 l_updated Number;
1349 BEGIN
1350
1351 l_updated := 0;
1352
1353 begin -- Added UT fix
1354
1355 select 1
1356 into l_updated
1357 from dual
1358 where exists(
1359 ((select
1360 component_item_id,component_quantity,component_item_revision_id,
1361 component_remarks,change_notice,quantity_related,
1362 component_yield_factor,enforce_int_requirements,include_in_cost_rollup,
1363 basis_type,bom_item_type,planning_factor,
1364 supply_locator_id,supply_subinventory,auto_request_material,
1365 wip_supply_type,check_atp,optional,
1366 mutually_exclusive_options,low_quantity,high_quantity,
1367 so_basis,shipping_allowed,include_on_ship_docs,
1368 required_for_revenue,required_to_ship
1369 from bom_components_b
1370 where COMPONENT_SEQUENCE_ID = p_draft_comp_seq_id)
1371 minus
1372 (select
1373 component_item_id,component_quantity,component_item_revision_id,
1374 component_remarks,change_notice,quantity_related,
1375 component_yield_factor,enforce_int_requirements,include_in_cost_rollup,
1376 basis_type,bom_item_type,planning_factor,
1377 supply_locator_id,supply_subinventory,auto_request_material,
1378 wip_supply_type,check_atp,optional,
1379 mutually_exclusive_options,low_quantity,high_quantity,
1380 so_basis,shipping_allowed,include_on_ship_docs,
1381 required_for_revenue,required_to_ship
1382 from bom_components_b
1383 where COMPONENT_SEQUENCE_ID = p_released_comp_seq_id))
1384 union
1385 ((select
1386 component_item_id,component_quantity,component_item_revision_id,
1387 component_remarks,change_notice,quantity_related,
1388 component_yield_factor,enforce_int_requirements,include_in_cost_rollup,
1389 basis_type,bom_item_type,planning_factor,
1390 supply_locator_id,supply_subinventory,auto_request_material,
1391 wip_supply_type,check_atp,optional,
1392 mutually_exclusive_options,low_quantity,high_quantity,
1393 so_basis,shipping_allowed,include_on_ship_docs,
1394 required_for_revenue,required_to_ship
1395 from bom_components_b
1396 where COMPONENT_SEQUENCE_ID = p_released_comp_seq_id)
1397 minus
1398 (select
1399 component_item_id,component_quantity,component_item_revision_id,
1400 component_remarks,change_notice,quantity_related,
1401 component_yield_factor,enforce_int_requirements,include_in_cost_rollup,
1402 basis_type,bom_item_type,planning_factor,
1403 supply_locator_id,supply_subinventory,auto_request_material,
1404 wip_supply_type,check_atp,optional,
1405 mutually_exclusive_options,low_quantity,high_quantity,
1406 so_basis,shipping_allowed,include_on_ship_docs,
1407 required_for_revenue,required_to_ship
1408 from bom_components_b
1409 where COMPONENT_SEQUENCE_ID = p_draft_comp_seq_id)));
1410
1411 -- Added as UT fix
1412 exception
1413 when others then
1414 l_updated := 0;
1415 end;
1416
1417 if l_updated = 0 then
1418 l_updated := compare_uda_values(p_draft_comp_seq_id,
1419 p_released_comp_seq_id);
1420 end if;
1421 return l_updated;
1422 EXCEPTION
1423 WHEN OTHERS THEN
1424 return 0;
1425 END Compare_components;
1426
1427 /**** This function returns the catalog group id ****/
1428 FUNCTION get_icc_id(p_inventory_item_id NUMBER,
1429 p_organzation_id NUMBER) RETURN NUMBER IS
1430
1431 l_catalog_group_id NUMBER;
1432 BEGIN
1433 select item_catalog_group_id
1434 INTO l_catalog_group_id
1435 from mtl_system_items_b
1436 where inventory_item_id = p_inventory_item_id
1437 and organization_id = p_organzation_id;
1438
1439 RETURN l_catalog_group_id;
1440
1441 END get_icc_id;
1442
1443 procedure populate_item_rev_details(p_inventory_item_id Number,
1444 p_organization_id Number)
1445 Is
1446 l_counter Number := 0;
1447 cursor get_revision_details(p_inventory_item_id NUMBER,
1448 p_organization_id NUMBER) is
1449 select revision_id,
1450 revision,
1451 effectivity_date,
1452 (select nvl( min(b.effectivity_date)-(1/86400),to_date('9999/12/31 00:00:00',date_fmt)) end_date
1453 from mtl_item_revisions_b b
1454 where b.inventory_item_id = a.inventory_item_id
1455 and b.organization_id = a.organization_id
1456 and b.effectivity_date > a.effectivity_date) end_date
1457 from mtl_item_revisions_b a
1458 where inventory_item_id = p_inventory_item_id
1459 and organization_id = p_organization_id
1460 order by effectivity_date;
1461 Begin
1462 v_item_revisions_tbl.delete;
1463 v_rev_index.delete;
1464 for revision in get_revision_details(p_inventory_item_id,p_organization_id) Loop
1465 l_counter := l_counter+1;
1466 v_rev_index(revision.revision_id) := l_counter;
1467 v_item_revisions_tbl(l_counter).revision_id := revision.revision_id;
1468 v_item_revisions_tbl(l_counter).revision := revision.revision;
1469 v_item_revisions_tbl(l_counter).start_date := revision.effectivity_date;
1470 v_item_revisions_tbl(l_counter).end_date := revision.end_date;
1471 End Loop;
1472 l_counter := l_counter+1;
1473 v_rev_index(-1) := l_counter;
1474 v_item_revisions_tbl(l_counter).revision_id := null;
1475 v_item_revisions_tbl(l_counter).revision := null;
1476 v_item_revisions_tbl(l_counter).start_date := null;
1477 v_item_revisions_tbl(l_counter).end_date := to_date('9999/12/31 00:00:00',date_fmt);
1478 End populate_item_rev_details;
1479
1480
1481 Function validate_component_overlap(p_bill_seq_id IN Number,
1482 p_alt_desg IN VARCHAR2,
1483 x_error_msg OUT NOCOPY VARCHAR2)
1484 Return Number
1485 Is
1486
1487 cursor components is
1488 select * from bom_components_b
1489 where bill_sequence_id = p_bill_seq_id;
1490
1491 l_count Number := 0;
1492 l_return_val Number := 0;
1493 fromEndItemMinorRevCode Varchar2(80);
1494 toEndItemMinorRevCode Varchar2(80);
1495 endItemId Number;
1496 endItemOrgId Number;
1497 fromMinorRevCode Varchar2(80);
1498 toMinorRevCode Varchar2(80);
1499
1500 cursor overlap(P_comp_seq_id Number,
1501 P_obj_type varchar2,
1502 P_pk1_value varchar2,
1503 P_pk2_value varchar2,
1504 P_op_seq number,
1505 P_toMinorRevCode varchar2,
1506 P_fromMinorRevCode varchar2,
1507 P_fromMinorRevisionId number,
1508 P_changeNotice varchar2,
1509 P_fromEndItemMinorRevCode varchar2,
1510 P_toEndItemMinorRevCode varchar2,
1511 P_endItemOrgId Number,
1512 P_endItemId Number)
1513 is
1514 SELECT count(1)
1515 FROM bom_components_b bic
1516 WHERE bill_sequence_id = p_bill_seq_id
1517 AND component_sequence_id <> P_comp_seq_id
1518 AND nvl(obj_name,'EGO_ITEM') = nvl(P_obj_type,'EGO_ITEM')
1519 AND pk1_value = P_pk1_value
1520 AND nvl(pk2_value,'-1') = nvl(P_pk2_value,'-1')
1521 AND operation_seq_num = P_op_seq
1522 AND ((P_obj_type IS NOT NULL AND
1523 P_fromMinorRevisionId BETWEEN nvl(from_minor_revision_id,P_fromMinorRevisionId) AND nvl(to_minor_revision_id,P_fromMinorRevisionId))
1524 OR (P_obj_type IS NULL AND P_toMinorRevCode IS NULL OR
1525 P_toMinorRevCode >= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(nvl(from_minor_revision_id,0)))
1526 FROM mtl_item_revisions_b WHERE revision_id = FROM_OBJECT_REVISION_ID)
1527 AND (to_object_revision_id IS NULL OR
1528 P_fromMinorRevCode <= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(nvl(to_minor_revision_id,9999999999999999)))
1529 FROM mtl_item_revisions_b WHERE revision_id = TO_OBJECT_REVISION_ID))))
1530 AND (change_notice is not null
1531 and( implementation_date is not null and P_changeNotice is null OR
1532 (implementation_date is null and change_notice = P_changeNotice
1533 AND EXISTS( SELECT 1 from eng_revised_items eri
1534 where eri.revised_item_sequence_id = bic.revised_item_sequence_id
1535 and eri.bill_Sequence_id = bic.bill_Sequence_id )))
1536 OR (change_notice is null and P_changeNotice is null))
1537 AND (( EXISTS (SELECT null FROM mtl_item_revisions_b
1538 WHERE inventory_item_id = P_endItemId AND organization_id = P_endItemOrgId
1539 AND revision_id = from_end_item_rev_id)
1540 AND ( P_toEndItemMinorRevCode IS NULL OR P_toEndItemMinorRevCode >= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(from_end_item_minor_rev_id))
1541 FROM mtl_item_revisions_b WHERE revision_id = from_end_item_rev_id))
1542 AND ( to_end_item_rev_id IS NULL OR P_fromEndItemMinorRevCode <= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(nvl(to_end_item_minor_rev_id,9999999999999999)))
1543 FROM mtl_item_revisions_b WHERE revision_id = to_end_item_rev_id))));
1544
1545 begin
1546
1547 for comp in components loop
1548
1549 l_count := 0;
1550 fromEndItemMinorRevCode := null;
1551 toEndItemMinorRevCode := null;
1552 endItemId := null;
1553 endItemOrgId := null;
1554 fromMinorRevCode := null;
1555 toMinorRevCode := null;
1556
1557 if comp.FROM_END_ITEM_REV_ID is not null then
1558 SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(comp.FROM_END_ITEM_MINOR_REV_ID)),
1559 inventory_item_id,
1560 organization_id
1561 into fromEndItemMinorRevCode,
1562 endItemId,
1563 endItemOrgId
1564 FROM mtl_item_revisions_b
1565 WHERE revision_id = comp.FROM_END_ITEM_REV_ID;
1566 end if;
1567
1568 if comp.TO_END_ITEM_REV_ID is not null then
1569 SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),nvl(to_char(comp.TO_END_ITEM_MINOR_REV_ID),'9999999999999999L')),
1570 inventory_item_id,
1571 organization_id
1572 into toEndItemMinorRevCode,
1573 endItemId,
1574 endItemOrgId
1575 FROM mtl_item_revisions_b
1576 WHERE revision_id = comp.TO_END_ITEM_REV_ID;
1577 end if;
1578
1579 if fromEndItemMinorRevCode is not null then
1580 SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),nvl(to_char(comp.FROM_MINOR_REVISION_ID),'9999999999999999L')),
1581 inventory_item_id,
1582 organization_id
1583 into fromMinorRevCode,
1584 endItemId,
1585 endItemOrgId
1586 FROM mtl_item_revisions_b
1587 WHERE revision_id = comp.FROM_OBJECT_REVISION_ID;
1588 end if;
1589
1590 if toMinorRevCode is not null then
1591 SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),nvl(to_char(comp.TO_MINOR_REVISION_ID),'9999999999999999L')),
1592 inventory_item_id,
1593 organization_id
1594 into toMinorRevCode,
1595 endItemId,
1596 endItemOrgId
1597 FROM mtl_item_revisions_b
1598 WHERE revision_id = comp.TO_OBJECT_REVISION_ID;
1599 end if;
1600
1601 open overlap(comp.component_sequence_id,
1602 comp.OBJ_NAME,
1603 comp.PK1_VALUE,
1604 comp.PK2_VALUE,
1605 comp.OPERATION_SEQ_NUM,
1606 toMinorRevCode,
1607 fromMinorRevCode,
1608 comp.FROM_MINOR_REVISION_ID,
1609 comp.change_notice,
1610 fromEndItemMinorRevCode,
1611 toEndItemMinorRevCode,
1612 endItemOrgId,
1613 endItemId);
1614 fetch overlap into l_count;
1615 close overlap;
1616
1617 if l_count <> 0 then
1618 l_return_val := 1;
1619 begin
1620
1621 select concatenated_segments into G_COMP_ITEM_NAME
1622 from mtl_system_items_kfv where inventory_item_id = comp.PK1_VALUE
1623 and organization_id = comp.pk2_value;
1624
1625 /* UT Fix: Modified query to use ass_item_id instead of comp_item_id. */
1626 select revision_label into G_EFF_FROM
1627 from mtl_item_revisions where inventory_item_id = G_INV_ITEM_ID
1628 and organization_id = comp.pk2_value and revision_id = comp.FROM_END_ITEM_REV_ID;
1629
1630 exception
1631 when others then
1632 G_COMP_ITEM_NAME := null;
1633 G_EFF_FROM := null;
1634 end;
1635 fnd_message.set_name('EGO','EGO_INHERIT_COMP_OVERLAP');
1636 fnd_message.set_token('COMPONENT_ITEM_NAME', G_COMP_ITEM_NAME);
1637 fnd_message.set_token('ALTCODE', p_alt_desg);
1638 fnd_message.set_token('EFFECTIVE_FROM', G_EFF_FROM);
1639 x_error_msg := substr(x_error_msg||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1640 --return l_count;
1641 end if;
1642
1643 end loop;
1644
1645 l_count := l_return_val;
1646 return l_count;
1647
1648 end validate_component_overlap;
1649
1650 PROCEDURE create_structure_inherit(p_inventory_item_id IN NUMBER,
1651 p_organization_id IN NUMBER,
1652 p_bill_seq_id IN NUMBER,
1653 p_comm_bill_seq_id IN NUMBER,
1654 p_structure_type_id IN NUMBER,
1655 p_alt_desg IN VARCHAR2,
1656 x_Return_Status OUT NOCOPY NUMBER,
1657 x_Error_Message OUT NOCOPY VARCHAR2,
1658 p_eff_control IN NUMBER) IS
1659
1660 l_catalog_group_id Number;
1661 l_catalog_bill_sequence_id Number;
1662 l_effective_version Number;
1663 l_icc_index Number;
1664 l_ins_cntr Number;
1665 l_current_rev_id Number;
1666 l_prev_rev_id Number;
1667 l_prev_icc_version Number;
1668 l_item_comp_id Number;
1669 l_new_component_seq_id Number;
1670 l_errorcode Number;
1671 l_msg_count Number;
1672 l_msg_data Varchar2(2000);
1673
1674 cursor get_all_icc_components(p_item_catalog_grp_id Number,
1675 p_rev_date Date) is
1676 select icc_str_comp.component_sequence_id,icc_str_comp.component_item_id,icc_str_comp.bill_sequence_id,0 is_component_present,
1677 msiv.bom_item_type,msiv.eam_item_type,msiv.base_item_id,msiv.replenish_to_order_flag,msiv.pick_components_flag,
1678 icc_str_comp.component_yield_factor,icc_str_comp.basis_type,msiv.ato_forecast_control,icc_str_comp.planning_factor,
1679 icc_str_comp.optional,msiv.atp_components_flag,icc_str_comp.component_quantity,
1680 icc_str_comp.required_to_ship,icc_str_comp.required_for_revenue
1681 from (
1682 select icc_str_components.component_sequence_id,
1683 icc_str_components.component_item_id,
1684 icc_structure.pk1_value,
1685 icc_str_components.from_object_revision_id,
1686 icc_str_components.bill_sequence_id,
1687 icc_str_components.component_yield_factor,
1688 icc_str_components.basis_type,
1689 icc_str_components.planning_factor,
1690 icc_str_components.optional,
1691 icc_str_components.component_quantity,
1692 icc_str_components.required_to_ship,
1693 icc_str_components.required_for_revenue
1694 from (select item_catalog_group_id
1695 from mtl_item_catalog_groups_b
1696 connect by prior parent_catalog_group_id = item_catalog_group_id
1697 start with item_catalog_group_id = p_item_catalog_grp_id) icc,
1698 bom_structures_b icc_structure,
1699 bom_components_b icc_str_components
1700 where icc_structure.pk1_value = icc.item_catalog_group_id
1701 and icc_structure.pk2_value = p_organization_id
1702 and icc_structure.obj_name = 'EGO_CATALOG_GROUP'
1703 and icc_structure.structure_type_id = p_structure_type_id
1704 and icc_structure.alternate_bom_designator = p_alt_desg
1705 and icc_structure.assembly_type = 2
1706 and icc_structure.effectivity_control = 4
1707 and icc_structure.bill_sequence_id = icc_str_components.bill_sequence_id
1708 and nvl(icc_str_components.parent_bill_seq_id,icc_str_components.bill_sequence_id) = icc_structure.bill_sequence_id) icc_str_comp,
1709 mtl_system_items_vl msiv
1710 where icc_str_comp.from_object_revision_id = EGO_ICC_STRUCTURE_PVT.Get_Effective_Version(icc_str_comp.pk1_value,p_rev_date)
1711 and msiv.inventory_item_id = icc_str_comp.component_item_id
1712 and msiv.organization_id = p_organization_id;
1713
1714 /* This PL/SQL table will be populated with ICC components for the effective version. */
1715 type icc_component_record is record
1716 (component_sequence_id Number,
1717 component_item_id Number,
1718 bill_sequence_id Number,
1719 is_component_present Number);
1720
1721 type icc_component_rec is table of icc_component_record index by binary_integer;
1722 v_icc_comp_tbl icc_component_rec;
1723
1724 type component_record is record
1725 (bill_sequence_id Number,
1726 component_sequence_id Number,
1727 component_item_id Number,
1728 from_revision_id Number,
1729 to_revision_id Number);
1730
1731 /* This PL/SQL table is used for final DML Operations. */
1732 type component_rec is table of component_record index by binary_integer;
1733 v_insert_comp_tbl component_rec;
1734
1735 l_dest_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1736 l_src_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1737 l_str_type EGO_COL_NAME_VALUE_PAIR_ARRAY;
1738 l_data_level_pks EGO_COL_NAME_VALUE_PAIR_ARRAY;
1739 l_data_level_id Number;
1740
1741 l_assy_bom_item_type MTL_SYSTEM_ITEMS_VL.bom_item_type%type;
1742 l_assy_eam_item_type MTL_SYSTEM_ITEMS_VL.eam_item_type%type;
1743 l_assy_base_item_id MTL_SYSTEM_ITEMS_VL.base_item_id%type;
1744 l_assy_replenish_to_order_flag MTL_SYSTEM_ITEMS_VL.replenish_to_order_flag%type;
1745 l_assy_pick_components_flag MTL_SYSTEM_ITEMS_VL.pick_components_flag%type;
1746 l_assy_atp_comp_flag MTL_SYSTEM_ITEMS_VL.atp_components_flag%type;
1747
1748 l_Return_Status Varchar2(1) := 'S';
1749
1750 l_item_seq_incr_prof VARCHAR2(10) := nvl(fnd_profile.value('BOM:ITEM_SEQUENCE_INCREMENT'),10);
1751 l_item_seq_incr Number := 0;
1752 l_stmt_no Number := 0;
1753
1754 BEGIN
1755 l_stmt_no := 10;
1756 If nvl(p_eff_control,1) <> 4 Then
1757 x_Return_Status := 0;
1758 x_Error_Message := null;
1759 Return;
1760 End If;
1761 l_stmt_no := 20;
1762 If p_bill_seq_id <> nvl(p_comm_bill_seq_id,p_bill_seq_id) Then
1763 x_Return_Status := 0;
1764 x_Error_Message := null;
1765 Return;
1766 End If;
1767 l_stmt_no := 30;
1768 l_catalog_group_id := get_icc_id(p_inventory_item_id,p_organization_id);
1769 l_stmt_no := 40;
1770 if l_catalog_group_id is null then
1771 x_Return_Status := 0;
1772 x_Error_Message := null;
1773 Return;
1774 end if;
1775 l_stmt_no := 50;
1776 begin
1777 select bill_sequence_id
1778 into l_catalog_bill_sequence_id
1779 from bom_structures_b
1780 where pk1_value = to_char(l_catalog_group_id)
1781 and pk2_value = to_char(p_organization_id)
1782 and obj_name = 'EGO_CATALOG_GROUP'
1783 and assembly_type = 2
1784 and effectivity_control = 4
1785 and structure_type_id = p_structure_type_id
1786 and alternate_bom_designator = p_alt_desg;
1787 exception
1788 when no_data_found then
1789 /* Some Parent ICC might have structure and components */
1790 l_stmt_no := 60;
1791 begin
1792 select null
1793 into l_catalog_bill_sequence_id
1794 from bom_structures_b
1795 where pk1_value in (select item_catalog_group_id
1796 from mtl_item_catalog_groups_b
1797 connect by prior parent_catalog_group_id = item_catalog_group_id
1798 start with item_catalog_group_id = l_catalog_group_id)
1799 and pk2_value = to_char(p_organization_id)
1800 and obj_name = 'EGO_CATALOG_GROUP'
1801 and assembly_type = 2
1802 and effectivity_control = 4
1803 and structure_type_id = p_structure_type_id
1804 and alternate_bom_designator = p_alt_desg
1805 and rownum = 1;
1806 exception
1807 when no_data_found then
1808 x_Return_Status := 0;
1809 x_Error_Message := null;
1810 Return;
1811 when others then
1812 null;
1813 end;
1814 when others then
1815 null;
1816 end;
1817 l_stmt_no := 70;
1818 select bom_item_type,eam_item_type,base_item_id,replenish_to_order_flag,pick_components_flag,atp_components_flag
1819 into l_assy_bom_item_type,l_assy_eam_item_type,l_assy_base_item_id,l_assy_replenish_to_order_flag,l_assy_pick_components_flag,
1820 l_assy_atp_comp_flag
1821 from MTL_SYSTEM_ITEMS_VL
1822 where inventory_item_id = p_inventory_item_id
1823 and organization_id = p_organization_id;
1824 l_stmt_no := 80;
1825 select concatenated_segments into G_ASSY_ITEM_NAME
1826 from mtl_system_items_kfv where inventory_item_id = p_inventory_item_id and organization_id = p_organization_id;
1827 l_stmt_no := 90;
1828 populate_item_rev_details(p_inventory_item_id,p_organization_id);
1829 l_stmt_no := 100;
1830 l_ins_cntr := 0;
1831 For rev_count in 1..v_rev_index.count loop
1832 l_stmt_no := 110;
1833 l_current_rev_id := v_item_revisions_tbl(rev_count).revision_id;
1834
1835 --For first iteration, l_current_rev_id is null
1836 If l_current_rev_id is null then
1837 goto process_next_revision;
1838 End if;
1839 l_stmt_no := 120;
1840 -- Before populating, we need to clear v_icc_comp_tbl
1841 v_icc_comp_tbl.DELETE;
1842 l_stmt_no := 130;
1843 for component in get_all_icc_components(l_catalog_group_id,v_item_revisions_tbl(rev_count).start_date) loop
1844 If(component.component_item_id = p_inventory_item_id) then
1845 x_Return_Status := 1;
1846 fnd_message.set_name('EGO','EGO_ASSY_AS_INH_COMP');
1847 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1848 --Return;
1849 End If;
1850 l_stmt_no := 140;
1851 select concatenated_segments into G_COMP_ITEM_NAME
1852 from mtl_system_items_kfv where inventory_item_id = component.component_item_id
1853 and organization_id = p_organization_id;
1854 l_stmt_no := 150;
1855 if v_icc_comp_tbl.exists(component.component_item_id) then
1856 x_Return_Status := 1;
1857
1858 fnd_message.set_name('EGO','EGO_INHERIT_COMP_OVERLAP');
1859 fnd_message.set_token('COMPONENT_ITEM_NAME', G_COMP_ITEM_NAME);
1860 fnd_message.set_token('ALTCODE', p_alt_desg);
1861 fnd_message.set_token('EFFECTIVE_FROM', v_item_revisions_tbl(rev_count).revision);
1862 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1863 --Return;
1864 end if;
1865 l_stmt_no := 160;
1866 -- Added for Bom Item type validation for inherited components.
1867 if (
1868 ( ( l_assy_bom_item_type = 1 and component.bom_item_type <> 3) or
1869 ( l_assy_bom_item_type = 2 and component.bom_item_type <> 3) or
1870 ( l_assy_bom_item_type = 3) or
1871 ( l_assy_bom_item_type = 4 and
1872 (component.bom_item_type = 4 or
1873 (l_assy_eam_item_type is null and
1874 (component.bom_item_type IN (2, 1) and
1875 component.replenish_to_order_flag = 'Y' and
1876 l_assy_base_item_id IS NOT NULL and
1877 l_assy_replenish_to_order_flag = 'Y'))))
1878 )
1879 and ( l_assy_bom_item_type = 3 or
1880 l_assy_pick_components_flag = 'Y' or
1881 component.pick_components_flag = 'N')
1882 and ( l_assy_bom_item_type = 3 or
1883 NVL(component.bom_item_type, 4) <> 2 or
1884 (component.bom_item_type = 2 and
1885 ((l_assy_pick_components_flag = 'Y' and
1886 component.pick_components_flag = 'Y') or
1887 (l_assy_replenish_to_order_flag = 'Y' and
1888 component.replenish_to_order_flag = 'Y')))
1889 )
1890 and NOT(l_assy_bom_item_type = 4 and
1891 l_assy_pick_components_flag = 'Y' and
1892 component.bom_item_type = 4 and
1893 component.replenish_to_order_flag = 'Y')
1894 ) then
1895 null;
1896 else
1897 x_Return_Status := 1;
1898 l_stmt_no := 170;
1899 fnd_message.set_name('EGO','EGO_INHERIT_COMP_ITEMTYPE');
1900 fnd_message.set_token('COMPONENT_ITEM_NAME', G_COMP_ITEM_NAME);
1901 fnd_message.set_token('ALTCODE', p_alt_desg);
1902 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1903 --Return;
1904 end if;
1905 l_stmt_no := 180;
1906 if (component.component_yield_factor <> 1 and l_assy_bom_item_type = 3) then
1907 fnd_message.set_name('BOM','BOM_COMP_YIELD_NOT_ONE');
1908 fnd_message.set_token('REVISED_COMPONENT_NAME', G_COMP_ITEM_NAME);
1909 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
1910 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1911 x_Return_Status := 1;
1912 end if;
1913 l_stmt_no := 190;
1914 if l_assy_pick_components_flag = 'Y' and component.basis_type = 2 then
1915 fnd_message.set_name('BOM','BOM_LOT_BASED_PTO');
1916 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1917 x_Return_Status := 1;
1918 end if;
1919 l_stmt_no := 200;
1920 IF component.PLANNING_FACTOR <> 100 THEN
1921 IF l_assy_bom_item_type = 4 THEN
1922 fnd_message.set_name('BOM','BOM_NOT_A_PLANNING_PARENT');
1923 fnd_message.set_token('REVISED_COMPONENT_NAME', G_COMP_ITEM_NAME);
1924 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
1925 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1926 x_Return_Status := 1;
1927 ELSIF ( l_assy_bom_item_type IN (1,2) AND component.OPTIONAL <> 1 AND
1928 component.ato_forecast_control <> 2 ) THEN
1929 fnd_message.set_name('BOM','BOM_COMP_MODEL_OC_OPTIONAL');
1930 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
1931 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1932 x_Return_Status := 1;
1933 ELSIF ( l_assy_bom_item_type IN (1,2) AND ( component.OPTIONAL = 1 AND
1934 component.ato_forecast_control <> 2 )) THEN
1935 fnd_message.set_name('BOM','BOM_COMP_OPTIONAL_ATO_FORECAST');
1936 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
1937 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1938 x_Return_Status := 1;
1939 END IF;
1940 END IF;
1941 l_stmt_no := 210;
1942 IF ( l_assy_pick_components_flag = 'Y' AND l_assy_bom_item_type IN ( 1, 2) AND
1943 component.replenish_to_order_flag = 'Y' AND component.bom_item_type = 4 AND
1944 NVL(component.base_item_id,0) = 0 AND component.OPTIONAL = 2 ) THEN
1945 fnd_message.set_name('BOM','BOM_COMP_OPTIONAL');
1946 fnd_message.set_token('REVISED_COMPONENT_NAME', G_COMP_ITEM_NAME);
1947 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1948 x_Return_Status := 1;
1949 ELSIF ( l_assy_bom_item_type IN (3,4) AND component.OPTIONAL = 1 ) THEN
1950 fnd_message.set_name('BOM','BOM_COMP_NOT_OPTIONAL');
1951 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1952 x_Return_Status := 1;
1953 END IF;
1954 l_stmt_no := 220;
1955 IF component.required_for_revenue = 1 AND component.required_to_ship = 2 AND l_assy_atp_comp_flag = 'Y' THEN
1956 fnd_message.set_name('BOM','BOM_COMP_REQ_FOR_REV_INVALID');
1957 fnd_message.set_token('REVISED_COMPONENT_NAME', G_COMP_ITEM_NAME);
1958 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
1959 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1960 x_Return_Status := 1;
1961 ELSIF component.required_to_ship = 1 AND component.required_for_revenue = 2 AND l_assy_atp_comp_flag = 'Y' THEN
1962 fnd_message.set_name('BOM','BOM_COMP_REQ_TO_SHIP_INVALID');
1963 fnd_message.set_token('REVISED_COMPONENT_NAME', G_COMP_ITEM_NAME);
1964 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
1965 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1966 x_Return_Status := 1;
1967 ELSIF component.required_to_ship = 1 AND component.required_for_revenue = 1 AND l_assy_atp_comp_flag = 'Y' THEN
1968 fnd_message.set_name('BOM','BOM_COMP_REQ_TO_SHIP_INVALID');
1969 fnd_message.set_token('REVISED_COMPONENT_NAME', G_COMP_ITEM_NAME);
1970 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
1971 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1972 fnd_message.set_name('BOM','BOM_COMP_REQ_FOR_REV_INVALID');
1973 fnd_message.set_token('REVISED_COMPONENT_NAME', G_COMP_ITEM_NAME);
1974 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
1975 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
1976 x_Return_Status := 1;
1977 END IF;
1978 l_stmt_no := 230;
1979 v_icc_comp_tbl(component.component_item_id).bill_sequence_id := component.bill_sequence_id;
1980 v_icc_comp_tbl(component.component_item_id).component_item_id := component.component_item_id;
1981 v_icc_comp_tbl(component.component_item_id).component_sequence_id := component.component_sequence_id;
1982 v_icc_comp_tbl(component.component_item_id).is_component_present := component.is_component_present;
1983 end loop;
1984 l_stmt_no := 240;
1985 If(rev_count <> 1 and v_insert_comp_tbl.count >= 1) then
1986 For insert_comp_tbl_count in 1..v_insert_comp_tbl.count loop
1987 l_item_comp_id := v_insert_comp_tbl(insert_comp_tbl_count).component_item_id;
1988 l_stmt_no := 250;
1989 if(v_icc_comp_tbl.exists(l_item_comp_id)) then
1990 if EGO_ICC_STRUCTURE_PVT.Compare_components(v_insert_comp_tbl(insert_comp_tbl_count).component_sequence_id,
1991 v_icc_comp_tbl(l_item_comp_id).component_sequence_id) = 0 then
1992 --Components with same attributes
1993 l_stmt_no := 260;
1994 If v_insert_comp_tbl(insert_comp_tbl_count).to_revision_id is null then
1995 v_icc_comp_tbl(l_item_comp_id).is_component_present := 1;
1996 End If;
1997 Else
1998 --Components with different attributes
1999 l_stmt_no := 270;
2000 If v_insert_comp_tbl(insert_comp_tbl_count).to_revision_id is null then
2001 v_insert_comp_tbl(insert_comp_tbl_count).to_revision_id := l_prev_rev_id;
2002 End If;
2003 End If;
2004 Else
2005 --Update current row in v_insert_comp_tbl as : to_revision_id := l_prev_rev_id;
2006 l_stmt_no := 280;
2007 If v_insert_comp_tbl(insert_comp_tbl_count).to_revision_id is null then
2008 v_insert_comp_tbl(insert_comp_tbl_count).to_revision_id := l_prev_rev_id;
2009 End If;
2010 End If;
2011 End loop;
2012 End If;
2013 l_stmt_no := 290;
2014 -- process unprocessed rows in v_icc_comp_tbl
2015 l_icc_index := v_icc_comp_tbl.first;
2016 while l_icc_index <= v_icc_comp_tbl.last loop
2017 if v_icc_comp_tbl(l_icc_index).is_component_present = 0 then
2018 l_ins_cntr := l_ins_cntr+1;
2019 v_insert_comp_tbl(l_ins_cntr).bill_sequence_id := v_icc_comp_tbl(l_icc_index).bill_sequence_id;
2020 v_insert_comp_tbl(l_ins_cntr).component_sequence_id := v_icc_comp_tbl(l_icc_index).component_sequence_id;
2021 v_insert_comp_tbl(l_ins_cntr).component_item_id := v_icc_comp_tbl(l_icc_index).component_item_id;
2022 v_insert_comp_tbl(l_ins_cntr).from_revision_id := l_current_rev_id;
2023 v_insert_comp_tbl(l_ins_cntr).to_revision_id := null;
2024 End If;
2025 l_stmt_no := 300;
2026 l_icc_index := v_icc_comp_tbl.next(l_icc_index);
2027 End loop;
2028 <<process_next_revision>>
2029 l_prev_rev_id := l_current_rev_id;
2030 End loop;
2031 l_stmt_no := 310;
2032 if x_Return_Status <> 0 then
2033 return;
2034 end if;
2035 l_stmt_no := 320;
2036 select data_level_id
2037 into l_data_level_id
2038 from ego_data_level_b
2039 where data_level_name = 'COMPONENTS_LEVEL'
2040 and attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
2041 and application_id = 702;
2042 l_stmt_no := 330;
2043 --Create UDA values for the new components created.
2044 for cntr in 1..v_insert_comp_tbl.count loop
2045
2046 l_item_seq_incr := l_item_seq_incr+to_number(l_item_seq_incr_prof);
2047
2048 select BOM_INVENTORY_COMPONENTS_S.NEXTVAL
2049 into l_new_component_seq_id
2050 from dual;
2051 l_stmt_no := 340;
2052 Insert into BOM_COMPONENTS_B
2053 (OPERATION_SEQ_NUM,
2054 COMPONENT_ITEM_ID,
2055 LAST_UPDATE_DATE,
2056 LAST_UPDATED_BY,
2057 CREATION_DATE,
2058 CREATED_BY,
2059 LAST_UPDATE_LOGIN,
2060 ITEM_NUM,
2061 COMPONENT_QUANTITY,
2062 COMPONENT_YIELD_FACTOR,
2063 EFFECTIVITY_DATE,
2064 IMPLEMENTATION_DATE,
2065 PLANNING_FACTOR,
2066 QUANTITY_RELATED,
2067 SO_BASIS,
2068 OPTIONAL,
2069 MUTUALLY_EXCLUSIVE_OPTIONS,
2070 INCLUDE_IN_COST_ROLLUP,
2071 CHECK_ATP,
2072 SHIPPING_ALLOWED,
2073 REQUIRED_TO_SHIP,
2074 REQUIRED_FOR_REVENUE,
2075 INCLUDE_ON_SHIP_DOCS,
2076 COMPONENT_SEQUENCE_ID,
2077 BILL_SEQUENCE_ID,
2078 WIP_SUPPLY_TYPE,
2079 PICK_COMPONENTS,
2080 SUPPLY_SUBINVENTORY,
2081 SUPPLY_LOCATOR_ID,
2082 BOM_ITEM_TYPE,
2083 ENFORCE_INT_REQUIREMENTS,
2084 COMPONENT_ITEM_REVISION_ID,
2085 PARENT_BILL_SEQ_ID,
2086 AUTO_REQUEST_MATERIAL,
2087 PK1_VALUE,
2088 PK2_VALUE,
2089 PK3_VALUE,
2090 PK4_VALUE,
2091 PK5_VALUE,
2092 FROM_END_ITEM_REV_ID,
2093 TO_END_ITEM_REV_ID,
2094 FROM_OBJECT_REVISION_ID,
2095 TO_OBJECT_REVISION_ID,
2096 INHERIT_FLAG,
2097 COMPONENT_REMARKS,
2098 CHANGE_NOTICE,
2099 BASIS_TYPE,
2100 LOW_QUANTITY,
2101 HIGH_QUANTITY)
2102 select
2103 BCB.OPERATION_SEQ_NUM,
2104 BCB.COMPONENT_ITEM_ID,
2105 sysdate,
2106 fnd_global.user_id,
2107 sysdate,
2108 fnd_global.user_id,
2109 fnd_global.login_id,
2110 l_item_seq_incr,
2111 BCB.COMPONENT_QUANTITY,
2112 BCB.COMPONENT_YIELD_FACTOR,
2113 BCB.EFFECTIVITY_DATE,
2114 BCB.IMPLEMENTATION_DATE,
2115 BCB.PLANNING_FACTOR,
2116 BCB.QUANTITY_RELATED,
2117 BCB.SO_BASIS,
2118 BCB.OPTIONAL,
2119 BCB.MUTUALLY_EXCLUSIVE_OPTIONS,
2120 BCB.INCLUDE_IN_COST_ROLLUP,
2121 BCB.CHECK_ATP,
2122 BCB.SHIPPING_ALLOWED,
2123 BCB.REQUIRED_TO_SHIP,
2124 BCB.REQUIRED_FOR_REVENUE,
2125 BCB.INCLUDE_ON_SHIP_DOCS,
2126 l_new_component_seq_id,
2127 p_bill_seq_id,
2128 BCB.WIP_SUPPLY_TYPE,
2129 BCB.PICK_COMPONENTS,
2130 BCB.SUPPLY_SUBINVENTORY,
2131 BCB.SUPPLY_LOCATOR_ID,
2132 BCB.BOM_ITEM_TYPE,
2133 BCB.ENFORCE_INT_REQUIREMENTS,
2134 BCB.COMPONENT_ITEM_REVISION_ID,
2135 null,
2136 BCB.AUTO_REQUEST_MATERIAL,
2137 BCB.PK1_VALUE,
2138 BCB.PK2_VALUE,
2139 BCB.PK3_VALUE,
2140 BCB.PK4_VALUE,
2141 BCB.PK5_VALUE,
2142 v_insert_comp_tbl(cntr).from_revision_id,
2143 v_insert_comp_tbl(cntr).to_revision_id,
2144 v_insert_comp_tbl(cntr).from_revision_id,
2145 v_insert_comp_tbl(cntr).to_revision_id,
2146 1,
2147 BCB.COMPONENT_REMARKS,
2148 BCB.CHANGE_NOTICE,
2149 BCB.BASIS_TYPE,
2150 BCB.LOW_QUANTITY,
2151 BCB.HIGH_QUANTITY
2152 from BOM_COMPONENTS_B BCB
2153 where BCB.COMPONENT_SEQUENCE_ID = v_insert_comp_tbl(cntr).component_sequence_id;
2154 /* UT Fix : Commented as component_seq_id is primary key in bom_components_b */
2155 -- and BCB.BILL_SEQUENCE_ID = l_catalog_bill_sequence_id;
2156
2157 l_stmt_no := 350;
2158 l_src_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'COMPONENT_SEQUENCE_ID' ,
2159 to_char(v_insert_comp_tbl(cntr).component_sequence_id)),
2160 EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' ,
2161 to_char(v_insert_comp_tbl(cntr).bill_sequence_id)));
2162 l_dest_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'COMPONENT_SEQUENCE_ID' ,
2163 to_char(l_new_component_seq_id)),
2164 EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' ,
2165 to_char(p_bill_seq_id)));
2166 l_str_type := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'STRUCTURE_TYPE_ID',
2167 TO_CHAR(p_structure_type_id)));
2168
2169
2170 l_data_level_pks := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'CONTEXT_ID' , null));
2171 l_stmt_no := 360;
2172 EGO_USER_ATTRS_DATA_PVT.Copy_User_Attrs_Data
2173 (
2174 p_api_version => 1.0
2175 ,p_application_id => 702
2176 ,p_object_name => 'BOM_COMPONENTS'
2177 ,p_old_pk_col_value_pairs => l_src_pk_col_name_val_pairs
2178 ,p_new_pk_col_value_pairs => l_dest_pk_col_name_val_pairs
2179 ,p_old_dtlevel_col_value_pairs => l_data_level_pks
2180 ,p_new_dtlevel_col_value_pairs => l_data_level_pks
2181 ,p_old_data_level_id => l_data_level_id
2182 ,p_new_data_level_id => l_data_level_id
2183 ,p_new_cc_col_value_pairs => l_str_type
2184 ,x_return_status => l_Return_Status
2185 ,x_errorcode => l_errorcode
2186 ,x_msg_count => l_msg_count
2187 ,x_msg_data => l_msg_data
2188 );
2189 l_stmt_no := 370;
2190 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2191 x_Return_Status := 1;
2192 x_Error_Message := l_msg_data;
2193 exit;
2194 END IF;
2195 l_stmt_no := 380;
2196 end loop;
2197
2198 /* Assigned ass_item_id to global variable, this will be used in validate_component_overlap procedure. */
2199 G_INV_ITEM_ID := p_inventory_item_id;
2200 --x_Return_Status := validate_component_overlap(p_bill_seq_id);
2201 l_stmt_no := 390;
2202 x_Return_Status := validate_component_overlap(p_bill_seq_id => p_bill_seq_id,
2203 p_alt_desg => p_alt_desg,
2204 x_error_msg => x_Error_Message);
2205 l_stmt_no := 400;
2206
2207 EXCEPTION
2208 WHEN others THEN
2209 x_Return_Status := 1;
2210 x_Error_Message := 'UnHandled exception while inheriting structure components('||l_stmt_no||') : '||sqlerrm(sqlcode);
2211
2212 END create_structure_inherit;
2213
2214 Function get_revision_start_date(p_rev_id in Number)
2215 Return Date is
2216 Begin
2217 return v_item_revisions_tbl(v_rev_index(p_rev_id)).start_date;
2218 End get_revision_start_date;
2219
2220 Function get_revision_end_date(p_rev_id in Number)
2221 Return Date is
2222 Begin
2223 return v_item_revisions_tbl(v_rev_index(p_rev_id)).end_date;
2224 End get_revision_end_date;
2225
2226
2227 PROCEDURE inherit_icc_components(p_inventory_item_id IN NUMBER,
2228 p_organization_id IN NUMBER,
2229 p_revision_id IN NUMBER,
2230 p_rev_date IN DATE,
2231 x_Return_Status OUT NOCOPY NUMBER,
2232 x_Error_Message OUT NOCOPY VARCHAR2) IS
2233
2234 l_catalog_group_id NUMBER;
2235 l_temp Number :=0;
2236 l_catalog_str_type_id Number;
2237 l_catalog_str_name Varchar2(10);
2238 l_item_bill_sequence_id Number;
2239 l_catalog_bill_sequence_id Number;
2240 l_effective_version Number;
2241
2242 l_prev_rev_id Number;
2243 l_next_rev_id Number;
2244 l_upd_cntr Number;
2245 l_ins_cntr Number;
2246 l_comp_id Number;
2247 l_comp_diff_count Number;
2248 l_icc_index Number;
2249 l_new_component_seq_id Number;
2250 l_comp_from_rev_id Number;
2251 l_comp_to_rev_id Number;
2252 l_errorcode Number;
2253 l_msg_count Number;
2254 l_msg_data Varchar2(2000);
2255
2256 l_assy_bom_item_type MTL_SYSTEM_ITEMS_VL.bom_item_type%type;
2257 l_assy_eam_item_type MTL_SYSTEM_ITEMS_VL.eam_item_type%type;
2258 l_assy_base_item_id MTL_SYSTEM_ITEMS_VL.base_item_id%type;
2259 l_assy_replenish_to_order_flag MTL_SYSTEM_ITEMS_VL.replenish_to_order_flag%type;
2260 l_assy_pick_components_flag MTL_SYSTEM_ITEMS_VL.pick_components_flag%type;
2261 l_assy_atp_comp_flag MTL_SYSTEM_ITEMS_VL.atp_components_flag%type;
2262
2263 cursor item_structure(p_inventory_item_id Number,
2264 p_organization_id Number) is
2265 select structure_type_id,alternate_bom_designator,bill_sequence_id
2266 from bom_structures_b
2267 where
2268 assembly_item_id = p_inventory_item_id
2269 and organization_id = p_organization_id
2270 and obj_name is null
2271 and assembly_type = 2
2272 and effectivity_control = 4
2273 and source_bill_sequence_id = bill_sequence_id;
2274
2275 cursor get_all_icc_components(p_item_catalog_grp_id Number,
2276 p_structure_type_id Number,
2277 p_alt_desg Varchar2) is
2278 select icc_str_comp.component_sequence_id,icc_str_comp.component_item_id,icc_str_comp.bill_sequence_id,0 is_component_present,
2279 msiv.bom_item_type,msiv.eam_item_type,msiv.base_item_id,msiv.replenish_to_order_flag,msiv.pick_components_flag,
2280 icc_str_comp.component_yield_factor,icc_str_comp.basis_type,msiv.ato_forecast_control,icc_str_comp.planning_factor,
2281 icc_str_comp.optional,msiv.atp_components_flag,icc_str_comp.component_quantity,
2282 icc_str_comp.required_to_ship,icc_str_comp.required_for_revenue
2283 from (
2284 select icc_str_components.component_sequence_id,
2285 icc_str_components.component_item_id,
2286 icc_structure.pk1_value,
2287 icc_str_components.from_object_revision_id,
2288 icc_structure.bill_sequence_id,
2289 icc_str_components.component_yield_factor,
2290 icc_str_components.basis_type,
2291 icc_str_components.planning_factor,
2292 icc_str_components.optional,
2293 icc_str_components.component_quantity,
2294 icc_str_components.required_to_ship,
2295 icc_str_components.required_for_revenue
2296 from (select item_catalog_group_id
2297 from mtl_item_catalog_groups_b
2298 connect by prior parent_catalog_group_id = item_catalog_group_id
2299 start with item_catalog_group_id = p_item_catalog_grp_id) icc,
2300 bom_structures_b icc_structure,
2301 bom_components_b icc_str_components
2302 where icc_structure.pk1_value = icc.item_catalog_group_id
2303 and icc_structure.pk2_value = p_organization_id
2304 and icc_structure.obj_name = 'EGO_CATALOG_GROUP'
2305 and icc_structure.structure_type_id = p_structure_type_id
2306 and icc_structure.alternate_bom_designator = p_alt_desg
2307 and icc_structure.assembly_type = 2
2308 and icc_structure.effectivity_control = 4
2309 and icc_structure.bill_sequence_id = icc_str_components.bill_sequence_id
2310 and nvl(icc_str_components.parent_bill_seq_id,icc_str_components.bill_sequence_id) = icc_structure.bill_sequence_id) icc_str_comp,
2311 mtl_system_items_vl msiv
2312 where icc_str_comp.from_object_revision_id = EGO_ICC_STRUCTURE_PVT.Get_Effective_Version(icc_str_comp.pk1_value,p_rev_date)
2313 and msiv.inventory_item_id = icc_str_comp.component_item_id
2314 and msiv.organization_id = p_organization_id;
2315
2316 type component_record is record
2317 (component_sequence_id Number,
2318 component_item_id Number,
2319 from_revision_id Number,
2320 to_revision_id Number,
2321 bill_seq_id Number);
2322
2323 /* This PL/SQL table is used for final DML Operations. */
2324 type component_rec is table of component_record index by binary_integer;
2325 v_insert_comp_tbl component_rec;
2326 v_update_comp_tbl component_rec;
2327 v_delete_comp_tbl component_rec;
2328
2329 /* This PL/SQL table will be populated with ICC components. */
2330 type icc_component_record is record
2331 (component_sequence_id Number,
2332 component_item_id Number,
2333 bill_sequence_id Number,
2334 is_component_present Number);
2335 type icc_component_rec is table of icc_component_record index by binary_integer;
2336 v_icc_comp_tbl icc_component_rec;
2337
2338 /* This PL/SQL table will be populated with Item components. */
2339 type item_component_record is record(
2340 component_sequence_id Number,
2341 component_item_id Number,
2342 from_end_item_rev_id Number,
2343 to_end_item_rev_id Number,
2344 inherit_flag Number);
2345
2346 type item_component_rec is table of item_component_record index by binary_integer;
2347 v_item_comp_tbl item_component_rec;
2348
2349 l_dest_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2350 l_src_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2351 l_str_type EGO_COL_NAME_VALUE_PAIR_ARRAY;
2352 l_data_level_pks EGO_COL_NAME_VALUE_PAIR_ARRAY;
2353 l_data_level_id Number;
2354 l_Return_Status Varchar2(1) := 'S';
2355
2356 l_item_seq_incr_prof VARCHAR2(10) := nvl(fnd_profile.value('BOM:ITEM_SEQUENCE_INCREMENT'),10);
2357 l_item_seq_incr Number := 0;
2358 l_stmt_no Number := 0;
2359
2360 BEGIN
2361 l_stmt_no := 10;
2362 l_catalog_group_id := get_icc_id(p_inventory_item_id,p_organization_id);
2363 x_Return_Status := 0;
2364 if l_catalog_group_id is null then
2365 x_Error_Message := null;
2366 Return;
2367 end if;
2368 l_stmt_no := 20;
2369 for structure in item_structure(p_inventory_item_id,p_organization_id) loop
2370 begin
2371 l_stmt_no := 30;
2372 select bill_sequence_id,structure_type_id,alternate_bom_designator
2373 into l_catalog_bill_sequence_id,l_catalog_str_type_id,G_ALTCODE
2374 from bom_structures_b
2375 where pk1_value = to_char(l_catalog_group_id)
2376 and pk2_value = to_char(p_organization_id)
2377 and obj_name = 'EGO_CATALOG_GROUP'
2378 and assembly_type = 2
2379 and effectivity_control = 4
2380 and structure_type_id = structure.structure_type_id
2381 and alternate_bom_designator = structure.alternate_bom_designator;
2382
2383 l_item_bill_sequence_id := structure.bill_sequence_id;
2384 exit;
2385 exception
2386 when no_data_found then
2387 /* Some Parent ICC might have structure and components */
2388 begin
2389 l_stmt_no := 40;
2390 select structure_type_id,alternate_bom_designator
2391 into l_catalog_str_type_id,G_ALTCODE
2392 from bom_structures_b
2393 where pk1_value in (select item_catalog_group_id
2394 from mtl_item_catalog_groups_b
2395 connect by prior parent_catalog_group_id = item_catalog_group_id
2396 start with item_catalog_group_id = l_catalog_group_id)
2397 and pk2_value = to_char(p_organization_id)
2398 and obj_name = 'EGO_CATALOG_GROUP'
2399 and assembly_type = 2
2400 and effectivity_control = 4
2401 and structure_type_id = structure.structure_type_id
2402 and alternate_bom_designator = structure.alternate_bom_designator
2403 and rownum = 1;
2404
2405 l_item_bill_sequence_id := structure.bill_sequence_id;
2406 exit;
2407 exception
2408 when others then
2409 null;
2410 end;
2411 when others then
2412 null;
2413 end;
2414 end loop;
2415
2416 l_stmt_no := 50;
2417 if l_item_bill_sequence_id is null then
2418 x_Return_Status := 0;
2419 x_Error_Message := null;
2420 Return;
2421 end if;
2422
2423 l_stmt_no := 60;
2424 select max(ITEM_NUM) into l_item_seq_incr
2425 from bom_components_b where bill_sequence_id = l_item_bill_sequence_id;
2426
2427 l_stmt_no := 70;
2428 select bom_item_type,eam_item_type,base_item_id,replenish_to_order_flag,pick_components_flag,atp_components_flag
2429 into l_assy_bom_item_type,l_assy_eam_item_type,l_assy_base_item_id,l_assy_replenish_to_order_flag,l_assy_pick_components_flag,
2430 l_assy_atp_comp_flag
2431 from MTL_SYSTEM_ITEMS_VL
2432 where inventory_item_id = p_inventory_item_id
2433 and organization_id = p_organization_id;
2434
2435 l_stmt_no := 80;
2436 select concatenated_segments into G_ASSY_ITEM_NAME
2437 from mtl_system_items_kfv where inventory_item_id = p_inventory_item_id and organization_id = p_organization_id;
2438 l_stmt_no := 90;
2439 populate_item_rev_details(p_inventory_item_id,p_organization_id);
2440 l_stmt_no := 100;
2441 if v_item_revisions_tbl.exists(v_rev_index(p_revision_id)-1) then
2442 l_prev_rev_id := v_item_revisions_tbl(v_rev_index(p_revision_id)-1).revision_id;
2443 else
2444 l_prev_rev_id := null;
2445 end if;
2446 l_stmt_no := 110;
2447 if v_item_revisions_tbl.exists(v_rev_index(p_revision_id)+1) then
2448 l_next_rev_id := v_item_revisions_tbl(v_rev_index(p_revision_id)+1).revision_id;
2449 else
2450 l_next_rev_id := null;
2451 end if;
2452 l_stmt_no := 120;
2453 for component in get_all_icc_components(l_catalog_group_id,l_catalog_str_type_id,G_ALTCODE) loop
2454 If(component.component_item_id = p_inventory_item_id) then
2455 x_Return_Status := 1;
2456 fnd_message.set_name('EGO','EGO_ASSY_AS_INH_COMP');
2457 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
2458 --Return;
2459 End If;
2460 l_stmt_no := 130;
2461 select concatenated_segments into G_COMP_ITEM_NAME
2462 from mtl_system_items_kfv where inventory_item_id = component.component_item_id
2463 and organization_id = p_organization_id;
2464 l_stmt_no := 140;
2465 if v_icc_comp_tbl.exists(component.component_item_id) then
2466 x_Return_Status := 1;
2467 fnd_message.set_name('EGO','EGO_INHERIT_COMP_OVERLAP');
2468 fnd_message.set_token('COMPONENT_ITEM_NAME', G_COMP_ITEM_NAME);
2469 fnd_message.set_token('ALTCODE', G_ALTCODE);
2470 fnd_message.set_token('EFFECTIVE_FROM', v_item_revisions_tbl(v_rev_index(p_revision_id)).revision);
2471 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
2472 end if;
2473 l_stmt_no := 150;
2474 -- Added for Bom Item type validation for inherited components.
2475 if (
2476 ( ( l_assy_bom_item_type = 1 and component.bom_item_type <> 3) or
2477 ( l_assy_bom_item_type = 2 and component.bom_item_type <> 3) or
2478 ( l_assy_bom_item_type = 3) or
2479 ( l_assy_bom_item_type = 4 and
2480 (component.bom_item_type = 4 or
2481 (l_assy_eam_item_type is null and
2482 (component.bom_item_type IN (2, 1) and
2483 component.replenish_to_order_flag = 'Y' and
2484 l_assy_base_item_id IS NOT NULL and
2485 l_assy_replenish_to_order_flag = 'Y'))))
2486 )
2487 and ( l_assy_bom_item_type = 3 or
2488 l_assy_pick_components_flag = 'Y' or
2489 component.pick_components_flag = 'N')
2490 and ( l_assy_bom_item_type = 3 or
2491 NVL(component.bom_item_type, 4) <> 2 or
2492 (component.bom_item_type = 2 and
2493 ((l_assy_pick_components_flag = 'Y' and
2494 component.pick_components_flag = 'Y') or
2495 (l_assy_replenish_to_order_flag = 'Y' and
2496 component.replenish_to_order_flag = 'Y')))
2497 )
2498 and NOT(l_assy_bom_item_type = 4 and
2499 l_assy_pick_components_flag = 'Y' and
2500 component.bom_item_type = 4 and
2501 component.replenish_to_order_flag = 'Y')
2502 ) then
2503 null;
2504 else
2505 x_Return_Status := 1;
2506 fnd_message.set_name('EGO','EGO_INHERIT_COMP_ITEMTYPE');
2507 fnd_message.set_token('COMPONENT_ITEM_NAME', G_COMP_ITEM_NAME);
2508 fnd_message.set_token('ALTCODE', G_ALTCODE);
2509 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
2510 --Return;
2511 end if;
2512 l_stmt_no := 160;
2513 if (component.component_yield_factor <> 1 and l_assy_bom_item_type = 3) then
2514 fnd_message.set_name('BOM','BOM_COMP_YIELD_NOT_ONE');
2515 fnd_message.set_token('REVISED_COMPONENT_NAME', G_COMP_ITEM_NAME);
2516 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
2517 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
2518 x_Return_Status := 1;
2519 end if;
2520 l_stmt_no := 170;
2521 if l_assy_pick_components_flag = 'Y' and component.basis_type = 2 then
2522 fnd_message.set_name('BOM','BOM_LOT_BASED_PTO');
2523 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
2524 x_Return_Status := 1;
2525 end if;
2526 l_stmt_no := 180;
2527 IF component.PLANNING_FACTOR <> 100 THEN
2528 IF l_assy_bom_item_type = 4 THEN
2529 fnd_message.set_name('BOM','BOM_NOT_A_PLANNING_PARENT');
2530 fnd_message.set_token('REVISED_COMPONENT_NAME', G_COMP_ITEM_NAME);
2531 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
2532 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
2533 x_Return_Status := 1;
2534 ELSIF ( l_assy_bom_item_type IN (1,2) AND component.OPTIONAL <> 1 AND
2535 component.ato_forecast_control <> 2 ) THEN
2536 fnd_message.set_name('BOM','BOM_COMP_MODEL_OC_OPTIONAL');
2537 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
2538 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
2539 x_Return_Status := 1;
2540 ELSIF ( l_assy_bom_item_type IN (1,2) AND ( component.OPTIONAL = 1 AND
2541 component.ato_forecast_control <> 2 )) THEN
2542 fnd_message.set_name('BOM','BOM_COMP_OPTIONAL_ATO_FORECAST');
2543 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
2544 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
2545 x_Return_Status := 1;
2546 END IF;
2547 END IF;
2548 l_stmt_no := 190;
2549 IF ( l_assy_pick_components_flag = 'Y' AND l_assy_bom_item_type IN ( 1, 2) AND
2550 component.replenish_to_order_flag = 'Y' AND component.bom_item_type = 4 AND
2551 NVL(component.base_item_id,0) = 0 AND component.OPTIONAL = 2 ) THEN
2552 fnd_message.set_name('BOM','BOM_COMP_OPTIONAL');
2553 fnd_message.set_token('REVISED_COMPONENT_NAME', G_COMP_ITEM_NAME);
2554 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
2555 x_Return_Status := 1;
2556 ELSIF ( l_assy_bom_item_type IN (3,4) AND component.OPTIONAL = 1 ) THEN
2557 fnd_message.set_name('BOM','BOM_COMP_NOT_OPTIONAL');
2558 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
2559 x_Return_Status := 1;
2560 END IF;
2561 l_stmt_no := 200;
2562 IF component.required_for_revenue = 1 AND component.required_to_ship = 2 AND l_assy_atp_comp_flag = 'Y' THEN
2563 fnd_message.set_name('BOM','BOM_COMP_REQ_FOR_REV_INVALID');
2564 fnd_message.set_token('REVISED_COMPONENT_NAME', G_COMP_ITEM_NAME);
2565 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
2566 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
2567 x_Return_Status := 1;
2568 ELSIF component.required_to_ship = 1 AND component.required_for_revenue = 2 AND l_assy_atp_comp_flag = 'Y' THEN
2569 fnd_message.set_name('BOM','BOM_COMP_REQ_TO_SHIP_INVALID');
2570 fnd_message.set_token('REVISED_COMPONENT_NAME', G_COMP_ITEM_NAME);
2571 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
2572 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
2573 x_Return_Status := 1;
2574 ELSIF component.required_to_ship = 1 AND component.required_for_revenue = 1 AND l_assy_atp_comp_flag = 'Y' THEN
2575 fnd_message.set_name('BOM','BOM_COMP_REQ_TO_SHIP_INVALID');
2576 fnd_message.set_token('REVISED_COMPONENT_NAME', G_COMP_ITEM_NAME);
2577 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
2578 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
2579 fnd_message.set_name('BOM','BOM_COMP_REQ_FOR_REV_INVALID');
2580 fnd_message.set_token('REVISED_COMPONENT_NAME', G_COMP_ITEM_NAME);
2581 fnd_message.set_token('REVISED_ITEM_NAME', G_ASSY_ITEM_NAME);
2582 x_Error_Message := substr(x_Error_Message||FND_CONST.NEWLINE||fnd_message.get,1,2000) ;
2583 x_Return_Status := 1;
2584 END IF;
2585 l_stmt_no := 210;
2586 v_icc_comp_tbl(component.component_item_id).bill_sequence_id := component.bill_sequence_id;
2587 v_icc_comp_tbl(component.component_item_id).component_item_id := component.component_item_id;
2588 v_icc_comp_tbl(component.component_item_id).component_sequence_id := component.component_sequence_id;
2589 v_icc_comp_tbl(component.component_item_id).is_component_present := component.is_component_present;
2590 end loop;
2591 l_stmt_no := 220;
2592 if x_Return_Status <> 0 then
2593 return;
2594 end if;
2595 l_stmt_no := 230;
2596 /* Get components that are valid only for current revision */
2597 begin
2598 select bcb.component_sequence_id,bcb.component_item_id,bcb.from_end_item_rev_id,bcb.to_end_item_rev_id,bcb.inherit_flag
2599 bulk collect into v_item_comp_tbl
2600 from bom_components_b bcb
2601 where bcb.bill_sequence_id = l_item_bill_sequence_id
2602 and bcb.inherit_flag = 1
2603 and (p_rev_date between EGO_ICC_STRUCTURE_PVT.get_revision_start_date(bcb.from_end_item_rev_id) and
2604 EGO_ICC_STRUCTURE_PVT.get_revision_end_date(nvl(bcb.to_end_item_rev_id,-1)));
2605 exception
2606 when others then
2607 null;
2608 end;
2609 l_stmt_no := 240;
2610 l_upd_cntr := 0;
2611 l_ins_cntr := 0;
2612
2613 for cntr in 1..v_item_comp_tbl.count loop
2614 l_comp_id := v_item_comp_tbl(cntr).component_item_id;
2615 l_stmt_no := 250;
2616 -- Start: Process components existing in both ICC and Item structure with same attributes
2617 if v_icc_comp_tbl.exists(l_comp_id) and
2618 EGO_ICC_STRUCTURE_PVT.Compare_components(v_item_comp_tbl(cntr).component_sequence_id,
2619 v_icc_comp_tbl(l_comp_id).component_sequence_id) = 0 then
2620 /* If the component has same attributes as that in ICC component list, We do not need to process it. */
2621 l_stmt_no := 260;
2622 v_icc_comp_tbl(l_comp_id).is_component_present := 1;
2623 else
2624
2625 /* Processing components having differing attributes is same as component not found in ICC.
2626 But we need to additionally insert a new row with modified attributes. * /
2627
2628 /* Say we are updating revision B's date. If loop handles below cases :
2629 A - null, A - B, A - C Revision A can be any of the earlier revisions. */
2630 -- Start: Process Components efeective from previous revisions
2631 l_stmt_no := 270;
2632 if (v_rev_index(v_item_comp_tbl(cntr).from_end_item_rev_id) <= v_rev_index(l_prev_rev_id)) and -- if-4
2633 (v_rev_index(nvl(v_item_comp_tbl(cntr).to_end_item_rev_id,p_revision_id)) >= v_rev_index(p_revision_id)) then
2634
2635 /* As the comp is not present for current rev, Upd to_end_item_rev_id = l_prev_rev_id. for eg: A - A */
2636 l_upd_cntr := l_upd_cntr+1;
2637 v_update_comp_tbl(l_upd_cntr).component_sequence_id := v_item_comp_tbl(cntr).component_sequence_id;
2638 v_update_comp_tbl(l_upd_cntr).component_item_id := v_item_comp_tbl(cntr).component_item_id;
2639 v_update_comp_tbl(l_upd_cntr).from_revision_id := v_item_comp_tbl(cntr).from_end_item_rev_id;
2640 v_update_comp_tbl(l_upd_cntr).to_revision_id := l_prev_rev_id;
2641 l_stmt_no := 280;
2642 if v_item_comp_tbl(cntr).to_end_item_rev_id is null then -- if-2
2643 if l_next_rev_id is not null then -- if-1
2644 /* In case of A - C,A - null and next rev exists, Insert with from_rev_id as l_next_rev_id. For eg : C - C,C - null */
2645 l_stmt_no := 290;
2646 l_ins_cntr := l_ins_cntr+1;
2647 v_insert_comp_tbl(l_ins_cntr).component_sequence_id := v_item_comp_tbl(cntr).component_sequence_id;
2648 v_insert_comp_tbl(l_ins_cntr).component_item_id := v_item_comp_tbl(cntr).component_item_id;
2649 v_insert_comp_tbl(l_ins_cntr).from_revision_id := l_next_rev_id;
2650 v_insert_comp_tbl(l_ins_cntr).to_revision_id := null;
2651 v_insert_comp_tbl(l_ins_cntr).bill_seq_id := l_item_bill_sequence_id;
2652 end if; -- if-1
2653 else
2654 if v_rev_index(v_item_comp_tbl(cntr).to_end_item_rev_id) > v_rev_index(p_revision_id) then -- if-3
2655 /* If to_rev_id is not null and greater than current rev, then insert Eg : C - null or C - D */
2656 l_stmt_no := 300;
2657 l_ins_cntr := l_ins_cntr+1;
2658 v_insert_comp_tbl(l_ins_cntr).component_sequence_id := v_item_comp_tbl(cntr).component_sequence_id;
2659 v_insert_comp_tbl(l_ins_cntr).component_item_id := v_item_comp_tbl(cntr).component_item_id;
2660 v_insert_comp_tbl(l_ins_cntr).from_revision_id := l_next_rev_id;
2661 v_insert_comp_tbl(l_ins_cntr).to_revision_id := v_item_comp_tbl(cntr).to_end_item_rev_id;
2662 v_insert_comp_tbl(l_ins_cntr).bill_seq_id := l_item_bill_sequence_id;
2663 end if; -- if-3
2664 end if; -- if-2
2665 else
2666 /* This else handles comp with from_rev_id as current rev. Eg : B - B, B - C, B - null */
2667 -- Start: Process Components efeective from current revision
2668 l_stmt_no := 310;
2669 if v_item_comp_tbl(cntr).from_end_item_rev_id = p_revision_id then -- if-5
2670 if v_item_comp_tbl(cntr).to_end_item_rev_id = p_revision_id then --if-6
2671 /* Deleting the comp and its attributes. if it exists only for the current revision. */
2672 l_stmt_no := 320;
2673 Delete_Comp_User_Attrs(v_item_comp_tbl(cntr).component_sequence_id);
2674
2675 delete from bom_components_b
2676 where component_sequence_id = v_item_comp_tbl(cntr).component_sequence_id;
2677
2678 else
2679 if v_item_comp_tbl(cntr).to_end_item_rev_id is null then -- if-7
2680 if l_next_rev_id is not null then -- if-8
2681 /* In case of B - C or B - null and next rev exists :
2682 Inserting a row with from_rev_id as l_next_rev_id. For eg : C - C or C - null */
2683 l_stmt_no := 330;
2684 l_upd_cntr := l_upd_cntr+1;
2685 v_update_comp_tbl(l_upd_cntr).component_sequence_id := v_item_comp_tbl(cntr).component_sequence_id;
2686 v_update_comp_tbl(l_upd_cntr).component_item_id := v_item_comp_tbl(cntr).component_item_id;
2687 v_update_comp_tbl(l_upd_cntr).from_revision_id := l_next_rev_id;
2688 v_update_comp_tbl(l_upd_cntr).to_revision_id := v_item_comp_tbl(cntr).to_end_item_rev_id;
2689 else
2690 /* Delete if component and its attributes which exists as B - null */
2691 l_stmt_no := 340;
2692 Delete_Comp_User_Attrs(v_item_comp_tbl(cntr).component_sequence_id);
2693
2694 delete from bom_components_b
2695 where component_sequence_id = v_item_comp_tbl(cntr).component_sequence_id;
2696 end if; -- if-8
2697 else
2698 /* If to_rev_id is not null then update from_rev_id. For eg : C - null or C - D */
2699 l_stmt_no := 350;
2700 l_upd_cntr := l_upd_cntr+1;
2701 v_update_comp_tbl(l_upd_cntr).component_sequence_id := v_item_comp_tbl(cntr).component_sequence_id;
2702 v_update_comp_tbl(l_upd_cntr).component_item_id := v_item_comp_tbl(cntr).component_item_id;
2703 v_update_comp_tbl(l_upd_cntr).from_revision_id := l_next_rev_id;
2704 v_update_comp_tbl(l_upd_cntr).to_revision_id := v_item_comp_tbl(cntr).to_end_item_rev_id;
2705 end if; --if-7
2706 end if; -- if-6
2707 end if; -- if-5
2708 -- End: Process Components efeective from current revision
2709 end if; --if-4
2710 -- End: Process Components efeective from previous revisions
2711 end if;
2712 -- End: Process components existing in both ICC and Item structure with same attributes
2713 end loop;
2714 l_stmt_no := 360;
2715 -- process rows which earlier didn't exist in item structure.
2716 l_icc_index := v_icc_comp_tbl.first;
2717 while l_icc_index <= v_icc_comp_tbl.last loop
2718 if v_icc_comp_tbl(l_icc_index).is_component_present = 0 then
2719 l_stmt_no := 370;
2720 l_ins_cntr := l_ins_cntr+1;
2721 v_insert_comp_tbl(l_ins_cntr).component_sequence_id := v_icc_comp_tbl(l_icc_index).component_sequence_id;
2722 v_insert_comp_tbl(l_ins_cntr).component_item_id := v_icc_comp_tbl(l_icc_index).component_item_id;
2723 v_insert_comp_tbl(l_ins_cntr).from_revision_id := p_revision_id;
2724 v_insert_comp_tbl(l_ins_cntr).bill_seq_id := v_icc_comp_tbl(l_icc_index).bill_sequence_id;
2725 -- Modified
2726 If l_next_rev_id is null then
2727 v_insert_comp_tbl(l_ins_cntr).to_revision_id := l_next_rev_id;
2728 Else
2729 v_insert_comp_tbl(l_ins_cntr).to_revision_id := p_revision_id;
2730 End If;
2731 end if;
2732 l_icc_index := v_icc_comp_tbl.next(l_icc_index);
2733 l_stmt_no := 380;
2734 end loop;
2735 l_stmt_no := 390;
2736 select data_level_id
2737 into l_data_level_id
2738 from ego_data_level_b
2739 where data_level_name = 'COMPONENTS_LEVEL'
2740 and attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
2741 and application_id = 702;
2742 l_stmt_no := 400;
2743 -- Create UDA values for the new components created.
2744 for cntr in 1..v_insert_comp_tbl.count loop
2745 l_stmt_no := 410;
2746 l_item_seq_incr := l_item_seq_incr + to_number(l_item_seq_incr_prof);
2747
2748 select BOM_INVENTORY_COMPONENTS_S.NEXTVAL
2749 into l_new_component_seq_id
2750 from dual;
2751 l_stmt_no := 420;
2752 Insert into BOM_COMPONENTS_B
2753 (OPERATION_SEQ_NUM,
2754 COMPONENT_ITEM_ID,
2755 LAST_UPDATE_DATE,
2756 LAST_UPDATED_BY,
2757 CREATION_DATE,
2758 CREATED_BY,
2759 LAST_UPDATE_LOGIN,
2760 ITEM_NUM,
2761 COMPONENT_QUANTITY,
2762 COMPONENT_YIELD_FACTOR,
2763 EFFECTIVITY_DATE,
2764 IMPLEMENTATION_DATE,
2765 PLANNING_FACTOR,
2766 QUANTITY_RELATED,
2767 SO_BASIS,
2768 OPTIONAL,
2769 MUTUALLY_EXCLUSIVE_OPTIONS,
2770 INCLUDE_IN_COST_ROLLUP,
2771 CHECK_ATP,
2772 SHIPPING_ALLOWED,
2773 REQUIRED_TO_SHIP,
2774 REQUIRED_FOR_REVENUE,
2775 INCLUDE_ON_SHIP_DOCS,
2776 COMPONENT_SEQUENCE_ID,
2777 BILL_SEQUENCE_ID,
2778 WIP_SUPPLY_TYPE,
2779 PICK_COMPONENTS,
2780 SUPPLY_SUBINVENTORY,
2781 SUPPLY_LOCATOR_ID,
2782 BOM_ITEM_TYPE,
2783 ENFORCE_INT_REQUIREMENTS,
2784 COMPONENT_ITEM_REVISION_ID,
2785 PARENT_BILL_SEQ_ID,
2786 AUTO_REQUEST_MATERIAL,
2787 PK1_VALUE,
2788 PK2_VALUE,
2789 PK3_VALUE,
2790 PK4_VALUE,
2791 PK5_VALUE,
2792 FROM_END_ITEM_REV_ID,
2793 TO_END_ITEM_REV_ID,
2794 FROM_OBJECT_REVISION_ID,
2795 TO_OBJECT_REVISION_ID,
2796 INHERIT_FLAG,
2797 COMPONENT_REMARKS,
2798 CHANGE_NOTICE,
2799 BASIS_TYPE,
2800 LOW_QUANTITY,
2801 HIGH_QUANTITY)
2802 select
2803 BCB.OPERATION_SEQ_NUM,
2804 BCB.COMPONENT_ITEM_ID,
2805 sysdate,
2806 fnd_global.user_id,
2807 sysdate,
2808 fnd_global.user_id,
2809 fnd_global.login_id,
2810 l_item_seq_incr,
2811 BCB.COMPONENT_QUANTITY,
2812 BCB.COMPONENT_YIELD_FACTOR,
2813 BCB.EFFECTIVITY_DATE,
2814 BCB.IMPLEMENTATION_DATE,
2815 BCB.PLANNING_FACTOR,
2816 BCB.QUANTITY_RELATED,
2817 BCB.SO_BASIS,
2818 BCB.OPTIONAL,
2819 BCB.MUTUALLY_EXCLUSIVE_OPTIONS,
2820 BCB.INCLUDE_IN_COST_ROLLUP,
2821 BCB.CHECK_ATP,
2822 BCB.SHIPPING_ALLOWED,
2823 BCB.REQUIRED_TO_SHIP,
2824 BCB.REQUIRED_FOR_REVENUE,
2825 BCB.INCLUDE_ON_SHIP_DOCS,
2826 l_new_component_seq_id,
2827 l_item_bill_sequence_id,
2828 BCB.WIP_SUPPLY_TYPE,
2829 BCB.PICK_COMPONENTS,
2830 BCB.SUPPLY_SUBINVENTORY,
2831 BCB.SUPPLY_LOCATOR_ID,
2832 BCB.BOM_ITEM_TYPE,
2833 BCB.ENFORCE_INT_REQUIREMENTS,
2834 BCB.COMPONENT_ITEM_REVISION_ID,
2835 null,
2836 BCB.AUTO_REQUEST_MATERIAL,
2837 BCB.PK1_VALUE,
2838 BCB.PK2_VALUE,
2839 BCB.PK3_VALUE,
2840 BCB.PK4_VALUE,
2841 BCB.PK5_VALUE,
2842 v_insert_comp_tbl(cntr).from_revision_id,
2843 v_insert_comp_tbl(cntr).to_revision_id,
2844 v_insert_comp_tbl(cntr).from_revision_id,
2845 v_insert_comp_tbl(cntr).to_revision_id,
2846 1,
2847 BCB.COMPONENT_REMARKS,
2848 BCB.CHANGE_NOTICE,
2849 BCB.BASIS_TYPE,
2850 BCB.LOW_QUANTITY,
2851 BCB.HIGH_QUANTITY
2852 from BOM_COMPONENTS_B BCB
2853 where
2854 BCB.COMPONENT_SEQUENCE_ID = v_insert_comp_tbl(cntr).component_sequence_id;
2855 /* UT Fix : Commented as Comp_seq_id is alone enough to find a record in bom_components_b */
2856 -- and BCB.BILL_SEQUENCE_ID = l_catalog_bill_sequence_id) );
2857 l_stmt_no := 430;
2858 l_src_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'COMPONENT_SEQUENCE_ID' ,
2859 to_char(v_insert_comp_tbl(cntr).component_sequence_id)),
2860 EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' ,
2861 to_char(v_insert_comp_tbl(cntr).bill_seq_id)));
2862 l_dest_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'COMPONENT_SEQUENCE_ID' ,
2863 l_new_component_seq_id),
2864 EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' ,
2865 to_char(l_item_bill_sequence_id)));
2866 l_str_type := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'STRUCTURE_TYPE_ID',
2867 TO_CHAR(l_catalog_str_type_id)));
2868 l_data_level_pks := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'CONTEXT_ID' , null));
2869 l_stmt_no := 440;
2870 EGO_USER_ATTRS_DATA_PVT.Copy_User_Attrs_Data
2871 (
2872 p_api_version => 1.0
2873 ,p_application_id => 702
2874 ,p_object_name => 'BOM_COMPONENTS'
2875 ,p_old_pk_col_value_pairs => l_src_pk_col_name_val_pairs
2876 ,p_new_pk_col_value_pairs => l_dest_pk_col_name_val_pairs
2877 ,p_old_dtlevel_col_value_pairs => l_data_level_pks
2878 ,p_new_dtlevel_col_value_pairs => l_data_level_pks
2879 ,p_old_data_level_id => l_data_level_id
2880 ,p_new_data_level_id => l_data_level_id
2881 ,p_new_cc_col_value_pairs => l_str_type
2882 ,x_return_status => l_Return_Status
2883 ,x_errorcode => l_errorcode
2884 ,x_msg_count => l_msg_count
2885 ,x_msg_data => l_msg_data
2886 );
2887 l_stmt_no := 450;
2888 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2889 x_Return_Status := 1;
2890 x_Error_Message := l_msg_data;
2891 exit;
2892 END IF;
2893
2894 end loop;
2895 l_stmt_no := 460;
2896 -- update bom_components_b based on v_update_comp_tbl.
2897 -- Modified Since 10G doesn't support BULK In-BIND table of records. Only 11G supports it.
2898
2899 /* forall upd_index in v_update_comp_tbl.first..v_update_comp_tbl.last
2900 update bom_components_b set
2901 from_end_item_rev_id = v_update_comp_tbl(upd_index).from_revision_id,
2902 to_end_item_rev_id = v_update_comp_tbl(upd_index).to_revision_id,
2903 from_object_revision_id = v_update_comp_tbl(upd_index).from_revision_id,
2904 last_update_date = sysdate,
2905 last_updated_by = fnd_global.user_id,
2906 last_update_login = fnd_global.login_id
2907 where
2908 component_sequence_id = v_update_comp_tbl(upd_index).component_sequence_id; */
2909 l_stmt_no := 470;
2910 if v_update_comp_tbl.exists(v_update_comp_tbl.first) then
2911 for upd_index in v_update_comp_tbl.first..v_update_comp_tbl.last
2912 loop
2913 update bom_components_b
2914 set
2915 from_end_item_rev_id = v_update_comp_tbl(upd_index).from_revision_id,
2916 to_end_item_rev_id = v_update_comp_tbl(upd_index).to_revision_id,
2917 from_object_revision_id = v_update_comp_tbl(upd_index).from_revision_id,
2918 last_update_date = sysdate,
2919 last_updated_by = fnd_global.user_id,
2920 last_update_login = fnd_global.login_id
2921 where
2922 component_sequence_id = v_update_comp_tbl(upd_index).component_sequence_id;
2923 end loop;
2924 end if;
2925 l_stmt_no := 480;
2926 if l_item_bill_sequence_id is not null then
2927 /* UT Fix : Assigned ass_item_id to global variable and this value will be used in validate_component_overlap. */
2928 G_INV_ITEM_ID := p_inventory_item_id;
2929 --x_Return_Status := validate_component_overlap(l_item_bill_sequence_id);
2930
2931 x_Return_Status := validate_component_overlap(p_bill_seq_id => l_item_bill_sequence_id,
2932 p_alt_desg => G_ALTCODE,
2933 x_error_msg => x_Error_Message);
2934 l_stmt_no := 490;
2935 end if;
2936
2937 EXCEPTION
2938 WHEN others THEN
2939 x_Return_Status := 1;
2940 x_Error_Message := x_Return_Status||'UnHandled exception while inheriting structure components('||l_stmt_no||') : '||sqlerrm(sqlcode);
2941
2942 END inherit_icc_components;
2943
2944 Function Is_Structure_Inheriting(p_item_catalog_grp_id IN NUMBER,
2945 p_organization_id IN NUMBER,
2946 p_inv_item_id IN NUMBER,
2947 p_structure_type_id IN NUMBER,
2948 p_alt_desig IN VARCHAR2)
2949 RETURN NUMBER
2950 IS
2951 l_return_status Number;
2952 l_effective_version Number;
2953 l_bill_seq_id Number;
2954
2955 cursor item_revisions is
2956 select effectivity_date
2957 from mtl_item_revisions_b
2958 where inventory_item_id = p_inv_item_id
2959 and organization_id = p_organization_id;
2960
2961 BEGIN
2962
2963 for revision in item_revisions loop
2964
2965 begin
2966
2967 select 1
2968 into l_return_status
2969 from dual
2970 where exists (select 1
2971 from (select item_catalog_group_id
2972 from mtl_item_catalog_groups_b
2973 connect by prior parent_catalog_group_id = item_catalog_group_id
2974 start with item_catalog_group_id = p_item_catalog_grp_id) icc,
2975 bom_structures_b icc_structure,
2976 bom_components_b icc_str_components
2977 where icc_structure.pk1_value = icc.item_catalog_group_id
2978 and icc_structure.pk2_value = p_organization_id
2979 and icc_structure.obj_name = 'EGO_CATALOG_GROUP'
2980 and icc_structure.structure_type_id = p_structure_type_id
2981 and icc_structure.alternate_bom_designator = p_alt_desig
2982 and icc_structure.assembly_type = 2
2983 and icc_structure.effectivity_control = 4
2984 and icc_structure.bill_sequence_id = icc_str_components.bill_sequence_id
2985 and nvl(icc_str_components.parent_bill_seq_id,icc_str_components.bill_sequence_id) = icc_structure.bill_sequence_id
2986 and icc_str_components.from_object_revision_id = EGO_ICC_STRUCTURE_PVT.Get_Effective_Version(icc_structure.pk1_value,
2987 revision.effectivity_date)
2988 and rownum = 1);
2989
2990 exception
2991 when no_data_found then
2992 l_return_status := 0;
2993 end;
2994
2995 if l_return_status = 1 then
2996 exit;
2997 end if;
2998
2999 end loop;
3000 return l_return_status;
3001 EXCEPTION
3002 WHEN OTHERS THEN
3003 return 0;
3004 END Is_Structure_Inheriting;
3005
3006 /*
3007 * This Procedure creates default structure header for versioned ICCs based on its hierarchy.
3008 * This procedure has autonomous commit since, it will be called from ICC Structures Page processRequest().
3009 */
3010 Procedure Create_Default_Header(p_item_catalog_grp_id IN NUMBER,
3011 p_commit_flag IN NUMBER)
3012 IS
3013 PRAGMA autonomous_transaction;
3014
3015 l_create_header Number := 0;
3016 l_bill_sequence_id Number;
3017
3018 cursor Get_str_catalog_hierarchy
3019 IS
3020 select structures.bill_sequence_id
3021 from ( select item_catalog_group_id
3022 from mtl_item_catalog_groups_b
3023 connect by prior parent_catalog_group_id = item_catalog_group_id
3024 start with item_catalog_group_id = p_item_catalog_grp_id ) icc,
3025 BOM_STRUCTURES_B structures
3026 where structures.pk1_value = icc.item_catalog_group_id
3027 and structures.obj_name = 'EGO_CATALOG_GROUP'
3028 and rownum = 1;
3029
3030 Begin
3031
3032 /* We need to create structure header only for versioned ICCs which doesn't have structure header already. */
3033
3034 select 1
3035 into l_create_header
3036 from dual
3037 where exists (select 1
3038 from EGO_MTL_CATALOG_GRP_VERS_B
3039 where item_catalog_group_id = p_item_catalog_grp_id)
3040 and not exists (select 1
3041 from BOM_STRUCTURES_B
3042 where pk1_value = p_item_catalog_grp_id
3043 and obj_name = 'EGO_CATALOG_GROUP');
3044
3045 if l_create_header = 1 then
3046
3047 for icc_structure in Get_str_catalog_hierarchy loop
3048
3049 select bom_inventory_components_s.nextval
3050 into l_bill_sequence_id
3051 from dual;
3052
3053 insert into BOM_STRUCTURES_B
3054 (BILL_SEQUENCE_ID,
3055 SOURCE_BILL_SEQUENCE_ID,
3056 COMMON_BILL_SEQUENCE_ID,
3057 ORGANIZATION_ID,
3058 ALTERNATE_BOM_DESIGNATOR,
3059 ASSEMBLY_TYPE,
3060 STRUCTURE_TYPE_ID,
3061 EFFECTIVITY_CONTROL,
3062 IS_PREFERRED,
3063 OBJ_NAME,
3064 PK1_VALUE,
3065 PK2_VALUE,
3066 LAST_UPDATE_DATE,
3067 LAST_UPDATED_BY,
3068 CREATION_DATE,
3069 CREATED_BY,
3070 LAST_UPDATE_LOGIN)
3071 select
3072 l_bill_sequence_id,
3073 l_bill_sequence_id,
3074 l_bill_sequence_id,
3075 ORGANIZATION_ID,
3076 ALTERNATE_BOM_DESIGNATOR,
3077 ASSEMBLY_TYPE,
3078 STRUCTURE_TYPE_ID,
3079 EFFECTIVITY_CONTROL,
3080 IS_PREFERRED,
3081 OBJ_NAME,
3082 p_item_catalog_grp_id,
3083 PK2_VALUE,
3084 LAST_UPDATE_DATE,
3085 LAST_UPDATED_BY,
3086 CREATION_DATE,
3087 CREATED_BY,
3088 LAST_UPDATE_LOGIN
3089 from BOM_STRUCTURES_B
3090 where BILL_SEQUENCE_ID = icc_structure.bill_sequence_id ;
3091
3092 end loop;
3093
3094 end if;
3095
3096 if p_commit_flag = 1 then
3097 commit;
3098 end if;
3099
3100 Exception
3101 when others then
3102 null;
3103 End Create_Default_Header;
3104
3105 /*
3106 * This Function gives whether Parent-ICC is updatable for a ICC.
3107 */
3108 Function Is_Parent_Updatable(p_item_catalog_grp_id IN NUMBER)
3109 Return NUMBER
3110 IS
3111 l_updatable Number := 0 ;
3112 Begin
3113
3114 select 1
3115 into l_updatable
3116 from dual
3117 where not exists (select 1
3118 from BOM_STRUCTURES_B bsb,
3119 (select item_catalog_group_id
3120 from mtl_item_catalog_groups_b
3121 connect by prior item_catalog_group_id = parent_catalog_group_id
3122 start with item_catalog_group_id = p_item_catalog_grp_id ) child_icc
3123 where bsb.pk1_value = child_icc.item_catalog_group_id
3124 and bsb.obj_name = 'EGO_CATALOG_GROUP'
3125 and rownum = 1);
3126
3127 return l_updatable;
3128 Exception
3129 when others then
3130 return 0;
3131 End Is_Parent_Updatable;
3132
3133 /*
3134 * This Function Validates component Base Attributes.
3135 */
3136 Function Validate_Base_attributes(
3137 p_organization_id IN NUMBER,
3138 p_operation_seq_num IN NUMBER,
3139 p_component_item_id IN NUMBER,
3140 p_item_num IN NUMBER,
3141 p_basis_type IN NUMBER,
3142 p_component_quantity IN NUMBER,
3143 p_component_yield_factor IN NUMBER,
3144 p_component_remarks IN VARCHAR2,
3145 p_planning_factor IN NUMBER,
3146 p_quantity_related IN NUMBER,
3147 p_so_basis IN NUMBER,
3148 p_optional IN NUMBER,
3149 p_mutually_exclusive_options IN NUMBER,
3150 p_include_in_cost_rollup IN NUMBER,
3151 p_check_atp IN NUMBER,
3152 p_shipping_allowed IN NUMBER,
3153 p_required_to_ship IN NUMBER,
3154 p_required_for_revenue IN NUMBER,
3155 p_include_on_ship_docs IN NUMBER,
3156 p_low_quantity IN NUMBER,
3157 p_high_quantity IN NUMBER,
3158 p_component_sequence_id IN NUMBER,
3159 p_bill_sequence_id IN NUMBER,
3160 p_wip_supply_type IN NUMBER,
3161 p_pick_components IN NUMBER,
3162 p_supply_subinventory IN VARCHAR2,
3163 p_supply_locator_id IN NUMBER,
3164 p_bom_item_type IN NUMBER,
3165 p_component_item_revision_id IN NUMBER,
3166 p_enforce_int_requirements IN NUMBER,
3167 p_auto_request_material IN VARCHAR2,
3168 p_component_name IN VARCHAR2)
3169 Return NUMBER
3170 IS
3171 l_val_failure Number := 0 ;
3172 l_dummy Number;
3173 l_Bom_Status VARCHAR2(80);
3174 l_Industry VARCHAR2(80);
3175 l_org_locator_control Number := 0;
3176 l_item_locator_control Number := 0;
3177 l_locator_control Number;
3178 l_sub_locator_control Number :=0;
3179 Begin
3180 FND_MSG_PUB.Delete_Msg(null);
3181 if p_component_yield_factor is null then
3182 FND_MESSAGE.set_name('BOM', 'BOM_COMP_YIELD_MISSING');
3183 FND_MESSAGE.set_token('REVISED_COMPONENT_NAME', p_component_name);
3184 FND_MSG_PUB.add;
3185 l_val_failure := l_val_failure+1;
3186 end if;
3187
3188 if (p_component_yield_factor is not null AND
3189 (p_component_yield_factor < 0 OR p_component_yield_factor > 1 )) then
3190 FND_MESSAGE.set_name('BOM', 'BOM_COMPYIELD_OUT_OF_RANGE');
3191 FND_MESSAGE.set_token('REVISED_COMPONENT_NAME', p_component_name);
3192 FND_MSG_PUB.add;
3193 l_val_failure := l_val_failure+1;
3194 end if;
3195
3196 -- Component is Option Class and yield <> 1
3197 if nvl(p_component_yield_factor,1) <> 1 and
3198 p_bom_item_type = 2 then
3199 FND_MESSAGE.set_name('BOM', 'BOM_COMP_YIELD_NOT_ONE');
3200 FND_MESSAGE.set_token('REVISED_COMPONENT_NAME', p_component_name);
3201 FND_MESSAGE.set_token('REVISED_ITEM_NAME', null);
3202 FND_MSG_PUB.add;
3203 l_val_failure := l_val_failure+1;
3204 end if;
3205
3206 -- Enforce_Integer can be UP only if rounding control type allows to round order quantities.
3207 if p_enforce_int_requirements = 1 then
3208 BEGIN
3209 select 1
3210 into l_dummy
3211 from mtl_system_items
3212 where inventory_item_id = p_component_item_id
3213 and organization_id = p_organization_id
3214 and rounding_control_type = 1;
3215 EXCEPTION
3216 WHEN NO_DATA_FOUND THEN
3217 FND_MESSAGE.set_name('BOM', 'BOM_ENFORCE_INT_INVALID');
3218 FND_MSG_PUB.add;
3219 l_val_failure := l_val_failure+1;
3220 END;
3221 end if;
3222
3223 if p_basis_type = 2 and p_wip_supply_type = 6 then
3224 FND_MESSAGE.set_name('BOM', 'BOM_LOT_BASED_PHANTOM');
3225 FND_MSG_PUB.add;
3226 l_val_failure := l_val_failure+1;
3227 end if;
3228
3229 if ( p_basis_type = 2 and ( p_optional = 1 or p_bom_item_type in (1,2))) THEN
3230 FND_MESSAGE.set_name('BOM', 'BOM_LOT_BASED_ATO');
3231 FND_MSG_PUB.add;
3232 l_val_failure := l_val_failure+1;
3233 end if;
3234
3235 if p_auto_request_material IS NOT NULL AND upper(p_auto_request_material) NOT IN ('Y','N') THEN
3236 FND_MESSAGE.set_name('BOM', 'BOM_AUTO_REQ_MAT_INVALID');
3237 FND_MESSAGE.set_token('REVISED_COMPONENT_NAME', p_component_name);
3238 FND_MESSAGE.set_token('AUTO_REQ_MATERIAL', p_auto_request_material);
3239 FND_MSG_PUB.add;
3240 l_val_failure := l_val_failure+1;
3241 end if;
3242
3243 if NVL(p_high_quantity,p_component_quantity) < NVL(p_component_quantity,0) THEN
3244 FND_MESSAGE.set_name('BOM', 'BOM_MAX_QUANTITY_INVALID');
3245 FND_MESSAGE.set_token('REVISED_COMPONENT_NAME', p_component_name);
3246 FND_MSG_PUB.add;
3247 l_val_failure := l_val_failure+1;
3248 end if;
3249
3250 if NVL(p_low_quantity, p_component_quantity) > NVL(p_component_quantity,0) THEN
3251 FND_MESSAGE.set_name('BOM', 'BOM_MIN_QUANTITY_INVALID');
3252 FND_MESSAGE.set_token('REVISED_COMPONENT_NAME', p_component_name);
3253 FND_MSG_PUB.add;
3254 l_val_failure := l_val_failure+1;
3255 end if;
3256
3257 if BOM_EAMUTIL.Asset_Activity_Item(item_id => p_component_item_id,
3258 org_id => p_organization_id ) = 'Y' and
3259 p_wip_supply_type NOT IN (1,4) THEN
3260 FND_MESSAGE.set_name('BOM', 'BOM_INVALID_AA_SUPTYPES');
3261 FND_MSG_PUB.add;
3262 l_val_failure := l_val_failure+1;
3263 end if;
3264
3265 if p_wip_supply_type is not null and p_wip_supply_type = 7 THEN
3266 FND_MESSAGE.set_name('BOM', 'BOM_WIP_SUPPLY_TYPE_7');
3267 FND_MESSAGE.set_token('REVISED_COMPONENT_NAME', p_component_name);
3268 FND_MESSAGE.set_token('WIP_SUPPLY_TYPE', 'Based On Bill');
3269 FND_MSG_PUB.add;
3270 l_val_failure := l_val_failure+1;
3271 end if;
3272
3273 if p_check_atp = 1 and p_component_quantity < 0 THEN
3274 FND_MESSAGE.set_name('BOM', 'BOM_COMP_QTY_NEGATIVE');
3275 FND_MESSAGE.set_token('REVISED_COMPONENT_NAME', p_component_name);
3276 FND_MSG_PUB.add;
3277 l_val_failure := l_val_failure+1;
3278 end if;
3279
3280 if p_pick_components = 1 and p_component_quantity < 0 THEN
3281 FND_MESSAGE.set_name('BOM', 'BOM_COMP_PTO_QTY_NEGATIVE');
3282 FND_MESSAGE.set_token('REVISED_COMPONENT_NAME', p_component_name);
3283 FND_MSG_PUB.add;
3284 l_val_failure := l_val_failure+1;
3285 end if;
3286
3287 IF p_mutually_exclusive_options = 1 THEN
3288 IF Fnd_Installation.Get( appl_id => '702',
3289 dep_appl_id => '702',
3290 status => l_bom_status,
3291 industry => l_industry) AND
3292 p_bom_item_type IN (1,2) THEN
3293 null;
3294 ELSIF p_bom_item_type NOT IN (1,2) THEN
3295 FND_MESSAGE.set_name('BOM', 'BOM_MUT_EXCL_NOT_MDL_OPTCLASS');
3296 FND_MESSAGE.set_token('REVISED_COMPONENT_NAME', p_component_name);
3297 FND_MSG_PUB.add;
3298 l_val_failure := l_val_failure+1;
3299 ELSE
3300 FND_MESSAGE.set_name('BOM', 'BOM_MUT_EXCL_BOM_NOT_INST');
3301 FND_MSG_PUB.add;
3302 l_val_failure := l_val_failure+1;
3303 END IF;
3304 ELSE
3305 null;
3306 END IF;
3307
3308 IF p_so_basis = 1 AND p_bom_item_type <> Bom_Globals.G_OPTION_CLASS THEN
3309 FND_MESSAGE.set_name('BOM', 'BOM_SO_BASIS_ONE');
3310 FND_MESSAGE.set_token('REVISED_COMPONENT_NAME', p_component_name);
3311 FND_MSG_PUB.add;
3312 l_val_failure := l_val_failure+1;
3313 END IF;
3314
3315 Begin
3316 SELECT stock_locator_control_code INTO l_org_locator_control
3317 FROM mtl_parameters WHERE organization_id = p_organization_id;
3318
3319 SELECT location_control_code INTO l_item_locator_control
3320 FROM mtl_system_items
3321 WHERE organization_id = p_organization_id AND inventory_item_id = p_component_item_id;
3322
3323 SELECT LOCATOR_TYPE into l_sub_locator_control
3324 FROM MTL_SECONDARY_INVENTORIES
3325 WHERE ORGANIZATION_ID = p_organization_id and SECONDARY_INVENTORY_NAME = p_supply_subinventory;
3326
3327 l_locator_control := BOM_Validate_Bom_Component.Control
3328 ( Org_Control => l_org_locator_control,
3329 Sub_Control => l_item_locator_control,
3330 Item_Control => l_sub_locator_control);
3331
3332 if l_locator_control = 1 and p_supply_locator_id is null then
3333 FND_MESSAGE.set_name('BOM', 'BOM_LOCATOR_REQUIRED');
3334 FND_MESSAGE.set_token('REVISED_COMPONENT_NAME', p_component_name);
3335 FND_MSG_PUB.add;
3336 l_val_failure := l_val_failure+1;
3337 end if;
3338 Exception
3339 when others then
3340 null;
3341 End;
3342
3343 return l_val_failure;
3344
3345 Exception
3346 when others then
3347 FND_MSG_PUB.Add_Exc_Msg('EGO_ICC_STRUCTURE_PVT','Validate_Base_attributes',sqlerrm(sqlcode));
3348 return 1;
3349 End Validate_Base_attributes;
3350
3351 END EGO_ICC_STRUCTURE_PVT;