[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT status_type
INTO l_status_type
FROM eng_engineering_changes
WHERE change_notice = g_revised_item_rec.eco_name
AND organization_id = g_rev_item_unexp_rec.organization_id;
SELECT bill_sequence_id
INTO l_bill_sequence_id
FROM bom_bill_of_materials
WHERE assembly_item_id = g_rev_item_unexp_rec.revised_item_id
AND NVL(alternate_bom_designator, 'NONE') =
NVL(g_revised_item_rec.alternate_bom_code, 'NONE')
AND organization_id = g_rev_item_unexp_rec.organization_id;
SELECT routing_sequence_id
INTO l_rtg_sequence_id
FROM bom_operational_routings
WHERE assembly_item_id = g_rev_item_unexp_rec.revised_item_id
AND NVL(alternate_routing_designator, 'NONE') =
NVL(g_revised_item_rec.alternate_bom_code, 'NONE')
AND organization_id = g_rev_item_unexp_rec.organization_id;
* Function : Get_Update_WIP
* Returns : Number
* Purpose : Function will look at the item attribute build_in_wip for the
* the current revised item and will return that as the default
* value for the column.
*****************************************************************************/
FUNCTION Get_Update_Wip
RETURN NUMBER
IS
l_build_in_wip VARCHAR2(1) := NULL;
l_update_wip NUMBER := NULL;
SELECT build_in_wip_flag
INTO l_build_in_wip
FROM mtl_system_items
WHERE inventory_item_id = g_rev_item_unexp_rec.revised_item_id
AND organization_id = g_rev_item_unexp_rec.organization_id;
l_update_wip := 1;
l_update_wip := 2;
RETURN l_update_wip;
END Get_Update_Wip;
SELECT eng_revised_items_s.NEXTVAL
INTO l_revised_item_seq_id
FROM DUAL;
/*SELECT LP.PROJ_ELEMENT_ID
into l_id
FROM PA_EGO_LIFECYCLES_PHASES_V LP, MTL_ITEM_REVISIONS MIR
WHERE
LP.PROJ_ELEMENT_ID =MIR.CURRENT_PHASE_ID
AND MIR.INVENTORY_ITEM_ID = p_rev_item_id
AND MIR.ORGANIZATION_ID = p_org_id
AND MIR.REVISION = p_current_revision; */ -- Commented By LKASTURI
-- Bug 3311072: Change the query to select item phase
-- Added By LKASTURI
SELECT CURRENT_PHASE_ID
INTO l_id
FROM MTL_System_items_vl
WHERE INVENTORY_ITEM_ID = p_rev_item_id
AND ORGANIZATION_ID = p_org_id;
select STRUCTURE_REVISION_ID into l_id
from should use minor revision
where
BILL_SEQUENCE_ID = p_bill_seq_id
and REVISION = p_current_revision
and OBJECT_REVISION_ID = p_item_rev_id;
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;
SELECT ecsv.status_code , ecsv.status_type
FROM eng_change_statuses_vl ecsv
WHERE ecsv.status_code IN (SELECT els1.status_code
FROM eng_lifecycle_statuses els1
WHERE els1.entity_name='ENG_CHANGE'
AND els1.entity_id1 = cp_change_id
AND els1.active_flag = 'Y'
AND els1.sequence_number = (SELECT min(els2.sequence_number)
FROM eng_lifecycle_statuses els2
WHERE els2.entity_name='ENG_CHANGE'
AND els2.entity_id1 = cp_change_id
AND els2.active_flag = 'Y'));
SELECT status_code ,status_type
FROM eng_revised_items
WHERE revised_item_sequence_id = cp_rev_item_seq_id;
IF g_revised_item_rec.update_wip IS NULL THEN
-- Added IF condition because update_wip will be set to No
-- for Unit Controlled items.
-- Added by AS on 07/06/99
IF NOT BOM_Globals.Get_Unit_Controlled_Item
THEN
g_revised_item_rec.update_wip := Get_Update_Wip;
g_revised_item_rec.update_wip := 2;
Select CFM_ROUTING_FLAG,CTP_FLAG
into g_rev_item_unexp_rec.cfm_routing_flag,g_revised_item_rec.ctp_flag
from BOM_OPERATIONAL_ROUTINGS
where ROUTING_SEQUENCE_ID = g_rev_item_unexp_rec.routing_sequence_id;
* filled in for an update record and will copy values for those
* columns from the old record. If the columns that the user has
* given are having a missing or any other then those columns
* are not copied.
********************************************************************************/
PROCEDURE Populate_Null_Columns
( p_revised_item_rec IN ENG_Eco_PUB.Revised_item_Rec_Type
, p_old_revised_item_rec IN Eng_Eco_Pub.Revised_item_Rec_Type
, p_rev_item_unexp_Rec IN Eng_Eco_Pub.Rev_item_Unexposed_Rec_Type
, p_old_rev_item_unexp_Rec IN Eng_Eco_Pub.Rev_item_Unexposed_Rec_Type
, x_revised_item_Rec IN OUT NOCOPY Eng_Eco_Pub.Revised_Item_Rec_Type
, x_rev_item_unexp_Rec IN OUT NOCOPY Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
)
IS
l_revised_item_rec ENG_Eco_PUB.Revised_Item_Rec_Type :=
p_revised_item_rec;
IF l_revised_item_rec.update_wip IS NULL THEN
l_revised_item_rec.update_wip := p_old_revised_item_rec.update_wip;
IF l_revised_item_rec.updated_revised_item_revision IS NULL THEN
l_revised_item_rec.updated_revised_item_revision := p_old_revised_item_rec.new_revised_item_revision ;
IF l_revised_item_rec.updated_routing_revision IS NULL THEN
l_revised_item_rec.updated_routing_revision := p_old_revised_item_rec.new_routing_revision ;
SELECT 1
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 p_alternate_bom_designator IS NULL)
OR alternate_bom_designator = p_alternate_bom_designator);
SELECT 1
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 l_assembly_type = 1)
or l_assembly_type = 2);
SELECT 1
FROM BOM_OPERATIONAL_ROUTINGS
WHERE assembly_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND alternate_routing_designator = p_alternate_bom_designator;
SELECT 1
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 l_routing_type = 1)
or l_routing_type = 2);
cursor bill_seq_id is select bom_inventory_components_s.nextval from sys.dual;
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 : If the user has update the use up plan or the use up item
* then the schedule date also needs to be changed. This schedule
* is has to be such that it is not greater than any of the
* components on the ECO and should be within the range of the
* given mrp plan.
******************************************************************************/
FUNCTION Get_Scheduled_Date
( p_use_up_item_id IN NUMBER
, p_use_up_plan_name IN VARCHAR2
, p_revised_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_bill_sequence_id IN NUMBER
) RETURN DATE
IS
l_scheduled_date DATE := NULL;
SELECT si.inventory_use_up_date, bl.assembly_item_id
FROM mrp_system_items si,
bom_lists bl
WHERE bl.sequence_id = l_sequence_id
AND ( bl.assembly_item_id = p_revised_item_id
OR bl.assembly_item_id IN
( SELECT component_item_id
FROM bom_inventory_components
WHERE bill_sequence_id = p_bill_sequence_id
AND nvl(acd_type,1) <> 3
AND effectivity_date <= si.inventory_use_up_date
AND nvl(disable_date, si.inventory_use_up_date + 1) >
si.inventory_use_up_date
)
)
AND si.organization_id = p_organization_id
AND si.compile_designator = p_use_up_plan_name
AND si.inventory_item_id = bl.assembly_item_id
AND si.inventory_use_up_date >= trunc(sysdate)
AND (EXISTS ( SELECT 'valid'
FROM mrp_plans pl2
WHERE pl2.organization_id = p_organization_id
AND pl2.explosion_completion_date <=
pl2.data_completion_date
AND pl2.data_completion_date <=
pl2.plan_completion_date
AND pl2.plan_type in (1,2)
AND pl2.compile_designator = p_use_up_plan_name
)
OR
EXISTS ( --added by arudresh, bug: 3725067
SELECT plan_completion_date
FROM mrp_plan_organizations_v
WHERE compile_designator = p_use_up_plan_name
AND planned_organization = p_organization_id
)
);
ENG_REVISED_ITEMS_PKG.Insert_BOM_Lists
( X_Revised_Item_Id => p_revised_item_id
, X_Sequence_Id => l_sequence_id
, X_Bill_Sequence_Id => p_bill_sequence_id
);
ENG_REVISED_ITEMS_PKG.Delete_BOM_Lists (X_Sequence_Id => l_sequence_id);
SELECT requestor_id
INTO l_requestor
FROM eng_engineering_changes
WHERE change_notice = p_change_notice
AND organization_id = p_organization_id;
SELECT ecsv.status_code
FROM eng_change_statuses_vl ecsv
WHERE ecsv.status_code IN (SELECT els1.status_code
FROM eng_lifecycle_statuses els1
WHERE els1.entity_name='ENG_CHANGE'
AND els1.entity_id1 = cp_change_id
AND els1.active_flag = 'Y'
AND els1.sequence_number = (SELECT min(els2.sequence_number)
FROM eng_lifecycle_statuses els2
WHERE els2.entity_name='ENG_CHANGE'
AND els2.entity_id1 = cp_change_id
AND els2.active_flag = 'Y'));
CURSOR c_CheckApproval IS 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 1
FROM MTL_ITEM_REVISIONS
WHERE inventory_item_id = p_rev_item_unexp_rec.revised_item_id
AND organization_id = p_rev_item_unexp_rec.organization_id
AND revision = p_revision;
SELECT bill_sequence_id
FROM bom_bill_of_materials
WHERE assembly_item_id = p_rev_item_unexp_rec.revised_item_id
AND organization_id = p_rev_item_unexp_rec.organization_id
AND NVL(alternate_bom_designator, 'none') =
NVL(p_revised_item_rec.alternate_bom_code, 'none');
SELECT 1
FROM MTL_RTG_ITEM_REVISIONS
WHERE inventory_item_id = p_rev_item_unexp_rec.revised_item_id
AND organization_id = p_rev_item_unexp_rec.organization_id
AND process_revision = p_revision;
SELECT routing_sequence_id
FROM BOM_OPERATIONAL_ROUTINGS
WHERE assembly_item_id = p_rev_item_unexp_rec.revised_item_id
AND organization_id = p_rev_item_unexp_rec.organization_id
AND NVL(alternate_routing_designator, 'none') =
NVL(p_revised_item_rec.alternate_bom_code, 'none');
SELECT msi.bom_item_type , msi.tracking_quantity_ind
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 process_enabled_flag
FROM mtl_parameters
WHERE organization_id = p_rev_item_unexp_rec.organization_id;
l_revised_item_rec.updated_revised_item_revision IS NOT NULL OR
l_revised_item_rec.updated_revised_item_revision <> FND_API.G_MISS_CHAR OR
l_revised_item_rec.new_routing_revision IS NOT NULL OR
l_revised_item_rec.new_routing_revision <> FND_API.G_MISS_CHAR OR
l_revised_item_rec.updated_routing_revision IS NOT NULL OR
l_revised_item_rec.updated_routing_revision <> FND_API.G_MISS_CHAR
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_SET_REV_NULL'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
, p_message_type => 'W');
l_revised_item_rec.updated_revised_item_revision := NULL ;
l_revised_item_rec.updated_routing_revision := NULL ;
IF (( (l_revised_item_rec.updated_revised_item_revision <>
l_revised_item_rec.new_revised_item_revision )
OR
( l_revised_item_rec.updated_revised_item_revision IS NULL AND
l_revised_item_rec.new_revised_item_revision IS NOT NULL )
OR
( l_revised_item_rec.updated_revised_item_revision IS NOT NULL AND
l_revised_item_rec.new_revised_item_revision IS NULL )
)
AND
l_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
)
OR
l_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE
THEN
IF l_revised_item_rec.transaction_type =
ENG_Globals.G_OPR_CREATE
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN
Error_Handler.Write_Debug('In transaction type = Create, Checking for revised item revision: ' ||
p_revised_item_rec.new_revised_item_revision);
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Item Revision set for Insert . . .'); END IF;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Item Revision set for Update . . .'); END IF;
ENG_Globals.G_OPR_UPDATE
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN
Error_Handler.Write_Debug('In transaction type = Update , Checking for revised item revision: ' ||
l_revised_item_rec.updated_revised_item_revision );
l_revised_item_rec.updated_revised_item_revision
)
LOOP
l_rev_already_exists := 1;
l_revised_item_rec.updated_revised_item_revision <>
FND_API.G_MISS_CHAR
THEN
--
-- Insert new revision information into
-- MTL_ITEM_REVISIONS
--
IF l_revised_item_rec.new_revised_item_revision IS NULL OR
l_revised_item_rec.new_revised_item_revision
= l_current_revision -- Added by MK on 02/13/2001
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Item Revision set for Insert . . .'); END IF;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Item Revision set for Update. . .'); END IF;
l_revised_item_rec.updated_revised_item_revision =
FND_API.G_MISS_CHAR OR
l_revised_item_rec.updated_revised_item_revision
IS NULL )
)
OR -- Added by MK on 02/13/2001 for Bug 1641488
( l_rev_already_exists = 1 AND
l_revised_item_rec.updated_revised_item_revision
= l_current_revision
))
-- and l_revised_item_rec.transaction_type = ENG_Globals.G_OPR_DELETE
-- Bug 3629755
-- Commented as it is within the
-- l_revised_item_rec.transaction_type = ENG_Globals.G_OPR_UPDATE Condition
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Item Revision set for Delete . . .'); END IF;
l_revised_item_rec.updated_revised_item_revision
<> l_current_revision
THEN
-- Update new item revision information in
-- MTL_ITEM_REVISIONS
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Item Revision set for Update. . .'); END IF;
END IF; /* If Update Ends */
END IF; /* If Create or Update Ends */
SELECT 1
INTO l_revEffStrc_exists
FROM bom_structures_b
WHERE effectivity_control = 4
AND assembly_item_id = l_rev_item_unexp_rec.revised_item_id
AND organization_id = l_rev_item_unexp_rec.organization_id
AND structure_type_id = l_structure_type_id
AND ROWNUM = 1;
l_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
THEN
G_SCHED_DATE_CHANGED := TRUE;
IF p_revised_item_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE
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 Bom_Globals.Get_Debug = 'Y' THEN
Error_Handler.Write_Debug('Scheduled Date is been trying to udpate. . .');
IF l_revised_item_rec.transaction_type = ENG_Globals.G_OPR_UPDATE AND
l_revised_item_rec.status_type = 2 AND
l_revised_item_rec.status_type <>
p_old_revised_item_rec.status_type AND
l_revised_item_rec.mrp_active <> 2
THEN
l_revised_item_rec.mrp_active := 2;
ELSIF l_revised_item_rec.transaction_type = ENG_Globals.G_OPR_UPDATE AND
l_revised_item_rec.status_type <> 2 AND
l_revised_item_rec.status_type <>
p_old_revised_item_rec.status_type AND
l_revised_item_rec.mrp_active <> 1
-- add the next line for fixing BUG 1577957
AND nvl(l_revised_item_rec.eco_for_production,2) = 2
-- add the next line for fixing BUG 2218574
AND fnd_profile.value('ENG:DEFAULT_MRP_ACTIVE') = '1'
THEN
l_revised_item_rec.mrp_active := 1;
IF l_revised_item_rec.updated_revised_item_revision = FND_API.G_MISS_CHAR
THEN
l_revised_item_rec.updated_revised_item_revision := NULL;
IF l_revised_item_rec.updated_routing_revision = FND_API.G_MISS_CHAR
THEN
l_revised_item_rec.updated_routing_revision := NULL;
IF (( (l_revised_item_rec.updated_routing_revision <>
l_revised_item_rec.new_routing_revision )
OR
( l_revised_item_rec.updated_routing_revision IS NULL AND
l_revised_item_rec.new_routing_revision IS NOT NULL )
OR
( l_revised_item_rec.updated_routing_revision IS NOT NULL AND
l_revised_item_rec.new_routing_revision IS NULL )
)
AND l_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
)
OR
l_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
( 'Checking for routing revision: ' || p_revised_item_rec.new_routing_revision );
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Rtg Revision set for Insert . . .'); END IF;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Rtg Revision set for Update . . .'); END IF;
ELSIF l_revised_item_rec.transaction_type = ENG_Globals.G_OPR_UPDATE
THEN
l_current_rtg_revision :=
Get_Current_Rtg_Revision
( p_revised_item_id => l_rev_item_unexp_rec.revised_item_id
, p_organization_id => l_rev_item_unexp_rec.organization_id
, p_revision_date => SYSDATE
);
l_revised_item_rec.updated_routing_revision
)
LOOP
l_rtg_rev_already_exists := 1;
l_revised_item_rec.updated_routing_revision <>
FND_API.G_MISS_CHAR
THEN
--
-- Insert updated routing revision information into
-- MTL_RTG_ITEM_REVISIONS
IF l_revised_item_rec.new_routing_revision IS NULL OR
l_revised_item_rec.new_routing_revision
= l_current_rtg_revision -- Added by MK on 02/13/2001
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Rtg Revision set for Insert . . .'); END IF;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Rtg Revision set for Update. . .'); END IF;
( l_revised_item_rec.updated_routing_revision
= FND_API.G_MISS_CHAR OR
l_revised_item_rec.updated_routing_revision
IS NULL )
)
OR -- Added by MK on 02/13/2001 for Bug 1641488
( l_rtg_rev_already_exists = 1 AND
l_revised_item_rec.updated_routing_revision
= l_current_rtg_revision
)
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Rtg Revision set for Delete. . .'); END IF;
l_revised_item_rec.updated_routing_revision <> l_current_revision
THEN
-- Update new routing revision information in
-- MTL_RTG_ITEM_REVISIONS
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Rtg Revision set for Update. . .'); END IF;
END IF; /* If Update Ends */
END IF; /* If Create or Update Ends */
Error_Handler.Write_Debug('Before MRP Active and WIP Update Defaulting') ;
Error_Handler.Write_Debug('Update Wip: ' || to_char(l_revised_item_rec.update_wip )) ;
l_revised_item_rec.update_wip <> 1 )
AND
( l_revised_item_rec.lot_number IS NOT NULL OR
l_revised_item_rec.from_cumulative_quantity IS NOT NULL OR
l_rev_item_unexp_rec.to_wip_entity_id IS NOT NULL OR
l_rev_item_unexp_rec.from_wip_entity_id IS NOT NULL
)
THEN
-- Set MRP_Active to No and Update_Wip to Yes
l_revised_item_rec.mrp_active := 2;
l_revised_item_rec.update_wip := 1;
Error_Handler.Write_Debug('Mrp Active and Update Wip are set to Yes') ;
Error_Handler.Write_Debug('UPDATE Wip: ' || to_char(l_revised_item_rec.update_wip )) ;
IF l_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
AND ( l_revised_item_rec.eco_for_production
<> p_old_revised_item_rec.eco_for_production OR
( l_revised_item_rec.eco_for_production IS NOT NULL AND
p_old_revised_item_rec.eco_for_production IS NULL )
)
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN
Error_Handler.Write_Debug('Eco for Prod has been changed. . . Yes') ;