[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE eng_revised_items
SET implementation_req_id = x_request_id
WHERE revised_item_sequence_id = p_rev_item_seq_id;
UPDATE eng_engineering_changes
SET implementation_req_id = x_request_id
WHERE change_notice = p_change_notice
AND organization_id = p_org_id;
Select count(*)
Into l_dummy_counter
From Eng_Revised_Items eri
Where eri.change_id = p_change_id
And eri.status_type not in ( 5, -- CANCELLED
6, -- IMPLEMENTED
9, -- IMPLEMENTATION_IN_PROGRESS
2 -- HOLD
)
And exists (
Select null
From mtl_system_items msi
Where msi.inventory_item_id = eri.revised_item_id
And msi.organization_id = eri.organization_Id
And msi.bom_item_type in (X_Model, X_OptionClass, X_Planning, X_Standard)
And rownum = 1
)
And rownum = 1;
SELECT bcb.component_sequence_id
FROM bom_components_b bcb
WHERE bcb.CHANGE_NOTICE = cp_change_notice
AND exists
(select 'x' from bom_bill_of_materials
where bill_sequence_id = bcb.bill_sequence_id
and organization_id = x_organization_id )
AND (bcb.common_component_sequence_id IS NULL
OR bcb.common_component_sequence_id = bcb.component_sequence_id)
AND bcb.IMPLEMENTATION_DATE IS NULL;
SELECT change_notice, organization_id
INTO x_change_notice, x_organization_id
FROM eng_engineering_changes
WHERE change_id = p_change_id;
Update Eng_Revised_Items eri
Set eri.scheduled_date = p_effectivity_date,
eri.last_update_date = sysdate,
eri.last_updated_by = x_user_id,
eri.last_update_login = x_login_id
Where eri.change_id = p_change_id
And eri.status_type not in ( 5, -- CANCELLED
6, -- IMPLEMENTED
9, -- IMPLEMENTATION_IN_PROGRESS
2 -- HOLD
)
And exists (
Select null
From mtl_system_items msi
Where msi.inventory_item_id = eri.revised_item_id
And msi.organization_id = eri.organization_Id
And msi.bom_item_type in (X_Model, X_OptionClass, X_Planning, X_Standard)
);
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Eng_Revised_Items updated ... ' );
Insert into Eng_Current_Scheduled_Dates(
change_id,
change_notice,
organization_id,
revised_item_id,
scheduled_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
schedule_id,
employee_id,
comments,
revised_item_sequence_id)
Select p_change_id,
eri.change_notice,
eri.organization_id,
eri.revised_item_id,
p_effectivity_date,
sysdate,
x_user_id,
sysdate,
x_user_id,
x_login_id,
eng_current_scheduled_dates_s.nextval,
p_requestor_id,
substr(p_comment, 1, 240),
eri.revised_item_sequence_id
From eng_revised_items eri,
mtl_system_items msi
Where eri.change_id = p_change_id
And eri.revised_item_id = msi.inventory_item_id
And eri.organization_id = msi.organization_id
And eri.status_type not in ( 5, -- CANCELLED
6, -- IMPLEMENTED
9, -- IMPLEMENTATION_IN_PROGRESS
2 -- HOLD
)
And msi.bom_item_type in
(X_Model, X_OptionClass, X_Planning, X_Standard);
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Eng_Current_Scheduled_Dates inserted ... ' );
UPDATE BOM_INVENTORY_COMPONENTS bic
SET bic.EFFECTIVITY_DATE = p_effectivity_date
WHERE bic.CHANGE_NOTICE = x_change_notice
AND (bic.common_component_sequence_id IS NULL
OR bic.common_component_sequence_id = bic.component_sequence_id)
-- This is to ensure that the destination bill's revised item
-- reschedule doesnt affect its components effectivity date
AND exists
(select 'x' from bom_bill_of_materials
where bill_sequence_id = bic.bill_sequence_id
and organization_id = x_organization_id )
AND bic.IMPLEMENTATION_DATE IS NULL;
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_INVENTORY_COMPONENTS.EFFECTIVITY_DATE updated ... ' );
UPDATE BOM_INVENTORY_COMPONENTS bic1
SET bic1.DISABLE_DATE = p_effectivity_date
WHERE bic1.CHANGE_NOTICE = x_change_notice
AND bic1.ACD_TYPE = 3 -- ACD Type: Disable
AND exists
(select 'x' from bom_bill_of_materials
where bill_sequence_id = bic1.bill_sequence_id
and organization_id = x_organization_id )
AND bic1.IMPLEMENTATION_DATE IS NULL;
BOMPCMBM.Update_Related_Components(
p_src_comp_seq_id => c_sc.component_sequence_id
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_return_status);
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_INVENTORY_COMPONENTS.DISABLE_DATE updated ... ' );
UPDATE BOM_OPERATION_SEQUENCES bos
SET bos.EFFECTIVITY_DATE = p_effectivity_date
WHERE bos.CHANGE_NOTICE = x_change_notice
AND exists
(select 'x' from bom_operational_routings
where routing_sequence_id = bos.routing_sequence_id
and organization_id = x_organization_id )
AND bos.IMPLEMENTATION_DATE IS NULL;
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_OPERATION_SEQUENCES.EFFECTIVITY_DATE updated ... ' );
UPDATE BOM_OPERATION_SEQUENCES bos1
SET bos1.DISABLE_DATE = p_effectivity_date
WHERE bos1.CHANGE_NOTICE = x_change_notice
and bos1.ACD_TYPE = 3 -- ACD Type: Disable
AND exists
(select 'x' from bom_operational_routings
where routing_sequence_id = bos1.routing_sequence_id
and organization_id = x_organization_id )
AND bos1.IMPLEMENTATION_DATE IS NULL;
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_OPERATION_SEQUENCES.DISABLE_DATE updated ... ' );
UPDATE MTL_ITEM_REVISIONS_B
SET effectivity_date = p_effectivity_date,
last_update_date = sysdate
WHERE change_notice = x_change_notice
AND organization_id = x_organization_id
AND implementation_date is NULL
AND (revised_item_sequence_id, revision_id) in (SELECT revised_item_sequence_id, new_item_revision_id
FROM eng_revised_items eri
WHERE change_id = p_change_id
AND scheduled_date = p_effectivity_date
AND new_item_revision is NOT NULL
AND status_type not in ( 5, -- CANCELLED
6, -- IMPLEMENTED
9, -- IMPLEMENTATION_IN_PROGRESS
2 -- HOLD
)
AND exists (SELECT null
FROM mtl_system_items msi
WHERE msi.inventory_item_id = eri.revised_item_id
AND msi.organization_id = eri.organization_Id
AND msi.bom_item_type in (X_Model, X_OptionClass,
X_Planning, X_Standard)));
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('MTL_ITEM_REVISIONS updated ... ' );
UPDATE MTL_RTG_ITEM_REVISIONS
SET effectivity_date = p_effectivity_date,
last_update_date = sysdate
WHERE change_notice = x_change_notice
AND organization_id = x_organization_id
AND implementation_date is NULL
AND revised_item_sequence_id in (SELECT revised_item_sequence_id
FROM eng_revised_items eri
WHERE change_id = p_change_id
AND scheduled_date = p_effectivity_date
AND new_routing_revision is NOT NULL
AND status_type not in ( 5, -- CANCELLED
6, -- IMPLEMENTED
9, -- IMPLEMENTATION_IN_PROGRESS
2 -- HOLD
)
AND exists (SELECT null
FROM mtl_system_items msi
WHERE msi.inventory_item_id = eri.revised_item_id
AND msi.organization_id = eri.organization_Id
AND msi.bom_item_type in (X_Model, X_OptionClass,
X_Planning, X_Standard)));
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('MTL_RTG_ITEM_REVISIONS updated ... ' );
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('ENG_REVISED_ITEMS rows NOT found. NO updates. ' );
SELECT bcb.component_sequence_id
FROM bom_components_b bcb
WHERE bcb.CHANGE_NOTICE = cp_change_notice
AND exists
(select 'x' from bom_bill_of_materials
where bill_sequence_id = bcb.bill_sequence_id
and organization_id = x_organization_id )
AND (bcb.common_component_sequence_id IS NULL
OR bcb.common_component_sequence_id = bcb.component_sequence_id)
AND bcb.IMPLEMENTATION_DATE IS NULL;
SELECT change_notice, organization_id
INTO x_change_notice, x_organization_id
FROM eng_engineering_changes
WHERE change_id = p_change_id;
Update Eng_Revised_Items eri
Set eri.scheduled_date = p_effectivity_date,
eri.last_update_date = sysdate,
eri.last_updated_by = x_user_id,
eri.last_update_login = x_login_id
Where eri.change_id = p_change_id
And eri.status_type not in ( 5, -- CANCELLED
6, -- IMPLEMENTED
9, -- IMPLEMENTATION_IN_PROGRESS
2 -- HOLD
)
And exists (
Select null
From mtl_system_items msi
Where msi.inventory_item_id = eri.revised_item_id
And msi.organization_id = eri.organization_Id
And msi.bom_item_type in (X_Model, X_OptionClass, X_Planning, X_Standard)
);
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Eng_Revised_Items updated ... ' );
UPDATE BOM_INVENTORY_COMPONENTS bic
SET bic.EFFECTIVITY_DATE = p_effectivity_date
WHERE bic.CHANGE_NOTICE = x_change_notice
AND (bic.common_component_sequence_id IS NULL
OR bic.common_component_sequence_id = bic.component_sequence_id)
-- This is to ensure that the destination bill's revised item
-- reschedule doesnt affect its components effectivity date
AND exists
(select 'x' from bom_bill_of_materials
where bill_sequence_id = bic.bill_sequence_id
and organization_id = x_organization_id )
AND bic.IMPLEMENTATION_DATE IS NULL;
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_INVENTORY_COMPONENTS.EFFECTIVITY_DATE updated ... ' );
UPDATE BOM_INVENTORY_COMPONENTS bic1
SET bic1.DISABLE_DATE = p_effectivity_date
WHERE bic1.CHANGE_NOTICE = x_change_notice
AND bic1.ACD_TYPE = 3 -- ACD Type: Disable
AND exists
(select 'x' from bom_bill_of_materials
where bill_sequence_id = bic1.bill_sequence_id
and organization_id = x_organization_id )
AND bic1.IMPLEMENTATION_DATE IS NULL;
BOMPCMBM.Update_Related_Components(
p_src_comp_seq_id => c_sc.component_sequence_id
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_return_status);
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_INVENTORY_COMPONENTS.DISABLE_DATE updated ... ' );
UPDATE BOM_OPERATION_SEQUENCES bos
SET bos.EFFECTIVITY_DATE = p_effectivity_date
WHERE bos.CHANGE_NOTICE = x_change_notice
AND exists
(select 'x' from bom_operational_routings
where routing_sequence_id = bos.routing_sequence_id
and organization_id = x_organization_id )
AND bos.IMPLEMENTATION_DATE IS NULL;
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_OPERATION_SEQUENCES.EFFECTIVITY_DATE updated ... ' );
UPDATE BOM_OPERATION_SEQUENCES bos1
SET bos1.DISABLE_DATE = p_effectivity_date
WHERE bos1.CHANGE_NOTICE = x_change_notice
and bos1.ACD_TYPE = 3 -- ACD Type: Disable
AND exists
(select 'x' from bom_operational_routings
where routing_sequence_id = bos1.routing_sequence_id
and organization_id = x_organization_id )
AND bos1.IMPLEMENTATION_DATE IS NULL;
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_OPERATION_SEQUENCES.DISABLE_DATE updated ... ' );
UPDATE MTL_ITEM_REVISIONS_B
SET effectivity_date = p_effectivity_date,
last_update_date = sysdate
WHERE change_notice = x_change_notice
AND organization_id = x_organization_id
AND implementation_date is NULL
AND (revised_item_sequence_id, revision_id) in (SELECT revised_item_sequence_id, new_item_revision_id
FROM eng_revised_items eri
WHERE change_id = p_change_id
AND scheduled_date = p_effectivity_date
AND new_item_revision is NOT NULL
AND status_type not in ( 5, -- CANCELLED
6, -- IMPLEMENTED
9, -- IMPLEMENTATION_IN_PROGRESS
2 -- HOLD
)
AND exists (SELECT null
FROM mtl_system_items msi
WHERE msi.inventory_item_id = eri.revised_item_id
AND msi.organization_id = eri.organization_Id
AND msi.bom_item_type in (X_Model, X_OptionClass,
X_Planning, X_Standard)));
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('MTL_ITEM_REVISIONS updated ... ' );
UPDATE MTL_RTG_ITEM_REVISIONS
SET effectivity_date = p_effectivity_date,
last_update_date = sysdate
WHERE change_notice = x_change_notice
AND organization_id = x_organization_id
AND implementation_date is NULL
AND revised_item_sequence_id in (SELECT revised_item_sequence_id
FROM eng_revised_items eri
WHERE change_id = p_change_id
AND scheduled_date = p_effectivity_date
AND new_routing_revision is NOT NULL
AND status_type not in ( 5, -- CANCELLED
6, -- IMPLEMENTED
9, -- IMPLEMENTATION_IN_PROGRESS
2 -- HOLD
)
AND exists (SELECT null
FROM mtl_system_items msi
WHERE msi.inventory_item_id = eri.revised_item_id
AND msi.organization_id = eri.organization_Id
AND msi.bom_item_type in (X_Model, X_OptionClass,
X_Planning, X_Standard)));
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('MTL_RTG_ITEM_REVISIONS updated ... ' );
ENG_CHANGE_ACTIONS_UTIL.Write_Debug('ENG_REVISED_ITEMS rows NOT found. NO updates. ' );
DELETE FROM BOM_SUB_OPERATION_RESOURCES sor
WHERE EXISTS (SELECT NULL
FROM BOM_OPERATION_SEQUENCES bos
, ENG_REVISED_ITEMS ri
WHERE sor.operation_sequence_id = bos.operation_sequence_id
AND bos.implementation_date IS NULL
AND bos.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.status_type = 5 -- Cancelled
AND ri.organization_id = p_org_id
AND ri.change_notice = p_eco_name
) ;
DELETE FROM BOM_OPERATION_RESOURCES bor
WHERE EXISTS (SELECT NULL
FROM BOM_OPERATION_SEQUENCES bos
, ENG_REVISED_ITEMS ri
WHERE bor.operation_sequence_id = bos.operation_sequence_id
AND bos.implementation_date IS NULL
AND bos.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.status_type = 5 -- Cancelled
AND ri.organization_id = p_org_id
AND ri.change_notice = p_eco_name
) ;
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_coordinate
, y_coordinate
, include_in_rollup
, operation_yield_enabled
, change_notice
, implementation_date
, old_operation_sequence_id
, acd_type
, revised_item_sequence_id
, cancellation_date
, cancel_comments
, original_system_reference )
SELECT
bos.OPERATION_SEQUENCE_ID
, bos.ROUTING_SEQUENCE_ID
, bos.OPERATION_SEQ_NUM
, SYSDATE -- Last Update Date
, p_user_id -- Last Updated By
, SYSDATE -- Creation Date
, p_user_id -- Created By
, p_login_id -- Last Update Login
, bos.STANDARD_OPERATION_ID
, bos.DEPARTMENT_ID
, bos.OPERATION_LEAD_TIME_PERCENT
, bos.MINIMUM_TRANSFER_QUANTITY
, bos.COUNT_POINT_TYPE
, bos.OPERATION_DESCRIPTION
, bos.EFFECTIVITY_DATE
, bos.DISABLE_DATE
, bos.BACKFLUSH_FLAG
, bos.OPTION_DEPENDENT_FLAG
, bos.ATTRIBUTE_CATEGORY
, bos.ATTRIBUTE1
, bos.ATTRIBUTE2
, bos.ATTRIBUTE3
, bos.ATTRIBUTE4
, bos.ATTRIBUTE5
, bos.ATTRIBUTE6
, bos.ATTRIBUTE7
, bos.ATTRIBUTE8
, bos.ATTRIBUTE9
, bos.ATTRIBUTE10
, bos.ATTRIBUTE11
, bos.ATTRIBUTE12
, bos.ATTRIBUTE13
, bos.ATTRIBUTE14
, bos.ATTRIBUTE15
, NULL -- Request Id
, p_prog_appid -- Application Id
, p_prog_id -- Program Id
, SYSDATE -- program_update_date
, bos.OPERATION_TYPE
, bos.REFERENCE_FLAG
, bos.PROCESS_OP_SEQ_ID
, bos.LINE_OP_SEQ_ID
, bos.YIELD
, bos.CUMULATIVE_YIELD
, bos.REVERSE_CUMULATIVE_YIELD
, bos.LABOR_TIME_CALC
, bos.MACHINE_TIME_CALC
, bos.TOTAL_TIME_CALC
, bos.LABOR_TIME_USER
, bos.MACHINE_TIME_USER
, bos.TOTAL_TIME_USER
, bos.NET_PLANNING_PERCENT
, bos.X_COORDINATE
, bos.Y_COORDINATE
, bos.INCLUDE_IN_ROLLUP
, bos.OPERATION_YIELD_ENABLED
, bos.CHANGE_NOTICE
, bos.IMPLEMENTATION_DATE
, bos.OLD_OPERATION_SEQUENCE_ID
, bos.ACD_TYPE
, bos.REVISED_ITEM_SEQUENCE_ID
, SYSDATE -- Cancellation Date
, substr(p_cancel_comments, 1, 240) -- Cancel Comments
, p_original_system_ref
FROM BOM_OPERATION_SEQUENCES bos
, ENG_REVISED_ITEMS ri
WHERE bos.implementation_date IS NULL
AND bos.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.status_type = 5 -- Cancelled
AND ri.organization_id = p_org_id
AND ri.change_notice = p_eco_name ;
DELETE FROM BOM_OPERATION_SEQUENCES bos
WHERE EXISTS (SELECT NULL
FROM ENG_REVISED_ITEMS ri
WHERE bos.implementation_date IS NULL
AND bos.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.status_type = 5 -- Cancelled
AND ri.organization_id = p_org_id
AND ri.change_notice = p_eco_name
) ;
DELETE FROM MTL_RTG_ITEM_REVISIONS rev
WHERE EXISTS (SELECT NULL
FROM ENG_REVISED_ITEMS ri
WHERE rev.implementation_date IS NULL
AND rev.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.status_type = 5 -- Cancelled
AND ri.organization_id = p_org_id
AND ri.change_notice = p_eco_name
) ;
DELETE FROM BOM_OPERATIONAL_ROUTINGS bor
WHERE EXISTS ( SELECT NULL
FROM ENG_REVISED_ITEMS ri
WHERE bor.routing_sequence_id = ri.change_notice
AND bor.routing_sequence_id = ri.routing_sequence_id
AND TRUNC(ri.last_update_date) = TRUNC(SYSDATE)
AND ri.status_type = 5 -- Cancelled
AND ri.organization_id = p_org_id
AND ri.change_notice = p_eco_name
)
AND NOT EXISTS (SELECT NULL
FROM BOM_OPERATION_SEQUENCES bos
WHERE bos.routing_sequence_id = bor.routing_sequence_id
AND (bos.change_notice IS NULL
OR bos.change_notice <> p_eco_name)
)
AND (( bor.alternate_routing_designator IS NULL
AND NOT EXISTS( SELECT NULL
FROM BOM_OPERATIONAL_ROUTINGS bor2
WHERE bor2.organization_id = bor.organization_id
AND bor2.assembly_item_id = bor.assembly_item_id
AND bor2.alternate_routing_designator IS NOT NULL )
)
OR
( bor.alternate_routing_designator IS NOT NULL
AND NOT EXISTS( SELECT NULL
FROM ENG_REVISED_ITEMS ri2
WHERE ri2.organization_id = bor.organization_id
AND ri2.routing_sequence_id = bor.routing_sequence_id
AND ri2.change_notice <> p_eco_name )
)) ;
UPDATE ENG_REVISED_ITEMS ri
SET routing_sequence_id = ''
, program_id = p_prog_id
, program_application_id = p_prog_appid
, original_system_reference = p_original_system_ref
, last_updated_by = p_user_id
, last_update_login = p_login_id
WHERE ri.organization_id = p_org_id
AND ri.change_notice = p_eco_name
AND ri.status_type = 5 -- Cancelled
AND NOT EXISTS (SELECT 'No Rtg Header'
FROM BOM_OPERATIONAL_ROUTINGS bor
WHERE bor.routing_sequence_id = ri.routing_sequence_id
) ;
UPDATE ENG_REVISED_ITEMS
SET CANCELLATION_DATE = SYSDATE,
STATUS_TYPE = 5,
REQUEST_ID = request_id,
PROGRAM_ID = prog_id,
PROGRAM_APPLICATION_ID = prog_appid,
ORIGINAL_SYSTEM_REFERENCE = orig_sysref,
LAST_UPDATED_BY = user_id,
LAST_UPDATE_LOGIN = login
WHERE ORGANIZATION_ID = org_id
AND CHANGE_NOTICE = change_order
AND STATUS_TYPE NOT IN (5,6);
DELETE FROM BOM_SUBSTITUTE_COMPONENTS SC
WHERE SC.COMPONENT_SEQUENCE_ID IN
(SELECT IC.COMPONENT_SEQUENCE_ID
FROM BOM_INVENTORY_COMPONENTS IC, ENG_REVISED_ITEMS RI
WHERE RI.ORGANIZATION_ID = org_id
AND RI.CHANGE_NOTICE = change_order
AND IC.REVISED_ITEM_SEQUENCE_ID = RI.REVISED_ITEM_SEQUENCE_ID
AND IC.IMPLEMENTATION_DATE IS NULL);
DELETE FROM BOM_REFERENCE_DESIGNATORS RD
WHERE RD.COMPONENT_SEQUENCE_ID IN
(SELECT IC.COMPONENT_SEQUENCE_ID
FROM BOM_INVENTORY_COMPONENTS IC, ENG_REVISED_ITEMS RI
WHERE RI.ORGANIZATION_ID = org_id
AND RI.CHANGE_NOTICE = change_order
AND IC.REVISED_ITEM_SEQUENCE_ID = RI.REVISED_ITEM_SEQUENCE_ID
AND IC.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,
CANCELLATION_DATE,
CANCEL_COMMENTS, -- Added by MK on 09/01/2000
OLD_COMPONENT_SEQUENCE_ID,
ITEM_NUM,
WIP_SUPPLY_TYPE,
COMPONENT_REMARKS,
SUPPLY_SUBINVENTORY,
SUPPLY_LOCATOR_ID,
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,
LOW_QUANTITY,
HIGH_QUANTITY,
REVISED_ITEM_SEQUENCE_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
ORIGINAL_SYSTEM_REFERENCE,
BASIS_TYPE)
SELECT
IC.COMPONENT_SEQUENCE_ID,
IC.COMPONENT_ITEM_ID,
IC.OPERATION_SEQ_NUM,
IC.BILL_SEQUENCE_ID,
IC.CHANGE_NOTICE,
IC.EFFECTIVITY_DATE,
IC.COMPONENT_QUANTITY,
IC. COMPONENT_YIELD_FACTOR,
SYSDATE,
user_id,
SYSDATE,
user_id,
login,
sysdate,
substr(p_cancel_comments, 1, 240), -- Added by MK on 09/01/2000
IC.OLD_COMPONENT_SEQUENCE_ID,
IC.ITEM_NUM,
IC.WIP_SUPPLY_TYPE,
IC.COMPONENT_REMARKS,
IC.SUPPLY_SUBINVENTORY,
IC.SUPPLY_LOCATOR_ID,
IC.DISABLE_DATE,
IC.ACD_TYPE,
IC.PLANNING_FACTOR,
IC.QUANTITY_RELATED,
IC.SO_BASIS,
IC.OPTIONAL,
IC.MUTUALLY_EXCLUSIVE_OPTIONS,
IC.INCLUDE_IN_COST_ROLLUP,
IC.CHECK_ATP,
IC.SHIPPING_ALLOWED,
IC.REQUIRED_TO_SHIP,
IC.REQUIRED_FOR_REVENUE,
IC.INCLUDE_ON_SHIP_DOCS,
IC.LOW_QUANTITY,
IC.HIGH_QUANTITY,
IC.REVISED_ITEM_SEQUENCE_ID,
IC.ATTRIBUTE_CATEGORY,
IC.ATTRIBUTE1,
IC.ATTRIBUTE2,
IC.ATTRIBUTE3,
IC.ATTRIBUTE4,
IC.ATTRIBUTE5,
IC.ATTRIBUTE6,
IC.ATTRIBUTE7,
IC.ATTRIBUTE8,
IC.ATTRIBUTE9,
IC.ATTRIBUTE10,
IC.ATTRIBUTE11,
IC.ATTRIBUTE12,
IC.ATTRIBUTE13,
IC.ATTRIBUTE14,
IC.ATTRIBUTE15,
req_id,
prog_id,
prog_appid,
orig_sysref,
IC.BASIS_TYPE
FROM BOM_INVENTORY_COMPONENTS IC, ENG_REVISED_ITEMS RI
WHERE RI.ORGANIZATION_ID = org_id
AND RI.CHANGE_NOTICE = change_order
AND IC.CHANGE_NOTICE = RI.CHANGE_NOTICE
AND IC.REVISED_ITEM_SEQUENCE_ID = RI.REVISED_ITEM_SEQUENCE_ID
AND RI.BILL_SEQUENCE_ID = IC.BILL_SEQUENCE_ID
AND IC.IMPLEMENTATION_DATE IS NULL;
DELETE FROM BOM_INVENTORY_COMPONENTS IC
WHERE CHANGE_NOTICE = change_order
AND IMPLEMENTATION_DATE IS NULL
AND REVISED_ITEM_SEQUENCE_ID IN (SELECT REVISED_ITEM_SEQUENCE_ID
FROM ENG_REVISED_ITEMS ERI
WHERE ERI.ORGANIZATION_ID = org_id
AND ERI.CHANGE_NOTICE = change_order
AND ERI.STATUS_TYPE = 5);
delete from MTL_ITEM_REVISIONS_TL
where revision_id in(select revision_id
from MTL_ITEM_REVISIONS_B I
WHERE CHANGE_NOTICE = change_order
AND ORGANIZATION_ID = org_id
AND IMPLEMENTATION_DATE IS NULL
AND INVENTORY_ITEM_ID IN
(SELECT REVISED_ITEM_ID
FROM ENG_REVISED_ITEMS R
WHERE R.CHANGE_NOTICE = change_order
AND R.ORGANIZATION_ID = org_id
AND R.REVISED_ITEM_SEQUENCE_ID = I.REVISED_ITEM_SEQUENCE_ID
AND R.CANCELLATION_DATE IS NOT NULL));
DELETE FROM MTL_ITEM_REVISIONS_B I
WHERE CHANGE_NOTICE = change_order
AND ORGANIZATION_ID = org_id
AND IMPLEMENTATION_DATE IS NULL
AND INVENTORY_ITEM_ID IN (SELECT REVISED_ITEM_ID
FROM ENG_REVISED_ITEMS R
WHERE R.CHANGE_NOTICE = change_order
AND R.ORGANIZATION_ID = org_id
AND R.REVISED_ITEM_SEQUENCE_ID = I.REVISED_ITEM_SEQUENCE_ID
AND R.CANCELLATION_DATE IS NOT NULL);
DELETE FROM BOM_BILL_OF_MATERIALS B
WHERE B.BILL_SEQUENCE_ID in (SELECT BILL_SEQUENCE_ID
FROM ENG_REVISED_ITEMS ERI
WHERE ORGANIZATION_ID = org_id
AND CHANGE_NOTICE = change_order
AND STATUS_TYPE = 5
AND TRUNC(LAST_UPDATE_DATE) = trunc(sysdate))
AND B.PENDING_FROM_ECN = change_order
AND NOT EXISTS (SELECT NULL
FROM BOM_INVENTORY_COMPONENTS C
WHERE C.BILL_SEQUENCE_ID = B.BILL_SEQUENCE_ID
AND (C.CHANGE_NOTICE IS NULL
OR C.CHANGE_NOTICE <> change_order))
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 IS NOT NULL))
OR
(B.ALTERNATE_BOM_DESIGNATOR IS NOT NULL
AND NOT EXISTS (SELECT NULL
FROM ENG_REVISED_ITEMS R
WHERE R.ORGANIZATION_ID = B.ORGANIZATION_ID
AND R.BILL_SEQUENCE_ID = B.BILL_SEQUENCE_ID
AND R.CHANGE_NOTICE <> change_order)));
UPDATE ENG_REVISED_ITEMS R
SET BILL_SEQUENCE_ID = '',
REQUEST_ID = request_id,
PROGRAM_ID = prog_id,
PROGRAM_APPLICATION_ID = prog_appid,
ORIGINAL_SYSTEM_REFERENCE = orig_sysref,
LAST_UPDATED_BY = user_id,
LAST_UPDATE_LOGIN = login
WHERE R.ORGANIZATION_ID = org_id
AND R.CHANGE_NOTICE = change_order
AND R.STATUS_TYPE = 5
AND NOT EXISTS (SELECT 'NO SUCH BILL'
FROM BOM_BILL_OF_MATERIALS BOM
WHERE BOM.BILL_SEQUENCE_ID = R.BILL_SEQUENCE_ID);
PROCEDURE Update_Row
( p_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
, p_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_Unexposed_Rec_Type
, p_old_ECO_rec IN ENG_ECO_PUB.ECO_Rec_Type
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status OUT NOCOPY VARCHAR
)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
UPDATE eng_engineering_changes
SET attribute7 = p_eco_rec.attribute7,
attribute8 = p_eco_rec.attribute8,
attribute9 = p_eco_rec.attribute9,
attribute10 = p_eco_rec.attribute10,
attribute11 = p_eco_rec.attribute11,
attribute12 = p_eco_rec.attribute12,
attribute13 = p_eco_rec.attribute13,
attribute14 = p_eco_rec.attribute14,
attribute15 = p_eco_rec.attribute15,
request_id = l_request_id,
program_application_id = l_prog_appid,
program_id = l_prog_id,
approval_status_type = p_unexp_eco_rec.approval_status_type,
approval_date = p_eco_rec.approval_date,
approval_list_id = p_unexp_eco_rec.approval_list_id,
change_order_type_id = p_unexp_eco_rec.change_order_type_id,
responsible_organization_id = p_unexp_eco_rec.responsible_org_id,
approval_request_date = p_eco_rec.approval_request_date,
change_notice = p_eco_rec.eco_name,
organization_id = p_unexp_eco_rec.organization_id,
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_login_id,
description = p_eco_rec.description,
status_type = p_unexp_eco_rec.status_type,
initiation_date = p_unexp_eco_rec.initiation_date,
implementation_date = p_unexp_eco_rec.implementation_date,
cancellation_date = p_unexp_eco_rec.cancellation_date,
cancellation_comments = p_eco_rec.cancellation_comments,
priority_code = p_eco_rec.priority_code,
reason_code = p_eco_rec.reason_code,
estimated_eng_cost = p_eco_rec.eng_implementation_cost,
estimated_mfg_cost = p_eco_rec.mfg_implementation_cost,
requestor_id = p_unexp_eco_rec.requestor_id,
attribute_category = p_eco_rec.attribute_category,
attribute1 = p_eco_rec.attribute1,
attribute2 = p_eco_rec.attribute2,
attribute3 = p_eco_rec.attribute3,
attribute4 = p_eco_rec.attribute4,
attribute5 = p_eco_rec.attribute5,
attribute6 = p_eco_rec.attribute6,
original_system_reference = p_eco_rec.original_system_reference,
project_id = p_unexp_eco_rec.project_id,
task_id = p_unexp_eco_rec.task_id,
organization_hierarchy = p_eco_rec.organization_hierarchy,
change_mgmt_type_code = p_unexp_eco_rec.change_mgmt_type_code, -- eng change,
assignee_id = p_unexp_eco_rec.assignee_id, -- eng chagne,
need_by_date = p_eco_rec.need_by_date, -- eng chagne,
internal_use_only = p_eco_rec.internal_use_only, -- eng chagne,
source_type_code = p_unexp_eco_rec.source_type_code, -- eng chagne,
source_id = p_unexp_eco_rec.source_id, -- eng chagne,
effort = p_eco_rec.effort, -- eng chagne,
hierarchy_id = p_unexp_eco_rec.hierarchy_id, -- eng chagne
-- Bug 2919076 // kamohan
-- Start Changes
change_name = l_change_name , -- Bug 3032565 p_eco_rec.change_name
-- status_code = p_unexp_eco_rec.status_code
status_code = nvl(p_unexp_eco_rec.status_code, p_unexp_eco_rec.status_type), -- Bug 3424007
source_name = p_ECO_rec.Source_Name
-- End Changes
WHERE change_notice = p_eco_rec.eco_name
AND organization_id = p_unexp_eco_rec.organization_id;
l_err_text := G_PKG_NAME || ' : Utility (ECO Update) '
|| substrb(SQLERRM,1,200);
UPDATE eng_revised_items eri
SET eri.status_type = p_Unexp_ECO_rec.status_type,
-- If ECO status is 'Scheduled', set Auto-Implement Date to SYSDATE, else NULL
eri.auto_implement_date = decode(p_Unexp_ECO_rec.status_type, 4, SYSDATE, NULL),
-- If ECO status is Hold, set MRP Active to No, else Yes
eri.mrp_active = decode(p_Unexp_ECO_rec.status_type, 2, 2, 1),
eri.last_update_date = SYSDATE,
eri.last_updated_by = l_user_id,
eri.last_update_login = l_login_id,
eri.request_id = l_request_id,
eri.program_id = l_prog_id,
eri.program_application_id = l_prog_appid,
eri.original_system_reference
= p_ECO_rec.original_system_reference
WHERE eri.change_notice = p_ECO_rec.ECO_name
AND eri.organization_id = p_Unexp_ECO_rec.organization_id
AND eri.status_type not in (5,6) -- Cancelled or Implemented
AND exists
-- modify only those items which the user has access to
(SELECT null
FROM mtl_system_items msi
WHERE msi.inventory_item_id = eri.revised_item_id
AND msi.organization_id = eri.organization_id
AND msi.bom_item_type IN
(l_STD_Item_Access
,l_OC_Item_Access
,l_PLN_Item_Access
,l_MDL_Item_Access));
l_err_text := G_PKG_NAME || ' : Utility (ECO Update) '
|| substrb(SQLERRM,1,200);
END Update_Row;
SELECT eng_lifecycle_statuses_s.nextval
INTO l_lifecycle_phase_id
FROM dual;
insert into ENG_LIFECYCLE_STATUSES (
CHANGE_LIFECYCLE_STATUS_ID
, ENTITY_NAME
, ENTITY_ID1
, ENTITY_ID2
, ENTITY_ID3
, ENTITY_ID4
, ENTITY_ID5
, SEQUENCE_NUMBER
, STATUS_CODE
, START_DATE
, COMPLETION_DATE
, CHANGE_WF_ROUTE_ID
, AUTO_PROMOTE_STATUS
, AUTO_DEMOTE_STATUS
, WORKFLOW_STATUS
, CHANGE_EDITABLE_FLAG
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, ITERATION_NUMBER
, ACTIVE_FLAG
, WF_SIG_POLICY
, CHANGE_WF_ROUTE_TEMPLATE_ID)
values (
l_lifecycle_phase_id
, 'ENG_CHANGE'
, p_change_id
, null
, null
, null
, null
, l_seq_no
, phase_types(lp)
, null
, null
, null
, null
, null
, null
, null
, sysdate
, l_user_id
, sysdate
, l_user_id
, l_login_id
, 0
, 'Y'
, null
, NULL );
PROCEDURE Insert_Row
( p_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
, p_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_Unexposed_Rec_Type
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status OUT NOCOPY VARCHAR
)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
Error_Handler.write_debug('Start to insert');
INSERT INTO ENG_ENGINEERING_CHANGES
( ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, APPROVAL_STATUS_TYPE
, APPROVAL_DATE
, APPROVAL_LIST_ID
, CHANGE_ORDER_TYPE_ID
, RESPONSIBLE_ORGANIZATION_ID
, APPROVAL_REQUEST_DATE
, CHANGE_NOTICE
, ORGANIZATION_ID
, CHANGE_NAME
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, DESCRIPTION
, STATUS_TYPE
, INITIATION_DATE
, IMPLEMENTATION_DATE
, CANCELLATION_DATE
, CANCELLATION_COMMENTS
, PRIORITY_CODE
, REASON_CODE
, ESTIMATED_ENG_COST
, ESTIMATED_MFG_COST
, REQUESTOR_ID
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ORIGINAL_SYSTEM_REFERENCE
, PROJECT_ID
, TASK_ID
, CHANGE_ID
, ORGANIZATION_HIERARCHY
, CHANGE_MGMT_TYPE_CODE
, ASSIGNEE_ID
, NEED_BY_DATE
, INTERNAL_USE_ONLY
, SOURCE_TYPE_CODE
, SOURCE_ID
, EFFORT
, HIERARCHY_ID
, PLM_OR_ERP_CHANGE --11.5.10
, status_code
, Change_Notice_Prefix --11.5.10
, source_name
)
VALUES
( p_ECO_rec.attribute7
, p_ECO_rec.attribute8
, p_ECO_rec.attribute9
, p_ECO_rec.attribute10
, p_ECO_rec.attribute11
, p_ECO_rec.attribute12
, p_ECO_rec.attribute13
, p_ECO_rec.attribute14
, p_ECO_rec.attribute15
, l_request_id
, l_prog_appid
, l_prog_id
, SYSDATE
, p_Unexp_ECO_rec.approval_status_type
, p_ECO_rec.approval_date
, p_Unexp_ECO_rec.approval_list_id
, p_Unexp_ECO_rec.change_order_type_id
, p_Unexp_ECO_rec.responsible_org_id
, p_ECO_rec.approval_request_date
, p_ECO_rec.ECO_name
, p_Unexp_ECO_rec.organization_id
, l_change_name -- Bug 3032565 nvl(p_ECO_rec.change_name, p_ECO_rec.ECO_name)
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, p_ECO_rec.description
, p_Unexp_ECO_rec.status_type
, p_Unexp_ECO_rec.initiation_date
, p_Unexp_ECO_rec.implementation_date
, p_Unexp_ECO_rec.cancellation_date
, p_ECO_rec.cancellation_comments
, p_ECO_rec.priority_code
, p_ECO_rec.reason_code
, p_ECO_rec.ENG_implementation_cost
, p_ECO_rec.MFG_implementation_Cost
, p_Unexp_ECO_rec.requestor_id
, p_ECO_rec.attribute_category
, p_ECO_rec.attribute1
, p_ECO_rec.attribute2
, p_ECO_rec.attribute3
, p_ECO_rec.attribute4
, p_ECO_rec.attribute5
, p_ECO_rec.attribute6
, p_ECO_rec.original_system_reference
, p_Unexp_ECO_rec.project_id
, p_Unexp_ECO_rec.task_id
, p_Unexp_ECO_rec.change_id
, p_ECO_rec.organization_hierarchy
, p_Unexp_ECO_rec.change_mgmt_type_code -- Eng Change
, p_Unexp_ECO_rec.assignee_id -- Eng Change
, p_ECO_rec.need_by_date -- Eng Chagne
, p_ECO_rec.internal_use_only -- Eng Chagne
, p_Unexp_ECO_rec.source_type_code -- Eng Chagne
, p_Unexp_ECO_rec.source_id -- Eng Chagne
, p_ECO_rec.effort -- Eng Chagne
, p_Unexp_ECO_rec.hierarchy_id -- Eng Chagne
, p_Eco_rec.Plm_Or_Erp_Change --11.5.10
, p_Unexp_ECO_rec.status_code
, NULL --l_change_name --Bug 3570162
, p_ECO_rec.Source_Name
);
Error_Handler.write_debug('right after insert');
ENG_CHANGE_TEXT_UTIL.Insert_Update_Change ( p_change_id => p_Unexp_ECO_rec.change_id );
Error_Handler.write_debug('right after ENG_CHANGE_TEXT_UTIL.Insert_Update_Change');
Error_Handler.write_debug('error in insert');
l_err_text := G_PKG_NAME || ' : Utility (ECO Insert) '
|| substrb(SQLERRM,1,200);
END Insert_Row;
PROCEDURE Delete_Row
( p_change_notice IN VARCHAR2
, p_organization_id IN NUMBER
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status OUT NOCOPY VARCHAR
)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
DELETE FROM ENG_ENGINEERING_CHANGES
WHERE CHANGE_NOTICE = p_change_notice
AND ORGANIZATION_ID = p_organization_id;
l_err_text := G_PKG_NAME || ' : Utility (ECO Delete) '
|| substrb(SQLERRM,1,200);
DELETE FROM ENG_CHANGE_ORDER_REVISIONS
WHERE CHANGE_NOTICE = p_change_notice
AND ORGANIZATION_ID = p_organization_id;
l_err_text := G_PKG_NAME || ' : Utility (ECO Revisions Delete) '
|| substrb(SQLERRM,1,200);
DELETE FROM ENG_ECO_SUBMIT_REVISIONS
WHERE CHANGE_NOTICE = p_change_notice
AND ORGANIZATION_ID = p_organization_id;
( p_Message_Name => 'ENG_ECO_APP_HISTORY_DELETED'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
l_err_text := G_PKG_NAME || ' : Utility (Approval History Delete) '
|| substrb(SQLERRM,1,200);
END Delete_Row;
SELECT status_code ,sequence_number , name
FROM eng_change_lines_vl
WHERE eng_change_lines_vl.change_id = p_change_id
and sequence_number<> -1;
Insert_Row
( p_ECO_rec => p_ECO_rec
, p_Unexp_ECO_rec => p_Unexp_ECO_rec
, x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
, x_return_status => x_return_status
);
Error_Handler.write_debug('end of insert row..');
ELSIF p_ECO_rec.transaction_type = 'UPDATE'
THEN
--change the status of all open lines bug:5414834
if( p_Unexp_ECO_rec.status_type = 11 OR p_Unexp_ECO_rec.status_type = 5 ) then
FOR line_rec IN lines_for_eco(p_Unexp_ECO_rec.Change_Id)
LOOP
UPDATE eng_change_lines SET status_code = p_Unexp_ECO_rec.status_type
WHERE status_code=1 AND change_id = p_Unexp_ECO_rec.Change_Id;
Update_Row
( p_ECO_rec => p_ECO_rec
, p_Unexp_ECO_rec => p_Unexp_ECO_rec
, p_old_ECO_rec => p_old_ECO_rec
, x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
, x_return_status => x_return_status
);
ENG_CHANGE_BES_UTIL.Raise_Update_Change_Event
( p_change_id => p_Unexp_ECO_rec.change_id
);
Error_Handler.write_debug('Raised PLM CM update event ..');
Error_Handler.write_debug('end of update row..');
ELSIF p_ECO_rec.transaction_type = 'DELETE'
THEN
Delete_Row
( p_change_notice => p_ECO_rec.ECO_name
, p_organization_id => p_Unexp_ECO_rec.organization_id
, x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
, x_return_status => x_return_status
);
Error_Handler.write_debug('end of delete row..');
SELECT ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, APPROVAL_STATUS_TYPE
, APPROVAL_DATE
, APPROVAL_LIST_ID
, CHANGE_ORDER_TYPE_ID
, RESPONSIBLE_ORGANIZATION_ID
, APPROVAL_REQUEST_DATE
, CHANGE_NOTICE
, ORGANIZATION_ID
, DESCRIPTION
, STATUS_CODE
, STATUS_TYPE
, INITIATION_DATE
, IMPLEMENTATION_DATE
, CANCELLATION_DATE
, CANCELLATION_COMMENTS
, PRIORITY_CODE
, REASON_CODE
, ESTIMATED_ENG_COST
, ESTIMATED_MFG_COST
, REQUESTOR_ID
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, PROJECT_ID
, TASK_ID
, CHANGE_ID
, ORGANIZATION_HIERARCHY
, CHANGE_MGMT_TYPE_CODE -- Eng Change
, ASSIGNEE_ID -- Eng Change
, NEED_BY_DATE -- Eng Chagne
, INTERNAL_USE_ONLY -- Eng Chagne
, SOURCE_TYPE_CODE -- Eng Chagne
, SOURCE_ID -- Eng Change
, EFFORT -- Eng Change
INTO l_ECO_rec.attribute7
, l_ECO_rec.attribute8
, l_ECO_rec.attribute9
, l_ECO_rec.attribute10
, l_ECO_rec.attribute11
, l_ECO_rec.attribute12
, l_ECO_rec.attribute13
, l_ECO_rec.attribute14
, l_ECO_rec.attribute15
, l_ECO_Unexp_rec.approval_status_type
, l_ECO_rec.approval_date
, l_ECO_Unexp_rec.approval_list_id
, l_ECO_Unexp_rec.change_order_type_id
, l_ECO_Unexp_rec.responsible_org_id
, l_ECO_rec.approval_request_date
, l_ECO_rec.ECO_Name
, l_ECO_Unexp_rec.organization_id
, l_ECO_rec.description
, l_ECO_Unexp_rec.status_code
, l_ECO_Unexp_rec.status_type
, l_ECO_Unexp_rec.initiation_date
, l_ECO_Unexp_rec.implementation_date
, l_ECO_Unexp_rec.cancellation_date
, l_ECO_rec.cancellation_comments
, l_ECO_rec.priority_code
, l_ECO_rec.reason_code
, l_ECO_rec.ENG_implementation_cost
, l_ECO_rec.MFG_implementation_cost
, l_ECO_Unexp_rec.requestor_id
, l_ECO_rec.attribute_category
, l_ECO_rec.attribute1
, l_ECO_rec.attribute2
, l_ECO_rec.attribute3
, l_ECO_rec.attribute4
, l_ECO_rec.attribute5
, l_ECO_rec.attribute6
, l_ECO_Unexp_rec.project_id
, l_ECO_Unexp_rec.task_id
, l_ECO_Unexp_rec.change_id
-- , l_ECO_rec.hierarchy_flag
, l_ECO_rec.organization_hierarchy
, l_ECO_Unexp_rec.change_mgmt_type_code -- Eng Change
, l_ECO_Unexp_rec.assignee_id -- Eng Change
, l_ECO_rec.need_by_date -- Eng Chagne
, l_ECO_rec.internal_use_only -- Eng Chagne
, l_ECO_Unexp_rec.source_type_code -- Eng Chagne
, l_ECO_Unexp_rec.source_id -- Eng Chagne
, l_ECO_rec.effort -- Eng Chagne
FROM ENG_ENGINEERING_CHANGES
WHERE CHANGE_NOTICE = p_change_notice
AND ORGANIZATION_ID = p_organization_id
;
UPDATE eng_engineering_changes
SET approval_status_type = p_approval_status_type ,
approval_date = sysdate ,
request_id = l_request_id ,
last_update_date = SYSDATE ,
last_updated_by = l_user_id ,
last_update_login = l_login_id
WHERE change_id = p_change_id ;
UPDATE eng_revised_items
SET status_type = 4 , -- Set Rev Item Status: Scheduled
request_id = l_request_id ,
last_update_date = SYSDATE ,
last_updated_by = l_user_id ,
last_update_login = l_login_id
WHERE change_id = p_change_id
AND status_type = 1; -- Rev Item Status: Open
UPDATE eng_engineering_changes
SET status_type = 4 , -- Scheduled
request_id = l_request_id ,
last_update_date = SYSDATE ,
last_updated_by = l_user_id ,
last_update_login = l_login_id
WHERE change_id = p_change_id
AND status_type = 1; -- Open
UPDATE eng_engineering_changes
SET approval_status_type = p_approval_status_type ,
approval_date = NULL ,
request_id = l_request_id ,
last_update_date = SYSDATE ,
last_updated_by = l_user_id ,
last_update_login = l_login_id
WHERE change_id = p_change_id ;
UPDATE eng_engineering_changes
SET approval_status_type = p_approval_status_type ,
approval_date = NULL ,
request_id = l_request_id ,
last_update_date = SYSDATE ,
last_updated_by = l_user_id ,
last_update_login = l_login_id
WHERE change_id = p_change_id ;
update eng_revised_items
set STATUS_TYPE = 1
where CHANGE_ID = p_change_id;
update eng_engineering_changes
set STATUS_TYPE = 1
where CHANGE_ID = p_change_id;
SELECT ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, APPROVAL_STATUS_TYPE
, APPROVAL_DATE
, APPROVAL_LIST_ID
, CHANGE_ORDER_TYPE_ID
, RESPONSIBLE_ORGANIZATION_ID
, APPROVAL_REQUEST_DATE
, CHANGE_NOTICE
, ORGANIZATION_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, DESCRIPTION
, STATUS_TYPE
, INITIATION_DATE
, IMPLEMENTATION_DATE
, CANCELLATION_DATE
, CANCELLATION_COMMENTS
, PRIORITY_CODE
, REASON_CODE
, ESTIMATED_ENG_COST
, ESTIMATED_MFG_COST
, REQUESTOR_ID
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
INTO l_ECO_rec.attribute7
, l_ECO_rec.attribute8
, l_ECO_rec.attribute9
, l_ECO_rec.attribute10
, l_ECO_rec.attribute11
, l_ECO_rec.attribute12
, l_ECO_rec.attribute13
, l_ECO_rec.attribute14
, l_ECO_rec.attribute15
, l_ECO_rec.request_id
, l_ECO_rec.program_application_id
, l_ECO_rec.program_id
, l_ECO_rec.program_update_date
, l_ECO_rec.approval_status_type
, l_ECO_rec.approval_date
, l_ECO_rec.approval_list_id
, l_ECO_rec.change_order_type_id
, l_ECO_rec.responsible_org_id
, l_ECO_rec.approval_request_date
, l_ECO_rec.change_notice
, l_ECO_rec.organization_id
, l_ECO_rec.last_update_date
, l_ECO_rec.last_updated_by
, l_ECO_rec.creation_date
, l_ECO_rec.created_by
, l_ECO_rec.last_update_login
, l_ECO_rec.description
, l_ECO_rec.status_type
, l_ECO_rec.initiation_date
, l_ECO_rec.implementation_date
, l_ECO_rec.cancellation_date
, l_ECO_rec.cancellation_comments
, l_ECO_rec.priority_code
, l_ECO_rec.reason_code
, l_ECO_rec.ENG_implementation_cost
, l_ECO_rec.MFG_implementation_cost
, l_ECO_rec.requestor_id
, l_ECO_rec.attribute_category
, l_ECO_rec.attribute1
, l_ECO_rec.attribute2
, l_ECO_rec.attribute3
, l_ECO_rec.attribute4
, l_ECO_rec.attribute5
, l_ECO_rec.attribute6
FROM ENG_ENGINEERING_CHANGES
WHERE CHANGE_NOTICE = p_ECO_rec.change_notice
AND ORGANIZATION_ID = p_ECO_rec.organization_id
FOR UPDATE NOWAIT;
AND ( (l_ECO_rec.program_update_date =
p_ECO_rec.program_update_date) OR
((p_ECO_rec.program_update_date = FND_API.G_MISS_DATE) OR
( (l_ECO_rec.program_update_date IS NULL) AND
(p_ECO_rec.program_update_date IS NULL))))
AND ( (l_ECO_rec.approval_status_type =
p_ECO_rec.approval_status_type) OR
((p_ECO_rec.approval_status_type = FND_API.G_MISS_NUM) OR
( (l_ECO_rec.approval_status_type IS NULL) AND
(p_ECO_rec.approval_status_type IS NULL))))
AND ( (l_ECO_rec.approval_date =
p_ECO_rec.approval_date) OR
((p_ECO_rec.approval_date = FND_API.G_MISS_DATE) OR
( (l_ECO_rec.approval_date IS NULL) AND
(p_ECO_rec.approval_date IS NULL))))
AND ( (l_ECO_rec.approval_list_id =
p_ECO_rec.approval_list_id) OR
((p_ECO_rec.approval_list_id = FND_API.G_MISS_NUM) OR
( (l_ECO_rec.approval_list_id IS NULL) AND
(p_ECO_rec.approval_list_id IS NULL))))
AND ( (l_ECO_rec.change_order_type_id =
p_ECO_rec.change_order_type_id) OR
((p_ECO_rec.change_order_type_id = FND_API.G_MISS_NUM) OR
( (l_ECO_rec.change_order_type_id IS NULL) AND
(p_ECO_rec.change_order_type_id IS NULL))))
AND ( (l_ECO_rec.responsible_org_id =
p_ECO_rec.responsible_org_id) OR
((p_ECO_rec.responsible_org_id = FND_API.G_MISS_NUM) OR
( (l_ECO_rec.responsible_org_id IS NULL) AND
(p_ECO_rec.responsible_org_id IS NULL))))
AND ( (l_ECO_rec.approval_request_date =
p_ECO_rec.approval_request_date) OR
((p_ECO_rec.approval_request_date = FND_API.G_MISS_DATE) OR
( (l_ECO_rec.approval_request_date IS NULL) AND
(p_ECO_rec.approval_request_date IS NULL))))
AND ( (l_ECO_rec.change_notice =
p_ECO_rec.change_notice) OR
((p_ECO_rec.change_notice = FND_API.G_MISS_CHAR) OR
( (l_ECO_rec.change_notice IS NULL) AND
(p_ECO_rec.change_notice IS NULL))))
AND ( (l_ECO_rec.organization_id =
p_ECO_rec.organization_id) OR
((p_ECO_rec.organization_id = FND_API.G_MISS_NUM) OR
( (l_ECO_rec.organization_id IS NULL) AND
(p_ECO_rec.organization_id IS NULL))))
AND ( (l_ECO_rec.last_update_date =
p_ECO_rec.last_update_date) OR
((p_ECO_rec.last_update_date = FND_API.G_MISS_DATE) OR
( (l_ECO_rec.last_update_date IS NULL) AND
(p_ECO_rec.last_update_date IS NULL))))
AND ( (l_ECO_rec.last_updated_by =
p_ECO_rec.last_updated_by) OR
((p_ECO_rec.last_updated_by = FND_API.G_MISS_NUM) OR
( (l_ECO_rec.last_updated_by IS NULL) AND
(p_ECO_rec.last_updated_by IS NULL))))
AND ( (l_ECO_rec.creation_date =
p_ECO_rec.creation_date) OR
((p_ECO_rec.creation_date = FND_API.G_MISS_DATE) OR
( (l_ECO_rec.creation_date IS NULL) AND
(p_ECO_rec.creation_date IS NULL))))
AND ( (l_ECO_rec.created_by =
p_ECO_rec.created_by) OR
((p_ECO_rec.created_by = FND_API.G_MISS_NUM) OR
( (l_ECO_rec.created_by IS NULL) AND
(p_ECO_rec.created_by IS NULL))))
AND ( (l_ECO_rec.last_update_login =
p_ECO_rec.last_update_login) OR
((p_ECO_rec.last_update_login = FND_API.G_MISS_NUM) OR
( (l_ECO_rec.last_update_login IS NULL) AND
(p_ECO_rec.last_update_login IS NULL))))
AND ( (l_ECO_rec.description =
p_ECO_rec.description) OR
((p_ECO_rec.description = FND_API.G_MISS_CHAR) OR
( (l_ECO_rec.description IS NULL) AND
(p_ECO_rec.description IS NULL))))
AND ( (l_ECO_rec.status_type =
p_ECO_rec.status_type) OR
((p_ECO_rec.status_type = FND_API.G_MISS_NUM) OR
( (l_ECO_rec.status_type IS NULL) AND
(p_ECO_rec.status_type IS NULL))))
AND ( (l_ECO_rec.initiation_date =
p_ECO_rec.initiation_date) OR
((p_ECO_rec.initiation_date = FND_API.G_MISS_DATE) OR
( (l_ECO_rec.initiation_date IS NULL) AND
(p_ECO_rec.initiation_date IS NULL))))
AND ( (l_ECO_rec.implementation_date =
p_ECO_rec.implementation_date) OR
((p_ECO_rec.implementation_date = FND_API.G_MISS_DATE) OR
( (l_ECO_rec.implementation_date IS NULL) AND
(p_ECO_rec.implementation_date IS NULL))))
AND ( (l_ECO_rec.cancellation_date =
p_ECO_rec.cancellation_date) OR
((p_ECO_rec.cancellation_date = FND_API.G_MISS_DATE) OR
( (l_ECO_rec.cancellation_date IS NULL) AND
(p_ECO_rec.cancellation_date IS NULL))))
AND ( (l_ECO_rec.cancellation_comments =
p_ECO_rec.cancellation_comments) OR
((p_ECO_rec.cancellation_comments = FND_API.G_MISS_CHAR) OR
( (l_ECO_rec.cancellation_comments IS NULL) AND
(p_ECO_rec.cancellation_comments IS NULL))))
AND ( (l_ECO_rec.priority_code =
p_ECO_rec.priority_code) OR
((p_ECO_rec.priority_code = FND_API.G_MISS_CHAR) OR
( (l_ECO_rec.priority_code IS NULL) AND
(p_ECO_rec.priority_code IS NULL))))
AND ( (l_ECO_rec.reason_code =
p_ECO_rec.reason_code) OR
((p_ECO_rec.reason_code = FND_API.G_MISS_CHAR) OR
( (l_ECO_rec.reason_code IS NULL) AND
(p_ECO_rec.reason_code IS NULL))))
AND ( (l_ECO_rec.estimated_eng_cost =
p_ECO_rec.estimated_eng_cost) OR
((p_ECO_rec.estimated_eng_cost = FND_API.G_MISS_NUM) OR
( (l_ECO_rec.estimated_eng_cost IS NULL) AND
(p_ECO_rec.estimated_eng_cost IS NULL))))
AND ( (l_ECO_rec.estimated_mfg_cost =
p_ECO_rec.estimated_mfg_cost) OR
((p_ECO_rec.estimated_mfg_cost = FND_API.G_MISS_NUM) OR
( (l_ECO_rec.estimated_mfg_cost IS NULL) AND
(p_ECO_rec.estimated_mfg_cost IS NULL))))
AND ( (l_ECO_rec.requestor_id =
p_ECO_rec.requestor_id) OR
((p_ECO_rec.requestor_id = FND_API.G_MISS_NUM) OR
( (l_ECO_rec.requestor_id IS NULL) AND
(p_ECO_rec.requestor_id IS NULL))))
AND ( (l_ECO_rec.attribute_category =
p_ECO_rec.attribute_category) OR
((p_ECO_rec.attribute_category = FND_API.G_MISS_CHAR) OR
( (l_ECO_rec.attribute_category IS NULL) AND
(p_ECO_rec.attribute_category IS NULL))))
AND ( (l_ECO_rec.attribute1 =
p_ECO_rec.attribute1) OR
((p_ECO_rec.attribute1 = FND_API.G_MISS_CHAR) OR
( (l_ECO_rec.attribute1 IS NULL) AND
(p_ECO_rec.attribute1 IS NULL))))
AND ( (l_ECO_rec.attribute2 =
p_ECO_rec.attribute2) OR
((p_ECO_rec.attribute2 = FND_API.G_MISS_CHAR) OR
( (l_ECO_rec.attribute2 IS NULL) AND
(p_ECO_rec.attribute2 IS NULL))))
AND ( (l_ECO_rec.attribute3 =
p_ECO_rec.attribute3) OR
((p_ECO_rec.attribute3 = FND_API.G_MISS_CHAR) OR
( (l_ECO_rec.attribute3 IS NULL) AND
(p_ECO_rec.attribute3 IS NULL))))
AND ( (l_ECO_rec.attribute4 =
p_ECO_rec.attribute4) OR
((p_ECO_rec.attribute4 = FND_API.G_MISS_CHAR) OR
( (l_ECO_rec.attribute4 IS NULL) AND
(p_ECO_rec.attribute4 IS NULL))))
AND ( (l_ECO_rec.attribute5 =
p_ECO_rec.attribute5) OR
((p_ECO_rec.attribute5 = FND_API.G_MISS_CHAR) OR
( (l_ECO_rec.attribute5 IS NULL) AND
(p_ECO_rec.attribute5 IS NULL))))
AND ( (l_ECO_rec.attribute6 =
p_ECO_rec.attribute6) OR
((p_ECO_rec.attribute6 = FND_API.G_MISS_CHAR) OR
( (l_ECO_rec.attribute6 IS NULL) AND
(p_ECO_rec.attribute6 IS NULL))))
THEN
-- Row has not changed. Set out parameter.
x_ECO_rec := l_ECO_rec;
, p_msg_name => 'OE_LOCK_ROW_DELETED'
, x_err_text => x_err_text );
select ect.type_name ,ect.subject_id ,ese.entity_name ,ese.parent_entity_name from
eng_change_order_types_vl ect ,eng_subject_entities ese
where ect.subject_id =ese.subject_id
and change_order_type_id =p_change_type_id
and subject_level=1 ;
SELECT LP.PROJ_ELEMENT_ID -- into l_current_lifecycle_id
FROM PA_EGO_LIFECYCLES_PHASES_V LP, MTL_ITEM_REVISIONS MIR
WHERE LP.PROJ_ELEMENT_ID = MIR.CURRENT_PHASE_ID
AND MIR.INVENTORY_ITEM_ID = item_id
AND MIR.ORGANIZATION_ID = l_org_id
AND MIR.REVISION = revision; */ -- Commented By LKASTURI
SELECT ITEM_CATALOG_GROUP_ID
from mtl_system_items msi
where msi.INVENTORY_ITEM_ID = item_id
AND msi.ORGANIZATION_ID = l_org_id;
SELECT CURRENT_PHASE_ID
INTO l_change_subject_unexp_rec.lifecycle_state_id
FROM MTL_System_items_vl
WHERE INVENTORY_ITEM_ID = l_change_subject_unexp_rec.pk1_value
AND ORGANIZATION_ID = l_change_subject_unexp_rec.pk2_value;
SELECT eng_change_subjects_s.nextval INTO l_change_subject_unexp_rec.change_subject_id
FROM SYS.DUAL;
Insert into eng_change_subjects
(CHANGE_SUBJECT_ID,
CHANGE_ID,
CHANGE_LINE_ID,
ENTITY_NAME,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
SUBJECT_LEVEL,
LIFECYCLE_STATE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE)
values
(l_change_subject_unexp_rec.change_subject_id,
l_change_subject_unexp_rec.change_id,
l_change_subject_unexp_rec.change_line_id,
l_change_subject_unexp_rec.entity_name,
l_change_subject_unexp_rec.pk1_value,
l_change_subject_unexp_rec.pk2_value,
l_change_subject_unexp_rec.pk3_value,
l_change_subject_unexp_rec.pk4_value,
l_change_subject_unexp_rec.pk5_value,
l_change_subject_unexp_rec.subject_level,
l_change_subject_unexp_rec.lifecycle_state_id,
SYSDATE,
l_User_Id,
SYSDATE,
l_User_Id,
l_Login_Id,
l_request_id,
l_prog_id,
l_prog_appid,
SYSDATE) returning CHANGE_SUBJECT_ID into l_sub_id;
Insert into eng_change_subjects
(CHANGE_SUBJECT_ID,
CHANGE_ID,
CHANGE_LINE_ID,
ENTITY_NAME,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
SUBJECT_LEVEL,
LIFECYCLE_STATE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE)
values
(eng_change_subjects_s.nextval,
l_change_subject_unexp_rec.change_id,
null,
l_parent_entity_name, -- bug 3572698
l_change_subject_unexp_rec.pk1_value,
l_change_subject_unexp_rec.pk2_value,
null,
null,
null,
2,
null,
SYSDATE,
l_User_Id,
SYSDATE,
l_User_Id,
l_Login_Id,
l_request_id,
l_prog_appid,
l_prog_id,sysdate);
Insert into eng_change_subjects
(CHANGE_SUBJECT_ID,
CHANGE_ID,
CHANGE_LINE_ID,
ENTITY_NAME,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
SUBJECT_LEVEL,
LIFECYCLE_STATE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE)
values
(eng_change_subjects_s.nextval,
l_change_subject_unexp_rec.change_id,
null,
l_parent_entity_name, -- bug 3572698
l_item_catalog_group_id,
null,
null,
null,
null,
2,
null,
SYSDATE,
l_User_Id,
SYSDATE,
l_User_Id,
l_Login_Id,
l_request_id,
l_prog_appid,
l_prog_id,sysdate);
ELSIF p_eco_rec.transaction_type = Eng_Globals.G_OPR_UPDATE THEN
UPDATE eng_change_subjects SET
pk1_value = l_change_subject_unexp_rec.pk1_value,
pk2_value = l_change_subject_unexp_rec.pk2_value,
pk3_value = l_change_subject_unexp_rec.pk3_value
WHERE change_id = l_change_subject_unexp_rec.change_id
AND subject_level = 1
AND change_line_id is null;
UPDATE eng_change_subjects SET
pk1_value = l_change_subject_unexp_rec.pk1_value,
pk2_value = l_change_subject_unexp_rec.pk2_value
WHERE change_id = l_change_subject_unexp_rec.change_id
AND subject_level = 2
AND change_line_id is null;
UPDATE eng_change_subjects SET
pk1_value = l_item_catalog_group_id
WHERE change_id = l_change_subject_unexp_rec.change_id
AND subject_level = 2
AND change_line_id is null;
DELETE FROM eng_change_subjects
WHERE change_line_id is null
AND change_id = p_ECO_Unexp_Rec.change_id;
PROCEDURE delete_ECO
(
p_api_version IN NUMBER --
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
,p_commit IN VARCHAR2 := FND_API.G_FALSE --
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2 --
,x_msg_count OUT NOCOPY NUMBER --
,x_msg_data OUT NOCOPY VARCHAR2 --
,p_change_id IN NUMBER -- header's change_id
,p_api_caller IN VARCHAR2 := 'UI'
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_ECO';
DELETE FROM ENG_CHANGE_ROUTE_ASSOCS
WHERE ROUTE_PEOPLE_ID in
( select ecrp.ROUTE_PEOPLE_ID
from ENG_CHANGE_ROUTE_PEOPLE ecrp , ENG_CHANGE_ROUTE_STEPS ecrs , ENG_LIFECYCLE_STATUSES els
where ecrp.STEP_ID = ecrs.step_id
AND ecrs.ROUTE_ID = els.CHANGE_WF_ROUTE_ID
and els.ENTITY_ID1 = p_change_id
and els.ENTITY_NAME = 'ENG_CHANGE'
);
/*( select ROUTE_PEOPLE_ID
from ENG_CHANGE_ROUTE_PEOPLE
where STEP_ID in
( select STEP_ID
from ENG_CHANGE_ROUTE_STEPS
where ROUTE_ID in
( select CHANGE_WF_ROUTE_ID
from ENG_LIFECYCLE_STATUSES
where ENTITY_ID1 = p_change_id
and ENTITY_NAME = 'ENG_CHANGE'
)
)
);*/
delete from ENG_CHANGE_ROUTE_PEOPLE_tl
where ROUTE_PEOPLE_ID in
( select ecrp.ROUTE_PEOPLE_ID
from ENG_CHANGE_ROUTE_PEOPLE ecrp , ENG_CHANGE_ROUTE_STEPS ecrs , ENG_LIFECYCLE_STATUSES els
where ecrp.STEP_ID = ecrs.step_id
AND ecrs.ROUTE_ID = els.CHANGE_WF_ROUTE_ID
and els.ENTITY_ID1 = p_change_id
and els.ENTITY_NAME = 'ENG_CHANGE'
);
/*( select ROUTE_PEOPLE_ID
from ENG_CHANGE_ROUTE_PEOPLE
where STEP_ID in
( select STEP_ID
from ENG_CHANGE_ROUTE_STEPS
where ROUTE_ID in
( select CHANGE_WF_ROUTE_ID
from ENG_LIFECYCLE_STATUSES
where ENTITY_ID1 = p_change_id
and ENTITY_NAME = 'ENG_CHANGE'
)
)
);*/
delete from ENG_CHANGE_ROUTE_PEOPLE
where STEP_ID in
( select STEP_ID
from ENG_CHANGE_ROUTE_STEPS
where ROUTE_ID in
( select CHANGE_WF_ROUTE_ID
from ENG_LIFECYCLE_STATUSES
where ENTITY_ID1 = p_change_id
and ENTITY_NAME = 'ENG_CHANGE'
)
);
delete from ENG_CHANGE_ROUTE_STEPS_TL
where STEP_ID in
( select STEP_ID
from ENG_CHANGE_ROUTE_STEPS
where ROUTE_ID in
( select CHANGE_WF_ROUTE_ID
from ENG_LIFECYCLE_STATUSES
where ENTITY_ID1 = p_change_id
and ENTITY_NAME = 'ENG_CHANGE'
)
);
delete from ENG_CHANGE_ROUTE_STEPS
where ROUTE_ID in
( select CHANGE_WF_ROUTE_ID
from ENG_LIFECYCLE_STATUSES
where ENTITY_ID1 = p_change_id
and ENTITY_NAME = 'ENG_CHANGE'
);
delete from ENG_CHANGE_ROUTES_tl
where ROUTE_ID in
( select CHANGE_WF_ROUTE_ID
from ENG_LIFECYCLE_STATUSES
where ENTITY_ID1 = p_change_id
and ENTITY_NAME = 'ENG_CHANGE'
);
delete from ENG_CHANGE_ROUTES
where ROUTE_ID in
( select CHANGE_WF_ROUTE_ID
from ENG_LIFECYCLE_STATUSES
where ENTITY_ID1 = p_change_id
and ENTITY_NAME = 'ENG_CHANGE' );
delete from ENG_LIFECYCLE_STATUSES
where ENTITY_ID1 = p_change_id
and ENTITY_NAME = 'ENG_CHANGE' ;
delete from ENG_LIFECYCLE_STATUSES
where ENTITY_ID1 = p_change_id
and ENTITY_NAME = 'ENG_CHANGE' ;
delete from eng_revised_items
where change_id = p_change_id ;
delete from eng_engineering_changes
where change_id = p_change_id ;
END delete_ECO;
select 'X'
from eng_revised_items REV1, eng_revised_items REV2
where REV1.change_id = p_change_id
and REV2.change_id = p_change_id
and REV1.organization_id = REV2.organization_id
and REV1.revised_item_id = REV2.revised_item_id
and REV1.revised_item_sequence_id <> REV2.revised_item_sequence_id
and REV1.status_type <> 5
and REV2.status_type <> 5
and REV1.new_item_revision is not null
and REV2.new_item_revision is not null;