[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
LAST_UPDATE_DATE DATE , -- := FND_API.G_MISS_DATE,
LAST_UPDATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
CREATION_DATE DATE , -- := FND_API.G_MISS_DATE,
CREATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
LAST_UPDATE_LOGIN NUMBER , -- := FND_API.G_MISS_NUM,
TERR_ID NUMBER , -- := FND_API.G_MISS_NUM,
QUAL_USG_ID NUMBER , -- := FND_API.G_MISS_NUM,
USE_TO_NAME_FLAG VARCHAR2(1) , -- := FND_API.G_MISS_CHAR,
GENERATE_FLAG VARCHAR2(1) , -- := FND_API.G_MISS_CHAR,
OVERLAP_ALLOWED_FLAG VARCHAR2(1) , -- := FND_API.G_MISS_CHAR,
QUALIFIER_MODE VARCHAR2(30) , -- := FND_API.G_MISS_CHAR,
ORG_ID NUMBER -- := FND_API.G_MISS_NUM
, START_RECORD NUMBER
, NUM_RECORDS NUMBER
, CURRENT_VALUE_SET NUMBER
, CURRENT_RECORD NUMBER );
l_terr_values_tbl.DELETE;
JTF_TERR_QUAL_PKG.Delete_Row(x_terr_qual_id => l_Terr_Qual_Id );
l_Terr_Values_Out_Tbl.Delete;
PROCEDURE Delete_Territory
(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_Id IN NUMBER)
AS
v_Terr_Id NUMBER := p_Terr_Id;
select terr_id from jtf_terr_all
connect by parent_territory_id = prior terr_id AND TERR_ID <> 1
start with terr_id = v_Terr_Id;
SELECT TERR_QUAL_ID
FROM JTF_TERR_QUAL_ALL
WHERE TERR_ID = p_Terr_Id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Territory';
SAVEPOINT Delete_Territory_Pvt;
DELETE from JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID = l_terr_qual_id;
DELETE from JTF_TERR_QUAL_ALL WHERE TERR_ID = c.Terr_Id;
DELETE from JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID = c.Terr_Id;
DELETE from JTF_TERR_USGS_ALL WHERE TERR_ID = c.Terr_Id;
DELETE from JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
( SELECT TERR_RSC_ID FROM JTF_TERR_RSC_ALL WHERE TERR_ID = c.Terr_Id );
DELETE from JTF_TERR_RSC_ALL Where TERR_ID = c.Terr_Id;
DELETE from JTF_TERR_ALL WHERE TERR_ID = c.Terr_Id;
ROLLBACK TO DELETE_TERRITORY_PVT;
ROLLBACK TO DELETE_TERRITORY_PVT;
ROLLBACK TO DELETE_TERRITORY_PVT;
END Delete_Territory;
PROCEDURE Update_Territory
(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_All_Rec IN Terr_All_Rec_Type := G_Miss_Terr_All_Rec,
-- p_Terr_Usgs_Tbl IN Terr_Usgs_Tbl_Type := G_MISS_Terr_Usgs_Tbl,
-- p_Terr_QualTypeUsgs_Tbl IN Terr_QualTypeUsgs_Tbl_Type := G_Miss_Terr_QualTypeUsgs_Tbl,
-- p_Terr_Qual_Tbl IN Terr_Qual_Tbl_Type := G_Miss_Terr_Qual_Tbl,
-- p_Terr_Values_Tbl IN Terr_Values_Tbl_Type := G_Miss_Terr_Values_Tbl,
X_Terr_All_Out_Rec OUT NOCOPY Terr_All_Out_Rec_Type
-- X_Terr_Usgs_Out_Tbl OUT NOCOPY Terr_Usgs_Out_Tbl_Type,
-- X_Terr_QualTypeUsgs_Out_Tbl OUT NOCOPY Terr_QualTypeUsgs_Out_Tbl_Type,
-- X_Terr_Qual_Out_Tbl OUT NOCOPY Terr_Qual_Out_Tbl_Type,
-- X_Terr_Values_Out_Tbl OUT NOCOPY Terr_Values_Out_Tbl_Type
)
AS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Territory';
SAVEPOINT UPDATE_TERRITORY_PVT;
Update_territory_Record( P_Api_Version_Number => P_Api_Version_Number,
P_Init_Msg_List => P_Init_Msg_List,
P_Commit => P_Commit,
p_validation_level => p_validation_level,
P_Terr_All_Rec => p_Terr_All_Rec,
X_Return_Status => l_Return_Status,
X_Msg_Count => x_Msg_Count,
X_Msg_Data => x_Msg_Data,
X_Terr_All_Out_rec => X_Terr_All_Out_Rec);
/* Usage can't be updated in R12. -- VPALLE
-- Check whether ant data is passed for update of value table
If P_Terr_Usgs_Tbl.Count > 0 Then
--
--dbms_output('Update_Territory PVT: Before Calling Update_Terr_QualType_Usage');
Update_Territory_Usages( P_Api_Version_Number => P_Api_Version_Number,
P_Init_Msg_List => P_Init_Msg_List,
P_Commit => P_Commit,
p_validation_level => p_validation_level,
P_Terr_Usgs_Tbl => p_Terr_Usgs_Tbl,
X_Return_Status => l_Return_Status,
X_Msg_Count => x_Msg_Count,
X_Msg_Data => x_Msg_Data,
X_Terr_Usgs_Out_Tbl => X_Terr_Usgs_Out_Tbl);
As the Territory Type associated with a territory can't be updated in R12, we can't update
Transaction Type usages. --VPALLE
-- Check whether ant data is passed for update of value table
If P_Terr_QualTypeUsgs_Tbl.Count > 0 Then
--
--dbms_output('Update_Territory PVT: Before Calling Update_Terr_QualType_Usage');
Update_Terr_QualType_Usage( P_Api_Version_Number => P_Api_Version_Number,
P_Init_Msg_List => P_Init_Msg_List,
P_Commit => P_Commit,
p_validation_level => p_validation_level,
P_Terr_QualTypeUsgs_Tbl => p_Terr_QualTypeUsgs_Tbl,
X_Return_Status => l_Return_Status,
X_Msg_Count => x_Msg_Count,
X_Msg_Data => x_Msg_Data,
X_Terr_QualTypeUsgs_Out_Tbl => X_Terr_QualTypeUsgs_Out_Tbl);
Update_Terr_Qualifier( P_Api_Version_Number => P_Api_Version_Number,
P_Init_Msg_List => P_Init_Msg_List,
P_Commit => P_Commit,
p_validation_level => p_validation_level,
P_Terr_Qual_Tbl => p_Terr_Qual_Tbl,
X_Return_Status => l_Return_Status,
X_Msg_Count => x_Msg_Count,
X_Msg_Data => x_Msg_Data,
X_Terr_Qual_Out_Tbl => X_Terr_Qual_Out_Tbl);
Update_Terr_Value( P_Api_Version_Number => P_Api_Version_Number,
P_Init_Msg_List => P_Init_Msg_List,
P_Commit => P_Commit,
p_validation_level => p_validation_level,
P_Terr_Value_Tbl => p_Terr_Values_Tbl,
X_Return_Status => l_Return_Status,
X_Msg_Count => x_Msg_Count,
X_Msg_Data => x_Msg_Data,
X_Terr_Value_Out_Tbl => X_Terr_Values_Out_Tbl);
ROLLBACK TO UPDATE_TERRITORY_PVT;
ROLLBACK TO UPDATE_TERRITORY_PVT;
ROLLBACK TO UPDATE_TERRITORY_PVT;
END Update_Territory;
Select Rowid,
TERR_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
APPLICATION_SHORT_NAME,
NAME,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PLANNED_FLAG,
PARENT_TERRITORY_ID,
TERRITORY_TYPE_ID,
TEMPLATE_TERRITORY_ID,
TEMPLATE_FLAG,
ESCALATION_TERRITORY_ID,
ESCALATION_TERRITORY_FLAG,
OVERLAP_ALLOWED_FLAG,
RANK,
DESCRIPTION,
UPDATE_FLAG,
AUTO_ASSIGN_RESOURCES_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORG_ID,
NUM_WINNERS,
NUM_QUAL
From JTF_TERR_ALL
Where TERR_ID = l_terr_id
For Update NOWAIT;
l_ref_terr_all_rec.LAST_UPDATE_DATE,
l_ref_terr_all_rec.LAST_UPDATED_BY,
l_ref_terr_all_rec.CREATION_DATE,
l_ref_terr_all_rec.CREATED_BY,
l_ref_terr_all_rec.LAST_UPDATE_LOGIN,
l_ref_terr_all_rec.REQUEST_ID,
l_ref_terr_all_rec.PROGRAM_APPLICATION_ID,
l_ref_terr_all_rec.PROGRAM_ID,
l_ref_terr_all_rec.PROGRAM_UPDATE_DATE,
l_ref_terr_all_rec.APPLICATION_SHORT_NAME,
l_ref_terr_all_rec.NAME,
l_ref_terr_all_rec.ENABLED_FLAG,
l_ref_terr_all_rec.START_DATE_ACTIVE,
l_ref_terr_all_rec.END_DATE_ACTIVE,
l_ref_terr_all_rec.PLANNED_FLAG,
l_ref_terr_all_rec.PARENT_TERRITORY_ID,
l_ref_terr_all_rec.TERRITORY_TYPE_ID,
l_ref_terr_all_rec.TEMPLATE_TERRITORY_ID,
l_ref_terr_all_rec.TEMPLATE_FLAG,
l_ref_terr_all_rec.ESCALATION_TERRITORY_ID,
l_ref_terr_all_rec.ESCALATION_TERRITORY_FLAG,
l_ref_terr_all_rec.OVERLAP_ALLOWED_FLAG,
l_ref_terr_all_rec.RANK,
l_ref_terr_all_rec.DESCRIPTION,
l_ref_terr_all_rec.UPDATE_FLAG,
l_ref_terr_all_rec.AUTO_ASSIGN_RESOURCES_FLAG,
l_ref_terr_all_rec.ATTRIBUTE_CATEGORY,
l_ref_terr_all_rec.ATTRIBUTE1,
l_ref_terr_all_rec.ATTRIBUTE2,
l_ref_terr_all_rec.ATTRIBUTE3,
l_ref_terr_all_rec.ATTRIBUTE4,
l_ref_terr_all_rec.ATTRIBUTE5,
l_ref_terr_all_rec.ATTRIBUTE6,
l_ref_terr_all_rec.ATTRIBUTE7,
l_ref_terr_all_rec.ATTRIBUTE8,
l_ref_terr_all_rec.ATTRIBUTE9,
l_ref_terr_all_rec.ATTRIBUTE10,
l_ref_terr_all_rec.ATTRIBUTE11,
l_ref_terr_all_rec.ATTRIBUTE12,
l_ref_terr_all_rec.ATTRIBUTE13,
l_ref_terr_all_rec.ATTRIBUTE14,
l_ref_terr_all_rec.ATTRIBUTE15,
l_ref_terr_all_rec.ORG_ID,
l_ref_terr_all_rec.NUM_WINNERS,
l_ref_terr_all_rec.NUM_QUAL;
FND_MESSAGE.Set_Name('JTF', 'API_MISSING_UPDATE_TARGET');
JTF_TERR_PKG.Update_Row(x_rowid => l_rowid,
x_terr_id => p_terr_id,
x_last_update_date => l_ref_terr_all_rec.LAST_UPDATE_DATE,
x_last_updated_by => l_ref_terr_all_rec.LAST_UPDATED_BY,
x_creation_date => l_ref_terr_all_rec.CREATION_DATE,
x_created_by => l_ref_terr_all_rec.CREATED_BY,
x_last_update_login => l_ref_terr_all_rec.LAST_UPDATE_LOGIN,
x_request_id => null,
x_program_application_id => null,
x_program_id => null,
x_program_update_date => null,
x_application_short_name => l_ref_terr_all_rec.APPLICATION_SHORT_NAME,
x_name => l_ref_terr_all_rec.name,
--x_enabled_flag => l_ref_terr_all_rec.enabled_flag,
x_start_date_active => l_ref_terr_all_rec.start_date_active,
x_end_date_active => (sysdate -1),
x_planned_flag => l_ref_terr_all_rec.planned_flag,
x_parent_territory_id => l_ref_terr_all_rec.parent_territory_id,
-- x_territory_type_id => l_ref_terr_all_rec.territory_type_id,
x_template_territory_id => l_ref_terr_all_rec.template_territory_id,
x_template_flag => l_ref_terr_all_rec.template_flag,
x_escalation_territory_id => l_ref_terr_all_rec.escalation_territory_id,
x_escalation_territory_flag => l_ref_terr_all_rec.escalation_territory_flag,
x_overlap_allowed_flag => l_ref_terr_all_rec.overlap_allowed_flag,
x_rank => l_ref_terr_all_rec.rank,
x_description => l_ref_terr_all_rec.description,
x_update_flag => l_ref_terr_all_rec.update_flag,
x_auto_assign_resources_flag => l_ref_terr_all_rec.AUTO_ASSIGN_RESOURCES_FLAG,
x_attribute_category => l_ref_terr_all_rec.attribute_category,
x_attribute1 => l_ref_terr_all_rec.attribute1,
x_attribute2 => l_ref_terr_all_rec.attribute2,
x_attribute3 => l_ref_terr_all_rec.attribute3,
x_attribute4 => l_ref_terr_all_rec.attribute4,
x_attribute5 => l_ref_terr_all_rec.attribute5,
x_attribute6 => l_ref_terr_all_rec.attribute6,
x_attribute7 => l_ref_terr_all_rec.attribute7,
x_attribute8 => l_ref_terr_all_rec.attribute8,
x_attribute9 => l_ref_terr_all_rec.attribute9,
x_attribute10 => l_ref_terr_all_rec.attribute10,
x_attribute11 => l_ref_terr_all_rec.attribute11,
x_attribute12 => l_ref_terr_all_rec.attribute12,
x_attribute13 => l_ref_terr_all_rec.attribute13,
x_attribute14 => l_ref_terr_all_rec.attribute14,
x_attribute15 => l_ref_terr_all_rec.attribute15,
x_org_id => l_ref_terr_all_rec.ORG_ID,
x_num_winners => l_ref_terr_all_rec.NUM_WINNERS,
x_num_qual => l_ref_terr_all_rec.NUM_QUAL );
SELECT QUAL_TYPE_USG_ID from JTF_TYPE_QTYPE_USGS_ALL WHERE terr_type_id = P_terr_type_id ;
SELECT 1
INTO l_dummy
FROM jtf_terr_usgs_all jtua
WHERE jtua.terr_id = p_terr_all_rec.PARENT_TERRITORY_ID
AND jtua.source_id = p_terr_usgs_tbl (1).source_id
AND jtua.org_id = p_terr_all_rec.org_id ;
SELECT 1
INTO l_dummy
FROM jtf_terr_types_all a,
jtf_terr_type_usgs_all b
WHERE a.enabled_flag = 'Y'
AND a.terr_type_id = b.terr_type_id
AND a.org_id = b.org_id
AND b.source_id = p_terr_usgs_tbl (1).source_id
AND b.org_id = p_terr_all_rec.org_id
AND a.terr_type_id = p_terr_all_rec.territory_type_id;
UPDATE JTF_TERR_ALL
SET terr_group_id = p_terr_all_rec.territory_group_id,
LAST_UPDATED_BY = p_terr_all_rec.LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = p_terr_all_rec.LAST_UPDATE_LOGIN,
NAME = p_terr_all_rec.NAME,
START_DATE_ACTIVE = p_terr_all_rec.START_DATE_ACTIVE,
END_DATE_ACTIVE = p_terr_all_rec.END_DATE_ACTIVE,
PARENT_TERRITORY_ID= p_terr_all_rec.PARENT_TERRITORY_ID,
RANK = p_terr_all_rec.RANK,
DESCRIPTION = p_terr_all_rec.DESCRIPTION,
NUM_WINNERS = p_terr_all_rec.NUM_WINNERS,
territory_type_id = p_terr_all_rec.territory_type_id
WHERE terr_id = p_terr_all_rec.terr_id;
P_Terr_QualTypeUsgs_Tbl (l_iterator).last_update_date := p_terr_all_rec.last_update_date;
P_Terr_QualTypeUsgs_Tbl (l_iterator).last_updated_by := p_terr_all_rec.last_updated_by;
P_Terr_QualTypeUsgs_Tbl (l_iterator).last_update_login := p_terr_all_rec.LAST_UPDATE_LOGIN;
JTF_TERR_PKG.Insert_Row(x_rowid => l_rowid,
x_terr_id => l_terr_id,
x_last_update_date => p_terr_all_rec.LAST_UPDATE_DATE,
x_last_updated_by => p_terr_all_rec.LAST_UPDATED_BY,
x_creation_date => p_terr_all_rec.CREATION_DATE,
x_created_by => p_terr_all_rec.CREATED_BY,
x_last_update_login => p_terr_all_rec.LAST_UPDATE_LOGIN,
x_request_id => p_terr_all_rec.request_id,
x_program_application_id => p_terr_all_rec.program_application_id,
x_program_id => p_terr_all_rec.program_id,
x_program_update_date => p_terr_all_rec.program_update_date,
x_application_short_name => p_terr_all_rec.APPLICATION_SHORT_NAME,
x_name => p_terr_all_rec.name,
x_enabled_flag => 'Y',
x_start_date_active => p_terr_all_rec.start_date_active,
x_end_date_active => p_terr_all_rec.end_date_active,
x_planned_flag => p_terr_all_rec.planned_flag,
x_parent_territory_id => p_terr_all_rec.parent_territory_id,
x_territory_type_id => p_terr_all_rec.territory_type_id,
x_template_territory_id => p_terr_all_rec.template_territory_id,
x_template_flag => p_terr_all_rec.template_flag,
x_escalation_territory_id => p_terr_all_rec.escalation_territory_id,
x_escalation_territory_flag => p_terr_all_rec.escalation_territory_flag,
x_overlap_allowed_flag => p_terr_all_rec.overlap_allowed_flag,
x_rank => p_terr_all_rec.rank,
x_description => p_terr_all_rec.description,
x_update_flag => p_terr_all_rec.update_flag,
x_auto_assign_resources_flag => p_terr_all_rec.AUTO_ASSIGN_RESOURCES_FLAG,
x_attribute_category => p_terr_all_rec.attribute_category,
x_attribute1 => p_terr_all_rec.attribute1,
x_attribute2 => p_terr_all_rec.attribute2,
x_attribute3 => p_terr_all_rec.attribute3,
x_attribute4 => p_terr_all_rec.attribute4,
x_attribute5 => p_terr_all_rec.attribute5,
x_attribute6 => p_terr_all_rec.attribute6,
x_attribute7 => p_terr_all_rec.attribute7,
x_attribute8 => p_terr_all_rec.attribute8,
x_attribute9 => p_terr_all_rec.attribute9,
x_attribute10 => p_terr_all_rec.attribute10,
x_attribute11 => p_terr_all_rec.attribute11,
x_attribute12 => p_terr_all_rec.attribute12,
x_attribute13 => p_terr_all_rec.attribute13,
x_attribute14 => p_terr_all_rec.attribute14,
x_attribute15 => p_terr_all_rec.attribute15,
x_org_id => P_terr_all_rec.ORG_ID,
x_num_winners => p_terr_all_rec.NUM_WINNERS,
x_num_qual => p_terr_all_rec.NUM_QUAL);
JTF_TERR_USGS_PKG.Insert_Row(x_Rowid => l_rowid,
x_TERR_USG_ID => l_terr_usg_id,
x_LAST_UPDATE_DATE => P_Terr_Usgs_Tbl(l_Counter).LAST_UPDATE_DATE,
x_LAST_UPDATED_BY => P_Terr_Usgs_Tbl(l_Counter).LAST_UPDATED_BY,
x_CREATION_DATE => P_Terr_Usgs_Tbl(l_Counter).CREATION_DATE,
x_CREATED_BY => P_Terr_Usgs_Tbl(l_Counter).CREATED_BY,
x_LAST_UPDATE_LOGIN => P_Terr_Usgs_Tbl(l_Counter).LAST_UPDATE_LOGIN,
x_TERR_ID => P_terr_id,
x_SOURCE_ID => P_Terr_Usgs_Tbl(l_Counter).source_id,
x_Org_Id => P_Terr_Usgs_Tbl(l_Counter).Org_Id);
JTF_TERR_QTYPE_USGS_PKG.Insert_Row(x_Rowid => l_rowid,
x_TERR_QTYPE_USG_ID => l_TERR_QUAL_TYPE_USG_ID,
x_LAST_UPDATED_BY => P_Terr_QualTypeUsgs_Rec.LAST_UPDATED_BY,
x_LAST_UPDATE_DATE => P_Terr_QualTypeUsgs_Rec.LAST_UPDATE_DATE,
x_CREATED_BY => P_Terr_QualTypeUsgs_Rec.CREATED_BY,
x_CREATION_DATE => P_Terr_QualTypeUsgs_Rec.CREATION_DATE,
x_LAST_UPDATE_LOGIN => P_Terr_QualTypeUsgs_Rec.LAST_UPDATE_LOGIN,
x_TERR_ID => p_terr_id,
x_QUAL_TYPE_USG_ID => P_Terr_QualTypeUsgs_Rec.QUAL_TYPE_USG_ID,
x_ORG_ID => P_Terr_QualTypeUsgs_Rec.ORG_ID);
/* update territory's number of qualifiers
*/
PROCEDURE update_terr_num_qual(p_terr_id IN NUMBER, p_qual_type_id IN NUMBER) AS
BEGIN
UPDATE jtf_terr_ALL jt
SET jt.num_qual = (
SELECT COUNT(jtq.qual_usg_id)
FROM jtf_terr_qual_ALL jtq, jtf_qual_usgs_ALL jqu, jtf_qual_type_usgs_ALL jqtu
WHERE jtq.terr_id = jt.terr_id
AND jtq.qual_usg_id = jqu.qual_usg_id
AND jqu.qual_type_usg_id = jqtu.qual_type_usg_id
AND jqtu.qual_type_id = p_qual_type_id
AND jqtu.qual_type_id <> -1001
)
WHERE jt.terr_id = p_terr_id;
END update_terr_num_qual;
JTF_TERR_QUAL_PKG.Insert_Row(x_Rowid => l_rowid,
x_TERR_QUAL_ID => l_terr_qual_id,
x_LAST_UPDATE_DATE => P_Terr_Qual_Rec.LAST_UPDATE_DATE,
x_LAST_UPDATED_BY => P_Terr_Qual_Rec.LAST_UPDATED_BY,
x_CREATION_DATE => P_Terr_Qual_Rec.CREATION_DATE,
x_CREATED_BY => P_Terr_Qual_Rec.CREATED_BY,
x_LAST_UPDATE_LOGIN => P_Terr_Qual_Rec.LAST_UPDATE_LOGIN,
x_TERR_ID => P_terr_id,
x_QUAL_USG_ID => P_Terr_Qual_Rec.QUAL_USG_ID,
x_USE_TO_NAME_FLAG => P_Terr_Qual_Rec.USE_TO_NAME_FLAG,
x_GENERATE_FLAG => P_Terr_Qual_Rec.GENERATE_FLAG,
x_OVERLAP_ALLOWED_FLAG => P_Terr_Qual_Rec.OVERLAP_ALLOWED_FLAG,
x_QUALIFIER_MODE => P_Terr_Qual_Rec.QUALIFIER_MODE,
x_ORG_ID => P_Terr_Qual_Rec.ORG_ID);
/* update Sales territory's number of Account qualifiers
*/
--update_terr_num_qual(p_terr_id, -1002);
JTF_TERR_VALUES_PKG.Insert_Row(x_Rowid => l_rowid,
x_TERR_VALUE_ID => l_terr_value_id,
x_LAST_UPDATED_BY => P_Terr_Value_Rec.LAST_UPDATED_BY,
x_LAST_UPDATE_DATE => P_Terr_Value_Rec.LAST_UPDATE_DATE,
x_CREATED_BY => P_Terr_Value_Rec.CREATED_BY,
x_CREATION_DATE => P_Terr_Value_Rec.CREATION_DATE,
x_LAST_UPDATE_LOGIN => P_Terr_Value_Rec.LAST_UPDATE_LOGIN,
x_TERR_QUAL_ID => P_terr_qual_id,
x_INCLUDE_FLAG => P_Terr_Value_Rec.INCLUDE_FLAG,
x_COMPARISON_OPERATOR => P_Terr_Value_Rec.COMPARISON_OPERATOR,
x_LOW_VALUE_CHAR => P_Terr_Value_Rec.LOW_VALUE_CHAR,
x_HIGH_VALUE_CHAR => P_Terr_Value_Rec.HIGH_VALUE_CHAR,
x_LOW_VALUE_NUMBER => P_Terr_Value_Rec.LOW_VALUE_NUMBER,
x_HIGH_VALUE_NUMBER => P_Terr_Value_Rec.HIGH_VALUE_NUMBER,
x_VALUE_SET => P_Terr_Value_Rec.VALUE_SET,
x_INTEREST_TYPE_ID => P_Terr_Value_Rec.INTEREST_TYPE_ID,
x_PRIMARY_INTEREST_CODE_ID => P_Terr_Value_Rec.PRIMARY_INTEREST_CODE_ID,
x_SECONDARY_INTEREST_CODE_ID => P_Terr_Value_Rec.SECONDARY_INTEREST_CODE_ID,
x_CURRENCY_CODE => P_Terr_Value_Rec.CURRENCY_CODE,
x_ID_USED_FLAG => P_Terr_Value_Rec.ID_USED_FLAG,
x_LOW_VALUE_CHAR_ID => P_Terr_Value_Rec.LOW_VALUE_CHAR_ID,
x_ORG_ID => P_Terr_Value_Rec.ORG_ID,
x_CNR_GROUP_ID => p_terr_value_rec.CNR_GROUP_ID,
x_VALUE1_ID => p_terr_value_rec.VALUE1_ID,
x_VALUE2_ID => p_terr_value_rec.VALUE2_ID,
x_VALUE3_ID => p_terr_value_rec.VALUE3_ID,
x_VALUE4_ID => p_terr_value_rec.VALUE4_ID );
PROCEDURE Update_Territory_Record
( 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,
P_Terr_All_Rec IN Terr_All_Rec_Type := G_Miss_Terr_All_Rec,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_Terr_All_Out_Rec OUT NOCOPY Terr_All_Out_Rec_Type
)
AS
Cursor C_GetTerritory(l_terr_id Number) IS
Select Rowid,
TERR_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
APPLICATION_SHORT_NAME,
NAME,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PLANNED_FLAG,
PARENT_TERRITORY_ID,
TERRITORY_TYPE_ID,
TEMPLATE_TERRITORY_ID,
TEMPLATE_FLAG,
ESCALATION_TERRITORY_ID,
ESCALATION_TERRITORY_FLAG,
OVERLAP_ALLOWED_FLAG,
RANK,
DESCRIPTION,
UPDATE_FLAG,
AUTO_ASSIGN_RESOURCES_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORG_ID,
NUM_WINNERS,
NUM_QUAL
From JTF_TERR_ALL
Where TERR_ID = l_terr_id
For Update NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_territory';
FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_TerrRec_Update');
Validate_TerrRec_Update (p_init_msg_list => FND_API.G_FALSE,
x_Return_Status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_Terr_All_Rec => P_Terr_All_Rec);
l_ref_terr_all_rec.LAST_UPDATE_DATE,
l_ref_terr_all_rec.LAST_UPDATED_BY,
l_ref_terr_all_rec.CREATION_DATE,
l_ref_terr_all_rec.CREATED_BY,
l_ref_terr_all_rec.LAST_UPDATE_LOGIN,
l_ref_terr_all_rec.REQUEST_ID,
l_ref_terr_all_rec.PROGRAM_APPLICATION_ID,
l_ref_terr_all_rec.PROGRAM_ID,
l_ref_terr_all_rec.PROGRAM_UPDATE_DATE,
l_ref_terr_all_rec.APPLICATION_SHORT_NAME,
l_ref_terr_all_rec.NAME,
l_ref_terr_all_rec.ENABLED_FLAG,
l_ref_terr_all_rec.START_DATE_ACTIVE,
l_ref_terr_all_rec.END_DATE_ACTIVE,
l_ref_terr_all_rec.PLANNED_FLAG,
l_ref_terr_all_rec.PARENT_TERRITORY_ID,
l_ref_terr_all_rec.TERRITORY_TYPE_ID,
l_ref_terr_all_rec.TEMPLATE_TERRITORY_ID,
l_ref_terr_all_rec.TEMPLATE_FLAG,
l_ref_terr_all_rec.ESCALATION_TERRITORY_ID,
l_ref_terr_all_rec.ESCALATION_TERRITORY_FLAG,
l_ref_terr_all_rec.OVERLAP_ALLOWED_FLAG,
l_ref_terr_all_rec.RANK,
l_ref_terr_all_rec.DESCRIPTION,
l_ref_terr_all_rec.UPDATE_FLAG,
l_ref_terr_all_rec.AUTO_ASSIGN_RESOURCES_FLAG,
l_ref_terr_all_rec.ATTRIBUTE_CATEGORY,
l_ref_terr_all_rec.ATTRIBUTE1,
l_ref_terr_all_rec.ATTRIBUTE2,
l_ref_terr_all_rec.ATTRIBUTE3,
l_ref_terr_all_rec.ATTRIBUTE4,
l_ref_terr_all_rec.ATTRIBUTE5,
l_ref_terr_all_rec.ATTRIBUTE6,
l_ref_terr_all_rec.ATTRIBUTE7,
l_ref_terr_all_rec.ATTRIBUTE8,
l_ref_terr_all_rec.ATTRIBUTE9,
l_ref_terr_all_rec.ATTRIBUTE10,
l_ref_terr_all_rec.ATTRIBUTE11,
l_ref_terr_all_rec.ATTRIBUTE12,
l_ref_terr_all_rec.ATTRIBUTE13,
l_ref_terr_all_rec.ATTRIBUTE14,
l_ref_terr_all_rec.ATTRIBUTE15,
l_ref_terr_all_rec.ORG_ID,
l_ref_terr_all_rec.NUM_WINNERS,
l_ref_terr_all_rec.NUM_QUAL;
JTF_TERR_PKG.Update_Row(x_rowid => l_rowid,
x_terr_id => p_terr_all_rec.terr_id,
x_last_update_date => p_terr_all_rec.LAST_UPDATE_DATE,
x_last_updated_by => p_terr_all_rec.LAST_UPDATED_BY,
x_creation_date => p_terr_all_rec.CREATION_DATE,
x_created_by => p_terr_all_rec.CREATED_BY,
x_last_update_login => p_terr_all_rec.LAST_UPDATE_LOGIN,
x_request_id => null,
x_program_application_id => null,
x_program_id => null,
x_program_update_date => null,
x_application_short_name => p_terr_all_rec.application_short_name,
x_name => p_terr_all_rec.name,
-- x_enabled_flag => p_terr_all_rec.enabled_flag,
x_start_date_active => p_terr_all_rec.start_date_active,
x_end_date_active => p_terr_all_rec.end_date_active,
x_planned_flag => p_terr_all_rec.planned_flag,
x_parent_territory_id => p_terr_all_rec.parent_territory_id,
--One Can't update the Territory Type in R12. -- VPALLE
-- x_territory_type_id => p_terr_all_rec.territory_type_id,
x_template_territory_id => p_terr_all_rec.template_territory_id,
x_template_flag => p_terr_all_rec.template_flag,
x_escalation_territory_id => p_terr_all_rec.escalation_territory_id,
x_escalation_territory_flag => p_terr_all_rec.escalation_territory_flag,
x_overlap_allowed_flag => p_terr_all_rec.overlap_allowed_flag,
x_rank => p_terr_all_rec.rank,
x_description => p_terr_all_rec.description,
x_update_flag => p_terr_all_rec.update_flag,
x_auto_assign_resources_flag => p_terr_all_rec.AUTO_ASSIGN_RESOURCES_FLAG,
x_attribute_category => p_terr_all_rec.attribute_category,
x_attribute1 => p_terr_all_rec.attribute1,
x_attribute2 => p_terr_all_rec.attribute2,
x_attribute3 => p_terr_all_rec.attribute3,
x_attribute4 => p_terr_all_rec.attribute4,
x_attribute5 => p_terr_all_rec.attribute5,
x_attribute6 => p_terr_all_rec.attribute6,
x_attribute7 => p_terr_all_rec.attribute7,
x_attribute8 => p_terr_all_rec.attribute8,
x_attribute9 => p_terr_all_rec.attribute9,
x_attribute10 => p_terr_all_rec.attribute10,
x_attribute11 => p_terr_all_rec.attribute11,
x_attribute12 => p_terr_all_rec.attribute12,
x_attribute13 => p_terr_all_rec.attribute13,
x_attribute14 => p_terr_all_rec.attribute14,
x_attribute15 => p_terr_all_rec.attribute15,
-- We can't update the ORG_ID -- VPALLE
x_org_id => FND_API.G_MISS_NUM,
x_num_winners => p_terr_all_rec.NUM_WINNERS,
x_num_qual => p_terr_all_rec.NUM_QUAL);
FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Update error inside Update_Territory_Record');
End Update_territory_Record;
PROCEDURE Update_Territory_Usages
( 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,
P_Terr_Usgs_Rec IN Terr_Usgs_Rec_Type := G_MISS_TERR_USGS_REC,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_Terr_Usgs_Out_Rec OUT NOCOPY Terr_Usgs_Out_Rec_Type
)
AS
Cursor C_GetTerritoryUsage(l_terr_usg_id Number) IS
Select Rowid,
TERR_USG_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_ID,
SOURCE_ID
From JTF_TERR_USGS_ALL
Where terr_usg_id = l_terr_usg_id
FOR Update NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_territory_Usages';
l_ref_terr_Usg_rec.LAST_UPDATE_DATE,
l_ref_terr_Usg_rec.LAST_UPDATED_BY,
l_ref_terr_Usg_rec.CREATION_DATE,
l_ref_terr_Usg_rec.CREATED_BY,
l_ref_terr_Usg_rec.LAST_UPDATE_LOGIN,
l_ref_terr_Usg_rec.TERR_ID,
l_ref_terr_Usg_rec.SOURCE_ID;
JTF_TERR_USGS_PKG.Update_Row(x_Rowid => l_rowid,
x_TERR_USG_ID => P_Terr_Usgs_Rec.terr_usg_id,
x_LAST_UPDATE_DATE => P_Terr_Usgs_Rec.LAST_UPDATE_DATE,
x_LAST_UPDATED_BY => P_Terr_Usgs_Rec.LAST_UPDATED_BY,
x_CREATION_DATE => P_Terr_Usgs_Rec.CREATION_DATE,
x_CREATED_BY => P_Terr_Usgs_Rec.CREATED_BY,
x_LAST_UPDATE_LOGIN => P_Terr_Usgs_Rec.LAST_UPDATE_LOGIN,
x_TERR_ID => P_Terr_Usgs_Rec.Terr_Id,
x_SOURCE_ID => P_Terr_Usgs_Rec.source_id,
x_ORG_ID => P_Terr_Usgs_Rec.org_id);
FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Update error inside Update_Territory_Usages');
End Update_Territory_Usages;
PROCEDURE Update_Territory_Usages
( 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,
P_Terr_Usgs_Tbl IN Terr_Usgs_Tbl_Type := G_MISS_Terr_Usgs_Tbl,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_Terr_Usgs_Out_Tbl OUT NOCOPY Terr_Usgs_Out_Tbl_Type
)
AS
l_api_name CONSTANT VARCHAR2(30) := 'Update_territory_Usages (Tbl)';
Update_Territory_Usages( P_Api_Version_Number => P_Api_Version_Number,
P_Init_Msg_List => P_Init_Msg_List,
P_Commit => P_Commit,
p_validation_level => p_validation_level,
P_Terr_Usgs_Rec => P_Terr_Usgs_Tbl(l_counter),
X_Return_Status => l_Return_Status,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data,
X_Terr_Usgs_Out_Rec => l_Terr_Usg_Out_Rec);
END Update_Territory_Usages;
PROCEDURE Update_Terr_QualType_Usage
( 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,
P_Terr_QualTypeUsgs_Rec IN Terr_QualTypeUsgs_Rec_Type := G_Miss_Terr_QualTypeUsgs_Rec,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_Terr_QualTypeUsgs_Out_Rec OUT NOCOPY Terr_QualTypeUsgs_Out_Rec_Type
)
AS
Cursor C_GetTerrQualTypeUsgs(l_terr_qual_type_usg_id Number) IS
Select rowid,
TERR_QTYPE_USG_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
TERR_ID,
QUAL_TYPE_USG_ID
From JTF_TERR_QTYPE_USGS_ALL
Where terr_qtype_usg_id = l_terr_qual_type_usg_id
FOR Update NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_QualType_Usage';
l_ref_Terr_QualTypeUsgs_Rec.LAST_UPDATED_BY,
l_ref_Terr_QualTypeUsgs_Rec.LAST_UPDATE_DATE,
l_ref_Terr_QualTypeUsgs_Rec.CREATED_BY,
l_ref_Terr_QualTypeUsgs_Rec.CREATION_DATE,
l_ref_Terr_QualTypeUsgs_Rec.LAST_UPDATE_LOGIN,
l_ref_Terr_QualTypeUsgs_Rec.TERR_ID,
l_ref_Terr_QualTypeUsgs_Rec.QUAL_TYPE_USG_ID;
JTF_TERR_QTYPE_USGS_PKG.Update_Row(x_Rowid => l_rowid,
x_TERR_QTYPE_USG_ID => P_Terr_QualTypeUsgs_Rec.TERR_QUAL_TYPE_USG_ID,
x_LAST_UPDATED_BY => P_Terr_QualTypeUsgs_Rec.LAST_UPDATED_BY,
x_LAST_UPDATE_DATE => P_Terr_QualTypeUsgs_Rec.LAST_UPDATE_DATE,
x_CREATED_BY => P_Terr_QualTypeUsgs_Rec.CREATED_BY,
x_CREATION_DATE => P_Terr_QualTypeUsgs_Rec.CREATION_DATE,
x_LAST_UPDATE_LOGIN => P_Terr_QualTypeUsgs_Rec.LAST_UPDATE_LOGIN,
x_TERR_ID => P_Terr_QualTypeUsgs_Rec.terr_id,
x_QUAL_TYPE_USG_ID => P_Terr_QualTypeUsgs_Rec.QUAL_TYPE_USG_ID,
x_ORG_ID => P_Terr_QualTypeUsgs_Rec.ORG_ID);
FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Error: Inside Update_Terr_QualType_Usage');
End Update_Terr_QualType_Usage;
PROCEDURE Update_Terr_QualType_Usage
( 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,
P_Terr_QualTypeUsgs_Tbl IN Terr_QualTypeUsgs_Tbl_Type := G_Miss_Terr_QualTypeUsgs_Tbl,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_Terr_QualTypeUsgs_Out_Tbl OUT NOCOPY Terr_QualTypeUsgs_Out_Tbl_Type
)
AS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_QualType_Usage Tbl';
Update_Terr_QualType_Usage( P_Api_Version_Number => P_Api_Version_Number,
P_Init_Msg_List => P_Init_Msg_List,
P_Commit => P_Commit,
p_validation_level => p_validation_level,
P_Terr_QualTypeUsgs_Rec => P_Terr_QualTypeUsgs_Tbl(l_counter),
X_Return_Status => l_Return_Status,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data,
X_Terr_QualTypeUsgs_Out_Rec => l_Terr_QTypUsg_Out_Rec);
END Update_Terr_QualType_Usage;
PROCEDURE Update_Terr_Qualifier
( 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,
P_Terr_Qual_Rec IN Terr_Qual_Rec_Type := G_Miss_Terr_Qual_Rec,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_Terr_Qual_Out_Rec OUT NOCOPY Terr_Qual_Out_Rec_Type
)
AS
Cursor C_GetTerrQualifier(l_terr_qual_id Number) IS
Select Rowid,
TERR_QUAL_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_ID,
QUAL_USG_ID,
USE_TO_NAME_FLAG,
GENERATE_FLAG,
OVERLAP_ALLOWED_FLAG
From JTF_TERR_QUAL_ALL
Where terr_qual_id = l_terr_qual_id
FOR Update NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Qualifier';
l_ref_Terr_Qual_Rec.LAST_UPDATE_DATE,
l_ref_Terr_Qual_Rec.LAST_UPDATED_BY,
l_ref_Terr_Qual_Rec.CREATION_DATE,
l_ref_Terr_Qual_Rec.CREATED_BY,
l_ref_Terr_Qual_Rec.LAST_UPDATE_LOGIN,
l_ref_Terr_Qual_Rec.TERR_ID,
l_ref_Terr_Qual_Rec.QUAL_USG_ID,
l_ref_Terr_Qual_Rec.USE_TO_NAME_FLAG,
l_ref_Terr_Qual_Rec.GENERATE_FLAG,
l_ref_Terr_Qual_Rec.OVERLAP_ALLOWED_FLAG;
JTF_TERR_QUAL_PKG.Update_Row(x_Rowid => l_rowid,
x_TERR_QUAL_ID => P_Terr_Qual_Rec.terr_qual_id,
x_LAST_UPDATE_DATE => P_Terr_Qual_Rec.LAST_UPDATE_DATE,
x_LAST_UPDATED_BY => P_Terr_Qual_Rec.LAST_UPDATED_BY,
x_CREATION_DATE => P_Terr_Qual_Rec.creation_date,
x_CREATED_BY => P_Terr_Qual_Rec.created_by,
x_LAST_UPDATE_LOGIN => P_Terr_Qual_Rec.LAST_UPDATE_LOGIN,
x_TERR_ID => P_Terr_Qual_Rec.Terr_Id,
x_QUAL_USG_ID => P_Terr_Qual_Rec.QUAL_USG_ID,
x_USE_TO_NAME_FLAG => P_Terr_Qual_Rec.USE_TO_NAME_FLAG,
x_GENERATE_FLAG => P_Terr_Qual_Rec.GENERATE_FLAG,
x_OVERLAP_ALLOWED_FLAG => P_Terr_Qual_Rec.OVERLAP_ALLOWED_FLAG,
x_QUALIFIER_MODE => P_Terr_Qual_Rec.QUALIFIER_MODE,
-- We can't update the ORG_ID -- VPALLE
x_ORG_ID => FND_API.G_MISS_NUM );
FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Update error inside Update_Terr_Qualifier');
End Update_Terr_Qualifier;
PROCEDURE Update_Terr_Qualifier
( 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,
P_Terr_Qual_Tbl IN Terr_Qual_Tbl_Type := G_Miss_Terr_Qual_Tbl,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_Terr_Qual_Out_Tbl OUT NOCOPY Terr_Qual_Out_Tbl_Type
)
AS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Qualifier (Tbl)';
Update_Terr_Qualifier( P_Api_Version_Number => P_Api_Version_Number,
P_Init_Msg_List => P_Init_Msg_List,
P_Commit => P_Commit,
p_validation_level => p_validation_level,
P_Terr_Qual_Rec => P_Terr_Qual_Tbl(l_counter),
X_Return_Status => l_Return_Status,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data,
X_Terr_Qual_Out_Rec => l_Terr_Qual_Out_Rec);
END Update_Terr_Qualifier;
PROCEDURE Update_Terr_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,
P_Terr_Value_Rec IN Terr_Values_Rec_Type := G_Miss_Terr_Values_Rec,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_Terr_Value_Out_Rec OUT NOCOPY Terr_Values_Out_Rec_Type
)
AS
Cursor C_GetTerritoryValue(l_TERR_VALUE_ID Number) IS
Select Rowid,
TERR_VALUE_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
TERR_QUAL_ID,
INCLUDE_FLAG,
COMPARISON_OPERATOR,
LOW_VALUE_CHAR,
HIGH_VALUE_CHAR,
LOW_VALUE_NUMBER,
HIGH_VALUE_NUMBER,
VALUE_SET,
INTEREST_TYPE_ID,
PRIMARY_INTEREST_CODE_ID,
SECONDARY_INTEREST_CODE_ID,
CURRENCY_CODE,
ID_USED_FLAG,
LOW_VALUE_CHAR_ID,
ORG_ID,
CNR_GROUP_ID
From JTF_TERR_VALUES_ALL
Where TERR_VALUE_ID = l_TERR_VALUE_ID
For Update NOWAIT;
Select TERR_QUAL_ID
From JTF_TERR_VALUES_ALL
Where TERR_VALUE_ID = l_TERR_VALUE_ID ;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Value';
Check_duplicate_Value_update(p_init_msg_list => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_terr_qual_id => nvl(P_Terr_Value_Rec.terr_qual_id, l_ref_Terr_Value_Rec.TERR_QUAL_ID),
p_Terr_Value_Rec => P_Terr_Value_Rec);
l_ref_Terr_Value_Rec.LAST_UPDATED_BY,
l_ref_Terr_Value_Rec.LAST_UPDATE_DATE,
l_ref_Terr_Value_Rec.CREATED_BY,
l_ref_Terr_Value_Rec.CREATION_DATE,
l_ref_Terr_Value_Rec.LAST_UPDATE_LOGIN,
l_ref_Terr_Value_Rec.TERR_QUAL_ID,
l_ref_Terr_Value_Rec.INCLUDE_FLAG,
l_ref_Terr_Value_Rec.COMPARISON_OPERATOR,
l_ref_Terr_Value_Rec.LOW_VALUE_CHAR,
l_ref_Terr_Value_Rec.HIGH_VALUE_CHAR,
l_ref_Terr_Value_Rec.LOW_VALUE_NUMBER,
l_ref_Terr_Value_Rec.HIGH_VALUE_NUMBER,
l_ref_Terr_Value_Rec.VALUE_SET,
l_ref_Terr_Value_Rec.INTEREST_TYPE_ID,
l_ref_Terr_Value_Rec.PRIMARY_INTEREST_CODE_ID,
l_ref_Terr_Value_Rec.SECONDARY_INTEREST_CODE_ID,
l_ref_Terr_Value_Rec.CURRENCY_CODE,
l_ref_Terr_Value_Rec.ID_USED_FLAG,
l_ref_Terr_Value_Rec.LOW_VALUE_CHAR_ID,
l_ref_Terr_Value_Rec.ORG_ID,
l_ref_Terr_Value_Rec.CNR_GROUP_ID;
JTF_TERR_VALUES_PKG.Update_Row(x_Rowid => l_rowid,
x_TERR_VALUE_ID => P_Terr_Value_Rec.TERR_VALUE_ID,
x_LAST_UPDATED_BY => P_Terr_Value_Rec.LAST_UPDATED_BY,
x_LAST_UPDATE_DATE => P_Terr_Value_Rec.LAST_UPDATE_DATE,
x_CREATED_BY => P_Terr_Value_Rec.created_by,
x_CREATION_DATE => P_Terr_Value_Rec.creation_date,
x_LAST_UPDATE_LOGIN => P_Terr_Value_Rec.LAST_UPDATE_LOGIN,
x_TERR_QUAL_ID => P_Terr_Value_Rec.terr_qual_id,
x_INCLUDE_FLAG => P_Terr_Value_Rec.INCLUDE_FLAG,
x_COMPARISON_OPERATOR => P_Terr_Value_Rec.COMPARISON_OPERATOR,
x_LOW_VALUE_CHAR => P_Terr_Value_Rec.LOW_VALUE_CHAR,
x_HIGH_VALUE_CHAR => P_Terr_Value_Rec.HIGH_VALUE_CHAR,
x_LOW_VALUE_NUMBER => P_Terr_Value_Rec.LOW_VALUE_NUMBER,
x_HIGH_VALUE_NUMBER => P_Terr_Value_Rec.HIGH_VALUE_NUMBER,
x_VALUE_SET => P_Terr_Value_Rec.VALUE_SET,
x_INTEREST_TYPE_ID => P_Terr_Value_Rec.INTEREST_TYPE_ID,
x_PRIMARY_INTEREST_CODE_ID => P_Terr_Value_Rec.PRIMARY_INTEREST_CODE_ID,
x_SECONDARY_INTEREST_CODE_ID => P_Terr_Value_Rec.SECONDARY_INTEREST_CODE_ID,
x_CURRENCY_CODE => P_Terr_Value_Rec.CURRENCY_CODE,
x_ID_USED_FLAG => P_Terr_Value_Rec.ID_USED_FLAG,
x_LOW_VALUE_CHAR_ID => P_Terr_Value_Rec.LOW_VALUE_CHAR_ID,
-- Can't update the ORG_ID -- VPALLE
x_ORG_ID => FND_API.G_MISS_NUM,
x_CNR_GROUP_ID => P_Terr_Value_Rec.CNR_GROUP_ID,
x_VALUE1_ID => p_terr_value_rec.VALUE1_ID,
x_VALUE2_ID => p_terr_value_rec.VALUE2_ID,
x_VALUE3_ID => p_terr_value_rec.VALUE3_ID,
x_VALUE4_ID => p_terr_value_rec.VALUE4_ID );
FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Update error inside Update_Terr_Value');
End Update_Terr_Value;
PROCEDURE Update_Terr_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,
P_Terr_Value_Tbl IN Terr_Values_Tbl_Type := G_Miss_Terr_Values_Tbl,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_Terr_Value_Out_Tbl OUT NOCOPY Terr_Values_Out_Tbl_Type
)
AS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Value (Tbl)';
Update_Terr_Value( P_Api_Version_Number => P_Api_Version_Number,
P_Init_Msg_List => P_Init_Msg_List,
P_Commit => P_Commit,
p_validation_level => p_validation_level,
P_Terr_Value_Rec => P_Terr_Value_Tbl(l_counter),
X_Return_Status => l_Return_Status,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data,
X_Terr_Value_Out_Rec => l_Terr_Value_Out_Rec);
End Update_Terr_Value;
PROCEDURE Delete_territory_Record
(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_Terr_Id IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY VARCHAR2,
X_Msg_Data OUT NOCOPY VARCHAR2)
AS
l_row_count NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_territory_Record';
SAVEPOINT Delete_territory_Record_Pvt;
JTF_TERR_PKG.Delete_Row(x_terr_Id => P_Terr_Id);
FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
ROLLBACK TO Delete_territory_Record_Pvt;
FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Delete error inside Delete_territory_Record');
END Delete_territory_Record;
PROCEDURE Delete_Territory_Usages
(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_Terr_usg_Id IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY VARCHAR2,
X_Msg_Data OUT NOCOPY VARCHAR2)
AS
l_row_count NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Territory_Usages';
SAVEPOINT Delete_Territory_Usages_Pvt;
JTF_TERR_USGS_PKG.Delete_Row(x_TERR_USG_ID => P_Terr_usg_Id);
FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
ROLLBACK TO Delete_Territory_Usages_Pvt;
FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Delete error inside Delete_Territory_Usages');
END Delete_Territory_Usages;
PROCEDURE Delete_Terr_QualType_Usage
(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_Terr_Qual_Type_Usg_Id IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY VARCHAR2,
X_Msg_Data OUT NOCOPY VARCHAR2)
AS
l_row_count NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Terr_QualType_Usage';
SAVEPOINT Delete_Terr_QualType_Usage_Pvt;
JTF_TERR_QTYPE_USGS_PKG.Delete_Row(x_terr_qtype_usg_id => P_Terr_Qual_Type_Usg_Id);
FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
ROLLBACK TO Delete_Terr_QualType_Usage_Pvt;
FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Delete error inside Delete_Terr_QualType_Usage');
END Delete_Terr_QualType_Usage;
PROCEDURE Delete_Terr_Qualifier
(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_Terr_Qual_Id IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY VARCHAR2,
X_Msg_Data OUT NOCOPY VARCHAR2)
AS
l_row_count NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Terr_Qualifier';
/* for BULK deletes */
TYPE NumTab IS TABLE OF NUMBER;
deletedIds NUMTAB;
SAVEPOINT Delete_Terr_Qualifier_Pvt;
SELECT terr_id
INTO lp_terr_id
FROM jtf_terr_qual_ALL jtq
WHERE jtq.terr_qual_id = p_terr_qual_id;
/* Do a bulk delete of Territory Qualifier VALUES */
DELETE jtf_terr_values_ALL
WHERE terr_qual_id = p_terr_qual_id
RETURNING terr_value_id
BULK COLLECT INTO deletedIds;
JTF_TERR_QUAL_PKG.Delete_Row(x_terr_qual_id => P_Terr_Qual_Id );
FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
/* update Sales territory's number of Account qualifiers
*/
-- update_terr_num_qual(lp_terr_id, -1002);
ROLLBACK TO Delete_Terr_Qualifier_Pvt;
FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Delete error inside Delete_Terr_Qualifier');
END Delete_Terr_Qualifier;
PROCEDURE Delete_Terr_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_Terr_Value_Id IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY VARCHAR2,
X_Msg_Data OUT NOCOPY VARCHAR2)
AS
l_row_count NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Terr_Value';
SAVEPOINT Delete_Terr_Value_Pvt;
JTF_TERR_VALUES_PKG.Delete_Row(X_TERR_VALUE_ID => P_Terr_Value_Id);
FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
ROLLBACK TO Delete_Terr_Value_Pvt;
FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Delete error inside Delete_Terr_Value');
End Delete_Terr_Value;
/* SELECT 'x'
into l_Temp
from jtf_qual_usgs_ALL jqu,
jtf_qual_type_usgs_ALL jqtu,
jtf_terr_qtype_usgs_ALL jtqu
where jtqu.terr_id = p_Terr_Id and
jqtu.qual_type_usg_id = jtqu.qual_type_usg_id and
jqu.qual_usg_id = P_Terr_Qual_Rec.Qual_Usg_Id and
jqu.enabled_flag = 'Y' and
jqtu.qual_type_id IN ( SELECT related_id
FROM jtf_qual_type_denorm_v
WHERE qual_type_id = jqtu.qual_type_id )
AND ROWNUM < 2; */
SELECT 'x'
INTO l_Temp
FROM jtf_terr_all jta,
jtf_terr_type_qual_all jtqa,
jtf_qual_usgs_all jqua
WHERE jta.terr_id = p_Terr_Id
AND jta.territory_type_id = jtqa.terr_type_id
AND jtqa.qual_usg_id = jqua.qual_usg_id
AND jqua.org_id = jtqa.org_id
AND jqua.enabled_flag = 'Y'
AND jqua.qual_usg_id = P_Terr_Qual_Rec.Qual_Usg_Id
AND ROWNUM < 2;
SELECT COUNT(*) INTO l_qual_count
FROM JTF_TERR_QUAL_ALL
WHERE TERR_ID = p_Terr_Id
AND QUAL_USG_ID = P_Terr_Qual_Rec.Qual_Usg_Id ;
IF ( P_Terr_Qual_Rec.LAST_UPDATED_BY is NULL OR
P_Terr_Qual_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_Qual_Rec.LAST_UPDATE_DATE IS NULL OR
P_Terr_Qual_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_Qual_Rec.LAST_UPDATE_LOGIN is NULL OR
P_Terr_Qual_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' );
Select count(*) INTO l_ter_name_count
FROM JTF_TERR_ALL childterr,
HR_OPERATING_UNITS hr,
JTF_TERR_ALL parentterr
WHERE childterr.org_id = hr.organization_id
AND childterr.parent_territory_id = parentTerr.terr_id
AND childterr.org_id = parentTerr.org_id
AND childterr.NAME = p_Terr_All_Rec.NAME
AND childterr.terr_id <> p_Terr_All_Rec.PARENT_TERRITORY_ID
AND ( ((childterr.END_DATE_ACTIVE >= p_Terr_All_Rec.START_DATE_ACTIVE and childterr.START_DATE_ACTIVE <= p_Terr_All_Rec.START_DATE_ACTIVE)
or (childterr.END_DATE_ACTIVE >= p_Terr_All_Rec.END_DATE_ACTIVE and childterr.START_DATE_ACTIVE <= p_Terr_All_Rec.END_DATE_ACTIVE ))
or ((childterr.START_DATE_ACTIVE >= p_Terr_All_Rec.START_DATE_ACTIVE and childterr.START_DATE_ACTIVE <= p_Terr_All_Rec.END_DATE_ACTIVE)
or (childterr.END_DATE_ACTIVE >= p_Terr_All_Rec.START_DATE_ACTIVE and childterr.END_DATE_ACTIVE <= p_Terr_All_Rec.END_DATE_ACTIVE)));
IF ( p_Terr_All_Rec.LAST_UPDATED_BY is NULL OR
p_Terr_All_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_All_Rec.LAST_UPDATE_DATE IS NULL OR
p_Terr_All_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' );
PROCEDURE Validate_TerrRec_Update
(p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
x_Return_Status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_Terr_All_Rec IN Terr_All_Rec_Type := G_Miss_Terr_All_Rec)
AS
l_Return_Status VARCHAR2(1);
SELECT name,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PARENT_TERRITORY_ID
INTO l_ter_name,
l_start_date_active,
l_end_date_active,
l_parent_territory_id
FROM JTF_TERR_ALL
WHERE TERR_ID = p_Terr_All_Rec.TERR_ID;
Select count(*) INTO l_ter_name_count
FROM JTF_TERR_ALL childterr,
HR_OPERATING_UNITS hr,
JTF_TERR_ALL parentterr
WHERE childterr.org_id = hr.organization_id
AND childterr.parent_territory_id = parentTerr.terr_id
AND childterr.org_id = parentTerr.org_id
AND childterr.NAME = p_Terr_All_Rec.NAME
AND childterr.terr_id <> l_parent_territory_id
AND ( ((childterr.END_DATE_ACTIVE >= l_start_date_active and childterr.START_DATE_ACTIVE <= l_start_date_active)
or (childterr.END_DATE_ACTIVE >= l_end_date_active and childterr.START_DATE_ACTIVE <= l_end_date_active ))
or ((childterr.START_DATE_ACTIVE >= l_start_date_active and childterr.START_DATE_ACTIVE <= l_end_date_active)
or (childterr.END_DATE_ACTIVE >= l_start_date_active and childterr.END_DATE_ACTIVE <= l_end_date_active)));
SELECT jta.start_date_active,jta.end_date_active
INTO l_pterr_start_date,l_pterr_end_date
FROM jtf_terr_all jta
WHERE jta.terr_id = l_parent_territory_id ;
SELECT 1
INTO l_dummy
FROM jtf_terr_usgs_all childusg, jtf_terr_usgs_all parusg
WHERE childusg.terr_id = p_terr_all_rec.TERR_ID
AND parusg.terr_id = p_terr_all_rec.PARENT_TERRITORY_ID
AND childusg.source_id = parusg.source_id
AND childusg.org_id = parusg.org_id ;
IF ( p_Terr_All_Rec.LAST_UPDATED_BY is NULL OR
p_Terr_All_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_All_Rec.LAST_UPDATE_DATE IS NULL OR
p_Terr_All_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_All_Rec.LAST_UPDATE_LOGIN is NULL OR
p_Terr_All_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' );
FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_TerrRec_Update' );
END Validate_TerrRec_Update;
SELECT START_DATE_ACTIVE, END_DATE_ACTIVE INTO PSTART_DATE_ACTIVE, PEND_DATE_ACTIVE
FROM JTF_TERR_ALL
WHERE TERR_ID = p_Terr_All_Rec.PARENT_TERRITORY_ID;
IF ( p_Terr_Usgs_rec.LAST_UPDATED_BY is NULL OR
p_Terr_Usgs_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_Usgs_rec.LAST_UPDATE_DATE IS NULL OR
p_Terr_Usgs_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' );
Select 1
into l_dummy
From jtf_terr_usgs_ALL jtu, jtf_qual_type_usgs_ALL jqtu
where jtu.terr_id = p_Terr_Id and
jqtu.source_id = jtu.source_id and
jqtu.qual_type_usg_id = l_qual_Type_Usg_id;
IF ( p_Terr_QualTypeUsgs_Rec.LAST_UPDATED_BY is NULL OR
p_Terr_QualTypeUsgs_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_QualTypeUsgs_Rec.LAST_UPDATE_DATE IS NULL OR
p_Terr_QualTypeUsgs_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' );
select jqu.qual_usg_id,
jqu.qual_col1_datatype,
jqu.display_type,
jqu.convert_to_id_flag,
jqu.display_sql1,
jqu.display_sql2,
jqu.display_sql3,
html_lov_sql1
from jtf_qual_usgs_ALL jqu, jtf_terr_qual_ALL jtq
where jqu.qual_usg_id = jtq.qual_Usg_Id and
jqu.org_id = jtq.org_id AND
jtq.terr_qual_id = p_Terr_Qual_Id;
IF ( p_Terr_Value_Rec.LAST_UPDATED_BY is NULL OR
p_Terr_Value_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_Value_Rec.LAST_UPDATE_DATE IS NULL OR
p_Terr_Value_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_Value_Rec.LAST_UPDATE_LOGIN is NULL OR
p_Terr_Value_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' );
Select 'X'
into l_dummy
From JTF_TERR_VALUES_ALL
WHERE TERR_QUAL_ID = P_terr_qual_id
AND nvl(COMPARISON_OPERATOR , '-9999') = nvl( decode(P_Terr_Value_Rec.COMPARISON_OPERATOR, FND_API.G_MISS_CHAR, NULL , P_Terr_Value_Rec.COMPARISON_OPERATOR ) , '-9999')
AND nvl(LOW_VALUE_CHAR , '-9999') = nvl( decode(P_Terr_Value_Rec.LOW_VALUE_CHAR, FND_API.G_MISS_CHAR, NULL , P_Terr_Value_Rec.LOW_VALUE_CHAR ) , '-9999')
AND nvl(HIGH_VALUE_CHAR , '-9999') = nvl( decode(P_Terr_Value_Rec.HIGH_VALUE_CHAR, FND_API.G_MISS_CHAR, NULL , P_Terr_Value_Rec.HIGH_VALUE_CHAR ) , '-9999')
AND nvl(LOW_VALUE_NUMBER , -9999) = nvl( decode(P_Terr_Value_Rec.LOW_VALUE_NUMBER, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.LOW_VALUE_NUMBER ) , -9999)
AND nvl(HIGH_VALUE_NUMBER , -9999) = nvl( decode(P_Terr_Value_Rec.HIGH_VALUE_NUMBER, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.HIGH_VALUE_NUMBER ) , -9999)
AND nvl(VALUE_SET , -9999) = nvl( decode(P_Terr_Value_Rec.VALUE_SET, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.VALUE_SET ) , -9999)
AND nvl(INTEREST_TYPE_ID , -9999) = nvl( decode(P_Terr_Value_Rec.INTEREST_TYPE_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.INTEREST_TYPE_ID ) , -9999)
AND nvl(PRIMARY_INTEREST_CODE_ID,-9999) = nvl( decode(P_Terr_Value_Rec.PRIMARY_INTEREST_CODE_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.PRIMARY_INTEREST_CODE_ID ) , -9999)
AND nvl(SECONDARY_INTEREST_CODE_ID,-9999) = nvl( decode(P_Terr_Value_Rec.SECONDARY_INTEREST_CODE_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.SECONDARY_INTEREST_CODE_ID ), -9999)
AND nvl(CURRENCY_CODE , '-9999') = nvl( decode(P_Terr_Value_Rec.CURRENCY_CODE, FND_API.G_MISS_CHAR, NULL , P_Terr_Value_Rec.CURRENCY_CODE ) , '-9999')
AND nvl(LOW_VALUE_CHAR_ID , -9999) = nvl( decode(P_Terr_Value_Rec.LOW_VALUE_CHAR_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.LOW_VALUE_CHAR_ID ) , -9999)
AND nvl(ORG_ID , -9999) = nvl( decode(P_Terr_Value_Rec.ORG_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.ORG_ID ) , -9999)
AND nvl(CNR_GROUP_ID , -9999) = nvl( decode(P_Terr_Value_Rec.CNR_GROUP_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.CNR_GROUP_ID ) , -9999)
AND nvl(VALUE1_ID , -9999) = nvl( decode(P_Terr_Value_Rec.VALUE1_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.VALUE1_ID ) , -9999)
AND nvl(VALUE2_ID , -9999) = nvl( decode(P_Terr_Value_Rec.VALUE2_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.VALUE2_ID ) , -9999)
AND nvl(VALUE3_ID , -9999) = nvl( decode(P_Terr_Value_Rec.VALUE3_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.VALUE3_ID ) , -9999)
AND nvl(VALUE4_ID , -9999) = nvl( decode(P_Terr_Value_Rec.VALUE4_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.VALUE4_ID ) , -9999) ;
PROCEDURE Check_duplicate_Value_update
(p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
x_Return_Status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_Terr_Qual_Id IN NUMBER,
p_Terr_Value_Rec IN Terr_Values_Rec_Type := G_Miss_Terr_Values_Rec)
AS
l_dummy VARCHAR2(5);
Select 'X'
into l_dummy
From JTF_TERR_VALUES_ALL
WHERE TERR_VALUE_ID <> P_Terr_Value_Rec.TERR_VALUE_ID
AND TERR_QUAL_ID = P_terr_qual_id
AND nvl(COMPARISON_OPERATOR , '-9999') = nvl( decode(P_Terr_Value_Rec.COMPARISON_OPERATOR, FND_API.G_MISS_CHAR, NULL , P_Terr_Value_Rec.COMPARISON_OPERATOR ) , '-9999')
AND nvl(LOW_VALUE_CHAR , '-9999') = nvl( decode(P_Terr_Value_Rec.LOW_VALUE_CHAR, FND_API.G_MISS_CHAR, NULL , P_Terr_Value_Rec.LOW_VALUE_CHAR ) , '-9999')
AND nvl(HIGH_VALUE_CHAR , '-9999') = nvl( decode(P_Terr_Value_Rec.HIGH_VALUE_CHAR, FND_API.G_MISS_CHAR, NULL , P_Terr_Value_Rec.HIGH_VALUE_CHAR ) , '-9999')
AND nvl(LOW_VALUE_NUMBER , -9999) = nvl( decode(P_Terr_Value_Rec.LOW_VALUE_NUMBER, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.LOW_VALUE_NUMBER ) , -9999)
AND nvl(HIGH_VALUE_NUMBER , -9999) = nvl( decode(P_Terr_Value_Rec.HIGH_VALUE_NUMBER, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.HIGH_VALUE_NUMBER ) , -9999)
AND nvl(VALUE_SET , -9999) = nvl( decode(P_Terr_Value_Rec.VALUE_SET, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.VALUE_SET ) , -9999)
AND nvl(INTEREST_TYPE_ID , -9999) = nvl( decode(P_Terr_Value_Rec.INTEREST_TYPE_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.INTEREST_TYPE_ID ) , -9999)
AND nvl(PRIMARY_INTEREST_CODE_ID,-9999) = nvl( decode(P_Terr_Value_Rec.PRIMARY_INTEREST_CODE_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.PRIMARY_INTEREST_CODE_ID ) , -9999)
AND nvl(SECONDARY_INTEREST_CODE_ID,-9999) = nvl( decode(P_Terr_Value_Rec.SECONDARY_INTEREST_CODE_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.SECONDARY_INTEREST_CODE_ID ), -9999)
AND nvl(CURRENCY_CODE , '-9999') = nvl( decode(P_Terr_Value_Rec.CURRENCY_CODE, FND_API.G_MISS_CHAR, NULL , P_Terr_Value_Rec.CURRENCY_CODE ) , '-9999')
AND nvl(LOW_VALUE_CHAR_ID , -9999) = nvl( decode(P_Terr_Value_Rec.LOW_VALUE_CHAR_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.LOW_VALUE_CHAR_ID ) , -9999)
AND nvl(ORG_ID , -9999) = nvl( decode(P_Terr_Value_Rec.ORG_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.ORG_ID ) , -9999)
AND nvl(CNR_GROUP_ID , -9999) = nvl( decode(P_Terr_Value_Rec.CNR_GROUP_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.CNR_GROUP_ID ) , -9999)
AND nvl(VALUE1_ID , -9999) = nvl( decode(P_Terr_Value_Rec.VALUE1_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.VALUE1_ID ) , -9999)
AND nvl(VALUE2_ID , -9999) = nvl( decode(P_Terr_Value_Rec.VALUE2_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.VALUE2_ID ) , -9999)
AND nvl(VALUE3_ID , -9999) = nvl( decode(P_Terr_Value_Rec.VALUE3_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.VALUE3_ID ) , -9999)
AND nvl(VALUE4_ID , -9999) = nvl( decode(P_Terr_Value_Rec.VALUE4_ID, FND_API.G_MISS_NUM, NULL , P_Terr_Value_Rec.VALUE4_ID ) , -9999) ;
END Check_duplicate_Value_update;
Select Max(rank)
into x_Rank
from jtf_terr_ALL jt, jtf_terr_usgs_ALL jtu
where jt.Parent_Territory_id = p_Parent_Terr_Id and
jt.Terr_Id = jtu.Terr_Id and
jtu.source_id = p_Source_Id;
** generation or update the template.
*/
PROCEDURE Validate_Template_Record (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_template_terr_id IN NUMBER,
x_Return_Status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
AS
/* (1) check template exists */
CURSOR c_chk_template (p_template_terr_id NUMBER) IS
SELECT 'X'
FROM jtf_terr_ALL j
WHERE j.terr_id = p_template_terr_id
AND j.template_flag = 'Y';
SELECT COUNT(*)
FROM jtf_terr_qual j
WHERE j.terr_id = p_template_terr_id
AND j.qualifier_mode = 'DYNAMIC';
** generation or update the template
*/
BEGIN
--OPEN c_lock_template (p_template_terr_id);
SELECT j1.terr_id
INTO l_terr_id
FROM jtf_terr j1
WHERE j1.terr_id = p_template_terr_id
FOR UPDATE NOWAIT;
SELECT j1.TERR_ID
, j1.LAST_UPDATE_DATE
, j1.LAST_UPDATED_BY
, j1.CREATION_DATE
, j1.CREATED_BY
, j1.LAST_UPDATE_LOGIN
, j1.APPLICATION_SHORT_NAME
, j1.NAME
, j1.ENABLED_FLAG
, j1.REQUEST_ID
, j1.PROGRAM_APPLICATION_ID
, j1.PROGRAM_ID
, j1.PROGRAM_UPDATE_DATE
, j1.START_DATE_ACTIVE
, j1.RANK
, j1.END_DATE_ACTIVE
, j1.DESCRIPTION
, j1.UPDATE_FLAG
, j1.AUTO_ASSIGN_RESOURCES_FLAG
, j1.PLANNED_FLAG
, j1.TERRITORY_TYPE_ID
, j1.PARENT_TERRITORY_ID
, j1.TEMPLATE_FLAG
, j1.TEMPLATE_TERRITORY_ID
, j1.ESCALATION_TERRITORY_FLAG
, j1.ESCALATION_TERRITORY_ID
, j1.OVERLAP_ALLOWED_FLAG
, j1.ATTRIBUTE_CATEGORY
, j1.ATTRIBUTE1
, j1.ATTRIBUTE2
, j1.ATTRIBUTE3
, j1.ATTRIBUTE4
, j1.ATTRIBUTE5
, j1.ATTRIBUTE6
, j1.ATTRIBUTE7
, j1.ATTRIBUTE8
, j1.ATTRIBUTE9
, j1.ATTRIBUTE10
, j1.ATTRIBUTE11
, j1.ATTRIBUTE12
, j1.ATTRIBUTE13
, j1.ATTRIBUTE14
, j1.ATTRIBUTE15
, j1.ORG_ID
, j1.NUM_WINNERS
FROM jtf_terr j1
WHERE j1.terr_id = p_template_terr_id;
SELECT MAX (j1.VALUE_SET)
FROM jtf_terr_values j1, jtf_terr_qual j2, jtf_terr j3
WHERE j1.terr_qual_id = j2.terr_qual_id
AND j2.terr_id = j3.terr_id
AND j3.terr_id = p_template_terr_id
AND j2.qualifier_mode = 'DYNAMIC'
GROUP BY j2.qual_usg_id;
SELECT j1.TERR_USG_ID
, j1.SOURCE_ID
, j1.TERR_ID
, j1.LAST_UPDATE_DATE
, j1.LAST_UPDATED_BY
, j1.CREATION_DATE
, j1.CREATED_BY
, j1.LAST_UPDATE_LOGIN
, j1.ORG_ID
FROM jtf_terr_usgs j1
WHERE j1.terr_id = p_template_terr_id;
SELECT j1.TERR_QTYPE_USG_ID
, j1.TERR_ID
, j1.QUAL_TYPE_USG_ID
, j1.LAST_UPDATE_DATE
, j1.LAST_UPDATED_BY
, j1.CREATION_DATE
, j1.CREATED_BY
, j1.LAST_UPDATE_LOGIN
, j1.ORG_ID
from jtf_terr_qtype_usgs j1
WHERE j1.terr_id = p_template_terr_id;
, x_tmpl_terr_rec.LAST_UPDATE_DATE
, x_tmpl_terr_rec.LAST_UPDATED_BY
, x_tmpl_terr_rec.CREATION_DATE
, x_tmpl_terr_rec.CREATED_BY
, x_tmpl_terr_rec.LAST_UPDATE_LOGIN
, x_tmpl_terr_rec.APPLICATION_SHORT_NAME
, x_tmpl_terr_rec.NAME
, x_tmpl_terr_rec.ENABLED_FLAG
, x_tmpl_terr_rec.REQUEST_ID
, x_tmpl_terr_rec.PROGRAM_APPLICATION_ID
, x_tmpl_terr_rec.PROGRAM_ID
, x_tmpl_terr_rec.PROGRAM_UPDATE_DATE
, x_tmpl_terr_rec.START_DATE_ACTIVE
, x_tmpl_terr_rec.RANK
, x_tmpl_terr_rec.END_DATE_ACTIVE
, x_tmpl_terr_rec.DESCRIPTION
, x_tmpl_terr_rec.UPDATE_FLAG
, x_tmpl_terr_rec.AUTO_ASSIGN_RESOURCES_FLAG
, x_tmpl_terr_rec.PLANNED_FLAG
, x_tmpl_terr_rec.TERRITORY_TYPE_ID
, x_tmpl_terr_rec.PARENT_TERRITORY_ID
, x_tmpl_terr_rec.TEMPLATE_FLAG
, x_tmpl_terr_rec.TEMPLATE_TERRITORY_ID
, x_tmpl_terr_rec.ESCALATION_TERRITORY_FLAG
, x_tmpl_terr_rec.ESCALATION_TERRITORY_ID
, x_tmpl_terr_rec.OVERLAP_ALLOWED_FLAG
, x_tmpl_terr_rec.ATTRIBUTE_CATEGORY
, x_tmpl_terr_rec.ATTRIBUTE1
, x_tmpl_terr_rec.ATTRIBUTE2
, x_tmpl_terr_rec.ATTRIBUTE3
, x_tmpl_terr_rec.ATTRIBUTE4
, x_tmpl_terr_rec.ATTRIBUTE5
, x_tmpl_terr_rec.ATTRIBUTE6
, x_tmpl_terr_rec.ATTRIBUTE7
, x_tmpl_terr_rec.ATTRIBUTE8
, x_tmpl_terr_rec.ATTRIBUTE9
, x_tmpl_terr_rec.ATTRIBUTE10
, x_tmpl_terr_rec.ATTRIBUTE11
, x_tmpl_terr_rec.ATTRIBUTE12
, x_tmpl_terr_rec.ATTRIBUTE13
, x_tmpl_terr_rec.ATTRIBUTE14
, x_tmpl_terr_rec.ATTRIBUTE15
, x_tmpl_terr_rec.ORG_ID
, x_tmpl_terr_rec.NUM_WINNERS;
, x_tmpl_usgs_tbl(i).LAST_UPDATE_DATE
, x_tmpl_usgs_tbl(i).LAST_UPDATED_BY
, x_tmpl_usgs_tbl(i).CREATION_DATE
, x_tmpl_usgs_tbl(i).CREATED_BY
, x_tmpl_usgs_tbl(i).LAST_UPDATE_LOGIN
, x_tmpl_usgs_tbl(i).ORG_ID;
, x_tmpl_qtype_usgs_tbl(j).LAST_UPDATE_DATE
, x_tmpl_qtype_usgs_tbl(j).LAST_UPDATED_BY
, x_tmpl_qtype_usgs_tbl(j).CREATION_DATE
, x_tmpl_qtype_usgs_tbl(j).CREATED_BY
, x_tmpl_qtype_usgs_tbl(j).LAST_UPDATE_LOGIN
, x_tmpl_qtype_usgs_tbl(j).ORG_ID;
l_new_terr_rec.LAST_UPDATE_DATE := SYSDATE;
l_new_terr_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_new_terr_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
l_new_terr_rec.PROGRAM_UPDATE_DATE := FND_API.G_MISS_DATE;
l_new_terr_rec.UPDATE_FLAG := p_template_terr_rec.update_flag;
/* update the territory's name */
l_new_terr_rec.NAME := l_new_terr_rec.name || TO_CHAR(l_new_terr_id);
update_territory_record (
P_Api_Version_Number => 1.0,
P_Init_Msg_List => FND_API.G_TRUE,
P_Commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
P_Terr_All_Rec => l_new_terr_rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data,
X_Terr_All_Out_Rec => l_terr_out_rec );
l_new_terr_rec.LAST_UPDATE_DATE := SYSDATE;
l_new_terr_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_new_terr_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
l_new_terr_rec.PROGRAM_UPDATE_DATE := FND_API.G_MISS_DATE;
l_new_terr_rec.UPDATE_FLAG := 'Y'; --- p_copied_terr_rec.update_flag;
/* update the territory's name */
l_new_terr_rec.NAME := l_new_terr_rec.name || TO_CHAR(l_new_terr_id);
l_terr_usgs_rec.LAST_UPDATE_DATE := SYSDATE;
l_terr_usgs_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_terr_usgs_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
l_terr_qtype_usgs_rec.LAST_UPDATE_DATE := SYSDATE;
l_terr_qtype_usgs_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_terr_qtype_usgs_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
IS SELECT 'Y'
FROM jtf_terr_ALL
WHERE template_flag = 'Y'
AND terr_id = lp_terr_id;
l_new_terr_qual_rec.LAST_UPDATE_DATE := SYSDATE;
l_new_terr_qual_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_new_terr_qual_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
IS SELECT 'Y'
FROM jtf_terr
WHERE template_flag = 'Y'
AND terr_id = lp_terr_id;
l_new_terr_value_rec.LAST_UPDATE_DATE := SYSDATE;
l_new_terr_value_rec.LAST_UPDATED_BY := fnd_global.user_id;
l_new_terr_value_rec.LAST_UPDATE_LOGIN := fnd_global.login_id;
/* insert value */
create_terr_value(
P_Api_Version_Number => 1.0,
P_Init_Msg_List => FND_API.G_TRUE,
P_Commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
P_Terr_Id => p_new_terr_id,
P_Terr_Qual_id => l_new_terr_value_rec.terr_qual_id,
P_Terr_Value_Rec => l_new_terr_value_rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data,
X_Terr_Value_Id => l_new_terr_value_id,
X_Terr_Value_Out_Rec => l_terr_value_out_rec);
PROCEDURE insert_copied_qual_values ( p_template_terr_id NUMBER
, p_new_terr_id NUMBER ) IS
/* local standard API variables */
l_api_version_number NUMBER := 1.0;
SELECT j1.rowid
, j1.TERR_QUAL_ID
, j1.LAST_UPDATE_DATE
, j1.LAST_UPDATED_BY
, j1.CREATION_DATE
, j1.CREATED_BY
, j1.LAST_UPDATE_LOGIN
, j1.TERR_ID
, j1.QUAL_USG_ID
, j1.USE_TO_NAME_FLAG
, j1.GENERATE_FLAG
, j1.OVERLAP_ALLOWED_FLAG
, j1.QUALIFIER_MODE
, j1.ORG_ID
FROM jtf_terr_qual j1
WHERE j1.terr_id = p_template_terr_id;
SELECT j1.TERR_VALUE_ID
, j1.LAST_UPDATE_DATE
, j1.LAST_UPDATED_BY
, j1.CREATION_DATE
, j1.CREATED_BY
, j1.LAST_UPDATE_LOGIN
, j1.TERR_QUAL_ID
, j1.INCLUDE_FLAG
, j1.COMPARISON_OPERATOR
, j1.LOW_VALUE_CHAR
, j1.HIGH_VALUE_CHAR
, j1.LOW_VALUE_NUMBER
, j1.HIGH_VALUE_NUMBER
, j1.VALUE_SET
, j1.INTEREST_TYPE_ID
, j1.PRIMARY_INTEREST_CODE_ID
, j1.SECONDARY_INTEREST_CODE_ID
, j1.CURRENCY_CODE
, j1.ORG_ID
, j1.ID_USED_FLAG
, j1.LOW_VALUE_CHAR_ID
, NULL QUALIFIER_TBL_INDEX
, j1.CNR_GROUP_ID
, j1.VALUE1_ID
, j1.VALUE2_ID
, j1.VALUE3_ID
, j1.VALUE4_ID
FROM jtf_terr_values j1
WHERE j1.terr_qual_id = p_terr_qual_id;
, l_tmpl_terr_qual_rec.LAST_UPDATE_DATE
, l_tmpl_terr_qual_rec.LAST_UPDATED_BY
, l_tmpl_terr_qual_rec.CREATION_DATE
, l_tmpl_terr_qual_rec.CREATED_BY
, l_tmpl_terr_qual_rec.LAST_UPDATE_LOGIN
, l_tmpl_terr_qual_rec.TERR_ID
, l_tmpl_terr_qual_rec.QUAL_USG_ID
, l_tmpl_terr_qual_rec.USE_TO_NAME_FLAG
, l_tmpl_terr_qual_rec.GENERATE_FLAG
, l_tmpl_terr_qual_rec.OVERLAP_ALLOWED_FLAG
, l_tmpl_terr_qual_rec.QUALIFIER_MODE
, l_tmpl_terr_qual_rec.ORG_ID;
, l_tmpl_terr_value_rec.LAST_UPDATE_DATE
, l_tmpl_terr_value_rec.LAST_UPDATED_BY
, l_tmpl_terr_value_rec.CREATION_DATE
, l_tmpl_terr_value_rec.CREATED_BY
, l_tmpl_terr_value_rec.LAST_UPDATE_LOGIN
, l_tmpl_terr_value_rec.TERR_QUAL_ID
, l_tmpl_terr_value_rec.INCLUDE_FLAG
, l_tmpl_terr_value_rec.COMPARISON_OPERATOR
, l_tmpl_terr_value_rec.LOW_VALUE_CHAR
, l_tmpl_terr_value_rec.HIGH_VALUE_CHAR
, l_tmpl_terr_value_rec.LOW_VALUE_NUMBER
, l_tmpl_terr_value_rec.HIGH_VALUE_NUMBER
, l_tmpl_terr_value_rec.VALUE_SET
, l_tmpl_terr_value_rec.INTEREST_TYPE_ID
, l_tmpl_terr_value_rec.PRIMARY_INTEREST_CODE_ID
, l_tmpl_terr_value_rec.SECONDARY_INTEREST_CODE_ID
, l_tmpl_terr_value_rec.CURRENCY_CODE
, l_tmpl_terr_value_rec.ORG_ID
, l_tmpl_terr_value_rec.ID_USED_FLAG
, l_tmpl_terr_value_rec.LOW_VALUE_CHAR_ID
, l_tmpl_terr_value_rec.QUALIFIER_TBL_INDEX
, l_tmpl_terr_value_rec.CNR_GROUP_ID
, l_tmpl_terr_value_rec.VALUE1_ID
, l_tmpl_terr_value_rec.VALUE2_ID
, l_tmpl_terr_value_rec.VALUE3_ID
, l_tmpl_terr_value_rec.VALUE4_ID;
END insert_copied_qual_values;
PROCEDURE insert_static_qual_values ( p_template_terr_id NUMBER
, p_new_terr_id NUMBER ) IS
/* local standard API variables */
l_api_version_number NUMBER := 1.0;
SELECT j1.rowid
, j1.TERR_QUAL_ID
, j1.LAST_UPDATE_DATE
, j1.LAST_UPDATED_BY
, j1.CREATION_DATE
, j1.CREATED_BY
, j1.LAST_UPDATE_LOGIN
, j1.TERR_ID
, j1.QUAL_USG_ID
, j1.USE_TO_NAME_FLAG
, j1.GENERATE_FLAG
, j1.OVERLAP_ALLOWED_FLAG
, j1.QUALIFIER_MODE
, j1.ORG_ID
FROM jtf_terr_qual j1
WHERE j1.terr_id = p_template_terr_id
AND j1.qualifier_mode = 'STATIC';
SELECT j1.TERR_VALUE_ID
, j1.LAST_UPDATE_DATE
, j1.LAST_UPDATED_BY
, j1.CREATION_DATE
, j1.CREATED_BY
, j1.LAST_UPDATE_LOGIN
, j1.TERR_QUAL_ID
, j1.INCLUDE_FLAG
, j1.COMPARISON_OPERATOR
, j1.LOW_VALUE_CHAR
, j1.HIGH_VALUE_CHAR
, j1.LOW_VALUE_NUMBER
, j1.HIGH_VALUE_NUMBER
, j1.VALUE_SET
, j1.INTEREST_TYPE_ID
, j1.PRIMARY_INTEREST_CODE_ID
, j1.SECONDARY_INTEREST_CODE_ID
, j1.CURRENCY_CODE
, j1.ORG_ID
, j1.ID_USED_FLAG
, j1.LOW_VALUE_CHAR_ID
, NULL QUALIFIER_TBL_INDEX
, j1.CNR_GROUP_ID
, j1.VALUE1_ID
, j1.VALUE2_ID
, j1.VALUE3_ID
, j1.VALUE4_ID
FROM jtf_terr_values j1
WHERE j1.terr_qual_id = p_terr_qual_id;
, l_tmpl_terr_qual_rec.LAST_UPDATE_DATE
, l_tmpl_terr_qual_rec.LAST_UPDATED_BY
, l_tmpl_terr_qual_rec.CREATION_DATE
, l_tmpl_terr_qual_rec.CREATED_BY
, l_tmpl_terr_qual_rec.LAST_UPDATE_LOGIN
, l_tmpl_terr_qual_rec.TERR_ID
, l_tmpl_terr_qual_rec.QUAL_USG_ID
, l_tmpl_terr_qual_rec.USE_TO_NAME_FLAG
, l_tmpl_terr_qual_rec.GENERATE_FLAG
, l_tmpl_terr_qual_rec.OVERLAP_ALLOWED_FLAG
, l_tmpl_terr_qual_rec.QUALIFIER_MODE
, l_tmpl_terr_qual_rec.ORG_ID;
, l_tmpl_terr_value_rec.LAST_UPDATE_DATE
, l_tmpl_terr_value_rec.LAST_UPDATED_BY
, l_tmpl_terr_value_rec.CREATION_DATE
, l_tmpl_terr_value_rec.CREATED_BY
, l_tmpl_terr_value_rec.LAST_UPDATE_LOGIN
, l_tmpl_terr_value_rec.TERR_QUAL_ID
, l_tmpl_terr_value_rec.INCLUDE_FLAG
, l_tmpl_terr_value_rec.COMPARISON_OPERATOR
, l_tmpl_terr_value_rec.LOW_VALUE_CHAR
, l_tmpl_terr_value_rec.HIGH_VALUE_CHAR
, l_tmpl_terr_value_rec.LOW_VALUE_NUMBER
, l_tmpl_terr_value_rec.HIGH_VALUE_NUMBER
, l_tmpl_terr_value_rec.VALUE_SET
, l_tmpl_terr_value_rec.INTEREST_TYPE_ID
, l_tmpl_terr_value_rec.PRIMARY_INTEREST_CODE_ID
, l_tmpl_terr_value_rec.SECONDARY_INTEREST_CODE_ID
, l_tmpl_terr_value_rec.CURRENCY_CODE
, l_tmpl_terr_value_rec.ORG_ID
, l_tmpl_terr_value_rec.ID_USED_FLAG
, l_tmpl_terr_value_rec.LOW_VALUE_CHAR_ID
, l_tmpl_terr_value_rec.QUALIFIER_TBL_INDEX
, l_tmpl_terr_value_rec.CNR_GROUP_ID
, l_tmpl_terr_value_rec.VALUE1_ID
, l_tmpl_terr_value_rec.VALUE2_ID
, l_tmpl_terr_value_rec.VALUE3_ID
, l_tmpl_terr_value_rec.VALUE4_ID;
END insert_static_qual_values;
SELECT j1.rowid
, j1.TERR_QUAL_ID
, j1.LAST_UPDATE_DATE
, j1.LAST_UPDATED_BY
, j1.CREATION_DATE
, j1.CREATED_BY
, j1.LAST_UPDATE_LOGIN
, j1.TERR_ID
, j1.QUAL_USG_ID
, j1.USE_TO_NAME_FLAG
, j1.GENERATE_FLAG
, j1.OVERLAP_ALLOWED_FLAG
, j1.QUALIFIER_MODE
, j1.ORG_ID
FROM jtf_terr_qual j1
WHERE j1.terr_id = p_template_terr_id
AND j1.qualifier_mode = 'DYNAMIC';
SELECT j1.TERR_VALUE_ID
, j1.LAST_UPDATE_DATE
, j1.LAST_UPDATED_BY
, j1.CREATION_DATE
, j1.CREATED_BY
, j1.LAST_UPDATE_LOGIN
, j1.TERR_QUAL_ID
, j1.INCLUDE_FLAG
, j1.COMPARISON_OPERATOR
, j1.LOW_VALUE_CHAR
, j1.HIGH_VALUE_CHAR
, j1.LOW_VALUE_NUMBER
, j1.HIGH_VALUE_NUMBER
, j1.VALUE_SET
, j1.INTEREST_TYPE_ID
, j1.PRIMARY_INTEREST_CODE_ID
, j1.SECONDARY_INTEREST_CODE_ID
, j1.CURRENCY_CODE
, j1.ID_USED_FLAG
, j1.LOW_VALUE_CHAR_ID
, NULL QUALIFIER_TBL_INDEX
, j1.ORG_ID
, j1.CNR_GROUP_ID
, j1.VALUE1_ID
, j1.VALUE2_ID
, j1.VALUE3_ID
, j1.VALUE4_ID
FROM jtf_terr_values j1
WHERE j1.terr_qual_id = p_terr_qual_id
ORDER BY j1.value_set;
, l_tmpl_terr_qual_rec.LAST_UPDATE_DATE
, l_tmpl_terr_qual_rec.LAST_UPDATED_BY
, l_tmpl_terr_qual_rec.CREATION_DATE
, l_tmpl_terr_qual_rec.CREATED_BY
, l_tmpl_terr_qual_rec.LAST_UPDATE_LOGIN
, l_tmpl_terr_qual_rec.TERR_ID
, l_tmpl_terr_qual_rec.QUAL_USG_ID
, l_tmpl_terr_qual_rec.USE_TO_NAME_FLAG
, l_tmpl_terr_qual_rec.GENERATE_FLAG
, l_tmpl_terr_qual_rec.OVERLAP_ALLOWED_FLAG
, l_tmpl_terr_qual_rec.QUALIFIER_MODE
, l_tmpl_terr_qual_rec.ORG_ID;
, x_val_tbl(l_val_curr_rec_num).LAST_UPDATE_DATE
, x_val_tbl(l_val_curr_rec_num).LAST_UPDATED_BY
, x_val_tbl(l_val_curr_rec_num).CREATION_DATE
, x_val_tbl(l_val_curr_rec_num).CREATED_BY
, x_val_tbl(l_val_curr_rec_num).LAST_UPDATE_LOGIN
, x_val_tbl(l_val_curr_rec_num).TERR_QUAL_ID
, x_val_tbl(l_val_curr_rec_num).INCLUDE_FLAG
, x_val_tbl(l_val_curr_rec_num).COMPARISON_OPERATOR
, x_val_tbl(l_val_curr_rec_num).LOW_VALUE_CHAR
, x_val_tbl(l_val_curr_rec_num).HIGH_VALUE_CHAR
, x_val_tbl(l_val_curr_rec_num).LOW_VALUE_NUMBER
, x_val_tbl(l_val_curr_rec_num).HIGH_VALUE_NUMBER
, x_val_tbl(l_val_curr_rec_num).VALUE_SET
, x_val_tbl(l_val_curr_rec_num).INTEREST_TYPE_ID
, x_val_tbl(l_val_curr_rec_num).PRIMARY_INTEREST_CODE_ID
, x_val_tbl(l_val_curr_rec_num).SECONDARY_INTEREST_CODE_ID
, x_val_tbl(l_val_curr_rec_num).CURRENCY_CODE
, x_val_tbl(l_val_curr_rec_num).ID_USED_FLAG
, x_val_tbl(l_val_curr_rec_num).LOW_VALUE_CHAR_ID
, x_val_tbl(l_val_curr_rec_num).QUALIFIER_TBL_INDEX
, x_val_tbl(l_val_curr_rec_num).ORG_ID
, x_val_tbl(l_val_curr_rec_num).CNR_GROUP_ID
, x_val_tbl(l_val_curr_rec_num).VALUE1_ID
, x_val_tbl(l_val_curr_rec_num).VALUE2_ID
, x_val_tbl(l_val_curr_rec_num).VALUE3_ID
, x_val_tbl(l_val_curr_rec_num).VALUE4_ID;
insert_static_qual_values(p_template_terr_id, l_new_terr_id);
/* insert record IN JTF_TERR_QUAL_ALL */
create_new_terr_qual_rec(l_new_terr_id, l_tmpl_terr_qual_rec, l_new_terr_qual_id);
select jta.terr_id
from JTF_TERR_ALL jta
where jta.parent_territory_id = p_terr_Id;
insert_copied_qual_values(p_copy_source_terr_id, l_new_terr_id);
update JTF_TERR_ALL
set parent_territory_id = l_new_terr_id
where terr_id = l_terr_id;
select jqu.qual_col1_datatype, jqu.display_type, jqu.convert_to_id_flag
into l_qual_col1_datatype, l_display_type, l_convert_to_id_flag
from jtf_qual_usgs jqu
where jqu.qual_usg_id = p_Qual_Usg_Id;
Select count(*)
into dummy
from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
Where jt.parent_territory_id = p_Parent_Terr_Id
and jtq.terr_id = jt.terr_id
and jtq.qual_usg_id = p_Qual_Usg_Id
and jtv.terr_qual_id = jtq.terr_qual_id
and ( ( jtv.COMPARISON_OPERATOR = '<' and p_terr_value_record.low_value_char < jtv.low_value_char )
or ( jtv.COMPARISON_OPERATOR = '<=' and p_terr_value_record.low_value_char <= jtv.low_value_char )
or ( jtv.COMPARISON_OPERATOR IN ('!=', '<>') and p_terr_value_record.low_value_char <> jtv.low_value_char )
or ( jtv.COMPARISON_OPERATOR = '=' and p_terr_value_record.low_value_char = jtv.low_value_char )
or ( jtv.COMPARISON_OPERATOR = '=' and jtv.low_value_char
between p_terr_value_record.low_value_char and p_terr_value_record.high_value_char )
or ( jtv.COMPARISON_OPERATOR = '>' and p_terr_value_record.low_value_char > jtv.low_value_char )
or ( jtv.COMPARISON_OPERATOR = '>=' and p_terr_value_record.low_value_char >= jtv.low_value_char )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'BETWEEN' and p_terr_value_record.low_value_char between jtv.low_value_char and jtv.high_value_char )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'LIKE' and p_terr_value_record.low_value_char LIKE jtv.low_value_char )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'NOT BETWEEN' and p_terr_value_record.low_value_char not between jtv.low_value_char and jtv.high_value_char )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'NOT LIKE' and p_terr_value_record.low_value_char NOT LIKE jtv.low_value_char ) )
and rownum < 2;
Select count(*)
into dummy
from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
Where jt.parent_territory_id = p_Parent_Terr_Id
and jtq.terr_id = jt.terr_id
and jtq.qual_usg_id = p_Qual_Usg_Id
and jtv.terr_qual_id = jtq.terr_qual_id
and ( ( jtv.COMPARISON_OPERATOR = '<' and p_terr_value_record.high_value_char < jtv.high_value_char )
or ( jtv.COMPARISON_OPERATOR = '<=' and p_terr_value_record.high_value_char <= jtv.high_value_char )
or ( jtv.COMPARISON_OPERATOR IN ('!=', '<>') and p_terr_value_record.high_value_char <> jtv.high_value_char )
or ( jtv.COMPARISON_OPERATOR = '=' and p_terr_value_record.high_value_char = jtv.high_value_char )
or ( jtv.COMPARISON_OPERATOR = '>' and p_terr_value_record.high_value_char > jtv.high_value_char )
or ( jtv.COMPARISON_OPERATOR = '>=' and p_terr_value_record.high_value_char >= jtv.high_value_char )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'BETWEEN' and p_terr_value_record.high_value_char between jtv.low_value_char and jtv.high_value_char )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'LIKE' and p_terr_value_record.high_value_char LIKE jtv.low_value_char )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'NOT BETWEEN' and p_terr_value_record.high_value_char not between jtv.low_value_char and jtv.high_value_char )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'NOT LIKE' and p_terr_value_record.high_value_char NOT LIKE jtv.low_value_char ) )
and rownum < 2;
Select count(*)
into dummy
from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
Where jt.parent_territory_id = p_Parent_Terr_Id
and jtq.terr_id = jt.terr_id
and jtq.qual_usg_id = p_Qual_Usg_Id
and jtv.terr_qual_id = jtq.terr_qual_id
and jtv.LOW_VALUE_CHAR_ID = p_terr_value_record.LOW_VALUE_CHAR_ID
and rownum < 2;
Select 1
into dummy
from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
Where jt.parent_territory_id = p_Parent_Terr_Id
and jtq.terr_id = jt.terr_id
and jtq.qual_usg_id = p_Qual_Usg_Id
and jtv.terr_qual_id = jtq.terr_qual_id
and ( ( jtv.COMPARISON_OPERATOR = '<' and p_terr_value_record.LOW_VALUE_number < jtv.low_value_number )
or ( jtv.COMPARISON_OPERATOR = '<=' and p_terr_value_record.LOW_VALUE_number <= jtv.low_value_number )
or ( jtv.COMPARISON_OPERATOR IN ('!=', '<>') and p_terr_value_record.LOW_VALUE_number <> jtv.low_value_number )
or ( jtv.COMPARISON_OPERATOR = '=' and p_terr_value_record.LOW_VALUE_number = jtv.low_value_number )
or ( jtv.COMPARISON_OPERATOR = '>' and p_terr_value_record.LOW_VALUE_number > jtv.low_value_number )
or ( jtv.COMPARISON_OPERATOR = '>=' and p_terr_value_record.LOW_VALUE_number >= jtv.low_value_number )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'BETWEEN' and p_terr_value_record.LOW_VALUE_number between jtv.low_value_number and jtv.high_value_number )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'NOT BETWEEN' and p_terr_value_record.LOW_VALUE_number not between jtv.low_value_number and jtv.high_value_number ))
and rownum < 2;
Select 1
into dummy
from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
Where jt.parent_territory_id = p_Parent_Terr_Id
and jtq.terr_id = jt.terr_id
and jtq.qual_usg_id = p_Qual_Usg_Id
and jtv.terr_qual_id = jtq.terr_qual_id
and ( ( jtv.COMPARISON_OPERATOR = '<' and p_terr_value_record.high_value_number < jtv.high_value_number )
or ( jtv.COMPARISON_OPERATOR = '<=' and p_terr_value_record.high_value_number <= jtv.high_value_number )
or ( jtv.COMPARISON_OPERATOR IN ('!=', '<>') and p_terr_value_record.high_value_number <> jtv.high_value_number )
or ( jtv.COMPARISON_OPERATOR = '=' and p_terr_value_record.high_value_number = jtv.high_value_number )
or ( jtv.COMPARISON_OPERATOR = '>' and p_terr_value_record.high_value_number > jtv.high_value_number )
or ( jtv.COMPARISON_OPERATOR = '>=' and p_terr_value_record.high_value_number >= jtv.high_value_number )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'BETWEEN' and p_terr_value_record.high_value_number between jtv.low_value_number and jtv.high_value_number )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'NOT BETWEEN' and p_terr_value_record.high_value_number not between jtv.low_value_number and jtv.high_value_number ))
and rownum < 2;
Select count(*)
into dummy
from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
Where jt.parent_territory_id = p_Parent_Terr_Id
and jtq.terr_id = jt.terr_id
and jtq.qual_usg_id = p_Qual_Usg_Id
and jtv.terr_qual_id = jtq.terr_qual_id
and ( ( jtv.COMPARISON_OPERATOR = '<' and p_terr_value_record.LOW_VALUE_number < jtv.low_value_number )
or ( jtv.COMPARISON_OPERATOR = '<=' and p_terr_value_record.LOW_VALUE_number <= jtv.low_value_number )
or ( jtv.COMPARISON_OPERATOR IN ('!=', '<>') and p_terr_value_record.LOW_VALUE_number <> jtv.low_value_number )
or ( jtv.COMPARISON_OPERATOR = '=' and p_terr_value_record.LOW_VALUE_number = jtv.low_value_number )
or ( jtv.COMPARISON_OPERATOR = '>' and p_terr_value_record.LOW_VALUE_number > jtv.low_value_number )
or ( jtv.COMPARISON_OPERATOR = '>=' and p_terr_value_record.LOW_VALUE_number >= jtv.low_value_number )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'BETWEEN' and p_terr_value_record.LOW_VALUE_number between jtv.low_value_number and jtv.high_value_number )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'NOT BETWEEN' and p_terr_value_record.LOW_VALUE_number not between jtv.low_value_number and jtv.high_value_number ))
and rownum < 2;
Select count(*)
into dummy
from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
Where jt.parent_territory_id = p_Parent_Terr_Id
and jtq.terr_id = jt.terr_id
and jtq.qual_usg_id = p_Qual_Usg_Id
and jtv.terr_qual_id = jtq.terr_qual_id
and ( ( jtv.COMPARISON_OPERATOR = '<' and p_terr_value_record.HIGH_VALUE_number < jtv.high_value_number )
or ( jtv.COMPARISON_OPERATOR = '<=' and p_terr_value_record.HIGH_VALUE_number <= jtv.high_value_number )
or ( jtv.COMPARISON_OPERATOR IN ('!=', '<>') and p_terr_value_record.HIGH_VALUE_number <> jtv.high_value_number )
or ( jtv.COMPARISON_OPERATOR = '=' and p_terr_value_record.HIGH_VALUE_number = jtv.high_value_number )
or ( jtv.COMPARISON_OPERATOR = '>' and p_terr_value_record.HIGH_VALUE_number > jtv.high_value_number )
or ( jtv.COMPARISON_OPERATOR = '>=' and p_terr_value_record.HIGH_VALUE_number >= jtv.high_value_number )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'BETWEEN' and p_terr_value_record.HIGH_VALUE_number between jtv.low_value_number and jtv.high_value_number )
or ( UPPER(jtv.COMPARISON_OPERATOR) = 'NOT BETWEEN' and p_terr_value_record.HIGH_VALUE_number not between jtv.low_value_number and jtv.high_value_number ))
and rownum < 2;
Select count(*)
into dummy
from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
Where jt.parent_territory_id = p_Parent_Terr_Id
and jtq.terr_id = jt.terr_id
and jtq.qual_usg_id = p_Qual_Usg_Id
and jtv.terr_qual_id = jtq.terr_qual_id
and ( ( jtv.interest_type_id = p_terr_value_record.interest_type_id )
and ( jtv.primary_interest_code_id = p_terr_value_record.primary_interest_code_id or
( jtv.primary_interest_code_id IS NULL and p_terr_value_record.primary_interest_code_id is NULL) )
and ( jtv.secondary_interest_code_id = p_terr_value_record.secondary_interest_code_id or
( jtv.secondary_interest_code_id is NULL and p_terr_value_record.secondary_interest_code_id is NULL) ) )
and rownum < 2;
SELECT 'X'
FROM JTF_TERR_USGS_ALL
WHERE terr_id = lp_terr_id
AND source_id = lp_source_id;
SELECT 'X'
FROM JTF_TERR_QTYPE_USGS_ALL
WHERE terr_id = lp_terr_id
AND qual_type_usg_id = lp_qual_type_usg_id;
SELECT 'X'
FROM jtf_terr_ALL j
WHERE j.parent_territory_id = 1
AND j.terr_id = lp_terr_id;
SELECT COUNT(*)
FROM jtf_terr_all jt
CONNECT BY PRIOR jt.terr_id = jt.parent_territory_id
START WITH jt.terr_id = lp_terr_id;
l_terr_rec.LAST_UPDATE_DATE := FND_API.G_MISS_DATE;
l_terr_rec.LAST_UPDATED_BY := FND_API.G_MISS_NUM;
l_terr_rec.LAST_UPDATE_LOGIN := FND_API.G_MISS_NUM;
l_terr_rec.PROGRAM_UPDATE_DATE := FND_API.G_MISS_DATE;
l_terr_rec.UPDATE_FLAG := FND_API.G_MISS_CHAR;
SELECT COUNT(*)
INTO l_hier_terr_count
FROM jtf_terr_all jt
CONNECT BY PRIOR jt.terr_id = jt.parent_territory_id
START WITH jt.terr_id = p_copy_source_terr_Id;
Write_Log(2, 'Inserted ' || l_hier_terr_count || ' rows into JTF_TERR_ALL ');
SELECT j.parent_territory_id
INTO l_existing_parent_terr_id
FROM jtf_terr_all j
WHERE j.terr_id = p_terr_all_rec.terr_id;
SELECT COUNT(*)
INTO l_return_csr
FROM jtf_terr_all j
WHERE j.terr_id = p_terr_all_rec.parent_territory_id -- new parent territory id
CONNECT BY PRIOR j.terr_id = j.parent_territory_id
START WITH j.terr_id = p_terr_all_rec.terr_id; -- territory_id
select_flag IN VARCHAR2)
RETURN VARCHAR2
IS
v_tot_query VARCHAR2(1000);
SELECT 'GROUP1' COMPARISION_GROUP,
f.lookup_code,
f.meaning
FROM fnd_lookups f
WHERE f.lookup_type = 'JTF_TERR_COMP_OPERATOR'
AND f.lookup_code IN ('=', '<>')
AND f.enabled_flag = 'Y'
UNION ALL
SELECT 'GROUP2' COMPARISION_GROUP,
f.lookup_code,
f.meaning
FROM fnd_lookups f
WHERE f.lookup_type = 'JTF_TERR_COMP_OPERATOR'
AND f.lookup_code IN ( '=', '<>', 'LIKE', 'NOT LIKE' , 'BETWEEN', 'NOT BETWEEN')
AND f.enabled_flag = 'Y'
UNION ALL
SELECT 'GROUP3' COMPARISION_GROUP,
f.lookup_code,
f.meaning
FROM fnd_lookups f
WHERE f.lookup_type = 'JTF_TERR_COMP_OPERATOR'
AND f.lookup_code IN ( '=', '<>' , 'BETWEEN', 'NOT BETWEEN')
AND f.enabled_flag = 'Y'
UNION ALL
SELECT 'GROUP4' COMPARISION_GROUP,
f.lookup_code,
f.meaning
FROM fnd_lookups f
WHERE f.lookup_type = 'JTF_TERR_COMP_OPERATOR'
AND f.lookup_code = '='
AND f.enabled_flag = 'Y';
SELECT f.name currency_name,
f.currency_code
FROM fnd_currencies_vl f
WHERE f.enabled_flag = 'Y'
ORDER BY 1;
IF (select_flag ='CO' AND v_COMPARISON_OPERATOR IS NOT NULL) THEN
OPEN c_comparision_operator;
IF (select_flag ='1') THEN
IF(v_display_type='INTEREST_TYPE') THEN
v_tot_query :=(v_display_sql1||''||v_interest_type_id);
ELSIF SELECT_FLAG ='2' THEN
IF(v_display_type='INTEREST_TYPE') THEN
v_tot_query := (v_display_sql2||''||v_primary_interest_code_id);
ELSIF (SELECT_FLAG ='3') THEN
IF(v_display_type='INTEREST_TYPE') THEN
v_tot_query := (v_display_sql3||''||v_secondary_interest_code_id);