[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* user has to perform any insert/update/deletes to the
* Inventory Components table.
*********************************************************************/
PROCEDURE Perform_Writes
( p_bom_component_rec IN Bom_Bo_Pub.Bom_Comps_Rec_Type
, p_bom_comp_unexp_rec IN Bom_Bo_Pub.Bom_Comps_Unexposed_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2
)
IS
l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type;
SELECT ROWID
, SUPPLY_SUBINVENTORY
, REVISED_ITEM_SEQUENCE_ID
, REQUIRED_FOR_REVENUE
, HIGH_QUANTITY
, COMPONENT_SEQUENCE_ID
, WIP_SUPPLY_TYPE
, SUPPLY_LOCATOR_ID
, BOM_ITEM_TYPE
, OPERATION_SEQ_NUM
, COMPONENT_ITEM_ID
, ITEM_NUM
, BASIS_TYPE
, COMPONENT_QUANTITY
, COMPONENT_YIELD_FACTOR
, COMPONENT_REMARKS
, EFFECTIVITY_DATE
, CHANGE_NOTICE
, DISABLE_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, PLANNING_FACTOR
, QUANTITY_RELATED
, SO_BASIS
, OPTIONAL
, MUTUALLY_EXCLUSIVE_OPTIONS
, INCLUDE_IN_COST_ROLLUP
, CHECK_ATP
, SHIPPING_ALLOWED
, REQUIRED_TO_SHIP
, INCLUDE_ON_SHIP_DOCS
, LOW_QUANTITY
, ACD_TYPE
, OLD_COMPONENT_SEQUENCE_ID
, BILL_SEQUENCE_ID
, PICK_COMPONENTS
, FROM_END_ITEM_UNIT_NUMBER
, TO_END_ITEM_UNIT_NUMBER
, ENFORCE_INT_REQUIREMENTS
, AUTO_REQUEST_MATERIAL -- Added in 11.5.9 by ADEY
, SUGGESTED_VENDOR_NAME --- Deepu
, VENDOR_ID --- Deepu
-- , PURCHASING_CATEGORY_ID --- Deepu
, UNIT_PRICE --- Deepu
INTO l_rev_comp_Unexp_rec.rowid
, l_rev_component_rec.supply_subinventory
, l_rev_comp_Unexp_rec.revised_item_sequence_id
, l_rev_component_rec.required_for_revenue
, l_rev_component_rec.maximum_allowed_quantity
, l_rev_comp_Unexp_rec.component_sequence_id
, l_rev_component_rec.wip_supply_type
, l_rev_comp_Unexp_rec.supply_locator_id
, l_rev_comp_Unexp_rec.bom_item_type
, l_rev_component_rec.operation_sequence_number
, l_rev_comp_Unexp_rec.component_item_id
, l_rev_component_rec.item_sequence_number
, l_rev_component_rec.basis_type
, l_rev_component_rec.quantity_per_assembly
, l_rev_component_rec.projected_yield
, l_rev_component_rec.comments
, l_rev_component_rec.start_effective_date
, l_rev_component_rec.Eco_Name
, l_rev_component_rec.disable_date
, l_rev_component_rec.attribute_category
, l_rev_component_rec.attribute1
, l_rev_component_rec.attribute2
, l_rev_component_rec.attribute3
, l_rev_component_rec.attribute4
, l_rev_component_rec.attribute5
, l_rev_component_rec.attribute6
, l_rev_component_rec.attribute7
, l_rev_component_rec.attribute8
, l_rev_component_rec.attribute9
, l_rev_component_rec.attribute10
, l_rev_component_rec.attribute11
, l_rev_component_rec.attribute12
, l_rev_component_rec.attribute13
, l_rev_component_rec.attribute14
, l_rev_component_rec.attribute15
, l_rev_component_rec.planning_percent
, l_rev_component_rec.quantity_related
, l_rev_component_rec.so_basis
, l_rev_component_rec.optional
, l_rev_component_rec.mutually_exclusive
, l_rev_component_rec.include_in_cost_rollup
, l_rev_component_rec.check_atp
, l_rev_component_rec.shipping_allowed
, l_rev_component_rec.required_to_ship
, l_rev_component_rec.include_on_ship_docs
, l_rev_component_rec.minimum_allowed_quantity
, l_rev_component_rec.acd_type
, l_rev_comp_unexp_rec.old_component_sequence_id
, l_rev_comp_unexp_rec.bill_sequence_id
, l_rev_comp_unexp_rec.pick_components
, l_rev_component_rec.from_end_item_unit_number
, l_rev_component_rec.to_end_item_unit_number
, l_rev_comp_unexp_rec.enforce_int_requirements_code
, l_rev_component_rec.auto_request_material -- Added in 11.5.9 by ADEY
, l_rev_component_rec.Suggested_Vendor_Name --- Deepu
-- , l_rev_component_rec.purchasing_category_id --- Deepu
, l_rev_comp_unexp_rec.Vendor_Id --- Deepu
, l_rev_component_rec.Unit_Price --- Deepu
FROM BOM_INVENTORY_COMPONENTS
WHERE component_item_id = p_component_item_id
AND effectivity_date = p_effectivity_date
AND operation_seq_num = nvl(p_operation_sequence_number,1) --Bug 5856042
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_number, FND_API.G_MISS_CHAR);
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Selecting the lookup meaning for enforce int requirements code . . .'); END IF;
SELECT meaning INTO l_rev_component_rec.enforce_int_requirements FROM mfg_lookups
WHERE lookup_type = 'BOM_ENFORCE_INT_REQUIREMENTS' AND
lookup_code = l_rev_comp_unexp_rec.enforce_int_requirements_code;
* Procedure : Update_Row
* Parameters IN : Revised Component exposed column record
* Revised Component unexposed column record
* Parameters OUT: Mesg_Token_Tbl
* Return_Status
* Purpose : Update_Row procedure will update the production record with
* the user given values. Any errors will be returned by filling
* the Mesg_Token_Tbl and setting the return_status.
****************************************************************************/
PROCEDURE Update_Row
( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
, p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2
)
IS
l_return_status varchar2(80);
UPDATE BOM_INVENTORY_COMPONENTS
SET SUPPLY_SUBINVENTORY = p_rev_component_rec.supply_subinventory
, REQUIRED_FOR_REVENUE = p_rev_component_rec.required_for_revenue
, HIGH_QUANTITY = p_rev_component_rec.maximum_allowed_quantity
, WIP_SUPPLY_TYPE = p_rev_component_rec.wip_supply_type
, SUPPLY_LOCATOR_ID =
DECODE(p_rev_comp_Unexp_rec.supply_locator_id, FND_API.G_MISS_NUM,
NULL, p_rev_comp_Unexp_rec.supply_locator_id)
, OPERATION_SEQ_NUM = l_operation_seq_num
, EFFECTIVITY_DATE =
DECODE( p_rev_component_rec.new_effectivity_date
, FND_API.G_MISS_DATE
, p_rev_component_rec.start_effective_date
, NULL
, p_rev_component_rec.start_effective_date
, p_rev_component_rec.new_effectivity_date
)
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATED_BY = BOM_Globals.Get_User_Id
, LAST_UPDATE_LOGIN = BOM_Globals.Get_User_Id
, ITEM_NUM = p_rev_component_rec.item_sequence_number
, BASIS_TYPE = decode(p_rev_component_rec.basis_type,
FND_API.G_MISS_NUM, null,p_rev_component_rec.basis_type)
, COMPONENT_QUANTITY = p_rev_component_rec.quantity_per_assembly
, COMPONENT_YIELD_FACTOR = p_rev_component_rec.projected_yield
, COMPONENT_REMARKS =
DECODE( p_rev_component_rec.comments, --bug:4178604 Replace FND_API.G_MISS_CHAR by NULL
FND_API.G_MISS_CHAR,NULL,
p_rev_component_rec.comments)
, DISABLE_DATE = p_rev_component_rec.disable_date
, ATTRIBUTE_CATEGORY = p_rev_component_rec.attribute_category
, ATTRIBUTE1 = p_rev_component_rec.attribute1
, ATTRIBUTE2 = p_rev_component_rec.attribute2
, ATTRIBUTE3 = p_rev_component_rec.attribute3
, ATTRIBUTE4 = p_rev_component_rec.attribute4
, ATTRIBUTE5 = p_rev_component_rec.attribute5
, ATTRIBUTE6 = p_rev_component_rec.attribute6
, ATTRIBUTE7 = p_rev_component_rec.attribute7
, ATTRIBUTE8 = p_rev_component_rec.attribute8
, ATTRIBUTE9 = p_rev_component_rec.attribute9
, ATTRIBUTE10 = p_rev_component_rec.attribute10
, ATTRIBUTE11 = p_rev_component_rec.attribute11
, ATTRIBUTE12 = p_rev_component_rec.attribute12
, ATTRIBUTE13 = p_rev_component_rec.attribute13
, ATTRIBUTE14 = p_rev_component_rec.attribute14
, ATTRIBUTE15 = p_rev_component_rec.attribute15
, PLANNING_FACTOR = p_rev_component_rec.planning_percent
, QUANTITY_RELATED = p_rev_component_rec.quantity_related
, SO_BASIS = p_rev_component_rec.so_basis
, OPTIONAL = p_rev_component_rec.optional
, MUTUALLY_EXCLUSIVE_OPTIONS = p_rev_component_rec.mutually_exclusive
, INCLUDE_IN_COST_ROLLUP = p_rev_component_rec.include_in_cost_rollup
, CHECK_ATP = p_rev_component_rec.check_atp
, SHIPPING_ALLOWED = p_rev_component_rec.shipping_allowed
, REQUIRED_TO_SHIP = p_rev_component_rec.required_to_ship
, INCLUDE_ON_SHIP_DOCS = p_rev_component_rec.include_on_ship_docs
, LOW_QUANTITY = p_rev_component_rec.minimum_allowed_quantity
, ACD_TYPE = p_rev_component_rec.acd_type
, PROGRAM_UPDATE_DATE = SYSDATE
, PROGRAM_ID = BOM_Globals.Get_Prog_Id
, OPERATION_LEAD_TIME_PERCENT = l_operation_leadtime
, Original_System_Reference =
p_rev_component_rec.original_system_reference
, From_End_Item_Unit_Number =
DECODE(p_rev_component_rec.new_from_end_item_unit_number
,FND_API.G_MISS_CHAR
,p_rev_component_rec.from_end_item_unit_number
,NULL
,p_rev_component_rec.from_end_item_unit_number
,p_rev_component_rec.new_from_end_item_unit_number
)
, To_End_Item_Unit_Number =
DECODE( p_rev_component_rec.to_end_item_unit_number
, FND_API.G_MISS_CHAR
, NULL
, p_rev_component_rec.to_end_item_unit_number
)
, Enforce_Int_Requirements = p_rev_comp_Unexp_rec.Enforce_Int_Requirements_code
, Auto_Request_Material = p_rev_component_rec.auto_request_material -- Added in 11.5.9 by ADEY
, Suggested_Vendor_Name = p_rev_component_rec.Suggested_Vendor_Name --- Deepu
, Vendor_Id = p_rev_comp_Unexp_rec.Vendor_Id --- Deepu
-- , Purchasing_Category_id = p_rev_component_rec.purchasing_category_id --- Deepu
, Unit_Price = p_rev_component_rec.Unit_Price --- Deepu
, REQUEST_ID = Fnd_Global.Conc_Request_Id
, PROGRAM_APPLICATION_ID = Fnd_Global.Prog_Appl_Id
WHERE COMPONENT_SEQUENCE_ID = p_Rev_Comp_Unexp_Rec.component_sequence_id
;
BOMPCMBM.Update_Related_Components( p_src_comp_seq_id => p_Rev_Comp_Unexp_Rec.component_sequence_id
, x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
, x_Return_Status => x_Return_Status
);
' : Utility (Component Update) ' ||
SUBSTR(SQLERRM, 1, 200);
END Update_Row;
* Procedure : Insert_Row
* Parameters IN : Revised Component exposed column record
* Revised Component unexposed column record
* Parameters OUT: Mesg_Token_Tbl
* Return_Status
* Purpose : This procedure will insert a record in the bom_inventory-
* component table. Any errors will be filled in the Mesg_Token
* Tbl and returned with a return_status of U
*****************************************************************************/
PROCEDURE Insert_Row
( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
, p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2
)
IS
l_err_text VARCHAR2(2000);
INSERT INTO BOM_INVENTORY_COMPONENTS
( SUPPLY_SUBINVENTORY
, OPERATION_LEAD_TIME_PERCENT
, REVISED_ITEM_SEQUENCE_ID
, COST_FACTOR
, REQUIRED_FOR_REVENUE
, HIGH_QUANTITY
, COMPONENT_SEQUENCE_ID
, PROGRAM_APPLICATION_ID
, WIP_SUPPLY_TYPE
, SUPPLY_LOCATOR_ID
, BOM_ITEM_TYPE
, OPERATION_SEQ_NUM
, COMPONENT_ITEM_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, ITEM_NUM
, BASIS_TYPE
, COMPONENT_QUANTITY
, COMPONENT_YIELD_FACTOR
, COMPONENT_REMARKS
, EFFECTIVITY_DATE
, CHANGE_NOTICE
, IMPLEMENTATION_DATE
, DISABLE_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, PLANNING_FACTOR
, QUANTITY_RELATED
, SO_BASIS
, OPTIONAL
, MUTUALLY_EXCLUSIVE_OPTIONS
, INCLUDE_IN_COST_ROLLUP
, CHECK_ATP
, SHIPPING_ALLOWED
, REQUIRED_TO_SHIP
, INCLUDE_ON_SHIP_DOCS
, INCLUDE_ON_BILL_DOCS
, LOW_QUANTITY
, ACD_TYPE
, OLD_COMPONENT_SEQUENCE_ID
, BILL_SEQUENCE_ID
, REQUEST_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, PICK_COMPONENTS
, Original_System_Reference
, From_End_Item_Unit_Number
, To_End_Item_Unit_Number
, Eco_For_Production -- Added by MK
, Enforce_Int_Requirements
, Auto_Request_Material -- Added in 11.5.9 by ADEY
, Obj_Name -- Added by hgelli.
, pk1_value
, pk2_value
, Suggested_Vendor_Name --- Deepu
, Vendor_Id --- Deepu
-- , Purchasing_Category_id --- Deepu
, Unit_Price --- Deepu
,from_object_revision_id
, from_minor_revision_id
--,component_item_revision_id
--,component_minor_revision_id
, common_component_sequence_id
)
VALUES
( p_rev_component_rec.supply_subinventory
, l_operation_leadtime
, p_rev_comp_unexp_rec.revised_item_sequence_id
, NULL /* Cost Factor */
, p_rev_component_rec.required_for_revenue
, p_rev_component_rec.maximum_allowed_quantity
, p_rev_comp_Unexp_rec.component_sequence_id
, BOM_Globals.Get_Prog_AppId
, p_rev_component_rec.wip_supply_type
, DECODE(p_rev_comp_Unexp_rec.supply_locator_id, FND_API.G_MISS_NUM,
NULL, p_rev_comp_Unexp_rec.supply_locator_id)
, p_rev_comp_Unexp_rec.bom_item_type
, l_operation_seq_num
, p_rev_comp_Unexp_rec.component_item_id
, SYSDATE /* Last Update Date */
, BOM_Globals.Get_User_Id /* Last Updated By */
, SYSDATE /* Creation Date */
, BOM_Globals.Get_User_Id /* Created By */
, BOM_Globals.Get_User_Id /* Last Update Login */
, DECODE(p_rev_component_rec.item_sequence_number, FND_API.G_MISS_NUM,
1, NULL,1,p_rev_component_rec.item_sequence_number)
, DECODE(p_rev_component_rec.basis_type,FND_API.G_MISS_NUM,
NULL,p_rev_component_rec.basis_type)
, p_rev_component_rec.quantity_per_assembly
, p_rev_component_rec.projected_yield
, p_rev_component_rec.comments
, nvl(p_rev_component_rec.start_effective_date,SYSDATE) --2169237
, p_rev_component_rec.Eco_Name
, DECODE(l_Bo_Id,
Bom_Globals.G_BOM_BO,
Decode( p_rev_comp_Unexp_rec.bom_implementation_date,
null,
null,
SYSDATE),
NULL
) /* Implementation Date */
/*
, DECODE(l_Bo_Id,
Bom_Globals.G_BOM_BO,
SYSDATE,
NULL
) -- Implementation Date
*/
, p_rev_component_rec.disable_date
, p_rev_component_rec.attribute_category
, p_rev_component_rec.attribute1
, p_rev_component_rec.attribute2
, p_rev_component_rec.attribute3
, p_rev_component_rec.attribute4
, p_rev_component_rec.attribute5
, p_rev_component_rec.attribute6
, p_rev_component_rec.attribute7
, p_rev_component_rec.attribute8
, p_rev_component_rec.attribute9
, p_rev_component_rec.attribute10
, p_rev_component_rec.attribute11
, p_rev_component_rec.attribute12
, p_rev_component_rec.attribute13
, p_rev_component_rec.attribute14
, p_rev_component_rec.attribute15
, p_rev_component_rec.planning_percent
, p_rev_component_rec.quantity_related
, p_rev_component_rec.so_basis
, p_rev_component_rec.optional
, p_rev_component_rec.mutually_exclusive
, p_rev_component_rec.include_in_cost_rollup
, p_rev_component_rec.check_atp
, p_rev_component_rec.shipping_allowed
, p_rev_component_rec.required_to_ship
, p_rev_component_rec.include_on_ship_docs
, NULL /* Include On Bill Docs */
, p_rev_component_rec.minimum_allowed_quantity
, p_rev_component_rec.acd_type
-- , DECODE( p_rev_comp_Unexp_rec.old_component_sequence_id
-- , FND_API.G_MISS_NUM
-- , NULL
-- ,p_rev_comp_Unexp_rec.old_component_sequence_id
-- )
, l_old_component_sequence_id
, p_rev_comp_Unexp_rec.bill_sequence_id
, Fnd_Global.Conc_Request_Id /* Request Id */
, BOM_Globals.Get_Prog_Id
, SYSDATE /* program_update_date */
, p_rev_comp_Unexp_rec.pick_components
, p_rev_component_rec.original_system_reference
, DECODE( p_rev_component_rec.from_end_item_unit_number
, FND_API.G_MISS_CHAR
, null
, p_rev_component_rec.from_end_item_unit_number
)
, DECODE( p_rev_component_rec.to_end_item_unit_number
, FND_API.G_MISS_CHAR
, null
, p_rev_component_rec.to_end_item_unit_number
)
, BOM_Globals.Get_Eco_For_Production
-- DECODE( l_Bo_Id, BOM_Globals.G_ECO_BO, l_Eco_For_Production, 2) /* Eco for Production flag */
, p_rev_comp_Unexp_rec.Enforce_Int_Requirements_Code
, p_rev_component_rec.auto_request_material -- Added in 11.5.9 by ADEY
, NULL-- Added by hgelli. Identifies this record as Bom Component.
, p_rev_comp_Unexp_rec.component_item_id
, p_rev_comp_Unexp_rec.organization_id
, p_rev_component_rec.Suggested_Vendor_Name --- Deepu
, p_rev_comp_Unexp_rec.Vendor_Id --- Deepu
-- , p_rev_component_rec.purchasing_category_id --- Deepu
, p_rev_component_rec.Unit_Price --- Deepu
,l_object_revision_id
,l_minor_revision_id
--,l_comp_revision_id
--,l_comp_minor_revision_id
,(Select common_component_sequence_id from bom_inventory_components where
component_sequence_id = l_old_component_sequence_id)
);
BOMPCMBM.Insert_Related_Components( p_src_bill_seq_id => p_rev_comp_Unexp_rec.bill_sequence_id
, p_src_comp_seq_id => p_rev_comp_Unexp_rec.component_sequence_id
, x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
, x_Return_Status => x_Return_Status
);
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Unexpected Error occured in Insert . . .' || SQLERRM); END IF;
' : Utility (Component Insert) ' ||
SUBSTR(SQLERRM, 1, 200);
END Insert_Row;
* Procedure : Delete_Row
* Parameters IN : Revised Component Key
* Parameters OUT: Mesg_Token_Tbl
* Return_Status
* Purpose : Will delete a revised component record for a ECO.
* Delete operation will not delete a record in production which
* is already implemented.
*****************************************************************************/
/* Comment out by MK to support delet
PROCEDURE Delete_Row
( p_component_sequence_id IN NUMBER
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2
)
*/
PROCEDURE Delete_Row
( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
, p_rev_comp_unexp_rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2
)
IS
l_dummy number;
SELECT description,
delete_group_sequence_id,
delete_type
FROM bom_delete_groups
WHERE delete_group_name = p_rev_comp_unexp_rec.delete_group_name
AND organization_id = p_rev_comp_unexp_rec.organization_id;
DELETE FROM BOM_INVENTORY_COMPONENTS
WHERE COMPONENT_SEQUENCE_ID = p_rev_comp_unexp_rec.component_sequence_id;
DELETE from bom_reference_designators
WHERE component_sequence_id = p_rev_comp_unexp_rec.component_sequence_id ;
DELETE from bom_substitute_components
WHERE component_Sequence_id = p_rev_comp_unexp_rec.component_sequence_id ;
IF DG.delete_type <> 4 /* Component */ then
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_DUPLICATE_DELETE_GROUP'
, p_mesg_token_tbl => l_mesg_token_Tbl
, x_mesg_token_tbl => l_mesg_token_tbl
);
DG.delete_group_sequence_id;
( p_message_name => 'NEW_DELETE_GROUP'
, p_mesg_token_tbl => l_mesg_token_Tbl
, x_mesg_token_tbl => l_mesg_token_tbl
, p_message_type => 'W' /* Warning */
);
select assembly_type into l_assembly_type
from bom_structures_b
where bill_sequence_id = l_rev_comp_unexp_rec.bill_sequence_id;
MODAL_DELETE.DELETE_MANAGER
( new_group_seq_id => l_dg_sequence_id,
name => l_rev_comp_unexp_rec.Delete_Group_Name,
group_desc => l_rev_comp_unexp_rec.dg_description,
org_id => l_rev_comp_unexp_rec.organization_id,
bom_or_eng => l_assembly_type, /*dg type must be same as that of bill */
del_type => 4 /* Component */,
ent_bill_seq_id => l_rev_comp_unexp_rec.bill_sequence_id,
ent_rtg_seq_id => NULL,
ent_inv_item_id => l_rev_comp_unexp_rec.revised_item_id,
ent_alt_designator => l_rev_component_rec.alternate_bom_code,
ent_comp_seq_id => l_rev_comp_unexp_rec.component_sequence_id,
ent_op_seq_id => NULL,
user_id => BOM_Globals.Get_User_Id
);
BOMPCMBM.Delete_Related_Pending_Comps(p_src_comp_seq_id => p_rev_comp_unexp_rec.component_sequence_id
, x_Return_Status => x_Return_Status);
, p_Message_Text => 'Error Rev. Comp Delete Row ' ||
SUBSTR(SQLERRM, 1, 100)
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
);
END Delete_Row;
** insert the cancelled rev components into eng_revised_components
*/
stmt_num := 10;
INSERT INTO ENG_REVISED_COMPONENTS (
COMPONENT_SEQUENCE_ID,
COMPONENT_ITEM_ID,
OPERATION_SEQUENCE_NUM,
BILL_SEQUENCE_ID,
CHANGE_NOTICE,
EFFECTIVITY_DATE,
BASIS_TYPE,
COMPONENT_QUANTITY,
COMPONENT_YIELD_FACTOR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
CANCELLATION_DATE,
CANCEL_COMMENTS,
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)
SELECT
IC.COMPONENT_SEQUENCE_ID,
IC.COMPONENT_ITEM_ID,
IC.OPERATION_SEQ_NUM,
IC.BILL_SEQUENCE_ID,
IC.CHANGE_NOTICE,
IC.EFFECTIVITY_DATE,
IC.BASIS_TYPE,
IC.COMPONENT_QUANTITY,
IC. COMPONENT_YIELD_FACTOR,
SYSDATE,
user_id,
SYSDATE,
user_id,
login,
sysdate,
comment,
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
FROM BOM_INVENTORY_COMPONENTS IC
WHERE IC.COMPONENT_SEQUENCE_ID = comp_seq_id;
** delete from bom_inventory_comps
*/
DELETE FROM BOM_INVENTORY_COMPONENTS
WHERE COMPONENT_SEQUENCE_ID = comp_seq_id;
** Delete the Substitute Components and also the Reference Designators
*/
DELETE FROM BOM_SUBSTITUTE_COMPONENTS SC
WHERE SC.COMPONENT_SEQUENCE_ID = comp_seq_id;
** delete reference designators of all pending revised items on ECO
*/
stmt_num := 30;
DELETE FROM BOM_REFERENCE_DESIGNATORS RD
WHERE RD.COMPONENT_SEQUENCE_ID = comp_seq_id;
SELECT 1
FROM sys.dual
WHERE NOT EXISTS
( SELECT bill_sequence_id
FROM bom_bill_of_materials
WHERE assembly_item_id =
l_rev_comp_unexp_rec.revised_item_id
AND organization_id =
l_rev_comp_unexp_rec.organization_id
AND NVL(alternate_bom_designator, 'NONE') =
NVL(l_rev_component_rec.alternate_bom_code,
'NONE')
);
SELECT bom_inventory_components_s.nextval bill_sequence_id
FROM sys.dual;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Test Harness: Executing Insert Row. . . '); END IF;
select assembly_type
INTO l_assembly_type
--from eng_change_order_types
from eng_change_order_types_vl
where change_order_type_id =
(select change_order_type_id
from eng_engineering_changes
where change_notice =
l_rev_component_rec.eco_name
and organization_id =
l_rev_comp_unexp_rec.organization_id);
SELECT decode(eng_item_flag, 'N', 1, 2)
INTO l_assembly_type
FROM mtl_system_items
WHERE inventory_item_id = l_rev_comp_unexp_rec.revised_item_id
AND organization_id = l_rev_comp_unexp_rec.organization_id;
, p_last_update_date => SYSDATE
, p_last_updated_by => BOM_Globals.Get_User_Id
, p_creation_date => SYSDATE
, p_created_by => BOM_Globals.Get_User_Id
, p_revised_item_seq_id =>
l_rev_comp_unexp_rec.revised_item_sequence_id
, p_original_system_reference =>
l_rev_component_rec.original_system_reference);
Insert_Row
( p_Rev_component_rec => l_Rev_Component_Rec
, p_Rev_Comp_Unexp_Rec => l_Rev_Comp_Unexp_Rec
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => l_Return_Status
);
SELECT BOM_ITEM_TYPE
INTO l_bom_item_type
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = l_rev_comp_unexp_rec.revised_item_id
AND ORGANIZATION_ID = l_rev_comp_unexp_rec.organization_id;
Product_Family_PKG.Update_PF_Item_Id
(X_Inventory_Item_Id => l_Rev_Comp_Unexp_Rec.component_item_id,
X_Organization_Id => l_rev_comp_unexp_rec.organization_id,
X_PF_Item_Id => l_rev_comp_unexp_rec.revised_item_id,
X_Trans_Type => NULL,
X_Error_Msg => err_text,
X_Error_Code => err_code);
err_text := 'Update product family Item id error' || SQLERRM;
ELSIF l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Test Harness: Executing Update Row. . . '); END IF;
Update_Row
( p_Rev_component_rec => l_Rev_Component_Rec
, p_Rev_Comp_Unexp_Rec => l_Rev_Comp_Unexp_Rec
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => l_Return_Status
);
ELSIF l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_DELETE
THEN
-- dbms_output.put_line('Test Harness: Executing Delete Row. . . ');
Delete_Row
( p_component_sequence_id =>
l_Rev_Comp_Unexp_Rec.Component_Sequence_Id
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => l_Return_Status
);
Delete_Row
( p_Rev_component_rec => l_Rev_Component_Rec
, p_Rev_Comp_Unexp_Rec => l_Rev_Comp_Unexp_Rec
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => l_Return_Status
);
SELECT component_sequence_id
INTO l_comp_seq_id
FROM bom_inventory_components
WHERE component_item_id =
l_rev_comp_unexp_rec.component_item_id
AND bill_sequence_id = l_rev_comp_unexp_rec.bill_sequence_id
AND operation_seq_num =
l_rev_component_rec.operation_sequence_number
AND effectivity_date =
l_rev_component_rec.start_Effective_date;
OR l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
OR l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_DELETE
THEN
SELECT STRUCTURE_TYPE_NAME,
ASSEMBLY_ITEM_ID,
ORGANIZATION_ID,
ALTERNATE_BOM_DESIGNATOR
INTO
l_Structure_Type_Name,
l_Assembly_Item_Id,
l_Organization_Id,
l_Structure_Name
FROM BOM_STRUCTURE_TYPES_B STRUCT_TYPE,
BOM_STRUCTURES_B BOM_STRUCT
WHERE BOM_STRUCT.STRUCTURE_TYPE_ID = STRUCT_TYPE.STRUCTURE_TYPE_ID
AND BOM_STRUCT.BILL_SEQUENCE_ID = l_Rev_Comp_Unexp_Rec.BILL_SEQUENCE_ID;
BOM_GTIN_RULES.Update_Top_GTIN (
p_organization_id => l_Organization_Id
,p_component_item_id => l_Rev_Comp_Unexp_Rec.component_item_id
,p_parent_item_id => l_Assembly_Item_Id
,p_structure_name => l_Structure_Name
);
* procedure will create a Bill and update the revised item
* information indicating that bill for this revised item now
* exists.
******************************************************************************/
PROCEDURE Create_New_Bill( p_assembly_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_pending_from_ecn IN VARCHAR2
, p_bill_sequence_id IN NUMBER
, p_common_bill_sequence_id IN NUMBER
, p_assembly_type IN NUMBER
, p_last_update_date IN DATE
, p_last_updated_by IN NUMBER
, p_creation_date IN DATE
, p_created_by IN NUMBER
, p_revised_item_seq_id IN NUMBER
, p_original_system_reference IN VARCHAR2
, p_alternate_bom_code IN VARCHAR2 := NULL)
IS
CURSOR c_structure_type( p_alternate_bom_code IN VARCHAR2
, p_organization_id IN NUMBER
)
IS
SELECT structure_type_id
FROM bom_alternate_designators
WHERE nvl(alternate_designator_code,'XXXXXXXXXXX') =
nvl(p_alternate_bom_code, 'XXXXXXXXXXX' )
and organization_id = p_organization_id;
SELECT effectivity_control
FROM mtl_system_items
WHERE inventory_item_id = p_assembly_item_id
AND organization_id = p_organization_id;
INSERT INTO Bom_Bill_Of_Materials
( assembly_item_id
, organization_id
, pending_from_ecn
, bill_sequence_id
, common_bill_sequence_id
, assembly_type
, last_update_date
, last_updated_by
, creation_date
, created_by
, original_system_reference
, structure_type_id
, effectivity_control -- bug 4550996
, implementation_date -- bug 4550996
, alternate_bom_designator
, source_bill_sequence_id --Bug 4550996
, pk1_value --Bug 4550996
, pk2_value --Bug 4550996
)
VALUES ( p_assembly_item_id
, p_organization_id
, p_pending_from_ecn
, p_bill_sequence_id
, p_common_bill_sequence_id
, p_assembly_type
, p_last_update_date
, p_last_updated_by
, p_creation_date
, p_created_by
, p_original_system_reference
, l_structure_type_id
, l_effectivity_control -- bug 4550996
, sysdate -- bug 4550996
, p_alternate_bom_code
, p_bill_sequence_id
, p_assembly_item_id
, p_organization_id
);
UPDATE eng_revised_items
SET bill_sequence_id = p_bill_sequence_id
WHERE revised_item_sequence_id = p_revised_item_seq_id;
SELECT OPERATION_LEAD_TIME_PERCENT
into
l_leadtime_percent
FROM
bom_operation_sequences bos
WHERE
bos.routing_sequence_id =
(
select common_routing_sequence_id
from bom_operational_routings
where assembly_item_id = p_assembly_item_id
and organization_id = p_organization_id
and nvl(alternate_routing_designator,
nvl(p_alternate_bom_code, 'NONE')) =
nvl(p_alternate_bom_code, 'NONE')
and (p_alternate_bom_code is null
or (p_alternate_bom_code is not null
and (alternate_routing_designator =
p_alternate_bom_code
or not exists
(select null
from bom_operational_routings bor2
where bor2.assembly_item_id =
p_assembly_item_id
and bor2.organization_id = p_organization_id
and bor2.alternate_routing_designator = p_alternate_bom_code
)
)
)
)
)
AND bos.operation_type = 1 --bug: 4161149
AND bos.operation_seq_num = p_operation_seq_num
and bos.implementation_date is not null
and bos.EFFECTIVITY_DATE <= sysdate
AND nvl(disable_date, sysdate+1)
> sysdate;