[Home] [Help]
1: Package BODY JTF_TERRITORY_PVT AS
2: /* $Header: jtfvterb.pls 120.11 2011/05/12 20:29:07 srguntur ship $ */
3: -- ---------------------------------------------------
4: -- Start of Comments
5: -- ---------------------------------------------------
2: /* $Header: jtfvterb.pls 120.11 2011/05/12 20:29:07 srguntur 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;
255: -- Check whether the Terr Transaction Types are specified.
256: If (p_Terr_QualTypeUsgs_Tbl.COUNT = 0 and
257: p_Terr_All_Rec.ESCALATION_TERRITORY_FLAG <> 'Y') Then
258: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
259: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERR_QUAL_USG');
260: FND_MSG_PUB.ADD;
261: END IF;
262: x_return_status := FND_API.G_RET_STS_ERROR;
263: raise FND_API.G_EXC_ERROR;
304:
305: --Check whether the Qualifer records are specified
306: --If (p_Terr_Qual_Tbl.count = 0 ) Then
307: -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
308: -- FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERR_QUAL');
309: -- FND_MSG_PUB.ADD;
310: -- END IF;
311: -- x_return_status := FND_API.G_RET_STS_ERROR;
312: -- raise FND_API.G_EXC_ERROR;
369:
370: --Remove the qualifier if no values are specified.
371: BEGIN
372: IF l_index = 0 THEN
373: JTF_TERR_QUAL_PKG.Delete_Row(x_terr_qual_id => l_Terr_Qual_Id );
374: FND_MESSAGE.Set_Name('JTF', 'JTY_TERR_MISSING_TERR_VALUES');
375: FND_MESSAGE.Set_Token('QUAL_USG_ID', to_char(l_Terr_Qual_Tbl(1).QUAL_USG_ID));
376: FND_MSG_PUB.ADD;
377: End If;
430:
431: -- Debug Message
432: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
433: THEN
434: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
435: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
436: FND_MSG_PUB.Add;
437: END IF;
438:
529: v_Terr_Id NUMBER := p_Terr_Id;
530:
531: --Declare cursor to get usages
532: Cursor C_Terr IS
533: select terr_id from jtf_terr_all
534: connect by parent_territory_id = prior terr_id AND TERR_ID <> 1
535: start with terr_id = v_Terr_Id;
536:
537: CURSOR C_TERR_QUAL_IDS ( p_Terr_Id IN NUMBER) IS
535: start with terr_id = v_Terr_Id;
536:
537: CURSOR C_TERR_QUAL_IDS ( p_Terr_Id IN NUMBER) IS
538: SELECT TERR_QUAL_ID
539: FROM JTF_TERR_QUAL_ALL
540: WHERE TERR_ID = p_Terr_Id;
541:
542:
543:
574:
575: -- Debug Message
576: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
577: THEN
578: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
579: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
580: FND_MSG_PUB.Add;
581: END IF;
582:
591: LOOP
592: FETCH C_TERR_QUAL_IDS INTO l_terr_qual_id;
593: EXIT WHEN C_TERR_QUAL_IDS%NOTFOUND;
594: --Delete Territory Values
595: DELETE from JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID = l_terr_qual_id;
596: END LOOP;
597: CLOSE C_TERR_QUAL_IDS;
598: EXCEPTION
599: WHEN NO_DATA_FOUND THEN
602: CLOSE C_TERR_QUAL_IDS;
603: END;
604:
605: --Delete Territory Qualifer records
606: DELETE from JTF_TERR_QUAL_ALL WHERE TERR_ID = c.Terr_Id;
607:
608: --Delete Territory qual type usgs
609: DELETE from JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID = c.Terr_Id;
610:
605: --Delete Territory Qualifer records
606: DELETE from JTF_TERR_QUAL_ALL WHERE TERR_ID = c.Terr_Id;
607:
608: --Delete Territory qual type usgs
609: DELETE from JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID = c.Terr_Id;
610:
611: --Delete Territory usgs
612: DELETE from JTF_TERR_USGS_ALL WHERE TERR_ID = c.Terr_Id;
613:
608: --Delete Territory qual type usgs
609: DELETE from JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID = c.Terr_Id;
610:
611: --Delete Territory usgs
612: DELETE from JTF_TERR_USGS_ALL WHERE TERR_ID = c.Terr_Id;
613:
614: --Delete Territory Resource Access
615: DELETE from JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
616: ( SELECT TERR_RSC_ID FROM JTF_TERR_RSC_ALL WHERE TERR_ID = c.Terr_Id );
611: --Delete Territory usgs
612: DELETE from JTF_TERR_USGS_ALL WHERE TERR_ID = c.Terr_Id;
613:
614: --Delete Territory Resource Access
615: DELETE from JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
616: ( SELECT TERR_RSC_ID FROM JTF_TERR_RSC_ALL WHERE TERR_ID = c.Terr_Id );
617:
618: -- Delete the Territory Resource records
619: DELETE from JTF_TERR_RSC_ALL Where TERR_ID = c.Terr_Id;
612: DELETE from JTF_TERR_USGS_ALL WHERE TERR_ID = c.Terr_Id;
613:
614: --Delete Territory Resource Access
615: DELETE from JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
616: ( SELECT TERR_RSC_ID FROM JTF_TERR_RSC_ALL WHERE TERR_ID = c.Terr_Id );
617:
618: -- Delete the Territory Resource records
619: DELETE from JTF_TERR_RSC_ALL Where TERR_ID = c.Terr_Id;
620:
615: DELETE from JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
616: ( SELECT TERR_RSC_ID FROM JTF_TERR_RSC_ALL WHERE TERR_ID = c.Terr_Id );
617:
618: -- Delete the Territory Resource records
619: DELETE from JTF_TERR_RSC_ALL Where TERR_ID = c.Terr_Id;
620:
621: --Delete Territory record
622: DELETE from JTF_TERR_ALL WHERE TERR_ID = c.Terr_Id;
623:
618: -- Delete the Territory Resource records
619: DELETE from JTF_TERR_RSC_ALL Where TERR_ID = c.Terr_Id;
620:
621: --Delete Territory record
622: DELETE from JTF_TERR_ALL WHERE TERR_ID = c.Terr_Id;
623:
624: --
625: END LOOP;
626:
626:
627: -- Debug Message
628: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
629: THEN
630: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
631: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
632: FND_MSG_PUB.Add;
633: END IF;
634:
767:
768: -- Debug Message
769: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
770: THEN
771: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
772: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
773: FND_MSG_PUB.Add;
774: END IF;
775:
783: If (p_Terr_All_Rec.TERR_ID is null) OR
784: (p_Terr_All_Rec.TERR_ID = FND_API.G_MISS_NUM)
785: Then
786: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
787: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
788: FND_MESSAGE.Set_Token('COL_NAME', 'TERR_ID' );
789: FND_MSG_PUB.ADD;
790: END IF;
791: x_Return_Status := FND_API.G_RET_STS_ERROR ;
892: */
893: -- Debug Message
894: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
895: THEN
896: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
897: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
898: FND_MSG_PUB.Add;
899: END IF;
900:
1037: ATTRIBUTE15,
1038: ORG_ID,
1039: NUM_WINNERS,
1040: NUM_QUAL
1041: From JTF_TERR_ALL
1042: Where TERR_ID = l_terr_id
1043: For Update NOWAIT;
1044:
1045: --Local variable declaration
1072:
1073: -- Debug Message
1074: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1075: THEN
1076: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1077: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
1078: FND_MSG_PUB.Add;
1079: END IF;
1080:
1147: raise FND_API.G_EXC_ERROR;
1148: End if;
1149: CLOSE C_GetTerritory;
1150:
1151: --dbms_output('Deactivate_Territory PVT: Before Calling JTF_TERR_PKG.Update_Row');
1152: JTF_TERR_PKG.Update_Row(x_rowid => l_rowid,
1153: x_terr_id => p_terr_id,
1154: x_last_update_date => l_ref_terr_all_rec.LAST_UPDATE_DATE,
1155: x_last_updated_by => l_ref_terr_all_rec.LAST_UPDATED_BY,
1148: End if;
1149: CLOSE C_GetTerritory;
1150:
1151: --dbms_output('Deactivate_Territory PVT: Before Calling JTF_TERR_PKG.Update_Row');
1152: JTF_TERR_PKG.Update_Row(x_rowid => l_rowid,
1153: x_terr_id => p_terr_id,
1154: x_last_update_date => l_ref_terr_all_rec.LAST_UPDATE_DATE,
1155: x_last_updated_by => l_ref_terr_all_rec.LAST_UPDATED_BY,
1156: x_creation_date => l_ref_terr_all_rec.CREATION_DATE,
1201:
1202: -- Debug Message
1203: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1204: THEN
1205: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1206: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
1207: FND_MSG_PUB.Add;
1208: END IF;
1209:
1321: l_Terr_QualTypeUsgs_Out_Tbl Terr_QualTypeUsgs_Out_Tbl_Type;
1322: l_qual_type_usg_id NUMBER ;
1323: l_dummy NUMBER;
1324: l_iterator NUMBER;
1325: p_terr_qualtypeusgs_tbl JTF_TERRITORY_PVT.terr_qualtypeusgs_tbl_type;
1326:
1327: CURSOR C_QUAL_TYPE_USG_ID (P_terr_type_id NUMBER)
1328: is
1329: SELECT QUAL_TYPE_USG_ID from JTF_TYPE_QTYPE_USGS_ALL WHERE terr_type_id = P_terr_type_id ;
1333:
1334: -- Debug Message
1335: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1336: THEN
1337: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1338: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
1339: FND_MSG_PUB.Add;
1340: END IF;
1341: -- Initialize API return status to success
1349: AND p_terr_all_rec.PARENT_TERRITORY_ID <> 1 ) THEN
1350:
1351: SELECT 1
1352: INTO l_dummy
1353: FROM jtf_terr_usgs_all jtua
1354: WHERE jtua.terr_id = p_terr_all_rec.PARENT_TERRITORY_ID
1355: AND jtua.source_id = p_terr_usgs_tbl (1).source_id
1356: AND jtua.org_id = p_terr_all_rec.org_id ;
1357: END IF;
1357: END IF;
1358:
1359: EXCEPTION
1360: WHEN NO_DATA_FOUND THEN
1361: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_PARENT_TERR');
1362: FND_MESSAGE.Set_Token('TERR_ID', to_char(p_terr_all_rec.PARENT_TERRITORY_ID));
1363: FND_MSG_PUB.ADD;
1364: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1365: END;
1366: -- Check if the Terr Type is valid for the given source.
1367: BEGIN
1368: SELECT 1
1369: INTO l_dummy
1370: FROM jtf_terr_types_all a,
1371: jtf_terr_type_usgs_all b
1372: WHERE a.enabled_flag = 'Y'
1373: AND a.terr_type_id = b.terr_type_id
1374: AND a.org_id = b.org_id
1367: BEGIN
1368: SELECT 1
1369: INTO l_dummy
1370: FROM jtf_terr_types_all a,
1371: jtf_terr_type_usgs_all b
1372: WHERE a.enabled_flag = 'Y'
1373: AND a.terr_type_id = b.terr_type_id
1374: AND a.org_id = b.org_id
1375: AND b.source_id = p_terr_usgs_tbl (1).source_id
1399: X_Terr_Id => l_terr_id,
1400: X_Terr_All_Out_Rec => l_Terr_All_Out_Rec);
1401:
1402: ELSE
1403: UPDATE JTF_TERR_ALL
1404: SET terr_group_id = p_terr_all_rec.territory_group_id,
1405: LAST_UPDATED_BY = p_terr_all_rec.LAST_UPDATED_BY,
1406: LAST_UPDATE_LOGIN = p_terr_all_rec.LAST_UPDATE_LOGIN,
1407: NAME = p_terr_all_rec.NAME,
1478: EXCEPTION
1479: WHEN OTHERS THEN
1480: CLOSE C_QUAL_TYPE_USG_ID;
1481: END;
1482: -- Call api to insert records into jtf_terr_qualtype_usgs
1483: --
1484: --dbms_output('Create_Territory_Header PVT: Before Calling Create_Terr_QualType_Usage');
1485:
1486: Create_Terr_QualType_Usage(P_Api_Version_Number => P_Api_Version_Number,
1507: --
1508: -- Debug Message
1509: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1510: THEN
1511: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
1512: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
1513: FND_MSG_PUB.Add;
1514: END IF;
1515:
1547: -- start of comments
1548: -- ***************************************************
1549: -- API name : Create_Territory_record
1550: -- Type : PUBLIC
1551: -- Function : To create a records in jtf_Terr_all table
1552: --
1553: -- Pre-reqs :
1554: -- Parameters:
1555: -- IN :
1609:
1610: -- Debug Message
1611: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1612: THEN
1613: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1614: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
1615: FND_MSG_PUB.Add;
1616: END IF;
1617:
1622: THEN
1623: -- Debug message
1624: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1625: THEN
1626: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
1627: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Territory_Record');
1628: FND_MSG_PUB.Add;
1629: END IF;
1630: --
1641: END IF;
1642:
1643: -- Call insert terr_all table handler
1644: --
1645: --dbms_output('Create_Territory_Record PVT: Before Calling JTF_TERR_PKG.Insert_Row APP_SHORT_NAME-' || G_APP_SHORT_NAME);
1646: --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);
1647:
1648: /* Intialise to NULL if FND_API.G_MISS_NUM,
1649: ** otherwise use passed in value
1642:
1643: -- Call insert terr_all table handler
1644: --
1645: --dbms_output('Create_Territory_Record PVT: Before Calling JTF_TERR_PKG.Insert_Row APP_SHORT_NAME-' || G_APP_SHORT_NAME);
1646: --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);
1647:
1648: /* Intialise to NULL if FND_API.G_MISS_NUM,
1649: ** otherwise use passed in value
1650: */
1651: IF (l_terr_id = FND_API.G_MISS_NUM) THEN
1652: l_terr_id := NULL;
1653: END IF;
1654:
1655: JTF_TERR_PKG.Insert_Row(x_rowid => l_rowid,
1656: x_terr_id => l_terr_id,
1657: x_last_update_date => p_terr_all_rec.LAST_UPDATE_DATE,
1658: x_last_updated_by => p_terr_all_rec.LAST_UPDATED_BY,
1659: x_creation_date => p_terr_all_rec.CREATION_DATE,
1709:
1710: -- Debug Message
1711: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1712: THEN
1713: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1714: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
1715: FND_MSG_PUB.Add;
1716: END IF;
1717:
1832:
1833: -- Debug Message
1834: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1835: THEN
1836: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1837: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
1838: FND_MSG_PUB.Add;
1839: END IF;
1840:
1843:
1844: --
1845: If (p_Terr_Usgs_Tbl.count = 0 ) Then
1846: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1847: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERR_USAGE');
1848: FND_MSG_PUB.ADD;
1849: END IF;
1850: x_return_status := FND_API.G_RET_STS_ERROR;
1851: RAISE FND_API.G_EXC_ERROR;
1855: FOR l_Counter IN 1 .. l_Terr_Usgs_Tbl_Count LOOP
1856: --
1857: BEGIN
1858:
1859: --dbms_output('Create_Territory_Usages PVT: Before Calling JTF_TERR_USGS_PKG.Insert_Row');
1860:
1861: -- Initialize API return status to success
1862: x_return_status := FND_API.G_RET_STS_SUCCESS;
1863:
1906: IF (l_terr_usg_id = FND_API.G_MISS_NUM) THEN
1907: l_terr_usg_id := NULL;
1908: END IF;
1909:
1910: JTF_TERR_USGS_PKG.Insert_Row(x_Rowid => l_rowid,
1911: x_TERR_USG_ID => l_terr_usg_id,
1912: x_LAST_UPDATE_DATE => P_Terr_Usgs_Tbl(l_Counter).LAST_UPDATE_DATE,
1913: x_LAST_UPDATED_BY => P_Terr_Usgs_Tbl(l_Counter).LAST_UPDATED_BY,
1914: x_CREATION_DATE => P_Terr_Usgs_Tbl(l_Counter).CREATION_DATE,
1975: END LOOP;
1976: --
1977: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1978: THEN
1979: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
1980: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
1981: FND_MSG_PUB.Add;
1982: END IF;
1983: --
2065:
2066: -- Debug Message
2067: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2068: THEN
2069: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
2070: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2071: FND_MSG_PUB.Add;
2072: END IF;
2073:
2079:
2080: -- Debug message
2081: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2082: THEN
2083: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
2084: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Qtype_Usage');
2085: FND_MSG_PUB.Add;
2086: END IF;
2087:
2110: RAISE FND_API.G_EXC_ERROR;
2111: END IF;
2112:
2113: END IF;
2114: --dbms_output('Create_Terr_QualType_Usage PVT(REC): Before Calling JTF_TERR_QTYPE_USGS_PKG.Insert_Row');
2115:
2116: /* Intialise to NULL if FND_API.G_MISS_NUM,
2117: ** otherwise used passed in value
2118: */
2121: END IF;
2122:
2123:
2124: -- Call insert terr_Qual_Type_Usgs table handler
2125: JTF_TERR_QTYPE_USGS_PKG.Insert_Row(x_Rowid => l_rowid,
2126: x_TERR_QTYPE_USG_ID => l_TERR_QUAL_TYPE_USG_ID,
2127: x_LAST_UPDATED_BY => P_Terr_QualTypeUsgs_Rec.LAST_UPDATED_BY,
2128: x_LAST_UPDATE_DATE => P_Terr_QualTypeUsgs_Rec.LAST_UPDATE_DATE,
2129: x_CREATED_BY => P_Terr_QualTypeUsgs_Rec.CREATED_BY,
2142: X_Terr_QualTypeUsgs_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
2143:
2144: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2145: THEN
2146: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2147: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2148: FND_MSG_PUB.Add;
2149: END IF;
2150:
2271:
2272: -- Debug Message
2273: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2274: THEN
2275: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
2276: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2277: FND_MSG_PUB.Add;
2278: END IF;
2279:
2332: END LOOP;
2333:
2334: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2335: THEN
2336: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2337: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2338: FND_MSG_PUB.Add;
2339: END IF;
2340:
2360: */
2361: PROCEDURE update_terr_num_qual(p_terr_id IN NUMBER, p_qual_type_id IN NUMBER) AS
2362: BEGIN
2363:
2364: UPDATE jtf_terr_ALL jt
2365: SET jt.num_qual = (
2366: SELECT COUNT(jtq.qual_usg_id)
2367: FROM jtf_terr_qual_ALL jtq, jtf_qual_usgs_ALL jqu, jtf_qual_type_usgs_ALL jqtu
2368: WHERE jtq.terr_id = jt.terr_id
2363:
2364: UPDATE jtf_terr_ALL jt
2365: SET jt.num_qual = (
2366: SELECT COUNT(jtq.qual_usg_id)
2367: FROM jtf_terr_qual_ALL jtq, jtf_qual_usgs_ALL jqu, jtf_qual_type_usgs_ALL jqtu
2368: WHERE jtq.terr_id = jt.terr_id
2369: AND jtq.qual_usg_id = jqu.qual_usg_id
2370: AND jqu.qual_type_usg_id = jqtu.qual_type_usg_id
2371: AND jqtu.qual_type_id = p_qual_type_id
2450:
2451: -- Debug Message
2452: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2453: THEN
2454: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
2455: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2456: FND_MSG_PUB.Add;
2457: END IF;
2458:
2464:
2465: -- Debug message
2466: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2467: THEN
2468: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
2469: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Qualifier');
2470: FND_MSG_PUB.Add;
2471: END IF;
2472: -- Check for ORG_ID
2472: -- Check for ORG_ID
2473: IF (P_Terr_Qual_Rec.ORG_ID is NULL OR
2474: P_Terr_Qual_Rec.ORG_ID = FND_API.G_MISS_NUM ) THEN
2475: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2476: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
2477: FND_MESSAGE.Set_Token('COL_NAME', 'ORG_ID' );
2478: FND_MSG_PUB.ADD;
2479: END IF;
2480: x_Return_Status := FND_API.G_RET_STS_ERROR ;
2483: --Check created by
2484: IF ( P_Terr_Qual_Rec.CREATED_BY is NULL OR
2485: P_Terr_Qual_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
2486: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2487: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
2488: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
2489: FND_MSG_PUB.ADD;
2490: END IF;
2491: x_Return_Status := FND_API.G_RET_STS_ERROR ;
2494: --Check creation date
2495: If ( P_Terr_Qual_Rec.CREATION_DATE is NULL OR
2496: P_Terr_Qual_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
2497: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2498: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
2499: FND_MESSAGE.Set_Token('COL_NAME', 'ORG_ID' );
2500: FND_MSG_PUB.ADD;
2501: END IF;
2502: x_Return_Status := FND_API.G_RET_STS_ERROR ;
2525: l_terr_qual_id := NULL;
2526: END IF;
2527:
2528: -- Call insert terr_Qual_Type_Usgs table handler
2529: JTF_TERR_QUAL_PKG.Insert_Row(x_Rowid => l_rowid,
2530: x_TERR_QUAL_ID => l_terr_qual_id,
2531: x_LAST_UPDATE_DATE => P_Terr_Qual_Rec.LAST_UPDATE_DATE,
2532: x_LAST_UPDATED_BY => P_Terr_Qual_Rec.LAST_UPDATED_BY,
2533: x_CREATION_DATE => P_Terr_Qual_Rec.CREATION_DATE,
2557:
2558: --
2559: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2560: THEN
2561: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2562: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2563: FND_MSG_PUB.Add;
2564: END IF;
2565:
2691:
2692: -- Debug Message
2693: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2694: THEN
2695: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
2696: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2697: FND_MSG_PUB.Add;
2698: END IF;
2699:
2752: END LOOP;
2753: --
2754: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2755: THEN
2756: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2757: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2758: FND_MSG_PUB.Add;
2759: END IF;
2760:
2847: END IF;
2848:
2849: -- Debug Message
2850: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2851: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
2852: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
2853: FND_MSG_PUB.ADD;
2854: END IF;
2855: -- Initialize API return status to success
2857: --
2858: --Check whether the territory values are specified
2859: IF (p_Terr_Values_Tbl.COUNT = 0 ) THEN
2860: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2861: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERR_VALUES');
2862: FND_MSG_PUB.ADD;
2863: END IF;
2864: x_return_status := FND_API.G_RET_STS_ERROR;
2865: RAISE FND_API.G_EXC_ERROR;
2867:
2868: -- Terr ID can't be null.
2869: IF (P_Terr_Qual_Rec.TERR_ID IS NULL) OR (P_Terr_Qual_Rec.TERR_ID = FND_API.G_MISS_NUM) THEN
2870: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2871: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
2872: FND_MESSAGE.Set_Token('COL_NAME', 'TERR_ID' );
2873: FND_MSG_PUB.ADD;
2874: END IF;
2875: x_Return_Status := FND_API.G_RET_STS_ERROR ;
2878:
2879: -- Validate the territory Id
2880: If ( P_Terr_Qual_Rec.TERR_ID IS NOT NULL )AND (P_Terr_Qual_Rec.TERR_ID = FND_API.G_MISS_NUM ) Then
2881: --dbms_output('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
2882: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(P_Terr_Qual_Rec.TERR_ID, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
2883: --dbms_output('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
2884: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2885: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
2886: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_ALL');
2881: --dbms_output('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
2882: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(P_Terr_Qual_Rec.TERR_ID, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
2883: --dbms_output('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
2884: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2885: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
2886: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_ALL');
2887: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
2888: FND_MSG_PUB.ADD;
2889: END IF;
2882: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(P_Terr_Qual_Rec.TERR_ID, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
2883: --dbms_output('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
2884: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2885: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
2886: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_ALL');
2887: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
2888: FND_MSG_PUB.ADD;
2889: END IF;
2890: x_Return_Status := FND_API.G_RET_STS_ERROR ;
2944: x_return_status := FND_API.G_RET_STS_SUCCESS;
2945:
2946: -- Debug Message
2947: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2948: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2949: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
2950: FND_MSG_PUB.ADD;
2951: END IF;
2952: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
3048:
3049: -- Debug Message
3050: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3051: THEN
3052: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
3053: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
3054: FND_MSG_PUB.Add;
3055: END IF;
3056:
3061: IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
3062: -- Debug message
3063: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3064: THEN
3065: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
3066: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_terr_Value_Rec');
3067: FND_MSG_PUB.Add;
3068: END IF;
3069:
3071: --Check created by
3072: IF ( p_Terr_Value_Rec.CREATED_BY is NULL OR
3073: p_Terr_Value_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
3074: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3075: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3076: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
3077: FND_MSG_PUB.ADD;
3078: END IF;
3079: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3082: --Check creation date
3083: If ( p_Terr_Value_Rec.CREATION_DATE is NULL OR
3084: p_Terr_Value_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
3085: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3086: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3087: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
3088: FND_MSG_PUB.ADD;
3089: END IF;
3090: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3125: IF (l_terr_value_id = FND_API.G_MISS_NUM) THEN
3126: l_terr_value_id := NULL;
3127: END IF;
3128: --
3129: --dbms_output('Create_Terr_Value PVT: Before Calling JTF_TERR_VALUES_PKG.Insert_Row');
3130: JTF_TERR_VALUES_PKG.Insert_Row(x_Rowid => l_rowid,
3131: x_TERR_VALUE_ID => l_terr_value_id,
3132: x_LAST_UPDATED_BY => P_Terr_Value_Rec.LAST_UPDATED_BY,
3133: x_LAST_UPDATE_DATE => P_Terr_Value_Rec.LAST_UPDATE_DATE,
3126: l_terr_value_id := NULL;
3127: END IF;
3128: --
3129: --dbms_output('Create_Terr_Value PVT: Before Calling JTF_TERR_VALUES_PKG.Insert_Row');
3130: JTF_TERR_VALUES_PKG.Insert_Row(x_Rowid => l_rowid,
3131: x_TERR_VALUE_ID => l_terr_value_id,
3132: x_LAST_UPDATED_BY => P_Terr_Value_Rec.LAST_UPDATED_BY,
3133: x_LAST_UPDATE_DATE => P_Terr_Value_Rec.LAST_UPDATE_DATE,
3134: x_CREATED_BY => P_Terr_Value_Rec.CREATED_BY,
3165:
3166: --
3167: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3168: THEN
3169: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
3170: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
3171: FND_MSG_PUB.Add;
3172: END IF;
3173:
3301:
3302: -- Debug Message
3303: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3304: THEN
3305: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
3306: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
3307: FND_MSG_PUB.Add;
3308: END IF;
3309:
3365: --
3366: -- Debug Message
3367: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3368: THEN
3369: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
3370: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
3371: FND_MSG_PUB.Add;
3372: END IF;
3373:
3470: ATTRIBUTE15,
3471: ORG_ID,
3472: NUM_WINNERS,
3473: NUM_QUAL
3474: From JTF_TERR_ALL
3475: Where TERR_ID = l_terr_id
3476: For Update NOWAIT;
3477:
3478: --Local variable declaration
3486:
3487: -- Debug Message
3488: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3489: THEN
3490: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
3491: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3492: FND_MSG_PUB.Add;
3493: END IF;
3494: -- Initialize API return status to SUCCESS
3498: THEN
3499: -- Debug message
3500: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3501: THEN
3502: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
3503: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_TerrRec_Update');
3504: FND_MSG_PUB.Add;
3505: END IF;
3506: --
3571: If ( C_GetTerritory%NOTFOUND) Then
3572: --dbms_output('Update_territory_Record: NO-RCORDS-FOUND');
3573: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3574: THEN
3575: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
3576: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
3577: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(p_terr_all_rec.terr_id));
3578: FND_MSG_PUB.Add;
3579: END IF;
3572: --dbms_output('Update_territory_Record: NO-RCORDS-FOUND');
3573: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3574: THEN
3575: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
3576: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
3577: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(p_terr_all_rec.terr_id));
3578: FND_MSG_PUB.Add;
3579: END IF;
3580: raise FND_API.G_EXC_ERROR;
3580: raise FND_API.G_EXC_ERROR;
3581: End if;
3582: CLOSE C_GetTerritory;
3583:
3584: --dbms_output('Update_territory_Record: Before Calling JTF_TERR_PKG.Update_Row');
3585: JTF_TERR_PKG.Update_Row(x_rowid => l_rowid,
3586: x_terr_id => p_terr_all_rec.terr_id,
3587: x_last_update_date => p_terr_all_rec.LAST_UPDATE_DATE,
3588: x_last_updated_by => p_terr_all_rec.LAST_UPDATED_BY,
3581: End if;
3582: CLOSE C_GetTerritory;
3583:
3584: --dbms_output('Update_territory_Record: Before Calling JTF_TERR_PKG.Update_Row');
3585: JTF_TERR_PKG.Update_Row(x_rowid => l_rowid,
3586: x_terr_id => p_terr_all_rec.terr_id,
3587: x_last_update_date => p_terr_all_rec.LAST_UPDATE_DATE,
3588: x_last_updated_by => p_terr_all_rec.LAST_UPDATED_BY,
3589: x_creation_date => p_terr_all_rec.CREATION_DATE,
3637:
3638: -- Debug Message
3639: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3640: THEN
3641: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
3642: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3643: FND_MSG_PUB.Add;
3644: END IF;
3645:
3711: CREATED_BY,
3712: LAST_UPDATE_LOGIN,
3713: TERR_ID,
3714: SOURCE_ID
3715: From JTF_TERR_USGS_ALL
3716: Where terr_usg_id = l_terr_usg_id
3717: FOR Update NOWAIT;
3718: --Local variable declaration
3719: l_api_name CONSTANT VARCHAR2(30) := 'Update_territory_Usages';
3724: BEGIN
3725: -- Debug Message
3726: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3727: THEN
3728: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
3729: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3730: FND_MSG_PUB.Add;
3731: END IF;
3732:
3737: THEN
3738: -- Debug message
3739: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3740: THEN
3741: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
3742: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Territory_Usage');
3743: FND_MSG_PUB.Add;
3744: END IF;
3745: --
3775: If ( C_GetTerritoryUsage%NOTFOUND) Then
3776: --dbms_output('Update_Territory_Usages PVT: NO-RCORDS-FOUND');
3777: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3778: THEN
3779: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
3780: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
3781: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Usgs_Rec.terr_usg_id));
3782: FND_MSG_PUB.Add;
3783: END IF;
3776: --dbms_output('Update_Territory_Usages PVT: NO-RCORDS-FOUND');
3777: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3778: THEN
3779: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
3780: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
3781: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Usgs_Rec.terr_usg_id));
3782: FND_MSG_PUB.Add;
3783: END IF;
3784: raise FND_API.G_EXC_ERROR;
3784: raise FND_API.G_EXC_ERROR;
3785: End if;
3786: CLOSE C_GetTerritoryUsage;
3787:
3788: --dbms_output('Update_Territory_Usages PVT: Before Calling JTF_TERR_USGS_PKG.Update_Row');
3789: -- Call insert terr_Qual_Type_Usgs table handler
3790: JTF_TERR_USGS_PKG.Update_Row(x_Rowid => l_rowid,
3791: x_TERR_USG_ID => P_Terr_Usgs_Rec.terr_usg_id,
3792: x_LAST_UPDATE_DATE => P_Terr_Usgs_Rec.LAST_UPDATE_DATE,
3786: CLOSE C_GetTerritoryUsage;
3787:
3788: --dbms_output('Update_Territory_Usages PVT: Before Calling JTF_TERR_USGS_PKG.Update_Row');
3789: -- Call insert terr_Qual_Type_Usgs table handler
3790: JTF_TERR_USGS_PKG.Update_Row(x_Rowid => l_rowid,
3791: x_TERR_USG_ID => P_Terr_Usgs_Rec.terr_usg_id,
3792: x_LAST_UPDATE_DATE => P_Terr_Usgs_Rec.LAST_UPDATE_DATE,
3793: x_LAST_UPDATED_BY => P_Terr_Usgs_Rec.LAST_UPDATED_BY,
3794: x_CREATION_DATE => P_Terr_Usgs_Rec.CREATION_DATE,
3803:
3804: -- Debug Message
3805: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3806: THEN
3807: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
3808: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3809: FND_MSG_PUB.Add;
3810: END IF;
3811:
3881:
3882: -- Debug Message
3883: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3884: THEN
3885: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
3886: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3887: FND_MSG_PUB.Add;
3888: END IF;
3889:
3941:
3942: -- Debug Message
3943: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3944: THEN
3945: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
3946: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
3947: FND_MSG_PUB.Add;
3948: END IF;
3949: --dbms_output('Update_Territory_Usages TBL: Exiting API');
3993: CREATION_DATE,
3994: LAST_UPDATE_LOGIN,
3995: TERR_ID,
3996: QUAL_TYPE_USG_ID
3997: From JTF_TERR_QTYPE_USGS_ALL
3998: Where terr_qtype_usg_id = l_terr_qual_type_usg_id
3999: FOR Update NOWAIT;
4000: --Local variable declaration
4001: l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_QualType_Usage';
4008:
4009: -- Debug Message
4010: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4011: THEN
4012: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4013: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4014: FND_MSG_PUB.Add;
4015: END IF;
4016:
4021: THEN
4022: -- Debug message
4023: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4024: THEN
4025: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
4026: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Qtype_Usage');
4027: FND_MSG_PUB.Add;
4028: END IF;
4029: -- Invoke validation procedures
4054: If (C_GetTerrQualTypeUsgs%NOTFOUND) Then
4055: --dbms_output('Update_Terr_QualType_Usage REC: NO-RCORDS-FOUND');
4056: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4057: THEN
4058: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
4059: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
4060: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_QualTypeUsgs_Rec.TERR_QUAL_TYPE_USG_ID));
4061: FND_MSG_PUB.Add;
4062: END IF;
4055: --dbms_output('Update_Terr_QualType_Usage REC: NO-RCORDS-FOUND');
4056: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4057: THEN
4058: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
4059: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
4060: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_QualTypeUsgs_Rec.TERR_QUAL_TYPE_USG_ID));
4061: FND_MSG_PUB.Add;
4062: END IF;
4063: raise FND_API.G_EXC_ERROR;
4063: raise FND_API.G_EXC_ERROR;
4064: End if;
4065: CLOSE C_GetTerrQualTypeUsgs;
4066:
4067: --dbms_output('Update_Terr_QualType_Usage REC: Before Calling JTF_TERR_QTYPE_USGS_PKG.Update_Row');
4068: JTF_TERR_QTYPE_USGS_PKG.Update_Row(x_Rowid => l_rowid,
4069: x_TERR_QTYPE_USG_ID => P_Terr_QualTypeUsgs_Rec.TERR_QUAL_TYPE_USG_ID,
4070: x_LAST_UPDATED_BY => P_Terr_QualTypeUsgs_Rec.LAST_UPDATED_BY,
4071: x_LAST_UPDATE_DATE => P_Terr_QualTypeUsgs_Rec.LAST_UPDATE_DATE,
4064: End if;
4065: CLOSE C_GetTerrQualTypeUsgs;
4066:
4067: --dbms_output('Update_Terr_QualType_Usage REC: Before Calling JTF_TERR_QTYPE_USGS_PKG.Update_Row');
4068: JTF_TERR_QTYPE_USGS_PKG.Update_Row(x_Rowid => l_rowid,
4069: x_TERR_QTYPE_USG_ID => P_Terr_QualTypeUsgs_Rec.TERR_QUAL_TYPE_USG_ID,
4070: x_LAST_UPDATED_BY => P_Terr_QualTypeUsgs_Rec.LAST_UPDATED_BY,
4071: x_LAST_UPDATE_DATE => P_Terr_QualTypeUsgs_Rec.LAST_UPDATE_DATE,
4072: x_CREATED_BY => P_Terr_QualTypeUsgs_Rec.CREATED_BY,
4080: X_Terr_QualTypeUsgs_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
4081:
4082: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4083: THEN
4084: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
4085: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4086: FND_MSG_PUB.Add;
4087: END IF;
4088:
4157:
4158: -- Debug Message
4159: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4160: THEN
4161: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4162: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4163: FND_MSG_PUB.Add;
4164: END IF;
4165:
4215:
4216: -- Debug Message
4217: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4218: THEN
4219: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
4220: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4221: FND_MSG_PUB.Add;
4222: END IF;
4223:
4270: QUAL_USG_ID,
4271: USE_TO_NAME_FLAG,
4272: GENERATE_FLAG,
4273: OVERLAP_ALLOWED_FLAG
4274: From JTF_TERR_QUAL_ALL
4275: Where terr_qual_id = l_terr_qual_id
4276: FOR Update NOWAIT;
4277: --Local variable declaration
4278: l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Qualifier';
4285:
4286: -- Debug Message
4287: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4288: THEN
4289: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4290: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4291: FND_MSG_PUB.Add;
4292: END IF;
4293:
4298: THEN
4299: -- Debug message
4300: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4301: THEN
4302: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
4303: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Qualifier');
4304: FND_MSG_PUB.Add;
4305: END IF;
4306: --
4337: If ( C_GetTerrQualifier%NOTFOUND) Then
4338: --dbms_output('Update_Terr_Qualifier REC: NO-RCORDS-FOUND');
4339: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4340: THEN
4341: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
4342: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QUAL');
4343: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Qual_Rec.terr_qual_id));
4344: FND_MSG_PUB.Add;
4345: END IF;
4338: --dbms_output('Update_Terr_Qualifier REC: NO-RCORDS-FOUND');
4339: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4340: THEN
4341: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
4342: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QUAL');
4343: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Qual_Rec.terr_qual_id));
4344: FND_MSG_PUB.Add;
4345: END IF;
4346: raise FND_API.G_EXC_ERROR;
4346: raise FND_API.G_EXC_ERROR;
4347: End if;
4348: CLOSE C_GetTerrQualifier;
4349: -- P_Terr_Qual_Rec.terr_qual_id cant be null
4350: --dbms_output('Update_Terr_Qualifier REC: Before Calling JTF_TERR_QUAL_PKG.Update_Row');
4351: JTF_TERR_QUAL_PKG.Update_Row(x_Rowid => l_rowid,
4352: x_TERR_QUAL_ID => P_Terr_Qual_Rec.terr_qual_id,
4353: x_LAST_UPDATE_DATE => P_Terr_Qual_Rec.LAST_UPDATE_DATE,
4354: x_LAST_UPDATED_BY => P_Terr_Qual_Rec.LAST_UPDATED_BY,
4347: End if;
4348: CLOSE C_GetTerrQualifier;
4349: -- P_Terr_Qual_Rec.terr_qual_id cant be null
4350: --dbms_output('Update_Terr_Qualifier REC: Before Calling JTF_TERR_QUAL_PKG.Update_Row');
4351: JTF_TERR_QUAL_PKG.Update_Row(x_Rowid => l_rowid,
4352: x_TERR_QUAL_ID => P_Terr_Qual_Rec.terr_qual_id,
4353: x_LAST_UPDATE_DATE => P_Terr_Qual_Rec.LAST_UPDATE_DATE,
4354: x_LAST_UPDATED_BY => P_Terr_Qual_Rec.LAST_UPDATED_BY,
4355: x_CREATION_DATE => P_Terr_Qual_Rec.creation_date,
4370:
4371: -- Debug Message
4372: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4373: THEN
4374: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4375: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4376: FND_MSG_PUB.Add;
4377: END IF;
4378:
4444:
4445: -- Debug Message
4446: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4447: THEN
4448: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4449: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4450: FND_MSG_PUB.Add;
4451: END IF;
4452:
4499:
4500: -- Debug Message
4501: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4502: THEN
4503: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
4504: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4505: FND_MSG_PUB.Add;
4506: END IF;
4507: --dbms_output('Update_Terr_Qualifier TBL: Exiting API');
4565: ID_USED_FLAG,
4566: LOW_VALUE_CHAR_ID,
4567: ORG_ID,
4568: CNR_GROUP_ID
4569: From JTF_TERR_VALUES_ALL
4570: Where TERR_VALUE_ID = l_TERR_VALUE_ID
4571: For Update NOWAIT;
4572:
4573: Cursor C_GetTerr_qual_id(l_TERR_VALUE_ID Number) IS
4571: For Update NOWAIT;
4572:
4573: Cursor C_GetTerr_qual_id(l_TERR_VALUE_ID Number) IS
4574: Select TERR_QUAL_ID
4575: From JTF_TERR_VALUES_ALL
4576: Where TERR_VALUE_ID = l_TERR_VALUE_ID ;
4577:
4578: --Local variable declaration
4579: l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Value';
4586:
4587: -- Debug Message
4588: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4589: THEN
4590: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4591: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4592: FND_MSG_PUB.Add;
4593: END IF;
4594:
4605: --
4606: --dbms_output('Update_Terr_Value REC: NO-RCORDS-FOUND');
4607: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4608: THEN
4609: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
4610: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_VALUES');
4611: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Value_Rec.TERR_VALUE_ID));
4612: FND_MSG_PUB.Add;
4613: END IF;
4606: --dbms_output('Update_Terr_Value REC: NO-RCORDS-FOUND');
4607: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4608: THEN
4609: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
4610: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_VALUES');
4611: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Value_Rec.TERR_VALUE_ID));
4612: FND_MSG_PUB.Add;
4613: END IF;
4614: raise FND_API.G_EXC_ERROR;
4621: THEN
4622: -- Debug message
4623: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4624: THEN
4625: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
4626: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_terr_Value_Rec');
4627: FND_MSG_PUB.Add;
4628: END IF;
4629:
4686: --
4687: --dbms_output('Update_Terr_Value REC: NO-RCORDS-FOUND');
4688: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4689: THEN
4690: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
4691: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_VALUES');
4692: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Value_Rec.TERR_VALUE_ID));
4693: FND_MSG_PUB.Add;
4694: END IF;
4687: --dbms_output('Update_Terr_Value REC: NO-RCORDS-FOUND');
4688: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4689: THEN
4690: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
4691: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_VALUES');
4692: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Value_Rec.TERR_VALUE_ID));
4693: FND_MSG_PUB.Add;
4694: END IF;
4695: raise FND_API.G_EXC_ERROR;
4695: raise FND_API.G_EXC_ERROR;
4696:
4697: End if;
4698: CLOSE C_GetTerritoryValue;
4699: --dbms_output('Update_Terr_Value REC: Before Calling JTF_TERR_VALUES_PKG.Update_Row');
4700: --call the table handler
4701: JTF_TERR_VALUES_PKG.Update_Row(x_Rowid => l_rowid,
4702: x_TERR_VALUE_ID => P_Terr_Value_Rec.TERR_VALUE_ID,
4703: x_LAST_UPDATED_BY => P_Terr_Value_Rec.LAST_UPDATED_BY,
4697: End if;
4698: CLOSE C_GetTerritoryValue;
4699: --dbms_output('Update_Terr_Value REC: Before Calling JTF_TERR_VALUES_PKG.Update_Row');
4700: --call the table handler
4701: JTF_TERR_VALUES_PKG.Update_Row(x_Rowid => l_rowid,
4702: x_TERR_VALUE_ID => P_Terr_Value_Rec.TERR_VALUE_ID,
4703: x_LAST_UPDATED_BY => P_Terr_Value_Rec.LAST_UPDATED_BY,
4704: x_LAST_UPDATE_DATE => P_Terr_Value_Rec.LAST_UPDATE_DATE,
4705: x_CREATED_BY => P_Terr_Value_Rec.created_by,
4732:
4733: -- Debug Message
4734: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4735: THEN
4736: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
4737: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4738: FND_MSG_PUB.Add;
4739: END IF;
4740:
4811:
4812: -- Debug Message
4813: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4814: THEN
4815: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4816: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4817: FND_MSG_PUB.Add;
4818: END IF;
4819:
4868:
4869: -- Debug Message
4870: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4871: THEN
4872: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
4873: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4874: FND_MSG_PUB.Add;
4875: END IF;
4876:
4938:
4939: -- Debug Message
4940: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4941: THEN
4942: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4943: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4944: FND_MSG_PUB.Add;
4945: END IF;
4946:
4948: x_return_status := FND_API.G_RET_STS_SUCCESS;
4949: --
4950: --dbms_output('Delete_territory_Record: Before Calling Create_TerrType_Qualifier TBL');
4951: --
4952: JTF_TERR_PKG.Delete_Row(x_terr_Id => P_Terr_Id);
4953: --
4954: --Prepare message name
4955: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
4956:
4951: --
4952: JTF_TERR_PKG.Delete_Row(x_terr_Id => P_Terr_Id);
4953: --
4954: --Prepare message name
4955: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
4956:
4957: IF SQL%FOUND THEN
4958: --dbms_output('Delete_territory_Record: # Records deleted -' || to_char(SQL%ROWCOUNT));
4959: x_return_status := FND_API.G_RET_STS_SUCCESS;
4960: l_row_count := SQL%ROWCOUNT;
4961: END IF;
4962: --prepare the message
4963: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
4964: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR');
4965: FND_MSG_PUB.ADD;
4966:
4967: -- Debug Message
4968: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4966:
4967: -- Debug Message
4968: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4969: THEN
4970: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
4971: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
4972: FND_MSG_PUB.Add;
4973: END IF;
4974:
4990: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4991: l_row_count := 0;
4992: --Prepare message token
4993: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
4994: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR');
4995: FND_MSG_PUB.ADD;
4996: --
4997: FND_MSG_PUB.Count_And_Get
4998: ( p_count => x_msg_count,
5074:
5075: -- Debug Message
5076: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5077: THEN
5078: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
5079: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
5080: FND_MSG_PUB.Add;
5081: END IF;
5082:
5084: x_return_status := FND_API.G_RET_STS_SUCCESS;
5085: --
5086: --dbms_output('Delete_Territory_Usages: Before Calling Create_TerrType_Qualifier TBL');
5087: --
5088: JTF_TERR_USGS_PKG.Delete_Row(x_TERR_USG_ID => P_Terr_usg_Id);
5089: --
5090: --Prepare message name
5091: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
5092: IF SQL%FOUND THEN
5087: --
5088: JTF_TERR_USGS_PKG.Delete_Row(x_TERR_USG_ID => P_Terr_usg_Id);
5089: --
5090: --Prepare message name
5091: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
5092: IF SQL%FOUND THEN
5093: --dbms_output('Delete_Territory_Usages: # Records deleted -' || to_char(SQL%ROWCOUNT));
5094: x_return_status := FND_API.G_RET_STS_SUCCESS;
5095: l_row_count := SQL%ROWCOUNT;
5095: l_row_count := SQL%ROWCOUNT;
5096: END IF;
5097: --Prepare message token
5098: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5099: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_USGS');
5100: FND_MSG_PUB.ADD;
5101:
5102: -- Debug Message
5103: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5101:
5102: -- Debug Message
5103: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5104: THEN
5105: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
5106: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
5107: FND_MSG_PUB.Add;
5108: END IF;
5109:
5125: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5126: l_row_count := 0;
5127: --Prepare message token
5128: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5129: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_USGS');
5130: FND_MSG_PUB.ADD;
5131: --
5132: FND_MSG_PUB.Count_And_Get
5133: ( p_count => x_msg_count,
5209:
5210: -- Debug Message
5211: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5212: THEN
5213: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
5214: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
5215: FND_MSG_PUB.Add;
5216: END IF;
5217:
5217:
5218: --Initialize the return status to success
5219: x_return_status := FND_API.G_RET_STS_SUCCESS;
5220:
5221: --dbms_output('Delete_Terr_QualType_Usage: Before Calling JTF_TERR_QTYPE_USGS_PKG.Delete_Row');
5222: --
5223: JTF_TERR_QTYPE_USGS_PKG.Delete_Row(x_terr_qtype_usg_id => P_Terr_Qual_Type_Usg_Id);
5224: --
5225: --
5219: x_return_status := FND_API.G_RET_STS_SUCCESS;
5220:
5221: --dbms_output('Delete_Terr_QualType_Usage: Before Calling JTF_TERR_QTYPE_USGS_PKG.Delete_Row');
5222: --
5223: JTF_TERR_QTYPE_USGS_PKG.Delete_Row(x_terr_qtype_usg_id => P_Terr_Qual_Type_Usg_Id);
5224: --
5225: --
5226: --Prepare message name
5227: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
5223: JTF_TERR_QTYPE_USGS_PKG.Delete_Row(x_terr_qtype_usg_id => P_Terr_Qual_Type_Usg_Id);
5224: --
5225: --
5226: --Prepare message name
5227: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
5228:
5229: IF SQL%FOUND THEN
5230: --dbms_output('Delete_Terr_QualType_Usage: # Records deleted -' || to_char(SQL%ROWCOUNT));
5231: x_return_status := FND_API.G_RET_STS_SUCCESS;
5233: END IF;
5234:
5235: --Prepare message token
5236: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5237: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
5238: FND_MSG_PUB.ADD;
5239:
5240: -- Debug Message
5241: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5239:
5240: -- Debug Message
5241: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5242: THEN
5243: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
5244: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
5245: FND_MSG_PUB.Add;
5246: END IF;
5247:
5265: l_row_count := 0;
5266:
5267: --Prepare message token
5268: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5269: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_QTYPE_USGS');
5270: FND_MSG_PUB.ADD;
5271: --
5272: FND_MSG_PUB.Count_And_Get
5273: ( p_count => x_msg_count,
5357:
5358: -- Debug Message
5359: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5360: THEN
5361: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
5362: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
5363: FND_MSG_PUB.Add;
5364: END IF;
5365:
5372: /* anonymous block to get territory id */
5373: BEGIN
5374: SELECT terr_id
5375: INTO lp_terr_id
5376: FROM jtf_terr_qual_ALL jtq
5377: WHERE jtq.terr_qual_id = p_terr_qual_id;
5378: EXCEPTION
5379: WHEN NO_DATA_FOUND THEN
5380: NULL;
5382: NULL;
5383: END;
5384:
5385: /* Do a bulk delete of Territory Qualifier VALUES */
5386: DELETE jtf_terr_values_ALL
5387: WHERE terr_qual_id = p_terr_qual_id
5388: RETURNING terr_value_id
5389: BULK COLLECT INTO deletedIds;
5390:
5387: WHERE terr_qual_id = p_terr_qual_id
5388: RETURNING terr_value_id
5389: BULK COLLECT INTO deletedIds;
5390:
5391: JTF_TERR_QUAL_PKG.Delete_Row(x_terr_qual_id => P_Terr_Qual_Id );
5392: --
5393: --Prepare message name
5394: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
5395: IF SQL%FOUND THEN
5390:
5391: JTF_TERR_QUAL_PKG.Delete_Row(x_terr_qual_id => P_Terr_Qual_Id );
5392: --
5393: --Prepare message name
5394: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
5395: IF SQL%FOUND THEN
5396: --dbms_output('Delete_Terr_Qualifier: # Records deleted -' || to_char(SQL%ROWCOUNT));
5397: x_return_status := FND_API.G_RET_STS_SUCCESS;
5398: l_row_count := SQL%ROWCOUNT;
5403: -- update_terr_num_qual(lp_terr_id, -1002);
5404:
5405: --Prepare message token
5406: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5407: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_QUALIFIERS');
5408: FND_MSG_PUB.ADD();
5409:
5410: -- Debug Message
5411: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5409:
5410: -- Debug Message
5411: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5412: THEN
5413: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
5414: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
5415: FND_MSG_PUB.Add;
5416: END IF;
5417:
5434: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5435: l_row_count := 0;
5436: --Prepare message token
5437: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5438: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_QUALIFIERS');
5439: --Add message to API message list
5440: FND_MSG_PUB.ADD();
5441: --
5442: FND_MSG_PUB.Count_And_Get
5518:
5519: -- Debug Message
5520: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5521: THEN
5522: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
5523: FND_MESSAGE.Set_Name('PROC_NAME', l_api_name);
5524: FND_MSG_PUB.Add;
5525: END IF;
5526:
5527: --Initialize the return status to success
5528: x_return_status := FND_API.G_RET_STS_SUCCESS;
5529: --
5530:
5531: JTF_TERR_VALUES_PKG.Delete_Row(X_TERR_VALUE_ID => P_Terr_Value_Id);
5532: --
5533: --Prepare message name
5534: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
5535: IF SQL%FOUND THEN
5530:
5531: JTF_TERR_VALUES_PKG.Delete_Row(X_TERR_VALUE_ID => P_Terr_Value_Id);
5532: --
5533: --Prepare message name
5534: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
5535: IF SQL%FOUND THEN
5536: --dbms_output('Delete_Terr_Value: # Records deleted -' || to_char(SQL%ROWCOUNT));
5537: x_return_status := FND_API.G_RET_STS_SUCCESS;
5538: l_row_count := SQL%ROWCOUNT;
5538: l_row_count := SQL%ROWCOUNT;
5539: END IF;
5540: --Prepare message token
5541: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5542: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_VALUES');
5543: FND_MSG_PUB.ADD();
5544:
5545: -- Debug Message
5546: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5544:
5545: -- Debug Message
5546: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5547: THEN
5548: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
5549: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name );
5550: FND_MSG_PUB.Add;
5551: END IF;
5552:
5570: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5571: l_row_count := 0;
5572: --Prepare message token
5573: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5574: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_VALUES');
5575: --Add message to API message list
5576: FND_MSG_PUB.ADD();
5577: --
5578: FND_MSG_PUB.Count_And_Get
5621: --
5622: if (P_Terr_Qual_Rec.Qual_Usg_Id IS NULL) OR
5623: (P_Terr_Qual_Rec.Qual_Usg_Id = FND_API.G_MISS_NUM ) THEN
5624: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5625: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5626: FND_MESSAGE.Set_Token('COL_NAME', 'QUAL_USG_ID' );
5627: FND_MSG_PUB.ADD;
5628: END IF;
5629: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5638: /* SELECT 'x'
5639: into l_Temp
5640: from jtf_qual_usgs_ALL jqu,
5641: jtf_qual_type_usgs_ALL jqtu,
5642: jtf_terr_qtype_usgs_ALL jtqu
5643: where jtqu.terr_id = p_Terr_Id and
5644: jqtu.qual_type_usg_id = jtqu.qual_type_usg_id and
5645: jqu.qual_usg_id = P_Terr_Qual_Rec.Qual_Usg_Id and
5646: jqu.enabled_flag = 'Y' and
5650: AND ROWNUM < 2; */
5651:
5652: SELECT 'x'
5653: INTO l_Temp
5654: FROM jtf_terr_all jta,
5655: jtf_terr_type_qual_all jtqa,
5656: jtf_qual_usgs_all jqua
5657: WHERE jta.terr_id = p_Terr_Id
5658: AND jta.territory_type_id = jtqa.terr_type_id
5651:
5652: SELECT 'x'
5653: INTO l_Temp
5654: FROM jtf_terr_all jta,
5655: jtf_terr_type_qual_all jtqa,
5656: jtf_qual_usgs_all jqua
5657: WHERE jta.terr_id = p_Terr_Id
5658: AND jta.territory_type_id = jtqa.terr_type_id
5659: AND jtqa.qual_usg_id = jqua.qual_usg_id
5665: EXCEPTION
5666: WHEN NO_DATA_FOUND THEN
5667: --dbms_output('Validate_Qualifier: NO_DATA_FOUND Exception');
5668: x_return_status := FND_API.G_RET_STS_ERROR ;
5669: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_DISABLED_TERR_QUAL');
5670: FND_MSG_PUB.ADD;
5671: FND_MSG_PUB.Count_And_Get
5672: ( P_count => x_msg_count,
5673: P_data => x_msg_data
5678: IF ( p_Terr_Id IS NOT NULL ) AND
5679: ( P_Terr_Qual_Rec.Qual_Usg_Id IS NOT NULL ) Then
5680:
5681: SELECT COUNT(*) INTO l_qual_count
5682: FROM JTF_TERR_QUAL_ALL
5683: WHERE TERR_ID = p_Terr_Id
5684: AND QUAL_USG_ID = P_Terr_Qual_Rec.Qual_Usg_Id ;
5685:
5686: IF ( l_qual_count IS NOT NULL ) AND
5696: /* -- Check for ORG_ID - obsolete: org_id is optional
5697: IF (P_Terr_Qual_Rec.ORG_ID is NULL OR
5698: P_Terr_Qual_Rec.ORG_ID = FND_API.G_MISS_NUM ) THEN
5699: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5700: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5701: FND_MESSAGE.Set_Token('COL_NAME', 'ORG_ID' );
5702: FND_MSG_PUB.ADD;
5703: END IF;
5704: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5708: -- Validate last updated by
5709: IF ( P_Terr_Qual_Rec.LAST_UPDATED_BY is NULL OR
5710: P_Terr_Qual_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
5711: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5712: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5713: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
5714: FND_MSG_PUB.ADD;
5715: END IF;
5716: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5719: -- Check last update date
5720: If ( P_Terr_Qual_Rec.LAST_UPDATE_DATE IS NULL OR
5721: P_Terr_Qual_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
5722: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5723: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5724: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
5725: FND_MSG_PUB.ADD;
5726: END IF;
5727: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5731: --Check last update login
5732: If ( P_Terr_Qual_Rec.LAST_UPDATE_LOGIN is NULL OR
5733: P_Terr_Qual_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
5734: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5735: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5736: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
5737: FND_MSG_PUB.ADD;
5738: END IF;
5739: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5749: --
5750: WHEN NO_DATA_FOUND THEN
5751: --dbms_output('Validate_Qualifier: NO_DATA_FOUND Exception');
5752: x_return_status := FND_API.G_RET_STS_ERROR ;
5753: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_DISABLED_TERR_QUAL');
5754: FND_MSG_PUB.ADD;
5755: FND_MSG_PUB.Count_And_Get
5756: ( P_count => x_msg_count,
5757: P_data => x_msg_data
5759:
5760: WHEN OTHERS THEN
5761: --dbms_output('Validate_Qualifier: Others Exception');
5762: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5763: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
5764: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Qualifer' );
5765: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
5766: FND_MSG_PUB.ADD;
5767: FND_MSG_PUB.Count_And_Get
5795: -- Check whether the territory Name is specified
5796: --
5797: IF (p_Terr_All_Rec.NAME is NULL) OR (p_Terr_All_Rec.NAME = FND_API.G_MISS_CHAR) THEN
5798: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5799: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5800: FND_MESSAGE.Set_Token('COL_NAME', 'NAME' );
5801: FND_MSG_PUB.ADD;
5802: END IF;
5803: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5807: AND p_Terr_All_Rec.END_DATE_ACTIVE IS NOT NULL
5808: AND p_Terr_All_Rec.PARENT_TERRITORY_ID IS NOT NULL THEN
5809:
5810: Select count(*) INTO l_ter_name_count
5811: FROM JTF_TERR_ALL childterr,
5812: HR_OPERATING_UNITS hr,
5813: JTF_TERR_ALL parentterr
5814: WHERE childterr.org_id = hr.organization_id
5815: AND childterr.parent_territory_id = parentTerr.terr_id
5809:
5810: Select count(*) INTO l_ter_name_count
5811: FROM JTF_TERR_ALL childterr,
5812: HR_OPERATING_UNITS hr,
5813: JTF_TERR_ALL parentterr
5814: WHERE childterr.org_id = hr.organization_id
5815: AND childterr.parent_territory_id = parentTerr.terr_id
5816: AND childterr.org_id = parentTerr.org_id
5817: AND childterr.NAME = p_Terr_All_Rec.NAME
5836: If (p_Terr_All_Rec.PARENT_TERRITORY_ID is null) OR
5837: (p_Terr_All_Rec.PARENT_TERRITORY_ID = FND_API.G_MISS_NUM)
5838: Then
5839: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5840: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5841: FND_MESSAGE.Set_Token('COL_NAME', 'PARENT_TERRITORY_ID' );
5842: FND_MSG_PUB.ADD;
5843: END IF;
5844: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5850: (p_Terr_All_Rec.TERRITORY_TYPE_ID = FND_API.G_MISS_NUM)
5851: then
5852:
5853: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5854: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5855: FND_MESSAGE.Set_Token('COL_NAME', 'TERRITORY_TYPE_ID' );
5856: FND_MSG_PUB.ADD;
5857: END IF;
5858: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5862: --
5863: IF (p_Terr_All_Rec.APPLICATION_SHORT_NAME is NULL) OR
5864: (p_Terr_All_Rec.APPLICATION_SHORT_NAME = FND_API.G_MISS_CHAR) THEN
5865: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5866: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5867: FND_MESSAGE.Set_Token('COL_NAME', 'APPLICATION_SHORT_NAME' );
5868: FND_MSG_PUB.ADD;
5869: END IF;
5870: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5875: -- whether territory is enabled
5876: IF (p_Terr_All_Rec.ENABLED_FLAG is NULL) OR
5877: (p_Terr_All_Rec.ENABLED_FLAG = FND_API.G_MISS_CHAR) THEN
5878: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5879: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5880: FND_MESSAGE.Set_Token('COL_NAME', 'ENABLED_FLAG' );
5881: FND_MSG_PUB.ADD;
5882: END IF;
5883: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5887: -- Check for ORG_ID
5888: IF (p_Terr_All_Rec.ORG_ID is NULL) OR
5889: (p_Terr_All_Rec.ORG_ID = FND_API.G_MISS_NUM) THEN
5890: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5891: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5892: FND_MESSAGE.Set_Token('COL_NAME', 'ORG_ID' );
5893: FND_MSG_PUB.ADD;
5894: END IF;
5895: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5897:
5898: If (p_Terr_All_Rec.ESCALATION_TERRITORY_FLAG = 'Y' and
5899: p_Terr_All_Rec.TEMPLATE_FLAG = 'Y' ) Then
5900: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5901: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_OVERLAPPING_FLAG');
5902: FND_MSG_PUB.ADD;
5903: END IF;
5904: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5905: End If;
5907: --Check created by
5908: IF ( p_Terr_All_Rec.CREATED_BY is NULL OR
5909: p_Terr_All_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
5910: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5911: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5912: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
5913: FND_MSG_PUB.ADD;
5914: END IF;
5915: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5918: --Check creation date
5919: If ( p_Terr_All_Rec.CREATION_DATE is NULL OR
5920: p_Terr_All_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
5921: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5922: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5923: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
5924: FND_MSG_PUB.ADD;
5925: END IF;
5926: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5929: -- Validate last updated by
5930: IF ( p_Terr_All_Rec.LAST_UPDATED_BY is NULL OR
5931: p_Terr_All_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
5932: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5933: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5934: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
5935: FND_MSG_PUB.ADD;
5936: END IF;
5937: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5940: -- Check last update date
5941: If ( p_Terr_All_Rec.LAST_UPDATE_DATE IS NULL OR
5942: p_Terr_All_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
5943: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5944: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5945: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
5946: FND_MSG_PUB.ADD;
5947: END IF;
5948: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5951: --Check last update login
5952: --If ( p_Terr_All_Rec.LAST_UPDATE_LOGIN is NULL OR
5953: -- p_Terr_All_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
5954: -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5955: -- FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5956: -- FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
5957: -- FND_MSG_PUB.ADD;
5958: -- END IF;
5959: -- x_Return_Status := FND_API.G_RET_STS_ERROR ;
5962: --Check start date active
5963: If ( p_Terr_All_Rec.START_DATE_ACTIVE IS NULL OR
5964: p_Terr_All_Rec.START_DATE_ACTIVE = FND_API.G_MISS_DATE ) THEN
5965: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5966: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5967: FND_MESSAGE.Set_Token('COL_NAME', 'START_DATE_ACTIVE' );
5968: FND_MSG_PUB.ADD;
5969: END IF;
5970: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5973: --Check End date active
5974: If ( p_Terr_All_Rec.END_DATE_ACTIVE IS NULL OR
5975: p_Terr_All_Rec.END_DATE_ACTIVE = FND_API.G_MISS_DATE ) THEN
5976: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5977: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5978: FND_MESSAGE.Set_Token('COL_NAME', 'END_DATE_ACTIVE' );
5979: FND_MSG_PUB.ADD;
5980: END IF;
5981: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5984: IF (p_Terr_All_Rec.START_DATE_ACTIVE IS NOT NULL AND p_Terr_All_Rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE )
5985: AND (p_Terr_All_Rec.END_DATE_ACTIVE IS NOT NULL AND p_Terr_All_Rec.END_DATE_ACTIVE <> FND_API.G_MISS_DATE ) THEN
5986: IF ( p_Terr_All_Rec.START_DATE_ACTIVE > p_Terr_All_Rec.END_DATE_ACTIVE ) THEN
5987: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5988: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_END_BAD');
5989: FND_MSG_PUB.ADD;
5990: END IF;
5991: x_Return_Status := FND_API.G_RET_STS_ERROR ;
5992: END IF;
5995: --Check Rank
5996: If ( p_Terr_All_Rec.RANK IS NULL OR
5997: p_Terr_All_Rec.RANK = FND_API.G_MISS_NUM ) THEN
5998: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5999: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6000: FND_MESSAGE.Set_Token('COL_NAME', 'RANK' );
6001: FND_MSG_PUB.ADD;
6002: END IF;
6003: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6065:
6066: WHEN OTHERS THEN
6067: --dbms_output('Validate_Territory_Record: OTHERS - ' || SQLERRM);
6068: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6069: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
6070: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Territory_Record' );
6071: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
6072: FND_MSG_PUB.ADD;
6073: FND_MSG_PUB.Count_And_Get
6104:
6105: -- Validate the territory Id
6106: l_Validate_id := p_Terr_All_Rec.TERR_ID;
6107: If l_Validate_id IS NOT NULL THEN
6108: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
6109: --dbms_output('Validate_Territory_Usage: l_status <> FND_API.G_TRUE');
6110: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
6111: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
6112: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
6106: l_Validate_id := p_Terr_All_Rec.TERR_ID;
6107: If l_Validate_id IS NOT NULL THEN
6108: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
6109: --dbms_output('Validate_Territory_Usage: l_status <> FND_API.G_TRUE');
6110: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
6111: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
6112: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
6113: FND_MSG_PUB.ADD;
6114: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6107: If l_Validate_id IS NOT NULL THEN
6108: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
6109: --dbms_output('Validate_Territory_Usage: l_status <> FND_API.G_TRUE');
6110: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
6111: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
6112: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
6113: FND_MSG_PUB.ADD;
6114: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6115: ELSE
6121: INTO l_ter_name,
6122: l_start_date_active,
6123: l_end_date_active,
6124: l_parent_territory_id
6125: FROM JTF_TERR_ALL
6126: WHERE TERR_ID = p_Terr_All_Rec.TERR_ID;
6127: -- Following values are used in finding the duplicate names.
6128: IF (p_Terr_All_Rec.START_DATE_ACTIVE IS NOT NULL AND p_Terr_All_Rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE ) THEN
6129: l_start_date_active := p_Terr_All_Rec.START_DATE_ACTIVE ;
6149: AND l_end_date_active IS NOT NULL
6150: AND l_parent_territory_id IS NOT NULL THEN
6151:
6152: Select count(*) INTO l_ter_name_count
6153: FROM JTF_TERR_ALL childterr,
6154: HR_OPERATING_UNITS hr,
6155: JTF_TERR_ALL parentterr
6156: WHERE childterr.org_id = hr.organization_id
6157: AND childterr.parent_territory_id = parentTerr.terr_id
6151:
6152: Select count(*) INTO l_ter_name_count
6153: FROM JTF_TERR_ALL childterr,
6154: HR_OPERATING_UNITS hr,
6155: JTF_TERR_ALL parentterr
6156: WHERE childterr.org_id = hr.organization_id
6157: AND childterr.parent_territory_id = parentTerr.terr_id
6158: AND childterr.org_id = parentTerr.org_id
6159: AND childterr.NAME = p_Terr_All_Rec.NAME
6177: IF (l_start_date_active IS NOT NULL )
6178: AND (l_end_date_active IS NOT NULL ) THEN
6179: IF ( l_start_date_active > l_end_date_active ) THEN
6180: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6181: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_END_BAD');
6182: FND_MSG_PUB.ADD;
6183: END IF;
6184: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6185: END IF;
6190: BEGIN
6191:
6192: SELECT jta.start_date_active,jta.end_date_active
6193: INTO l_pterr_start_date,l_pterr_end_date
6194: FROM jtf_terr_all jta
6195: WHERE jta.terr_id = l_parent_territory_id ;
6196:
6197: -- Validate Terr start date .
6198: IF ( l_start_date_active IS NOT NULL ) AND ( ( l_start_date_active < l_pterr_start_date ) OR ( l_start_date_active > l_pterr_end_date ) ) THEN
6196:
6197: -- Validate Terr start date .
6198: IF ( l_start_date_active IS NOT NULL ) AND ( ( l_start_date_active < l_pterr_start_date ) OR ( l_start_date_active > l_pterr_end_date ) ) THEN
6199: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6200: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_STARTDATE_NOT_VALID');
6201: FND_MESSAGE.Set_Token('RES_NAME', ' ' );
6202: FND_MSG_PUB.ADD;
6203: END IF;
6204: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6206:
6207: -- Validate Terr end date.
6208: IF (l_end_date_active IS NOT NULL ) AND (( l_end_date_active < l_pterr_start_date ) OR ( l_end_date_active > l_pterr_end_date ) ) THEN
6209: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6210: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_ENDDATE_NOT_VALID');
6211: FND_MESSAGE.Set_Token('RES_NAME', ' ' );
6212: FND_MSG_PUB.ADD;
6213: END IF;
6214: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6231: AND p_terr_all_rec.PARENT_TERRITORY_ID <> 1) THEN
6232:
6233: SELECT 1
6234: INTO l_dummy
6235: FROM jtf_terr_usgs_all childusg, jtf_terr_usgs_all parusg
6236: WHERE childusg.terr_id = p_terr_all_rec.TERR_ID
6237: AND parusg.terr_id = p_terr_all_rec.PARENT_TERRITORY_ID
6238: AND childusg.source_id = parusg.source_id
6239: AND childusg.org_id = parusg.org_id ;
6241: END IF;
6242:
6243: EXCEPTION
6244: WHEN NO_DATA_FOUND THEN
6245: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_PARENT_TERR');
6246: FND_MESSAGE.Set_Token('TERR_ID', to_char(p_terr_all_rec.PARENT_TERRITORY_ID));
6247: FND_MSG_PUB.ADD;
6248: END;
6249:
6249:
6250: If (p_Terr_All_Rec.ESCALATION_TERRITORY_FLAG = 'Y' and
6251: p_Terr_All_Rec.TEMPLATE_FLAG = 'Y' ) Then
6252: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6253: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_OVERLAPPING_FLAG');
6254: FND_MSG_PUB.ADD;
6255: END IF;
6256: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6257: End If;
6259: -- Validate last updated by
6260: IF ( p_Terr_All_Rec.LAST_UPDATED_BY is NULL OR
6261: p_Terr_All_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
6262: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6263: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6264: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
6265: FND_MSG_PUB.ADD;
6266: END IF;
6267: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6270: -- Check last update date
6271: If ( p_Terr_All_Rec.LAST_UPDATE_DATE IS NULL OR
6272: p_Terr_All_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
6273: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6274: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6275: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
6276: FND_MSG_PUB.ADD;
6277: END IF;
6278: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6281: --Check last update login
6282: If ( p_Terr_All_Rec.LAST_UPDATE_LOGIN is NULL OR
6283: p_Terr_All_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
6284: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6285: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6286: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
6287: FND_MSG_PUB.ADD;
6288: END IF;
6289: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6325:
6326: WHEN OTHERS THEN
6327: --dbms_output('Validate_Territory_Record: OTHERS - ' || SQLERRM);
6328: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6329: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
6330: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_TerrRec_Update' );
6331: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
6332: FND_MSG_PUB.ADD;
6333: FND_MSG_PUB.Count_And_Get
6362: Then
6363: --
6364: l_parent_terr_id := p_Terr_All_Rec.parent_territory_Id;
6365: --dbms_output('Validate_Foreign_Key: Returned from JTF_CTM_UTILITY_PVT.fk_id_is_valid');
6366: IF JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_parent_terr_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
6367: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6368: --dbms_output('PARENT Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
6369: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_PARENT_TERR');
6370: FND_MESSAGE.Set_Token('TERR_ID', to_char(l_parent_terr_id));
6365: --dbms_output('Validate_Foreign_Key: Returned from JTF_CTM_UTILITY_PVT.fk_id_is_valid');
6366: IF JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_parent_terr_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
6367: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6368: --dbms_output('PARENT Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
6369: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_PARENT_TERR');
6370: FND_MESSAGE.Set_Token('TERR_ID', to_char(l_parent_terr_id));
6371: FND_MSG_PUB.ADD;
6372: End If;
6373: x_return_status := FND_API.G_RET_STS_ERROR;
6377: IF (p_Terr_All_Rec.START_DATE_ACTIVE IS NOT NULL
6378: AND p_Terr_All_Rec.END_DATE_ACTIVE IS NOT NULL AND p_Terr_All_Rec.PARENT_TERRITORY_ID <> 1 ) THEN
6379:
6380: SELECT START_DATE_ACTIVE, END_DATE_ACTIVE INTO PSTART_DATE_ACTIVE, PEND_DATE_ACTIVE
6381: FROM JTF_TERR_ALL
6382: WHERE TERR_ID = p_Terr_All_Rec.PARENT_TERRITORY_ID;
6383:
6384: IF PSTART_DATE_ACTIVE is NOT NULL
6385: AND PSTART_DATE_ACTIVE > p_Terr_All_Rec.START_DATE_ACTIVE THEN
6383:
6384: IF PSTART_DATE_ACTIVE is NOT NULL
6385: AND PSTART_DATE_ACTIVE > p_Terr_All_Rec.START_DATE_ACTIVE THEN
6386: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6387: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_STARTDATE_NOT_VALID');
6388: FND_MSG_PUB.ADD;
6389: END IF;
6390: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6391: END IF;
6392:
6393: IF PEND_DATE_ACTIVE is NOT NULL
6394: AND PEND_DATE_ACTIVE < p_Terr_All_Rec.END_DATE_ACTIVE THEN
6395: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6396: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_ENDDATE_NOT_VALID');
6397: FND_MSG_PUB.ADD;
6398: END IF;
6399: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6400: END IF;
6407: --
6408: If (p_Terr_All_Rec.TERRITORY_TYPE_ID is not null) AND
6409: (p_Terr_All_Rec.TERRITORY_TYPE_ID <> FND_API.G_MISS_NUM)
6410: then
6411: l_status := JTF_CTM_UTILITY_PVT.fk_id_is_valid(p_Terr_All_Rec.TERRITORY_TYPE_ID, 'TERR_TYPE_ID', 'JTF_TERR_TYPES_ALL');
6412: If l_status <> FND_API.G_TRUE Then
6413: x_return_status := FND_API.G_RET_STS_ERROR;
6414: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6415: --dbms_output('TERR TYPE: Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
6412: If l_status <> FND_API.G_TRUE Then
6413: x_return_status := FND_API.G_RET_STS_ERROR;
6414: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6415: --dbms_output('TERR TYPE: Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
6416: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERR_TYPE');
6417: FND_MESSAGE.Set_Token('TERR_TYPE_ID', to_char(p_Terr_All_Rec.TERRITORY_TYPE_ID));
6418: FND_MSG_PUB.ADD;
6419: End If;
6420: End If;
6441: --dbms_output('Validate_Foreign_Key: Checking for TEMPLATE_TERRITORY_ID');
6442: --
6443: iF (p_Terr_All_Rec.TEMPLATE_TERRITORY_ID is not null) AND
6444: (p_Terr_All_Rec.TEMPLATE_TERRITORY_ID <> FND_API.G_MISS_NUM) Then
6445: l_status := JTF_CTM_UTILITY_PVT.fk_id_is_valid(p_Terr_All_Rec.TEMPLATE_TERRITORY_ID, 'TERR_ID', 'JTF_TERR_ALL');
6446: if l_status <> FND_API.G_TRUE Then
6447: x_return_status := FND_API.G_RET_STS_ERROR;
6448: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6449: --dbms_output('TEMP: Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
6446: if l_status <> FND_API.G_TRUE Then
6447: x_return_status := FND_API.G_RET_STS_ERROR;
6448: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6449: --dbms_output('TEMP: Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
6450: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TEMP_TERR');
6451: FND_MESSAGE.Set_Token('TERR_ID', to_char(p_Terr_All_Rec.TEMPLATE_TERRITORY_ID));
6452: FND_MSG_PUB.ADD;
6453: End If;
6454: End If;
6457: --dbms_output('Validate_Foreign_Key: Checking for ESCALATION_TERRITORY_ID');
6458: --
6459: If (p_Terr_All_Rec.ESCALATION_TERRITORY_ID is not null) AND
6460: (p_Terr_All_Rec.ESCALATION_TERRITORY_ID <> FND_API.G_MISS_NUM) Then
6461: l_status := JTF_CTM_UTILITY_PVT.fk_id_is_valid(p_Terr_All_Rec.escalation_territory_id, 'TERR_ID', 'JTF_TERR_ALL');
6462: if l_status <> FND_API.G_TRUE Then
6463: x_return_status := FND_API.G_RET_STS_ERROR;
6464: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6465: --dbms_output('ESC TERR: Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
6462: if l_status <> FND_API.G_TRUE Then
6463: x_return_status := FND_API.G_RET_STS_ERROR;
6464: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6465: --dbms_output('ESC TERR: Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
6466: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_ESC_TERR');
6467: FND_MESSAGE.Set_Token('TERR_ID', to_char(p_Terr_All_Rec.terr_Id));
6468: FND_MSG_PUB.ADD;
6469: End If;
6470: End If;
6477: --
6478: WHEN OTHERS THEN
6479: --dbms_output('Validate_Foreign_Key: Others exception' || SQLERRM);
6480: X_return_status := FND_API.G_RET_STS_ERROR;
6481: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
6482: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Foreign_Key' );
6483: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
6484: FND_MSG_PUB.ADD;
6485: FND_MSG_PUB.Count_And_Get
6515:
6516: -- Validate the territory Id
6517: If p_Terr_Id IS NOT NULL THEN
6518: l_Validate_id := p_Terr_Id;
6519: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
6520: --dbms_output('Validate_Territory_Usage: l_status <> FND_API.G_TRUE');
6521: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
6522: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
6523: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
6517: If p_Terr_Id IS NOT NULL THEN
6518: l_Validate_id := p_Terr_Id;
6519: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
6520: --dbms_output('Validate_Territory_Usage: l_status <> FND_API.G_TRUE');
6521: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
6522: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
6523: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
6524: FND_MSG_PUB.ADD;
6525: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6518: l_Validate_id := p_Terr_Id;
6519: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
6520: --dbms_output('Validate_Territory_Usage: l_status <> FND_API.G_TRUE');
6521: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
6522: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
6523: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
6524: FND_MSG_PUB.ADD;
6525: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6526: End If;
6532: -- Make sure the foreign key source_id is valid
6533: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'SOURCE_ID', 'JTF_SOURCES_ALL') <> FND_API.G_TRUE Then
6534: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6535: --dbms_output('Validate_Territory_Usage: FND_MSG_PUB.ADD');
6536: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
6537: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_SOURCES');
6538: FND_MESSAGE.Set_Token('COLUMN_NAME', 'SOURCE_ID');
6539: FND_MSG_PUB.ADD;
6540: End If;
6546: -- Check for ORG_ID
6547: IF JTF_CTM_UTILITY_PVT.fk_id_is_valid(p_Terr_Usgs_rec.ORG_ID, 'ORGANIZATION_ID', 'HR_OPERATING_UNITS') <> FND_API.G_TRUE THEN
6548: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6549: --dbms_output('TERR TYPE: Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
6550: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERR_TYPE');
6551: FND_MESSAGE.Set_Token('ORG_ID', to_char(p_Terr_Usgs_rec.ORG_ID));
6552: FND_MSG_PUB.ADD;
6553: End If;
6554: x_return_status := FND_API.G_RET_STS_ERROR;
6557: --Check created by
6558: IF ( p_Terr_Usgs_rec.CREATED_BY is NULL OR
6559: p_Terr_Usgs_rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
6560: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6561: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6562: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
6563: FND_MSG_PUB.ADD;
6564: END IF;
6565: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6568: --Check creation date
6569: If ( p_Terr_Usgs_rec.CREATION_DATE is NULL OR
6570: p_Terr_Usgs_rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
6571: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6572: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6573: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
6574: FND_MSG_PUB.ADD;
6575: END IF;
6576: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6579: -- Validate last updated by
6580: IF ( p_Terr_Usgs_rec.LAST_UPDATED_BY is NULL OR
6581: p_Terr_Usgs_rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
6582: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6583: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6584: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
6585: FND_MSG_PUB.ADD;
6586: END IF;
6587: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6590: -- Check last update date
6591: If ( p_Terr_Usgs_rec.LAST_UPDATE_DATE IS NULL OR
6592: p_Terr_Usgs_rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
6593: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6594: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6595: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
6596: FND_MSG_PUB.ADD;
6597: END IF;
6598: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6601: --Check last update login
6602: --If ( p_Terr_Usgs_rec.LAST_UPDATE_LOGIN is NULL OR
6603: -- p_Terr_Usgs_rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
6604: -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6605: -- FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6606: -- FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
6607: -- FND_MSG_PUB.ADD;
6608: -- END IF;
6609: -- x_Return_Status := FND_API.G_RET_STS_ERROR ;
6633:
6634: WHEN OTHERS THEN
6635: --dbms_output('Validate_Territory_Usage: OTHERS - ' || SQLERRM);
6636: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6637: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
6638: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Territory_Usage' );
6639: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
6640: FND_MSG_PUB.ADD;
6641: FND_MSG_PUB.Count_And_Get
6678: l_qual_Type_Usg_id := p_Terr_QualTypeUsgs_Rec.QUAL_TYPE_USG_ID;
6679: --Check the qual_type_usg_id specified is valid
6680: Select 1
6681: into l_dummy
6682: From jtf_terr_usgs_ALL jtu, jtf_qual_type_usgs_ALL jqtu
6683: where jtu.terr_id = p_Terr_Id and
6684: jqtu.source_id = jtu.source_id and
6685: jqtu.qual_type_usg_id = l_qual_Type_Usg_id;
6686:
6685: jqtu.qual_type_usg_id = l_qual_Type_Usg_id;
6686:
6687: EXCEPTION
6688: WHEN NO_DATA_FOUND THEN
6689: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_QTYPE');
6690: FND_MSG_PUB.ADD;
6691: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6692: END;
6693:
6694: -- Validate the territory Id
6695: l_Validate_id := p_Terr_Id;
6696: If p_Terr_Id IS NOT NULL Then
6697: --dbms_output('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
6698: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
6699: --dbms_output('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
6700: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6701: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
6702: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_ALL');
6697: --dbms_output('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
6698: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
6699: --dbms_output('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
6700: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6701: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
6702: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_ALL');
6703: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
6704: FND_MSG_PUB.ADD;
6705: END IF;
6698: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
6699: --dbms_output('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
6700: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6701: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
6702: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_ALL');
6703: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
6704: FND_MSG_PUB.ADD;
6705: END IF;
6706: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6712: /*-- Check for ORG_ID - obsolete: org_id is optional
6713: IF (p_Terr_QualTypeUsgs_Rec.ORG_ID is NULL) OR
6714: (p_Terr_QualTypeUsgs_Rec.ORG_ID = FND_API.G_MISS_NUM) THEN
6715: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6716: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6717: FND_MESSAGE.Set_Token('COL_NAME', 'ORG_ID' );
6718: FND_MSG_PUB.ADD;
6719: END IF;
6720: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6724: --Check created by
6725: IF ( p_Terr_QualTypeUsgs_Rec.CREATED_BY is NULL OR
6726: p_Terr_QualTypeUsgs_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
6727: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6728: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6729: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
6730: FND_MSG_PUB.ADD;
6731: END IF;
6732: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6735: --Check creation date
6736: If ( p_Terr_QualTypeUsgs_Rec.CREATION_DATE is NULL OR
6737: p_Terr_QualTypeUsgs_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
6738: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6739: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6740: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
6741: FND_MSG_PUB.ADD;
6742: END IF;
6743: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6746: -- Validate last updated by
6747: IF ( p_Terr_QualTypeUsgs_Rec.LAST_UPDATED_BY is NULL OR
6748: p_Terr_QualTypeUsgs_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
6749: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6750: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6751: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
6752: FND_MSG_PUB.ADD;
6753: END IF;
6754: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6757: -- Check last update date
6758: If ( p_Terr_QualTypeUsgs_Rec.LAST_UPDATE_DATE IS NULL OR
6759: p_Terr_QualTypeUsgs_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
6760: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6761: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6762: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
6763: FND_MSG_PUB.ADD;
6764: END IF;
6765: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6768: --Check last update login
6769: --If ( p_Terr_QualTypeUsgs_Rec.LAST_UPDATE_LOGIN is NULL OR
6770: -- p_Terr_QualTypeUsgs_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
6771: -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6772: -- FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6773: -- FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
6774: -- FND_MSG_PUB.ADD;
6775: -- END IF;
6776: -- x_Return_Status := FND_API.G_RET_STS_ERROR ;
6800: WHEN OTHERS THEN
6801: --dbms_output.put_line('Validate_Terr_Qtype_Usage: OTHERS - ' || SQLERRM);
6802: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6803:
6804: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
6805: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Qtype_Usage' );
6806: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
6807: FND_MSG_PUB.ADD;
6808: FND_MSG_PUB.Count_And_Get
6811: );
6812: --
6813: END Validate_Terr_Qtype_Usage;
6814:
6815: /* Function used in JTF_TERR_VALUES_DESC_V to return
6816: ** descriptive values for ids and lookup_codes
6817: */
6818: PROCEDURE Validate_terr_Value
6819: (p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
6870: EXCEPTION
6871: WHEN NO_DATA_FOUND THEN
6872: X_return_status := FND_API.G_RET_STS_ERROR;
6873: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6874: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
6875: FND_MSG_PUB.ADD;
6876: END IF;
6877:
6878: WHEN OTHERS THEN
6876: END IF;
6877:
6878: WHEN OTHERS THEN
6879: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6880: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
6881: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_terr_Value' );
6882: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
6883: FND_MSG_PUB.ADD;
6884: FND_MSG_PUB.Count_And_Get
6919: jqu.display_sql1,
6920: jqu.display_sql2,
6921: jqu.display_sql3,
6922: html_lov_sql1
6923: from jtf_qual_usgs_ALL jqu, jtf_terr_qual_ALL jtq
6924: where jqu.qual_usg_id = jtq.qual_Usg_Id and
6925: jqu.org_id = jtq.org_id AND
6926: jtq.terr_qual_id = p_Terr_Qual_Id;
6927:
6958: /*-- Check for ORG_ID - obsolete: org_id is optional
6959: IF (p_Terr_Value_Rec.ORG_ID is NULL) OR
6960: (p_Terr_Value_Rec.ORG_ID = FND_API.G_MISS_NUM) THEN
6961: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6962: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6963: FND_MESSAGE.Set_Token('COL_NAME', 'ORG_ID' );
6964: FND_MSG_PUB.ADD;
6965: END IF;
6966: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6972:
6973: If ( p_Terr_Value_Rec.ID_USED_FLAG IS NULL OR
6974: p_Terr_Value_Rec.ID_USED_FLAG = FND_API.G_MISS_CHAR ) Then
6975: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6976: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6977: FND_MESSAGE.Set_Token('COL_NAME', 'ID_USED_FLAG' );
6978: FND_MSG_PUB.ADD;
6979: END IF;
6980: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6985: -- Validate last updated by
6986: IF ( p_Terr_Value_Rec.LAST_UPDATED_BY is NULL OR
6987: p_Terr_Value_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
6988: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6989: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
6990: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
6991: FND_MSG_PUB.ADD;
6992: END IF;
6993: x_Return_Status := FND_API.G_RET_STS_ERROR ;
6996: -- Check last update date
6997: If ( p_Terr_Value_Rec.LAST_UPDATE_DATE IS NULL OR
6998: p_Terr_Value_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
6999: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7000: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
7001: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
7002: FND_MSG_PUB.ADD;
7003: END IF;
7004: x_Return_Status := FND_API.G_RET_STS_ERROR ;
7007: --Check last update login
7008: If ( p_Terr_Value_Rec.LAST_UPDATE_LOGIN is NULL OR
7009: p_Terr_Value_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
7010: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7011: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
7012: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
7013: FND_MSG_PUB.ADD;
7014: END IF;
7015: x_Return_Status := FND_API.G_RET_STS_ERROR ;
7039: IF ( p_Terr_Value_Rec.COMPARISON_OPERATOR <> '=' ) THEN
7040:
7041: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7042:
7043: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7044: FND_MSG_PUB.ADD;
7045:
7046: END IF;
7047:
7058: IF ( p_Terr_Value_Rec.COMPARISON_OPERATOR <> '=' ) THEN
7059:
7060: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7061:
7062: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7063: FND_MSG_PUB.ADD;
7064:
7065: END IF;
7066:
7075: AND p_Terr_Value_Rec.COMPARISON_OPERATOR <> 'BETWEEN' ) THEN
7076:
7077: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7078:
7079: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7080: FND_MSG_PUB.ADD;
7081:
7082: END IF;
7083:
7095: AND p_Terr_Value_Rec.COMPARISON_OPERATOR <> 'BETWEEN') THEN
7096:
7097: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7098:
7099: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7100: FND_MSG_PUB.ADD;
7101:
7102: END IF;
7103:
7115: IF ( p_Terr_Value_Rec.COMPARISON_OPERATOR <> '=' ) THEN
7116:
7117: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7118:
7119: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7120: FND_MSG_PUB.ADD;
7121:
7122: END IF;
7123:
7151: ( p_Terr_Value_Rec.INTEREST_TYPE_ID IS NOT NULL and
7152: p_Terr_Value_Rec.INTEREST_TYPE_ID <> FND_API.G_MISS_NUM ) ) THEN
7153:
7154: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7155: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7156: FND_MSG_PUB.ADD;
7157: END IF;
7158:
7159: x_Return_Status := FND_API.G_RET_STS_ERROR ;
7201: (p_Terr_Value_Rec.INTEREST_TYPE_ID IS NOT NULL and
7202: p_Terr_Value_Rec.INTEREST_TYPE_ID <> FND_API.G_MISS_NUM ) ) THEN
7203:
7204: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7205: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7206: FND_MSG_PUB.ADD;
7207: END IF;
7208:
7209: x_Return_Status := FND_API.G_RET_STS_ERROR ;
7240: (p_Terr_Value_Rec.COMPARISON_OPERATOR IS NULL OR
7241: p_Terr_Value_Rec.COMPARISON_OPERATOR = FND_API.G_MISS_CHAR ) ) THEN
7242:
7243: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7244: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7245: FND_MSG_PUB.ADD;
7246: END IF;
7247:
7248: x_Return_Status := FND_API.G_RET_STS_ERROR ;
7267: (p_Terr_Value_Rec.INTEREST_TYPE_ID IS NOT NULL and
7268: p_Terr_Value_Rec.INTEREST_TYPE_ID <> FND_API.G_MISS_NUM ) ) THEN
7269:
7270: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7271: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7272: FND_MSG_PUB.ADD;
7273: END IF;
7274:
7275: x_Return_Status := FND_API.G_RET_STS_ERROR ;
7293: p_Terr_Value_Rec.LOW_VALUE_CHAR <> FND_API.G_MISS_CHAR ) ) THEN
7294:
7295: --dbms_output('Error INTEREST_TYPE');
7296: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7297: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7298: FND_MSG_PUB.ADD;
7299: END IF;
7300:
7301: x_Return_Status := FND_API.G_RET_STS_ERROR ;
7376: p_Terr_Value_Rec.LOW_VALUE_CHAR <> FND_API.G_MISS_CHAR ) ) THEN
7377:
7378: --dbms_output('Error INTEREST_TYPE');
7379: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7380: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7381: FND_MSG_PUB.ADD;
7382: END IF;
7383:
7384: x_Return_Status := FND_API.G_RET_STS_ERROR ;
7414: p_Terr_Value_Rec.COMPARISON_OPERATOR = FND_API.G_MISS_CHAR ) ) THEN
7415:
7416: --dbms_output('Error INTEREST_TYPE');
7417: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7418: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7419: FND_MSG_PUB.ADD;
7420: END IF;
7421:
7422: x_Return_Status := FND_API.G_RET_STS_ERROR ;
7470: (p_Terr_Value_Rec.COMPARISON_OPERATOR IS NULL OR
7471: p_Terr_Value_Rec.COMPARISON_OPERATOR = FND_API.G_MISS_CHAR ) ) THEN
7472:
7473: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7474: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7475: FND_MSG_PUB.ADD;
7476: END IF;
7477: x_Return_Status := FND_API.G_RET_STS_ERROR ;
7478: ELSE
7526: (p_Terr_Value_Rec.COMPARISON_OPERATOR IS NULL OR
7527: p_Terr_Value_Rec.COMPARISON_OPERATOR = FND_API.G_MISS_CHAR ) ) THEN
7528:
7529: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7530: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7531: FND_MSG_PUB.ADD;
7532: END IF;
7533: x_Return_Status := FND_API.G_RET_STS_ERROR ;
7534: ELSE
7604: ( p_Terr_Value_Rec.COMPARISON_OPERATOR IS NULL OR
7605: p_Terr_Value_Rec.COMPARISON_OPERATOR = FND_API.G_MISS_CHAR ) ) THEN
7606:
7607: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
7608: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_TERR_VALUE');
7609: FND_MSG_PUB.ADD;
7610: END IF;
7611: x_Return_Status := FND_API.G_RET_STS_ERROR ;
7612: ELSE
7680: WHEN OTHERS THEN
7681: --dbms_output('Validate_Terr_Qtype_Usage: OTHERS - ' || SQLERRM);
7682: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7683:
7684: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
7685: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_terr_Value_Rec' );
7686: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
7687: FND_MSG_PUB.ADD;
7688:
7707: BEGIN
7708:
7709: Select 'X'
7710: into l_dummy
7711: From JTF_TERR_VALUES_ALL
7712: WHERE TERR_QUAL_ID = P_terr_qual_id
7713: AND nvl(COMPARISON_OPERATOR , '-9999') = nvl( decode(P_Terr_Value_Rec.COMPARISON_OPERATOR, FND_API.G_MISS_CHAR, NULL , P_Terr_Value_Rec.COMPARISON_OPERATOR ) , '-9999')
7714: AND nvl(LOW_VALUE_CHAR , '-9999') = nvl( decode(P_Terr_Value_Rec.LOW_VALUE_CHAR, FND_API.G_MISS_CHAR, NULL , P_Terr_Value_Rec.LOW_VALUE_CHAR ) , '-9999')
7715: AND nvl(HIGH_VALUE_CHAR , '-9999') = nvl( decode(P_Terr_Value_Rec.HIGH_VALUE_CHAR, FND_API.G_MISS_CHAR, NULL , P_Terr_Value_Rec.HIGH_VALUE_CHAR ) , '-9999')
7758: WHEN OTHERS THEN
7759: --dbms_output('Validate_Terr_Qtype_Usage: OTHERS - ' || SQLERRM);
7760: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7761:
7762: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
7763: FND_MESSAGE.Set_Token('PROC_NAME', 'Check_duplicate_Value');
7764: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
7765: FND_MSG_PUB.ADD;
7766:
7783: l_dummy VARCHAR2(5);
7784: BEGIN
7785: Select 'X'
7786: into l_dummy
7787: From JTF_TERR_VALUES_ALL
7788: WHERE TERR_VALUE_ID <> P_Terr_Value_Rec.TERR_VALUE_ID
7789: AND TERR_QUAL_ID = P_terr_qual_id
7790: AND nvl(COMPARISON_OPERATOR , '-9999') = nvl( decode(P_Terr_Value_Rec.COMPARISON_OPERATOR, FND_API.G_MISS_CHAR, NULL , P_Terr_Value_Rec.COMPARISON_OPERATOR ) , '-9999')
7791: AND nvl(LOW_VALUE_CHAR , '-9999') = nvl( decode(P_Terr_Value_Rec.LOW_VALUE_CHAR, FND_API.G_MISS_CHAR, NULL , P_Terr_Value_Rec.LOW_VALUE_CHAR ) , '-9999')
7835: WHEN OTHERS THEN
7836: --dbms_output('Validate_Terr_Qtype_Usage: OTHERS - ' || SQLERRM);
7837: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7838:
7839: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
7840: FND_MESSAGE.Set_Token('PROC_NAME', 'Check_duplicate_Value');
7841: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
7842: FND_MSG_PUB.ADD;
7843:
7860: AS
7861: BEGIN
7862: Select Max(rank)
7863: into x_Rank
7864: from jtf_terr_ALL jt, jtf_terr_usgs_ALL jtu
7865: where jt.Parent_Territory_id = p_Parent_Terr_Id and
7866: jt.Terr_Id = jtu.Terr_Id and
7867: jtu.source_id = p_Source_Id;
7868:
7903:
7904: /* (1) check template exists */
7905: CURSOR c_chk_template (p_template_terr_id NUMBER) IS
7906: SELECT 'X'
7907: FROM jtf_terr_ALL j
7908: WHERE j.terr_id = p_template_terr_id
7909: AND j.template_flag = 'Y';
7910:
7911:
7911:
7912: /* (2) check that at least 1 dynamic is specified for use in generation */
7913: CURSOR c_chk_qual_mode (p_template_terr_id NUMBER) IS
7914: SELECT COUNT(*)
7915: FROM jtf_terr_qual j
7916: WHERE j.terr_id = p_template_terr_id
7917: AND j.qualifier_mode = 'DYNAMIC';
7918:
7919: l_terr_id NUMBER := 0;
7935: OPEN c_chk_template (p_template_terr_id);
7936: FETCH c_chk_template INTO l_csr_rtn;
7937: IF (c_chk_template%NOTFOUND) THEN
7938:
7939: fnd_message.set_name('JTF', 'JTF_TERR_INVALID_TEMPLATE');
7940: --fnd_msg_pub.add;
7941: x_return_status := FND_API.G_RET_STS_ERROR;
7942:
7943: END IF;
7950: --arpatel 07/13 bug#1872642
7951: --IF (c_chk_qual_mode%NOTFOUND) THEN
7952:
7953: IF (l_dynamic_qual_count = 0) THEN
7954: fnd_message.set_name ('JTF', 'JTF_TERR_NO_DYNAMIC_QUALIFIERS');
7955: fnd_msg_pub.add;
7956: x_return_status := FND_API.G_RET_STS_ERROR;
7957:
7958: END IF;
7966: --OPEN c_lock_template (p_template_terr_id);
7967:
7968: SELECT j1.terr_id
7969: INTO l_terr_id
7970: FROM jtf_terr j1
7971: WHERE j1.terr_id = p_template_terr_id
7972: FOR UPDATE NOWAIT;
7973:
7974: EXCEPTION
7972: FOR UPDATE NOWAIT;
7973:
7974: EXCEPTION
7975: WHEN OTHERS THEN
7976: fnd_message.set_name('JTF','JTF_TERR_TEMPLATE_LOCKED');
7977: x_return_status := FND_API.G_RET_STS_ERROR;
7978: END; /* block for validation (4) */
7979:
7980: --dbms_output('Validate_Template_Record: x_return_status = '|| x_return_status);
8071: , j1.ATTRIBUTE14
8072: , j1.ATTRIBUTE15
8073: , j1.ORG_ID
8074: , j1.NUM_WINNERS
8075: FROM jtf_terr j1
8076: WHERE j1.terr_id = p_template_terr_id;
8077:
8078: /* cursor to get the number of territories that will be generated */
8079: CURSOR c_get_max_value_set (p_template_terr_id NUMBER) IS
8077:
8078: /* cursor to get the number of territories that will be generated */
8079: CURSOR c_get_max_value_set (p_template_terr_id NUMBER) IS
8080: SELECT MAX (j1.VALUE_SET)
8081: FROM jtf_terr_values j1, jtf_terr_qual j2, jtf_terr j3
8082: WHERE j1.terr_qual_id = j2.terr_qual_id
8083: AND j2.terr_id = j3.terr_id
8084: AND j3.terr_id = p_template_terr_id
8085: AND j2.qualifier_mode = 'DYNAMIC'
8095: , j1.CREATION_DATE
8096: , j1.CREATED_BY
8097: , j1.LAST_UPDATE_LOGIN
8098: , j1.ORG_ID
8099: FROM jtf_terr_usgs j1
8100: WHERE j1.terr_id = p_template_terr_id;
8101:
8102: /* cursor to get territory's qual type usages */
8103: CURSOR c_get_terr_qtype_usgs (p_template_terr_id NUMBER) IS
8109: , j1.CREATION_DATE
8110: , j1.CREATED_BY
8111: , j1.LAST_UPDATE_LOGIN
8112: , j1.ORG_ID
8113: from jtf_terr_qtype_usgs j1
8114: WHERE j1.terr_id = p_template_terr_id;
8115:
8116: l_product NUMBER := 1;
8117: l_num_gen_terr NUMBER := 1;
8377: l_new_terr_rec.CREATED_BY := FND_GLOBAL.USER_ID;
8378: l_new_terr_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
8379: l_new_terr_rec.APPLICATION_SHORT_NAME := FND_GLOBAL.APPLICATION_SHORT_NAME;
8380:
8381: -- dbms_output.put_line('JTF_TERRITORY_PVT:create_copied_terr:l_new_terr_rec.APPLICATION_SHORT_NAME-' || l_new_terr_rec.APPLICATION_SHORT_NAME);
8382:
8383: -- 04/06/01 ARPATEL - START
8384: IF (p_copy_hierarchy_flag = 'Y') THEN
8385:
8598:
8599: /* START OF 1520656 BUG FIX - JDOCHERT 12/07 */
8600: CURSOR c_get_terr(lp_terr_id NUMBER)
8601: IS SELECT 'Y'
8602: FROM jtf_terr_ALL
8603: WHERE template_flag = 'Y'
8604: AND terr_id = lp_terr_id;
8605:
8606: l_template_flag VARCHAR2(1);
8684:
8685: /* START OF 1520656 BUG FIX - JDOCHERT 12/07 */
8686: CURSOR c_get_terr(lp_terr_id NUMBER)
8687: IS SELECT 'Y'
8688: FROM jtf_terr
8689: WHERE template_flag = 'Y'
8690: AND terr_id = lp_terr_id;
8691:
8692: l_template_flag VARCHAR2(1);
8790: , j1.GENERATE_FLAG
8791: , j1.OVERLAP_ALLOWED_FLAG
8792: , j1.QUALIFIER_MODE
8793: , j1.ORG_ID
8794: FROM jtf_terr_qual j1
8795: WHERE j1.terr_id = p_template_terr_id;
8796:
8797: /* cursor to get values for STATIC qualifiers' values */
8798: CURSOR c_get_terr_value (p_terr_qual_id NUMBER) IS
8822: , j1.VALUE1_ID
8823: , j1.VALUE2_ID
8824: , j1.VALUE3_ID
8825: , j1.VALUE4_ID
8826: FROM jtf_terr_values j1
8827: WHERE j1.terr_qual_id = p_terr_qual_id;
8828:
8829: /* local record variables */
8830: l_tmpl_terr_qual_rec Terr_Qual_Rec_Type;
8943: , j1.GENERATE_FLAG
8944: , j1.OVERLAP_ALLOWED_FLAG
8945: , j1.QUALIFIER_MODE
8946: , j1.ORG_ID
8947: FROM jtf_terr_qual j1
8948: WHERE j1.terr_id = p_template_terr_id
8949: AND j1.qualifier_mode = 'STATIC';
8950:
8951: /* cursor to get values for STATIC qualifiers' values */
8976: , j1.VALUE1_ID
8977: , j1.VALUE2_ID
8978: , j1.VALUE3_ID
8979: , j1.VALUE4_ID
8980: FROM jtf_terr_values j1
8981: WHERE j1.terr_qual_id = p_terr_qual_id;
8982:
8983: /* local record variables */
8984: l_tmpl_terr_qual_rec Terr_Qual_Rec_Type;
9089: , j1.GENERATE_FLAG
9090: , j1.OVERLAP_ALLOWED_FLAG
9091: , j1.QUALIFIER_MODE
9092: , j1.ORG_ID
9093: FROM jtf_terr_qual j1
9094: WHERE j1.terr_id = p_template_terr_id
9095: AND j1.qualifier_mode = 'DYNAMIC';
9096:
9097: /* cursor to get values for dynamic qualifier values */
9122: , j1.VALUE1_ID
9123: , j1.VALUE2_ID
9124: , j1.VALUE3_ID
9125: , j1.VALUE4_ID
9126: FROM jtf_terr_values j1
9127: WHERE j1.terr_qual_id = p_terr_qual_id
9128: ORDER BY j1.value_set;
9129:
9130: l_tmpl_terr_qual_rec Terr_Qual_Rec_Type;
9396:
9397: -- Debug Message
9398: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
9399: THEN
9400: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
9401: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
9402: FND_MSG_PUB.Add;
9403: END IF;
9404:
9415:
9416: /* Debug message */
9417: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
9418: THEN
9419: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
9420: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Template_Record');
9421: FND_MSG_PUB.Add;
9422: END IF;
9423:
9474: l_tmpl_terr_qual_rec.overlap_allowed_flag := l_dyn_qual_tbl(i).OVERLAP_ALLOWED_FLAG;
9475: l_tmpl_terr_qual_rec.qualifier_mode := l_dyn_qual_tbl(i).QUALIFIER_MODE;
9476: l_tmpl_terr_qual_rec.org_id := l_dyn_qual_tbl(i).ORG_ID;
9477:
9478: /* insert record IN JTF_TERR_QUAL_ALL */
9479: create_new_terr_qual_rec(l_new_terr_id, l_tmpl_terr_qual_rec, l_new_terr_qual_id);
9480:
9481: l_rec_index := l_dyn_qual_tbl(i).start_record + l_dyn_qual_tbl(i).current_record;
9482:
9560:
9561: -- Debug Message
9562: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
9563: THEN
9564: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
9565: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
9566: FND_MSG_PUB.Add;
9567: END IF;
9568:
9659: l_tbl_count NUMBER;
9660:
9661: CURSOR csr_child_terrs (p_terr_id NUMBER) IS
9662: select jta.terr_id
9663: from JTF_TERR_ALL jta
9664: where jta.parent_territory_id = p_terr_Id;
9665:
9666: BEGIN
9667:
9684:
9685: -- Debug Message
9686: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
9687: THEN
9688: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
9689: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
9690: FND_MSG_PUB.Add;
9691: END IF;
9692:
9699: /* ======================*/
9700:
9701: /* (validation procedures not necessary) */
9702:
9703: --dbms_output('JTF_TERRITORY_PVT:gen_dupliacte_territory: - BEGIN');
9704: -- get table of terr usages and terr qual type usages for the source territory
9705: initialise( p_copy_source_terr_id -- input TERR_ID
9706: , l_copy_source_terr_rec -- output desc
9707: , l_num_gen_terr -- output (irrelevant)
9721: create_new_terr_qtype_usgs(l_new_terr_id, l_tmpl_qtype_usgs_tbl);
9722:
9723: /* for all qualifier values */
9724: insert_copied_qual_values(p_copy_source_terr_id, l_new_terr_id);
9725: --dbms_output('JTF_TERRITORY_PVT:gen_dupliacte_territory: - END');
9726:
9727: /* START: 09/17/00 - JDOCHERT */
9728: IF (p_copy_rsc_flag = 'Y') THEN
9729:
9726:
9727: /* START: 09/17/00 - JDOCHERT */
9728: IF (p_copy_rsc_flag = 'Y') THEN
9729:
9730: JTF_TERRITORY_RESOURCE_PVT.copy_terr_resources( p_Api_Version_Number => p_Api_Version_Number,
9731: p_Init_Msg_List => p_Init_Msg_List,
9732: p_Commit => p_Commit,
9733: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
9734: p_source_terr_id => p_copy_source_terr_id,
9748: loop
9749:
9750: --p_new_terr_rec.parent_territory_id := l_new_terr_id;
9751:
9752: JTF_TERRITORY_PVT.Copy_Territory (
9753: p_Api_Version_Number => p_Api_Version_Number,
9754: p_Init_Msg_List => p_Init_Msg_List,
9755: p_Commit => p_Commit,
9756: p_validation_level => p_validation_level,
9763: x_Msg_Count => l_msg_count,
9764: x_Msg_Data => l_msg_data,
9765: x_terr_id => l_terr_id );
9766:
9767: update JTF_TERR_ALL
9768: set parent_territory_id = l_new_terr_id
9769: where terr_id = l_terr_id;
9770:
9771: end loop;
9783:
9784: -- Debug Message
9785: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
9786: THEN
9787: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
9788: FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
9789: FND_MSG_PUB.Add;
9790: END IF;
9791:
9844: -- checked only under a single parent.
9845: ---------------------------------------------------------------------
9846: FUNCTION Overlap_Exists(p_Parent_Terr_Id IN NUMBER,
9847: p_Qual_Usg_Id IN NUMBER,
9848: p_terr_value_record IN jtf_terr_values%ROWTYPE )
9849: RETURN VARCHAR2
9850: AS
9851: dummy NUMBER := 0;
9852: l_found NUMBER := 0;
9869: Then
9870: If p_terr_value_record.low_value_char is NOT NULL Then
9871: Select count(*)
9872: into dummy
9873: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
9874: Where jt.parent_territory_id = p_Parent_Terr_Id
9875: and jtq.terr_id = jt.terr_id
9876: and jtq.qual_usg_id = p_Qual_Usg_Id
9877: and jtv.terr_qual_id = jtq.terr_qual_id
9896: -- Check the high_value_char
9897: If p_terr_value_record.high_value_char is NOT NULL Then
9898: Select count(*)
9899: into dummy
9900: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
9901: Where jt.parent_territory_id = p_Parent_Terr_Id
9902: and jtq.terr_id = jt.terr_id
9903: and jtq.qual_usg_id = p_Qual_Usg_Id
9904: and jtv.terr_qual_id = jtq.terr_qual_id
9927: -- only check for a subset of the availabel operator as these are the only
9928: -- available one for this combination
9929: Select count(*)
9930: into dummy
9931: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
9932: Where jt.parent_territory_id = p_Parent_Terr_Id
9933: and jtq.terr_id = jt.terr_id
9934: and jtq.qual_usg_id = p_Qual_Usg_Id
9935: and jtv.terr_qual_id = jtq.terr_qual_id
9945:
9946: If p_terr_value_record.low_value_number is NOT NULL Then
9947: Select 1
9948: into dummy
9949: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
9950: Where jt.parent_territory_id = p_Parent_Terr_Id
9951: and jtq.terr_id = jt.terr_id
9952: and jtq.qual_usg_id = p_Qual_Usg_Id
9953: and jtv.terr_qual_id = jtq.terr_qual_id
9967:
9968: If p_terr_value_record.high_value_number is NOT NULL Then
9969: Select 1
9970: into dummy
9971: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
9972: Where jt.parent_territory_id = p_Parent_Terr_Id
9973: and jtq.terr_id = jt.terr_id
9974: and jtq.qual_usg_id = p_Qual_Usg_Id
9975: and jtv.terr_qual_id = jtq.terr_qual_id
9995:
9996: If p_terr_value_record.low_value_number is NOT NULL Then
9997: Select count(*)
9998: into dummy
9999: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
10000: Where jt.parent_territory_id = p_Parent_Terr_Id
10001: and jtq.terr_id = jt.terr_id
10002: and jtq.qual_usg_id = p_Qual_Usg_Id
10003: and jtv.terr_qual_id = jtq.terr_qual_id
10017:
10018: If p_terr_value_record.high_value_number is NOT NULL Then
10019: Select count(*)
10020: into dummy
10021: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
10022: Where jt.parent_territory_id = p_Parent_Terr_Id
10023: and jtq.terr_id = jt.terr_id
10024: and jtq.qual_usg_id = p_Qual_Usg_Id
10025: and jtv.terr_qual_id = jtq.terr_qual_id
10043: l_convert_to_id_flag is NULL ) )
10044: Then
10045: Select count(*)
10046: into dummy
10047: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
10048: Where jt.parent_territory_id = p_Parent_Terr_Id
10049: and jtq.terr_id = jt.terr_id
10050: and jtq.qual_usg_id = p_Qual_Usg_Id
10051: and jtv.terr_qual_id = jtq.terr_qual_id
10074:
10075: END Overlap_Exists;
10076:
10077: -- jdochert 09/09
10078: -- check for Unique Key constraint violation on JTF_TERR_USGS table
10079: PROCEDURE validate_terr_usgs_UK(
10080: p_Terr_Id IN NUMBER,
10081: p_Source_Id IN NUMBER,
10082: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
10088: -- cursor to check that Unique Key constraint not violated
10089: CURSOR csr_chk_uk_violation ( lp_terr_id NUMBER
10090: , lp_source_id NUMBER) IS
10091: SELECT 'X'
10092: FROM JTF_TERR_USGS_ALL
10093: WHERE terr_id = lp_terr_id
10094: AND source_id = lp_source_id;
10095:
10096: l_return_csr VARCHAR2(1);
10110: FETCH csr_chk_uk_violation INTO l_return_csr;
10111:
10112: IF csr_chk_uk_violation%FOUND THEN
10113:
10114: --dbms_output.put_line('Validate_Unique_Key: jtf_terr_usgs: VIOLATION');
10115:
10116: x_return_status := FND_API.G_RET_STS_ERROR;
10117:
10118: /* Debug message */
10116: x_return_status := FND_API.G_RET_STS_ERROR;
10117:
10118: /* Debug message */
10119: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
10120: FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_USGS_UK_CON');
10121: FND_MSG_PUB.ADD;
10122: END IF;
10123:
10124: END IF; /* c_chk_uk_violation%FOUND */
10133:
10134: WHEN OTHERS THEN
10135: --dbms_output('Validate_Foreign_Key: Others exception' || SQLERRM);
10136: X_return_status := FND_API.G_RET_STS_ERROR;
10137: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
10138: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Unique_Key' );
10139: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
10140: FND_MSG_PUB.ADD;
10141: FND_MSG_PUB.Count_And_Get
10145:
10146: END validate_terr_usgs_UK;
10147:
10148: -- jdochert 09/09
10149: -- check for Unique Key constraint violation on JTF_TERR_QTYPE_USGS table
10150: PROCEDURE validate_terr_qtype_usgs_UK(
10151: p_Terr_Id IN NUMBER,
10152: p_Qual_Type_Usg_Id IN NUMBER,
10153: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
10159: -- cursor to check that Unique Key constraint not violated
10160: CURSOR csr_chk_uk_violation ( lp_terr_id NUMBER
10161: , lp_qual_type_usg_id NUMBER) IS
10162: SELECT 'X'
10163: FROM JTF_TERR_QTYPE_USGS_ALL
10164: WHERE terr_id = lp_terr_id
10165: AND qual_type_usg_id = lp_qual_type_usg_id;
10166:
10167: l_return_csr VARCHAR2(1);
10188:
10189: /* Debug message */
10190: --arpatel 09/18 - set message for form to pick up.
10191: --IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
10192: FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_QTYPE_USGS_UK_CON');
10193: FND_MSG_PUB.ADD;
10194:
10195: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
10196: p_data => x_msg_data);
10209:
10210: WHEN OTHERS THEN
10211: --dbms_output('Validate_Foreign_Key: Others exception' || SQLERRM);
10212: X_return_status := FND_API.G_RET_STS_ERROR;
10213: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
10214: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Unique_Key' );
10215: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
10216: FND_MSG_PUB.ADD;
10217: FND_MSG_PUB.Count_And_Get
10238: ** 09/17/00 JDOCHERT BUG#1408610 FIX
10239: */
10240: CURSOR csr_chk_num_winners (lp_terr_id NUMBER) IS
10241: SELECT 'X'
10242: FROM jtf_terr_ALL j
10243: WHERE j.parent_territory_id = 1
10244: AND j.terr_id = lp_terr_id;
10245:
10246: l_return_csr VARCHAR2(1);
10269: x_return_status := FND_API.G_RET_STS_ERROR;
10270:
10271: /* Debug message */
10272: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
10273: FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_NUM_WINNERS_TOP_LEVEL');
10274: FND_MSG_PUB.ADD;
10275: END IF;
10276:
10277: END IF;
10283:
10284: WHEN OTHERS THEN
10285: --dbms_output('Validate_Num_Winners: Others exception' || SQLERRM);
10286: X_return_status := FND_API.G_RET_STS_ERROR;
10287: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
10288: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Num_Winners' );
10289: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
10290: FND_MSG_PUB.ADD;
10291: FND_MSG_PUB.Count_And_Get
10307: AS
10308:
10309: CURSOR csr_get_terr_num (lp_terr_id NUMBER) IS
10310: SELECT COUNT(*)
10311: FROM jtf_terr_all jt
10312: CONNECT BY PRIOR jt.terr_id = jt.parent_territory_id
10313: START WITH jt.terr_id = lp_terr_id;
10314:
10315: BEGIN
10333:
10334:
10335: /* Debug message */
10336: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
10337: FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_NUM_COPY_TERR');
10338: FND_MSG_PUB.ADD;
10339: END IF;
10340:
10341: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
10344: EXCEPTION
10345: WHEN OTHERS THEN
10346: --dbms_output('chk_num_copy_terr: Others exception' || SQLERRM);
10347: X_return_status := FND_API.G_RET_STS_ERROR;
10348: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
10349: FND_MESSAGE.Set_Token('PROC_NAME', 'chk_num_copy_terr' );
10350: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
10351: FND_MSG_PUB.ADD;
10352: FND_MSG_PUB.Count_And_Get
10371: p_debug_flag IN VARCHAR2 := 'N',
10372: p_sql_trace IN VARCHAR2 := 'N' ) AS
10373:
10374: /* local record variables */
10375: l_terr_rec JTF_TERRITORY_PVT.TERR_ALL_REC_TYPE;
10376:
10377: /* local item variables */
10378: l_terr_id NUMBER;
10379: l_hier_terr_count NUMBER;
10462: l_terr_rec.ATTRIBUTE15 := FND_API.G_MISS_CHAR;
10463: l_terr_rec.ORG_ID := FND_API.G_MISS_NUM;
10464: l_terr_rec.NUM_WINNERS := FND_API.G_MISS_NUM;
10465:
10466: JTF_TERRITORY_PVT.Copy_Territory (
10467: p_Api_Version_Number => l_Api_Version_Number,
10468: p_Init_Msg_List => FND_API.G_TRUE,
10469: p_Commit => FND_API.G_TRUE,
10470: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
10501: IF (G_Debug) THEN
10502:
10503: SELECT COUNT(*)
10504: INTO l_hier_terr_count
10505: FROM jtf_terr_all jt
10506: CONNECT BY PRIOR jt.terr_id = jt.parent_territory_id
10507: START WITH jt.terr_id = p_copy_source_terr_Id;
10508:
10509: Write_Log(2, ' ');
10507: START WITH jt.terr_id = p_copy_source_terr_Id;
10508:
10509: Write_Log(2, ' ');
10510: Write_Log(2, '/***************** BEGIN: COPY TERRITORY STATUS *********************/');
10511: Write_Log(2, 'Populating JTF_TERR_ALL table. Copied ' || l_hier_terr_count || ' territories.');
10512: Write_Log(2, 'Inserted ' || l_hier_terr_count || ' rows into JTF_TERR_ALL ');
10513: Write_Log(2, '/***************** END: COPY TERRITORY STATUS *********************/');
10514:
10515: END IF;
10508:
10509: Write_Log(2, ' ');
10510: Write_Log(2, '/***************** BEGIN: COPY TERRITORY STATUS *********************/');
10511: Write_Log(2, 'Populating JTF_TERR_ALL table. Copied ' || l_hier_terr_count || ' territories.');
10512: Write_Log(2, 'Inserted ' || l_hier_terr_count || ' rows into JTF_TERR_ALL ');
10513: Write_Log(2, '/***************** END: COPY TERRITORY STATUS *********************/');
10514:
10515: END IF;
10516:
10636: BEGIN
10637:
10638: SELECT j.parent_territory_id
10639: INTO l_existing_parent_terr_id
10640: FROM jtf_terr_all j
10641: WHERE j.terr_id = p_terr_all_rec.terr_id;
10642:
10643: EXCEPTION
10644: WHEN NO_DATA_FOUND THEN
10652: ** of the current territory
10653: */
10654: SELECT COUNT(*)
10655: INTO l_return_csr
10656: FROM jtf_terr_all j
10657: WHERE j.terr_id = p_terr_all_rec.parent_territory_id -- new parent territory id
10658: CONNECT BY PRIOR j.terr_id = j.parent_territory_id
10659: START WITH j.terr_id = p_terr_all_rec.terr_id; -- territory_id
10660:
10667: --dbms_output.put_line('[3]l_return_csr = ' || l_return_csr);
10668:
10669: /* Debug message */
10670: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
10671: FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_CIRCULAR_REFERENCE');
10672: FND_MSG_PUB.ADD;
10673: END IF;
10674:
10675: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
10681: --dbms_output.put_line('Validate_Parent: e_Circular_Reference exception' || SQLERRM);
10682:
10683: X_return_status := FND_API.G_RET_STS_ERROR;
10684:
10685: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_CIRCULAR_REFERENCE');
10686: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Parent' );
10687: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
10688: FND_MSG_PUB.ADD;
10689:
10694:
10695: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
10696: --dbms_output.put_line('Validate_Parent: FND_API.G_EXC_UNEXPECTED_ERROR');
10697: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
10698: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
10699: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Parent' );
10700: FND_MSG_PUB.Count_And_Get
10701: ( P_count => x_msg_count,
10702: P_data => x_msg_data
10704:
10705: WHEN OTHERS THEN
10706: --dbms_output.put_line('Validate_Parent: Others exception' || SQLERRM);
10707: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
10708: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
10709: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Parent' );
10710: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
10711: FND_MSG_PUB.ADD;
10712: FND_MSG_PUB.Count_And_Get
10715: );
10716: END validate_parent;
10717:
10718:
10719: /* Function used in JTF_TERR_VALUES_DESC_V to return
10720: ** descriptive values for ids and lookup_codes
10721: */
10722: FUNCTION get_terr_value_desc (
10723: p_convert_to_id_flag VARCHAR2
10835: SELECT 'GROUP1' COMPARISION_GROUP,
10836: f.lookup_code,
10837: f.meaning
10838: FROM fnd_lookups f
10839: WHERE f.lookup_type = 'JTF_TERR_COMP_OPERATOR'
10840: AND f.lookup_code IN ('=', '<>')
10841: AND f.enabled_flag = 'Y'
10842: UNION ALL
10843: SELECT 'GROUP2' COMPARISION_GROUP,
10843: SELECT 'GROUP2' COMPARISION_GROUP,
10844: f.lookup_code,
10845: f.meaning
10846: FROM fnd_lookups f
10847: WHERE f.lookup_type = 'JTF_TERR_COMP_OPERATOR'
10848: AND f.lookup_code IN ( '=', '<>', 'LIKE', 'NOT LIKE' , 'BETWEEN', 'NOT BETWEEN')
10849: AND f.enabled_flag = 'Y'
10850: UNION ALL
10851: SELECT 'GROUP3' COMPARISION_GROUP,
10851: SELECT 'GROUP3' COMPARISION_GROUP,
10852: f.lookup_code,
10853: f.meaning
10854: FROM fnd_lookups f
10855: WHERE f.lookup_type = 'JTF_TERR_COMP_OPERATOR'
10856: AND f.lookup_code IN ( '=', '<>' , 'BETWEEN', 'NOT BETWEEN')
10857: AND f.enabled_flag = 'Y'
10858: UNION ALL
10859: SELECT 'GROUP4' COMPARISION_GROUP,
10859: SELECT 'GROUP4' COMPARISION_GROUP,
10860: f.lookup_code,
10861: f.meaning
10862: FROM fnd_lookups f
10863: WHERE f.lookup_type = 'JTF_TERR_COMP_OPERATOR'
10864: AND f.lookup_code = '='
10865: AND f.enabled_flag = 'Y';
10866: CURSOR c_get_currency_code
10867: IS
10981: raise_application_error(-20001,'An error was encountered while executing the Function QualValue- '||SQLCODE||' -ERROR- '||SQLERRM);
10982: END get_qual_value;
10983:
10984: -- Package Body
10985: END JTF_TERRITORY_PVT;
10986: