The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT update_access_flag
FROM ASO_QUOTE_ACCESSES
WHERE quote_number = l_quote_number
AND resource_id = l_resource_id;
SELECT ACC.ACCESS_ID,
ACC.UPDATE_ACCESS_FLAG
FROM JTF_RS_REP_MANAGERS MGR,
JTF_RS_GROUP_USAGES UGS,
ASO_QUOTE_ACCESSES ACC
WHERE UGS.USAGE = 'SALES'
AND UGS.GROUP_ID = MGR.GROUP_ID
AND MGR.HIERARCHY_TYPE IN ('MGR_TO_MGR', 'MGR_TO_REP')
AND SYSDATE BETWEEN MGR.START_DATE_ACTIVE AND NVL(MGR.END_DATE_ACTIVE, SYSDATE)
AND ACC.RESOURCE_ID = MGR.RESOURCE_ID
AND NVL(ACC.RESOURCE_GRP_ID, MGR.GROUP_ID) = MGR.GROUP_ID
AND MGR.PARENT_RESOURCE_ID = l_resource_id
AND ACC.QUOTE_NUMBER = l_quote_number;
IF c_direct_access_rec.update_access_flag = 'Y' THEN
IF aso_debug_pub.g_debug_flag = 'Y' THEN
aso_debug_pub.add('ASO_SECURITY_INT: Get_Quote_Access: resource has direct UPDATE access', 1, 'Y');
RETURN 'UPDATE';
IF FND_PROFILE.VALUE('ASO_API_MGR_ROLE_ACCESS') = 'UPDATE' THEN
l_profile_access := 'Y';
IF l_profile_access = 'Y' OR c_manager_access_rec.UPDATE_ACCESS_FLAG = 'Y' THEN
IF aso_debug_pub.g_debug_flag = 'Y' THEN
aso_debug_pub.add('ASO_SECURITY_INT: Get_Quote_Access: resource has profile UPDATE access OR subordinate of resource has direct UPDATE access', 1, 'Y');
aso_debug_pub.add('ASO_SECURITY_INT: Get_Quote_Access: subordinate access: ' || c_manager_access_rec.UPDATE_ACCESS_FLAG, 1, 'Y');
RETURN 'UPDATE';
SELECT access_id
FROM ASO_QUOTE_ACCESSES
WHERE quote_number = l_quote_number
AND resource_id = l_resource_id;
SELECT quote_number,
resource_id,
resource_grp_id,
update_access_flag
FROM ASO_QUOTE_ACCESSES
WHERE access_id = l_access_id;
SELECT resource_id,quote_header_id,
resource_grp_id
FROM ASO_QUOTE_HEADERS_ALL
WHERE quote_number = l_quote_number
AND max_version_flag = 'Y';
select access_id
from aso_quote_accesses
where access_id = p_access_id;
IS SELECT price_request_id FROM
ASO_QUOTE_HEADERS_ALL where quote_number = p_qte_number
AND max_version_flag = 'Y';
l_update_access_flag VARCHAR2(1);
l_update_access_flag := FND_API.G_MISS_CHAR;
OR ((nvl(p_call_from_oafwk_flag, 'N') = fnd_api.g_true) and (l_qte_access_tbl(i).operation_code = 'UPDATE')) then
open c_access_id_exist(l_qte_access_tbl(i).access_id);
l_qte_access_tbl(i).operation_code := 'UPDATE';
l_qte_access_tbl(i).operation_code := 'UPDATE';
aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: before Insert_Row: counter: ' || i, 1, 'Y');
ASO_QUOTE_ACCESSES_PKG.Insert_Row(
px_ACCESS_ID => l_qte_access_tbl(i).access_id,
p_QUOTE_NUMBER => l_qte_access_tbl(i).QUOTE_NUMBER,
p_RESOURCE_ID => l_qte_access_tbl(i).RESOURCE_ID,
p_RESOURCE_GRP_ID => l_qte_access_tbl(i).RESOURCE_GRP_ID,
p_CREATED_BY => G_USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_REQUEST_ID => l_qte_access_tbl(i).REQUEST_ID,
p_PROGRAM_APPLICATION_ID => l_qte_access_tbl(i).PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => l_qte_access_tbl(i).PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => l_qte_access_tbl(i).PROGRAM_UPDATE_DATE,
p_KEEP_FLAG => l_qte_access_tbl(i).KEEP_FLAG,
p_UPDATE_ACCESS_FLAG => l_qte_access_tbl(i).UPDATE_ACCESS_FLAG,
p_CREATED_BY_TAP_FLAG => l_qte_access_tbl(i).CREATED_BY_TAP_FLAG,
p_TERRITORY_ID => l_qte_access_tbl(i).TERRITORY_ID,
p_TERRITORY_SOURCE_FLAG => 'N',
p_ROLE_ID => l_qte_access_tbl(i).ROLE_ID,
p_ATTRIBUTE_CATEGORY => l_qte_access_tbl(i).ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_qte_access_tbl(i).ATTRIBUTE1,
p_ATTRIBUTE2 => l_qte_access_tbl(i).ATTRIBUTE2,
p_ATTRIBUTE3 => l_qte_access_tbl(i).ATTRIBUTE3,
p_ATTRIBUTE4 => l_qte_access_tbl(i).ATTRIBUTE4,
p_ATTRIBUTE5 => l_qte_access_tbl(i).ATTRIBUTE5,
p_ATTRIBUTE6 => l_qte_access_tbl(i).ATTRIBUTE6,
p_ATTRIBUTE7 => l_qte_access_tbl(i).ATTRIBUTE7,
p_ATTRIBUTE8 => l_qte_access_tbl(i).ATTRIBUTE8,
p_ATTRIBUTE9 => l_qte_access_tbl(i).ATTRIBUTE9,
p_ATTRIBUTE10 => l_qte_access_tbl(i).ATTRIBUTE10,
p_ATTRIBUTE11 => l_qte_access_tbl(i).ATTRIBUTE11,
p_ATTRIBUTE12 => l_qte_access_tbl(i).ATTRIBUTE12,
p_ATTRIBUTE13 => l_qte_access_tbl(i).ATTRIBUTE13,
p_ATTRIBUTE14 => l_qte_access_tbl(i).ATTRIBUTE14,
p_ATTRIBUTE15 => l_qte_access_tbl(i).ATTRIBUTE15,
p_ATTRIBUTE16 => l_qte_access_tbl(i).ATTRIBUTE16,
p_ATTRIBUTE17 => l_qte_access_tbl(i).ATTRIBUTE17,
p_ATTRIBUTE18 => l_qte_access_tbl(i).ATTRIBUTE18,
p_ATTRIBUTE19 => l_qte_access_tbl(i).ATTRIBUTE19,
p_ATTRIBUTE20 => l_qte_access_tbl(i).ATTRIBUTE20,
p_OBJECT_VERSION_NUMBER => l_qte_access_tbl(i).OBJECT_VERSION_NUMBER
);
aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: after Insert_Row: x_qte_access_tbl('||i||').access_id: ' || x_qte_access_tbl(i).access_id, 1, 'Y');
ELSIF l_qte_access_tbl(i).operation_code = 'UPDATE' THEN
IF aso_debug_pub.g_debug_flag = 'Y' THEN
aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: before Update_Row: counter: ' || i, 1, 'Y');
ASO_QUOTE_ACCESSES_PKG.Update_Row(
p_ACCESS_ID => l_qte_access_tbl(i).access_id,
p_QUOTE_NUMBER => l_qte_access_tbl(i).QUOTE_NUMBER,
p_RESOURCE_ID => l_qte_access_tbl(i).RESOURCE_ID,
p_RESOURCE_GRP_ID => l_qte_access_tbl(i).RESOURCE_GRP_ID,
p_CREATED_BY => G_USER_ID,
p_CREATION_DATE => fnd_api.g_miss_date,
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_REQUEST_ID => l_qte_access_tbl(i).REQUEST_ID,
p_PROGRAM_APPLICATION_ID => l_qte_access_tbl(i).PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => l_qte_access_tbl(i).PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => l_qte_access_tbl(i).PROGRAM_UPDATE_DATE,
p_KEEP_FLAG => l_qte_access_tbl(i).KEEP_FLAG,
p_UPDATE_ACCESS_FLAG => l_qte_access_tbl(i).UPDATE_ACCESS_FLAG,
p_CREATED_BY_TAP_FLAG => l_qte_access_tbl(i).CREATED_BY_TAP_FLAG,
p_TERRITORY_ID => l_qte_access_tbl(i).TERRITORY_ID,
p_TERRITORY_SOURCE_FLAG => l_qte_access_tbl(i).TERRITORY_SOURCE_FLAG,
p_ROLE_ID => l_qte_access_tbl(i).ROLE_ID,
p_ATTRIBUTE_CATEGORY => l_qte_access_tbl(i).ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_qte_access_tbl(i).ATTRIBUTE1,
p_ATTRIBUTE2 => l_qte_access_tbl(i).ATTRIBUTE2,
p_ATTRIBUTE3 => l_qte_access_tbl(i).ATTRIBUTE3,
p_ATTRIBUTE4 => l_qte_access_tbl(i).ATTRIBUTE4,
p_ATTRIBUTE5 => l_qte_access_tbl(i).ATTRIBUTE5,
p_ATTRIBUTE6 => l_qte_access_tbl(i).ATTRIBUTE6,
p_ATTRIBUTE7 => l_qte_access_tbl(i).ATTRIBUTE7,
p_ATTRIBUTE8 => l_qte_access_tbl(i).ATTRIBUTE8,
p_ATTRIBUTE9 => l_qte_access_tbl(i).ATTRIBUTE9,
p_ATTRIBUTE10 => l_qte_access_tbl(i).ATTRIBUTE10,
p_ATTRIBUTE11 => l_qte_access_tbl(i).ATTRIBUTE11,
p_ATTRIBUTE12 => l_qte_access_tbl(i).ATTRIBUTE12,
p_ATTRIBUTE13 => l_qte_access_tbl(i).ATTRIBUTE13,
p_ATTRIBUTE14 => l_qte_access_tbl(i).ATTRIBUTE14,
p_ATTRIBUTE15 => l_qte_access_tbl(i).ATTRIBUTE15,
p_ATTRIBUTE16 => l_qte_access_tbl(i).ATTRIBUTE16,
p_ATTRIBUTE17 => l_qte_access_tbl(i).ATTRIBUTE17,
p_ATTRIBUTE18 => l_qte_access_tbl(i).ATTRIBUTE18,
p_ATTRIBUTE19 => l_qte_access_tbl(i).ATTRIBUTE19,
p_ATTRIBUTE20 => l_qte_access_tbl(i).ATTRIBUTE20,
p_OBJECT_VERSION_NUMBER => l_qte_access_tbl(i).OBJECT_VERSION_NUMBER
);
aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: after Update_Row: x_qte_access_tbl('||i||').access_id: ' || x_qte_access_tbl(i).access_id, 1, 'Y');
l_update_access_flag := l_resource_rec.update_access_flag;
aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: l_update_access_flag: ' || l_update_access_flag, 1, 'Y');
IF l_update_access_flag <> 'Y' THEN
x_return_status := FND_API.G_RET_STS_ERROR;
aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: trying to set primary resource update_access_flag other than Y', 1, 'Y');
aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: before update ASO_QUOTE_HEADERS_ALL', 1, 'Y');
UPDATE ASO_QUOTE_HEADERS_ALL
SET resource_grp_id = l_resource_grp_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id
WHERE quote_number = l_quote_number
AND max_version_flag = 'Y';
aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: inside update ASO_QUOTE_HEADERS_ALL: SQL%ROWCOUNT = 0', 1, 'Y');
aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: after update ASO_QUOTE_HEADERS_ALL', 1, 'Y');
aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: after Update_Row: x_qte_access_tbl('||i||').access_id:: ' || x_qte_access_tbl(i).access_id, 1, 'Y');
aso_debug_pub.add('ASO_SECURITY_INT: End of Add_Resource: before update ASO_QUOTE_HEADERS_ALL', 1, 'Y');
UPDATE ASO_QUOTE_HEADERS_ALL
SET last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id
WHERE quote_number = l_quote_number AND
max_version_flag = 'Y';
aso_debug_pub.add('ASO_SECURITY_INT: End of Add_Resource: after update ASO_QUOTE_HEADERS_ALL: SQL%ROWCOUNT = 0', 1, 'Y');
aso_debug_pub.add('ASO_SECURITY_INT.Add_Resource : Calling ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES, quote number : ' || l_quote_number, 1, 'Y');
ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES(l_quote_number);
PROCEDURE Delete_Resource
(
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
P_Qte_Access_Tbl IN Qte_Access_Tbl_Type,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
X_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
X_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)
IS
L_API_NAME VARCHAR2(50) := 'Delete_Resource';
SELECT access_id
FROM ASO_QUOTE_ACCESSES
WHERE quote_number = l_quote_number
AND resource_id = l_resource_id;
SELECT quote_number,
resource_id
FROM ASO_QUOTE_ACCESSES
WHERE access_id = l_access_id;
SELECT resource_id
FROM ASO_QUOTE_HEADERS_ALL
WHERE quote_number = l_quote_number
AND max_version_flag = 'Y';
IS SELECT price_request_id FROM
ASO_QUOTE_HEADERS_ALL where quote_number = p_qte_number
AND max_version_flag = 'Y';
SELECT quote_number FROM
ASO_QUOTE_ACCESSES WHERE
access_id = p_access_id;
aso_debug_pub.add('ASO_SECURITY_INT: ****** Start of Delete_Resource API ******', 1, 'Y');
SAVEPOINT Delete_Resource_INT;
aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: LOOP for access_id: ' || P_Qte_Access_Tbl(i).access_id, 1, 'Y');
aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: LOOP for resource_id: ' || P_Qte_Access_Tbl(i).resource_id, 1, 'Y');
aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: LOOP for quote_number: ' || P_Qte_Access_Tbl(i).quote_number, 1, 'Y');
aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: count: ' || P_Qte_Access_Tbl.count);
aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: *** C_resource LOOP variables ***');
aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: l_quote_number: ' || l_quote_number);
aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: l_resource_id: ' || l_resource_id);
aso_debug_pub.add('Delete_Resource: *** C_primary_resource LOOP variables ***');
aso_debug_pub.add('Delete_Resource: l_primary_resource_id: ' || l_primary_resource_id);
aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: trying to delete primary salesrep', 1, 'Y');
FND_MESSAGE.Set_Name('ASO', 'ASO_SEC_DELETE_PRIMARY_RES');
aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: before Delete_Row: l_access_id: ' || l_access_id, 1, 'Y');
ASO_QUOTE_ACCESSES_PKG.Delete_Row(
p_ACCESS_ID => l_access_id
);
aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: after Delete_Row', 1, 'Y');
aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: trying to delete a non-existent entry', 1, 'Y');
FND_MESSAGE.Set_Name('ASO', 'ASO_SEC_DELETE_INVALID_ID');
aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: trying to delete without passing enough info', 1, 'Y');
FND_MESSAGE.Set_Name('ASO', 'ASO_SEC_DELETE_INSUFFICIENT');
aso_debug_pub.add('ASO_SECURITY_INT: End of Delete_Resource: before update ASO_QUOTE_HEADERS_ALL', 1, 'Y');
UPDATE ASO_QUOTE_HEADERS_ALL
SET last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id
WHERE quote_number = l_quote_number AND
max_version_flag = 'Y';
aso_debug_pub.add('ASO_SECURITY_INT: End of Delete_Resource: after update ASO_QUOTE_HEADERS_ALL: SQL%ROWCOUNT = 0', 1, 'Y');
aso_debug_pub.add('ASO_SECURITY_INT.Delete_Resource : Calling ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES, quote number : ' || l_quote_number, 1, 'Y');
ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES(l_quote_number);
aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: End of API body', 1, 'Y');
END Delete_Resource;
SELECT resource_id
FROM jtf_rs_resource_extns
WHERE user_id = l_user_id
AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
SELECT rel.role_id
FROM jtf_rs_role_relations rel, jtf_rs_roles_b rolb
WHERE rel.role_id = rolb.role_id
AND rolb.role_type_code = 'SALES'
AND NVL(rolb.active_flag, 'Y') <> 'N'
AND NVL(rel.delete_flag , 'N') <> 'Y'
AND TRUNC(NVL(rel.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(rel.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
AND rel.role_resource_id = l_resource_id;
l_qte_access_rec.LAST_UPDATED_BY := G_USER_ID;
l_qte_access_rec.LAST_UPDATE_LOGIN := G_LOGIN_ID;
l_qte_access_rec.LAST_UPDATE_DATE := SYSDATE;
l_qte_access_rec.PROGRAM_UPDATE_DATE := p_qte_header_rec.program_update_date;
l_qte_access_rec.UPDATE_ACCESS_FLAG := 'Y';