The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
MR_ROUTE_ID,
OBJECT_VERSION_NUMBER,
MR_HEADER_ID,
ROUTE_ID,
ROUTE_NUMBER,
ROUTE_REVISION_NUMBER,
ROUTE_DESCRIPTION,
OPERATOR,
PRODUCT_TYPE,
STAGE,
--MANESING::VWP Enhancements, 21-Jan-2011, added stage type code column
STAGE_TYPE_CODE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM AHL_MR_ROUTES_V
WHERE MR_ROUTE_ID=C_MR_ROUTE_ID;
* Stage column exists just for backward compatibility and can no longer be updated.
* So if Stage Type is not null, then Stage must be blanked out.
*/
IF P_X_MR_ROUTE_TBL(I).STAGE_TYPE_CODE IS NOT NULL THEN
P_X_MR_ROUTE_TBL(I).STAGE := NULL;
P_X_MR_ROUTE_TBL(I).STAGE:=NULL; -- code commented since Stage cannot be updated
SELECT
route_id,
revision_status_code
FROM
ahl_routes_b
WHERE
UPPER(route_no)=UPPER(c_route_no) AND
revision_number=NVL(c_revision_number,revision_number) AND
TRUNC(NVL(end_date_active,SYSDATE+1))>TRUNC(SYSDATE) AND
revision_status_code='COMPLETE' AND
application_usg_code = RTRIM(LTRIM(fnd_profile.value('AHL_APPLN_USAGE')));
/*select ROUTE_ID,REVISION_STATUS_CODE
from AHL_ROUTES_V
where UPPER(ROUTE_NO)=upper(C_ROUTE_NO)
and revision_number=nvl(C_REVISION_NUMBER,revision_number)
and TRUNC(NVL(END_DATE_ACTIVE,SYSDATE+1))>TRUNC(SYSDATE)
AND REVISION_STATUS_CODE='COMPLETE';*/
SELECT MR_STATUS_CODE,TYPE_CODE
from AHL_MR_HEADERS_APP_V
where MR_HEADER_ID=c_mr_header_id
and MR_STATUS_CODE in('DRAFT','APPROVAL_REJECTED');
SELECT
COUNT(*) into l_counter
FROM
ahl_routes_b
WHERE
route_no=p_x_mr_route_rec.route_number AND
revision_status_code='COMPLETE' AND
NVL(end_date_active,SYSDATE+1)>SYSDATE AND
application_usg_code = RTRIM(LTRIM(fnd_profile.value('AHL_APPLN_USAGE')));
/*Select count(*) into l_counter
From ahl_routes_v
where route_no=p_x_mr_route_rec.route_number
and revision_status_code='COMPLETE'
and NVL(end_date_active,SYSDATE+1)>SYSDATE;*/
SELECT program_type_code INTO l_mr_prog_type FROM ahl_mr_headers_app_v WHERE mr_header_id = p_x_mr_route_rec.mr_header_id;
SELECT route_type_code INTO l_route_type FROM ahl_routes_app_v WHERE route_id = p_x_mr_route_rec.route_id;
* Stage can no longer be updated since the required functionality is taken care by Stage Type.
* This validation is solely intended for Public APIs. From UI, Stage being read only can never be updated.
*/
IF (p_x_mr_route_rec.dml_operation = 'U') THEN
SELECT NVL(stage, 0), NVL(p_x_mr_route_rec.stage, 0)
INTO l_curr_stage_number, l_passed_stage_number
FROM AHL_MR_ROUTES
WHERE mr_route_id = p_x_mr_route_rec.mr_route_id;
SELECT 'X'
FROM AHL_MR_ROUTE_SEQUENCES
WHERE mr_route_id = p_mr_route_id OR related_mr_route_id = p_mr_route_id;
DELETE AHL_MR_ROUTE_SEQUENCES a
where (MR_ROUTE_ID =P_X_MR_ROUTE_TBL(i).MR_ROUTE_ID or RELATED_MR_ROUTE_ID=P_X_MR_ROUTE_TBL(i).MR_ROUTE_ID);
DELETE AHL_MR_ROUTES
where MR_ROUTE_ID =p_x_MR_ROUTE_TBL(i).MR_ROUTE_ID
and OBJECT_VERSION_NUMBER=p_x_MR_ROUTE_TBL(i).object_version_number;
AHL_MR_ROUTES_PKG.UPDATE_ROW (
X_MR_ROUTE_ID =>P_X_MR_ROUTE_TBL(i).MR_ROUTE_ID,
X_OBJECT_VERSION_NUMBER =>p_x_MR_ROUTE_TBL(i).object_version_number,
X_MR_HEADER_ID =>P_X_MR_ROUTE_TBL(i).MR_HEADER_ID,
X_ROUTE_ID =>P_X_MR_ROUTE_TBL(i).ROUTE_ID,
X_STAGE =>P_X_MR_ROUTE_TBL(i).STAGE,
--MANESING::VWP Enhancements, 21-Jan-2011, added stage type code column
X_STAGE_TYPE_CODE =>P_X_MR_ROUTE_TBL(i).STAGE_TYPE_CODE,
X_ATTRIBUTE_CATEGORY =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE1,
X_ATTRIBUTE2 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE2,
X_ATTRIBUTE3 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE3,
X_ATTRIBUTE4 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE4,
X_ATTRIBUTE5 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE5,
X_ATTRIBUTE6 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE6,
X_ATTRIBUTE7 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE7,
X_ATTRIBUTE8 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE8,
X_ATTRIBUTE9 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE9,
X_ATTRIBUTE10 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE10,
X_ATTRIBUTE11 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE11,
X_ATTRIBUTE12 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE12,
X_ATTRIBUTE13 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE13,
X_ATTRIBUTE14 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE14,
X_ATTRIBUTE15 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE15,
X_LAST_UPDATE_DATE =>sysdate,
X_LAST_UPDATED_BY =>fnd_global.user_id,
X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
AHL_MR_ROUTES_PKG.INSERT_ROW (
X_MR_ROUTE_ID =>P_X_MR_ROUTE_TBL(i).MR_ROUTE_ID,
X_OBJECT_VERSION_NUMBER =>1,
X_MR_HEADER_ID =>P_X_MR_ROUTE_TBL(i).MR_HEADER_ID,
X_ROUTE_ID =>P_X_MR_ROUTE_TBL(i).ROUTE_ID,
X_STAGE =>P_X_MR_ROUTE_TBL(i).STAGE,
--MANESING::VWP Enhancements, 21-Jan-2011, added stage type code column
X_STAGE_TYPE_CODE =>P_X_MR_ROUTE_TBL(i).STAGE_TYPE_CODE,
X_ATTRIBUTE_CATEGORY =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE1,
X_ATTRIBUTE2 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE2,
X_ATTRIBUTE3 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE3,
X_ATTRIBUTE4 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE4,
X_ATTRIBUTE5 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE5,
X_ATTRIBUTE6 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE6,
X_ATTRIBUTE7 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE7,
X_ATTRIBUTE8 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE8,
X_ATTRIBUTE9 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE9,
X_ATTRIBUTE10 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE10,
X_ATTRIBUTE11 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE11,
X_ATTRIBUTE12 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE12,
X_ATTRIBUTE13 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE13,
X_ATTRIBUTE14 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE14,
X_ATTRIBUTE15 =>P_X_MR_ROUTE_TBL(i).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);