[Home] [Help]
805: END IF;
806:
807: -- Check whether there are any component changes created for the item with this change order ..
808: SELECT count(*) INTO l_pending_changes_count
809: FROM bom_components_b comp, bom_bill_of_materials bom
810: WHERE revised_item_sequence_id = p_change_line_id
811: AND comp.implementation_date IS NULL
812: AND comp.bill_sequence_id = bom.bill_sequence_id
813: AND bom.structure_type_id = l_current_attr_group_id
1339: -- for the change in the soruce bill
1340: --
1341: CURSOR c_related_components IS
1342: SELECT bcb.component_sequence_id, old_component_sequence_id, bill_sequence_id
1343: FROM bom_components_b bcb
1344: WHERE bcb.change_notice = p_change_notice
1345: AND bcb.revised_item_sequence_id = p_revised_item_sequence_id
1346: AND bcb.common_component_sequence_id = p_src_comp_seq_id
1347: AND bcb.common_component_sequence_id <> bcb.component_sequence_id
1363:
1364: -- For each destination component record that will be implemented
1365: FOR c_dest_comp_rec IN c_related_components
1366: LOOP
1367: UPDATE bom_components_b bcb
1368: SET bcb.old_component_sequence_id = c_dest_comp_rec.component_sequence_id
1369: , bcb.common_component_sequence_id = p_src_comp_seq_id
1370: , bcb.last_update_date = sysdate
1371: , bcb.last_updated_by = FND_PROFILE.value('USER_ID')
1401: g_Common_Rev_Comp_Tbl(g_common_rev_comps_cnt).component_sequence_id := c_dest_comp_rec.component_sequence_id;
1402: g_Common_Rev_Comp_Tbl(g_common_rev_comps_cnt).common_component_sequence_id := p_src_comp_seq_id;
1403: g_Common_Rev_Comp_Tbl(g_common_rev_comps_cnt).prev_common_comp_sequence_id := p_src_old_comp_seq_id;
1404:
1405: UPDATE bom_components_b bcb
1406: SET bcb.common_component_sequence_id = p_src_comp_seq_id
1407: , bcb.last_update_date = sysdate
1408: , bcb.last_updated_by = FND_PROFILE.value('USER_ID')
1409: , bcb.last_update_login = FND_PROFILE.value('LOGIN_ID')
1440: x_return_status := FND_API.G_RET_STS_SUCCESS;
1441: -- For each component thats been
1442: FOR i IN 1..g_common_rev_comps_cnt
1443: LOOP
1444: UPDATE bom_components_b bcb
1445: SET bcb.common_component_sequence_id = g_Common_Rev_Comp_Tbl(i).prev_common_comp_sequence_id
1446: , bcb.last_update_date = sysdate
1447: , bcb.last_updated_by = FND_PROFILE.value('USER_ID')
1448: , bcb.last_update_login = FND_PROFILE.value('LOGIN_ID')
1500: supply_locator_id,
1501: supply_subinventory,
1502: wip_supply_type,
1503: acd_type
1504: FROM bom_components_b
1505: WHERE revised_item_sequence_id = p_revised_item_sequence_id;
1506:
1507: BEGIN
1508: FOR comp_details IN c_comps
1897: f.bom_item_type, --BOM ER 9946990
1898: f.replenish_to_order_flag, --BOM ER 9946990
1899: c.optional, --BOM ER 9946990
1900: c.component_remarks
1901: from bom_components_b c, --bom_inventory_components c,
1902: mtl_system_items_b_kfv f
1903: where c.revised_item_sequence_id = revised_item
1904: AND c.bill_sequence_id = cp_bill_sequence_id -- R12: Added for common bom changes
1905: and f.inventory_item_id = c.component_item_id
1903: where c.revised_item_sequence_id = revised_item
1904: AND c.bill_sequence_id = cp_bill_sequence_id -- R12: Added for common bom changes
1905: and f.inventory_item_id = c.component_item_id
1906: and f.organization_id = item.organization_id
1907: AND c.obj_name IS NULL -- added for bom_components_b
1908: for update of c.implementation_date,
1909: c.change_notice,
1910: c.disable_date,
1911: c.from_end_item_unit_number,
1929: Cursor check_existing_component(X_bill number, X_component number,
1930: X_operation number, X_comp_seq_id number, X_disable_date date,X_old_comp_seq_id number
1931: ,X_old_rec_disable_date date) is
1932: Select 'x' -- overlapping effectivity
1933: from bom_components_b c --bom_inventory_components c
1934: where c.bill_sequence_id = X_bill
1935: and c.component_item_id = X_component
1936: and c.operation_seq_num = X_operation
1937: and c.implementation_date is not null
1934: where c.bill_sequence_id = X_bill
1935: and c.component_item_id = X_component
1936: and c.operation_seq_num = X_operation
1937: and c.implementation_date is not null
1938: AND c.obj_name IS NULL -- added for bom_components_b
1939: /* Bug: 2307923 Date filter logic has been modified to prevent
1940: the duplicate creation of components through ECO */
1941: and ( (eff_date < c.effectivity_date
1942: and nvl(X_disable_date,c.effectivity_date + 1) > c.effectivity_date)
1958: and (c.disable_date <> c.effectivity_date or c.disable_date is null) ) ); --bug 12807712
1959: /*
1960: union
1961: select 'x' -- duplicate value on unique index
1962: from bom_components_b c --bom_inventory_components c
1963: where c.bill_sequence_id = X_bill
1964: and c.component_item_id = X_component
1965: and c.operation_seq_num = X_operation
1966: and c.effectivity_date = eff_date
1963: where c.bill_sequence_id = X_bill
1964: and c.component_item_id = X_component
1965: and c.operation_seq_num = X_operation
1966: and c.effectivity_date = eff_date
1967: AND c.obj_name IS NULL -- added for bom_components_b
1968: and c.component_sequence_id <> X_comp_seq_id; */
1969:
1970: Cursor check_existing_unit(X_bill number, X_component number,
1971: X_operation number, X_comp_seq_id number, X_from_unit_number varchar2,
1970: Cursor check_existing_unit(X_bill number, X_component number,
1971: X_operation number, X_comp_seq_id number, X_from_unit_number varchar2,
1972: X_to_unit_number varchar2) is
1973: Select 'x' -- overlapping effectivity
1974: from bom_components_b c--bom_inventory_components c
1975: where c.bill_sequence_id = X_bill
1976: and c.component_item_id = X_component
1977: and c.operation_seq_num = X_operation
1978: and c.implementation_date is not null
1975: where c.bill_sequence_id = X_bill
1976: and c.component_item_id = X_component
1977: and c.operation_seq_num = X_operation
1978: and c.implementation_date is not null
1979: AND c.obj_name IS NULL -- added for bom_components_b
1980: and c.disable_date is NULL
1981: and (X_To_Unit_Number IS NULL
1982: or (X_To_Unit_Number >= c.from_end_item_unit_number))
1983: and ((X_From_Unit_Number <= c.to_end_item_unit_number)
1995: o.overlapping_changes,
1996: o.component_sequence_id,
1997: o.from_end_item_rev_id,
1998: o.to_end_item_rev_id
1999: FROM bom_components_b o -- bom_inventory_components o
2000: where o.component_sequence_id = old_id
2001: for update of o.change_notice,
2002: o.disable_date,
2003: o.to_object_revision_id,
2553: )
2554: IS
2555:
2556: SELECT operation_seq_num
2557: FROM bom_components_b --bom_inventory_components
2558: WHERE component_sequence_id = p_old_component_sequence_id ;
2559: -- Cursor completed Bug 2722280
2560:
2561: CURSOR l_wip_name_for_job_cur
2954: Cursor check_from_to_revision ( cp_from_rev_eff DATE
2955: , cp_comp_sequence_id NUMBER)
2956: Is
2957: SELECT -1
2958: FROM bom_components_b bcb
2959: where bcb.component_sequence_id = cp_comp_sequence_id
2960: AND cp_from_rev_eff >= (SELECT mirb1.effectivity_date FROM mtl_item_revisions_b mirb1
2961: WHERE mirb1.revision_id = bcb.from_end_item_rev_id)
2962: AND (bcb.to_end_item_rev_id is null
2985: , cp_from_rev_eff DATE
2986: , cp_to_rev_eff DATE)
2987: Is
2988: SELECT 1
2989: FROM bom_components_b bcb
2990: where bcb.bill_sequence_id = cp_bill_id
2991: and bcb.component_item_id = cp_component_item_id
2992: and bcb.operation_seq_num = cp_operation_seq_num
2993: and bcb.implementation_date is not null
3030: -- effectivity date is in the future.
3031: -- For common bom the component change made are to be immediately effective
3032: CURSOR get_common_bom_eff_date( cp_bill_id IN NUMBER, cp_rev_seq_id IN NUMBER ) IS
3033: SELECT bcb.effectivity_date
3034: FROM bom_components_b bcb
3035: WHERE bcb.revised_item_sequence_id = cp_rev_seq_id
3036: AND bcb.bill_sequence_id = cp_bill_id
3037: AND EXISTS
3038: (SELECT 1
3174: --if it's implemented only then check if it's already implemented or going to be implemented as a part of this change...
3175: select count(*) into l_no_bom_dis_comps from dual where exists(
3176: select 1 from mtl_system_items_b where inventory_item_id in
3177: (
3178: select COMPONENT_ITEM_ID from bom_components_b WHERE bill_sequence_id IN
3179: (
3180: select bill_sequence_id from bom_bill_of_materials where
3181: ASSEMBLY_ITEM_ID = item.revised_item_id AND
3182: ORGANIZATION_ID = item.organization_id AND
3210: -- If implemented only is not set then check everything even other pending ECOS
3211: select count(*) into l_no_bom_dis_comps from dual where exists(
3212: select 1 from mtl_system_items_b where inventory_item_id in
3213: (
3214: select COMPONENT_ITEM_ID from bom_components_b WHERE bill_sequence_id IN
3215: (
3216: select bill_sequence_id from bom_bill_of_materials where
3217: ASSEMBLY_ITEM_ID = item.revised_item_id AND
3218: ORGANIZATION_ID = item.organization_id AND
3268: (
3269: --Get all the sub components for all the components for this item
3270: select SUBSTITUTE_COMPONENT_ID from bom_substitute_components where COMPONENT_SEQUENCE_ID in(
3271: -- Get all the components for this item
3272: select COMPONENT_SEQUENCE_ID from bom_components_b WHERE bill_sequence_id IN
3273: (
3274: select bill_sequence_id from bom_bill_of_materials where
3275: ASSEMBLY_ITEM_ID = item.revised_item_id AND
3276: ORGANIZATION_ID = item.organization_id AND
3307: (
3308: --Get all the sub components for all the components for this item
3309: select SUBSTITUTE_COMPONENT_ID from bom_substitute_components where COMPONENT_SEQUENCE_ID in(
3310: -- Get all the components for this item
3311: select COMPONENT_SEQUENCE_ID from bom_components_b WHERE bill_sequence_id IN
3312: (
3313: select bill_sequence_id from bom_bill_of_materials where
3314: ASSEMBLY_ITEM_ID = item.revised_item_id AND
3315: ORGANIZATION_ID = item.organization_id AND
3378: --This is done because.. BOMs are created the first item we add a component in ECO.. but there is no way
3379: -- to delete that BOM, if the user wants to implement it by deleting the components and proceeding with other
3380: -- changes...
3381: SELECT Count(*) INTO l_no_components FROM dual WHERE EXISTS(
3382: select 1 from bom_components_b where bill_sequence_id in
3383: (
3384: select bill_sequence_id from bom_bill_of_materials where
3385: ASSEMBLY_ITEM_ID = item.revised_item_id AND
3386: ORGANIZATION_ID = item.organization_id
3444: --Check if there is atleast one component such that it's bom_enabled_flag is false for this Revised Item
3445: SELECT Count(*) INTO no_bom_disabled_comps FROM dual WHERE EXISTS(
3446: select 1 from mtl_system_items_b where inventory_item_id in
3447: (
3448: select COMPONENT_ITEM_ID from bom_components_b WHERE bill_sequence_id IN
3449: (
3450: select bill_sequence_id from bom_bill_of_materials where
3451: ASSEMBLY_ITEM_ID = item.revised_item_id AND
3452: ORGANIZATION_ID = item.organization_id
3478: (
3479: --Get all the sub components for all the components for this item
3480: select SUBSTITUTE_COMPONENT_ID from bom_substitute_components where COMPONENT_SEQUENCE_ID in(
3481: -- Get all the components for this item
3482: select COMPONENT_SEQUENCE_ID from bom_components_b WHERE bill_sequence_id IN
3483: (
3484: select bill_sequence_id from bom_bill_of_materials where
3485: ASSEMBLY_ITEM_ID = item.revised_item_id AND
3486: ORGANIZATION_ID = item.organization_id
6668: ELSE*/
6669: old_comp_rec.disable_date := eff_date;
6670: --END IF;
6671:
6672: Update bom_components_b --bom_inventory_components
6673: set disable_date = old_comp_rec.disable_date,
6674: to_object_revision_id = old_comp_rec.to_object_revision_id,
6675: overlapping_changes = old_comp_rec.overlapping_changes,
6676: change_notice = old_comp_rec.change_notice,
6707: Raise abort_implementation;
6708: end if;
6709: end if;
6710:
6711: Update bom_components_b --bom_inventory_components
6712: set to_end_item_unit_number = X_prev_unit_number,
6713: to_object_revision_id = old_comp_rec.to_object_revision_id,
6714: overlapping_changes = old_comp_rec.overlapping_changes,
6715: change_notice = old_comp_rec.change_notice,
6741: ELSE*/
6742: old_comp_rec.disable_date := eff_date;
6743: --END IF;
6744:
6745: Update bom_components_b --bom_inventory_components
6746: set disable_date = old_comp_rec.disable_date,
6747: to_object_revision_id = old_comp_rec.to_object_revision_id,
6748: overlapping_changes = old_comp_rec.overlapping_changes,
6749: change_notice = old_comp_rec.change_notice,
6790: Raise abort_implementation;
6791: end if;
6792: end if;
6793:
6794: Update bom_components_b --bom_inventory_components
6795: set to_end_item_rev_id = l_prev_end_item_rev_id,
6796: to_object_revision_id = old_comp_rec.to_object_revision_id,
6797: overlapping_changes = old_comp_rec.overlapping_changes,
6798: change_notice = old_comp_rec.change_notice,
6827: END IF;*/
6828: old_comp_rec.disable_date := eff_date; --bug 5622459
6829: l_old_disable_date := old_comp_rec.disable_date;
6830: -- Date effectivity implementation
6831: Update bom_components_b --bom_inventory_components
6832: set disable_date = old_comp_rec.disable_date,
6833: to_object_revision_id = old_comp_rec.to_object_revision_id,
6834: overlapping_changes = old_comp_rec.overlapping_changes,
6835: change_notice = old_comp_rec.change_notice,
7315: component.overlapping_changes := null;
7316: END IF;
7317: end if;
7318:
7319: Update bom_components_b--bom_inventory_components
7320: set implementation_date = today,
7321: change_notice = item.change_notice,
7322: disable_date = component.disable_date,
7323: effectivity_date = eff_date,
7340: -- Not done as any UI updates will maintain the attrs in sync
7341: -----------------------------------------------------------
7342: IF isCommonedBOM = 'Y'
7343: THEN
7344: UPDATE bom_components_b--bom_inventory_components
7345: SET implementation_date = today,
7346: change_notice = item.change_notice,
7347: disable_date = component.disable_date,
7348: effectivity_date = eff_date,
7414: --
7415:
7416: If component.acd_type = acd_change then
7417: IF item.eco_for_production = 2 Then
7418: Update bom_components_b--bom_inventory_components
7419: set old_component_sequence_id = component.component_sequence_id,
7420: last_update_date = sysdate,
7421: last_updated_by = userid,
7422: last_update_login = loginid,
7577: TO_END_ITEM_MINOR_REV_ID,
7578: COMPONENT_ITEM_REVISION_ID,
7579: COMMON_COMPONENT_SEQUENCE_ID,
7580: BASIS_TYPE
7581: from bom_components_b --bom_inventory_components
7582: where component_sequence_id = component.component_sequence_id;
7583: If component.acd_type = acd_delete then
7584: Delete from bom_components_b --bom_inventory_components
7585: -- where current of chng_component_rows;
7580: BASIS_TYPE
7581: from bom_components_b --bom_inventory_components
7582: where component_sequence_id = component.component_sequence_id;
7583: If component.acd_type = acd_delete then
7584: Delete from bom_components_b --bom_inventory_components
7585: -- where current of chng_component_rows;
7586: where component_sequence_id = component.component_sequence_id;
7587:
7588: -----------------------------------------------------------
7595: -- revised_item_sequence_id of the commoned component.
7596: -----------------------------------------------------------
7597: IF isCommonedBOM = 'Y'
7598: THEN
7599: DELETE FROM bom_components_b bcb
7600: WHERE bcb.common_component_sequence_id = component.component_sequence_id
7601: AND bcb.common_component_sequence_id <> bcb.component_sequence_id
7602: --AND bcb.implementation_date IS null --bug 11731038
7603: AND bcb.change_notice = item.change_notice
8975: -- make the old conponents active again.
8976: FOR i in 1..rev_comp_disable_date_tbl.count
8977: LOOP
8978:
8979: UPDATE bom_components_b--bom_inventory_components
8980: SET
8981: disable_date = rev_comp_disable_date_tbl(i).disable_date,
8982: last_update_date = sysdate,
8983: last_updated_by = userid,
8999: Reset_Common_Comp_Details(
9000: x_Mesg_Token_Tbl => l_comn_Mesg_Token_Tbl
9001: , x_return_status => l_comn_return_status);
9002: -- For copied pending destination components
9003: DELETE FROM bom_components_b
9004: WHERE implementation_date IS NULL -- as pending changes were copied to the new component
9005: AND (bill_sequence_id, old_component_sequence_id) IN
9006: (SELECT bsb.bill_sequence_id, rbcb.component_sequence_id
9007: FROM bom_components_b rbcb, bom_structures_b bsb
9003: DELETE FROM bom_components_b
9004: WHERE implementation_date IS NULL -- as pending changes were copied to the new component
9005: AND (bill_sequence_id, old_component_sequence_id) IN
9006: (SELECT bsb.bill_sequence_id, rbcb.component_sequence_id
9007: FROM bom_components_b rbcb, bom_structures_b bsb
9008: WHERE bsb.bill_sequence_id <> p_bill_sequence_id
9009: AND bsb.source_bill_sequence_id = p_bill_sequence_id
9010: AND rbcb.bill_sequence_id = bsb.bill_sequence_id
9011: AND rbcb.revised_item_sequence_id = p_revised_item_sequence_id);
9014: -- R12: End Step 2.1: Changes for Common BOM Enhancement --
9015: -----------------------------------------------------------
9016: -- Delete the related bom_inventory_components whose
9017: -- eco_for_production = 1.
9018: DELETE FROM bom_components_b--bom_inventory_components
9019: WHERE revised_item_sequence_id = p_revised_item_sequence_id ;
9020:
9021: -- Delete the related eng revised _components whose
9022: -- eco_for_production = 1.