[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT A.APPROVAL_RULE_ID,
A.APPROVAL_OBJECT_CODE,
A.STATUS_CODE,
B.APPROVER_NAME,
B.APPROVER_SEQUENCE
FROM AHL_APPROVAL_RULES_Vl A,AHL_APPROVERS_V B
WHERE A.APPROVAL_RULE_ID=B.APPROVAL_RULE_ID
AND A.STATUS_CODE='ACTIVE'
AND A.APPROVAL_OBJECT_CODE=p_object_type
ORDER BY B.APPROVER_SEQUENCE;
SELECT C.*,D.VISIT_NUMBER
FROM AHL_ROUTES_APP_V A,AHL_MR_ROUTES B,AHL_VISIT_TASKS_B C,AHL_VISITS_VL D
WHERE A.ROUTE_ID=p_activity_id
AND A.ROUTE_ID=B.ROUTE_ID
AND B.MR_ROUTE_ID=C.MR_ROUTE_ID
AND C.VISIT_ID=D.VISIT_ID
AND D.STATUS_CODE<>'CLOSED';
SELECT *
FROM AHL_ROUTES_VL
WHERE ROUTE_ID=p_activity_id;
SELECT C.*,D.VISIT_NUMBER
FROM AHL_ROUTES_APP_V A,AHL_MR_ROUTES B,AHL_VISIT_TASKS_B C,AHL_VISITS_VL D,AHL_ROUTE_OPERATIONS E
WHERE A.ROUTE_ID=B.ROUTE_ID
AND A.ROUTE_ID=E.ROUTE_ID
AND B.MR_ROUTE_ID=C.MR_ROUTE_ID
AND C.VISIT_ID=D.VISIT_ID
AND E.OPERATION_ID=p_activity_id
AND D.STATUS_CODE<>'CLOSED';
SELECT *
FROM AHL_OPERATIONS_B_KFV
WHERE OPERATION_ID=p_activity_id;
SELECT * FROM AHL_JTF_RS_EMP_V
WHERE USER_NAME=C_USERNAME;
Select revision_status_code,
revision_number,
start_date_active,
end_date_active,
concatenated_segments
From ahl_operations_b_KFV
Where operation_id = c_operation_id;
Select revision_status_code,
revision_number,
start_date_active,
end_date_active,
concatenated_segments
From ahl_operations_b_kfv
Where operation_id = c_operation_id;
SELECT rou.route_id
FROM AHL_ROUTES_APP_V rou ,
ahl_route_operations arop,
ahl_operations_b oper
WHERE (
TRUNC(rou.start_date_active) >= TRUNC(oper.start_date_active)
AND
TRUNC(NVL(oper.end_date_active, rou.start_date_active + 1)) > TRUNC(rou.start_date_active)
)
AND TRUNC( NVL( rou.end_date_active , SYSDATE + 1 )) > TRUNC( SYSDATE )
AND rou.time_span < c_op_duration
AND rou.route_id = arop.route_id
AND oper.operation_id = arop.operation_id
AND arop.operation_id = c_oper_id
AND rownum < 2;
SELECT NVL(SUM(RES_DURATION),0)
FROM ( SELECT MAX( duration ) RES_DURATION
FROM AHL_RT_OPER_RESOURCES
WHERE association_type_code = 'OPERATION'
AND scheduled_type_id = 1
AND object_id = c_operation_id
GROUP BY schedule_seq
);
Select ENIGMA_OP_ID into l_enig_op_id
From ahl_operations_b
Where operation_id = p_source_operation_id;
SELECT COUNT(*) into l_counter
FROM AHL_OPERATIONS_B_KFV
WHERE CONCATENATED_SEGMENTS=l_oper_Rec.CONCATENATED_SEGMENTS
AND REVISION_NUMBER=l_oper_Rec.revision_number+1;
Update AHL_OPERATIONS_B
Set REVISION_STATUS_CODE=l_upd_status,
OBJECT_VERSION_number=OBJECT_VERSION_number+1
Where OPERATION_ID=p_source_operation_id
and OBJECT_VERSION_NUMBER=p_object_Version_number;
Update AHL_OPERATIONS_TL
Set APPROVER_NOTE=null
Where OPERATION_ID=p_source_operation_id;
UPDATE AHL_OPERATIONS_B
SET REVISION_STATUS_CODE=l_upd_status,
OBJECT_VERSION_number=OBJECT_VERSION_number+1
WHERE OPERATION_ID=p_source_OPERATION_id
and OBJECT_VERSION_NUMBER=p_object_Version_number;
SELECT *
FROM AHL_ROUTES_APP_V
WHERE route_id = c_route_id;
Select ENIGMA_ROUTE_ID into l_enigma_route_id
From ahl_routes_b
Where route_id = p_source_route_id;
/*SELECT COUNT(*) into l_counter
FROM AHL_ROUTE_MR_V
WHERE ROUTE_ID=l_route_Rec.ROUTE_ID
AND trunc(nvl(AHL_ROUTE_MR_V.EFFECTIVE_TO,SYSDATE))>=trunc(SYSDATE);*/
SELECT COUNT(*) into l_counter
from ahl_mr_routes a, ahl_mr_headers_b b
where
a.route_id = l_route_rec.route_id and
a.mr_header_id = b.mr_header_id and
b.application_usg_code = rtrim(ltrim(fnd_profile.value('AHL_APPLN_USAGE'))) and
trunc(nvl(b.effective_to,sysdate)) >= trunc(sysdate);
SELECT COUNT(*) into l_counter
FROM AHL_ROUTES_APP_V
WHERE UPPER(TRIM(ROUTE_NO))=UPPER(TRIM(l_route_Rec.route_no))
AND REVISION_NUMBER=l_route_Rec.revision_number+1;
UPDATE AHL_ROUTES_B
SET REVISION_STATUS_CODE=l_upd_status,
OBJECT_VERSION_number=OBJECT_VERSION_number+1
WHERE ROUTE_ID=p_source_route_id
and OBJECT_VERSION_NUMBER=p_object_Version_number;
UPDATE AHL_ROUTES_TL
SET APPROVER_NOTE=null
WHERE ROUTE_ID=p_source_route_id;
UPDATE AHL_ROUTES_B
SET REVISION_STATUS_CODE=l_upd_status,
OBJECT_VERSION_number=OBJECT_VERSION_number+1
WHERE ROUTE_ID=p_source_route_id
and OBJECT_VERSION_NUMBER=p_object_Version_number;
SELECT ROUTE_ID,ROUTE_NO,REVISION_NUMBER,START_DATE_ACTIVE,END_DATE_ACTIVE
FROM AHL_ROUTES_APP_V
WHERE ROUTE_ID=C_ROUTE_ID;
SELECT ROUTE_ID,ROUTE_NO,REVISION_NUMBER,START_DATE_ACTIVE,END_DATE_ACTIVE
FROM AHL_ROUTES_APP_V
WHERE UPPER(TRIM(ROUTE_NO))=UPPER(TRIM(C_ROUTE_NO))
AND REVISION_NUMBER=C_REVISION_NUMBER-1;
SELECT * from AHL_MR_ROUTES
WHERE ROUTE_ID=C_ROUTE_ID;
SELECT * FROM AHL_MR_ROUTE_SEQUENCES
WHERE (MR_ROUTE_ID=C_MR_ROUTE_ID OR RELATED_MR_ROUTE_ID=C_MR_ROUTE_ID);
SELECT REVISION_STATUS_CODE INTO l_curr_status
FROM AHL_ROUTES_APP_V WHERE ROUTE_ID=p_route_id;
UPDATE AHL_ROUTES_B
SET REVISION_STATUS_CODE=l_status,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE ROUTE_ID=P_ROUTE_ID;
UPDATE AHL_ROUTES_TL
SET APPROVER_NOTE=p_approver_note
WHERE ROUTE_ID=P_ROUTE_ID;
UPDATE AHL_ROUTES_B
SET REVISION_STATUS_CODE='TERMINATED',
-- START_DATE_ACTIVE=l_fr_date,
END_DATE_ACTIVE=l_to_date,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE ROUTE_ID=P_ROUTE_ID;
UPDATE AHL_ROUTES_B
SET REVISION_STATUS_CODE=l_status,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE ROUTE_ID=P_ROUTE_ID;
UPDATE AHL_ROUTES_B
SET REVISION_STATUS_CODE= l_status,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
START_DATE_ACTIVE=l_fr_date
WHERE ROUTE_ID=P_ROUTE_ID;
UPDATE AHL_ROUTES_B
SET REVISION_STATUS_CODE= l_status,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
END_DATE_ACTIVE=l_to_date
WHERE ROUTE_ID=l_prev_route_rec.ROUTE_ID;
SELECT AHL_MR_ROUTES_S.NEXTVAL
INTO l_new_mr_ROUTE_ID
FROM DUAL;
AHL_MR_ROUTES_PKG.INSERT_ROW (
-- X_ROWID =>L_ROWID,
X_MR_ROUTE_ID =>l_new_mr_ROUTE_ID,
X_MR_HEADER_ID =>l_mr_route_Rec.mr_header_id,
X_ROUTE_ID =>P_ROUTE_ID,
X_STAGE =>l_mr_route_Rec.STAGE,
--MANESING::VWP Enhancements, 21-Jan-2011, added stage type code column
X_STAGE_TYPE_CODE =>l_mr_route_Rec.STAGE_TYPE_CODE,
X_ATTRIBUTE_CATEGORY =>l_mr_route_Rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 =>l_mr_route_Rec.ATTRIBUTE1,
X_ATTRIBUTE2 =>l_mr_route_Rec.ATTRIBUTE2,
X_ATTRIBUTE3 =>l_mr_route_Rec.ATTRIBUTE3,
X_ATTRIBUTE4 =>l_mr_route_Rec.ATTRIBUTE4,
X_ATTRIBUTE5 =>l_mr_route_Rec.ATTRIBUTE5,
X_ATTRIBUTE6 =>l_mr_route_Rec.ATTRIBUTE6,
X_ATTRIBUTE7 =>l_mr_route_Rec.ATTRIBUTE7,
X_ATTRIBUTE8 =>l_mr_route_Rec.ATTRIBUTE8,
X_ATTRIBUTE9 =>l_mr_route_Rec.ATTRIBUTE9,
X_ATTRIBUTE10 =>l_mr_route_Rec.ATTRIBUTE10,
X_ATTRIBUTE11 =>l_mr_route_Rec.ATTRIBUTE11,
X_ATTRIBUTE12 =>l_mr_route_Rec.ATTRIBUTE12,
X_ATTRIBUTE13 =>l_mr_route_Rec.ATTRIBUTE13,
X_ATTRIBUTE14 =>l_mr_route_Rec.ATTRIBUTE14,
X_ATTRIBUTE15 =>l_mr_route_Rec.ATTRIBUTE15,
X_OBJECT_VERSION_NUMBER =>1,
X_CREATION_DATE =>sysdate,
X_CREATED_BY =>fnd_global.user_id,
X_LAST_UPDATE_DATE =>sysdate,
X_LAST_UPDATED_BY =>fnd_global.user_id,
X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
SELECT AHL_MR_ROUTE_SEQUENCES_S .NEXTVAL
INTO l_new_mr_ROUTE_SEQ_ID FROM DUAL;
AHL_MR_ROUTE_SEQUENCES_PKG.INSERT_ROW (
X_MR_ROUTE_SEQUENCE_ID =>l_new_mr_route_seq_id,
X_RELATED_MR_ROUTE_ID =>l_seq_rel_mr_route_id,
X_SEQUENCE_CODE =>l_mr_route_seq_rec.SEQUENCE_CODE,
X_MR_ROUTE_ID =>l_seq_mr_route_id,
X_OBJECT_VERSION_NUMBER =>1,
X_ATTRIBUTE_CATEGORY =>l_mr_route_seq_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 =>l_mr_route_seq_rec.ATTRIBUTE1,
X_ATTRIBUTE2 =>l_mr_route_seq_rec.ATTRIBUTE2,
X_ATTRIBUTE3 =>l_mr_route_seq_rec.ATTRIBUTE3,
X_ATTRIBUTE4 =>l_mr_route_seq_rec.ATTRIBUTE4,
X_ATTRIBUTE5 =>l_mr_route_seq_rec.ATTRIBUTE5,
X_ATTRIBUTE6 =>l_mr_route_seq_rec.ATTRIBUTE6,
X_ATTRIBUTE7 =>l_mr_route_seq_rec.ATTRIBUTE7,
X_ATTRIBUTE8 =>l_mr_route_seq_rec.ATTRIBUTE8,
X_ATTRIBUTE9 =>l_mr_route_seq_rec.ATTRIBUTE9,
X_ATTRIBUTE10 =>l_mr_route_seq_rec.ATTRIBUTE10,
X_ATTRIBUTE11 =>l_mr_route_seq_rec.ATTRIBUTE11,
X_ATTRIBUTE12 =>l_mr_route_seq_rec.ATTRIBUTE12,
X_ATTRIBUTE13 =>l_mr_route_seq_rec.ATTRIBUTE13,
X_ATTRIBUTE14 =>l_mr_route_seq_rec.ATTRIBUTE14,
X_ATTRIBUTE15 =>l_mr_route_seq_rec.ATTRIBUTE15,
X_CREATION_DATE =>sysdate,
X_CREATED_BY =>fnd_global.user_id,
X_LAST_UPDATE_DATE =>sysdate,
X_LAST_UPDATED_BY =>fnd_global.user_id,
X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
UPDATE AHL_ROUTES_B
SET REVISION_STATUS_CODE= l_status,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
END_DATE_ACTIVE=l_to_date
WHERE ROUTE_ID=l_prev_route_rec.ROUTE_ID;
SELECT OPERATION_ID,
CONCATENATED_SEGMENTS,
REVISION_NUMBER,
START_DATE_ACTIVE,
END_DATE_ACTIVE
FROM AHL_OPERATIONS_B_KFV
WHERE OPERATION_ID=C_OPERATION_ID;
SELECT OPERATION_ID,
CONCATENATED_SEGMENTS,
REVISION_NUMBER,
START_DATE_ACTIVE,
END_DATE_ACTIVE
FROM AHL_OPERATIONS_B_KFV
WHERE CONCATENATED_SEGMENTS=C_CONCATENATED_SEGMENTS
AND REVISION_NUMBER=C_REVISION_NUMBER-1;
SELECT REVISION_STATUS_CODE INTO l_curr_status
FROM AHL_OPERATIONS_B_KFV WHERE OPERATION_ID=p_operation_id;
UPDATE AHL_OPERATIONS_B
SET REVISION_STATUS_CODE=l_status,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE OPERATION_ID=P_OPERATION_ID;
UPDATE AHL_OPERATIONS_TL
SET APPROVER_NOTE=p_approver_note
WHERE OPERATION_ID=P_OPERATION_ID;
UPDATE AHL_OPERATIONS_B
SET REVISION_STATUS_CODE='TERMINATED',
-- START_DATE_ACTIVE=l_fr_date,
END_DATE_ACTIVE=l_to_date,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE OPERATION_ID=P_OPERATION_ID;
UPDATE AHL_OPERATIONS_B
SET REVISION_STATUS_CODE=l_status,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE OPERATION_ID=P_OPERATION_ID;
UPDATE AHL_OPERATIONS_B
SET REVISION_STATUS_CODE=l_status,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
START_DATE_ACTIVE=l_fr_date
WHERE OPERATION_ID=P_OPERATION_ID;
UPDATE AHL_OPERATIONS_B
SET REVISION_STATUS_CODE=l_status,
OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
END_DATE_ACTIVE=l_to_date
WHERE OPERATION_ID=l_prev_oper_rec.OPERATION_ID;