[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor c1 is select change_notice from ENG_REVISED_ITEMS eri
where eri.Organization_Id = x_organization_id
and eri.Revised_Item_Id = x_revised_item_id
and eri.New_Item_Revision = x_new_item_revision
-- Query to fetch unimplemented ECOs only
and eri.implementation_date is null --added for bug 9764163
and eri.Cancellation_Date is null;
select BOM_LISTS_S.nextval
into seq_id
from DUAL;
PROCEDURE Insert_BOM_Lists (x_revised_item_id NUMBER,
x_sequence_id NUMBER,
x_bill_sequence_id NUMBER) IS
BEGIN
insert into BOM_LISTS (sequence_id, assembly_item_id)
values (x_sequence_id, x_revised_item_id);
insert into BOM_LISTS (sequence_id, assembly_item_id)
select distinct(x_sequence_id), component_item_id
from BOM_INVENTORY_COMPONENTS
where bill_sequence_id = x_bill_sequence_id;
END Insert_BOM_Lists;
PROCEDURE Delete_BOM_Lists (x_sequence_id NUMBER) IS
BEGIN
delete from BOM_LISTS
where sequence_id = x_sequence_id;
END Delete_BOM_Lists;
PROCEDURE Delete_Details (x_organization_id NUMBER,
x_revised_item_id NUMBER,
x_revised_item_sequence_id NUMBER,
x_bill_sequence_id NUMBER,
x_change_notice VARCHAR2)
IS
BEGIN
delete from MTL_ITEM_REVISIONS_TL
where revision_id IN (SELECT revision_id
FROM MTL_ITEM_REVISIONS_B
WHERE organization_id = x_organization_id
and inventory_item_id = x_revised_item_id
and revised_item_sequence_Id = x_revised_item_sequence_id
and change_notice = x_change_notice
and implementation_date is null);
delete from MTL_ITEM_REVISIONS_B
where organization_id = x_organization_id
and inventory_item_id = x_revised_item_id
and revised_item_sequence_Id = x_revised_item_sequence_id
and change_notice = x_change_notice
and implementation_date is null;
delete from ENG_CURRENT_SCHEDULED_DATES
where organization_id = x_organization_id
and revised_item_id = x_revised_item_id
and revised_item_sequence_id = x_revised_item_sequence_id
and change_notice = x_change_notice;
delete from BOM_BILL_OF_MATERIALS bom
where bom.bill_sequence_id = x_bill_sequence_id
and bom.pending_from_ecn = x_change_notice
and not exists (select null
from BOM_INVENTORY_COMPONENTS bic
where bic.bill_sequence_id = bom.bill_sequence_id
and (bic.change_notice is null
or
bic.change_notice <> x_change_notice
or
(bic.change_notice = x_change_notice
and bic.revised_item_sequence_id <> x_revised_item_sequence_id)))
and ((bom.alternate_bom_designator is null
and not exists (select null
from BOM_BILL_OF_MATERIALS bom2
where bom2.organization_id = bom.organization_id
and bom2.assembly_item_id = bom.assembly_item_id
and bom2.alternate_bom_designator is not null))
or
(bom.alternate_bom_designator is not null
and not exists (select null
from ENG_REVISED_ITEMS eri
where eri.organization_id = bom.organization_id
and eri.bill_sequence_id = bom.bill_sequence_id
and eri.change_notice <> x_change_notice))
);
update BOM_BILL_OF_MATERIALS bom
set pending_from_ecn = null
where bom.bill_sequence_id = x_bill_sequence_id
and bom.pending_from_ecn = x_change_notice
and not exists (select null
from BOM_INVENTORY_COMPONENTS bic
where bic.bill_sequence_id = bom.bill_sequence_id
and (bic.change_notice is null
or
bic.change_notice <> x_change_notice
or
(bic.change_notice = x_change_notice
and bic.revised_item_sequence_id <> x_revised_item_sequence_id)))
and ((bom.alternate_bom_designator is null
and not exists (select null
from BOM_BILL_OF_MATERIALS bom2
where bom2.organization_id = bom.organization_id
and bom2.assembly_item_id = bom.assembly_item_id
and bom2.alternate_bom_designator is not null))
or
(bom.alternate_bom_designator is not null
and not exists (select null
from ENG_REVISED_ITEMS eri
where eri.organization_id = bom.organization_id
and eri.bill_sequence_id = bom.bill_sequence_id
and eri.change_notice <> x_change_notice))
);
update ENG_REVISED_ITEMS
set bill_sequence_id = ''
where bill_sequence_id = x_bill_sequence_id
and organization_id = x_organization_id
and implementation_date is null
and not exists (select null
from BOM_BILL_OF_MATERIALS bom
where bom.bill_sequence_id = x_bill_sequence_id);
END Delete_Details;
SELECT structure_type_id
INTO l_structure_type_id
FROM bom_alternate_designators
WHERE
((x_alternate_BOM_designator IS NULL
AND alternate_designator_code IS NULL
AND organization_id = -1)
OR
(x_alternate_BOM_designator IS NOT NULL
AND alternate_designator_code = x_alternate_BOM_designator
AND organization_id = x_organization_id));
select effectivity_control
INTO l_effectivity_control
from mtl_system_items
where inventory_item_id = x_assembly_item_id
and organization_id = x_organization_id;
insert into BOM_BILL_OF_MATERIALS (
assembly_item_id,
organization_id,
alternate_BOM_designator,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
pending_from_ecn,
assembly_type,
common_bill_sequence_id,
bill_sequence_id,
structure_type_id,
implementation_date,
effectivity_control,
source_bill_sequence_id,
pk1_value, --Bug 4707618
pk2_value) --Bug 4707618
values (x_assembly_item_id,
x_organization_id,
x_alternate_BOM_designator,
sysdate,
x_userid,
sysdate,
x_userid,
x_userid,
x_change_notice,
x_assembly_type,
x_bill_sequence_id,
x_bill_sequence_id,
l_structure_type_id,
sysdate,
l_effectivity_control,
x_bill_sequence_id,
x_assembly_item_id, --Bug 4707618
x_organization_id); --Bug 4707618
PROCEDURE Insert_Current_Scheduled_Dates (x_change_notice VARCHAR2,
x_organization_id NUMBER,
x_revised_item_id NUMBER,
x_scheduled_date DATE,
x_revised_item_sequence_id NUMBER,
x_requestor_id NUMBER,
x_userid NUMBER) IS
x_schedule_id NUMBER;
select ENG_CURRENT_SCHEDULED_DATES_S.nextval
into x_schedule_id
from sys.dual;
insert into ENG_CURRENT_SCHEDULED_DATES (
change_notice,
organization_id,
revised_item_id,
scheduled_date,
last_update_date,
last_updated_by,
schedule_id,
creation_date,
created_by,
last_update_login,
employee_id,
revised_item_sequence_id )
values (x_change_notice,
x_organization_id,
x_revised_item_id,
x_scheduled_date,
sysdate,
x_userid,
x_schedule_id,
sysdate,
x_userid,
x_userid,
x_requestor_id,
x_revised_item_sequence_id );
END Insert_Current_Scheduled_Dates;
PROCEDURE Delete_Item_Revisions (x_change_notice VARCHAR2,
x_organization_id NUMBER,
x_inventory_item_id NUMBER,
x_revised_item_sequence_id NUMBER)
IS
l_revision_id NUMBER;
delete from eng_attachment_changes
where
change_id IN (select change_id
from eng_engineering_changes
where change_notice = x_change_notice
and organization_id = x_organization_id) and --change_id is required for index
revised_item_sequence_id = x_revised_item_sequence_id and
entity_name = 'MTL_ITEM_REVISIONS' and
pk3_value IN (select revision_id
from MTL_ITEM_REVISIONS_B
where organization_id = x_organization_id
and inventory_item_id = x_inventory_item_id
and revised_item_sequence_Id = x_revised_item_sequence_id
and change_notice = x_change_notice
and implementation_date is null);
delete from MTL_ITEM_REVISIONS_TL
where revision_id IN (select revision_id
from MTL_ITEM_REVISIONS_B
where organization_id = x_organization_id
and inventory_item_id = x_inventory_item_id
and revised_item_sequence_Id = x_revised_item_sequence_id
and change_notice = x_change_notice
and implementation_date is null);
delete from MTL_ITEM_REVISIONS_B
where organization_id = x_organization_id
and inventory_item_id =x_inventory_item_id
and revised_item_sequence_Id = x_revised_item_sequence_id
and change_notice = x_change_notice
and implementation_date is null;
END Delete_Item_Revisions;
PROCEDURE Insert_Item_Revisions (x_inventory_item_id NUMBER,
x_organization_id NUMBER,
x_revision VARCHAR2,
x_userid NUMBER,
x_change_notice VARCHAR2,
x_scheduled_date DATE,
x_revised_item_sequence_id NUMBER,
x_revision_description VARCHAR2 := NULL,
p_new_revision_label VARCHAR2 DEFAULT NULL,
p_new_revision_reason_code VARCHAR2 DEFAULT NULL,
p_from_revision_id NUMBER DEFAULT NULL)
IS
l_revision_id NUMBER;
Insert_Item_Revisions (x_inventory_item_id => x_inventory_item_id,
x_organization_id => x_organization_id,
x_revision => x_revision,
x_userid => x_userid,
x_change_notice => x_change_notice,
x_scheduled_date => x_scheduled_date,
x_revised_item_sequence_id => x_revised_item_sequence_id,
x_revision_description => x_revision_description,
p_new_revision_label => p_new_revision_label,
p_new_revision_reason_code => p_new_revision_reason_code,
p_from_revision_id => p_from_revision_id,
x_new_revision_id => l_revision_id);
PROCEDURE Insert_Item_Revisions (x_inventory_item_id NUMBER,
x_organization_id NUMBER,
x_revision VARCHAR2,
x_userid NUMBER,
x_change_notice VARCHAR2,
x_scheduled_date DATE,
x_revised_item_sequence_id NUMBER,
x_revision_description VARCHAR2 := NULL,
p_new_revision_label VARCHAR2 DEFAULT NULL,
p_new_revision_reason_code VARCHAR2 DEFAULT NULL,
p_from_revision_id NUMBER DEFAULT NULL,
x_new_revision_id IN OUT NOCOPY NUMBER)
IS
l_language_code VARCHAR2(3);
insert into MTL_ITEM_REVISIONS_B (
inventory_item_id,
organization_id,
revision,
revision_label,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
change_notice,
ecn_initiation_date,
effectivity_date,
revised_item_sequence_id,
revision_id,
object_version_number,
description,
revision_reason
)
values (x_inventory_item_id,
x_organization_id,
x_revision,
--x_revision,
decode( decode(p_new_revision_label, FND_API.G_MISS_CHAR, NULL, p_new_revision_label),
NULL, x_revision, p_new_revision_label),
sysdate,
x_userid,
sysdate,
x_userid,
x_userid,
x_change_notice,
sysdate,
decode(x_scheduled_date, trunc(sysdate), sysdate, x_scheduled_date),
x_revised_item_sequence_id,
mtl_item_revisions_b_s.NEXTVAL,
1,
decode(x_revision_description,FND_API.G_MISS_CHAR,NULL,x_revision_description),
decode(p_new_revision_reason_code, FND_API.G_MISS_CHAR, NULL, p_new_revision_reason_code)
)RETURNING revision_id INTO l_revision_id;
SELECT userenv('LANG') INTO l_language_code FROM dual;
insert into MTL_ITEM_REVISIONS_TL (
inventory_item_id,
organization_id,
revision_id,
language,
source_lang,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
description )
SELECT x_inventory_item_id,
x_organization_id,
l_revision_id,
lang.language_code,
l_language_code,
sysdate,
x_userid,
sysdate,
x_userid,
x_userid,
/* Item revision description support for ECO Bug: 1667419 */
decode(x_revision_description,FND_API.G_MISS_CHAR,NULL,x_revision_description)
FROM FND_LANGUAGES lang
where lang.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from MTL_ITEM_REVISIONS_TL T
where T.INVENTORY_ITEM_ID = x_inventory_item_id
and T.ORGANIZATION_ID = x_organization_id
and T.REVISION_ID = l_revision_id
and T.LANGUAGE = lang.LANGUAGE_CODE);
SELECT change_id
INTO l_change_id
FROM eng_engineering_changes
WHERE change_notice = x_change_notice
AND organization_id = x_organization_id;
END Insert_Item_Revisions;
PROCEDURE Update_Item_Revisions (x_revision VARCHAR2,
x_scheduled_date DATE,
x_change_notice VARCHAR2,
x_organization_id NUMBER,
x_inventory_item_id NUMBER,
x_revised_item_sequence_id NUMBER,
x_revision_description VARCHAR2 := NULL)
IS
l_language_code VARCHAR2(3);
update MTL_ITEM_REVISIONS_B
set revision = x_revision,
revision_label = x_revision, -- Bug No:3612330 added by sseraphi to update rev label along with rev code.
effectivity_date = decode(x_scheduled_date, trunc(sysdate), sysdate, x_scheduled_date),
last_update_date = SYSDATE,
last_update_login = l_login_id,
last_updated_by = l_user_id
where change_notice = x_change_notice
and organization_id = x_organization_id
and inventory_item_id = x_inventory_item_id
and revised_item_sequence_id = x_revised_item_sequence_id
RETURNING revision_id INTO l_revision_id;
SELECT userenv('LANG') INTO l_language_code FROM dual;
update MTL_ITEM_REVISIONS_TL
set
last_update_date = SYSDATE, --who column
last_update_login = l_login_id, --who column
last_updated_by = l_user_id, --who column
description = x_revision_description,
source_lang = l_language_code
where revision_id = l_revision_id
AND LANGUAGE = l_language_code;
END Update_Item_Revisions;
* updated for the components on destination bill ECOs.
*
* For source bill ECO changes,
* these changes in effectivity should be propagated to the
* related replicated components.
*********************************************************************/
PROCEDURE Update_Inventory_Components (x_change_notice VARCHAR2,
x_bill_sequence_id NUMBER,
x_revised_item_sequence_id NUMBER,
x_scheduled_date DATE,
x_from_end_item_unit_number VARCHAR2 DEFAULT NULL) IS
-- R12 Changes for common BOM
l_return_status varchar2(80);
SELECT bcb.component_sequence_id
FROM bom_components_b bcb
WHERE bcb.CHANGE_NOTICE = cp_change_notice
AND bcb.revised_item_sequence_id = cp_revised_item_seq_id
AND bcb.bill_sequence_id = cp_bill_sequence_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;
update BOM_INVENTORY_COMPONENTS
set effectivity_date = x_scheduled_date,
from_end_item_unit_number = x_from_end_item_unit_number,
last_update_date = sysdate, --Bug 9240045 fix
last_updated_by = BOM_Globals.Get_User_Id, --Bug 9240045 fix
last_update_login = BOM_Globals.Get_User_Id --Bug 9240045 fix
where change_notice = x_change_notice
and bill_sequence_id = x_bill_sequence_id
and revised_item_sequence_id = x_revised_item_sequence_id
AND (common_component_sequence_id IS NULL
OR common_component_sequence_id = component_sequence_id)
-- This is to ensure that the destination bill's revised item
-- reschedule doesnt affect its components effectivity date
and implementation_date is null;
update BOM_INVENTORY_COMPONENTS
set disable_date = x_scheduled_date
where change_notice = x_change_notice
and bill_sequence_id = x_bill_sequence_id
and revised_item_sequence_id = x_revised_item_sequence_id
and implementation_date is null
and acd_type = 3;
update BOM_INVENTORY_COMPONENTS
set effectivity_date = x_scheduled_date,
from_end_item_unit_number = x_from_end_item_unit_number,
last_update_date = sysdate,
last_updated_by = BOM_Globals.Get_User_Id,
last_update_login = BOM_Globals.Get_User_Id
where change_notice = x_change_notice
and bill_sequence_id = x_bill_sequence_id
and revised_item_sequence_id = x_revised_item_sequence_id
AND common_component_sequence_id is not NULL
and implementation_date is null
and acd_type = 2;
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);
END Update_Inventory_Components;
* API Name : UPDATE_REVISION_CHANGE_NOTICE
* Parameters IN : p_revision_id, p_change_notice
* Parameters OUT: None
* Purpose : Updates the value of change_notice in the
* mtl_item_revisions_b/_tl table with the value passed as parameter
* for the row specified.
*********************************************************************/
PROCEDURE UPDATE_REVISION_CHANGE_NOTICE ( p_revision_id IN NUMBER
, p_change_notice IN VARCHAR2
) IS
l_language_code VARCHAR2(3);
UPDATE MTL_ITEM_REVISIONS_B
SET change_notice = p_change_notice,
last_update_date = SYSDATE,
last_update_login = l_login_id,
last_updated_by = l_user_id
WHERE revision_id = p_revision_id;
SELECT userenv('LANG')
INTO l_language_code
FROM dual;
UPDATE MTL_ITEM_REVISIONS_TL
SET last_update_date = SYSDATE, --who column
last_update_login = l_login_id, --who column
last_updated_by = l_user_id, --who column
source_lang = l_language_code
where revision_id = l_revision_id
AND LANGUAGE = l_language_code;
END UPDATE_REVISION_CHANGE_NOTICE;
SELECT 1
FROM mtl_item_revisions
WHERE revision_id = p_revision_id
AND inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id;
SELECT eri.revised_item_sequence_id
FROM eng_revised_items eri , mtl_system_items_vl msiv
WHERE eri.change_id = p_change_id
AND eri.organization_id = p_organization_id
AND eri.revised_item_id = p_revised_item_id
AND eri.revised_item_id = msiv.inventory_item_id
AND eri.organization_id = msiv.organization_id
AND decode(msiv.bom_item_type ,
4 , nvl(FND_PROFILE.value('ENG:STANDARD_ITEM_ECN_ACCESS'), 1) ,
3 , nvl(FND_PROFILE.value('ENG:PLANNING_ITEM_ECN_ACCESS'), 1) ,
2 , nvl(FND_PROFILE.value('ENG:MODEL_ITEM_ECN_ACCESS'), 1) ,
1 , nvl(FND_PROFILE.value('ENG:MODEL_ITEM_ECN_ACCESS'), 1) , 1) = 1
AND (eri.status_type = 1
OR (eri.status_type = 10
AND EXISTS
(SELECT 1
FROM eng_change_statuses ecsb
WHERE ecsb.status_code = eri.status_code
AND ecsb.status_type = 1)))
AND nvl(eri.new_item_revision_id, eri.current_item_revision_id)
= nvl(p_revision_id, nvl(eri.new_item_revision_id, eri.current_item_revision_id))
AND eri.scheduled_date IN
(SELECT eri2.scheduled_date
FROM eng_revised_items eri2
WHERE eri2.change_id = eri.change_id
AND eri2.organization_id = eri.organization_id
AND eri2.revised_item_id = eri.revised_item_id)
ORDER BY eri.scheduled_date DESC;
SELECT alternate_bom_designator
FROM bom_structures_b
WHERE bill_sequence_id = p_bill_sequence_id;
SELECT revised_item_sequence_id
FROM eng_revised_items
WHERE revised_item_id = p_revised_item_id
AND (p_effectivity_date IS NULL OR scheduled_date = p_effectivity_date)
AND bill_sequence_id = p_bill_sequence_id
AND NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR)
= nvl(p_from_end_item_unit_number, FND_API.G_MISS_CHAR)
AND nvl(from_end_item_rev_id, '-1')
= nvl(p_from_end_item_rev_id, '-1')
AND change_id = p_change_id
AND status_type IN (1);
SELECT revised_item_sequence_id, acd_type, component_sequence_id
FROM bom_components_b
WHERE component_item_id = p_component_item_id
AND (p_effectivity_date IS NULL OR effectivity_date = p_effectivity_date)
AND bill_sequence_id = p_bill_sequence_id
AND NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR)
= nvl(p_from_end_item_unit_number, FND_API.G_MISS_CHAR)
AND nvl(from_end_item_rev_id, '-1')
= nvl(p_from_end_item_rev_id, '-1')
AND change_notice = p_change_notice
AND old_component_sequence_id = p_old_component_sequence_id
AND implementation_date IS NULL;
ELSIF (p_transaction_type = 'DELETE')
THEN
x_change_transaction_type := 'DELETE';
ELSIF (p_transaction_type = 'UPDATE')
THEN
OPEN c_query_revised_component;
SELECT eri.revised_item_sequence_id
FROM eng_revised_items eri , mtl_system_items_vl msiv , bom_components_b bcb
WHERE eri.revised_item_sequence_id = bcb.revised_item_sequence_id
and bcb.component_sequence_id = p_component_sequence_id
AND eri.revised_item_id = msiv.inventory_item_id
AND eri.organization_id = msiv.organization_id
-- 1: revised item privilege based on profile access values
AND decode(msiv.bom_item_type ,
4 , nvl(FND_PROFILE.value('ENG:STANDARD_ITEM_ECN_ACCESS'), 1) ,
3 , nvl(FND_PROFILE.value('ENG:PLANNING_ITEM_ECN_ACCESS'), 1) ,
2 , nvl(FND_PROFILE.value('ENG:MODEL_ITEM_ECN_ACCESS'), 1) ,
1 , nvl(FND_PROFILE.value('ENG:MODEL_ITEM_ECN_ACCESS'), 1) , 1) = 1
-- 2: revised item status check
AND (eri.status_type = 1
OR (eri.status_type = 10
AND EXISTS
(SELECT 1
FROM eng_change_statuses ecsb
WHERE ecsb.status_code = eri.status_code
AND ecsb.status_type = 1)))
-- 3: common bom for src pending changes
AND bcb.bill_sequence_id = eri.bill_sequence_id;