[Home] [Help]
1: Package BODY JTF_TERRITORY_PVT AS
2: /* $Header: jtfvterb.pls 120.3 2005/10/14 16:12:30 shli ship $ */
3: -- ---------------------------------------------------
4: -- Start of Comments
5: -- ---------------------------------------------------
2: /* $Header: jtfvterb.pls 120.3 2005/10/14 16:12:30 shli ship $ */
3: -- ---------------------------------------------------
4: -- Start of Comments
5: -- ---------------------------------------------------
6: -- PACKAGE NAME: JTF_TERRITORY_PVT
7: -- ---------------------------------------------------
8: -- PURPOSE
9: -- Joint task force core territory manager private api's.
10: -- This package is defins all private API for manupulating
7: -- ---------------------------------------------------
8: -- PURPOSE
9: -- Joint task force core territory manager private api's.
10: -- This package is defins all private API for manupulating
11: -- territory related information in JTF Territory tables.
12: --
13: -- Procedures:
14: -- (see below for specification)
15: --
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
51: -- 04/05/01 ARPATEL Added processing for copy hierarchy in proc 'Copy Territory'
52: -- 04/12/01 jdochert Added PROCEDURE chk_num_copy_terr
53: -- 04/20/01 ARPATEL Added Concurrent_Copy_Territory procedure for concurrent requests
54: -- 04/28/01 ARPATEL Added FUNCTION conc_req_copy_terr returning concurrent request ID.
55: -- 05/22/01 ARPATEL Added CNR_GROUP_ID to cursors selecting from JTF_TERR_VALUES.
56: -- 07/13/01 ARPATEL Added validation for territory templates without 'DYNAMIC QUALIFIERS'
57: -- 09/06/01 ARPATEL Added VALUE1_ID, VALUE2_ID AND VALUE3_ID to JTF_TERR_VALUES cursors
58: -- and procedure create_new_terr_value_rec
59: -- 09/18/01 ARPATEL Removed debug message level checks to enable form to pick up these messages
53: -- 04/20/01 ARPATEL Added Concurrent_Copy_Territory procedure for concurrent requests
54: -- 04/28/01 ARPATEL Added FUNCTION conc_req_copy_terr returning concurrent request ID.
55: -- 05/22/01 ARPATEL Added CNR_GROUP_ID to cursors selecting from JTF_TERR_VALUES.
56: -- 07/13/01 ARPATEL Added validation for territory templates without 'DYNAMIC QUALIFIERS'
57: -- 09/06/01 ARPATEL Added VALUE1_ID, VALUE2_ID AND VALUE3_ID to JTF_TERR_VALUES cursors
58: -- and procedure create_new_terr_value_rec
59: -- 09/18/01 ARPATEL Removed debug message level checks to enable form to pick up these messages
60: -- 12/03/04 achanda Added value4_id : bug # 3726007
61: --
65:
66: -- ***************************************************
67: -- GLOBAL VARIABLES
68: -- ***************************************************
69: G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_TERRITORY_PVT';
70: G_FILE_NAME CONSTANT VARCHAR2(12):='jtfvterb.pls';
71:
72:
73: G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
230:
231: -- Debug Message
232: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
233: THEN
234: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
235: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
236: FND_MSG_PUB.Add;
237: END IF;
238:
241: --
242: --Check whether the Usage records are specified
243: If (p_Terr_Usgs_Tbl.COUNT = 0 ) Then
244: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
245: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERR_USAGE');
246: FND_MSG_PUB.ADD;
247: END IF;
248: x_return_status := FND_API.G_RET_STS_ERROR;
249: raise FND_API.G_EXC_ERROR;
254: -- Check whether the Terr Transaction Types are specified.
255: If (p_Terr_QualTypeUsgs_Tbl.COUNT = 0 and
256: p_Terr_All_Rec.ESCALATION_TERRITORY_FLAG <> 'Y') Then
257: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
258: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERR_QUAL_USG');
259: FND_MSG_PUB.ADD;
260: END IF;
261: x_return_status := FND_API.G_RET_STS_ERROR;
262: raise FND_API.G_EXC_ERROR;
301:
302: --Check whether the Qualifer records are specified
303: --If (p_Terr_Qual_Tbl.count = 0 ) Then
304: -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
305: -- FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERR_QUAL');
306: -- FND_MSG_PUB.ADD;
307: -- END IF;
308: -- x_return_status := FND_API.G_RET_STS_ERROR;
309: -- raise FND_API.G_EXC_ERROR;
316: -- JDOCHERT 07/27 Territory Values no longer required
317: --dbms_output('About to check p_Terr_Values_Tbl.count - ' || to_char(p_Terr_Values_Tbl.count));
318: --If (p_Terr_Values_Tbl.Count = 0 ) Then
319: -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
320: -- FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERR_VALUES');
321: -- FND_MSG_PUB.ADD;
322: -- END IF;
323: -- x_return_status := FND_API.G_RET_STS_ERROR;
324: -- raise FND_API.G_EXC_ERROR;
380: --
381: -- JDOCHERT 07/27 Territory Values no longer required
382: --If l_Index = 0 Then
383: -- x_return_status := FND_API.G_RET_STS_ERROR;
384: -- FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERR_VALUES');
385: -- FND_MSG_PUB.ADD;
386: -- raise FND_API.G_EXC_ERROR;
387: --End If;
388: --
440:
441: -- Debug Message
442: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
443: THEN
444: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
445: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
446: FND_MSG_PUB.Add;
447: END IF;
448:
539: v_Terr_Id NUMBER := p_Terr_Id;
540:
541: --Declare cursor to get usages
542: Cursor C_Terr IS
543: select terr_id from jtf_terr_all
544: connect by parent_territory_id = prior terr_id AND TERR_ID <> 1
545: start with terr_id = v_Terr_Id;
546:
547:
578:
579: -- Debug Message
580: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
581: THEN
582: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
583: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
584: FND_MSG_PUB.Add;
585: END IF;
586:
590: -- Get all the Territories in the Hierarchy
591: FOR c in c_Terr LOOP
592: --
593: --Delete Territory Values
594: DELETE from JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
595: ( SELECT TERR_QUAL_ID FROM JTF_TERR_QUAL_ALL WHERE TERR_ID = c.Terr_Id );
596:
597: --Delete Territory Qualifer records
598: DELETE from JTF_TERR_QUAL_ALL WHERE TERR_ID = c.Terr_Id;
591: FOR c in c_Terr LOOP
592: --
593: --Delete Territory Values
594: DELETE from JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
595: ( SELECT TERR_QUAL_ID FROM JTF_TERR_QUAL_ALL WHERE TERR_ID = c.Terr_Id );
596:
597: --Delete Territory Qualifer records
598: DELETE from JTF_TERR_QUAL_ALL WHERE TERR_ID = c.Terr_Id;
599:
594: DELETE from JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
595: ( SELECT TERR_QUAL_ID FROM JTF_TERR_QUAL_ALL WHERE TERR_ID = c.Terr_Id );
596:
597: --Delete Territory Qualifer records
598: DELETE from JTF_TERR_QUAL_ALL WHERE TERR_ID = c.Terr_Id;
599:
600: --Delete Territory qual type usgs
601: DELETE from JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID = c.Terr_Id;
602:
597: --Delete Territory Qualifer records
598: DELETE from JTF_TERR_QUAL_ALL WHERE TERR_ID = c.Terr_Id;
599:
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:
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 );
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 );
609:
610: -- Delete the Territory Resource records
611: DELETE from JTF_TERR_RSC_ALL Where TERR_ID = c.Terr_Id;
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 );
609:
610: -- Delete the Territory Resource records
611: DELETE from JTF_TERR_RSC_ALL Where TERR_ID = c.Terr_Id;
612:
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 );
609:
610: -- Delete the Territory Resource records
611: DELETE from JTF_TERR_RSC_ALL Where TERR_ID = c.Terr_Id;
612:
613: --Delete Territory record
614: DELETE from JTF_TERR_ALL WHERE TERR_ID = c.Terr_Id;
615:
610: -- Delete the Territory Resource records
611: DELETE from JTF_TERR_RSC_ALL Where TERR_ID = c.Terr_Id;
612:
613: --Delete Territory record
614: DELETE from JTF_TERR_ALL WHERE TERR_ID = c.Terr_Id;
615:
616: --
617: END LOOP;
618:
618:
619: -- Debug Message
620: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
621: THEN
622: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
623: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
624: FND_MSG_PUB.Add;
625: END IF;
626:
758:
759: -- Debug Message
760: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
761: THEN
762: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
763: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
764: FND_MSG_PUB.Add;
765: END IF;
766:
866:
867: -- Debug Message
868: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
869: THEN
870: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
871: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
872: FND_MSG_PUB.Add;
873: END IF;
874:
1011: ATTRIBUTE15,
1012: ORG_ID,
1013: NUM_WINNERS,
1014: NUM_QUAL
1015: From JTF_TERR_ALL
1016: Where TERR_ID = l_terr_id
1017: For Update NOWAIT;
1018:
1019: --Local variable declaration
1046:
1047: -- Debug Message
1048: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1049: THEN
1050: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1051: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
1052: FND_MSG_PUB.Add;
1053: END IF;
1054:
1121: raise FND_API.G_EXC_ERROR;
1122: End if;
1123: CLOSE C_GetTerritory;
1124:
1125: --dbms_output('Deactivate_Territory PVT: Before Calling JTF_TERR_PKG.Update_Row');
1126: JTF_TERR_PKG.Update_Row(x_rowid => l_rowid,
1127: x_terr_id => p_terr_id,
1128: x_last_update_date => l_ref_terr_all_rec.LAST_UPDATE_DATE,
1129: x_last_updated_by => l_ref_terr_all_rec.LAST_UPDATED_BY,
1122: End if;
1123: CLOSE C_GetTerritory;
1124:
1125: --dbms_output('Deactivate_Territory PVT: Before Calling JTF_TERR_PKG.Update_Row');
1126: JTF_TERR_PKG.Update_Row(x_rowid => l_rowid,
1127: x_terr_id => p_terr_id,
1128: x_last_update_date => l_ref_terr_all_rec.LAST_UPDATE_DATE,
1129: x_last_updated_by => l_ref_terr_all_rec.LAST_UPDATED_BY,
1130: x_creation_date => l_ref_terr_all_rec.CREATION_DATE,
1175:
1176: -- Debug Message
1177: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1178: THEN
1179: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1180: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
1181: FND_MSG_PUB.Add;
1182: END IF;
1183:
1298:
1299: -- Debug Message
1300: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1301: THEN
1302: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1303: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
1304: FND_MSG_PUB.Add;
1305: END IF;
1306: -- Initialize API return status to success
1322: X_Terr_Id => l_terr_id,
1323: X_Terr_All_Out_Rec => l_Terr_All_Out_Rec);
1324:
1325: ELSE
1326: UPDATE JTF_TERR_ALL
1327: SET terr_group_id = p_terr_all_rec.territory_group_id,
1328: LAST_UPDATED_BY = p_terr_all_rec.LAST_UPDATED_BY,
1329: LAST_UPDATE_LOGIN = p_terr_all_rec.LAST_UPDATE_LOGIN,
1330: NAME = p_terr_all_rec.NAME,
1377: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1378: RAISE FND_API.G_EXC_ERROR;
1379: END IF;
1380: --
1381: -- Call api to insert records into jtf_terr_qualtype_usgs
1382: --
1383: --dbms_output('Create_Territory_Header PVT: Before Calling Create_Terr_QualType_Usage');
1384:
1385: Create_Terr_QualType_Usage(P_Api_Version_Number => P_Api_Version_Number,
1406: --
1407: -- Debug Message
1408: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1409: THEN
1410: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
1411: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
1412: FND_MSG_PUB.Add;
1413: END IF;
1414:
1446: -- start of comments
1447: -- ***************************************************
1448: -- API name : Create_Territory_record
1449: -- Type : PUBLIC
1450: -- Function : To create a records in jtf_Terr_all table
1451: --
1452: -- Pre-reqs :
1453: -- Parameters:
1454: -- IN :
1508:
1509: -- Debug Message
1510: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1511: THEN
1512: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1513: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
1514: FND_MSG_PUB.Add;
1515: END IF;
1516:
1521: THEN
1522: -- Debug message
1523: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1524: THEN
1525: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
1526: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Territory_Record');
1527: FND_MSG_PUB.Add;
1528: END IF;
1529: --
1540: END IF;
1541:
1542: -- Call insert terr_all table handler
1543: --
1544: --dbms_output('Create_Territory_Record PVT: Before Calling JTF_TERR_PKG.Insert_Row APP_SHORT_NAME-' || G_APP_SHORT_NAME);
1545: --dbms_output('Create_Territory_Record PVT: Before Calling JTF_TERR_PKG.Insert_Row p_terr_all_rec.APPLICATION_SHORT_NAME-' || p_terr_all_rec.APPLICATION_SHORT_NAME);
1546:
1547: /* Intialise to NULL if FND_API.G_MISS_NUM,
1548: ** otherwise use passed in value
1541:
1542: -- Call insert terr_all table handler
1543: --
1544: --dbms_output('Create_Territory_Record PVT: Before Calling JTF_TERR_PKG.Insert_Row APP_SHORT_NAME-' || G_APP_SHORT_NAME);
1545: --dbms_output('Create_Territory_Record PVT: Before Calling JTF_TERR_PKG.Insert_Row p_terr_all_rec.APPLICATION_SHORT_NAME-' || p_terr_all_rec.APPLICATION_SHORT_NAME);
1546:
1547: /* Intialise to NULL if FND_API.G_MISS_NUM,
1548: ** otherwise use passed in value
1549: */
1550: IF (l_terr_id = FND_API.G_MISS_NUM) THEN
1551: l_terr_id := NULL;
1552: END IF;
1553:
1554: JTF_TERR_PKG.Insert_Row(x_rowid => l_rowid,
1555: x_terr_id => l_terr_id,
1556: x_last_update_date => p_terr_all_rec.LAST_UPDATE_DATE,
1557: x_last_updated_by => p_terr_all_rec.LAST_UPDATED_BY,
1558: x_creation_date => p_terr_all_rec.CREATION_DATE,
1608:
1609: -- Debug Message
1610: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1611: THEN
1612: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1613: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
1614: FND_MSG_PUB.Add;
1615: END IF;
1616:
1731:
1732: -- Debug Message
1733: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1734: THEN
1735: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1736: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
1737: FND_MSG_PUB.Add;
1738: END IF;
1739:
1742:
1743: --
1744: If (p_Terr_Usgs_Tbl.count = 0 ) Then
1745: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1746: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERR_USAGE');
1747: FND_MSG_PUB.ADD;
1748: END IF;
1749: x_return_status := FND_API.G_RET_STS_ERROR;
1750: RAISE FND_API.G_EXC_ERROR;
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,
1874: END LOOP;
1875: --
1876: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1877: THEN
1878: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
1879: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
1880: FND_MSG_PUB.Add;
1881: END IF;
1882: --
1964:
1965: -- Debug Message
1966: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1967: THEN
1968: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1969: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
1970: FND_MSG_PUB.Add;
1971: END IF;
1972:
1978:
1979: -- Debug message
1980: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1981: THEN
1982: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
1983: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Qtype_Usage');
1984: FND_MSG_PUB.Add;
1985: END IF;
1986:
2009: RAISE FND_API.G_EXC_ERROR;
2010: END IF;
2011:
2012: END IF;
2013: --dbms_output('Create_Terr_QualType_Usage PVT(REC): Before Calling JTF_TERR_QTYPE_USGS_PKG.Insert_Row');
2014:
2015: /* Intialise to NULL if FND_API.G_MISS_NUM,
2016: ** otherwise used passed in value
2017: */
2020: END IF;
2021:
2022:
2023: -- Call insert terr_Qual_Type_Usgs table handler
2024: JTF_TERR_QTYPE_USGS_PKG.Insert_Row(x_Rowid => l_rowid,
2025: x_TERR_QTYPE_USG_ID => l_TERR_QUAL_TYPE_USG_ID,
2026: x_LAST_UPDATED_BY => P_Terr_QualTypeUsgs_Rec.LAST_UPDATED_BY,
2027: x_LAST_UPDATE_DATE => P_Terr_QualTypeUsgs_Rec.LAST_UPDATE_DATE,
2028: x_CREATED_BY => P_Terr_QualTypeUsgs_Rec.CREATED_BY,
2041: X_Terr_QualTypeUsgs_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
2042:
2043: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2044: THEN
2045: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2046: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2047: FND_MSG_PUB.Add;
2048: END IF;
2049:
2170:
2171: -- Debug Message
2172: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2173: THEN
2174: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
2175: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2176: FND_MSG_PUB.Add;
2177: END IF;
2178:
2231: END LOOP;
2232:
2233: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2234: THEN
2235: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2236: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2237: FND_MSG_PUB.Add;
2238: END IF;
2239:
2259: */
2260: PROCEDURE update_terr_num_qual(p_terr_id IN NUMBER, p_qual_type_id IN NUMBER) AS
2261: BEGIN
2262:
2263: UPDATE jtf_terr_ALL jt
2264: SET jt.num_qual = (
2265: SELECT COUNT(jtq.qual_usg_id)
2266: FROM jtf_terr_qual_ALL jtq, jtf_qual_usgs_ALL jqu, jtf_qual_type_usgs_ALL jqtu
2267: WHERE jtq.terr_id = jt.terr_id
2262:
2263: UPDATE jtf_terr_ALL jt
2264: SET jt.num_qual = (
2265: SELECT COUNT(jtq.qual_usg_id)
2266: FROM jtf_terr_qual_ALL jtq, jtf_qual_usgs_ALL jqu, jtf_qual_type_usgs_ALL jqtu
2267: WHERE jtq.terr_id = jt.terr_id
2268: AND jtq.qual_usg_id = jqu.qual_usg_id
2269: AND jqu.qual_type_usg_id = jqtu.qual_type_usg_id
2270: AND jqtu.qual_type_id = p_qual_type_id
2349:
2350: -- Debug Message
2351: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2352: THEN
2353: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
2354: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2355: FND_MSG_PUB.Add;
2356: END IF;
2357:
2363:
2364: -- Debug message
2365: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2366: THEN
2367: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
2368: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Qualifier');
2369: FND_MSG_PUB.Add;
2370: END IF;
2371: --
2392: l_terr_qual_id := NULL;
2393: END IF;
2394:
2395: -- Call insert terr_Qual_Type_Usgs table handler
2396: JTF_TERR_QUAL_PKG.Insert_Row(x_Rowid => l_rowid,
2397: x_TERR_QUAL_ID => l_terr_qual_id,
2398: x_LAST_UPDATE_DATE => P_Terr_Qual_Rec.LAST_UPDATE_DATE,
2399: x_LAST_UPDATED_BY => P_Terr_Qual_Rec.LAST_UPDATED_BY,
2400: x_CREATION_DATE => P_Terr_Qual_Rec.CREATION_DATE,
2424:
2425: --
2426: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2427: THEN
2428: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2429: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2430: FND_MSG_PUB.Add;
2431: END IF;
2432:
2558:
2559: -- Debug Message
2560: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2561: THEN
2562: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
2563: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2564: FND_MSG_PUB.Add;
2565: END IF;
2566:
2619: END LOOP;
2620: --
2621: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2622: THEN
2623: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2624: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2625: FND_MSG_PUB.Add;
2626: END IF;
2627:
2711:
2712: -- Debug Message
2713: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2714: THEN
2715: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
2716: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2717: FND_MSG_PUB.Add;
2718: END IF;
2719:
2724: IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
2725: -- Debug message
2726: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2727: THEN
2728: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
2729: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_terr_Value_Rec');
2730: FND_MSG_PUB.Add;
2731: END IF;
2732:
2751: l_terr_value_id := NULL;
2752: END IF;
2753:
2754: --
2755: --dbms_output('Create_Terr_Value PVT: Before Calling JTF_TERR_VALUES_PKG.Insert_Row');
2756: JTF_TERR_VALUES_PKG.Insert_Row(x_Rowid => l_rowid,
2757: x_TERR_VALUE_ID => l_terr_value_id,
2758: x_LAST_UPDATED_BY => P_Terr_Value_Rec.LAST_UPDATED_BY,
2759: x_LAST_UPDATE_DATE => P_Terr_Value_Rec.LAST_UPDATE_DATE,
2752: END IF;
2753:
2754: --
2755: --dbms_output('Create_Terr_Value PVT: Before Calling JTF_TERR_VALUES_PKG.Insert_Row');
2756: JTF_TERR_VALUES_PKG.Insert_Row(x_Rowid => l_rowid,
2757: x_TERR_VALUE_ID => l_terr_value_id,
2758: x_LAST_UPDATED_BY => P_Terr_Value_Rec.LAST_UPDATED_BY,
2759: x_LAST_UPDATE_DATE => P_Terr_Value_Rec.LAST_UPDATE_DATE,
2760: x_CREATED_BY => P_Terr_Value_Rec.CREATED_BY,
2791:
2792: --
2793: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2794: THEN
2795: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2796: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2797: FND_MSG_PUB.Add;
2798: END IF;
2799:
2927:
2928: -- Debug Message
2929: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2930: THEN
2931: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
2932: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2933: FND_MSG_PUB.Add;
2934: END IF;
2935:
2991: --
2992: -- Debug Message
2993: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2994: THEN
2995: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2996: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2997: FND_MSG_PUB.Add;
2998: END IF;
2999:
3096: ATTRIBUTE15,
3097: ORG_ID,
3098: NUM_WINNERS,
3099: NUM_QUAL
3100: From JTF_TERR_ALL
3101: Where TERR_ID = l_terr_id
3102: For Update NOWAIT;
3103:
3104: --Local variable declaration
3112:
3113: -- Debug Message
3114: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3115: THEN
3116: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
3117: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3118: FND_MSG_PUB.Add;
3119: END IF;
3120:
3125: THEN
3126: -- Debug message
3127: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3128: THEN
3129: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
3130: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Territory_Record');
3131: FND_MSG_PUB.Add;
3132: END IF;
3133: --
3198: If ( C_GetTerritory%NOTFOUND) Then
3199: --dbms_output('Update_territory_Record: NO-RCORDS-FOUND');
3200: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3201: THEN
3202: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
3203: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
3204: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(p_terr_all_rec.terr_id));
3205: FND_MSG_PUB.Add;
3206: END IF;
3199: --dbms_output('Update_territory_Record: NO-RCORDS-FOUND');
3200: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3201: THEN
3202: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
3203: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
3204: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(p_terr_all_rec.terr_id));
3205: FND_MSG_PUB.Add;
3206: END IF;
3207: raise FND_API.G_EXC_ERROR;
3207: raise FND_API.G_EXC_ERROR;
3208: End if;
3209: CLOSE C_GetTerritory;
3210:
3211: --dbms_output('Update_territory_Record: Before Calling JTF_TERR_PKG.Update_Row');
3212: JTF_TERR_PKG.Update_Row(x_rowid => l_rowid,
3213: x_terr_id => p_terr_all_rec.terr_id,
3214: x_last_update_date => p_terr_all_rec.LAST_UPDATE_DATE,
3215: x_last_updated_by => p_terr_all_rec.LAST_UPDATED_BY,
3208: End if;
3209: CLOSE C_GetTerritory;
3210:
3211: --dbms_output('Update_territory_Record: Before Calling JTF_TERR_PKG.Update_Row');
3212: JTF_TERR_PKG.Update_Row(x_rowid => l_rowid,
3213: x_terr_id => p_terr_all_rec.terr_id,
3214: x_last_update_date => p_terr_all_rec.LAST_UPDATE_DATE,
3215: x_last_updated_by => p_terr_all_rec.LAST_UPDATED_BY,
3216: x_creation_date => p_terr_all_rec.CREATION_DATE,
3262:
3263: -- Debug Message
3264: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3265: THEN
3266: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
3267: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3268: FND_MSG_PUB.Add;
3269: END IF;
3270:
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';
3349: BEGIN
3350: -- Debug Message
3351: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3352: THEN
3353: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
3354: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3355: FND_MSG_PUB.Add;
3356: END IF;
3357:
3362: THEN
3363: -- Debug message
3364: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3365: THEN
3366: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
3367: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Territory_Usage');
3368: FND_MSG_PUB.Add;
3369: END IF;
3370: --
3400: If ( C_GetTerritoryUsage%NOTFOUND) Then
3401: --dbms_output('Update_Territory_Usages PVT: NO-RCORDS-FOUND');
3402: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3403: THEN
3404: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
3405: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
3406: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Usgs_Rec.terr_usg_id));
3407: FND_MSG_PUB.Add;
3408: END IF;
3401: --dbms_output('Update_Territory_Usages PVT: NO-RCORDS-FOUND');
3402: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3403: THEN
3404: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
3405: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
3406: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Usgs_Rec.terr_usg_id));
3407: FND_MSG_PUB.Add;
3408: END IF;
3409: raise FND_API.G_EXC_ERROR;
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,
3428:
3429: -- Debug Message
3430: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3431: THEN
3432: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
3433: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3434: FND_MSG_PUB.Add;
3435: END IF;
3436:
3506:
3507: -- Debug Message
3508: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3509: THEN
3510: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
3511: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3512: FND_MSG_PUB.Add;
3513: END IF;
3514:
3566:
3567: -- Debug Message
3568: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3569: THEN
3570: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
3571: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3572: FND_MSG_PUB.Add;
3573: END IF;
3574: --dbms_output('Update_Territory_Usages TBL: Exiting API');
3618: CREATION_DATE,
3619: LAST_UPDATE_LOGIN,
3620: TERR_ID,
3621: QUAL_TYPE_USG_ID
3622: From JTF_TERR_QTYPE_USGS_ALL
3623: Where terr_qtype_usg_id = l_terr_qual_type_usg_id
3624: FOR Update NOWAIT;
3625: --Local variable declaration
3626: l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_QualType_Usage';
3633:
3634: -- Debug Message
3635: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3636: THEN
3637: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
3638: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3639: FND_MSG_PUB.Add;
3640: END IF;
3641:
3646: THEN
3647: -- Debug message
3648: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3649: THEN
3650: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
3651: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Qtype_Usage');
3652: FND_MSG_PUB.Add;
3653: END IF;
3654: -- Invoke validation procedures
3679: If (C_GetTerrQualTypeUsgs%NOTFOUND) Then
3680: --dbms_output('Update_Terr_QualType_Usage REC: NO-RCORDS-FOUND');
3681: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3682: THEN
3683: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
3684: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
3685: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_QualTypeUsgs_Rec.TERR_QUAL_TYPE_USG_ID));
3686: FND_MSG_PUB.Add;
3687: END IF;
3680: --dbms_output('Update_Terr_QualType_Usage REC: NO-RCORDS-FOUND');
3681: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3682: THEN
3683: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
3684: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
3685: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_QualTypeUsgs_Rec.TERR_QUAL_TYPE_USG_ID));
3686: FND_MSG_PUB.Add;
3687: END IF;
3688: raise FND_API.G_EXC_ERROR;
3688: raise FND_API.G_EXC_ERROR;
3689: End if;
3690: CLOSE C_GetTerrQualTypeUsgs;
3691:
3692: --dbms_output('Update_Terr_QualType_Usage REC: Before Calling JTF_TERR_QTYPE_USGS_PKG.Update_Row');
3693: JTF_TERR_QTYPE_USGS_PKG.Update_Row(x_Rowid => l_rowid,
3694: x_TERR_QTYPE_USG_ID => P_Terr_QualTypeUsgs_Rec.TERR_QUAL_TYPE_USG_ID,
3695: x_LAST_UPDATED_BY => P_Terr_QualTypeUsgs_Rec.LAST_UPDATED_BY,
3696: x_LAST_UPDATE_DATE => P_Terr_QualTypeUsgs_Rec.LAST_UPDATE_DATE,
3689: End if;
3690: CLOSE C_GetTerrQualTypeUsgs;
3691:
3692: --dbms_output('Update_Terr_QualType_Usage REC: Before Calling JTF_TERR_QTYPE_USGS_PKG.Update_Row');
3693: JTF_TERR_QTYPE_USGS_PKG.Update_Row(x_Rowid => l_rowid,
3694: x_TERR_QTYPE_USG_ID => P_Terr_QualTypeUsgs_Rec.TERR_QUAL_TYPE_USG_ID,
3695: x_LAST_UPDATED_BY => P_Terr_QualTypeUsgs_Rec.LAST_UPDATED_BY,
3696: x_LAST_UPDATE_DATE => P_Terr_QualTypeUsgs_Rec.LAST_UPDATE_DATE,
3697: x_CREATED_BY => P_Terr_QualTypeUsgs_Rec.CREATED_BY,
3705: X_Terr_QualTypeUsgs_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
3706:
3707: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3708: THEN
3709: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
3710: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3711: FND_MSG_PUB.Add;
3712: END IF;
3713:
3782:
3783: -- Debug Message
3784: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3785: THEN
3786: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
3787: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3788: FND_MSG_PUB.Add;
3789: END IF;
3790:
3840:
3841: -- Debug Message
3842: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3843: THEN
3844: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
3845: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3846: FND_MSG_PUB.Add;
3847: END IF;
3848:
3895: QUAL_USG_ID,
3896: USE_TO_NAME_FLAG,
3897: GENERATE_FLAG,
3898: OVERLAP_ALLOWED_FLAG
3899: From JTF_TERR_QUAL_ALL
3900: Where terr_qual_id = l_terr_qual_id
3901: FOR Update NOWAIT;
3902: --Local variable declaration
3903: l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Qualifier';
3910:
3911: -- Debug Message
3912: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3913: THEN
3914: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
3915: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3916: FND_MSG_PUB.Add;
3917: END IF;
3918:
3923: THEN
3924: -- Debug message
3925: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3926: THEN
3927: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
3928: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Qualifier');
3929: FND_MSG_PUB.Add;
3930: END IF;
3931: --
3962: If ( C_GetTerrQualifier%NOTFOUND) Then
3963: --dbms_output('Update_Terr_Qualifier REC: NO-RCORDS-FOUND');
3964: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3965: THEN
3966: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
3967: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QUAL');
3968: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Qual_Rec.terr_qual_id));
3969: FND_MSG_PUB.Add;
3970: END IF;
3963: --dbms_output('Update_Terr_Qualifier REC: NO-RCORDS-FOUND');
3964: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3965: THEN
3966: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
3967: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QUAL');
3968: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Qual_Rec.terr_qual_id));
3969: FND_MSG_PUB.Add;
3970: END IF;
3971: raise FND_API.G_EXC_ERROR;
3971: raise FND_API.G_EXC_ERROR;
3972: End if;
3973: CLOSE C_GetTerrQualifier;
3974:
3975: --dbms_output('Update_Terr_Qualifier REC: Before Calling JTF_TERR_QUAL_PKG.Update_Row');
3976: JTF_TERR_QUAL_PKG.Update_Row(x_Rowid => l_rowid,
3977: x_TERR_QUAL_ID => P_Terr_Qual_Rec.terr_qual_id,
3978: x_LAST_UPDATE_DATE => P_Terr_Qual_Rec.LAST_UPDATE_DATE,
3979: x_LAST_UPDATED_BY => P_Terr_Qual_Rec.LAST_UPDATED_BY,
3972: End if;
3973: CLOSE C_GetTerrQualifier;
3974:
3975: --dbms_output('Update_Terr_Qualifier REC: Before Calling JTF_TERR_QUAL_PKG.Update_Row');
3976: JTF_TERR_QUAL_PKG.Update_Row(x_Rowid => l_rowid,
3977: x_TERR_QUAL_ID => P_Terr_Qual_Rec.terr_qual_id,
3978: x_LAST_UPDATE_DATE => P_Terr_Qual_Rec.LAST_UPDATE_DATE,
3979: x_LAST_UPDATED_BY => P_Terr_Qual_Rec.LAST_UPDATED_BY,
3980: x_CREATION_DATE => P_Terr_Qual_Rec.creation_date,
3994:
3995: -- Debug Message
3996: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3997: THEN
3998: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
3999: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4000: FND_MSG_PUB.Add;
4001: END IF;
4002:
4068:
4069: -- Debug Message
4070: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4071: THEN
4072: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4073: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4074: FND_MSG_PUB.Add;
4075: END IF;
4076:
4123:
4124: -- Debug Message
4125: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4126: THEN
4127: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
4128: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4129: FND_MSG_PUB.Add;
4130: END IF;
4131: --dbms_output('Update_Terr_Qualifier TBL: Exiting API');
4189: ID_USED_FLAG,
4190: LOW_VALUE_CHAR_ID,
4191: ORG_ID,
4192: CNR_GROUP_ID
4193: From JTF_TERR_VALUES_ALL
4194: Where TERR_VALUE_ID = l_TERR_VALUE_ID
4195: For Update NOWAIT;
4196:
4197: --Local variable declaration
4205:
4206: -- Debug Message
4207: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4208: THEN
4209: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4210: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4211: FND_MSG_PUB.Add;
4212: END IF;
4213:
4219: THEN
4220: -- Debug message
4221: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4222: THEN
4223: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
4224: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_terr_Value_Rec');
4225: FND_MSG_PUB.Add;
4226: END IF;
4227:
4270: --
4271: --dbms_output('Update_Terr_Value REC: NO-RCORDS-FOUND');
4272: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4273: THEN
4274: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
4275: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_VALUES');
4276: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Value_Rec.TERR_VALUE_ID));
4277: FND_MSG_PUB.Add;
4278: END IF;
4271: --dbms_output('Update_Terr_Value REC: NO-RCORDS-FOUND');
4272: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4273: THEN
4274: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
4275: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_VALUES');
4276: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Value_Rec.TERR_VALUE_ID));
4277: FND_MSG_PUB.Add;
4278: END IF;
4279: raise FND_API.G_EXC_ERROR;
4282: CLOSE C_GetTerritoryValue;
4283: --
4284: -- Add check for concurrency
4285: --
4286: --dbms_output('Update_Terr_Value REC: Before Calling JTF_TERR_VALUES_PKG.Update_Row');
4287: --call the table handler
4288: JTF_TERR_VALUES_PKG.Update_Row(x_Rowid => l_rowid,
4289: x_TERR_VALUE_ID => P_Terr_Value_Rec.TERR_VALUE_ID,
4290: x_LAST_UPDATED_BY => P_Terr_Value_Rec.LAST_UPDATED_BY,
4284: -- Add check for concurrency
4285: --
4286: --dbms_output('Update_Terr_Value REC: Before Calling JTF_TERR_VALUES_PKG.Update_Row');
4287: --call the table handler
4288: JTF_TERR_VALUES_PKG.Update_Row(x_Rowid => l_rowid,
4289: x_TERR_VALUE_ID => P_Terr_Value_Rec.TERR_VALUE_ID,
4290: x_LAST_UPDATED_BY => P_Terr_Value_Rec.LAST_UPDATED_BY,
4291: x_LAST_UPDATE_DATE => P_Terr_Value_Rec.LAST_UPDATE_DATE,
4292: x_CREATED_BY => P_Terr_Value_Rec.created_by,
4318:
4319: -- Debug Message
4320: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4321: THEN
4322: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
4323: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4324: FND_MSG_PUB.Add;
4325: END IF;
4326:
4397:
4398: -- Debug Message
4399: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4400: THEN
4401: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4402: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4403: FND_MSG_PUB.Add;
4404: END IF;
4405:
4454:
4455: -- Debug Message
4456: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4457: THEN
4458: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
4459: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4460: FND_MSG_PUB.Add;
4461: END IF;
4462:
4524:
4525: -- Debug Message
4526: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4527: THEN
4528: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4529: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4530: FND_MSG_PUB.Add;
4531: END IF;
4532:
4534: x_return_status := FND_API.G_RET_STS_SUCCESS;
4535: --
4536: --dbms_output('Delete_territory_Record: Before Calling Create_TerrType_Qualifier TBL');
4537: --
4538: JTF_TERR_PKG.Delete_Row(x_terr_Id => P_Terr_Id);
4539: --
4540: --Prepare message name
4541: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
4542:
4537: --
4538: JTF_TERR_PKG.Delete_Row(x_terr_Id => P_Terr_Id);
4539: --
4540: --Prepare message name
4541: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
4542:
4543: IF SQL%FOUND THEN
4544: --dbms_output('Delete_territory_Record: # Records deleted -' || to_char(SQL%ROWCOUNT));
4545: x_return_status := FND_API.G_RET_STS_SUCCESS;
4546: l_row_count := SQL%ROWCOUNT;
4547: END IF;
4548: --prepare the message
4549: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
4550: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR');
4551: FND_MSG_PUB.ADD;
4552:
4553: -- Debug Message
4554: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4552:
4553: -- Debug Message
4554: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4555: THEN
4556: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
4557: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4558: FND_MSG_PUB.Add;
4559: END IF;
4560:
4576: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4577: l_row_count := 0;
4578: --Prepare message token
4579: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
4580: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR');
4581: FND_MSG_PUB.ADD;
4582: --
4583: FND_MSG_PUB.Count_And_Get
4584: ( p_count => x_msg_count,
4660:
4661: -- Debug Message
4662: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4663: THEN
4664: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4665: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4666: FND_MSG_PUB.Add;
4667: END IF;
4668:
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
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
4679: --dbms_output('Delete_Territory_Usages: # Records deleted -' || to_char(SQL%ROWCOUNT));
4680: x_return_status := FND_API.G_RET_STS_SUCCESS;
4681: l_row_count := SQL%ROWCOUNT;
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)
4687:
4688: -- Debug Message
4689: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4690: THEN
4691: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
4692: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4693: FND_MSG_PUB.Add;
4694: END IF;
4695:
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,
4795:
4796: -- Debug Message
4797: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4798: THEN
4799: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4800: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4801: FND_MSG_PUB.Add;
4802: END IF;
4803:
4803:
4804: --Initialize the return status to success
4805: x_return_status := FND_API.G_RET_STS_SUCCESS;
4806:
4807: --dbms_output('Delete_Terr_QualType_Usage: Before Calling JTF_TERR_QTYPE_USGS_PKG.Delete_Row');
4808: --
4809: JTF_TERR_QTYPE_USGS_PKG.Delete_Row(x_terr_qtype_usg_id => P_Terr_Qual_Type_Usg_Id);
4810: --
4811: --
4805: x_return_status := FND_API.G_RET_STS_SUCCESS;
4806:
4807: --dbms_output('Delete_Terr_QualType_Usage: Before Calling JTF_TERR_QTYPE_USGS_PKG.Delete_Row');
4808: --
4809: JTF_TERR_QTYPE_USGS_PKG.Delete_Row(x_terr_qtype_usg_id => P_Terr_Qual_Type_Usg_Id);
4810: --
4811: --
4812: --Prepare message name
4813: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
4809: JTF_TERR_QTYPE_USGS_PKG.Delete_Row(x_terr_qtype_usg_id => P_Terr_Qual_Type_Usg_Id);
4810: --
4811: --
4812: --Prepare message name
4813: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
4814:
4815: IF SQL%FOUND THEN
4816: --dbms_output('Delete_Terr_QualType_Usage: # Records deleted -' || to_char(SQL%ROWCOUNT));
4817: x_return_status := FND_API.G_RET_STS_SUCCESS;
4819: END IF;
4820:
4821: --Prepare message token
4822: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
4823: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
4824: FND_MSG_PUB.ADD;
4825:
4826: -- Debug Message
4827: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4825:
4826: -- Debug Message
4827: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4828: THEN
4829: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
4830: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4831: FND_MSG_PUB.Add;
4832: END IF;
4833:
4851: l_row_count := 0;
4852:
4853: --Prepare message token
4854: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
4855: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
4856: FND_MSG_PUB.ADD;
4857: --
4858: FND_MSG_PUB.Count_And_Get
4859: ( p_count => x_msg_count,
4943:
4944: -- Debug Message
4945: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4946: THEN
4947: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4948: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4949: FND_MSG_PUB.Add;
4950: END IF;
4951:
4958: /* anonymous block to get territory id */
4959: BEGIN
4960: SELECT terr_id
4961: INTO lp_terr_id
4962: FROM jtf_terr_qual_ALL jtq
4963: WHERE jtq.terr_qual_id = p_terr_qual_id;
4964: EXCEPTION
4965: WHEN NO_DATA_FOUND THEN
4966: NULL;
4968: NULL;
4969: END;
4970:
4971: /* Do a bulk delete of Territory Qualifier VALUES */
4972: DELETE jtf_terr_values_ALL
4973: WHERE terr_qual_id = p_terr_qual_id
4974: RETURNING terr_value_id
4975: BULK COLLECT INTO deletedIds;
4976:
4973: WHERE terr_qual_id = p_terr_qual_id
4974: RETURNING terr_value_id
4975: BULK COLLECT INTO deletedIds;
4976:
4977: JTF_TERR_QUAL_PKG.Delete_Row(x_terr_qual_id => P_Terr_Qual_Id );
4978: --
4979: --Prepare message name
4980: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
4981: IF SQL%FOUND THEN
4976:
4977: JTF_TERR_QUAL_PKG.Delete_Row(x_terr_qual_id => P_Terr_Qual_Id );
4978: --
4979: --Prepare message name
4980: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
4981: IF SQL%FOUND THEN
4982: --dbms_output('Delete_Terr_Qualifier: # Records deleted -' || to_char(SQL%ROWCOUNT));
4983: x_return_status := FND_API.G_RET_STS_SUCCESS;
4984: l_row_count := SQL%ROWCOUNT;
4989: update_terr_num_qual(lp_terr_id, -1002);
4990:
4991: --Prepare message token
4992: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
4993: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_QUALIFIERS');
4994: FND_MSG_PUB.ADD();
4995:
4996: -- Debug Message
4997: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4995:
4996: -- Debug Message
4997: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4998: THEN
4999: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
5000: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
5001: FND_MSG_PUB.Add;
5002: END IF;
5003:
5020: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5021: l_row_count := 0;
5022: --Prepare message token
5023: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5024: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_QUALIFIERS');
5025: --Add message to API message list
5026: FND_MSG_PUB.ADD();
5027: --
5028: FND_MSG_PUB.Count_And_Get
5104:
5105: -- Debug Message
5106: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5107: THEN
5108: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
5109: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
5110: FND_MSG_PUB.Add;
5111: END IF;
5112:
5112:
5113: --Initialize the return status to success
5114: x_return_status := FND_API.G_RET_STS_SUCCESS;
5115: --
5116: --dbms_output('Delete_Terr_Value: Before Calling JTF_TERR_VALUES_PKG.Delete_Row');
5117:
5118: JTF_TERR_VALUES_PKG.Delete_Row(X_TERR_VALUE_ID => P_Terr_Value_Id);
5119: --
5120: --Prepare message name
5114: x_return_status := FND_API.G_RET_STS_SUCCESS;
5115: --
5116: --dbms_output('Delete_Terr_Value: Before Calling JTF_TERR_VALUES_PKG.Delete_Row');
5117:
5118: JTF_TERR_VALUES_PKG.Delete_Row(X_TERR_VALUE_ID => P_Terr_Value_Id);
5119: --
5120: --Prepare message name
5121: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
5122: IF SQL%FOUND THEN
5117:
5118: JTF_TERR_VALUES_PKG.Delete_Row(X_TERR_VALUE_ID => P_Terr_Value_Id);
5119: --
5120: --Prepare message name
5121: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
5122: IF SQL%FOUND THEN
5123: --dbms_output('Delete_Terr_Value: # Records deleted -' || to_char(SQL%ROWCOUNT));
5124: x_return_status := FND_API.G_RET_STS_SUCCESS;
5125: l_row_count := SQL%ROWCOUNT;
5125: l_row_count := SQL%ROWCOUNT;
5126: END IF;
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_VALUES');
5130: FND_MSG_PUB.ADD();
5131:
5132: -- Debug Message
5133: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5131:
5132: -- Debug Message
5133: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5134: THEN
5135: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
5136: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name );
5137: FND_MSG_PUB.Add;
5138: END IF;
5139:
5157: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5158: l_row_count := 0;
5159: --Prepare message token
5160: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5161: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_VALUES');
5162: --Add message to API message list
5163: FND_MSG_PUB.ADD();
5164: --
5165: FND_MSG_PUB.Count_And_Get
5211: SELECT 'x'
5212: into l_Temp
5213: from jtf_qual_usgs_ALL jqu,
5214: jtf_qual_type_usgs_ALL jqtu,
5215: jtf_terr_qtype_usgs_ALL jtqu
5216: where jtqu.terr_id = p_Terr_Id and
5217: jqtu.qual_type_usg_id = jtqu.qual_type_usg_id and
5218: jqu.qual_usg_id = P_Terr_Qual_Rec.Qual_Usg_Id and
5219: jqu.enabled_flag = 'Y' and
5225: EXCEPTION
5226: WHEN NO_DATA_FOUND THEN
5227: --dbms_output('Validate_Qualifier: NO_DATA_FOUND Exception');
5228: x_return_status := FND_API.G_RET_STS_ERROR ;
5229: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_DISABLED_TERR_QUAL');
5230: FND_MSG_PUB.ADD;
5231: FND_MSG_PUB.Count_And_Get
5232: ( P_count => x_msg_count,
5233: P_data => x_msg_data
5238: -- Check for ORG_ID - obsolete: org_id is optional
5239: IF (P_Terr_Qual_Rec.ORG_ID is NULL OR
5240: P_Terr_Qual_Rec.ORG_ID = FND_API.G_MISS_NUM ) THEN
5241: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5242: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5243: FND_MESSAGE.Set_Token('COL_NAME', 'ORG_ID' );
5244: FND_MSG_PUB.ADD;
5245: END IF;
5246: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5250: --Check created by
5251: IF ( P_Terr_Qual_Rec.CREATED_BY is NULL OR
5252: P_Terr_Qual_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
5253: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5254: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5255: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
5256: FND_MSG_PUB.ADD;
5257: END IF;
5258: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5261: --Check creation date
5262: If ( P_Terr_Qual_Rec.CREATION_DATE is NULL OR
5263: P_Terr_Qual_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
5264: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5265: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5266: FND_MESSAGE.Set_Token('COL_NAME', 'ORG_ID' );
5267: FND_MSG_PUB.ADD;
5268: END IF;
5269: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5272: -- Validate last updated by
5273: IF ( P_Terr_Qual_Rec.LAST_UPDATED_BY is NULL OR
5274: P_Terr_Qual_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
5275: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5276: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5277: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
5278: FND_MSG_PUB.ADD;
5279: END IF;
5280: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5283: -- Check last update date
5284: If ( P_Terr_Qual_Rec.LAST_UPDATE_DATE IS NULL OR
5285: P_Terr_Qual_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
5286: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5287: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5288: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
5289: FND_MSG_PUB.ADD;
5290: END IF;
5291: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5295: --Check last update login
5296: --If ( P_Terr_Qual_Rec.LAST_UPDATE_LOGIN is NULL OR
5297: -- P_Terr_Qual_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
5298: -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5299: -- FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5300: -- FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
5301: -- FND_MSG_PUB.ADD;
5302: -- END IF;
5303: -- x_Return_Status := FND_API.G_RET_STS_ERROR ;
5313: --
5314: WHEN NO_DATA_FOUND THEN
5315: --dbms_output('Validate_Qualifier: NO_DATA_FOUND Exception');
5316: x_return_status := FND_API.G_RET_STS_ERROR ;
5317: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_DISABLED_TERR_QUAL');
5318: FND_MSG_PUB.ADD;
5319: FND_MSG_PUB.Count_And_Get
5320: ( P_count => x_msg_count,
5321: P_data => x_msg_data
5323:
5324: WHEN OTHERS THEN
5325: --dbms_output('Validate_Qualifier: Others Exception');
5326: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5327: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
5328: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Qualifer' );
5329: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
5330: FND_MSG_PUB.ADD;
5331: FND_MSG_PUB.Count_And_Get
5358: -- Check whether the territory Name is specified
5359: --
5360: IF (p_Terr_All_Rec.NAME is NULL) OR (p_Terr_All_Rec.NAME = FND_API.G_MISS_CHAR) THEN
5361: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5362: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5363: FND_MESSAGE.Set_Token('COL_NAME', 'NAME' );
5364: FND_MSG_PUB.ADD;
5365: END IF;
5366: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5370: --
5371: IF (p_Terr_All_Rec.APPLICATION_SHORT_NAME is NULL) OR
5372: (p_Terr_All_Rec.APPLICATION_SHORT_NAME = FND_API.G_MISS_CHAR) THEN
5373: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5374: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5375: FND_MESSAGE.Set_Token('COL_NAME', 'APPLICATION_SHORT_NAME' );
5376: FND_MSG_PUB.ADD;
5377: END IF;
5378: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5383: -- whether territory is enabled
5384: IF (p_Terr_All_Rec.ENABLED_FLAG is NULL) OR
5385: (p_Terr_All_Rec.ENABLED_FLAG = FND_API.G_MISS_CHAR) THEN
5386: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5387: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5388: FND_MESSAGE.Set_Token('COL_NAME', 'ENABLED_FLAG' );
5389: FND_MSG_PUB.ADD;
5390: END IF;
5391: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5395: /*-- Check for ORG_ID - obsolete org_id is optional
5396: IF (p_Terr_All_Rec.ORG_ID is NULL) OR
5397: (p_Terr_All_Rec.ORG_ID = FND_API.G_MISS_NUM) THEN
5398: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5399: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5400: FND_MESSAGE.Set_Token('COL_NAME', 'ORG_ID' );
5401: FND_MSG_PUB.ADD;
5402: END IF;
5403: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5406:
5407: If (p_Terr_All_Rec.ESCALATION_TERRITORY_FLAG = 'Y' and
5408: p_Terr_All_Rec.TEMPLATE_FLAG = 'Y' ) Then
5409: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5410: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_OVERLAPPING_FLAG');
5411: FND_MSG_PUB.ADD;
5412: END IF;
5413: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5414: End If;
5416: --Check created by
5417: IF ( p_Terr_All_Rec.CREATED_BY is NULL OR
5418: p_Terr_All_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
5419: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5420: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5421: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
5422: FND_MSG_PUB.ADD;
5423: END IF;
5424: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5427: --Check creation date
5428: If ( p_Terr_All_Rec.CREATION_DATE is NULL OR
5429: p_Terr_All_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
5430: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5431: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5432: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
5433: FND_MSG_PUB.ADD;
5434: END IF;
5435: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5438: -- Validate last updated by
5439: IF ( p_Terr_All_Rec.LAST_UPDATED_BY is NULL OR
5440: p_Terr_All_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
5441: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5442: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5443: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
5444: FND_MSG_PUB.ADD;
5445: END IF;
5446: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5449: -- Check last update date
5450: If ( p_Terr_All_Rec.LAST_UPDATE_DATE IS NULL OR
5451: p_Terr_All_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
5452: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5453: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5454: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
5455: FND_MSG_PUB.ADD;
5456: END IF;
5457: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5460: --Check last update login
5461: --If ( p_Terr_All_Rec.LAST_UPDATE_LOGIN is NULL OR
5462: -- p_Terr_All_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
5463: -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5464: -- FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5465: -- FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
5466: -- FND_MSG_PUB.ADD;
5467: -- END IF;
5468: -- x_Return_Status := FND_API.G_RET_STS_ERROR ;
5471: --Check start date active
5472: If ( p_Terr_All_Rec.START_DATE_ACTIVE IS NULL OR
5473: p_Terr_All_Rec.START_DATE_ACTIVE = FND_API.G_MISS_DATE ) THEN
5474: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5475: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5476: FND_MESSAGE.Set_Token('COL_NAME', 'START_DATE_ACTIVE' );
5477: FND_MSG_PUB.ADD;
5478: END IF;
5479: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5542:
5543: WHEN OTHERS THEN
5544: --dbms_output('Validate_Territory_Record: OTHERS - ' || SQLERRM);
5545: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5546: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
5547: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Territory_Record' );
5548: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
5549: FND_MSG_PUB.ADD;
5550: FND_MSG_PUB.Count_And_Get
5577: Then
5578: --
5579: l_parent_terr_id := p_Terr_All_Rec.parent_territory_Id;
5580: --dbms_output('Validate_Foreign_Key: Returned from JTF_CTM_UTILITY_PVT.fk_id_is_valid');
5581: IF JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_parent_terr_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
5582: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5583: --dbms_output('PARENT Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
5584: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_PARENT_TERR');
5585: FND_MESSAGE.Set_Token('TERR_ID', to_char(l_parent_terr_id));
5580: --dbms_output('Validate_Foreign_Key: Returned from JTF_CTM_UTILITY_PVT.fk_id_is_valid');
5581: IF JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_parent_terr_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
5582: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5583: --dbms_output('PARENT Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
5584: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_PARENT_TERR');
5585: FND_MESSAGE.Set_Token('TERR_ID', to_char(l_parent_terr_id));
5586: FND_MSG_PUB.ADD;
5587: End If;
5588: x_return_status := FND_API.G_RET_STS_ERROR;
5594: --
5595: If (p_Terr_All_Rec.TERRITORY_TYPE_ID is not null) AND
5596: (p_Terr_All_Rec.TERRITORY_TYPE_ID <> FND_API.G_MISS_NUM)
5597: then
5598: l_status := JTF_CTM_UTILITY_PVT.fk_id_is_valid(p_Terr_All_Rec.TERRITORY_TYPE_ID, 'TERR_TYPE_ID', 'JTF_TERR_TYPES_ALL');
5599: If l_status <> FND_API.G_TRUE Then
5600: x_return_status := FND_API.G_RET_STS_ERROR;
5601: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5602: --dbms_output('TERR TYPE: Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
5599: If l_status <> FND_API.G_TRUE Then
5600: x_return_status := FND_API.G_RET_STS_ERROR;
5601: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5602: --dbms_output('TERR TYPE: Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
5603: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERR_TYPE');
5604: FND_MESSAGE.Set_Token('TERR_TYPE_ID', to_char(p_Terr_All_Rec.TERRITORY_TYPE_ID));
5605: FND_MSG_PUB.ADD;
5606: End If;
5607: End If;
5610: --dbms_output('Validate_Foreign_Key: Checking for TEMPLATE_TERRITORY_ID');
5611: --
5612: iF (p_Terr_All_Rec.TEMPLATE_TERRITORY_ID is not null) AND
5613: (p_Terr_All_Rec.TEMPLATE_TERRITORY_ID <> FND_API.G_MISS_NUM) Then
5614: l_status := JTF_CTM_UTILITY_PVT.fk_id_is_valid(p_Terr_All_Rec.TEMPLATE_TERRITORY_ID, 'TERR_ID', 'JTF_TERR_ALL');
5615: if l_status <> FND_API.G_TRUE Then
5616: x_return_status := FND_API.G_RET_STS_ERROR;
5617: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5618: --dbms_output('TEMP: Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
5615: if l_status <> FND_API.G_TRUE Then
5616: x_return_status := FND_API.G_RET_STS_ERROR;
5617: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5618: --dbms_output('TEMP: Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
5619: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TEMP_TERR');
5620: FND_MESSAGE.Set_Token('TERR_ID', to_char(p_Terr_All_Rec.TEMPLATE_TERRITORY_ID));
5621: FND_MSG_PUB.ADD;
5622: End If;
5623: End If;
5626: --dbms_output('Validate_Foreign_Key: Checking for ESCALATION_TERRITORY_ID');
5627: --
5628: If (p_Terr_All_Rec.ESCALATION_TERRITORY_ID is not null) AND
5629: (p_Terr_All_Rec.ESCALATION_TERRITORY_ID <> FND_API.G_MISS_NUM) Then
5630: l_status := JTF_CTM_UTILITY_PVT.fk_id_is_valid(p_Terr_All_Rec.escalation_territory_id, 'TERR_ID', 'JTF_TERR_ALL');
5631: if l_status <> FND_API.G_TRUE Then
5632: x_return_status := FND_API.G_RET_STS_ERROR;
5633: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5634: --dbms_output('ESC TERR: Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
5631: if l_status <> FND_API.G_TRUE Then
5632: x_return_status := FND_API.G_RET_STS_ERROR;
5633: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5634: --dbms_output('ESC TERR: Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
5635: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_ESC_TERR');
5636: FND_MESSAGE.Set_Token('TERR_ID', to_char(p_Terr_All_Rec.terr_Id));
5637: FND_MSG_PUB.ADD;
5638: End If;
5639: End If;
5646: --
5647: WHEN OTHERS THEN
5648: --dbms_output('Validate_Foreign_Key: Others exception' || SQLERRM);
5649: X_return_status := FND_API.G_RET_STS_ERROR;
5650: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
5651: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Foreign_Key' );
5652: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
5653: FND_MSG_PUB.ADD;
5654: FND_MSG_PUB.Count_And_Get
5685:
5686: -- Validate the territory Id
5687: If p_Terr_Id IS NOT NULL THEN
5688: l_Validate_id := p_Terr_Id;
5689: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
5690: --dbms_output('Validate_Territory_Usage: l_status <> FND_API.G_TRUE');
5691: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
5692: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
5693: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
5687: If p_Terr_Id IS NOT NULL THEN
5688: l_Validate_id := p_Terr_Id;
5689: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
5690: --dbms_output('Validate_Territory_Usage: l_status <> FND_API.G_TRUE');
5691: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
5692: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
5693: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
5694: FND_MSG_PUB.ADD;
5695: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5688: l_Validate_id := p_Terr_Id;
5689: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
5690: --dbms_output('Validate_Territory_Usage: l_status <> FND_API.G_TRUE');
5691: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
5692: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
5693: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
5694: FND_MSG_PUB.ADD;
5695: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5696: End If;
5703: -- Make sure the foreign key source_id is valid
5704: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'SOURCE_ID', 'JTF_SOURCES_ALL') <> FND_API.G_TRUE Then
5705: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5706: --dbms_output('Validate_Territory_Usage: FND_MSG_PUB.ADD');
5707: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
5708: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_SOURCES');
5709: FND_MESSAGE.Set_Token('COLUMN_NAME', 'SOURCE_ID');
5710: FND_MSG_PUB.ADD;
5711: End If;
5716: /*-- Check for ORG_ID - obsolete: org_id is optional
5717: IF (p_Terr_Usgs_rec.ORG_ID is NULL) OR
5718: (p_Terr_Usgs_rec.ORG_ID = FND_API.G_MISS_NUM) THEN
5719: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5720: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5721: FND_MESSAGE.Set_Token('COL_NAME', 'ORG_ID' );
5722: FND_MSG_PUB.ADD;
5723: END IF;
5724: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5728: --Check created by
5729: IF ( p_Terr_Usgs_rec.CREATED_BY is NULL OR
5730: p_Terr_Usgs_rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
5731: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5732: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5733: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
5734: FND_MSG_PUB.ADD;
5735: END IF;
5736: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5739: --Check creation date
5740: If ( p_Terr_Usgs_rec.CREATION_DATE is NULL OR
5741: p_Terr_Usgs_rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
5742: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5743: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5744: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
5745: FND_MSG_PUB.ADD;
5746: END IF;
5747: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5750: -- Validate last updated by
5751: IF ( p_Terr_Usgs_rec.LAST_UPDATED_BY is NULL OR
5752: p_Terr_Usgs_rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
5753: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5754: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5755: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
5756: FND_MSG_PUB.ADD;
5757: END IF;
5758: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5761: -- Check last update date
5762: If ( p_Terr_Usgs_rec.LAST_UPDATE_DATE IS NULL OR
5763: p_Terr_Usgs_rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
5764: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5765: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5766: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
5767: FND_MSG_PUB.ADD;
5768: END IF;
5769: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5772: --Check last update login
5773: --If ( p_Terr_Usgs_rec.LAST_UPDATE_LOGIN is NULL OR
5774: -- p_Terr_Usgs_rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
5775: -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5776: -- FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5777: -- FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
5778: -- FND_MSG_PUB.ADD;
5779: -- END IF;
5780: -- x_Return_Status := FND_API.G_RET_STS_ERROR ;
5804:
5805: WHEN OTHERS THEN
5806: --dbms_output('Validate_Territory_Usage: OTHERS - ' || SQLERRM);
5807: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5808: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
5809: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Territory_Usage' );
5810: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
5811: FND_MSG_PUB.ADD;
5812: FND_MSG_PUB.Count_And_Get
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:
5856: jqtu.qual_type_usg_id = l_qual_Type_Usg_id;
5857:
5858: EXCEPTION
5859: WHEN NO_DATA_FOUND THEN
5860: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_QTYPE');
5861: FND_MSG_PUB.ADD;
5862: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5863: END;
5864:
5865: -- Validate the territory Id
5866: l_Validate_id := p_Terr_Id;
5867: If p_Terr_Id IS NOT NULL Then
5868: --dbms_output('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
5869: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
5870: --dbms_output('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
5871: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5872: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
5873: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
5868: --dbms_output('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
5869: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
5870: --dbms_output('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
5871: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5872: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
5873: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
5874: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
5875: FND_MSG_PUB.ADD;
5876: END IF;
5869: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
5870: --dbms_output('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
5871: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5872: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
5873: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
5874: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
5875: FND_MSG_PUB.ADD;
5876: END IF;
5877: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5883: /*-- Check for ORG_ID - obsolete: org_id is optional
5884: IF (p_Terr_QualTypeUsgs_Rec.ORG_ID is NULL) OR
5885: (p_Terr_QualTypeUsgs_Rec.ORG_ID = FND_API.G_MISS_NUM) THEN
5886: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5887: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5888: FND_MESSAGE.Set_Token('COL_NAME', 'ORG_ID' );
5889: FND_MSG_PUB.ADD;
5890: END IF;
5891: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5895: --Check created by
5896: IF ( p_Terr_QualTypeUsgs_Rec.CREATED_BY is NULL OR
5897: p_Terr_QualTypeUsgs_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
5898: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5899: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5900: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
5901: FND_MSG_PUB.ADD;
5902: END IF;
5903: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5906: --Check creation date
5907: If ( p_Terr_QualTypeUsgs_Rec.CREATION_DATE is NULL OR
5908: p_Terr_QualTypeUsgs_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
5909: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5910: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5911: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
5912: FND_MSG_PUB.ADD;
5913: END IF;
5914: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5917: -- Validate last updated by
5918: IF ( p_Terr_QualTypeUsgs_Rec.LAST_UPDATED_BY is NULL OR
5919: p_Terr_QualTypeUsgs_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
5920: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5921: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5922: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
5923: FND_MSG_PUB.ADD;
5924: END IF;
5925: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5928: -- Check last update date
5929: If ( p_Terr_QualTypeUsgs_Rec.LAST_UPDATE_DATE IS NULL OR
5930: p_Terr_QualTypeUsgs_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
5931: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5932: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5933: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
5934: FND_MSG_PUB.ADD;
5935: END IF;
5936: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5939: --Check last update login
5940: --If ( p_Terr_QualTypeUsgs_Rec.LAST_UPDATE_LOGIN is NULL OR
5941: -- p_Terr_QualTypeUsgs_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
5942: -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5943: -- FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5944: -- FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
5945: -- FND_MSG_PUB.ADD;
5946: -- END IF;
5947: -- x_Return_Status := FND_API.G_RET_STS_ERROR ;
5971: WHEN OTHERS THEN
5972: --dbms_output.put_line('Validate_Terr_Qtype_Usage: OTHERS - ' || SQLERRM);
5973: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5974:
5975: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
5976: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Qtype_Usage' );
5977: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
5978: FND_MSG_PUB.ADD;
5979: FND_MSG_PUB.Count_And_Get
6007: AS
6008: Cursor C_QualDef1 IS
6009: -- Get the qualifier usage related information
6010: select jqu.qual_col1_datatype, jqu.display_type, jqu.convert_to_id_flag
6011: from jtf_qual_usgs_ALL jqu, jtf_terr_qual_ALL jtq
6012: where jqu.qual_usg_id = jtq.qual_Usg_Id and
6013: jqu.org_id = jtq.org_id AND
6014: jtq.terr_qual_id = p_Terr_Qual_Id;
6015:
6033: /*-- Check for ORG_ID - obsolete: org_id is optional
6034: IF (p_Terr_Value_Rec.ORG_ID is NULL) OR
6035: (p_Terr_Value_Rec.ORG_ID = FND_API.G_MISS_NUM) THEN
6036: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6037: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6038: FND_MESSAGE.Set_Token('COL_NAME', 'ORG_ID' );
6039: FND_MSG_PUB.ADD;
6040: END IF;
6041: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6047:
6048: If ( p_Terr_Value_Rec.ID_USED_FLAG IS NULL OR
6049: p_Terr_Value_Rec.ID_USED_FLAG = FND_API.G_MISS_CHAR ) Then
6050: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6051: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6052: FND_MESSAGE.Set_Token('COL_NAME', 'ID_USED_FLAG' );
6053: FND_MSG_PUB.ADD;
6054: END IF;
6055: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6060: --Check created by
6061: IF ( p_Terr_Value_Rec.CREATED_BY is NULL OR
6062: p_Terr_Value_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
6063: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6064: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6065: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
6066: FND_MSG_PUB.ADD;
6067: END IF;
6068: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6071: --Check creation date
6072: If ( p_Terr_Value_Rec.CREATION_DATE is NULL OR
6073: p_Terr_Value_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
6074: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6075: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6076: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
6077: FND_MSG_PUB.ADD;
6078: END IF;
6079: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6082: -- Validate last updated by
6083: IF ( p_Terr_Value_Rec.LAST_UPDATED_BY is NULL OR
6084: p_Terr_Value_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
6085: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6086: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6087: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
6088: FND_MSG_PUB.ADD;
6089: END IF;
6090: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6093: -- Check last update date
6094: If ( p_Terr_Value_Rec.LAST_UPDATE_DATE IS NULL OR
6095: p_Terr_Value_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
6096: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6097: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6098: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
6099: FND_MSG_PUB.ADD;
6100: END IF;
6101: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6104: --Check last update login
6105: --If ( p_Terr_Value_Rec.LAST_UPDATE_LOGIN is NULL OR
6106: -- p_Terr_Value_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
6107: -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6108: -- FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6109: -- FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
6110: -- FND_MSG_PUB.ADD;
6111: -- END IF;
6112: -- x_Return_Status := FND_API.G_RET_STS_ERROR ;
6133: ( p_Terr_Value_Rec.INTEREST_TYPE_ID IS NOT NULL and
6134: p_Terr_Value_Rec.INTEREST_TYPE_ID <> FND_API.G_MISS_NUM ) ) THEN
6135:
6136: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6137: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
6138: FND_MSG_PUB.ADD;
6139: END IF;
6140:
6141: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6167: (p_Terr_Value_Rec.INTEREST_TYPE_ID IS NOT NULL and
6168: p_Terr_Value_Rec.INTEREST_TYPE_ID <> FND_API.G_MISS_NUM ) ) THEN
6169:
6170: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6171: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
6172: FND_MSG_PUB.ADD;
6173: END IF;
6174:
6175: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6186: (p_Terr_Value_Rec.COMPARISON_OPERATOR IS NULL OR
6187: p_Terr_Value_Rec.COMPARISON_OPERATOR = FND_API.G_MISS_CHAR ) ) THEN
6188:
6189: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6190: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
6191: FND_MSG_PUB.ADD;
6192: END IF;
6193:
6194: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6211: (p_Terr_Value_Rec.INTEREST_TYPE_ID IS NOT NULL and
6212: p_Terr_Value_Rec.INTEREST_TYPE_ID <> FND_API.G_MISS_NUM ) ) THEN
6213:
6214: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6215: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
6216: FND_MSG_PUB.ADD;
6217: END IF;
6218:
6219: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6235: p_Terr_Value_Rec.LOW_VALUE_CHAR <> FND_API.G_MISS_CHAR ) ) THEN
6236:
6237: --dbms_output('Error INTEREST_TYPE');
6238: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6239: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
6240: FND_MSG_PUB.ADD;
6241: END IF;
6242:
6243: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6270: WHEN OTHERS THEN
6271: --dbms_output('Validate_Terr_Qtype_Usage: OTHERS - ' || SQLERRM);
6272: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6273:
6274: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
6275: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_terr_Value_Rec' );
6276: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
6277: FND_MSG_PUB.ADD;
6278:
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:
6339:
6340: /* (1) check template exists */
6341: CURSOR c_chk_template (p_template_terr_id NUMBER) IS
6342: SELECT 'X'
6343: FROM jtf_terr_ALL j
6344: WHERE j.terr_id = p_template_terr_id
6345: AND j.template_flag = 'Y';
6346:
6347:
6347:
6348: /* (2) check that at least 1 dynamic is specified for use in generation */
6349: CURSOR c_chk_qual_mode (p_template_terr_id NUMBER) IS
6350: SELECT COUNT(*)
6351: FROM jtf_terr_qual j
6352: WHERE j.terr_id = p_template_terr_id
6353: AND j.qualifier_mode = 'DYNAMIC';
6354:
6355: l_terr_id NUMBER := 0;
6371: OPEN c_chk_template (p_template_terr_id);
6372: FETCH c_chk_template INTO l_csr_rtn;
6373: IF (c_chk_template%NOTFOUND) THEN
6374:
6375: fnd_message.set_name('JTF', 'JTF_TERR_INVALID_TEMPLATE');
6376: --fnd_msg_pub.add;
6377: x_return_status := FND_API.G_RET_STS_ERROR;
6378:
6379: END IF;
6386: --arpatel 07/13 bug#1872642
6387: --IF (c_chk_qual_mode%NOTFOUND) THEN
6388:
6389: IF (l_dynamic_qual_count = 0) THEN
6390: fnd_message.set_name ('JTF', 'JTF_TERR_NO_DYNAMIC_QUALIFIERS');
6391: fnd_msg_pub.add;
6392: x_return_status := FND_API.G_RET_STS_ERROR;
6393:
6394: END IF;
6402: --OPEN c_lock_template (p_template_terr_id);
6403:
6404: SELECT j1.terr_id
6405: INTO l_terr_id
6406: FROM jtf_terr j1
6407: WHERE j1.terr_id = p_template_terr_id
6408: FOR UPDATE NOWAIT;
6409:
6410: EXCEPTION
6408: FOR UPDATE NOWAIT;
6409:
6410: EXCEPTION
6411: WHEN OTHERS THEN
6412: fnd_message.set_name('JTF','JTF_TERR_TEMPLATE_LOCKED');
6413: x_return_status := FND_API.G_RET_STS_ERROR;
6414: END; /* block for validation (4) */
6415:
6416: --dbms_output('Validate_Template_Record: x_return_status = '|| x_return_status);
6507: , j1.ATTRIBUTE14
6508: , j1.ATTRIBUTE15
6509: , j1.ORG_ID
6510: , j1.NUM_WINNERS
6511: FROM jtf_terr j1
6512: WHERE j1.terr_id = p_template_terr_id;
6513:
6514: /* cursor to get the number of territories that will be generated */
6515: CURSOR c_get_max_value_set (p_template_terr_id NUMBER) IS
6513:
6514: /* cursor to get the number of territories that will be generated */
6515: CURSOR c_get_max_value_set (p_template_terr_id NUMBER) IS
6516: SELECT MAX (j1.VALUE_SET)
6517: FROM jtf_terr_values j1, jtf_terr_qual j2, jtf_terr j3
6518: WHERE j1.terr_qual_id = j2.terr_qual_id
6519: AND j2.terr_id = j3.terr_id
6520: AND j3.terr_id = p_template_terr_id
6521: AND j2.qualifier_mode = 'DYNAMIC'
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
6545: , j1.CREATION_DATE
6546: , j1.CREATED_BY
6547: , j1.LAST_UPDATE_LOGIN
6548: , j1.ORG_ID
6549: from jtf_terr_qtype_usgs j1
6550: WHERE j1.terr_id = p_template_terr_id;
6551:
6552: l_product NUMBER := 1;
6553: l_num_gen_terr NUMBER := 1;
6813: l_new_terr_rec.CREATED_BY := FND_GLOBAL.USER_ID;
6814: l_new_terr_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
6815: l_new_terr_rec.APPLICATION_SHORT_NAME := FND_GLOBAL.APPLICATION_SHORT_NAME;
6816:
6817: -- dbms_output.put_line('JTF_TERRITORY_PVT:create_copied_terr:l_new_terr_rec.APPLICATION_SHORT_NAME-' || l_new_terr_rec.APPLICATION_SHORT_NAME);
6818:
6819: -- 04/06/01 ARPATEL - START
6820: IF (p_copy_hierarchy_flag = 'Y') THEN
6821:
7034:
7035: /* START OF 1520656 BUG FIX - JDOCHERT 12/07 */
7036: CURSOR c_get_terr(lp_terr_id NUMBER)
7037: IS SELECT 'Y'
7038: FROM jtf_terr_ALL
7039: WHERE template_flag = 'Y'
7040: AND terr_id = lp_terr_id;
7041:
7042: l_template_flag VARCHAR2(1);
7120:
7121: /* START OF 1520656 BUG FIX - JDOCHERT 12/07 */
7122: CURSOR c_get_terr(lp_terr_id NUMBER)
7123: IS SELECT 'Y'
7124: FROM jtf_terr
7125: WHERE template_flag = 'Y'
7126: AND terr_id = lp_terr_id;
7127:
7128: l_template_flag VARCHAR2(1);
7226: , j1.GENERATE_FLAG
7227: , j1.OVERLAP_ALLOWED_FLAG
7228: , j1.QUALIFIER_MODE
7229: , j1.ORG_ID
7230: FROM jtf_terr_qual j1
7231: WHERE j1.terr_id = p_template_terr_id;
7232:
7233: /* cursor to get values for STATIC qualifiers' values */
7234: CURSOR c_get_terr_value (p_terr_qual_id NUMBER) IS
7258: , j1.VALUE1_ID
7259: , j1.VALUE2_ID
7260: , j1.VALUE3_ID
7261: , j1.VALUE4_ID
7262: FROM jtf_terr_values j1
7263: WHERE j1.terr_qual_id = p_terr_qual_id;
7264:
7265: /* local record variables */
7266: l_tmpl_terr_qual_rec Terr_Qual_Rec_Type;
7379: , j1.GENERATE_FLAG
7380: , j1.OVERLAP_ALLOWED_FLAG
7381: , j1.QUALIFIER_MODE
7382: , j1.ORG_ID
7383: FROM jtf_terr_qual j1
7384: WHERE j1.terr_id = p_template_terr_id
7385: AND j1.qualifier_mode = 'STATIC';
7386:
7387: /* cursor to get values for STATIC qualifiers' values */
7412: , j1.VALUE1_ID
7413: , j1.VALUE2_ID
7414: , j1.VALUE3_ID
7415: , j1.VALUE4_ID
7416: FROM jtf_terr_values j1
7417: WHERE j1.terr_qual_id = p_terr_qual_id;
7418:
7419: /* local record variables */
7420: l_tmpl_terr_qual_rec Terr_Qual_Rec_Type;
7525: , j1.GENERATE_FLAG
7526: , j1.OVERLAP_ALLOWED_FLAG
7527: , j1.QUALIFIER_MODE
7528: , j1.ORG_ID
7529: FROM jtf_terr_qual j1
7530: WHERE j1.terr_id = p_template_terr_id
7531: AND j1.qualifier_mode = 'DYNAMIC';
7532:
7533: /* cursor to get values for dynamic qualifier values */
7558: , j1.VALUE1_ID
7559: , j1.VALUE2_ID
7560: , j1.VALUE3_ID
7561: , j1.VALUE4_ID
7562: FROM jtf_terr_values j1
7563: WHERE j1.terr_qual_id = p_terr_qual_id
7564: ORDER BY j1.value_set;
7565:
7566: l_tmpl_terr_qual_rec Terr_Qual_Rec_Type;
7832:
7833: -- Debug Message
7834: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
7835: THEN
7836: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
7837: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
7838: FND_MSG_PUB.Add;
7839: END IF;
7840:
7851:
7852: /* Debug message */
7853: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
7854: THEN
7855: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
7856: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Template_Record');
7857: FND_MSG_PUB.Add;
7858: END IF;
7859:
7910: l_tmpl_terr_qual_rec.overlap_allowed_flag := l_dyn_qual_tbl(i).OVERLAP_ALLOWED_FLAG;
7911: l_tmpl_terr_qual_rec.qualifier_mode := l_dyn_qual_tbl(i).QUALIFIER_MODE;
7912: l_tmpl_terr_qual_rec.org_id := l_dyn_qual_tbl(i).ORG_ID;
7913:
7914: /* insert record IN JTF_TERR_QUAL_ALL */
7915: create_new_terr_qual_rec(l_new_terr_id, l_tmpl_terr_qual_rec, l_new_terr_qual_id);
7916:
7917: l_rec_index := l_dyn_qual_tbl(i).start_record + l_dyn_qual_tbl(i).current_record;
7918:
7996:
7997: -- Debug Message
7998: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
7999: THEN
8000: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
8001: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
8002: FND_MSG_PUB.Add;
8003: END IF;
8004:
8095: l_tbl_count NUMBER;
8096:
8097: CURSOR csr_child_terrs (p_terr_id NUMBER) IS
8098: select jta.terr_id
8099: from JTF_TERR_ALL jta
8100: where jta.parent_territory_id = p_terr_Id;
8101:
8102: BEGIN
8103:
8120:
8121: -- Debug Message
8122: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
8123: THEN
8124: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
8125: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
8126: FND_MSG_PUB.Add;
8127: END IF;
8128:
8135: /* ======================*/
8136:
8137: /* (validation procedures not necessary) */
8138:
8139: --dbms_output('JTF_TERRITORY_PVT:gen_dupliacte_territory: - BEGIN');
8140: -- get table of terr usages and terr qual type usages for the source territory
8141: initialise( p_copy_source_terr_id -- input TERR_ID
8142: , l_copy_source_terr_rec -- output desc
8143: , l_num_gen_terr -- output (irrelevant)
8157: create_new_terr_qtype_usgs(l_new_terr_id, l_tmpl_qtype_usgs_tbl);
8158:
8159: /* for all qualifier values */
8160: insert_copied_qual_values(p_copy_source_terr_id, l_new_terr_id);
8161: --dbms_output('JTF_TERRITORY_PVT:gen_dupliacte_territory: - END');
8162:
8163: /* START: 09/17/00 - JDOCHERT */
8164: IF (p_copy_rsc_flag = 'Y') THEN
8165:
8162:
8163: /* START: 09/17/00 - JDOCHERT */
8164: IF (p_copy_rsc_flag = 'Y') THEN
8165:
8166: JTF_TERRITORY_RESOURCE_PVT.copy_terr_resources( p_Api_Version_Number => p_Api_Version_Number,
8167: p_Init_Msg_List => p_Init_Msg_List,
8168: p_Commit => p_Commit,
8169: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
8170: p_source_terr_id => p_copy_source_terr_id,
8184: loop
8185:
8186: --p_new_terr_rec.parent_territory_id := l_new_terr_id;
8187:
8188: JTF_TERRITORY_PVT.Copy_Territory (
8189: p_Api_Version_Number => p_Api_Version_Number,
8190: p_Init_Msg_List => p_Init_Msg_List,
8191: p_Commit => p_Commit,
8192: p_validation_level => p_validation_level,
8199: x_Msg_Count => l_msg_count,
8200: x_Msg_Data => l_msg_data,
8201: x_terr_id => l_terr_id );
8202:
8203: update JTF_TERR_ALL
8204: set parent_territory_id = l_new_terr_id
8205: where terr_id = l_terr_id;
8206:
8207: end loop;
8219:
8220: -- Debug Message
8221: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
8222: THEN
8223: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
8224: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
8225: FND_MSG_PUB.Add;
8226: END IF;
8227:
8280: -- checked only under a single parent.
8281: ---------------------------------------------------------------------
8282: FUNCTION Overlap_Exists(p_Parent_Terr_Id IN NUMBER,
8283: p_Qual_Usg_Id IN NUMBER,
8284: p_terr_value_record IN jtf_terr_values%ROWTYPE )
8285: RETURN VARCHAR2
8286: AS
8287: dummy NUMBER := 0;
8288: l_found NUMBER := 0;
8305: Then
8306: If p_terr_value_record.low_value_char is NOT NULL Then
8307: Select count(*)
8308: into dummy
8309: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8310: Where jt.parent_territory_id = p_Parent_Terr_Id
8311: and jtq.terr_id = jt.terr_id
8312: and jtq.qual_usg_id = p_Qual_Usg_Id
8313: and jtv.terr_qual_id = jtq.terr_qual_id
8332: -- Check the high_value_char
8333: If p_terr_value_record.high_value_char is NOT NULL Then
8334: Select count(*)
8335: into dummy
8336: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8337: Where jt.parent_territory_id = p_Parent_Terr_Id
8338: and jtq.terr_id = jt.terr_id
8339: and jtq.qual_usg_id = p_Qual_Usg_Id
8340: and jtv.terr_qual_id = jtq.terr_qual_id
8363: -- only check for a subset of the availabel operator as these are the only
8364: -- available one for this combination
8365: Select count(*)
8366: into dummy
8367: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8368: Where jt.parent_territory_id = p_Parent_Terr_Id
8369: and jtq.terr_id = jt.terr_id
8370: and jtq.qual_usg_id = p_Qual_Usg_Id
8371: and jtv.terr_qual_id = jtq.terr_qual_id
8381:
8382: If p_terr_value_record.low_value_number is NOT NULL Then
8383: Select 1
8384: into dummy
8385: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8386: Where jt.parent_territory_id = p_Parent_Terr_Id
8387: and jtq.terr_id = jt.terr_id
8388: and jtq.qual_usg_id = p_Qual_Usg_Id
8389: and jtv.terr_qual_id = jtq.terr_qual_id
8403:
8404: If p_terr_value_record.high_value_number is NOT NULL Then
8405: Select 1
8406: into dummy
8407: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8408: Where jt.parent_territory_id = p_Parent_Terr_Id
8409: and jtq.terr_id = jt.terr_id
8410: and jtq.qual_usg_id = p_Qual_Usg_Id
8411: and jtv.terr_qual_id = jtq.terr_qual_id
8431:
8432: If p_terr_value_record.low_value_number is NOT NULL Then
8433: Select count(*)
8434: into dummy
8435: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8436: Where jt.parent_territory_id = p_Parent_Terr_Id
8437: and jtq.terr_id = jt.terr_id
8438: and jtq.qual_usg_id = p_Qual_Usg_Id
8439: and jtv.terr_qual_id = jtq.terr_qual_id
8453:
8454: If p_terr_value_record.high_value_number is NOT NULL Then
8455: Select count(*)
8456: into dummy
8457: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8458: Where jt.parent_territory_id = p_Parent_Terr_Id
8459: and jtq.terr_id = jt.terr_id
8460: and jtq.qual_usg_id = p_Qual_Usg_Id
8461: and jtv.terr_qual_id = jtq.terr_qual_id
8479: l_convert_to_id_flag is NULL ) )
8480: Then
8481: Select count(*)
8482: into dummy
8483: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8484: Where jt.parent_territory_id = p_Parent_Terr_Id
8485: and jtq.terr_id = jt.terr_id
8486: and jtq.qual_usg_id = p_Qual_Usg_Id
8487: and jtv.terr_qual_id = jtq.terr_qual_id
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 */
8569:
8570: WHEN OTHERS THEN
8571: --dbms_output('Validate_Foreign_Key: Others exception' || SQLERRM);
8572: X_return_status := FND_API.G_RET_STS_ERROR;
8573: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
8574: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Unique_Key' );
8575: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
8576: FND_MSG_PUB.ADD;
8577: FND_MSG_PUB.Count_And_Get
8581:
8582: END validate_terr_usgs_UK;
8583:
8584: -- jdochert 09/09
8585: -- check for Unique Key constraint violation on JTF_TERR_QTYPE_USGS table
8586: PROCEDURE validate_terr_qtype_usgs_UK(
8587: p_Terr_Id IN NUMBER,
8588: p_Qual_Type_Usg_Id IN NUMBER,
8589: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8595: -- cursor to check that Unique Key constraint not violated
8596: CURSOR csr_chk_uk_violation ( lp_terr_id NUMBER
8597: , lp_qual_type_usg_id NUMBER) IS
8598: SELECT 'X'
8599: FROM JTF_TERR_QTYPE_USGS_ALL
8600: WHERE terr_id = lp_terr_id
8601: AND qual_type_usg_id = lp_qual_type_usg_id;
8602:
8603: l_return_csr VARCHAR2(1);
8624:
8625: /* Debug message */
8626: --arpatel 09/18 - set message for form to pick up.
8627: --IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
8628: FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_QTYPE_USGS_UK_CON');
8629: FND_MSG_PUB.ADD;
8630:
8631: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
8632: p_data => x_msg_data);
8645:
8646: WHEN OTHERS THEN
8647: --dbms_output('Validate_Foreign_Key: Others exception' || SQLERRM);
8648: X_return_status := FND_API.G_RET_STS_ERROR;
8649: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
8650: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Unique_Key' );
8651: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
8652: FND_MSG_PUB.ADD;
8653: FND_MSG_PUB.Count_And_Get
8674: ** 09/17/00 JDOCHERT BUG#1408610 FIX
8675: */
8676: CURSOR csr_chk_num_winners (lp_terr_id NUMBER) IS
8677: SELECT 'X'
8678: FROM jtf_terr_ALL j
8679: WHERE j.parent_territory_id = 1
8680: AND j.terr_id = lp_terr_id;
8681:
8682: l_return_csr VARCHAR2(1);
8705: x_return_status := FND_API.G_RET_STS_ERROR;
8706:
8707: /* Debug message */
8708: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
8709: FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_NUM_WINNERS_TOP_LEVEL');
8710: FND_MSG_PUB.ADD;
8711: END IF;
8712:
8713: END IF;
8719:
8720: WHEN OTHERS THEN
8721: --dbms_output('Validate_Num_Winners: Others exception' || SQLERRM);
8722: X_return_status := FND_API.G_RET_STS_ERROR;
8723: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
8724: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Num_Winners' );
8725: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
8726: FND_MSG_PUB.ADD;
8727: FND_MSG_PUB.Count_And_Get
8743: AS
8744:
8745: CURSOR csr_get_terr_num (lp_terr_id NUMBER) IS
8746: SELECT COUNT(*)
8747: FROM jtf_terr_all jt
8748: CONNECT BY PRIOR jt.terr_id = jt.parent_territory_id
8749: START WITH jt.terr_id = lp_terr_id;
8750:
8751: BEGIN
8769:
8770:
8771: /* Debug message */
8772: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
8773: FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_NUM_COPY_TERR');
8774: FND_MSG_PUB.ADD;
8775: END IF;
8776:
8777: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
8780: EXCEPTION
8781: WHEN OTHERS THEN
8782: --dbms_output('chk_num_copy_terr: Others exception' || SQLERRM);
8783: X_return_status := FND_API.G_RET_STS_ERROR;
8784: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
8785: FND_MESSAGE.Set_Token('PROC_NAME', 'chk_num_copy_terr' );
8786: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
8787: FND_MSG_PUB.ADD;
8788: FND_MSG_PUB.Count_And_Get
8807: p_debug_flag IN VARCHAR2 := 'N',
8808: p_sql_trace IN VARCHAR2 := 'N' ) AS
8809:
8810: /* local record variables */
8811: l_terr_rec JTF_TERRITORY_PVT.TERR_ALL_REC_TYPE;
8812:
8813: /* local item variables */
8814: l_terr_id NUMBER;
8815: l_hier_terr_count NUMBER;
8898: l_terr_rec.ATTRIBUTE15 := FND_API.G_MISS_CHAR;
8899: l_terr_rec.ORG_ID := FND_API.G_MISS_NUM;
8900: l_terr_rec.NUM_WINNERS := FND_API.G_MISS_NUM;
8901:
8902: JTF_TERRITORY_PVT.Copy_Territory (
8903: p_Api_Version_Number => l_Api_Version_Number,
8904: p_Init_Msg_List => FND_API.G_TRUE,
8905: p_Commit => FND_API.G_TRUE,
8906: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
8937: IF (G_Debug) THEN
8938:
8939: SELECT COUNT(*)
8940: INTO l_hier_terr_count
8941: FROM jtf_terr_all jt
8942: CONNECT BY PRIOR jt.terr_id = jt.parent_territory_id
8943: START WITH jt.terr_id = p_copy_source_terr_Id;
8944:
8945: Write_Log(2, ' ');
8943: START WITH jt.terr_id = p_copy_source_terr_Id;
8944:
8945: Write_Log(2, ' ');
8946: Write_Log(2, '/***************** BEGIN: COPY TERRITORY STATUS *********************/');
8947: Write_Log(2, 'Populating JTF_TERR_ALL table. Copied ' || l_hier_terr_count || ' territories.');
8948: Write_Log(2, 'Inserted ' || l_hier_terr_count || ' rows into JTF_TERR_ALL ');
8949: Write_Log(2, '/***************** END: COPY TERRITORY STATUS *********************/');
8950:
8951: END IF;
8944:
8945: Write_Log(2, ' ');
8946: Write_Log(2, '/***************** BEGIN: COPY TERRITORY STATUS *********************/');
8947: Write_Log(2, 'Populating JTF_TERR_ALL table. Copied ' || l_hier_terr_count || ' territories.');
8948: Write_Log(2, 'Inserted ' || l_hier_terr_count || ' rows into JTF_TERR_ALL ');
8949: Write_Log(2, '/***************** END: COPY TERRITORY STATUS *********************/');
8950:
8951: END IF;
8952:
9072: BEGIN
9073:
9074: SELECT j.parent_territory_id
9075: INTO l_existing_parent_terr_id
9076: FROM jtf_terr_all j
9077: WHERE j.terr_id = p_terr_all_rec.terr_id;
9078:
9079: EXCEPTION
9080: WHEN NO_DATA_FOUND THEN
9088: ** of the current territory
9089: */
9090: SELECT COUNT(*)
9091: INTO l_return_csr
9092: FROM jtf_terr_all j
9093: WHERE j.terr_id = p_terr_all_rec.parent_territory_id -- new parent territory id
9094: CONNECT BY PRIOR j.terr_id = j.parent_territory_id
9095: START WITH j.terr_id = p_terr_all_rec.terr_id; -- territory_id
9096:
9103: --dbms_output.put_line('[3]l_return_csr = ' || l_return_csr);
9104:
9105: /* Debug message */
9106: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
9107: FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_CIRCULAR_REFERENCE');
9108: FND_MSG_PUB.ADD;
9109: END IF;
9110:
9111: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
9117: --dbms_output.put_line('Validate_Parent: e_Circular_Reference exception' || SQLERRM);
9118:
9119: X_return_status := FND_API.G_RET_STS_ERROR;
9120:
9121: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_EXPECTED_ERROR');
9122: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Parent' );
9123: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
9124: FND_MSG_PUB.ADD;
9125:
9130:
9131: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
9132: --dbms_output.put_line('Validate_Parent: FND_API.G_EXC_UNEXPECTED_ERROR');
9133: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
9134: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
9135: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Parent' );
9136: FND_MSG_PUB.Count_And_Get
9137: ( P_count => x_msg_count,
9138: P_data => x_msg_data
9140:
9141: WHEN OTHERS THEN
9142: --dbms_output.put_line('Validate_Parent: Others exception' || SQLERRM);
9143: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
9144: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
9145: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Parent' );
9146: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
9147: FND_MSG_PUB.ADD;
9148: FND_MSG_PUB.Count_And_Get
9151: );
9152: END validate_parent;
9153:
9154:
9155: /* Function used in JTF_TERR_VALUES_DESC_V to return
9156: ** descriptive values for ids and lookup_codes
9157: */
9158: FUNCTION get_terr_value_desc (
9159: p_convert_to_id_flag VARCHAR2
9239: END get_terr_value_desc;
9240:
9241:
9242: -- Package Body
9243: END JTF_TERRITORY_PVT;