[Home] [Help]
101: from JTF_TERR_RSC_ALL JTR1, JTF_TERR_RSC_ALL JTR2
102: where JTR2.TERR_ID = p_Terr_Id
103: AND JTR1.TERR_RSC_ID = P_TerrRsc_Rec.Terr_Rsc_Id
104: --resource with same role and group assigned to this territory
105: AND JTR2.RESOURCE_ID = decode(P_TerrRsc_Rec.Resource_Id, FND_API.G_MISS_NUM, JTR1.RESOURCE_ID, P_TerrRsc_Rec.Resource_Id)
106: AND JTR2.RESOURCE_TYPE = decode(P_TerrRsc_Rec.Resource_TYPE , FND_API.G_MISS_CHAR, JTR1.RESOURCE_TYPE, P_TerrRsc_Rec.Resource_TYPE)
107: AND JTR2.GROUP_ID = decode( P_TerrRsc_Rec.GROUP_ID , FND_API.G_MISS_NUM,JTR1.GROUP_ID,P_TerrRsc_Rec.GROUP_ID )
108: AND JTR2.ROLE = decode(P_TerrRsc_Rec.ROLE, FND_API.G_MISS_CHAR, JTR1.ROLE, P_TerrRsc_Rec.ROLE )
109: AND JTR2.TERR_RSC_ID <> P_TerrRsc_Rec.Terr_Rsc_Id;
102: where JTR2.TERR_ID = p_Terr_Id
103: AND JTR1.TERR_RSC_ID = P_TerrRsc_Rec.Terr_Rsc_Id
104: --resource with same role and group assigned to this territory
105: AND JTR2.RESOURCE_ID = decode(P_TerrRsc_Rec.Resource_Id, FND_API.G_MISS_NUM, JTR1.RESOURCE_ID, P_TerrRsc_Rec.Resource_Id)
106: AND JTR2.RESOURCE_TYPE = decode(P_TerrRsc_Rec.Resource_TYPE , FND_API.G_MISS_CHAR, JTR1.RESOURCE_TYPE, P_TerrRsc_Rec.Resource_TYPE)
107: AND JTR2.GROUP_ID = decode( P_TerrRsc_Rec.GROUP_ID , FND_API.G_MISS_NUM,JTR1.GROUP_ID,P_TerrRsc_Rec.GROUP_ID )
108: AND JTR2.ROLE = decode(P_TerrRsc_Rec.ROLE, FND_API.G_MISS_CHAR, JTR1.ROLE, P_TerrRsc_Rec.ROLE )
109: AND JTR2.TERR_RSC_ID <> P_TerrRsc_Rec.Terr_Rsc_Id;
110:
103: AND JTR1.TERR_RSC_ID = P_TerrRsc_Rec.Terr_Rsc_Id
104: --resource with same role and group assigned to this territory
105: AND JTR2.RESOURCE_ID = decode(P_TerrRsc_Rec.Resource_Id, FND_API.G_MISS_NUM, JTR1.RESOURCE_ID, P_TerrRsc_Rec.Resource_Id)
106: AND JTR2.RESOURCE_TYPE = decode(P_TerrRsc_Rec.Resource_TYPE , FND_API.G_MISS_CHAR, JTR1.RESOURCE_TYPE, P_TerrRsc_Rec.Resource_TYPE)
107: AND JTR2.GROUP_ID = decode( P_TerrRsc_Rec.GROUP_ID , FND_API.G_MISS_NUM,JTR1.GROUP_ID,P_TerrRsc_Rec.GROUP_ID )
108: AND JTR2.ROLE = decode(P_TerrRsc_Rec.ROLE, FND_API.G_MISS_CHAR, JTR1.ROLE, P_TerrRsc_Rec.ROLE )
109: AND JTR2.TERR_RSC_ID <> P_TerrRsc_Rec.Terr_Rsc_Id;
110:
111: BEGIN
104: --resource with same role and group assigned to this territory
105: AND JTR2.RESOURCE_ID = decode(P_TerrRsc_Rec.Resource_Id, FND_API.G_MISS_NUM, JTR1.RESOURCE_ID, P_TerrRsc_Rec.Resource_Id)
106: AND JTR2.RESOURCE_TYPE = decode(P_TerrRsc_Rec.Resource_TYPE , FND_API.G_MISS_CHAR, JTR1.RESOURCE_TYPE, P_TerrRsc_Rec.Resource_TYPE)
107: AND JTR2.GROUP_ID = decode( P_TerrRsc_Rec.GROUP_ID , FND_API.G_MISS_NUM,JTR1.GROUP_ID,P_TerrRsc_Rec.GROUP_ID )
108: AND JTR2.ROLE = decode(P_TerrRsc_Rec.ROLE, FND_API.G_MISS_CHAR, JTR1.ROLE, P_TerrRsc_Rec.ROLE )
109: AND JTR2.TERR_RSC_ID <> P_TerrRsc_Rec.Terr_Rsc_Id;
110:
111: BEGIN
112:
110:
111: BEGIN
112:
113: -- Initialize API return status to success
114: x_return_status := FND_API.G_RET_STS_SUCCESS;
115:
116: --Get the missing values from the database to check the duplicate resource.
117: BEGIN
118: SELECT terr_id, start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
119: INTO l_terr_id,l_start_date_active, l_end_date_active
120: FROM JTF_TERR_RSC_ALL
121: WHERE TERR_RSC_ID = P_TerrRsc_Rec.Terr_Rsc_Id;
122:
123: IF ( P_TerrRsc_Rec.START_DATE_ACTIVE IS NOT NULL AND P_TerrRsc_Rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE ) THEN
124: l_start_date_active := P_TerrRsc_Rec.START_DATE_ACTIVE;
125: END IF;
126: -- Else use the date from Database
127:
124: l_start_date_active := P_TerrRsc_Rec.START_DATE_ACTIVE;
125: END IF;
126: -- Else use the date from Database
127:
128: IF ( P_TerrRsc_Rec.END_DATE_ACTIVE IS NOT NULL AND P_TerrRsc_Rec.END_DATE_ACTIVE <> FND_API.G_MISS_DATE ) THEN
129: l_end_date_active := P_TerrRsc_Rec.END_DATE_ACTIVE;
130: END IF;
131: -- Else use the date from Database
132:
129: l_end_date_active := P_TerrRsc_Rec.END_DATE_ACTIVE;
130: END IF;
131: -- Else use the date from Database
132:
133: IF ( P_TerrRsc_Rec.TERR_ID IS NOT NULL AND P_TerrRsc_Rec.TERR_ID <> FND_API.G_MISS_NUM ) THEN
134: l_Terr_Id := P_TerrRsc_Rec.TERR_ID;
135: END IF;
136: -- Else use the date from Database
137:
136: -- Else use the date from Database
137:
138: EXCEPTION
139: WHEN NO_DATA_FOUND THEN
140: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
141: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
142: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'NO_DATA_FOUND Exception in Chack Duplicate2_update procedure : ' || SQLERRM);
143: END IF;
144: END;
172: END LOOP;
173:
174: if l_temp = 'X' then
175: fnd_msg_pub.initialize;
176: x_return_status := FND_API.G_RET_STS_ERROR ;
177: fnd_message.set_name('JTF', 'JTF_TERR_DUPLICATE_RESOURCE');
178: FND_MSG_PUB.ADD;
179: FND_MSG_PUB.Count_And_Get( P_count => x_msg_count,
180: P_data => x_msg_data);
184: WHEN NO_DATA_FOUND THEN
185: --no duplicates
186: NULL;
187: WHEN OTHERS THEN
188: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
189: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
190: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others Exception in Check_for_duplicate2 ' || SQLERRM);
191: END IF;
192:
208: from JTF_TERR_RSC_ALL
209: where TERR_ID = P_TerrRsc_Rec.Terr_Id
210: --resource with same role and group assigned to this territory
211: AND RESOURCE_ID = P_TerrRsc_Rec.Resource_Id
212: AND ( (RESOURCE_TYPE IS NULL and ( ( P_TerrRsc_Rec.Resource_TYPE IS NULL ) OR (P_TerrRsc_Rec.Resource_TYPE = FND_API.G_MISS_CHAR) ) )
213: OR (RESOURCE_TYPE = P_TerrRsc_Rec.Resource_TYPE))
214: AND ( (GROUP_ID IS NULL and ( ( P_TerrRsc_Rec.GROUP_ID IS NULL ) OR (P_TerrRsc_Rec.GROUP_ID = FND_API.G_MISS_NUM ) ) )
215: OR (P_TerrRsc_Rec.GROUP_ID = GROUP_ID) )
216: AND ( (ROLE IS NULL and ( (P_TerrRsc_Rec.ROLE IS NULL ) OR (P_TerrRsc_Rec.ROLE = FND_API.G_MISS_CHAR) ) )
210: --resource with same role and group assigned to this territory
211: AND RESOURCE_ID = P_TerrRsc_Rec.Resource_Id
212: AND ( (RESOURCE_TYPE IS NULL and ( ( P_TerrRsc_Rec.Resource_TYPE IS NULL ) OR (P_TerrRsc_Rec.Resource_TYPE = FND_API.G_MISS_CHAR) ) )
213: OR (RESOURCE_TYPE = P_TerrRsc_Rec.Resource_TYPE))
214: AND ( (GROUP_ID IS NULL and ( ( P_TerrRsc_Rec.GROUP_ID IS NULL ) OR (P_TerrRsc_Rec.GROUP_ID = FND_API.G_MISS_NUM ) ) )
215: OR (P_TerrRsc_Rec.GROUP_ID = GROUP_ID) )
216: AND ( (ROLE IS NULL and ( (P_TerrRsc_Rec.ROLE IS NULL ) OR (P_TerrRsc_Rec.ROLE = FND_API.G_MISS_CHAR) ) )
217: OR (P_TerrRsc_Rec.ROLE = ROLE));
218:
212: AND ( (RESOURCE_TYPE IS NULL and ( ( P_TerrRsc_Rec.Resource_TYPE IS NULL ) OR (P_TerrRsc_Rec.Resource_TYPE = FND_API.G_MISS_CHAR) ) )
213: OR (RESOURCE_TYPE = P_TerrRsc_Rec.Resource_TYPE))
214: AND ( (GROUP_ID IS NULL and ( ( P_TerrRsc_Rec.GROUP_ID IS NULL ) OR (P_TerrRsc_Rec.GROUP_ID = FND_API.G_MISS_NUM ) ) )
215: OR (P_TerrRsc_Rec.GROUP_ID = GROUP_ID) )
216: AND ( (ROLE IS NULL and ( (P_TerrRsc_Rec.ROLE IS NULL ) OR (P_TerrRsc_Rec.ROLE = FND_API.G_MISS_CHAR) ) )
217: OR (P_TerrRsc_Rec.ROLE = ROLE));
218:
219: BEGIN
220:
218:
219: BEGIN
220:
221: -- Initialize API return status to success
222: x_return_status := FND_API.G_RET_STS_SUCCESS;
223:
224: FOR l_c_res IN c_res LOOP
225:
226: IF P_TerrRsc_Rec.START_DATE_ACTIVE IS NOT NULL AND P_TerrRsc_Rec.END_DATE_ACTIVE IS NOT NULL THEN
250: END LOOP;
251:
252: if l_temp = 'X' then
253: fnd_msg_pub.initialize;
254: x_return_status := FND_API.G_RET_STS_ERROR ;
255: fnd_message.set_name('JTF', 'JTF_TERR_DUPLICATE_RESOURCE');
256: FND_MSG_PUB.ADD;
257: FND_MSG_PUB.Count_And_Get ( P_count => x_msg_count, P_data => x_msg_data);
258: end if;
261: WHEN NO_DATA_FOUND THEN
262: --no duplicates
263: NULL;
264: WHEN OTHERS THEN
265: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
267: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Others Exception in Check_for_duplicate2 ' || SQLERRM);
268: END IF;
269: END check_for_duplicate2;
354: -- Parameter Name Data Type Default
355: -- p_Api_Version_Number NUMBER
356: -- p_TerrRsc_Tbl TerrResource_tbl_type_wflex := G_MISS_TERRRESOURCE_TBL_WFLEX
357: -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
358: -- p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL,
359: --
360: -- Optional
361: -- Parameter Name Data Type Default
362: -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
358: -- p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL,
359: --
360: -- Optional
361: -- Parameter Name Data Type Default
362: -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
363: -- p_Commit VARCHAR2 := FND_API.G_FALSE
364: --
365: -- OUT :
366: -- Parameter Name Data Type
359: --
360: -- Optional
361: -- Parameter Name Data Type Default
362: -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
363: -- p_Commit VARCHAR2 := FND_API.G_FALSE
364: --
365: -- OUT :
366: -- Parameter Name Data Type
367: -- x_Return_Status VARCHAR2(1)
379:
380: PROCEDURE Create_TerrResource
381: (
382: p_Api_Version_Number IN NUMBER,
383: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
384: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
385: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
386: x_Return_Status OUT NOCOPY VARCHAR2,
387: x_Msg_Count OUT NOCOPY NUMBER,
380: PROCEDURE Create_TerrResource
381: (
382: p_Api_Version_Number IN NUMBER,
383: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
384: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
385: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
386: x_Return_Status OUT NOCOPY VARCHAR2,
387: x_Msg_Count OUT NOCOPY NUMBER,
388: x_Msg_Data OUT NOCOPY VARCHAR2,
381: (
382: p_Api_Version_Number IN NUMBER,
383: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
384: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
385: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
386: x_Return_Status OUT NOCOPY VARCHAR2,
387: x_Msg_Count OUT NOCOPY NUMBER,
388: x_Msg_Data OUT NOCOPY VARCHAR2,
389: p_TerrRsc_Tbl IN TerrResource_tbl_type_wflex := G_MISS_TERRRESOURCE_TBL_WFLEX,
405: -- Standard Start of API savepoint
406: SAVEPOINT CREATE_TERRRESOURCE_PVT;
407:
408: -- Standard call to check for call compatibility.
409: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
410: p_api_version_number,
411: l_api_name,
412: G_PKG_NAME)
413: THEN
410: p_api_version_number,
411: l_api_name,
412: G_PKG_NAME)
413: THEN
414: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
415: END IF;
416:
417: -- Initialize message list if p_init_msg_list is set to TRUE.
418: IF FND_API.to_Boolean( p_init_msg_list ) THEN
414: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
415: END IF;
416:
417: -- Initialize message list if p_init_msg_list is set to TRUE.
418: IF FND_API.to_Boolean( p_init_msg_list ) THEN
419: FND_MSG_PUB.initialize;
420: END IF;
421:
422: -- Debug Message
427: FND_MSG_PUB.Add;
428: END IF;
429:
430: -- Initialize API return status to success
431: x_return_status := FND_API.G_RET_STS_SUCCESS;
432: --
433: -- API body
434: --
435: -- Convert incomming data from public to private Tbl format
441: -- API body
442: --
443: create_terrresource (
444: p_api_version_number => 1.0,
445: p_init_msg_list => fnd_api.g_false,
446: p_commit => fnd_api.g_false,
447: p_validation_level => fnd_api.g_valid_level_full,
448: x_return_status => x_Return_Status,
449: x_msg_count => x_msg_count,
442: --
443: create_terrresource (
444: p_api_version_number => 1.0,
445: p_init_msg_list => fnd_api.g_false,
446: p_commit => fnd_api.g_false,
447: p_validation_level => fnd_api.g_valid_level_full,
448: x_return_status => x_Return_Status,
449: x_msg_count => x_msg_count,
450: x_msg_data => x_msg_data,
443: create_terrresource (
444: p_api_version_number => 1.0,
445: p_init_msg_list => fnd_api.g_false,
446: p_commit => fnd_api.g_false,
447: p_validation_level => fnd_api.g_valid_level_full,
448: x_return_status => x_Return_Status,
449: x_msg_count => x_msg_count,
450: x_msg_data => x_msg_data,
451: p_terrrsc_tbl => l_TerrRsc_Tbl,
454: x_terrrsc_access_out_tbl => x_TerrRsc_Access_Out_Tbl
455: );
456:
457:
458: IF x_Return_Status = fnd_api.g_ret_sts_error
459: THEN
460: RAISE fnd_api.g_exc_error;
461: ELSIF x_Return_Status = fnd_api.g_ret_sts_unexp_error
462: THEN
456:
457:
458: IF x_Return_Status = fnd_api.g_ret_sts_error
459: THEN
460: RAISE fnd_api.g_exc_error;
461: ELSIF x_Return_Status = fnd_api.g_ret_sts_unexp_error
462: THEN
463: RAISE fnd_api.g_exc_unexpected_error;
464: END IF;
457:
458: IF x_Return_Status = fnd_api.g_ret_sts_error
459: THEN
460: RAISE fnd_api.g_exc_error;
461: ELSIF x_Return_Status = fnd_api.g_ret_sts_unexp_error
462: THEN
463: RAISE fnd_api.g_exc_unexpected_error;
464: END IF;
465: --
459: THEN
460: RAISE fnd_api.g_exc_error;
461: ELSIF x_Return_Status = fnd_api.g_ret_sts_unexp_error
462: THEN
463: RAISE fnd_api.g_exc_unexpected_error;
464: END IF;
465: --
466: -- End of API body.
467: --
473: fnd_msg_pub.add;
474: END IF;
475:
476: -- Standard check for p_commit
477: IF FND_API.to_Boolean( p_commit )
478: THEN
479: COMMIT WORK;
480: END IF;
481:
481:
482: --dbms_output.put_line('Create_TerrResource PVT: Exiting API');
483: EXCEPTION
484: --
485: WHEN FND_API.G_EXC_ERROR THEN
486: --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_ERROR');
487: ROLLBACK TO CREATE_TERRRESOURCE_PVT;
488: x_return_status := FND_API.G_RET_STS_ERROR ;
489: FND_MSG_PUB.Count_And_Get
482: --dbms_output.put_line('Create_TerrResource PVT: Exiting API');
483: EXCEPTION
484: --
485: WHEN FND_API.G_EXC_ERROR THEN
486: --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_ERROR');
487: ROLLBACK TO CREATE_TERRRESOURCE_PVT;
488: x_return_status := FND_API.G_RET_STS_ERROR ;
489: FND_MSG_PUB.Count_And_Get
490: ( p_count => x_msg_count,
484: --
485: WHEN FND_API.G_EXC_ERROR THEN
486: --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_ERROR');
487: ROLLBACK TO CREATE_TERRRESOURCE_PVT;
488: x_return_status := FND_API.G_RET_STS_ERROR ;
489: FND_MSG_PUB.Count_And_Get
490: ( p_count => x_msg_count,
491: p_data => x_msg_data
492: );
490: ( p_count => x_msg_count,
491: p_data => x_msg_data
492: );
493:
494: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
495: --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
496: ROLLBACK TO CREATE_TERRRESOURCE_PVT;
497: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
498: FND_MSG_PUB.Count_And_Get
491: p_data => x_msg_data
492: );
493:
494: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
495: --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
496: ROLLBACK TO CREATE_TERRRESOURCE_PVT;
497: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
498: FND_MSG_PUB.Count_And_Get
499: ( p_count => x_msg_count,
493:
494: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
495: --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
496: ROLLBACK TO CREATE_TERRRESOURCE_PVT;
497: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
498: FND_MSG_PUB.Count_And_Get
499: ( p_count => x_msg_count,
500: p_data => x_msg_data
501: );
502:
503: WHEN OTHERS THEN
504: --dbms_output.put_line('Create_TerrResource PVT: OTHERS - ' || SQLERRM);
505: ROLLBACK TO CREATE_TERRRESOURCE_PVT;
506: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
508: THEN
509: fnd_msg_pub.add_exc_msg (
510: g_pkg_name,
528: -- Parameter Name Data Type Default
529: -- p_Api_Version_Number NUMBER
530: -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
531: -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
532: -- p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL,
533: --
534: -- Optional
535: -- Parameter Name Data Type Default
536: -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
532: -- p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL,
533: --
534: -- Optional
535: -- Parameter Name Data Type Default
536: -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
537: -- p_Commit VARCHAR2 := FND_API.G_FALSE
538: --
539: -- OUT :
540: -- Parameter Name Data Type
533: --
534: -- Optional
535: -- Parameter Name Data Type Default
536: -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
537: -- p_Commit VARCHAR2 := FND_API.G_FALSE
538: --
539: -- OUT :
540: -- Parameter Name Data Type
541: -- x_Return_Status VARCHAR2(1)
553:
554: PROCEDURE Create_TerrResource
555: (
556: p_Api_Version_Number IN NUMBER,
557: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
558: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
559: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
560: x_Return_Status OUT NOCOPY VARCHAR2,
561: x_Msg_Count OUT NOCOPY NUMBER,
554: PROCEDURE Create_TerrResource
555: (
556: p_Api_Version_Number IN NUMBER,
557: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
558: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
559: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
560: x_Return_Status OUT NOCOPY VARCHAR2,
561: x_Msg_Count OUT NOCOPY NUMBER,
562: x_Msg_Data OUT NOCOPY VARCHAR2,
555: (
556: p_Api_Version_Number IN NUMBER,
557: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
558: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
559: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
560: x_Return_Status OUT NOCOPY VARCHAR2,
561: x_Msg_Count OUT NOCOPY NUMBER,
562: x_Msg_Data OUT NOCOPY VARCHAR2,
563: p_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
600: -- Standard Start of API savepoint
601: SAVEPOINT CREATE_TERRRESOURCE_PVT;
602:
603: -- Standard call to check for call compatibility.
604: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
605: p_api_version_number,
606: l_api_name,
607: G_PKG_NAME)
608: THEN
605: p_api_version_number,
606: l_api_name,
607: G_PKG_NAME)
608: THEN
609: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
610: END IF;
611:
612: -- Initialize message list if p_init_msg_list is set to TRUE.
613: IF FND_API.to_Boolean( p_init_msg_list ) THEN
609: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
610: END IF;
611:
612: -- Initialize message list if p_init_msg_list is set to TRUE.
613: IF FND_API.to_Boolean( p_init_msg_list ) THEN
614: FND_MSG_PUB.initialize;
615: END IF;
616:
617: -- Debug Message
622: FND_MSG_PUB.Add;
623: END IF;
624:
625: -- Initialize API return status to success
626: x_return_status := FND_API.G_RET_STS_SUCCESS;
627: --
628: -- API body
629: --
630: -- ******************************************************************
631: -- Validate Territory parameters Access
632: -- ******************************************************************
633:
634: --mark#2
635: If(p_validation_level <> FND_API.G_VALID_LEVEL_NONE) Then
636: --dbms_output.put_line('Create_TerrResource PVT: About to call Validate_TerrResource_Data');
637:
638: --Validate the incomming data for territory creation
639: Validate_TerrResource_Data(p_TerrRsc_Tbl => p_TerrRsc_Tbl,
641: x_Return_Status => l_return_status,
642: x_Msg_Count => x_Msg_Count,
643: x_Msg_Data => x_Msg_Data);
644:
645: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
646: --dbms_output.put_line('Create_TerrResource PVT: Returned x_return_status <> FND_API.G_RET_STS_SUCCESS');
647: RAISE FND_API.G_EXC_ERROR;
648: END IF;
649: End If;
642: x_Msg_Count => x_Msg_Count,
643: x_Msg_Data => x_Msg_Data);
644:
645: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
646: --dbms_output.put_line('Create_TerrResource PVT: Returned x_return_status <> FND_API.G_RET_STS_SUCCESS');
647: RAISE FND_API.G_EXC_ERROR;
648: END IF;
649: End If;
650: --
643: x_Msg_Data => x_Msg_Data);
644:
645: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
646: --dbms_output.put_line('Create_TerrResource PVT: Returned x_return_status <> FND_API.G_RET_STS_SUCCESS');
647: RAISE FND_API.G_EXC_ERROR;
648: END IF;
649: End If;
650: --
651: --
694:
695: -- Save the terr qualifier id
696: l_TerrRsc_Id := l_TerrRsc_Out_Tbl(1).TERR_RSC_ID;
697:
698: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
699: X_Return_Status := l_return_status;
700: RAISE FND_API.G_EXC_ERROR;
701: END IF;
702: -- Add the access types
696: l_TerrRsc_Id := l_TerrRsc_Out_Tbl(1).TERR_RSC_ID;
697:
698: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
699: X_Return_Status := l_return_status;
700: RAISE FND_API.G_EXC_ERROR;
701: END IF;
702: -- Add the access types
703: -- Get into this loop only if there are access records found
704: If l_TerrRsc_Access_Tbl.Count > 0 Then
723: l_index := l_index + 1;
724: x_TerrRsc_Access_Out_Tbl(l_index) := l_TerrRsc_Access_Out_Tbl(l_counter);
725: End LOOP;
726: --
727: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
728: X_Return_Status := l_return_status;
729: RAISE FND_API.G_EXC_ERROR;
730: END IF;
731: -- Reset the table and records to G_MISS_RECORD and G_MISS_TABLE
725: End LOOP;
726: --
727: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
728: X_Return_Status := l_return_status;
729: RAISE FND_API.G_EXC_ERROR;
730: END IF;
731: -- Reset the table and records to G_MISS_RECORD and G_MISS_TABLE
732: l_TerrRsc_Tbl := G_MISS_TERRRESOURCE_TBL;
733: l_TerrRsc_Access_Tbl := G_MISS_TERRRSC_ACCESS_TBL;
794: l_index := l_index + 1;
795: x_TerrRsc_Access_Out_Tbl(l_index) := l_TerrRsc_Access_Out_Tbl(l_counter);
796: End LOOP;
797: --
798: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
799: X_Return_Status := l_return_status;
800: RAISE FND_API.G_EXC_ERROR;
801: END IF;
802: END IF;
796: End LOOP;
797: --
798: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
799: X_Return_Status := l_return_status;
800: RAISE FND_API.G_EXC_ERROR;
801: END IF;
802: END IF;
803: -- Reset the table and records to G_MISS_RECORD and G_MISS_TABLE
804: l_TerrRsc_Tbl := G_MISS_TERRRESOURCE_TBL;
807: End If;
808: --
809: End LOOP;
810:
811: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
812: X_Return_Status := l_return_status;
813: RAISE FND_API.G_EXC_ERROR;
814: END IF;
815:
809: End LOOP;
810:
811: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
812: X_Return_Status := l_return_status;
813: RAISE FND_API.G_EXC_ERROR;
814: END IF;
815:
816: -- Debug Message
817: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
827: p_data => x_msg_data
828: );
829:
830: -- Standard check for p_commit
831: IF FND_API.to_Boolean( p_commit )
832: THEN
833: COMMIT WORK;
834: END IF;
835:
835:
836: --dbms_output.put_line('Create_TerrResource PVT: Exiting API');
837: EXCEPTION
838: --
839: WHEN FND_API.G_EXC_ERROR THEN
840: --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_ERROR');
841: ROLLBACK TO CREATE_TERRRESOURCE_PVT;
842: x_return_status := FND_API.G_RET_STS_ERROR ;
843: FND_MSG_PUB.Count_And_Get
836: --dbms_output.put_line('Create_TerrResource PVT: Exiting API');
837: EXCEPTION
838: --
839: WHEN FND_API.G_EXC_ERROR THEN
840: --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_ERROR');
841: ROLLBACK TO CREATE_TERRRESOURCE_PVT;
842: x_return_status := FND_API.G_RET_STS_ERROR ;
843: FND_MSG_PUB.Count_And_Get
844: ( p_count => x_msg_count,
838: --
839: WHEN FND_API.G_EXC_ERROR THEN
840: --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_ERROR');
841: ROLLBACK TO CREATE_TERRRESOURCE_PVT;
842: x_return_status := FND_API.G_RET_STS_ERROR ;
843: FND_MSG_PUB.Count_And_Get
844: ( p_count => x_msg_count,
845: p_data => x_msg_data
846: );
844: ( p_count => x_msg_count,
845: p_data => x_msg_data
846: );
847:
848: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
849: --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
850: ROLLBACK TO CREATE_TERRRESOURCE_PVT;
851: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
852: FND_MSG_PUB.Count_And_Get
845: p_data => x_msg_data
846: );
847:
848: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
849: --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
850: ROLLBACK TO CREATE_TERRRESOURCE_PVT;
851: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
852: FND_MSG_PUB.Count_And_Get
853: ( p_count => x_msg_count,
847:
848: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
849: --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
850: ROLLBACK TO CREATE_TERRRESOURCE_PVT;
851: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
852: FND_MSG_PUB.Count_And_Get
853: ( p_count => x_msg_count,
854: p_data => x_msg_data
855: );
856:
857: WHEN OTHERS THEN
858: --dbms_output.put_line('Create_TerrResource PVT: OTHERS - ' || SQLERRM);
859: ROLLBACK TO CREATE_TERRRESOURCE_PVT;
860: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
861: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
862: THEN
863: fnd_msg_pub.add_exc_msg (
864: g_pkg_name,
888: -- p_TerrRsc_Id NUMBER
889: --
890: -- Optional
891: -- Parameter Name Data Type Default
892: -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
893: -- p_Commit VARCHAR2 FND_API.G_FALSE
894: -- p_validation_level NUMBER FND_API.G_VALID_LEVEL_FULL,
895: --
896: -- OUT :
889: --
890: -- Optional
891: -- Parameter Name Data Type Default
892: -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
893: -- p_Commit VARCHAR2 FND_API.G_FALSE
894: -- p_validation_level NUMBER FND_API.G_VALID_LEVEL_FULL,
895: --
896: -- OUT :
897: -- Parameter Name Data Type
890: -- Optional
891: -- Parameter Name Data Type Default
892: -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
893: -- p_Commit VARCHAR2 FND_API.G_FALSE
894: -- p_validation_level NUMBER FND_API.G_VALID_LEVEL_FULL,
895: --
896: -- OUT :
897: -- Parameter Name Data Type
898: -- X_Return_Status VARCHAR2(1)
908:
909: PROCEDURE Delete_Terr_Resource
910: (
911: p_Api_Version_Number IN NUMBER,
912: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
913: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
914: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
915: X_Return_Status OUT NOCOPY VARCHAR2,
916: X_Msg_Count OUT NOCOPY NUMBER,
909: PROCEDURE Delete_Terr_Resource
910: (
911: p_Api_Version_Number IN NUMBER,
912: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
913: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
914: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
915: X_Return_Status OUT NOCOPY VARCHAR2,
916: X_Msg_Count OUT NOCOPY NUMBER,
917: X_Msg_Data OUT NOCOPY VARCHAR2,
910: (
911: p_Api_Version_Number IN NUMBER,
912: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
913: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
914: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
915: X_Return_Status OUT NOCOPY VARCHAR2,
916: X_Msg_Count OUT NOCOPY NUMBER,
917: X_Msg_Data OUT NOCOPY VARCHAR2,
918: p_TerrRsc_Id IN NUMBER
937: -- Standard Start of API savepoint
938: SAVEPOINT DELETE_TERR_RESOURCE_PVT;
939:
940: -- Standard call to check for call compatibility.
941: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
942: p_api_version_number,
943: l_api_name,
944: G_PKG_NAME)
945: THEN
942: p_api_version_number,
943: l_api_name,
944: G_PKG_NAME)
945: THEN
946: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
947: END IF;
948:
949: -- Initialize message list if p_init_msg_list is set to TRUE.
950: IF FND_API.to_Boolean( p_init_msg_list ) THEN
946: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
947: END IF;
948:
949: -- Initialize message list if p_init_msg_list is set to TRUE.
950: IF FND_API.to_Boolean( p_init_msg_list ) THEN
951: FND_MSG_PUB.initialize;
952: END IF;
953:
954: -- Debug Message
959: FND_MSG_PUB.Add;
960: END IF;
961:
962: -- Initialize API return status to success
963: x_return_status := FND_API.G_RET_STS_SUCCESS;
964: --
965: -- API body
966: --
967: OPEN C_GetTerrRscAccess (p_TerrRsc_Id);
976: l_Return_Status,
977: X_Msg_Count,
978: X_Msg_Data);
979:
980: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
981: RAISE FND_API.G_EXC_ERROR;
982: END IF;
983: --
984: END LOOP;
977: X_Msg_Count,
978: X_Msg_Data);
979:
980: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
981: RAISE FND_API.G_EXC_ERROR;
982: END IF;
983: --
984: END LOOP;
985:
994: l_Return_Status,
995: X_Msg_Count,
996: X_Msg_Data);
997:
998: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
999: RAISE FND_API.G_EXC_ERROR;
1000: END IF;
1001:
1002: -- Debug Message
995: X_Msg_Count,
996: X_Msg_Data);
997:
998: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
999: RAISE FND_API.G_EXC_ERROR;
1000: END IF;
1001:
1002: -- Debug Message
1003: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1013: p_data => x_msg_data
1014: );
1015:
1016: -- Standard check for p_commit
1017: IF FND_API.to_Boolean( p_commit )
1018: THEN
1019: COMMIT WORK;
1020: END IF;
1021:
1020: END IF;
1021:
1022: EXCEPTION
1023: --
1024: WHEN FND_API.G_EXC_ERROR THEN
1025: ROLLBACK TO DELETE_TERR_RESOURCE_PVT;
1026: x_return_status := FND_API.G_RET_STS_ERROR ;
1027: FND_MSG_PUB.Count_And_Get
1028: ( p_count => x_msg_count,
1022: EXCEPTION
1023: --
1024: WHEN FND_API.G_EXC_ERROR THEN
1025: ROLLBACK TO DELETE_TERR_RESOURCE_PVT;
1026: x_return_status := FND_API.G_RET_STS_ERROR ;
1027: FND_MSG_PUB.Count_And_Get
1028: ( p_count => x_msg_count,
1029: p_data => x_msg_data
1030: );
1028: ( p_count => x_msg_count,
1029: p_data => x_msg_data
1030: );
1031:
1032: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1033: ROLLBACK TO DELETE_TERR_RESOURCE_PVT;
1034: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1035: FND_MSG_PUB.Count_And_Get
1036: ( p_count => x_msg_count,
1030: );
1031:
1032: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1033: ROLLBACK TO DELETE_TERR_RESOURCE_PVT;
1034: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1035: FND_MSG_PUB.Count_And_Get
1036: ( p_count => x_msg_count,
1037: p_data => x_msg_data
1038: );
1037: p_data => x_msg_data
1038: );
1039: WHEN NO_DATA_FOUND THEN
1040: CLOSE C_GetTerrRscAccess;
1041: x_return_status := FND_API.G_RET_STS_ERROR ;
1042:
1043: WHEN OTHERS THEN
1044: ROLLBACK TO DELETE_TERR_RESOURCE_PVT;
1045: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1041: x_return_status := FND_API.G_RET_STS_ERROR ;
1042:
1043: WHEN OTHERS THEN
1044: ROLLBACK TO DELETE_TERR_RESOURCE_PVT;
1045: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1046: FND_MSG_PUB.Count_And_Get
1047: ( p_count => x_msg_count,
1048: p_data => x_msg_data
1049: );
1072: -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
1073: --
1074: -- Optional
1075: -- Parameter Name Data Type Default
1076: -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
1077: -- p_Commit VARCHAR2 := FND_API.G_FALSE
1078: --
1079: -- OUT :
1080: -- Parameter Name Data Type
1073: --
1074: -- Optional
1075: -- Parameter Name Data Type Default
1076: -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
1077: -- p_Commit VARCHAR2 := FND_API.G_FALSE
1078: --
1079: -- OUT :
1080: -- Parameter Name Data Type
1081: -- x_Return_Status VARCHAR2(1)
1093:
1094: PROCEDURE Update_TerrResource
1095: (
1096: p_Api_Version_Number IN NUMBER,
1097: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1098: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1099: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1100: x_Return_Status OUT NOCOPY VARCHAR2,
1101: x_Msg_Count OUT NOCOPY NUMBER,
1094: PROCEDURE Update_TerrResource
1095: (
1096: p_Api_Version_Number IN NUMBER,
1097: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1098: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1099: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1100: x_Return_Status OUT NOCOPY VARCHAR2,
1101: x_Msg_Count OUT NOCOPY NUMBER,
1102: x_Msg_Data OUT NOCOPY VARCHAR2,
1095: (
1096: p_Api_Version_Number IN NUMBER,
1097: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1098: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1099: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1100: x_Return_Status OUT NOCOPY VARCHAR2,
1101: x_Msg_Count OUT NOCOPY NUMBER,
1102: x_Msg_Data OUT NOCOPY VARCHAR2,
1103: p_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
1120: x_Return_Status => l_return_status,
1121: x_msg_count => x_msg_count,
1122: x_Msg_Data => x_Msg_Data);
1123:
1124: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1125: x_return_status := l_return_status;
1126: END IF;
1127: */
1128: -- Standard call to check for call compatibility.
1125: x_return_status := l_return_status;
1126: END IF;
1127: */
1128: -- Standard call to check for call compatibility.
1129: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1130: p_api_version_number,
1131: l_api_name,
1132: G_PKG_NAME)
1133: THEN
1130: p_api_version_number,
1131: l_api_name,
1132: G_PKG_NAME)
1133: THEN
1134: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1135: END IF;
1136:
1137: -- Initialize message list if p_init_msg_list is set to TRUE.
1138: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1134: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1135: END IF;
1136:
1137: -- Initialize message list if p_init_msg_list is set to TRUE.
1138: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1139: FND_MSG_PUB.initialize;
1140: END IF;
1141:
1142: -- Debug Message
1148: END IF;
1149:
1150:
1151: -- Initialize API return status to success
1152: x_return_status := FND_API.G_RET_STS_SUCCESS;
1153: --
1154: -- API body
1155: --
1156: If P_TerrRsc_Tbl.Count > 0 Then
1165: x_msg_data => x_msg_data,
1166: X_TerrRsc_Out_Tbl => X_TerrRsc_Out_Tbl);
1167:
1168:
1169: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1170: x_return_status := l_return_status;
1171: RAISE FND_API.G_EXC_ERROR;
1172: END IF;
1173: --
1167:
1168:
1169: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1170: x_return_status := l_return_status;
1171: RAISE FND_API.G_EXC_ERROR;
1172: END IF;
1173: --
1174: End If;
1175: --
1184: x_msg_count => x_msg_count,
1185: x_msg_data => x_msg_data,
1186: X_TerrRsc_Access_Out_Tbl => x_TerrRsc_Access_Out_Tbl);
1187: --
1188: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1189: x_return_status := l_return_status;
1190: RAISE FND_API.G_EXC_ERROR;
1191: END IF;
1192: --
1186: X_TerrRsc_Access_Out_Tbl => x_TerrRsc_Access_Out_Tbl);
1187: --
1188: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1189: x_return_status := l_return_status;
1190: RAISE FND_API.G_EXC_ERROR;
1191: END IF;
1192: --
1193: End If;
1194:
1206: p_data => x_msg_data
1207: );
1208:
1209: -- Standard check for p_commit
1210: IF FND_API.to_Boolean( p_commit )
1211: THEN
1212: COMMIT WORK;
1213: END IF;
1214: --
1213: END IF;
1214: --
1215: EXCEPTION
1216: --
1217: WHEN FND_API.G_EXC_ERROR THEN
1218: ROLLBACK TO UPDATE_TERRRESOURCE_PVT;
1219: x_return_status := FND_API.G_RET_STS_ERROR ;
1220: FND_MSG_PUB.Count_And_Get
1221: ( p_count => x_msg_count,
1215: EXCEPTION
1216: --
1217: WHEN FND_API.G_EXC_ERROR THEN
1218: ROLLBACK TO UPDATE_TERRRESOURCE_PVT;
1219: x_return_status := FND_API.G_RET_STS_ERROR ;
1220: FND_MSG_PUB.Count_And_Get
1221: ( p_count => x_msg_count,
1222: p_data => x_msg_data
1223: );
1221: ( p_count => x_msg_count,
1222: p_data => x_msg_data
1223: );
1224:
1225: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1226: ROLLBACK TO UPDATE_TERRRESOURCE_PVT;
1227: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1228: FND_MSG_PUB.Count_And_Get
1229: ( p_count => x_msg_count,
1223: );
1224:
1225: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1226: ROLLBACK TO UPDATE_TERRRESOURCE_PVT;
1227: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1228: FND_MSG_PUB.Count_And_Get
1229: ( p_count => x_msg_count,
1230: p_data => x_msg_data
1231: );
1231: );
1232:
1233: WHEN OTHERS THEN
1234: ROLLBACK TO UPDATE_TERRRESOURCE_PVT;
1235: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1236: FND_MSG_PUB.Count_And_Get
1237: ( p_count => x_msg_count,
1238: p_data => x_msg_data
1239: );
1244: -- Validate Resource
1245: ---------------------------------------------------------------------
1246: PROCEDURE Validate_Resource
1247: (
1248: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1249: x_Return_Status OUT NOCOPY VARCHAR2,
1250: x_msg_count OUT NOCOPY NUMBER,
1251: x_msg_data OUT NOCOPY VARCHAR2,
1252: p_TerrRsc_Rec IN TerrResource_Rec_type
1257: l_rsc_validate_sql VARCHAR2(30000);
1258:
1259: BEGIN
1260: -- Initialize the status to success
1261: x_return_status := FND_API.G_RET_STS_SUCCESS;
1262:
1263: SELECT JSA.RSC_LOV_SQL
1264: INTO l_rsc_lov_sql
1265: FROM JTF_TERR_ALL JTA,
1272: l_rsc_validate_sql := 'SELECT ''X'' FROM ( ' || l_rsc_lov_sql || ' ) ' ;
1273: l_rsc_validate_sql := l_rsc_validate_sql || 'WHERE RESOURCE_ID = ' || P_TerrRsc_Rec.Resource_Id ;
1274: l_rsc_validate_sql := l_rsc_validate_sql || ' AND DB_RSC_TYPE = ''' || P_TerrRsc_Rec.Resource_TYPE || '''';
1275:
1276: IF (( P_TerrRsc_Rec.GROUP_ID IS NULL ) OR (P_TerrRsc_Rec.GROUP_ID = FND_API.G_MISS_NUM ) ) THEN
1277: l_rsc_validate_sql := l_rsc_validate_sql || ' AND GROUP_ID IS NULL ' ;
1278: ELSE
1279: l_rsc_validate_sql := l_rsc_validate_sql || ' AND GROUP_ID = ' || P_TerrRsc_Rec.GROUP_ID ;
1280: END IF;
1278: ELSE
1279: l_rsc_validate_sql := l_rsc_validate_sql || ' AND GROUP_ID = ' || P_TerrRsc_Rec.GROUP_ID ;
1280: END IF;
1281:
1282: IF (( P_TerrRsc_Rec.ROLE IS NULL ) OR (P_TerrRsc_Rec.ROLE = FND_API.G_MISS_CHAR ) ) THEN
1283: l_rsc_validate_sql := l_rsc_validate_sql ||' AND ROLE_CODE IS NULL ' ;
1284: ELSE
1285: l_rsc_validate_sql := l_rsc_validate_sql ||' AND ROLE_CODE = ''' || P_TerrRsc_Rec.ROLE || '''';
1286: END IF;
1290: BEGIN
1291: EXECUTE IMMEDIATE l_rsc_validate_sql INTO l_temp;
1292: EXCEPTION
1293: WHEN NO_DATA_FOUND THEN
1294: x_return_status := FND_API.G_RET_STS_ERROR ;
1295: fnd_message.set_name('JTF', 'JTY_TERR_INVALID_RESOURCE');
1296: FND_MSG_PUB.ADD;
1297: END;
1298: --
1299: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1300: p_data => x_msg_data);
1301: EXCEPTION
1302: WHEN NO_DATA_FOUND THEN
1303: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1304: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1305: THEN
1306: FND_MSG_PUB.Add_Exc_Msg
1307: ( G_PKG_NAME,
1308: 'NO_DATA_FOUND Exception in Validate_Resource ' || SQLERRM
1309: );
1310: END IF;
1311: WHEN OTHERS THEN
1312: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1313: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1314: THEN
1315: FND_MSG_PUB.Add_Exc_Msg
1316: ( G_PKG_NAME,
1321: END Validate_Resource;
1322: -- Validate the resource while updating the resource details.
1323: PROCEDURE Validate_Resource_update
1324: (
1325: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1326: x_Return_Status OUT NOCOPY VARCHAR2,
1327: x_msg_count OUT NOCOPY NUMBER,
1328: x_msg_data OUT NOCOPY VARCHAR2,
1329: p_TerrRsc_Rec IN TerrResource_Rec_type
1338: l_resource_type VARCHAR2(100);
1339:
1340: BEGIN
1341: -- Initialize the status to success
1342: x_return_status := FND_API.G_RET_STS_SUCCESS;
1343:
1344: BEGIN
1345: SELECT JSA.RSC_LOV_SQL
1346: INTO l_rsc_lov_sql
1353: AND JTA.TERR_ID = JTU.TERR_ID
1354: AND JTU.SOURCE_ID = JSA.SOURCE_ID;
1355: EXCEPTION
1356: WHEN NO_DATA_FOUND THEN
1357: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1358: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1359: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'NO_DATA_FOUND Exception in Validate_Resource_update procedure : ' || SQLERRM);
1360: END IF;
1361: END;
1367: FROM JTF_TERR_RSC_ALL
1368: WHERE TERR_RSC_ID = P_TerrRsc_Rec.Terr_Rsc_Id;
1369: EXCEPTION
1370: WHEN NO_DATA_FOUND THEN
1371: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1372: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1373: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'NO_DATA_FOUND Exception in Validate_Resource_update procedure : ' || SQLERRM);
1374: END IF;
1375: END;
1373: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'NO_DATA_FOUND Exception in Validate_Resource_update procedure : ' || SQLERRM);
1374: END IF;
1375: END;
1376:
1377: IF P_TerrRsc_Rec.Resource_Id <> FND_API.G_MISS_NUM THEN
1378: l_resource_id := P_TerrRsc_Rec.Resource_Id;
1379: END IF;
1380:
1381: IF P_TerrRsc_Rec.Resource_Type <> FND_API.G_MISS_CHAR THEN
1377: IF P_TerrRsc_Rec.Resource_Id <> FND_API.G_MISS_NUM THEN
1378: l_resource_id := P_TerrRsc_Rec.Resource_Id;
1379: END IF;
1380:
1381: IF P_TerrRsc_Rec.Resource_Type <> FND_API.G_MISS_CHAR THEN
1382: l_resource_type := P_TerrRsc_Rec.Resource_Type;
1383: END IF;
1384:
1385: IF P_TerrRsc_Rec.GROUP_ID <> FND_API.G_MISS_NUM THEN
1381: IF P_TerrRsc_Rec.Resource_Type <> FND_API.G_MISS_CHAR THEN
1382: l_resource_type := P_TerrRsc_Rec.Resource_Type;
1383: END IF;
1384:
1385: IF P_TerrRsc_Rec.GROUP_ID <> FND_API.G_MISS_NUM THEN
1386: l_group_id := P_TerrRsc_Rec.GROUP_ID;
1387: END IF;
1388:
1389: IF P_TerrRsc_Rec.ROLE <> FND_API.G_MISS_CHAR THEN
1385: IF P_TerrRsc_Rec.GROUP_ID <> FND_API.G_MISS_NUM THEN
1386: l_group_id := P_TerrRsc_Rec.GROUP_ID;
1387: END IF;
1388:
1389: IF P_TerrRsc_Rec.ROLE <> FND_API.G_MISS_CHAR THEN
1390: l_role := P_TerrRsc_Rec.ROLE;
1391: END IF;
1392:
1393: l_rsc_validate_sql := 'SELECT ''X'' FROM ( ' || l_rsc_lov_sql || ' ) ' ;
1411: BEGIN
1412: EXECUTE IMMEDIATE l_rsc_validate_sql INTO l_temp;
1413: EXCEPTION
1414: WHEN NO_DATA_FOUND THEN
1415: x_return_status := FND_API.G_RET_STS_ERROR ;
1416: fnd_message.set_name('JTF', 'JTY_TERR_INVALID_RESOURCE');
1417: FND_MSG_PUB.ADD;
1418: END;
1419: --
1420: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1421: p_data => x_msg_data);
1422: EXCEPTION
1423: WHEN NO_DATA_FOUND THEN
1424: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1425: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1426: THEN
1427: FND_MSG_PUB.Add_Exc_Msg
1428: ( G_PKG_NAME,
1429: 'NO_DATA_FOUND Exception in Validate_Resource_update ' || SQLERRM
1430: );
1431: END IF;
1432: WHEN OTHERS THEN
1433: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1434: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1435: THEN
1436: FND_MSG_PUB.Add_Exc_Msg
1437: ( G_PKG_NAME,
1472: PROCEDURE Create_Terr_Resource
1473: (
1474: P_TerrRsc_Rec IN TerrResource_Rec_type,
1475: p_Api_Version_Number IN NUMBER,
1476: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1477: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1478: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1479: x_Return_Status OUT NOCOPY VARCHAR2,
1480: x_Msg_Count OUT NOCOPY NUMBER,
1473: (
1474: P_TerrRsc_Rec IN TerrResource_Rec_type,
1475: p_Api_Version_Number IN NUMBER,
1476: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1477: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1478: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1479: x_Return_Status OUT NOCOPY VARCHAR2,
1480: x_Msg_Count OUT NOCOPY NUMBER,
1481: x_Msg_Data OUT NOCOPY VARCHAR2,
1474: P_TerrRsc_Rec IN TerrResource_Rec_type,
1475: p_Api_Version_Number IN NUMBER,
1476: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1477: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1478: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1479: x_Return_Status OUT NOCOPY VARCHAR2,
1480: x_Msg_Count OUT NOCOPY NUMBER,
1481: x_Msg_Data OUT NOCOPY VARCHAR2,
1482: X_TerrRsc_Out_Rec OUT NOCOPY TerrResource_out_Rec_type
1498: FND_MSG_PUB.Add;
1499: END IF;
1500:
1501: -- Initialize API return status to success
1502: x_return_status := FND_API.G_RET_STS_SUCCESS;
1503:
1504: IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE)
1505: THEN
1506:
1500:
1501: -- Initialize API return status to success
1502: x_return_status := FND_API.G_RET_STS_SUCCESS;
1503:
1504: IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE)
1505: THEN
1506:
1507: -- Debug message
1508: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1512: FND_MSG_PUB.Add;
1513: END IF;
1514: --
1515: -- Invoke validation procedures
1516: Validate_Terr_Rsc(p_init_msg_list => FND_API.G_FALSE,
1517: x_Return_Status => x_return_status,
1518: x_msg_count => x_msg_count,
1519: x_msg_data => x_msg_data,
1520: P_TerrRsc_Rec => P_TerrRsc_Rec);
1518: x_msg_count => x_msg_count,
1519: x_msg_data => x_msg_data,
1520: P_TerrRsc_Rec => P_TerrRsc_Rec);
1521:
1522: IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1523: RAISE FND_API.G_EXC_ERROR;
1524: END IF;
1525: --
1526: END IF;
1519: x_msg_data => x_msg_data,
1520: P_TerrRsc_Rec => P_TerrRsc_Rec);
1521:
1522: IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1523: RAISE FND_API.G_EXC_ERROR;
1524: END IF;
1525: --
1526: END IF;
1527:
1524: END IF;
1525: --
1526: END IF;
1527:
1528: Validate_Resource(p_init_msg_list => FND_API.G_FALSE,
1529: x_Return_Status => x_return_status,
1530: x_msg_count => x_msg_count,
1531: x_msg_data => x_msg_data,
1532: P_TerrRsc_Rec => P_TerrRsc_Rec);
1530: x_msg_count => x_msg_count,
1531: x_msg_data => x_msg_data,
1532: P_TerrRsc_Rec => P_TerrRsc_Rec);
1533:
1534: IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1535: RAISE FND_API.G_EXC_ERROR;
1536: END IF;
1537:
1538: Check_for_duplicate2 (P_TerrRsc_Rec => P_TerrRsc_Rec,
1531: x_msg_data => x_msg_data,
1532: P_TerrRsc_Rec => P_TerrRsc_Rec);
1533:
1534: IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1535: RAISE FND_API.G_EXC_ERROR;
1536: END IF;
1537:
1538: Check_for_duplicate2 (P_TerrRsc_Rec => P_TerrRsc_Rec,
1539: x_Return_Status => l_return_status,
1539: x_Return_Status => l_return_status,
1540: x_msg_count => x_msg_count,
1541: x_Msg_Data => x_Msg_Data);
1542:
1543: IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1544: RAISE FND_API.G_EXC_ERROR;
1545: END IF;
1546:
1547: /* Intialise to NULL if FND_API.G_MISS_NUM,
1540: x_msg_count => x_msg_count,
1541: x_Msg_Data => x_Msg_Data);
1542:
1543: IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1544: RAISE FND_API.G_EXC_ERROR;
1545: END IF;
1546:
1547: /* Intialise to NULL if FND_API.G_MISS_NUM,
1548: ** otherwise used passed in value
1543: IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1544: RAISE FND_API.G_EXC_ERROR;
1545: END IF;
1546:
1547: /* Intialise to NULL if FND_API.G_MISS_NUM,
1548: ** otherwise used passed in value
1549: */
1550: IF (l_TerrRsc_id = FND_API.G_MISS_NUM) THEN
1551: l_TerrRsc_id := NULL;
1546:
1547: /* Intialise to NULL if FND_API.G_MISS_NUM,
1548: ** otherwise used passed in value
1549: */
1550: IF (l_TerrRsc_id = FND_API.G_MISS_NUM) THEN
1551: l_TerrRsc_id := NULL;
1552: END IF;
1553:
1554: --dbms_output.put_line('Create_Terr_Resource REC: Before Calling JTF_TERR_RSC_PKG.Insert_Row');
1590: -- Save the terr_usg_id and
1591: X_TerrRsc_Out_Rec.TERR_RSC_ID := l_TerrRsc_Id;
1592:
1593: -- If successful then save the success status for the record
1594: X_TerrRsc_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
1595:
1596: -- Debug Message
1597: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1598: THEN
1602: END IF;
1603:
1604: --dbms_output.put_line('Create_Terr_Resource REC: Exiting API');
1605: EXCEPTION
1606: WHEN FND_API.G_EXC_ERROR THEN
1607: --dbms_output.put_line('Create_Terr_Resource: FND_API.G_EXC_ERROR');
1608:
1609: X_TerrRsc_Out_Rec.TERR_RSC_ID := NULL;
1610: X_TerrRsc_Out_Rec.return_status := x_return_status;
1603:
1604: --dbms_output.put_line('Create_Terr_Resource REC: Exiting API');
1605: EXCEPTION
1606: WHEN FND_API.G_EXC_ERROR THEN
1607: --dbms_output.put_line('Create_Terr_Resource: FND_API.G_EXC_ERROR');
1608:
1609: X_TerrRsc_Out_Rec.TERR_RSC_ID := NULL;
1610: X_TerrRsc_Out_Rec.return_status := x_return_status;
1611: x_return_status := FND_API.G_RET_STS_ERROR ;
1607: --dbms_output.put_line('Create_Terr_Resource: FND_API.G_EXC_ERROR');
1608:
1609: X_TerrRsc_Out_Rec.TERR_RSC_ID := NULL;
1610: X_TerrRsc_Out_Rec.return_status := x_return_status;
1611: x_return_status := FND_API.G_RET_STS_ERROR ;
1612:
1613: FND_MSG_PUB.Count_And_Get
1614: ( P_count => x_msg_count,
1615: P_data => x_msg_data
1616: );
1617:
1618: WHEN OTHERS THEN
1619: --dbms_output.put_line('Create_Terr_Resource REC: OTHERS - ' || SQLERRM);
1620: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1621: --
1622: X_TerrRsc_Out_Rec.TERR_RSC_ID := NULL;
1623: X_TerrRsc_Out_Rec.return_status := x_return_status;
1624: --
1657: PROCEDURE Create_Terr_Resource
1658: (
1659: P_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
1660: p_Api_Version_Number IN NUMBER,
1661: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1662: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1663: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1664: x_Return_Status OUT NOCOPY VARCHAR2,
1665: x_Msg_Count OUT NOCOPY NUMBER,
1658: (
1659: P_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
1660: p_Api_Version_Number IN NUMBER,
1661: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1662: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1663: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1664: x_Return_Status OUT NOCOPY VARCHAR2,
1665: x_Msg_Count OUT NOCOPY NUMBER,
1666: x_Msg_Data OUT NOCOPY VARCHAR2,
1659: P_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
1660: p_Api_Version_Number IN NUMBER,
1661: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1662: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1663: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1664: x_Return_Status OUT NOCOPY VARCHAR2,
1665: x_Msg_Count OUT NOCOPY NUMBER,
1666: x_Msg_Data OUT NOCOPY VARCHAR2,
1667: X_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type
1688: FND_MSG_PUB.Add;
1689: END IF;
1690:
1691: -- Initialize API return status to success
1692: x_return_status := FND_API.G_RET_STS_SUCCESS;
1693:
1694: -- Call overloaded Create_Terr_Qualifier procedure
1695: --
1696: FOR l_Counter IN 1 .. l_TerrRsc_Tbl_Count LOOP
1707: x_msg_data => x_msg_data,
1708: X_TerrRsc_Out_Rec => l_TerrRsc_Out_Rec);
1709: --
1710: --If there is a major error
1711: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1712: --dbms_output.put_line('Create_Terr_Resource TBL: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
1713:
1714: -- Save the terr_usg_id and
1715: X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := NULL;
1708: X_TerrRsc_Out_Rec => l_TerrRsc_Out_Rec);
1709: --
1710: --If there is a major error
1711: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1712: --dbms_output.put_line('Create_Terr_Resource TBL: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
1713:
1714: -- Save the terr_usg_id and
1715: X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := NULL;
1716:
1714: -- Save the terr_usg_id and
1715: X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := NULL;
1716:
1717: -- If save the ERROR status for the record
1718: X_TerrRsc_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1719: ELSE
1720: --dbms_output.put_line('Create_Terr_Resource TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
1721:
1722: -- Save the terr_usg_id and
1716:
1717: -- If save the ERROR status for the record
1718: X_TerrRsc_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1719: ELSE
1720: --dbms_output.put_line('Create_Terr_Resource TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
1721:
1722: -- Save the terr_usg_id and
1723: X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := l_TerrRsc_Out_Rec.TERR_RSC_ID;
1724:
1722: -- Save the terr_usg_id and
1723: X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := l_TerrRsc_Out_Rec.TERR_RSC_ID;
1724:
1725: -- If successful then save the success status for the record
1726: X_TerrRsc_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_SUCCESS;
1727: END IF;
1728: --
1729: END LOOP;
1730:
1729: END LOOP;
1730:
1731: --Get the API overall return status
1732: -- Initialize API return status to success
1733: x_return_status := FND_API.G_RET_STS_SUCCESS;
1734:
1735: --Get number of records in the ouput table
1736: l_TerrRsc_Out_Tbl_Count := X_TerrRsc_Out_Tbl.Count;
1737:
1735: --Get number of records in the ouput table
1736: l_TerrRsc_Out_Tbl_Count := X_TerrRsc_Out_Tbl.Count;
1737:
1738: FOR l_Counter IN 1 .. l_TerrRsc_Out_Tbl_Count LOOP
1739: If x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
1740: x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
1741: THEN
1742: X_return_status := FND_API.G_RET_STS_ERROR;
1743: END IF;
1736: l_TerrRsc_Out_Tbl_Count := X_TerrRsc_Out_Tbl.Count;
1737:
1738: FOR l_Counter IN 1 .. l_TerrRsc_Out_Tbl_Count LOOP
1739: If x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
1740: x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
1741: THEN
1742: X_return_status := FND_API.G_RET_STS_ERROR;
1743: END IF;
1744: END LOOP;
1738: FOR l_Counter IN 1 .. l_TerrRsc_Out_Tbl_Count LOOP
1739: If x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
1740: x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
1741: THEN
1742: X_return_status := FND_API.G_RET_STS_ERROR;
1743: END IF;
1744: END LOOP;
1745:
1746: -- Debug Message
1784: (
1785: p_TerrRsc_Id NUMBER,
1786: P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC,
1787: p_Api_Version_Number IN NUMBER,
1788: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1789: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1790: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1791: x_Return_Status OUT NOCOPY VARCHAR2,
1792: x_Msg_Count OUT NOCOPY NUMBER,
1785: p_TerrRsc_Id NUMBER,
1786: P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC,
1787: p_Api_Version_Number IN NUMBER,
1788: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1789: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1790: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1791: x_Return_Status OUT NOCOPY VARCHAR2,
1792: x_Msg_Count OUT NOCOPY NUMBER,
1793: x_Msg_Data OUT NOCOPY VARCHAR2,
1786: P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC,
1787: p_Api_Version_Number IN NUMBER,
1788: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1789: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1790: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1791: x_Return_Status OUT NOCOPY VARCHAR2,
1792: x_Msg_Count OUT NOCOPY NUMBER,
1793: x_Msg_Data OUT NOCOPY VARCHAR2,
1794: X_TerrRsc_Access_Out_Rec OUT NOCOPY TerrRsc_Access_out_rec_type
1809: FND_MSG_PUB.Add;
1810: END IF;
1811:
1812: -- Initialize API return status to success
1813: x_return_status := FND_API.G_RET_STS_SUCCESS;
1814:
1815:
1816: IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE)
1817: THEN
1812: -- Initialize API return status to success
1813: x_return_status := FND_API.G_RET_STS_SUCCESS;
1814:
1815:
1816: IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE)
1817: THEN
1818: -- Debug message
1819: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1820: THEN
1823: FND_MSG_PUB.Add;
1824: END IF;
1825: --Check created by
1826: IF ( p_TerrRsc_Access_Rec.CREATED_BY is NULL OR
1827: p_TerrRsc_Access_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
1828: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1829: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
1830: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
1831: FND_MSG_PUB.ADD;
1829: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
1830: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
1831: FND_MSG_PUB.ADD;
1832: END IF;
1833: x_Return_Status := FND_API.G_RET_STS_ERROR ;
1834: End If;
1835:
1836: --Check creation date
1837: If ( p_TerrRsc_Access_Rec.CREATION_DATE is NULL OR
1834: End If;
1835:
1836: --Check creation date
1837: If ( p_TerrRsc_Access_Rec.CREATION_DATE is NULL OR
1838: p_TerrRsc_Access_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
1839: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1840: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
1841: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
1842: FND_MSG_PUB.ADD;
1840: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
1841: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
1842: FND_MSG_PUB.ADD;
1843: END IF;
1844: x_Return_Status := FND_API.G_RET_STS_ERROR ;
1845: End If;
1846: --
1847: --Check ACCESS_TYPE
1848: IF ( p_TerrRsc_Access_Rec.ACCESS_TYPE is NULL OR
1845: End If;
1846: --
1847: --Check ACCESS_TYPE
1848: IF ( p_TerrRsc_Access_Rec.ACCESS_TYPE is NULL OR
1849: p_TerrRsc_Access_Rec.ACCESS_TYPE = FND_API.G_MISS_CHAR ) THEN
1850: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1851: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
1852: FND_MESSAGE.Set_Token('COL_NAME', 'ACCESS_TYPE' );
1853: FND_MSG_PUB.ADD;
1851: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
1852: FND_MESSAGE.Set_Token('COL_NAME', 'ACCESS_TYPE' );
1853: FND_MSG_PUB.ADD;
1854: END IF;
1855: x_Return_Status := FND_API.G_RET_STS_ERROR ;
1856: End If;
1857: --
1858: --Check TRANS_ACCESS_CODE
1859: IF ( p_TerrRsc_Access_Rec.TRANS_ACCESS_CODE is NULL OR
1856: End If;
1857: --
1858: --Check TRANS_ACCESS_CODE
1859: IF ( p_TerrRsc_Access_Rec.TRANS_ACCESS_CODE is NULL OR
1860: p_TerrRsc_Access_Rec.TRANS_ACCESS_CODE = FND_API.G_MISS_CHAR ) THEN
1861: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1862: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
1863: FND_MESSAGE.Set_Token('COL_NAME', 'TRANS_ACCESS_CODE' );
1864: FND_MSG_PUB.ADD;
1862: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
1863: FND_MESSAGE.Set_Token('COL_NAME', 'TRANS_ACCESS_CODE' );
1864: FND_MSG_PUB.ADD;
1865: END IF;
1866: x_Return_Status := FND_API.G_RET_STS_ERROR ;
1867: End If;
1868: --
1869: --
1870: -- Invoke validation procedures
1867: End If;
1868: --
1869: --
1870: -- Invoke validation procedures
1871: Validate_Terr_Rsc_Access(p_init_msg_list => FND_API.G_FALSE,
1872: x_Return_Status => x_return_status,
1873: x_msg_count => x_msg_count,
1874: x_msg_data => x_msg_data,
1875: p_TerrRsc_Id => p_TerrRsc_Id,
1874: x_msg_data => x_msg_data,
1875: p_TerrRsc_Id => p_TerrRsc_Id,
1876: p_TerrRsc_Access_Rec => P_TerrRsc_Access_Rec);
1877:
1878: IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1879: RAISE FND_API.G_EXC_ERROR;
1880: END IF;
1881: --
1882: END IF;
1875: p_TerrRsc_Id => p_TerrRsc_Id,
1876: p_TerrRsc_Access_Rec => P_TerrRsc_Access_Rec);
1877:
1878: IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1879: RAISE FND_API.G_EXC_ERROR;
1880: END IF;
1881: --
1882: END IF;
1883:
1885: -- check for Unique Key constraint violation
1886: validate_terr_rsc_access_UK(
1887: p_Terr_Rsc_Id => p_terrrsc_id,
1888: p_Access_Type => p_TerrRsc_access_rec.access_type,
1889: p_init_msg_list => FND_API.G_FALSE,
1890: x_Return_Status => x_return_status,
1891: x_msg_count => x_msg_count,
1892: x_msg_data => x_msg_data );
1893:
1890: x_Return_Status => x_return_status,
1891: x_msg_count => x_msg_count,
1892: x_msg_data => x_msg_data );
1893:
1894: IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1895: RAISE FND_API.G_EXC_ERROR;
1896: END IF;
1897:
1898: -- Call insert Terr_Resource_Access table handler
1891: x_msg_count => x_msg_count,
1892: x_msg_data => x_msg_data );
1893:
1894: IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1895: RAISE FND_API.G_EXC_ERROR;
1896: END IF;
1897:
1898: -- Call insert Terr_Resource_Access table handler
1899: --
1896: END IF;
1897:
1898: -- Call insert Terr_Resource_Access table handler
1899: --
1900: /* Intialise to NULL if FND_API.G_MISS_NUM,
1901: ** otherwise used passed in value
1902: */
1903: IF (l_TerrRsc_Access_id = FND_API.G_MISS_NUM) THEN
1904: l_TerrRsc_Access_id := NULL;
1899: --
1900: /* Intialise to NULL if FND_API.G_MISS_NUM,
1901: ** otherwise used passed in value
1902: */
1903: IF (l_TerrRsc_Access_id = FND_API.G_MISS_NUM) THEN
1904: l_TerrRsc_Access_id := NULL;
1905: END IF;
1906:
1907: --dbms_output.put_line('Create_Resource _Access REC: Calling JTF_TERR_RSC_ACCESS_PKG.Insert_Row');
1920: -- Save the terr_usg_id and
1921: X_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID := l_terrRsc_Access_id;
1922:
1923: -- If successful then save the success status for the record
1924: X_TerrRsc_Access_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
1925:
1926: -- Debug Message
1927: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1928: THEN
1932: END IF;
1933:
1934: --dbms_output.put_line('Create_Resource _Access REC: Exiting API');
1935: EXCEPTION
1936: WHEN FND_API.G_EXC_ERROR THEN
1937: --dbms_output.put_line('Create_Resource_Access: FND_API.G_EXC_ERROR');
1938:
1939: x_return_status := FND_API.G_RET_STS_ERROR ;
1940: X_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID := NULL;
1933:
1934: --dbms_output.put_line('Create_Resource _Access REC: Exiting API');
1935: EXCEPTION
1936: WHEN FND_API.G_EXC_ERROR THEN
1937: --dbms_output.put_line('Create_Resource_Access: FND_API.G_EXC_ERROR');
1938:
1939: x_return_status := FND_API.G_RET_STS_ERROR ;
1940: X_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID := NULL;
1941: X_TerrRsc_Access_Out_Rec.return_status := x_return_status;
1935: EXCEPTION
1936: WHEN FND_API.G_EXC_ERROR THEN
1937: --dbms_output.put_line('Create_Resource_Access: FND_API.G_EXC_ERROR');
1938:
1939: x_return_status := FND_API.G_RET_STS_ERROR ;
1940: X_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID := NULL;
1941: X_TerrRsc_Access_Out_Rec.return_status := x_return_status;
1942:
1943: FND_MSG_PUB.Count_And_Get
1947:
1948: WHEN OTHERS THEN
1949: --dbms_output.put_line('Others exception in Create_Resource_Access' || SQLERRM);
1950: --
1951: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1952: --
1953: X_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID := NULL;
1954: X_TerrRsc_Access_Out_Rec.return_status := x_return_status;
1955: --
1997: (
1998: p_TerrRsc_Id NUMBER,
1999: P_TerrRsc_Access_Tbl TerrRsc_Access_Tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
2000: p_Api_Version_Number IN NUMBER,
2001: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2002: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2003: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2004: x_Return_Status OUT NOCOPY VARCHAR2,
2005: x_Msg_Count OUT NOCOPY NUMBER,
1998: p_TerrRsc_Id NUMBER,
1999: P_TerrRsc_Access_Tbl TerrRsc_Access_Tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
2000: p_Api_Version_Number IN NUMBER,
2001: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2002: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2003: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2004: x_Return_Status OUT NOCOPY VARCHAR2,
2005: x_Msg_Count OUT NOCOPY NUMBER,
2006: x_Msg_Data OUT NOCOPY VARCHAR2,
1999: P_TerrRsc_Access_Tbl TerrRsc_Access_Tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
2000: p_Api_Version_Number IN NUMBER,
2001: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2002: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2003: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2004: x_Return_Status OUT NOCOPY VARCHAR2,
2005: x_Msg_Count OUT NOCOPY NUMBER,
2006: x_Msg_Data OUT NOCOPY VARCHAR2,
2007: X_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_Tbl_type
2031: END IF;
2032:
2033:
2034: -- Initialize API return status to success
2035: x_return_status := FND_API.G_RET_STS_SUCCESS;
2036:
2037: -- -- Call overloaded Create_Terr_Qualifier procedure
2038: --
2039: FOR l_Counter IN 1 .. l_TerrRsc_Access_Tbl_Count LOOP
2050: x_msg_data => x_msg_data,
2051: x_TerrRsc_Access_Out_Rec => l_TerrRsc_Access_Out_Rec);
2052: --
2053: --If there is a major error
2054: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2055: --dbms_output.put_line('Create_Resource _Access REC: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
2056: -- Save the terr_usg_id and
2057: X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := NULL;
2058: -- If save the ERROR status for the record
2051: x_TerrRsc_Access_Out_Rec => l_TerrRsc_Access_Out_Rec);
2052: --
2053: --If there is a major error
2054: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2055: --dbms_output.put_line('Create_Resource _Access REC: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
2056: -- Save the terr_usg_id and
2057: X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := NULL;
2058: -- If save the ERROR status for the record
2059: X_TerrRsc_Access_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2055: --dbms_output.put_line('Create_Resource _Access REC: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
2056: -- Save the terr_usg_id and
2057: X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := NULL;
2058: -- If save the ERROR status for the record
2059: X_TerrRsc_Access_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2060: ELSE
2061: --dbms_output.put_line('Create_Resource _Access REC: l_return_status = FND_API.G_RET_STS_SUCCESS');
2062: -- Save the terr_usg_id and
2063: X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := l_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID;
2057: X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := NULL;
2058: -- If save the ERROR status for the record
2059: X_TerrRsc_Access_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2060: ELSE
2061: --dbms_output.put_line('Create_Resource _Access REC: l_return_status = FND_API.G_RET_STS_SUCCESS');
2062: -- Save the terr_usg_id and
2063: X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := l_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID;
2064:
2065: -- If successful then save the success status for the record
2062: -- Save the terr_usg_id and
2063: X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := l_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID;
2064:
2065: -- If successful then save the success status for the record
2066: X_TerrRsc_Access_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_SUCCESS;
2067: END IF;
2068: --
2069: END LOOP;
2070:
2069: END LOOP;
2070:
2071: --Get the API overall return status
2072: --Initialize API return status to success
2073: x_return_status := FND_API.G_RET_STS_SUCCESS;
2074:
2075: --Get number of records in the ouput table
2076: l_TerrRscAcc_Out_Tbl_Count := X_TerrRsc_Access_Out_Tbl.Count;
2077:
2075: --Get number of records in the ouput table
2076: l_TerrRscAcc_Out_Tbl_Count := X_TerrRsc_Access_Out_Tbl.Count;
2077:
2078: FOR l_Counter IN 1 .. l_TerrRscAcc_Out_Tbl_Count LOOP
2079: If x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
2080: x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
2081: THEN
2082: X_return_status := FND_API.G_RET_STS_ERROR;
2083: END IF;
2076: l_TerrRscAcc_Out_Tbl_Count := X_TerrRsc_Access_Out_Tbl.Count;
2077:
2078: FOR l_Counter IN 1 .. l_TerrRscAcc_Out_Tbl_Count LOOP
2079: If x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
2080: x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
2081: THEN
2082: X_return_status := FND_API.G_RET_STS_ERROR;
2083: END IF;
2084: END LOOP;
2078: FOR l_Counter IN 1 .. l_TerrRscAcc_Out_Tbl_Count LOOP
2079: If x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
2080: x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
2081: THEN
2082: X_return_status := FND_API.G_RET_STS_ERROR;
2083: END IF;
2084: END LOOP;
2085:
2086: -- Debug Message
2107: -- Parameters:
2108: -- IN
2109: -- Parameter Name Data Type Default
2110: -- P_Api_Version_Number IN NUMBER,
2111: -- P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
2112: -- P_Commit IN VARCHAR2 := FND_API.G_FALSE
2113: -- P_TerrRsc_Id IN NUMBER
2114: --
2115: -- Optional:
2108: -- IN
2109: -- Parameter Name Data Type Default
2110: -- P_Api_Version_Number IN NUMBER,
2111: -- P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
2112: -- P_Commit IN VARCHAR2 := FND_API.G_FALSE
2113: -- P_TerrRsc_Id IN NUMBER
2114: --
2115: -- Optional:
2116: --
2125:
2126: PROCEDURE Delete_TerrRsc_Access
2127: (
2128: P_Api_Version_Number IN NUMBER,
2129: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2130: P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2131: P_TerrRsc_Access_Id IN NUMBER,
2132: X_Return_Status OUT NOCOPY VARCHAR2,
2133: X_Msg_Count OUT NOCOPY VARCHAR2,
2126: PROCEDURE Delete_TerrRsc_Access
2127: (
2128: P_Api_Version_Number IN NUMBER,
2129: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2130: P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2131: P_TerrRsc_Access_Id IN NUMBER,
2132: X_Return_Status OUT NOCOPY VARCHAR2,
2133: X_Msg_Count OUT NOCOPY VARCHAR2,
2134: X_Msg_Data OUT NOCOPY VARCHAR2
2143: -- Standard start of PAI savepoint
2144: SAVEPOINT DELETE_TERRRSC_ACCESS_PVT;
2145:
2146: -- Standard call to check for call compatibility.
2147: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2148: p_api_version_number,
2149: l_api_name,
2150: G_PKG_NAME)
2151: THEN
2148: p_api_version_number,
2149: l_api_name,
2150: G_PKG_NAME)
2151: THEN
2152: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2153: END IF;
2154:
2155: -- Initialize message list if p_init_msg_list is set to TRUE.
2156: IF FND_API.to_Boolean( p_init_msg_list )
2152: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2153: END IF;
2154:
2155: -- Initialize message list if p_init_msg_list is set to TRUE.
2156: IF FND_API.to_Boolean( p_init_msg_list )
2157: THEN
2158: FND_MSG_PUB.initialize;
2159: END IF;
2160:
2165: FND_MSG_PUB.Add;
2166: END IF;
2167:
2168: --Initialize the return status to success
2169: x_return_status := FND_API.G_RET_STS_SUCCESS;
2170: --
2171: JTF_TERR_RSC_ACCESS_PKG.Delete_Row(x_TERR_RSC_ACCESS_ID => P_TerrRsc_Access_Id );
2172: --
2173: --Prepare message name
2173: --Prepare message name
2174: FND_MESSAGE.SET_NAME('JTF','TERR_RSCACCESSES_DELETED');
2175:
2176: IF SQL%FOUND THEN
2177: x_return_status := FND_API.G_RET_STS_SUCCESS;
2178: l_row_count := SQL%ROWCOUNT;
2179: END IF;
2180:
2181: --Prepare message token
2196: p_data => x_msg_data
2197: );
2198:
2199: -- Standard check for p_commit
2200: IF FND_API.to_Boolean( p_commit )
2201: THEN
2202: COMMIT WORK;
2203: END IF;
2204: --
2203: END IF;
2204: --
2205: EXCEPTION
2206: WHEN NO_DATA_FOUND THEN
2207: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2208: l_row_count := 0;
2209: --Prepare message token
2210: FND_MESSAGE.SET_NAME('ITEMS_DELETED', l_row_count);
2211: --Add message to API message list
2215: ( p_count => x_msg_count,
2216: p_data => x_msg_data
2217: );
2218:
2219: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2220: ROLLBACK TO DELETE_TERRRSC_ACCESS_PVT;
2221: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2222: FND_MSG_PUB.Count_And_Get
2223: ( p_count => x_msg_count,
2217: );
2218:
2219: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2220: ROLLBACK TO DELETE_TERRRSC_ACCESS_PVT;
2221: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2222: FND_MSG_PUB.Count_And_Get
2223: ( p_count => x_msg_count,
2224: p_data => x_msg_data
2225: );
2225: );
2226:
2227: WHEN OTHERS THEN
2228: ROLLBACK TO DELETE_TERRRSC_ACCESS_PVT;
2229: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2230: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2231: THEN
2232: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Delete error inside Delete_TerrRsc_Access');
2233: END IF;
2247: -- IN
2248: -- Required:
2249: -- Parameter Name Data Type Default
2250: -- P_Api_Version_Number IN NUMBER,
2251: -- P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
2252: -- P_Commit IN VARCHAR2 := FND_API.G_FALSE
2253: -- P_TerrRsc_Id IN NUMBER
2254: --
2255: -- Optional:
2248: -- Required:
2249: -- Parameter Name Data Type Default
2250: -- P_Api_Version_Number IN NUMBER,
2251: -- P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
2252: -- P_Commit IN VARCHAR2 := FND_API.G_FALSE
2253: -- P_TerrRsc_Id IN NUMBER
2254: --
2255: -- Optional:
2256: -- OUT:
2264:
2265: PROCEDURE Delete_TerrResource
2266: (
2267: P_Api_Version_Number IN NUMBER,
2268: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2269: P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2270: P_TerrRsc_Id IN NUMBER,
2271: X_Return_Status OUT NOCOPY VARCHAR2,
2272: X_Msg_Count OUT NOCOPY VARCHAR2,
2265: PROCEDURE Delete_TerrResource
2266: (
2267: P_Api_Version_Number IN NUMBER,
2268: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2269: P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2270: P_TerrRsc_Id IN NUMBER,
2271: X_Return_Status OUT NOCOPY VARCHAR2,
2272: X_Msg_Count OUT NOCOPY VARCHAR2,
2273: X_Msg_Data OUT NOCOPY VARCHAR2
2282: -- Standard start of PAI savepoint
2283: SAVEPOINT DELETE_TERRRSC_PVT;
2284:
2285: -- Standard call to check for call compatibility.
2286: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2287: p_api_version_number,
2288: l_api_name,
2289: G_PKG_NAME)
2290: THEN
2287: p_api_version_number,
2288: l_api_name,
2289: G_PKG_NAME)
2290: THEN
2291: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2292: END IF;
2293: -- Initialize message list if p_init_msg_list is set to TRUE.
2294: IF FND_API.to_Boolean( p_init_msg_list )
2295: THEN
2290: THEN
2291: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2292: END IF;
2293: -- Initialize message list if p_init_msg_list is set to TRUE.
2294: IF FND_API.to_Boolean( p_init_msg_list )
2295: THEN
2296: FND_MSG_PUB.initialize;
2297: END IF;
2298:
2304: FND_MSG_PUB.Add;
2305: END IF;
2306:
2307: --Initialize the return status to success
2308: x_return_status := FND_API.G_RET_STS_SUCCESS;
2309: --
2310: JTF_TERR_RSC_PKG.Delete_Row(x_TERR_RSC_ID => P_TerrRsc_Id);
2311: --
2312: --Prepare message name
2312: --Prepare message name
2313: FND_MESSAGE.SET_NAME('JTF','TERR_RESOURCE_DELETED');
2314:
2315: IF SQL%FOUND THEN
2316: x_return_status := FND_API.G_RET_STS_SUCCESS;
2317: l_row_count := SQL%ROWCOUNT;
2318: END IF;
2319:
2320: --Prepare message token
2337: p_data => x_msg_data
2338: );
2339:
2340: -- Standard check for p_commit
2341: IF FND_API.to_Boolean( p_commit )
2342: THEN
2343: COMMIT WORK;
2344: END IF;
2345: --
2345: --
2346: EXCEPTION
2347: WHEN NO_DATA_FOUND THEN
2348: ROLLBACK TO DELETE_TERRRSC_PVT;
2349: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2350: l_row_count := 0;
2351:
2352: --Prepare message token
2353: FND_MESSAGE.SET_NAME('ITEMS_DELETED', l_row_count);
2359: ( p_count => x_msg_count,
2360: p_data => x_msg_data
2361: );
2362:
2363: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2364: ROLLBACK TO DELETE_TERRRSC_PVT;
2365: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2366: FND_MSG_PUB.Count_And_Get
2367: ( p_count => x_msg_count,
2361: );
2362:
2363: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2364: ROLLBACK TO DELETE_TERRRSC_PVT;
2365: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2366: FND_MSG_PUB.Count_And_Get
2367: ( p_count => x_msg_count,
2368: p_data => x_msg_data
2369: );
2369: );
2370:
2371: WHEN OTHERS THEN
2372: ROLLBACK TO DELETE_TERRRSC_PVT;
2373: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2374: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2375: THEN
2376: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Delete error inside Delete_Territory_Resource');
2377: END IF;
2388: ---------------------------------------------------------------------
2389:
2390: PROCEDURE Validate_Terr_Rsc_update
2391: (
2392: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2393: x_Return_Status OUT NOCOPY VARCHAR2,
2394: x_msg_count OUT NOCOPY NUMBER,
2395: x_msg_data OUT NOCOPY VARCHAR2,
2396: p_TerrRsc_Rec IN TerrResource_Rec_type
2406: BEGIN
2407: --dbms_output.put_line('Inside Validate_Terr_Rsc: Entering API');
2408:
2409: -- Initialize the status to success
2410: x_return_status := FND_API.G_RET_STS_SUCCESS;
2411:
2412: -- Validate the territory Id
2413: l_Validate_id := p_TerrRsc_Rec.Terr_Id;
2414: If l_Validate_id <> FND_API.G_MISS_NUM Then
2410: x_return_status := FND_API.G_RET_STS_SUCCESS;
2411:
2412: -- Validate the territory Id
2413: l_Validate_id := p_TerrRsc_Rec.Terr_Id;
2414: If l_Validate_id <> FND_API.G_MISS_NUM Then
2415: -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
2416: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
2417: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
2418: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2412: -- Validate the territory Id
2413: l_Validate_id := p_TerrRsc_Rec.Terr_Id;
2414: If l_Validate_id <> FND_API.G_MISS_NUM Then
2415: -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
2416: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
2417: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
2418: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2419: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
2420: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
2413: l_Validate_id := p_TerrRsc_Rec.Terr_Id;
2414: If l_Validate_id <> FND_API.G_MISS_NUM Then
2415: -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
2416: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
2417: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
2418: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2419: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
2420: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
2421: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
2420: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
2421: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
2422: FND_MSG_PUB.ADD;
2423: END IF;
2424: x_Return_Status := FND_API.G_RET_STS_ERROR ;
2425: End If;
2426: End If;
2427:
2428: --Get the missing values from the database to check with the territory dates.
2431: INTO l_terr_id, l_res_start_date_active, l_res_end_date_active
2432: FROM JTF_TERR_RSC_ALL
2433: WHERE TERR_RSC_ID = P_TerrRsc_Rec.Terr_Rsc_Id;
2434:
2435: IF ( P_TerrRsc_Rec.START_DATE_ACTIVE IS NOT NULL AND P_TerrRsc_Rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE ) THEN
2436: l_res_start_date_active := P_TerrRsc_Rec.START_DATE_ACTIVE;
2437: END IF;
2438: -- Else use the date from Database
2439:
2436: l_res_start_date_active := P_TerrRsc_Rec.START_DATE_ACTIVE;
2437: END IF;
2438: -- Else use the date from Database
2439:
2440: IF ( P_TerrRsc_Rec.END_DATE_ACTIVE IS NOT NULL AND P_TerrRsc_Rec.END_DATE_ACTIVE <> FND_API.G_MISS_DATE ) THEN
2441: l_res_end_date_active := P_TerrRsc_Rec.END_DATE_ACTIVE;
2442: END IF;
2443:
2444: -- Else use the date from Database
2441: l_res_end_date_active := P_TerrRsc_Rec.END_DATE_ACTIVE;
2442: END IF;
2443:
2444: -- Else use the date from Database
2445: IF ( P_TerrRsc_Rec.TERR_ID IS NOT NULL AND P_TerrRsc_Rec.TERR_ID <> FND_API.G_MISS_NUM ) THEN
2446: l_Terr_Id := P_TerrRsc_Rec.TERR_ID;
2447: END IF;
2448: -- Else use the date from Database
2449:
2448: -- Else use the date from Database
2449:
2450: EXCEPTION
2451: WHEN NO_DATA_FOUND THEN
2452: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2453: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2454: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'NO_DATA_FOUND Exception in Validate_Terr_Rsc ' || SQLERRM);
2455: END IF;
2456: WHEN OTHERS THEN
2453: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2454: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'NO_DATA_FOUND Exception in Validate_Terr_Rsc ' || SQLERRM);
2455: END IF;
2456: WHEN OTHERS THEN
2457: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2458: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2459: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Other Exception in Validate_Terr_Rsc ' || SQLERRM);
2460: END IF;
2461: END;
2465: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2466: FND_MESSAGE.Set_Name('JTF', 'JTY_RSC_INV_DATE_RANGE');
2467: FND_MSG_PUB.ADD;
2468: END IF;
2469: x_Return_Status := FND_API.G_RET_STS_ERROR ;
2470: END IF;
2471:
2472: -- Resource start and end active dates should fall in territory dates.
2473: BEGIN
2483: FND_MESSAGE.Set_Name('JTF', 'JTY_RSC_STARTDATE_NOT_VALID');
2484: FND_MESSAGE.Set_Token('RES_NAME', ' ' );
2485: FND_MSG_PUB.ADD;
2486: END IF;
2487: x_Return_Status := FND_API.G_RET_STS_ERROR ;
2488: END IF;
2489:
2490: -- Validate end date.
2491: IF ( l_res_end_date_active < l_terr_start_date ) OR ( l_res_end_date_active > l_terr_end_date ) THEN
2493: FND_MESSAGE.Set_Name('JTF', 'JTY_RSC_ENDDATE_NOT_VALID');
2494: FND_MESSAGE.Set_Token('RES_NAME', ' ' );
2495: FND_MSG_PUB.ADD;
2496: END IF;
2497: x_Return_Status := FND_API.G_RET_STS_ERROR ;
2498: END IF;
2499:
2500: EXCEPTION
2501: WHEN OTHERS THEN
2498: END IF;
2499:
2500: EXCEPTION
2501: WHEN OTHERS THEN
2502: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2503: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2504: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Others Exception in Validate_Terr_Rsc_update ' || SQLERRM);
2505: END IF;
2506: END;
2508: END IF;
2509:
2510: -- Validate last updated by
2511: IF ( p_TerrRsc_Rec.LAST_UPDATED_BY is NULL OR
2512: p_TerrRsc_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
2513: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2514: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
2515: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
2516: FND_MSG_PUB.ADD;
2514: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
2515: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
2516: FND_MSG_PUB.ADD;
2517: END IF;
2518: x_Return_Status := FND_API.G_RET_STS_ERROR ;
2519: End If;
2520:
2521: -- Check last update date
2522: If ( p_TerrRsc_Rec.LAST_UPDATE_DATE IS NULL OR
2519: End If;
2520:
2521: -- Check last update date
2522: If ( p_TerrRsc_Rec.LAST_UPDATE_DATE IS NULL OR
2523: p_TerrRsc_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
2524: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2525: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
2526: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
2527: FND_MSG_PUB.ADD;
2525: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
2526: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
2527: FND_MSG_PUB.ADD;
2528: END IF;
2529: x_Return_Status := FND_API.G_RET_STS_ERROR ;
2530: End If;
2531:
2532: --Check last update login
2533: If ( p_TerrRsc_Rec.LAST_UPDATE_LOGIN is NULL OR
2530: End If;
2531:
2532: --Check last update login
2533: If ( p_TerrRsc_Rec.LAST_UPDATE_LOGIN is NULL OR
2534: p_TerrRsc_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
2535: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2536: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
2537: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
2538: FND_MSG_PUB.ADD;
2536: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
2537: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
2538: FND_MSG_PUB.ADD;
2539: END IF;
2540: x_Return_Status := FND_API.G_RET_STS_ERROR ;
2541: End If;
2542:
2543: --
2544: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2544: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2545: p_data => x_msg_data);
2546: EXCEPTION
2547: --
2548: WHEN FND_API.G_EXC_ERROR THEN
2549: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_ERROR');
2550: x_return_status := FND_API.G_RET_STS_ERROR ;
2551: FND_MSG_PUB.Count_And_Get
2552: ( P_count => x_msg_count,
2545: p_data => x_msg_data);
2546: EXCEPTION
2547: --
2548: WHEN FND_API.G_EXC_ERROR THEN
2549: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_ERROR');
2550: x_return_status := FND_API.G_RET_STS_ERROR ;
2551: FND_MSG_PUB.Count_And_Get
2552: ( P_count => x_msg_count,
2553: P_data => x_msg_data
2546: EXCEPTION
2547: --
2548: WHEN FND_API.G_EXC_ERROR THEN
2549: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_ERROR');
2550: x_return_status := FND_API.G_RET_STS_ERROR ;
2551: FND_MSG_PUB.Count_And_Get
2552: ( P_count => x_msg_count,
2553: P_data => x_msg_data
2554: );
2552: ( P_count => x_msg_count,
2553: P_data => x_msg_data
2554: );
2555:
2556: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2557: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR');
2558: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2559: FND_MSG_PUB.Count_And_Get
2560: ( P_count => x_msg_count,
2553: P_data => x_msg_data
2554: );
2555:
2556: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2557: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR');
2558: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2559: FND_MSG_PUB.Count_And_Get
2560: ( P_count => x_msg_count,
2561: P_data => x_msg_data
2554: );
2555:
2556: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2557: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR');
2558: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2559: FND_MSG_PUB.Count_And_Get
2560: ( P_count => x_msg_count,
2561: P_data => x_msg_data
2562: );
2562: );
2563:
2564: WHEN OTHERS THEN
2565: --dbms_output.put_line('Validate_Terr_Qtype_Usage: OTHERS - ' || SQLERRM);
2566: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2567: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2568: THEN
2569: FND_MSG_PUB.Add_Exc_Msg
2570: ( G_PKG_NAME,
2603:
2604: PROCEDURE Update_Terr_Resource (
2605: P_TerrRsc_Rec IN TerrResource_Rec_type,
2606: p_Api_Version_Number IN NUMBER,
2607: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2608: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2609: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2610: x_Return_Status OUT NOCOPY VARCHAR2,
2611: x_Msg_Count OUT NOCOPY NUMBER,
2604: PROCEDURE Update_Terr_Resource (
2605: P_TerrRsc_Rec IN TerrResource_Rec_type,
2606: p_Api_Version_Number IN NUMBER,
2607: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2608: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2609: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2610: x_Return_Status OUT NOCOPY VARCHAR2,
2611: x_Msg_Count OUT NOCOPY NUMBER,
2612: x_Msg_Data OUT NOCOPY VARCHAR2,
2605: P_TerrRsc_Rec IN TerrResource_Rec_type,
2606: p_Api_Version_Number IN NUMBER,
2607: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2608: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2609: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2610: x_Return_Status OUT NOCOPY VARCHAR2,
2611: x_Msg_Count OUT NOCOPY NUMBER,
2612: x_Msg_Data OUT NOCOPY VARCHAR2,
2613: x_TerrRsc_Out_Rec OUT NOCOPY TerrResource_out_Rec_type) AS
2628:
2629: BEGIN
2630:
2631: -- Initialize API return status to SUCCESS
2632: x_return_status := FND_API.G_RET_STS_SUCCESS;
2633:
2634: -- Check for TERR_RSC_ID
2635: OPEN C_GetTerrResource( P_TerrRsc_Rec.TERR_RSC_ID);
2636: Fetch C_GetTerrResource into l_Rowid, l_ref_TerrRsc_Rec.TERR_RSC_ID, l_ref_TerrRsc_Rec.LAST_UPDATE_DATE,
2645: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
2646: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_TerrRsc_Rec.TERR_RSC_ID));
2647: FND_MSG_PUB.Add;
2648: END IF;
2649: raise FND_API.G_EXC_ERROR;
2650: End if;
2651: CLOSE C_GetTerrResource;
2652:
2653: --Validate the incomming record
2650: End if;
2651: CLOSE C_GetTerrResource;
2652:
2653: --Validate the incomming record
2654: IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE) THEN
2655: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2656: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
2657: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Rsc');
2658: FND_MSG_PUB.Add;
2658: FND_MSG_PUB.Add;
2659: END IF;
2660:
2661: -- Invoke validation procedures
2662: Validate_Terr_Rsc_update(p_init_msg_list => FND_API.G_FALSE,
2663: x_Return_Status => x_return_status,
2664: x_msg_count => x_msg_count,
2665: x_msg_data => x_msg_data,
2666: p_TerrRsc_Rec => P_TerrRsc_Rec);
2664: x_msg_count => x_msg_count,
2665: x_msg_data => x_msg_data,
2666: p_TerrRsc_Rec => P_TerrRsc_Rec);
2667:
2668: IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2669: RAISE FND_API.G_EXC_ERROR;
2670: END IF;
2671: END IF;
2672:
2665: x_msg_data => x_msg_data,
2666: p_TerrRsc_Rec => P_TerrRsc_Rec);
2667:
2668: IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2669: RAISE FND_API.G_EXC_ERROR;
2670: END IF;
2671: END IF;
2672:
2673: -- Check if the resource exist or not.
2675: x_Return_Status => l_return_status,
2676: x_msg_count => x_msg_count,
2677: x_Msg_Data => x_Msg_Data);
2678:
2679: IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2680: RAISE FND_API.G_EXC_ERROR;
2681: END IF;
2682:
2683: -- Check for duplicates.
2676: x_msg_count => x_msg_count,
2677: x_Msg_Data => x_Msg_Data);
2678:
2679: IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2680: RAISE FND_API.G_EXC_ERROR;
2681: END IF;
2682:
2683: -- Check for duplicates.
2684: Check_for_duplicate2_updates(P_TerrRsc_Rec => P_TerrRsc_Rec,
2685: x_Return_Status => l_return_status,
2686: x_msg_count => x_msg_count,
2687: x_Msg_Data => x_Msg_Data);
2688:
2689: IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2690: RAISE FND_API.G_EXC_ERROR;
2691: END IF;
2692:
2693:
2686: x_msg_count => x_msg_count,
2687: x_Msg_Data => x_Msg_Data);
2688:
2689: IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2690: RAISE FND_API.G_EXC_ERROR;
2691: END IF;
2692:
2693:
2694: JTF_TERR_RSC_PKG.Update_Row(x_Rowid => l_rowid,
2707: X_START_DATE_ACTIVE => P_TerrRsc_Rec.START_DATE_ACTIVE,
2708: X_END_DATE_ACTIVE => P_TerrRsc_Rec.END_DATE_ACTIVE,
2709: X_FULL_ACCESS_FLAG => P_TerrRsc_Rec.FULL_ACCESS_FLAG,
2710: -- ORG_ID can't be updated. -- VPALLE
2711: X_ORG_ID => FND_API.G_MISS_NUM,
2712: x_attribute_category => P_TerrRsc_Rec.attribute_category,
2713: x_attribute1 => P_TerrRsc_Rec.attribute1,
2714: x_attribute2 => P_TerrRsc_Rec.attribute2,
2715: x_attribute3 => P_TerrRsc_Rec.attribute3,
2730: -- Save the terr_usg_id and
2731: X_TerrRsc_Out_Rec.TERR_RSC_ID := P_TerrRsc_Rec.Terr_Rsc_Id;
2732:
2733: -- If successful then save the success status for the record
2734: X_TerrRsc_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
2735:
2736: EXCEPTION
2737: WHEN FND_API.G_EXC_ERROR THEN
2738: x_return_status := FND_API.G_RET_STS_ERROR ;
2733: -- If successful then save the success status for the record
2734: X_TerrRsc_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
2735:
2736: EXCEPTION
2737: WHEN FND_API.G_EXC_ERROR THEN
2738: x_return_status := FND_API.G_RET_STS_ERROR ;
2739: FND_MSG_PUB.Count_And_Get(P_count => x_msg_count,
2740: P_data => x_msg_data);
2741:
2734: X_TerrRsc_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
2735:
2736: EXCEPTION
2737: WHEN FND_API.G_EXC_ERROR THEN
2738: x_return_status := FND_API.G_RET_STS_ERROR ;
2739: FND_MSG_PUB.Count_And_Get(P_count => x_msg_count,
2740: P_data => x_msg_data);
2741:
2742: WHEN OTHERS THEN
2739: FND_MSG_PUB.Count_And_Get(P_count => x_msg_count,
2740: P_data => x_msg_data);
2741:
2742: WHEN OTHERS THEN
2743: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2744: x_TerrRsc_Out_Rec.TERR_RSC_ID := NULL;
2745: x_TerrRsc_Out_Rec.return_status := x_return_status;
2746: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2747: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others exception in Update_Territory_Resources');
2781: PROCEDURE Update_Terr_Resource
2782: (
2783: P_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
2784: p_Api_Version_Number IN NUMBER,
2785: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2786: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2787: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2788: x_Return_Status OUT NOCOPY VARCHAR2,
2789: x_Msg_Count OUT NOCOPY NUMBER,
2782: (
2783: P_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
2784: p_Api_Version_Number IN NUMBER,
2785: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2786: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2787: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2788: x_Return_Status OUT NOCOPY VARCHAR2,
2789: x_Msg_Count OUT NOCOPY NUMBER,
2790: x_Msg_Data OUT NOCOPY VARCHAR2,
2783: P_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
2784: p_Api_Version_Number IN NUMBER,
2785: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2786: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2787: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2788: x_Return_Status OUT NOCOPY VARCHAR2,
2789: x_Msg_Count OUT NOCOPY NUMBER,
2790: x_Msg_Data OUT NOCOPY VARCHAR2,
2791: X_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type
2802: BEGIN
2803: --dbms_output.put_line('Update_Terr_Resource TBL: Entering API');
2804:
2805: -- Initialize API return status to success
2806: x_return_status := FND_API.G_RET_STS_SUCCESS;
2807:
2808: -- Call overloaded Create_Terr_Qualifier procedure
2809: --
2810: FOR l_Counter IN 1 .. l_TerrRsc_Tbl_Count LOOP
2821: x_msg_data => x_msg_data,
2822: X_TerrRsc_Out_Rec => l_TerrRsc_Out_Rec);
2823: --
2824: --If there is a major error
2825: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2826: --dbms_output.put_line('Update_Terr_Resource TBL: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
2827: -- Save the terr_usg_id and
2828: X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := NULL;
2829:
2822: X_TerrRsc_Out_Rec => l_TerrRsc_Out_Rec);
2823: --
2824: --If there is a major error
2825: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2826: --dbms_output.put_line('Update_Terr_Resource TBL: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
2827: -- Save the terr_usg_id and
2828: X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := NULL;
2829:
2830: -- If save the ERROR status for the record
2827: -- Save the terr_usg_id and
2828: X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := NULL;
2829:
2830: -- If save the ERROR status for the record
2831: X_TerrRsc_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2832: ELSE
2833: --dbms_output.put_line('Update_Terr_Resource TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
2834: -- Save the terr_usg_id and
2835: X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := l_TerrRsc_Out_Rec.TERR_RSC_ID;
2829:
2830: -- If save the ERROR status for the record
2831: X_TerrRsc_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2832: ELSE
2833: --dbms_output.put_line('Update_Terr_Resource TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
2834: -- Save the terr_usg_id and
2835: X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := l_TerrRsc_Out_Rec.TERR_RSC_ID;
2836: -- If successful then save the success status for the record
2837: X_TerrRsc_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_SUCCESS;
2833: --dbms_output.put_line('Update_Terr_Resource TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
2834: -- Save the terr_usg_id and
2835: X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := l_TerrRsc_Out_Rec.TERR_RSC_ID;
2836: -- If successful then save the success status for the record
2837: X_TerrRsc_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_SUCCESS;
2838: END IF;
2839: --
2840: END LOOP;
2841:
2840: END LOOP;
2841:
2842: --Get the API overall return status
2843: -- Initialize API return status to success
2844: x_return_status := FND_API.G_RET_STS_SUCCESS;
2845:
2846: --Get number of records in the ouput table
2847: l_TerrRsc_Out_Tbl_Count := X_TerrRsc_Out_Tbl.Count;
2848:
2846: --Get number of records in the ouput table
2847: l_TerrRsc_Out_Tbl_Count := X_TerrRsc_Out_Tbl.Count;
2848:
2849: FOR l_Counter IN 1 .. l_TerrRsc_Out_Tbl_Count LOOP
2850: If x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
2851: x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
2852: THEN
2853: X_return_status := FND_API.G_RET_STS_ERROR;
2854: END IF;
2847: l_TerrRsc_Out_Tbl_Count := X_TerrRsc_Out_Tbl.Count;
2848:
2849: FOR l_Counter IN 1 .. l_TerrRsc_Out_Tbl_Count LOOP
2850: If x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
2851: x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
2852: THEN
2853: X_return_status := FND_API.G_RET_STS_ERROR;
2854: END IF;
2855: END LOOP;
2849: FOR l_Counter IN 1 .. l_TerrRsc_Out_Tbl_Count LOOP
2850: If x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
2851: x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
2852: THEN
2853: X_return_status := FND_API.G_RET_STS_ERROR;
2854: END IF;
2855: END LOOP;
2856: --dbms_output.put_line('Update_Terr_Resource TBL: Exiting API');
2857: --
2890: PROCEDURE Update_Resource_Access
2891: (
2892: P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC,
2893: p_Api_Version_Number IN NUMBER,
2894: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2895: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2896: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2897: x_Return_Status OUT NOCOPY VARCHAR2,
2898: x_Msg_Count OUT NOCOPY NUMBER,
2891: (
2892: P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC,
2893: p_Api_Version_Number IN NUMBER,
2894: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2895: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2896: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2897: x_Return_Status OUT NOCOPY VARCHAR2,
2898: x_Msg_Count OUT NOCOPY NUMBER,
2899: x_Msg_Data OUT NOCOPY VARCHAR2,
2892: P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC,
2893: p_Api_Version_Number IN NUMBER,
2894: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2895: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2896: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2897: x_Return_Status OUT NOCOPY VARCHAR2,
2898: x_Msg_Count OUT NOCOPY NUMBER,
2899: x_Msg_Data OUT NOCOPY VARCHAR2,
2900: X_TerrRsc_Access_Out_Rec OUT NOCOPY TerrRsc_Access_out_rec_type
2926: BEGIN
2927: --dbms_output.put_line('Update_Resource_Access REC: Entering API');
2928:
2929: -- Initialize API return status to SUCCESS
2930: x_return_status := FND_API.G_RET_STS_SUCCESS;
2931:
2932: IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE)
2933: THEN
2934: -- Debug message
2928:
2929: -- Initialize API return status to SUCCESS
2930: x_return_status := FND_API.G_RET_STS_SUCCESS;
2931:
2932: IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE)
2933: THEN
2934: -- Debug message
2935: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2936: THEN
2939: FND_MSG_PUB.Add;
2940: END IF;
2941: --
2942: -- Invoke validation procedures
2943: Validate_Terr_Rsc_Access(p_init_msg_list => FND_API.G_FALSE,
2944: x_Return_Status => l_return_status,
2945: x_msg_count => x_msg_count,
2946: x_msg_data => x_msg_data,
2947: p_TerrRsc_Id => P_TerrRsc_Access_Rec.terr_rsc_id,
2946: x_msg_data => x_msg_data,
2947: p_TerrRsc_Id => P_TerrRsc_Access_Rec.terr_rsc_id,
2948: p_TerrRsc_Access_Rec => P_TerrRsc_Access_Rec);
2949:
2950: IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2951: x_return_status := l_return_status;
2952: RAISE FND_API.G_EXC_ERROR;
2953: END IF;
2954: --
2948: p_TerrRsc_Access_Rec => P_TerrRsc_Access_Rec);
2949:
2950: IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2951: x_return_status := l_return_status;
2952: RAISE FND_API.G_EXC_ERROR;
2953: END IF;
2954: --
2955: END IF;
2956:
2976: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC_ACCESS');
2977: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID));
2978: FND_MSG_PUB.Add;
2979: END IF;
2980: raise FND_API.G_EXC_ERROR;
2981: End if;
2982: CLOSE C_GetTerrResAccess;
2983:
2984: -- jdochert 09/09
2986: /*
2987: validate_terr_rsc_access_UK(
2988: p_Terr_Rsc_Id => p_TerrRsc_access_rec.terr_rsc_id,
2989: p_Access_Type => p_TerrRsc_access_rec.access_type,
2990: p_init_msg_list => FND_API.G_FALSE,
2991: x_Return_Status => x_return_status,
2992: x_msg_count => x_msg_count,
2993: x_msg_data => x_msg_data );
2994:
2991: x_Return_Status => x_return_status,
2992: x_msg_count => x_msg_count,
2993: x_msg_data => x_msg_data );
2994:
2995: IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2996: RAISE FND_API.G_EXC_ERROR;
2997: END IF;
2998: */
2999: --
2992: x_msg_count => x_msg_count,
2993: x_msg_data => x_msg_data );
2994:
2995: IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2996: RAISE FND_API.G_EXC_ERROR;
2997: END IF;
2998: */
2999: --
3000: --dbms_output.put_line('Update_Resource_Access REC: Calling JTF_TERR_RSC_ACCESS_PKG.Insert_Row');
3008: x_TERR_RSC_ID => P_TerrRsc_Access_Rec.TERR_RSC_ID,
3009: x_ACCESS_TYPE => P_TerrRsc_Access_Rec.ACCESS_TYPE,
3010: x_TRANS_ACCESS_CODE => P_TerrRsc_Access_Rec.TRANS_ACCESS_CODE,
3011: -- ORG_ID can't be updated. -- VPALEE
3012: X_ORG_ID => FND_API.G_MISS_NUM );
3013:
3014: -- Save the terr_usg_id and
3015: X_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID := P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID;
3016: -- If successful then save the success status for the record
3013:
3014: -- Save the terr_usg_id and
3015: X_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID := P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID;
3016: -- If successful then save the success status for the record
3017: X_TerrRsc_Access_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
3018:
3019: --dbms_output.put_line('Update_Resource_Access REC: Exiting API');
3020: EXCEPTION
3021: WHEN FND_API.G_EXC_ERROR THEN
3017: X_TerrRsc_Access_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
3018:
3019: --dbms_output.put_line('Update_Resource_Access REC: Exiting API');
3020: EXCEPTION
3021: WHEN FND_API.G_EXC_ERROR THEN
3022: --dbms_output.put_line('Update_Resource_Access: FND_API.G_EXC_ERROR');
3023: x_return_status := FND_API.G_RET_STS_ERROR ;
3024: FND_MSG_PUB.Count_And_Get
3025: ( P_count => x_msg_count,
3018:
3019: --dbms_output.put_line('Update_Resource_Access REC: Exiting API');
3020: EXCEPTION
3021: WHEN FND_API.G_EXC_ERROR THEN
3022: --dbms_output.put_line('Update_Resource_Access: FND_API.G_EXC_ERROR');
3023: x_return_status := FND_API.G_RET_STS_ERROR ;
3024: FND_MSG_PUB.Count_And_Get
3025: ( P_count => x_msg_count,
3026: P_data => x_msg_data
3019: --dbms_output.put_line('Update_Resource_Access REC: Exiting API');
3020: EXCEPTION
3021: WHEN FND_API.G_EXC_ERROR THEN
3022: --dbms_output.put_line('Update_Resource_Access: FND_API.G_EXC_ERROR');
3023: x_return_status := FND_API.G_RET_STS_ERROR ;
3024: FND_MSG_PUB.Count_And_Get
3025: ( P_count => x_msg_count,
3026: P_data => x_msg_data
3027: );
3028:
3029: WHEN OTHERS THEN
3030: --dbms_output.put_line('Others exception in Update_Territory_Qualifiers');
3031: --
3032: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3033: --
3034: X_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID := NULL;
3035: X_TerrRsc_Access_Out_Rec.return_status := x_return_status;
3036: --
3076: PROCEDURE Update_Resource_Access
3077: (
3078: P_TerrRsc_Access_Tbl TerrRsc_Access_Tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
3079: p_Api_Version_Number IN NUMBER,
3080: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3081: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
3082: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3083: x_Return_Status OUT NOCOPY VARCHAR2,
3084: x_Msg_Count OUT NOCOPY NUMBER,
3077: (
3078: P_TerrRsc_Access_Tbl TerrRsc_Access_Tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
3079: p_Api_Version_Number IN NUMBER,
3080: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3081: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
3082: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3083: x_Return_Status OUT NOCOPY VARCHAR2,
3084: x_Msg_Count OUT NOCOPY NUMBER,
3085: x_Msg_Data OUT NOCOPY VARCHAR2,
3078: P_TerrRsc_Access_Tbl TerrRsc_Access_Tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
3079: p_Api_Version_Number IN NUMBER,
3080: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3081: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
3082: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3083: x_Return_Status OUT NOCOPY VARCHAR2,
3084: x_Msg_Count OUT NOCOPY NUMBER,
3085: x_Msg_Data OUT NOCOPY VARCHAR2,
3086: X_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_Tbl_type
3101: BEGIN
3102: --dbms_output.put_line('Update_Resource _Access REC: Entering API');
3103:
3104: -- Initialize API return status to success
3105: x_return_status := FND_API.G_RET_STS_SUCCESS;
3106:
3107: -- -- Call overloaded Update_Terr_Qualifier procedure
3108: --
3109: FOR l_Counter IN 1 .. l_TerrRsc_Access_Tbl_Count LOOP
3119: x_msg_data => x_msg_data,
3120: x_TerrRsc_Access_Out_Rec => l_TerrRsc_Access_Out_Rec);
3121: --
3122: --If there is a major error
3123: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3124: --dbms_output.put_line('Update_Resource _Access REC: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
3125: -- Save the terr_usg_id and
3126: X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := NULL;
3127: -- If save the ERROR status for the record
3120: x_TerrRsc_Access_Out_Rec => l_TerrRsc_Access_Out_Rec);
3121: --
3122: --If there is a major error
3123: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3124: --dbms_output.put_line('Update_Resource _Access REC: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
3125: -- Save the terr_usg_id and
3126: X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := NULL;
3127: -- If save the ERROR status for the record
3128: X_TerrRsc_Access_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3124: --dbms_output.put_line('Update_Resource _Access REC: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
3125: -- Save the terr_usg_id and
3126: X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := NULL;
3127: -- If save the ERROR status for the record
3128: X_TerrRsc_Access_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3129: ELSE
3130: --dbms_output.put_line('Update_Resource _Access REC: l_return_status = FND_API.G_RET_STS_SUCCESS');
3131: -- Save the terr_usg_id and
3132: X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := l_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID;
3126: X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := NULL;
3127: -- If save the ERROR status for the record
3128: X_TerrRsc_Access_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3129: ELSE
3130: --dbms_output.put_line('Update_Resource _Access REC: l_return_status = FND_API.G_RET_STS_SUCCESS');
3131: -- Save the terr_usg_id and
3132: X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := l_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID;
3133: -- If successful then save the success status for the record
3134: X_TerrRsc_Access_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_SUCCESS;
3130: --dbms_output.put_line('Update_Resource _Access REC: l_return_status = FND_API.G_RET_STS_SUCCESS');
3131: -- Save the terr_usg_id and
3132: X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := l_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID;
3133: -- If successful then save the success status for the record
3134: X_TerrRsc_Access_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_SUCCESS;
3135: END IF;
3136: --
3137: END LOOP;
3138:
3137: END LOOP;
3138:
3139: --Get the API overall return status
3140: --Initialize API return status to success
3141: x_return_status := FND_API.G_RET_STS_SUCCESS;
3142:
3143: --Get number of records in the ouput table
3144: l_TerrRscAcc_Out_Tbl_Count := X_TerrRsc_Access_Out_Tbl.Count;
3145:
3143: --Get number of records in the ouput table
3144: l_TerrRscAcc_Out_Tbl_Count := X_TerrRsc_Access_Out_Tbl.Count;
3145:
3146: FOR l_Counter IN 1 .. l_TerrRscAcc_Out_Tbl_Count LOOP
3147: If x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
3148: x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
3149: THEN
3150: X_return_status := FND_API.G_RET_STS_ERROR;
3151: END IF;
3144: l_TerrRscAcc_Out_Tbl_Count := X_TerrRsc_Access_Out_Tbl.Count;
3145:
3146: FOR l_Counter IN 1 .. l_TerrRscAcc_Out_Tbl_Count LOOP
3147: If x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
3148: x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
3149: THEN
3150: X_return_status := FND_API.G_RET_STS_ERROR;
3151: END IF;
3152: END LOOP;
3146: FOR l_Counter IN 1 .. l_TerrRscAcc_Out_Tbl_Count LOOP
3147: If x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
3148: x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
3149: THEN
3150: X_return_status := FND_API.G_RET_STS_ERROR;
3151: END IF;
3152: END LOOP;
3153: --
3154: End Update_Resource_Access;
3198: --
3199: --dbms_output.put_line('Inside Validate_Foreign_Keys');
3200:
3201: -- Initialize API return status to success
3202: x_return_status := FND_API.G_RET_STS_SUCCESS;
3203:
3204: For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
3205: --
3206: l_Terr_Id := p_TerrRsc_Tbl(l_res_counter).Terr_Id;
3216: EXCEPTION
3217: --
3218: WHEN NO_DATA_FOUND THEN
3219: --dbms_output.put_line('Validate_Foreign_Keys: NO_DATA_FOUND');
3220: x_return_status := FND_API.G_RET_STS_ERROR ;
3221: fnd_message.set_name('JTF', 'JTF_TERR_INVALID_TERRITORY');
3222: fnd_message.Set_Token('TERR_ID', to_char(l_Terr_Id) );
3223: FND_MSG_PUB.ADD;
3224: FND_MSG_PUB.Count_And_Get
3227: );
3228:
3229: WHEN OTHERS THEN
3230: --dbms_output.put_line('Validate_Foreign_Keys: OTHERS - ' || SQLERRM);
3231: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3232: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3233: THEN
3234: FND_MSG_PUB.Add_Exc_Msg
3235: ( G_PKG_NAME,
3282:
3283: BEGIN
3284: --
3285: --Initialize API return status to success
3286: x_return_status := FND_API.G_RET_STS_SUCCESS;
3287:
3288: For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
3289: --
3290: If p_TerrRsc_Tbl(l_Res_Counter).PRIMARY_CONTACT_FLAG = 'Y' Then
3297: If l_Primary_Count > 1 Then
3298: --
3299: fnd_message.set_name('JTF', 'JTF_TERR_MULTIPLE_PRIMARY_FLAG');
3300: FND_MSG_PUB.ADD;
3301: raise FND_API.G_EXC_ERROR;
3302: --
3303: End If;
3304: --
3305: EXCEPTION
3303: End If;
3304: --
3305: EXCEPTION
3306: --
3307: WHEN FND_API.G_EXC_ERROR THEN
3308: --dbms_output.put_line('Validate_Primary_Flag: FND_API.G_EXC_ERROR');
3309: x_return_status := FND_API.G_RET_STS_ERROR ;
3310: FND_MSG_PUB.Count_And_Get
3311: ( P_count => x_msg_count,
3304: --
3305: EXCEPTION
3306: --
3307: WHEN FND_API.G_EXC_ERROR THEN
3308: --dbms_output.put_line('Validate_Primary_Flag: FND_API.G_EXC_ERROR');
3309: x_return_status := FND_API.G_RET_STS_ERROR ;
3310: FND_MSG_PUB.Count_And_Get
3311: ( P_count => x_msg_count,
3312: P_data => x_msg_data
3305: EXCEPTION
3306: --
3307: WHEN FND_API.G_EXC_ERROR THEN
3308: --dbms_output.put_line('Validate_Primary_Flag: FND_API.G_EXC_ERROR');
3309: x_return_status := FND_API.G_RET_STS_ERROR ;
3310: FND_MSG_PUB.Count_And_Get
3311: ( P_count => x_msg_count,
3312: P_data => x_msg_data
3313: );
3311: ( P_count => x_msg_count,
3312: P_data => x_msg_data
3313: );
3314:
3315: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3316: --dbms_output.put_line('Validate_Primary_Flag: FND_API.G_EXC_UNEXPECTED_ERROR');
3317: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3318: FND_MSG_PUB.Count_And_Get
3319: ( P_count => x_msg_count,
3312: P_data => x_msg_data
3313: );
3314:
3315: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3316: --dbms_output.put_line('Validate_Primary_Flag: FND_API.G_EXC_UNEXPECTED_ERROR');
3317: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3318: FND_MSG_PUB.Count_And_Get
3319: ( P_count => x_msg_count,
3320: P_data => x_msg_data
3313: );
3314:
3315: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3316: --dbms_output.put_line('Validate_Primary_Flag: FND_API.G_EXC_UNEXPECTED_ERROR');
3317: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3318: FND_MSG_PUB.Count_And_Get
3319: ( P_count => x_msg_count,
3320: P_data => x_msg_data
3321: );
3321: );
3322:
3323: WHEN OTHERS THEN
3324: --dbms_output.put_line('Validate_Primary_Flag: OTHERS - ' || SQLERRM);
3325: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3326: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3327: THEN
3328: FND_MSG_PUB.Add_Exc_Msg
3329: ( G_PKG_NAME,
3375: --
3376: --dbms_output.put_line('Inside Validate_TerrResource_Data');
3377:
3378: -- Initialize API return status to success
3379: x_return_status := FND_API.G_RET_STS_SUCCESS;
3380:
3381: --ARPATEL: bug#2849410
3382: /*
3383: Check_for_duplicate (p_TerrRsc_Tbl => p_TerrRsc_Tbl,
3384: x_Return_Status => l_return_status,
3385: x_msg_count => x_msg_count,
3386: x_Msg_Data => x_Msg_Data);
3387:
3388: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3389: x_return_status := l_return_status;
3390: END IF;
3391: */
3392: -- If the territory resource records is missing
3394: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3395: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERRRES_REC');
3396: FND_MSG_PUB.ADD;
3397: END IF;
3398: x_return_status := FND_API.G_RET_STS_ERROR;
3399: raise FND_API.G_EXC_ERROR;
3400: END IF;
3401:
3402: -- Initialize API return status to success
3395: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERRRES_REC');
3396: FND_MSG_PUB.ADD;
3397: END IF;
3398: x_return_status := FND_API.G_RET_STS_ERROR;
3399: raise FND_API.G_EXC_ERROR;
3400: END IF;
3401:
3402: -- Initialize API return status to success
3403: x_return_status := FND_API.G_RET_STS_SUCCESS;
3399: raise FND_API.G_EXC_ERROR;
3400: END IF;
3401:
3402: -- Initialize API return status to success
3403: x_return_status := FND_API.G_RET_STS_SUCCESS;
3404:
3405:
3406: --dbms_output.put_line('Before Calling Validate_Primary_Flag');
3407: Validate_Primary_Flag(p_TerrRsc_Tbl => p_TerrRsc_Tbl ,
3409: x_Return_Status => l_Return_Status,
3410: x_msg_count => x_msg_count,
3411: x_Msg_Data => x_Msg_Data);
3412:
3413: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3414: x_return_status := l_return_status;
3415: END IF;
3416:
3417: --dbms_output.put_line('Before Calling Validate_Foreign_Keys');
3419: x_Return_Status => l_return_status,
3420: x_msg_count => x_msg_count,
3421: x_Msg_Data => x_Msg_Data);
3422:
3423: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3424: x_return_status := l_return_status;
3425: END IF;
3426: --
3427: EXCEPTION
3425: END IF;
3426: --
3427: EXCEPTION
3428: --
3429: WHEN FND_API.G_EXC_ERROR THEN
3430: --dbms_output.put_line('Validate_TerrResource_Data: FND_API.G_EXC_ERROR');
3431: x_return_status := FND_API.G_RET_STS_ERROR ;
3432: FND_MSG_PUB.Count_And_Get
3433: ( P_count => x_msg_count,
3426: --
3427: EXCEPTION
3428: --
3429: WHEN FND_API.G_EXC_ERROR THEN
3430: --dbms_output.put_line('Validate_TerrResource_Data: FND_API.G_EXC_ERROR');
3431: x_return_status := FND_API.G_RET_STS_ERROR ;
3432: FND_MSG_PUB.Count_And_Get
3433: ( P_count => x_msg_count,
3434: P_data => x_msg_data
3427: EXCEPTION
3428: --
3429: WHEN FND_API.G_EXC_ERROR THEN
3430: --dbms_output.put_line('Validate_TerrResource_Data: FND_API.G_EXC_ERROR');
3431: x_return_status := FND_API.G_RET_STS_ERROR ;
3432: FND_MSG_PUB.Count_And_Get
3433: ( P_count => x_msg_count,
3434: P_data => x_msg_data
3435: );
3433: ( P_count => x_msg_count,
3434: P_data => x_msg_data
3435: );
3436:
3437: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3438: --dbms_output.put_line('Validate_TerrResource_Data: FND_API.G_EXC_UNEXPECTED_ERROR');
3439: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3440: FND_MSG_PUB.Count_And_Get
3441: ( P_count => x_msg_count,
3434: P_data => x_msg_data
3435: );
3436:
3437: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3438: --dbms_output.put_line('Validate_TerrResource_Data: FND_API.G_EXC_UNEXPECTED_ERROR');
3439: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3440: FND_MSG_PUB.Count_And_Get
3441: ( P_count => x_msg_count,
3442: P_data => x_msg_data
3435: );
3436:
3437: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3438: --dbms_output.put_line('Validate_TerrResource_Data: FND_API.G_EXC_UNEXPECTED_ERROR');
3439: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3440: FND_MSG_PUB.Count_And_Get
3441: ( P_count => x_msg_count,
3442: P_data => x_msg_data
3443: );
3443: );
3444:
3445: WHEN OTHERS THEN
3446: --dbms_output.put_line('Validate_TerrResource_Data: OTHERS - ' || SQLERRM);
3447: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3448: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3449: THEN
3450: FND_MSG_PUB.Add_Exc_Msg
3451: ( G_PKG_NAME,
3465: ---------------------------------------------------------------------
3466:
3467: PROCEDURE Validate_Terr_Rsc
3468: (
3469: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3470: x_Return_Status OUT NOCOPY VARCHAR2,
3471: x_msg_count OUT NOCOPY NUMBER,
3472: x_msg_data OUT NOCOPY VARCHAR2,
3473: p_TerrRsc_Rec IN TerrResource_Rec_type
3481: BEGIN
3482: --dbms_output.put_line('Inside Validate_Terr_Rsc: Entering API');
3483:
3484: -- Initialize the status to success
3485: x_return_status := FND_API.G_RET_STS_SUCCESS;
3486:
3487: -- Validate the territory Id
3488: l_Validate_id := p_TerrRsc_Rec.Terr_Id;
3489: If l_Validate_id IS NOT NULL Then
3487: -- Validate the territory Id
3488: l_Validate_id := p_TerrRsc_Rec.Terr_Id;
3489: If l_Validate_id IS NOT NULL Then
3490: -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
3491: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
3492: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3493: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3494: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3495: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3488: l_Validate_id := p_TerrRsc_Rec.Terr_Id;
3489: If l_Validate_id IS NOT NULL Then
3490: -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
3491: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR_ALL') <> FND_API.G_TRUE Then
3492: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3493: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3494: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3495: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3496: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
3495: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3496: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
3497: FND_MSG_PUB.ADD;
3498: END IF;
3499: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3500: End If;
3501: Else
3502: -- Invalid Territory Id specified
3503: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3504: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3505: FND_MESSAGE.Set_Token('COL_NAME', 'TERR_ID' );
3506: FND_MSG_PUB.ADD;
3507: END IF;
3508: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3509: End If;
3510:
3511: --Check created by
3512: IF ( p_TerrRsc_Rec.CREATED_BY is NULL OR
3509: End If;
3510:
3511: --Check created by
3512: IF ( p_TerrRsc_Rec.CREATED_BY is NULL OR
3513: p_TerrRsc_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
3514: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3515: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3516: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
3517: FND_MSG_PUB.ADD;
3515: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3516: FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
3517: FND_MSG_PUB.ADD;
3518: END IF;
3519: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3520: End If;
3521: --Check creation date
3522: If ( p_TerrRsc_Rec.CREATION_DATE is NULL OR
3523: p_TerrRsc_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
3519: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3520: End If;
3521: --Check creation date
3522: If ( p_TerrRsc_Rec.CREATION_DATE is NULL OR
3523: p_TerrRsc_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
3524: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3525: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3526: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
3527: FND_MSG_PUB.ADD;
3525: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3526: FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
3527: FND_MSG_PUB.ADD;
3528: END IF;
3529: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3530: End If;
3531: --Check ORG_ID
3532: If ( p_TerrRsc_Rec.ORG_ID is NULL OR
3533: p_TerrRsc_Rec.ORG_ID = FND_API.G_MISS_NUM ) THEN
3529: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3530: End If;
3531: --Check ORG_ID
3532: If ( p_TerrRsc_Rec.ORG_ID is NULL OR
3533: p_TerrRsc_Rec.ORG_ID = FND_API.G_MISS_NUM ) THEN
3534: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3535: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3536: FND_MESSAGE.Set_Token('COL_NAME', 'ORG_ID' );
3537: FND_MSG_PUB.ADD;
3535: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3536: FND_MESSAGE.Set_Token('COL_NAME', 'ORG_ID' );
3537: FND_MSG_PUB.ADD;
3538: END IF;
3539: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3540: End If;
3541: --Check RESOURCE_ID
3542: If ( p_TerrRsc_Rec.RESOURCE_ID is NULL OR
3543: p_TerrRsc_Rec.RESOURCE_ID = FND_API.G_MISS_NUM ) THEN
3539: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3540: End If;
3541: --Check RESOURCE_ID
3542: If ( p_TerrRsc_Rec.RESOURCE_ID is NULL OR
3543: p_TerrRsc_Rec.RESOURCE_ID = FND_API.G_MISS_NUM ) THEN
3544: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3545: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3546: FND_MESSAGE.Set_Token('COL_NAME', 'RESOURCE_ID' );
3547: FND_MSG_PUB.ADD;
3545: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3546: FND_MESSAGE.Set_Token('COL_NAME', 'RESOURCE_ID' );
3547: FND_MSG_PUB.ADD;
3548: END IF;
3549: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3550: End If;
3551: --Check RESOURCE_TYPE
3552: If ( p_TerrRsc_Rec.RESOURCE_TYPE is NULL OR
3553: p_TerrRsc_Rec.RESOURCE_TYPE = FND_API.G_MISS_CHAR ) THEN
3549: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3550: End If;
3551: --Check RESOURCE_TYPE
3552: If ( p_TerrRsc_Rec.RESOURCE_TYPE is NULL OR
3553: p_TerrRsc_Rec.RESOURCE_TYPE = FND_API.G_MISS_CHAR ) THEN
3554: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3555: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3556: FND_MESSAGE.Set_Token('COL_NAME', 'RESOURCE_TYPE' );
3557: FND_MSG_PUB.ADD;
3555: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3556: FND_MESSAGE.Set_Token('COL_NAME', 'RESOURCE_TYPE' );
3557: FND_MSG_PUB.ADD;
3558: END IF;
3559: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3560: End If;
3561: --Check START_DATE_ACTIVE
3562: If ( p_TerrRsc_Rec.START_DATE_ACTIVE is NULL OR
3563: p_TerrRsc_Rec.START_DATE_ACTIVE = FND_API.G_MISS_DATE ) THEN
3559: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3560: End If;
3561: --Check START_DATE_ACTIVE
3562: If ( p_TerrRsc_Rec.START_DATE_ACTIVE is NULL OR
3563: p_TerrRsc_Rec.START_DATE_ACTIVE = FND_API.G_MISS_DATE ) THEN
3564: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3565: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3566: FND_MESSAGE.Set_Token('COL_NAME', 'START_DATE_ACTIVE' );
3567: FND_MSG_PUB.ADD;
3565: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3566: FND_MESSAGE.Set_Token('COL_NAME', 'START_DATE_ACTIVE' );
3567: FND_MSG_PUB.ADD;
3568: END IF;
3569: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3570: End If;
3571: --Check END_DATE_ACTIVE
3572: If ( p_TerrRsc_Rec.END_DATE_ACTIVE is NULL OR
3573: p_TerrRsc_Rec.END_DATE_ACTIVE = FND_API.G_MISS_DATE ) THEN
3569: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3570: End If;
3571: --Check END_DATE_ACTIVE
3572: If ( p_TerrRsc_Rec.END_DATE_ACTIVE is NULL OR
3573: p_TerrRsc_Rec.END_DATE_ACTIVE = FND_API.G_MISS_DATE ) THEN
3574: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3575: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3576: FND_MESSAGE.Set_Token('COL_NAME', 'END_DATE_ACTIVE' );
3577: FND_MSG_PUB.ADD;
3575: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3576: FND_MESSAGE.Set_Token('COL_NAME', 'END_DATE_ACTIVE' );
3577: FND_MSG_PUB.ADD;
3578: END IF;
3579: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3580: End If;
3581:
3582: IF (p_TerrRsc_Rec.START_DATE_ACTIVE IS NOT NULL AND p_TerrRsc_Rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE )
3583: AND (p_TerrRsc_Rec.END_DATE_ACTIVE IS NOT NULL AND p_TerrRsc_Rec.END_DATE_ACTIVE <> FND_API.G_MISS_DATE ) THEN
3578: END IF;
3579: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3580: End If;
3581:
3582: IF (p_TerrRsc_Rec.START_DATE_ACTIVE IS NOT NULL AND p_TerrRsc_Rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE )
3583: AND (p_TerrRsc_Rec.END_DATE_ACTIVE IS NOT NULL AND p_TerrRsc_Rec.END_DATE_ACTIVE <> FND_API.G_MISS_DATE ) THEN
3584:
3585: IF ( p_TerrRsc_Rec.START_DATE_ACTIVE > p_TerrRsc_Rec.END_DATE_ACTIVE ) THEN
3586: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3579: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3580: End If;
3581:
3582: IF (p_TerrRsc_Rec.START_DATE_ACTIVE IS NOT NULL AND p_TerrRsc_Rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE )
3583: AND (p_TerrRsc_Rec.END_DATE_ACTIVE IS NOT NULL AND p_TerrRsc_Rec.END_DATE_ACTIVE <> FND_API.G_MISS_DATE ) THEN
3584:
3585: IF ( p_TerrRsc_Rec.START_DATE_ACTIVE > p_TerrRsc_Rec.END_DATE_ACTIVE ) THEN
3586: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3587: FND_MESSAGE.Set_Name('JTF', 'JTY_RSC_INV_DATE_RANGE');
3586: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3587: FND_MESSAGE.Set_Name('JTF', 'JTY_RSC_INV_DATE_RANGE');
3588: FND_MSG_PUB.ADD;
3589: END IF;
3590: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3591: END IF;
3592:
3593: -- Resource start and end active dates should fall in territory dates.
3594: BEGIN
3604: FND_MESSAGE.Set_Name('JTF', 'JTY_RSC_STARTDATE_NOT_VALID');
3605: FND_MESSAGE.Set_Token('RES_NAME', ' ' );
3606: FND_MSG_PUB.ADD;
3607: END IF;
3608: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3609: END IF;
3610:
3611: -- Validate end date.
3612: IF ( p_TerrRsc_Rec.END_DATE_ACTIVE < l_terr_start_date ) OR ( p_TerrRsc_Rec.END_DATE_ACTIVE > l_terr_end_date ) THEN
3614: FND_MESSAGE.Set_Name('JTF', 'JTY_RSC_ENDDATE_NOT_VALID');
3615: FND_MESSAGE.Set_Token('RES_NAME', ' ' );
3616: FND_MSG_PUB.ADD;
3617: END IF;
3618: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3619: END IF;
3620:
3621: EXCEPTION
3622: WHEN OTHERS THEN
3619: END IF;
3620:
3621: EXCEPTION
3622: WHEN OTHERS THEN
3623: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3624: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3625: FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Others Exception in Validate_Terr_Rsc ' || SQLERRM);
3626: END IF;
3627: END;
3629: END IF;
3630:
3631: -- Validate last updated by
3632: IF ( p_TerrRsc_Rec.LAST_UPDATED_BY is NULL OR
3633: p_TerrRsc_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
3634: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3635: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3636: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
3637: FND_MSG_PUB.ADD;
3635: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3636: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
3637: FND_MSG_PUB.ADD;
3638: END IF;
3639: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3640: End If;
3641:
3642: -- Check last update date
3643: If ( p_TerrRsc_Rec.LAST_UPDATE_DATE IS NULL OR
3640: End If;
3641:
3642: -- Check last update date
3643: If ( p_TerrRsc_Rec.LAST_UPDATE_DATE IS NULL OR
3644: p_TerrRsc_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
3645: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3646: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3647: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
3648: FND_MSG_PUB.ADD;
3646: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3647: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
3648: FND_MSG_PUB.ADD;
3649: END IF;
3650: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3651: End If;
3652:
3653: --Check last update login
3654: If ( p_TerrRsc_Rec.LAST_UPDATE_LOGIN is NULL OR
3651: End If;
3652:
3653: --Check last update login
3654: If ( p_TerrRsc_Rec.LAST_UPDATE_LOGIN is NULL OR
3655: p_TerrRsc_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
3656: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3657: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3658: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
3659: FND_MSG_PUB.ADD;
3657: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3658: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
3659: FND_MSG_PUB.ADD;
3660: END IF;
3661: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3662: End If;
3663:
3664: --
3665: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
3665: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
3666: p_data => x_msg_data);
3667: EXCEPTION
3668: --
3669: WHEN FND_API.G_EXC_ERROR THEN
3670: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_ERROR');
3671: x_return_status := FND_API.G_RET_STS_ERROR ;
3672: FND_MSG_PUB.Count_And_Get
3673: ( P_count => x_msg_count,
3666: p_data => x_msg_data);
3667: EXCEPTION
3668: --
3669: WHEN FND_API.G_EXC_ERROR THEN
3670: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_ERROR');
3671: x_return_status := FND_API.G_RET_STS_ERROR ;
3672: FND_MSG_PUB.Count_And_Get
3673: ( P_count => x_msg_count,
3674: P_data => x_msg_data
3667: EXCEPTION
3668: --
3669: WHEN FND_API.G_EXC_ERROR THEN
3670: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_ERROR');
3671: x_return_status := FND_API.G_RET_STS_ERROR ;
3672: FND_MSG_PUB.Count_And_Get
3673: ( P_count => x_msg_count,
3674: P_data => x_msg_data
3675: );
3673: ( P_count => x_msg_count,
3674: P_data => x_msg_data
3675: );
3676:
3677: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3678: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR');
3679: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3680: FND_MSG_PUB.Count_And_Get
3681: ( P_count => x_msg_count,
3674: P_data => x_msg_data
3675: );
3676:
3677: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3678: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR');
3679: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3680: FND_MSG_PUB.Count_And_Get
3681: ( P_count => x_msg_count,
3682: P_data => x_msg_data
3675: );
3676:
3677: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3678: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR');
3679: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3680: FND_MSG_PUB.Count_And_Get
3681: ( P_count => x_msg_count,
3682: P_data => x_msg_data
3683: );
3683: );
3684:
3685: WHEN OTHERS THEN
3686: --dbms_output.put_line('Validate_Terr_Qtype_Usage: OTHERS - ' || SQLERRM);
3687: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3688: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3689: THEN
3690: FND_MSG_PUB.Add_Exc_Msg
3691: ( G_PKG_NAME,
3704: ---------------------------------------------------------------------
3705:
3706: PROCEDURE Validate_Terr_Rsc_Access
3707: (
3708: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3709: x_Return_Status OUT NOCOPY VARCHAR2,
3710: x_msg_count OUT NOCOPY NUMBER,
3711: x_msg_data OUT NOCOPY VARCHAR2,
3712: p_TerrRsc_Id IN NUMBER,
3720: BEGIN
3721: --dbms_output.put_line('Inside Validate_Terr_Rsc_Access: TERR_RSC_ID ' || to_char(p_TerrRsc_Access_Rec.TERR_RSC_ID) );
3722:
3723: -- Initialize the status to success
3724: x_return_status := FND_API.G_RET_STS_SUCCESS;
3725:
3726: -- Validate the territory Id
3727: l_Validate_id := p_TerrRsc_Id;
3728: If l_Validate_id IS NOT NULL Then
3726: -- Validate the territory Id
3727: l_Validate_id := p_TerrRsc_Id;
3728: If l_Validate_id IS NOT NULL Then
3729: -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
3730: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_RSC_ID', 'JTF_TERR_RSC_ALL') <> FND_API.G_TRUE Then
3731: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3732: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3733: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3734: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
3727: l_Validate_id := p_TerrRsc_Id;
3728: If l_Validate_id IS NOT NULL Then
3729: -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
3730: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_RSC_ID', 'JTF_TERR_RSC_ALL') <> FND_API.G_TRUE Then
3731: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3732: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3733: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3734: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
3735: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_RSC_ID');
3734: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
3735: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_RSC_ID');
3736: FND_MSG_PUB.ADD;
3737: END IF;
3738: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3739: End If;
3740: Else
3741: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3742: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3742: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3743: FND_MESSAGE.Set_Token('COL_NAME', 'TERR_RSC_ID' );
3744: FND_MSG_PUB.ADD;
3745: END IF;
3746: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3747: End If;
3748:
3749: -- Validate the access type
3750: IF ( p_TerrRsc_Access_Rec.ACCESS_TYPE IS NOT NULL AND
3747: End If;
3748:
3749: -- Validate the access type
3750: IF ( p_TerrRsc_Access_Rec.ACCESS_TYPE IS NOT NULL AND
3751: p_TerrRsc_Access_Rec.ACCESS_TYPE <> FND_API.G_MISS_CHAR ) THEN
3752: BEGIN
3753: select 'x' into l_Temp
3754: from JTF_QUAL_TYPES jqt
3755: Where jqt.NAME = p_TerrRsc_Access_Rec.ACCESS_TYPE;
3760: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3761: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_QUAL_TYPES');
3762: FND_MESSAGE.Set_Token('COLUMN_NAME', 'ACCESS_TYPE');
3763: FND_MSG_PUB.ADD;
3764: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3765: END;
3766: END IF;
3767: -- Validate the TRANS_ACCESS_CODE
3768: IF ( p_TerrRsc_Access_Rec.TRANS_ACCESS_CODE is NOT NULL AND
3765: END;
3766: END IF;
3767: -- Validate the TRANS_ACCESS_CODE
3768: IF ( p_TerrRsc_Access_Rec.TRANS_ACCESS_CODE is NOT NULL AND
3769: p_TerrRsc_Access_Rec.TRANS_ACCESS_CODE <> FND_API.G_MISS_CHAR ) THEN
3770: BEGIN
3771: SELECT 'X' INTO l_Temp
3772: FROM ( select DISTINCT lookup_code LOOKUP_CODE
3773: from fnd_lookups
3781: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3782: FND_MESSAGE.Set_Token('TABLE_NAME', 'FND_LOOKUPS');
3783: FND_MESSAGE.Set_Token('COLUMN_NAME', 'TRANS_ACCESS_CODE');
3784: FND_MSG_PUB.ADD;
3785: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3786: END;
3787: END IF;
3788:
3789: -- Validate last updated by
3787: END IF;
3788:
3789: -- Validate last updated by
3790: IF ( p_TerrRsc_Access_Rec.LAST_UPDATED_BY is NULL OR
3791: p_TerrRsc_Access_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
3792: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3793: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3794: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
3795: FND_MSG_PUB.ADD;
3793: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3794: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
3795: FND_MSG_PUB.ADD;
3796: END IF;
3797: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3798: End If;
3799:
3800: -- Check last update date
3801: If ( p_TerrRsc_Access_Rec.LAST_UPDATE_DATE IS NULL OR
3798: End If;
3799:
3800: -- Check last update date
3801: If ( p_TerrRsc_Access_Rec.LAST_UPDATE_DATE IS NULL OR
3802: p_TerrRsc_Access_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
3803: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3804: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3805: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
3806: FND_MSG_PUB.ADD;
3804: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3805: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
3806: FND_MSG_PUB.ADD;
3807: END IF;
3808: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3809: End If;
3810:
3811: --Check last update login
3812: If ( p_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN is NULL OR
3809: End If;
3810:
3811: --Check last update login
3812: If ( p_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN is NULL OR
3813: p_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
3814: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3815: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3816: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
3817: FND_MSG_PUB.ADD;
3815: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3816: FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
3817: FND_MSG_PUB.ADD;
3818: END IF;
3819: x_Return_Status := FND_API.G_RET_STS_ERROR ;
3820: End If;
3821:
3822: --
3823: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
3823: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
3824: p_data => x_msg_data);
3825: EXCEPTION
3826: --
3827: WHEN FND_API.G_EXC_ERROR THEN
3828: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_ERROR');
3829: x_return_status := FND_API.G_RET_STS_ERROR ;
3830: FND_MSG_PUB.Count_And_Get
3831: ( P_count => x_msg_count,
3824: p_data => x_msg_data);
3825: EXCEPTION
3826: --
3827: WHEN FND_API.G_EXC_ERROR THEN
3828: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_ERROR');
3829: x_return_status := FND_API.G_RET_STS_ERROR ;
3830: FND_MSG_PUB.Count_And_Get
3831: ( P_count => x_msg_count,
3832: P_data => x_msg_data
3825: EXCEPTION
3826: --
3827: WHEN FND_API.G_EXC_ERROR THEN
3828: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_ERROR');
3829: x_return_status := FND_API.G_RET_STS_ERROR ;
3830: FND_MSG_PUB.Count_And_Get
3831: ( P_count => x_msg_count,
3832: P_data => x_msg_data
3833: );
3831: ( P_count => x_msg_count,
3832: P_data => x_msg_data
3833: );
3834:
3835: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3836: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR');
3837: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3838: FND_MSG_PUB.Count_And_Get
3839: ( P_count => x_msg_count,
3832: P_data => x_msg_data
3833: );
3834:
3835: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3836: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR');
3837: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3838: FND_MSG_PUB.Count_And_Get
3839: ( P_count => x_msg_count,
3840: P_data => x_msg_data
3833: );
3834:
3835: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3836: --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR');
3837: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3838: FND_MSG_PUB.Count_And_Get
3839: ( P_count => x_msg_count,
3840: P_data => x_msg_data
3841: );
3841: );
3842:
3843: WHEN OTHERS THEN
3844: --dbms_output.put_line('Validate_Terr_Qtype_Usage: OTHERS - ' || SQLERRM);
3845: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3846: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3847: THEN
3848: FND_MSG_PUB.Add_Exc_Msg
3849: ( G_PKG_NAME,
4206: AND rownum < 2;
4207:
4208: BEGIN
4209:
4210: IF ( p_group_id = FND_API.G_MISS_NUM OR
4211: p_group_id IS NULL) THEN
4212:
4213: /* no group_id so return NULL */
4214: RETURN NULL;
4315: -- IN :
4316: -- Required
4317: -- Parameter Name Data Type Default
4318: -- p_Api_Version_Number IN NUMBER,
4319: -- p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4320: -- p_Commit IN VARCHAR2 := FND_API.G_FALSE,
4321: -- p_source_terr_id NUMBER := G_MISS_NUM
4322: -- p_dest_terr_id NUMBER := G_MISS_NUM
4323: --
4316: -- Required
4317: -- Parameter Name Data Type Default
4318: -- p_Api_Version_Number IN NUMBER,
4319: -- p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4320: -- p_Commit IN VARCHAR2 := FND_API.G_FALSE,
4321: -- p_source_terr_id NUMBER := G_MISS_NUM
4322: -- p_dest_terr_id NUMBER := G_MISS_NUM
4323: --
4324: -- Optional
4336: --
4337: PROCEDURE Copy_Terr_Resources
4338: (
4339: p_Api_Version_Number IN NUMBER,
4340: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4341: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
4342: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4343: p_source_terr_id IN NUMBER,
4344: p_dest_terr_id IN NUMBER,
4337: PROCEDURE Copy_Terr_Resources
4338: (
4339: p_Api_Version_Number IN NUMBER,
4340: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4341: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
4342: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4343: p_source_terr_id IN NUMBER,
4344: p_dest_terr_id IN NUMBER,
4345: x_msg_count OUT NOCOPY NUMBER,
4338: (
4339: p_Api_Version_Number IN NUMBER,
4340: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4341: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
4342: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4343: p_source_terr_id IN NUMBER,
4344: p_dest_terr_id IN NUMBER,
4345: x_msg_count OUT NOCOPY NUMBER,
4346: x_msg_data OUT NOCOPY VARCHAR2,
4405: SAVEPOINT COPY_TERR_RESOURCES;
4406:
4407: /*
4408: -- Standard call to check for call compatibility.
4409: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4410: p_api_version_number,
4411: l_api_name,
4412: G_PKG_NAME)
4413: THEN
4410: p_api_version_number,
4411: l_api_name,
4412: G_PKG_NAME)
4413: THEN
4414: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4415: END IF;
4416:
4417:
4418: -- Initialize message list if p_init_msg_list is set to TRUE.
4415: END IF;
4416:
4417:
4418: -- Initialize message list if p_init_msg_list is set to TRUE.
4419: IF FND_API.to_Boolean( p_init_msg_list ) THEN
4420: FND_MSG_PUB.initialize;
4421: END IF;
4422: */
4423:
4430: FND_MSG_PUB.Add;
4431: END IF;
4432:
4433: -- Initialize API return status to success
4434: x_return_status := FND_API.G_RET_STS_SUCCESS;
4435:
4436:
4437: -- API Body starts here
4438:
4472: x_Msg_Data => x_Msg_Data,
4473: x_TerrRsc_Out_Rec => l_TerrRsc_Out_rec
4474: );
4475:
4476: IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
4477:
4478: /*
4479: dbms_output.put_line(
4480: 'Unexpected Execution Error from call to Create Terr Resource API');
4481: */
4482:
4483: fnd_message.set_name('JTF', 'JTF_ERROR_TERRRSC_API');
4484: fnd_msg_pub.add;
4485: RAISE fnd_api.g_exc_unexpected_error;
4486:
4487: END IF;
4488:
4489: l_dest_terr_rsc_id := l_TerrRsc_Out_rec.terr_rsc_id;
4511: (
4512: p_TerrRsc_Id => l_dest_terr_rsc_id,
4513: p_TerrRsc_Access_Rec => l_TerrRsc_Access_rec,
4514: p_Api_Version_Number => l_api_version_number,
4515: p_Init_Msg_List => FND_API.G_FALSE,
4516: p_Commit => FND_API.G_FALSE,
4517: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4518: x_Return_Status => x_Return_Status,
4519: x_Msg_Count => x_Msg_Count,
4512: p_TerrRsc_Id => l_dest_terr_rsc_id,
4513: p_TerrRsc_Access_Rec => l_TerrRsc_Access_rec,
4514: p_Api_Version_Number => l_api_version_number,
4515: p_Init_Msg_List => FND_API.G_FALSE,
4516: p_Commit => FND_API.G_FALSE,
4517: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4518: x_Return_Status => x_Return_Status,
4519: x_Msg_Count => x_Msg_Count,
4520: x_Msg_Data => x_Msg_Data,
4513: p_TerrRsc_Access_Rec => l_TerrRsc_Access_rec,
4514: p_Api_Version_Number => l_api_version_number,
4515: p_Init_Msg_List => FND_API.G_FALSE,
4516: p_Commit => FND_API.G_FALSE,
4517: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4518: x_Return_Status => x_Return_Status,
4519: x_Msg_Count => x_Msg_Count,
4520: x_Msg_Data => x_Msg_Data,
4521: x_TerrRsc_Access_Out_Rec => l_TerrRsc_Access_Out_rec
4520: x_Msg_Data => x_Msg_Data,
4521: x_TerrRsc_Access_Out_Rec => l_TerrRsc_Access_Out_rec
4522: );
4523:
4524: IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
4525: /*
4526: dbms_output.put_line(
4527: 'Unexpected Execution Error from call to Create Terr Resource Access API');
4528: */
4527: 'Unexpected Execution Error from call to Create Terr Resource Access API');
4528: */
4529: fnd_message.set_name('JTF', 'JTF_ERROR_TERRRSCACCESS_API');
4530: fnd_msg_pub.add;
4531: RAISE fnd_api.g_exc_unexpected_error;
4532: END IF;
4533:
4534: END LOOP;
4535: CLOSE csr_rsc_access_all;
4554: );
4555:
4556:
4557: EXCEPTION
4558: WHEN FND_API.G_EXC_ERROR THEN
4559: --dbms_output.put_line('Copy Territory Resources : FND_API.G_EXC_ERROR');
4560: x_return_status := FND_API.G_RET_STS_ERROR ;
4561: FND_MSG_PUB.Count_And_Get
4562: ( P_count => x_msg_count,
4555:
4556:
4557: EXCEPTION
4558: WHEN FND_API.G_EXC_ERROR THEN
4559: --dbms_output.put_line('Copy Territory Resources : FND_API.G_EXC_ERROR');
4560: x_return_status := FND_API.G_RET_STS_ERROR ;
4561: FND_MSG_PUB.Count_And_Get
4562: ( P_count => x_msg_count,
4563: P_data => x_msg_data
4556:
4557: EXCEPTION
4558: WHEN FND_API.G_EXC_ERROR THEN
4559: --dbms_output.put_line('Copy Territory Resources : FND_API.G_EXC_ERROR');
4560: x_return_status := FND_API.G_RET_STS_ERROR ;
4561: FND_MSG_PUB.Count_And_Get
4562: ( P_count => x_msg_count,
4563: P_data => x_msg_data
4564: );
4562: ( P_count => x_msg_count,
4563: P_data => x_msg_data
4564: );
4565:
4566: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4567: --dbms_output.put_line('Copy Territory Resources : FND_API.G_EXC_UNEXPECTED_ERROR');
4568: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4569: FND_MSG_PUB.Count_And_Get
4570: ( P_count => x_msg_count,
4563: P_data => x_msg_data
4564: );
4565:
4566: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4567: --dbms_output.put_line('Copy Territory Resources : FND_API.G_EXC_UNEXPECTED_ERROR');
4568: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4569: FND_MSG_PUB.Count_And_Get
4570: ( P_count => x_msg_count,
4571: P_data => x_msg_data
4564: );
4565:
4566: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4567: --dbms_output.put_line('Copy Territory Resources : FND_API.G_EXC_UNEXPECTED_ERROR');
4568: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4569: FND_MSG_PUB.Count_And_Get
4570: ( P_count => x_msg_count,
4571: P_data => x_msg_data
4572: );
4572: );
4573:
4574: WHEN OTHERS THEN
4575: --dbms_output.put_line('Copy Territory Resources : OTHERS - ' || SQLERRM);
4576: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4577: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
4578: THEN
4579: FND_MSG_PUB.Add_Exc_Msg
4580: ( G_PKG_NAME,
4592: */
4593: PROCEDURE validate_terr_rsc_access_UK(
4594: p_Terr_Rsc_Id IN NUMBER,
4595: p_Access_Type IN VARCHAR2,
4596: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4597: x_Return_Status OUT NOCOPY VARCHAR2,
4598: x_msg_count OUT NOCOPY NUMBER,
4599: x_msg_data OUT NOCOPY VARCHAR2 )
4600: AS
4612: BEGIN
4613:
4614: --dbms_output('Validate_Unique_Key: Entering API');
4615: -- Initialize the status to success
4616: x_return_status := FND_API.G_RET_STS_SUCCESS;
4617:
4618: /* check that Unique Key constraint not violated */
4619: IF ( p_terr_rsc_id IS NOT NULL AND p_terr_rsc_id <> FND_API.G_MISS_NUM AND
4620: p_access_type IS NOT NULL AND p_access_type <> FND_API.G_MISS_CHAR ) THEN
4615: -- Initialize the status to success
4616: x_return_status := FND_API.G_RET_STS_SUCCESS;
4617:
4618: /* check that Unique Key constraint not violated */
4619: IF ( p_terr_rsc_id IS NOT NULL AND p_terr_rsc_id <> FND_API.G_MISS_NUM AND
4620: p_access_type IS NOT NULL AND p_access_type <> FND_API.G_MISS_CHAR ) THEN
4621:
4622: /* check if rec already exists */
4623: OPEN csr_chk_uk_violation ( p_terr_rsc_id
4616: x_return_status := FND_API.G_RET_STS_SUCCESS;
4617:
4618: /* check that Unique Key constraint not violated */
4619: IF ( p_terr_rsc_id IS NOT NULL AND p_terr_rsc_id <> FND_API.G_MISS_NUM AND
4620: p_access_type IS NOT NULL AND p_access_type <> FND_API.G_MISS_CHAR ) THEN
4621:
4622: /* check if rec already exists */
4623: OPEN csr_chk_uk_violation ( p_terr_rsc_id
4624: , p_access_type);
4625: FETCH csr_chk_uk_violation INTO l_return_csr;
4626:
4627: IF csr_chk_uk_violation%FOUND THEN
4628:
4629: x_return_status := FND_API.G_RET_STS_ERROR;
4630:
4631: /* Debug message */
4632: --arpatel bug#1500581 (part of fix)
4633: --IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4647: EXCEPTION
4648:
4649: WHEN OTHERS THEN
4650: --dbms_output('Validate_Foreign_Key: Others exception' || SQLERRM);
4651: X_return_status := FND_API.G_RET_STS_ERROR;
4652: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
4653: FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Unique_Key' );
4654: FND_MESSAGE.Set_Token('ERROR', sqlerrm );
4655: FND_MSG_PUB.ADD;
4675: -- IN :
4676: -- Required
4677: -- Parameter Name Data Type Default
4678: -- p_Api_Version_Number IN NUMBER,
4679: -- p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4680: -- p_Commit IN VARCHAR2 := FND_API.G_FALSE,
4681: -- p_source_resource_rec TerrResource_Rec_type
4682: -- p_p_dest_resource_rec TerrResource_Rec_type
4683: -- p_all_terr_flag IN VARCHAR2 := 'Y',
4676: -- Required
4677: -- Parameter Name Data Type Default
4678: -- p_Api_Version_Number IN NUMBER,
4679: -- p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4680: -- p_Commit IN VARCHAR2 := FND_API.G_FALSE,
4681: -- p_source_resource_rec TerrResource_Rec_type
4682: -- p_p_dest_resource_rec TerrResource_Rec_type
4683: -- p_all_terr_flag IN VARCHAR2 := 'Y',
4684: -- p_terr_ids_tbl IN Terr_Ids_Tbl_Type,
4701: --
4702: PROCEDURE Transfer_Resource_Territories
4703: (
4704: p_Api_Version_Number IN NUMBER,
4705: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4706: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
4707: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4708: p_source_resource_rec IN TerrResource_Rec_type,
4709: p_dest_resource_rec IN TerrResource_Rec_type,
4702: PROCEDURE Transfer_Resource_Territories
4703: (
4704: p_Api_Version_Number IN NUMBER,
4705: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4706: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
4707: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4708: p_source_resource_rec IN TerrResource_Rec_type,
4709: p_dest_resource_rec IN TerrResource_Rec_type,
4710: p_all_terr_flag IN VARCHAR2 := 'Y',
4703: (
4704: p_Api_Version_Number IN NUMBER,
4705: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4706: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
4707: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4708: p_source_resource_rec IN TerrResource_Rec_type,
4709: p_dest_resource_rec IN TerrResource_Rec_type,
4710: p_all_terr_flag IN VARCHAR2 := 'Y',
4711: p_terr_ids_tbl IN Terr_Ids_Tbl_Type,
4751: SAVEPOINT TRANSFER_TERR_RES;
4752:
4753:
4754: -- Standard call to check for call compatibility.
4755: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4756: p_api_version_number,
4757: l_api_name,
4758: G_PKG_NAME)
4759: THEN
4756: p_api_version_number,
4757: l_api_name,
4758: G_PKG_NAME)
4759: THEN
4760: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4761: END IF;
4762:
4763:
4764: -- Initialize message list if p_init_msg_list is set to TRUE.
4761: END IF;
4762:
4763:
4764: -- Initialize message list if p_init_msg_list is set to TRUE.
4765: IF FND_API.to_Boolean( p_init_msg_list ) THEN
4766: FND_MSG_PUB.initialize;
4767: END IF;
4768:
4769:
4776: FND_MSG_PUB.Add;
4777: END IF;
4778:
4779: -- Initialize API return status to success
4780: x_return_status := FND_API.G_RET_STS_SUCCESS;
4781:
4782:
4783: -- API Body starts here
4784: -- create list of the source resource territories
4821: FULL_ACCESS_FLAG,
4822: ORG_ID
4823: ) VALUES (
4824: JTF_TERR_RSC_s.nextval,
4825: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4826: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4827: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4828: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4829: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4822: ORG_ID
4823: ) VALUES (
4824: JTF_TERR_RSC_s.nextval,
4825: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4826: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4827: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4828: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4829: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4830: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4823: ) VALUES (
4824: JTF_TERR_RSC_s.nextval,
4825: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4826: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4827: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4828: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4829: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4830: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4831: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4824: JTF_TERR_RSC_s.nextval,
4825: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4826: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4827: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4828: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4829: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4830: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4831: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4832: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4825: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4826: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4827: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4828: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4829: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4830: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4831: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4832: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4833: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4826: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4827: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4828: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4829: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4830: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4831: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4832: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4833: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4834: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4827: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4828: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4829: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4830: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4831: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4832: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4833: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4834: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4835: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4828: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4829: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4830: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4831: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4832: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4833: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4834: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4835: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4836: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4829: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4830: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4831: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4832: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4833: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4834: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4835: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4836: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4837: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4830: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4831: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4832: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4833: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4834: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4835: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4836: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4837: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4838: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4831: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4832: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4833: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4834: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4835: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4836: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4837: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4838: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4839: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4832: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4833: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4834: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4835: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4836: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4837: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4838: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4839: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4840: );
4833: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4834: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4835: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4836: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4837: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4838: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4839: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4840: );
4841:
4834: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4835: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4836: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4837: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4838: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4839: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4840: );
4841:
4842:
4835: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4836: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4837: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4838: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4839: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4840: );
4841:
4842:
4843: ELSIF p_replace_flag = 'Y'
4864: FULL_ACCESS_FLAG,
4865: ORG_ID
4866: ) VALUES (
4867: JTF_TERR_RSC_s.nextval,
4868: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4869: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4870: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4871: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4872: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4865: ORG_ID
4866: ) VALUES (
4867: JTF_TERR_RSC_s.nextval,
4868: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4869: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4870: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4871: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4872: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4873: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4866: ) VALUES (
4867: JTF_TERR_RSC_s.nextval,
4868: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4869: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4870: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4871: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4872: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4873: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4874: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4867: JTF_TERR_RSC_s.nextval,
4868: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4869: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4870: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4871: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4872: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4873: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4874: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4875: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4868: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4869: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4870: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4871: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4872: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4873: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4874: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4875: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4876: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4869: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4870: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4871: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4872: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4873: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4874: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4875: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4876: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4877: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4870: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4871: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4872: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4873: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4874: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4875: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4876: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4877: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4878: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4871: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4872: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4873: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4874: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4875: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4876: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4877: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4878: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4879: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4872: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4873: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4874: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4875: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4876: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4877: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4878: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4879: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4880: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4873: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4874: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4875: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4876: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4877: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4878: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4879: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4880: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4881: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4874: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4875: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4876: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4877: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4878: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4879: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4880: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4881: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4882: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4875: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4876: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4877: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4878: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4879: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4880: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4881: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4882: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4883: );
4876: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4877: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4878: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4879: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4880: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4881: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4882: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4883: );
4884:
4877: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4878: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4879: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4880: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4881: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4882: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4883: );
4884:
4885: --ARPATEL: 11/06/2003 BUG#2798581 START
4878: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4879: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4880: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4881: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4882: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4883: );
4884:
4885: --ARPATEL: 11/06/2003 BUG#2798581 START
4886: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4950: FULL_ACCESS_FLAG,
4951: ORG_ID
4952: ) VALUES (
4953: JTF_TERR_RSC_s.nextval,
4954: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4955: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4956: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4957: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4958: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4951: ORG_ID
4952: ) VALUES (
4953: JTF_TERR_RSC_s.nextval,
4954: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4955: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4956: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4957: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4958: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4959: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4952: ) VALUES (
4953: JTF_TERR_RSC_s.nextval,
4954: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4955: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4956: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4957: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4958: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4959: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4960: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4953: JTF_TERR_RSC_s.nextval,
4954: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4955: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4956: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4957: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4958: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4959: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4960: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4961: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4954: decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4955: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4956: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4957: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4958: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4959: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4960: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4961: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4962: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4955: decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4956: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4957: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4958: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4959: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4960: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4961: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4962: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4963: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4956: decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4957: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4958: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4959: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4960: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4961: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4962: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4963: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4964: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4957: decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4958: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4959: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4960: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4961: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4962: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4963: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4964: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4965: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4958: decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4959: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4960: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4961: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4962: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4963: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4964: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4965: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4966: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4959: decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4960: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4961: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4962: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4963: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4964: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4965: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4966: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4967: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4960: decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4961: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4962: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4963: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4964: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4965: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4966: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4967: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4968: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4961: decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4962: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4963: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4964: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4965: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4966: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4967: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4968: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4969: );
4962: decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4963: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4964: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4965: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4966: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4967: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4968: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4969: );
4970:
4963: decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4964: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4965: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4966: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4967: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4968: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4969: );
4970:
4971: --ARPATEL: 11/06/2003 BUG#2798581 START
4964: decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4965: decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4966: decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4967: decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4968: decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4969: );
4970:
4971: --ARPATEL: 11/06/2003 BUG#2798581 START
4972: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5034: p_data => x_msg_data
5035: );
5036:
5037: -- Standard check for p_commit
5038: IF FND_API.to_Boolean( p_commit )
5039: THEN
5040: COMMIT WORK;
5041: END IF;
5042:
5042:
5043: --dbms_output.put_line('Transfer_Resource_Territories: Exiting API');
5044: EXCEPTION
5045: --
5046: WHEN FND_API.G_EXC_ERROR THEN
5047: --dbms_output.put_line('Transfer_Resource_Territories: FND_API.G_EXC_ERROR');
5048: ROLLBACK TO TRANSFER_TERR_RES;
5049: x_return_status := FND_API.G_RET_STS_ERROR ;
5050: FND_MSG_PUB.Count_And_Get
5043: --dbms_output.put_line('Transfer_Resource_Territories: Exiting API');
5044: EXCEPTION
5045: --
5046: WHEN FND_API.G_EXC_ERROR THEN
5047: --dbms_output.put_line('Transfer_Resource_Territories: FND_API.G_EXC_ERROR');
5048: ROLLBACK TO TRANSFER_TERR_RES;
5049: x_return_status := FND_API.G_RET_STS_ERROR ;
5050: FND_MSG_PUB.Count_And_Get
5051: ( p_count => x_msg_count,
5045: --
5046: WHEN FND_API.G_EXC_ERROR THEN
5047: --dbms_output.put_line('Transfer_Resource_Territories: FND_API.G_EXC_ERROR');
5048: ROLLBACK TO TRANSFER_TERR_RES;
5049: x_return_status := FND_API.G_RET_STS_ERROR ;
5050: FND_MSG_PUB.Count_And_Get
5051: ( p_count => x_msg_count,
5052: p_data => x_msg_data
5053: );
5051: ( p_count => x_msg_count,
5052: p_data => x_msg_data
5053: );
5054:
5055: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5056: --dbms_output.put_line('Transfer_Resource_Territories: FND_API.G_EXC_UNEXPECTED_ERROR');
5057: ROLLBACK TO TRANSFER_TERR_RES;
5058: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5059: FND_MSG_PUB.Count_And_Get
5052: p_data => x_msg_data
5053: );
5054:
5055: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5056: --dbms_output.put_line('Transfer_Resource_Territories: FND_API.G_EXC_UNEXPECTED_ERROR');
5057: ROLLBACK TO TRANSFER_TERR_RES;
5058: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5059: FND_MSG_PUB.Count_And_Get
5060: ( p_count => x_msg_count,
5054:
5055: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5056: --dbms_output.put_line('Transfer_Resource_Territories: FND_API.G_EXC_UNEXPECTED_ERROR');
5057: ROLLBACK TO TRANSFER_TERR_RES;
5058: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5059: FND_MSG_PUB.Count_And_Get
5060: ( p_count => x_msg_count,
5061: p_data => x_msg_data
5062: );
5063:
5064: WHEN OTHERS THEN
5065: --dbms_output.put_line('Transfer_Resource_Territories PVT: OTHERS - ' || SQLERRM);
5066: ROLLBACK TO TRANSFER_TERR_RES;
5067: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5068: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
5069: THEN
5070: fnd_msg_pub.add_exc_msg (
5071: g_pkg_name,