[Home] [Help]
89: VAR_ALTERNATE_BOM_DESIGNATOR IN VARCHAR2)
90: RETURN BOOLEAN IS
91: CURSOR check_components IS
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,
1956: p_organization_id NUMBER,
1957: p_effective_date DATE )
1958: IS
1959: SELECT 'X'
1960: FROM DUAL
1961: WHERE EXISTS (
1962: SELECT 1
1963: FROM wip_discrete_jobs wdj,
1964: wip_entities we,
1983: ( p_wip_lot_number VARCHAR2
1984: , p_effective_date DATE)
1985: IS
1986: SELECT 'X'
1987: FROM DUAL
1988: WHERE EXISTS (
1989: SELECT 1
1990: FROM wip_discrete_jobs wdj, wip_entities we
1991: WHERE wdj.lot_number = p_wip_lot_number
2143: p_operation_seq_num NUMBER,
2144: p_organization_id NUMBER )
2145: IS
2146: SELECT 'X'
2147: FROM DUAL
2148: WHERE EXISTS (
2149: SELECT 1
2150: FROM wip_discrete_jobs wdj,
2151: wip_entities we,
2171: p_operation_seq_num NUMBER
2172: )
2173: IS
2174: SELECT 'X'
2175: FROM DUAL
2176: WHERE EXISTS (
2177: SELECT 1
2178: FROM wip_discrete_jobs wdj
2179: WHERE wdj.lot_number= p_wip_lot_number
2262: p_organization_id NUMBER
2263: )
2264: IS
2265: SELECT 'X'
2266: FROM DUAL
2267: WHERE EXISTS (
2268: SELECT 1
2269: FROM wip_discrete_jobs wdj,
2270: wip_entities we,
2294: p_organization_id NUMBER
2295: )
2296: IS
2297: SELECT 'X'
2298: FROM DUAL
2299: WHERE EXISTS (
2300: SELECT 1
2301: FROM wip_discrete_jobs wdj
2302: WHERE wdj.lot_number= p_wip_lot_number
2341: p_organization_id NUMBER
2342: )
2343: IS
2344: SELECT 'X'
2345: FROM DUAL
2346: WHERE EXISTS (
2347: SELECT 1
2348: FROM wip_discrete_jobs wdj,
2349: wip_entities we,
2373: p_organization_id NUMBER
2374: )
2375: IS
2376: SELECT 'X'
2377: FROM DUAL
2378: WHERE EXISTS (
2379: SELECT 1
2380: FROM wip_discrete_jobs wdj
2381: WHERE wdj.lot_number= p_wip_lot_number
2921: l_BOMEvents_Comps_ACD NUMBER;
2922: l_BOMEvents_Bill_Event_Name VARCHAR2(240);
2923: CURSOR c_Comp_Child_Entity_Modified(cp_component_sequence_id IN NUMBER)
2924: IS
2925: SELECT 1 FROM dual
2926: WHERE EXISTS (SELECT 1 FROM bom_substitute_components
2927: WHERE component_sequence_id = cp_component_sequence_id
2928: AND acd_type IS NOT NULL)
2929: OR EXISTS (SELECT 1 FROM bom_substitute_components
2977: Fetch get_item_info into item;
2978: /* -- changed for bug 2827313
2979: Select trunc(sysdate, 'MI')
2980: into now
2981: from dual;
2982: today := trunc(now, 'DD');
2983: */
2984: now := p_now;
2985: today := now;
3015:
3016: IF(item.transfer_or_copy_bill = 1 OR item.transfer_or_copy_routing = 1) then
3017: IF(item.implemented_only = 1) THEN
3018: --if it's implemented only then check if it's already implemented or going to be implemented as a part of this change...
3019: select count(*) into l_no_bom_dis_comps from dual where exists(
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: (
3034: ) and organization_id = item.organization_id and bom_enabled_flag = 'N'
3035: );
3036: ELSE
3037: -- If implemented only is not set then check everything even other pending ECOS
3038: select count(*) into l_no_bom_dis_comps from dual where exists(
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: (
3072:
3073:
3074: --Get all the sub components for which bom_enabled_flag is false...
3075: IF(item.implemented_only = 1) THEN
3076: select count(*) into l_no_bom_dis_sub_comps from dual where exists(
3077: select 1 from mtl_system_items_b WHERE inventory_item_id IN
3078: (
3079: --Get all the sub components for all the components for this item
3080: select SUBSTITUTE_COMPONENT_ID from bom_substitute_components where COMPONENT_SEQUENCE_ID in(
3094: )
3095: ) and organization_id = item.organization_id and bom_enabled_flag = 'N'
3096: );
3097: ELSE
3098: select count(*) into l_no_bom_dis_sub_comps from dual where exists(
3099: select 1 from mtl_system_items_b WHERE inventory_item_id IN
3100: (
3101: --Get all the sub components for all the components for this item
3102: select SUBSTITUTE_COMPONENT_ID from bom_substitute_components where COMPONENT_SEQUENCE_ID in(
3153: --Note that in implementation we check only if there are any compoenents. Empty BOMs will be implemented
3154: --This is done because.. BOMs are created the first item we add a component in ECO.. but there is no way
3155: -- to delete that BOM, if the user wants to implement it by deleting the components and proceeding with other
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
3181: END IF;
3182:
3183: --Check if there are any operations for this item ..
3184: --Again, we allow empty routings to get implemented for the same reason for which we allowe empty BOMs
3185: SELECT Count(*) INTO l_no_operations FROM dual WHERE EXISTS(
3186: select 1 FROM bom_operation_sequences WHERE routing_sequence_id IN
3187: (
3188: SELECT routing_sequence_id FROM BOM_OPERATIONAL_ROUTINGS WHERE
3189: ASSEMBLY_ITEM_ID = item.revised_item_id and
3217: no_bom_disabled_comps NUMBER;
3218: no_bom_disabled_sub_comps NUMBER;
3219: BEGIN
3220: --Check if there is atleast one component such that it's bom_enabled_flag is false for this Revised Item
3221: SELECT Count(*) INTO no_bom_disabled_comps FROM dual WHERE EXISTS(
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: (
3248: END IF;
3249: END IF;
3250:
3251: --Check if there is atleast one sub component such that it's bom_enabled_flag is false for this Revised Item
3252: SELECT Count(*) INTO no_bom_disabled_sub_comps FROM dual WHERE EXISTS(
3253: select 1 from mtl_system_items_b WHERE inventory_item_id IN
3254: (
3255: --Get all the sub components for all the components for this item
3256: select SUBSTITUTE_COMPONENT_ID from bom_substitute_components where COMPONENT_SEQUENCE_ID in(
3374: l_routing_revision := r1.starting_revision;
3375: END LOOP;
3376: END IF;
3377:
3378: SELECT mtl_system_items_s.NEXTVAL INTO l_new_assembly_item_id FROM dual;
3379:
3380: SELECT concatenated_copy_segments INTO l_concatenated_copy_segments
3381: FROM eng_revised_items WHERE revised_item_sequence_id = item.revised_item_sequence_id;
3382:
4089: X_change_notice => item.change_notice,
4090: X_implementation_date => today);
4091: --Start of changes Bug 2963301
4092: Begin
4093: SELECT userenv('LANG') INTO l_language_code FROM dual;
4094: Update mtl_item_revisions_tl MIR
4095: set description =
4096: (select MIR1.description
4097: from mtl_item_revisions_tl MIR1
7924: IF l_update_wip = 1
7925: THEN
7926:
7927: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_group_id1
7928: FROM DUAL;
7929: group_id1 := l_wip_group_id1; --- set out type value
7930:
7931: --bug 2327582
7932: l_update_all_jobs := fnd_profile.value('ENG:UPDATE_UNRELEASED_WIP_JOBS');
7936: THEN
7937: IF l_wip_start_quantity1 <> 0
7938: THEN
7939: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_group_id2
7940: FROM DUAL;
7941: group_id2 := l_wip_group_id2; --- set out type value
7942: ELSE group_id2 := -1;
7943: END IF;
7944:
8149: l_wip_job_name2 := wip_name_for_job_rec.wip_entity_name;
8150: l_wip_last_u_compl_date2 := wip_name_for_job_rec.scheduled_completion_date;
8151:
8152: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_header_id
8153: FROM DUAL; --fix bug 5667398 cannot have duplicate header id from same group id
8154: /* Added for Bug2970539, Bug 3076067 */
8155:
8156: --if (l_wip_bom_revision_date2 > today) then -- 3412747
8157: if (wip_name_for_job_rec.bom_revision_date > today)
8265: l_wip_job_name2 := wip_name_for_lot_rec.wip_entity_name;
8266: l_wip_last_u_compl_date2 := wip_name_for_lot_rec.scheduled_completion_date;
8267:
8268: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_header_id
8269: FROM DUAL; --fix bug 5667398 cannot have duplicate header id from same group id
8270:
8271: /* Added for Bug2970539, Bug 3076067 */
8272:
8273: --if (l_wip_bom_revision_date2 > today) then --3412747
8385: l_wip_job_name2 := wip_name_for_common_rec.wip_entity_name;
8386: l_wip_last_u_compl_date2 := wip_name_for_common_rec.scheduled_completion_date;
8387:
8388: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_header_id
8389: FROM DUAL; --fix bug 5667398 cannot have duplicate header id from same group id
8390:
8391: /* Added for Bug2970539, Bug 3076067 */
8392:
8393: --if (l_wip_bom_revision_date2 > today) then --3412747
8765: l_organization_id IN NUMBER
8766: )
8767: IS
8768: SELECT '1'
8769: FROM DUAL
8770: WHERE NOT EXISTS
8771: (SELECT 1
8772: FROM WIP_ENTITIES
8773: WHERE organization_id = l_organization_id