[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
wip_entity_id
, organization_id
, operation_sequence_id
, operation_seq_num
, standard_operation_id
, department_id
, description
, minimum_transfer_quantity
, count_point_type
, backflush_flag
, shutdown_type
, first_unit_start_date
, first_unit_completion_date
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, long_description
INTO
l_eam_op_rec.wip_entity_id
, l_eam_op_rec.organization_id
, l_eam_op_rec.operation_sequence_id
, l_eam_op_rec.operation_seq_num
, l_eam_op_rec.standard_operation_id
, l_eam_op_rec.department_id
, l_eam_op_rec.description
, l_eam_op_rec.minimum_transfer_quantity
, l_eam_op_rec.count_point_type
, l_eam_op_rec.backflush_flag
, l_eam_op_rec.shutdown_type
, l_eam_op_rec.start_date
, l_eam_op_rec.completion_date
, l_eam_op_rec.attribute_category
, l_eam_op_rec.attribute1
, l_eam_op_rec.attribute2
, l_eam_op_rec.attribute3
, l_eam_op_rec.attribute4
, l_eam_op_rec.attribute5
, l_eam_op_rec.attribute6
, l_eam_op_rec.attribute7
, l_eam_op_rec.attribute8
, l_eam_op_rec.attribute9
, l_eam_op_rec.attribute10
, l_eam_op_rec.attribute11
, l_eam_op_rec.attribute12
, l_eam_op_rec.attribute13
, l_eam_op_rec.attribute14
, l_eam_op_rec.attribute15
, l_eam_op_rec.long_description
FROM wip_operations wo
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.organization_id = p_organization_id
AND wo.operation_seq_num = p_operation_seq_num;
* Procedure : Insert_Row
* Parameters IN : EAM OP column record
* Parameters OUT NOCOPY: Message Token Table
* Return Status
* Purpose : Procedure will perfrom an insert into the
* wip_operations table.
*********************************************************************/
PROCEDURE Insert_Row
( p_eam_op_rec IN 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_count number;
INSERT INTO WIP_OPERATIONS
( wip_entity_id
, operation_seq_num
, organization_id
, operation_sequence_id
, standard_operation_id
, department_id
, description
, scheduled_quantity
, quantity_in_queue
, quantity_running
, quantity_waiting_to_move
, quantity_rejected
, quantity_scrapped
, quantity_completed
, first_unit_start_date
, first_unit_completion_date
, last_unit_start_date
, last_unit_completion_date
, count_point_type
, backflush_flag
, minimum_transfer_quantity
, shutdown_type
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, long_description
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, x_pos
, y_pos)
VALUES
( p_eam_op_rec.wip_entity_id
, p_eam_op_rec.operation_seq_num
, p_eam_op_rec.organization_id
, p_eam_op_rec.operation_sequence_id
, p_eam_op_rec.standard_operation_id
, p_eam_op_rec.department_id
, p_eam_op_rec.description
, 1
, 0
, 0,0,0,0,0
, p_eam_op_rec.start_date
, p_eam_op_rec.completion_date
, p_eam_op_rec.start_date
, p_eam_op_rec.completion_date
, p_eam_op_rec.count_point_type
, p_eam_op_rec.backflush_flag
, p_eam_op_rec.minimum_transfer_quantity
, p_eam_op_rec.shutdown_type
, p_eam_op_rec.attribute_category
, p_eam_op_rec.attribute1
, p_eam_op_rec.attribute2
, p_eam_op_rec.attribute3
, p_eam_op_rec.attribute4
, p_eam_op_rec.attribute5
, p_eam_op_rec.attribute6
, p_eam_op_rec.attribute7
, p_eam_op_rec.attribute8
, p_eam_op_rec.attribute9
, p_eam_op_rec.attribute10
, p_eam_op_rec.attribute11
, p_eam_op_rec.attribute12
, p_eam_op_rec.attribute13
, p_eam_op_rec.attribute14
, p_eam_op_rec.attribute15
, p_eam_op_rec.long_description
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
, p_eam_op_rec.request_id
, p_eam_op_rec.program_application_id
, p_eam_op_rec.program_id
, SYSDATE
, p_eam_op_rec.x_pos
, p_eam_op_rec.y_pos); --Added for bug#4615678
X_last_update_login => '',
X_program_application_id=> '',
X_program_id => '',
X_request_id => ''
);
SELECT count(*) INTO l_count
FROM wip_requirement_operations
WHERE organization_id = p_eam_op_rec.organization_id
AND wip_entity_id = p_eam_op_rec.wip_entity_id
AND operation_seq_num = 1
AND rownum <=1;
select min(operation_seq_num) into l_min_op_seq_num
from wip_operations
where organization_id = p_eam_op_rec.organization_id
and wip_entity_id = p_eam_op_rec.wip_entity_id ;
select department_id into l_department_id
from wip_operations
where organization_id = p_eam_op_rec.organization_id
and wip_entity_id = p_eam_op_rec.wip_entity_id
and operation_seq_num = l_min_op_seq_num;
update wip_requirement_operations
set operation_seq_num = l_min_op_seq_num,
department_id = l_department_id
where operation_seq_num = 1
and organization_id = p_eam_op_rec.organization_id
and wip_entity_id = p_eam_op_rec.wip_entity_id ;
SELECT count(*) INTO l_count
FROM wip_eam_direct_items
WHERE organization_id = p_eam_op_rec.organization_id
AND wip_entity_id = p_eam_op_rec.wip_entity_id
AND operation_seq_num = 1
AND rownum <=1;
select min(operation_seq_num) into l_min_op_seq_num
from wip_operations
where organization_id = p_eam_op_rec.organization_id
and wip_entity_id = p_eam_op_rec.wip_entity_id ;
select department_id into l_department_id
from wip_eam_direct_items
where organization_id = p_eam_op_rec.organization_id
and wip_entity_id = p_eam_op_rec.wip_entity_id
and operation_seq_num = l_min_op_seq_num;
update wip_eam_direct_items
set operation_seq_num = l_min_op_seq_num,
department_id = l_department_id
where operation_seq_num = 1
and organization_id = p_eam_op_rec.organization_id
and wip_entity_id = p_eam_op_rec.wip_entity_id ;
, p_message_text => G_PKG_NAME ||' :Inserting Record ' || SQLERRM
, x_mesg_token_Tbl => x_mesg_token_tbl
);
END Insert_Row;
* Procedure : Update_Row
* Parameters IN : EAM OP column record
* Parameters OUT NOCOPY: Message Token Table
* Return Status
* Purpose : Procedure will perfrom an Update on the
* wip_operations table.
*********************************************************************/
PROCEDURE Update_Row
( p_eam_op_rec IN 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_old_dept_id NUMBER;
select department_id into l_old_dept_id
from wip_operations
WHERE organization_id = p_eam_op_rec.organization_id
AND wip_entity_id = p_eam_op_rec.wip_entity_id
AND operation_seq_num = p_eam_op_rec.operation_seq_num;
UPDATE WIP_OPERATIONS
SET operation_sequence_id = p_eam_op_rec.operation_sequence_id
, standard_operation_id = p_eam_op_rec.standard_operation_id
, department_id = p_eam_op_rec.department_id
, description = p_eam_op_rec.description
, first_unit_start_date = p_eam_op_rec.start_date
, first_unit_completion_date = p_eam_op_rec.completion_date
, last_unit_start_date = p_eam_op_rec.start_date
, last_unit_completion_date = p_eam_op_rec.completion_date
, count_point_type = p_eam_op_rec.count_point_type
, backflush_flag = p_eam_op_rec.backflush_flag
, minimum_transfer_quantity = p_eam_op_rec.minimum_transfer_quantity
, shutdown_type = p_eam_op_rec.shutdown_type
, attribute_category = p_eam_op_rec.attribute_category
, attribute1 = p_eam_op_rec.attribute1
, attribute2 = p_eam_op_rec.attribute2
, attribute3 = p_eam_op_rec.attribute3
, attribute4 = p_eam_op_rec.attribute4
, attribute5 = p_eam_op_rec.attribute5
, attribute6 = p_eam_op_rec.attribute6
, attribute7 = p_eam_op_rec.attribute7
, attribute8 = p_eam_op_rec.attribute8
, attribute9 = p_eam_op_rec.attribute9
, attribute10 = p_eam_op_rec.attribute10
, attribute11 = p_eam_op_rec.attribute11
, attribute12 = p_eam_op_rec.attribute12
, attribute13 = p_eam_op_rec.attribute13
, attribute14 = p_eam_op_rec.attribute14
, attribute15 = p_eam_op_rec.attribute15
, long_description = p_eam_op_rec.long_description
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
, request_id = p_eam_op_rec.request_id
, program_application_id = p_eam_op_rec.program_application_id
, program_id = p_eam_op_rec.program_id
, program_update_date = SYSDATE
, x_pos = p_eam_op_rec.x_pos --Added for bug#4615678
, y_pos = p_eam_op_rec.y_pos --Added for bug#4615678
WHERE organization_id = p_eam_op_rec.organization_id
AND wip_entity_id = p_eam_op_rec.wip_entity_id
AND operation_seq_num = p_eam_op_rec.operation_seq_num;
-- If Department of operation is updated then correponding departemnt of materials should also get updated
IF l_old_dept_id <> p_eam_op_rec.department_id THEN
UPDATE WIP_REQUIREMENT_OPERATIONS
set department_id = p_eam_op_rec.department_id
WHERE organization_id = p_eam_op_rec.organization_id
AND wip_entity_id = p_eam_op_rec.wip_entity_id
AND operation_seq_num = p_eam_op_rec.operation_seq_num ;
UPDATE WIP_EAM_DIRECT_ITEMS
SET Department_id = p_eam_op_rec.department_id
WHERE organization_id = p_eam_op_rec.organization_id
AND wip_entity_id = p_eam_op_rec.wip_entity_id
AND operation_seq_num = p_eam_op_rec.operation_seq_num ;
END Update_Row;
PROCEDURE Delete_Row
( p_eam_op_rec IN 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
BEGIN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Deleting Operation: '|| p_eam_op_rec.operation_seq_num); END IF;
DELETE FROM WIP_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;
UPDATE eam_asset_status_history
SET enable_flag = 'N'
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
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
AND (enable_flag = 'Y' OR enable_flag IS NULL);
END Delete_Row;
Insert_Row
( p_eam_op_rec => p_eam_op_rec
, x_mesg_token_Tbl => l_mesg_token_tbl
, x_return_Status => l_return_status
);
ELSIF p_eam_op_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
THEN
Update_Row
( p_eam_op_rec => p_eam_op_rec
, x_mesg_token_Tbl => l_mesg_token_tbl
, x_return_Status => l_return_status
);
ELSIF p_eam_op_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE
THEN
Delete_Row
( p_eam_op_rec => p_eam_op_rec
, x_mesg_token_Tbl => l_mesg_token_tbl
, x_return_Status => l_return_status
);
select count(*)
into l_count
from wip_operations
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
AND rownum <=1;