[Home] [Help]
92: SELECT count(1)
93: FROM DUAL
94: WHERE EXISTS (
95: SELECT 1
96: FROM bom_bill_of_materials bbom,
97: bom_inventory_components bic,
98: mtl_system_items msi
99: WHERE bbom.assembly_item_id = L_NEW_ASSEMBLY_ITEM_ID
100: AND bbom.organization_id = VAR_ORGANIZATION_ID
717: l_current_attr_group_id := rev_items.attribute_number_value;
718:
719: -- Check whether there are any structures created for the item with this change order ..
720: SELECT count(*) INTO l_pending_changes_count
721: FROM bom_bill_of_materials
722: WHERE assembly_item_id = l_inventory_item_id
723: AND organization_id = l_org_id
724: AND structure_type_id = l_current_attr_group_id
725: AND pending_from_ecn = ( SELECT change_notice FROM eng_engineering_changes
733: END IF;
734:
735: -- Check whether there are any component changes created for the item with this change order ..
736: SELECT count(*) INTO l_pending_changes_count
737: FROM bom_components_b comp, bom_bill_of_materials bom
738: WHERE revised_item_sequence_id = p_change_line_id
739: AND comp.implementation_date IS NULL
740: AND comp.bill_sequence_id = bom.bill_sequence_id
741: AND bom.structure_type_id = l_current_attr_group_id
755: WHERE op.revised_item_sequence_id = p_change_line_id
756: AND op.revised_item_sequence_id = ri.revised_item_sequence_id
757: AND op.implementation_date IS NULL
758: AND st.structure_type_id = ( SELECT bbom.structure_type_id
759: FROM bom_bill_of_materials bbom
760: WHERE bbom.bill_sequence_id = ri.bill_sequence_id )
761: AND st.structure_type_id = l_current_attr_group_id
762: AND op.change_notice = ( SELECT change_notice FROM eng_engineering_changes
763: WHERE change_id in (SELECT change_id FROM eng_revised_items
1567:
1568: CURSOR Get_Bill_of_Materials_Info( bill_id IN NUMBER) IS
1569: SELECT pending_from_ecn
1570: , alternate_bom_designator
1571: FROM bom_bill_of_materials
1572: WHERE bill_sequence_id = bill_id;
1573:
1574: CURSOR Get_Operational_Routing_Info( routing_id IN NUMBER) IS
1575: SELECT pending_from_ecn
1750:
1751: Cursor get_common_bills is
1752: Select b.organization_id, b.assembly_item_id,
1753: b.bill_sequence_id
1754: from bom_bill_of_materials b
1755: where b.common_assembly_item_id = item.revised_item_id
1756: and b.common_organization_id = item.organization_id
1757: AND b.source_bill_sequence_id = item.bill_sequence_id; -- R12: Common BOM changes
1758: --and b.common_bill_sequence_id = item.bill_sequence_id;
2549: null bom_revision, -- Bug 3381547
2550: null routing_revision -- Bug 3381547
2551: FROM wip_discrete_jobs wdj,
2552: wip_entities we,
2553: bom_bill_of_materials b --3412747
2554: WHERE we.organization_id = wdj.organization_id
2555: AND we.wip_entity_id = wdj.wip_entity_id
2556: AND wdj.status_type = 1
2557: AND wdj.job_type = 1 -- 2986915
2616: null bom_revision, -- Bug 3381547
2617: null routing_revision -- Bug 3381547
2618: FROM wip_discrete_jobs wdj,
2619: wip_entities we,
2620: bom_bill_of_materials b --3412747
2621: WHERE we.organization_id = wdj.organization_id
2622: --WHERE we.organization_id = l_wip_organization_id
2623: AND wdj.wip_entity_id = we.wip_entity_id
2624: AND wdj.status_type = 1
2651: wdj.bom_revision bom_revision, -- Bug 3381547
2652: wdj.routing_revision routing_revision -- Bug 3381547
2653: FROM wip_discrete_jobs wdj,
2654: wip_entities we,
2655: bom_bill_of_materials b, --3412747
2656: wip_requirement_operations o
2657: WHERE wdj.wip_entity_id = we.wip_entity_id
2658: AND wdj.status_type = 1
2659: AND wdj.job_type = 1 -- 2986915
2673: (wdj.alternate_bom_designator is not null
2674: and
2675: b.alternate_bom_designator is null
2676: and not exists (select null
2677: from bom_bill_of_materials b2
2678: where b2.organization_id = b.organization_id
2679: and b2.assembly_item_id = b.assembly_item_id
2680: and b2.alternate_bom_designator =
2681: wdj.alternate_bom_designator)
2711: Cursor Check_Bill(p_item_id IN NUMBER,
2712: p_org_id IN NUMBER,
2713: p_alternate IN VARCHAR2) is
2714: Select 'x' dummy
2715: From bom_bill_of_materials bbom
2716: Where bbom.assembly_item_id = p_item_id
2717: And bbom.organization_id = p_org_id
2718: And nvl(bbom.alternate_bom_designator, 'PRIMARY ALTERNATE') =
2719: nvl(p_alternate, 'PRIMARY ALTERNATE')
3020: select 1 from mtl_system_items_b where inventory_item_id in
3021: (
3022: select COMPONENT_ITEM_ID from bom_components_b WHERE bill_sequence_id IN
3023: (
3024: select bill_sequence_id from bom_bill_of_materials where
3025: ASSEMBLY_ITEM_ID = item.revised_item_id AND
3026: ORGANIZATION_ID = item.organization_id AND
3027: (
3028: (item.designator_selection_type = 1) --select everything
3039: select 1 from mtl_system_items_b where inventory_item_id in
3040: (
3041: select COMPONENT_ITEM_ID from bom_components_b WHERE bill_sequence_id IN
3042: (
3043: select bill_sequence_id from bom_bill_of_materials where
3044: ASSEMBLY_ITEM_ID = item.revised_item_id AND
3045: ORGANIZATION_ID = item.organization_id AND
3046: (
3047: (item.designator_selection_type = 1) --select everything
3080: select SUBSTITUTE_COMPONENT_ID from bom_substitute_components where COMPONENT_SEQUENCE_ID in(
3081: -- Get all the components for this item
3082: select COMPONENT_SEQUENCE_ID from bom_components_b WHERE bill_sequence_id IN
3083: (
3084: select bill_sequence_id from bom_bill_of_materials where
3085: ASSEMBLY_ITEM_ID = item.revised_item_id AND
3086: ORGANIZATION_ID = item.organization_id AND
3087: (
3088: (item.designator_selection_type = 1) --select everything
3102: select SUBSTITUTE_COMPONENT_ID from bom_substitute_components where COMPONENT_SEQUENCE_ID in(
3103: -- Get all the components for this item
3104: select COMPONENT_SEQUENCE_ID from bom_components_b WHERE bill_sequence_id IN
3105: (
3106: select bill_sequence_id from bom_bill_of_materials where
3107: ASSEMBLY_ITEM_ID = item.revised_item_id AND
3108: ORGANIZATION_ID = item.organization_id AND
3109: (
3110: (item.designator_selection_type = 1) --select everything
3156: -- changes...
3157: SELECT Count(*) INTO l_no_components FROM dual WHERE EXISTS(
3158: select 1 from bom_components_b where bill_sequence_id in
3159: (
3160: select bill_sequence_id from bom_bill_of_materials where
3161: ASSEMBLY_ITEM_ID = item.revised_item_id AND
3162: ORGANIZATION_ID = item.organization_id
3163: ) AND revised_item_sequence_id = revised_item AND acd_type <> acd_delete
3164: );
3222: select 1 from mtl_system_items_b where inventory_item_id in
3223: (
3224: select COMPONENT_ITEM_ID from bom_components_b WHERE bill_sequence_id IN
3225: (
3226: select bill_sequence_id from bom_bill_of_materials where
3227: ASSEMBLY_ITEM_ID = item.revised_item_id AND
3228: ORGANIZATION_ID = item.organization_id
3229: )
3230: AND revised_item_sequence_id = revised_item AND acd_type <> acd_delete
3256: select SUBSTITUTE_COMPONENT_ID from bom_substitute_components where COMPONENT_SEQUENCE_ID in(
3257: -- Get all the components for this item
3258: select COMPONENT_SEQUENCE_ID from bom_components_b WHERE bill_sequence_id IN
3259: (
3260: select bill_sequence_id from bom_bill_of_materials where
3261: ASSEMBLY_ITEM_ID = item.revised_item_id AND
3262: ORGANIZATION_ID = item.organization_id
3263: ) AND revised_item_sequence_id = revised_item AND acd_type <> acd_delete
3264: ) AND acd_type <> acd_delete
5819: begin
5820:
5821: select assembly_type
5822: into l_bom_assembly_type
5823: from bom_bill_of_materials
5824: where bill_sequence_id = item.bill_sequence_id;
5825:
5826: end;
5827:
7308: end loop; -- all components
7309:
7310:
7311:
7312: Update bom_bill_of_materials
7313: set last_update_date = sysdate,
7314: last_updated_by = userid,
7315: last_update_login = loginid,
7316: request_id = reqstid,
7362: IF (l_eres_enabled = 'Y')
7363: THEN
7364: -- bug 3234628, May-2004:
7365: -- odaboval added this test in order to only raise an ERES event
7366: -- when a row in bom_bill_of_materials has been updated:
7367: -- bug 3741444 : added bERES_Flag_for_BOM in the condition
7368: -- and removed ROWCOUNT>0 from the condition.
7369: IF (bERES_Flag_for_BOM)
7370: THEN