[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_rec IN EAM_PROCESS_WO_PUB.eam_res_rec_type
, x_old_eam_res_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_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_res_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
if (p_eam_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_res_rec.organization_id
and wo.wip_entity_id = p_eam_res_rec.wip_entity_id
and wo.operation_seq_num = p_eam_res_rec.operation_seq_num;
if (p_eam_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
if p_eam_res_rec.start_date > p_eam_res_rec.completion_date then
raise fnd_api.g_exc_unexpected_error;
if (p_eam_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
if (p_eam_res_rec.assigned_units is null or p_eam_res_rec.assigned_units < 0 ) then
raise fnd_api.g_exc_unexpected_error;
select 1
into g_dummy
from wip_operation_resources wor
where wor.organization_id = p_eam_res_rec.organization_id
and wor.wip_entity_id = p_eam_res_rec.wip_entity_id
and wor.operation_seq_num = p_eam_res_rec.operation_seq_num
and wor.resource_id = p_eam_res_rec.resource_id;
if (p_eam_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_res_rec.organization_id
and resource_id = p_eam_res_rec.resource_id;
select
1 into g_dummy
from
wip_operations wo,
bom_departments bd,
bom_department_resources bdr
where
wo.organization_id = p_eam_res_rec.organization_id
and wo.wip_entity_id = p_eam_res_rec.wip_entity_id
and wo.operation_seq_num = p_eam_res_rec.operation_seq_num
and bd.department_id = wo.department_id
and bdr.department_id = bd.department_id
and bdr.resource_id = p_eam_res_rec.resource_id;
if (p_eam_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_res_rec.uom_code;
select 1 into g_dummy from bom_resources
where organization_id = p_eam_res_rec.organization_id
and resource_id = p_eam_res_rec.resource_id
and unit_of_measure = p_eam_res_rec.uom_code;
if (p_eam_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_res_rec.basis_type;
if (p_eam_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
IF (p_eam_res_rec.activity_id is not NULL)
THEN
select 1
into g_dummy
from cst_activities
where (organization_id = p_eam_res_rec.organization_id or organization_id is null)
and nvl(disable_date, sysdate + 2) > sysdate
and activity_id = p_eam_res_rec.activity_id;
if (p_eam_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_res_rec.autocharge_type;
if (p_eam_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
if p_eam_res_rec.scheduled_flag not in (1,2) then
raise fnd_api.g_exc_error;
if (p_eam_res_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
IF (p_eam_res_rec.standard_rate_flag is not NULL) and (p_eam_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_res_rec.wip_entity_id
and wo.organization_id = p_eam_res_rec.organization_id
and wo.operation_seq_num = p_eam_res_rec.operation_seq_num
and wo.department_id = p_eam_res_rec.department_id;
select 1 into g_dummy from
bom_departments bd where
bd.organization_id = p_eam_res_rec.organization_id
and bd.department_id = p_eam_res_rec.department_id;
IF p_eam_res_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE THEN
SELECT 1 into g_dummy
FROM wip_operation_resources wor, wip_operations wo
WHERE wor.wip_entity_id = p_eam_res_rec.wip_entity_id
AND wo.wip_entity_id = p_eam_res_rec.wip_entity_id
AND wor.organization_id = p_eam_res_rec.organization_id
AND wo.organization_id = p_eam_res_rec.organization_id
AND wor.resource_seq_num = p_eam_res_rec.resource_seq_num
AND wor.operation_seq_num = p_eam_res_rec.operation_seq_num
AND wo.operation_seq_num = p_eam_res_rec.operation_seq_num
AND wo.department_id = p_eam_res_rec.department_id;
if (p_eam_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_res_rec.resource_id
and BR.unit_of_measure = UOM.uom_code;
select calendar_code into l_calendar_code
from mtl_parameters
where organization_id = p_eam_res_rec.organization_id;
select available_24_hours_flag into l_24_hr_resource
from bom_department_resources bdr
where bdr.department_id = p_eam_res_rec.department_id
and bdr.resource_id = p_eam_res_rec.resource_id ;
select count(rsh.shift_num) into l_count_shift_num
from bom_shift_times shf,
bom_resource_shifts rsh,
bom_department_resources bdr
where bdr.department_id = p_eam_res_rec.department_id
and bdr.resource_id = p_eam_res_rec.resource_id
and nvl(bdr.share_from_dept_id, bdr.department_id) = rsh.department_id
and bdr.resource_id = rsh.resource_id
and rsh.shift_num = shf.shift_num
and shf.calendar_code = l_calendar_code;
if (p_eam_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_res_rec.wip_entity_id
and organization_id = p_eam_res_rec.organization_id
and operation_seq_num = p_eam_res_rec.operation_seq_num
and resource_seq_num = p_eam_res_rec.resource_seq_num
and resource_id = p_eam_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_res_rec.wip_entity_id
and organization_id = p_eam_res_rec.organization_id
and operation_seq_num = p_eam_res_rec.operation_seq_num
and resource_seq_num = p_eam_res_rec.resource_seq_num
and resource_id = p_eam_res_rec.resource_id);
select nvl(applied_resource_units,0)
into l_applied_res_units
from wip_operation_resources
where wip_entity_id = p_eam_res_rec.wip_entity_id
and organization_id = p_eam_res_rec.organization_id
and operation_seq_num = p_eam_res_rec.operation_seq_num
and resource_seq_num = p_eam_res_rec.resource_seq_num
and resource_id = p_eam_res_rec.resource_id;
( p_message_name => 'EAM_RES_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_res_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE) then
select count(*)
into l_count_inst
from wip_op_resource_instances
where wip_entity_id = p_eam_res_rec.wip_entity_id
and organization_id = p_eam_res_rec.organization_id
and operation_seq_num = p_eam_res_rec.operation_seq_num
and resource_seq_num = p_eam_res_rec.resource_seq_num
and rownum <=1;
if (p_eam_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_res_rec.wip_entity_id
and destination_organization_id = p_eam_res_rec.organization_id
and wip_operation_seq_num = p_eam_res_rec.operation_seq_num
and wip_resource_seq_num = p_eam_res_rec.resource_seq_num
and bom_resource_id = p_eam_res_rec.resource_id);
select count(*)
into l_count_req
from DUAL
WHERE EXISTS (SELECT 1
FROM po_requisition_lines prl, po_requisition_headers prh
where prl.requisition_header_id = prh.requisition_header_id
and prl.wip_entity_id = p_eam_res_rec.wip_entity_id
and prl.destination_organization_id = p_eam_res_rec.organization_id
and prl.wip_operation_seq_num = p_eam_res_rec.operation_seq_num
and prl.wip_resource_seq_num = p_eam_res_rec.resource_seq_num
and prl.bom_resource_id = p_eam_res_rec.resource_id
and (prh.authorization_status <>'CANCELLED'
or prh.authorization_status is null)
and (prl.cancel_flag <>'Y' or prl.cancel_flag is null)
and (prl.closed_code not in ('FINALLY CLOSED')
or prl.closed_code is null)
and (prl.modified_by_agent_flag <> 'Y'
or prl.modified_by_agent_flag is null));
select count(*)
into l_count_dist
from DUAL
WHERE EXISTS (SELECT 1
FROM po_distributions pds,po_line_locations poll
where pds.line_location_id = poll.line_location_id
and pds.wip_entity_id = p_eam_res_rec.wip_entity_id
and pds.destination_organization_id = p_eam_res_rec.organization_id
and pds.wip_operation_seq_num = p_eam_res_rec.operation_seq_num
and pds.wip_resource_seq_num = p_eam_res_rec.resource_seq_num
and pds.bom_resource_id = p_eam_res_rec.resource_id
and (poll.cancel_flag <>'Y' or poll.cancel_flag is null)
and (poll.closed_code not in ('CANCELLED','FINALLY CLOSED')
or poll.closed_code is null));
( p_message_name => 'EAM_RES_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
);