[Home] [Help]
41: -- l_terr_id NUMBER := P_TERR_ALL_REC.TERR_ID;
42: -- 08/08/00 jdochert Removing if statement that causes error when LOW_VALUE_CHAR = FND_API.G_MISS_CHAR
43: -- and ID_USED_FLAG = 'Y": this was an error in logic.
44: -- 08/21/00 jdochert Changing overlap_exists procedure to do an UPPER on comparison operator
45: -- 09/09/00 jdochert Added Unique validation for JTF_TERR_USGS_ALL + JTF_TERR_QTYPE_USGS_ALL
46: -- 09/17/00 jdochert BUG#1408610 FIX
47: --
48: -- 10/04/00 jdochert Added validation for NUM_WINNERS
49: -- 11/02/00 jdochert Added processing for NUM_QUAL
600: --Delete Territory qual type usgs
601: DELETE from JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID = c.Terr_Id;
602:
603: --Delete Territory usgs
604: DELETE from JTF_TERR_USGS_ALL WHERE TERR_ID = c.Terr_Id;
605:
606: --Delete Territory Resource Access
607: DELETE from JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
608: ( SELECT TERR_RSC_ID FROM JTF_TERR_RSC_ALL WHERE TERR_ID = c.Terr_Id );
1754: FOR l_Counter IN 1 .. l_Terr_Usgs_Tbl_Count LOOP
1755: --
1756: BEGIN
1757:
1758: --dbms_output('Create_Territory_Usages PVT: Before Calling JTF_TERR_USGS_PKG.Insert_Row');
1759:
1760: -- Initialize API return status to success
1761: x_return_status := FND_API.G_RET_STS_SUCCESS;
1762:
1805: IF (l_terr_usg_id = FND_API.G_MISS_NUM) THEN
1806: l_terr_usg_id := NULL;
1807: END IF;
1808:
1809: JTF_TERR_USGS_PKG.Insert_Row(x_Rowid => l_rowid,
1810: x_TERR_USG_ID => l_terr_usg_id,
1811: x_LAST_UPDATE_DATE => P_Terr_Usgs_Tbl(l_Counter).LAST_UPDATE_DATE,
1812: x_LAST_UPDATED_BY => P_Terr_Usgs_Tbl(l_Counter).LAST_UPDATED_BY,
1813: x_CREATION_DATE => P_Terr_Usgs_Tbl(l_Counter).CREATION_DATE,
3336: CREATED_BY,
3337: LAST_UPDATE_LOGIN,
3338: TERR_ID,
3339: SOURCE_ID
3340: From JTF_TERR_USGS_ALL
3341: Where terr_usg_id = l_terr_usg_id
3342: FOR Update NOWAIT;
3343: --Local variable declaration
3344: l_api_name CONSTANT VARCHAR2(30) := 'Update_territory_Usages';
3409: raise FND_API.G_EXC_ERROR;
3410: End if;
3411: CLOSE C_GetTerritoryUsage;
3412:
3413: --dbms_output('Update_Territory_Usages PVT: Before Calling JTF_TERR_USGS_PKG.Update_Row');
3414: -- Call insert terr_Qual_Type_Usgs table handler
3415: JTF_TERR_USGS_PKG.Update_Row(x_Rowid => l_rowid,
3416: x_TERR_USG_ID => P_Terr_Usgs_Rec.terr_usg_id,
3417: x_LAST_UPDATE_DATE => P_Terr_Usgs_Rec.LAST_UPDATE_DATE,
3411: CLOSE C_GetTerritoryUsage;
3412:
3413: --dbms_output('Update_Territory_Usages PVT: Before Calling JTF_TERR_USGS_PKG.Update_Row');
3414: -- Call insert terr_Qual_Type_Usgs table handler
3415: JTF_TERR_USGS_PKG.Update_Row(x_Rowid => l_rowid,
3416: x_TERR_USG_ID => P_Terr_Usgs_Rec.terr_usg_id,
3417: x_LAST_UPDATE_DATE => P_Terr_Usgs_Rec.LAST_UPDATE_DATE,
3418: x_LAST_UPDATED_BY => P_Terr_Usgs_Rec.LAST_UPDATED_BY,
3419: x_CREATION_DATE => P_Terr_Usgs_Rec.CREATION_DATE,
4670: x_return_status := FND_API.G_RET_STS_SUCCESS;
4671: --
4672: --dbms_output('Delete_Territory_Usages: Before Calling Create_TerrType_Qualifier TBL');
4673: --
4674: JTF_TERR_USGS_PKG.Delete_Row(x_TERR_USG_ID => P_Terr_usg_Id);
4675: --
4676: --Prepare message name
4677: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
4678: IF SQL%FOUND THEN
4681: l_row_count := SQL%ROWCOUNT;
4682: END IF;
4683: --Prepare message token
4684: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
4685: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_USGS');
4686: FND_MSG_PUB.ADD;
4687:
4688: -- Debug Message
4689: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4711: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4712: l_row_count := 0;
4713: --Prepare message token
4714: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
4715: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_USGS');
4716: FND_MSG_PUB.ADD;
4717: --
4718: FND_MSG_PUB.Count_And_Get
4719: ( p_count => x_msg_count,
5849: l_qual_Type_Usg_id := p_Terr_QualTypeUsgs_Rec.QUAL_TYPE_USG_ID;
5850: --Check the qual_type_usg_id specified is valid
5851: Select 1
5852: into l_dummy
5853: From jtf_terr_usgs_ALL jtu, jtf_qual_type_usgs_ALL jqtu
5854: where jtu.terr_id = p_Terr_Id and
5855: jqtu.source_id = jtu.source_id and
5856: jqtu.qual_type_usg_id = l_qual_Type_Usg_id;
5857:
6296: AS
6297: BEGIN
6298: Select Max(rank)
6299: into x_Rank
6300: from jtf_terr_ALL jt, jtf_terr_usgs_ALL jtu
6301: where jt.Parent_Territory_id = p_Parent_Terr_Id and
6302: jt.Terr_Id = jtu.Terr_Id and
6303: jtu.source_id = p_Source_Id;
6304:
6531: , j1.CREATION_DATE
6532: , j1.CREATED_BY
6533: , j1.LAST_UPDATE_LOGIN
6534: , j1.ORG_ID
6535: FROM jtf_terr_usgs j1
6536: WHERE j1.terr_id = p_template_terr_id;
6537:
6538: /* cursor to get territory's qual type usages */
6539: CURSOR c_get_terr_qtype_usgs (p_template_terr_id NUMBER) IS
8510:
8511: END Overlap_Exists;
8512:
8513: -- jdochert 09/09
8514: -- check for Unique Key constraint violation on JTF_TERR_USGS table
8515: PROCEDURE validate_terr_usgs_UK(
8516: p_Terr_Id IN NUMBER,
8517: p_Source_Id IN NUMBER,
8518: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8524: -- cursor to check that Unique Key constraint not violated
8525: CURSOR csr_chk_uk_violation ( lp_terr_id NUMBER
8526: , lp_source_id NUMBER) IS
8527: SELECT 'X'
8528: FROM JTF_TERR_USGS_ALL
8529: WHERE terr_id = lp_terr_id
8530: AND source_id = lp_source_id;
8531:
8532: l_return_csr VARCHAR2(1);
8546: FETCH csr_chk_uk_violation INTO l_return_csr;
8547:
8548: IF csr_chk_uk_violation%FOUND THEN
8549:
8550: --dbms_output.put_line('Validate_Unique_Key: jtf_terr_usgs: VIOLATION');
8551:
8552: x_return_status := FND_API.G_RET_STS_ERROR;
8553:
8554: /* Debug message */
8552: x_return_status := FND_API.G_RET_STS_ERROR;
8553:
8554: /* Debug message */
8555: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
8556: FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_USGS_UK_CON');
8557: FND_MSG_PUB.ADD;
8558: END IF;
8559:
8560: END IF; /* c_chk_uk_violation%FOUND */