[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Check_for_duplicate2_updates (
P_TerrRsc_Rec IN TerrResource_Rec_type,
x_Return_Status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) AS
l_start_date_active DATE;
Select JTR2.start_date_active, nvl(JTR2.end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
from JTF_TERR_RSC_ALL JTR1, JTF_TERR_RSC_ALL JTR2
where JTR2.TERR_ID = p_Terr_Id
AND JTR1.TERR_RSC_ID = P_TerrRsc_Rec.Terr_Rsc_Id
--resource with same role and group assigned to this territory
AND JTR2.RESOURCE_ID = decode(P_TerrRsc_Rec.Resource_Id, FND_API.G_MISS_NUM, JTR1.RESOURCE_ID, P_TerrRsc_Rec.Resource_Id)
AND JTR2.RESOURCE_TYPE = decode(P_TerrRsc_Rec.Resource_TYPE , FND_API.G_MISS_CHAR, JTR1.RESOURCE_TYPE, P_TerrRsc_Rec.Resource_TYPE)
AND JTR2.GROUP_ID = decode( P_TerrRsc_Rec.GROUP_ID , FND_API.G_MISS_NUM,JTR1.GROUP_ID,P_TerrRsc_Rec.GROUP_ID )
AND JTR2.ROLE = decode(P_TerrRsc_Rec.ROLE, FND_API.G_MISS_CHAR, JTR1.ROLE, P_TerrRsc_Rec.ROLE )
AND JTR2.TERR_RSC_ID <> P_TerrRsc_Rec.Terr_Rsc_Id;
SELECT terr_id, start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
INTO l_terr_id,l_start_date_active, l_end_date_active
FROM JTF_TERR_RSC_ALL
WHERE TERR_RSC_ID = P_TerrRsc_Rec.Terr_Rsc_Id;
FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'NO_DATA_FOUND Exception in Chack Duplicate2_update procedure : ' || SQLERRM);
END Check_for_duplicate2_updates;
Select start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
from JTF_TERR_RSC_ALL
where TERR_ID = P_TerrRsc_Rec.Terr_Id
--resource with same role and group assigned to this territory
AND RESOURCE_ID = P_TerrRsc_Rec.Resource_Id
AND ( (RESOURCE_TYPE IS NULL and ( ( P_TerrRsc_Rec.Resource_TYPE IS NULL ) OR (P_TerrRsc_Rec.Resource_TYPE = FND_API.G_MISS_CHAR) ) )
OR (RESOURCE_TYPE = P_TerrRsc_Rec.Resource_TYPE))
AND ( (GROUP_ID IS NULL and ( ( P_TerrRsc_Rec.GROUP_ID IS NULL ) OR (P_TerrRsc_Rec.GROUP_ID = FND_API.G_MISS_NUM ) ) )
OR (P_TerrRsc_Rec.GROUP_ID = GROUP_ID) )
AND ( (ROLE IS NULL and ( (P_TerrRsc_Rec.ROLE IS NULL ) OR (P_TerrRsc_Rec.ROLE = FND_API.G_MISS_CHAR) ) )
OR (P_TerrRsc_Rec.ROLE = ROLE));
x_terrrsc_tbl (l_counter).last_update_date :=
p_terrrsc_tbl_wflex (l_counter).last_update_date;
x_terrrsc_tbl (l_counter).last_updated_by :=
p_terrrsc_tbl_wflex (l_counter).last_updated_by;
x_terrrsc_tbl (l_counter).last_update_login :=
p_terrrsc_tbl_wflex (l_counter).last_update_login;
SELECT NAME
FROM JTF_TERR_QTYPE_USGS_all jtqu,
jtf_qual_type_usgs_all jqtu ,
jtf_qual_types_all jqt
WHERE jtqu.terr_id = p_terr_id
AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
AND jqt.qual_type_id = jqtu.qual_type_id;
l_TerrRsc_Access_Tbl.Delete;
SELECT DECODE(source_id, '-1001' , 'FULL_ACCESS' , 'DEFAULT' )
INTO l_trans_access_code
FROM jtf_terr_usgs_all WHERE terr_id = l_TerrRsc_Tbl(1).terr_id ;
l_TerrRsc_def_Acc_Tbl.DELETE;
l_TerrRsc_def_Acc_Tbl (l_Res_def_Acc_Counter).last_update_date := l_TerrRsc_Tbl(1).last_update_date;
l_TerrRsc_def_Acc_Tbl (l_Res_def_Acc_Counter).last_updated_by := l_TerrRsc_Tbl(1).last_updated_by;
l_TerrRsc_def_Acc_Tbl (l_Res_def_Acc_Counter).last_update_login := l_TerrRsc_Tbl(1).last_update_login;
PROCEDURE Delete_Terr_Resource
(
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_TerrRsc_Id IN NUMBER
)
AS
l_Terr_rsc_access_id NUMBER;
Select JTRA.TERR_RSC_ACCESS_ID
From JTF_TERR_RSC_ACCESS_ALL JTRA
Where TERR_RSC_ID = v_TerrRsc_Id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Terr_Resource';
SAVEPOINT DELETE_TERR_RESOURCE_PVT;
Delete_TerrRsc_Access(P_Api_Version_Number,
P_Init_Msg_List,
P_Commit,
l_Terr_rsc_access_id,
l_Return_Status,
X_Msg_Count,
X_Msg_Data);
Delete_TerrResource(P_Api_Version_Number,
P_Init_Msg_List,
P_Commit,
p_TerrRsc_Id,
l_Return_Status,
X_Msg_Count,
X_Msg_Data);
ROLLBACK TO DELETE_TERR_RESOURCE_PVT;
ROLLBACK TO DELETE_TERR_RESOURCE_PVT;
ROLLBACK TO DELETE_TERR_RESOURCE_PVT;
END Delete_Terr_Resource;
PROCEDURE Update_TerrResource
(
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_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
x_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type,
x_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_tbl_type
)
AS
l_api_name CONSTANT VARCHAR2(30) := 'Update_TerrResource (Tbl)';
SAVEPOINT UPDATE_TERRRESOURCE_PVT;
Update_Terr_Resource(P_TerrRsc_Tbl => P_TerrRsc_Tbl,
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,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
X_TerrRsc_Out_Tbl => X_TerrRsc_Out_Tbl);
Update_Resource_Access(p_TerrRsc_Access_Tbl => p_TerrRsc_Access_Tbl,
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,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
X_TerrRsc_Access_Out_Tbl => x_TerrRsc_Access_Out_Tbl);
ROLLBACK TO UPDATE_TERRRESOURCE_PVT;
ROLLBACK TO UPDATE_TERRRESOURCE_PVT;
ROLLBACK TO UPDATE_TERRRESOURCE_PVT;
END Update_TerrResource;
SELECT JSA.RSC_LOV_SQL
INTO l_rsc_lov_sql
FROM JTF_TERR_ALL JTA,
JTF_TERR_USGS_ALL JTU,
JTF_SOURCES_ALL JSA
WHERE JTA.TERR_ID = P_TerrRsc_Rec.Terr_Id
AND JTA.TERR_ID = JTU.TERR_ID
AND JTU.SOURCE_ID = JSA.SOURCE_ID;
l_rsc_validate_sql := 'SELECT ''X'' FROM ( ' || l_rsc_lov_sql || ' ) ' ;
PROCEDURE Validate_Resource_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_TerrRsc_Rec IN TerrResource_Rec_type
)
AS
l_temp VARCHAR2(3);
SELECT JSA.RSC_LOV_SQL
INTO l_rsc_lov_sql
FROM JTF_TERR_ALL JTA,
JTF_TERR_RSC_ALL JTR,
JTF_TERR_USGS_ALL JTU,
JTF_SOURCES_ALL JSA
WHERE JTR.terr_rsc_id = P_TerrRsc_Rec.Terr_Rsc_Id
AND JTR.TERR_ID = JTA.Terr_Id
AND JTA.TERR_ID = JTU.TERR_ID
AND JTU.SOURCE_ID = JSA.SOURCE_ID;
FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'NO_DATA_FOUND Exception in Validate_Resource_update procedure : ' || SQLERRM);
SELECT resource_id, group_id, role, resource_type
INTO l_resource_id, l_group_id, l_role, l_resource_type
FROM JTF_TERR_RSC_ALL
WHERE TERR_RSC_ID = P_TerrRsc_Rec.Terr_Rsc_Id;
FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'NO_DATA_FOUND Exception in Validate_Resource_update procedure : ' || SQLERRM);
l_rsc_validate_sql := 'SELECT ''X'' FROM ( ' || l_rsc_lov_sql || ' ) ' ;
'NO_DATA_FOUND Exception in Validate_Resource_update ' || SQLERRM
);
'Others Exception in Validate_Resource_update ' || SQLERRM
);
END Validate_Resource_update;
JTF_TERR_RSC_PKG.Insert_Row(x_Rowid => l_rowid,
x_TERR_RSC_ID => l_TerrRsc_Id,
x_LAST_UPDATE_DATE => P_TerrRsc_Rec.LAST_UPDATE_DATE,
x_LAST_UPDATED_BY => P_TerrRsc_Rec.LAST_UPDATED_BY,
x_CREATION_DATE => P_TerrRsc_Rec.CREATION_DATE,
x_CREATED_BY => P_TerrRsc_Rec.CREATED_BY,
x_LAST_UPDATE_LOGIN => P_TerrRsc_Rec.LAST_UPDATE_LOGIN,
x_TERR_ID => P_TerrRsc_Rec.TERR_ID,
x_RESOURCE_ID => P_TerrRsc_Rec.RESOURCE_ID,
x_GROUP_ID => P_TerrRsc_Rec.GROUP_ID,
x_RESOURCE_TYPE => P_TerrRsc_Rec.RESOURCE_TYPE,
x_ROLE => P_TerrRsc_Rec.ROLE,
x_PRIMARY_CONTACT_FLAG => P_TerrRsc_Rec.PRIMARY_CONTACT_FLAG,
X_START_DATE_ACTIVE => P_TerrRsc_Rec.START_DATE_ACTIVE,
X_END_DATE_ACTIVE => P_TerrRsc_Rec.END_DATE_ACTIVE,
X_FULL_ACCESS_FLAG => P_TerrRsc_Rec.FULL_ACCESS_FLAG,
X_ORG_ID => P_TerrRsc_Rec.ORG_ID,
X_ATTRIBUTE_CATEGORY => P_TerrRsc_Rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => P_TerrRsc_Rec.ATTRIBUTE1,
X_ATTRIBUTE2 => P_TerrRsc_Rec.ATTRIBUTE2,
X_ATTRIBUTE3 => P_TerrRsc_Rec.ATTRIBUTE3,
X_ATTRIBUTE4 => P_TerrRsc_Rec.ATTRIBUTE4,
X_ATTRIBUTE5 => P_TerrRsc_Rec.ATTRIBUTE5,
X_ATTRIBUTE6 => P_TerrRsc_Rec.ATTRIBUTE6,
X_ATTRIBUTE7 => P_TerrRsc_Rec.ATTRIBUTE7,
X_ATTRIBUTE8 => P_TerrRsc_Rec.ATTRIBUTE8,
X_ATTRIBUTE9 => P_TerrRsc_Rec.ATTRIBUTE9,
X_ATTRIBUTE10 => P_TerrRsc_Rec.ATTRIBUTE10,
X_ATTRIBUTE11 => P_TerrRsc_Rec.ATTRIBUTE11,
X_ATTRIBUTE12 => P_TerrRsc_Rec.ATTRIBUTE12,
X_ATTRIBUTE13 => P_TerrRsc_Rec.ATTRIBUTE13,
X_ATTRIBUTE14 => P_TerrRsc_Rec.ATTRIBUTE14,
X_ATTRIBUTE15 => P_TerrRsc_Rec.ATTRIBUTE15 );
JTF_TERR_RSC_ACCESS_PKG.Insert_Row(x_Rowid => l_rowid,
x_TERR_RSC_ACCESS_ID => l_terrRsc_Access_id,
x_LAST_UPDATE_DATE => P_TerrRsc_Access_Rec.LAST_UPDATE_DATE,
x_LAST_UPDATED_BY => P_TerrRsc_Access_Rec.LAST_UPDATED_BY,
x_CREATION_DATE => P_TerrRsc_Access_Rec.CREATION_DATE,
x_CREATED_BY => P_TerrRsc_Access_Rec.CREATED_BY,
x_LAST_UPDATE_LOGIN => P_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN,
x_TERR_RSC_ID => p_TerrRsc_Id,
x_ACCESS_TYPE => P_TerrRsc_Access_Rec.ACCESS_TYPE,
x_TRANS_ACCESS_CODE => P_TerrRsc_Access_Rec.TRANS_ACCESS_CODE,
X_ORG_ID => P_TerrRsc_Access_Rec.ORG_ID );
PROCEDURE Delete_TerrRsc_Access
(
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_TerrRsc_Access_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_TerrRsc_Access';
SAVEPOINT DELETE_TERRRSC_ACCESS_PVT;
FND_MESSAGE.Set_Name('JTF', 'Delete TerrRscAccess : Start');
JTF_TERR_RSC_ACCESS_PKG.Delete_Row(x_TERR_RSC_ACCESS_ID => P_TerrRsc_Access_Id );
FND_MESSAGE.SET_NAME('JTF','TERR_RSCACCESSES_DELETED');
FND_MESSAGE.SET_NAME('ITEMS_DELETED', l_row_count);
FND_MESSAGE.Set_Name('JTF', 'Delete TerrRscAccess: End');
FND_MESSAGE.SET_NAME('ITEMS_DELETED', l_row_count);
ROLLBACK TO DELETE_TERRRSC_ACCESS_PVT;
ROLLBACK TO DELETE_TERRRSC_ACCESS_PVT;
FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Delete error inside Delete_TerrRsc_Access');
END Delete_TerrRsc_Access;
PROCEDURE Delete_TerrResource
(
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_TerrRsc_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_TerrResource';
SAVEPOINT DELETE_TERRRSC_PVT;
JTF_TERR_RSC_PKG.Delete_Row(x_TERR_RSC_ID => P_TerrRsc_Id);
FND_MESSAGE.SET_NAME('JTF','TERR_RESOURCE_DELETED');
FND_MESSAGE.SET_TOKEN('ITEMS_DELETED', l_row_count);
ROLLBACK TO DELETE_TERRRSC_PVT;
FND_MESSAGE.SET_NAME('ITEMS_DELETED', l_row_count);
ROLLBACK TO DELETE_TERRRSC_PVT;
ROLLBACK TO DELETE_TERRRSC_PVT;
FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Delete error inside Delete_Territory_Resource');
End Delete_TerrResource;
PROCEDURE Validate_Terr_Rsc_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_TerrRsc_Rec IN TerrResource_Rec_type
)
AS
l_Validate_id NUMBER;
SELECT terr_id, start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
INTO l_terr_id, l_res_start_date_active, l_res_end_date_active
FROM JTF_TERR_RSC_ALL
WHERE TERR_RSC_ID = P_TerrRsc_Rec.Terr_Rsc_Id;
SELECT jta.start_date_active,jta.end_date_active
INTO l_terr_start_date,l_terr_end_date
FROM jtf_terr_all jta
WHERE jta.terr_id = l_terr_id ;
FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Others Exception in Validate_Terr_Rsc_update ' || SQLERRM);
IF ( p_TerrRsc_Rec.LAST_UPDATED_BY is NULL OR
p_TerrRsc_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_TerrRsc_Rec.LAST_UPDATE_DATE IS NULL OR
p_TerrRsc_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_TerrRsc_Rec.LAST_UPDATE_LOGIN is NULL OR
p_TerrRsc_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' );
'Others Exception in Validate_Terr_Rsc_update ' || SQLERRM
);
END Validate_Terr_Rsc_update;
PROCEDURE Update_Terr_Resource (
P_TerrRsc_Rec IN TerrResource_Rec_type,
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,
x_TerrRsc_Out_Rec OUT NOCOPY TerrResource_out_Rec_type) AS
Cursor C_GetTerrResource(l_TerrRsc_id Number) IS
Select Rowid, TERR_RSC_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, TERR_ID, RESOURCE_ID, RESOURCE_TYPE, ROLE, PRIMARY_CONTACT_FLAG, ORG_ID
From jtf_terr_rsc_ALL
Where TERR_RSC_ID = l_TerrRsc_id
FOR Update NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Resource';
Fetch C_GetTerrResource into l_Rowid, l_ref_TerrRsc_Rec.TERR_RSC_ID, l_ref_TerrRsc_Rec.LAST_UPDATE_DATE,
l_ref_TerrRsc_Rec.LAST_UPDATED_BY, l_ref_TerrRsc_Rec.CREATION_DATE, l_ref_TerrRsc_Rec.CREATED_BY,
l_ref_TerrRsc_Rec.LAST_UPDATE_LOGIN, l_ref_TerrRsc_Rec.TERR_ID, l_ref_TerrRsc_Rec.RESOURCE_ID,
l_ref_TerrRsc_Rec.RESOURCE_TYPE, l_ref_TerrRsc_Rec.ROLE, l_ref_TerrRsc_Rec.PRIMARY_CONTACT_FLAG,
l_ref_TerrRsc_Rec.ORG_ID;
Validate_Terr_Rsc_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_TerrRsc_Rec => P_TerrRsc_Rec);
Validate_Resource_update(P_TerrRsc_Rec => P_TerrRsc_Rec,
x_Return_Status => l_return_status,
x_msg_count => x_msg_count,
x_Msg_Data => x_Msg_Data);
Check_for_duplicate2_updates(P_TerrRsc_Rec => P_TerrRsc_Rec,
x_Return_Status => l_return_status,
x_msg_count => x_msg_count,
x_Msg_Data => x_Msg_Data);
JTF_TERR_RSC_PKG.Update_Row(x_Rowid => l_rowid,
x_TERR_RSC_ID => P_TerrRsc_Rec.Terr_Rsc_Id,
x_LAST_UPDATE_DATE => P_TerrRsc_Rec.LAST_UPDATE_DATE,
x_LAST_UPDATED_BY => P_TerrRsc_Rec.LAST_UPDATED_BY ,
x_CREATION_DATE => P_TerrRsc_Rec.CREATION_DATE,
x_CREATED_BY => P_TerrRsc_Rec.CREATED_BY,
x_LAST_UPDATE_LOGIN => P_TerrRsc_Rec.LAST_UPDATE_LOGIN ,
x_TERR_ID => P_TerrRsc_Rec.TERR_ID,
x_RESOURCE_ID => P_TerrRsc_Rec.RESOURCE_ID,
x_GROUP_ID => P_TerrRsc_Rec.GROUP_ID,
x_RESOURCE_TYPE => P_TerrRsc_Rec.RESOURCE_TYPE,
x_ROLE => P_TerrRsc_Rec.ROLE,
x_PRIMARY_CONTACT_FLAG => P_TerrRsc_Rec.PRIMARY_CONTACT_FLAG,
X_START_DATE_ACTIVE => P_TerrRsc_Rec.START_DATE_ACTIVE,
X_END_DATE_ACTIVE => P_TerrRsc_Rec.END_DATE_ACTIVE,
X_FULL_ACCESS_FLAG => P_TerrRsc_Rec.FULL_ACCESS_FLAG,
-- ORG_ID can't be updated. -- VPALLE
X_ORG_ID => FND_API.G_MISS_NUM,
x_attribute_category => P_TerrRsc_Rec.attribute_category,
x_attribute1 => P_TerrRsc_Rec.attribute1,
x_attribute2 => P_TerrRsc_Rec.attribute2,
x_attribute3 => P_TerrRsc_Rec.attribute3,
x_attribute4 => P_TerrRsc_Rec.attribute4,
x_attribute5 => P_TerrRsc_Rec.attribute5,
x_attribute6 => P_TerrRsc_Rec.attribute6,
x_attribute7 => P_TerrRsc_Rec.attribute7,
x_attribute8 => P_TerrRsc_Rec.attribute8,
x_attribute9 => P_TerrRsc_Rec.attribute9,
x_attribute10 => P_TerrRsc_Rec.attribute10,
x_attribute11 => P_TerrRsc_Rec.attribute11,
x_attribute12 => P_TerrRsc_Rec.attribute12,
x_attribute13 => P_TerrRsc_Rec.attribute13,
x_attribute14 => P_TerrRsc_Rec.attribute14,
x_attribute15 => P_TerrRsc_Rec.attribute15
);
FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others exception in Update_Territory_Resources');
End Update_Terr_Resource;
PROCEDURE Update_Terr_Resource
(
P_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
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,
X_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type
)
AS
l_return_Status VARCHAR2(1);
Update_Terr_Resource(P_TerrRsc_Rec => P_TerrRsc_Tbl(l_counter),
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,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
X_TerrRsc_Out_Rec => l_TerrRsc_Out_Rec);
End Update_Terr_Resource;
PROCEDURE Update_Resource_Access
(
P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC,
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,
X_TerrRsc_Access_Out_Rec OUT NOCOPY TerrRsc_Access_out_rec_type
)
AS
Cursor C_GetTerrResAccess(l_TerrRsc_Access_id Number) IS
Select Rowid,
TERR_RSC_ACCESS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_RSC_ID,
ACCESS_TYPE,
TRANS_ACCESS_CODE,
ORG_ID
From jtf_terr_rsc_access_ALL
Where TERR_RSC_ACCESS_ID = l_TerrRsc_Access_id
FOR Update NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Resource_Access';
l_ref_TerrRsc_Access_Rec.LAST_UPDATE_DATE,
l_ref_TerrRsc_Access_Rec. LAST_UPDATED_BY,
l_ref_TerrRsc_Access_Rec.CREATION_DATE,
l_ref_TerrRsc_Access_Rec.CREATED_BY,
l_ref_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN,
l_ref_TerrRsc_Access_Rec.TERR_RSC_ID,
l_ref_TerrRsc_Access_Rec.ACCESS_TYPE,
l_ref_TerrRsc_Access_Rec.TRANS_ACCESS_CODE,
l_ref_TerrRsc_Access_Rec.ORG_ID;
JTF_TERR_RSC_ACCESS_PKG.Update_Row(x_Rowid => l_rowid,
x_TERR_RSC_ACCESS_ID => P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID,
x_LAST_UPDATE_DATE => P_TerrRsc_Access_Rec.LAST_UPDATE_DATE,
x_LAST_UPDATED_BY => P_TerrRsc_Access_Rec.LAST_UPDATED_BY,
x_CREATION_DATE => P_TerrRsc_Access_Rec.CREATION_DATE,
x_CREATED_BY => P_TerrRsc_Access_Rec.CREATED_BY,
x_LAST_UPDATE_LOGIN => P_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN,
x_TERR_RSC_ID => P_TerrRsc_Access_Rec.TERR_RSC_ID,
x_ACCESS_TYPE => P_TerrRsc_Access_Rec.ACCESS_TYPE,
x_TRANS_ACCESS_CODE => P_TerrRsc_Access_Rec.TRANS_ACCESS_CODE,
-- ORG_ID can't be updated. -- VPALEE
X_ORG_ID => FND_API.G_MISS_NUM );
FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others exception in Update_Resource _Access');
End Update_Resource_Access;
PROCEDURE Update_Resource_Access
(
P_TerrRsc_Access_Tbl TerrRsc_Access_Tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
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,
X_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_Tbl_type
)
AS
l_terr_value_id NUMBER;
Update_Resource_Access(P_TerrRsc_Access_Rec => P_TerrRsc_Access_Tbl(l_counter),
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,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_TerrRsc_Access_Out_Rec => l_TerrRsc_Access_Out_Rec);
End Update_Resource_Access;
Select 'X' into l_temp
from JTF_TERR_ALL
where TERR_ID = p_TerrRsc_Tbl(l_res_counter).Terr_Id;
SELECT jta.start_date_active,jta.end_date_active
INTO l_terr_start_date,l_terr_end_date
FROM jtf_terr_all jta
WHERE jta.terr_id = p_TerrRsc_Rec.Terr_Id ;
IF ( p_TerrRsc_Rec.LAST_UPDATED_BY is NULL OR
p_TerrRsc_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_TerrRsc_Rec.LAST_UPDATE_DATE IS NULL OR
p_TerrRsc_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_TerrRsc_Rec.LAST_UPDATE_LOGIN is NULL OR
p_TerrRsc_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_Temp
from JTF_QUAL_TYPES jqt
Where jqt.NAME = p_TerrRsc_Access_Rec.ACCESS_TYPE;
SELECT 'X' INTO l_Temp
FROM ( select DISTINCT lookup_code LOOKUP_CODE
from fnd_lookups
where lookup_type IN ( select rsc_access_lkup
from jtf_sources_all)
)
WHERE LOOKUP_CODE = p_TerrRsc_Access_Rec.TRANS_ACCESS_CODE;
IF ( p_TerrRsc_Access_Rec.LAST_UPDATED_BY is NULL OR
p_TerrRsc_Access_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_TerrRsc_Access_Rec.LAST_UPDATE_DATE IS NULL OR
p_TerrRsc_Access_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_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN is NULL OR
p_TerrRsc_Access_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 JTQ.TERR_QUAL_ID
FROM jtf_seeded_qual_usgs_v jsquv, JTF_TERR_QUAL JTQ
WHERE JTQ.terr_id = p_Terr_id and
JTQ.qual_usg_id = jsquv.qual_usg_id and
jsquv.qual_type_id = -1001 and
jsquv.qual_type_id in ( select related_id
from JTF_QUAL_TYPE_DENORM_V
where qual_type_id = p_qual_type_id);
Select JTV.COMPARISON_OPERATOR, JTV.INCLUDE_FLAG, jsquv.QUAL_COL1,
jsquv.QUAL_COL1_TABLE, jsquv.QUAL_COL1_ALIAS, jsquv.PRIM_INT_CDE_COL_ALIAS,
jsquv.SEC_INT_CDE_COL_ALIAS, jtv.low_value_char,jtv.high_value_char,
jtv.low_value_number, jtv.high_value_number,
jtv.INTEREST_TYPE_ID, jtv.PRIMARY_INTEREST_CODE_ID,
jtv.SECONDARY_INTEREST_CODE_ID, jsquv.DISPLAY_TYPE, jsquv.CONVERT_TO_ID_FLAG,
jtv.ID_USED_FLAG, jtv.CURRENCY_CODE, jtv.LOW_VALUE_CHAR_ID
from jtf_seeded_qual_usgs_v jsquv, jtf_terr_values jtv, jtf_terr_qual jtq
where jtv.terr_qual_id = v_Terr_Qual_Id and
jtv.terr_qual_id = jtq.terr_qual_id and
jtq.qual_usg_id = jsquv.qual_usg_id;
SELECT jrgv.group_name
FROM jtf_rs_groups_vl jrgv
WHERE jrgv.group_id = p_group_id
AND rownum < 2;
SELECT jv.resource_name
FROM jtf_rs_resources_vl jv
WHERE jv.resource_id = lp_resource_id
AND jv.resource_type = lp_resource_type
AND rownum < 2;
SELECT jo.name
FROM jtf_objects_vl jo
WHERE jo.object_code = lp_rs_type_code
AND rownum < 2;
SELECT terr_rsc_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
--terr_id,
resource_id,
group_id,
resource_type,
role,
primary_contact_flag,
start_date_active,
end_date_active,
full_access_flag,
org_id
FROM jtf_terr_rsc_ALL
WHERE terr_id = lp_terr_id;
SELECT
--terr_rsc_access_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
--terr_rsc_id,
access_type,
org_id
FROM jtf_terr_rsc_access_ALL
WHERE terr_rsc_id = lp_terr_rsc_id;
l_TerrRsc_rec.last_update_date,
l_TerrRsc_rec.last_updated_by,
l_TerrRsc_rec.creation_date,
l_TerrRsc_rec.created_by,
l_TerrRsc_rec.last_update_login,
--l_TerrRsc_rec.terr_id,
l_TerrRsc_rec.resource_id,
l_TerrRsc_rec.group_id,
l_TerrRsc_rec.resource_type,
l_TerrRsc_rec.role,
l_TerrRsc_rec.primary_contact_flag,
l_TerrRsc_rec.start_date_active,
l_TerrRsc_rec.end_date_active,
l_TerrRsc_rec.full_access_flag,
l_TerrRsc_rec.org_id;
l_TerrRsc_Access_rec.last_update_date,
l_TerrRsc_Access_rec.last_updated_by,
l_TerrRsc_Access_rec.creation_date,
l_TerrRsc_Access_rec.created_by,
l_TerrRsc_Access_rec.last_update_login,
--l_TerrRsc_Access_rec.terr_rsc_id,
l_TerrRsc_Access_rec.access_type,
l_TerrRsc_Access_rec.org_id;
SELECT 'X'
FROM JTF_TERR_RSC_ACCESS_ALL
WHERE terr_rsc_id = lp_terr_rsc_id
AND access_type = lp_access_type;
p_delete_flag IN VARCHAR2 := 'Y',
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) AS
l_api_name CONSTANT VARCHAR2(30) := 'Transfer_Resource_Territories';
select j.terr_id
from jtf_terr_rsc_ALL j, jtf_terr_ALL jt
where j.resource_id = l_resource_id
and j.resource_type = l_resource_type
and j.terr_id = jt.terr_id
and jt.template_flag = 'N'
and jt.escalation_territory_flag = 'N'
--ARPATEL: bug#2897391
and ( jt.terr_group_flag is null OR jt.terr_group_flag = 'N' )
and not jt.terr_id = 1;
select terr_id
from JTF_TERR_ALL jt
where NOT EXISTS (select jtr.terr_id
from jtf_terr_rsc_ALL jtr
where jt.terr_id = jtr.terr_id
)
and jt.template_flag = 'N'
and jt.escalation_territory_flag = 'N'
--ARPATEL: bug#2897391
and ( jt.terr_group_flag is null OR jt.terr_group_flag = 'N' )
and not jt.terr_id = 1;
INSERT INTO JTF_TERR_RSC_ALL(
TERR_RSC_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_ID,
RESOURCE_ID,
GROUP_ID,
RESOURCE_TYPE,
ROLE,
PRIMARY_CONTACT_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
FULL_ACCESS_FLAG,
ORG_ID
) VALUES (
JTF_TERR_RSC_s.nextval,
decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
);
IF p_delete_flag = 'Y'
THEN
FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
INSERT INTO JTF_TERR_RSC_ALL(
TERR_RSC_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_ID,
RESOURCE_ID,
GROUP_ID,
RESOURCE_TYPE,
ROLE,
PRIMARY_CONTACT_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
FULL_ACCESS_FLAG,
ORG_ID
) VALUES (
JTF_TERR_RSC_s.nextval,
decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
);
INSERT INTO JTF_TERR_RSC_ACCESS_ALL(
TERR_RSC_ACCESS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_RSC_ID,
ACCESS_TYPE,
ORG_ID
)
SELECT
JTF_TERR_RSC_ACCESS_s.nextval,
SYSDATE,
G_USER_ID,
SYSDATE,
G_USER_ID,
G_LOGIN_ID,
ntra.terr_rsc_id, -- needs to be the newly created terr_rsc_id from above
raa.access_type,
p_dest_resource_rec.ORG_ID
FROM
JTF_TERR_RSC_ACCESS_ALL raa
,JTF_TERR_RSC_ALL tra -- use old record to find access_type
,JTF_TERR_RSC_ALL ntra -- pick up new records from above
WHERE
tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
AND tra.resource_id = p_source_resource_rec.resource_id
AND ntra.terr_id = tra.terr_id
AND ntra.resource_id = p_dest_resource_rec.resource_id
AND tra.terr_id = l_terr_ids_tbl(i)
;
DELETE from jtf_terr_rsc_ALL
where terr_id = l_terr_ids_tbl(i)
and resource_id = p_source_resource_rec.resource_id;
INSERT INTO JTF_TERR_RSC_ALL(
TERR_RSC_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_ID,
RESOURCE_ID,
GROUP_ID,
RESOURCE_TYPE,
ROLE,
PRIMARY_CONTACT_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
FULL_ACCESS_FLAG,
ORG_ID
) VALUES (
JTF_TERR_RSC_s.nextval,
decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
);
INSERT INTO JTF_TERR_RSC_ACCESS_ALL(
TERR_RSC_ACCESS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_RSC_ID,
ACCESS_TYPE,
ORG_ID
)
SELECT
JTF_TERR_RSC_ACCESS_s.nextval,
SYSDATE,
G_USER_ID,
SYSDATE,
G_USER_ID,
G_LOGIN_ID,
ntra.terr_rsc_id, -- needs to be the newly created terr_rsc_id from above
raa.access_type,
p_dest_resource_rec.ORG_ID
FROM
JTF_TERR_RSC_ACCESS_ALL raa
,JTF_TERR_RSC_ALL tra -- use old record to find access_type
,JTF_TERR_RSC_ALL ntra -- pick up new records from above
WHERE
tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
AND tra.resource_id = p_source_resource_rec.resource_id
AND ntra.terr_id = tra.terr_id
AND ntra.resource_id = p_dest_resource_rec.resource_id
AND tra.terr_id = l_terr_ids_tbl(i)
;
UPDATE jtf_terr_rsc_all j
SET j.end_date_active = SYSDATE
WHERE j.resource_id = p_source_resource_rec.RESOURCE_ID
AND j.resource_type = p_source_resource_rec.RESOURCE_TYPE
AND j.terr_id = l_terr_ids_tbl(i);