[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
acd_delete constant number(1) := 3;
* Parameters IN : L_NEW_ASSEMBLY_ITEM_ID, VAR_ORGANIZATION_ID, VAR_SELECTION_OPTION, VAR_ALTERNATE_BOM_DESIGNATOR
* Parameters OUT: None
* Returns : Boolean 'TRUE' or 'FALSE' depending on whether bill contains any un-approved items.
* Purpose : For an Engineering BOM to transfer to Manufacturing, the BOM should not contain any un-approved items.
* This API will find if the BOM contains any un-approved items.
*********************************************************************/
FUNCTION UNAPPROVED_COMPONENTS_EXISTS (L_NEW_ASSEMBLY_ITEM_ID IN NUMBER,
VAR_ORGANIZATION_ID IN NUMBER,
VAR_SELECTION_OPTION IN NUMBER,
VAR_ALTERNATE_BOM_DESIGNATOR IN VARCHAR2)
RETURN BOOLEAN IS
CURSOR check_components IS
SELECT count(1)
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM bom_bill_of_materials bbom,
bom_inventory_components bic,
mtl_system_items msi
WHERE bbom.assembly_item_id = L_NEW_ASSEMBLY_ITEM_ID
AND bbom.organization_id = VAR_ORGANIZATION_ID
AND ((VAR_SELECTION_OPTION = 2 AND
bBOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
OR (VAR_SELECTION_OPTION = 3 AND
bBOM.ALTERNATE_BOM_DESIGNATOR = VAR_ALTERNATE_BOM_DESIGNATOR)
OR (VAR_SELECTION_OPTION = 1))
AND bic.bill_Sequence_id = bbom.bill_sequence_id
AND msi.inventory_item_id = bic.component_item_id
AND msi.organization_id = VAR_ORGANIZATION_ID
AND nvl(msi.approval_status,'A') <> 'A');
SELECT 1
FROM fnd_concurrent_requests
WHERE request_id = p_request_id
AND ARGUMENT4 IS NOT NULL
AND ARGUMENT5 IS NULL;
SELECT 1
FROM eng_lifecycle_statuses els
WHERE els.entity_id1 = p_change_id
AND els.entity_name = 'ENG_CHANGE'
AND els.status_code = p_curr_status_code
AND els.active_flag = 'Y'
AND ( 1 = l_eco_impl_mode
OR ( 1 = ( SELECT count(*)
FROM eng_lifecycle_statuses els_in
WHERE els_in.entity_id1 = p_change_id
AND els_in.entity_name = 'ENG_CHANGE'
AND els_in.active_flag = 'Y'
AND els_in.sequence_number > els.sequence_number)
AND (( els.change_wf_route_id IS NOT NULL
AND ( els.workflow_status = 'APPROVED'
OR els.workflow_status = 'COMPLETED')
)
OR ( els.change_wf_route_id IS NULL
AND els.CHANGE_WF_ROUTE_TEMPLATE_ID IS NULL)
)
)
);
SELECT count(1)
INTO l_mandatory_task_count
FROM eng_change_lines l,
eng_change_statuses s
WHERE l.change_id = p_change_id
AND s.status_code = l.status_code
AND l.complete_before_status_code IS NOT NULL
AND s.status_type NOT IN (5, 6, 11)
AND nvl(l.required_flag , 'Y') = 'Y';
SELECT attr_group_id, attr_group_name FROM ego_attr_groups_v WHERE attr_group_type='EGO_ITEM_GTIN_ATTRS' AND application_id=431;
SELECT count(*) INTO l_single_changes_count FROM ego_gtn_attr_chg_vl WHERE change_line_id = p_change_line_id AND implementation_date IS NULL;
SELECT count(*) INTO l_multi_changes_count FROM ego_gtn_mul_attr_chg_vl WHERE change_line_id = p_change_line_id AND implementation_date IS NULL;
SELECT revised_item_id, organization_id
INTO l_inventory_item_id, l_org_id
FROM eng_revised_items
WHERE revised_item_sequence_id = p_change_line_id;
SELECT * INTO l_single_gdsn_item_pend_rec FROM ego_gtn_attr_chg_vl WHERE change_line_id = p_change_line_id;
SELECT * INTO l_single_gdsn_item_prod_rec FROM ego_item_gtn_attrs_vl WHERE inventory_item_id = l_inventory_item_id AND organization_id = l_org_id;
SELECT pv.policy_char_value, r.attribute_number_value, ra.attribute_code
FROM eng_change_policies p, eng_change_policy_values pv, eng_change_rules r, eng_change_rule_attributes_vl ra
WHERE p.policy_object_pk1_value = l_item_catalog_cat_id
and p.policy_object_pk2_value = l_item_lifecycle_id
and p.policy_object_pk3_value = l_item_current_phase_id
AND pv.POLICY_CHAR_VALUE='NOT_ALLOWED'
and pv.change_rule_id = r.change_rule_id
and r.attribute_object_name = ra.attribute_object_name
and r.attribute_code = ra.attribute_code
AND p.change_policy_id = pv.change_policy_id
AND p.policy_object_name = 'CATALOG_LIFECYCLE_PHASE'
and p.policy_code= 'CHANGE_POLICY'
and ra.attribute_object_name = 'EGO_CATALOG_GROUP';
SELECT revised_item_id, organization_id
INTO l_inventory_item_id, l_org_id
FROM eng_revised_items
WHERE revised_item_sequence_id = p_change_line_id;
SELECT item_catalog_group_id, lifecycle_id, current_phase_id , concatenated_segments
INTO l_item_catalog_cat_id, l_item_lifecycle_id, l_item_current_phase_id, l_concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_org_id;
SELECT attr_group_type INTO l_attribute_group_type FROM ego_attr_groups_v WHERE attr_group_id = l_current_attr_group_id;
SELECT count(*) INTO l_pending_changes_count
FROM ego_items_attrs_changes_b
WHERE change_line_id = p_change_line_id
AND attr_group_id = l_current_attr_group_id
AND data_level_id not in (43103,43104,43105); -- Bug 6439100 Supplier datalevels change policy not supported
SELECT count(*) INTO l_pending_changes_count
FROM ego_gtn_mul_attr_chg_vl
WHERE change_line_id = p_change_line_id
AND attr_group_id = l_current_attr_group_id
AND implementation_date IS NULL;
SELECT count(*) INTO l_pending_changes_count
FROM mtl_item_revisions
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_org_id
AND revised_item_sequence_id = p_change_line_id
AND change_notice = ( SELECT change_notice FROM eng_engineering_changes
WHERE change_id in (SELECT change_id FROM eng_revised_items
WHERE revised_item_sequence_id = p_change_line_id))
AND implementation_date IS NULL;
SELECT count(*) INTO l_pending_changes_count
FROM ego_mfg_part_num_chgs
WHERE change_line_id = p_change_line_id
AND implmentation_date IS NULL;
SELECT count(*) INTO l_pending_changes_count
FROM eng_relationship_changes
WHERE entity_id = p_change_line_id;
SELECT count(*) INTO l_pending_changes_count
FROM bom_bill_of_materials
WHERE assembly_item_id = l_inventory_item_id
AND organization_id = l_org_id
AND structure_type_id = l_current_attr_group_id
AND pending_from_ecn = ( SELECT change_notice FROM eng_engineering_changes
WHERE change_id in (SELECT change_id FROM eng_revised_items
WHERE revised_item_sequence_id = p_change_line_id));
SELECT count(*) INTO l_pending_changes_count
FROM bom_components_b comp, bom_bill_of_materials bom
WHERE revised_item_sequence_id = p_change_line_id
AND comp.implementation_date IS NULL
AND comp.bill_sequence_id = bom.bill_sequence_id
AND bom.structure_type_id = l_current_attr_group_id
AND comp.change_notice = ( SELECT change_notice FROM eng_engineering_changes
WHERE change_id in (SELECT change_id FROM eng_revised_items
WHERE revised_item_sequence_id = p_change_line_id));
SELECT count(st.structure_type_id) INTO l_pending_changes_count
FROM bom_operation_sequences op, eng_revised_items ri , bom_structure_types_vl st
WHERE op.revised_item_sequence_id = p_change_line_id
AND op.revised_item_sequence_id = ri.revised_item_sequence_id
AND op.implementation_date IS NULL
AND st.structure_type_id = ( SELECT bbom.structure_type_id
FROM bom_bill_of_materials bbom
WHERE bbom.bill_sequence_id = ri.bill_sequence_id )
AND st.structure_type_id = l_current_attr_group_id
AND op.change_notice = ( SELECT change_notice FROM eng_engineering_changes
WHERE change_id in (SELECT change_id FROM eng_revised_items
WHERE revised_item_sequence_id = p_change_line_id));
SELECT count(*) INTO l_pending_changes_count
FROM eng_attachment_changes
WHERE revised_item_sequence_id = p_change_line_id
AND category_id = l_current_attr_group_id; -- ?
* Purpose : Used to update the lifecycle states of the header
* and create a log in header Action Log if implementation fails.
* In case of revised item implementation failure, updates the revised
* item status_type
*********************************************************************/
PROCEDURE LOG_IMPLEMENT_FAILURE(p_change_id IN NUMBER
, p_revised_item_seq_id IN NUMBER) IS
l_plsql_block VARCHAR2(1000);
SELECT ecs.status_type
FROM eng_change_statuses ecs, eng_revised_items eri
WHERE ecs.status_code = eri.status_code
AND eri.revised_item_sequence_id = p_revised_item_seq_id;
SELECT nvl(plm_or_erp_change, 'PLM') plm_or_erp
FROM eng_engineering_changes
WHERE change_id = p_change_id;
l_plsql_block := 'begin ENG_CHANGE_LIFECYCLE_UTIL.Update_Lifecycle_States('
|| 'p_api_version => 1.0 '
|| ',p_change_id => :1 ' -- l_change_id
|| ',p_status_code => 10 '
|| ',p_api_caller => :2 ' -- 'CP'
|| ',p_wf_route_id => NULL '
|| ',p_route_status => NULL '
|| ',x_return_status => :3 ' -- l_return_status
|| ',x_msg_count => :4 ' -- l_msg_count
|| ',x_msg_data => :5 );' -- l_msg_data
l_plsql_block := 'begin ENG_CHANGE_LIFECYCLE_UTIL.Update_RevItem_Lifecycle('
|| 'p_api_version => 1.0 '
|| ',p_rev_item_seq_id => :1 ' -- p_revised_item_seq_id
|| ',p_status_type => 10 '
|| ',p_api_caller => :2 ' -- 'CP'
|| ',x_return_status => :3 ' -- l_return_status
|| ',x_msg_count => :4 ' -- l_msg_count
|| ',x_msg_data => :5 );' -- l_msg_data
l_plsql_block := 'begin ENG_CHANGE_LIFECYCLE_UTIL.Update_RevItem_Lifecycle('
|| 'p_api_version => 1.0 '
|| ',p_rev_item_seq_id => :1 ' -- p_revised_item_seq_id
|| ',p_status_type => :2 '
|| ',p_api_caller => :3 ' -- 'CP'
|| ',x_return_status => :4 ' -- l_return_status
|| ',x_msg_count => :5 ' -- l_msg_count
|| ',x_msg_data => :6 );' -- l_msg_data
SELECT parent_event_name
, parent_event_key
, parent_erecord_id
, event_name
, event_key
, erecord_id
, event_status
FROM eng_parent_child_events_temp;
DELETE FROM eng_parent_child_events_temp
WHERE erecord_id = my_events.erecord_id
AND parent_erecord_id = my_events.parent_erecord_id;
select transaction_quantity into xdummy
from mtl_onhand_quantities
where inventory_item_id = p_inventory_item_id
and organization_id = p_org_id
and transaction_quantity > 0;
l_sql_stmt := ' SELECT eip.project_id, ppa.name '
|| 'FROM EGO_ITEM_PROJECTS eip, PA_PROJECTS_ALL ppa '
|| 'WHERE eip.project_id = ppa.project_id '
|| 'AND eip.INVENTORY_ITEM_ID = :1 '
|| 'AND eip.ORGANIZATION_ID = :2 '
|| 'AND eip.REVISION IS NULL '
|| 'AND eip.ASSOCIATION_TYPE = :3 '
|| 'AND eip.ASSOCIATION_CODE = :4 '
|| 'AND ROWNUM = 1 ';
l_sql_stmt := ' SELECT name '
|| 'FROM PA_EGO_PHASES_V '
|| 'WHERE PROJ_ELEMENT_ID = :1; ';
l_plsql_block := 'begin PA_PROJ_TASK_STRUC_PUB.Update_Current_Phase '
|| ' ( p_project_id => :1 '
|| ' , p_project_name => :2 '
|| ' , p_current_lifecycle_phase_id => :3 '
|| ' , p_current_lifecycle_phase => :4 '
|| ' , x_return_status => :5 '
|| ' , x_msg_count => :6 '
|| ' , x_msg_data => :7 '
|| ' ); '
l_sql_stmt := ' SELECT lifecycle_id '
|| ' FROM mtl_system_items_b '
|| ' WHERE inventory_item_id = :1 '
|| ' AND organization_id = :2 ' ;
SELECT bcb.component_sequence_id, old_component_sequence_id, bill_sequence_id
FROM bom_components_b bcb
WHERE bcb.change_notice = p_change_notice
AND bcb.revised_item_sequence_id = p_revised_item_sequence_id
AND bcb.common_component_sequence_id = p_src_comp_seq_id
AND bcb.common_component_sequence_id <> bcb.component_sequence_id
AND bcb.implementation_date IS NULL;
UPDATE bom_components_b bcb
SET bcb.old_component_sequence_id = c_dest_comp_rec.component_sequence_id
, bcb.common_component_sequence_id = p_src_comp_seq_id
, bcb.last_update_date = sysdate
, bcb.last_updated_by = FND_PROFILE.value('USER_ID')
, bcb.last_update_login = FND_PROFILE.value('LOGIN_ID')
, bcb.request_id = FND_PROFILE.value('REQUEST_ID')
, bcb.program_application_id = FND_PROFILE.value('RESP_APPL_ID')
, bcb.program_id = FND_PROFILE.value('PROGRAM_ID')
, bcb.program_update_date = sysdate
WHERE bcb.old_component_sequence_id = c_dest_comp_rec.old_component_sequence_id
AND bcb.bill_sequence_id = c_dest_comp_rec.bill_sequence_id
AND bcb.common_component_sequence_id = p_src_old_comp_seq_id
AND bcb.implementation_date IS NULL
-- The following exists clause is to ensure that the pending component is not a source
-- referenced component but the one actually created for the destination bill itself
AND EXISTS (SELECT 1 FROM eng_revised_items eri
WHERE eri.revised_item_sequence_id = bcb.revised_item_sequence_id
AND eri.change_notice= bcb.change_notice
AND eri.bill_sequence_id = bcb.bill_sequence_id);
UPDATE bom_components_b bcb
SET bcb.common_component_sequence_id = p_src_comp_seq_id
, bcb.last_update_date = sysdate
, bcb.last_updated_by = FND_PROFILE.value('USER_ID')
, bcb.last_update_login = FND_PROFILE.value('LOGIN_ID')
, bcb.request_id = FND_PROFILE.value('REQUEST_ID')
, bcb.program_application_id = FND_PROFILE.value('RESP_APPL_ID')
, bcb.program_id = FND_PROFILE.value('PROGRAM_ID')
, bcb.program_update_date = sysdate
WHERE bcb.old_component_sequence_id = c_dest_comp_rec.old_component_sequence_id
AND bcb.bill_sequence_id = c_dest_comp_rec.bill_sequence_id
AND bcb.common_component_sequence_id = p_src_old_comp_seq_id
AND bcb.implementation_date IS NULL
-- The following exists clause is to ensure that the pending component is not a source
-- referenced component but the one actually created for the destination bill itself
AND EXISTS (SELECT 1 FROM eng_revised_items eri
WHERE eri.revised_item_sequence_id = bcb.revised_item_sequence_id
AND eri.change_notice= bcb.change_notice
AND eri.bill_sequence_id = bcb.bill_sequence_id);
UPDATE bom_components_b bcb
SET bcb.common_component_sequence_id = g_Common_Rev_Comp_Tbl(i).prev_common_comp_sequence_id
, bcb.last_update_date = sysdate
, bcb.last_updated_by = FND_PROFILE.value('USER_ID')
, bcb.last_update_login = FND_PROFILE.value('LOGIN_ID')
, bcb.request_id = FND_PROFILE.value('REQUEST_ID')
, bcb.program_application_id = FND_PROFILE.value('RESP_APPL_ID')
, bcb.program_id = FND_PROFILE.value('PROGRAM_ID')
, bcb.program_update_date = sysdate
WHERE bcb.old_component_sequence_id = g_Common_Rev_Comp_Tbl(i).dest_old_comp_sequence_id
AND bcb.bill_sequence_id = g_Common_Rev_Comp_Tbl(i).dest_bill_sequence_id
AND bcb.common_component_sequence_id = g_Common_Rev_Comp_Tbl(i).common_component_sequence_id
AND bcb.implementation_date IS NULL
-- The following exists clause is to ensure that the pending component is not a source
-- referenced component but the one actually created for the destination bill itself
AND EXISTS (SELECT 1 FROM eng_revised_items eri
WHERE eri.revised_item_sequence_id = bcb.revised_item_sequence_id
AND eri.change_notice= bcb.change_notice
AND eri.bill_sequence_id = bcb.bill_sequence_id);
BOMPCMBM.Update_Related_Components(
p_src_comp_seq_id => g_Common_Rev_Comp_Tbl(i).prev_common_comp_sequence_id
, x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
, x_Return_Status => x_return_status);
SELECT component_item_id,
operation_seq_num,
component_quantity,
supply_locator_id,
supply_subinventory,
wip_supply_type,
acd_type
FROM bom_components_b
WHERE revised_item_sequence_id = p_revised_item_sequence_id;
INSERT INTO WIP_JOB_DTLS_INTERFACE
(created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
group_id,
load_type,
parent_header_id,
process_phase,
process_status,
substitution_type,
inventory_item_id_new,
inventory_item_id_old,
operation_seq_num,
quantity_per_assembly,
supply_locator_id,
supply_subinventory,
wip_supply_type,
mrp_net_flag)
VALUES
(p_user_id,
sysdate,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_program_id,
p_program_application_id,
sysdate,
p_group_id,
2, -- Load Type is 2 for components
p_parent_header_id,
2, -- process_phase 2 for validation
1, -- process_status 1 for pending
decode(comp_details.acd_type, 1, 2,
2, 3,
1), -- substitution_type 1->Delete, 2->Add, 3->Change
decode(comp_details.acd_type, 1,
comp_details.component_item_id, -- inventory_item_id_new populated only for component add
null),
decode(comp_details.acd_type, 1, -- inventory_item_id_old populated only for component change/delete
null,
comp_details.component_item_id),
comp_details.operation_seq_num,
comp_details.component_quantity,
comp_details.supply_locator_id,
comp_details.supply_subinventory,
comp_details.wip_supply_type,
p_mrp_active);
update_wip OUT NOCOPY eng_revised_items.update_wip%type ,
group_id1 OUT NOCOPY wip_job_schedule_interface.group_id%type,
group_id2 OUT NOCOPY wip_job_schedule_interface.group_id%type,
wip_job_name1 OUT NOCOPY wip_entities.wip_entity_name%type,
wip_job_name2 OUT NOCOPY wip_entities.wip_entity_name%type,
wip_job_name2_org_id OUT NOCOPY wip_entities.organization_id%type,
message_names OUT NOCOPY NameArray,
token1 OUT NOCOPY NameArray,
value1 OUT NOCOPY StringArray,
translate1 OUT NOCOPY BooleanArray,
token2 OUT NOCOPY NameArray,
value2 OUT NOCOPY StringArray,
translate2 OUT NOCOPY BooleanArray,
msg_qty in OUT NOCOPY binary_integer,
warnings in OUT NOCOPY number,
p_is_lifecycle_phase_change IN NUMBER,
p_now IN DATE,
p_status_code IN NUMBER)
IS
-- ERES change begins
bERES_Flag_for_BOM BOOLEAN := FALSE; -- bug 3741444.
SELECT pending_from_ecn
, alternate_bom_designator
FROM bom_bill_of_materials
WHERE bill_sequence_id = bill_id;
SELECT pending_from_ecn
, alternate_routing_designator
FROM bom_operational_routings
WHERE routing_sequence_id = routing_id ;
l_update_all_jobs NUMBER := nvl(fnd_profile.value('ENG:UPDATE_UNRELEASED_WIP_JOBS'),2);
Select i.change_notice,
i.change_id, -- ERES change
i.organization_id,
mp1.organization_code, -- ERES change
hou.name organization_name, -- ERES change
i.revised_item_id,
si.concatenated_segments, -- ERES change
si.description, -- ERES change
si.bom_enabled_flag, -- Bug 5846248
i.new_item_revision,
i.bill_sequence_id,
i.update_wip,
si.pick_components_flag,
i.revised_item_sequence_id,
i.scheduled_date,
si.inventory_item_status_code,
si.eng_item_flag,
i.mrp_active,
i.from_wip_entity_id,
i.to_wip_entity_id,
i.from_cum_qty,
i.lot_number,
i.new_routing_revision,
i.routing_sequence_id,
i.cfm_routing_flag,
i.completion_locator_id ,
i.completion_subinventory,
i.mixed_model_map_flag,
i.eco_for_production,
i.ctp_flag,
i.priority,
i.routing_comment,
i.designator_selection_type,
i.alternate_bom_designator,
i.transfer_or_copy,
i.transfer_or_copy_item,
i.transfer_or_copy_bill,
i.transfer_or_copy_routing,
i.copy_to_item,
i.copy_to_item_desc,
i.implemented_only,
i.selection_option,
i.selection_date,
i.selection_unit_number, -- ERES change
i.last_update_date, -- ERES change
i.last_updated_by, -- ERES change
i.creation_date, -- ERES change
i.created_by , -- ERES change
i.new_item_revision_id,
i.current_item_revision_id ,
i.new_lifecycle_state_id,
i.use_up_item_id ,
i.disposition_type,
i.new_structure_revision,
i.current_lifecycle_state_id,
i.enable_item_in_local_org,
i.from_end_item_id,
i.from_end_item_rev_id
from eng_revised_items i,
mtl_system_items_vl si, -- ERES change
mtl_parameters mp1, -- ERES change
hr_all_organization_units_tl hou -- ERES change
where i.revised_item_sequence_id = revised_item
and si.inventory_item_id = i.revised_item_id
and si.organization_id = i.organization_id
AND hou.organization_id = i.organization_id
AND hou.language(+) = USERENV('LANG')
AND mp1.organization_id = i.organization_id
for update of i.implementation_date,
i.status_type,
i.last_update_date,
i.last_updated_by,
i.last_update_login,
i.request_id,
i.program_application_id,
i.program_id,
i.program_update_date,
i.status_code;
Select 'x'
from bom_parameters
where organization_id = item.organization_id
and bom_delete_status_code = item.inventory_item_status_code;
Select 'x'
from eng_revised_items
where organization_id = item.organization_id
and change_notice = item.change_notice
and status_type not in
(cancelled_status, implemented_status);
Select eri.new_item_revision
from eng_engineering_changes eec,
eng_revised_items eri
where eec.change_notice = eri.change_notice
and eec.organization_id = eri.organization_id
and eri.organization_id = item.organization_id
and eri.revised_item_id = item.revised_item_id
and eec.status_type not in
(cancelled_status, implemented_status)
and eri.status_type not in
(cancelled_status, implemented_status)
and nlssort(eri.new_item_revision) <
nlssort(item.new_item_revision);
Select r.revision, r.effectivity_date
from mtl_item_revisions r
where r.inventory_item_id = item.revised_item_id
and r.organization_id = item.organization_id
and r.effectivity_date = (
select max(cr.effectivity_date)
from mtl_item_revisions cr
where cr.inventory_item_id = item.revised_item_id
and cr.organization_id = item.organization_id
and cr.implementation_date is not null
and cr.effectivity_date <= eff_date);
Select 'x'
from mtl_item_revisions r
where r.inventory_item_id = item.revised_item_id
and r.organization_id = item.organization_id
and r.effectivity_date >= eff_date
and r.revision < item.new_item_revision
and r.implementation_date is not null;
Select b.organization_id, b.assembly_item_id,
b.bill_sequence_id
from bom_bill_of_materials b
where b.common_assembly_item_id = item.revised_item_id
and b.common_organization_id = item.organization_id
AND b.source_bill_sequence_id = item.bill_sequence_id; -- R12: Common BOM changes
Select r.revision
from mtl_item_revisions r
where r.inventory_item_id = common_assembly_item_id
and r.organization_id = common_org_id
and r.effectivity_date = (
select max(cr.effectivity_date)
from mtl_item_revisions cr
where cr.inventory_item_id = common_assembly_item_id
and cr.organization_id = common_org_id
and cr.implementation_date is not null
and cr.effectivity_date <= eff_date);
select count(*)
from mtl_item_revisions_b
where inventory_item_id = common_assembly_item_id
and organization_id = common_org_id
and revision = common_revision;
Select c.component_sequence_id,
f.concatenated_segments item_number,
c.component_item_id,
c.operation_seq_num,
c.acd_type,
c.quantity_related,
c.component_quantity,
c.old_component_sequence_id,
c.disable_date,
c.from_end_item_unit_number,
c.to_end_item_unit_number,
c.from_object_revision_id,
c.to_object_revision_id,
c.overlapping_changes,
f.eng_item_flag,
c.from_end_item_rev_id,
c.to_end_item_rev_id,
c.component_item_revision_id,
c.obj_name,
c.component_remarks
from bom_components_b c, --bom_inventory_components c,
mtl_system_items_b_kfv f
where c.revised_item_sequence_id = revised_item
AND c.bill_sequence_id = cp_bill_sequence_id -- R12: Added for common bom changes
and f.inventory_item_id = c.component_item_id
and f.organization_id = item.organization_id
AND c.obj_name IS NULL -- added for bom_components_b
for update of c.implementation_date,
c.change_notice,
c.disable_date,
c.from_end_item_unit_number,
c.to_end_item_unit_number,
c.from_object_revision_id,
c.overlapping_changes,
c.effectivity_date,
c.last_update_date,
c.last_updated_by,
c.last_update_login,
c.request_id,
c.program_application_id,
c.program_id,
c.program_update_date;
Select 'x' -- overlapping effectivity
from bom_components_b c --bom_inventory_components c
where c.bill_sequence_id = X_bill
and c.component_item_id = X_component
and c.operation_seq_num = X_operation
and c.implementation_date is not null
AND c.obj_name IS NULL -- added for bom_components_b
/* Bug: 2307923 Date filter logic has been modified to prevent
the duplicate creation of components through ECO */
and ( (eff_date < c.effectivity_date
and nvl(X_disable_date,c.effectivity_date + 1) > c.effectivity_date)
or
(eff_date < c.effectivity_date
and nvl(X_disable_date,c.effectivity_date ) <> c.effectivity_date
and nvl(X_old_rec_disable_date,c.effectivity_date) = c.effectivity_date )
or
/*Bug no:2867564 Eco is implementing and allowing duplicate item, and seqs. */
/*and eff_date <= nvl(c.disable_date,eff_date-1)*/
(eff_date > c.effectivity_date
and eff_date < nvl(c.disable_date,eff_date+1) )
or
(eff_date = c.effectivity_date
and c.component_sequence_id <> X_old_comp_seq_id
and c.disable_date <> c.effectivity_date ) );
select 'x' -- duplicate value on unique index
from bom_components_b c --bom_inventory_components c
where c.bill_sequence_id = X_bill
and c.component_item_id = X_component
and c.operation_seq_num = X_operation
and c.effectivity_date = eff_date
AND c.obj_name IS NULL -- added for bom_components_b
and c.component_sequence_id <> X_comp_seq_id; */
Select 'x' -- overlapping effectivity
from bom_components_b c--bom_inventory_components c
where c.bill_sequence_id = X_bill
and c.component_item_id = X_component
and c.operation_seq_num = X_operation
and c.implementation_date is not null
AND c.obj_name IS NULL -- added for bom_components_b
and c.disable_date is NULL
and (X_To_Unit_Number IS NULL
or (X_To_Unit_Number >= c.from_end_item_unit_number))
and ((X_From_Unit_Number <= c.to_end_item_unit_number)
or c.to_end_item_unit_number IS NULL);
select o.change_notice,
o.implementation_date,
o.disable_date,
o.effectivity_date,
o.from_end_item_unit_number,
o.to_end_item_unit_number,
o.from_object_revision_id,
o.to_object_revision_id,
o.overlapping_changes,
o.component_sequence_id,
o.from_end_item_rev_id,
o.to_end_item_rev_id
FROM bom_components_b o -- bom_inventory_components o
where o.component_sequence_id = old_id
for update of o.change_notice,
o.disable_date,
o.to_object_revision_id,
o.overlapping_changes,
o.last_update_date,
o.last_updated_by,
o.last_update_login,
o.request_id,
o.program_application_id,
o.program_id,
o.program_update_date;
Select count(*)
from bom_reference_designators r
where r.component_sequence_id = comp_id
and nvl(r.acd_type, acd_add) = acd_add;
SELECT wdj.scheduled_start_date,
wdj.scheduled_completion_date,
wdj.start_quantity,
wdj.net_quantity,
we.wip_entity_name,
wdj.bom_revision,
wdj.routing_revision,
wdj.bom_revision_date,
wdj.routing_revision_date
FROM wip_discrete_jobs wdj, wip_entities we
WHERE wdj.wip_entity_id = p_from_wip_entity_id
AND we.wip_entity_id = wdj.wip_entity_id
AND wdj.status_type = 1;
SELECT 'X'
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM wip_discrete_jobs wdj,
wip_entities we,
wip_entities we1,
wip_entities we2
WHERE we1.wip_entity_id = p_from_wip_entity_id
AND we2.wip_entity_id = p_to_wip_entity_id
AND we.wip_entity_name >= we1.wip_entity_name
AND we.wip_entity_name <= we2.wip_entity_name
AND we.organization_id = p_organization_id
AND wdj.wip_entity_id = we.wip_entity_id
AND ( wdj.status_type <> 1
OR
(
wdj.scheduled_start_date < p_effective_date
and l_update_all_jobs = 2 -- Bug 5657390
)
)
);
SELECT 'X'
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM wip_discrete_jobs wdj, wip_entities we
WHERE wdj.lot_number = p_wip_lot_number
AND wdj.wip_entity_id = we.wip_entity_id
AND wdj.primary_item_id = item.revised_item_id
AND wdj.organization_Id = item.organization_id
AND ( status_type <> 1
OR ( wdj.scheduled_start_date < p_effective_date
and l_update_all_jobs = 2 ) -- Bug 5662105
)
);
SELECT 'X'
FROM bom_operation_sequences
WHERE revised_item_sequence_id = revised_item;
SELECT change_notice
, operation_seq_num
, operation_sequence_id
, old_operation_sequence_id
, routing_sequence_id
, acd_type
, revised_item_sequence_id
, disable_date
, effectivity_date
FROM bom_operation_sequences
WHERE revised_item_sequence_id = revised_item
AND change_notice = item.change_notice
FOR UPDATE OF change_notice
, implementation_date
, old_operation_sequence_id
, acd_type
, revised_item_sequence_id
, effectivity_date
, disable_date
, last_update_date
, last_updated_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
order by operation_sequence_id;
SELECT 'X'
FROM bom_operation_sequences
WHERE operation_sequence_id = chng_operation_rec.old_operation_sequence_id
AND operation_seq_num = chng_operation_rec.operation_seq_num;
SELECT eri.new_routing_revision
FROM eng_engineering_changes eec,
eng_revised_items eri
WHERE eec.change_notice = eri.change_notice
AND eec.organization_id = eri.organization_id
AND eri.organization_id = item.organization_id
AND eri.revised_item_id = item.revised_item_id
AND eec.status_type NOT IN
( cancelled_status, implemented_status)
AND eri.status_type NOT IN
(cancelled_status, implemented_status)
AND nlssort(eri.new_routing_revision) <
nlssort(item.new_routing_revision);
SELECT 'x'
FROM mtl_rtg_item_revisions r
WHERE r.inventory_item_id = item.revised_item_id
AND r.organization_id = item.organization_id
AND r.effectivity_date > eff_date
AND r.process_revision < item.new_routing_revision --bug 3476154
AND r.implementation_date IS NOT null;
SELECT r.organization_id,
r.assembly_item_id,
r.routing_sequence_id
FROM bom_operational_routings r
WHERE r.common_assembly_item_id = item.revised_item_id
AND r.common_routing_sequence_id = item.routing_sequence_id;
Select r.process_revision
from mtl_rtg_item_revisions r
where r.inventory_item_id = common_assembly_item_id
and r.organization_id = common_org_id
and r.effectivity_date = (
select max(cr.effectivity_date)
from mtl_rtg_item_revisions cr
where cr.inventory_item_id = common_assembly_item_id
and cr.organization_id = common_org_id
and cr.implementation_date is not null
and cr.effectivity_date <= eff_date);
select count(*)
from mtl_rtg_item_revisions
where inventory_item_id = common_assembly_item_id
and organization_id = common_org_id
and process_revision = common_revision;
SELECT r.process_revision,
r.effectivity_date
FROM mtl_rtg_item_revisions r
WHERE r.inventory_item_id = item.revised_item_id
AND r.organization_id = item.organization_id
AND r.effectivity_date = (
SELECT max(cr.effectivity_date)
FROM mtl_rtg_item_revisions cr
WHERE cr.inventory_item_id = item.revised_item_id
AND cr.organization_id = item.organization_id
AND cr.implementation_date is not null
AND cr.effectivity_date <= eff_date);
SELECT 'X'
FROM wip_operations o,
wip_discrete_jobs w
WHERE w.wip_entity_id = p_from_wip_entity_id
AND w.status_type = 1
AND o.wip_entity_id = w.wip_entity_id
AND o.operation_seq_num = p_operation_seq_num
AND o.organization_id = p_organization_id;
SELECT 'X'
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM wip_discrete_jobs wdj,
wip_entities we,
wip_entities we1,
wip_entities we2
WHERE we1.wip_entity_id = p_from_wip_entity_id
AND we2.wip_entity_id = p_to_wip_entity_id
AND we.wip_entity_name >= we1.wip_entity_name
AND we.wip_entity_name <= we2.wip_entity_name
AND wdj.wip_entity_id = we.wip_entity_id
AND we.organization_id = p_organization_id
AND status_type = 1
AND NOT EXISTS (
SELECT 1
FROM wip_operations wo
WHERE wo.wip_entity_id = we.wip_entity_id
AND operation_seq_num = p_operation_seq_num
)
);
SELECT 'X'
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM wip_discrete_jobs wdj
WHERE wdj.lot_number= p_wip_lot_number
AND wdj.status_type = 1
AND wdj.primary_item_id = item.revised_item_id
AND wdj.organization_Id = item.organization_id
AND NOT EXISTS (
SELECT 1
FROM wip_operations wo
WHERE wo.wip_entity_id = wdj.wip_entity_id
AND operation_seq_num = p_operation_seq_num
)
);
select o.change_notice,
o.implementation_date,
o.disable_date,
o.effectivity_date,
o.operation_sequence_id
from bom_operation_sequences o
where o.operation_sequence_id = old_id
for update of o.change_notice,
o.disable_date,
o.last_update_date,
o.last_updated_by,
o.last_update_login,
o.request_id,
o.program_application_id,
o.program_id,
o.program_update_date;
SELECT acd_type,
operation_sequence_id,
resource_seq_num,
resource_id
FROM bom_operation_resources
WHERE operation_sequence_id= chng_operation_rec.operation_sequence_id;
Select 'x' -- overlapping effectivity
from bom_operation_sequences b
where b.routing_sequence_id= routing_seq_id
-- and b.operation_sequence_id = operation_seq_id
and b.operation_seq_num = operation_num
and b.implementation_date is not null
and b.effectivity_date <= eff_date
and nvl(b.disable_date, eff_date + 1) > eff_date
union
select 'x' -- duplicate value on unique index
from bom_operation_sequences b
where b.routing_sequence_id= routing_seq_id
and b.operation_seq_num = operation_num
and b.effectivity_date = eff_date
and b.operation_sequence_id <> operation_seq_id;
SELECT 'X'
FROM wip_operation_resources wor,
wip_discrete_jobs w
WHERE w.wip_entity_id = p_from_wip_entity_id
AND w.status_type = 1
AND wor.wip_entity_id = p_from_wip_entity_id
AND wor.operation_seq_num = p_operation_seq_num
AND wor.organization_id = p_organization_id
AND wor.resource_seq_num = p_resource_seq_num;
SELECT 'X'
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM wip_discrete_jobs wdj,
wip_entities we,
wip_entities we1,
wip_entities we2
WHERE we1.wip_entity_id = p_from_wip_entity_id
AND we2.wip_entity_id = p_to_wip_entity_id
AND we.wip_entity_name >= we1.wip_entity_name
AND we.wip_entity_name <= we2.wip_entity_name
AND wdj.wip_entity_id = we.wip_entity_id
AND we.organization_id = p_organization_id
AND status_type = 1
AND NOT EXISTS (
SELECT 1
FROM wip_operation_resources wor
WHERE wor.wip_entity_id = we.wip_entity_id
AND wor.operation_seq_num = p_operation_seq_num
AND wor.resource_seq_num = p_resource_seq_num
AND wor.organization_id = p_organization_id
)
);
SELECT 'X'
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM wip_discrete_jobs wdj
WHERE wdj.lot_number= p_wip_lot_number
AND wdj.primary_item_id = item.revised_item_id
AND wdj.organization_Id = item.organization_id
AND wdj.status_type = 1
AND NOT EXISTS (
SELECT 1
FROM wip_operation_resources wor
WHERE wor.wip_entity_id = wdj.wip_entity_id
AND wor.operation_seq_num = p_operation_seq_num
AND wor.resource_seq_num = p_resource_seq_num
AND wor.organization_id = p_organization_id
)
);
SELECT 'X'
FROM wip_requirement_operations ro,
wip_discrete_jobs w
WHERE w.wip_entity_id = p_from_wip_entity_id
AND w.status_type = 1
AND ro.wip_entity_id = p_from_wip_entity_id
AND ro.operation_seq_num = p_operation_seq_num
AND ro.organization_id = p_organization_id
AND ro.inventory_item_id = p_inventory_item_id
;
SELECT 'X'
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM wip_discrete_jobs wdj,
wip_entities we,
wip_entities we1,
wip_entities we2
WHERE we1.wip_entity_id = p_from_wip_entity_id
AND we2.wip_entity_id = p_to_wip_entity_id
AND we.wip_entity_name >= we1.wip_entity_name
AND we.wip_entity_name <= we2.wip_entity_name
AND wdj.wip_entity_id = we.wip_entity_id
AND we.organization_id = p_organization_id
AND status_type = 1
AND NOT EXISTS (
SELECT 1
FROM wip_requirement_operations ro
WHERE ro.wip_entity_id = we.wip_entity_id
AND ro.operation_seq_num = p_operation_seq_num
AND ro.inventory_item_id= p_inventory_item_id
AND ro.organization_id = p_organization_id
)
);
SELECT 'X'
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM wip_discrete_jobs wdj
WHERE wdj.lot_number= p_wip_lot_number
AND wdj.primary_item_id = item.revised_item_id
AND wdj.organization_Id = item.organization_id
AND wdj.status_type = 1
AND NOT EXISTS (
SELECT 1
FROM wip_requirement_operations ro
WHERE ro.wip_entity_id = wdj.wip_entity_id
AND ro.operation_seq_num = p_operation_seq_num
AND ro.inventory_item_id = p_inventory_item_id
AND ro.organization_id = p_organization_id
)
);
l_wip_jsi_insert_flag NUMBER :=0;
l_update_wip NUMBER;
SELECT operation_seq_num
FROM bom_components_b --bom_inventory_components
WHERE component_sequence_id = p_old_component_sequence_id ;
SELECT we.wip_entity_name,
we.organization_id,
wdj.start_quantity,
wdj.scheduled_start_date,
wdj.scheduled_completion_date,
wdj.primary_item_id,
wdj.alternate_bom_designator, --2964588
wdj.alternate_routing_designator, --2964588
wdj.bom_revision_date,
wdj.routing_revision_date,
null bom_revision, -- Bug 3381547
null routing_revision -- Bug 3381547
FROM wip_discrete_jobs wdj,
wip_entities we,
wip_entities we1,
wip_entities we2
WHERE we1.wip_entity_id = l_from_wip_entity_id
AND we2.wip_entity_id = l_to_wip_entity_id
AND ( (we.wip_entity_name >= we1.wip_entity_name
and we.wip_entity_name <= we2.wip_entity_name)
)
AND we.organization_id = l_wip_organization_id
AND wdj.wip_entity_id = we.wip_entity_id
AND wdj.status_type = 1
AND wdj.job_type = 1 -- 2986915
AND (( wdj.scheduled_start_date >= eff_date
OR wdj.scheduled_completion_date >= eff_date) --1900068
OR l_update_all_jobs =1) --bug 2327582.
/* Modified for Bug 2883762 */
UNION
SELECT we.wip_entity_name,
we.organization_id,
wdj.start_quantity,
wdj.scheduled_start_date,
wdj.scheduled_completion_date,
wdj.primary_item_id,
wdj.alternate_bom_designator, --2964588
wdj.alternate_routing_designator, --2964588
wdj.bom_revision_date,
wdj.routing_revision_date,
wdj.bom_revision bom_revision, -- Bug 3381547
wdj.routing_revision routing_revision -- Bug 3381547
FROM wip_discrete_jobs wdj,
wip_entities we,
wip_requirement_operations o,
wip_entities we1,
wip_entities we2
WHERE wdj.wip_entity_id = we.wip_entity_id
AND we1.wip_entity_id = l_from_wip_entity_id
AND we2.wip_entity_id = l_to_wip_entity_id
AND ( (we.wip_entity_name >= we1.wip_entity_name
and we.wip_entity_name <= we2.wip_entity_name)
)
AND wdj.status_type = 1
AND wdj.job_type = 1 -- 2986915
AND (( wdj.scheduled_start_date >= eff_date
or wdj.scheduled_completion_date >= eff_date ) --1900068
OR l_update_all_jobs =1) --bug 2327582
AND wdj.organization_id = we.organization_id
AND we.organization_id = o.organization_id
AND we.wip_entity_id = o.wip_entity_id
AND o.inventory_item_id = l_wip_primary_item_id
AND o.organization_id = l_wip_organization_id
AND o.repetitive_schedule_id is NULL
AND o.wip_supply_type = 6 ;
SELECT we.wip_entity_name,
we.organization_id,
wdj.start_quantity,
wdj.scheduled_start_date,
wdj.scheduled_completion_date,
wdj.primary_item_id,
wdj.alternate_bom_designator, --2964588
wdj.alternate_routing_designator, --2964588
wdj.bom_revision_date,
wdj.routing_revision_date,
null bom_revision, -- Bug 3381547
null routing_revision -- Bug 3381547
FROM wip_discrete_jobs wdj,
wip_entities we,
bom_bill_of_materials b --3412747
WHERE we.organization_id = wdj.organization_id
AND we.wip_entity_id = wdj.wip_entity_id
AND wdj.status_type = 1
AND wdj.job_type = 1 -- 2986915
AND (( wdj.scheduled_start_date >= eff_date
or wdj.scheduled_completion_date >= eff_date) --1900068
OR l_update_all_jobs =1) --bug 2327582
AND wdj.lot_number = l_lot_number
--AND wdj.primary_item_id = item.revised_item_id --3412747
AND wdj.primary_item_id = b.assembly_item_id
AND wdj.organization_id = b.organization_id
AND nvl(wdj.alternate_bom_designator,'NO ALTERNATE') =
nvl(b.alternate_bom_designator,'NO ALTERNATE')
--AND b.common_bill_sequence_id = item.bill_sequence_id
AND b.source_bill_sequence_id = item.bill_sequence_id
/* Modified for Bug 2883762 */
UNION
SELECT we.wip_entity_name,
we.organization_id,
wdj.start_quantity,
wdj.scheduled_start_date,
wdj.scheduled_completion_date,
wdj.primary_item_id,
wdj.alternate_bom_designator, --2964588
wdj.alternate_routing_designator, --2964588
wdj.bom_revision_date,
wdj.routing_revision_date,
wdj.bom_revision bom_revision, -- Bug 3381547
wdj.routing_revision routing_revision -- Bug 3381547
FROM wip_discrete_jobs wdj,
wip_entities we,
wip_requirement_operations o
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.status_type = 1
AND wdj.job_type = 1 -- 2986915
AND (( wdj.scheduled_start_date >= eff_date
or wdj.scheduled_completion_date >= eff_date ) --1900068
OR l_update_all_jobs =1) --bug 2327582
AND wdj.organization_id = we.organization_id
AND we.organization_id = o.organization_id
AND we.wip_entity_id = o.wip_entity_id
AND o.inventory_item_id = l_wip_primary_item_id
AND o.organization_id = l_wip_organization_id
AND o.repetitive_schedule_id is NULL
AND o.wip_supply_type = 6
AND wdj.lot_number = l_lot_number;
SELECT we.wip_entity_name,
we.organization_id,
wdj.start_quantity,
wdj.scheduled_start_date,
wdj.scheduled_completion_date,
wdj.primary_item_id,
wdj.alternate_bom_designator, --2964588
wdj.alternate_routing_designator, --2964588
wdj.bom_revision_date,
wdj.routing_revision_date,
null bom_revision, -- Bug 3381547
null routing_revision -- Bug 3381547
FROM wip_discrete_jobs wdj,
wip_entities we,
bom_bill_of_materials b --3412747
WHERE we.organization_id = wdj.organization_id
--WHERE we.organization_id = l_wip_organization_id
AND wdj.wip_entity_id = we.wip_entity_id
AND wdj.status_type = 1
AND wdj.job_type = 1 -- 2986915
AND (( wdj.scheduled_start_date >= eff_date
or wdj.scheduled_completion_date >= eff_date ) --1900068
OR l_update_all_jobs =1) --bug 2327582
--AND wdj.primary_item_id = l_wip_primary_item_id --3412747
AND wdj.primary_item_id = b.assembly_item_id
AND wdj.organization_id = b.organization_id
AND nvl(wdj.alternate_bom_designator,'NO ALTERNATE') =
nvl(b.alternate_bom_designator,'NO ALTERNATE')
--AND b.common_bill_sequence_id = item.bill_sequence_id
AND b.source_bill_sequence_id = item.bill_sequence_id
AND l_lot_number IS NULL
AND l_from_wip_entity_id IS NULL
AND l_to_wip_entity_id IS NULL
/* Modified for Bug 2883762 */
UNION
SELECT we.wip_entity_name,
we.organization_id,
wdj.start_quantity,
wdj.scheduled_start_date,
wdj.scheduled_completion_date,
wdj.primary_item_id,
wdj.alternate_bom_designator, --2964588
wdj.alternate_routing_designator, --2964588
wdj.bom_revision_date,
wdj.routing_revision_date,
wdj.bom_revision bom_revision, -- Bug 3381547
wdj.routing_revision routing_revision -- Bug 3381547
FROM wip_discrete_jobs wdj,
wip_entities we,
bom_bill_of_materials b, --3412747
wip_requirement_operations o
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.status_type = 1
AND wdj.job_type = 1 -- 2986915
AND (( wdj.scheduled_start_date >= eff_date
or wdj.scheduled_completion_date >= eff_date ) --1900068
OR l_update_all_jobs =1) --bug 2327582
AND wdj.organization_id = we.organization_id
AND we.organization_id = o.organization_id
AND we.wip_entity_id = o.wip_entity_id
--AND o.inventory_item_id = l_wip_primary_item_id --3412747
--AND o.organization_id = l_wip_organization_id
AND o.inventory_item_id = b.assembly_item_id
AND o.organization_id = b.organization_id
AND (nvl(wdj.alternate_bom_designator,'NO ALTERNATE') =
nvl(b.alternate_bom_designator,'NO ALTERNATE')
or
(wdj.alternate_bom_designator is not null
and
b.alternate_bom_designator is null
and not exists (select null
from bom_bill_of_materials b2
where b2.organization_id = b.organization_id
and b2.assembly_item_id = b.assembly_item_id
and b2.alternate_bom_designator =
wdj.alternate_bom_designator)
)
)
AND b.source_bill_sequence_id = item.bill_sequence_id -- r12 common bom changes
--AND b.common_bill_sequence_id = item.bill_sequence_id
AND o.repetitive_schedule_id is NULL
AND o.wip_supply_type = 6 ;
SELECT 1
FROM mtl_system_items_kfv
WHERE concatenated_segments = p_tomfg_item;
select starting_revision
from mtl_parameters where
organization_id = p_org_id;
Select 'x' dummy
From mtl_system_items msi
Where msi.inventory_item_id = p_item_id
And msi.organization_id = p_org_id
And msi.eng_item_flag = 'N';
Select 'x' dummy
From bom_bill_of_materials bbom
Where bbom.assembly_item_id = p_item_id
And bbom.organization_id = p_org_id
And nvl(bbom.alternate_bom_designator, 'PRIMARY ALTERNATE') =
nvl(p_alternate, 'PRIMARY ALTERNATE')
And bbom.assembly_type = 1;
Select 'x' dummy
From bom_operational_routings bor
Where bor.assembly_item_id = p_item_id
And bor.organization_id = p_org_id
And nvl(bor.alternate_routing_designator, 'PRIMARY ALTERNATE') =
nvl(p_alternate, 'PRIMARY ALTERNATE')
And bor.routing_type = 1;
SELECT scheduled_date, implementation_date
FROM eng_revised_items
WHERE revised_item_sequence_id IN
(SELECT local_revised_item_sequence_id
FROM eng_change_logs_vl
WHERE (local_change_id, local_organization_id) IN
(SELECT object_to_id1, object_to_id3 -- local_change_id, local_org_id
From Eng_Change_Obj_Relationships
Where object_to_name = 'ENG_CHANGE'
and change_id = p_change_id
And relationship_code = 'PROPAGATED_TO')
AND local_change_id IS NOT NULL
AND local_revised_item_sequence_id IS NOT NULL
AND log_classification_code = 'PROPAGATN'
AND change_id = p_change_id
AND revised_item_sequence_id = p_revised_item_sequence_id
AND log_type_code = 'INFO')
AND status_type <> 5
AND transfer_or_copy = 'O'
AND parent_revised_item_seq_id IS NULL;
SELECT effectivity_control
FROM bom_structures_b
WHERE bill_sequence_id = cp_bill_id;
select effectivity_date, revision
from mtl_item_revisions_b
where revision_id = cp_revision_id
and inventory_item_id = cp_item_id
and organization_id = cp_org_id
and implementation_date is not null;
SELECT -1
FROM bom_components_b bcb
where bcb.component_sequence_id = cp_comp_sequence_id
AND cp_from_rev_eff >= (SELECT mirb1.effectivity_date FROM mtl_item_revisions_b mirb1
WHERE mirb1.revision_id = bcb.from_end_item_rev_id)
AND (bcb.to_end_item_rev_id is null
OR cp_from_rev_eff <= (SELECT mirb2.effectivity_date FROM mtl_item_revisions_b mirb2
WHERE mirb2.revision_id = bcb.to_end_item_rev_id)
);
Select mirb1.revision_id, mirb1.effectivity_date
from mtl_item_revisions_b mirb1
where mirb1.inventory_item_id = cp_item_id
and mirb1.organization_id = cp_org_id
and mirb1.effectivity_date < cp_effec_date
and mirb1.implementation_date is not null
and rownum < 2
order by mirb1.revision desc;
SELECT 1
FROM bom_components_b bcb
where bcb.bill_sequence_id = cp_bill_id
and bcb.component_item_id = cp_component_item_id
and bcb.operation_seq_num = cp_operation_seq_num
and bcb.implementation_date is not null
and bcb.disable_date is NULL
AND EXISTS (SELECT null FROM mtl_item_revisions_b mirb1 WHERE
mirb1.inventory_item_id = cp_end_item_id AND mirb1.organization_id = cp_org_id
AND mirb1.revision_id = bcb.from_end_item_rev_id)
AND ( cp_to_rev_eff IS NULL
OR cp_to_rev_eff >= (SELECT mirb2.effectivity_date FROM mtl_item_revisions_b mirb2
WHERE mirb2.revision_id = bcb.from_end_item_rev_id)
)
AND ( bcb.to_end_item_rev_id IS NULL
OR cp_from_rev_eff <= (SELECT mirb3.effectivity_date FROM mtl_item_revisions_b mirb3
WHERE mirb3.revision_id = bcb.to_end_item_rev_id)
);
select revision, implementation_date
from mtl_item_revisions_b
where revision_id = cp_revision_id;
SELECT nvl(bsb.SOURCE_BILL_SEQUENCE_ID, bsb.BILL_SEQUENCE_ID) SOURCE_BILL_SEQUENCE_ID
FROM bom_structures_b bsb
WHERE bsb.source_bill_sequence_id = cp_bill_id
AND bsb.bill_sequence_id <> bsb.source_bill_sequence_id;
SELECT bcb.effectivity_date
FROM bom_components_b bcb
WHERE bcb.revised_item_sequence_id = cp_rev_seq_id
AND bcb.bill_sequence_id = cp_bill_id
AND EXISTS
(SELECT 1
FROM bom_structures_b bsb
WHERE bsb.bill_sequence_id = cp_bill_id
AND bsb.bill_sequence_id <> bsb.source_bill_sequence_id)
AND ROWNUM < 2;
SELECT 1 FROM dual
WHERE EXISTS (SELECT 1 FROM bom_substitute_components
WHERE component_sequence_id = cp_component_sequence_id
AND acd_type IS NOT NULL)
OR EXISTS (SELECT 1 FROM bom_substitute_components
WHERE component_sequence_id = cp_component_sequence_id
AND acd_type IS NOT NULL);
SELECT a.component_sequence_id,
b.old_component_sequence_id,
a.acd_type,
a.substitute_component_id,
f.concatenated_segments item_number
FROM bom_substitute_components a,
bom_inventory_components b,
mtl_system_items_b_kfv f
WHERE a.component_sequence_id = b.component_sequence_id
and b.revised_item_sequence_id = revised_item
and f.inventory_item_id = a.substitute_component_id
-- and f.organization_id = item.organization_id
and f.organization_id = b.pk2_value
and nvl(a.acd_type,acd_add) = acd_add;
select 'x'
from bom_substitute_components a, bom_inventory_components b
where a.component_sequence_id = b.component_sequence_id
and b.component_sequence_id = X_old_comp_seq_id
and nvl(a.acd_type, acd_add) = acd_add
and b.change_notice = X_change_notice
and a.substitute_component_id = X_sub_comp_id;
rev_op_disable_date_tbl.delete;
rev_comp_disable_date_tbl.delete;
Select trunc(sysdate, 'MI')
into now
from dual;
select count(*) into l_no_bom_dis_comps from dual where exists(
select 1 from mtl_system_items_b where inventory_item_id in
(
select COMPONENT_ITEM_ID from bom_components_b WHERE bill_sequence_id IN
(
select bill_sequence_id from bom_bill_of_materials where
ASSEMBLY_ITEM_ID = item.revised_item_id AND
ORGANIZATION_ID = item.organization_id AND
(
(item.designator_selection_type = 1) --select everything
OR (item.designator_selection_type =2 AND ALTERNATE_BOM_DESIGNATOR IS NULL) --select only primary BOM
OR (item.designator_selection_type = 3 AND ALTERNATE_BOM_DESIGNATOR = item.alternate_bom_designator) --select that particular BOM
)
)
AND (CHANGE_NOTICE IS NULL OR IMPLEMENTATION_DATE IS NOT NULL OR revised_item_sequence_id = revised_item)
) and organization_id = item.organization_id and bom_enabled_flag = 'N'
);
select count(*) into l_no_bom_dis_comps from dual where exists(
select 1 from mtl_system_items_b where inventory_item_id in
(
select COMPONENT_ITEM_ID from bom_components_b WHERE bill_sequence_id IN
(
select bill_sequence_id from bom_bill_of_materials where
ASSEMBLY_ITEM_ID = item.revised_item_id AND
ORGANIZATION_ID = item.organization_id AND
(
(item.designator_selection_type = 1) --select everything
OR (item.designator_selection_type =2 AND ALTERNATE_BOM_DESIGNATOR IS NULL) --select only primary BOM
OR (item.designator_selection_type = 3 AND ALTERNATE_BOM_DESIGNATOR = item.alternate_bom_designator) --select that particular BOM
)
)
) and organization_id = item.organization_id and bom_enabled_flag = 'N'
);
select count(*) into l_no_bom_dis_sub_comps from dual where exists(
select 1 from mtl_system_items_b WHERE inventory_item_id IN
(
--Get all the sub components for all the components for this item
select SUBSTITUTE_COMPONENT_ID from bom_substitute_components where COMPONENT_SEQUENCE_ID in(
-- Get all the components for this item
select COMPONENT_SEQUENCE_ID from bom_components_b WHERE bill_sequence_id IN
(
select bill_sequence_id from bom_bill_of_materials where
ASSEMBLY_ITEM_ID = item.revised_item_id AND
ORGANIZATION_ID = item.organization_id AND
(
(item.designator_selection_type = 1) --select everything
OR (item.designator_selection_type =2 AND ALTERNATE_BOM_DESIGNATOR IS NULL) --select only primary BOM
OR (item.designator_selection_type = 3 AND ALTERNATE_BOM_DESIGNATOR = item.alternate_bom_designator) --select that particular BOM
)
) AND (CHANGE_NOTICE IS NULL OR IMPLEMENTATION_DATE IS NOT NULL OR revised_item_sequence_id = revised_item)
)
) and organization_id = item.organization_id and bom_enabled_flag = 'N'
);
select count(*) into l_no_bom_dis_sub_comps from dual where exists(
select 1 from mtl_system_items_b WHERE inventory_item_id IN
(
--Get all the sub components for all the components for this item
select SUBSTITUTE_COMPONENT_ID from bom_substitute_components where COMPONENT_SEQUENCE_ID in(
-- Get all the components for this item
select COMPONENT_SEQUENCE_ID from bom_components_b WHERE bill_sequence_id IN
(
select bill_sequence_id from bom_bill_of_materials where
ASSEMBLY_ITEM_ID = item.revised_item_id AND
ORGANIZATION_ID = item.organization_id AND
(
(item.designator_selection_type = 1) --select everything
OR (item.designator_selection_type =2 AND ALTERNATE_BOM_DESIGNATOR IS NULL) --select only primary BOM
OR (item.designator_selection_type = 3 AND ALTERNATE_BOM_DESIGNATOR = item.alternate_bom_designator) --select that particular BOM
)
)
)
) and organization_id = item.organization_id and bom_enabled_flag = 'N'
);
SELECT Count(*) INTO l_no_components FROM dual WHERE EXISTS(
select 1 from bom_components_b where bill_sequence_id in
(
select bill_sequence_id from bom_bill_of_materials where
ASSEMBLY_ITEM_ID = item.revised_item_id AND
ORGANIZATION_ID = item.organization_id
) AND revised_item_sequence_id = revised_item AND acd_type <> acd_delete
);
SELECT Count(*) INTO l_no_operations FROM dual WHERE EXISTS(
select 1 FROM bom_operation_sequences WHERE routing_sequence_id IN
(
SELECT routing_sequence_id FROM BOM_OPERATIONAL_ROUTINGS WHERE
ASSEMBLY_ITEM_ID = item.revised_item_id and
ORGANIZATION_ID = item.organization_id
) AND revised_item_sequence_id = item.revised_item_sequence_id AND acd_type <> acd_delete
);
SELECT Count(*) INTO no_bom_disabled_comps FROM dual WHERE EXISTS(
select 1 from mtl_system_items_b where inventory_item_id in
(
select COMPONENT_ITEM_ID from bom_components_b WHERE bill_sequence_id IN
(
select bill_sequence_id from bom_bill_of_materials where
ASSEMBLY_ITEM_ID = item.revised_item_id AND
ORGANIZATION_ID = item.organization_id
)
AND revised_item_sequence_id = revised_item AND acd_type <> acd_delete
) and organization_id = item.organization_id and bom_enabled_flag = 'N'
);
SELECT Count(*) INTO no_bom_disabled_sub_comps FROM dual WHERE EXISTS(
select 1 from mtl_system_items_b WHERE inventory_item_id IN
(
--Get all the sub components for all the components for this item
select SUBSTITUTE_COMPONENT_ID from bom_substitute_components where COMPONENT_SEQUENCE_ID in(
-- Get all the components for this item
select COMPONENT_SEQUENCE_ID from bom_components_b WHERE bill_sequence_id IN
(
select bill_sequence_id from bom_bill_of_materials where
ASSEMBLY_ITEM_ID = item.revised_item_id AND
ORGANIZATION_ID = item.organization_id
) AND revised_item_sequence_id = revised_item AND acd_type <> acd_delete
) AND acd_type <> acd_delete
) and organization_id = item.organization_id and bom_enabled_flag = 'N'
);
SELECT mtl_system_items_s.NEXTVAL INTO l_new_assembly_item_id FROM dual;
SELECT concatenated_copy_segments INTO l_concatenated_copy_segments
FROM eng_revised_items WHERE revised_item_sequence_id = item.revised_item_sequence_id;
INSERT INTO eng_revised_items_temp
( temp_id
, organization_id
, organization_code
, organization_name
, inventory_item_id
, item_number
, item_description
, transfer_or_copy_item
, transfer_or_copy_bill
, transfer_or_copy_routing
, new_item_revision
, new_routing_revision
, designator_selection_type
, alternate_bom_designator
, change_notice
, copy_to_item
, copy_to_item_desc
, transfer_or_copy
, last_update_date
, last_updated_by
, creation_date
, created_by)
VALUES ( l_temp_id
, item.organization_id
, item.organization_code
, item.organization_name
, item.revised_item_id
, item.concatenated_segments
, item.description
, item.transfer_or_copy_item
, item.transfer_or_copy_bill
, item.transfer_or_copy_routing
, item.new_item_revision
, item.new_routing_revision
, item.designator_selection_type
, item.alternate_bom_designator
, item.change_notice
, item.copy_to_item
, item.copy_to_item_desc
, item.transfer_or_copy
, item.last_update_date
, item.last_updated_by
, item.creation_date
, item.created_by);
INSERT INTO ENG_PARENT_CHILD_EVENTS_TEMP(parent_event_name
, parent_event_key, parent_erecord_id
, event_name, event_key, erecord_id
, event_status)
VALUES ( 'oracle.apps.eng.ecoImplement', TO_CHAR(item.change_id)
, l_parent_record_id
, l_event.event_name, l_event.event_key, l_event.erecord_id
, l_event.event_status);
IF item.transfer_or_copy_bill = 1 AND UNAPPROVED_COMPONENTS_EXISTS (item.revised_item_id, item.organization_id, item.designator_selection_type, item.alternate_bom_designator) THEN
IF msg_qty < max_messages THEN
msg_qty := msg_qty + 1;
X_transfer_option => item.selection_option,
X_designator_option => item.designator_selection_type,
X_alt_bom_designator => item.alternate_bom_designator,
X_alt_rtg_designator => item.alternate_bom_designator,
X_effectivity_date => item.selection_date,
X_last_login_id => loginid,
X_bom_rev_starting => l_item_revision,
X_rtg_rev_starting => l_routing_revision,
X_ecn_name => item.change_notice,
X_item_code => item.transfer_or_copy_item,
X_bom_code => item.transfer_or_copy_bill,
X_rtg_code => item.transfer_or_copy_routing,
X_mfg_description => item.copy_to_item_desc,
X_segment1 => copy_segments(1),
X_segment2 => copy_segments(2),
X_segment3 => copy_segments(3),
X_segment4 => copy_segments(4),
X_segment5 => copy_segments(5),
X_segment6 => copy_segments(6),
X_segment7 => copy_segments(7),
X_segment8 => copy_segments(8),
X_segment9 => copy_segments(9),
X_segment10 => copy_segments(10),
X_segment11 => copy_segments(11),
X_segment12 => copy_segments(12),
X_segment13 => copy_segments(13),
X_segment14 => copy_segments(14),
X_segment15 => copy_segments(15),
X_segment16 => copy_segments(16),
X_segment17 => copy_segments(17),
X_segment18 => copy_segments(18),
X_segment19 => copy_segments(19),
X_segment20 => copy_segments(20),
X_implemented_only => item.implemented_only,
X_unit_number => item.selection_unit_number);
Update eng_revised_items
set implementation_date = today,
status_type = 6,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate,
status_code = p_status_code
where revised_item_sequence_id = item.revised_item_sequence_id;
UPDATE eng_revised_items
SET scheduled_date = l_max_scheduled_date
WHERE revised_item_sequence_id = item.revised_item_sequence_id;
INV_Item_GRP.Update_Item (
p_Item_rec => l_Item_rec_in
, p_Revision_rec => l_revision_rec
, p_Template_Id => NULL
, p_Template_Name => NULL
, x_Item_rec => l_Item_rec_out
, x_return_status => l_inv_return_status
, x_Error_tbl => l_Error_tbl );
Update eng_revised_items
set implementation_date = today,
status_type = 6,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate,
status_code = p_status_code
where revised_item_sequence_id = item.revised_item_sequence_id;
Update eng_revised_items
set implementation_date = today,
status_type = 6,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate,
status_code = p_status_code
where revised_item_sequence_id = item.revised_item_sequence_id;
update_wip := item.update_wip;
g_Common_Rev_Comp_Tbl.delete;
SELECT substrb(profile_option_value,1,1)
INTO eco_rev_warning_flag
FROM fnd_profile_options opt,
fnd_application appl,
fnd_profile_option_values val
WHERE opt.application_id = val.application_id
AND opt.profile_option_id = val.profile_option_id
AND opt.application_id = appl.application_id
AND appl.application_short_name = 'ENG'
AND opt.profile_option_name = 'ENG:ECO_REV_WARNING'
AND val.level_id = 10001;
Update mtl_item_revisions_b --changed mtl_item_revisions to mtl_item_revisions_b
set implementation_date = today,
effectivity_date = eff_date,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
where inventory_item_id = item.revised_item_id
and organization_id = item.organization_id
and revision = item.new_item_revision;
,p_dml_type => 'UPDATE'
,p_inventory_item_id => item.revised_item_id
,p_organization_id => item.organization_id
,p_revision_id => item.new_item_revision_id );
X_last_update_date => SYSDATE,
X_last_updated_by => userid,
X_creation_date => SYSDATE,
X_created_by => userid,
X_last_update_login => loginid,
X_effectivity_date => eff_date,
X_change_notice => item.change_notice,
X_implementation_date => today);
SELECT userenv('LANG') INTO l_language_code FROM dual;
Update mtl_item_revisions_tl MIR
set description =
(select MIR1.description
from mtl_item_revisions_tl MIR1
where revision_id IN (SELECT revision_id
FROM MTL_ITEM_REVISIONS_B
WHERE
inventory_item_id = item.revised_item_id
and organization_id = item.organization_id
and revision = item.new_item_revision)
and language = l_language_code
)
where inventory_item_id = common.assembly_item_id
and organization_id = common.organization_id
and revision_id in (SELECT revision_id
FROM MTL_ITEM_REVISIONS_B
WHERE
inventory_item_id = common.assembly_item_id
and organization_id = common.organization_id
and revision = item.new_item_revision);
IF item.update_wip = 1 and item.mrp_active = 2
THEN
-- For ECO cumulative type
IF NVL(item.from_cum_qty, 0) > 0
THEN
OPEN check_job_valid_for_cum
( p_from_wip_entity_id => item.from_wip_entity_id);
/* select effectivity_date into l_wip_bom_revision_date1
from mtl_item_revisions
where inventory_item_id = item.revised_item_id
and revision = l_wip_bom_revision1
and organization_id = item.organization_id;
select effectivity_date into l_wip_routing_revision_date1
from mtl_rtg_item_revisions
where inventory_item_id = item.revised_item_id
and process_revision = l_wip_routing_revision1
and organization_id = item.organization_id;
SELECT substrb(profile_option_value,1,1)
INTO eco_rev_warning_flag
FROM fnd_profile_options opt,
fnd_application appl,
fnd_profile_option_values val
WHERE opt.application_id = val.application_id
AND opt.profile_option_id = val.profile_option_id
AND opt.application_id = appl.application_id
AND appl.application_short_name = 'ENG'
AND opt.profile_option_name = 'ENG:ECO_REV_WARNING'
AND val.level_id = 10001;
UPDATE mtl_rtg_item_revisions
SET implementation_date = today,
effectivity_date = eff_date,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
WHERE inventory_item_id = item.revised_item_id
AND organization_id = item.organization_id
AND process_revision = item.new_routing_revision;
X_last_update_date => SYSDATE,
X_last_updated_by => userid,
X_creation_date => SYSDATE,
X_created_by => userid,
X_last_update_login => loginid,
X_effectivity_date => eff_date,
X_change_notice => item.change_notice,
X_implementation_date => today);
SELECT alternate_routing_designator
INTO l_alternate_routing_designator
FROM bom_operational_routings
WHERE routing_sequence_id = item.routing_sequence_id;
SELECT
routing_sequence_id
,cfm_routing_flag
,completion_subinventory
,completion_locator_id
,mixed_model_map_flag
,common_assembly_item_id
,common_routing_sequence_id
,ctp_flag
,priority
,routing_comment
INTO
l_routing_sequence_id
,l_cfm_routing_flag
,l_completion_subinventory
,l_completion_locator_id
,l_mixed_model_map_flag
,l_common_assembly_item_id
,l_common_routing_sequence_id
,l_ctp_flag
,l_priority
,l_routing_comment
FROM bom_operational_routings
WHERE assembly_item_id = item.revised_item_id
AND organization_id = item.organization_id
AND NVL(alternate_routing_designator, 'NULL_ALTERNATE_DESIGNATOR' )
= NVL(l_alternate_routing_designator, 'NULL_ALTERNATE_DESIGNATOR')
;
UPDATE bom_operational_routings
SET common_assembly_item_id =
l_common_assembly_item_id
, common_routing_sequence_id =
l_common_routing_sequence_id
, ctp_flag = NVL(item.ctp_flag,l_ctp_flag)
, priority = NVL(item.priority,l_priority)
, cfm_routing_flag =
NVL(item.cfm_routing_flag, l_cfm_routing_flag)
, routing_comment =
NVL(item.routing_comment, l_routing_comment)
, mixed_model_map_flag =
NVL(item.mixed_model_map_flag, l_mixed_model_map_flag)
, completion_subinventory =
NVL(item.completion_subinventory, l_completion_subinventory)
, completion_locator_id =
NVL(item.completion_locator_id, l_completion_locator_id)
, last_update_date = SYSDATE
, last_updated_by = userid
, last_update_login = loginid
WHERE routing_sequence_id =
l_routing_sequence_id;
IF item.update_wip = 1
AND chng_operation_rec.acd_type IN (acd_change, acd_delete)
THEN
-- For ECO Cumulative type record
-- Check if the current operation is not existing in
-- the specified WIP discrete job.
IF NVL(item.from_cum_qty, 0) > 0
THEN
OPEN check_not_existing_op_cum
( p_from_wip_entity_id => item.from_wip_entity_id,
p_operation_seq_num => chng_operation_rec.operation_seq_num,
p_organization_id => item.organization_id ) ;
END IF; -- end of IF item.update_wip = 1
IF chng_operation_rec.acd_type IN ( acd_change, acd_delete)
THEN
Open old_operation(chng_operation_rec.old_operation_sequence_id);
If chng_operation_rec.acd_type = acd_delete then
chng_operation_rec.disable_date := eff_date;
UPDATE bom_operation_sequences
SET
change_notice = old_op_rec.change_notice,
implementation_date = today,
disable_date = old_op_rec.disable_date,
--bug 5622459 disable_date = old_op_rec.disable_date - 1/(60*60*24),
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
WHERE operation_sequence_id=
old_op_rec.operation_sequence_id;
INSERT INTO bom_operation_resources
(
operation_sequence_id
, resource_seq_num
, resource_id
, activity_id
, standard_rate_flag
, assigned_units
, usage_rate_or_amount
, usage_rate_or_amount_inverse
, basis_type
, schedule_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, resource_offset_percent
, autocharge_type
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, request_id
, program_application_id
, program_id
, program_update_date
, schedule_seq_num
, substitute_group_num
, principle_flag
, change_notice
, acd_type
, original_system_reference
)
SELECT
chng_operation_rec.operation_sequence_id
, resource_seq_num
, resource_id
, activity_id
, standard_rate_flag
, assigned_units
, usage_rate_or_amount
, usage_rate_or_amount_inverse
, basis_type
, schedule_flag
, sysdate
, userid
, sysdate
, userid
, loginid
, resource_offset_percent
, autocharge_type
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, reqstid
, appid
, progid
, sysdate
, schedule_seq_num
, substitute_group_num
, principle_flag
, change_notice
, acd_type
, original_system_reference
FROM bom_operation_resources a
WHERE operation_sequence_id =
chng_operation_rec.old_operation_sequence_id
--* Commented following line for Bug 3520302
--* AND nvl(acd_type, acd_add) = acd_add
--* Added for Bug 3520302
AND nvl(acd_type, acd_add) in (acd_add,acd_change)
AND resource_seq_num NOT IN (
SELECT b.resource_seq_num
FROM bom_operation_resources b
WHERE b.operation_sequence_id =
chng_operation_rec.operation_sequence_id);
/* Fix for bug 4606950 - In the above select query, modified the sub-query in the where clause.
Replaced the resource_id with resource_seq_num. The old sub-query was commented as below*/
/*
AND resource_id NOT IN (
SELECT b.resource_id
FROM bom_operation_resources b
WHERE b.operation_sequence_id =
chng_operation_rec.operation_sequence_id
and b.resource_seq_num = a.resource_seq_num);
INSERT INTO bom_sub_operation_resources
(
operation_sequence_id
, substitute_group_num
, resource_id
, replacement_group_num
, activity_id
, standard_rate_flag
, assigned_units
, usage_rate_or_amount
, usage_rate_or_amount_inverse
, basis_type
, schedule_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, resource_offset_percent
, autocharge_type
, principle_flag
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, request_id
, program_application_id
, program_id
, program_update_date
, schedule_seq_num
, change_notice
, acd_type
, original_system_reference
)
select
chng_operation_rec.operation_sequence_id
, substitute_group_num
, resource_id
, replacement_group_num
, activity_id
, standard_rate_flag
, assigned_units
, usage_rate_or_amount
, usage_rate_or_amount_inverse
, basis_type
, schedule_flag
, sysdate
, userid
, sysdate
, userid
, loginid
, resource_offset_percent
, autocharge_type
, principle_flag
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, reqstid
, appid
, progid
, sysdate
, schedule_seq_num
, change_notice
, acd_type
, original_system_reference
FROM bom_sub_operation_resources
WHERE operation_sequence_id =
chng_operation_rec.old_operation_sequence_id
AND nvl(acd_type, acd_add) = acd_add
AND resource_id NOT IN (
SELECT resource_id
FROM bom_sub_operation_resources
WHERE operation_sequence_id =
chng_operation_rec.operation_sequence_id );
, X_last_update_login => loginid
, X_program_application_id => appid
, X_program_id => progid
, X_request_id => reqstid);
END IF; -- end of IF chng_operation_rec.acd_type in ( acd_change, acd_delete )
IF item.update_wip = 1
AND chng_resource_rec.acd_type IN ( acd_change, acd_delete)
THEN
--For ECO Cumulative type record
--Check if the current resource is not existing in the
--specified WIP discrete job'operation.
IF NVL(item.from_cum_qty, 0) > 0
THEN
OPEN check_not_existing_res_cum
(p_from_wip_entity_id => item.from_wip_entity_id,
p_operation_seq_num => chng_operation_rec.operation_seq_num,
p_resource_seq_num => chng_resource_rec.resource_seq_num,
p_organization_id => item.organization_id
) ;
END IF; -- end of IF item.update_wip = 1 and chng_resource_rec
/* Commented the below delete st for bug 4577459 . This delete st deletes all rows from the table
that have been created through the routing form irrespective of which routing they belong to.
As while implementing the ECO we do not delete the bom_operation_resources data the sub resources
associated need not be deleted */
/* DELETE
FROM bom_sub_operation_resources sr
WHERE NOT EXISTS (
SELECT 1
FROM bom_operation_resources bor
WHERE bor.operation_sequence_id = sr.operation_sequence_id
AND bor.substitute_group_num = sr.substitute_group_num
AND bor.acd_type <> 3
);*/
UPDATE bom_operation_sequences
SET
change_notice = item.change_notice,
implementation_date = today,
disable_date = chng_operation_rec.disable_date,
effectivity_date = eff_date,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
WHERE operation_sequence_id =
chng_operation_rec.operation_sequence_id;
Update bom_operation_sequences
set old_operation_sequence_id = chng_operation_rec.operation_sequence_id,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
where old_operation_sequence_id =
chng_operation_rec.old_operation_sequence_id
and implementation_date is null;
INSERT into eng_revised_operations(
operation_sequence_id ,
routing_sequence_id ,
operation_seq_num ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
standard_operation_id ,
department_id ,
operation_lead_time_percent ,
minimum_transfer_quantity ,
count_point_type ,
operation_description ,
effectivity_date ,
disable_date ,
backflush_flag ,
option_dependent_flag ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
operation_type ,
reference_flag ,
process_op_seq_id ,
line_op_seq_id ,
yield ,
cumulative_yield ,
reverse_cumulative_yield ,
labor_time_calc ,
machine_time_calc ,
total_time_calc ,
labor_time_user ,
machine_time_user ,
total_time_user ,
net_planning_percent ,
--x_coodinate,
--y_coordinate,
include_in_rollup ,
operation_yield_enabled ,
change_notice ,
implementation_date ,
old_operation_sequence_id ,
acd_type ,
revised_item_sequence_id ,
original_system_reference,
eco_for_production
)
SELECT
operation_sequence_id ,
routing_sequence_id ,
operation_seq_num ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
standard_operation_id ,
department_id ,
operation_lead_time_percent ,
minimum_transfer_quantity ,
count_point_type ,
operation_description ,
effectivity_date ,
disable_date ,
backflush_flag ,
option_dependent_flag ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
operation_type ,
reference_flag ,
process_op_seq_id ,
line_op_seq_id ,
yield ,
cumulative_yield ,
reverse_cumulative_yield ,
labor_time_calc ,
machine_time_calc ,
total_time_calc ,
labor_time_user ,
machine_time_user ,
total_time_user ,
net_planning_percent ,
--x_coodinate,
--y_coordinate,
include_in_rollup ,
operation_yield_enabled ,
change_notice ,
implementation_date ,
old_operation_sequence_id ,
acd_type ,
revised_item_sequence_id ,
original_system_reference,
eco_for_production
FROM bom_operation_sequences b
WHERE operation_sequence_id
= chng_operation_rec.operation_sequence_id;
IF chng_operation_rec.acd_type = acd_delete then
Delete from bom_operation_sequences
where operation_sequence_id = chng_operation_rec.operation_sequence_id;
-- Decision for BillCreate or Update depends on pending_from_ecn:
OPEN Get_Bill_of_Materials_Info( item.bill_sequence_id);
select assembly_type
into l_bom_assembly_type
from bom_bill_of_materials
where bill_sequence_id = item.bill_sequence_id;
IF item.update_wip = 1
AND component.acd_type IN (2, 3)
THEN
--For ECO Cumulative type record
--Check if the current compoment is not existing in the
--specified WIP discrete job'operation.
IF NVL(item.from_cum_qty, 0) > 0
THEN
OPEN check_not_existing_comp_cum
(p_from_wip_entity_id => item.from_wip_entity_id,
p_operation_seq_num => component.operation_seq_num,
p_inventory_item_id => component.component_item_id,
p_organization_id => item.organization_id
) ;
END IF; -- end of IF item.update_wip = 1
If component.acd_type in (acd_change, acd_delete)
then
-- Fetch The Old component Details For Validations
Open old_component(component.old_component_sequence_id);
If component.acd_type = acd_delete
then
component.disable_date := eff_date;
Update bom_components_b --bom_inventory_components
set disable_date = old_comp_rec.disable_date,
to_object_revision_id = old_comp_rec.to_object_revision_id,
overlapping_changes = old_comp_rec.overlapping_changes,
change_notice = old_comp_rec.change_notice,
implementation_date = today,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
where component_sequence_id = old_comp_rec.component_sequence_id;
Update bom_components_b --bom_inventory_components
set to_end_item_unit_number = X_prev_unit_number,
to_object_revision_id = old_comp_rec.to_object_revision_id,
overlapping_changes = old_comp_rec.overlapping_changes,
change_notice = old_comp_rec.change_notice,
implementation_date = today,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
where component_sequence_id =
old_comp_rec.component_sequence_id;
Update bom_components_b --bom_inventory_components
set disable_date = old_comp_rec.disable_date,
to_object_revision_id = old_comp_rec.to_object_revision_id,
overlapping_changes = old_comp_rec.overlapping_changes,
change_notice = old_comp_rec.change_notice,
implementation_date = today,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
where component_sequence_id = old_comp_rec.component_sequence_id;
Update bom_components_b --bom_inventory_components
set to_end_item_rev_id = l_prev_end_item_rev_id,
to_object_revision_id = old_comp_rec.to_object_revision_id,
overlapping_changes = old_comp_rec.overlapping_changes,
change_notice = old_comp_rec.change_notice,
implementation_date = today,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
where component_sequence_id = old_comp_rec.component_sequence_id;
Update bom_components_b --bom_inventory_components
set disable_date = old_comp_rec.disable_date,
to_object_revision_id = old_comp_rec.to_object_revision_id,
overlapping_changes = old_comp_rec.overlapping_changes,
change_notice = old_comp_rec.change_notice,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
-- where current of old_component;
message_names(msg_qty) := 'ENG_COM_COMP_UPDATE_FAILED';
BOMPCMBM.Update_Related_Components(
p_src_comp_seq_id => old_comp_rec.component_sequence_id
, x_Mesg_Token_Tbl => l_comn_mesg_token_tbl
, x_Return_Status => l_comn_return_status);
message_names(msg_qty) := 'ENG_COM_COMP_UPDATE_FAILED';
delete since this row will be deleted from the inventory_components table at the
end if implementation */
IF component.acd_type <> acd_delete
THEN
-- Initialize
l_overlap_found := 2;
SELECT bsc.acd_type into rec_exist
FROM Bom_Inventory_Components bic,
BOM_SUBSTITUTE_COMPONENTS bsc
WHERE bic.Old_Component_Sequence_Id = sub_component.old_component_sequence_id
AND bic.Change_Notice = item.change_notice
AND bic.Implementation_Date IS NULL
AND bsc.component_sequence_id = bic.component_sequence_id
AND bsc.substitute_component_id = sub_component.substitute_component_id
AND bsc. ACD_TYPE = acd_delete;
If rec_exist <> acd_delete then
rec_exist := 0;
Insert into bom_substitute_components(
substitute_component_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
substitute_item_quantity,
component_sequence_id,
acd_type,
change_notice,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
select
substitute_component_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
substitute_item_quantity,
component.component_sequence_id, -- new component
acd_type,
change_notice, --null, bug 5174519
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
from bom_substitute_components
where component_sequence_id =
component.old_component_sequence_id
and nvl(acd_type, acd_add) = acd_add
and substitute_component_id not in (
select substitute_component_id
from bom_substitute_components
where component_sequence_id =
component.component_sequence_id);
Insert into bom_reference_designators (
component_reference_designator,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
ref_designator_comment,
change_notice,
component_sequence_id,
acd_type,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
select
component_reference_designator,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
ref_designator_comment,
change_notice, --null, bug 5174519
component.component_sequence_id, -- new component
acd_type,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
from bom_reference_designators
where component_sequence_id =
component.old_component_sequence_id
and nvl(acd_type, acd_add) = acd_add -- adds only
and component_reference_designator not in (
select component_reference_designator
from bom_reference_designators
where component_sequence_id =
component.component_sequence_id);
Update bom_components_b--bom_inventory_components
set implementation_date = today,
change_notice = item.change_notice,
disable_date = component.disable_date,
effectivity_date = eff_date,
from_object_revision_id = component.from_object_revision_id,
overlapping_changes = component.overlapping_changes,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
-- where current of chng_component_rows;
UPDATE bom_components_b--bom_inventory_components
SET implementation_date = today,
change_notice = item.change_notice,
disable_date = component.disable_date,
effectivity_date = eff_date,
overlapping_changes = component.overlapping_changes,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
WHERE common_component_sequence_id = component.component_sequence_id
AND common_component_sequence_id <> component_sequence_id
AND implementation_date IS NULL
AND change_notice = item.change_notice
AND revised_item_sequence_id = item.revised_item_sequence_id;
BOMPCMBM.Update_Related_Components(
p_src_comp_seq_id => component.component_sequence_id
, x_Mesg_Token_Tbl => l_comn_mesg_token_tbl
, x_Return_Status => l_comn_return_status);
message_names(msg_qty) := 'ENG_COM_COMP_UPDATE_FAILED';
Update bom_components_b--bom_inventory_components
set old_component_sequence_id = component.component_sequence_id,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
where old_component_sequence_id =
component.old_component_sequence_id
and implementation_date is null;
Insert into eng_revised_components(
component_sequence_id,
component_item_id,
operation_sequence_num,
bill_sequence_id,
change_notice,
effectivity_date,
component_quantity,
component_yield_factor,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
old_component_sequence_id,
item_num,
wip_supply_type,
component_remarks,
supply_subinventory,
supply_locator_id,
implementation_date,
disable_date,
acd_type,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date,
revised_item_sequence_id,
from_end_item_unit_number,
to_end_item_unit_number,
eco_for_production,
FROM_END_ITEM_REV_ID,
TO_END_ITEM_REV_ID,
FROM_OBJECT_REVISION_ID,
TO_OBJECT_REVISION_ID,
FROM_END_ITEM_MINOR_REV_ID,
TO_END_ITEM_MINOR_REV_ID,
COMPONENT_ITEM_REVISION_ID,
COMMON_COMPONENT_SEQUENCE_ID,
BASIS_TYPE)
select
component_sequence_id,
component_item_id,
operation_seq_num,
bill_sequence_id,
change_notice,
effectivity_date,
component_quantity,
component_yield_factor,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
old_component_sequence_id,
item_num,
wip_supply_type,
component_remarks,
supply_subinventory,
supply_locator_id,
implementation_date,
disable_date,
acd_type,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date,
revised_item_sequence_id,
from_end_item_unit_number,
to_end_item_unit_number,
eco_for_production,
FROM_END_ITEM_REV_ID,
TO_END_ITEM_REV_ID,
FROM_OBJECT_REVISION_ID,
TO_OBJECT_REVISION_ID,
FROM_END_ITEM_MINOR_REV_ID,
TO_END_ITEM_MINOR_REV_ID,
COMPONENT_ITEM_REVISION_ID,
COMMON_COMPONENT_SEQUENCE_ID,
BASIS_TYPE
from bom_components_b --bom_inventory_components
where component_sequence_id = component.component_sequence_id;
If component.acd_type = acd_delete then
Delete from bom_components_b --bom_inventory_components
-- where current of chng_component_rows;
DELETE FROM bom_components_b bcb
WHERE bcb.common_component_sequence_id = component.component_sequence_id
AND bcb.common_component_sequence_id <> bcb.component_sequence_id
AND bcb.implementation_date IS null
AND bcb.change_notice = item.change_notice
AND bcb.revised_item_sequence_id = item.revised_item_sequence_id
AND bcb.acd_type = acd_delete;
ELSIF (component.acd_type IN (acd_change, acd_delete) )
THEN
l_BOMEvents_Comps_ACD := acd_change;
Update bom_bill_of_materials
set last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate,
pending_from_ecn = null
where bill_sequence_id = item.bill_sequence_id
AND pending_from_ecn = item.change_notice; -- Fixed for bug 3646438
l_child_event_name := 'oracle.apps.bom.billUpdate';
INSERT INTO ENG_PARENT_CHILD_EVENTS_TEMP(parent_event_name
, parent_event_key, parent_erecord_id
, event_name, event_key, erecord_id
, event_status)
VALUES ( 'oracle.apps.eng.ecoImplement', TO_CHAR(item.change_id)
, l_parent_record_id
, l_event.event_name, l_event.event_key, l_event.erecord_id
, l_event.event_status);
Update bom_operational_routings
set last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate,
pending_from_ecn = null
where routing_sequence_id = item.routing_sequence_id
and pending_from_ecn is not null; --for bugfix 3234628
l_child_event_name := 'oracle.apps.bom.routingUpdate';
INSERT INTO ENG_PARENT_CHILD_EVENTS_TEMP(parent_event_name
, parent_event_key, parent_erecord_id
, event_name, event_key, erecord_id
, event_status)
VALUES ( 'oracle.apps.eng.ecoImplement', TO_CHAR(item.change_id)
, l_parent_record_id
, l_event.event_name, l_event.event_key, l_event.erecord_id
, l_event.event_status);
Update eng_revised_items
set implementation_date = today,
scheduled_date = eff_date,
status_type = 6,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate,
status_code = p_status_code
-- where current of get_item_info;
l_update_wip := NVL(item.update_wip,2);
Update eng_engineering_changes
set implementation_date = today,
status_type = 6,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
where organization_id = item.organization_id
and change_notice = item.change_notice;
select nvl(plm_or_erp_change, 'PLM') , status_code
into l_plm_or_erp_change, l_curr_status_code
from eng_engineering_changes where
change_id = item.change_id;
UPDATE eng_lifecycle_statuses
SET completion_date = sysdate,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid
WHERE entity_name = 'ENG_CHANGE'
AND entity_id1 = item.change_id
AND status_code = l_curr_status_code
AND active_flag = 'Y'
AND completion_date IS NULL;
Update eng_engineering_changes
set implementation_date = today,
status_type = 6,
status_code = p_status_code,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
where organization_id = item.organization_id
and change_notice = item.change_notice;
Update eng_engineering_changes
set promote_status_code = null
where organization_id = item.organization_id
and change_notice = item.change_notice;
UPDATE eng_lifecycle_statuses
SET start_date = nvl(start_date,sysdate), -- set the start date on implemented phase after promoting the header to implemented phase
-- added for bug 3482152
completion_date = sysdate,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid
WHERE entity_name = 'ENG_CHANGE'
AND entity_id1 = (SELECT change_id
FROM eng_engineering_changes
WHERE organization_id = item.organization_id
AND change_notice = item.change_notice
)
AND active_flag = 'Y'
AND sequence_number = (SELECT max(sequence_number)
FROM eng_lifecycle_statuses
WHERE entity_name = 'ENG_CHANGE'
AND entity_id1 = (SELECT change_id
FROM eng_engineering_changes
WHERE organization_id = item.organization_id
AND change_notice = item.change_notice
)
);
/* does not work for some reason, but do not delete
FND_FILE.PUT_NAMES('CP.impECO.wf.log',
'CP.impECO.wf.out',
'/appslog/bis_top/utl/plm115dt/log'
);
SELECT change_wf_route_id
INTO l_wf_route_id
FROM eng_lifecycle_statuses
WHERE entity_name = 'ENG_CHANGE'
AND entity_id1 = item.change_id
AND status_code = p_status_code
AND active_flag = 'Y'
AND rownum = 1;
/* does not work for some reason, but do not delete
FND_FILE.PUT_LINE(fnd_file.log, 'After: calling startWorkflow');
IF l_update_wip = 1
THEN
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_group_id1
FROM DUAL;
l_update_all_jobs := fnd_profile.value('ENG:UPDATE_UNRELEASED_WIP_JOBS');
l_wip_jsi_insert_flag := 0;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_group_id2
FROM DUAL;
INSERT INTO wip_job_schedule_interface
(
last_update_date
, last_updated_by
, creation_date
, created_by
, request_id
, program_application_id
, program_id
, program_update_date
, group_id
, process_phase
, process_status
, organization_id
, load_type
, status_type
, wip_entity_id
)
VALUES
(
sysdate
, userid
, sysdate
, userid
, reqstid
, appid
, progid
, sysdate
, l_wip_group_id1
, l_wip_process_phase
, l_wip_process_status
, item.organization_id
, 3 -- update or delete wip
, 7 -- cancel wip order
, item.from_wip_entity_id
);
INSERT INTO wip_job_schedule_interface
(
last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_id
, program_application_id
, program_update_date
, group_id
, organization_id
, load_type
, status_type
, primary_item_id
, bom_revision_date
, routing_revision_date
, job_name
, start_quantity
, net_quantity
, process_phase
, process_status
, last_unit_completion_date
-- , routing_revision
-- , bom_revision
, completion_subinventory
, completion_locator_id
, allow_explosion
, header_id
)
values
( sysdate
, userid
, sysdate
, userid
, loginid
, reqstid
, progid
, appid
, sysdate
, l_wip_group_id2
, l_wip_organization_id
, l_wip_load_type
, l_wip_status_type
, l_wip_primary_item_id
, l_wip_bom_revision_date1
, decode(l_WIP_Flag_for_routing,'Y', l_wip_routing_revision_date1, NULL) -- Bug 4455543
, l_wip_job_name1
, l_wip_start_quantity1
, l_wip_net_quantity1
, l_wip_process_phase
, l_wip_process_status
, l_wip_last_u_compl_date1
--, l_wip_routing_revision1
--, l_wip_bom_revision1
, l_wip_completion_subinventory
, l_wip_completion_locator_id
, l_wip_allow_explosion
, l_wip_group_id2
);
INSERT INTO wip_job_schedule_interface (
last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_id
, program_application_id
, program_update_date
, group_id
, organization_id
, load_type
, status_type
, primary_item_id
, bom_revision_date
, routing_revision_date
, job_name
, start_quantity
, net_quantity
, process_phase
, process_status
, last_unit_completion_date
-- , routing_revision
-- , bom_revision
, completion_subinventory
, completion_locator_id
, allow_explosion
, header_id
)
values
(
sysdate
, userid
, sysdate
, userid
, loginid
, reqstid
, progid
, appid
, sysdate
, l_wip_group_id1
, l_wip_organization_id
, l_wip_load_type
, l_wip_status_type
, l_wip_primary_item_id
, l_wip_bom_revision_date2
, decode(l_WIP_Flag_for_routing,'Y', l_wip_routing_revision_date2, NULL) -- Bug 4455543
, l_wip_job_name2
, l_wip_start_quantity2
, l_wip_net_quantity2
, l_wip_process_phase
, l_wip_process_status
, decode(l_WIP_Flag_for_routing,'Y',l_wip_last_u_compl_date2, NULL) -- Bug 4455543
-- , l_wip_routing_revision2
-- , l_wip_bom_revision2
, l_wip_completion_subinventory
, l_wip_completion_locator_id
, l_wip_allow_explosion
, l_wip_group_id1
);
l_wip_jsi_insert_flag := 1;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_header_id
FROM DUAL; --fix bug 5667398 cannot have duplicate header id from same group id
INSERT INTO wip_job_schedule_interface
(
last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_id
, program_application_id
, program_update_date
, group_id
, organization_id
, load_type
, status_type
, primary_item_id
, bom_revision_date
, routing_revision_date
, job_name
, process_phase
, process_status
, last_unit_completion_date
, routing_revision
, bom_revision
, bom_reference_id
, routing_reference_id
, allow_explosion
, alternate_bom_designator
, alternate_routing_designator
, completion_subinventory
, completion_locator_id
, header_id
)
values
(
sysdate
, userid
, sysdate
, userid
, loginid
, reqstid
, progid
, appid
, sysdate
, l_wip_group_id1
, l_wip_organization_id
, 3 --l_wip_load_type
, l_wip_status_type
, wip_name_for_job_rec.primary_item_id
--, l_wip_primary_item_id
, l_wip_bom_revision_date2
, decode(l_WIP_Flag_for_routing,'Y',l_wip_routing_revision_date2, NULL) -- Bug 4455543
, l_wip_job_name2
, l_wip_process_phase
, l_wip_process_status
, decode(l_WIP_Flag_for_routing,'Y', l_wip_last_u_compl_date2, NULL) -- Bug 4455543
, decode(l_WIP_Flag_for_routing,'Y', nvl(wip_name_for_job_rec.routing_revision, l_wip_routing_revision2) -- Bug 3381547
, NULL) -- Bug 4455543
, nvl(wip_name_for_job_rec.bom_revision, l_wip_bom_revision2) -- Bug 3381547
, wip_name_for_job_rec.primary_item_id
, decode(l_WIP_Flag_for_routing,'Y',wip_name_for_job_rec.primary_item_id, NULL) -- Bug 4455543
--, l_wip_primary_item_id
--, l_wip_primary_item_id
, l_wip_allow_explosion
,wip_name_for_job_rec.alternate_bom_designator --2964588
,wip_name_for_job_rec.alternate_routing_designator --2964588
, l_wip_completion_subinventory -- Bug 5896479
, l_wip_completion_locator_id -- Bug 5896479
, l_wip_header_id
);
IF l_wip_jsi_insert_flag = 0
THEN
l_wip_jsi_insert_flag := 1;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_header_id
FROM DUAL; --fix bug 5667398 cannot have duplicate header id from same group id
INSERT INTO wip_job_schedule_interface
(
last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_id
, program_application_id
, program_update_date
, group_id
, organization_id
, load_type
, status_type
, primary_item_id
, bom_revision_date
, routing_revision_date
, job_name
, process_phase
, process_status
, last_unit_completion_date
, routing_revision
, bom_revision
, bom_reference_id
, routing_reference_id
, allow_explosion
, alternate_bom_designator
, alternate_routing_designator
, completion_subinventory
, completion_locator_id
, header_id
)
values
(
sysdate
, userid
, sysdate
, userid
, loginid
, reqstid
, progid
, appid
, sysdate
, l_wip_group_id1
--, l_wip_organization_id --3412747
, wip_name_for_lot_rec.organization_id
, 3 --l_wip_load_type
, l_wip_status_type
, wip_name_for_lot_rec.primary_item_id
-- , l_wip_primary_item_id
, l_wip_bom_revision_date2
, decode(l_WIP_Flag_for_routing,'Y',l_wip_routing_revision_date2, null) -- Bug 4455543
, l_wip_job_name2
, l_wip_process_phase
, l_wip_process_status
, decode(l_WIP_Flag_for_routing,'Y',l_wip_last_u_compl_date2, null) -- Bug 4455543
, decode(l_WIP_Flag_for_routing,'Y',nvl(wip_name_for_lot_rec.routing_revision, l_wip_routing_revision2) -- Bug 3381547
, null) -- Bug 4455543
, nvl(wip_name_for_lot_rec.bom_revision, l_wip_bom_revision2) -- Bug 3381547
, wip_name_for_lot_rec.primary_item_id
, decode(l_WIP_Flag_for_routing,'Y',wip_name_for_lot_rec.primary_item_id, null) -- Bug 4455543
-- , l_wip_primary_item_id
-- , l_wip_primary_item_id
, l_wip_allow_explosion
,wip_name_for_lot_rec.alternate_bom_designator --2964588
,wip_name_for_lot_rec.alternate_routing_designator --2964588
, l_wip_completion_subinventory -- Bug 5896479
, l_wip_completion_locator_id -- Bug 5896479
, l_wip_header_id
);
IF l_wip_jsi_insert_flag = 0
THEN
l_wip_jsi_insert_flag := 1;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_wip_header_id
FROM DUAL; --fix bug 5667398 cannot have duplicate header id from same group id
INSERT INTO wip_job_schedule_interface
(
last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_id
, program_application_id
, program_update_date
, group_id
, organization_id
, load_type
, status_type
, primary_item_id
, bom_revision_date
, routing_revision_date
, job_name
, process_phase
, process_status
, last_unit_completion_date
, routing_revision
, bom_revision
, bom_reference_id
, routing_reference_id
, allow_explosion
, alternate_bom_designator
, alternate_routing_designator
, completion_subinventory
, completion_locator_id
, header_id
)
values
(
sysdate
, userid
, sysdate
, userid
, loginid
, reqstid
, progid
, appid
, sysdate
, l_wip_group_id1
--, l_wip_organization_id --3412747
, wip_name_for_common_rec.organization_id
, 3 --l_wip_load_type
, l_wip_status_type
, wip_name_for_common_rec.primary_item_id
--, l_wip_primary_item_id
, l_wip_bom_revision_date2
, decode(l_WIP_Flag_for_routing,'Y',l_wip_routing_revision_date2, null) -- Bug 4455543
, l_wip_job_name2
, l_wip_process_phase
, l_wip_process_status
, decode(l_WIP_Flag_for_routing,'Y',l_wip_last_u_compl_date2, null) -- Bug 4455543
, decode(l_WIP_Flag_for_routing,'Y', nvl(wip_name_for_common_rec.routing_revision,l_wip_routing_revision2) -- Bug 3381547
, null) -- Bug 4455543
, nvl(wip_name_for_common_rec.bom_revision,l_wip_bom_revision2) -- Bug 3381547
, wip_name_for_common_rec.primary_item_id
, decode(l_WIP_Flag_for_routing,'Y',wip_name_for_common_rec.primary_item_id, null) -- Bug 4455543
--, l_wip_primary_item_id
--, l_wip_primary_item_id
, l_wip_allow_explosion
,wip_name_for_common_rec.alternate_bom_designator --2964588
,wip_name_for_common_rec.alternate_routing_designator --2964588
, l_wip_completion_subinventory -- Bug 5896479
, l_wip_completion_locator_id -- Bug 5896479
, l_wip_header_id
);
IF l_wip_jsi_insert_flag = 0
THEN
l_wip_jsi_insert_flag := 1;
END IF; -- end of item.update_wip = 1 and NVL...
IF l_wip_jsi_insert_flag = 0
THEN group_id1 := -1; --- reset out type value
/* DELETE FROM bom_reference_designators
WHERE component_sequence_id IN
( SELECT component_sequence_id
FROM bom_inventory_components
WHERE
--fixed for bug 1870813
revised_item_sequence_id = p_revised_item_sequence_id
) ;
/* DELETE FROM bom_substitute_components
WHERE component_sequence_id IN
( SELECT component_sequence_id
FROM bom_inventory_components
-- fixed for bug 1870813
WHERE revised_item_sequence_id = p_revised_item_sequence_id
) ;
UPDATE bom_components_b--bom_inventory_components
SET
disable_date = rev_comp_disable_date_tbl(i).disable_date,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
WHERE component_sequence_id = rev_comp_disable_date_tbl(i).component_seq_id;
DELETE FROM bom_components_b
WHERE implementation_date IS NULL -- as pending changes were copied to the new component
AND (bill_sequence_id, old_component_sequence_id) IN
(SELECT bsb.bill_sequence_id, rbcb.component_sequence_id
FROM bom_components_b rbcb, bom_structures_b bsb
WHERE bsb.bill_sequence_id <> p_bill_sequence_id
AND bsb.source_bill_sequence_id = p_bill_sequence_id
AND rbcb.bill_sequence_id = bsb.bill_sequence_id
AND rbcb.revised_item_sequence_id = p_revised_item_sequence_id);
DELETE FROM bom_components_b--bom_inventory_components
WHERE revised_item_sequence_id = p_revised_item_sequence_id ;
/* DELETE FROM eng_revised_components
WHERE bill_sequence_id = p_bill_sequence_id
AND eco_for_production = 1;
/* DELETE FROM bom_sub_operation_resources
WHERE operation_sequence_id IN
( SELECT operation_sequence_id
FROM bom_operation_sequences
WHERE revised_item_sequence_id = p_revised_item_sequence_id
);
DELETE FROM bom_operation_resources
WHERE operation_sequence_id IN
( SELECT operation_sequence_id
FROM bom_operation_sequences
WHERE revised_item_sequence_id = p_revised_item_sequence_id
);
UPDATE bom_operation_sequences
SET
disable_date = rev_op_disable_date_tbl(i).disable_date,
last_update_date = sysdate,
last_updated_by = userid,
last_update_login = loginid,
request_id = reqstid,
program_application_id = appid,
program_id = progid,
program_update_date = sysdate
WHERE operation_sequence_id = rev_op_disable_date_tbl(i).operation_seq_id;
DELETE FROM bom_operation_sequences
WHERE revised_item_sequence_id = p_revised_item_sequence_id;
/* DELETE FROM eng_revised_operations
WHERE routing_sequence_id = p_routing_sequence_id
AND eco_for_production = 1;
SELECT '1'
FROM DUAL
WHERE NOT EXISTS
(SELECT 1
FROM WIP_ENTITIES
WHERE organization_id = l_organization_id
AND wip_entity_name = l_wip_entity_name)
;
update_wip OUT NOCOPY eng_revised_items.update_wip%type ,
group_id1 OUT NOCOPY wip_job_schedule_interface.group_id%type,
group_id2 OUT NOCOPY wip_job_schedule_interface.group_id%type,
wip_job_name1 OUT NOCOPY wip_entities.wip_entity_name%type,
wip_job_name2 OUT NOCOPY wip_entities.wip_entity_name%type,
wip_job_name2_org_id OUT NOCOPY wip_entities.organization_id%type,
message_names OUT NOCOPY NameArray,
token1 OUT NOCOPY NameArray,
value1 OUT NOCOPY StringArray,
translate1 OUT NOCOPY BooleanArray,
token2 OUT NOCOPY NameArray,
value2 OUT NOCOPY StringArray,
translate2 OUT NOCOPY BooleanArray,
msg_qty in OUT NOCOPY binary_integer,
warnings in OUT NOCOPY number) is
l_is_revised_item_change NUMBER;
SELECT *
FROM eng_revised_items
WHERE (revised_item_sequence_id = revised_item
/*OR parent_revised_item_seq_id = revised_item*/)
AND status_type <> 5; -- to remove cancelled revised items
SELECT change_id
INTO l_change_id
FROM eng_revised_items
WHERE revised_item_sequence_id = revised_item;
SELECT nvl(plm_or_erp_change, 'PLM')
INTO l_plm_or_erp_change
FROM eng_engineering_changes
WHERE change_id = l_change_id;
SELECT els1.status_code
INTO l_status_code
FROM eng_lifecycle_statuses els1
WHERE els1.entity_id1 = l_change_id
AND els1.entity_name = 'ENG_CHANGE'
AND els1.active_flag = 'Y' -- added for bug 3553682
AND els1.sequence_number = (SELECT max(els2.sequence_number)
FROM eng_lifecycle_statuses els2
WHERE els2.entity_id1 = l_change_id
AND els2.entity_name = 'ENG_CHANGE'
AND els2.active_flag = 'Y'); -- added for bug 3553682
select approval_status_type into l_approval_status
from eng_engineering_changes
where change_id = l_change_id;
, update_wip => update_wip
, group_id1 => group_id1
, group_id2 => group_id2
, wip_job_name1 => wip_job_name1
, wip_job_name2 => wip_job_name2
, wip_job_name2_org_id => wip_job_name2_org_id
, message_names => message_names
, token1 => token1
, value1 => value1
, translate1 => translate1
, token2 => token2
, value2 => value2
, translate2 => translate2
, msg_qty => msg_qty
, warnings => warnings
, p_is_lifecycle_phase_change => l_is_revised_item_change
, p_now => l_now
, p_status_code => l_status_code) ;
select change_notice,change_id,organization_id,
nvl(plm_or_erp_change, 'PLM') l_plm_or_erp_change,
status_code curr_status_code
from eng_engineering_changes e
where to_char(e.organization_id) = temp_organization_id
AND ((p_change_notice IS NULL and e.STATUS_TYPE = 4 ) -- scheduled
OR (p_change_notice IS NOT NULL AND E.CHANGE_NOTICE = p_change_notice))
AND e.APPROVAL_STATUS_TYPE <> 4 --eco rejected
and e.status_type not in (5,6)
and not exists (select 1 from eng_revised_items r
where r.change_notice = e.change_notice
and r.organization_id = e.organization_id
and r.status_type not in (5,6))
and exists (select 1 from eng_revised_items r1
where r1.change_notice = e.change_notice
and r1.organization_id = e.organization_id
and r1.status_type = 6);
UPDATE
ENG_ENGINEERING_CHANGES
SET STATUS_TYPE = 6,
STATUS_CODE=6,
IMPLEMENTATION_DATE = SYSDATE,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id,
REQUEST_ID = fnd_global.conc_request_id,
PROGRAM_APPLICATION_ID = fnd_global.prog_appl_id,
PROGRAM_ID = fnd_global.conc_program_id,
PROGRAM_UPDATE_DATE = SYSDATE,
promote_status_code = null
WHERE CHANGE_NOTICE = IMPL.change_notice
AND ORGANIZATION_ID = IMPL.organization_id;
UPDATE
eng_lifecycle_statuses
SET start_date = nvl(start_date,sysdate), -- set the start date on implemented phase after promoting the header to implemented phase
completion_date = sysdate,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE entity_name = 'ENG_CHANGE'
AND entity_id1 =
(
SELECT
change_id
FROM eng_engineering_changes
WHERE organization_id = IMPL.organization_id
AND change_notice = IMPL.change_notice
)
AND active_flag = 'Y'
AND sequence_number =
(
SELECT
max(sequence_number)
FROM eng_lifecycle_statuses
WHERE entity_name = 'ENG_CHANGE'
AND entity_id1 =
(
SELECT
change_id
FROM eng_engineering_changes
WHERE organization_id = IMPL.organization_id
AND change_notice = IMPL.change_notice
) );