The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_DML_Operation CONSTANT VARCHAR2(30) := 'INSERT';
JTF_TERR_CNR_GROUPS_PKG.Insert_Row(
x_rowid => l_rowid,
x_CNR_GROUP_ID => l_terr_cnr_group_Id,
x_LAST_UPDATED_BY => p_Terr_cnr_group_rec.LAST_UPDATED_BY,
x_LAST_UPDATE_DATE => p_Terr_cnr_group_rec.LAST_UPDATE_DATE,
x_CREATED_BY => p_Terr_cnr_group_rec.CREATED_BY,
x_CREATION_DATE => p_Terr_cnr_group_rec.CREATION_DATE,
x_LAST_UPDATE_LOGIN => p_Terr_cnr_group_rec.LAST_UPDATE_LOGIN,
x_NAME => p_Terr_cnr_group_rec.NAME,
x_DESCRIPTION => p_Terr_cnr_group_rec.DESCRIPTION,
x_ATTRIBUTE_CATEGORY => p_Terr_cnr_group_rec.ATTRIBUTE_CATEGORY,
x_ATTRIBUTE1 => p_Terr_cnr_group_rec.ATTRIBUTE1,
x_ATTRIBUTE2 => p_Terr_cnr_group_rec.ATTRIBUTE2,
x_ATTRIBUTE3 => p_Terr_cnr_group_rec.ATTRIBUTE3,
x_ATTRIBUTE4 => p_Terr_cnr_group_rec.ATTRIBUTE4,
x_ATTRIBUTE5 => p_Terr_cnr_group_rec.ATTRIBUTE5,
x_ATTRIBUTE6 => p_Terr_cnr_group_rec.ATTRIBUTE6,
x_ATTRIBUTE7 => p_Terr_cnr_group_rec.ATTRIBUTE7,
x_ATTRIBUTE8 => p_Terr_cnr_group_rec.ATTRIBUTE8,
x_ATTRIBUTE9 => p_Terr_cnr_group_rec.ATTRIBUTE9,
x_ATTRIBUTE10 => p_Terr_cnr_group_rec.ATTRIBUTE10,
x_ATTRIBUTE11 => p_Terr_cnr_group_rec.ATTRIBUTE11,
x_ATTRIBUTE12 => p_Terr_cnr_group_rec.ATTRIBUTE12,
x_ATTRIBUTE13 => p_Terr_cnr_group_rec.ATTRIBUTE13,
x_ATTRIBUTE14 => p_Terr_cnr_group_rec.ATTRIBUTE14,
x_ATTRIBUTE15 => p_Terr_cnr_group_rec.ATTRIBUTE15);
PROCEDURE Delete_Terr_Cnr_Group
(
p_Api_Version_Number 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_Terr_cnr_group_rec IN Terr_cnr_group_rec_type := G_MISS_TERR_CNR_GROUP_REC
)
AS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Terr_Cnr_Group';
l_DML_Operation CONSTANT VARCHAR2(30) := 'DELETE';
SAVEPOINT DELETE_CNR_GROUP_PVT;
JTF_TERR_CNR_GROUPS_PKG.Delete_Row(
x_CNR_GROUP_ID => p_Terr_cnr_group_rec.cnr_group_id
);
delete
from JTF_TERR_CNR_GROUP_VALUES jt
where jt.cnr_group_id = p_Terr_cnr_group_rec.cnr_group_id;
ROLLBACK TO DELETE_CNR_GROUP_PVT;
ROLLBACK TO DELETE_CNR_GROUP_PVT;
ROLLBACK TO DELETE_CNR_GROUP_PVT;
END Delete_Terr_Cnr_Group;
PROCEDURE Update_Terr_Cnr_Group
(
p_Api_Version_Number 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_Terr_cnr_group_rec IN Terr_cnr_group_rec_type := G_MISS_TERR_CNR_GROUP_REC,
x_Terr_cnr_group_out_rec OUT NOCOPY Terr_cnr_group_out_rec_type
)
AS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Cnr_Group';
l_DML_Operation CONSTANT VARCHAR2(30) := 'UPDATE';
SAVEPOINT UPDATE_CNR_GROUP_PVT;
JTF_TERR_CNR_GROUPS_PKG.Update_Row(
x_Rowid => l_rowid,
x_CNR_GROUP_ID => l_terr_cnr_group_Id,
x_LAST_UPDATED_BY => p_Terr_cnr_group_rec.LAST_UPDATED_BY,
x_LAST_UPDATE_DATE => p_Terr_cnr_group_rec.LAST_UPDATE_DATE,
x_CREATED_BY => p_Terr_cnr_group_rec.CREATED_BY,
x_CREATION_DATE => p_Terr_cnr_group_rec.CREATION_DATE,
x_LAST_UPDATE_LOGIN => p_Terr_cnr_group_rec.LAST_UPDATE_LOGIN,
x_NAME => p_Terr_cnr_group_rec.NAME,
x_DESCRIPTION => p_Terr_cnr_group_rec.DESCRIPTION,
x_ATTRIBUTE_CATEGORY => p_Terr_cnr_group_rec.ATTRIBUTE_CATEGORY,
x_ATTRIBUTE1 => p_Terr_cnr_group_rec.ATTRIBUTE1,
x_ATTRIBUTE2 => p_Terr_cnr_group_rec.ATTRIBUTE2,
x_ATTRIBUTE3 => p_Terr_cnr_group_rec.ATTRIBUTE3,
x_ATTRIBUTE4 => p_Terr_cnr_group_rec.ATTRIBUTE4,
x_ATTRIBUTE5 => p_Terr_cnr_group_rec.ATTRIBUTE5,
x_ATTRIBUTE6 => p_Terr_cnr_group_rec.ATTRIBUTE6,
x_ATTRIBUTE7 => p_Terr_cnr_group_rec.ATTRIBUTE7,
x_ATTRIBUTE8 => p_Terr_cnr_group_rec.ATTRIBUTE8,
x_ATTRIBUTE9 => p_Terr_cnr_group_rec.ATTRIBUTE9,
x_ATTRIBUTE10 => p_Terr_cnr_group_rec.ATTRIBUTE10,
x_ATTRIBUTE11 => p_Terr_cnr_group_rec.ATTRIBUTE11,
x_ATTRIBUTE12 => p_Terr_cnr_group_rec.ATTRIBUTE12,
x_ATTRIBUTE13 => p_Terr_cnr_group_rec.ATTRIBUTE13,
x_ATTRIBUTE14 => p_Terr_cnr_group_rec.ATTRIBUTE14,
x_ATTRIBUTE15 => p_Terr_cnr_group_rec.ATTRIBUTE15
);
ROLLBACK TO UPDATE_CNR_GROUP_PVT;
ROLLBACK TO UPDATE_CNR_GROUP_PVT;
ROLLBACK TO UPDATE_CNR_GROUP_PVT;
END Update_Terr_Cnr_Group;
select 1
from JTF_TERR_CNR_GROUPS jtcg
where jtcg.NAME = p_Terr_cnr_group_rec.NAME
and rownum < 2;
select 1
from JTF_TERR_VALUES_ALL jtva
where jtva.CNR_GROUP_ID = p_Terr_cnr_group_rec.CNR_GROUP_ID
and rownum < 2;
CURSOR c_cnrgroupname_update IS
select 1
from JTF_TERR_CNR_GROUPS jtcg
where jtcg.NAME = p_Terr_cnr_group_rec.NAME
and jtcg.CNR_GROUP_ID <> p_Terr_cnr_group_rec.CNR_GROUP_ID
and rownum < 2;
select 1
from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
where jtq.qual_usg_id = -1102
and jtv.terr_qual_id = jtq.terr_qual_id
and jtv.low_value_char_id = p_Terr_cnr_group_rec.CNR_GROUP_ID
and rownum < 2;
IF ( p_DML_Operation = 'DELETE' ) THEN
--dbms_output.put_line('DELETE');
ELSIF (p_DML_Operation IN ('INSERT','UPDATE')) THEN
--dbms_output.put_line('INSERT,UPDATE');
IF ( p_Terr_cnr_group_rec.LAST_UPDATED_BY is NULL OR
p_Terr_cnr_group_rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
If ( p_Terr_cnr_group_rec.LAST_UPDATE_DATE IS NULL OR
p_Terr_cnr_group_rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
If ( p_Terr_cnr_group_rec.LAST_UPDATE_LOGIN is NULL OR
p_Terr_cnr_group_rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
IF ( p_DML_Operation = 'INSERT' ) THEN
--dbms_output.put_line('INSERT CHECK AT END');
ELSIF ( p_DML_Operation = 'UPDATE' ) THEN
--dbms_output.put_line('first update check');
OPEN c_cnrgroupname_update;
FETCH c_cnrgroupname_update INTO l_cnr_group_num;
IF c_cnrgroupname_update%FOUND THEN
--dbms_output.put_line('c_cnrgroupname_update%FOUND');
CLOSE c_cnrgroupname_update;
l_DML_Operation CONSTANT VARCHAR2(30) := 'INSERT';
JTF_TERR_CNR_GROUP_VALUES_PKG.Insert_Row(
x_rowid => l_rowid,
x_CNR_GROUP_VALUE_ID => l_terr_cnr_group_value_Id,
x_LAST_UPDATED_BY => p_Terr_cnr_values_rec.LAST_UPDATED_BY,
x_LAST_UPDATE_DATE => p_Terr_cnr_values_rec.LAST_UPDATE_DATE,
x_CREATED_BY => p_Terr_cnr_values_rec.CREATED_BY,
x_CREATION_DATE => p_Terr_cnr_values_rec.CREATION_DATE,
x_LAST_UPDATE_LOGIN => p_Terr_cnr_values_rec.LAST_UPDATE_LOGIN,
x_CNR_GROUP_ID => p_Terr_cnr_values_rec.CNR_GROUP_ID,
x_COMPARISON_OPERATOR => p_Terr_cnr_values_rec.COMPARISON_OPERATOR,
x_LOW_VALUE_CHAR => p_Terr_cnr_values_rec.LOW_VALUE_CHAR,
x_HIGH_VALUE_CHAR => p_Terr_cnr_values_rec.HIGH_VALUE_CHAR,
x_START_DATE_ACTIVE => p_Terr_cnr_values_rec.START_DATE_ACTIVE,
x_END_DATE_ACTIVE => p_Terr_cnr_values_rec.END_DATE_ACTIVE,
x_ORG_ID => p_Terr_cnr_values_rec.ORG_ID
);
PROCEDURE Delete_Terr_Cnr_Value
(
p_Api_Version_Number 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_Terr_cnr_values_rec IN Terr_cnr_values_rec_type := G_MISS_TERR_CNR_VALUES_REC
) AS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Terr_Cnr_Value';
l_DML_Operation CONSTANT VARCHAR2(30) := 'DELETE';
SAVEPOINT DELETE_CNR_VALUE_PVT;
JTF_TERR_CNR_GROUP_VALUES_PKG.Delete_Row(
x_CNR_GROUP_VALUE_ID => p_Terr_cnr_values_rec.cnr_group_value_id
);
ROLLBACK TO DELETE_CNR_VALUE_PVT;
ROLLBACK TO DELETE_CNR_VALUE_PVT;
ROLLBACK TO DELETE_CNR_VALUE_PVT;
End Delete_Terr_Cnr_Value;
PROCEDURE Update_Terr_Cnr_Value
(
p_Api_Version_Number 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_Terr_cnr_values_rec IN Terr_cnr_values_rec_type := G_MISS_TERR_CNR_VALUES_REC,
x_Terr_cnr_values_out_rec OUT NOCOPY Terr_cnr_values_out_rec_type
) AS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Cnr_Values';
l_DML_Operation CONSTANT VARCHAR2(30) := 'UPDATE';
SAVEPOINT UPDATE_CNR_VALUES_PVT;
JTF_TERR_CNR_GROUP_VALUES_PKG.Update_Row(
x_rowid => l_rowid,
x_CNR_GROUP_VALUE_ID => l_terr_cnr_group_value_Id,
x_LAST_UPDATED_BY => p_Terr_cnr_values_rec.LAST_UPDATED_BY,
x_LAST_UPDATE_DATE => p_Terr_cnr_values_rec.LAST_UPDATE_DATE,
x_CREATED_BY => p_Terr_cnr_values_rec.CREATED_BY,
x_CREATION_DATE => p_Terr_cnr_values_rec.CREATION_DATE,
x_LAST_UPDATE_LOGIN => p_Terr_cnr_values_rec.LAST_UPDATE_LOGIN,
x_CNR_GROUP_ID => p_Terr_cnr_values_rec.CNR_GROUP_ID,
x_COMPARISON_OPERATOR => p_Terr_cnr_values_rec.COMPARISON_OPERATOR,
x_LOW_VALUE_CHAR => p_Terr_cnr_values_rec.LOW_VALUE_CHAR,
x_HIGH_VALUE_CHAR => p_Terr_cnr_values_rec.HIGH_VALUE_CHAR,
x_START_DATE_ACTIVE => p_Terr_cnr_values_rec.START_DATE_ACTIVE,
x_END_DATE_ACTIVE => p_Terr_cnr_values_rec.END_DATE_ACTIVE,
x_ORG_ID => p_Terr_cnr_values_rec.ORG_ID
);
ROLLBACK TO UPDATE_CNR_VALUES_PVT;
ROLLBACK TO UPDATE_CNR_VALUES_PVT;
ROLLBACK TO UPDATE_CNR_VALUES_PVT;
'Error inside Update_Terr_Cnr_Group ' || sqlerrm);
End Update_Terr_Cnr_Value;