[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update resource availability
Delete resource availability
Calls to these procedures will invoke procedures from JTF_RS_RES_AVAILABILITY_PUB
to do business validations and to do actual inserts, updates and deletes into tables.
******************************************************************************************/
/* Package variables. */
G_PKG_NAME VARCHAR2(30) := 'JTF_RS_RES_AVAILABILITY_PVT';
SELECT 1
FROM JTF_RS_RES_AVAILABILITY
WHERE resource_id = l_resource_id
AND mode_of_availability = l_mode_of_availability;
SELECT jtf_rs_res_availability_s.nextval
INTO l_availability_id
FROM dual;
JTF_RS_RES_AVAILABILITY_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_AVAILABILITY_ID => l_availability_id,
X_RESOURCE_ID => l_resource_id,
X_AVAILABLE_FLAG => P_AVAILABLE_FLAG,
X_REASON_CODE => P_REASON_CODE,
X_START_DATE => P_START_DATE,
X_END_DATE => P_END_DATE,
X_MODE_OF_AVAILABILITY => P_MODE_OF_AVAILABILITY,
X_OBJECT_VERSION_NUMBER => l_object_version_number,
X_ATTRIBUTE1 => p_attribute1,
X_ATTRIBUTE2 => p_attribute2,
X_ATTRIBUTE3 => p_attribute3,
X_ATTRIBUTE4 => p_attribute4,
X_ATTRIBUTE5 => p_attribute5,
X_ATTRIBUTE6 => p_attribute6,
X_ATTRIBUTE7 => p_attribute7,
X_ATTRIBUTE8 => p_attribute8,
X_ATTRIBUTE9 => p_attribute9,
X_ATTRIBUTE10 => p_attribute10,
X_ATTRIBUTE11 => p_attribute11,
X_ATTRIBUTE12 => p_attribute12,
X_ATTRIBUTE13 => p_attribute13,
X_ATTRIBUTE14 => p_attribute14,
X_ATTRIBUTE15 => p_attribute15,
X_ATTRIBUTE_CATEGORY => p_attribute_category,
X_CREATION_DATE => sysdate,
X_CREATED_BY => l_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => 0);
/* Procedure to update resource availability
based on input values passed by calling routines. */
PROCEDURE update_res_availability
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_AVAILABILITY_ID IN JTF_RS_RES_AVAILABILITY.AVAILABILITY_ID%TYPE,
P_RESOURCE_ID IN JTF_RS_RES_AVAILABILITY.RESOURCE_ID%TYPE,
P_AVAILABLE_FLAG IN JTF_RS_RES_AVAILABILITY.AVAILABLE_FLAG%TYPE,
P_REASON_CODE IN JTF_RS_RES_AVAILABILITY.REASON_CODE%TYPE,
P_START_DATE IN JTF_RS_RES_AVAILABILITY.START_DATE%TYPE,
P_END_DATE IN JTF_RS_RES_AVAILABILITY.END_DATE%TYPE,
P_MODE_OF_AVAILABILITY IN JTF_RS_RES_AVAILABILITY.MODE_OF_AVAILABILITY%TYPE,
P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RES_AVAILABILITY.OBJECT_VERSION_NUMBER%TYPE,
P_ATTRIBUTE1 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE1%TYPE,
P_ATTRIBUTE2 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE2%TYPE,
P_ATTRIBUTE3 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE3%TYPE,
P_ATTRIBUTE4 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE4%TYPE,
P_ATTRIBUTE5 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE5%TYPE,
P_ATTRIBUTE6 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE6%TYPE,
P_ATTRIBUTE7 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE7%TYPE,
P_ATTRIBUTE8 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE8%TYPE,
P_ATTRIBUTE9 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE9%TYPE,
P_ATTRIBUTE10 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE10%TYPE,
P_ATTRIBUTE11 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE11%TYPE,
P_ATTRIBUTE12 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE12%TYPE,
P_ATTRIBUTE13 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE13%TYPE,
P_ATTRIBUTE14 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE14%TYPE,
P_ATTRIBUTE15 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE15%TYPE,
P_ATTRIBUTE_CATEGORY IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE_CATEGORY%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RES_AVAILABILITY';
SELECT AVAILABILITY_ID,
RESOURCE_ID,
AVAILABLE_FLAG,
REASON_CODE,
START_DATE,
END_DATE ,
MODE_OF_AVAILABILITY,
OBJECT_VERSION_NUMBER,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM jtf_rs_res_availability
WHERE availability_id = ll_availability_id;
SELECT 1
FROM jtf_rs_res_availability
WHERE resource_id = l_resource_id
AND mode_of_availability = l_mode_of_availability;
jtf_rs_res_availability_pkg.update_row(
X_AVAILABILITY_ID => l_availability_id,
X_RESOURCE_ID => l_resource_id,
X_AVAILABLE_FLAG => l_AVAILABLE_FLAG,
X_REASON_CODE => l_REASON_CODE,
X_START_DATE => l_START_DATE,
X_END_DATE => l_END_DATE,
X_MODE_OF_AVAILABILITY => l_MODE_OF_AVAILABILITY,
X_OBJECT_VERSION_NUMBER => l_object_version_number,
X_ATTRIBUTE1 => l_attribute1,
X_ATTRIBUTE2 => l_attribute2,
X_ATTRIBUTE3 => l_attribute3,
X_ATTRIBUTE4 => l_attribute4,
X_ATTRIBUTE5 => l_attribute5,
X_ATTRIBUTE6 => l_attribute6,
X_ATTRIBUTE7 => l_attribute7,
X_ATTRIBUTE8 => l_attribute8,
X_ATTRIBUTE9 => l_attribute9,
X_ATTRIBUTE10 => l_attribute10,
X_ATTRIBUTE11 => l_attribute11,
X_ATTRIBUTE12 => l_attribute12,
X_ATTRIBUTE13 => l_attribute13,
X_ATTRIBUTE14 => l_attribute14,
X_ATTRIBUTE15 => l_attribute15,
X_ATTRIBUTE_CATEGORY => l_attribute_category,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_id);
END update_res_availability;
/* Procedure to delete the resource availability */
PROCEDURE delete_res_availability
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_AVAILABILITY_ID IN JTF_RS_RES_AVAILABILITY.AVAILABILITY_ID%TYPE,
P_OBJECT_VERSION_NUM IN JTF_RS_RES_AVAILABILITY.OBJECT_VERSION_NUMBER%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)IS
CURSOR chk_res_exist_cur(ll_availability_id JTF_RS_RES_AVAILABILITY.AVAILABILITY_ID%TYPE)
IS
SELECT resource_id
FROM JTF_RS_RES_AVAILABILITY
WHERE availability_id = ll_availability_id;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RES_AVAILABILITY';
JTF_RS_RES_AVAILABILITY_PKG.DELETE_ROW(
X_AVAILABILITY_ID => l_availability_id);
END delete_res_availability;