The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* If record is being updated, check that primary key is not null */
IF ( (p_validation_mode = JTF_CTM_UTILITY_PVT.G_UPDATE) AND
(l_seed_qual_rec.seeded_qual_id IS NULL OR
l_seed_qual_rec.seeded_qual_id = FND_API.G_MISS_NUM) )THEN
l_return_status := FND_API.G_RET_STS_ERROR;
/* Insert seeded qualifier record into database */
PROCEDURE Create_Seed_Qual_Record
( p_seed_qual_rec IN JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type,
x_seed_qual_out_rec OUT NOCOPY JTF_QUALIFIER_PUB.Seed_Qual_Out_Rec_Type )
IS
CURSOR c_chk_qual_name (l_qual_name VARCHAR2) IS
SELECT seeded_qual_id
FROM JTF_SEEDED_QUAL
WHERE UPPER(name) = UPPER(l_qual_name);
JTF_SEEDED_QUAL_PKG.INSERT_ROW(
X_Rowid => l_rowid,
X_SEEDED_QUAL_ID => l_seed_qual_rec.seeded_qual_id,
X_LAST_UPDATE_DATE => l_seed_qual_rec.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => l_seed_qual_rec.LAST_UPDATED_BY,
X_CREATION_DATE => l_seed_qual_rec.CREATION_DATE,
X_CREATED_BY => l_seed_qual_rec.CREATED_BY,
X_LAST_UPDATE_LOGIN => l_seed_qual_rec.LAST_UPDATE_LOGIN,
X_NAME => l_seed_qual_rec.NAME,
X_DESCRIPTION => l_seed_qual_rec.DESCRIPTION,
X_ORG_ID => l_seed_qual_rec.ORG_ID
);
PROCEDURE Update_Seed_Qual_Record
( p_seed_qual_rec IN JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type,
x_seed_qual_out_rec OUT NOCOPY JTF_QUALIFIER_PUB.Seed_Qual_Out_Rec_Type )
IS
l_rowid ROWID;
JTF_SEEDED_QUAL_PKG.UPDATE_ROW(
X_Rowid => l_rowid,
X_SEEDED_QUAL_ID => l_seed_qual_rec.seeded_qual_id,
X_LAST_UPDATE_DATE => l_seed_qual_rec.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => l_seed_qual_rec.LAST_UPDATED_BY,
X_CREATION_DATE => l_seed_qual_rec.CREATION_DATE,
X_CREATED_BY => l_seed_qual_rec.CREATED_BY,
X_LAST_UPDATE_LOGIN => l_seed_qual_rec.LAST_UPDATE_LOGIN,
X_NAME => l_seed_qual_rec.NAME,
X_DESCRIPTION => l_seed_qual_rec.DESCRIPTION,
X_ORG_ID => l_seed_qual_rec.ORG_ID
);
END Update_Seed_Qual_Record;
PROCEDURE Delete_Seed_Qual_Record
( p_seeded_qual_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2 )
IS
l_rowid ROWID;
JTF_SEEDED_QUAL_PKG.DELETE_ROW ( X_SEEDED_QUAL_ID => p_seeded_qual_id);
END Delete_Seed_Qual_Record;
SELECT data_type
FROM ALL_TAB_COLUMNS
WHERE column_name = UPPER(l_column_name)
AND table_name = UPPER(l_table_name)
AND owner =
(select table_owner
from all_synonyms
where synonym_name = UPPER(l_table_name)
and owner = l_apps_schema);
SELECT oracle_username
INTO l_apps_schema_name
FROM fnd_oracle_userid
WHERE read_only_flag = 'U';
select 1
into l_count
from jtf_terr jta,
jtf_terr_qual jtq
where jta.terr_id = jtq.terr_id
and jtq.qual_usg_id = l_qual_usg_id
and rownum < 2;
SELECT 'X'
FROM ALL_TAB_COLUMNS
WHERE table_name = UPPER(l_table_name)
AND owner =
(select table_owner
from all_synonyms
where synonym_name = UPPER(l_table_name)
and owner = l_apps_schema);
SELECT 'X'
FROM ALL_TAB_COLUMNS
WHERE column_name = UPPER(l_col_name)
AND table_name = UPPER(l_table_name)
AND owner =
(select table_owner
from all_synonyms
where synonym_name = UPPER(l_table_name)
and owner = l_apps_schema);
SELECT oracle_username
INTO l_apps_schema_name
FROM fnd_oracle_userid
WHERE read_only_flag = 'U';
** value, so that the record can still be inserted into the database
** Checks items that use lookup values
*/
FUNCTION qual_usgs_info_is_valid
( p_qual_usgs_rec IN OUT NOCOPY JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type)
RETURN VARCHAR2
IS
/* return varaible */
l_return_variable VARCHAR2(1) := FND_API.G_TRUE;
SELECT 'X'
FROM JTF_QUAL_USGS
WHERE seeded_qual_id = p_seeded_qual_id
AND qual_type_usg_id = p_qual_type_usg_id;
/* If record is being updated, check that primary key is not null */
IF ( (p_validation_mode = JTF_CTM_UTILITY_PVT.G_UPDATE) AND
( l_qual_usgs_rec.qual_usg_id IS NULL OR
l_qual_usgs_rec.qual_usg_id = FND_API.G_MISS_NUM) ) THEN
l_return_status := FND_API.G_RET_STS_ERROR;
/* If record is being updated, check that primary key is not null */
IF (p_validation_mode = JTF_CTM_UTILITY_PVT.G_CREATE) THEN
/* check that Unique Key constraint not violated */
IF ( l_qual_usgs_rec.seeded_qual_id IS NOT NULL AND
l_qual_usgs_rec.seeded_qual_id <> FND_API.G_MISS_NUM AND
l_qual_usgs_rec.qual_type_usg_id IS NOT NULL AND
l_qual_usgs_rec.qual_type_usg_id <> FND_API.G_MISS_NUM )THEN
/* check if rec already exists */
OPEN c_chk_uk_violation ( l_qual_usgs_rec.seeded_qual_id
, l_qual_usgs_rec.qual_type_usg_id);
JTF_QUAL_USGS_PKG.INSERT_ROW(
X_Rowid => l_rowid,
X_QUAL_USG_ID => l_qual_usgs_rec.QUAL_USG_ID,
X_LAST_UPDATE_DATE => l_qual_usgs_rec.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => l_qual_usgs_rec.LAST_UPDATED_BY,
X_CREATION_DATE => l_qual_usgs_rec.CREATION_DATE,
X_CREATED_BY => l_qual_usgs_rec.CREATED_BY,
X_LAST_UPDATE_LOGIN => l_qual_usgs_rec.LAST_UPDATE_LOGIN,
X_APPLICATION_SHORT_NAME => l_qual_usgs_rec.APPLICATION_SHORT_NAME,
X_SEEDED_QUAL_ID => p_seed_qual_id,
X_QUAL_TYPE_USG_ID => l_qual_usgs_rec.QUAL_TYPE_USG_ID,
X_ENABLED_FLAG => l_qual_usgs_rec.ENABLED_FLAG,
X_QUAL_COL1 => l_qual_usgs_rec.QUAL_COL1,
X_QUAL_COL1_ALIAS => l_qual_usgs_rec.QUAL_COL1_ALIAS,
X_QUAL_COL1_DATATYPE => l_qual_usgs_rec.QUAL_COL1_DATATYPE,
X_QUAL_COL1_TABLE => l_qual_usgs_rec.QUAL_COL1_TABLE,
X_QUAL_COL1_TABLE_ALIAS => l_qual_usgs_rec.QUAL_COL1_TABLE_ALIAS,
X_PRIM_INT_CDE_COL => l_qual_usgs_rec.PRIM_INT_CDE_COL,
X_PRIM_INT_CDE_COL_DATATYPE => l_qual_usgs_rec.PRIM_INT_CDE_COL_DATATYPE,
X_PRIM_INT_CDE_COL_ALIAS => l_qual_usgs_rec.PRIM_INT_CDE_COL_ALIAS,
X_SEC_INT_CDE_COL => l_qual_usgs_rec.SEC_INT_CDE_COL,
X_SEC_INT_CDE_COL_ALIAS => l_qual_usgs_rec.SEC_INT_CDE_COL_ALIAS,
X_SEC_INT_CDE_COL_DATATYPE => l_qual_usgs_rec.SEC_INT_CDE_COL_DATATYPE,
X_INT_CDE_COL_TABLE => l_qual_usgs_rec.INT_CDE_COL_TABLE,
X_INT_CDE_COL_TABLE_ALIAS => l_qual_usgs_rec.INT_CDE_COL_TABLE_ALIAS,
X_SEEDED_FLAG => l_qual_usgs_rec.SEEDED_FLAG,
X_DISPLAY_TYPE => l_qual_usgs_rec.DISPLAY_TYPE,
X_LOV_SQL => l_qual_usgs_rec.LOV_SQL,
x_CONVERT_TO_ID_FLAG => l_qual_usgs_rec.CONVERT_TO_ID_FLAG,
x_COLUMN_COUNT => l_qual_usgs_rec.COLUMN_COUNT,
x_FORMATTING_FUNCTION_FLAG => l_qual_usgs_rec.FORMATTING_FUNCTION_FLAG,
x_FORMATTING_FUNCTION_NAME => l_qual_usgs_rec.FORMATTING_FUNCTION_NAME,
x_SPECIAL_FUNCTION_FLAG => l_qual_usgs_rec.SPECIAL_FUNCTION_FLAG,
x_SPECIAL_FUNCTION_NAME => l_qual_usgs_rec.SPECIAL_FUNCTION_NAME,
x_ENABLE_LOV_VALIDATION => l_qual_usgs_rec.ENABLE_LOV_VALIDATION,
x_DISPLAY_SQL1 => l_qual_usgs_rec.DISPLAY_SQL1,
x_LOV_SQL2 => l_qual_usgs_rec.LOV_SQL2,
x_DISPLAY_SQL2 => l_qual_usgs_rec.DISPLAY_SQL2,
x_LOV_SQL3 => l_qual_usgs_rec.LOV_SQL3,
x_DISPLAY_SQL3 => l_qual_usgs_rec.DISPLAY_SQL3,
X_ORG_ID => l_qual_usgs_rec.ORG_ID,
X_RULE1 => l_qual_usgs_rec.RULE1,
X_RULE2 => l_qual_usgs_rec.RULE2,
X_DISPLAY_SEQUENCE => l_qual_usgs_rec.DISPLAY_SEQUENCE,
X_DISPLAY_LENGTH => l_qual_usgs_rec.DISPLAY_LENGTH,
X_JSP_LOV_SQL => l_qual_usgs_rec.JSP_LOV_SQL,
x_use_in_lookup_flag => l_qual_usgs_rec.use_in_lookup_flag);
PROCEDURE Update_Qual_Usgs_Record
( p_qual_usgs_rec IN JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type,
x_qual_usgs_out_rec OUT NOCOPY JTF_QUALIFIER_PUB.Qual_Usgs_All_Out_Rec_Type)
IS
l_rowid ROWID;
JTF_QUAL_USGS_PKG.UPDATE_ROW(
X_Rowid => l_rowid,
X_QUAL_USG_ID => l_qual_usgs_rec.QUAL_USG_ID,
X_LAST_UPDATE_DATE => l_qual_usgs_rec.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => l_qual_usgs_rec.LAST_UPDATED_BY,
X_CREATION_DATE => l_qual_usgs_rec.CREATION_DATE,
X_CREATED_BY => l_qual_usgs_rec.CREATED_BY,
X_LAST_UPDATE_LOGIN => l_qual_usgs_rec.LAST_UPDATE_LOGIN,
X_APPLICATION_SHORT_NAME => l_qual_usgs_rec.APPLICATION_SHORT_NAME,
X_SEEDED_QUAL_ID => l_qual_usgs_rec.SEEDED_QUAL_ID,
X_QUAL_TYPE_USG_ID => l_qual_usgs_rec.QUAL_TYPE_USG_ID,
X_ENABLED_FLAG => l_qual_usgs_rec.ENABLED_FLAG,
X_QUAL_COL1 => l_qual_usgs_rec.QUAL_COL1,
X_QUAL_COL1_ALIAS => l_qual_usgs_rec.QUAL_COL1_ALIAS,
X_QUAL_COL1_DATATYPE => l_qual_usgs_rec.QUAL_COL1_DATATYPE,
X_QUAL_COL1_TABLE => l_qual_usgs_rec.QUAL_COL1_TABLE,
X_QUAL_COL1_TABLE_ALIAS => l_qual_usgs_rec.QUAL_COL1_TABLE_ALIAS,
X_PRIM_INT_CDE_COL => l_qual_usgs_rec.PRIM_INT_CDE_COL,
X_PRIM_INT_CDE_COL_DATATYPE => l_qual_usgs_rec.PRIM_INT_CDE_COL_DATATYPE,
X_PRIM_INT_CDE_COL_ALIAS => l_qual_usgs_rec.PRIM_INT_CDE_COL_ALIAS,
X_SEC_INT_CDE_COL => l_qual_usgs_rec.SEC_INT_CDE_COL,
X_SEC_INT_CDE_COL_ALIAS => l_qual_usgs_rec.SEC_INT_CDE_COL_ALIAS,
X_SEC_INT_CDE_COL_DATATYPE => l_qual_usgs_rec.SEC_INT_CDE_COL_DATATYPE,
X_INT_CDE_COL_TABLE => l_qual_usgs_rec.INT_CDE_COL_TABLE,
X_INT_CDE_COL_TABLE_ALIAS => l_qual_usgs_rec.INT_CDE_COL_TABLE_ALIAS,
X_SEEDED_FLAG => l_qual_usgs_rec.SEEDED_FLAG,
X_DISPLAY_TYPE => l_qual_usgs_rec.DISPLAY_TYPE,
X_LOV_SQL => l_qual_usgs_rec.LOV_SQL,
x_CONVERT_TO_ID_FLAG => l_qual_usgs_rec.CONVERT_TO_ID_FLAG,
x_COLUMN_COUNT => l_qual_usgs_rec.COLUMN_COUNT,
x_FORMATTING_FUNCTION_FLAG => l_qual_usgs_rec.FORMATTING_FUNCTION_FLAG,
x_FORMATTING_FUNCTION_NAME => l_qual_usgs_rec.FORMATTING_FUNCTION_NAME,
x_SPECIAL_FUNCTION_FLAG => l_qual_usgs_rec.SPECIAL_FUNCTION_FLAG,
x_SPECIAL_FUNCTION_NAME => l_qual_usgs_rec.SPECIAL_FUNCTION_NAME,
x_ENABLE_LOV_VALIDATION => l_qual_usgs_rec.ENABLE_LOV_VALIDATION,
x_DISPLAY_SQL1 => l_qual_usgs_rec.DISPLAY_SQL1,
x_LOV_SQL2 => l_qual_usgs_rec.LOV_SQL2,
x_DISPLAY_SQL2 => l_qual_usgs_rec.DISPLAY_SQL2,
x_LOV_SQL3 => l_qual_usgs_rec.LOV_SQL3,
x_DISPLAY_SQL3 => l_qual_usgs_rec.DISPLAY_SQL3,
X_ORG_ID => l_qual_usgs_rec.ORG_ID,
X_RULE1 => l_qual_usgs_rec.RULE1,
X_RULE2 => l_qual_usgs_rec.RULE2,
X_DISPLAY_SEQUENCE => l_qual_usgs_rec.DISPLAY_SEQUENCE,
X_DISPLAY_LENGTH => l_qual_usgs_rec.DISPLAY_LENGTH,
X_JSP_LOV_SQL => l_qual_usgs_rec.JSP_LOV_SQL,
X_use_in_lookup_flag => l_qual_usgs_rec.use_in_lookup_flag
);
END Update_Qual_Usgs_Record;
/* Check if records should be deleted
** seeded flag <> Y
** cannot delete seeded_qual if more that one qual_usg exists for that qualifier
** cannot delete qualifier if it is being used in a territory or territory type definition
** check if record should be deleted
*/
PROCEDURE is_qualifier_delete_allowed ( p_seeded_qual_id IN NUMBER
, p_qual_usg_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2 )
IS
/* seeded qualifier record does not have more than one child */
CURSOR c_chk_sq_child ( p_seeded_qual_id NUMBER, p_qual_usg_id NUMBER ) IS
SELECT qual_usg_id
FROM JTF_QUAL_USGS
WHERE seeded_qual_id = p_seeded_qual_id
AND qual_usg_id <> p_qual_usg_id;
SELECT qual_usg_id
FROM JTF_QUAL_USGS
WHERE seeded_flag = 'Y'
AND qual_usg_id = p_qual_usg_id;
SELECT terr_qual_id
FROM JTF_TERR_QUAL
WHERE qual_usg_id = p_qual_usg_id;
SELECT terr_type_qual_id
FROM JTF_TERR_TYPE_QUAL
WHERE qual_usg_id = p_qual_usg_id;
FND_MESSAGE.Set_Name('JTF', 'DELETE QUAL PVT: CHECK_DEL1');
FND_MESSAGE.Set_Name('JTF', 'DELETE QUAL PVT: CHECK_DEL2');
FND_MESSAGE.Set_Name('JTF', 'DELETE QUAL PVT: CHECK_DEL3');
FND_MESSAGE.Set_Name('JTF', 'DELETE QUAL PVT: CHECK_DEL4');
PROCEDURE Delete_Qual_Usgs_Record
( p_qual_usg_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2 )
IS
l_rowid ROWID;
JTF_QUAL_USGS_PKG.DELETE_ROW ( X_QUAL_USG_ID => p_qual_usg_id);
END Delete_Qual_Usgs_Record;
PROCEDURE Update_Qualifier
(p_api_version IN NUMBER,
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,
x_Return_Status OUT NOCOPY VARCHAR2,
x_Msg_Count OUT NOCOPY NUMBER,
x_Msg_Data OUT NOCOPY VARCHAR2,
p_Seed_Qual_Rec IN JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type := JTF_QUALIFIER_PUB.G_MISS_SEED_QUAL_REC,
p_Qual_Usgs_Rec IN JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type := JTF_QUALIFIER_PUB.G_MISS_QUAL_USGS_ALL_REC,
x_Seed_Qual_Rec OUT NOCOPY JTF_QUALIFIER_PUB.Seed_Qual_Out_Rec_Type,
x_Qual_Usgs_Rec OUT NOCOPY JTF_QUALIFIER_PUB.Qual_Usgs_All_Out_Rec_Type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Qualifier';
SAVEPOINT UPDATE_QUALIFIER_PVT;
FND_MESSAGE.Set_Name('JTF', 'PVT UpdateQual API: Start');
JTF_CTM_UTILITY_PVT.G_UPDATE,
p_validation_level,
l_return_status);
JTF_CTM_UTILITY_PVT.G_UPDATE,
p_validation_level,
l_return_status);
update_seed_qual_record ( l_seed_qual_rec,
l_seed_qual_out_rec);
FND_MESSAGE.Set_Name('JTF', 'PVTQUAL API:Update QualUsgRec');
update_qual_usgs_record ( l_qual_usgs_rec,
l_qual_usgs_out_rec);
FND_MESSAGE.Set_Name('JTF', 'PVT Update Qual API: End');
ROLLBACK TO UPDATE_QUALIFIER_PVT;
ROLLBACK TO UPDATE_QUALIFIER_PVT;
ROLLBACK TO UPDATE_QUALIFIER_PVT;
END Update_Qualifier;
PROCEDURE Delete_Qualifier
(p_api_version IN NUMBER,
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,
x_Return_Status OUT NOCOPY VARCHAR2,
x_Msg_Count OUT NOCOPY NUMBER,
x_Msg_Data OUT NOCOPY VARCHAR2,
p_Seeded_Qual_Id IN NUMBER := FND_API.G_MISS_NUM,
p_Qual_Usg_Id IN NUMBER := FND_API.G_MISS_NUM
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Qualifier';
SAVEPOINT DELETE_QUALIFIER_PVT;
FND_MESSAGE.Set_Name('JTF', 'Delete Qualifier PVT: Start');
/* check if record should be deleted */
is_qualifier_delete_allowed ( l_seeded_qual_id, l_qual_usg_id , l_return_status);
FND_MESSAGE.Set_Name('JTF', 'Delete Qualifier PVT: Debug1');
delete_seed_qual_record ( l_seeded_qual_id,
l_return_status);
FND_MESSAGE.Set_Name('JTF', 'Delete Qualifier PVT: Debug2');
delete_qual_usgs_record ( l_qual_usg_id,
l_return_status);
FND_MESSAGE.Set_Name('JTF', 'PVT Delete Qual API: End');
ROLLBACK TO DELETE_QUALIFIER_PVT;
ROLLBACK TO DELETE_QUALIFIER_PVT;
ROLLBACK TO DELETE_QUALIFIER_PVT;