The following lines contain the word 'select', 'insert', 'update' or 'delete':
( SELECT WSR1.CHILD_OBJECT_ID CHILD_OBJECT_ID,
WSR1.CHILD_OBJECT_TYPE_ID CHILD_OBJECT_TYPE_ID,
WSR1.PARENT_OBJECT_ID PARENT_OBJECT_ID,
WDJ.ORGANIZATION_ID,
WDJ.SCHEDULED_START_DATE,
WDJ.SCHEDULED_COMPLETION_DATE,
WDJ.requested_start_date,
WDJ.due_date,
WSR1.WO_LEVEL ,
WDJ.FIRM_PLANNED_FLAG
FROM (SELECT WSR.CHILD_OBJECT_ID,
WSR.CHILD_OBJECT_TYPE_ID,
WSR.PARENT_OBJECT_ID,
LEVEL WO_LEVEL
FROM WIP_SCHED_RELATIONSHIPS WSR
WHERE WSR.RELATIONSHIP_TYPE = 1
CONNECT BY prior WSR.CHILD_OBJECT_ID = WSR.PARENT_OBJECT_ID
START WITH WSR.PARENT_OBJECT_ID = l_obj_id) WSR1,
WIP_DISCRETE_JOBS WDJ
WHERE l_obj_type_id = 1
AND WSR1.CHILD_OBJECT_ID = WDJ.WIP_ENTITY_ID
AND ( WDJ.firm_planned_flag = l_cur_non_firm_flag OR WDJ.firm_planned_flag = l_cur_firm_flag)
AND WDJ.status_type NOT IN (7,4,5,12,14)
)
UNION
( SELECT WDJ.WIP_ENTITY_ID CHILD_OBJECT_ID,
l_obj_type_id "CHILD_OBJECT_TYPE_ID",
WDJ.PARENT_WIP_ENTITY_ID PARENT_OBJECT_ID,
WDJ.ORGANIZATION_ID,
WDJ.SCHEDULED_START_DATE,
WDJ.SCHEDULED_COMPLETION_DATE,
WDJ.requested_start_date,
WDJ.due_date,
0 WO_LEVEL,
WDJ.FIRM_PLANNED_FLAG
FROM WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = l_obj_id
AND l_obj_type_id = 1
AND WDJ.status_type NOT IN (7,4,5,12,14)
) ORDER BY 8 DESC ;
SELECT WO.FIRST_UNIT_START_DATE START_DATE,
WO.FIRST_UNIT_COMPLETION_DATE COMPLETION_DATE,
WO.OPERATION_SEQ_NUM,WO.DESCRIPTION,WO.LONG_DESCRIPTION,
WO.SHUTDOWN_TYPE, WO.ATTRIBUTE_CATEGORY, WO.ATTRIBUTE1, -- added columns for Bug 12320466
WO.ATTRIBUTE2, WO.ATTRIBUTE3, WO.ATTRIBUTE4, WO.ATTRIBUTE5,
WO.ATTRIBUTE6, WO.ATTRIBUTE7, WO.ATTRIBUTE8, WO.ATTRIBUTE9,
WO.ATTRIBUTE10, WO.ATTRIBUTE11, WO.ATTRIBUTE12, WO.ATTRIBUTE13,
WO.ATTRIBUTE14, WO.ATTRIBUTE15
FROM WIP_OPERATIONS WO
WHERE WO.WIP_ENTITY_ID = l_obj_id
AND l_obj_type_id = 1;
select WRO.INVENTORY_ITEM_ID,OPERATION_SEQ_NUM,DATE_REQUIRED,STOCK_ENABLED_FLAG
FROM WIP_REQUIREMENT_OPERATIONS WRO,
MTL_SYSTEM_ITEMS_B MSI
WHERE WRO.WIP_ENTITY_ID = l_obj_id
AND MSI.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND l_obj_type_id = 1;
select WOR.START_DATE, WOR.COMPLETION_DATE,
WOR.OPERATION_SEQ_NUM,
WOR.RESOURCE_SEQ_NUM
FROM WIP_OPERATION_RESOURCES WOR
WHERE WOR.WIP_ENTITY_ID = l_obj_id
AND l_obj_type_id = 1;
select WORI.START_DATE, WORI.COMPLETION_DATE,
wORI.OPERATION_SEQ_NUM,
WORI.RESOURCE_SEQ_NUM,
WORI.INSTANCE_ID,
WORI.SERIAL_NUMBER
FROM WIP_OP_RESOURCE_INSTANCES WORI
WHERE WORI.WIP_ENTITY_ID = l_obj_id
AND l_obj_type_id = 1;
SELECT woru.start_date,
woru.completion_date,
woru.operation_seq_num,
woru.resource_seq_num,
woru.instance_id,
woru.serial_number,
woru.assigned_units
FROM wip_operation_resource_usage woru, wip_discrete_jobs wdj
WHERE woru.wip_entity_id = l_obj_id
AND wdj.wip_entity_id = l_obj_id
-- AND wdj.firm_planned_flag = l_cur_firm_flag
AND l_obj_type_id = 1;
select scheduled_start_date, scheduled_completion_date
into l_wo_start_date, l_wo_completion_date
from wip_discrete_jobs where
wip_entity_id = p_work_object_id;
l_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
l_eam_op_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
l_eam_res_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
l_eam_res_inst_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
l_eam_res_usage_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
SELECT WDJ.STATUS_TYPE,
WDJ.ORGANIZATION_ID,
WDJ.SCHEDULED_START_DATE,
WDJ.SCHEDULED_COMPLETION_DATE
INTO l_job_status,
l_organization_id,
l_scheduled_start_date,
l_scheduled_completion_date
FROM WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = l_work_object_id
AND l_work_object_type_id = 1;
/* Update the job start and Enc Dates */
UPDATE WIP_DISCRETE_JOBS WDJ
SET WDJ.SCHEDULED_START_DATE = WDJ.SCHEDULED_START_DATE + l_offset_days,
WDJ.SCHEDULED_COMPLETION_DATE = WDJ.SCHEDULED_COMPLETION_DATE + l_offset_days
WHERE WDJ.WIP_ENTITY_ID = l_work_object_id
AND l_work_object_type_id = 1;
UPDATE WIP_OPERATIONS WO
SET WO.FIRST_UNIT_START_DATE = WO.FIRST_UNIT_START_DATE + l_offset_days,
WO.FIRST_UNIT_COMPLETION_DATE = WO.FIRST_UNIT_COMPLETION_DATE + l_offset_days,
WO.LAST_UNIT_START_DATE = WO.LAST_UNIT_START_DATE + l_offset_days,
WO.LAST_UNIT_COMPLETION_DATE = WO.LAST_UNIT_COMPLETION_DATE + l_offset_days
WHERE WO.WIP_ENTITY_ID = l_work_object_id
AND l_work_object_type_id = 1;
UPDATE WIP_OPERATION_RESOURCES WOR
SET WOR.START_DATE = WOR.START_DATE + l_offset_days,
WOR.COMPLETION_DATE = WOR.COMPLETION_DATE + l_offset_days
WHERE WOR.WIP_ENTITY_ID = l_work_object_id
AND l_work_object_type_id = 1;
UPDATE WIP_OPERATION_RESOURCE_USAGE WORU
SET WORU.START_DATE = WORU.START_DATE + l_offset_days,
WORU.COMPLETION_DATE = WORU.COMPLETION_DATE + l_offset_days
WHERE WORU.WIP_ENTITY_ID = l_work_object_id
AND l_work_object_type_id = 1;
UPDATE WIP_OP_RESOURCE_INSTANCES WORI
SET WORI.START_DATE = WORI.START_DATE + l_offset_days,
WORI.COMPLETION_DATE = WORI.COMPLETION_DATE + l_offset_days
WHERE WORI.WIP_ENTITY_ID = l_work_object_id
AND l_work_object_type_id = 1;
/* Test portion -- delete later */
BEGIN
SELECT WDJ.STATUS_TYPE,
WDJ.ORGANIZATION_ID,
WDJ.SCHEDULED_START_DATE,
WDJ.SCHEDULED_COMPLETION_DATE
INTO l_job_status,
l_organization_id,
l_scheduled_start_date,
l_scheduled_completion_date
FROM WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = l_work_object_id
AND l_work_object_type_id = 1;