[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,
2052: p_organization_id NUMBER,
2053: p_effective_date DATE )
2054: IS
2055: SELECT 'X'
2056: FROM DUAL
2057: WHERE EXISTS (
2058: SELECT 1
2059: FROM wip_discrete_jobs wdj,
2060: wip_entities we,
2079: ( p_wip_lot_number VARCHAR2
2080: , p_effective_date DATE)
2081: IS
2082: SELECT 'X'
2083: FROM DUAL
2084: WHERE EXISTS (
2085: SELECT 1
2086: FROM wip_discrete_jobs wdj, wip_entities we
2087: WHERE wdj.lot_number = p_wip_lot_number
2239: p_operation_seq_num NUMBER,
2240: p_organization_id NUMBER )
2241: IS
2242: SELECT 'X'
2243: FROM DUAL
2244: WHERE EXISTS (
2245: SELECT 1
2246: FROM wip_discrete_jobs wdj,
2247: wip_entities we,
2267: p_operation_seq_num NUMBER
2268: )
2269: IS
2270: SELECT 'X'
2271: FROM DUAL
2272: WHERE EXISTS (
2273: SELECT 1
2274: FROM wip_discrete_jobs wdj
2275: WHERE wdj.lot_number= p_wip_lot_number
2358: p_organization_id NUMBER
2359: )
2360: IS
2361: SELECT 'X'
2362: FROM DUAL
2363: WHERE EXISTS (
2364: SELECT 1
2365: FROM wip_discrete_jobs wdj,
2366: wip_entities we,
2390: p_organization_id NUMBER
2391: )
2392: IS
2393: SELECT 'X'
2394: FROM DUAL
2395: WHERE EXISTS (
2396: SELECT 1
2397: FROM wip_discrete_jobs wdj
2398: WHERE wdj.lot_number= p_wip_lot_number
2437: p_organization_id NUMBER
2438: )
2439: IS
2440: SELECT 'X'
2441: FROM DUAL
2442: WHERE EXISTS (
2443: SELECT 1
2444: FROM wip_discrete_jobs wdj,
2445: wip_entities we,
2469: p_organization_id NUMBER
2470: )
2471: IS
2472: SELECT 'X'
2473: FROM DUAL
2474: WHERE EXISTS (
2475: SELECT 1
2476: FROM wip_discrete_jobs wdj
2477: WHERE wdj.lot_number= p_wip_lot_number
3052: l_BOMEvents_Comps_ACD NUMBER;
3053: l_BOMEvents_Bill_Event_Name VARCHAR2(240);
3054: CURSOR c_Comp_Child_Entity_Modified(cp_component_sequence_id IN NUMBER)
3055: IS
3056: SELECT 1 FROM dual
3057: WHERE EXISTS (SELECT 1 FROM bom_substitute_components
3058: WHERE component_sequence_id = cp_component_sequence_id
3059: AND acd_type IS NOT NULL)
3060: OR EXISTS (SELECT 1 FROM bom_substitute_components
3109:
3110: /* -- changed for bug 2827313
3111: Select trunc(sysdate, 'MI')
3112: into now
3113: from dual;
3114: today := trunc(now, 'DD');
3115: */
3116: now := p_now;
3117: today := now;
3145: declare
3146: l_no_del_groups number;
3147: begin
3148: IF( item.transfer_or_copy_item =1 OR item.transfer_or_copy_bill = 1 OR item.transfer_or_copy_routing = 1) THEN
3149: select count(*) into l_no_del_groups from dual where exists
3150: (select 1 from bom_delete_entities where delete_status_type <> 4 and inventory_item_id = item.revised_item_id and organization_id = item.organization_id);
3151: if (l_no_del_groups > 0) then
3152: msg_qty := msg_qty + 1;
3153: message_names(msg_qty) := 'ENG_TRANSFER_PENDING_DG';
3171:
3172: IF(item.transfer_or_copy_bill = 1 OR item.transfer_or_copy_routing = 1) then
3173: IF(item.implemented_only = 1) THEN
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: (
3207: ) and organization_id = item.organization_id and bom_enabled_flag = 'N'
3208: );
3209: ELSE
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: (
3262:
3263:
3264: --Get all the sub components for which bom_enabled_flag is false...
3265: IF(item.implemented_only = 1) THEN
3266: select count(*) into l_no_bom_dis_sub_comps from dual where exists(
3267: select 1 from mtl_system_items_b WHERE inventory_item_id IN
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(
3301: )
3302: ) and organization_id = item.organization_id and bom_enabled_flag = 'N'
3303: );
3304: ELSE
3305: select count(*) into l_no_bom_dis_sub_comps from dual where exists(
3306: select 1 from mtl_system_items_b WHERE inventory_item_id IN
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(
3377: --Note that in implementation we check only if there are any compoenents. Empty BOMs will be implemented
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
3405: END IF;
3406:
3407: --Check if there are any operations for this item ..
3408: --Again, we allow empty routings to get implemented for the same reason for which we allowe empty BOMs
3409: SELECT Count(*) INTO l_no_operations FROM dual WHERE EXISTS(
3410: select 1 FROM bom_operation_sequences WHERE routing_sequence_id IN
3411: (
3412: SELECT routing_sequence_id FROM BOM_OPERATIONAL_ROUTINGS WHERE
3413: ASSEMBLY_ITEM_ID = item.revised_item_id and
3441: no_bom_disabled_comps NUMBER;
3442: no_bom_disabled_sub_comps NUMBER;
3443: BEGIN
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: (
3472: END IF;
3473: END IF;
3474:
3475: --Check if there is atleast one sub component such that it's bom_enabled_flag is false for this Revised Item
3476: SELECT Count(*) INTO no_bom_disabled_sub_comps FROM dual WHERE EXISTS(
3477: select 1 from mtl_system_items_b WHERE inventory_item_id IN
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(
3618: l_routing_revision := r1.starting_revision;
3619: END LOOP;
3620: END IF;
3621:
3622: SELECT mtl_system_items_s.NEXTVAL INTO l_new_assembly_item_id FROM dual;
3623:
3624: SELECT concatenated_copy_segments INTO l_concatenated_copy_segments
3625: FROM eng_revised_items WHERE revised_item_sequence_id = item.revised_item_sequence_id;
3626:
4347: X_change_notice => item.change_notice,
4348: X_implementation_date => today);
4349: --Start of changes Bug 2963301
4350: Begin
4351: SELECT userenv('LANG') INTO l_language_code FROM dual;
4352: Update mtl_item_revisions_tl MIR
4353: set description =
4354: (select MIR1.description
4355: from mtl_item_revisions_tl MIR1
8265: IF l_update_wip = 1
8266: THEN
8267:
8268: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_group_id1
8269: FROM DUAL;
8270: group_id1 := l_wip_group_id1; --- set out type value
8271:
8272: --bug 2327582
8273: l_update_all_jobs := fnd_profile.value('ENG:UPDATE_UNRELEASED_WIP_JOBS');
8277: THEN
8278: IF l_wip_start_quantity1 <> 0
8279: THEN
8280: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_group_id2
8281: FROM DUAL;
8282: group_id2 := l_wip_group_id2; --- set out type value
8283: ELSE group_id2 := -1;
8284: END IF;
8285:
8492: l_wip_job_name2 := wip_name_for_job_rec.wip_entity_name;
8493: l_wip_last_u_compl_date2 := wip_name_for_job_rec.scheduled_completion_date;
8494:
8495: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_header_id
8496: FROM DUAL; --fix bug 5667398 cannot have duplicate header id from same group id
8497: /* Added for Bug2970539, Bug 3076067 */
8498:
8499: --if (l_wip_bom_revision_date2 > today) then -- 3412747
8500: if (wip_name_for_job_rec.bom_revision_date > today)
8609: l_wip_job_name2 := wip_name_for_lot_rec.wip_entity_name;
8610: l_wip_last_u_compl_date2 := wip_name_for_lot_rec.scheduled_completion_date;
8611:
8612: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_header_id
8613: FROM DUAL; --fix bug 5667398 cannot have duplicate header id from same group id
8614:
8615: /* Added for Bug2970539, Bug 3076067 */
8616:
8617: --if (l_wip_bom_revision_date2 > today) then --3412747
8729: l_wip_job_name2 := wip_name_for_common_rec.wip_entity_name;
8730: l_wip_last_u_compl_date2 := wip_name_for_common_rec.scheduled_completion_date;
8731:
8732: SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_header_id
8733: FROM DUAL; --fix bug 5667398 cannot have duplicate header id from same group id
8734:
8735: /* Added for Bug2970539, Bug 3076067 */
8736:
8737: --if (l_wip_bom_revision_date2 > today) then --3412747
9110: l_organization_id IN NUMBER
9111: )
9112: IS
9113: SELECT '1'
9114: FROM DUAL
9115: WHERE NOT EXISTS
9116: (SELECT 1
9117: FROM WIP_ENTITIES
9118: WHERE organization_id = l_organization_id