[Home] [Help]
PACKAGE BODY: APPS.JTF_TERRITORY_RESOURCE_PVT
Source
1 Package Body JTF_TERRITORY_RESOURCE_PVT AS
2 /* $Header: jtfvtrsb.pls 120.12 2008/06/18 09:46:52 vpalle ship $ */
3
4 -- ---------------------------------------------------
5 -- Start of Comments
6 -- ---------------------------------------------------
7 -- PACKAGE NAME: JTF_TERRITORY_RESOURCE_PVT
8 -- ---------------------------------------------------
9 -- PURPOSE
10 -- Joint task force core territory resource private api's.
11 -- This package is a private API for inserting territory
12 -- resources into JTF tables. It contains specification
13 -- for pl/sql records and tables related to territory
14 -- resource.
15 --
16 -- Procedures:
17 -- (see below for specification)
18 --
19 -- NOTES
20 -- This package is for PRIVATE USE ONLY use
21 --
22 -- HISTORY
23 -- 07/29/99 VNEDUNGA Created
24 -- 12/22/99 NEDUNGA Making changes to confirm to
25 -- JTF_TERR_RSC_ALL table change
26 -- 01/06/00 VNEDUNGA Fixing problem with the build rule
27 -- expression
28 -- 01/16/00 VNEDUNGA Commenting out dbms_output
29 -- 01/17/00 VNEDUNGA Cahnging the the hard code value for
30 -- resourece qualifer type from 1 to -1001
31 -- 02/10/00 VNEDUNGA Changing call to table handlers
32 -- 03/15/00 VNEDUNGA Fixng the messaging and record validation
33 -- 06/08/00 VNEDUNGA Adding group id column to resource record
34 --
35 -- 06/12/00 JDOCHERT Added function (get_group_name)
36 -- to get the name
37 -- of the group that the resource
38 -- belongs to
39 --
40 -- 07/20/00 JDOCHERT Changed as follows in Create_TerrResource
41 -- as this meant that a terr_rsc_id passed
42 -- into Create API was ignored:
43 -- l_terr_type_id := 0;
44 -- TO
45 -- L_TerrRsc_Id NUMBER := P_TERRRSC_REC.TERR_RSC_ID;
46 --
47 -- 09/16/00 VVUYYURU Added the NEW procedure Copy_Terr_Resources
48 --
49 -- 09/19/00 JDOCHERT Added 'validate_terr_rsc_access_UK'
50 -- and 'Transfer_Resource_Territories' procedures
51 --
52 -- 10/04/00 JDOCHERT Added get_rs_type_name function
53 --
54 -- 02/15/01 ARPATEL Adapted 'Transfer_Resource_Territories' to allow mass updates
55 -- 09/04/01 ARPATEL Adapted 'Transfer_Resource_Territories' to allow mass assignment of unallocated terrs
56 -- 05/30/01 ARPATEL Added commit processing to transfer_resource_territories and removed from JTFTRMRU.fmb form
57 -- 05/30/01 ARPATEL Added end_date_active checks for cursors of transfer_resource_territories
58 -- 06/06/01 ARPATEL Changed SYSDATE-1 to SYSDATE in transfer_resource_territories
59 -- 06/14/01 ARPATEL Taken out start/end date active clauses in transfer_resource_territories cursors.
60 -- 04/06/04 SHLI Took out check_for_duplicate2 from update_terr_resource.
61 -- 04/13/04 VXSRINIV Added new proc check_for_duplicate2_updates and called from update_terr_resource.
62 -- 09/15/05 mhtran added TRANS_ACCESS_CODE
63 --
64 -- End of Comments
65
66
67
68
69 -- ***************************************************
70 -- GLOBAL VARIABLES
71 -- ***************************************************
72 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_TERRITORY_RESOURCE_PVT';
73 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfvtrsb.pls';
74
75
76 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
77 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
78 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
79 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
80 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
81
82
83
84 --Vai: Bug # 3520561
85 PROCEDURE Check_for_duplicate2_updates (
86 P_TerrRsc_Rec IN TerrResource_Rec_type,
87 x_Return_Status OUT NOCOPY VARCHAR2,
88 x_msg_count OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2) AS
90
91 l_start_date_active DATE;
92 l_end_date_active DATE;
93 l_index NUMBER := 0;
94 l_Res_Counter NUMBER := 0;
95 l_Temp VARCHAR2(1);
96 l_Terr_Id NUMBER;
97
98 --check if duplicate resource_id, group, role exists for this territory
99 cursor c_res (p_terr_id NUMBER)is
100 Select JTR2.start_date_active, nvl(JTR2.end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
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;
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
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
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
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;
145
146 FOR l_c_res IN c_res(l_Terr_Id) LOOP
147
148 IF l_start_date_active IS NOT NULL AND l_end_date_active IS NOT NULL THEN
149
150 IF l_start_date_active BETWEEN l_c_res.start_date_active AND l_c_res.end_date_active THEN
151 l_temp := 'X';
152 EXIT;
153 END IF;
154
155 IF l_end_date_active BETWEEN l_c_res.start_date_active AND l_c_res.end_date_active THEN
156 l_temp := 'X';
157 EXIT;
158 END IF;
159
160 IF l_c_res.start_date_active BETWEEN l_start_date_active AND l_end_date_active THEN
161 l_temp := 'X';
162 EXIT;
163 END IF;
164
165 IF l_c_res.end_date_active BETWEEN l_start_date_active AND l_end_date_active THEN
166 l_temp := 'X';
167 EXIT;
168 END IF;
169
170 END IF;
171
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);
181 end if;
182
183 EXCEPTION
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
193 END Check_for_duplicate2_updates;
194
195 /* ARPATEL: bug#2849410 fix */
196 PROCEDURE Check_for_duplicate2 (
197 P_TerrRsc_Rec IN TerrResource_Rec_type,
198 x_Return_Status OUT NOCOPY VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_msg_data OUT NOCOPY VARCHAR2) AS
201 l_index NUMBER := 0;
202 l_Res_Counter NUMBER := 0;
203 l_Temp VARCHAR2(1);
204 l_Terr_Id NUMBER;
205
206 cursor c_res is
207 Select start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
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) ) )
217 OR (P_TerrRsc_Rec.ROLE = ROLE));
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
227
228 IF P_TerrRsc_Rec.START_DATE_ACTIVE BETWEEN l_c_res.start_date_active AND l_c_res.end_date_active THEN
229 l_temp := 'X';
230 EXIT;
231 END IF;
232
233 IF P_TerrRsc_Rec.END_DATE_ACTIVE BETWEEN l_c_res.start_date_active AND l_c_res.end_date_active THEN
234 l_temp := 'X';
235 EXIT;
236 END IF;
237
238 IF l_c_res.start_date_active BETWEEN P_TerrRsc_Rec.START_DATE_ACTIVE AND P_TerrRsc_Rec.END_DATE_ACTIVE THEN
239 l_temp := 'X';
240 EXIT;
241 END IF;
242
243 IF l_c_res.end_date_active BETWEEN P_TerrRsc_Rec.START_DATE_ACTIVE AND P_TerrRsc_Rec.END_DATE_ACTIVE THEN
244 l_temp := 'X';
245 EXIT;
246 END IF;
247
248 END IF;
249
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;
259
260 EXCEPTION
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;
270
271 PROCEDURE convert_terrrsc_wflex (
272 p_terrrsc_tbl_wflex IN TerrResource_tbl_type_wflex,
273 x_terrrsc_tbl OUT NOCOPY TerrResource_tbl_type
274 )
275 AS
276 l_counter NUMBER;
277 BEGIN
278 -- If the table is empty
279 IF p_terrrsc_tbl_wflex.COUNT = 0
280 THEN
281 RETURN;
282 END IF;
283
284 --
285 FOR l_counter IN 1 .. p_terrrsc_tbl_wflex.COUNT
286 LOOP
287 --
288 x_terrrsc_tbl (l_counter).terr_rsc_id :=
289 p_terrrsc_tbl_wflex (l_counter).terr_rsc_id;
290 x_terrrsc_tbl (l_counter).last_update_date :=
291 p_terrrsc_tbl_wflex (l_counter).last_update_date;
292 x_terrrsc_tbl (l_counter).last_updated_by :=
293 p_terrrsc_tbl_wflex (l_counter).last_updated_by;
294 x_terrrsc_tbl (l_counter).creation_date :=
295 p_terrrsc_tbl_wflex (l_counter).creation_date;
296 x_terrrsc_tbl (l_counter).created_by :=
297 p_terrrsc_tbl_wflex (l_counter).created_by;
298 x_terrrsc_tbl (l_counter).last_update_login :=
299 p_terrrsc_tbl_wflex (l_counter).last_update_login;
300 x_terrrsc_tbl (l_counter).terr_id :=
301 p_terrrsc_tbl_wflex (l_counter).terr_id;
302 x_terrrsc_tbl (l_counter).resource_id :=
303 p_terrrsc_tbl_wflex (l_counter).resource_id;
304 x_terrrsc_tbl (l_counter).group_id :=
305 p_terrrsc_tbl_wflex (l_counter).group_id;
306 x_terrrsc_tbl (l_counter).resource_type :=
307 p_terrrsc_tbl_wflex (l_counter).resource_type;
308 x_terrrsc_tbl (l_counter).role := p_terrrsc_tbl_wflex (l_counter).role;
309 x_terrrsc_tbl (l_counter).primary_contact_flag :=
310 p_terrrsc_tbl_wflex (l_counter).primary_contact_flag;
311 x_terrrsc_tbl (l_counter).start_date_active :=
312 p_terrrsc_tbl_wflex (l_counter).start_date_active;
313 x_terrrsc_tbl (l_counter).end_date_active :=
314 p_terrrsc_tbl_wflex (l_counter).end_date_active;
315 x_terrrsc_tbl (l_counter).full_access_flag :=
316 p_terrrsc_tbl_wflex (l_counter).full_access_flag;
317 x_terrrsc_tbl (l_counter).org_id := p_terrrsc_tbl_wflex (l_counter).org_id;
318 x_terrrsc_tbl (l_counter).ATTRIBUTE_CATEGORY := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE_CATEGORY;
319 x_terrrsc_tbl (l_counter).ATTRIBUTE1 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE1;
320 x_terrrsc_tbl (l_counter).ATTRIBUTE2 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE2;
321 x_terrrsc_tbl (l_counter).ATTRIBUTE3 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE3;
322 x_terrrsc_tbl (l_counter).ATTRIBUTE4 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE4;
323 x_terrrsc_tbl (l_counter).ATTRIBUTE5 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE5;
324 x_terrrsc_tbl (l_counter).ATTRIBUTE6 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE6;
325 x_terrrsc_tbl (l_counter).ATTRIBUTE7 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE7;
326 x_terrrsc_tbl (l_counter).ATTRIBUTE8 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE8;
327 x_terrrsc_tbl (l_counter).ATTRIBUTE9 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE9;
328 x_terrrsc_tbl (l_counter).ATTRIBUTE10 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE10;
329 x_terrrsc_tbl (l_counter).ATTRIBUTE11 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE11;
330 x_terrrsc_tbl (l_counter).ATTRIBUTE12 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE12;
331 x_terrrsc_tbl (l_counter).ATTRIBUTE13 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE13;
332 x_terrrsc_tbl (l_counter).ATTRIBUTE14 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE14;
333 x_terrrsc_tbl (l_counter).ATTRIBUTE15 := p_terrrsc_tbl_wflex (l_counter).ATTRIBUTE15;
334 END LOOP;
335 --
336 END convert_terrrsc_wflex;
337
338
339
340 -- ***************************************************
341 -- ***************************************************
342 -- start of comments
343 -- ***************************************************
344 -- API name : Create_TerrResource
345 -- Type : PUBLIC
346 -- Function : To create Territory Resources - which will insert
347 -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
348 -- tables.
349 --
350 -- Pre-reqs :
351 -- Parameters:
352 -- IN :
353 -- Required
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
363 -- p_Commit VARCHAR2 := FND_API.G_FALSE
364 --
365 -- OUT :
366 -- Parameter Name Data Type
367 -- x_Return_Status VARCHAR2(1)
368 -- x_Msg_Count NUMBER
369 -- x_Msg_Data VARCHAR2(2000)
370 -- x_TerrRsc_Id NUMBER
371 -- x_Terr_Usgs_Out_Tbl TerrResource_out_tbl_type
372 -- x_Terr_QualTypeUsgs_Out_Tbl TerrRes_Access_out_tbl_type
373 --
374 -- Notes:
375 --
376 --
377 -- End of Comments
378 --
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,
388 x_Msg_Data OUT NOCOPY VARCHAR2,
389 p_TerrRsc_Tbl IN TerrResource_tbl_type_wflex := G_MISS_TERRRESOURCE_TBL_WFLEX,
390 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
391 x_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type,
392 x_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_tbl_type
393 )
394 IS
395 l_api_name CONSTANT VARCHAR2(30) := 'Create_TerrResource';
396 l_api_version_number CONSTANT NUMBER := 1.0;
397 l_return_status VARCHAR2(1);
398 l_Res_Counter NUMBER;
399 l_Res_Access_Counter NUMBER;
400 l_TerrRsc_Tbl TerrResource_tbl_type;
401 --
402 BEGIN
403 --dbms_output.put_line('Create_TerrResource PVT: Entering API');
404
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
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
423 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
424 THEN
425 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
426 fnd_message.set_name ('PROC_NAME', l_api_name);
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
436 convert_terrrsc_wflex (
437 p_terrrsc_tbl_wflex => p_terrrsc_tbl,
438 x_terrrsc_tbl => l_TerrRsc_Tbl
439 );
440 --
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,
450 x_msg_data => x_msg_data,
451 p_terrrsc_tbl => l_TerrRsc_Tbl,
452 p_terrrsc_access_tbl => p_TerrRsc_Access_Tbl,
453 x_terrrsc_out_tbl => x_TerrRsc_Out_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
463 RAISE fnd_api.g_exc_unexpected_error;
464 END IF;
465 --
466 -- End of API body.
467 --
468 -- Debug Message
469 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
470 THEN
471 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
472 fnd_message.set_name ('PROC_NAME', l_api_name);
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
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,
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,
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,
511 'Error inside Create_TerrResource ' || sqlerrm);
512 END IF;
513 --
514 END Create_TerrResource;
515
516 -- start of comments
517 -- ***************************************************
518 -- API name : Create_TerrResource
519 -- Type : PUBLIC
520 -- Function : To create Territory Resources - which will insert
521 -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
522 -- tables.
523 --
524 -- Pre-reqs :
525 -- Parameters:
526 -- IN :
527 -- Required
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
537 -- p_Commit VARCHAR2 := FND_API.G_FALSE
538 --
539 -- OUT :
540 -- Parameter Name Data Type
541 -- x_Return_Status VARCHAR2(1)
542 -- x_Msg_Count NUMBER
543 -- x_Msg_Data VARCHAR2(2000)
544 -- x_TerrRsc_Id NUMBER
545 -- x_Terr_Usgs_Out_Tbl TerrResource_out_tbl_type
546 -- x_Terr_QualTypeUsgs_Out_Tbl TerrRes_Access_out_tbl_type
547 --
548 -- Notes:
549 --
550 --
551 -- End of Comments
552 --
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,
562 x_Msg_Data OUT NOCOPY VARCHAR2,
563 p_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
564 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
565 x_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type,
566 x_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_tbl_type
567 )
568 IS
569 l_api_name CONSTANT VARCHAR2(30) := 'Create_TerrResource';
570 l_api_version_number CONSTANT NUMBER := 1.0;
571 l_return_status VARCHAR2(1);
572 l_Res_Counter NUMBER;
573 l_Res_Access_Counter NUMBER;
574 l_Res_def_Acc_Counter NUMBER;
575 l_TerrRsc_Tbl TerrResource_tbl_type;
576 l_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type;
577 l_TerrRsc_def_Acc_Tbl TerrRsc_Access_tbl_type;
578 l_TerrRsc_Out_Tbl TerrResource_out_tbl_type;
579 l_TerrRsc_Access_Out_Tbl TerrRsc_Access_out_tbl_type;
580 l_terrRsc_Id NUMBER := 0;
581 l_index NUMBER := 0;
582 l_Counter NUMBER := 0;
583 l_terr_res_access VARCHAR2 (20) ;
584 l_trans_access_code VARCHAR2 (20);
585
586 CURSOR C_TERR_RES_ACCESS (p_terr_id NUMBER)
587 IS
588 SELECT NAME
589 FROM JTF_TERR_QTYPE_USGS_all jtqu,
590 jtf_qual_type_usgs_all jqtu ,
591 jtf_qual_types_all jqt
592 WHERE jtqu.terr_id = p_terr_id
593 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
594 AND jqt.qual_type_id = jqtu.qual_type_id;
595
596 --
597 BEGIN
598 --dbms_output.put_line('Create_TerrResource PVT: Entering API');
599
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
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
618 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
619 THEN
620 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
621 fnd_message.set_name ('PROC_NAME', l_api_name);
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,
640 p_TerrRsc_Access_Tbl => p_TerrRsc_Access_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;
650 --
651 --
652 -- If incomming data is good
653 -- Start creating territory related records
654 --
655 --dbms_output.put_line('Create_TerrResource PVT: Before Calling Create_Terr_Resource PVT');
656 --
657 For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
658 --
659 l_TerrRsc_Tbl(1) := p_TerrRsc_Tbl(l_Res_Counter);
660 l_TerrRsc_Access_Tbl.Delete;
661 l_index := 0;
662 --
663 --dbms_output.put_line('Inside the for loop');
664 --
665 IF p_TerrRsc_Access_Tbl.count > 0 THEN
666 For l_Res_Access_Counter IN p_TerrRsc_Access_Tbl.first .. p_TerrRsc_Access_Tbl.count LOOP
667 --dbms_output.put_line('Inside Values loop - ' || to_char(l_Res_Access_Counter) );
668 -- If the table index changes, then skip the loop
669 If p_TerrRsc_Access_Tbl(l_Res_Access_Counter).qualifier_tbl_index = l_Res_Counter Then
670 l_index := l_index + 1;
671 --dbms_output.put_line('Found values - ' || to_char(l_Res_Counter) || ' Index - ' || to_char(l_index) );
672 l_TerrRsc_Access_Tbl(l_index) := p_TerrRsc_Access_Tbl(l_Res_Access_Counter);
673 End If;
674 END LOOP;
675 END IF;
676
677 --dbms_output.put_line('Before calling create Territory Resource');
678 --
679 -- Create the territory qualifier record
680 --
681 Create_Terr_Resource(P_TerrRsc_Tbl => l_TerrRsc_Tbl,
682 p_api_version_number => p_api_version_number,
683 p_init_msg_list => p_init_msg_list,
684 p_commit => p_commit,
685 p_validation_level => p_validation_level,
686 x_return_status => l_return_status,
687 x_msg_count => x_msg_count,
688 x_msg_data => x_msg_data,
689 X_TerrRsc_Out_Tbl => l_TerrRsc_Out_Tbl);
690
691
692 --Save the output status
693 x_TerrRsc_Out_Tbl(nvl(x_TerrRsc_Out_Tbl.first, 0)+1) := l_TerrRsc_Out_Tbl(1);
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
703 -- Get into this loop only if there are access records found
704 If l_TerrRsc_Access_Tbl.Count > 0 Then
705 --dbms_output.put_line('l_TerrRsc_Access_Tbl.Count > 0. Before calling Create_TerrResc_Access');
706 --
707 Create_Resource_Access(p_TerrRsc_Id => l_TerrRsc_Id,
708 p_TerrRsc_Access_Tbl => l_TerrRsc_Access_Tbl,
709 p_api_version_number => p_api_version_number,
710 p_init_msg_list => p_init_msg_list,
711 p_commit => p_commit,
712 p_validation_level => p_validation_level,
713 x_return_status => l_return_status,
714 x_msg_count => x_msg_count,
715 x_msg_data => x_msg_data,
716 x_TerrRsc_Access_Out_Tbl => l_TerrRsc_Access_Out_Tbl);
717 --
718 -- Get the last index used
719 l_index := x_TerrRsc_Access_Out_Tbl.Count;
720 --
721 -- Save the OUT parameters to the original PAI out parametrs
722 For l_Counter IN l_TerrRsc_Access_Out_Tbl.first .. l_TerrRsc_Access_Out_Tbl.count LOOP
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
732 l_TerrRsc_Tbl := G_MISS_TERRRESOURCE_TBL;
733 l_TerrRsc_Access_Tbl := G_MISS_TERRRSC_ACCESS_TBL;
734 ELSE
735 -- Get the default trans_access_code for the usage.
736 BEGIN
737 SELECT DECODE(source_id, '-1001' , 'FULL_ACCESS' , 'DEFAULT' )
738 INTO l_trans_access_code
739 FROM jtf_terr_usgs_all WHERE terr_id = l_TerrRsc_Tbl(1).terr_id ;
740 EXCEPTION
741 WHEN OTHERS THEN
742 NULL;
743 END;
744 -- For Every Resource, create the defualt access as FULL_ACCESS for all
745 -- access types.
746 BEGIN
747 l_TerrRsc_def_Acc_Tbl.DELETE;
748 l_Res_def_Acc_Counter := 1;
749 OPEN C_TERR_RES_ACCESS (l_TerrRsc_Tbl(1).terr_id);
750 LOOP
751 FETCH C_TERR_RES_ACCESS
752 INTO l_terr_res_access;
753 EXIT WHEN C_TERR_RES_ACCESS%NOTFOUND;
754 l_TerrRsc_def_Acc_Tbl (l_Res_def_Acc_Counter).terr_rsc_access_id := NULL;
755 l_TerrRsc_def_Acc_Tbl (l_Res_def_Acc_Counter).last_update_date := l_TerrRsc_Tbl(1).last_update_date;
756 l_TerrRsc_def_Acc_Tbl (l_Res_def_Acc_Counter).last_updated_by := l_TerrRsc_Tbl(1).last_updated_by;
757 l_TerrRsc_def_Acc_Tbl (l_Res_def_Acc_Counter).creation_date := l_TerrRsc_Tbl(1).creation_date;
758 l_TerrRsc_def_Acc_Tbl (l_Res_def_Acc_Counter).created_by := l_TerrRsc_Tbl(1).created_by;
759 l_TerrRsc_def_Acc_Tbl (l_Res_def_Acc_Counter).last_update_login := l_TerrRsc_Tbl(1).last_update_login;
760 l_TerrRsc_def_Acc_Tbl (l_Res_def_Acc_Counter).terr_rsc_id := l_TerrRsc_Id;
761 l_TerrRsc_def_Acc_Tbl (l_Res_def_Acc_Counter).access_type := l_terr_res_access;
762 l_TerrRsc_def_Acc_Tbl (l_Res_def_Acc_Counter).org_id := l_TerrRsc_Tbl(1).org_id;
763 l_TerrRsc_def_Acc_Tbl (l_Res_def_Acc_Counter).TRANS_ACCESS_CODE := l_trans_access_code;
764 l_Res_def_Acc_Counter := l_Res_def_Acc_Counter + 1 ;
765 END LOOP;
766 CLOSE C_TERR_RES_ACCESS;
767
768 EXCEPTION
769 WHEN OTHERS THEN
770 CLOSE C_TERR_RES_ACCESS;
771 END;
772
773 --
774 -- Get into this loop only if there are access records found
775 If l_TerrRsc_def_Acc_Tbl.Count > 0 Then
776 --dbms_output.put_line('l_TerrRsc_Access_Tbl.Count > 0. Before calling Create_TerrResc_Access');
777 --
778 Create_Resource_Access(p_TerrRsc_Id => l_TerrRsc_Id,
779 p_TerrRsc_Access_Tbl => l_TerrRsc_def_Acc_Tbl,
780 p_api_version_number => p_api_version_number,
781 p_init_msg_list => p_init_msg_list,
782 p_commit => p_commit,
783 p_validation_level => p_validation_level,
784 x_return_status => l_return_status,
785 x_msg_count => x_msg_count,
786 x_msg_data => x_msg_data,
787 x_TerrRsc_Access_Out_Tbl => l_TerrRsc_Access_Out_Tbl);
788 --
789 -- Get the last index used
790 l_index := x_TerrRsc_Access_Out_Tbl.Count;
791 --
792 -- Save the OUT parameters to the original PAI out parametrs
793 For l_Counter IN l_TerrRsc_Access_Out_Tbl.first .. l_TerrRsc_Access_Out_Tbl.count LOOP
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;
803 -- Reset the table and records to G_MISS_RECORD and G_MISS_TABLE
804 l_TerrRsc_Tbl := G_MISS_TERRRESOURCE_TBL;
805 l_TerrRsc_Access_Tbl := G_MISS_TERRRSC_ACCESS_TBL;
806
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
816 -- Debug Message
817 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
818 THEN
819 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
820 fnd_message.set_name ('PROC_NAME', l_api_name);
821 FND_MSG_PUB.Add;
822 END IF;
823
824
825 FND_MSG_PUB.Count_And_Get
826 ( p_count => x_msg_count,
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
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,
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,
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,
865 'Error inside Create_TerrResource ' || sqlerrm);
866 END IF;
867 --
868 END Create_TerrResource;
869
870
871
872
873 -- ***************************************************
874 -- start of comments
875 -- ***************************************************
876 --
877 -- API name : Delete_Terr_Resource
878 -- Type : PUBLIC
879 -- Function : To delete resources associated with
880 -- Territories
881 --
882 -- Pre-reqs :
883 -- Parameters:
884 -- IN :
885 -- Required
886 -- Parameter Name Data Type Default
887 -- p_Api_Version_Number NUMBER
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 :
897 -- Parameter Name Data Type
898 -- X_Return_Status VARCHAR2(1)
899 -- X_Msg_Count NUMBER
900 -- X_Msg_Data VARCHAR2(2000)
901 --
902 --
903 -- Notes:
904 -- Rules for deletion have to be very strict
905 --
906 -- End of Comments
907 --
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,
917 X_Msg_Data OUT NOCOPY VARCHAR2,
918 p_TerrRsc_Id IN NUMBER
919 )
920 AS
921 l_Terr_rsc_access_id NUMBER;
922
923
924 --Declare cursor to get resource accesses
925 Cursor C_GetTerrRscAccess (v_TerrRsc_Id IN NUMBER) IS
926 Select JTRA.TERR_RSC_ACCESS_ID
927 From JTF_TERR_RSC_ACCESS_ALL JTRA
928 Where TERR_RSC_ID = v_TerrRsc_Id;
929
930 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Terr_Resource';
931 l_api_version_number CONSTANT NUMBER := 1.0;
932
933 l_return_status VARCHAR2(1);
934
935 BEGIN
936 --
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
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
955 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
956 THEN
957 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
958 fnd_message.set_name ('PROC_NAME', l_api_name);
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);
968 LOOP
969 FETCH C_GetTerrRscAccess INTO l_Terr_rsc_access_id;
970 EXIT WHEN C_GetTerrRscAccess%NOTFOUND ;
971
972 Delete_TerrRsc_Access(P_Api_Version_Number,
973 P_Init_Msg_List,
974 P_Commit,
975 l_Terr_rsc_access_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;
985
986 CLOSE C_GetTerrRscAccess;
987 --
988 --
989
990 Delete_TerrResource(P_Api_Version_Number,
991 P_Init_Msg_List,
992 P_Commit,
993 p_TerrRsc_Id,
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
1003 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1004 THEN
1005 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1006 fnd_message.set_name ('PROC_NAME', l_api_name);
1007 FND_MSG_PUB.Add;
1008 END IF;
1009
1010
1011 FND_MSG_PUB.Count_And_Get
1012 ( p_count => x_msg_count,
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
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 );
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 );
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;
1046 FND_MSG_PUB.Count_And_Get
1047 ( p_count => x_msg_count,
1048 p_data => x_msg_data
1049 );
1050 --
1051 END Delete_Terr_Resource;
1052
1053
1054
1055
1056 -- ***************************************************
1057 -- start of comments
1058 -- ***************************************************
1059 -- API name : Update_TerrResource
1060 -- Type : PUBLIC
1061 -- Function : To Update Territory Resources - which will update
1062 -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
1063 -- tables.
1064 --
1065 -- Pre-reqs :
1066 -- Parameters:
1067 -- IN :
1068 -- Required
1069 -- Parameter Name Data Type Default
1070 -- p_Api_Version_Number NUMBER
1071 -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
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
1081 -- x_Return_Status VARCHAR2(1)
1082 -- x_Msg_Count NUMBER
1083 -- x_Msg_Data VARCHAR2(2000)
1084 -- x_TerrRsc_Id NUMBER
1085 -- x_Terr_Usgs_Out_Tbl TerrResource_out_tbl_type
1086 -- x_Terr_QualTypeUsgs_Out_Tbl TerrRes_Access_out_tbl_type
1087 --
1088 -- Notes:
1089 --
1090 --
1091 -- End of Comments
1092 --
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,
1102 x_Msg_Data OUT NOCOPY VARCHAR2,
1103 p_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
1104 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
1105 x_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type,
1106 x_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_tbl_type
1107 )
1108 AS
1109 l_api_name CONSTANT VARCHAR2(30) := 'Update_TerrResource (Tbl)';
1110 l_api_version_number CONSTANT NUMBER := 1.0;
1111 l_return_status VARCHAR2(1);
1112
1113
1114 BEGIN
1115 -- Standard Start of API savepoint
1116 SAVEPOINT UPDATE_TERRRESOURCE_PVT;
1117
1118 --ARPATEL: bug#2849410
1119 /* Check_for_duplicate (p_TerrRsc_Tbl => p_TerrRsc_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.
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
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
1143 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1144 THEN
1145 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1146 fnd_message.set_name ('PROC_NAME', l_api_name);
1147 FND_MSG_PUB.Add;
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
1157 --
1158 Update_Terr_Resource(P_TerrRsc_Tbl => P_TerrRsc_Tbl,
1159 p_api_version_number => p_api_version_number,
1160 p_init_msg_list => p_init_msg_list,
1161 p_commit => p_commit,
1162 p_validation_level => p_validation_level,
1163 x_return_status => l_return_status,
1164 x_msg_count => x_msg_count,
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 --
1174 End If;
1175 --
1176 If p_TerrRsc_Access_Tbl.Count > 0 Then
1177 --
1178 Update_Resource_Access(p_TerrRsc_Access_Tbl => p_TerrRsc_Access_Tbl,
1179 p_api_version_number => p_api_version_number,
1180 p_init_msg_list => p_init_msg_list,
1181 p_commit => p_commit,
1182 p_validation_level => p_validation_level,
1183 x_return_status => l_return_status,
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 --
1193 End If;
1194
1195 -- Debug Message
1196 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1197 THEN
1198 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1199 fnd_message.set_name ('PROC_NAME', l_api_name);
1200 FND_MSG_PUB.Add;
1201 END IF;
1202
1203
1204 FND_MSG_PUB.Count_And_Get
1205 ( p_count => x_msg_count,
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 --
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 );
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 );
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 );
1240 --
1241 END Update_TerrResource;
1242
1243 ---------------------------------------------------------------------
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
1253 )
1254 AS
1255 l_temp VARCHAR2(3);
1256 l_rsc_lov_sql VARCHAR2(30000);
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,
1266 JTF_TERR_USGS_ALL JTU,
1267 JTF_SOURCES_ALL JSA
1268 WHERE JTA.TERR_ID = P_TerrRsc_Rec.Terr_Id
1269 AND JTA.TERR_ID = JTU.TERR_ID
1270 AND JTU.SOURCE_ID = JSA.SOURCE_ID;
1271
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;
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;
1287
1288 l_rsc_validate_sql := l_rsc_validate_sql ||'AND ROWNUM <= 1 ';
1289
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,
1317 'Others Exception in Validate_Resource ' || SQLERRM
1318 );
1319 END IF;
1320 --
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
1330 )
1331 AS
1332 l_temp VARCHAR2(3);
1333 l_rsc_lov_sql VARCHAR2(30000);
1334 l_rsc_validate_sql VARCHAR2(30000);
1335 l_resource_id NUMBER;
1336 l_group_id NUMBER;
1337 l_role VARCHAR2(300);
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
1347 FROM JTF_TERR_ALL JTA,
1348 JTF_TERR_RSC_ALL JTR,
1349 JTF_TERR_USGS_ALL JTU,
1350 JTF_SOURCES_ALL JSA
1351 WHERE JTR.terr_rsc_id = P_TerrRsc_Rec.Terr_Rsc_Id
1352 AND JTR.TERR_ID = JTA.Terr_Id
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;
1362
1363 --Get the missing values from the database to Valiadte the resource.
1364 BEGIN
1365 SELECT resource_id, group_id, role, resource_type
1366 INTO l_resource_id, l_group_id, l_role, l_resource_type
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;
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
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
1390 l_role := P_TerrRsc_Rec.ROLE;
1391 END IF;
1392
1393 l_rsc_validate_sql := 'SELECT ''X'' FROM ( ' || l_rsc_lov_sql || ' ) ' ;
1394 l_rsc_validate_sql := l_rsc_validate_sql || 'WHERE RESOURCE_ID = ' || l_resource_id ;
1395 l_rsc_validate_sql := l_rsc_validate_sql || ' AND DB_RSC_TYPE = ''' || l_resource_type || '''';
1396
1397 IF l_group_id IS NULL THEN
1398 l_rsc_validate_sql := l_rsc_validate_sql || ' AND GROUP_ID IS NULL ' ;
1399 ELSE
1400 l_rsc_validate_sql := l_rsc_validate_sql || ' AND GROUP_ID = ' || l_group_id ;
1401 END IF;
1402
1403 IF l_role IS NULL THEN
1404 l_rsc_validate_sql := l_rsc_validate_sql ||' AND ROLE_CODE IS NULL ' ;
1405 ELSE
1406 l_rsc_validate_sql := l_rsc_validate_sql ||' AND ROLE_CODE = ''' || l_role || '''';
1407 END IF;
1408
1409 l_rsc_validate_sql := l_rsc_validate_sql ||'AND ROWNUM <= 1 ';
1410
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,
1438 'Others Exception in Validate_Resource_update ' || SQLERRM
1439 );
1440 END IF;
1441 --
1442 END Validate_Resource_update;
1443
1444
1445 --
1446 -- ***************************************************
1447 -- start of comments
1448 -- ***************************************************
1449 -- API name : Create_Terr_Resource
1450 -- Type : PRIVATE
1451 -- Function : To create Territories resource
1452 --
1453 --
1454 -- Pre-reqs :
1455 -- Parameters:
1456 -- IN :
1457 -- Required
1458 -- Parameter Name Data Type Default
1459 -- P_TerrRsc_Rec TerrResource_tbl_type
1460 --
1461 -- OUT :
1462 -- Parameter Name Data Type
1463 -- X_Return_Status VARCHAR2(1)
1464 -- X_TerrRsc_Out_Rec TerrResource_out_tbl_type
1465 --
1466 -- Notes: This is a an overloaded procedure
1467 --
1468 --
1469 -- End of Comments
1470 --
1471
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,
1481 x_Msg_Data OUT NOCOPY VARCHAR2,
1482 X_TerrRsc_Out_Rec OUT NOCOPY TerrResource_out_Rec_type
1483 )
1484 AS
1485 l_rowid ROWID;
1486 l_api_name CONSTANT VARCHAR2(30) := 'Create_Terr_Resource';
1487 L_TerrRsc_Id NUMBER := P_TERRRSC_REC.TERR_RSC_ID;
1488 l_return_status VARCHAR2(1);
1489
1490 BEGIN
1491 --dbms_output.put_line('Create_Terr_Resource REC: Entering API');
1492
1493 -- Debug Message
1494 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1495 THEN
1496 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1497 fnd_message.set_name ('PROC_NAME', l_api_name);
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
1507 -- Debug message
1508 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1509 THEN
1510 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
1511 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Rsc');
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);
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
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);
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,
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
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');
1555 JTF_TERR_RSC_PKG.Insert_Row(x_Rowid => l_rowid,
1556 x_TERR_RSC_ID => l_TerrRsc_Id,
1557 x_LAST_UPDATE_DATE => P_TerrRsc_Rec.LAST_UPDATE_DATE,
1558 x_LAST_UPDATED_BY => P_TerrRsc_Rec.LAST_UPDATED_BY,
1559 x_CREATION_DATE => P_TerrRsc_Rec.CREATION_DATE,
1560 x_CREATED_BY => P_TerrRsc_Rec.CREATED_BY,
1561 x_LAST_UPDATE_LOGIN => P_TerrRsc_Rec.LAST_UPDATE_LOGIN,
1562 x_TERR_ID => P_TerrRsc_Rec.TERR_ID,
1563 x_RESOURCE_ID => P_TerrRsc_Rec.RESOURCE_ID,
1564 x_GROUP_ID => P_TerrRsc_Rec.GROUP_ID,
1565 x_RESOURCE_TYPE => P_TerrRsc_Rec.RESOURCE_TYPE,
1566 x_ROLE => P_TerrRsc_Rec.ROLE,
1567 x_PRIMARY_CONTACT_FLAG => P_TerrRsc_Rec.PRIMARY_CONTACT_FLAG,
1568 X_START_DATE_ACTIVE => P_TerrRsc_Rec.START_DATE_ACTIVE,
1569 X_END_DATE_ACTIVE => P_TerrRsc_Rec.END_DATE_ACTIVE,
1570 X_FULL_ACCESS_FLAG => P_TerrRsc_Rec.FULL_ACCESS_FLAG,
1571 X_ORG_ID => P_TerrRsc_Rec.ORG_ID,
1572 X_ATTRIBUTE_CATEGORY => P_TerrRsc_Rec.ATTRIBUTE_CATEGORY,
1573 X_ATTRIBUTE1 => P_TerrRsc_Rec.ATTRIBUTE1,
1574 X_ATTRIBUTE2 => P_TerrRsc_Rec.ATTRIBUTE2,
1575 X_ATTRIBUTE3 => P_TerrRsc_Rec.ATTRIBUTE3,
1576 X_ATTRIBUTE4 => P_TerrRsc_Rec.ATTRIBUTE4,
1577 X_ATTRIBUTE5 => P_TerrRsc_Rec.ATTRIBUTE5,
1578 X_ATTRIBUTE6 => P_TerrRsc_Rec.ATTRIBUTE6,
1579 X_ATTRIBUTE7 => P_TerrRsc_Rec.ATTRIBUTE7,
1580 X_ATTRIBUTE8 => P_TerrRsc_Rec.ATTRIBUTE8,
1581 X_ATTRIBUTE9 => P_TerrRsc_Rec.ATTRIBUTE9,
1582 X_ATTRIBUTE10 => P_TerrRsc_Rec.ATTRIBUTE10,
1583 X_ATTRIBUTE11 => P_TerrRsc_Rec.ATTRIBUTE11,
1584 X_ATTRIBUTE12 => P_TerrRsc_Rec.ATTRIBUTE12,
1585 X_ATTRIBUTE13 => P_TerrRsc_Rec.ATTRIBUTE13,
1586 X_ATTRIBUTE14 => P_TerrRsc_Rec.ATTRIBUTE14,
1587 X_ATTRIBUTE15 => P_TerrRsc_Rec.ATTRIBUTE15 );
1588
1589 --dbms_output.put_line('After 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
1599 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1600 fnd_message.set_name ('PROC_NAME', l_api_name);
1601 FND_MSG_PUB.Add;
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;
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 --
1625 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1626 THEN
1627 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others exception in Create_Terr_Resource ' || SQLERRM);
1628 END IF;
1629 --
1630 End Create_Terr_Resource;
1631 --
1632 --
1633 -- ***************************************************
1634 -- start of comments
1635 -- ***************************************************
1636 -- API name : Create_Terr_Resource
1637 -- Type : PRIVATE
1638 -- Function : To create Territories qualifier
1639 --
1640 -- Pre-reqs :
1641 -- Parameter Name Data Type Default
1642 -- P_TerrRsc_Tbl TerrResource_tbl_type
1643 --
1644 -- OUT :
1645 -- Parameter Name Data Type
1646 -- X_Return_Status VARCHAR2(1)
1647 -- X_TerrRsc_Out_Tbl TerrResource_out_tbl_type
1648 --
1649 -- Notes: This is a an overloaded procedure. This one
1650 -- will call the overloade procedure for records
1651 -- creation
1652 --
1653 --
1654 -- End of Comments
1655 --
1656
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,
1666 x_Msg_Data OUT NOCOPY VARCHAR2,
1667 X_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type
1668 )
1669 AS
1670 l_return_Status VARCHAR2(1);
1671
1672 l_api_name CONSTANT VARCHAR2(30) := 'Create_Resource_Access (Tbl)';
1673 l_TerrRsc_Tbl_Count NUMBER := P_TerrRsc_Tbl.Count;
1674 l_TerrRsc_out_Tbl_Count NUMBER;
1675 l_TerrRsc_Out_Tbl TerrResource_out_tbl_type;
1676 l_TerrRsc_Out_Rec TerrResource_out_Rec_type;
1677
1678 l_Counter NUMBER;
1679
1680 BEGIN
1681 --dbms_output.put_line('Create_Terr_Resource TBL: Entering API');
1682
1683 -- Debug Message
1684 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1685 THEN
1686 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1687 fnd_message.set_name ('PROC_NAME', l_api_name);
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
1697 --
1698 --dbms_output.put_line('Create_Terr_Resource TBL: Before Calling Create_Terr_Resource PVT');
1699 --
1700 Create_Terr_Resource(P_TerrRsc_Rec => P_TerrRsc_Tbl(l_counter),
1701 p_api_version_number => p_api_version_number,
1702 p_init_msg_list => p_init_msg_list,
1703 p_commit => p_commit,
1704 p_validation_level => p_validation_level,
1705 x_return_status => l_return_status,
1706 x_msg_count => x_msg_count,
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;
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
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
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
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
1747 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1748 THEN
1749 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1750 fnd_message.set_name ('PROC_NAME', l_api_name);
1751 FND_MSG_PUB.Add;
1752 END IF;
1753 --dbms_output.put_line('Create_Terr_Resource TBL: Exiting API');
1754 --
1755 End Create_Terr_Resource;
1756
1757 --
1758 -- ***************************************************
1759 -- start of comments
1760 -- ***************************************************
1761 -- API name : Create_Resource _Access
1762 -- Type : PUBLIC
1763 -- Function : To create Territories resource Access
1764 --
1765 --
1766 -- Pre-reqs :
1767 -- Parameters:
1768 -- IN :
1769 -- Required
1770 -- Parameter Name Data Type Default
1771 -- P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC
1772 --
1773 -- OUT :
1774 -- Parameter Name Data Type
1775 -- X_Return_Status VARCHAR2(1)
1776 -- X_TerrRsc_Access_Out_Rec TerrRsc_Access_out_rec_type
1777 --
1778 -- Notes: This is a an overloaded procedure
1779 --
1780 --
1781 -- End of Comments
1782 --
1783 PROCEDURE Create_Resource_Access
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,
1793 x_Msg_Data OUT NOCOPY VARCHAR2,
1794 X_TerrRsc_Access_Out_Rec OUT NOCOPY TerrRsc_Access_out_rec_type
1795 )
1796 AS
1797 l_rowid ROWID;
1798 l_api_name CONSTANT VARCHAR2(30) := 'Create_Resource_Access';
1799 l_terrRsc_Access_id NUMBER := P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID;
1800
1801 BEGIN
1802 --dbms_output.put_line('Create_Resource _Access REC: Entering API');
1803
1804 -- Debug Message
1805 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1806 THEN
1807 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1808 fnd_message.set_name ('PROC_NAME', l_api_name);
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
1818 -- Debug message
1819 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1820 THEN
1821 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
1822 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Rsc_Access');
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;
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
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;
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
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;
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
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;
1865 END IF;
1866 x_Return_Status := FND_API.G_RET_STS_ERROR ;
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,
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
1884 -- jdochert 09/09
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
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 --
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');
1908 JTF_TERR_RSC_ACCESS_PKG.Insert_Row(x_Rowid => l_rowid,
1909 x_TERR_RSC_ACCESS_ID => l_terrRsc_Access_id,
1910 x_LAST_UPDATE_DATE => P_TerrRsc_Access_Rec.LAST_UPDATE_DATE,
1911 x_LAST_UPDATED_BY => P_TerrRsc_Access_Rec.LAST_UPDATED_BY,
1912 x_CREATION_DATE => P_TerrRsc_Access_Rec.CREATION_DATE,
1913 x_CREATED_BY => P_TerrRsc_Access_Rec.CREATED_BY,
1914 x_LAST_UPDATE_LOGIN => P_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN,
1915 x_TERR_RSC_ID => p_TerrRsc_Id,
1916 x_ACCESS_TYPE => P_TerrRsc_Access_Rec.ACCESS_TYPE,
1917 x_TRANS_ACCESS_CODE => P_TerrRsc_Access_Rec.TRANS_ACCESS_CODE,
1918 X_ORG_ID => P_TerrRsc_Access_Rec.ORG_ID );
1919
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
1929 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1930 fnd_message.set_name ('PROC_NAME', l_api_name);
1931 FND_MSG_PUB.Add;
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;
1941 X_TerrRsc_Access_Out_Rec.return_status := x_return_status;
1942
1943 FND_MSG_PUB.Count_And_Get
1944 ( P_count => x_msg_count,
1945 P_data => x_msg_data
1946 );
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 --
1956 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1957 THEN
1958 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others exception in Create_Resource _Access');
1959 END IF;
1960 --
1961 End Create_Resource_Access;
1962
1963
1964
1965
1966 --
1967 --
1968 --
1969 -- ***************************************************
1970 -- start of comments
1971 -- ***************************************************
1972 -- API name : Create_Resource _Access
1973 -- Type : PUBLIC
1974 -- Function : To create Territories resource Access
1975 --
1976 --
1977 -- Pre-reqs :
1978 -- Parameters:
1979 -- IN :
1980 -- Required
1981 -- Parameter Name Data Type Default
1982 -- P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_TBL
1983 --
1984 -- OUT :
1985 -- Parameter Name Data Type
1986 -- X_Return_Status VARCHAR2(1)
1987 -- X_TerrRsc_Access_Out_Rec TerrRsc_Access_out_rec_type
1988 --
1989 -- Notes: This is a an overloaded procedure. This one
1990 -- will call the overloade procedure for records
1991 -- creation
1992 --
1993 -- End of Comments
1994 --
1995
1996 PROCEDURE Create_Resource_Access
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,
2006 x_Msg_Data OUT NOCOPY VARCHAR2,
2007 X_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_Tbl_type
2008 )
2009 AS
2010 l_terr_value_id NUMBER;
2011
2012 l_api_name CONSTANT VARCHAR2(30) := 'Create_Resource_Access (Tbl)';
2013 l_return_Status VARCHAR2(1);
2014 l_TerrRsc_Access_Tbl_Count NUMBER := P_TerrRsc_Access_Tbl.Count;
2015
2016 l_TerrRscAcc_Out_Tbl_Count NUMBER;
2017 l_TerrRsc_Access_Out_Tbl TerrRsc_Access_out_Tbl_type;
2018 l_TerrRsc_Access_Out_Rec TerrRsc_Access_out_Rec_type;
2019
2020 l_Counter NUMBER := 0;
2021
2022 BEGIN
2023 --dbms_output.put_line('Create_Resource_Access TBL: Entering API - ' || to_char(l_TerrRsc_Access_Tbl_Count));
2024
2025 -- Debug Message
2026 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2027 THEN
2028 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2029 fnd_message.set_name ('PROC_NAME', l_api_name);
2030 FND_MSG_PUB.Add;
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
2040 --
2041 --dbms_output.put_line('Inside Create_Resource_Access - ' || to_char(P_TerrRsc_Access_Tbl(l_counter).QUALIFIER_TBL_INDEX) );
2042 Create_Resource_Access(P_TerrRsc_Id => p_TerrRsc_Id,
2043 P_TerrRsc_Access_Rec => P_TerrRsc_Access_Tbl(l_counter),
2044 p_api_version_number => p_api_version_number,
2045 p_init_msg_list => p_init_msg_list,
2046 p_commit => p_commit,
2047 p_validation_level => p_validation_level,
2048 x_return_status => l_return_status,
2049 x_msg_count => x_msg_count,
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
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
2066 X_TerrRsc_Access_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_SUCCESS;
2067 END IF;
2068 --
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
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
2087 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2088 THEN
2089 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2090 fnd_message.set_name ('PROC_NAME', l_api_name);
2091 FND_MSG_PUB.Add;
2092 END IF;
2093
2094 --
2095 End Create_Resource_Access;
2096
2097
2098
2099
2100 --
2101 -- *******************************************************
2102 -- Start of Comments
2103 -- *******************************************************
2104 -- API Name: Delete_TerrRsc_Access
2105 -- Type : PRIVATE
2106 -- Pre-Req :
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:
2116 --
2117 -- OUT:
2118 -- Parameter Name Data Type Default
2119 -- X_Return_Status VARCHAR2
2120 --
2121 -- Note:
2122 --
2123 -- End of Comments
2124 --
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,
2134 X_Msg_Data OUT NOCOPY VARCHAR2
2135 )
2136 AS
2137 l_row_count NUMBER;
2138 l_api_name CONSTANT VARCHAR2(30) := 'Delete_TerrRsc_Access';
2139 l_api_version_number CONSTANT NUMBER := 1.0;
2140 l_return_status VARCHAR2(1);
2141
2142 BEGIN
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
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
2161 -- Debug Message
2162 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2163 THEN
2164 FND_MESSAGE.Set_Name('JTF', 'Delete TerrRscAccess : Start');
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
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
2182 FND_MESSAGE.SET_NAME('ITEMS_DELETED', l_row_count);
2183 --Add message to API message list
2184 FND_MSG_PUB.ADD();
2185
2186 -- Debug Message
2187 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2188 THEN
2189 FND_MESSAGE.Set_Name('JTF', 'Delete TerrRscAccess: End');
2190 FND_MSG_PUB.Add;
2191 END IF;
2192
2193
2194 FND_MSG_PUB.Count_And_Get
2195 ( p_count => x_msg_count,
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 --
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
2212 FND_MSG_PUB.ADD();
2213 --
2214 FND_MSG_PUB.Count_And_Get
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,
2224 p_data => x_msg_data
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;
2234 END Delete_TerrRsc_Access;
2235
2236
2237
2238
2239 --
2240 -- *******************************************************
2241 -- Start of Comments
2242 -- *******************************************************
2243 -- API Name: Delete_TerrResource
2244 -- Type : PRIVATE
2245 -- Pre-Req :
2246 -- Parameters:
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:
2256 -- OUT:
2257 -- Parameter Name Data Type Default
2258 -- X_Return_Status VARCHAR2
2259 --
2260 -- Note:
2261 --
2262 -- End of Comments
2263 --
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,
2273 X_Msg_Data OUT NOCOPY VARCHAR2
2274 )
2275 AS
2276 l_row_count NUMBER;
2277 l_api_name CONSTANT VARCHAR2(30) := 'Delete_TerrResource';
2278 l_api_version_number CONSTANT NUMBER := 1.0;
2279 l_return_status VARCHAR2(1);
2280
2281 BEGIN
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
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
2299 -- Debug Message
2300 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2301 THEN
2302 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2303 fnd_message.set_name ('PROC_NAME', l_api_name);
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
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
2321 FND_MESSAGE.SET_TOKEN('ITEMS_DELETED', l_row_count);
2322
2323 --Add message to API message list
2324 FND_MSG_PUB.ADD();
2325
2326 -- Debug Message
2327 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2328 THEN
2329 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2330 fnd_message.set_name ('PROC_NAME', l_api_name);
2331 FND_MSG_PUB.Add;
2332 END IF;
2333
2334
2335 FND_MSG_PUB.Count_And_Get
2336 ( p_count => x_msg_count,
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 --
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);
2354
2355 --Add message to API message list
2356 FND_MSG_PUB.ADD();
2357 --
2358 FND_MSG_PUB.Count_And_Get
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,
2368 p_data => x_msg_data
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;
2378 --
2379 End Delete_TerrResource;
2380 --
2381
2382
2383 ---------------------------------------------------------------------
2384 -- Validate Territory Resource
2385 ---------------------------------------------------------------------
2386 -- Columns Validated
2387 -- Make sure the Territory Id is valid
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
2397 )
2398 AS
2399 l_Validate_id NUMBER;
2400 l_dummy NUMBER;
2401 l_res_start_date_active DATE;
2402 l_res_end_date_active DATE;
2403 l_terr_start_date DATE;
2404 l_terr_end_date DATE;
2405 l_terr_id NUMBER;
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
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');
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.
2429 BEGIN
2430 SELECT terr_id, start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
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
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
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
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
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;
2462
2463 IF (l_res_start_date_active IS NOT NULL ) AND (l_res_end_date_active IS NOT NULL ) THEN
2464 IF ( l_res_start_date_active > l_res_end_date_active ) THEN
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
2474
2475 SELECT jta.start_date_active,jta.end_date_active
2476 INTO l_terr_start_date,l_terr_end_date
2477 FROM jtf_terr_all jta
2478 WHERE jta.terr_id = l_terr_id ;
2479
2480 -- Validate start date .
2481 IF ( l_res_start_date_active < l_terr_start_date ) OR ( l_res_start_date_active > l_terr_end_date ) THEN
2482 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
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
2492 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) 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
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;
2507
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;
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
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;
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
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;
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,
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
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 );
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,
2571 'Others Exception in Validate_Terr_Rsc_update ' || SQLERRM
2572 );
2573 END IF;
2574 --
2575 END Validate_Terr_Rsc_update;
2576
2577 --
2578 -- ***************************************************
2579 -- start of comments
2580 -- ***************************************************
2581 -- API name : Update_Terr_Resource
2582 -- Type : PRIVATE
2583 -- Function : To update Territories resource
2584 --
2585 --
2586 -- Pre-reqs :
2587 -- Parameters:
2588 -- IN :
2589 -- Required
2590 -- Parameter Name Data Type Default
2591 -- P_TerrRsc_Rec TerrResource_tbl_type
2592 --
2593 -- OUT NOCOPY :
2594 -- Parameter Name Data Type
2595 -- X_Return_Status VARCHAR2(1)
2596 -- X_TerrRsc_Out_Rec TerrResource_out_tbl_type
2597 --
2598 -- Notes: This is a an overloaded procedure
2599 --
2600 --
2601 -- End of Comments
2602 --
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,
2612 x_Msg_Data OUT NOCOPY VARCHAR2,
2613 x_TerrRsc_Out_Rec OUT NOCOPY TerrResource_out_Rec_type) AS
2614
2615 Cursor C_GetTerrResource(l_TerrRsc_id Number) IS
2616 Select Rowid, TERR_RSC_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2617 LAST_UPDATE_LOGIN, TERR_ID, RESOURCE_ID, RESOURCE_TYPE, ROLE, PRIMARY_CONTACT_FLAG, ORG_ID
2618 From jtf_terr_rsc_ALL
2619 Where TERR_RSC_ID = l_TerrRsc_id
2620 FOR Update NOWAIT;
2621
2622 --Local variable declaration
2623 l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Resource';
2624 l_rowid VARCHAR2(50);
2625 l_api_version_number CONSTANT NUMBER := 1.0;
2626 l_return_status VARCHAR2(1);
2627 l_ref_TerrRsc_Rec TerrResource_Rec_type;
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,
2637 l_ref_TerrRsc_Rec.LAST_UPDATED_BY, l_ref_TerrRsc_Rec.CREATION_DATE, l_ref_TerrRsc_Rec.CREATED_BY,
2638 l_ref_TerrRsc_Rec.LAST_UPDATE_LOGIN, l_ref_TerrRsc_Rec.TERR_ID, l_ref_TerrRsc_Rec.RESOURCE_ID,
2639 l_ref_TerrRsc_Rec.RESOURCE_TYPE, l_ref_TerrRsc_Rec.ROLE, l_ref_TerrRsc_Rec.PRIMARY_CONTACT_FLAG,
2640 l_ref_TerrRsc_Rec.ORG_ID;
2641
2642 If (C_GetTerrResource%NOTFOUND) Then
2643 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2644 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
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
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;
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);
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.
2674 Validate_Resource_update(P_TerrRsc_Rec => P_TerrRsc_Rec,
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.
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
2694 JTF_TERR_RSC_PKG.Update_Row(x_Rowid => l_rowid,
2695 x_TERR_RSC_ID => P_TerrRsc_Rec.Terr_Rsc_Id,
2696 x_LAST_UPDATE_DATE => P_TerrRsc_Rec.LAST_UPDATE_DATE,
2697 x_LAST_UPDATED_BY => P_TerrRsc_Rec.LAST_UPDATED_BY ,
2698 x_CREATION_DATE => P_TerrRsc_Rec.CREATION_DATE,
2699 x_CREATED_BY => P_TerrRsc_Rec.CREATED_BY,
2700 x_LAST_UPDATE_LOGIN => P_TerrRsc_Rec.LAST_UPDATE_LOGIN ,
2701 x_TERR_ID => P_TerrRsc_Rec.TERR_ID,
2702 x_RESOURCE_ID => P_TerrRsc_Rec.RESOURCE_ID,
2703 x_GROUP_ID => P_TerrRsc_Rec.GROUP_ID,
2704 x_RESOURCE_TYPE => P_TerrRsc_Rec.RESOURCE_TYPE,
2705 x_ROLE => P_TerrRsc_Rec.ROLE,
2706 x_PRIMARY_CONTACT_FLAG => P_TerrRsc_Rec.PRIMARY_CONTACT_FLAG,
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,
2716 x_attribute4 => P_TerrRsc_Rec.attribute4,
2717 x_attribute5 => P_TerrRsc_Rec.attribute5,
2718 x_attribute6 => P_TerrRsc_Rec.attribute6,
2719 x_attribute7 => P_TerrRsc_Rec.attribute7,
2720 x_attribute8 => P_TerrRsc_Rec.attribute8,
2721 x_attribute9 => P_TerrRsc_Rec.attribute9,
2722 x_attribute10 => P_TerrRsc_Rec.attribute10,
2723 x_attribute11 => P_TerrRsc_Rec.attribute11,
2724 x_attribute12 => P_TerrRsc_Rec.attribute12,
2725 x_attribute13 => P_TerrRsc_Rec.attribute13,
2726 x_attribute14 => P_TerrRsc_Rec.attribute14,
2727 x_attribute15 => P_TerrRsc_Rec.attribute15
2728 );
2729
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 ;
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');
2748 END IF;
2749
2750 End Update_Terr_Resource;
2751
2752
2753
2754
2755 --
2756 --
2757 -- ***************************************************
2758 -- start of comments
2759 -- ***************************************************
2760 -- API name : Update_Terr_Resource
2761 -- Type : PRIVATE
2762 -- Function : To create Territories qualifier
2763 --
2764 -- Pre-reqs :
2765 -- Parameter Name Data Type Default
2766 -- P_TerrRsc_Tbl TerrResource_tbl_type
2767 --
2768 -- OUT :
2769 -- Parameter Name Data Type
2770 -- X_Return_Status VARCHAR2(1)
2771 -- X_TerrRsc_Out_Tbl TerrResource_out_tbl_type
2772 --
2773 -- Notes: This is a an overloaded procedure. This one
2774 -- will call the overloade procedure for records
2775 -- creation
2776 --
2777 --
2778 -- End of Comments
2779 --
2780
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,
2790 x_Msg_Data OUT NOCOPY VARCHAR2,
2791 X_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type
2792 )
2793 AS
2794 l_return_Status VARCHAR2(1);
2795 l_TerrRsc_Tbl_Count NUMBER := P_TerrRsc_Tbl.Count;
2796 l_TerrRsc_out_Tbl_Count NUMBER;
2797 l_TerrRsc_Out_Tbl TerrResource_out_tbl_type;
2798 l_TerrRsc_Out_Rec TerrResource_out_Rec_type;
2799
2800 l_Counter NUMBER;
2801
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
2811 --
2812 --dbms_output.put_line('Update_Terr_Resource TBL: Before Calling Create_Terr_Resource PVT');
2813
2814 Update_Terr_Resource(P_TerrRsc_Rec => P_TerrRsc_Tbl(l_counter),
2815 p_api_version_number => p_api_version_number,
2816 p_init_msg_list => p_init_msg_list,
2817 p_commit => p_commit,
2818 p_validation_level => p_validation_level,
2819 x_return_status => l_return_status,
2820 x_msg_count => x_msg_count,
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
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;
2838 END IF;
2839 --
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
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 --
2858 End Update_Terr_Resource;
2859
2860
2861
2862
2863 --
2864 -- ***************************************************
2865 -- start of comments
2866 -- ***************************************************
2867 -- API name : Update_Resource_Access
2868 -- Type : PUBLIC
2869 -- Function : To Update Territories resource Access
2870 --
2871 --
2872 -- Pre-reqs :
2873 -- Parameters:
2874 -- IN :
2875 -- Required
2876 -- Parameter Name Data Type Default
2877 -- P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC
2878 --
2879 -- OUT :
2880 -- Parameter Name Data Type
2881 -- X_Return_Status VARCHAR2(1)
2882 -- X_TerrRsc_Access_Out_Rec TerrRsc_Access_out_rec_type
2883 --
2884 -- Notes: This is a an overloaded procedure
2885 --
2886 --
2887 -- End of Comments
2888 --
2889
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,
2899 x_Msg_Data OUT NOCOPY VARCHAR2,
2900 X_TerrRsc_Access_Out_Rec OUT NOCOPY TerrRsc_Access_out_rec_type
2901 )
2902 AS
2903 Cursor C_GetTerrResAccess(l_TerrRsc_Access_id Number) IS
2904 Select Rowid,
2905 TERR_RSC_ACCESS_ID,
2906 LAST_UPDATE_DATE,
2907 LAST_UPDATED_BY,
2908 CREATION_DATE,
2909 CREATED_BY,
2910 LAST_UPDATE_LOGIN,
2911 TERR_RSC_ID,
2912 ACCESS_TYPE,
2913 TRANS_ACCESS_CODE,
2914 ORG_ID
2915 From jtf_terr_rsc_access_ALL
2916 Where TERR_RSC_ACCESS_ID = l_TerrRsc_Access_id
2917 FOR Update NOWAIT;
2918
2919 --Local variable declaration
2920 l_api_name CONSTANT VARCHAR2(30) := 'Update_Resource_Access';
2921 l_rowid VARCHAR2(50);
2922 l_api_version_number CONSTANT NUMBER := 1.0;
2923 l_return_status VARCHAR2(1);
2924 l_ref_TerrRsc_Access_Rec TerrRsc_Access_rec_type;
2925
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
2935 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2936 THEN
2937 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
2938 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Rsc_Access');
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,
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
2957 OPEN C_GetTerrResAccess( P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID);
2958 Fetch C_GetTerrResAccess into
2959 l_Rowid,
2960 l_ref_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID,
2961 l_ref_TerrRsc_Access_Rec.LAST_UPDATE_DATE,
2962 l_ref_TerrRsc_Access_Rec. LAST_UPDATED_BY,
2963 l_ref_TerrRsc_Access_Rec.CREATION_DATE,
2964 l_ref_TerrRsc_Access_Rec.CREATED_BY,
2965 l_ref_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN,
2966 l_ref_TerrRsc_Access_Rec.TERR_RSC_ID,
2967 l_ref_TerrRsc_Access_Rec.ACCESS_TYPE,
2968 l_ref_TerrRsc_Access_Rec.TRANS_ACCESS_CODE,
2969 l_ref_TerrRsc_Access_Rec.ORG_ID;
2970
2971 If ( C_GetTerrResAccess%NOTFOUND) Then
2972 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2973 THEN
2974 --dbms_output.put_line('Update_Resource_Access REC: DATA-NOT-FOUND');
2975 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
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
2985 -- check for Unique Key constraint violation
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
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');
3001 JTF_TERR_RSC_ACCESS_PKG.Update_Row(x_Rowid => l_rowid,
3002 x_TERR_RSC_ACCESS_ID => P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID,
3003 x_LAST_UPDATE_DATE => P_TerrRsc_Access_Rec.LAST_UPDATE_DATE,
3004 x_LAST_UPDATED_BY => P_TerrRsc_Access_Rec.LAST_UPDATED_BY,
3005 x_CREATION_DATE => P_TerrRsc_Access_Rec.CREATION_DATE,
3006 x_CREATED_BY => P_TerrRsc_Access_Rec.CREATED_BY,
3007 x_LAST_UPDATE_LOGIN => P_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN,
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
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,
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 --
3037 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3038 THEN
3039 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others exception in Update_Resource _Access');
3040 END IF;
3041 --
3042 End Update_Resource_Access;
3043
3044
3045
3046
3047 --
3048 --
3049 -- ***************************************************
3050 -- start of comments
3051 -- ***************************************************
3052 -- API name : Update_Resource _Access
3053 -- Type : PUBLIC
3054 -- Function : To create Territories resource Access
3055 --
3056 --
3057 -- Pre-reqs :
3058 -- Parameters:
3059 -- IN :
3060 -- Required
3061 -- Parameter Name Data Type Default
3062 -- P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_TBL
3063 --
3064 -- OUT :
3065 -- Parameter Name Data Type
3066 -- X_Return_Status VARCHAR2(1)
3067 -- X_TerrRsc_Access_Out_Rec TerrRsc_Access_out_rec_type
3068 --
3069 -- Notes: This is a an overloaded procedure. This one
3070 -- will call the overloade procedure for records
3071 -- creation
3072 --
3073 -- End of Comments
3074 --
3075
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,
3085 x_Msg_Data OUT NOCOPY VARCHAR2,
3086 X_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_Tbl_type
3087 )
3088 AS
3089 l_terr_value_id NUMBER;
3090
3091
3092 l_return_Status VARCHAR2(1);
3093 l_TerrRsc_Access_Tbl_Count NUMBER := P_TerrRsc_Access_Tbl.Count;
3094
3095 l_TerrRscAcc_Out_Tbl_Count NUMBER;
3096 l_TerrRsc_Access_Out_Tbl TerrRsc_Access_out_Tbl_type;
3097 l_TerrRsc_Access_Out_Rec TerrRsc_Access_out_Rec_type;
3098
3099 l_Counter NUMBER;
3100
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
3110 --
3111 --dbms_output.put_line('Inside Update_Resource_Access - ' || to_char(P_TerrRsc_Access_Tbl(l_counter).QUALIFIER_TBL_INDEX) );
3112 Update_Resource_Access(P_TerrRsc_Access_Rec => P_TerrRsc_Access_Tbl(l_counter),
3113 p_api_version_number => p_api_version_number,
3114 p_init_msg_list => p_init_msg_list,
3115 p_commit => p_commit,
3116 p_validation_level => p_validation_level,
3117 x_return_status => l_return_status,
3118 x_msg_count => x_msg_count,
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
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;
3135 END IF;
3136 --
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
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;
3155 --
3156
3157
3158 -- ***************************************************
3159 -- start of comments
3160 -- ***************************************************
3161 -- API name : Validate_Foreign_Keys
3162 -- Type : PUBLIC
3163 -- Function : Validate Territory Resources
3164 --
3165 -- Pre-reqs :
3166 -- Parameters:
3167 -- IN :
3168 -- Required
3169 -- Parameter Name Data Type Default
3170 -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
3171 --
3172 -- Optional
3173 -- Parameter Name Data Type Default
3174 --
3175 -- OUT :
3176 -- Parameter Name Data Type
3177 -- x_Return_Status VARCHAR2(1)
3178 -- Notes:
3179 --
3180 --
3181 -- End of Comments
3182 --
3183
3184 PROCEDURE Validate_Foreign_Keys
3185 (
3186 p_TerrRsc_Tbl IN TerrResource_tbl_type,
3187 x_Return_Status OUT NOCOPY VARCHAR2,
3188 x_msg_count OUT NOCOPY NUMBER,
3189 x_msg_data OUT NOCOPY VARCHAR2
3190 )
3191 AS
3192 l_index NUMBER := 0;
3193 l_Res_Counter NUMBER := 0;
3194 l_Temp VARCHAR2(1);
3195 l_Terr_Id NUMBER;
3196
3197 BEGIN
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;
3207 --
3208 --dbms_output.put_line('Inside the for loop. Before validating Terr_Id');
3209 Select 'X' into l_temp
3210 from JTF_TERR_ALL
3211 where TERR_ID = p_TerrRsc_Tbl(l_res_counter).Terr_Id;
3212 --
3213 --
3214 End LOOP;
3215 --
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
3225 ( P_count => x_msg_count,
3226 P_data => x_msg_data
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,
3236 'Others Exception in Validate_Foreign_Keys ' || SQLERRM
3237 );
3238 END IF;
3239 --
3240 END Validate_Foreign_Keys;
3241
3242
3243 -- ***************************************************
3244 -- start of comments
3245 -- ***************************************************
3246 -- API name : Validate_Resorce_Object
3247 -- Type : PUBLIC
3248 -- Function : Validate Territory Resources
3249 --
3250 -- Pre-reqs :
3251 -- Parameters:
3252 -- IN :
3253 -- Required
3254 -- Parameter Name Data Type Default
3255 -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
3256 -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
3257 --
3258 -- Optional
3259 -- Parameter Name Data Type Default
3260 --
3261 -- OUT :
3262 -- Parameter Name Data Type
3263 -- x_Return_Status VARCHAR2(1)
3264 -- Notes:
3265 --
3266 --
3267 -- End of Comments
3268 --
3269
3270 PROCEDURE Validate_Primary_Flag
3271 (
3272 p_TerrRsc_Tbl IN TerrResource_tbl_type,
3273 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type,
3274 x_msg_count OUT NOCOPY NUMBER,
3275 x_msg_data OUT NOCOPY VARCHAR2,
3276 x_Return_Status OUT NOCOPY VARCHAR2
3277 )
3278 AS
3279 l_Primary_Count NUMBER := 0;
3280 l_Res_Counter NUMBER := 0;
3281 l_Res_Access_Counter NUMBER := 0;
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
3291 l_Primary_Count := l_Primary_Count + 1;
3292 End If;
3293 --
3294 End LOOP;
3295
3296 -- Cannot have more than one Primary flag
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
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 );
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 );
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,
3330 'Others Exception in Validate_Primary_Flag ' || SQLERRM
3331 );
3332 END IF;
3333 --
3334 END Validate_Primary_Flag;
3335
3336 -- ***************************************************
3337 -- start of comments
3338 -- ***************************************************
3339 -- API name : Validate_TerrResource_Data
3340 -- Type : PUBLIC
3341 -- Function : Validate Territory Resources
3342 --
3343 -- Pre-reqs :
3344 -- Parameters:
3345 -- IN :
3346 -- Required
3347 -- Parameter Name Data Type Default
3348 -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
3349 -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
3350 --
3351 -- Optional
3352 -- Parameter Name Data Type Default
3353 --
3354 -- OUT :
3355 -- Parameter Name Data Type
3356 -- x_Return_Status VARCHAR2(1)
3357 -- Notes:
3358 --
3359 --
3360 -- End of Comments
3361 --
3362
3363 PROCEDURE Validate_TerrResource_Data
3364 (
3365 p_TerrRsc_Tbl IN TerrResource_tbl_type,
3366 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type,
3367 x_msg_count OUT NOCOPY NUMBER,
3368 x_msg_data OUT NOCOPY VARCHAR2,
3369 x_Return_Status OUT NOCOPY VARCHAR2
3370 )
3371 AS
3372 l_Return_Status VARCHAR2(01);
3373
3374 BEGIN
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
3393 If (p_TerrRsc_Tbl.count = 0 ) Then
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
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 ,
3408 p_TerrRsc_Access_Tbl => p_TerrRsc_Access_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');
3418 Validate_Foreign_Keys(p_TerrRsc_Tbl => p_TerrRsc_Tbl,
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
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 );
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 );
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,
3452 'Others Exception in Validate_TerrResource_Data ' || SQLERRM
3453 );
3454 END IF;
3455
3456 --
3457 END Validate_TerrResource_Data;
3458
3459 ---------------------------------------------------------------------
3460 -- Validate Territory Resource
3461 ---------------------------------------------------------------------
3462 -- Columns Validated
3463 -- Make sure a Resource and Resource_Type is specified
3464 -- Make sure the Territory Id is valid
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
3474 )
3475 AS
3476 l_Validate_id NUMBER;
3477 l_dummy NUMBER;
3478 l_terr_start_date DATE;
3479 l_terr_end_date DATE;
3480
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
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');
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
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;
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
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;
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
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;
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
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;
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
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;
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
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;
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
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;
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
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
3595
3596 SELECT jta.start_date_active,jta.end_date_active
3597 INTO l_terr_start_date,l_terr_end_date
3598 FROM jtf_terr_all jta
3599 WHERE jta.terr_id = p_TerrRsc_Rec.Terr_Id ;
3600
3601 -- Validate start date .
3602 IF ( p_TerrRsc_Rec.START_DATE_ACTIVE < l_terr_start_date ) OR ( p_TerrRsc_Rec.START_DATE_ACTIVE > l_terr_end_date ) THEN
3603 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
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
3613 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) 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
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;
3628
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;
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
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;
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
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;
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,
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
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 );
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,
3692 'Others Exception in Validate_Terr_Rsc ' || SQLERRM
3693 );
3694 END IF;
3695 --
3696 END Validate_Terr_Rsc;
3697
3698 ---------------------------------------------------------------------
3699 -- Validate Territory Resource Access record
3700 ---------------------------------------------------------------------
3701 -- Columns Validated
3702 -- Make sure a TERR_RSC_ID is valid
3703 -- Make sure the ACCESS_TYPE is valid
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,
3713 p_TerrRsc_Access_Rec IN TerrRsc_Access_Rec_type
3714 )
3715 AS
3716 l_Temp VARCHAR2(01);
3717 l_Validate_id NUMBER;
3718 l_dummy NUMBER;
3719
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
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');
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');
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
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;
3756
3757 EXCEPTION
3758 WHEN NO_DATA_FOUND THEN
3759 -- Invalid Territory Id specified
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
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
3774 where lookup_type IN ( select rsc_access_lkup
3775 from jtf_sources_all)
3776 )
3777 WHERE LOOKUP_CODE = p_TerrRsc_Access_Rec.TRANS_ACCESS_CODE;
3778 EXCEPTION
3779 WHEN NO_DATA_FOUND THEN
3780 -- Invalid TRANS_ACCESS_CODE specified
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
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;
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
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;
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
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;
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,
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
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 );
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,
3850 'Others Exception in Validate_Terr_Rsc ' || SQLERRM
3851 );
3852 END IF;
3853 --
3854 END Validate_Terr_Rsc_Access;
3855
3856
3857
3858 -- This function will build the rule expression
3859
3860 FUNCTION BuildRuleExpression
3861 (
3862 p_Terr_Id NUMBER,
3863 p_qual_type_id NUMBER
3864 ) return VARCHAR2
3865 AS
3866 v_Terr_Qual_Id number;
3867
3868 CURSOR c_Terr_ResQual IS
3869 SELECT JTQ.TERR_QUAL_ID
3870 FROM jtf_seeded_qual_usgs_v jsquv, JTF_TERR_QUAL JTQ
3871 WHERE JTQ.terr_id = p_Terr_id and
3872 JTQ.qual_usg_id = jsquv.qual_usg_id and
3873 jsquv.qual_type_id = -1001 and
3874 jsquv.qual_type_id in ( select related_id
3875 from JTF_QUAL_TYPE_DENORM_V
3876 where qual_type_id = p_qual_type_id);
3877
3878 CURSOR c_Values IS
3879 Select JTV.COMPARISON_OPERATOR, JTV.INCLUDE_FLAG, jsquv.QUAL_COL1,
3880 jsquv.QUAL_COL1_TABLE, jsquv.QUAL_COL1_ALIAS, jsquv.PRIM_INT_CDE_COL_ALIAS,
3881 jsquv.SEC_INT_CDE_COL_ALIAS, jtv.low_value_char,jtv.high_value_char,
3882 jtv.low_value_number, jtv.high_value_number,
3883 jtv.INTEREST_TYPE_ID, jtv.PRIMARY_INTEREST_CODE_ID,
3884 jtv.SECONDARY_INTEREST_CODE_ID, jsquv.DISPLAY_TYPE, jsquv.CONVERT_TO_ID_FLAG,
3885 jtv.ID_USED_FLAG, jtv.CURRENCY_CODE, jtv.LOW_VALUE_CHAR_ID
3886 from jtf_seeded_qual_usgs_v jsquv, jtf_terr_values jtv, jtf_terr_qual jtq
3887 where jtv.terr_qual_id = v_Terr_Qual_Id and
3888 jtv.terr_qual_id = jtq.terr_qual_id and
3889 jtq.qual_usg_id = jsquv.qual_usg_id;
3890
3891 Type t_Pkgname IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
3892 l_Expr_Tbl t_Pkgname;
3893 l_Expr_Pointer NUMBER := 0;
3894 l_Record JTF_TERR_ENGINE_GEN_PVT.Terr_Value_Rec_Type;
3895
3896 v_Expression VARCHAR2(5000);
3897 v_Temp VARCHAR2(256);
3898 v_State BOOLEAN;
3899 v_Counter NUMBER := 0;
3900 l_Row NUMBER := 0;
3901
3902 BEGIN
3903 -- Reinitialize the v_Temp to NULL
3904 v_Temp := NULL;
3905
3906 FOR C IN c_Terr_ResQual LOOP
3907 l_Expr_Pointer := l_Expr_Pointer + 1;
3908 v_Terr_Qual_Id := C.terr_qual_Id;
3909
3910 --dbms_output.put_line( '[1] Inside BuildRuleExpression -> Terr_Qual_Id - ' || to_char(v_Terr_Qual_Id) );
3911 v_Counter := 1;
3912
3913 If v_Counter = 1 and l_Expr_Pointer = 1 Then
3914 l_Expr_Tbl(l_Expr_Pointer) := ' (( ';
3915 -- Inside the loop for the first qualifer
3916 ElsIf v_Counter = 1 Then
3917 l_Expr_Tbl(l_Expr_Pointer) := ' AND (( ';
3918 End If;
3919
3920 Open c_Values;
3921
3922 LOOP
3923 Fetch c_Values INTO l_Record;
3924
3925 Exit WHEN c_Values%NOTFOUND;
3926
3927 --for second set of qualifer value
3928 If l_Expr_Pointer > 1 and v_counter <> 1 Then
3929 l_Expr_Pointer := l_Expr_Pointer + 1;
3930 l_Expr_Tbl(l_Expr_Pointer) := ' OR ( ';
3931 End If;
3932
3933 v_Counter := v_Counter + 1;
3934 l_Expr_Pointer := l_Expr_Pointer + 1;
3935
3936 --dbms_output.put_line('Values -> l_Expr_Pointer ' || to_char( l_Expr_Pointer) );
3937
3938 -- --dbms_output.put_line( '[1] Inside the VALUES loop - '|| l_Record.DISPLAY_TYPE);
3939 -- Do all the special processing for for interest Category/Primary Intererst/ Secondary types
3940 IF l_Record.DISPLAY_TYPE = 'INTEREST_TYPE' Then
3941 --dbms_output.put_line( 'Inside first if interest_type');
3942 v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_Interest_Type(l_Record => l_record);
3943 -- This display type is only for resource qualifer (competence/competence level)
3944 ELSIf l_Record.DISPLAY_TYPE = 'COMPETENCE' Then
3945 --dbms_output.put_line( 'Inside first if competence');
3946 v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_COMPETENCE(l_Record => l_record);
3947 ELSIf l_Record.DISPLAY_TYPE = 'NUMERIC' Then
3948 --dbms_output.put_line( 'Inside char number if');
3949 v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_NUMERIC(l_Record => l_record);
3950 ELSIf l_Record.DISPLAY_TYPE like 'CHAR' Then
3951 --dbms_output.put_line( 'Inside char number if');
3952 v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_CHAR(l_Record => l_record);
3953 ELSIf l_Record.DISPLAY_TYPE = 'SPECIAL_FUNCTION' Then
3954 -- Need to add this
3955 null;
3956 ELSIF l_Record.DISPLAY_TYPE = 'CURRENCY' Then
3957 v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_CURRENCY(l_Record => l_record);
3958 End If;
3959 --
3960 l_Expr_Tbl(l_Expr_Pointer) := v_Temp;
3961
3962 l_Expr_Pointer := l_Expr_Pointer + 1;
3963 l_Expr_Tbl(l_Expr_Pointer) := ' ) ';
3964 --
3965 END LOOP;
3966
3967 Close c_Values;
3968 --
3969 l_Expr_Pointer := l_Expr_Pointer + 1;
3970 l_Expr_Tbl(l_Expr_Pointer) := ' ) ';
3971 END LOOP;
3972 --
3973 FOR l_Row IN 1 .. l_Expr_Tbl.Count LOOP
3974 If l_Row = l_Expr_Pointer and rtrim(l_Expr_Tbl(l_Row)) is not NULL Then
3975 v_Expression := v_Expression || rtrim(l_Expr_Tbl(l_Row));
3976 --dbms_output.put_line(l_Expr_Tbl(l_Row) );
3977 ElsIf rtrim(l_Expr_Tbl(l_Row)) is not NULL Then
3978 v_Expression := v_Expression || rtrim(l_Expr_Tbl(l_Row));
3979 --dbms_output.put_line(l_Expr_Tbl(l_Row) );
3980 End If;
3981 End LOOP;
3982 return v_Expression;
3983 EXCEPTION
3984 WHEN OTHERS Then
3985 v_Expression := NULL;
3986 return v_Expression;
3987 END BuildRuleExpression;
3988
3989
3990
3991
3992 FUNCTION Get_Expression_Interest_Type
3993 (
3994 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
3995 ) RETURN VARCHAR2
3996 AS
3997 l_Expression VARCHAR2(1000);
3998
3999 BEGIN
4000 If l_Record.COMPARISON_OPERATOR = '=' Then
4001 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' = ';
4002 -- If the following condition is NOT true then it is a bug/data problem
4003 If l_Record.INTEREST_TYPE_ID IS NOT NULL Then
4004 l_Expression := l_Expression || to_char(l_Record.INTEREST_TYPE_ID) || ' ';
4005 If l_Record.PRIMARY_INTEREST_CODE_ID is NOT NULL Then
4006 l_Expression := l_Expression || 'AND P_RECORD.' ||
4007 l_Record.PRIM_INT_CDE_COL_ALIAS || ' = ' ||
4008 to_char(l_Record.PRIMARY_INTEREST_CODE_ID) || ' ';
4009 If l_Record.SECONDARY_INTEREST_CODE_ID IS NOT NULL Then
4010 l_Expression := l_Expression || 'AND P_RECORD.' ||
4011 l_Record.SEC_INT_CDE_COL_ALIAS || ' = ' ||
4012 to_char(l_Record.SECONDARY_INTEREST_CODE_ID) || ' ';
4013 End If;
4014 End If;
4015 -- If the interest type id is NULL. This is actually data error
4016 Else
4017 l_Expression := NULL;
4018 End If;
4019 -- For interest type Other operator should be invalid
4020 Else
4021 l_Expression := NULL;
4022 End If;
4023 --dbms_output.put_line(l_Expression);
4024
4025 return l_Expression;
4026 END Get_Expression_Interest_Type;
4027
4028
4029
4030
4031 -- NUMERIC Display Type
4032
4033 FUNCTION Get_Expression_NUMERIC
4034 (
4035 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
4036 ) RETURN VARCHAR2
4037 AS
4038 l_Expression VARCHAR2(1000);
4039
4040 BEGIN
4041 --process between operator
4042 If l_Record.COMPARISON_OPERATOR IN ('BETWEEN', 'NOT BETWEEN') Then
4043 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4044 ' ' || to_char(l_Record.LOW_VALUE_NUMBER) || ' AND ' ||
4045 to_char(l_Record.HIGH_VALUE_NUMBER) || ' ';
4046 -- Process like operator
4047 ElsIf l_Record.COMPARISON_OPERATOR IN ('NOT LIKE', 'LIKE') Then
4048 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4049 ' ''' || to_char(l_Record.LOW_VALUE_NUMBER) || '%''';
4050
4051 --Other operator like <, >, <=, >=, <>, <, =
4052 Else
4053 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4054 ' ' || to_char(l_Record.LOW_VALUE_NUMBER);
4055 End If;
4056 return l_Expression;
4057
4058 --dbms_output.put_line(l_Expression);
4059 END Get_Expression_NUMERIC;
4060
4061
4062
4063
4064 -- CURRENCY Display Type
4065
4066 FUNCTION Get_Expression_CURRENCY
4067 (
4068 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
4069 ) RETURN VARCHAR2
4070 AS
4071 l_Expression VARCHAR2(1000);
4072
4073 BEGIN
4074 -----------------------------------------------------------
4075 -- Need to add the currency convertion routine
4076 ------------------------------------------------------------
4077
4078 --process between operator
4079 If l_Record.COMPARISON_OPERATOR IN ('BETWEEN', 'NOT BETWEEN') Then
4080 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4081 ' ' || to_char(l_Record.LOW_VALUE_NUMBER) || ' AND ' ||
4082 to_char(l_Record.HIGH_VALUE_NUMBER) || ' ';
4083 --Other operator like <, >, <=, >=, <>, <, =
4084 Else
4085 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4086 ' ' || to_char(l_Record.LOW_VALUE_NUMBER);
4087 End If;
4088 --dbms_output.put_line(l_Expression);
4089
4090 return l_Expression;
4091
4092 END Get_Expression_CURRENCY;
4093
4094
4095
4096
4097 -- VARCHAR2 Display Type
4098
4099 FUNCTION Get_Expression_CHAR
4100 (
4101 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
4102 ) RETURN VARCHAR2
4103 AS
4104 l_Expression VARCHAR2(1000);
4105
4106 BEGIN
4107 If nvl(l_Record.CONVERT_TO_ID_FLAG, 'N') = 'N' Then
4108 --process between operator
4109 If l_Record.COMPARISON_OPERATOR IN ('BETWEEN', 'NOT BETWEEN') Then
4110 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4111 ' ''' || l_Record.LOW_VALUE_CHAR || ''' AND ''' || l_Record.HIGH_VALUE_CHAR || '''';
4112
4113 -- Process like operator
4114 ElsIf l_Record.COMPARISON_OPERATOR IN ('NOT LIKE', 'LIKE') Then
4115 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4116 '''' || l_Record.LOW_VALUE_CHAR || '%''';
4117
4118 --Other operator like <, >, <=, >=, <>, <, =
4119 Else
4120 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4121 ' ''' || l_Record.LOW_VALUE_CHAR || ''' ';
4122
4123 End If;
4124 --
4125 --If the Convert to ID flag is Turned off
4126 Else
4127 If nvl(l_Record.ID_USED_FLAG, 'N') = 'N' Then
4128 If l_Record.COMPARISON_OPERATOR IN ('BETWEEN', 'NOT BETWEEN') Then
4129 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4130 '''' || l_Record.LOW_VALUE_CHAR || ''' AND ''' || l_Record.HIGH_VALUE_CHAR || '''';
4131
4132 End If;
4133 Else
4134 -- Process like operator
4135 If l_Record.COMPARISON_OPERATOR IN ('NOT LIKE', 'LIKE') Then
4136 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4137 '''' || l_Record.LOW_VALUE_CHAR || '%'' ';
4138
4139 --Other operator like <, >, <=, >=, <>, <, =
4140 Else
4141 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4142 ' ' || to_char(l_Record.LOW_VALUE_CHAR_ID) || ' ';
4143 End If;
4144 end if;
4145 End If;
4146 --dbms_output.put_line(l_Expression);
4147 return l_Expression;
4148 --
4149 END Get_Expression_CHAR;
4150
4151
4152
4153
4154 FUNCTION Get_Expression_Competence
4155 (
4156 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
4157 ) RETURN VARCHAR2
4158 AS
4159 l_Expression VARCHAR2(1000);
4160
4161 BEGIN
4162 If l_Record.COMPARISON_OPERATOR = '=' Then
4163 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' = ';
4164
4165 -- If the following condition is NOT true then it is a bug/data problem
4166 If l_Record.INTEREST_TYPE_ID IS NOT NULL Then
4167 l_Expression := l_Expression || to_char(l_Record.INTEREST_TYPE_ID) || ' ';
4168 If l_Record.PRIMARY_INTEREST_CODE_ID is NOT NULL Then
4169 l_Expression := l_Expression || 'AND P_RECORD.' ||
4170 l_Record.PRIM_INT_CDE_COL_ALIAS || ' = ' ||
4171 to_char(l_Record.PRIMARY_INTEREST_CODE_ID) || ' ';
4172 End If;
4173 -- If the competence id is NULL. This is actually data error
4174 Else
4175 l_Expression := NULL;
4176 End If;
4177
4178 -- For interest type Other operator should be invalid
4179 Else
4180 l_Expression := NULL;
4181 End If;
4182 --dbms_output.put_line(l_Expression);
4183 return l_Expression;
4184 --
4185 END Get_Expression_Competence;
4186
4187
4188
4189
4190 -- Function used in JTF_TERR_RESOURCES_V to return
4191 -- the group_name for the group_id of a resource
4192
4193 FUNCTION get_group_name
4194 (
4195 p_group_id NUMBER
4196 ) RETURN VARCHAR2 IS
4197
4198 x_group_name VARCHAR2(60);
4199
4200 /* cursor to get group_name */
4201
4202 CURSOR c_get_group_name (p_group_id NUMBER) IS
4203 SELECT jrgv.group_name
4204 FROM jtf_rs_groups_vl jrgv
4205 WHERE jrgv.group_id = p_group_id
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;
4215
4216 ELSE /* get group_name */
4217
4218 OPEN c_get_group_name(p_group_id);
4219 FETCH c_get_group_name INTO x_group_name;
4220 CLOSE c_get_group_name;
4221
4222 RETURN x_group_name;
4223
4224 END IF;
4225
4226 EXCEPTION
4227 WHEN NO_DATA_FOUND THEN
4228 RETURN NULL;
4229
4230 END get_group_name;
4231
4232 /* 10/12/00 JDOCHERT */
4233 -- Function used in views to return
4234 -- the resource name
4235 FUNCTION get_resource_name ( p_resource_id NUMBER
4236 , p_resource_type VARCHAR2)
4237 RETURN VARCHAR2 IS
4238
4239 lx_resource_name VARCHAR2(240);
4240
4241 /* cursor to get resource type name */
4242 CURSOR csr_get_rs_name ( lp_resource_id NUMBER
4243 , lp_resource_type VARCHAR2) IS
4244 SELECT jv.resource_name
4245 FROM jtf_rs_resources_vl jv
4246 WHERE jv.resource_id = lp_resource_id
4247 AND jv.resource_type = lp_resource_type
4248 AND rownum < 2;
4249
4250 lx_rs_type_code VARCHAR2(60);
4251
4252 BEGIN
4253
4254 lx_rs_type_code := p_resource_type;
4255
4256 /* 3/19/02: JDOCHERT: 2144381 + 2195839 bug fixes */
4257 IF lx_rs_type_code = 'RS_SUPPLIER' THEN
4258 lx_rs_type_code := 'RS_SUPPLIER_CONTACT';
4259 END IF;
4260
4261 /* get resource type name */
4262 OPEN csr_get_rs_name(p_resource_id, lx_rs_type_code);
4263 FETCH csr_get_rs_name INTO lx_resource_name;
4264 CLOSE csr_get_rs_name;
4265
4266 RETURN lx_resource_name;
4267
4268 EXCEPTION
4269 WHEN NO_DATA_FOUND THEN
4270 RETURN NULL;
4271
4272 END get_resource_name;
4273
4274 /* 10/04/00 JDOCHERT */
4275 -- Function used in views to return
4276 -- the resource type name for the resource type code
4277 -- of a resource
4278 FUNCTION get_rs_type_name (p_rs_type_code VARCHAR2)
4279 RETURN VARCHAR2 IS
4280
4281 lx_rs_type_name VARCHAR2(60);
4282
4283 /* cursor to get resource type name */
4284 CURSOR csr_get_rs_type_name (lp_rs_type_code VARCHAR2) IS
4285 SELECT jo.name
4286 FROM jtf_objects_vl jo
4287 WHERE jo.object_code = lp_rs_type_code
4288 AND rownum < 2;
4289
4290 BEGIN
4291
4292 OPEN csr_get_rs_type_name(p_rs_type_code);
4293 FETCH csr_get_rs_type_name INTO lx_rs_type_name;
4294 CLOSE csr_get_rs_type_name;
4295
4296 RETURN lx_rs_type_name;
4297
4298 EXCEPTION
4299 WHEN NO_DATA_FOUND THEN
4300 RETURN NULL;
4301
4302 END get_rs_type_name;
4303
4304
4305 /* 09/16/00 VVUYYURU */
4306 -- ***************************************************
4307 -- start of comments
4308 -- ***************************************************
4309 -- API name : Copy_Terr_Resources
4310 -- Type : PUBLIC
4311 -- Function : Copy Territory Resources and Resource Access
4312 --
4313 -- Pre-reqs :
4314 -- Parameters:
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 --
4324 -- Optional
4325 -- Parameter Name Data Type Default
4326 --
4327 -- OUT :
4328 -- Parameter Name Data Type
4329 -- x_Return_Status VARCHAR2
4330 -- x_msg_count NUMBER
4331 -- x_msg_data VARCHAR2
4332 -- Notes:
4333 --
4334 --
4335 -- End of Comments
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,
4345 x_msg_count OUT NOCOPY NUMBER,
4346 x_msg_data OUT NOCOPY VARCHAR2,
4347 x_return_status OUT NOCOPY VARCHAR2
4348 )
4349 IS
4350
4351 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Terr_Resources';
4352 l_api_version_number CONSTANT NUMBER := 1.0;
4353 l_return_status VARCHAR2(1);
4354 l_msg_count NUMBER;
4355 l_msg_data VARCHAR2(2000);
4356
4357 l_dest_terr_rsc_id NUMBER;
4358 l_source_terr_rsc_id NUMBER;
4359
4360 l_TerrRsc_rec TerrResource_rec_type;
4361 l_TerrRsc_Access_rec TerrRsc_Access_rec_type;
4362 l_TerrRsc_Out_rec TerrResource_out_rec_type;
4363 l_TerrRsc_Access_Out_rec TerrRsc_Access_out_rec_type;
4364
4365
4366 CURSOR csr_rsc_all (lp_terr_id NUMBER) IS
4367 SELECT terr_rsc_id,
4368 last_update_date,
4369 last_updated_by,
4370 creation_date,
4371 created_by,
4372 last_update_login,
4373 --terr_id,
4374 resource_id,
4375 group_id,
4376 resource_type,
4377 role,
4378 primary_contact_flag,
4379 start_date_active,
4380 end_date_active,
4381 full_access_flag,
4382 org_id
4383 FROM jtf_terr_rsc_ALL
4384 WHERE terr_id = lp_terr_id;
4385
4386
4387 CURSOR csr_rsc_access_all (lp_terr_rsc_id NUMBER) IS
4388 SELECT
4389 --terr_rsc_access_id,
4390 last_update_date,
4391 last_updated_by,
4392 creation_date,
4393 created_by,
4394 last_update_login,
4395 --terr_rsc_id,
4396 access_type,
4397 org_id
4398 FROM jtf_terr_rsc_access_ALL
4399 WHERE terr_rsc_id = lp_terr_rsc_id;
4400
4401
4402 BEGIN
4403
4404 -- Standard Start of API savepoint
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
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.
4419 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4420 FND_MSG_PUB.initialize;
4421 END IF;
4422 */
4423
4424
4425 -- Debug Message
4426 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4427 THEN
4428 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
4429 fnd_message.set_name ('PROC_NAME', l_api_name);
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
4439 OPEN csr_rsc_all (p_source_terr_id);
4440 LOOP
4441 FETCH csr_rsc_all INTO
4442 l_source_terr_rsc_id,
4443 l_TerrRsc_rec.last_update_date,
4444 l_TerrRsc_rec.last_updated_by,
4445 l_TerrRsc_rec.creation_date,
4446 l_TerrRsc_rec.created_by,
4447 l_TerrRsc_rec.last_update_login,
4448 --l_TerrRsc_rec.terr_id,
4449 l_TerrRsc_rec.resource_id,
4450 l_TerrRsc_rec.group_id,
4451 l_TerrRsc_rec.resource_type,
4452 l_TerrRsc_rec.role,
4453 l_TerrRsc_rec.primary_contact_flag,
4454 l_TerrRsc_rec.start_date_active,
4455 l_TerrRsc_rec.end_date_active,
4456 l_TerrRsc_rec.full_access_flag,
4457 l_TerrRsc_rec.org_id;
4458
4459 l_TerrRsc_rec.terr_id := p_dest_terr_id;
4460
4461 EXIT WHEN csr_rsc_all%NOTFOUND;
4462
4463 JTF_TERRITORY_RESOURCE_PVT.Create_Terr_Resource
4464 (
4465 p_TerrRsc_Rec => l_TerrRsc_rec,
4466 p_Api_Version_Number => l_api_version_number,
4467 p_Init_Msg_List => NULL,
4468 p_Commit => NULL,
4469 p_validation_level => NULL,
4470 x_Return_Status => x_Return_Status,
4471 x_Msg_Count => x_Msg_Count,
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;
4490
4491 OPEN csr_rsc_access_all(l_source_terr_rsc_id);
4492 LOOP
4493 FETCH csr_rsc_access_all INTO
4494 --l_TerrRsc_Access_rec.terr_rsc_access_id,
4495 l_TerrRsc_Access_rec.last_update_date,
4496 l_TerrRsc_Access_rec.last_updated_by,
4497 l_TerrRsc_Access_rec.creation_date,
4498 l_TerrRsc_Access_rec.created_by,
4499 l_TerrRsc_Access_rec.last_update_login,
4500 --l_TerrRsc_Access_rec.terr_rsc_id,
4501 l_TerrRsc_Access_rec.access_type,
4502 l_TerrRsc_Access_rec.org_id;
4503
4504 l_TerrRsc_Access_rec.terr_rsc_id := l_dest_terr_rsc_id;
4505
4506 EXIT WHEN csr_rsc_access_all%NOTFOUND;
4507
4508 --dbms_output.put_line('Terr Resource ID : '||l_TerrRsc_Access_rec.terr_rsc_id);
4509
4510 JTF_TERRITORY_RESOURCE_PVT.Create_Resource_Access
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,
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 */
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;
4536
4537 END LOOP;
4538 CLOSE csr_rsc_all;
4539
4540 /*
4541 x_Return_Status := l_Return_Status;
4542 x_Msg_Count := l_Msg_Count;
4543 x_Msg_Data := l_Msg_Data;
4544 */
4545
4546
4547 /* Standard call to get message count and
4548 the message information */
4549
4550 FND_MSG_PUB.Count_And_Get
4551 (
4552 p_count => x_msg_count,
4553 p_data => x_msg_data
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,
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
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,
4581 'Others Exception in Copy_Terr_Resources ' || SQLERRM
4582 );
4583 END IF;
4584
4585 END Copy_Terr_Resources;
4586
4587
4588
4589 /* procedure to check that UK constraint is not
4590 ** being violated on JTF_TERR_RSC_ALL table
4591 ** -- jdochert 09/19
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
4601
4602 -- cursor to check that Unique Key constraint not violated
4603 CURSOR csr_chk_uk_violation ( lp_terr_rsc_id NUMBER
4604 , lp_access_type VARCHAR2) IS
4605 SELECT 'X'
4606 FROM JTF_TERR_RSC_ACCESS_ALL
4607 WHERE terr_rsc_id = lp_terr_rsc_id
4608 AND access_type = lp_access_type;
4609
4610 l_return_csr VARCHAR2(1);
4611
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
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
4634 FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_RSC_ACCESS_UK_CON');
4635 --FND_MESSAGE.Set_Token ('TABLE', 'JTF_TERR_RSC_ACCESS_ALL');
4636 FND_MSG_PUB.ADD;
4637 --END IF;
4638
4639 END IF; /* c_chk_uk_violation%FOUND */
4640 CLOSE csr_chk_uk_violation;
4641
4642 END IF;
4643
4644 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
4645 p_data => x_msg_data);
4646
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;
4656 FND_MSG_PUB.Count_And_Get
4657 ( p_count => x_msg_count,
4658 p_data => x_msg_data
4659 );
4660
4661 END validate_terr_rsc_access_UK;
4662
4663
4664
4665 /* 09/19/00 JDOCHERT */
4666 -- ***************************************************
4667 -- start of comments
4668 -- ***************************************************
4669 -- API name : Transfer_Resource_Territories
4670 -- Type : PUBLIC
4671 -- Function : Transfer one Resource's Territories to another resource
4672 --
4673 -- Pre-reqs :
4674 -- Parameters:
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',
4684 -- p_terr_ids_tbl IN Terr_Ids_Tbl_Type,
4685 -- p_replace_flag IN VARCHAR2 := 'Y',
4686 -- p_add_flag IN VARCHAR2 := 'N',
4687 -- p_delete_flag IN VARCHAR2 := 'Y',
4688 --
4689 -- Optional
4690 -- Parameter Name Data Type Default
4691 --
4692 -- OUT :
4693 -- Parameter Name Data Type
4694 -- x_Return_Status VARCHAR2
4695 -- x_msg_count NUMBER
4696 -- x_msg_data VARCHAR2
4697 -- Notes:
4698 --
4699 --
4700 -- End of Comments
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,
4710 p_all_terr_flag IN VARCHAR2 := 'Y',
4711 p_terr_ids_tbl IN Terr_Ids_Tbl_Type,
4712 p_replace_flag IN VARCHAR2 := 'Y',
4713 p_add_flag IN VARCHAR2 := 'N',
4714 p_delete_flag IN VARCHAR2 := 'Y',
4715 x_msg_count OUT NOCOPY NUMBER,
4716 x_msg_data OUT NOCOPY VARCHAR2,
4717 x_return_status OUT NOCOPY VARCHAR2
4718 ) AS
4719
4720 l_api_name CONSTANT VARCHAR2(30) := 'Transfer_Resource_Territories';
4721 l_api_version_number CONSTANT NUMBER := 1.0;
4722 l_terr_ids_tbl Terr_Ids_Tbl_Type;
4723
4724 CURSOR csr_terr_rsc(l_resource_id NUMBER, l_resource_type VARCHAR) IS
4725 select j.terr_id
4726 from jtf_terr_rsc_ALL j, jtf_terr_ALL jt
4727 where j.resource_id = l_resource_id
4728 and j.resource_type = l_resource_type
4729 and j.terr_id = jt.terr_id
4730 and jt.template_flag = 'N'
4731 and jt.escalation_territory_flag = 'N'
4732 --ARPATEL: bug#2897391
4733 and ( jt.terr_group_flag is null OR jt.terr_group_flag = 'N' )
4734 and not jt.terr_id = 1;
4735
4736 CURSOR csr_unassigned_terrs IS
4737 select terr_id
4738 from JTF_TERR_ALL jt
4739 where NOT EXISTS (select jtr.terr_id
4740 from jtf_terr_rsc_ALL jtr
4741 where jt.terr_id = jtr.terr_id
4742 )
4743 and jt.template_flag = 'N'
4744 and jt.escalation_territory_flag = 'N'
4745 --ARPATEL: bug#2897391
4746 and ( jt.terr_group_flag is null OR jt.terr_group_flag = 'N' )
4747 and not jt.terr_id = 1;
4748
4749 BEGIN
4750 -- Standard Start of API savepoint
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
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.
4765 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4766 FND_MSG_PUB.initialize;
4767 END IF;
4768
4769
4770
4771 -- Debug Message
4772 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4773 THEN
4774 fnd_message.set_name ('JTF', 'JTF_TERRITORY_START_MSG');
4775 fnd_message.set_name ('PROC_NAME', l_api_name);
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
4785
4786 IF p_all_terr_flag = 'Y' and p_source_resource_rec.resource_id is not null
4787 THEN
4788 OPEN csr_terr_rsc(p_source_resource_rec.resource_id, p_source_resource_rec.resource_type);
4789 FETCH csr_terr_rsc
4790 BULK COLLECT INTO l_terr_ids_tbl;
4791 CLOSE csr_terr_rsc;
4792 ELSIF p_all_terr_flag = 'Y' and p_source_resource_rec.resource_id is null
4793 THEN
4794 OPEN csr_unassigned_terrs;
4795 FETCH csr_unassigned_terrs
4796 BULK COLLECT INTO l_terr_ids_tbl;
4797 CLOSE csr_unassigned_terrs;
4798 ELSE
4799 l_terr_ids_tbl := p_terr_ids_tbl;
4800 END IF;
4801
4802
4803 IF p_add_flag = 'Y'
4804 THEN
4805 FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4806 INSERT INTO JTF_TERR_RSC_ALL(
4807 TERR_RSC_ID,
4808 LAST_UPDATE_DATE,
4809 LAST_UPDATED_BY,
4810 CREATION_DATE,
4811 CREATED_BY,
4812 LAST_UPDATE_LOGIN,
4813 TERR_ID,
4814 RESOURCE_ID,
4815 GROUP_ID,
4816 RESOURCE_TYPE,
4817 ROLE,
4818 PRIMARY_CONTACT_FLAG,
4819 START_DATE_ACTIVE,
4820 END_DATE_ACTIVE,
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),
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),
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'
4844 THEN
4845 IF p_delete_flag = 'Y'
4846 THEN
4847
4848 FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4849 INSERT INTO JTF_TERR_RSC_ALL(
4850 TERR_RSC_ID,
4851 LAST_UPDATE_DATE,
4852 LAST_UPDATED_BY,
4853 CREATION_DATE,
4854 CREATED_BY,
4855 LAST_UPDATE_LOGIN,
4856 TERR_ID,
4857 RESOURCE_ID,
4858 GROUP_ID,
4859 RESOURCE_TYPE,
4860 ROLE,
4861 PRIMARY_CONTACT_FLAG,
4862 START_DATE_ACTIVE,
4863 END_DATE_ACTIVE,
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),
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),
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
4887 INSERT INTO JTF_TERR_RSC_ACCESS_ALL(
4888 TERR_RSC_ACCESS_ID,
4889 LAST_UPDATE_DATE,
4890 LAST_UPDATED_BY,
4891 CREATION_DATE,
4892 CREATED_BY,
4893 LAST_UPDATE_LOGIN,
4894 TERR_RSC_ID,
4895 ACCESS_TYPE,
4896 ORG_ID
4897 )
4898 SELECT
4899 JTF_TERR_RSC_ACCESS_s.nextval,
4900 SYSDATE,
4901 G_USER_ID,
4902 SYSDATE,
4903 G_USER_ID,
4904 G_LOGIN_ID,
4905 ntra.terr_rsc_id, -- needs to be the newly created terr_rsc_id from above
4906 raa.access_type,
4907 p_dest_resource_rec.ORG_ID
4908 FROM
4909 JTF_TERR_RSC_ACCESS_ALL raa
4910 ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
4911 ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
4912 WHERE
4913 tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
4914 AND tra.resource_id = p_source_resource_rec.resource_id
4915 AND ntra.terr_id = tra.terr_id
4916 AND ntra.resource_id = p_dest_resource_rec.resource_id
4917 AND tra.terr_id = l_terr_ids_tbl(i)
4918 ;
4919
4920 --Do all the deleting of old records at the end
4921
4922 FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4923 DELETE from jtf_terr_rsc_ALL
4924 where terr_id = l_terr_ids_tbl(i)
4925 and resource_id = p_source_resource_rec.resource_id;
4926
4927
4928 --ARPATEL: 11/06/2003 BUG#2798581 END
4929
4930
4931
4932 ELSE
4933
4934 FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4935 INSERT INTO JTF_TERR_RSC_ALL(
4936 TERR_RSC_ID,
4937 LAST_UPDATE_DATE,
4938 LAST_UPDATED_BY,
4939 CREATION_DATE,
4940 CREATED_BY,
4941 LAST_UPDATE_LOGIN,
4942 TERR_ID,
4943 RESOURCE_ID,
4944 GROUP_ID,
4945 RESOURCE_TYPE,
4946 ROLE,
4947 PRIMARY_CONTACT_FLAG,
4948 START_DATE_ACTIVE,
4949 END_DATE_ACTIVE,
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),
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),
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
4973 INSERT INTO JTF_TERR_RSC_ACCESS_ALL(
4974 TERR_RSC_ACCESS_ID,
4975 LAST_UPDATE_DATE,
4976 LAST_UPDATED_BY,
4977 CREATION_DATE,
4978 CREATED_BY,
4979 LAST_UPDATE_LOGIN,
4980 TERR_RSC_ID,
4981 ACCESS_TYPE,
4982 ORG_ID
4983 )
4984 SELECT
4985 JTF_TERR_RSC_ACCESS_s.nextval,
4986 SYSDATE,
4987 G_USER_ID,
4988 SYSDATE,
4989 G_USER_ID,
4990 G_LOGIN_ID,
4991 ntra.terr_rsc_id, -- needs to be the newly created terr_rsc_id from above
4992 raa.access_type,
4993 p_dest_resource_rec.ORG_ID
4994 FROM
4995 JTF_TERR_RSC_ACCESS_ALL raa
4996 ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
4997 ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
4998 WHERE
4999 tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5000 AND tra.resource_id = p_source_resource_rec.resource_id
5001 AND ntra.terr_id = tra.terr_id
5002 AND ntra.resource_id = p_dest_resource_rec.resource_id
5003 AND tra.terr_id = l_terr_ids_tbl(i)
5004 ;
5005
5006 --UPDATE old rsc to soft delete - end date
5007 FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5008 UPDATE jtf_terr_rsc_all j
5009 SET j.end_date_active = SYSDATE
5010 WHERE j.resource_id = p_source_resource_rec.RESOURCE_ID
5011 AND j.resource_type = p_source_resource_rec.RESOURCE_TYPE
5012 AND j.terr_id = l_terr_ids_tbl(i);
5013
5014 --ARPATEL: 11/06/2003 BUG#2798581 END
5015
5016
5017 END IF;
5018
5019 END IF;
5020
5021 --dbms_output.put_line('Value of l_terr_ids_tbl.first='||TO_CHAR(l_terr_ids_tbl.first));
5022 --dbms_output.put_line('Value of l_terr_ids_tbl.last='||TO_CHAR(l_terr_ids_tbl.last));
5023
5024 -- Debug Message
5025 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5026 THEN
5027 fnd_message.set_name ('JTF', 'JTF_TERRITORY_END_MSG');
5028 fnd_message.set_name ('PROC_NAME', l_api_name);
5029 FND_MSG_PUB.Add;
5030 END IF;
5031
5032 FND_MSG_PUB.Count_And_Get
5033 ( p_count => x_msg_count,
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
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,
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,
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,
5072 'Error inside Transfer_Resource_Territories ' || sqlerrm);
5073 END IF;
5074
5075 END Transfer_Resource_Territories;
5076
5077 END JTF_TERRITORY_RESOURCE_PVT;
5078