[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FND_API.g_attr_tbl.DELETE;
FND_API.g_attr_tbl(I).name := 'program_update_date';
FND_API.g_attr_tbl(I).name := 'last_update_date';
FND_API.g_attr_tbl(I).name := 'last_updated_by';
FND_API.g_attr_tbl(I).name := 'last_update_login';
FND_API.g_attr_tbl(I).name := 'update_wip';
SELECT 'VALID'
INTO l_dummy
FROM mfg_lookups
WHERE lookup_type = 'ENG_ECN_APPROVAL_STATUS'
AND lookup_code = p_approval_status_type;
SELECT 'VALID'
INTO l_dummy
FROM eng_ecn_approval_lists
WHERE approval_list_id = p_approval_list_id;
SELECT 'VALID'
INTO l_dummy
FROM eng_change_order_types
WHERE change_order_type_id = p_change_order_type_id
AND NVL(disable_date, SYSDATE + 1) > SYSDATE;
SELECT 'VALID'
INTO l_dummy
FROM hr_organization_units hou
-- ,org_organization_definitions org_def
WHERE hou.organization_id = p_responsible_org_id
-- AND org_def.organization_id = p_current_org_id
-- AND org_def.business_group_id = hou.business_group_id
AND EXISTS
(select null
from hr_organization_information hoi
where hoi.organization_id = hou.organization_id
and hoi.org_information_context = 'CLASS'
and hoi.org_information1 = 'BOM_ECOD'
and hoi.org_information2 = 'Y');
SELECT 'VALID'
INTO l_dummy
FROM eng_change_statuses
WHERE status_code = p_status_type;
SELECT 'VALID'
INTO l_dummy
FROM pjm_unit_numbers
--WHERE end_item_id = p_revised_item_id
WHERE unit_number = p_from_end_item_unit_number;
SELECT 'VALID', disable_date
INTO l_dummy, x_disable_date
FROM eng_change_priorities
WHERE eng_change_priority_code = p_priority_code
AND organization_id = -1; --p_organization_id;
SELECT 'VALID', disable_date
INTO l_dummy, x_disable_date
FROM eng_change_reasons
WHERE eng_change_reason_code = p_reason_code
AND organization_id = -1; --p_organization_id;
SELECT 'VALID'
INTO l_dummy
FROM mfg_lookups
WHERE lookup_type = 'ECG_MATERIAL_DISPOSITION'
and lookup_code = p_disposition_type;
FUNCTION Update_Wip ( p_update_wip IN NUMBER
, x_err_text OUT NOCOPY VARCHAR2
)
RETURN BOOLEAN
IS
l_dummy VARCHAR2(10);
IF p_update_wip IS NULL OR
p_update_wip = FND_API.G_MISS_NUM
THEN
RETURN TRUE;
IF p_update_wip IN (1, 2)
THEN
RETURN TRUE;
END Update_Wip;
SELECT explosion_completion_date, data_completion_date,
plan_completion_date
INTO l_explosion_completion_date,
l_data_completion_date,
l_plan_completion_date
FROM mrp_plans
WHERE compile_designator = p_use_up_plan_name
AND organization_id = p_organization_id;
SELECT plan_completion_date
INTO l_plan_completion_date
FROM mrp_plan_organizations_v
WHERE compile_designator = p_use_up_plan_name
AND planned_organization = p_organization_id;
SELECT 'VALID'
INTO l_dummy
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_supply_subinventory
AND organization_id = p_organization_id;
SELECT 'VALID'
INTO l_dummy
FROM mfg_lookups
WHERE lookup_code = p_wip_supply_type
AND lookup_type = 'WIP_SUPPLY' ;
SELECT scheduled_date
FROM eng_revised_items
WHERE revised_item_sequence_id = p_revised_item_sequence_id;
SELECT lot_number
, from_wip_entity_id
, to_wip_entity_id
, from_cum_qty
, organization_id
FROM ENG_REVISED_ITEMS
WHERE (lot_number IS NOT NULL OR
from_wip_entity_id IS NOT NULL)
AND revised_item_sequence_id = p_revised_item_sequence_id ;
SELECT 'Cmp does not exist'
FROM SYS.DUAL
WHERE EXISTS (SELECT NULL
FROM WIP_DISCRETE_JOBS wdj
WHERE (wdj.status_type <> 1
OR
NOT EXISTS(SELECT NULL
FROM WIP_REQUIREMENT_OPERATIONS wro
WHERE wro.operation_seq_num = p_operation_seq_num
AND wro.inventory_item_id = p_rev_comp_item_id
AND wro.wip_entity_id = wdj.wip_entity_id)
)
AND wdj.lot_number = p_lot_number
AND wdj.organization_id = p_organization_id
) ;
SELECT 'Cmp does not exist'
FROM SYS.DUAL
WHERE EXISTS (SELECT NULL
FROM WIP_DISCRETE_JOBS wdj
, WIP_ENTITIES we
, WIP_ENTITIES we1
, WIP_ENTITIES we2
WHERE (wdj.status_type <> 1
OR
NOT EXISTS (SELECT NULL
FROM WIP_REQUIREMENT_OPERATIONS wro
WHERE wro.operation_seq_num = p_operation_seq_num
AND wro.inventory_item_id = p_rev_comp_item_id
AND wro.wip_entity_id = wdj.wip_entity_id)
)
AND wdj.wip_entity_id = we.wip_entity_id
AND we.wip_entity_name >= we1.wip_entity_name
AND we.wip_entity_name <= we2.wip_entity_name
AND we1.wip_entity_id = p_from_wip_entity_id
AND we2.wip_entity_id = p_to_wip_entity_id
) ;
SELECT 'Cmp does not exist'
FROM SYS.DUAL
WHERE EXISTS (SELECT NULL
FROM WIP_DISCRETE_JOBS wdj
WHERE (wdj.status_type <> 1
OR
NOT EXISTS(SELECT NULL
FROM WIP_REQUIREMENT_OPERATIONS wro
WHERE wro.operation_seq_num = p_operation_seq_num
AND wro.inventory_item_id = p_rev_comp_item_id
AND wro.wip_entity_id = wdj.wip_entity_id)
)
AND wdj.wip_entity_id = p_from_wip_entity_id
) ;
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
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;
* 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) ;
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
);
l_err_text := G_PKG_NAME || ' : (Entity Delete Validation) ' ||
substrb(SQLERRM,1,200);
END Check_Entity_Delete;