[Home] [Help]
1064: IS
1065: SELECT
1066: bic.component_sequence_id
1067: FROM
1068: bom_inventory_components bic,
1069: mtl_system_items_kfv it
1070: WHERE
1071: bic.bill_sequence_id = p_from_sequence_id
1072: AND bic.component_item_id = it.inventory_item_id
1343: IS
1344: SELECT
1345: bic.component_sequence_id
1346: FROM
1347: bom_inventory_components bic,
1348: mtl_system_items_kfv it
1349: WHERE
1350: bic.bill_sequence_id = p_from_sequence_id
1351: AND bic.component_item_id = it.inventory_item_id
1618: SELECT
1619: msi.concatenated_segments component_item_name,
1620: bic.component_item_id,
1621: bic.operation_seq_num
1622: FROM bom_inventory_components bic,
1623: mtl_system_items_kfv msi
1624: WHERE bic.bill_sequence_id = p_from_sequence_id
1625: AND bic.component_item_id = msi.inventory_item_id
1626: AND bic.component_item_id <> p_to_item_id
1792: ,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.new_from_end_item_unit_number) new_from_end_item_unit_number
1793: ,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.new_routing_revision) new_routing_revision
1794: ,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.return_status) return_status
1795: FROM
1796: bom_inventory_components bic,
1797: mtl_system_items_kfv it
1798: WHERE
1799: bic.component_item_id = it.inventory_item_id AND
1800: it.organization_id = p_reference_org_id AND
2954: --This cursor is for redlining of Unimplemented BOM Components.
2955: -- This is added as part of Bug 10423301
2956: Cursor c_unimplemented_component(p_rev_item_id NUMBER, p_component_item_id NUMBER, p_bill_sequence_id NUMBER) IS
2957: SELECT bic.effectivity_date, bic.operation_seq_num, bic.from_end_item_unit_number
2958: FROM bom_inventory_components bic
2959: ,eng_revised_items eri
2960: WHERE eri.revised_item_id = p_rev_item_id
2961: AND eri.organization_id = p_organization_id
2962: AND eri.bill_sequence_id = p_bill_sequence_id
2964: AND bic.operation_seq_num = p_op_sequence_number
2965: AND bic.bill_sequence_id = eri.bill_sequence_id
2966: AND bic.revised_item_sequence_id = eri.revised_item_sequence_id
2967: AND eri.scheduled_date = (SELECT MAX(erj.scheduled_date)
2968: FROM bom_inventory_components bcc
2969: ,eng_revised_items erj
2970: WHERE erj.revised_item_id = p_rev_item_id
2971: AND erj.organization_id = p_organization_id
2972: AND erj.bill_sequence_id = p_bill_sequence_id
2981:
2982: --This cursor is for redlining of Implemented BOM Components.
2983: Cursor c_implemented_component(p_rev_item_id NUMBER, p_component_item_id NUMBER, p_bill_sequence_id NUMBER) IS
2984: SELECT bic.effectivity_date, bic.operation_seq_num, bic.from_end_item_unit_number
2985: FROM bom_inventory_components bic
2986: ,eng_revised_items eri
2987: WHERE eri.revised_item_id = p_rev_item_id
2988: AND eri.organization_id = p_organization_id
2989: AND eri.bill_sequence_id = p_bill_sequence_id
2991: AND bic.operation_seq_num = p_op_sequence_number
2992: AND bic.bill_sequence_id = eri.bill_sequence_id
2993: AND bic.revised_item_sequence_id = eri.revised_item_sequence_id
2994: AND eri.implementation_date = (SELECT MAX(erj.implementation_date)
2995: FROM bom_inventory_components bcc
2996: ,eng_revised_items erj
2997: WHERE erj.revised_item_id = p_rev_item_id
2998: AND erj.organization_id = p_organization_id
2999: AND erj.bill_sequence_id = p_bill_sequence_id
3011: CURSOR c_bom_components( p_component_item_id NUMBER,
3012: p_bill_sequence_id NUMBER) IS
3013:
3014: SELECT bic.effectivity_date, bic.operation_seq_num, bic.from_end_item_unit_number
3015: FROM bom_inventory_components bic
3016: WHERE bic.component_item_id = p_component_item_id
3017: AND bic.bill_sequence_id = p_bill_sequence_id
3018: AND bic.operation_seq_num = p_op_sequence_number
3019: --Start of Bug 13351524
3117: CLOSE c_unimplemented_component;
3118: END IF;-- End of Bug 10423301
3119:
3120: /*Start of Bug :8340804 If Item has BOM and no Change Order then retrieve old values from
3121: bom_inventory_components table */
3122:
3123:
3124: IF(l_eco_exist <> FND_API.G_TRUE AND p_bom_update_without_eco = FND_API.G_TRUE) THEN
3125:
3129: AND organization_id = p_organization_id;*/ -- Commented this query for Bug 9527466
3130:
3131: /*Above query was returning a row if item has Change Order associated,but we need
3132: to check if the BOM component is associated with the CO,if not query the required
3133: data from bom_inventory_components.So below query is incorporated*/
3134: INV_EBI_UTIL.debug_line('STEP 110:BOM exists without Change Order and p_bom_update_without_eco ' || p_bom_update_without_eco);
3135:
3136: --Start of Bug 9527466
3137: SELECT COUNT(1) INTO l_count
3135:
3136: --Start of Bug 9527466
3137: SELECT COUNT(1) INTO l_count
3138: FROM eng_revised_items eri,
3139: bom_inventory_components bic
3140: WHERE eri.revised_item_id = l_assembly_item_id
3141: AND eri.organization_id = p_organization_id
3142: AND eri.bill_sequence_id = l_bill_sequence_id
3143: AND bic.component_item_id = l_component_item_id
3163:
3164: --Start of Bug 13582896
3165: IF(l_bom_comp_exist_without_eco = FND_API.G_FALSE) THEN
3166: SELECT COUNT(1) INTO l_count
3167: FROM bom_inventory_components bic
3168: WHERE bic.bill_sequence_id = l_bill_sequence_id
3169: AND bic.component_item_id = l_component_item_id
3170: AND bic.operation_seq_num = p_op_sequence_number
3171: AND bic.change_notice IS NULL;
3593: SELECT
3594: msi.concatenated_segments component_item_name ,
3595: bic.component_item_id
3596: FROM
3597: bom_inventory_components bic,
3598: mtl_system_items_kfv msi
3599: WHERE
3600: bic.bill_sequence_id = p_src_bill_sequence_id AND
3601: bic.component_item_id = msi.inventory_item_id AND
3662:
3663: SELECT
3664: msi.concatenated_segments sub_comp_item_name ,
3665: bsc.substitute_component_id
3666: FROM bom_inventory_components bic,
3667: bom_substitute_components bsc,
3668: mtl_system_items_kfv msi
3669: WHERE bic.bill_sequence_id = p_src_bill_sequence_id
3670: AND bic.component_sequence_id = bsc.component_sequence_id
5212: ,bsc.original_system_reference
5213: ,bsc.enforce_int_requirements
5214: ,bsc.program_id
5215: FROM
5216: bom_inventory_components bic
5217: ,eng_revised_items eri
5218: ,mtl_item_flexfields mif
5219: ,bom_substitute_components bsc
5220: WHERE
5248: ,brd.attribute14
5249: ,brd.attribute15
5250: ,brd.original_system_reference
5251: FROM
5252: bom_inventory_components bic
5253: ,eng_revised_items eri
5254: ,bom_reference_designators brd
5255: WHERE
5256: eri.change_id = p_change_id AND
5304: ,bic.suggested_vendor_name
5305: ,bic.unit_price
5306: ,bic.original_system_reference
5307: FROM
5308: bom_inventory_components bic
5309: ,eng_revised_items eri
5310: ,mtl_item_flexfields mif
5311: WHERE
5312: eri.change_id = p_change_id AND