[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Procedure : update_new_description
* Parameters IN : p_api_version, p_revised_item_sequence_id, p_new_description
* Purpose : Update the new_item_description column of the eng_revised_items table with the given value
*****************************************************************************/
PROCEDURE update_new_description
(
p_api_version IN NUMBER := 1.0
, p_revised_item_sequence_id IN NUMBER
, p_new_description mtl_system_items_b.description%TYPE
) IS
BEGIN
UPDATE ENG_REVISED_ITEMS SET NEW_ITEM_DESCRIPTION = p_new_description WHERE REVISED_ITEM_SEQUENCE_ID = p_revised_item_sequence_id;
* Procedure : Delete_Routing_Details
* Parameters IN : Revised Item and Routing Header Key Column.
* Purpose : Delete Routing Details Routing from Tables.
*****************************************************************************/
PROCEDURE Delete_Routing_Details
( p_organization_id IN NUMBER
, p_revised_item_id IN NUMBER
, p_revised_item_sequence_id IN NUMBER
, p_routing_sequence_id IN NUMBER
, p_change_notice IN VARCHAR2 )
IS
BEGIN
DELETE FROM MTL_RTG_ITEM_REVISIONS
where organization_id = p_organization_id
and inventory_item_id = p_revised_item_id
and revised_item_sequence_Id = p_revised_item_sequence_id
and change_notice = p_change_notice
and implementation_date is null;
DELETE FROM ENG_CURRENT_SCHEDULED_DATES
where organization_id = p_organization_id
and revised_item_id = p_revised_item_id
and revised_item_sequence_Id = p_revised_item_sequence_id
and change_notice = p_change_notice ;
DELETE FROM BOM_OPERATIONAL_ROUTINGS bor
where bor.routing_sequence_id = p_routing_sequence_id
and bor.pending_from_ecn = p_change_notice
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_change_notice
or
(bos.change_notice = p_change_notice
and bos.revised_item_sequence_id <> p_revised_item_sequence_id)))
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 eri
where eri.organization_id = bor.organization_id
and eri.bill_sequence_id = bor.routing_sequence_id
and eri.change_notice <> p_change_notice
);
END Delete_Routing_Details;
* Procedure : Insert_Routing_Revisions
* Parameters IN : Routing Revision Column.
* Purpose : Insert the New Routing Revision Record into MTL_RTG_ITEM_REVISIONS
****************************************************************************/
PROCEDURE Insert_Routing_Revisions
( p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_revision IN VARCHAR2
, p_user_id IN NUMBER
, p_login_id IN NUMBER
, p_change_notice IN VARCHAR2
, p_effectivity_date IN DATE
, p_revised_item_sequence_id IN NUMBER
)
IS
BEGIN
INSERT INTO MTL_RTG_ITEM_REVISIONS
( inventory_item_id
, organization_id
, process_revision
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, change_notice
, ecn_initiation_date
, effectivity_date
, revised_item_sequence_id
)
VALUES
( p_inventory_item_id
, p_organization_id
, p_revision
, SYSDATE
, p_user_id
, SYSDATE
, p_user_id
, p_login_id
, p_change_notice
, SYSDATE
, DECODE(p_effectivity_date
, TRUNC(SYSDATE), SYSDATE
, p_effectivity_date)
, p_revised_item_sequence_id
) ;
END Insert_Routing_Revisions ;
Select 'x' dummy
From dual
Where not exists (
Select null
From eng_revised_items eri
Where eri.change_notice = p_change_notice
And eri.organization_id = p_organization_id
And eri.status_type not in (6, 5));
Select 'x' dummy
From dual
Where exists (
Select null
From eng_revised_items eri
Where eri.change_notice = p_change_notice
And eri.organization_id = p_organization_id
And eri.status_type = 6);
l_token_tbl.delete;
UPDATE ENG_ENGINEERING_CHANGES
SET IMPLEMENTATION_DATE = SYSDATE,
STATUS_TYPE = 6,
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_LOGIN = p_login
WHERE ORGANIZATION_ID = p_organization_id
AND CHANGE_NOTICE = p_change_notice;
UPDATE ENG_ENGINEERING_CHANGES
SET CANCELLATION_DATE = SYSDATE,
STATUS_TYPE = 5,
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_LOGIN = p_login
WHERE ORGANIZATION_ID = p_organization_id
AND CHANGE_NOTICE = p_change_notice;
* In doing so the procedure will delete the corresponding
* revisions and will also make sure that the underlying
* entities also get cancelled.
*
* History : 09/01/2000 MK ECO for Routing.
******************************************************************************/
Procedure Cancel_Revised_Item
( rev_item_seq IN NUMBER
, bill_seq_id IN NUMBER
, routing_seq_id IN NUMBER -- Added by MK on 09/01/2000
, user_id IN NUMBER
, login IN NUMBER
, change_order IN VARCHAR2
, cancel_comments IN VARCHAR2
, p_Mesg_Token_Tbl IN Error_Handler.Mesg_Token_Tbl_Type
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_err_text VARCHAR2(2000) := NULL;
SELECT component_sequence_id
FROM bom_inventory_components
WHERE revised_item_sequence_id = rev_item_seq;
SELECT operation_sequence_id
, operation_seq_num
FROM BOM_OPERATION_SEQUENCES
WHERE revised_item_sequence_id = rev_item_seq ;
select routing_sequence_id
from bom_operational_routings bor
where bor.routing_sequence_id = routing_seq_id
and bor.pending_from_ecn = change_order
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 <> change_order
or
(bos.change_notice = change_order
and bos.revised_item_sequence_id <> rev_item_seq)))
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)
and not exists (select null
from MTL_RTG_ITEM_REVISIONS mriv
where mriv.organization_id = bor.organization_id
and mriv.inventory_item_id = bor.assembly_item_id
and mriv.implementation_date is not null
and mriv.change_notice is null))
or
(bor.alternate_routing_designator is not null))
and not exists (select null
from ENG_REVISED_ITEMS eri
where eri.organization_id = bor.organization_id
and eri.routing_sequence_id = bor.routing_sequence_id
and eri.revised_item_sequence_id <> rev_item_seq
and eri.status_type <> 5);
DELETE FROM bom_components_b --BOM_INVENTORY_COMPONENTS IC -- R12: Modified for common bom changes
WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq;
/* delete from MTL_ITEM_REVISIONS_TL
where revision_id IN (select revision_id
from MTL_ITEM_REVISIONS_B
where REVISED_ITEM_SEQUENCE_ID = rev_item_seq);*/
delete from MTL_ITEM_REVISIONS_TL
where revision_id IN (select new_item_revision_id
from eng_revised_items I
WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq);
DELETE FROM MTL_ITEM_REVISIONS_B I
where revision_id IN (select new_item_revision_id
from eng_revised_items I
WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq);
DELETE FROM BOM_BILL_OF_MATERIALS B
WHERE B.BILL_SEQUENCE_ID = bill_seq_id
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.REVISED_ITEM_SEQUENCE_ID IS NULL
OR C.REVISED_ITEM_SEQUENCE_ID <> rev_item_seq))
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
(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.REVISED_ITEM_SEQUENCE_ID <> rev_item_seq
AND R.STATUS_TYPE <> 5)));
UPDATE ENG_REVISED_ITEMS R
SET BILL_SEQUENCE_ID = ''
, cancel_comments = cancel_comments
, cancellation_date = SYSDATE
WHERE R.REVISED_ITEM_SEQUENCE_ID = rev_item_seq;
DELETE FROM BOM_OPERATION_SEQUENCES
WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq;
DELETE FROM MTL_RTG_ITEM_REVISIONS I
WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq
AND implementation_date IS NULL; -- bug 3668603: delete only unimplemented revisions
DELETE FROM MTL_RTG_ITEM_REVISIONS rev
WHERE EXISTS (SELECT 1
FROM BOM_OPERATIONAL_ROUTINGS bor
WHERE bor.routing_sequence_id = routing_seq_id
AND bor.alternate_routing_designator IS NULL
AND bor.assembly_item_id = rev.INVENTORY_ITEM_ID
AND bor.organization_id = rev.organization_id);
DELETE FROM BOM_OPERATIONAL_ROUTINGS
WHERE routing_sequence_id = routing_seq_id;
UPDATE BOM_OPERATIONAL_ROUTINGS
SET last_update_date = SYSDATE,
last_updated_by = user_id,
last_update_login = login,
pending_from_ecn = null
WHERE routing_sequence_id = routing_seq_id
AND pending_from_ecn = change_order;
/* DELETE FROM BOM_OPERATIONAL_ROUTINGS bor1
WHERE bor1.routing_sequence_id = routing_seq_id
AND bor1.pending_from_ecn = change_order
AND NOT EXISTS (SELECT NULL
FROM BOM_OPERATION_SEQUENCES bos
WHERE bos.ROUTING_SEQUENCE_ID = bor1.ROUTING_SEQUENCE_ID
AND (bos.CHANGE_NOTICE IS NULL
OR bos.CHANGE_NOTICE <> change_order)
)
AND ((bor1.ALTERNATE_ROUTING_DESIGNATOR IS NULL
AND NOT EXISTS (SELECT NULL
FROM BOM_OPERATIONAL_ROUTINGS bor2
WHERE bor2.ORGANIZATION_ID = bor1.ORGANIZATION_ID
AND bor2.ASSEMBLY_ITEM_ID = bor1.ASSEMBLY_ITEM_ID
AND bor2.ALTERNATE_ROUTING_DESIGNATOR IS NOT NULL))
OR
(bor1.ALTERNATE_ROUTING_DESIGNATOR IS NOT NULL
AND NOT EXISTS (SELECT NULL
FROM ENG_REVISED_ITEMS eri
WHERE eri.ORGANIZATION_ID = bor1.ORGANIZATION_ID
AND eri.ROUTING_SEQUENCE_ID = bor1.ROUTING_SEQUENCE_ID
AND eri.CHANGE_NOTICE <> change_order)));*/
UPDATE ENG_REVISED_ITEMS eri
SET routing_sequence_id = ''
, cancel_comments = cancel_comments
, cancellation_date = SYSDATE
WHERE eri.REVISED_ITEM_SEQUENCE_ID = rev_item_seq;
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,
x_original_system_reference VARCHAR2,
x_comments VARCHAR2) -- Bug 3589974
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,
original_system_reference,
comments -- Bug 3589974
)
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,
x_original_system_reference,
x_comments -- Bug 3589974
);
END Insert_Current_Scheduled_Dates;
PROCEDURE Update_Component_Unit_Number
( p_new_from_end_item_number VARCHAR2
, p_revised_item_sequence_id NUMBER
)
IS
BEGIN
UPDATE bom_inventory_components
SET from_end_item_unit_number = p_new_from_end_item_number
WHERE revised_item_sequence_id = p_revised_item_sequence_id
AND implementation_date IS NOT NULL;
END Update_Component_Unit_Number;
* Procedure : Update_Rev_Operations
* Parameters IN : Revised item sequence Id
* Routing Sequence Id
* Scheduled Date(Effectivity Date)
* Change notice
* Purpose : Procedure will perform the update of effectivity date
* and disable date in revised operations when user trying
* to reschedule parent revised item.
* History : 11/13/2000 MK Added in ECO for Routing.
******************************************************************************/
PROCEDURE Update_Rev_Operations (x_change_notice VARCHAR2,
x_routing_sequence_id NUMBER,
x_revised_item_sequence_id NUMBER,
x_scheduled_date DATE,
x_from_end_item_unit_number VARCHAR2 DEFAULT NULL)
IS
BEGIN
UPDATE BOM_OPERATION_SEQUENCES
SET effectivity_date = x_scheduled_date
-- , from_end_item_unit_number = x_from_end_item_unit_number
WHERE implementation_date IS NULL
AND change_notice = x_change_notice
AND revised_item_sequence_id = x_revised_item_sequence_id
AND routing_sequence_id = x_routing_sequence_id ;
UPDATE BOM_OPERATION_SEQUENCES
SET disable_date = x_scheduled_date
WHERE implementation_date IS NULL
AND acd_type = 3
AND change_notice = x_change_notice
AND revised_item_sequence_id = x_revised_item_sequence_id
AND routing_sequence_id = x_routing_sequence_id ;
END Update_Rev_Operations ;
PROCEDURE Update_New_Rev_Lifecycle(
p_revised_item_seq_id IN NUMBER
, p_revised_item_id IN NUMBER
, p_org_id IN NUMBER
,p_lifecycle_name IN VARCHAR2
,p_new_item_revision IN VARCHAR2
,p_change_notice IN VARCHAR2
, x_Return_Status OUT NOCOPY VARCHAR2
)
is
l_new_life_cycle_state_id NUMBER;
SELECT 1
INTO l_fetch_lifecycle
FROM eng_engineering_changes
WHERE nvl(plm_or_erp_change , 'PLM') = 'PLM'
AND change_notice = p_change_notice
AND organization_id = p_org_id;
UPDATE ENG_REVISED_ITEMS
SET new_item_revision_id = l_new_item_rev_id,
new_lifecycle_state_id = l_new_life_cycle_state_id
WHERE REVISED_ITEM_SEQUENCE_ID = p_revised_item_seq_id;
end Update_New_Rev_Lifecycle;
* Procedure : Update_Row
* Parameters IN : Revised item exposed column record.
* Revised item unexposed column record
* Parameters OUT: Mesg Token Table
* Return Status
* Purpose : Update row procedure will update the revised item record. It
* will check if the user has tried to update the schedule date
* and if the date has been updated then it will update the
* dates on it revision and will also update the dates on all
* revised components on that revised item. If the user has
* updated the use up plan name or the item, then a new schedule
* must be fetched and updated in all the corresponding entities.
******************************************************************************/
PROCEDURE Update_Row
( p_revised_item_rec IN ENG_Eco_PUB.Revised_Item_Rec_Type
, p_rev_item_unexp_rec IN Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
, p_control_rec IN BOM_BO_Pub.Control_Rec_Type
:= BOM_BO_PUB.G_DEFAULT_CONTROL_REC
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status OUT NOCOPY VARCHAR2
)
IS
l_err_text VARCHAR2(2000);
UPDATE ENG_REVISED_ITEMS
SET CHANGE_NOTICE = p_revised_item_rec.eco_name
, ORGANIZATION_ID = p_rev_item_unexp_rec.organization_id
, REVISED_ITEM_ID = p_rev_item_unexp_rec.revised_item_id
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATED_BY = l_User_Id
, LAST_UPDATE_LOGIN = l_Login_Id
, IMPLEMENTATION_DATE =
DECODE( p_rev_item_unexp_rec.implementation_date,
FND_API.G_MISS_DATE,
to_date(NULL),
p_rev_item_unexp_rec.implementation_date
)
, CANCELLATION_DATE =
DECODE(p_rev_item_unexp_rec.cancellation_date,
FND_API.G_MISS_DATE,
to_date(NULL),
p_rev_item_unexp_rec.cancellation_date
)
, CANCEL_COMMENTS = p_revised_item_rec.cancel_comments
, DISPOSITION_TYPE = p_revised_item_rec.disposition_type
, NEW_ITEM_REVISION = --p_revised_item_rec.updated_revised_item_revision -- Added by MK
-- Comment Out by MK on 10/24/00 --Bug 2953132
DECODE(p_revised_item_rec.updated_revised_item_revision,
NULL,
p_revised_item_rec.new_revised_item_revision,
p_revised_item_rec.updated_revised_item_revision
)
, EARLY_SCHEDULE_DATE =
DECODE(p_revised_item_rec.earliest_effective_date,
FND_API.G_MISS_DATE,
to_date(NULL),
p_revised_item_rec.earliest_effective_date
)
, ATTRIBUTE_CATEGORY = p_revised_item_rec.attribute_category
, ATTRIBUTE2 = p_revised_item_rec.attribute2
, ATTRIBUTE3 = p_revised_item_rec.attribute3
, ATTRIBUTE4 = p_revised_item_rec.attribute4
, ATTRIBUTE5 = p_revised_item_rec.attribute5
, ATTRIBUTE7 = p_revised_item_rec.attribute7
, ATTRIBUTE8 = p_revised_item_rec.attribute8
, ATTRIBUTE9 = p_revised_item_rec.attribute9
, ATTRIBUTE11 = p_revised_item_rec.attribute11
, ATTRIBUTE12 = p_revised_item_rec.attribute12
, ATTRIBUTE13 = p_revised_item_rec.attribute13
, ATTRIBUTE14 = p_revised_item_rec.attribute14
, ATTRIBUTE15 = p_revised_item_rec.attribute15
, STATUS_TYPE = p_revised_item_rec.status_type
, SCHEDULED_DATE =
DECODE(p_revised_item_rec.new_effective_date, to_date(NULL),
p_revised_item_rec.start_effective_date,
p_revised_item_rec.new_effective_date
)
, BILL_SEQUENCE_ID = p_rev_item_unexp_rec.bill_sequence_id
, MRP_ACTIVE = p_revised_item_rec.mrp_active
, PROGRAM_ID = l_Prog_Id
, PROGRAM_UPDATE_DATE = SYSDATE
, UPDATE_WIP = p_revised_item_rec.update_wip
, USE_UP = p_rev_item_unexp_rec.use_up
, USE_UP_ITEM_ID = p_rev_item_unexp_rec.use_up_item_id
, REVISED_ITEM_SEQUENCE_ID=p_rev_item_unexp_rec.revised_item_sequence_id
, USE_UP_PLAN_NAME = p_revised_item_rec.use_up_plan_name
, DESCRIPTIVE_TEXT = p_revised_item_rec.change_description
, AUTO_IMPLEMENT_DATE = trunc(p_rev_item_unexp_rec.auto_implement_date)
, FROM_END_ITEM_UNIT_NUMBER= p_revised_item_rec.from_end_item_unit_number
, ATTRIBUTE1 = p_revised_item_rec.attribute1
, ATTRIBUTE6 = p_revised_item_rec.attribute6
, ATTRIBUTE10 = p_revised_item_rec.attribute10
, Original_System_Reference =
p_revised_item_rec.original_system_reference
-- Added by MK on 08/26/2000 ECO for Routing
, FROM_WIP_ENTITY_ID = p_rev_item_unexp_rec.from_wip_entity_id
, TO_WIP_ENTITY_ID = p_rev_item_unexp_rec.to_wip_entity_id
, FROM_CUM_QTY = p_revised_item_rec.from_cumulative_quantity
, LOT_NUMBER = p_revised_item_rec.lot_number
, CFM_ROUTING_FLAG = p_rev_item_unexp_rec.cfm_routing_flag
, COMPLETION_SUBINVENTORY = p_revised_item_rec.completion_subinventory
, COMPLETION_LOCATOR_ID = p_rev_item_unexp_rec.completion_locator_id
-- , MIXED_MODEL_MAP_FLAG = p_rev_item_unexp_rec.mixed_model_map_flag
, PRIORITY = p_revised_item_rec.priority
, CTP_FLAG = p_revised_item_rec.ctp_flag
, ROUTING_SEQUENCE_ID = p_rev_item_unexp_rec.routing_sequence_id
, NEW_ROUTING_REVISION = p_revised_item_rec.updated_routing_revision -- Added by MK
-- Comment out by MK on 10/24/00
-- DECODE(p_revised_item_rec.updated_routing_revision ,
-- NULL,
-- p_revised_item_rec.new_routing_revision,
-- p_revised_item_rec.updated_routing_revision
-- )
, ROUTING_COMMENT = p_revised_item_rec.routing_comment
, ECO_FOR_PRODUCTION = p_revised_item_rec.eco_for_production -- Added by MK on 10/06/00
, CHANGE_ID = p_rev_item_unexp_rec.change_id --Added on 12/12/02
, Transfer_Or_Copy = p_revised_item_rec.Transfer_Or_Copy
, Transfer_OR_Copy_Item = p_revised_item_rec.Transfer_OR_Copy_Item
, Transfer_OR_Copy_Bill = p_revised_item_rec.Transfer_OR_Copy_Bill
, Transfer_OR_Copy_Routing = p_revised_item_rec.Transfer_OR_Copy_Routing
, Copy_To_Item = p_revised_item_rec.Copy_To_Item
, Copy_To_Item_Desc = p_revised_item_rec.Copy_To_Item_Desc
, selection_option= p_revised_item_rec.selection_option
, selection_date = p_revised_item_rec.selection_date
, selection_unit_number= p_revised_item_rec.selection_unit_number
, STATUS_code = nvl(p_rev_item_unexp_rec.status_code, p_revised_item_rec.status_type) -- Bug 3424007
WHERE REVISED_ITEM_SEQUENCE_ID = p_rev_item_unexp_rec.revised_item_sequence_id
;
( p_Message_Name => 'ENG_REV_ITEM_REC_DELETED'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
);
l_err_text := G_PKG_NAME || ' : Utility (Revised Item Update) '
|| SUBSTR(SQLERRM, 1, 200);
if any existing need to be deleted ro modified.
*/
BEGIN
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Action on Item Revision is :'||to_char(Eng_Default_Revised_Item.G_DEL_UPD_INS_ITEM_REV) ); END IF;
ENG_REVISED_ITEMS_PKG.Delete_Item_Revisions
( x_change_notice =>
p_revised_item_rec.eco_name
, x_organization_id =>
p_rev_item_unexp_rec.organization_id
, x_inventory_item_id =>
p_rev_item_unexp_rec.revised_item_id
, x_revised_item_sequence_id =>
p_rev_item_unexp_rec.revised_item_sequence_id
);
Error_Handler.Write_Debug(p_revised_item_rec.updated_revised_item_revision
|| p_revised_item_rec.start_effective_date
||p_revised_item_rec.new_effective_date
|| p_revised_item_rec.eco_name
|| p_rev_item_unexp_rec.organization_id
|| p_rev_item_unexp_rec.revised_item_id
|| p_rev_item_unexp_rec.revised_item_sequence_id);
/* ENG_REVISED_ITEMS_PKG.Update_Item_Revisions
( x_revision =>
p_revised_item_rec.updated_revised_item_revision
-- , x_scheduled_date => p_revised_item_rec.new_effective_date
, x_scheduled_date =>
DECODE(
DECODE(p_revised_item_rec.new_effective_date,
to_date(NULL), p_revised_item_rec.start_effective_date,
p_revised_item_rec.new_effective_date),
TRUNC(SYSDATE), SYSDATE,
DECODE(p_revised_item_rec.new_effective_date,
to_date(NULL), p_revised_item_rec.start_effective_date,
p_revised_item_rec.new_effective_date)
)
, x_change_notice =>
p_revised_item_rec.eco_name
, x_organization_id =>
p_rev_item_unexp_rec.organization_id
, x_inventory_item_id =>
p_rev_item_unexp_rec.revised_item_id
, x_revised_item_sequence_id =>
p_rev_item_unexp_rec.revised_item_sequence_id
);
UPDATE MTL_ITEM_REVISIONS_B
SET revision =
DECODE( p_revised_item_rec.updated_revised_item_revision
, FND_API.G_MISS_CHAR
, p_revised_item_rec.new_revised_item_revision
, NULL
, p_revised_item_rec.new_revised_item_revision
, p_revised_item_rec.updated_revised_item_revision
)
--Bug No:3612330 added by sseraphi to update the rev label also with rev code.
, revision_label =
DECODE( p_revised_item_rec.updated_revised_item_revision
, FND_API.G_MISS_CHAR
, p_revised_item_rec.new_revised_item_revision
, NULL
, p_revised_item_rec.new_revised_item_revision
, p_revised_item_rec.updated_revised_item_revision
)
, effectivity_date =
DECODE( DECODE( p_revised_item_rec.new_effective_date
, to_date(NULL)
, p_revised_item_rec.start_effective_date
, p_revised_item_rec.new_effective_date
),
TRUNC(SYSDATE), SYSDATE,
DECODE( p_revised_item_rec.new_effective_date
, NULL
, p_revised_item_rec.start_effective_date
, p_revised_item_rec.new_effective_date
)
)
, description = Decode(p_revised_item_rec.new_revised_item_rev_desc,
FND_API.G_MISS_CHAR,
description,
p_revised_item_rec.new_revised_item_rev_desc)
, last_update_date = SYSDATE
, last_update_login = l_login_id
, last_updated_by = l_user_id
WHERE change_notice = p_revised_item_rec.eco_name
AND organization_id = p_rev_item_unexp_rec.organization_id
AND inventory_item_id = p_rev_item_unexp_rec.revised_item_id
AND revised_item_sequence_id =
p_rev_item_unexp_rec.revised_item_sequence_id
AND revision = nvl( p_revised_item_rec.new_revised_item_revision,'NULL')
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
/* Item revision description support Bug: 1667419*/
description = Decode(p_revised_item_rec.new_revised_item_rev_desc,
FND_API.G_MISS_CHAR,
description,
p_revised_item_rec.new_revised_item_rev_desc),
source_lang = l_language_code
where revision_id = l_revision_id
AND LANGUAGE = l_language_code;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Inserting Item Revisions . . .'); END IF;
ENG_REVISED_ITEMS_PKG.Insert_Item_Revisions
( x_inventory_item_id =>
p_rev_item_unexp_rec.revised_item_id
, x_organization_id =>
p_rev_item_unexp_rec.organization_id
, x_revision =>
p_revised_item_rec.updated_revised_item_revision
, x_userid =>
l_User_Id
, x_change_notice =>
p_revised_item_rec.eco_name
, x_scheduled_date => p_revised_item_rec.start_effective_date
, x_revised_item_sequence_id =>
p_rev_item_unexp_rec.revised_item_sequence_id
/* Item revision description support Bug: 1667419*/
, x_revision_description =>
p_revised_item_rec.new_revised_item_rev_desc
);
ENG_REVISED_ITEMS_PKG.Insert_Item_Revisions
( x_inventory_item_id =>
p_rev_item_unexp_rec.revised_item_id
, x_organization_id =>
p_rev_item_unexp_rec.organization_id
, x_revision =>
p_revised_item_rec.updated_revised_item_revision
, x_userid =>
l_User_Id
, x_change_notice =>
p_revised_item_rec.eco_name
, x_scheduled_date => p_revised_item_rec.new_effective_date
, x_revised_item_sequence_id =>
p_rev_item_unexp_rec.revised_item_sequence_id
/* Item revision description support Bug: 1667419*/
, x_revision_description =>
p_revised_item_rec.new_revised_item_rev_desc
);
UPDATE MTL_ITEM_REVISIONS_B
SET
description = Decode(p_revised_item_rec.new_revised_item_rev_desc,
FND_API.G_MISS_CHAR,
description,
p_revised_item_rec.new_revised_item_rev_desc)
/* Bug no :2905537
Revised item effectivity date updation doesnt update in item revisions table
adding effectivity_date to the update statement */
, effectivity_date =
DECODE( DECODE( p_revised_item_rec.new_effective_date
, to_date(NULL)
, p_revised_item_rec.start_effective_date
, p_revised_item_rec.new_effective_date
),
TRUNC(SYSDATE), SYSDATE,
DECODE( p_revised_item_rec.new_effective_date
, to_date(NULL)
, p_revised_item_rec.start_effective_date
, p_revised_item_rec.new_effective_date
)
)
/* End of bug 2905537 */
, last_update_date = SYSDATE
, last_update_login = l_login_id
, last_updated_by = l_user_id
WHERE change_notice = p_revised_item_rec.eco_name
AND organization_id = p_rev_item_unexp_rec.organization_id
AND inventory_item_id = p_rev_item_unexp_rec.revised_item_id
AND revised_item_sequence_id =
p_rev_item_unexp_rec.revised_item_sequence_id
AND revision = nvl( p_revised_item_rec.new_revised_item_revision,'NULL')
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 = Decode(p_revised_item_rec.new_revised_item_rev_desc,
FND_API.G_MISS_CHAR,
description,
p_revised_item_rec.new_revised_item_rev_desc),
source_lang = l_language_code
where revision_id = l_revision_id
AND LANGUAGE = l_language_code;
DELETE FROM MTL_RTG_ITEM_REVISIONS
WHERE implementation_date IS NULL
AND change_notice = p_revised_item_rec.eco_name
AND revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id
AND organization_id = p_rev_item_unexp_rec.organization_id
AND inventory_item_id = p_rev_item_unexp_rec.revised_item_id ;
UPDATE MTL_RTG_ITEM_REVISIONS
SET process_revision = p_revised_item_rec.updated_routing_revision
, effectivity_date = DECODE( DECODE(p_revised_item_rec.new_effective_date,
to_date(NULL), p_revised_item_rec.start_effective_date,
p_revised_item_rec.new_effective_date)
, TRUNC(SYSDATE), SYSDATE
, DECODE(p_revised_item_rec.new_effective_date,
to_date(NULL), p_revised_item_rec.start_effective_date,
p_revised_item_rec.new_effective_date)
)
, last_update_date = SYSDATE
, last_updated_by = l_user_id
, last_update_login = l_login_id
WHERE change_notice = p_revised_item_rec.eco_name
AND revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id
AND organization_id = p_rev_item_unexp_rec.organization_id
AND inventory_item_id = p_rev_item_unexp_rec.revised_item_id
AND process_revision = nvl(p_revised_item_rec.new_routing_revision, 'NULL') ;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Inserting Routing Revisions . . . '); END IF;
Insert_Routing_Revisions
( p_inventory_item_id => p_rev_item_unexp_rec.revised_item_id
, p_organization_id => p_rev_item_unexp_rec.organization_id
, p_revision => p_revised_item_rec.updated_routing_revision
, p_user_id => l_user_id
, p_login_id => l_login_id
, p_change_notice => p_revised_item_rec.eco_name
, p_effectivity_date => p_revised_item_rec.start_effective_date
, p_revised_item_sequence_id => p_rev_item_unexp_rec.revised_item_sequence_id
);
Insert_Routing_Revisions
( p_inventory_item_id => p_rev_item_unexp_rec.revised_item_id
, p_organization_id => p_rev_item_unexp_rec.organization_id
, p_revision => p_revised_item_rec.updated_routing_revision
, p_user_id => l_user_id
, p_login_id => l_login_id
, p_change_notice => p_revised_item_rec.eco_name
, p_effectivity_date => p_revised_item_rec.new_effective_date
, p_revised_item_sequence_id => p_rev_item_unexp_rec.revised_item_sequence_id
);
ENG_REVISED_ITEMS_PKG.Update_Inventory_Components
( x_change_notice =>
p_revised_item_rec.eco_name
, x_bill_sequence_id =>
p_rev_item_unexp_rec.bill_sequence_id
, x_revised_item_sequence_id =>
p_rev_item_unexp_rec.revised_item_sequence_id
, x_scheduled_date =>
-- p_revised_item_rec.start_effective_date
p_revised_item_rec.new_effective_date -- Added by MK on 11/13/00
, x_from_end_item_unit_number =>
p_revised_item_rec.from_end_item_unit_number
);
Update_Rev_Operations
( x_change_notice => p_revised_item_rec.eco_name
, x_routing_sequence_id => p_rev_item_unexp_rec.routing_sequence_id
, x_revised_item_sequence_id => p_rev_item_unexp_rec.revised_item_sequence_id
, x_scheduled_date => p_revised_item_rec.new_effective_date
) ;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Inserting current schedule date . . .'); END IF;
/*SELECT person_id
INTO req_id
FROM eng_security_people_v
WHERE user_id = l_User_Id;*/
/*SELECT party.PARTY_ID
INTO req_id
FROM HZ_PARTIES party, fnd_user fu
WHERE fu.user_id = l_User_Id
AND to_char(fu.employee_id) = party.person_identifier
AND ROWNUM = 1;*/
SELECT ppf.party_id INTO req_id
FROM per_people_f ppf, fnd_user fu
WHERE fu.user_id = l_User_Id
AND fu.employee_id = ppf.person_id
AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date; -- Fix for 4293553
Insert_Current_Scheduled_Dates
( x_change_notice =>
p_revised_item_rec.eco_name
, x_organization_id =>
p_rev_item_unexp_rec.organization_id
, x_revised_item_id =>
p_rev_item_unexp_rec.revised_item_id
, x_scheduled_date =>
p_revised_item_rec.start_effective_date
/* bug 8744651 p_revised_item_rec.new_effective_date -- p_revised_item_rec.start_effective_date
-- Bug 3589974 : Using the new effectivity date */
, x_revised_item_sequence_id =>
p_rev_item_unexp_rec.revised_item_sequence_id
, x_requestor_id =>
req_id
, x_userid =>
l_User_Id
, x_original_system_reference =>
p_revised_item_rec.original_system_reference
, x_comments => p_revised_item_rec.reschedule_comments -- Bug 3589974
);
UPDATE MTL_RTG_ITEM_REVISIONS
SET effectivity_date =
DECODE( DECODE( p_revised_item_rec.new_effective_date
, to_date(NULL)
, p_revised_item_rec.start_effective_date
, p_revised_item_rec.new_effective_date
)
, TRUNC(SYSDATE), SYSDATE
, DECODE( p_revised_item_rec.new_effective_date
, to_date(NULL)
, p_revised_item_rec.start_effective_date
, p_revised_item_rec.new_effective_date
)
)
, last_update_date = SYSDATE
, last_updated_by = l_user_id
, last_update_login = l_login_id
WHERE change_notice = p_revised_item_rec.eco_name
AND organization_id = p_rev_item_unexp_rec.organization_id
AND inventory_item_id = p_rev_item_unexp_rec.revised_item_id
AND revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id ;
UPDATE MTL_ITEM_REVISIONS_B
SET effectivity_date =
DECODE( DECODE( p_revised_item_rec.new_effective_date
, to_date(NULL)
, p_revised_item_rec.start_effective_date
, p_revised_item_rec.new_effective_date
),
TRUNC(SYSDATE), SYSDATE,
DECODE( p_revised_item_rec.new_effective_date
, to_date(NULL)
, p_revised_item_rec.start_effective_date
, p_revised_item_rec.new_effective_date
)
)
, description = Decode(p_revised_item_rec.new_revised_item_rev_desc,
FND_API.G_MISS_CHAR,
description,
p_revised_item_rec.new_revised_item_rev_desc)
, last_update_date = SYSDATE
, last_updated_by = l_user_id
, last_update_login = l_login_id
WHERE change_notice = p_revised_item_rec.eco_name
AND organization_id = p_rev_item_unexp_rec.organization_id
AND inventory_item_id = p_rev_item_unexp_rec.revised_item_id
AND revised_item_sequence_id =
p_rev_item_unexp_rec.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 = Decode(p_revised_item_rec.new_revised_item_rev_desc,
FND_API.G_MISS_CHAR,
description,
p_revised_item_rec.new_revised_item_rev_desc),
source_lang = l_language_code
where revision_id = l_revision_id
AND LANGUAGE = l_language_code;
Update_Component_Unit_Number
( p_new_from_end_item_number =>
p_revised_item_rec.new_from_end_item_unit_number
, p_revised_item_sequence_id =>
p_rev_item_unexp_rec.revised_item_sequence_id
);
UPDATE BOM_OPERATION_SEQUENCES
SET eco_for_production = p_revised_item_rec.eco_for_production
WHERE revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id ;
UPDATE BOM_INVENTORY_COMPONENTS
SET eco_for_production = p_revised_item_rec.eco_for_production
WHERE revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id ;
Update_New_Rev_Lifecycle(
p_revised_item_seq_id => p_rev_item_unexp_rec.revised_item_sequence_id
, p_revised_item_id => p_rev_item_unexp_rec.revised_item_id
, p_org_id => p_rev_item_unexp_rec.organization_id
, p_lifecycle_name => p_revised_item_rec.new_lifecycle_phase_name
, p_new_item_revision => p_revised_item_rec.New_Revised_Item_Revision
, p_change_notice => p_revised_item_rec.eco_name
, x_Return_Status => x_return_status);
END; /* Inser/Update/Delete revision and check reschedule block Ends */
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('returning from update_row'); END IF;
END Update_Row;
* Procedure : Insert_Row
* Paramaters IN : Revised item exposed column record
* Revised item unexposed column record
* Parameters OUT: Mesg Token Table
* Return Status
* Purpose : Procedure will insert a new revised item record. It will also
* add any new revision if the user has added a revision that
* does not exist. Also an entry into the table eng_current_
* effective dates is also made for the new item.
*****************************************************************************/
PROCEDURE Insert_Row
( p_revised_item_rec IN ENG_Eco_PUB.Revised_Item_Rec_Type
, p_rev_item_unexp_rec IN Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
, p_control_rec IN BOM_BO_Pub.Control_Rec_Type
:= BOM_BO_PUB.G_DEFAULT_CONTROL_REC
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status OUT NOCOPY VARCHAR2
)
IS
l_assembly_type NUMBER := NULL;
SELECT current_phase_id
FROM mtl_system_items
WHERE inventory_item_id = cp_revised_item_id
and organization_id = cp_organization_id ;
INSERT INTO ENG_REVISED_ITEMS
(
CHANGE_NOTICE
, ORGANIZATION_ID
, REVISED_ITEM_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, IMPLEMENTATION_DATE
, CANCELLATION_DATE
, CANCEL_COMMENTS
, DISPOSITION_TYPE
, NEW_ITEM_REVISION
, EARLY_SCHEDULE_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, STATUS_TYPE
, SCHEDULED_DATE
, BILL_SEQUENCE_ID
, MRP_ACTIVE
, REQUEST_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, UPDATE_WIP
, USE_UP
, USE_UP_ITEM_ID
, REVISED_ITEM_SEQUENCE_ID
, USE_UP_PLAN_NAME
, DESCRIPTIVE_TEXT
, AUTO_IMPLEMENT_DATE
, FROM_END_ITEM_UNIT_NUMBER
, ATTRIBUTE1
, ATTRIBUTE6
, ATTRIBUTE10
, Original_System_Reference
/* Added by MK on 08/26/2000 ECO for Routing */
, FROM_WIP_ENTITY_ID
, TO_WIP_ENTITY_ID
, FROM_CUM_QTY
, LOT_NUMBER
, CFM_ROUTING_FLAG
, COMPLETION_SUBINVENTORY
, COMPLETION_LOCATOR_ID
-- , MIXED_MODEL_MAP_FLAG
, PRIORITY
, CTP_FLAG
, ROUTING_SEQUENCE_ID
, NEW_ROUTING_REVISION
, ROUTING_COMMENT
, ECO_FOR_PRODUCTION -- Added by MK on 10/06/00
, CHANGE_ID --Added on 12/12/02
, ALTERNATE_BOM_DESIGNATOR -- Added by Maloy so that ALTERNATE_BOM_DESIGNATOR Get saved and 2871651 works fine
--11.5.10 Changes
,TRANSFER_OR_COPY
,TRANSFER_OR_COPY_ITEM
,TRANSFER_OR_COPY_BILL
,TRANSFER_OR_COPY_ROUTING
,COPY_TO_ITEM
,COPY_TO_ITEM_DESC
,STATUS_CODE
--end of 11.5.10 changes
,parent_revised_item_seq_id
,selection_option
,selection_date
,selection_unit_number
,new_item_revision_id
,current_item_revision_id
,current_lifecycle_state_id
,new_lifecycle_state_id
,enable_item_in_local_org
,create_bom_in_local_org )
VALUES
(
p_revised_item_rec.eco_name
, p_rev_item_unexp_rec.organization_id
, p_rev_item_unexp_rec.revised_item_id
, SYSDATE
, l_User_Id
, SYSDATE
, l_User_Id
, l_Login_Id
, DECODE(p_rev_item_unexp_rec.implementation_date,
FND_API.G_MISS_DATE,
to_date(NULL),
p_rev_item_unexp_rec.implementation_date
)
, DECODE(p_rev_item_unexp_rec.cancellation_date,
FND_API.G_MISS_DATE,
to_date(NULL),
p_rev_item_unexp_rec.cancellation_date
)
, p_revised_item_rec.cancel_comments
, p_revised_item_rec.disposition_type
, p_revised_item_rec.new_revised_item_revision
, p_revised_item_rec.earliest_effective_date
, p_revised_item_rec.attribute_category
, p_revised_item_rec.attribute2
, p_revised_item_rec.attribute3
, p_revised_item_rec.attribute4
, p_revised_item_rec.attribute5
, p_revised_item_rec.attribute7
, p_revised_item_rec.attribute8
, p_revised_item_rec.attribute9
, p_revised_item_rec.attribute11
, p_revised_item_rec.attribute12
, p_revised_item_rec.attribute13
, p_revised_item_rec.attribute14
, p_revised_item_rec.attribute15
, p_revised_item_rec.status_type
, p_revised_item_rec.start_effective_date
, DECODE(p_rev_item_unexp_rec.bill_sequence_id, FND_API.G_MISS_NUM,
NULL, p_rev_item_unexp_rec.bill_sequence_id)
, p_revised_item_rec.mrp_active
, NULL /* Request ID */
, l_prog_id
, SYSDATE
, p_revised_item_rec.update_wip
, p_rev_item_unexp_rec.use_up
, DECODE(p_rev_item_unexp_rec.use_up_item_id, FND_API.G_MISS_NUM,
NULL, p_rev_item_unexp_rec.use_up_item_id)
, p_rev_item_unexp_rec.revised_item_sequence_id
, p_revised_item_rec.use_up_plan_name
, p_revised_item_rec.change_description
, trunc(p_rev_item_unexp_rec.auto_implement_date)
, p_revised_item_rec.from_end_item_unit_number
, p_revised_item_rec.attribute1
, p_revised_item_rec.attribute6
, p_revised_item_rec.attribute10
, p_revised_item_rec.original_system_reference
/* Added by MK on 08/26/2000 ECO for Routing */
, p_rev_item_unexp_rec.from_wip_entity_id
, p_rev_item_unexp_rec.to_wip_entity_id
, p_revised_item_rec.from_cumulative_quantity
, p_revised_item_rec.lot_number
, p_rev_item_unexp_rec.cfm_routing_flag
, p_revised_item_rec.completion_subinventory
, p_rev_item_unexp_rec.completion_locator_id
-- , p_rev_item_unexp_rec.mixed_model_map_flag
, p_revised_item_rec.priority
, p_revised_item_rec.ctp_flag
, DECODE(p_rev_item_unexp_rec.routing_sequence_id, FND_API.G_MISS_NUM,
NULL, p_rev_item_unexp_rec.routing_sequence_id )
, p_revised_item_rec.new_routing_revision
, p_revised_item_rec.routing_comment
, p_revised_item_rec.eco_for_production
, p_rev_item_unexp_rec.change_id
, p_revised_item_rec.alternate_bom_code -- Added by Maloy so that ALTERNATE_BOM_DESIGNATOR Get saved and 2871651 works fine
--Start of 11.5.10 changes
, p_revised_item_rec.Transfer_Or_Copy
, p_revised_item_rec.Transfer_OR_Copy_Item
, p_revised_item_rec.Transfer_OR_Copy_Bill
, p_revised_item_rec.Transfer_OR_Copy_Routing
, p_revised_item_rec.Copy_To_Item
, p_revised_item_rec.Copy_To_Item_Desc
, nvl(p_rev_item_unexp_rec.status_code,p_revised_item_rec.status_type)
, p_rev_item_unexp_rec.parent_revised_item_seq_id
, p_revised_item_rec.selection_option
, p_revised_item_rec.selection_date
, p_revised_item_rec.selection_unit_number
, p_rev_item_unexp_rec.new_item_revision_id
, l_current_item_revision_id
-- , p_rev_item_unexp_rec.current_item_revision_id
, nvl(p_rev_item_unexp_rec.current_lifecycle_state_id ,l_current_lifecycle_phase_id)
, p_rev_item_unexp_rec.new_lifecycle_state_id
, p_revised_item_rec.enable_item_in_local_org
, p_revised_item_rec.create_bom_in_local_org --End of 11.5.10 changes
);
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Inserting item revisions . . . '); END IF;
ENG_REVISED_ITEMS_PKG.Insert_Item_Revisions
( x_inventory_item_id =>
p_rev_item_unexp_rec.revised_item_id
, x_organization_id =>
p_rev_item_unexp_rec.organization_id
, x_revision =>
p_revised_item_rec.new_revised_item_revision
, x_userid =>
l_User_Id
, x_change_notice =>
p_revised_item_rec.eco_name
, x_scheduled_date =>
p_revised_item_rec.start_effective_date
, x_revised_item_sequence_id =>
p_rev_item_unexp_rec.revised_item_sequence_id
/* Item revision description support Bug: 1667419*/
, x_revision_description =>
p_revised_item_rec.new_revised_item_rev_desc
, p_new_revision_label =>
p_revised_item_rec.new_revision_label
, p_new_revision_reason_code =>
p_rev_item_unexp_rec.new_revision_reason_code
, p_from_revision_id =>
p_rev_item_unexp_rec.from_item_revision_id
);
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Inserting routing revisions . . .'); END IF;
Insert_Routing_Revisions
( p_inventory_item_id => p_rev_item_unexp_rec.revised_item_id
, p_organization_id => p_rev_item_unexp_rec.organization_id
, p_revision => p_revised_item_rec.new_routing_revision
, p_user_id => l_user_id
, p_login_id => l_login_id
, p_change_notice => p_revised_item_rec.eco_name
, p_effectivity_date => p_revised_item_rec.start_effective_date
, p_revised_item_sequence_id => p_rev_item_unexp_rec.revised_item_sequence_id
);
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Inserting cur_sch_dates . . .'); END IF;
/*Insert_Current_Scheduled_Dates
( x_change_notice =>
p_revised_item_rec.eco_name
, x_organization_id =>
p_rev_item_unexp_rec.organization_id
, x_revised_item_id =>
p_rev_item_unexp_rec.revised_item_id
, x_scheduled_date =>
p_revised_item_rec.start_effective_date
, x_revised_item_sequence_id =>
p_rev_item_unexp_rec.revised_item_sequence_id
, x_requestor_id =>
p_rev_item_unexp_rec.requestor_id
, x_userid =>
l_User_Id
, x_original_system_reference =>
p_revised_item_rec.original_system_reference);*/
UPDATE ENG_REVISED_ITEMS
SET bill_sequence_id = p_rev_item_unexp_rec.bill_sequence_id
, last_update_date = SYSDATE -- Last Update Date
, last_updated_by = l_user_id -- Last Updated By
, last_update_login = l_login_id -- Last Update Login
WHERE revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id ;
, p_last_update_date => SYSDATE
, p_last_updated_by => l_user_id
, p_creation_date => SYSDATE
, p_created_by => l_user_id
, p_login_id => l_login_id
, p_revised_item_sequence_id => p_rev_item_unexp_rec.revised_item_sequence_id
, p_original_system_reference => p_revised_item_rec.original_system_reference
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => x_return_status
) ;
Update_New_Rev_Lifecycle(
p_revised_item_seq_id => p_rev_item_unexp_rec.revised_item_sequence_id
, p_revised_item_id => p_rev_item_unexp_rec.revised_item_id
, p_org_id => p_rev_item_unexp_rec.organization_id
,p_lifecycle_name => p_revised_item_rec.new_lifecycle_phase_name
,p_new_item_revision => p_revised_item_rec.New_Revised_Item_Revision
, p_change_notice => p_revised_item_rec.eco_name
, x_Return_Status => x_return_status);
l_err_text := G_PKG_NAME || ' : (Inserting Record - Revised Item) '
|| substrb(SQLERRM,1,200);
END Insert_Row;
* Procedure : Delete_Row
* Parameters IN : Revised item Key
* Parameters OUT: Mesg Token Table
* Return Status
* Purpose : Procedure will perfrom the deletion of revised item and all
* other entities depending on that revised item.
*****************************************************************************/
PROCEDURE Delete_Row
( p_revised_item_sequence_id IN NUMBER
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_organization_id NUMBER := NULL;
SELECT change_notice, organization_id, revised_item_id,
revised_item_sequence_id, bill_sequence_id
, routing_sequence_id -- Added by MK
INTO l_change_notice, l_organization_id, l_revised_item_id,
l_revised_item_sequence_id, l_bill_sequence_id
, l_routing_sequence_id -- Added by MK
FROM eng_revised_items
WHERE revised_item_sequence_id = p_revised_item_sequence_id;
DELETE FROM ENG_REVISED_ITEMS
WHERE REVISED_ITEM_SEQUENCE_ID = p_revised_item_sequence_id;
ENG_REVISED_ITEMS_PKG.Delete_Details
( x_organization_id => l_organization_id
, x_revised_item_id => l_revised_item_id
, x_revised_item_sequence_id => p_revised_item_sequence_id
, x_bill_sequence_id => l_bill_sequence_id
, x_change_notice => l_change_notice);
Delete_Routing_Details
( p_organization_id => l_organization_id
, p_revised_item_id => l_revised_item_id
, p_revised_item_sequence_id => p_revised_item_sequence_id
, p_routing_sequence_id => l_routing_sequence_id
, p_change_notice => l_change_notice) ;
END Delete_Row;
SELECT
CHANGE_NOTICE
, ORGANIZATION_ID
, REVISED_ITEM_ID
, IMPLEMENTATION_DATE
, CANCELLATION_DATE
, CANCEL_COMMENTS
, DISPOSITION_TYPE
, NEW_ITEM_REVISION
, EARLY_SCHEDULE_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, STATUS_TYPE
, SCHEDULED_DATE
, BILL_SEQUENCE_ID
, MRP_ACTIVE
, UPDATE_WIP
, USE_UP
, USE_UP_ITEM_ID
, REVISED_ITEM_SEQUENCE_ID
, USE_UP_PLAN_NAME
, DESCRIPTIVE_TEXT
, AUTO_IMPLEMENT_DATE
, ATTRIBUTE1
, ATTRIBUTE6
, ATTRIBUTE10
-- Added by MK on 09/01/2000 ECO for ROUTINGS
, FROM_WIP_ENTITY_ID
, TO_WIP_ENTITY_ID
, FROM_CUM_QTY
, LOT_NUMBER
, CFM_ROUTING_FLAG
, COMPLETION_SUBINVENTORY
, COMPLETION_LOCATOR_ID
-- , MIXED_MODEL_MAP_FLAG
, PRIORITY
, CTP_FLAG
, ROUTING_SEQUENCE_ID
, NEW_ROUTING_REVISION
, ROUTING_COMMENT -- End of ECO for Routing
, ECO_FOR_PRODUCTION -- Added by MK 10/06/00
, CHANGE_ID
, STATUS_CODE -- Added for bug 3618676
INTO
l_revised_item_rec.eco_name
, l_rev_item_unexp_rec.organization_id
, l_rev_item_unexp_rec.revised_item_id
, l_rev_item_unexp_rec.implementation_date
, l_rev_item_unexp_rec.cancellation_date
, l_revised_item_rec.cancel_comments
, l_revised_item_rec.disposition_type
, l_revised_item_rec.new_revised_item_revision
, l_revised_item_rec.earliest_effective_date
, l_revised_item_rec.attribute_category
, l_revised_item_rec.attribute2
, l_revised_item_rec.attribute3
, l_revised_item_rec.attribute4
, l_revised_item_rec.attribute5
, l_revised_item_rec.attribute7
, l_revised_item_rec.attribute8
, l_revised_item_rec.attribute9
, l_revised_item_rec.attribute11
, l_revised_item_rec.attribute12
, l_revised_item_rec.attribute13
, l_revised_item_rec.attribute14
, l_revised_item_rec.attribute15
, l_revised_item_rec.status_type
, l_revised_item_rec.start_effective_date
, l_rev_item_unexp_rec.bill_sequence_id
, l_revised_item_rec.mrp_active
, l_revised_item_rec.update_wip
, l_rev_item_unexp_rec.use_up
, l_rev_item_unexp_rec.use_up_item_id
, l_rev_item_unexp_rec.revised_item_sequence_id
, l_revised_item_rec.use_up_plan_name
, l_revised_item_rec.change_description
, l_rev_item_unexp_rec.auto_implement_date
, l_revised_item_rec.attribute1
, l_revised_item_rec.attribute6
, l_revised_item_rec.attribute10
/* Added by MK on 09/01/2000 ECO for Routing */
, l_rev_item_unexp_rec.from_wip_entity_id
, l_rev_item_unexp_rec.to_wip_entity_id
, l_revised_item_rec.from_cumulative_quantity
, l_revised_item_rec.lot_number
, l_rev_item_unexp_rec.cfm_routing_flag
, l_revised_item_rec.completion_subinventory
, l_rev_item_unexp_rec.completion_locator_id
-- , l_rev_item_unexp_rec.mixed_model_map_flag
, l_revised_item_rec.priority
, l_revised_item_rec.ctp_flag
, l_rev_item_unexp_rec.routing_sequence_id
, l_revised_item_rec.new_routing_revision
, l_revised_item_rec.routing_comment
, l_revised_item_rec.eco_for_production -- Added by MK on 10/06/00
, l_rev_item_unexp_rec.CHANGE_ID --Added ON 12/12/02
, l_rev_item_unexp_rec.status_code -- Added for bug 3618676
FROM ENG_REVISED_ITEMS
WHERE revised_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND change_notice = p_change_notice
AND NVL(new_item_revision, 'NONE') = NVL(p_new_item_revision, 'NONE')
AND NVL(new_routing_revision, 'NONE') = NVL(p_new_routing_revision, 'NONE') -- Added by MK
-- AND TRUNC(scheduled_date) = TRUNC(p_start_eff_date)
AND scheduled_date = p_start_eff_date -- Bug 3593861: Scheduled date not truncated when querying for existing records.
AND NVL(from_end_item_unit_number, 'NONE')
= NVL(p_from_end_item_number, 'NONE')
AND NVL(alternate_bom_designator,'-9999999999') = NVL(p_alternate_designator,'-9999999999');
* insert update and delete operations on a revised item.
* So based on the transaction type this procedure will call
* the internal insert, update and delete procedures.
******************************************************************************/
PROCEDURE Perform_Writes( p_revised_item_rec IN
Eng_Eco_Pub.Revised_Item_Rec_Type
, p_rev_item_unexp_rec IN
Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
, p_control_rec IN BOM_BO_Pub.Control_Rec_Type
:= BOM_BO_PUB.G_DEFAULT_CONTROL_REC
, x_Mesg_Token_Tbl OUT NOCOPY
Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status OUT NOCOPY VARCHAR2
)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
Insert_Row( p_revised_item_rec => p_revised_item_rec
, p_rev_item_unexp_rec => p_rev_item_unexp_rec
, p_control_rec => p_control_rec
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_return_status
);
ELSIF p_revised_item_rec.transaction_type = Eng_Globals.G_OPR_UPDATE
THEN
Update_Row( p_revised_item_rec => p_revised_item_rec
, p_rev_item_unexp_rec => p_rev_item_unexp_rec
, p_control_rec => p_control_rec
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_return_status
);
ELSIF p_revised_item_rec.transaction_type = Eng_Globals.G_OPR_DELETE
THEN
l_return_status := FND_API.G_RET_STS_SUCCESS;
ENG_Validate.Check_Entity_Delete
( x_return_status => l_return_status
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_revised_item_rec => p_revised_item_rec
, p_rev_item_unexp_rec => p_rev_item_unexp_rec
);
Delete_Row
( p_revised_item_sequence_id =>
p_rev_item_unexp_rec.revised_item_sequence_id
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => l_Return_Status
);
* 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 Reschedule_Revised_Item
( p_api_version IN NUMBER := 1.0 --
, 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 --
, p_debug IN VARCHAR2 := 'N' --
, p_output_dir IN VARCHAR2 := NULL --
, p_debug_filename IN VARCHAR2 := 'Resch_RevItem.log' --
, x_return_status OUT NOCOPY VARCHAR2 --
, x_msg_count OUT NOCOPY NUMBER --
, x_msg_data OUT NOCOPY VARCHAR2 --
, p_revised_item_sequence_id IN NUMBER
, p_effectivity_date IN DATE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Change_Effectivity_Date';
SELECT revised_item_id, bill_sequence_id, routing_sequence_id,
change_notice, organization_id
FROM eng_revised_items
WHERE revised_item_sequence_id = cp_revised_item_sequence_id;
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 MTL_ITEM_REVISIONS_B
SET effectivity_date = p_effectivity_date,
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_login_id
WHERE change_notice = l_rev_item.change_notice
AND organization_id = l_rev_item.organization_id
AND implementation_date is NULL
AND inventory_item_id = l_rev_item.revised_item_id
AND revised_item_sequence_id = p_revised_item_sequence_id;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updated '|| SQL%ROWCOUNT ||'rows for item revision effectivity'); END IF;
UPDATE BOM_INVENTORY_COMPONENTS bic
SET bic.EFFECTIVITY_DATE = p_effectivity_date
WHERE bic.CHANGE_NOTICE = l_rev_item.change_notice
AND bic.revised_item_sequence_id = p_revised_item_sequence_id
AND bic.bill_sequence_id = l_rev_item.bill_sequence_id
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 bic.IMPLEMENTATION_DATE IS NULL;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updated '|| SQL%ROWCOUNT ||'rows for component effectivity' ); END IF;
UPDATE BOM_INVENTORY_COMPONENTS bic1
SET bic1.DISABLE_DATE = p_effectivity_date
WHERE bic1.CHANGE_NOTICE = l_rev_item.change_notice
AND bic1.ACD_TYPE = 3 -- ACD Type: Disable
AND revised_item_sequence_id = p_revised_item_sequence_id
AND bill_sequence_id = l_rev_item.bill_sequence_id
AND bic1.IMPLEMENTATION_DATE IS NULL;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updated '|| SQL%ROWCOUNT ||'rows for component disable date' ); END IF;
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);
Update_Rev_Operations
( x_change_notice => l_rev_item.change_notice
, x_routing_sequence_id => l_rev_item.routing_sequence_id
, x_revised_item_sequence_id => p_revised_item_sequence_id
, x_scheduled_date => p_effectivity_date);
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updated operation sequences effectivity'); END IF;
UPDATE MTL_RTG_ITEM_REVISIONS
SET effectivity_date = p_effectivity_date,
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_login_id
WHERE change_notice = l_rev_item.change_notice
AND organization_id = l_rev_item.organization_id
AND implementation_date is NULL
AND inventory_item_id = l_rev_item.revised_item_id
AND revised_item_sequence_id = p_revised_item_sequence_id;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updated '|| SQL%ROWCOUNT ||'rows for routing revision effectivity'); END IF;
SELECT change_notice, organization_id, revised_item_id, disposition_type
, early_schedule_date, status_type, bill_sequence_id, mrp_active
, DESCRIPTIVE_TEXT, change_id, ALTERNATE_BOM_DESIGNATOR, status_code
FROM eng_revised_items
WHERE revised_item_sequence_id = cp_revised_item_sequence_id;
SELECT eng_revised_items_s.NEXTVAL INTO x_new_revised_item_seq_id FROM dual;