[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_res_inst_rec IN EAM_PROCESS_WO_PUB.eam_res_inst_rec_type
, x_old_eam_res_inst_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_inst_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;
select full_name
into l_token_tbl(1).token_value
from per_all_people_f papf,bom_resource_employees bre
where bre.instance_id = p_eam_res_inst_rec.instance_id
and papf.person_id = bre.person_id
and( trunc(sysdate) between papf.effective_start_date
and papf.effective_end_date);
(EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
THEN
l_token_tbl(1).token_name := 'INSTANCE_NAME';
select full_name
into l_token_tbl(1).token_value
from per_all_people_f papf,bom_resource_employees bre
where bre.instance_id = p_eam_res_inst_rec.instance_id
and papf.person_id = bre.person_id
and( trunc(sysdate) between papf.effective_start_date
and papf.effective_end_date);
x_old_eam_res_inst_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
if (p_eam_res_inst_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_res_inst_rec.organization_id
and wo.wip_entity_id = p_eam_res_inst_rec.wip_entity_id
and wo.operation_seq_num = p_eam_res_inst_rec.operation_seq_num;
if (p_eam_res_inst_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_operation_resources wor
where wor.organization_id = p_eam_res_inst_rec.organization_id
and wor.wip_entity_id = p_eam_res_inst_rec.wip_entity_id
and wor.operation_seq_num = p_eam_res_inst_rec.operation_seq_num
and wor.resource_seq_num = p_eam_res_inst_rec.resource_seq_num;
if (p_eam_res_inst_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_dept_res_instances bdri,
bom_department_resources bdr,
wip_operation_resources wor,
wip_operations wo where
wor.wip_entity_id = p_eam_res_inst_rec.wip_entity_id
and wor.organization_id = p_eam_res_inst_rec.organization_id
and wor.operation_seq_num = p_eam_res_inst_rec.operation_seq_num
and wor.resource_seq_num = p_eam_res_inst_rec.resource_seq_num
and (bdri.department_id = wo.department_id
or bdri.department_id = bdr.share_from_dept_id)
and bdri.resource_id = wor.resource_id
and bdri.instance_id = p_eam_res_inst_rec.instance_id
and bdr.department_id = wo.department_id
and bdr.resource_id = wor.resource_id
and wo.wip_entity_id = wor.wip_entity_id
and wo.organization_id = wor.organization_id
and wo.operation_seq_num = wor.operation_seq_num;
select full_name
into l_token_tbl(1).token_value
from per_all_people_f papf,bom_resource_employees bre
where bre.instance_id = p_eam_res_inst_rec.instance_id
and papf.person_id = bre.person_id
and( trunc(sysdate) between papf.effective_start_date
and papf.effective_end_date);
if (p_eam_res_inst_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
if (p_eam_res_inst_rec.serial_number is not NULL) then
select 1
into g_dummy
from mtl_serial_numbers msn, bom_resource_equipments bre
where msn.inventory_item_id = bre.inventory_item_id
and msn.current_organization_id = bre.organization_id
and bre.instance_id = p_eam_res_inst_rec.instance_id
and msn.serial_number = p_eam_res_inst_rec.serial_number;
if (p_eam_res_inst_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
if p_eam_res_inst_rec.start_date > p_eam_res_inst_rec.completion_date then
raise fnd_api.g_exc_unexpected_error;
IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating delete instance . . . '); END IF;
CURSOR res_inst IS select RESOURCE_id
from wip_operation_resources
where wip_entity_id = p_eam_res_inst_rec.wip_entity_id
and operation_seq_num = p_eam_res_inst_rec.operation_seq_num
and resource_seq_num = p_eam_res_inst_rec.resource_seq_num;
if (p_eam_res_inst_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE) then
open res_inst;
select count(*)
into l_count_wt
from DUAL
WHERE EXISTS (SELECT 1
FROM wip_transactions
where wip_entity_id = p_eam_res_inst_rec.wip_entity_id
and organization_id = p_eam_res_inst_rec.organization_id
and operation_seq_num = p_eam_res_inst_rec.operation_seq_num
and resource_seq_num = p_eam_res_inst_rec.resource_seq_num
and resource_id = l_resource_id
and instance_id = p_eam_res_inst_rec.instance_id)
AND rownum <=1 ;
select count(*)
into l_count_wcti
from DUAL
WHERE EXISTS (SELECT 1
FROM wip_cost_txn_interface
where wip_entity_id = p_eam_res_inst_rec.wip_entity_id
and organization_id = p_eam_res_inst_rec.organization_id
and operation_seq_num = p_eam_res_inst_rec.operation_seq_num
and resource_seq_num = p_eam_res_inst_rec.resource_seq_num
and resource_id = l_resource_id
and instance_id = p_eam_res_inst_rec.instance_id)
AND rownum <=1 ;
( p_message_name => 'EAM_RES_INST_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
);