[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_op_rec IN EAM_PROCESS_WO_PUB.eam_op_rec_type
, x_old_eam_op_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_op_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 := 'OPERATION_SEQ_NUM';
x_old_eam_op_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
* Procedure : Is_Dept_Updateable
* Parameters IN : Wip_Entity_Id and Operation_Seq_Num
* Parameters OUT NOCOPY: Return Status
* Mesg Token Table
* Purpose : Is_Dept_Updateable will check if the operation's
* department can be updateable or not
**********************************************************************/
FUNCTION Is_Dept_Updateable
( p_wip_entity_id NUMBER,
p_organization_id NUMBER,
p_operation_seq_num NUMBER
) RETURN BOOLEAN
IS
l_inv_count NUMBER;
select count(*)
into l_inv_count
from dual
where exists (select 1
from wip_requirement_operations wro
where wro.wip_entity_id = p_wip_entity_id
and wro.organization_id = p_organization_id
and wro.operation_seq_num = p_operation_seq_num
and ( quantity_issued > 0 or
EAM_MATERIAL_ALLOCQTY_PKG.allocated_quantity(
wro.wip_entity_id,
wro.organization_id,
wro.operation_seq_num,
wro.inventory_item_id ) >0 )
);
select count(*)
into l_res_count
from dual
where exists (select 1
from wip_operation_resources
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and operation_seq_num = p_operation_seq_num);
/* select count(*)
into l_po_count
from DUAL
WHERE EXISTS (SELECT 1
FROM po_requisitions_interface
where wip_entity_id = p_wip_entity_id
and destination_organization_id = p_organization_id
and wip_operation_seq_num = p_operation_seq_num);
select count(*)
into l_req_count
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_wip_entity_id
and prl.destination_organization_id = p_organization_id
and prl.wip_operation_seq_num = p_operation_seq_num
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_dist_count
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_wip_entity_id
and pds.destination_organization_id = p_organization_id
and pds.wip_operation_seq_num = p_operation_seq_num
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));
END Is_Dept_Updateable;
OP_DEPT_NOT_UPDATEABLE EXCEPTION;
if (p_eam_op_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_departments
where department_id = p_eam_op_rec.department_id
and organization_id = p_eam_op_rec.organization_id;
if (p_eam_op_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE and
(p_eam_op_rec.department_id <> p_old_eam_op_rec.department_id)
and ((Is_Dept_Updateable(p_wip_entity_id => p_eam_op_rec.wip_entity_id
, p_organization_id => p_eam_op_rec.organization_id
, p_operation_seq_num => p_eam_op_rec.operation_seq_num ))
in (FALSE) and is_op_dept_change_allowed(p_eam_op_rec.wip_entity_id,p_eam_op_rec.operation_seq_num)='N')
) THEN
raise OP_DEPT_NOT_UPDATEABLE;
end if; --end of check for update and is_dept_updateable
end if; --end of check for create/update transaction
WHEN OP_DEPT_NOT_UPDATEABLE THEN
l_token_tbl(1).token_name := 'DEPT_NAME';
SELECT bd.department_code into l_token_tbl(1).token_value
FROM bom_departments bd
WHERE bd.DEPARTMENT_ID = p_eam_op_rec.department_id
AND bd.organization_id = p_eam_op_rec.organization_id;
( p_message_name => 'EAM_OP_DEPT_UPDATE'
, p_token_tbl => l_token_tbl
, p_mesg_token_tbl => l_mesg_token_tbl
, x_mesg_token_tbl => l_out_mesg_token_tbl
);
SELECT bd.department_code into l_token_tbl(1).token_value
FROM bom_departments bd
WHERE bd.DEPARTMENT_ID = p_eam_op_rec.department_id
AND bd.organization_id = p_eam_op_rec.organization_id;
if (p_eam_op_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
if (p_eam_op_rec.standard_operation_id is not null) then
select 1
into g_dummy
from bom_standard_operations
where standard_operation_id = p_eam_op_rec.standard_operation_id
and organization_id = p_eam_op_rec.organization_id;
if (p_eam_op_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
if p_eam_op_rec.minimum_transfer_quantity < 0 then
raise fnd_api.g_exc_unexpected_error;
if (p_eam_op_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_COUNT_POINT_TYPE'
and lookup_code = p_eam_op_rec.count_point_type;
if (p_eam_op_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 = 'SYS_YES_NO'
and lookup_code = p_eam_op_rec.backflush_flag;
if (p_eam_op_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
if p_eam_op_rec.shutdown_type is not null then
select 1
into g_dummy
from mfg_lookups
where lookup_type = g_shutdown_type
and lookup_code = p_eam_op_rec.shutdown_type
and enabled_flag = 'Y';
if (p_eam_op_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
if p_eam_op_rec.start_date > p_eam_op_rec.completion_date then
raise fnd_api.g_exc_unexpected_error;
if (p_eam_op_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE) then
select count(*)
into l_count_eoct
from eam_op_completion_txns
where wip_entity_id = p_eam_op_rec.wip_entity_id
and organization_id = p_eam_op_rec.organization_id
and operation_seq_num = p_eam_op_rec.operation_seq_num;
select count(*)
into l_count_res
from wip_operation_resources
where wip_entity_id = p_eam_op_rec.wip_entity_id
and organization_id = p_eam_op_rec.organization_id
and operation_seq_num = p_eam_op_rec.operation_seq_num;
select count(*)
into l_count_on
from wip_operation_networks
where wip_entity_id = p_eam_op_rec.wip_entity_id
and organization_id = p_eam_op_rec.organization_id
and ( prior_operation = p_eam_op_rec.operation_seq_num
or next_operation = p_eam_op_rec.operation_seq_num);
select count(*)
into l_count_mr
from wip_requirement_operations
where wip_entity_id = p_eam_op_rec.wip_entity_id
and organization_id = p_eam_op_rec.organization_id
and operation_seq_num = p_eam_op_rec.operation_seq_num;
( p_message_name => 'EAM_OP_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
);
l_token_tbl.delete;
SELECT
won.prior_operation,won.next_operation,wo.last_unit_completion_date,wo1.first_unit_start_date
FROM wip_operation_networks won,wip_operations wo,wip_operations wo1
WHERE won.wip_entity_id = wo.wip_entity_id AND
won.wip_entity_id = wo1.wip_entity_id AND
won.prior_operation = wo.operation_seq_num AND
won.next_operation = wo1.operation_seq_num AND
won.wip_entity_id = l_wip_entity_id;
SELECT nvl(operation_completed, 'N')
INTO l_op_completed
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_op_seq_num;
SELECT nvl(sum(quantity_issued), 0)
INTO l_q_issued
FROM wip_requirement_operations
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_op_seq_num;
SELECT nvl(sum(quantity_received), 0),
nvl(sum(amount_delivered), 0)
INTO l_q_received,
l_amount_delivered
FROM eam_wo_direct_items_lite_v
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_op_seq_num;
select count(*) into l_tx_count from dual
where EXISTS (SELECT transaction_id FROM wip_cost_txn_interface
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_op_seq_num);
SELECT user_defined_status_id
INTO l_status
FROM eam_work_order_details
WHERE wip_entity_id = x_wip_entity_id;
select count(*) into l_rowcount from BOM_DEPARTMENT_RESOURCES_V where DEPARTMENT_ID=p_dept_id and RESOURCE_CODE=p_res_code;
select count(*) into l_rowcount from dual
where EXISTS (select ROW_ID from BOM_DEPT_RES_INSTANCES_EMP_V
where DEPARTMENT_ID=p_dept_id and INSTANCE_ID=p_inst_id and RESOURCE_ID=p_res_id);