[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
608: --Delete Territory qual type usgs
609: DELETE from JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID = c.Terr_Id;
610:
611: --Delete Territory usgs
612: DELETE from JTF_TERR_USGS_ALL WHERE TERR_ID = c.Terr_Id;
613:
614: --Delete Territory Resource Access
615: DELETE from JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
616: ( SELECT TERR_RSC_ID FROM JTF_TERR_RSC_ALL WHERE TERR_ID = c.Terr_Id );
1349: AND p_terr_all_rec.PARENT_TERRITORY_ID <> 1 ) THEN
1350:
1351: SELECT 1
1352: INTO l_dummy
1353: FROM jtf_terr_usgs_all jtua
1354: WHERE jtua.terr_id = p_terr_all_rec.PARENT_TERRITORY_ID
1355: AND jtua.source_id = p_terr_usgs_tbl (1).source_id
1356: AND jtua.org_id = p_terr_all_rec.org_id ;
1357: END IF;
1855: FOR l_Counter IN 1 .. l_Terr_Usgs_Tbl_Count LOOP
1856: --
1857: BEGIN
1858:
1859: --dbms_output('Create_Territory_Usages PVT: Before Calling JTF_TERR_USGS_PKG.Insert_Row');
1860:
1861: -- Initialize API return status to success
1862: x_return_status := FND_API.G_RET_STS_SUCCESS;
1863:
1906: IF (l_terr_usg_id = FND_API.G_MISS_NUM) THEN
1907: l_terr_usg_id := NULL;
1908: END IF;
1909:
1910: JTF_TERR_USGS_PKG.Insert_Row(x_Rowid => l_rowid,
1911: x_TERR_USG_ID => l_terr_usg_id,
1912: x_LAST_UPDATE_DATE => P_Terr_Usgs_Tbl(l_Counter).LAST_UPDATE_DATE,
1913: x_LAST_UPDATED_BY => P_Terr_Usgs_Tbl(l_Counter).LAST_UPDATED_BY,
1914: x_CREATION_DATE => P_Terr_Usgs_Tbl(l_Counter).CREATION_DATE,
3711: CREATED_BY,
3712: LAST_UPDATE_LOGIN,
3713: TERR_ID,
3714: SOURCE_ID
3715: From JTF_TERR_USGS_ALL
3716: Where terr_usg_id = l_terr_usg_id
3717: FOR Update NOWAIT;
3718: --Local variable declaration
3719: l_api_name CONSTANT VARCHAR2(30) := 'Update_territory_Usages';
3784: raise FND_API.G_EXC_ERROR;
3785: End if;
3786: CLOSE C_GetTerritoryUsage;
3787:
3788: --dbms_output('Update_Territory_Usages PVT: Before Calling JTF_TERR_USGS_PKG.Update_Row');
3789: -- Call insert terr_Qual_Type_Usgs table handler
3790: JTF_TERR_USGS_PKG.Update_Row(x_Rowid => l_rowid,
3791: x_TERR_USG_ID => P_Terr_Usgs_Rec.terr_usg_id,
3792: x_LAST_UPDATE_DATE => P_Terr_Usgs_Rec.LAST_UPDATE_DATE,
3786: CLOSE C_GetTerritoryUsage;
3787:
3788: --dbms_output('Update_Territory_Usages PVT: Before Calling JTF_TERR_USGS_PKG.Update_Row');
3789: -- Call insert terr_Qual_Type_Usgs table handler
3790: JTF_TERR_USGS_PKG.Update_Row(x_Rowid => l_rowid,
3791: x_TERR_USG_ID => P_Terr_Usgs_Rec.terr_usg_id,
3792: x_LAST_UPDATE_DATE => P_Terr_Usgs_Rec.LAST_UPDATE_DATE,
3793: x_LAST_UPDATED_BY => P_Terr_Usgs_Rec.LAST_UPDATED_BY,
3794: x_CREATION_DATE => P_Terr_Usgs_Rec.CREATION_DATE,
5084: x_return_status := FND_API.G_RET_STS_SUCCESS;
5085: --
5086: --dbms_output('Delete_Territory_Usages: Before Calling Create_TerrType_Qualifier TBL');
5087: --
5088: JTF_TERR_USGS_PKG.Delete_Row(x_TERR_USG_ID => P_Terr_usg_Id);
5089: --
5090: --Prepare message name
5091: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
5092: IF SQL%FOUND THEN
5095: l_row_count := SQL%ROWCOUNT;
5096: END IF;
5097: --Prepare message token
5098: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5099: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_USGS');
5100: FND_MSG_PUB.ADD;
5101:
5102: -- Debug Message
5103: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5125: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5126: l_row_count := 0;
5127: --Prepare message token
5128: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5129: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_USGS');
5130: FND_MSG_PUB.ADD;
5131: --
5132: FND_MSG_PUB.Count_And_Get
5133: ( p_count => x_msg_count,
6231: AND p_terr_all_rec.PARENT_TERRITORY_ID <> 1) THEN
6232:
6233: SELECT 1
6234: INTO l_dummy
6235: FROM jtf_terr_usgs_all childusg, jtf_terr_usgs_all parusg
6236: WHERE childusg.terr_id = p_terr_all_rec.TERR_ID
6237: AND parusg.terr_id = p_terr_all_rec.PARENT_TERRITORY_ID
6238: AND childusg.source_id = parusg.source_id
6239: AND childusg.org_id = parusg.org_id ;
6678: l_qual_Type_Usg_id := p_Terr_QualTypeUsgs_Rec.QUAL_TYPE_USG_ID;
6679: --Check the qual_type_usg_id specified is valid
6680: Select 1
6681: into l_dummy
6682: From jtf_terr_usgs_ALL jtu, jtf_qual_type_usgs_ALL jqtu
6683: where jtu.terr_id = p_Terr_Id and
6684: jqtu.source_id = jtu.source_id and
6685: jqtu.qual_type_usg_id = l_qual_Type_Usg_id;
6686:
7860: AS
7861: BEGIN
7862: Select Max(rank)
7863: into x_Rank
7864: from jtf_terr_ALL jt, jtf_terr_usgs_ALL jtu
7865: where jt.Parent_Territory_id = p_Parent_Terr_Id and
7866: jt.Terr_Id = jtu.Terr_Id and
7867: jtu.source_id = p_Source_Id;
7868:
8095: , j1.CREATION_DATE
8096: , j1.CREATED_BY
8097: , j1.LAST_UPDATE_LOGIN
8098: , j1.ORG_ID
8099: FROM jtf_terr_usgs j1
8100: WHERE j1.terr_id = p_template_terr_id;
8101:
8102: /* cursor to get territory's qual type usages */
8103: CURSOR c_get_terr_qtype_usgs (p_template_terr_id NUMBER) IS
10074:
10075: END Overlap_Exists;
10076:
10077: -- jdochert 09/09
10078: -- check for Unique Key constraint violation on JTF_TERR_USGS table
10079: PROCEDURE validate_terr_usgs_UK(
10080: p_Terr_Id IN NUMBER,
10081: p_Source_Id IN NUMBER,
10082: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
10088: -- cursor to check that Unique Key constraint not violated
10089: CURSOR csr_chk_uk_violation ( lp_terr_id NUMBER
10090: , lp_source_id NUMBER) IS
10091: SELECT 'X'
10092: FROM JTF_TERR_USGS_ALL
10093: WHERE terr_id = lp_terr_id
10094: AND source_id = lp_source_id;
10095:
10096: l_return_csr VARCHAR2(1);
10110: FETCH csr_chk_uk_violation INTO l_return_csr;
10111:
10112: IF csr_chk_uk_violation%FOUND THEN
10113:
10114: --dbms_output.put_line('Validate_Unique_Key: jtf_terr_usgs: VIOLATION');
10115:
10116: x_return_status := FND_API.G_RET_STS_ERROR;
10117:
10118: /* Debug message */
10116: x_return_status := FND_API.G_RET_STS_ERROR;
10117:
10118: /* Debug message */
10119: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
10120: FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_USGS_UK_CON');
10121: FND_MSG_PUB.ADD;
10122: END IF;
10123:
10124: END IF; /* c_chk_uk_violation%FOUND */