[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* transaction type is Update and the record
* does not exist then the return status would be an
* error as well. Mesg_Token_Table will carry the
* error messsage and the tokens associated with the
* message.
*********************************************************************/
PROCEDURE Check_Existence
( p_eam_sub_res_rec IN EAM_PROCESS_WO_PUB.eam_sub_res_rec_type
, x_old_eam_sub_res_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_sub_res_rec_type
, x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
(EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
THEN
l_token_tbl(1).token_name := 'RESOURCE_SEQ_NUM';
x_old_eam_sub_res_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
if (p_eam_sub_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
select 1
into g_dummy
from wip_operations wo
where wo.organization_id = p_eam_sub_res_rec.organization_id
and wo.wip_entity_id = p_eam_sub_res_rec.wip_entity_id
and wo.operation_seq_num = p_eam_sub_res_rec.operation_seq_num;
if (p_eam_sub_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
if p_eam_sub_res_rec.start_date > p_eam_sub_res_rec.completion_date then
raise fnd_api.g_exc_unexpected_error;
if (p_eam_sub_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
if (p_eam_sub_res_rec.assigned_units < 0 ) then
raise fnd_api.g_exc_unexpected_error;
if (p_eam_sub_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
select 1
into g_dummy
from bom_resources
where organization_id = p_eam_sub_res_rec.organization_id
and resource_id = p_eam_sub_res_rec.resource_id;
if (p_eam_sub_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
select 1
into g_dummy
from mtl_units_of_measure
where uom_code = p_eam_sub_res_rec.uom_code;
if (p_eam_sub_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
select 1
into g_dummy
from mfg_lookups
where lookup_type = 'CST_BASIS'
and lookup_code in (1,2)
and lookup_code = p_eam_sub_res_rec.basis_type;
if (p_eam_sub_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
IF (p_eam_sub_res_rec.activity_id is not NULL)
THEN
select 1
into g_dummy
from cst_activities
where (organization_id = p_eam_sub_res_rec.organization_id or organization_id is null)
and nvl(disable_date, sysdate + 2) > sysdate
and activity_id = p_eam_sub_res_rec.activity_id;
if (p_eam_sub_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
select 1
into g_dummy
from mfg_lookups
where lookup_type = g_autocharge_type
and lookup_code in (2,3)
and lookup_code = p_eam_sub_res_rec.autocharge_type;
if (p_eam_sub_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
select 1
into g_dummy
from mfg_lookups
where lookup_type = 'BOM_RESOURCE_SCHEDULE_TYPE'
and lookup_code = p_eam_sub_res_rec.scheduled_flag;
if (p_eam_sub_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
IF (p_eam_sub_res_rec.standard_rate_flag is not NULL) and (p_eam_sub_res_rec.standard_rate_flag not in (1, 2))
THEN
raise fnd_api.g_exc_unexpected_error;
select 1 into g_dummy from
wip_operations wo
where wo.wip_entity_id = p_eam_sub_res_rec.wip_entity_id
and wo.organization_id = p_eam_sub_res_rec.organization_id
and wo.operation_seq_num = p_eam_sub_res_rec.operation_seq_num
and wo.department_id = p_eam_sub_res_rec.department_id;
select 1 into g_dummy from
bom_departments bd where
bd.organization_id = p_eam_sub_res_rec.organization_id
and bd.department_id = p_eam_sub_res_rec.department_id;
IF p_eam_sub_res_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE THEN
select 1 into g_dummy from
wip_sub_operation_resources wsor where
wsor.wip_entity_id = p_eam_sub_res_rec.wip_entity_id
and wsor.organization_id = p_eam_sub_res_rec.organization_id
and wsor.resource_seq_num = p_eam_sub_res_rec.resource_seq_num
and wsor.operation_seq_num = p_eam_sub_res_rec.operation_seq_num
and wsor.department_id = p_eam_sub_res_rec.department_id;
if (p_eam_sub_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
l_hour_uom := FND_PROFILE.value('BOM:HOUR_UOM_CODE');
select UOM.uom_class
into l_uom_class
from BOM_RESOURCES BR, MTL_UNITS_OF_MEASURE_VL UOM
where BR.resource_id = p_eam_sub_res_rec.resource_id
and BR.unit_of_measure = UOM.uom_code;
if (p_eam_sub_res_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE) then
select count(*)
into l_count_wt
from DUAL
WHERE EXISTS (SELECT 1
FROM wip_transactions
where wip_entity_id = p_eam_sub_res_rec.wip_entity_id
and organization_id = p_eam_sub_res_rec.organization_id
and operation_seq_num = p_eam_sub_res_rec.operation_seq_num
and resource_seq_num = p_eam_sub_res_rec.resource_seq_num
and resource_id = p_eam_sub_res_rec.resource_id);
select count(*)
into l_count_wcti
from DUAL
WHERE EXISTS (SELECT 1
FROM wip_cost_txn_interface
where wip_entity_id = p_eam_sub_res_rec.wip_entity_id
and organization_id = p_eam_sub_res_rec.organization_id
and operation_seq_num = p_eam_sub_res_rec.operation_seq_num
and resource_seq_num = p_eam_sub_res_rec.resource_seq_num
and resource_id = p_eam_sub_res_rec.resource_id);
select applied_resource_units
into l_applied_res_units
from wip_operation_resources
where wip_entity_id = p_eam_sub_res_rec.wip_entity_id
and organization_id = p_eam_sub_res_rec.organization_id
and operation_seq_num = p_eam_sub_res_rec.operation_seq_num
and resource_seq_num = p_eam_sub_res_rec.resource_seq_num
and resource_id = p_eam_sub_res_rec.resource_id;
( p_message_name => 'EAM_SR_DELETE_INVALID'
, p_token_tbl => l_token_tbl
, p_mesg_token_tbl => l_mesg_token_tbl
, x_mesg_token_tbl => l_out_mesg_token_tbl
);
if (p_eam_sub_res_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE) then
select count(*)
into l_count_po
from DUAL
WHERE EXISTS (SELECT 1
FROM po_requisitions_interface
where wip_entity_id = p_eam_sub_res_rec.wip_entity_id
and destination_organization_id = p_eam_sub_res_rec.organization_id
and wip_operation_seq_num = p_eam_sub_res_rec.operation_seq_num
and wip_resource_seq_num = p_eam_sub_res_rec.resource_seq_num
and bom_resource_id = p_eam_sub_res_rec.resource_id);
select count(*)
into l_count_req
from DUAL
WHERE EXISTS (SELECT 1
FROM po_requisition_lines
where wip_entity_id = p_eam_sub_res_rec.wip_entity_id
and destination_organization_id = p_eam_sub_res_rec.organization_id
and wip_operation_seq_num = p_eam_sub_res_rec.operation_seq_num
and wip_resource_seq_num = p_eam_sub_res_rec.resource_seq_num
and bom_resource_id = p_eam_sub_res_rec.resource_id);
select count(*)
into l_count_dist
from DUAL
WHERE EXISTS (SELECT 1
FROM po_distributions
where wip_entity_id = p_eam_sub_res_rec.wip_entity_id
and destination_organization_id = p_eam_sub_res_rec.organization_id
and wip_operation_seq_num = p_eam_sub_res_rec.operation_seq_num
and wip_resource_seq_num = p_eam_sub_res_rec.resource_seq_num
and bom_resource_id = p_eam_sub_res_rec.resource_id);
( p_message_name => 'EAM_SR_DELETE_PO_INVALID'
, p_token_tbl => l_token_tbl
, p_mesg_token_tbl => l_mesg_token_tbl
, x_mesg_token_tbl => l_out_mesg_token_tbl
);