[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rt_op_dependency_id, from_rt_op_id, from_op_step, dependency_code, to_rt_op_id,
to_op_step, security_group_id, attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15
FROM ahl_rt_oper_dependencies_v
WHERE rt_op_dependency_id = c_rt_op_dependency_id;
SELECT route_operation_id
FROM ahl_route_operations ro,ahl_operations_vl op
WHERE ro.operation_id = op.operation_id
AND ro.route_id = c_route_id
AND ro.step = c_operation_step
AND op.concatenated_segments = c_operation_number
AND op.revision_status_code = c_operation_status
AND trunc(nvl(op.end_date_active, sysdate + 1)) > trunc(sysdate);
SELECT COUNT(*) INTO l_counter
FROM ahl_rt_oper_dependencies od
WHERE --dependency_code = p_dependency_code and -- sansatpa commented this for bug #14135529
from_rt_op_id
IN ( SELECT route_operation_id
FROM ahl_route_operations
WHERE ROUTE_ID =p_route_id
AND route_operation_id =od.from_rt_op_id
)
START WITH to_rt_op_id = p_from_rt_op_id
CONNECT BY PRIOR from_rt_op_id = to_rt_op_id;
2. Updating Operation step number which is already associated to route will update the respective dependecies step numbers.
3. Deleting Route - Operation Asscotion will Delete the Route - operation Dependencies which already created.
4. Route Time Span is calculated according to Operation Sequences created.
Example : a. If operation 1 duration is 10 from it's resources and operation 2 duration is 15 from it's resources
b. If the dependecy between operation 1 and operation 2 is parallel route time
span will max of both operations i.e 15
c. If the dependecy between operation 1 and operation 2 is before route time
span will sum of both operations i.e 25
*/
-- -----------------------------------------------------------------------------
PROCEDURE process_rt_oper_dep
(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_default IN VARCHAR2 := FND_API.G_FALSE,
p_module_type IN VARCHAR2 := NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_route_id IN NUMBER ,
p_x_rt_oper_dep_tbl IN OUT NOCOPY RT_OPER_DEP_TBL
)
IS
CURSOR validate_oper_step(c_route_id NUMBER, c_route_operation_id NUMBER)
IS
SELECT step
FROM AHL_ROUTE_OPERATIONS
WHERE route_id = c_route_id
AND route_operation_id = c_route_operation_id ;
SELECT 'X'
FROM ahl_rt_oper_dependencies
WHERE rt_op_dependency_id = c_rt_op_dependency_id and
object_version_number = c_object_version_number;
SELECT 'X'
FROM ahl_rt_oper_dependencies_v
WHERE route_id = c_route_id
-- Modified by sansatpa to check for flipped addition as A parallel to B is same as B parallel to A.
AND ((FROM_RT_OP_ID = C_FROM_RT_OP_ID AND TO_RT_OP_ID = C_TO_RT_OP_ID)
or (FROM_RT_OP_ID = C_TO_RT_OP_ID AND TO_RT_OP_ID = C_FROM_RT_OP_ID));
SELECT time_span, start_date_active
FROM ahl_routes_b
WHERE route_id = c_route_id;
DELETE FROM ahl_rt_oper_dependencies
WHERE rt_op_dependency_id = p_x_rt_oper_dep_tbl(i).rt_op_dependency_id;
UPDATE ahl_rt_oper_dependencies SET
object_version_number = p_x_rt_oper_dep_tbl(i).object_version_number,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
from_rt_op_id = p_x_rt_oper_dep_tbl(i).from_rt_op_id,
from_op_step = p_x_rt_oper_dep_tbl(i).from_op_step,
to_rt_op_id = p_x_rt_oper_dep_tbl(i).to_rt_op_id,
to_op_step = p_x_rt_oper_dep_tbl(i).to_op_step,
dependency_code = p_x_rt_oper_dep_tbl(i).dependency_code,
security_group_id = p_x_rt_oper_dep_tbl(i).security_group_id,
attribute_category = p_x_rt_oper_dep_tbl(i).attribute_category,
attribute1 = p_x_rt_oper_dep_tbl(i).attribute1,
attribute2 = p_x_rt_oper_dep_tbl(i).attribute2,
attribute3 = p_x_rt_oper_dep_tbl(i).attribute3,
attribute4 = p_x_rt_oper_dep_tbl(i).attribute4,
attribute5 = p_x_rt_oper_dep_tbl(i).attribute5,
attribute6 = p_x_rt_oper_dep_tbl(i).attribute6,
attribute7 = p_x_rt_oper_dep_tbl(i).attribute7,
attribute8 = p_x_rt_oper_dep_tbl(i).attribute8,
attribute9 = p_x_rt_oper_dep_tbl(i).attribute9,
attribute10 = p_x_rt_oper_dep_tbl(i).attribute10,
attribute11 = p_x_rt_oper_dep_tbl(i).attribute11,
attribute12 = p_x_rt_oper_dep_tbl(i).attribute12,
attribute13 = p_x_rt_oper_dep_tbl(i).attribute13,
attribute14 = p_x_rt_oper_dep_tbl(i).attribute14,
attribute15 = p_x_rt_oper_dep_tbl(i).attribute15
WHERE rt_op_dependency_id = p_x_rt_oper_dep_tbl(i).rt_op_dependency_id;
INSERT INTO ahl_rt_oper_dependencies(
RT_OP_DEPENDENCY_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FROM_RT_OP_ID,
FROM_OP_STEP,
DEPENDENCY_CODE,
TO_RT_OP_ID,
TO_OP_STEP,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15)
VALUES(
AHL_RT_OPER_DEP_S.NEXTVAL,
p_x_rt_oper_dep_tbl(i).object_version_number,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id,
p_x_rt_oper_dep_tbl(i).from_rt_op_id,
p_x_rt_oper_dep_tbl(i).from_op_step,
p_x_rt_oper_dep_tbl(i).dependency_code,
p_x_rt_oper_dep_tbl(i).to_rt_op_id,
p_x_rt_oper_dep_tbl(i).to_op_step,
p_x_rt_oper_dep_tbl(i).security_group_id,
p_x_rt_oper_dep_tbl(i).attribute_category,
p_x_rt_oper_dep_tbl(i).attribute1,
p_x_rt_oper_dep_tbl(i).attribute2,
p_x_rt_oper_dep_tbl(i).attribute3,
p_x_rt_oper_dep_tbl(i).attribute4,
p_x_rt_oper_dep_tbl(i).attribute5,
p_x_rt_oper_dep_tbl(i).attribute6,
p_x_rt_oper_dep_tbl(i).attribute7,
p_x_rt_oper_dep_tbl(i).attribute8,
p_x_rt_oper_dep_tbl(i).attribute9,
p_x_rt_oper_dep_tbl(i).attribute10,
p_x_rt_oper_dep_tbl(i).attribute11,
p_x_rt_oper_dep_tbl(i).attribute12,
p_x_rt_oper_dep_tbl(i).attribute13,
p_x_rt_oper_dep_tbl(i).attribute14,
p_x_rt_oper_dep_tbl(i).attribute15)
RETURNING rt_op_dependency_id INTO p_x_rt_oper_dep_tbl(i).rt_op_dependency_id;