[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SHIFT_NUM,
SHIFT_DATE,
SEQ_NUM,
CALENDAR_CODE
FROM BOM_SHIFT_DATES
WHERE CALENDAR_CODE = p_calendar_code
AND SHIFT_DATE >= trunc(p_curr_date)
AND SHIFT_DATE <= trunc(p_curr_date) + 50
AND EXCEPTION_SET_ID = -1
ORDER BY SHIFT_NUM, SHIFT_DATE;
SELECT SHIFT_NUM,
SHIFT_DATE,
SEQ_NUM,
CALENDAR_CODE
FROM BOM_SHIFT_DATES
WHERE CALENDAR_CODE = p_calendar_code
AND SHIFT_DATE >= trunc(p_curr_date) - 50
AND SHIFT_DATE <= trunc(p_curr_date)
AND EXCEPTION_SET_ID = -1
ORDER BY SHIFT_NUM DESC, SHIFT_DATE DESC;
shift_date_tbl.delete;
shift_date_tbl.delete;
l_res_sft_tbl.delete;
UPDATE WIP_OPERATION_RESOURCES
SET START_DATE = l_res_start_date,
COMPLETION_DATE = l_res_completion_date
WHERE WIP_ENTITY_ID = p_wip_entity_id
AND OPERATION_SEQ_NUM = p_op_seq_num
AND RESOURCE_SEQ_NUM = op_res_info_tbl(i).res_seq_num;
UPDATE WIP_OP_RESOURCE_INSTANCES
set start_date = l_res_start_date
, completion_date = l_res_completion_date
WHERE WIP_ENTITY_ID = p_wip_entity_id
AND OPERATION_SEQ_NUM = p_op_seq_num
AND RESOURCE_SEQ_NUM = op_res_info_tbl(i).res_seq_num;
SELECT WO.OPERATION_SEQ_NUM OP_SEQ_NUM,
WO.OPERATION_SEQUENCE_ID OP_SEQ_ID,
WO.FIRST_UNIT_START_DATE OP_START_DATE,
WO.LAST_UNIT_COMPLETION_DATE OP_COMPLETION_DATE,
WO.OPERATION_COMPLETED OP_COMPLETED,
WOR.RESOURCE_SEQ_NUM RES_SEQ_NUM,
NVL(WOR.SCHEDULE_SEQ_NUM, WOR.RESOURCE_SEQ_NUM) RES_SCH_NUM,
WOR.RESOURCE_ID RES_ID,
WOR.START_DATE RES_START_DATE,
WOR.COMPLETION_DATE RES_COMPLETION_DATE,
NVL(WOR.ASSIGNED_UNITS, 0) ASSIGNED_UNITS,
DR2.CAPACITY_UNITS CAPACITY_UNITS,
ROUND(WOR.USAGE_RATE_OR_AMOUNT * (1/p_hour_conv )* DECODE (CON.CONVERSION_RATE, '', 0, '0', 0, CON.CONVERSION_RATE) *
DECODE (WOR.BASIS_TYPE, 1, 1, 2, 1, 1) * 3600) USAGE_RATE,
DECODE(WOR.SCHEDULED_FLAG, 1, DECODE(WOR.USAGE_RATE_OR_AMOUNT, 0, 2, 1),
WOR.SCHEDULED_FLAG) SCHEDULED_FLAG,
DR2.AVAILABLE_24_HOURS_FLAG AVAIL_24_HRS_FLAG
FROM WIP_OPERATIONS WO,
BOM_DEPARTMENT_RESOURCES DR1,
BOM_DEPARTMENT_RESOURCES DR2,
WIP_OPERATION_RESOURCES WOR,
BOM_RESOURCES RES,
MTL_UOM_CONVERSIONS CON
WHERE
WO.WIP_ENTITY_ID = p_wip_entity_id
AND WO.ORGANIZATION_ID = p_organization_id
AND WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
AND WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
AND WO.DEPARTMENT_ID = DR1.DEPARTMENT_ID
AND WOR.RESOURCE_ID = DR1.RESOURCE_ID
AND NVL(DR1.SHARE_FROM_DEPT_ID, DR1.DEPARTMENT_ID) = DR2.DEPARTMENT_ID
AND WOR.RESOURCE_ID = DR2.RESOURCE_ID
AND WOR.RESOURCE_ID = RES.RESOURCE_ID
AND CON.UOM_CODE (+) = RES.UNIT_OF_MEASURE
AND CON.INVENTORY_ITEM_ID (+) = 0
ORDER BY WO.OPERATION_SEQ_NUM,
-- WOR.RESOURCE_SEQ_NUM;
SELECT -1 "LEVEL", operation_seq_num "OP_SEQ_NUM"
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num not in( SELECT prior_operation
FROM WIP_OPERATION_NETWORKS
WHERE wip_entity_id = p_wip_entity_id
UNION
SELECT next_operation
FROM WIP_OPERATION_NETWORKS
WHERE wip_entity_id = p_wip_entity_id)
UNION
SELECT 0 "LEVEL", prior_operation "OP_SEQ_NUM"
FROM wip_operation_networks
WHERE prior_operation NOT IN
(SELECT next_operation
FROM wip_operation_networks
WHERE wip_entity_id = p_wip_entity_id )
AND wip_entity_id=p_wip_entity_id
UNION
SELECT max(level) "LEVEL", next_operation "OP_SEQ_NUM"
FROM wip_operation_networks
WHERE wip_entity_id=p_wip_entity_id
START WITH prior_operation IN
(SELECT prior_operation
FROM wip_operation_networks
WHERE prior_operation NOT IN
(SELECT next_operation
FROM wip_operation_networks
WHERE wip_entity_id=p_wip_entity_id )
AND wip_entity_id=p_wip_entity_id)
CONNECT BY PRIOR next_operation = prior_operation
AND wip_entity_id = p_wip_entity_id
GROUP BY next_operation
ORDER BY 1;
SELECT -1 "LEVEL", operation_seq_num "OP_SEQ_NUM"
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num not in( SELECT prior_operation
FROM WIP_OPERATION_NETWORKS
WHERE wip_entity_id = p_wip_entity_id
UNION
SELECT next_operation
FROM WIP_OPERATION_NETWORKS
WHERE wip_entity_id = p_wip_entity_id)
UNION
SELECT 0 "LEVEL", next_operation "OP_SEQ_NUM"
FROM wip_operation_networks
WHERE next_operation NOT IN
(SELECT prior_operation
FROM wip_operation_networks
WHERE wip_entity_id = p_wip_entity_id )
AND wip_entity_id=p_wip_entity_id
UNION
SELECT max(level) "LEVEL", prior_operation "OP_SEQ_NUM"
FROM wip_operation_networks
WHERE wip_entity_id=p_wip_entity_id
START WITH next_operation IN
(SELECT next_operation
FROM wip_operation_networks
WHERE next_operation NOT IN
(SELECT prior_operation
FROM wip_operation_networks
WHERE wip_entity_id=p_wip_entity_id )
AND wip_entity_id=p_wip_entity_id)
CONNECT BY PRIOR prior_operation = next_operation
AND wip_entity_id = p_wip_entity_id
GROUP BY prior_operation
ORDER BY 1;
SELECT WO.OPERATION_SEQ_NUM OP_SEQ_NUM,
WOR.RESOURCE_SEQ_NUM RES_SEQ_NUM,
SHF.SHIFT_NUM SHIFT_NUM,
SHF.FROM_TIME FROM_TIME,
SHF.TO_TIME TO_TIME
FROM BOM_SHIFT_TIMES SHF,
BOM_RESOURCE_SHIFTS RSH,
BOM_DEPARTMENT_RESOURCES BDR,
WIP_OPERATION_RESOURCES WOR,
WIP_OPERATIONS WO
WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
AND WO.ORGANIZATION_ID = p_organization_id
AND WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
AND WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
AND WOR.SCHEDULED_FLAG IS NOT NULL
AND WO.DEPARTMENT_ID = BDR.DEPARTMENT_ID
AND WOR.RESOURCE_ID = BDR.RESOURCE_ID
AND NVL(BDR.SHARE_FROM_DEPT_ID, BDR.DEPARTMENT_ID) = RSH.DEPARTMENT_ID
AND RSH.RESOURCE_ID = WOR.RESOURCE_ID
AND RSH.SHIFT_NUM = SHF.SHIFT_NUM
AND SHF.CALENDAR_CODE = p_calendar_code
ORDER BY FROM_TIME, TO_TIME ;
SELECT WO.OPERATION_SEQ_NUM OP_SEQ_NUM,
WOR.RESOURCE_SEQ_NUM RES_SEQ_NUM,
SHF.SHIFT_NUM SHIFT_NUM,
SHF.FROM_TIME FROM_TIME,
SHF.TO_TIME TO_TIME
FROM BOM_SHIFT_TIMES SHF,
BOM_RESOURCE_SHIFTS RSH,
BOM_DEPARTMENT_RESOURCES BDR,
WIP_OPERATION_RESOURCES WOR,
WIP_OPERATIONS WO
WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
AND WO.ORGANIZATION_ID = p_organization_id
AND WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
AND WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
AND WOR.SCHEDULED_FLAG IS NOT NULL
AND WO.DEPARTMENT_ID = BDR.DEPARTMENT_ID
AND WOR.RESOURCE_ID = BDR.RESOURCE_ID
AND NVL(BDR.SHARE_FROM_DEPT_ID, BDR.DEPARTMENT_ID) = RSH.DEPARTMENT_ID
AND RSH.RESOURCE_ID = WOR.RESOURCE_ID
AND RSH.SHIFT_NUM = SHF.SHIFT_NUM
AND SHF.CALENDAR_CODE = p_calendar_code
ORDER BY TO_TIME DESC, FROM_TIME DESC;
op_res_info_tbl.delete;
op_res_sft_tbl.delete;
op_scd_seq_tbl.delete;
dep_op_seq_num_tbl.delete;
SELECT prior_operation
BULK COLLECT INTO dep_op_seq_num_tbl
FROM wip_operation_networks
WHERE wip_entity_id = p_wip_entity_id
AND next_operation = l_op_seq_num;
op_scd_seq_tbl.delete;
dep_op_seq_num_tbl.delete;
SELECT next_operation
BULK COLLECT INTO dep_op_seq_num_tbl
FROM wip_operation_networks
WHERE wip_entity_id = p_wip_entity_id
AND prior_operation = l_op_seq_num;
UPDATE WIP_OPERATIONS
SET FIRST_UNIT_START_DATE = l_op_start_date,
FIRST_UNIT_COMPLETION_DATE = l_op_completion_date,
LAST_UNIT_START_DATE = l_op_start_date,
LAST_UNIT_COMPLETION_DATE = l_op_completion_date
WHERE WIP_ENTITY_ID = p_wip_entity_id
AND OPERATION_SEQ_NUM = l_op_seq_num;
update wip_discrete_jobs
set scheduled_start_date = p_start_date,
scheduled_completion_date = p_completion_date
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id;
l_program_update_date DATE;
SELECT operation_seq_num,
resource_seq_num,
start_date,
completion_date,
assigned_units
FROM wip_operation_resources
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND usage_rate_or_amount = 0 ;
l_token_tbl.DELETE;
EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
( p_mesg_token_tbl => l_mesg_token_tbl
, p_error_level => EAM_ERROR_MESSAGE_PVT.G_WO_LEVEL
, p_entity_index => 1
);
SELECT CON.CONVERSION_RATE
INTO l_uom_conv
FROM MTL_UOM_CONVERSIONS CON
WHERE CON.UOM_CODE = l_hour_uom
AND NVL(DISABLE_DATE, SYSDATE + 1) > SYSDATE
AND CON.INVENTORY_ITEM_ID = 0;
SELECT CALENDAR_CODE,
CALENDAR_EXCEPTION_SET_ID
INTO l_calendar_code,
l_exception_set_id
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id;
DELETE FROM wip_operation_resource_usage
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
SELECT request_id,program_application_id,program_id,program_update_date
INTO l_request_id,l_program_application_id,l_program_id,l_program_update_date
FROM wip_discrete_jobs
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
INSERT INTO WIP_OPERATION_RESOURCE_USAGE
( wip_entity_id
, operation_seq_num
, resource_seq_num
, organization_id
, start_date
, completion_date
, assigned_units
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date )
VALUES
( p_wip_entity_id
, p_res_usage_tbl(cnt).operation_seq_num
, p_res_usage_tbl(cnt).resource_seq_num
, p_organization_id
, p_res_usage_tbl(cnt).start_date
, p_res_usage_tbl(cnt).completion_date
, p_res_usage_tbl(cnt).assigned_units
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
, l_request_id
, l_program_application_id
, l_program_id
, l_program_update_date );
SELECT operation_seq_num ,
resource_seq_num ,
instance_id ,
start_date ,
completion_date
BULK COLLECT INTO
l_WipOperation_tbl,
l_WipOperResource_tbl,
l_WipOperResInst_tbl,
l_WipOperResInstSt_tbl,
l_WipOperResInstEnd_tbl
FROM WIP_OP_RESOURCE_INSTANCES
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
INSERT INTO WIP_OPERATION_RESOURCE_USAGE
( wip_entity_id
, operation_seq_num
, resource_seq_num
, organization_id
, start_date
, completion_date
, assigned_units
, instance_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date )
SELECT
wip_entity_id
, operation_seq_num
, resource_seq_num
, organization_id
, start_date
, completion_date
, assigned_units
, l_WipOperResInst_tbl(mm)
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
FROM WIP_OPERATION_RESOURCE_USAGE
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = l_WipOperation_tbl(mm)
AND resource_seq_num = l_WipOperResource_tbl(mm)
AND instance_id IS NULL;
select * from
wip_sched_relationships
where relationship_type = 1
and parent_object_id = p_wip_entity_id
and parent_object_type_id = 1;
select scheduled_start_date,scheduled_completion_date,status_type,date_completed
into l_start_date,l_compl_date,l_status_type,l_date_completed
from wip_discrete_jobs
where wip_entity_id = l_relationship_record.child_object_id
and organization_id = p_organization_id;
select scheduled_start_date, scheduled_completion_date
into l_wo_start_date, l_wo_end_date
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id;
update wip_discrete_jobs set
scheduled_start_date = l_min_date
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id;
update wip_discrete_jobs set
scheduled_completion_date = l_max_date
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id;
select scheduled_start_date, scheduled_completion_date
into l_wo_start_date, l_wo_end_date
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id;