[Home] [Help]
1: Package Body JTF_TERRITORY_RESOURCE_PVT AS
2: /* $Header: jtfvtrsb.pls 120.4 2006/05/24 08:13:52 chchandr ship $ */
3:
4:
5: -- ---------------------------------------------------
4:
5: -- ---------------------------------------------------
6: -- Start of Comments
7: -- ---------------------------------------------------
8: -- PACKAGE NAME: JTF_TERRITORY_RESOURCE_PVT
9: -- ---------------------------------------------------
10: -- PURPOSE
11: -- Joint task force core territory resource private api's.
12: -- This package is a private API for inserting territory
22: --
23: -- HISTORY
24: -- 07/29/99 VNEDUNGA Created
25: -- 12/22/99 NEDUNGA Making changes to confirm to
26: -- JTF_TERR_RSC_ALL table change
27: -- 01/06/00 VNEDUNGA Fixing problem with the build rule
28: -- expression
29: -- 01/16/00 VNEDUNGA Commenting out dbms_output
30: -- 01/17/00 VNEDUNGA Cahnging the the hard code value for
69:
70: -- ***************************************************
71: -- GLOBAL VARIABLES
72: -- ***************************************************
73: G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_TERRITORY_RESOURCE_PVT';
74: G_FILE_NAME CONSTANT VARCHAR2(12):='jtfvtrsb.pls';
75:
76:
77: G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
96:
97: --check if duplicate resource_id, group, role exists for this territory
98: cursor c_res is
99: Select start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
100: from JTF_TERR_RSC_ALL
101: where TERR_ID = P_TerrRsc_Rec.Terr_Id
102: --resource with same role and group assigned to this territory
103: and RESOURCE_ID = P_TerrRsc_Rec.Resource_Id
104: and ( (RESOURCE_TYPE is null and P_TerrRsc_Rec.Resource_TYPE is null) or (RESOURCE_TYPE = P_TerrRsc_Rec.Resource_TYPE))
133:
134: if l_temp = 'X' then
135: fnd_msg_pub.initialize;
136: x_return_status := FND_API.G_RET_STS_ERROR ;
137: fnd_message.set_name('JTF', 'JTF_TERR_DUPLICATE_RESOURCE');
138: FND_MSG_PUB.ADD;
139: FND_MSG_PUB.Count_And_Get( P_count => x_msg_count,
140: P_data => x_msg_data);
141: end if;
163: l_Terr_Id NUMBER;
164:
165: cursor c_res is
166: Select start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
167: from JTF_TERR_RSC_ALL
168: where TERR_ID = P_TerrRsc_Rec.Terr_Id
169: --resource with same role and group assigned to this territory
170: and RESOURCE_ID = P_TerrRsc_Rec.Resource_Id
171: and ( (RESOURCE_TYPE is null and P_TerrRsc_Rec.Resource_TYPE is null) or (RESOURCE_TYPE = P_TerrRsc_Rec.Resource_TYPE))
199:
200: if l_temp = 'X' then
201: fnd_msg_pub.initialize;
202: x_return_status := FND_API.G_RET_STS_ERROR ;
203: fnd_message.set_name('JTF', 'JTF_TERR_DUPLICATE_RESOURCE');
204: FND_MSG_PUB.ADD;
205: FND_MSG_PUB.Count_And_Get ( P_count => x_msg_count, P_data => x_msg_data);
206: end if;
207:
228: l_Terr_Id NUMBER;
229:
230: cursor c_res is
231: Select start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
232: from JTF_TERR_RSC_ALL
233: where TERR_ID = P_TerrRsc_Rec.Terr_Id
234: --resource with same role and group assigned to this territory
235: and RESOURCE_ID = P_TerrRsc_Rec.Resource_Id
236: and ( (RESOURCE_TYPE is null and P_TerrRsc_Rec.Resource_TYPE is null) or (RESOURCE_TYPE = P_TerrRsc_Rec.Resource_TYPE))
264:
265: if l_temp = 'X' then
266: fnd_msg_pub.initialize;
267: x_return_status := FND_API.G_RET_STS_ERROR ;
268: fnd_message.set_name('JTF', 'JTF_TERR_DUPLICATE_RESOURCE');
269: FND_MSG_PUB.ADD;
270: FND_MSG_PUB.Count_And_Get ( P_count => x_msg_count, P_data => x_msg_data);
271: end if;
272:
302: For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
303:
304: --check if duplicate resource_id, group, role exists for this territory
305: Select 'X' into l_temp
306: from JTF_TERR_RSC_ALL
307: where TERR_ID = p_TerrRsc_Tbl(l_res_counter).Terr_Id
308: and RESOURCE_ID = p_TerrRsc_Tbl(l_res_counter).Resource_Id
309: and RESOURCE_TYPE = p_TerrRsc_Tbl(l_res_counter).Resource_TYPE
310: and GROUP_ID = p_TerrRsc_Tbl(l_res_counter).GROUP_ID
312:
313: if l_temp = 'X'
314: then
315: x_return_status := FND_API.G_RET_STS_ERROR ;
316: fnd_message.set_name('JTF', 'JTF_TERR_DUPLICATE_RESOURCE');
317: FND_MSG_PUB.ADD;
318: FND_MSG_PUB.Count_And_Get
319: ( P_count => x_msg_count,
320: P_data => x_msg_data
366: For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
367:
368: --check if duplicate resource_id, group, role exists for this territory
369: Select 'X' into l_temp
370: from JTF_TERR_RSC_ALL
371: where TERR_ID = p_TerrRsc_Tbl(l_res_counter).Terr_Id
372: and RESOURCE_ID = p_TerrRsc_Tbl(l_res_counter).Resource_Id
373: and RESOURCE_TYPE = p_TerrRsc_Tbl(l_res_counter).Resource_TYPE
374: and GROUP_ID = p_TerrRsc_Tbl(l_res_counter).GROUP_ID
376:
377: if l_temp = 'X'
378: then
379: x_return_status := FND_API.G_RET_STS_ERROR ;
380: fnd_message.set_name('JTF', 'JTF_TERR_DUPLICATE_RESOURCE');
381: FND_MSG_PUB.ADD;
382: FND_MSG_PUB.Count_And_Get
383: ( P_count => x_msg_count,
384: P_data => x_msg_data
414: -- ***************************************************
415: -- API name : Create_TerrResource
416: -- Type : PUBLIC
417: -- Function : To create Territory Resources - which will insert
418: -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
419: -- tables.
420: --
421: -- Pre-reqs :
422: -- Parameters:
499:
500: -- Debug Message
501: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
502: THEN
503: fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
504: fnd_message.set_name ('PROC_NAME', l_api_name);
505: FND_MSG_PUB.Add;
506: END IF;
507:
624:
625: -- Debug Message
626: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
627: THEN
628: fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
629: fnd_message.set_name ('PROC_NAME', l_api_name);
630: FND_MSG_PUB.Add;
631: END IF;
632:
680: -- ***************************************************
681: -- API name : Create_TerrResource
682: -- Type : PUBLIC
683: -- Function : To create Territory Resources - which will insert
684: -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
685: -- tables.
686: --
687: -- Pre-reqs :
688: -- Parameters:
765:
766: -- Debug Message
767: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
768: THEN
769: fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
770: fnd_message.set_name ('PROC_NAME', l_api_name);
771: FND_MSG_PUB.Add;
772: END IF;
773:
890:
891: -- Debug Message
892: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
893: THEN
894: fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
895: fnd_message.set_name ('PROC_NAME', l_api_name);
896: FND_MSG_PUB.Add;
897: END IF;
898:
998:
999: --Declare cursor to get resource accesses
1000: Cursor C_GetTerrRscAccess IS
1001: Select JTRA.TERR_RSC_ACCESS_ID
1002: From JTF_TERR_RSC_ACCESS_ALL JTRA
1003: Where TERR_RSC_ID = v_TerrRsc_Id
1004: for UPDATE;
1005:
1006: l_api_name CONSTANT VARCHAR2(30) := 'Delete_Terr_Resource';
1029:
1030: -- Debug Message
1031: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1032: THEN
1033: fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1034: fnd_message.set_name ('PROC_NAME', l_api_name);
1035: FND_MSG_PUB.Add;
1036: END IF;
1037:
1071:
1072: -- Debug Message
1073: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1074: THEN
1075: fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1076: fnd_message.set_name ('PROC_NAME', l_api_name);
1077: FND_MSG_PUB.Add;
1078: END IF;
1079:
1125: -- ***************************************************
1126: -- API name : Update_TerrResource
1127: -- Type : PUBLIC
1128: -- Function : To Update Territory Resources - which will update
1129: -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
1130: -- tables.
1131: --
1132: -- Pre-reqs :
1133: -- Parameters:
1208:
1209: -- Debug Message
1210: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1211: THEN
1212: fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1213: fnd_message.set_name ('PROC_NAME', l_api_name);
1214: FND_MSG_PUB.Add;
1215: END IF;
1216:
1261:
1262: -- Debug Message
1263: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1264: THEN
1265: fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1266: fnd_message.set_name ('PROC_NAME', l_api_name);
1267: FND_MSG_PUB.Add;
1268: END IF;
1269:
1332:
1333: -- Debug Message
1334: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1335: THEN
1336: fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1337: fnd_message.set_name ('PROC_NAME', l_api_name);
1338: FND_MSG_PUB.Add;
1339: END IF;
1340:
1356: THEN
1357: -- Debug message
1358: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1359: THEN
1360: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
1361: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Rsc');
1362: FND_MSG_PUB.Add;
1363: END IF;
1364: --
1383: IF (l_TerrRsc_id = FND_API.G_MISS_NUM) THEN
1384: l_TerrRsc_id := NULL;
1385: END IF;
1386:
1387: --dbms_output.put_line('Create_Terr_Resource REC: Before Calling JTF_TERR_RSC_PKG.Insert_Row');
1388: JTF_TERR_RSC_PKG.Insert_Row(x_Rowid => l_rowid,
1389: x_TERR_RSC_ID => l_TerrRsc_Id,
1390: x_LAST_UPDATE_DATE => P_TerrRsc_Rec.LAST_UPDATE_DATE,
1391: x_LAST_UPDATED_BY => P_TerrRsc_Rec.LAST_UPDATED_BY,
1384: l_TerrRsc_id := NULL;
1385: END IF;
1386:
1387: --dbms_output.put_line('Create_Terr_Resource REC: Before Calling JTF_TERR_RSC_PKG.Insert_Row');
1388: JTF_TERR_RSC_PKG.Insert_Row(x_Rowid => l_rowid,
1389: x_TERR_RSC_ID => l_TerrRsc_Id,
1390: x_LAST_UPDATE_DATE => P_TerrRsc_Rec.LAST_UPDATE_DATE,
1391: x_LAST_UPDATED_BY => P_TerrRsc_Rec.LAST_UPDATED_BY,
1392: x_CREATION_DATE => P_TerrRsc_Rec.CREATION_DATE,
1418: X_ATTRIBUTE13 => P_TerrRsc_Rec.ATTRIBUTE13,
1419: X_ATTRIBUTE14 => P_TerrRsc_Rec.ATTRIBUTE14,
1420: X_ATTRIBUTE15 => P_TerrRsc_Rec.ATTRIBUTE15 );
1421:
1422: --dbms_output.put_line('After calling JTF_TERR_RSC_PKG.Insert_Row');
1423: -- Save the terr_usg_id and
1424: X_TerrRsc_Out_Rec.TERR_RSC_ID := l_TerrRsc_Id;
1425:
1426: -- If successful then save the success status for the record
1428:
1429: -- Debug Message
1430: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1431: THEN
1432: fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1433: fnd_message.set_name ('PROC_NAME', l_api_name);
1434: FND_MSG_PUB.Add;
1435: END IF;
1436:
1514:
1515: -- Debug Message
1516: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1517: THEN
1518: fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1519: fnd_message.set_name ('PROC_NAME', l_api_name);
1520: FND_MSG_PUB.Add;
1521: END IF;
1522:
1538: THEN
1539: -- Debug message
1540: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1541: THEN
1542: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
1543: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Rsc');
1544: FND_MSG_PUB.Add;
1545: END IF;
1546: --
1565: IF (l_TerrRsc_id = FND_API.G_MISS_NUM) THEN
1566: l_TerrRsc_id := NULL;
1567: END IF;
1568:
1569: --dbms_output.put_line('Create_Terr_Resource REC: Before Calling JTF_TERR_RSC_PKG.Insert_Row');
1570: JTF_TERR_RSC_PKG.Insert_Row(x_Rowid => l_rowid,
1571: x_TERR_RSC_ID => l_TerrRsc_Id,
1572: x_LAST_UPDATE_DATE => P_TerrRsc_Rec.LAST_UPDATE_DATE,
1573: x_LAST_UPDATED_BY => P_TerrRsc_Rec.LAST_UPDATED_BY,
1566: l_TerrRsc_id := NULL;
1567: END IF;
1568:
1569: --dbms_output.put_line('Create_Terr_Resource REC: Before Calling JTF_TERR_RSC_PKG.Insert_Row');
1570: JTF_TERR_RSC_PKG.Insert_Row(x_Rowid => l_rowid,
1571: x_TERR_RSC_ID => l_TerrRsc_Id,
1572: x_LAST_UPDATE_DATE => P_TerrRsc_Rec.LAST_UPDATE_DATE,
1573: x_LAST_UPDATED_BY => P_TerrRsc_Rec.LAST_UPDATED_BY,
1574: x_CREATION_DATE => P_TerrRsc_Rec.CREATION_DATE,
1584: X_END_DATE_ACTIVE => P_TerrRsc_Rec.END_DATE_ACTIVE,
1585: X_FULL_ACCESS_FLAG => P_TerrRsc_Rec.FULL_ACCESS_FLAG,
1586: X_ORG_ID => P_TerrRsc_Rec.ORG_ID );
1587:
1588: --dbms_output.put_line('After calling JTF_TERR_RSC_PKG.Insert_Row');
1589: -- Save the terr_usg_id and
1590: X_TerrRsc_Out_Rec.TERR_RSC_ID := l_TerrRsc_Id;
1591:
1592: -- If successful then save the success status for the record
1594:
1595: -- Debug Message
1596: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1597: THEN
1598: fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1599: fnd_message.set_name ('PROC_NAME', l_api_name);
1600: FND_MSG_PUB.Add;
1601: END IF;
1602:
1657:
1658: -- Debug Message
1659: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1660: THEN
1661: fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1662: fnd_message.set_name ('PROC_NAME', l_api_name);
1663: FND_MSG_PUB.Add;
1664: END IF;
1665:
1720:
1721: -- Debug Message
1722: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1723: THEN
1724: fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1725: fnd_message.set_name ('PROC_NAME', l_api_name);
1726: FND_MSG_PUB.Add;
1727: END IF;
1728: --dbms_output.put_line('Create_Terr_Resource TBL: Exiting API');
1785:
1786: -- Debug Message
1787: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1788: THEN
1789: fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1790: fnd_message.set_name ('PROC_NAME', l_api_name);
1791: FND_MSG_PUB.Add;
1792: END IF;
1793:
1848:
1849: -- Debug Message
1850: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1851: THEN
1852: fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1853: fnd_message.set_name ('PROC_NAME', l_api_name);
1854: FND_MSG_PUB.Add;
1855: END IF;
1856: --dbms_output.put_line('Create_Terr_Resource TBL: Exiting API');
1909:
1910: -- Debug Message
1911: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1912: THEN
1913: fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1914: fnd_message.set_name ('PROC_NAME', l_api_name);
1915: FND_MSG_PUB.Add;
1916: END IF;
1917:
1923: THEN
1924: -- Debug message
1925: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1926: THEN
1927: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
1928: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Rsc_Access');
1929: FND_MSG_PUB.Add;
1930: END IF;
1931: --
1965: IF (l_TerrRsc_Access_id = FND_API.G_MISS_NUM) THEN
1966: l_TerrRsc_Access_id := NULL;
1967: END IF;
1968:
1969: --dbms_output.put_line('Create_Resource _Access REC: Calling JTF_TERR_RSC_ACCESS_PKG.Insert_Row');
1970: JTF_TERR_RSC_ACCESS_PKG.Insert_Row(x_Rowid => l_rowid,
1971: x_TERR_RSC_ACCESS_ID => l_terrRsc_Access_id,
1972: x_LAST_UPDATE_DATE => P_TerrRsc_Access_Rec.LAST_UPDATE_DATE,
1973: x_LAST_UPDATED_BY => P_TerrRsc_Access_Rec.LAST_UPDATED_BY,
1966: l_TerrRsc_Access_id := NULL;
1967: END IF;
1968:
1969: --dbms_output.put_line('Create_Resource _Access REC: Calling JTF_TERR_RSC_ACCESS_PKG.Insert_Row');
1970: JTF_TERR_RSC_ACCESS_PKG.Insert_Row(x_Rowid => l_rowid,
1971: x_TERR_RSC_ACCESS_ID => l_terrRsc_Access_id,
1972: x_LAST_UPDATE_DATE => P_TerrRsc_Access_Rec.LAST_UPDATE_DATE,
1973: x_LAST_UPDATED_BY => P_TerrRsc_Access_Rec.LAST_UPDATED_BY,
1974: x_CREATION_DATE => P_TerrRsc_Access_Rec.CREATION_DATE,
1987:
1988: -- Debug Message
1989: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1990: THEN
1991: fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1992: fnd_message.set_name ('PROC_NAME', l_api_name);
1993: FND_MSG_PUB.Add;
1994: END IF;
1995:
2086:
2087: -- Debug Message
2088: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2089: THEN
2090: fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2091: fnd_message.set_name ('PROC_NAME', l_api_name);
2092: FND_MSG_PUB.Add;
2093: END IF;
2094:
2147:
2148: -- Debug Message
2149: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2150: THEN
2151: fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2152: fnd_message.set_name ('PROC_NAME', l_api_name);
2153: FND_MSG_PUB.Add;
2154: END IF;
2155:
2229:
2230: --Initialize the return status to success
2231: x_return_status := FND_API.G_RET_STS_SUCCESS;
2232: --
2233: JTF_TERR_RSC_ACCESS_PKG.Delete_Row(x_TERR_RSC_ACCESS_ID => P_TerrRsc_Access_Id );
2234: --
2235: --Prepare message name
2236: FND_MESSAGE.SET_NAME('JTF','TERR_RSCACCESSES_DELETED');
2237:
2360:
2361: -- Debug Message
2362: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2363: THEN
2364: fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2365: fnd_message.set_name ('PROC_NAME', l_api_name);
2366: FND_MSG_PUB.Add;
2367: END IF;
2368:
2368:
2369: --Initialize the return status to success
2370: x_return_status := FND_API.G_RET_STS_SUCCESS;
2371: --
2372: JTF_TERR_RSC_PKG.Delete_Row(x_TERR_RSC_ID => P_TerrRsc_Id);
2373: --
2374: --Prepare message name
2375: FND_MESSAGE.SET_NAME('JTF','TERR_RESOURCE_DELETED');
2376:
2387:
2388: -- Debug Message
2389: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2390: THEN
2391: fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2392: fnd_message.set_name ('PROC_NAME', l_api_name);
2393: FND_MSG_PUB.Add;
2394: END IF;
2395:
2484:
2485: Cursor C_GetTerrResource(l_TerrRsc_id Number) IS
2486: Select Rowid, TERR_RSC_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2487: LAST_UPDATE_LOGIN, TERR_ID, RESOURCE_ID, RESOURCE_TYPE, ROLE, PRIMARY_CONTACT_FLAG, ORG_ID
2488: From jtf_terr_rsc_ALL
2489: Where TERR_RSC_ID = l_TerrRsc_id
2490: FOR Update NOWAIT;
2491:
2492: --Local variable declaration
2513:
2514: --Validate the incomming record
2515: IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE) THEN
2516: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2517: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
2518: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Rsc');
2519: FND_MSG_PUB.Add;
2520: END IF;
2521:
2539: l_ref_TerrRsc_Rec.ORG_ID;
2540:
2541: If (C_GetTerrResource%NOTFOUND) Then
2542: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2543: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
2544: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
2545: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_TerrRsc_Rec.TERR_RSC_ID));
2546: FND_MSG_PUB.Add;
2547: END IF;
2540:
2541: If (C_GetTerrResource%NOTFOUND) Then
2542: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2543: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
2544: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
2545: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_TerrRsc_Rec.TERR_RSC_ID));
2546: FND_MSG_PUB.Add;
2547: END IF;
2548: raise FND_API.G_EXC_ERROR;
2548: raise FND_API.G_EXC_ERROR;
2549: End if;
2550: CLOSE C_GetTerrResource;
2551:
2552: JTF_TERR_RSC_PKG.Update_Row(x_Rowid => l_rowid,
2553: x_TERR_RSC_ID => P_TerrRsc_Rec.Terr_Rsc_Id,
2554: x_LAST_UPDATE_DATE => P_TerrRsc_Rec.LAST_UPDATE_DATE,
2555: x_LAST_UPDATED_BY => P_TerrRsc_Rec.LAST_UPDATED_BY ,
2556: x_CREATION_DATE => P_TerrRsc_Rec.CREATION_DATE,
2751: TERR_RSC_ID,
2752: ACCESS_TYPE,
2753: TRANS_ACCESS_CODE,
2754: ORG_ID
2755: From jtf_terr_rsc_access_ALL
2756: Where TERR_RSC_ACCESS_ID = l_TerrRsc_Access_id
2757: FOR Update NOWAIT;
2758:
2759: --Local variable declaration
2773: THEN
2774: -- Debug message
2775: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2776: THEN
2777: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
2778: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Rsc_Access');
2779: FND_MSG_PUB.Add;
2780: END IF;
2781: --
2811: If ( C_GetTerrResAccess%NOTFOUND) Then
2812: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2813: THEN
2814: --dbms_output.put_line('Update_Resource_Access REC: DATA-NOT-FOUND');
2815: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
2816: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC_ACCESS');
2817: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID));
2818: FND_MSG_PUB.Add;
2819: END IF;
2812: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2813: THEN
2814: --dbms_output.put_line('Update_Resource_Access REC: DATA-NOT-FOUND');
2815: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
2816: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC_ACCESS');
2817: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID));
2818: FND_MSG_PUB.Add;
2819: END IF;
2820: raise FND_API.G_EXC_ERROR;
2835: RAISE FND_API.G_EXC_ERROR;
2836: END IF;
2837:
2838: --
2839: --dbms_output.put_line('Update_Resource_Access REC: Calling JTF_TERR_RSC_ACCESS_PKG.Insert_Row');
2840: JTF_TERR_RSC_ACCESS_PKG.Update_Row(x_Rowid => l_rowid,
2841: x_TERR_RSC_ACCESS_ID => P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID,
2842: x_LAST_UPDATE_DATE => P_TerrRsc_Access_Rec.LAST_UPDATE_DATE,
2843: x_LAST_UPDATED_BY => P_TerrRsc_Access_Rec.LAST_UPDATED_BY,
2836: END IF;
2837:
2838: --
2839: --dbms_output.put_line('Update_Resource_Access REC: Calling JTF_TERR_RSC_ACCESS_PKG.Insert_Row');
2840: JTF_TERR_RSC_ACCESS_PKG.Update_Row(x_Rowid => l_rowid,
2841: x_TERR_RSC_ACCESS_ID => P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID,
2842: x_LAST_UPDATE_DATE => P_TerrRsc_Access_Rec.LAST_UPDATE_DATE,
2843: x_LAST_UPDATED_BY => P_TerrRsc_Access_Rec.LAST_UPDATED_BY,
2844: x_CREATION_DATE => P_TerrRsc_Access_Rec.CREATION_DATE,
3018: l_Terr_Id := p_TerrRsc_Tbl(l_res_counter).Terr_Id;
3019: --
3020: --dbms_output.put_line('Inside the for loop. Before validating Terr_Id');
3021: Select 'X' into l_temp
3022: from JTF_TERR
3023: where TERR_ID = p_TerrRsc_Tbl(l_res_counter).Terr_Id;
3024: --
3025: --
3026: End LOOP;
3029: --
3030: WHEN NO_DATA_FOUND THEN
3031: --dbms_output.put_line('Validate_Foreign_Keys: NO_DATA_FOUND');
3032: x_return_status := FND_API.G_RET_STS_ERROR ;
3033: fnd_message.set_name('JTF', 'JTF_TERR_INVALID_TERRITORY');
3034: fnd_message.Set_Token('TERR_ID', to_char(l_Terr_Id) );
3035: FND_MSG_PUB.ADD;
3036: FND_MSG_PUB.Count_And_Get
3037: ( P_count => x_msg_count,
3104: l_Terr_Id := p_TerrRsc_Tbl(l_res_counter).Terr_Id;
3105: --
3106: --dbms_output.put_line('Inside the for loop. Before validating Terr_Id');
3107: Select 'X' into l_temp
3108: from JTF_TERR
3109: where TERR_ID = p_TerrRsc_Tbl(l_res_counter).Terr_Id;
3110: --
3111: --
3112: End LOOP;
3115: --
3116: WHEN NO_DATA_FOUND THEN
3117: --dbms_output.put_line('Validate_Foreign_Keys: NO_DATA_FOUND');
3118: x_return_status := FND_API.G_RET_STS_ERROR ;
3119: fnd_message.set_name('JTF', 'JTF_TERR_INVALID_TERRITORY');
3120: fnd_message.Set_Token('TERR_ID', to_char(l_Terr_Id) );
3121: FND_MSG_PUB.ADD;
3122: FND_MSG_PUB.Count_And_Get
3123: ( P_count => x_msg_count,
3166:
3167: -- Cannot have more than one Primary flag
3168: If l_Primary_Count > 1 Then
3169: --
3170: fnd_message.set_name('JTF', 'JTF_TERR_MULTIPLE_PRIMARY_FLAG');
3171: FND_MSG_PUB.ADD;
3172: raise FND_API.G_EXC_ERROR;
3173: --
3174: End If;
3261:
3262: -- Cannot have more than one Primary flag
3263: If l_Primary_Count > 1 Then
3264: --
3265: fnd_message.set_name('JTF', 'JTF_TERR_MULTIPLE_PRIMARY_FLAG');
3266: FND_MSG_PUB.ADD;
3267: raise FND_API.G_EXC_ERROR;
3268: --
3269: End If;
3357:
3358: -- If the territory Usage records is missing
3359: If (p_TerrRsc_Tbl.count = 0 ) Then
3360: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3361: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERRRES_REC');
3362: FND_MSG_PUB.ADD;
3363: END IF;
3364: x_return_status := FND_API.G_RET_STS_ERROR;
3365: raise FND_API.G_EXC_ERROR;
3481:
3482: -- If the territory Usage records is missing
3483: If (p_TerrRsc_Tbl.count = 0 ) Then
3484: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3485: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERRRES_REC');
3486: FND_MSG_PUB.ADD;
3487: END IF;
3488: x_return_status := FND_API.G_RET_STS_ERROR;
3489: raise FND_API.G_EXC_ERROR;
3569: -- Validate the territory Id
3570: l_Validate_id := p_TerrRsc_Rec.Terr_Id;
3571: If l_Validate_id IS NOT NULL Then
3572: -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
3573: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR') <> FND_API.G_TRUE Then
3574: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3575: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3576: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3577: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3572: -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
3573: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR') <> FND_API.G_TRUE Then
3574: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3575: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3576: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3577: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3578: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
3579: FND_MSG_PUB.ADD;
3580: END IF;
3573: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR') <> FND_API.G_TRUE Then
3574: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3575: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3576: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3577: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3578: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
3579: FND_MSG_PUB.ADD;
3580: END IF;
3581: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3581: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3582: End If;
3583: Else
3584: -- Invalid Territory Id specified
3585: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3586: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3587: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
3588: FND_MSG_PUB.ADD;
3589: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3582: End If;
3583: Else
3584: -- Invalid Territory Id specified
3585: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3586: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3587: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
3588: FND_MSG_PUB.ADD;
3589: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3590: End If;
3592: --Check last update login
3593: If ( p_TerrRsc_Rec.RESOURCE_TYPE is NULL OR
3594: p_TerrRsc_Rec.RESOURCE_TYPE = FND_API.G_MISS_CHAR ) THEN
3595: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3596: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3597: FND_MESSAGE.Set_Token('COL_NAME', 'RESOURCE_TYPE' );
3598: FND_MSG_PUB.ADD;
3599: END IF;
3600: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3603: --Check created by
3604: IF ( p_TerrRsc_Rec.CREATED_BY is NULL OR
3605: p_TerrRsc_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
3606: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3607: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3608: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
3609: FND_MSG_PUB.ADD;
3610: END IF;
3611: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3614: --Check creation date
3615: If ( p_TerrRsc_Rec.CREATION_DATE is NULL OR
3616: p_TerrRsc_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
3617: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3618: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3619: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
3620: FND_MSG_PUB.ADD;
3621: END IF;
3622: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3625: -- Validate last updated by
3626: IF ( p_TerrRsc_Rec.LAST_UPDATED_BY is NULL OR
3627: p_TerrRsc_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
3628: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3629: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3630: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
3631: FND_MSG_PUB.ADD;
3632: END IF;
3633: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3636: -- Check last update date
3637: If ( p_TerrRsc_Rec.LAST_UPDATE_DATE IS NULL OR
3638: p_TerrRsc_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
3639: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3640: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3641: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
3642: FND_MSG_PUB.ADD;
3643: END IF;
3644: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3647: --Check last update login
3648: If ( p_TerrRsc_Rec.LAST_UPDATE_LOGIN is NULL OR
3649: p_TerrRsc_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
3650: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3651: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3652: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
3653: FND_MSG_PUB.ADD;
3654: END IF;
3655: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3719: -- Validate the territory Id
3720: l_Validate_id := p_TerrRsc_Rec.Terr_Id;
3721: If l_Validate_id IS NOT NULL Then
3722: -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
3723: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR') <> FND_API.G_TRUE Then
3724: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3725: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3726: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3727: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3722: -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
3723: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR') <> FND_API.G_TRUE Then
3724: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3725: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3726: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3727: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3728: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
3729: FND_MSG_PUB.ADD;
3730: END IF;
3723: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR') <> FND_API.G_TRUE Then
3724: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3725: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3726: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3727: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3728: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
3729: FND_MSG_PUB.ADD;
3730: END IF;
3731: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3731: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3732: End If;
3733: Else
3734: -- Invalid Territory Id specified
3735: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3736: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3737: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
3738: FND_MSG_PUB.ADD;
3739: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3732: End If;
3733: Else
3734: -- Invalid Territory Id specified
3735: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3736: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3737: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
3738: FND_MSG_PUB.ADD;
3739: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3740: End If;
3742: --Check last update login
3743: If ( p_TerrRsc_Rec.RESOURCE_TYPE is NULL OR
3744: p_TerrRsc_Rec.RESOURCE_TYPE = FND_API.G_MISS_CHAR ) THEN
3745: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3746: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3747: FND_MESSAGE.Set_Token('COL_NAME', 'RESOURCE_TYPE' );
3748: FND_MSG_PUB.ADD;
3749: END IF;
3750: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3753: --Check created by
3754: IF ( p_TerrRsc_Rec.CREATED_BY is NULL OR
3755: p_TerrRsc_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
3756: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3757: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3758: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
3759: FND_MSG_PUB.ADD;
3760: END IF;
3761: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3764: --Check creation date
3765: If ( p_TerrRsc_Rec.CREATION_DATE is NULL OR
3766: p_TerrRsc_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
3767: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3768: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3769: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
3770: FND_MSG_PUB.ADD;
3771: END IF;
3772: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3775: -- Validate last updated by
3776: IF ( p_TerrRsc_Rec.LAST_UPDATED_BY is NULL OR
3777: p_TerrRsc_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
3778: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3779: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3780: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
3781: FND_MSG_PUB.ADD;
3782: END IF;
3783: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3786: -- Check last update date
3787: If ( p_TerrRsc_Rec.LAST_UPDATE_DATE IS NULL OR
3788: p_TerrRsc_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
3789: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3790: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3791: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
3792: FND_MSG_PUB.ADD;
3793: END IF;
3794: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3797: --Check last update login
3798: If ( p_TerrRsc_Rec.LAST_UPDATE_LOGIN is NULL OR
3799: p_TerrRsc_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
3800: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3801: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3802: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
3803: FND_MSG_PUB.ADD;
3804: END IF;
3805: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3873: -- Validate the territory Id
3874: l_Validate_id := p_TerrRsc_Id;
3875: If l_Validate_id IS NOT NULL Then
3876: -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
3877: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_RSC_ID', 'JTF_TERR_RSC') <> FND_API.G_TRUE Then
3878: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3879: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3880: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3881: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
3876: -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
3877: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_RSC_ID', 'JTF_TERR_RSC') <> FND_API.G_TRUE Then
3878: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3879: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3880: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3881: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
3882: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_RSC_ID');
3883: FND_MSG_PUB.ADD;
3884: END IF;
3877: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_RSC_ID', 'JTF_TERR_RSC') <> FND_API.G_TRUE Then
3878: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3879: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3880: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3881: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
3882: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_RSC_ID');
3883: FND_MSG_PUB.ADD;
3884: END IF;
3885: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3885: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3886: End If;
3887: Else
3888: -- Invalid Territory Id specified
3889: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3890: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
3891: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_RSC_ID');
3892: FND_MSG_PUB.ADD;
3893: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3886: End If;
3887: Else
3888: -- Invalid Territory Id specified
3889: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3890: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
3891: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_RSC_ID');
3892: FND_MSG_PUB.ADD;
3893: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3894: End If;
3901:
3902: EXCEPTION
3903: WHEN NO_DATA_FOUND THEN
3904: -- Invalid Territory Id specified
3905: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3906: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_QUAL_TYPES');
3907: FND_MESSAGE.Set_Token('COLUMN_NAME', 'ACCESS_TYPE');
3908: FND_MSG_PUB.ADD;
3909: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3912: --Check created by
3913: IF ( p_TerrRsc_Access_Rec.CREATED_BY is NULL OR
3914: p_TerrRsc_Access_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
3915: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3916: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3917: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
3918: FND_MSG_PUB.ADD;
3919: END IF;
3920: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3923: --Check creation date
3924: If ( p_TerrRsc_Access_Rec.CREATION_DATE is NULL OR
3925: p_TerrRsc_Access_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
3926: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3927: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3928: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
3929: FND_MSG_PUB.ADD;
3930: END IF;
3931: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3934: -- Validate last updated by
3935: IF ( p_TerrRsc_Access_Rec.LAST_UPDATED_BY is NULL OR
3936: p_TerrRsc_Access_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
3937: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3938: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3939: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
3940: FND_MSG_PUB.ADD;
3941: END IF;
3942: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3945: -- Check last update date
3946: If ( p_TerrRsc_Access_Rec.LAST_UPDATE_DATE IS NULL OR
3947: p_TerrRsc_Access_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
3948: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3949: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3950: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
3951: FND_MSG_PUB.ADD;
3952: END IF;
3953: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3956: --Check last update login
3957: If ( p_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN is NULL OR
3958: p_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
3959: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3960: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3961: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
3962: FND_MSG_PUB.ADD;
3963: END IF;
3964: x_Return_Status := FND_API.G_RET_STS_ERROR ;
4011: v_Terr_Qual_Id number;
4012:
4013: CURSOR c_Terr_ResQual IS
4014: SELECT JTQ.TERR_QUAL_ID
4015: FROM jtf_seeded_qual_usgs_v jsquv, JTF_TERR_QUAL JTQ
4016: WHERE JTQ.terr_id = p_Terr_id and
4017: JTQ.qual_usg_id = jsquv.qual_usg_id and
4018: jsquv.qual_type_id = -1001 and
4019: jsquv.qual_type_id in ( select related_id
4027: jtv.low_value_number, jtv.high_value_number,
4028: jtv.INTEREST_TYPE_ID, jtv.PRIMARY_INTEREST_CODE_ID,
4029: jtv.SECONDARY_INTEREST_CODE_ID, jsquv.DISPLAY_TYPE, jsquv.CONVERT_TO_ID_FLAG,
4030: jtv.ID_USED_FLAG, jtv.CURRENCY_CODE, jtv.LOW_VALUE_CHAR_ID
4031: from jtf_seeded_qual_usgs_v jsquv, jtf_terr_values jtv, jtf_terr_qual jtq
4032: where jtv.terr_qual_id = v_Terr_Qual_Id and
4033: jtv.terr_qual_id = jtq.terr_qual_id and
4034: jtq.qual_usg_id = jsquv.qual_usg_id;
4035:
4035:
4036: Type t_Pkgname IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
4037: l_Expr_Tbl t_Pkgname;
4038: l_Expr_Pointer NUMBER := 0;
4039: l_Record JTF_TERR_ENGINE_GEN_PVT.Terr_Value_Rec_Type;
4040:
4041: v_Expression VARCHAR2(5000);
4042: v_Temp VARCHAR2(256);
4043: v_State BOOLEAN;
4083: -- --dbms_output.put_line( '[1] Inside the VALUES loop - '|| l_Record.DISPLAY_TYPE);
4084: -- Do all the special processing for for interest Category/Primary Intererst/ Secondary types
4085: IF l_Record.DISPLAY_TYPE = 'INTEREST_TYPE' Then
4086: --dbms_output.put_line( 'Inside first if interest_type');
4087: v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_Interest_Type(l_Record => l_record);
4088: -- This display type is only for resource qualifer (competence/competence level)
4089: ELSIf l_Record.DISPLAY_TYPE = 'COMPETENCE' Then
4090: --dbms_output.put_line( 'Inside first if competence');
4091: v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_COMPETENCE(l_Record => l_record);
4087: v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_Interest_Type(l_Record => l_record);
4088: -- This display type is only for resource qualifer (competence/competence level)
4089: ELSIf l_Record.DISPLAY_TYPE = 'COMPETENCE' Then
4090: --dbms_output.put_line( 'Inside first if competence');
4091: v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_COMPETENCE(l_Record => l_record);
4092: ELSIf l_Record.DISPLAY_TYPE = 'NUMERIC' Then
4093: --dbms_output.put_line( 'Inside char number if');
4094: v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_NUMERIC(l_Record => l_record);
4095: ELSIf l_Record.DISPLAY_TYPE like 'CHAR' Then
4090: --dbms_output.put_line( 'Inside first if competence');
4091: v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_COMPETENCE(l_Record => l_record);
4092: ELSIf l_Record.DISPLAY_TYPE = 'NUMERIC' Then
4093: --dbms_output.put_line( 'Inside char number if');
4094: v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_NUMERIC(l_Record => l_record);
4095: ELSIf l_Record.DISPLAY_TYPE like 'CHAR' Then
4096: --dbms_output.put_line( 'Inside char number if');
4097: v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_CHAR(l_Record => l_record);
4098: ELSIf l_Record.DISPLAY_TYPE = 'SPECIAL_FUNCTION' Then
4093: --dbms_output.put_line( 'Inside char number if');
4094: v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_NUMERIC(l_Record => l_record);
4095: ELSIf l_Record.DISPLAY_TYPE like 'CHAR' Then
4096: --dbms_output.put_line( 'Inside char number if');
4097: v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_CHAR(l_Record => l_record);
4098: ELSIf l_Record.DISPLAY_TYPE = 'SPECIAL_FUNCTION' Then
4099: -- Need to add this
4100: null;
4101: ELSIF l_Record.DISPLAY_TYPE = 'CURRENCY' Then
4098: ELSIf l_Record.DISPLAY_TYPE = 'SPECIAL_FUNCTION' Then
4099: -- Need to add this
4100: null;
4101: ELSIF l_Record.DISPLAY_TYPE = 'CURRENCY' Then
4102: v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_CURRENCY(l_Record => l_record);
4103: End If;
4104: --
4105: l_Expr_Tbl(l_Expr_Pointer) := v_Temp;
4106:
4135:
4136:
4137: FUNCTION Get_Expression_Interest_Type
4138: (
4139: l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
4140: ) RETURN VARCHAR2
4141: AS
4142: l_Expression VARCHAR2(1000);
4143:
4176: -- NUMERIC Display Type
4177:
4178: FUNCTION Get_Expression_NUMERIC
4179: (
4180: l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
4181: ) RETURN VARCHAR2
4182: AS
4183: l_Expression VARCHAR2(1000);
4184:
4209: -- CURRENCY Display Type
4210:
4211: FUNCTION Get_Expression_CURRENCY
4212: (
4213: l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
4214: ) RETURN VARCHAR2
4215: AS
4216: l_Expression VARCHAR2(1000);
4217:
4242: -- VARCHAR2 Display Type
4243:
4244: FUNCTION Get_Expression_CHAR
4245: (
4246: l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
4247: ) RETURN VARCHAR2
4248: AS
4249: l_Expression VARCHAR2(1000);
4250:
4297:
4298:
4299: FUNCTION Get_Expression_Competence
4300: (
4301: l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
4302: ) RETURN VARCHAR2
4303: AS
4304: l_Expression VARCHAR2(1000);
4305:
4331:
4332:
4333:
4334:
4335: -- Function used in JTF_TERR_RESOURCES_V to return
4336: -- the group_name for the group_id of a resource
4337:
4338: FUNCTION get_group_name
4339: (
4524: start_date_active,
4525: end_date_active,
4526: full_access_flag,
4527: org_id
4528: FROM jtf_terr_rsc_ALL
4529: WHERE terr_id = lp_terr_id;
4530:
4531:
4532: CURSOR csr_rsc_access_all (lp_terr_rsc_id NUMBER) IS
4539: last_update_login,
4540: --terr_rsc_id,
4541: access_type,
4542: org_id
4543: FROM jtf_terr_rsc_access_ALL
4544: WHERE terr_rsc_id = lp_terr_rsc_id;
4545:
4546:
4547: BEGIN
4569:
4570: -- Debug Message
4571: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4572: THEN
4573: fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
4574: fnd_message.set_name ('PROC_NAME', l_api_name);
4575: FND_MSG_PUB.Add;
4576: END IF;
4577:
4604: l_TerrRsc_rec.terr_id := p_dest_terr_id;
4605:
4606: EXIT WHEN csr_rsc_all%NOTFOUND;
4607:
4608: JTF_TERRITORY_RESOURCE_PVT.Create_Terr_Resource
4609: (
4610: p_TerrRsc_Rec => l_TerrRsc_rec,
4611: p_Api_Version_Number => l_api_version_number,
4612: p_Init_Msg_List => NULL,
4651: EXIT WHEN csr_rsc_access_all%NOTFOUND;
4652:
4653: --dbms_output.put_line('Terr Resource ID : '||l_TerrRsc_Access_rec.terr_rsc_id);
4654:
4655: JTF_TERRITORY_RESOURCE_PVT.Create_Resource_Access
4656: (
4657: p_TerrRsc_Id => l_dest_terr_rsc_id,
4658: p_TerrRsc_Access_Rec => l_TerrRsc_Access_rec,
4659: p_Api_Version_Number => l_api_version_number,
4731:
4732:
4733:
4734: /* procedure to check that UK constraint is not
4735: ** being violated on JTF_TERR_RSC_ALL table
4736: ** -- jdochert 09/19
4737: */
4738: PROCEDURE validate_terr_rsc_access_UK(
4739: p_Terr_Rsc_Id IN NUMBER,
4747: -- cursor to check that Unique Key constraint not violated
4748: CURSOR csr_chk_uk_violation ( lp_terr_rsc_id NUMBER
4749: , lp_access_type VARCHAR2) IS
4750: SELECT 'X'
4751: FROM JTF_TERR_RSC_ACCESS_ALL
4752: WHERE terr_rsc_id = lp_terr_rsc_id
4753: AND access_type = lp_access_type;
4754:
4755: l_return_csr VARCHAR2(1);
4775:
4776: /* Debug message */
4777: --arpatel bug#1500581 (part of fix)
4778: --IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4779: FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_RSC_ACCESS_UK_CON');
4780: --FND_MESSAGE.Set_Token ('TABLE', 'JTF_TERR_RSC_ACCESS_ALL');
4781: FND_MSG_PUB.ADD;
4782: --END IF;
4783:
4776: /* Debug message */
4777: --arpatel bug#1500581 (part of fix)
4778: --IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4779: FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_RSC_ACCESS_UK_CON');
4780: --FND_MESSAGE.Set_Token ('TABLE', 'JTF_TERR_RSC_ACCESS_ALL');
4781: FND_MSG_PUB.ADD;
4782: --END IF;
4783:
4784: END IF; /* c_chk_uk_violation%FOUND */
4793:
4794: WHEN OTHERS THEN
4795: --dbms_output('Validate_Foreign_Key: Others exception' || SQLERRM);
4796: X_return_status := FND_API.G_RET_STS_ERROR;
4797: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
4798: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Unique_Key' );
4799: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
4800: FND_MSG_PUB.ADD;
4801: FND_MSG_PUB.Count_And_Get
4867: l_terr_ids_tbl Terr_Ids_Tbl_Type;
4868:
4869: CURSOR csr_terr_rsc(l_resource_id NUMBER, l_resource_type VARCHAR) IS
4870: select j.terr_id
4871: from jtf_terr_rsc_ALL j, jtf_terr_ALL jt
4872: where j.resource_id = l_resource_id
4873: and j.resource_type = l_resource_type
4874: and j.terr_id = jt.terr_id
4875: and jt.template_flag = 'N'
4879: and not jt.terr_id = 1;
4880:
4881: CURSOR csr_unassigned_terrs IS
4882: select terr_id
4883: from JTF_TERR_ALL jt
4884: where NOT EXISTS (select jtr.terr_id
4885: from jtf_terr_rsc_ALL jtr
4886: where jt.terr_id = jtr.terr_id
4887: )
4881: CURSOR csr_unassigned_terrs IS
4882: select terr_id
4883: from JTF_TERR_ALL jt
4884: where NOT EXISTS (select jtr.terr_id
4885: from jtf_terr_rsc_ALL jtr
4886: where jt.terr_id = jtr.terr_id
4887: )
4888: and jt.template_flag = 'N'
4889: and jt.escalation_territory_flag = 'N'
4915:
4916: -- Debug Message
4917: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4918: THEN
4919: fnd_message.set_name ('JTF', 'JTF_TERRITORY_START_MSG');
4920: fnd_message.set_name ('PROC_NAME', l_api_name);
4921: FND_MSG_PUB.Add;
4922: END IF;
4923:
4947:
4948: IF p_add_flag = 'Y'
4949: THEN
4950: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4951: INSERT INTO JTF_TERR_RSC_ALL(
4952: TERR_RSC_ID,
4953: LAST_UPDATE_DATE,
4954: LAST_UPDATED_BY,
4955: CREATION_DATE,
4965: END_DATE_ACTIVE,
4966: FULL_ACCESS_FLAG,
4967: ORG_ID
4968: ) VALUES (
4969: JTF_TERR_RSC_s.nextval,
4970: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4971: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4972: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4973: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4990: IF p_delete_flag = 'Y'
4991: THEN
4992:
4993: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4994: INSERT INTO JTF_TERR_RSC_ALL(
4995: TERR_RSC_ID,
4996: LAST_UPDATE_DATE,
4997: LAST_UPDATED_BY,
4998: CREATION_DATE,
5008: END_DATE_ACTIVE,
5009: FULL_ACCESS_FLAG,
5010: ORG_ID
5011: ) VALUES (
5012: JTF_TERR_RSC_s.nextval,
5013: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
5014: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
5015: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
5016: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
5028: );
5029:
5030: --ARPATEL: 11/06/2003 BUG#2798581 START
5031: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5032: INSERT INTO JTF_TERR_RSC_ACCESS_ALL(
5033: TERR_RSC_ACCESS_ID,
5034: LAST_UPDATE_DATE,
5035: LAST_UPDATED_BY,
5036: CREATION_DATE,
5040: ACCESS_TYPE,
5041: ORG_ID
5042: )
5043: SELECT
5044: JTF_TERR_RSC_ACCESS_s.nextval,
5045: SYSDATE,
5046: G_USER_ID,
5047: SYSDATE,
5048: G_USER_ID,
5050: ntra.terr_rsc_id, -- needs to be the newly created terr_rsc_id from above
5051: raa.access_type,
5052: p_dest_resource_rec.ORG_ID
5053: FROM
5054: JTF_TERR_RSC_ACCESS_ALL raa
5055: ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
5056: ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
5057: WHERE
5058: tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5051: raa.access_type,
5052: p_dest_resource_rec.ORG_ID
5053: FROM
5054: JTF_TERR_RSC_ACCESS_ALL raa
5055: ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
5056: ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
5057: WHERE
5058: tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5059: AND tra.resource_id = p_source_resource_rec.resource_id
5052: p_dest_resource_rec.ORG_ID
5053: FROM
5054: JTF_TERR_RSC_ACCESS_ALL raa
5055: ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
5056: ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
5057: WHERE
5058: tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5059: AND tra.resource_id = p_source_resource_rec.resource_id
5060: AND ntra.terr_id = tra.terr_id
5064:
5065: --Do all the deleting of old records at the end
5066:
5067: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5068: DELETE from jtf_terr_rsc_ALL
5069: where terr_id = l_terr_ids_tbl(i)
5070: and resource_id = p_source_resource_rec.resource_id;
5071:
5072:
5076:
5077: ELSE
5078:
5079: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5080: INSERT INTO JTF_TERR_RSC_ALL(
5081: TERR_RSC_ID,
5082: LAST_UPDATE_DATE,
5083: LAST_UPDATED_BY,
5084: CREATION_DATE,
5094: END_DATE_ACTIVE,
5095: FULL_ACCESS_FLAG,
5096: ORG_ID
5097: ) VALUES (
5098: JTF_TERR_RSC_s.nextval,
5099: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
5100: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
5101: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
5102: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
5114: );
5115:
5116: --ARPATEL: 11/06/2003 BUG#2798581 START
5117: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5118: INSERT INTO JTF_TERR_RSC_ACCESS_ALL(
5119: TERR_RSC_ACCESS_ID,
5120: LAST_UPDATE_DATE,
5121: LAST_UPDATED_BY,
5122: CREATION_DATE,
5126: ACCESS_TYPE,
5127: ORG_ID
5128: )
5129: SELECT
5130: JTF_TERR_RSC_ACCESS_s.nextval,
5131: SYSDATE,
5132: G_USER_ID,
5133: SYSDATE,
5134: G_USER_ID,
5136: ntra.terr_rsc_id, -- needs to be the newly created terr_rsc_id from above
5137: raa.access_type,
5138: p_dest_resource_rec.ORG_ID
5139: FROM
5140: JTF_TERR_RSC_ACCESS_ALL raa
5141: ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
5142: ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
5143: WHERE
5144: tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5137: raa.access_type,
5138: p_dest_resource_rec.ORG_ID
5139: FROM
5140: JTF_TERR_RSC_ACCESS_ALL raa
5141: ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
5142: ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
5143: WHERE
5144: tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5145: AND tra.resource_id = p_source_resource_rec.resource_id
5138: p_dest_resource_rec.ORG_ID
5139: FROM
5140: JTF_TERR_RSC_ACCESS_ALL raa
5141: ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
5142: ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
5143: WHERE
5144: tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5145: AND tra.resource_id = p_source_resource_rec.resource_id
5146: AND ntra.terr_id = tra.terr_id
5149: ;
5150:
5151: --UPDATE old rsc to soft delete - end date
5152: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5153: UPDATE jtf_terr_rsc_all j
5154: SET j.end_date_active = SYSDATE
5155: WHERE j.resource_id = p_source_resource_rec.RESOURCE_ID
5156: AND j.resource_type = p_source_resource_rec.RESOURCE_TYPE
5157: AND j.terr_id = l_terr_ids_tbl(i);
5168:
5169: -- Debug Message
5170: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5171: THEN
5172: fnd_message.set_name ('JTF', 'JTF_TERRITORY_END_MSG');
5173: fnd_message.set_name ('PROC_NAME', l_api_name);
5174: FND_MSG_PUB.Add;
5175: END IF;
5176:
5218: END IF;
5219:
5220: END Transfer_Resource_Territories;
5221:
5222: END JTF_TERRITORY_RESOURCE_PVT;