[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT eng_item_flag
FROM mtl_system_items
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id;
SELECT revision
FROM Mtl_Item_Revisions
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id
ORDER BY effectivity_date desc, revision desc;
SELECT change_notice
FROM ENG_REVISED_ITEMS
WHERE cancellation_date IS NULL
AND implementation_date IS NULL -- Added for bug 3598711, Query to fetch un-implemented ECOs only.
AND revised_item_id = p_revised_item_id
AND new_routing_revision = p_new_routing_revision
AND organization_id = p_organization_id ;
SELECT process_revision
FROM MTL_RTG_ITEM_REVISIONS
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id
ORDER BY effectivity_date desc, process_revision desc;
SELECT 'VALID'
FROM mrp_system_items
WHERE inventory_item_id = p_use_up_item_id
AND organization_id = p_organization_id
AND compile_designator = p_use_up_plan_name
AND inventory_use_up_date >= SYSDATE;
SELECT REVISION
FROM MTL_ITEM_REVISIONS
WHERE INVENTORY_ITEM_ID = p_revised_item_id
AND ORGANIZATION_ID = p_organization_id
AND EFFECTIVITY_DATE <= p_revision_date
AND IMPLEMENTATION_DATE IS NOT NULL
ORDER BY EFFECTIVITY_DATE DESC, REVISION DESC;
* Updated Revised item Revision
* Returns : Number - 1 - if the revision is less than the current rev.
* 2 - if the
* 3 - if the
* Purpose : Function will check if the new_revised_item_revision or the
* updated_revised_item_revision is not less than the current
* item revision. If it is then the function will return a
* value of 1. Else it will proceed to check if the revision
* is being created by another ECO and is still un-implemented
* If it finds this, then the function will return a value of 2.
* Else it will check if the revision exists in an implemented
* state. If it does exist then the function will return 3
* indicating that the revision already exists. If none of the
* conditions are true then the function returns a 0.
*
* 11.5.10E
* If from PLM, the validation is done against the 'From Revision'
* instead of the current revision.
******************************************************************************/
FUNCTION Validate_New_Item_Revision
( p_revised_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_from_revision IN VARCHAR2
, p_new_item_revision IN VARCHAR2
, p_revised_item_sequence_id IN NUMBER
, x_change_notice OUT NOCOPY VARCHAR2
) RETURN NUMBER
IS
l_Rev_Compare NUMBER := NULL;
CURSOR c1 IS SELECT change_notice
FROM MTL_ITEM_REVISIONS
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND revision = p_new_item_revision
AND revised_item_sequence_id <>
NVL(p_revised_item_sequence_id,
revised_item_sequence_id+99)
AND implementation_date is null;
CURSOR c2 IS SELECT 1
FROM MTL_ITEM_REVISIONS
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND revision = p_new_item_revision
AND implementation_date is not null;
SELECT 1
FROM mtl_item_revisions r
WHERE r.inventory_item_id = p_revised_item_id
AND r.organization_id = p_organization_id
AND NVL(r.revised_item_sequence_id, -1) <> NVL(p_rev_item_seq_id, -2)
AND ((r.effectivity_date >= p_scheduled_date and r.revision < p_new_item_revision)
OR (r.effectivity_date <= p_scheduled_date and r.revision > p_new_item_revision)
);
SELECT 1
FROM mtl_item_revisions r
WHERE r.inventory_item_id = p_revised_item_id
AND r.organization_id = p_organization_id
AND r.effectivity_date >= p_scheduled_date
AND r.revision = p_from_item_revision;
* Updated Revised item Revision
* Returns : Number - 1 - if the revision is less than the current rev.
* 2 - if the
* 3 - if the
* Purpose : Function will check if the new_revised_item_revision or the
* updated_revised_item_revision is not less than the current
* item revision. If it is then the function will return a
* value of 1. Else it will proceed to check if the revision
* is being created by another ECO and is still un-implemented
* If it finds this, then the function will return a value of 2.
* Else it will check if the revision exists in an implemented
* state. If it does exist then the function will return 3
* indicating that the revision already exists. If none of the
* conditions are true then the function returns a 0.
******************************************************************************/
FUNCTION Exp_Validate_New_Item_Revision
( p_revised_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_new_item_revision IN VARCHAR2
, p_revised_item_sequence_id IN NUMBER
, x_change_notice OUT NOCOPY VARCHAR2
) RETURN NUMBER
IS
l_Rev_Compare NUMBER := NULL;
CURSOR c1 IS SELECT change_notice
FROM MTL_ITEM_REVISIONS
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND revision = p_new_item_revision
AND implementation_date is null;
CURSOR c2 IS SELECT 1
FROM MTL_ITEM_REVISIONS
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND revision = p_new_item_revision
AND implementation_date is not null;
SELECT process_revision
FROM MTL_RTG_ITEM_REVISIONS
WHERE INVENTORY_ITEM_ID = p_revised_item_id
AND ORGANIZATION_ID = p_organization_id
AND EFFECTIVITY_DATE <= p_revision_date
AND IMPLEMENTATION_DATE IS NOT NULL
ORDER BY EFFECTIVITY_DATE DESC, PROCESS_REVISION DESC;
SELECT mp.starting_revision
INTO l_current_revision
FROM MTL_PARAMETERS mp
WHERE mp.organization_id = p_organization_id
AND NOT EXISTS( SELECT NULL
FROM MTL_RTG_ITEM_REVISIONS
WHERE implementation_date IS NOT NULL
AND organization_id = p_organization_id
AND inventory_item_id = p_revised_item_id
) ;
* Purpose : Function will check if the new_routing_revision or the updated
* _routing_revision is not less than the current routing revision.
* If it is then the function will return a value of 1.
* Else it will proceed to check if the revision is being created
* by another ECO and is still un-implemented.
* If it finds this, then the function will return a value of 2.
* Else it will check if the revision exists in an implemented
* state. If it does exist then the function will return 3
* indicating that the revision already exists. If none of the
* conditions are true then the function returns a 0.
******************************************************************************/
FUNCTION Validate_New_Rtg_Revision
( p_revised_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_new_routing_revision IN VARCHAR2
, p_revised_item_sequence_id IN NUMBER
, x_change_notice OUT NOCOPY VARCHAR2
) RETURN NUMBER
IS
l_Rev_Compare NUMBER := NULL;
CURSOR c1 IS SELECT change_notice
FROM MTL_RTG_ITEM_REVISIONS
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND process_revision = p_new_routing_revision
AND revised_item_sequence_id <>
NVL(p_revised_item_sequence_id,
revised_item_sequence_id+99)
AND implementation_date is null;
CURSOR c2 IS SELECT 1
FROM MTL_RTG_ITEM_REVISIONS
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND process_revision = p_new_routing_revision
AND implementation_date is not null;
IS SELECT 1
FROM ENG_REVISED_ITEMS
WHERE implementation_date IS NULL
AND cancellation_date IS NULL
AND change_notice <> p_change_notice
AND revised_item_id = p_revised_item_id
AND organization_id = p_organization_id;
SELECT bill_sequence_id
FROM Bom_Bill_Of_Materials
WHERE assembly_item_id = p_Revised_Item_Id
AND organization_id = p_Organization_Id
AND alternate_bom_designator is null
AND ((assembly_type = 1 and p_Assembly_Type = 1)
or p_Assembly_Type = 2);
SELECT routing_sequence_id
FROM BOM_OPERATIONAL_ROUTINGS
WHERE assembly_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND alternate_routing_designator IS NULL
AND ( (routing_type = 1 and p_assembly_type = 1)
OR p_assembly_type = 2 ) ;
select 1
from BOM_BILL_OF_MATERIALS
where bill_sequence_id = p_bill_sequence_id
and pending_from_ecn is not null
and pending_from_ecn = p_change_notice;
select 1
from BOM_BILL_OF_MATERIALS
where source_bill_sequence_id = p_bill_sequence_id -- R12: Common Bom
and source_bill_sequence_id <> bill_sequence_id;
select 1
from BOM_OPERATIONAL_ROUTINGS
where routing_sequence_id = p_routing_sequence_id
and pending_from_ecn is not null
and pending_from_ecn = p_change_notice;
select 1
from BOM_OPERATIONAL_ROUTINGS
where common_routing_sequence_id = p_routing_sequence_id
and common_routing_sequence_id <> routing_sequence_id;
cursor common_exists is select common_assembly_item_id
from BOM_BILL_OF_MATERIALS
where bill_sequence_id = p_bill_sequence_id
and bill_sequence_id <> source_bill_sequence_id; --R12
cursor common_exists is select 'Referencing'
from BOM_OPERATIONAL_ROUTINGS
where routing_sequence_id = p_routing_sequence_id
and routing_sequence_id <> common_routing_sequence_id;
SELECT 1
FROM ENG_ENGINEERING_CHANGES
WHERE change_notice = p_change_notice
AND organization_id = p_organization_id
AND approval_status_type = 3;
SELECT CHANGE_ID
FROM ENG_ENGINEERING_CHANGES
WHERE CHANGE_NOTICE = p_change_notice
AND ORGANIZATION_ID = p_organization_id;
SELECT ecr.ROUTE_TYPE_CODE
FROM ENG_ENGINEERING_CHANGES eec,
ENG_LIFECYCLE_STATUSES els,
ENG_CHANGE_ROUTES ecr
WHERE eec.CHANGE_ID = p_change_id
AND els.ENTITY_NAME(+) = 'ENG_CHANGE'
AND els.ENTITY_ID1(+) = eec.CHANGE_ID
AND els.STATUS_CODE(+) = eec.STATUS_CODE
AND els.ACTIVE_FLAG(+) = 'Y'
AND ecr.OBJECT_NAME(+) = 'ENG_CHANGE'
AND ecr.OBJECT_ID1(+) = p_change_id
AND ecr.ROUTE_ID(+) = els.CHANGE_WF_ROUTE_ID;
SELECT 1
FROM ENG_ENGINEERING_CHANGES
WHERE change_notice = p_change_notice
AND organization_id = p_organization_id
AND status_type = 1;
SELECT 'Invalid Op Seq Exists'
FROM SYS.DUAL
WHERE EXISTS ( SELECT 'X'
FROM BOM_OPERATION_SEQUENCES
WHERE change_notice = p_revised_item_rec.eco_name
AND routing_sequence_id = p_rev_item_unexp_rec.routing_sequence_id
AND revised_item_sequence_id =
p_rev_item_unexp_rec.Revised_Item_Sequence_Id
AND nvl(disable_date, p_revised_item_rec.new_effective_date+ 1)
<= p_revised_item_rec.new_effective_date ) ;
SELECT 1
FROM BOM_INVENTORY_COMPONENTS
WHERE change_notice = p_revised_item_rec.eco_name
AND bill_sequence_id = p_rev_item_unexp_rec.Bill_Sequence_Id
AND revised_item_sequence_id =
p_rev_item_unexp_rec.Revised_Item_Sequence_Id
AND nvl(disable_date,
p_revised_item_rec.new_effective_date+ 1)
<= p_revised_item_rec.new_effective_date
AND acd_type in (1,2);
CURSOR c_CheckUseUpDate IS
SELECT inventory_use_up_date
FROM mrp_system_items
WHERE inventory_use_up_date = p_schedule_date
AND inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND compile_designator = p_use_up_plan;
FOR CheckUseUpDate IN c_CheckUseUpDate LOOP
x_inventory_use_up_date := CheckUseUpDate.inventory_use_up_date;
SELECT bom_item_type
, pick_components_flag
, bom_enabled_flag
, eng_item_flag
FROM MTL_SYSTEM_ITEMS
WHERE ( bom_enabled_flag <> 'Y'
OR pick_components_flag <> 'N'
OR bom_item_type = l_PLANNING )
AND organization_id = p_org_id
AND inventory_item_id = p_item_id
;
SELECT 'CTP not unique'
FROM SYS.DUAL
WHERE EXISTS (SELECT NULL
FROM BOM_OPERATIONAL_ROUTINGS
WHERE ctp_flag = 1 -- Yes
AND NVL(cfm_routing_flag, 2) = NVL(p_cfm_routing_flag, 2)
AND organization_id = p_organization_id
AND assembly_item_id = p_revised_item_id
AND routing_sequence_id <> p_routing_sequence_id) ;
SELECT 'Priority not unique'
FROM SYS.DUAL
WHERE EXISTS (SELECT NULL
FROM BOM_OPERATIONAL_ROUTINGS
WHERE priority = p_priority
AND NVL(cfm_routing_flag, 2) = NVL(p_cfm_routing_flag, 2)
AND organization_id = p_organization_id
AND assembly_item_id = p_revised_item_id) ;
SELECT 'SubInv exists'
FROM SYS.DUAL
WHERE NOT EXISTS ( SELECT null
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory
);
SELECT DECODE(restrict_subinventories_code, 1, 'Y', 'N') restrict_code
, inventory_asset_flag
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id ;
SELECT 'checking for duplicates' dummy
FROM sys.dual
WHERE EXISTS (
SELECT null
FROM mtl_item_locations
WHERE organization_id = p_organization_id
AND inventory_location_id = p_locator_id
AND subinventory_code <> p_subinventory
);
SELECT stock_locator_control_code
INTO l_org_locator_control
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT location_control_code
INTO l_item_locator_control
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_revised_item_id;
SELECT RESTRICT_LOCATORS_CODE
INTO l_item_loc_restricted
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_revised_item_id;
SELECT 'Valid'
INTO l_dummy
FROM mtl_item_locations mil,
mtl_secondary_locators msl
WHERE msl.inventory_item_id = p_revised_item_id
AND msl.organization_id = p_organization_id
AND msl.subinventory_code = p_subinventory
AND msl.secondary_locator = p_locator_id
AND mil.inventory_location_id = msl.secondary_locator
AND mil.organization_id = msl.organization_id
AND NVL(mil.disable_date, SYSDATE+1) > SYSDATE ;
SELECT 'Valid'
INTO l_dummy
FROM mtl_item_locations mil
WHERE mil.subinventory_code = p_subinventory
AND mil.inventory_location_id = p_locator_id
AND mil.organization_id = p_organization_id
AND NVL(mil.DISABLE_DATE, SYSDATE+1) > SYSDATE;
SELECT 1
FROM bom_bill_of_materials
WHERE assembly_item_id = p_revied_item_id
AND organization_id = p_organization_id
AND NVL(alternate_bom_designator, 'NONE') = 'NONE';
SELECT 'Rev Item is only Eco for altenate routing'
FROM ENG_REVISED_ITEMS eri
, BOM_OPERATIONAL_ROUTINGS bor
WHERE bor.alternate_routing_designator IS NOT NULL
AND eri.routing_sequence_id = bor.routing_sequence_id(+)
AND eri.routing_sequence_id IS NOT NULL
AND eri.bill_sequence_id IS NULL
AND NVL(eri.from_end_item_unit_number,FND_API.G_MISS_CHAR)
= NVL(p_from_end_item_number,FND_API.G_MISS_CHAR )
AND NVL(eri.new_item_revision,FND_API.G_MISS_CHAR)
= NVL(p_new_item_revision ,FND_API.G_MISS_CHAR)
AND NVL(eri.new_routing_revision,FND_API.G_MISS_CHAR)
= NVL(p_new_routing_revsion,FND_API.G_MISS_CHAR)
AND TRUNC(eri.scheduled_date) = TRUNC(p_effective_date)
AND eri.change_notice = p_change_notice
AND eri.organization_id = p_organization_id
AND eri.revised_item_id = p_revised_item_id ;
SELECT structure_type_id
FROM bom_structures_b
WHERE assembly_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND ((alternate_bom_designator IS NULL AND p_alternate_bom_code IS NULL)
OR (p_alternate_bom_code IS NOT NULL AND alternate_bom_designator = p_alternate_bom_code));
SELECT bad.structure_type_id
FROM bom_alternate_designators bad
WHERE ((p_alternate_bom_code IS NULL AND bad.alternate_designator_code IS NULL AND bad.organization_id = -1)
OR (p_alternate_bom_code IS NOT NULL AND bad.alternate_designator_code = p_alternate_bom_code
AND bad.organization_id = p_organization_id));
SELECT 1
FROM BOM_OPERATIONAL_ROUTINGS
WHERE assembly_item_id = p_revied_item_id
AND organization_id = p_organization_id
AND NVL(alternate_routing_designator, 'NONE') = 'NONE';
SELECT 'Rev Item is only Eco for altenate routing'
FROM ENG_REVISED_ITEMS eri
, BOM_BILL_OF_MATERIALS bom
WHERE bom.alternate_bom_designator IS NOT NULL
AND eri.bill_sequence_id = bom.bill_sequence_id(+)
AND eri.bill_sequence_id IS NOT NULL
AND eri.routing_sequence_id IS NULL
AND NVL(eri.from_end_item_unit_number, FND_API.G_MISS_CHAR)
= NVL(p_from_end_item_number, FND_API.G_MISS_CHAR)
AND NVL(eri.new_item_revision,FND_API.G_MISS_CHAR)
= NVL(p_new_item_revision ,FND_API.G_MISS_CHAR)
AND NVL(eri.new_routing_revision,FND_API.G_MISS_CHAR)
= NVL(p_new_routing_revsion,FND_API.G_MISS_CHAR)
AND TRUNC(eri.scheduled_date) = trunc(p_effective_date)
AND eri.change_notice = p_change_notice
AND eri.organization_id = p_organization_id
AND eri.revised_item_id = p_revised_item_id ;
p_revised_item_rec.updated_revised_item_revision =
FND_API.G_MISS_CHAR
AND
p_revised_item_rec.alternate_bom_code IS NULL AND
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name =>'ENG_UPDATED_REVISION_MISSING'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
SELECT 'x'
FROM eng_revised_items
WHERE revised_item_id = p_rev_item_unexp_rec.revised_item_id
AND from_end_item_unit_number = p_revised_item_rec.From_End_Item_Unit_Number
AND revised_item_sequence_id <> NVL(p_rev_item_unexp_rec.revised_item_sequence_id,0)
AND change_notice = p_revised_item_rec.eco_name;
CURSOR CheckDupDateUnit IS
SELECT 'x'
FROM eng_revised_items
WHERE revised_item_id = p_rev_item_unexp_rec.revised_item_id
AND from_end_item_unit_number = NVL(p_revised_item_rec.New_From_End_Item_Unit_Number,
(NVL(p_revised_item_rec.From_End_Item_Unit_Number,
FND_API.G_MISS_NUM)))
AND scheduled_date = NVL(p_revised_item_rec.New_Effective_Date,
p_revised_item_rec.Start_Effective_Date)
AND new_item_revision = NVL(p_revised_item_rec.updated_revised_item_revision,
(NVL(p_revised_item_rec.new_revised_item_revision,
FND_API.G_MISS_NUM)))
AND organization_id = p_rev_item_unexp_rec.organization_id
AND change_notice = p_revised_item_rec.eco_name;
SELECT 1
FROM eng_engineering_changes
WHERE change_notice = p_revised_item_rec.eco_name
AND organization_id = p_rev_item_unexp_rec.organization_id
AND approval_status_type = 5;
SELECT bom_enabled_flag
FROM mtl_system_items msi
WHERE msi.inventory_item_id =
p_rev_item_unexp_rec.revised_item_id
AND msi.organization_id =
p_rev_item_unexp_rec.organization_id;
SELECT alternate_bom_designator
FROM bom_bill_of_materials
WHERE bill_sequence_id =
nvl(p_rev_item_unexp_rec.bill_sequence_id,
FND_API.G_MISS_NUM);
SELECT 'Product Family'
FROM mtl_system_items
WHERE inventory_item_id = p_rev_item_unexp_rec.revised_item_id
AND organization_id = p_rev_item_unexp_rec.organization_id
AND bom_item_type = 5; -- Product Family
SELECT component_sequence_id
FROM bom_inventory_components bic,
bom_bill_of_materials bom
WHERE bic.component_item_id = p_use_up_item_id
AND bic.implementation_date IS NOT NULL
AND bic.bill_sequence_id = bom.bill_sequence_id
AND bom.assembly_item_id = p_revised_item_id
AND bom.organization_id = p_organization_id
AND NVL(bom.alternate_bom_designator, 'NONE') =
NVL(p_alternate_designator, 'NONE')
AND NVL(bic.acd_type, -1) <> 3 -- Modified by MK on 10/30/2000
AND bic.effectivity_date <= NVL(p_use_up_date,SYSDATE)
AND NVL(bic.disable_date,p_use_up_date) >= NVL(p_use_up_date,SYSDATE); -- 2199507
SELECT scheduled_start_date
, start_quantity
FROM WIP_DISCRETE_JOBS
WHERE status_type = 1
AND wip_entity_id = p_wip_entity_id
AND ( common_bom_sequence_id = p_bill_sequence_id
OR common_routing_sequence_id = p_routing_sequence_id
) ;
SELECT 'Lot Number is invalid'
FROM SYS.DUAL
WHERE NOT EXISTS ( SELECT 'Valid Lot'
FROM WIP_DISCRETE_JOBS wdj1
WHERE wdj1.lot_number = p_lot_number
AND wdj1.status_type = 1
AND wdj1.scheduled_start_date >= p_start_effective_date
AND wdj1.organization_Id = p_org_id
AND wdj1.primary_item_id = p_rev_item_id
)
OR EXISTS (SELECT 'Invalid Lot'
FROM WIP_DISCRETE_JOBS wdj2
WHERE wdj2.lot_number = p_lot_number
AND ( wdj2.status_type <> 1 OR
wdj2.scheduled_start_date < p_start_effective_date)
AND wdj2.organization_Id = p_org_id
AND wdj2.primary_item_id = p_rev_item_id
) ;
SELECT 'WO Range is invalid'
FROM SYS.DUAL
WHERE NOT EXISTS ( SELECT 'Valid WO'
FROM WIP_DISCRETE_JOBS wdj1
, WIP_ENTITIES we1
WHERE wdj1.status_type = 1
AND ( wdj1.common_bom_sequence_id = p_bill_sequence_id
OR wdj1.common_routing_sequence_id = p_routing_sequence_id
)
AND wdj1.scheduled_start_date >= p_start_effective_date
AND wdj1.wip_entity_id = we1.wip_entity_id
AND we1.organization_id = p_org_id
AND we1.wip_entity_name >= p_from_wo_num
AND we1.wip_entity_name <= NVL(p_to_wo_num, p_from_wo_num)
)
OR EXISTS ( SELECT 'Invalid WO'
FROM WIP_DISCRETE_JOBS wdj2
, WIP_ENTITIES we2
WHERE ( wdj2.status_type <> 1 OR
wdj2.scheduled_start_date < p_start_effective_date )
AND ( wdj2.common_bom_sequence_id = p_bill_sequence_id
OR wdj2.common_routing_sequence_id = p_routing_sequence_id
)
AND wdj2.wip_entity_id = we2.wip_entity_id
AND we2.organization_id = p_org_id
AND we2.wip_entity_name >= p_from_wo_num
AND we2.wip_entity_name <= NVL(p_to_wo_num, p_from_wo_num)
) ;
SELECT completion_subinventory
, completion_locator_id
, ctp_flag
, priority
FROM BOM_OPERATIONAL_ROUTINGS
WHERE assembly_item_id = p_revised_item_id
AND organization_id = p_org_id
AND alternate_routing_designator = p_alternate_routing_code
;
SELECT locator_type
FROM mtl_item_sub_ast_trk_val_v
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory;
SELECT locator_type
FROM mtl_item_sub_trk_val_v
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory;
SELECT locator_type
FROM mtl_sub_ast_trk_val_v
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory;
SELECT locator_type
FROM mtl_subinventories_trk_val_v
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory;
SELECT alternate_routing_designator
FROM BOM_OPERATIONAL_ROUTINGS
WHERE routing_sequence_id = NVL( p_rev_item_unexp_rec.routing_sequence_id
, FND_API.G_MISS_NUM );
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE)
AND
( p_revised_item_rec.lot_number IS NOT NULL OR
p_revised_item_rec.from_cumulative_quantity IS NOT NULL OR
p_rev_item_unexp_rec.to_wip_entity_id IS NOT NULL OR
p_rev_item_unexp_rec.from_wip_entity_id IS NOT NULL )
)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_ACCESS_WOECTV_DENIED'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE )
AND ( p_revised_item_rec.mrp_active <> 2 OR
p_revised_item_rec.update_wip <> 1 )
AND
( p_revised_item_rec.lot_number IS NOT NULL OR
p_revised_item_rec.from_cumulative_quantity IS NOT NULL OR
p_rev_item_unexp_rec.to_wip_entity_id IS NOT NULL OR
p_rev_item_unexp_rec.from_wip_entity_id IS NOT NULL
)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_MAC_UWIP_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF p_revised_item_rec.mrp_active = 2 AND p_revised_item_rec.update_wip = 1
THEN
/*****************************************************************
-- If From Work Order is not Null then Lot Number must be Null
-- Added by MK 08/25/2000
******************************************************************/
IF (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE )
AND p_rev_item_unexp_rec.from_wip_entity_id IS NOT NULL
AND p_revised_item_rec.lot_number IS NOT NULL
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_LOTNUM_MUSTBE_NULL'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE )
AND p_rev_item_unexp_rec.to_wip_entity_id IS NOT NULL
AND p_rev_item_unexp_rec.from_wip_entity_id IS NULL
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_FROMWO_MUSTNOT_NULL'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE )
AND p_rev_item_unexp_rec.to_wip_entity_id IS NOT NULL
AND ( p_revised_item_rec.from_work_order > p_revised_item_rec.to_work_order
OR p_rev_item_unexp_rec.from_wip_entity_id IS NULL)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_FROMWO_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE )
AND p_rev_item_unexp_rec.to_wip_entity_id IS NOT NULL
AND p_rev_item_unexp_rec.from_wip_entity_id IS NOT NULL
AND p_revised_item_rec.from_cumulative_quantity IS NOT NULL
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_CUMQTY_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE )
AND ( p_rev_item_unexp_rec.from_wip_entity_id IS NULL
OR p_rev_item_unexp_rec.to_wip_entity_id IS NOT NULL )
AND p_revised_item_rec.from_cumulative_quantity IS NOT NULL
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_ECO_BY_CUMQTY_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE )
AND p_rev_item_unexp_rec.from_wip_entity_id IS NOT NULL
AND p_rev_item_unexp_rec.to_wip_entity_id IS NULL
AND p_revised_item_rec.from_cumulative_quantity IS NULL
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_FROMWO_ISNOT_NULL'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE )
AND p_revised_item_rec.lot_number IS NOT NULL
THEN
IF ( p_revised_item_rec.from_cumulative_quantity IS NOT NULL OR
p_rev_item_unexp_rec.to_wip_entity_id IS NOT NULL OR
p_rev_item_unexp_rec.from_wip_entity_id IS NOT NULL
)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_ECO_BY_LOT_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE )
AND p_rev_item_unexp_rec.from_wip_entity_id IS NOT NULL
AND p_revised_item_rec.from_cumulative_quantity IS NOT NULL
AND p_revised_item_rec.lot_number IS NULL
AND p_rev_item_unexp_rec.to_wip_entity_id IS NULL
THEN
OPEN l_wipjob_for_eco_cum_csr
( p_wip_entity_id => p_rev_item_unexp_rec.from_wip_entity_id
, p_bill_sequence_id => p_rev_item_unexp_rec.bill_sequence_id
, p_routing_sequence_id => p_rev_item_unexp_rec.routing_sequence_id
);
IF (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE )
AND p_rev_item_unexp_rec.from_wip_entity_id IS NOT NULL
AND p_revised_item_rec.from_cumulative_quantity IS NULL
AND p_revised_item_rec.lot_number IS NULL
AND p_revised_item_rec.from_work_order <= p_revised_item_rec.to_work_order
THEN
FOR l_wipjob_for_eco_wo_rec IN l_wipjob_for_eco_wo_csr
( p_from_wo_num => p_revised_item_rec.from_work_order
, p_to_wo_num => p_revised_item_rec.to_work_order
, p_org_id => p_rev_item_unexp_rec.organization_id
, p_start_effective_date => p_revised_item_rec.start_effective_date
, p_bill_sequence_id => p_rev_item_unexp_rec.bill_sequence_id
, p_routing_sequence_id => p_rev_item_unexp_rec.routing_sequence_id
)
LOOP
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_WORANGE_WO_RELEASED'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE )
AND p_revised_item_rec.eco_for_production = 1 -- Yes
AND
( p_revised_item_rec.lot_number IS NULL AND
p_rev_item_unexp_rec.from_wip_entity_id IS NULL
)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_ECO_FOR_PROD_MUSTBE_NO'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
(p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE )
AND p_revised_item_rec.eco_for_production = 2 -- No
AND
( p_revised_item_rec.lot_number IS NOT NULL OR
p_rev_item_unexp_rec.from_wip_entity_id IS NOT NULL
)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_ECO_FOR_PROD_MUSTBE_Y'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE )
AND l_is_item_unit_controlled
AND ( p_revised_item_rec.completion_subinventory IS NOT NULL OR
p_revised_item_rec.new_routing_revision IS NOT NULL OR
p_revised_item_rec.updated_routing_revision IS NOT NULL OR
p_rev_item_unexp_rec.completion_locator_id IS NOT NULL OR
NVL(p_revised_item_rec.ctp_flag, Bom_Default_Rtg_Header.Get_Ctp_Flag)
<> Bom_Default_Rtg_Header.Get_Ctp_Flag OR
p_revised_item_rec.routing_comment IS NOT NULL OR
p_revised_item_rec.priority IS NOT NULL )
THEN
OPEN l_rtg_header_csr ( p_revised_item_id => p_rev_item_unexp_rec.revised_item_id
, p_alternate_routing_code => p_revised_item_rec.alternate_bom_code
, p_org_id => p_rev_item_unexp_rec.organization_id
);
OR p_revised_item_rec.updated_routing_revision <> p_revised_item_rec.new_routing_revision
OR l_rtg_header_rec.completion_locator_id <> p_rev_item_unexp_rec.completion_locator_id
OR l_rtg_header_rec.ctp_flag <> p_revised_item_rec.ctp_flag
OR l_rtg_header_rec.priority <> p_revised_item_rec.priority
)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_CANNOT_CHANGE_RTG'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
p_revised_item_rec.transaction_type = ENG_Globals.G_OPR_UPDATE AND
( NVL(p_rev_item_unexp_rec.routing_sequence_id, 0) <>
NVL(p_old_rev_item_unexp_rec.routing_sequence_id, 0) OR
NVL(p_rev_item_unexp_rec.bill_sequence_id, 0) <>
NVL(p_old_rev_item_unexp_rec.bill_sequence_id, 0)
)
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Alternate you have entered : '
|| p_revised_item_rec.alternate_bom_code ); END IF;
l_token_tbl.DELETE;
( p_Message_Name => 'ENG_ALT_DESG_NOT_UPDATEABLE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
p_revised_item_rec.transaction_type = ENG_Globals.G_OPR_UPDATE
THEN
l_token_tbl.DELETE;
( p_Message_Name => 'ENG_ALT_DESG_NOT_UPDATEABLE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
l_token_tbl.delete;
(p_revised_item_rec.updated_routing_revision IS NOT NULL AND
p_revised_item_rec.updated_routing_revision <> FND_API.G_MISS_CHAR) OR
( p_revised_item_rec.completion_subinventory IS NOT NULL AND
p_revised_item_rec.completion_subinventory <> FND_API.G_MISS_CHAR )OR
(p_revised_item_rec.completion_location_name IS NOT NULL AND
p_revised_item_rec.completion_location_name <> FND_API.G_MISS_CHAR )
) AND
Not Val_Rev_Item_for_Rtg( p_revised_item_id => p_rev_item_unexp_rec.revised_item_id
, p_organization_id => p_rev_item_unexp_rec.organization_id
)
AND ( ((p_revised_item_rec.new_routing_revision IS NOT NULL) AND
(p_revised_item_rec.new_routing_revision <> FND_API.G_MISS_CHAR))
OR (p_revised_item_rec.updated_routing_revision IS NOT NULL AND
p_revised_item_rec.updated_routing_revision <> FND_API.G_MISS_CHAR)
OR p_revised_item_rec.ctp_flag <> Bom_Default_Rtg_Header.Get_Ctp_Flag
OR p_revised_item_rec.completion_subinventory IS NOT NULL
OR p_revised_item_rec.completion_location_name IS NOT NULL
OR p_revised_item_rec.priority IS NOT NULL
OR p_revised_item_rec.routing_comment IS NOT NULL
)
AND p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_CANNOT_BE_ON_RTG'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
ELSIF p_revised_item_rec.transaction_type = ENG_Globals.G_OPR_UPDATE AND
p_revised_item_rec.alternate_bom_code IS NOT NULL AND
p_revised_item_rec.updated_routing_revision IS NOT NULL
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_CANNOT_HAVE_RTG_REVISION'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF INSTR(NVL(p_revised_item_rec.updated_routing_revision,
p_revised_item_rec.new_routing_revision )
, '''') <> 0
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_RTGREV_QTE_NOT_ALLOWED'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
l_token_tbl.delete;
IF ( p_revised_item_rec.updated_routing_revision <>
p_old_revised_item_rec.new_routing_revision AND
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
) OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE
THEN
--
-- if the transaction type is create, the check new_routing_revision
--
IF p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE
THEN
l_new_revision_status :=
Validate_New_Rtg_Revision
( p_revised_item_id => p_rev_item_unexp_rec.revised_item_id
, p_organization_id => p_rev_item_unexp_rec.organization_id
, p_new_routing_revision =>
p_revised_item_rec.new_routing_revision
, p_revised_item_sequence_id =>
p_rev_item_unexp_rec.revised_item_sequence_id
, x_change_notice => l_change_notice
);
ELSE /* If Update, pass updated_routing_revision */
l_new_revision_status :=
Validate_New_Rtg_Revision
( p_revised_item_id => p_rev_item_unexp_rec.revised_item_id
, p_organization_id => p_rev_item_unexp_rec.organization_id
, p_new_routing_revision =>
p_revised_item_rec.updated_routing_revision
, p_revised_item_sequence_id =>
p_rev_item_unexp_rec.revised_item_sequence_id
, x_change_notice => l_change_notice
);
l_token_tbl.delete;
l_token_tbl(2).token_value := p_revised_item_rec.updated_routing_revision;
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
) OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE AND
p_revised_item_rec.ctp_flag = 1
THEN
IF NOT Check_CTP_Flag
( p_revised_item_id => p_rev_item_unexp_rec.revised_item_id
, p_organization_id => p_rev_item_unexp_rec.organization_id
, p_cfm_routing_flag => p_rev_item_unexp_rec.cfm_routing_flag
, p_routing_sequence_id =>
p_rev_item_unexp_rec.routing_sequence_id
)
THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_CTP_ALREADY_EXISTS'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
) OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE AND
p_revised_item_rec.priority IS NOT NULL
THEN
IF NOT Check_Priority
( p_revised_item_id => p_rev_item_unexp_rec.revised_item_id
, p_organization_id => p_rev_item_unexp_rec.organization_id
, p_cfm_routing_flag => p_rev_item_unexp_rec.cfm_routing_flag
, p_priority => p_revised_item_rec.priority )
THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_PRIORITY_DUPLICATE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF (( p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
AND
NVL(p_revised_item_rec.completion_subinventory, '0') <>
NVL(p_old_revised_item_rec.completion_subinventory, '0')
)
OR p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE
)
AND ( p_revised_item_rec.completion_subinventory IS NULL
OR p_revised_item_rec.completion_subinventory = FND_API.G_MISS_CHAR)
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN
Error_Handler.Write_Debug('Inside the process when subinventory is null' ) ;
(( p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
AND NVL(p_revised_item_rec.completion_subinventory, '0') <>
NVL(p_old_revised_item_rec.completion_subinventory, '0')
)
OR p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE
)
AND ( p_revised_item_rec.completion_subinventory IS NOT NULL
OR p_revised_item_rec.completion_subinventory <> FND_API.G_MISS_CHAR)
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN
Error_Handler.Write_Debug('Inside the process when subinventory is not null' ) ;
AND (( p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
AND NVL(p_rev_item_unexp_rec.completion_locator_id , 0) <>
NVL(p_old_rev_item_unexp_rec.completion_locator_id , 0)
)
OR (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE
AND p_revised_item_rec.completion_subinventory is not null
AND p_revised_item_rec.completion_subinventory <> FND_API.G_MISS_CHAR)
)
AND NOT Check_Locators( p_organization_id => p_rev_item_unexp_rec.organization_id
, p_revised_item_id => p_rev_item_unexp_rec.revised_item_id
, p_locator_id => p_rev_item_unexp_rec.completion_locator_id
, p_subinventory => p_revised_item_rec.completion_subinventory )
THEN
IF l_locator_control = 4 THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
) OR
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE AND
p_revised_item_rec.mixed_model_map = 1
THEN
IF NOT Check_Mixed_Model_Map THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_MMMF_ALREADY_EXISTS'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
( p_revised_item_rec.updated_revised_item_revision IS NOT NULL AND
p_revised_item_rec.updated_revised_item_revision <> FND_API.G_MISS_CHAR
)
THEN
FOR X_CheckDupUnit IN CheckDupDateUnit LOOP
--
-- The message text for this message is:
-- The revised item REVISED_ITEM_NAME already exists on ECO ECO_NAME with revision
-- NEW_OR_UPDATED_ITEM_REVISION and effective date NEW_OR_UPDATED_EFFECTIVE_DATE
-- So the tokens updated_item_revision and effective_date would need to replaced
-- depending
-- on what is being changed, since the user can change one and then leave the other
-- column to default.
--
SELECT 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
),
DECODE(p_revised_item_rec.new_effective_date, NULL,
p_revised_item_rec.start_effective_date,
p_revised_item_rec.new_effective_date
)
INTO l_token_tbl(3).token_value,
l_token_tbl(4).token_value
FROM SYS.DUAL;
l_token_tbl(3).token_name := 'NEW_OR_UPDATED_ITEM_REVISION';
l_token_tbl(4).token_name := 'NEW_OR_UPDATED_EFFECTIVE_DATE';
IF ENG_Globals.ECO_Cannot_Update
( p_change_notice => p_revised_item_rec.Eco_Name
, p_organization_id => p_rev_item_unexp_rec.organization_id
)
AND p_control_rec.caller_type <> 'SSWA' -- not required for plm
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_PROC_UPD_DISALLOWED'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
(p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE AND
p_revised_item_rec.new_effective_date IS NOT NULL))
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_UNIT_EFFDATE_NULL'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
(p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE AND
p_revised_item_rec.new_from_end_item_unit_number IS NOT NULL))
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_DATE_UNIT_NULL'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
p_revised_item_rec.update_wip = 1
THEN
/**********************************************************************
-- Added by AS on 07/06.
-- If revised item is unit controlled, Update_WIP must not be set
-- to Yes.
**********************************************************************/
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_ITEM_UNIT_UPDATE_WIP'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
p_revised_item_rec.transaction_type = Eng_Globals.G_OPR_UPDATE
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_SCHED_DATE_NOT_NULL'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
l_token_tbl.delete;
l_token_tbl.delete;
(p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE AND
trunc(p_revised_item_rec.earliest_effective_date) >
NVL(trunc(p_revised_item_rec.new_effective_date),
p_revised_item_rec.start_effective_date)))
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
l_token_tbl.delete;
( p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
AND
( p_revised_item_rec.new_effective_date IS NOT NULL AND
p_revised_item_rec.new_effective_date <> FND_API.G_MISS_DATE
)
)
)
THEN
l_IsDateValid := FALSE;
l_token_tbl.delete;
( p_revised_item_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE
AND
p_rev_item_unexp_rec.use_up_item_id <>
NVL(p_old_rev_item_unexp_rec.use_up_item_id, 0)
)
) AND
p_rev_item_unexp_rec.use_up_item_id <>
p_rev_item_unexp_rec.revised_item_id AND
p_rev_item_unexp_rec.use_up_item_id <> FND_API.G_MISS_NUM
THEN
l_IsUseUpValid := FALSE;
l_token_tbl.delete;
l_token_tbl.delete;
l_token_tbl.delete;
l_token_tbl.delete;
IF p_revised_item_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE
AND
( -- (p_control_rec.caller_type = 'FORM' AND
-- p_control_rec.validation_controller = 'SCHEDULED_DATE')
-- OR
-- (p_control_rec.caller_type = 'OI' AND
NVL( p_revised_item_rec.new_effective_date,
p_revised_item_rec.start_effective_date
) <> p_old_revised_item_rec.start_effective_date
)
THEN
l_result := Check_Reschedule
( p_revised_item_rec => p_revised_item_rec
, p_rev_item_unexp_rec => p_rev_item_unexp_rec
);
l_token_tbl.delete;
l_token_tbl.delete;
IF p_revised_item_rec.transaction_type = ENG_Globals.G_OPR_UPDATE
AND
( -- (p_control_rec.caller_type = 'FORM' AND
-- p_control_rec.validation_controller = 'SCHEDULED_DATE')
-- OR
-- (p_control_rec.caller_type = 'OI' AND
NVL( p_revised_item_rec.new_effective_date,
p_revised_item_rec.start_effective_date
) <> p_old_revised_item_rec.start_effective_date
)
THEN
IF NOT Check_Rtg_Reschedule
( p_revised_item_rec => p_revised_item_rec
, p_rev_item_unexp_rec => p_rev_item_unexp_rec
)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
l_token_tbl.delete;
(( p_revised_item_rec.transaction_type=ENG_Globals.G_OPR_UPDATE AND
NVL( p_revised_item_rec.status_type, 0) <>
p_old_revised_item_rec.status_type
)
)
AND
NOT ECO_Open
( p_change_notice => p_revised_item_rec.eco_name
, p_organization_id => p_rev_item_unexp_rec.organization_id
)
AND
p_revised_item_rec.status_type IN (1,2,4,7)
AND p_control_rec.caller_type <> 'SSWA'
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
l_token_tbl.delete;
SELECT 1
INTO l_result
FROM eng_engineering_changes
WHERE change_notice = p_revised_item_rec.eco_name
AND organization_id =
p_rev_item_unexp_rec.organization_id
AND status_type = p_revised_item_rec.status_type;
l_token_tbl.delete;
IF p_revised_item_rec.transaction_type = ENG_Globals.G_OPR_UPDATE AND
NVL(p_revised_item_rec.status_type, 0) <>
p_old_revised_item_rec.status_type
AND p_control_rec.caller_type <> 'SSWA'
AND
ECO_Approval_Requested
( p_change_notice => p_revised_item_rec.eco_name
, p_organization_id => p_rev_item_unexp_rec.organization_id
)
THEN
l_token_tbl.delete;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('checked - is status updateable'); END IF;
p_revised_item_rec.transaction_type = Eng_globals.G_OPR_UPDATE
THEN
l_token_tbl.DELETE;
( p_Message_Name => 'ENG_ALT_DESG_NOT_UPDATEABLE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
l_token_tbl.delete;
ELSIF p_revised_item_rec.transaction_type = Eng_Globals.G_OPR_UPDATE AND
p_revised_item_rec.alternate_bom_code IS NOT NULL AND
p_revised_item_rec.updated_revised_item_revision IS NOT NULL
AND l_plm_or_erp_change <> 'PLM'
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_CANNOT_HAVE_REVISION'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
l_token_tbl.delete;
( p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE AND
p_revised_item_rec.updated_revised_item_revision = l_current_item_revision))
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_ITEM_REV_NOT_EQ_CURR_REV'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF ( p_revised_item_rec.updated_revised_item_revision <>
p_old_revised_item_rec.new_revised_item_revision AND
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
) OR
(p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE AND
p_revised_item_rec.new_revised_item_revision IS NOT NULL)
THEN
-- 11.5.10E
-- Using from revision instead of the current revision in case
-- of PLM.
IF (l_plm_or_erp_change = 'PLM' AND
p_revised_item_rec.from_item_revision IS NOT NULL AND
p_revised_item_rec.from_item_revision <> FND_API.G_MISS_CHAR)
THEN
l_from_revision := p_revised_item_rec.from_item_revision;
ELSE /* If Update, pass updated_revised_item_revision */
l_new_revision_status :=
Validate_New_Item_Revision
( p_revised_item_id => p_rev_item_unexp_rec.revised_item_id
, p_organization_id => p_rev_item_unexp_rec.organization_id
, p_from_revision => l_from_revision
, p_new_item_revision =>
p_revised_item_rec.updated_revised_item_revision
, p_revised_item_sequence_id =>
p_rev_item_unexp_rec.revised_item_sequence_id
, x_change_notice => l_change_notice
);
l_token_tbl.delete;
l_token_tbl(1).token_value := p_revised_item_rec.updated_revised_item_revision ;
ELSIF (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
AND p_revised_item_rec.updated_revised_item_revision IS NOT NULL
AND ((p_revised_item_rec.new_revised_item_revision IS NOT NULL
AND p_revised_item_rec.updated_revised_item_revision <> p_revised_item_rec.new_revised_item_revision)
OR (p_revised_item_rec.new_revised_item_revision IS NULL))
AND p_control_rec.caller_type <> 'FORM' AND p_control_rec.caller_type <> 'SSWA')
THEN
l_is_revision_invalid := High_Date_Low_Revision (
p_revised_item_id => p_rev_item_unexp_rec.revised_item_id
, p_organization_id => p_rev_item_unexp_rec.organization_id
, p_new_item_revision => p_revised_item_rec.updated_revised_item_revision
, p_scheduled_date => NVL(p_revised_item_rec.New_Effective_Date, p_revised_item_rec.Start_Effective_Date)
, p_rev_item_seq_id => p_rev_item_unexp_rec.revised_item_sequence_id);
l_Token_Tbl.delete;
SELECT nvl(approval_status,'A')
FROM MTL_SYSTEM_ITEMS_B
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT 'Invalid Alaternatae'
FROM SYS.DUAL
WHERE NOT EXISTS ( SELECT NULL
FROM bom_alternate_designators
WHERE alternate_designator_code = p_alt_designator
AND organization_id = p_organization_id
) ;
IF p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE AND
(p_revised_item_rec.new_from_end_item_unit_number IS NOT NULL
AND
p_revised_item_rec.new_from_end_item_unit_number <> FND_API.G_MISS_CHAR)
THEN
IF NOT ENG_Validate.End_Item_Unit_Number
( p_from_end_item_unit_number =>
p_revised_item_rec.new_from_end_item_unit_number
, p_revised_item_id =>
p_rev_item_unexp_rec.revised_item_id
, x_err_text => l_err_text
)
THEN
x_return_status := FND_API.G_RET_STS_ERROR;
IF p_revised_item_rec.update_wip IS NOT NULL AND
( p_revised_item_rec.update_wip <>
p_old_revised_item_rec.update_wip OR
p_old_revised_item_rec.update_wip IS NULL )
THEN
IF NOT ENG_Validate.Update_Wip(p_revised_item_rec.update_wip ,
l_err_text ) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
l_token_tbl(2).token_name := 'UPDATE_WIP';
p_revised_item_rec.update_wip;
( p_Message_Name => 'ENG_UPDATE_WIP_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF p_revised_item_rec.update_wip = FND_API.G_MISS_NUM
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_UPDATE_WIP_MISSING'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Update WIP Validated . . .'); END IF;
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_ECO_FOR_PROD_MISSING'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
l_token_tbl.delete ;
* Procedure : Entity_Delete
* Parameters IN : Revised item exposed column record
* Revised item unexposed column record
* Parameters OUT: Mesg Token Table
* Return Status
* Purpose : Entity Delete procedure will check if the given revised item
* can be deleted without violating any business rules or
* constraints. Revised item's cannot be deleted if there are
* components on the bill or it revised item's bill is being
* referenced as common by any other bills in the same org or
* any other org.
* (Check of revised item being implemented or cancelled is done
* in the previous steps of the process flow)
******************************************************************************/
PROCEDURE Check_Entity_Delete
( x_return_status OUT NOCOPY VARCHAR2
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, 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
)
IS
l_err_text VARCHAR2(2000) := NULL;
check_delete NUMBER := 0;
SELECT 1
FROM BOM_INVENTORY_COMPONENTS
WHERE revised_item_sequence_id =
p_rev_item_unexp_rec.revised_item_sequence_id;
SELECT 'Rev Op Exist'
FROM SYS.DUAL
WHERE EXISTS ( SELECT NULL
FROM BOM_OPERATION_SEQUENCES
WHERE revised_item_sequence_id =
p_rev_item_unexp_rec.revised_item_sequence_id) ;
/* Bug 8491180: Do not consider the rows that has same CURRENT_ITEM_REVISION_ID and NEW_ITEM_REVISION_ID in the first SELECT
statement, as NEW_ITEM_REVISION_ID refer to existing current revision, but not the new revision, while deleting Item
Revision from Change Order. */
CURSOR allow_delete_rev IS
SELECT 1
FROM ENG_REVISED_ITEMS itm,
ENG_REVISED_ITEMS sitm
WHERE itm.REVISED_ITEM_ID = sitm.REVISED_ITEM_ID
AND itm.ORGANIZATION_ID = sitm.ORGANIZATION_ID
AND sitm.revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id
AND itm.STATUS_TYPE not in (5, 6)
AND (itm.CURRENT_ITEM_REVISION_ID = sitm.new_item_revision_id
OR itm.FROM_END_ITEM_REV_ID = sitm.new_item_revision_id)
AND (sitm.CURRENT_ITEM_REVISION_ID<>sitm.NEW_ITEM_REVISION_ID)
UNION ALL
SELECT 1
FROM ENG_REVISED_ITEMS sitm , bom_structures_b bsb
WHERE sitm.revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id
AND bsb.assembly_item_id = sitm.revised_item_id
AND bsb.organization_id = sitm.organization_id
AND EXISTS ( SELECT 1
FROM BOM_COMPONENTS_B bic
WHERE bic.bill_sequence_id = bsb.bill_sequence_id and
(bic.FROM_END_ITEM_REV_ID = sitm.new_item_revision_id
OR bic.TO_END_ITEM_REV_ID = sitm.new_item_revision_id
))
UNION ALL
SELECT 1
FROM eng_revised_items sitm
WHERE sitm.revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id
AND EXISTS ( SELECT 1
FROM BOM_COMPONENTS_B bic
WHERE bic.component_item_id = sitm.revised_item_id and
bic.COMPONENT_ITEM_REVISION_ID = sitm.new_item_revision_id);
check_delete := Check_Reference_Common
( p_change_notice => p_revised_item_rec.eco_name
, p_bill_sequence_id => p_rev_item_unexp_rec.bill_sequence_id
);
IF check_delete <> 0
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_CANNOT_DEL_COMMON_EXIST'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
check_delete := 0 ;
check_delete := Check_Reference_Rtg_Common
( p_change_notice => p_revised_item_rec.eco_name
, p_routing_sequence_id => p_rev_item_unexp_rec.routing_sequence_id
);
IF check_delete <> 0
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_CANNOT_DEL_RTG_COMMON_EXIST'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
OPEN allow_delete_rev;
FETCH allow_delete_rev into l_allow_rev;
CLOSE allow_delete_rev;
IF allow_delete_rev%ISOPEN
THEN
CLOSE allow_delete_rev;
l_err_text := G_PKG_NAME || ' : (Entity Delete Validation) ' ||
substrb(SQLERRM,1,200);
END Check_Entity_Delete;
* error if the operation is UPDATE and the record DOES NOT
* EXIST.
* In case of UPDATE if the record exists then the procedure
* will return the old record in the old entity parameters
* with a success status.
****************************************************************************/
PROCEDURE Check_Existence
( 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
, x_old_revised_item_rec IN OUT NOCOPY Eng_Eco_Pub.Revised_Item_Rec_Type
, x_old_rev_item_unexp_rec IN OUT NOCOPY Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status OUT NOCOPY VARCHAR2
, x_disable_revision OUT NOCOPY NUMBER --Bug no:3034642
)
IS
l_token_tbl Error_Handler.Token_Tbl_Type;
select current_phase_id, LIFECYCLE_ID ,ITEM_CATALOG_GROUP_ID from mtl_system_items
where INVENTORY_ITEM_ID =inv_id and organization_id =cp_org_id;
(Eng_Globals.G_OPR_UPDATE, Eng_Globals.G_OPR_DELETE)
THEN
Error_Handler.Add_Error_Token
( x_Mesg_token_tbl => l_Mesg_Token_Tbl
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_message_name => 'ENG_REV_ITEM_DOESNOT_EXIST'
, p_token_tbl => l_token_tbl
);
OR p_revised_item_rec.updated_revised_item_revision IS NOT NULL
OR p_revised_item_rec.earliest_effective_date IS NOT NULL
OR p_revised_item_rec.attribute_category IS NOT NULL
OR p_revised_item_rec.attribute2 IS NOT NULL
OR p_revised_item_rec.attribute3 IS NOT NULL
OR p_revised_item_rec.attribute4 IS NOT NULL
OR p_revised_item_rec.attribute5 IS NOT NULL
OR p_revised_item_rec.attribute7 IS NOT NULL
OR p_revised_item_rec.attribute8 IS NOT NULL
OR p_revised_item_rec.attribute9 IS NOT NULL
OR p_revised_item_rec.attribute11 IS NOT NULL
OR p_revised_item_rec.attribute12 IS NOT NULL
OR p_revised_item_rec.attribute13 IS NOT NULL
OR p_revised_item_rec.attribute14 IS NOT NULL
OR p_revised_item_rec.attribute15 IS NOT NULL
OR p_revised_item_rec.status_type IS NOT NULL
--p_revised_item_rec.new_effective_date --scheduled date
--p_rev_item_unexp_rec.bill_sequence_id IS NOT NULL
OR p_revised_item_rec.mrp_active IS NOT NULL
OR p_revised_item_rec.update_wip IS NOT NULL
OR p_rev_item_unexp_rec.use_up IS NOT NULL
OR p_rev_item_unexp_rec.use_up_item_id IS NOT NULL
OR p_rev_item_unexp_rec.revised_item_sequence_id IS NOT NULL
OR p_revised_item_rec.use_up_plan_name IS NOT NULL
OR p_revised_item_rec.change_description IS NOT NULL
OR p_rev_item_unexp_rec.auto_implement_date IS NOT NULL
OR p_revised_item_rec.from_end_item_unit_number IS NOT NULL
OR p_revised_item_rec.attribute1 IS NOT NULL
OR p_revised_item_rec.attribute6 IS NOT NULL
OR p_revised_item_rec.attribute10 IS NOT NULL
OR p_revised_item_rec.original_system_reference IS NOT NULL
OR p_rev_item_unexp_rec.from_wip_entity_id IS NOT NULL
OR p_rev_item_unexp_rec.to_wip_entity_id IS NOT NULL
OR p_revised_item_rec.from_cumulative_quantity IS NOT NULL
OR p_revised_item_rec.lot_number IS NOT NULL
OR p_rev_item_unexp_rec.cfm_routing_flag IS NOT NULL
OR p_revised_item_rec.completion_subinventory IS NOT NULL
OR p_rev_item_unexp_rec.completion_locator_id IS NOT NULL
OR p_revised_item_rec.priority IS NOT NULL
OR p_revised_item_rec.ctp_flag IS NOT NULL
OR p_rev_item_unexp_rec.routing_sequence_id IS NOT NULL
OR p_revised_item_rec.updated_routing_revision IS NOT NULL
OR p_revised_item_rec.routing_comment IS NOT NULL
OR p_revised_item_rec.eco_for_production IS NOT NULL
--p_rev_item_unexp_rec.change_id IS NOT NULL
OR p_revised_item_rec.Transfer_Or_Copy IS NOT NULL
OR p_revised_item_rec.Transfer_OR_Copy_Item IS NOT NULL
OR p_revised_item_rec.Transfer_OR_Copy_Bill IS NOT NULL
OR p_revised_item_rec.Transfer_OR_Copy_Routing IS NOT NULL
OR p_revised_item_rec.Copy_To_Item IS NOT NULL
OR p_revised_item_rec.Copy_To_Item_Desc IS NOT NULL
OR p_revised_item_rec.selection_option IS NOT NULL
OR p_revised_item_rec.selection_date IS NOT NULL
OR p_revised_item_rec.selection_unit_number IS NOT NULL
OR p_rev_item_unexp_rec.status_code IS NOT NULL
OR p_revised_item_rec.status_type IS NOT NULL
) THEN
-- The user has given values for some other colums
-- Thus, it is assumed that the user is trying to update these columns
-- Since these values cannot be updated when the CO is in scheduled status
-- Error is thrown
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_RIT_NO_UPDATE_SCHEDULED'
, p_Mesg_Token_Tbl => l_mesg_token_tbl
, x_Mesg_Token_Tbl => l_mesg_token_tbl
, p_Token_Tbl => l_token_tbl
);
SELECT status_type
FROM eng_revised_items
WHERE revised_item_id = p_revised_item_id
AND organization_id = p_organization_id --* Added for Bug 5174223
AND change_notice = p_change_notice
AND NVL(from_end_item_unit_number, 'NONE')
= NVL(p_from_end_item_number, 'NONE')
AND NVL(new_routing_revision,'NULL')
= NVL(p_new_routing_revsion,'NULL')
AND NVL(new_item_revision, 'NULL') = NVL(p_new_item_revision, 'NULL')
AND trunc(scheduled_date) = trunc(p_effectivity_date);
SELECT bom_item_type,eng_item_flag
FROM mtl_system_items
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id;
SELECT 1
FROM SYS.DUAL
WHERE NOT EXISTS
( SELECT NULL
FROM BOM_OPERATION_SEQUENCES
WHERE NVL(operation_type, 1) = NVL(p_operation_type, 1)
AND effectivity_date = p_effectivity_date
AND routing_sequence_id = p_routing_sequence_id
AND operation_seq_num = p_operation_seq_num
)
AND EXISTS
( SELECT NULL
FROM ENG_REVISED_OPERATIONS
WHERE NVL(operation_type, 1) = NVL(p_operation_type, 1)
AND TRUNC(effectivity_date) = TRUNC(p_effectivity_date)
AND routing_sequence_id = p_routing_sequence_id
AND operation_seq_num = p_operation_seq_num
);
SELECT meaning
INTO l_status_type_name
FROM fnd_lookup_values_vl
WHERE lookup_type='ECG_ECN_STATUS'
AND lookup_code = revised_item.status_type;
l_token_tbl.delete;
l_token_tbl.delete;
l_token_tbl.DELETE;
l_token_tbl.DELETE;
SELECT ecpv.attribute_code ,
ecpv.attribute_char_value
FROM eng_change_policies_v ecpv ,
eng_engineering_changes eec
WHERE ecpv.policy_object_name = 'EGO_CHANGE_TYPE'
AND ecpv.policy_object_pk1_value = eec.change_order_type_id
AND eec.change_notice = p_change_notice;
l_sql := 'SELECT COUNT(*)
FROM mtl_system_items_b i,
bom_parameters bp
WHERE i.organization_id = :1
AND i.inventory_item_status_code not in (''Inactive'', ''Obsolete'')
AND i.inventory_item_status_code <> nvl(bp.bom_delete_status_code, FND_API.G_MISS_CHAR)
AND i.organization_id = bp.organization_id
AND i.inventory_item_id = :2';
, p_updated_revision IN VARCHAR2
, p_new_item_revision_id IN NUMBER
, p_current_item_revision_id IN NUMBER
-- effectivity
, p_start_effective_date IN DATE
, p_new_effective_date IN DATE
, p_earliest_effective_date IN DATE
-- bill and routing
, p_alternate_bom_code IN VARCHAR2
, p_bill_sequence_id IN NUMBER
, p_from_unit_number IN VARCHAR2
, p_new_from_unit_number IN VARCHAR2
, p_from_end_item_id IN NUMBER
, p_from_end_item_revision_id IN NUMBER
, p_routing_sequence_id IN NUMBER
, p_completion_subinventory IN VARCHAR2
, p_completion_locator_id IN NUMBER
, p_priority IN NUMBER
, p_ctp_flag IN NUMBER
, p_new_routing_revision IN VARCHAR2
, p_updated_routing_revision IN VARCHAR2
, p_eco_for_production IN NUMBER
, p_cfm_routing_flag IN NUMBER
-- useup
, p_use_up_plan_name IN VARCHAR2
, p_use_up_item_id IN NUMBER
, p_use_up IN NUMBER
-- wip
, p_disposition_type IN NUMBER
, p_update_wip IN NUMBER
, p_mrp_active IN NUMBER
, p_from_wip_entity_id IN NUMBER
, p_to_wip_entity_id IN NUMBER
, p_from_cumulative_quantity IN NUMBER
, p_lot_number IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Validate_Revised_Item';
l_revised_item_rec.Updated_Revised_Item_Revision:= p_updated_revision;
l_revised_item_rec.updated_routing_revision := p_updated_routing_revision;
l_revised_item_rec.update_wip := p_update_wip;
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
, from_wip_entity_id
, to_wip_entity_id
, from_cum_qty
, lot_number
, cfm_routing_flag
, completion_subinventory
, completion_locator_id
, priority
, ctp_flag
, routing_sequence_id
, new_routing_revision
, routing_comment
, eco_for_production
, change_id
, status_code
INTO
l_old_revised_item_rec.eco_name
, l_old_rev_item_unexp_rec.organization_id
, l_old_rev_item_unexp_rec.revised_item_id
, l_old_rev_item_unexp_rec.implementation_date
, l_old_rev_item_unexp_rec.cancellation_date
, l_old_revised_item_rec.cancel_comments
, l_old_revised_item_rec.disposition_type
, l_old_revised_item_rec.new_revised_item_revision
, l_old_revised_item_rec.earliest_effective_date
, l_old_revised_item_rec.attribute_category
, l_old_revised_item_rec.attribute2
, l_old_revised_item_rec.attribute3
, l_old_revised_item_rec.attribute4
, l_old_revised_item_rec.attribute5
, l_old_revised_item_rec.attribute7
, l_old_revised_item_rec.attribute8
, l_old_revised_item_rec.attribute9
, l_old_revised_item_rec.attribute11
, l_old_revised_item_rec.attribute12
, l_old_revised_item_rec.attribute13
, l_old_revised_item_rec.attribute14
, l_old_revised_item_rec.attribute15
, l_old_revised_item_rec.status_type
, l_old_revised_item_rec.start_effective_date
, l_rev_item_unexp_rec.bill_sequence_id
, l_old_revised_item_rec.mrp_active
, l_old_revised_item_rec.update_wip
, l_old_rev_item_unexp_rec.use_up
, l_old_rev_item_unexp_rec.use_up_item_id
, l_old_rev_item_unexp_rec.revised_item_sequence_id
, l_old_revised_item_rec.use_up_plan_name
, l_old_revised_item_rec.change_description
, l_old_rev_item_unexp_rec.auto_implement_date
, l_old_revised_item_rec.attribute1
, l_old_revised_item_rec.attribute6
, l_old_revised_item_rec.attribute10
, l_old_rev_item_unexp_rec.from_wip_entity_id
, l_old_rev_item_unexp_rec.to_wip_entity_id
, l_old_revised_item_rec.from_cumulative_quantity
, l_old_revised_item_rec.lot_number
, l_old_rev_item_unexp_rec.cfm_routing_flag
, l_old_revised_item_rec.completion_subinventory
, l_old_rev_item_unexp_rec.completion_locator_id
, l_old_revised_item_rec.priority
, l_old_revised_item_rec.ctp_flag
, l_old_rev_item_unexp_rec.routing_sequence_id
, l_old_revised_item_rec.new_routing_revision
, l_old_revised_item_rec.routing_comment
, l_old_revised_item_rec.eco_for_production
, l_old_rev_item_unexp_rec.change_id
, l_old_rev_item_unexp_rec.status_code
FROM eng_revised_items
WHERE revised_item_sequence_id = p_revised_item_sequence_id;