[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wip_entity_name
FROM wip_entities
WHERE wip_entity_id = p_wip_entity_id;
* or not and then update that field at the work order
* level. API will return whether shortage
* exists in p_shortage_exists parameter.
*********************************************************************/
PROCEDURE Material_Shortage_CP
( errbuf OUT NOCOPY VARCHAR2
, retcode OUT NOCOPY VARCHAR2
, p_owning_department IN VARCHAR2
, p_assigned_department IN NUMBER
, p_asset_number IN VARCHAR2
, p_scheduled_start_date_from IN VARCHAR2
, p_scheduled_start_date_to IN VARCHAR2
, p_work_order_from IN VARCHAR2
, p_work_order_to IN VARCHAR2
, p_status_type IN NUMBER
, p_horizon IN NUMBER
, p_backlog_horizon IN NUMBER
, p_organization_id IN NUMBER
, p_project IN VARCHAR2
, p_task IN VARCHAR2
)
IS
TYPE WipIdCurType IS REF CURSOR;
l_sql_stmt := ' SELECT wdj.wip_entity_id
FROM wip_discrete_jobs wdj, eam_work_order_details ewod, csi_item_instances cii
WHERE wdj.wip_entity_id = ewod.wip_entity_id
AND wdj.maintenance_object_id = cii.instance_id
AND wdj.maintenance_object_type = 3
AND wdj.organization_id = :p_org_id ';
l_where_clause := l_where_clause || ' AND EXISTS (SELECT 1
FROM wip_operations wo
WHERE wo.wip_entity_id = wdj.wip_entity_id
AND wo.department_id = ' || p_assigned_department || ' ) ';