[Home] [Help]
PACKAGE BODY: APPS.JTF_TERRITORY_RESOURCE_PVT
Source
1 Package Body JTF_TERRITORY_RESOURCE_PVT AS
2 /* $Header: jtfvtrsb.pls 120.4 2006/05/24 08:13:52 chchandr ship $ */
3
4
5 -- ---------------------------------------------------
6 -- Start of Comments
7 -- ---------------------------------------------------
8 -- PACKAGE NAME: JTF_TERRITORY_RESOURCE_PVT
9 -- ---------------------------------------------------
10 -- PURPOSE
11 -- Joint task force core territory resource private api's.
12 -- This package is a private API for inserting territory
13 -- resources into JTF tables. It contains specification
14 -- for pl/sql records and tables related to territory
15 -- resource.
16 --
17 -- Procedures:
18 -- (see below for specification)
19 --
20 -- NOTES
21 -- This package is for PRIVATE USE ONLY use
22 --
23 -- HISTORY
24 -- 07/29/99 VNEDUNGA Created
25 -- 12/22/99 NEDUNGA Making changes to confirm to
26 -- JTF_TERR_RSC_ALL table change
27 -- 01/06/00 VNEDUNGA Fixing problem with the build rule
28 -- expression
29 -- 01/16/00 VNEDUNGA Commenting out dbms_output
30 -- 01/17/00 VNEDUNGA Cahnging the the hard code value for
31 -- resourece qualifer type from 1 to -1001
32 -- 02/10/00 VNEDUNGA Changing call to table handlers
33 -- 03/15/00 VNEDUNGA Fixng the messaging and record validation
34 -- 06/08/00 VNEDUNGA Adding group id column to resource record
35 --
36 -- 06/12/00 JDOCHERT Added function (get_group_name)
37 -- to get the name
38 -- of the group that the resource
39 -- belongs to
40 --
41 -- 07/20/00 JDOCHERT Changed as follows in Create_TerrResource
42 -- as this meant that a terr_rsc_id passed
43 -- into Create API was ignored:
44 -- l_terr_type_id := 0;
45 -- TO
46 -- L_TerrRsc_Id NUMBER := P_TERRRSC_REC.TERR_RSC_ID;
47 --
48 -- 09/16/00 VVUYYURU Added the NEW procedure Copy_Terr_Resources
49 --
50 -- 09/19/00 JDOCHERT Added 'validate_terr_rsc_access_UK'
51 -- and 'Transfer_Resource_Territories' procedures
52 --
53 -- 10/04/00 JDOCHERT Added get_rs_type_name function
54 --
55 -- 02/15/01 ARPATEL Adapted 'Transfer_Resource_Territories' to allow mass updates
56 -- 09/04/01 ARPATEL Adapted 'Transfer_Resource_Territories' to allow mass assignment of unallocated terrs
57 -- 05/30/01 ARPATEL Added commit processing to transfer_resource_territories and removed from JTFTRMRU.fmb form
58 -- 05/30/01 ARPATEL Added end_date_active checks for cursors of transfer_resource_territories
59 -- 06/06/01 ARPATEL Changed SYSDATE-1 to SYSDATE in transfer_resource_territories
60 -- 06/14/01 ARPATEL Taken out start/end date active clauses in transfer_resource_territories cursors.
61 -- 04/06/04 SHLI Took out check_for_duplicate2 from update_terr_resource.
62 -- 04/13/04 VXSRINIV Added new proc check_for_duplicate2_updates and called from update_terr_resource.
63 -- 09/15/05 mhtran added TRANS_ACCESS_CODE
64 --
65 -- End of Comments
66
67
68
69
70 -- ***************************************************
71 -- GLOBAL VARIABLES
72 -- ***************************************************
73 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_TERRITORY_RESOURCE_PVT';
74 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfvtrsb.pls';
75
76
77 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
78 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
79 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
80 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
81 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
82
83
84
85 --Vai: Bug # 3520561
86 PROCEDURE Check_for_duplicate2_updates (
87 P_TerrRsc_Rec IN TerrResource_Rec_type,
88 x_Return_Status OUT NOCOPY VARCHAR2,
89 x_msg_count OUT NOCOPY NUMBER,
90 x_msg_data OUT NOCOPY VARCHAR2) AS
91
92 l_index NUMBER := 0;
93 l_Res_Counter NUMBER := 0;
94 l_Temp VARCHAR2(1);
95 l_Terr_Id NUMBER;
96
97 --check if duplicate resource_id, group, role exists for this territory
98 cursor c_res is
99 Select start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
100 from JTF_TERR_RSC_ALL
101 where TERR_ID = P_TerrRsc_Rec.Terr_Id
102 --resource with same role and group assigned to this territory
103 and RESOURCE_ID = P_TerrRsc_Rec.Resource_Id
104 and ( (RESOURCE_TYPE is null and P_TerrRsc_Rec.Resource_TYPE is null) or (RESOURCE_TYPE = P_TerrRsc_Rec.Resource_TYPE))
105 and ( (GROUP_ID is null and P_TerrRsc_Rec.GROUP_ID is null) or (P_TerrRsc_Rec.GROUP_ID = GROUP_ID))
106 and ( (ROLE is null and P_TerrRsc_Rec.ROLE is null) or (P_TerrRsc_Rec.ROLE = ROLE))
107 and ( TERR_RSC_ID <> P_TerrRsc_Rec.Terr_Rsc_Id);
108
109 BEGIN
110
111 -- Initialize API return status to success
112 x_return_status := FND_API.G_RET_STS_SUCCESS;
113
114 for l_c_res in c_res loop
115 if P_TerrRsc_Rec.START_DATE_ACTIVE is not null then
116 if P_TerrRsc_Rec.START_DATE_ACTIVE between l_c_res.start_date_active and l_c_res.end_date_active then
117 l_temp := 'X';
118 exit;
119 end if;
120 end if;
121 if P_TerrRsc_Rec.END_DATE_ACTIVE is not null then
122 if P_TerrRsc_Rec.END_DATE_ACTIVE between l_c_res.start_date_active and l_c_res.end_date_active then
123 l_temp := 'X';
124 exit;
125 end if;
126 else
127 if P_TerrRsc_Rec.START_DATE_ACTIVE <= l_c_res.start_date_active then
128 l_temp := 'X';
129 exit;
130 end if;
131 end if;
132 end loop;
133
134 if l_temp = 'X' then
135 fnd_msg_pub.initialize;
136 x_return_status := FND_API.G_RET_STS_ERROR ;
137 fnd_message.set_name('JTF', 'JTF_TERR_DUPLICATE_RESOURCE');
138 FND_MSG_PUB.ADD;
139 FND_MSG_PUB.Count_And_Get( P_count => x_msg_count,
140 P_data => x_msg_data);
141 end if;
142
143 EXCEPTION
144 WHEN NO_DATA_FOUND THEN
145 --no duplicates
146 NULL;
147 WHEN OTHERS THEN
148 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
149 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
150 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others Exception in Check_for_duplicate2 ' || SQLERRM);
151 END IF;
152
153 END Check_for_duplicate2_updates;
154
155 PROCEDURE Check_for_duplicate2 (
156 P_TerrRsc_Rec IN TerrResource_Rec_type_wflex,
157 x_Return_Status OUT NOCOPY VARCHAR2,
158 x_msg_count OUT NOCOPY NUMBER,
159 x_msg_data OUT NOCOPY VARCHAR2) AS
160 l_index NUMBER := 0;
161 l_Res_Counter NUMBER := 0;
162 l_Temp VARCHAR2(1);
163 l_Terr_Id NUMBER;
164
165 cursor c_res is
166 Select start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
167 from JTF_TERR_RSC_ALL
168 where TERR_ID = P_TerrRsc_Rec.Terr_Id
169 --resource with same role and group assigned to this territory
170 and RESOURCE_ID = P_TerrRsc_Rec.Resource_Id
171 and ( (RESOURCE_TYPE is null and P_TerrRsc_Rec.Resource_TYPE is null) or (RESOURCE_TYPE = P_TerrRsc_Rec.Resource_TYPE))
172 and ( (GROUP_ID is null and P_TerrRsc_Rec.GROUP_ID is null) or (P_TerrRsc_Rec.GROUP_ID = GROUP_ID))
173 and ( (ROLE is null and P_TerrRsc_Rec.ROLE is null) or (P_TerrRsc_Rec.ROLE = ROLE));
174
175 BEGIN
176
177 -- Initialize API return status to success
178 x_return_status := FND_API.G_RET_STS_SUCCESS;
179
180 for l_c_res in c_res loop
181 if P_TerrRsc_Rec.START_DATE_ACTIVE is not null then
182 if P_TerrRsc_Rec.START_DATE_ACTIVE between l_c_res.start_date_active and l_c_res.end_date_active then
183 l_temp := 'X';
184 exit;
185 end if;
186 end if;
187 if P_TerrRsc_Rec.END_DATE_ACTIVE is not null then
188 if P_TerrRsc_Rec.END_DATE_ACTIVE between l_c_res.start_date_active and l_c_res.end_date_active then
189 l_temp := 'X';
190 exit;
191 end if;
192 else
193 if P_TerrRsc_Rec.START_DATE_ACTIVE <= l_c_res.start_date_active then
194 l_temp := 'X';
195 exit;
196 end if;
197 end if;
198 end loop;
199
200 if l_temp = 'X' then
201 fnd_msg_pub.initialize;
202 x_return_status := FND_API.G_RET_STS_ERROR ;
203 fnd_message.set_name('JTF', 'JTF_TERR_DUPLICATE_RESOURCE');
204 FND_MSG_PUB.ADD;
205 FND_MSG_PUB.Count_And_Get ( P_count => x_msg_count, P_data => x_msg_data);
206 end if;
207
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 --no duplicates
211 NULL;
212 WHEN OTHERS THEN
213 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
214 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
215 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Others Exception in Check_for_duplicate2 ' || SQLERRM);
216 END IF;
217 END check_for_duplicate2;
218
219 /* ARPATEL: bug#2849410 fix */
220 PROCEDURE Check_for_duplicate2 (
221 P_TerrRsc_Rec IN TerrResource_Rec_type,
222 x_Return_Status OUT NOCOPY VARCHAR2,
223 x_msg_count OUT NOCOPY NUMBER,
224 x_msg_data OUT NOCOPY VARCHAR2) AS
225 l_index NUMBER := 0;
226 l_Res_Counter NUMBER := 0;
227 l_Temp VARCHAR2(1);
228 l_Terr_Id NUMBER;
229
230 cursor c_res is
231 Select start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
232 from JTF_TERR_RSC_ALL
233 where TERR_ID = P_TerrRsc_Rec.Terr_Id
234 --resource with same role and group assigned to this territory
235 and RESOURCE_ID = P_TerrRsc_Rec.Resource_Id
236 and ( (RESOURCE_TYPE is null and P_TerrRsc_Rec.Resource_TYPE is null) or (RESOURCE_TYPE = P_TerrRsc_Rec.Resource_TYPE))
237 and ( (GROUP_ID is null and P_TerrRsc_Rec.GROUP_ID is null) or (P_TerrRsc_Rec.GROUP_ID = GROUP_ID))
238 and ( (ROLE is null and P_TerrRsc_Rec.ROLE is null) or (P_TerrRsc_Rec.ROLE = ROLE));
239
240 BEGIN
241
242 -- Initialize API return status to success
243 x_return_status := FND_API.G_RET_STS_SUCCESS;
244
245 for l_c_res in c_res loop
246 if P_TerrRsc_Rec.START_DATE_ACTIVE is not null then
247 if P_TerrRsc_Rec.START_DATE_ACTIVE between l_c_res.start_date_active and l_c_res.end_date_active then
248 l_temp := 'X';
249 exit;
250 end if;
251 end if;
252 if P_TerrRsc_Rec.END_DATE_ACTIVE is not null then
253 if P_TerrRsc_Rec.END_DATE_ACTIVE between l_c_res.start_date_active and l_c_res.end_date_active then
254 l_temp := 'X';
255 exit;
256 end if;
257 else
258 if P_TerrRsc_Rec.START_DATE_ACTIVE <= l_c_res.start_date_active then
259 l_temp := 'X';
260 exit;
261 end if;
262 end if;
263 end loop;
264
265 if l_temp = 'X' then
266 fnd_msg_pub.initialize;
267 x_return_status := FND_API.G_RET_STS_ERROR ;
268 fnd_message.set_name('JTF', 'JTF_TERR_DUPLICATE_RESOURCE');
269 FND_MSG_PUB.ADD;
270 FND_MSG_PUB.Count_And_Get ( P_count => x_msg_count, P_data => x_msg_data);
271 end if;
272
273 EXCEPTION
274 WHEN NO_DATA_FOUND THEN
275 --no duplicates
276 NULL;
277 WHEN OTHERS THEN
278 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
279 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
280 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Others Exception in Check_for_duplicate2 ' || SQLERRM);
281 END IF;
282 END check_for_duplicate2;
283
284 PROCEDURE Check_for_duplicate
285 (
286 p_TerrRsc_Tbl IN TerrResource_tbl_type_wflex,
287 x_Return_Status OUT NOCOPY VARCHAR2,
288 x_msg_count OUT NOCOPY NUMBER,
289 x_msg_data OUT NOCOPY VARCHAR2
290 )
291 AS
292 l_index NUMBER := 0;
293 l_Res_Counter NUMBER := 0;
294 l_Temp VARCHAR2(1);
295 l_Terr_Id NUMBER;
296
297 BEGIN
298 --
299 --dbms_output.put_line('Inside Validate_Foreign_Keys');
300 -- Initialize API return status to success
301 x_return_status := FND_API.G_RET_STS_SUCCESS;
302 For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
303
304 --check if duplicate resource_id, group, role exists for this territory
305 Select 'X' into l_temp
306 from JTF_TERR_RSC_ALL
307 where TERR_ID = p_TerrRsc_Tbl(l_res_counter).Terr_Id
308 and RESOURCE_ID = p_TerrRsc_Tbl(l_res_counter).Resource_Id
309 and RESOURCE_TYPE = p_TerrRsc_Tbl(l_res_counter).Resource_TYPE
310 and GROUP_ID = p_TerrRsc_Tbl(l_res_counter).GROUP_ID
311 and ROLE = p_TerrRsc_Tbl(l_res_counter).ROLE;
312
313 if l_temp = 'X'
314 then
315 x_return_status := FND_API.G_RET_STS_ERROR ;
316 fnd_message.set_name('JTF', 'JTF_TERR_DUPLICATE_RESOURCE');
317 FND_MSG_PUB.ADD;
318 FND_MSG_PUB.Count_And_Get
319 ( P_count => x_msg_count,
320 P_data => x_msg_data
321 );
322
323 EXIT;
324 end if;
325
326 End LOOP;
327 --
328 EXCEPTION
329 --
330 WHEN NO_DATA_FOUND THEN
331 --NO DUPLICATES
332 NULL;
333
334 WHEN OTHERS THEN
335 --dbms_output.put_line('Validate_Foreign_Keys: OTHERS - ' || SQLERRM);
336 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
337 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
338 THEN
339 FND_MSG_PUB.Add_Exc_Msg
340 ( G_PKG_NAME,
341 'Check_for_duplicate ' || SQLERRM
342 );
343 END IF;
344 --
345 END Check_for_duplicate;
346
347 /* ARPATEL: bug#2849410 fix */
348 PROCEDURE Check_for_duplicate
349 (
350 p_TerrRsc_Tbl IN TerrResource_tbl_type,
351 x_Return_Status OUT NOCOPY VARCHAR2,
352 x_msg_count OUT NOCOPY NUMBER,
353 x_msg_data OUT NOCOPY VARCHAR2
354 )
355 AS
356 l_index NUMBER := 0;
357 l_Res_Counter NUMBER := 0;
358 l_Temp VARCHAR2(1);
359 l_Terr_Id NUMBER;
360
361 BEGIN
362 --
363 --dbms_output.put_line('Inside Validate_Foreign_Keys');
364 -- Initialize API return status to success
365 x_return_status := FND_API.G_RET_STS_SUCCESS;
366 For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
367
368 --check if duplicate resource_id, group, role exists for this territory
369 Select 'X' into l_temp
370 from JTF_TERR_RSC_ALL
371 where TERR_ID = p_TerrRsc_Tbl(l_res_counter).Terr_Id
372 and RESOURCE_ID = p_TerrRsc_Tbl(l_res_counter).Resource_Id
373 and RESOURCE_TYPE = p_TerrRsc_Tbl(l_res_counter).Resource_TYPE
374 and GROUP_ID = p_TerrRsc_Tbl(l_res_counter).GROUP_ID
375 and ROLE = p_TerrRsc_Tbl(l_res_counter).ROLE;
376
377 if l_temp = 'X'
378 then
379 x_return_status := FND_API.G_RET_STS_ERROR ;
380 fnd_message.set_name('JTF', 'JTF_TERR_DUPLICATE_RESOURCE');
381 FND_MSG_PUB.ADD;
382 FND_MSG_PUB.Count_And_Get
383 ( P_count => x_msg_count,
384 P_data => x_msg_data
385 );
386
387 EXIT;
388 end if;
389
390 End LOOP;
391 --
392 EXCEPTION
393 --
394 WHEN NO_DATA_FOUND THEN
395 --NO DUPLICATES
396 NULL;
397
398 WHEN OTHERS THEN
399 --dbms_output.put_line('Validate_Foreign_Keys: OTHERS - ' || SQLERRM);
400 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
401 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
402 THEN
403 FND_MSG_PUB.Add_Exc_Msg
404 ( G_PKG_NAME,
405 'Check_for_duplicate ' || SQLERRM
406 );
407 END IF;
408 --
409 END Check_for_duplicate;
410
411 -- ***************************************************
412 -- ***************************************************
413 -- start of comments
414 -- ***************************************************
415 -- API name : Create_TerrResource
416 -- Type : PUBLIC
417 -- Function : To create Territory Resources - which will insert
418 -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
419 -- tables.
420 --
421 -- Pre-reqs :
422 -- Parameters:
423 -- IN :
424 -- Required
425 -- Parameter Name Data Type Default
426 -- p_Api_Version_Number NUMBER
427 -- p_TerrRsc_Tbl TerrResource_tbl_type_wflex := G_MISS_TERRRESOURCE_TBL_WFLEX
428 -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
429 -- p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL,
430 --
431 -- Optional
432 -- Parameter Name Data Type Default
433 -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
434 -- p_Commit VARCHAR2 := FND_API.G_FALSE
435 --
436 -- OUT :
437 -- Parameter Name Data Type
438 -- x_Return_Status VARCHAR2(1)
439 -- x_Msg_Count NUMBER
440 -- x_Msg_Data VARCHAR2(2000)
441 -- x_TerrRsc_Id NUMBER
442 -- x_Terr_Usgs_Out_Tbl TerrResource_out_tbl_type
443 -- x_Terr_QualTypeUsgs_Out_Tbl TerrRes_Access_out_tbl_type
444 --
445 -- Notes:
446 --
447 --
448 -- End of Comments
449 --
450
451 PROCEDURE Create_TerrResource
452 (
453 p_Api_Version_Number IN NUMBER,
454 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
455 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
456 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
457 x_Return_Status OUT NOCOPY VARCHAR2,
458 x_Msg_Count OUT NOCOPY NUMBER,
459 x_Msg_Data OUT NOCOPY VARCHAR2,
460 p_TerrRsc_Tbl IN TerrResource_tbl_type_wflex := G_MISS_TERRRESOURCE_TBL_WFLEX,
461 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
462 x_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type,
463 x_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_tbl_type
464 )
465 IS
466 l_api_name CONSTANT VARCHAR2(30) := 'Create_TerrResource';
467 l_api_version_number CONSTANT NUMBER := 1.0;
468 l_return_status VARCHAR2(1);
469 l_Res_Counter NUMBER;
470 l_Res_Access_Counter NUMBER;
471 l_TerrRsc_Tbl TerrResource_tbl_type_wflex;
472 l_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type;
473 l_TerrRsc_Out_Tbl TerrResource_out_tbl_type;
474 l_TerrRsc_Access_Out_Tbl TerrRsc_Access_out_tbl_type;
475 l_terrRsc_Id NUMBER := 0;
476 l_index NUMBER := 0;
477 l_Counter NUMBER := 0;
478
479 --
480 BEGIN
481 --dbms_output.put_line('Create_TerrResource PVT: Entering API');
482
483 -- Standard Start of API savepoint
484 SAVEPOINT CREATE_TERRRESOURCE_PVT;
485
486 -- Standard call to check for call compatibility.
487 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
488 p_api_version_number,
489 l_api_name,
490 G_PKG_NAME)
491 THEN
492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
493 END IF;
494
495 -- Initialize message list if p_init_msg_list is set to TRUE.
496 IF FND_API.to_Boolean( p_init_msg_list ) THEN
497 FND_MSG_PUB.initialize;
498 END IF;
499
500 -- Debug Message
501 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
502 THEN
503 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
504 fnd_message.set_name ('PROC_NAME', l_api_name);
505 FND_MSG_PUB.Add;
506 END IF;
507
508 -- Initialize API return status to success
509 x_return_status := FND_API.G_RET_STS_SUCCESS;
510 --
511 -- API body
512 --
513 -- ******************************************************************
514 -- Validate Territory parameters Access
515 -- ******************************************************************
516
517 --mark#2
518 If(p_validation_level <> FND_API.G_VALID_LEVEL_NONE) Then
519 --dbms_output.put_line('Create_TerrResource PVT: About to call Validate_TerrResource_Data');
520
521 --Validate the incomming data for territory creation
522 Validate_TerrResource_Data(p_TerrRsc_Tbl => p_TerrRsc_Tbl,
523 p_TerrRsc_Access_Tbl => p_TerrRsc_Access_Tbl,
524 x_Return_Status => l_return_status,
525 x_Msg_Count => x_Msg_Count,
526 x_Msg_Data => x_Msg_Data);
527
528 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
529 --dbms_output.put_line('Create_TerrResource PVT: Returned x_return_status <> FND_API.G_RET_STS_SUCCESS');
530 RAISE FND_API.G_EXC_ERROR;
531 END IF;
532 End If;
533 --
534 --
535 -- If incomming data is good
536 -- Start creating territory related records
537 --
538 --dbms_output.put_line('Create_TerrResource PVT: Before Calling Create_Terr_Resource PVT');
539 --
540 For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
541 --
542 l_TerrRsc_Tbl(1) := p_TerrRsc_Tbl(l_Res_Counter);
543 l_TerrRsc_Access_Tbl.Delete;
544 l_index := 0;
545 --
546 --dbms_output.put_line('Inside the for loop');
547 --
548 For l_Res_Access_Counter IN p_TerrRsc_Access_Tbl.first .. p_TerrRsc_Access_Tbl.count LOOP
549 --dbms_output.put_line('Inside Values loop - ' || to_char(l_Res_Access_Counter) );
550 -- If the table index changes, then skip the loop
551 If p_TerrRsc_Access_Tbl(l_Res_Access_Counter).qualifier_tbl_index = l_Res_Counter Then
552 l_index := l_index + 1;
553 --dbms_output.put_line('Found values - ' || to_char(l_Res_Counter) || ' Index - ' || to_char(l_index) );
554 l_TerrRsc_Access_Tbl(l_index) := p_TerrRsc_Access_Tbl(l_Res_Access_Counter);
555 End If;
556 END LOOP;
557
558 --dbms_output.put_line('Before calling create Territory Resource');
559 --
560 -- Create the territory qualifier record
561 --
562 Create_Terr_Resource(P_TerrRsc_Tbl => l_TerrRsc_Tbl,
563 p_api_version_number => p_api_version_number,
564 p_init_msg_list => p_init_msg_list,
565 p_commit => p_commit,
566 p_validation_level => p_validation_level,
567 x_return_status => l_return_status,
568 x_msg_count => x_msg_count,
569 x_msg_data => x_msg_data,
570 X_TerrRsc_Out_Tbl => l_TerrRsc_Out_Tbl);
571
572
573 --Save the output status
574 x_TerrRsc_Out_Tbl(nvl(x_TerrRsc_Out_Tbl.first, 0)+1) := l_TerrRsc_Out_Tbl(1);
575
576 -- Save the terr qualifier id
577 l_TerrRsc_Id := l_TerrRsc_Out_Tbl(1).TERR_RSC_ID;
578
579 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
580 X_Return_Status := l_return_status;
581 RAISE FND_API.G_EXC_ERROR;
582 END IF;
583 --
584 -- Get into this loop only if there are access records found
585 If l_TerrRsc_Access_Tbl.Count > 0 Then
586 --dbms_output.put_line('l_TerrRsc_Access_Tbl.Count > 0. Before calling Create_TerrResc_Access');
587 --
588 Create_Resource_Access(p_TerrRsc_Id => l_TerrRsc_Id,
589 p_TerrRsc_Access_Tbl => l_TerrRsc_Access_Tbl,
590 p_api_version_number => p_api_version_number,
591 p_init_msg_list => p_init_msg_list,
592 p_commit => p_commit,
593 p_validation_level => p_validation_level,
594 x_return_status => l_return_status,
595 x_msg_count => x_msg_count,
596 x_msg_data => x_msg_data,
597 x_TerrRsc_Access_Out_Tbl => l_TerrRsc_Access_Out_Tbl);
598 --
599 -- Get the last index used
600 l_index := x_TerrRsc_Access_Out_Tbl.Count;
601 --
602 -- Save the OUT parameters to the original PAI out parametrs
603 For l_Counter IN l_TerrRsc_Access_Out_Tbl.first .. l_TerrRsc_Access_Out_Tbl.count LOOP
604 l_index := l_index + 1;
605 x_TerrRsc_Access_Out_Tbl(l_index) := l_TerrRsc_Access_Out_Tbl(l_counter);
606 End LOOP;
607 --
608 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
609 X_Return_Status := l_return_status;
610 RAISE FND_API.G_EXC_ERROR;
611 END IF;
612
613 -- Reset the table and records to G_MISS_RECORD and G_MISS_TABLE
614 l_TerrRsc_Tbl := G_MISS_TERRRESOURCE_TBL_WFLEX;
615 l_TerrRsc_Access_Tbl := G_MISS_TERRRSC_ACCESS_TBL;
616 End If;
617 --
618 End LOOP;
619
620 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
621 X_Return_Status := l_return_status;
622 RAISE FND_API.G_EXC_ERROR;
623 END IF;
624
625 -- Debug Message
626 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
627 THEN
628 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
629 fnd_message.set_name ('PROC_NAME', l_api_name);
630 FND_MSG_PUB.Add;
631 END IF;
632
633
634 FND_MSG_PUB.Count_And_Get
635 ( p_count => x_msg_count,
636 p_data => x_msg_data
637 );
638
639 -- Standard check for p_commit
640 IF FND_API.to_Boolean( p_commit )
641 THEN
642 COMMIT WORK;
643 END IF;
644
645 --dbms_output.put_line('Create_TerrResource PVT: Exiting API');
646 EXCEPTION
647 --
648 WHEN FND_API.G_EXC_ERROR THEN
649 --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_ERROR');
650 ROLLBACK TO CREATE_TERRRESOURCE_PVT;
651 x_return_status := FND_API.G_RET_STS_ERROR ;
652 FND_MSG_PUB.Count_And_Get
653 ( p_count => x_msg_count,
654 p_data => x_msg_data
655 );
656
657 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
658 --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
659 ROLLBACK TO CREATE_TERRRESOURCE_PVT;
660 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
661 FND_MSG_PUB.Count_And_Get
662 ( p_count => x_msg_count,
663 p_data => x_msg_data
664 );
665
666 WHEN OTHERS THEN
667 --dbms_output.put_line('Create_TerrResource PVT: OTHERS - ' || SQLERRM);
668 ROLLBACK TO CREATE_TERRRESOURCE_PVT;
669 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
670 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
671 THEN
672 fnd_msg_pub.add_exc_msg (
673 g_pkg_name,
674 'Error inside Create_TerrResource ' || sqlerrm);
675 END IF;
676 --
677 END Create_TerrResource;
678
679 -- start of comments
680 -- ***************************************************
681 -- API name : Create_TerrResource
682 -- Type : PUBLIC
683 -- Function : To create Territory Resources - which will insert
684 -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
685 -- tables.
686 --
687 -- Pre-reqs :
688 -- Parameters:
689 -- IN :
690 -- Required
691 -- Parameter Name Data Type Default
692 -- p_Api_Version_Number NUMBER
693 -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
694 -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
695 -- p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL,
696 --
697 -- Optional
698 -- Parameter Name Data Type Default
699 -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
700 -- p_Commit VARCHAR2 := FND_API.G_FALSE
701 --
702 -- OUT :
703 -- Parameter Name Data Type
704 -- x_Return_Status VARCHAR2(1)
705 -- x_Msg_Count NUMBER
706 -- x_Msg_Data VARCHAR2(2000)
707 -- x_TerrRsc_Id NUMBER
708 -- x_Terr_Usgs_Out_Tbl TerrResource_out_tbl_type
709 -- x_Terr_QualTypeUsgs_Out_Tbl TerrRes_Access_out_tbl_type
710 --
711 -- Notes:
712 --
713 --
714 -- End of Comments
715 --
716
717 PROCEDURE Create_TerrResource
718 (
719 p_Api_Version_Number IN NUMBER,
720 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
721 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
722 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
723 x_Return_Status OUT NOCOPY VARCHAR2,
724 x_Msg_Count OUT NOCOPY NUMBER,
725 x_Msg_Data OUT NOCOPY VARCHAR2,
726 p_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
727 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
728 x_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type,
729 x_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_tbl_type
730 )
731 IS
732 l_api_name CONSTANT VARCHAR2(30) := 'Create_TerrResource';
733 l_api_version_number CONSTANT NUMBER := 1.0;
734 l_return_status VARCHAR2(1);
735 l_Res_Counter NUMBER;
736 l_Res_Access_Counter NUMBER;
737 l_TerrRsc_Tbl TerrResource_tbl_type;
738 l_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type;
739 l_TerrRsc_Out_Tbl TerrResource_out_tbl_type;
740 l_TerrRsc_Access_Out_Tbl TerrRsc_Access_out_tbl_type;
741 l_terrRsc_Id NUMBER := 0;
742 l_index NUMBER := 0;
743 l_Counter NUMBER := 0;
744
745 --
746 BEGIN
747 --dbms_output.put_line('Create_TerrResource PVT: Entering API');
748
749 -- Standard Start of API savepoint
750 SAVEPOINT CREATE_TERRRESOURCE_PVT;
751
752 -- Standard call to check for call compatibility.
753 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
754 p_api_version_number,
755 l_api_name,
756 G_PKG_NAME)
757 THEN
758 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
759 END IF;
760
761 -- Initialize message list if p_init_msg_list is set to TRUE.
762 IF FND_API.to_Boolean( p_init_msg_list ) THEN
763 FND_MSG_PUB.initialize;
764 END IF;
765
766 -- Debug Message
767 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
768 THEN
769 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
770 fnd_message.set_name ('PROC_NAME', l_api_name);
771 FND_MSG_PUB.Add;
772 END IF;
773
774 -- Initialize API return status to success
775 x_return_status := FND_API.G_RET_STS_SUCCESS;
776 --
777 -- API body
778 --
779 -- ******************************************************************
780 -- Validate Territory parameters Access
781 -- ******************************************************************
782
783 --mark#2
784 If(p_validation_level <> FND_API.G_VALID_LEVEL_NONE) Then
785 --dbms_output.put_line('Create_TerrResource PVT: About to call Validate_TerrResource_Data');
786
787 --Validate the incomming data for territory creation
788 Validate_TerrResource_Data(p_TerrRsc_Tbl => p_TerrRsc_Tbl,
789 p_TerrRsc_Access_Tbl => p_TerrRsc_Access_Tbl,
790 x_Return_Status => l_return_status,
791 x_Msg_Count => x_Msg_Count,
792 x_Msg_Data => x_Msg_Data);
793
794 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
795 --dbms_output.put_line('Create_TerrResource PVT: Returned x_return_status <> FND_API.G_RET_STS_SUCCESS');
796 RAISE FND_API.G_EXC_ERROR;
797 END IF;
798 End If;
799 --
800 --
801 -- If incomming data is good
802 -- Start creating territory related records
803 --
804 --dbms_output.put_line('Create_TerrResource PVT: Before Calling Create_Terr_Resource PVT');
805 --
806 For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
807 --
808 l_TerrRsc_Tbl(1) := p_TerrRsc_Tbl(l_Res_Counter);
809 l_TerrRsc_Access_Tbl.Delete;
810 l_index := 0;
811 --
812 --dbms_output.put_line('Inside the for loop');
813 --
814 For l_Res_Access_Counter IN p_TerrRsc_Access_Tbl.first .. p_TerrRsc_Access_Tbl.count LOOP
815 --dbms_output.put_line('Inside Values loop - ' || to_char(l_Res_Access_Counter) );
816 -- If the table index changes, then skip the loop
817 If p_TerrRsc_Access_Tbl(l_Res_Access_Counter).qualifier_tbl_index = l_Res_Counter Then
818 l_index := l_index + 1;
819 --dbms_output.put_line('Found values - ' || to_char(l_Res_Counter) || ' Index - ' || to_char(l_index) );
820 l_TerrRsc_Access_Tbl(l_index) := p_TerrRsc_Access_Tbl(l_Res_Access_Counter);
821 End If;
822 END LOOP;
823
824 --dbms_output.put_line('Before calling create Territory Resource');
825 --
826 -- Create the territory qualifier record
827 --
828 Create_Terr_Resource(P_TerrRsc_Tbl => l_TerrRsc_Tbl,
829 p_api_version_number => p_api_version_number,
830 p_init_msg_list => p_init_msg_list,
831 p_commit => p_commit,
832 p_validation_level => p_validation_level,
833 x_return_status => l_return_status,
834 x_msg_count => x_msg_count,
835 x_msg_data => x_msg_data,
836 X_TerrRsc_Out_Tbl => l_TerrRsc_Out_Tbl);
837
838
839 --Save the output status
840 x_TerrRsc_Out_Tbl(nvl(x_TerrRsc_Out_Tbl.first, 0)+1) := l_TerrRsc_Out_Tbl(1);
841
842 -- Save the terr qualifier id
843 l_TerrRsc_Id := l_TerrRsc_Out_Tbl(1).TERR_RSC_ID;
844
845 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
846 X_Return_Status := l_return_status;
847 RAISE FND_API.G_EXC_ERROR;
848 END IF;
849 --
850 -- Get into this loop only if there are access records found
851 If l_TerrRsc_Access_Tbl.Count > 0 Then
852 --dbms_output.put_line('l_TerrRsc_Access_Tbl.Count > 0. Before calling Create_TerrResc_Access');
853 --
854 Create_Resource_Access(p_TerrRsc_Id => l_TerrRsc_Id,
855 p_TerrRsc_Access_Tbl => l_TerrRsc_Access_Tbl,
856 p_api_version_number => p_api_version_number,
857 p_init_msg_list => p_init_msg_list,
858 p_commit => p_commit,
859 p_validation_level => p_validation_level,
860 x_return_status => l_return_status,
861 x_msg_count => x_msg_count,
862 x_msg_data => x_msg_data,
863 x_TerrRsc_Access_Out_Tbl => l_TerrRsc_Access_Out_Tbl);
864 --
865 -- Get the last index used
866 l_index := x_TerrRsc_Access_Out_Tbl.Count;
867 --
868 -- Save the OUT parameters to the original PAI out parametrs
869 For l_Counter IN l_TerrRsc_Access_Out_Tbl.first .. l_TerrRsc_Access_Out_Tbl.count LOOP
870 l_index := l_index + 1;
871 x_TerrRsc_Access_Out_Tbl(l_index) := l_TerrRsc_Access_Out_Tbl(l_counter);
872 End LOOP;
873 --
874 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
875 X_Return_Status := l_return_status;
876 RAISE FND_API.G_EXC_ERROR;
877 END IF;
878
879 -- Reset the table and records to G_MISS_RECORD and G_MISS_TABLE
880 l_TerrRsc_Tbl := G_MISS_TERRRESOURCE_TBL;
881 l_TerrRsc_Access_Tbl := G_MISS_TERRRSC_ACCESS_TBL;
882 End If;
883 --
884 End LOOP;
885
886 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
887 X_Return_Status := l_return_status;
888 RAISE FND_API.G_EXC_ERROR;
889 END IF;
890
891 -- Debug Message
892 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
893 THEN
894 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
895 fnd_message.set_name ('PROC_NAME', l_api_name);
896 FND_MSG_PUB.Add;
897 END IF;
898
899
900 FND_MSG_PUB.Count_And_Get
901 ( p_count => x_msg_count,
902 p_data => x_msg_data
903 );
904
905 -- Standard check for p_commit
906 IF FND_API.to_Boolean( p_commit )
907 THEN
908 COMMIT WORK;
909 END IF;
910
911 --dbms_output.put_line('Create_TerrResource PVT: Exiting API');
912 EXCEPTION
913 --
914 WHEN FND_API.G_EXC_ERROR THEN
915 --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_ERROR');
916 ROLLBACK TO CREATE_TERRRESOURCE_PVT;
917 x_return_status := FND_API.G_RET_STS_ERROR ;
918 FND_MSG_PUB.Count_And_Get
919 ( p_count => x_msg_count,
920 p_data => x_msg_data
921 );
922
923 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
924 --dbms_output.put_line('Create_TerrResource PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
925 ROLLBACK TO CREATE_TERRRESOURCE_PVT;
926 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
927 FND_MSG_PUB.Count_And_Get
928 ( p_count => x_msg_count,
929 p_data => x_msg_data
930 );
931
932 WHEN OTHERS THEN
933 --dbms_output.put_line('Create_TerrResource PVT: OTHERS - ' || SQLERRM);
934 ROLLBACK TO CREATE_TERRRESOURCE_PVT;
935 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
936 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
937 THEN
938 fnd_msg_pub.add_exc_msg (
939 g_pkg_name,
940 'Error inside Create_TerrResource ' || sqlerrm);
941 END IF;
942 --
943 END Create_TerrResource;
944
945
946
947
948 -- ***************************************************
949 -- start of comments
950 -- ***************************************************
951 --
952 -- API name : Delete_Terr_Resource
953 -- Type : PUBLIC
954 -- Function : To delete resources associated with
955 -- Territories
956 --
957 -- Pre-reqs :
958 -- Parameters:
959 -- IN :
960 -- Required
961 -- Parameter Name Data Type Default
962 -- p_Api_Version_Number NUMBER
963 -- p_TerrRsc_Id NUMBER
964 --
965 -- Optional
966 -- Parameter Name Data Type Default
967 -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
968 -- p_Commit VARCHAR2 FND_API.G_FALSE
969 -- p_validation_level NUMBER FND_API.G_VALID_LEVEL_FULL,
970 --
971 -- OUT :
972 -- Parameter Name Data Type
973 -- X_Return_Status VARCHAR2(1)
974 -- X_Msg_Count NUMBER
975 -- X_Msg_Data VARCHAR2(2000)
976 --
977 --
978 -- Notes:
979 -- Rules for deletion have to be very strict
980 --
981 -- End of Comments
982 --
983
984 PROCEDURE Delete_Terr_Resource
985 (
986 p_Api_Version_Number IN NUMBER,
987 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
988 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
989 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
990 X_Return_Status OUT NOCOPY VARCHAR2,
991 X_Msg_Count OUT NOCOPY NUMBER,
992 X_Msg_Data OUT NOCOPY VARCHAR2,
993 p_TerrRsc_Id IN NUMBER
994 )
995 AS
996 v_TerrRsc_Id NUMBER := p_TerrRsc_Id;
997
998
999 --Declare cursor to get resource accesses
1000 Cursor C_GetTerrRscAccess IS
1001 Select JTRA.TERR_RSC_ACCESS_ID
1002 From JTF_TERR_RSC_ACCESS_ALL JTRA
1003 Where TERR_RSC_ID = v_TerrRsc_Id
1004 for UPDATE;
1005
1006 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Terr_Resource';
1007 l_api_version_number CONSTANT NUMBER := 1.0;
1008
1009 l_return_status VARCHAR2(1);
1010
1011 BEGIN
1012 --
1013 -- Standard Start of API savepoint
1014 SAVEPOINT DELETE_TERR_RESOURCE_PVT;
1015
1016 -- Standard call to check for call compatibility.
1017 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1018 p_api_version_number,
1019 l_api_name,
1020 G_PKG_NAME)
1021 THEN
1022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1023 END IF;
1024
1025 -- Initialize message list if p_init_msg_list is set to TRUE.
1026 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1027 FND_MSG_PUB.initialize;
1028 END IF;
1029
1030 -- Debug Message
1031 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1032 THEN
1033 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1034 fnd_message.set_name ('PROC_NAME', l_api_name);
1035 FND_MSG_PUB.Add;
1036 END IF;
1037
1038 -- Initialize API return status to success
1039 x_return_status := FND_API.G_RET_STS_SUCCESS;
1040 --
1041 -- API body
1042 --
1043 FOR C IN C_GetTerrRscAccess LOOP
1044 --
1045 Delete_TerrRsc_Access(P_Api_Version_Number,
1046 P_Init_Msg_List,
1047 P_Commit,
1048 C.Terr_rsc_access_id,
1049 l_Return_Status,
1050 X_Msg_Count,
1051 X_Msg_Data);
1052
1053 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1054 RAISE FND_API.G_EXC_ERROR;
1055 END IF;
1056 --
1057 END LOOP;
1058 --
1059
1060 Delete_TerrResource(P_Api_Version_Number,
1061 P_Init_Msg_List,
1062 P_Commit,
1063 v_terrRsc_id,
1064 l_Return_Status,
1065 X_Msg_Count,
1066 X_Msg_Data);
1067
1068 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1069 RAISE FND_API.G_EXC_ERROR;
1070 END IF;
1071
1072 -- Debug Message
1073 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1074 THEN
1075 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1076 fnd_message.set_name ('PROC_NAME', l_api_name);
1077 FND_MSG_PUB.Add;
1078 END IF;
1079
1080
1081 FND_MSG_PUB.Count_And_Get
1082 ( p_count => x_msg_count,
1083 p_data => x_msg_data
1084 );
1085
1086 -- Standard check for p_commit
1087 IF FND_API.to_Boolean( p_commit )
1088 THEN
1089 COMMIT WORK;
1090 END IF;
1091
1092 EXCEPTION
1093 --
1094 WHEN FND_API.G_EXC_ERROR THEN
1095 ROLLBACK TO DELETE_TERR_RESOURCE_PVT;
1096 x_return_status := FND_API.G_RET_STS_ERROR ;
1097 FND_MSG_PUB.Count_And_Get
1098 ( p_count => x_msg_count,
1099 p_data => x_msg_data
1100 );
1101
1102 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1103 ROLLBACK TO DELETE_TERR_RESOURCE_PVT;
1104 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1105 FND_MSG_PUB.Count_And_Get
1106 ( p_count => x_msg_count,
1107 p_data => x_msg_data
1108 );
1109
1110 WHEN OTHERS THEN
1111 ROLLBACK TO DELETE_TERR_RESOURCE_PVT;
1112 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1113 FND_MSG_PUB.Count_And_Get
1114 ( p_count => x_msg_count,
1115 p_data => x_msg_data
1116 );
1117 --
1118 END Delete_Terr_Resource;
1119
1120
1121
1122
1123 -- ***************************************************
1124 -- start of comments
1125 -- ***************************************************
1126 -- API name : Update_TerrResource
1127 -- Type : PUBLIC
1128 -- Function : To Update Territory Resources - which will update
1129 -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
1130 -- tables.
1131 --
1132 -- Pre-reqs :
1133 -- Parameters:
1134 -- IN :
1135 -- Required
1136 -- Parameter Name Data Type Default
1137 -- p_Api_Version_Number NUMBER
1138 -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
1139 -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
1140 --
1141 -- Optional
1142 -- Parameter Name Data Type Default
1143 -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
1144 -- p_Commit VARCHAR2 := FND_API.G_FALSE
1145 --
1146 -- OUT :
1147 -- Parameter Name Data Type
1148 -- x_Return_Status VARCHAR2(1)
1149 -- x_Msg_Count NUMBER
1150 -- x_Msg_Data VARCHAR2(2000)
1151 -- x_TerrRsc_Id NUMBER
1152 -- x_Terr_Usgs_Out_Tbl TerrResource_out_tbl_type
1153 -- x_Terr_QualTypeUsgs_Out_Tbl TerrRes_Access_out_tbl_type
1154 --
1155 -- Notes:
1156 --
1157 --
1158 -- End of Comments
1159 --
1160
1161 PROCEDURE Update_TerrResource
1162 (
1163 p_Api_Version_Number IN NUMBER,
1164 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1165 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1166 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1167 x_Return_Status OUT NOCOPY VARCHAR2,
1168 x_Msg_Count OUT NOCOPY NUMBER,
1169 x_Msg_Data OUT NOCOPY VARCHAR2,
1170 p_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
1171 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
1172 x_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type,
1173 x_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_tbl_type
1174 )
1175 AS
1176 l_api_name CONSTANT VARCHAR2(30) := 'Update_TerrResource (Tbl)';
1177 l_api_version_number CONSTANT NUMBER := 1.0;
1178 l_return_status VARCHAR2(1);
1179
1180
1181 BEGIN
1182 -- Standard Start of API savepoint
1183 SAVEPOINT UPDATE_TERRRESOURCE_PVT;
1184
1185 --ARPATEL: bug#2849410
1186 Check_for_duplicate (p_TerrRsc_Tbl => p_TerrRsc_Tbl,
1187 x_Return_Status => l_return_status,
1188 x_msg_count => x_msg_count,
1189 x_Msg_Data => x_Msg_Data);
1190
1191 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1192 x_return_status := l_return_status;
1193 END IF;
1194
1195 -- Standard call to check for call compatibility.
1196 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1197 p_api_version_number,
1198 l_api_name,
1199 G_PKG_NAME)
1200 THEN
1201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1202 END IF;
1203
1204 -- Initialize message list if p_init_msg_list is set to TRUE.
1205 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1206 FND_MSG_PUB.initialize;
1207 END IF;
1208
1209 -- Debug Message
1210 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1211 THEN
1212 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1213 fnd_message.set_name ('PROC_NAME', l_api_name);
1214 FND_MSG_PUB.Add;
1215 END IF;
1216
1217
1218 -- Initialize API return status to success
1219 x_return_status := FND_API.G_RET_STS_SUCCESS;
1220 --
1221 -- API body
1222 --
1223 If P_TerrRsc_Tbl.Count > 0 Then
1224 --
1225 Update_Terr_Resource(P_TerrRsc_Tbl => P_TerrRsc_Tbl,
1226 p_api_version_number => p_api_version_number,
1227 p_init_msg_list => p_init_msg_list,
1228 p_commit => p_commit,
1229 p_validation_level => p_validation_level,
1230 x_return_status => l_return_status,
1231 x_msg_count => x_msg_count,
1232 x_msg_data => x_msg_data,
1233 X_TerrRsc_Out_Tbl => X_TerrRsc_Out_Tbl);
1234
1235
1236 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1237 x_return_status := l_return_status;
1238 RAISE FND_API.G_EXC_ERROR;
1239 END IF;
1240 --
1241 End If;
1242 --
1243 If p_TerrRsc_Access_Tbl.Count > 0 Then
1244 --
1245 Update_Resource_Access(p_TerrRsc_Access_Tbl => p_TerrRsc_Access_Tbl,
1246 p_api_version_number => p_api_version_number,
1247 p_init_msg_list => p_init_msg_list,
1248 p_commit => p_commit,
1249 p_validation_level => p_validation_level,
1250 x_return_status => l_return_status,
1251 x_msg_count => x_msg_count,
1252 x_msg_data => x_msg_data,
1253 X_TerrRsc_Access_Out_Tbl => x_TerrRsc_Access_Out_Tbl);
1254 --
1255 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1256 x_return_status := l_return_status;
1257 RAISE FND_API.G_EXC_ERROR;
1258 END IF;
1259 --
1260 End If;
1261
1262 -- Debug Message
1263 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1264 THEN
1265 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1266 fnd_message.set_name ('PROC_NAME', l_api_name);
1267 FND_MSG_PUB.Add;
1268 END IF;
1269
1270
1271 FND_MSG_PUB.Count_And_Get
1272 ( p_count => x_msg_count,
1273 p_data => x_msg_data
1274 );
1275
1276 -- Standard check for p_commit
1277 IF FND_API.to_Boolean( p_commit )
1278 THEN
1279 COMMIT WORK;
1280 END IF;
1281 --
1282 EXCEPTION
1283 --
1284 WHEN FND_API.G_EXC_ERROR THEN
1285 ROLLBACK TO UPDATE_TERRRESOURCE_PVT;
1286 x_return_status := FND_API.G_RET_STS_ERROR ;
1287 FND_MSG_PUB.Count_And_Get
1288 ( p_count => x_msg_count,
1289 p_data => x_msg_data
1290 );
1291
1292 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1293 ROLLBACK TO UPDATE_TERRRESOURCE_PVT;
1294 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1295 FND_MSG_PUB.Count_And_Get
1296 ( p_count => x_msg_count,
1297 p_data => x_msg_data
1298 );
1299
1300 WHEN OTHERS THEN
1301 ROLLBACK TO UPDATE_TERRRESOURCE_PVT;
1302 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1303 FND_MSG_PUB.Count_And_Get
1304 ( p_count => x_msg_count,
1305 p_data => x_msg_data
1306 );
1307 --
1308 END Update_TerrResource;
1309
1310
1311
1312 PROCEDURE Create_Terr_Resource
1313 (
1314 P_TerrRsc_Rec IN TerrResource_Rec_type_wflex,
1315 p_Api_Version_Number IN NUMBER,
1316 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1317 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1318 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1319 x_Return_Status OUT NOCOPY VARCHAR2,
1320 x_Msg_Count OUT NOCOPY NUMBER,
1321 x_Msg_Data OUT NOCOPY VARCHAR2,
1322 X_TerrRsc_Out_Rec OUT NOCOPY TerrResource_out_Rec_type
1323 )
1324 AS
1325 l_rowid ROWID;
1326 l_api_name CONSTANT VARCHAR2(30) := 'Create_Terr_Resource';
1327 L_TerrRsc_Id NUMBER := P_TERRRSC_REC.TERR_RSC_ID;
1328 l_return_status VARCHAR2(1);
1329
1330 BEGIN
1331 --dbms_output.put_line('Create_Terr_Resource REC: Entering API');
1332
1333 -- Debug Message
1334 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1335 THEN
1336 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1337 fnd_message.set_name ('PROC_NAME', l_api_name);
1338 FND_MSG_PUB.Add;
1339 END IF;
1340
1341 -- Initialize API return status to success
1342 x_return_status := FND_API.G_RET_STS_SUCCESS;
1343
1344 --ARPATEL: bug#2849410
1345 Check_for_duplicate2 (P_TerrRsc_Rec => P_TerrRsc_Rec,
1346 x_Return_Status => l_return_status,
1347 x_msg_count => x_msg_count,
1348 x_Msg_Data => x_Msg_Data);
1349
1350
1351 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1352 RAISE FND_API.G_EXC_ERROR;
1353 END IF;
1354
1355 IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE)
1356 THEN
1357 -- Debug message
1358 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1359 THEN
1360 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
1361 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Rsc');
1362 FND_MSG_PUB.Add;
1363 END IF;
1364 --
1365 -- Invoke validation procedures
1366 Validate_Terr_Rsc(p_init_msg_list => FND_API.G_FALSE,
1367 x_Return_Status => x_return_status,
1368 x_msg_count => x_msg_count,
1369 x_msg_data => x_msg_data,
1370 P_TerrRsc_Rec => P_TerrRsc_Rec);
1371
1372 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1373 RAISE FND_API.G_EXC_ERROR;
1374 END IF;
1375 --
1376 END IF;
1377
1378 -- Call insert terr_Qual_Type_Usgs table handler
1379 --
1380 /* Intialise to NULL if FND_API.G_MISS_NUM,
1381 ** otherwise used passed in value
1382 */
1383 IF (l_TerrRsc_id = FND_API.G_MISS_NUM) THEN
1384 l_TerrRsc_id := NULL;
1385 END IF;
1386
1387 --dbms_output.put_line('Create_Terr_Resource REC: Before Calling JTF_TERR_RSC_PKG.Insert_Row');
1388 JTF_TERR_RSC_PKG.Insert_Row(x_Rowid => l_rowid,
1389 x_TERR_RSC_ID => l_TerrRsc_Id,
1390 x_LAST_UPDATE_DATE => P_TerrRsc_Rec.LAST_UPDATE_DATE,
1391 x_LAST_UPDATED_BY => P_TerrRsc_Rec.LAST_UPDATED_BY,
1392 x_CREATION_DATE => P_TerrRsc_Rec.CREATION_DATE,
1393 x_CREATED_BY => P_TerrRsc_Rec.CREATED_BY,
1394 x_LAST_UPDATE_LOGIN => P_TerrRsc_Rec.LAST_UPDATE_LOGIN,
1395 x_TERR_ID => P_TerrRsc_Rec.TERR_ID,
1396 x_RESOURCE_ID => P_TerrRsc_Rec.RESOURCE_ID,
1397 x_GROUP_ID => P_TerrRsc_Rec.GROUP_ID,
1398 x_RESOURCE_TYPE => P_TerrRsc_Rec.RESOURCE_TYPE,
1399 x_ROLE => P_TerrRsc_Rec.ROLE,
1400 x_PRIMARY_CONTACT_FLAG => P_TerrRsc_Rec.PRIMARY_CONTACT_FLAG,
1401 X_START_DATE_ACTIVE => P_TerrRsc_Rec.START_DATE_ACTIVE,
1402 X_END_DATE_ACTIVE => P_TerrRsc_Rec.END_DATE_ACTIVE,
1403 X_FULL_ACCESS_FLAG => P_TerrRsc_Rec.FULL_ACCESS_FLAG,
1404 X_ORG_ID => P_TerrRsc_Rec.ORG_ID,
1405 X_ATTRIBUTE_CATEGORY => P_TerrRsc_Rec.ATTRIBUTE_CATEGORY,
1406 X_ATTRIBUTE1 => P_TerrRsc_Rec.ATTRIBUTE1,
1407 X_ATTRIBUTE2 => P_TerrRsc_Rec.ATTRIBUTE2,
1408 X_ATTRIBUTE3 => P_TerrRsc_Rec.ATTRIBUTE3,
1409 X_ATTRIBUTE4 => P_TerrRsc_Rec.ATTRIBUTE4,
1410 X_ATTRIBUTE5 => P_TerrRsc_Rec.ATTRIBUTE5,
1411 X_ATTRIBUTE6 => P_TerrRsc_Rec.ATTRIBUTE6,
1412 X_ATTRIBUTE7 => P_TerrRsc_Rec.ATTRIBUTE7,
1413 X_ATTRIBUTE8 => P_TerrRsc_Rec.ATTRIBUTE8,
1414 X_ATTRIBUTE9 => P_TerrRsc_Rec.ATTRIBUTE9,
1415 X_ATTRIBUTE10 => P_TerrRsc_Rec.ATTRIBUTE10,
1416 X_ATTRIBUTE11 => P_TerrRsc_Rec.ATTRIBUTE11,
1417 X_ATTRIBUTE12 => P_TerrRsc_Rec.ATTRIBUTE12,
1418 X_ATTRIBUTE13 => P_TerrRsc_Rec.ATTRIBUTE13,
1419 X_ATTRIBUTE14 => P_TerrRsc_Rec.ATTRIBUTE14,
1420 X_ATTRIBUTE15 => P_TerrRsc_Rec.ATTRIBUTE15 );
1421
1422 --dbms_output.put_line('After calling JTF_TERR_RSC_PKG.Insert_Row');
1423 -- Save the terr_usg_id and
1424 X_TerrRsc_Out_Rec.TERR_RSC_ID := l_TerrRsc_Id;
1425
1426 -- If successful then save the success status for the record
1427 X_TerrRsc_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
1428
1429 -- Debug Message
1430 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1431 THEN
1432 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1433 fnd_message.set_name ('PROC_NAME', l_api_name);
1434 FND_MSG_PUB.Add;
1435 END IF;
1436
1437 --dbms_output.put_line('Create_Terr_Resource REC: Exiting API');
1438 EXCEPTION
1439 WHEN FND_API.G_EXC_ERROR THEN
1440 --dbms_output.put_line('Create_Terr_Resource: FND_API.G_EXC_ERROR');
1441
1442 X_TerrRsc_Out_Rec.TERR_RSC_ID := NULL;
1443 X_TerrRsc_Out_Rec.return_status := x_return_status;
1444 x_return_status := FND_API.G_RET_STS_ERROR ;
1445
1446 FND_MSG_PUB.Count_And_Get
1447 ( P_count => x_msg_count,
1448 P_data => x_msg_data
1449 );
1450
1451 WHEN OTHERS THEN
1452 --dbms_output.put_line('Create_Terr_Resource REC: OTHERS - ' || SQLERRM);
1453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1454 --
1455 X_TerrRsc_Out_Rec.TERR_RSC_ID := NULL;
1456 X_TerrRsc_Out_Rec.return_status := x_return_status;
1457 --
1458 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1459 THEN
1460 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others exception in Create_Terr_Resource ' || SQLERRM);
1461 END IF;
1462 --
1463 End Create_Terr_Resource;
1464
1465
1466 --
1467 --
1468 -- ***************************************************
1469 -- start of comments
1470 -- ***************************************************
1471 -- API name : Create_Terr_Resource
1472 -- Type : PRIVATE
1473 -- Function : To create Territories resource
1474 --
1475 --
1476 -- Pre-reqs :
1477 -- Parameters:
1478 -- IN :
1479 -- Required
1480 -- Parameter Name Data Type Default
1481 -- P_TerrRsc_Rec TerrResource_tbl_type
1482 --
1483 -- OUT :
1484 -- Parameter Name Data Type
1485 -- X_Return_Status VARCHAR2(1)
1486 -- X_TerrRsc_Out_Rec TerrResource_out_tbl_type
1487 --
1488 -- Notes: This is a an overloaded procedure
1489 --
1490 --
1491 -- End of Comments
1492 --
1493
1494 PROCEDURE Create_Terr_Resource
1495 (
1496 P_TerrRsc_Rec IN TerrResource_Rec_type,
1497 p_Api_Version_Number IN NUMBER,
1498 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1499 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1500 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1501 x_Return_Status OUT NOCOPY VARCHAR2,
1502 x_Msg_Count OUT NOCOPY NUMBER,
1503 x_Msg_Data OUT NOCOPY VARCHAR2,
1504 X_TerrRsc_Out_Rec OUT NOCOPY TerrResource_out_Rec_type
1505 )
1506 AS
1507 l_rowid ROWID;
1508 l_api_name CONSTANT VARCHAR2(30) := 'Create_Terr_Resource';
1509 L_TerrRsc_Id NUMBER := P_TERRRSC_REC.TERR_RSC_ID;
1510 l_return_status VARCHAR2(1);
1511
1512 BEGIN
1513 --dbms_output.put_line('Create_Terr_Resource REC: Entering API');
1514
1515 -- Debug Message
1516 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1517 THEN
1518 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1519 fnd_message.set_name ('PROC_NAME', l_api_name);
1520 FND_MSG_PUB.Add;
1521 END IF;
1522
1523 -- Initialize API return status to success
1524 x_return_status := FND_API.G_RET_STS_SUCCESS;
1525
1526 --ARPATEL: bug#2849410
1527 Check_for_duplicate2 (P_TerrRsc_Rec => P_TerrRsc_Rec,
1528 x_Return_Status => l_return_status,
1529 x_msg_count => x_msg_count,
1530 x_Msg_Data => x_Msg_Data);
1531
1532
1533 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1534 RAISE FND_API.G_EXC_ERROR;
1535 END IF;
1536
1537 IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE)
1538 THEN
1539 -- Debug message
1540 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1541 THEN
1542 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
1543 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Rsc');
1544 FND_MSG_PUB.Add;
1545 END IF;
1546 --
1547 -- Invoke validation procedures
1548 Validate_Terr_Rsc(p_init_msg_list => FND_API.G_FALSE,
1549 x_Return_Status => x_return_status,
1550 x_msg_count => x_msg_count,
1551 x_msg_data => x_msg_data,
1552 P_TerrRsc_Rec => P_TerrRsc_Rec);
1553
1554 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1555 RAISE FND_API.G_EXC_ERROR;
1556 END IF;
1557 --
1558 END IF;
1559
1560 -- Call insert terr_Qual_Type_Usgs table handler
1561 --
1562 /* Intialise to NULL if FND_API.G_MISS_NUM,
1563 ** otherwise used passed in value
1564 */
1565 IF (l_TerrRsc_id = FND_API.G_MISS_NUM) THEN
1566 l_TerrRsc_id := NULL;
1567 END IF;
1568
1569 --dbms_output.put_line('Create_Terr_Resource REC: Before Calling JTF_TERR_RSC_PKG.Insert_Row');
1570 JTF_TERR_RSC_PKG.Insert_Row(x_Rowid => l_rowid,
1571 x_TERR_RSC_ID => l_TerrRsc_Id,
1572 x_LAST_UPDATE_DATE => P_TerrRsc_Rec.LAST_UPDATE_DATE,
1573 x_LAST_UPDATED_BY => P_TerrRsc_Rec.LAST_UPDATED_BY,
1574 x_CREATION_DATE => P_TerrRsc_Rec.CREATION_DATE,
1575 x_CREATED_BY => P_TerrRsc_Rec.CREATED_BY,
1576 x_LAST_UPDATE_LOGIN => P_TerrRsc_Rec.LAST_UPDATE_LOGIN,
1577 x_TERR_ID => P_TerrRsc_Rec.TERR_ID,
1578 x_RESOURCE_ID => P_TerrRsc_Rec.RESOURCE_ID,
1579 x_GROUP_ID => P_TerrRsc_Rec.GROUP_ID,
1580 x_RESOURCE_TYPE => P_TerrRsc_Rec.RESOURCE_TYPE,
1581 x_ROLE => P_TerrRsc_Rec.ROLE,
1582 x_PRIMARY_CONTACT_FLAG => P_TerrRsc_Rec.PRIMARY_CONTACT_FLAG,
1583 X_START_DATE_ACTIVE => P_TerrRsc_Rec.START_DATE_ACTIVE,
1584 X_END_DATE_ACTIVE => P_TerrRsc_Rec.END_DATE_ACTIVE,
1585 X_FULL_ACCESS_FLAG => P_TerrRsc_Rec.FULL_ACCESS_FLAG,
1586 X_ORG_ID => P_TerrRsc_Rec.ORG_ID );
1587
1588 --dbms_output.put_line('After calling JTF_TERR_RSC_PKG.Insert_Row');
1589 -- Save the terr_usg_id and
1590 X_TerrRsc_Out_Rec.TERR_RSC_ID := l_TerrRsc_Id;
1591
1592 -- If successful then save the success status for the record
1593 X_TerrRsc_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
1594
1595 -- Debug Message
1596 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1597 THEN
1598 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1599 fnd_message.set_name ('PROC_NAME', l_api_name);
1600 FND_MSG_PUB.Add;
1601 END IF;
1602
1603 --dbms_output.put_line('Create_Terr_Resource REC: Exiting API');
1604 EXCEPTION
1605 WHEN FND_API.G_EXC_ERROR THEN
1606 --dbms_output.put_line('Create_Terr_Resource: FND_API.G_EXC_ERROR');
1607
1608 X_TerrRsc_Out_Rec.TERR_RSC_ID := NULL;
1609 X_TerrRsc_Out_Rec.return_status := x_return_status;
1610 x_return_status := FND_API.G_RET_STS_ERROR ;
1611
1612 FND_MSG_PUB.Count_And_Get
1613 ( P_count => x_msg_count,
1614 P_data => x_msg_data
1615 );
1616
1617 WHEN OTHERS THEN
1618 --dbms_output.put_line('Create_Terr_Resource REC: OTHERS - ' || SQLERRM);
1619 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1620 --
1621 X_TerrRsc_Out_Rec.TERR_RSC_ID := NULL;
1622 X_TerrRsc_Out_Rec.return_status := x_return_status;
1623 --
1624 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1625 THEN
1626 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others exception in Create_Terr_Resource ' || SQLERRM);
1627 END IF;
1628 --
1629 End Create_Terr_Resource;
1630
1631
1632 PROCEDURE Create_Terr_Resource
1633 (
1634 P_TerrRsc_Tbl IN TerrResource_tbl_type_wflex := G_MISS_TERRRESOURCE_TBL_WFLEX,
1635 p_Api_Version_Number IN NUMBER,
1636 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1637 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1638 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1639 x_Return_Status OUT NOCOPY VARCHAR2,
1640 x_Msg_Count OUT NOCOPY NUMBER,
1641 x_Msg_Data OUT NOCOPY VARCHAR2,
1642 X_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type
1643 )
1644 AS
1645 l_return_Status VARCHAR2(1);
1646
1647 l_api_name CONSTANT VARCHAR2(30) := 'Create_Resource_Access (Tbl)';
1648 l_TerrRsc_Tbl_Count NUMBER := P_TerrRsc_Tbl.Count;
1649 l_TerrRsc_out_Tbl_Count NUMBER;
1650 l_TerrRsc_Out_Tbl TerrResource_out_tbl_type;
1651 l_TerrRsc_Out_Rec TerrResource_out_Rec_type;
1652
1653 l_Counter NUMBER;
1654
1655 BEGIN
1656 --dbms_output.put_line('Create_Terr_Resource TBL: Entering API');
1657
1658 -- Debug Message
1659 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1660 THEN
1661 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1662 fnd_message.set_name ('PROC_NAME', l_api_name);
1663 FND_MSG_PUB.Add;
1664 END IF;
1665
1666 -- Initialize API return status to success
1667 x_return_status := FND_API.G_RET_STS_SUCCESS;
1668
1669 -- Call overloaded Create_Terr_Qualifier procedure
1670 --
1671 FOR l_Counter IN 1 .. l_TerrRsc_Tbl_Count LOOP
1672 --
1673 --dbms_output.put_line('Create_Terr_Resource TBL: Before Calling Create_Terr_Resource PVT');
1674
1675 Create_Terr_Resource(P_TerrRsc_Rec => P_TerrRsc_Tbl(l_counter),
1676 p_api_version_number => p_api_version_number,
1677 p_init_msg_list => p_init_msg_list,
1678 p_commit => p_commit,
1679 p_validation_level => p_validation_level,
1680 x_return_status => l_return_status,
1681 x_msg_count => x_msg_count,
1682 x_msg_data => x_msg_data,
1683 X_TerrRsc_Out_Rec => l_TerrRsc_Out_Rec);
1684 --
1685 --If there is a major error
1686 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1687 --dbms_output.put_line('Create_Terr_Resource TBL: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
1688
1689 -- Save the terr_usg_id and
1690 X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := NULL;
1691
1692 -- If save the ERROR status for the record
1693 X_TerrRsc_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1694 ELSE
1695 --dbms_output.put_line('Create_Terr_Resource TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
1696
1697 -- Save the terr_usg_id and
1698 X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := l_TerrRsc_Out_Rec.TERR_RSC_ID;
1699
1700 -- If successful then save the success status for the record
1701 X_TerrRsc_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_SUCCESS;
1702 END IF;
1703 --
1704 END LOOP;
1705
1706 --Get the API overall return status
1707 -- Initialize API return status to success
1708 x_return_status := FND_API.G_RET_STS_SUCCESS;
1709
1710 --Get number of records in the ouput table
1711 l_TerrRsc_Out_Tbl_Count := X_TerrRsc_Out_Tbl.Count;
1712
1713 FOR l_Counter IN 1 .. l_TerrRsc_Out_Tbl_Count LOOP
1714 If x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
1715 x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
1716 THEN
1717 X_return_status := FND_API.G_RET_STS_ERROR;
1718 END IF;
1719 END LOOP;
1720
1721 -- Debug Message
1722 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1723 THEN
1724 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1725 fnd_message.set_name ('PROC_NAME', l_api_name);
1726 FND_MSG_PUB.Add;
1727 END IF;
1728 --dbms_output.put_line('Create_Terr_Resource TBL: Exiting API');
1729 --
1730 End Create_Terr_Resource;
1731
1732
1733
1734 --
1735 --
1736 -- ***************************************************
1737 -- start of comments
1738 -- ***************************************************
1739 -- API name : Create_Terr_Resource
1740 -- Type : PRIVATE
1741 -- Function : To create Territories qualifier
1742 --
1743 -- Pre-reqs :
1744 -- Parameter Name Data Type Default
1745 -- P_TerrRsc_Tbl TerrResource_tbl_type
1746 --
1747 -- OUT :
1748 -- Parameter Name Data Type
1749 -- X_Return_Status VARCHAR2(1)
1750 -- X_TerrRsc_Out_Tbl TerrResource_out_tbl_type
1751 --
1752 -- Notes: This is a an overloaded procedure. This one
1753 -- will call the overloade procedure for records
1754 -- creation
1755 --
1756 --
1757 -- End of Comments
1758 --
1759
1760 PROCEDURE Create_Terr_Resource
1761 (
1762 P_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
1763 p_Api_Version_Number IN NUMBER,
1764 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1765 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1766 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1767 x_Return_Status OUT NOCOPY VARCHAR2,
1768 x_Msg_Count OUT NOCOPY NUMBER,
1769 x_Msg_Data OUT NOCOPY VARCHAR2,
1770 X_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type
1771 )
1772 AS
1773 l_return_Status VARCHAR2(1);
1774
1775 l_api_name CONSTANT VARCHAR2(30) := 'Create_Resource_Access (Tbl)';
1776 l_TerrRsc_Tbl_Count NUMBER := P_TerrRsc_Tbl.Count;
1777 l_TerrRsc_out_Tbl_Count NUMBER;
1778 l_TerrRsc_Out_Tbl TerrResource_out_tbl_type;
1779 l_TerrRsc_Out_Rec TerrResource_out_Rec_type;
1780
1781 l_Counter NUMBER;
1782
1783 BEGIN
1784 --dbms_output.put_line('Create_Terr_Resource TBL: Entering API');
1785
1786 -- Debug Message
1787 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1788 THEN
1789 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1790 fnd_message.set_name ('PROC_NAME', l_api_name);
1791 FND_MSG_PUB.Add;
1792 END IF;
1793
1794 -- Initialize API return status to success
1795 x_return_status := FND_API.G_RET_STS_SUCCESS;
1796
1797 -- Call overloaded Create_Terr_Qualifier procedure
1798 --
1799 FOR l_Counter IN 1 .. l_TerrRsc_Tbl_Count LOOP
1800 --
1801 --dbms_output.put_line('Create_Terr_Resource TBL: Before Calling Create_Terr_Resource PVT');
1802
1803 Create_Terr_Resource(P_TerrRsc_Rec => P_TerrRsc_Tbl(l_counter),
1804 p_api_version_number => p_api_version_number,
1805 p_init_msg_list => p_init_msg_list,
1806 p_commit => p_commit,
1807 p_validation_level => p_validation_level,
1808 x_return_status => l_return_status,
1809 x_msg_count => x_msg_count,
1810 x_msg_data => x_msg_data,
1811 X_TerrRsc_Out_Rec => l_TerrRsc_Out_Rec);
1812 --
1813 --If there is a major error
1814 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1815 --dbms_output.put_line('Create_Terr_Resource TBL: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
1816
1817 -- Save the terr_usg_id and
1818 X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := NULL;
1819
1820 -- If save the ERROR status for the record
1821 X_TerrRsc_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1822 ELSE
1823 --dbms_output.put_line('Create_Terr_Resource TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
1824
1825 -- Save the terr_usg_id and
1826 X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := l_TerrRsc_Out_Rec.TERR_RSC_ID;
1827
1828 -- If successful then save the success status for the record
1829 X_TerrRsc_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_SUCCESS;
1830 END IF;
1831 --
1832 END LOOP;
1833
1834 --Get the API overall return status
1835 -- Initialize API return status to success
1836 x_return_status := FND_API.G_RET_STS_SUCCESS;
1837
1838 --Get number of records in the ouput table
1839 l_TerrRsc_Out_Tbl_Count := X_TerrRsc_Out_Tbl.Count;
1840
1841 FOR l_Counter IN 1 .. l_TerrRsc_Out_Tbl_Count LOOP
1842 If x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
1843 x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
1844 THEN
1845 X_return_status := FND_API.G_RET_STS_ERROR;
1846 END IF;
1847 END LOOP;
1848
1849 -- Debug Message
1850 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1851 THEN
1852 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1853 fnd_message.set_name ('PROC_NAME', l_api_name);
1854 FND_MSG_PUB.Add;
1855 END IF;
1856 --dbms_output.put_line('Create_Terr_Resource TBL: Exiting API');
1857 --
1858 End Create_Terr_Resource;
1859
1860
1861
1862
1863 --
1864 -- ***************************************************
1865 -- start of comments
1866 -- ***************************************************
1867 -- API name : Create_Resource _Access
1868 -- Type : PUBLIC
1869 -- Function : To create Territories resource Access
1870 --
1871 --
1872 -- Pre-reqs :
1873 -- Parameters:
1874 -- IN :
1875 -- Required
1876 -- Parameter Name Data Type Default
1877 -- P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC
1878 --
1879 -- OUT :
1880 -- Parameter Name Data Type
1881 -- X_Return_Status VARCHAR2(1)
1882 -- X_TerrRsc_Access_Out_Rec TerrRsc_Access_out_rec_type
1883 --
1884 -- Notes: This is a an overloaded procedure
1885 --
1886 --
1887 -- End of Comments
1888 --
1889 PROCEDURE Create_Resource_Access
1890 (
1891 p_TerrRsc_Id NUMBER,
1892 P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC,
1893 p_Api_Version_Number IN NUMBER,
1894 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1895 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1896 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1897 x_Return_Status OUT NOCOPY VARCHAR2,
1898 x_Msg_Count OUT NOCOPY NUMBER,
1899 x_Msg_Data OUT NOCOPY VARCHAR2,
1900 X_TerrRsc_Access_Out_Rec OUT NOCOPY TerrRsc_Access_out_rec_type
1901 )
1902 AS
1903 l_rowid ROWID;
1904 l_api_name CONSTANT VARCHAR2(30) := 'Create_Resource_Access';
1905 l_terrRsc_Access_id NUMBER := P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID;
1906
1907 BEGIN
1908 --dbms_output.put_line('Create_Resource _Access REC: Entering API');
1909
1910 -- Debug Message
1911 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1912 THEN
1913 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1914 fnd_message.set_name ('PROC_NAME', l_api_name);
1915 FND_MSG_PUB.Add;
1916 END IF;
1917
1918 -- Initialize API return status to success
1919 x_return_status := FND_API.G_RET_STS_SUCCESS;
1920
1921
1922 IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE)
1923 THEN
1924 -- Debug message
1925 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1926 THEN
1927 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
1928 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Rsc_Access');
1929 FND_MSG_PUB.Add;
1930 END IF;
1931 --
1932 -- Invoke validation procedures
1933 Validate_Terr_Rsc_Access(p_init_msg_list => FND_API.G_FALSE,
1934 x_Return_Status => x_return_status,
1935 x_msg_count => x_msg_count,
1936 x_msg_data => x_msg_data,
1937 p_TerrRsc_Id => p_TerrRsc_Id,
1938 p_TerrRsc_Access_Rec => P_TerrRsc_Access_Rec);
1939
1940 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1941 RAISE FND_API.G_EXC_ERROR;
1942 END IF;
1943 --
1944 END IF;
1945
1946 -- jdochert 09/09
1947 -- check for Unique Key constraint violation
1948 validate_terr_rsc_access_UK(
1949 p_Terr_Rsc_Id => p_terrrsc_id,
1950 p_Access_Type => p_TerrRsc_access_rec.access_type,
1951 p_init_msg_list => FND_API.G_FALSE,
1952 x_Return_Status => x_return_status,
1953 x_msg_count => x_msg_count,
1954 x_msg_data => x_msg_data );
1955
1956 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1957 RAISE FND_API.G_EXC_ERROR;
1958 END IF;
1959
1960 -- Call insert Terr_Resource_Access table handler
1961 --
1962 /* Intialise to NULL if FND_API.G_MISS_NUM,
1963 ** otherwise used passed in value
1964 */
1965 IF (l_TerrRsc_Access_id = FND_API.G_MISS_NUM) THEN
1966 l_TerrRsc_Access_id := NULL;
1967 END IF;
1968
1969 --dbms_output.put_line('Create_Resource _Access REC: Calling JTF_TERR_RSC_ACCESS_PKG.Insert_Row');
1970 JTF_TERR_RSC_ACCESS_PKG.Insert_Row(x_Rowid => l_rowid,
1971 x_TERR_RSC_ACCESS_ID => l_terrRsc_Access_id,
1972 x_LAST_UPDATE_DATE => P_TerrRsc_Access_Rec.LAST_UPDATE_DATE,
1973 x_LAST_UPDATED_BY => P_TerrRsc_Access_Rec.LAST_UPDATED_BY,
1974 x_CREATION_DATE => P_TerrRsc_Access_Rec.CREATION_DATE,
1975 x_CREATED_BY => P_TerrRsc_Access_Rec.CREATED_BY,
1976 x_LAST_UPDATE_LOGIN => P_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN,
1977 x_TERR_RSC_ID => p_TerrRsc_Id,
1978 x_ACCESS_TYPE => P_TerrRsc_Access_Rec.ACCESS_TYPE,
1979 x_TRANS_ACCESS_CODE => P_TerrRsc_Access_Rec.TRANS_ACCESS_CODE,
1980 X_ORG_ID => P_TerrRsc_Access_Rec.ORG_ID );
1981
1982 -- Save the terr_usg_id and
1983 X_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID := l_terrRsc_Access_id;
1984
1985 -- If successful then save the success status for the record
1986 X_TerrRsc_Access_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
1987
1988 -- Debug Message
1989 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1990 THEN
1991 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1992 fnd_message.set_name ('PROC_NAME', l_api_name);
1993 FND_MSG_PUB.Add;
1994 END IF;
1995
1996 --dbms_output.put_line('Create_Resource _Access REC: Exiting API');
1997 EXCEPTION
1998 WHEN FND_API.G_EXC_ERROR THEN
1999 --dbms_output.put_line('Create_Resource_Access: FND_API.G_EXC_ERROR');
2000
2001 x_return_status := FND_API.G_RET_STS_ERROR ;
2002 X_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID := NULL;
2003 X_TerrRsc_Access_Out_Rec.return_status := x_return_status;
2004
2005 FND_MSG_PUB.Count_And_Get
2006 ( P_count => x_msg_count,
2007 P_data => x_msg_data
2008 );
2009
2010 WHEN OTHERS THEN
2011 --dbms_output.put_line('Others exception in Create_Resource_Access' || SQLERRM);
2012 --
2013 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2014 --
2015 X_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID := NULL;
2016 X_TerrRsc_Access_Out_Rec.return_status := x_return_status;
2017 --
2018 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2019 THEN
2020 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others exception in Create_Resource _Access');
2021 END IF;
2022 --
2023 End Create_Resource_Access;
2024
2025
2026
2027
2028 --
2029 --
2030 --
2031 -- ***************************************************
2032 -- start of comments
2033 -- ***************************************************
2034 -- API name : Create_Resource _Access
2035 -- Type : PUBLIC
2036 -- Function : To create Territories resource Access
2037 --
2038 --
2039 -- Pre-reqs :
2040 -- Parameters:
2041 -- IN :
2042 -- Required
2043 -- Parameter Name Data Type Default
2044 -- P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_TBL
2045 --
2046 -- OUT :
2047 -- Parameter Name Data Type
2048 -- X_Return_Status VARCHAR2(1)
2049 -- X_TerrRsc_Access_Out_Rec TerrRsc_Access_out_rec_type
2050 --
2051 -- Notes: This is a an overloaded procedure. This one
2052 -- will call the overloade procedure for records
2053 -- creation
2054 --
2055 -- End of Comments
2056 --
2057
2058 PROCEDURE Create_Resource_Access
2059 (
2060 p_TerrRsc_Id NUMBER,
2061 P_TerrRsc_Access_Tbl TerrRsc_Access_Tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
2062 p_Api_Version_Number IN NUMBER,
2063 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2064 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2065 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2066 x_Return_Status OUT NOCOPY VARCHAR2,
2067 x_Msg_Count OUT NOCOPY NUMBER,
2068 x_Msg_Data OUT NOCOPY VARCHAR2,
2069 X_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_Tbl_type
2070 )
2071 AS
2072 l_terr_value_id NUMBER;
2073
2074 l_api_name CONSTANT VARCHAR2(30) := 'Create_Resource_Access (Tbl)';
2075 l_return_Status VARCHAR2(1);
2076 l_TerrRsc_Access_Tbl_Count NUMBER := P_TerrRsc_Access_Tbl.Count;
2077
2078 l_TerrRscAcc_Out_Tbl_Count NUMBER;
2079 l_TerrRsc_Access_Out_Tbl TerrRsc_Access_out_Tbl_type;
2080 l_TerrRsc_Access_Out_Rec TerrRsc_Access_out_Rec_type;
2081
2082 l_Counter NUMBER := 0;
2083
2084 BEGIN
2085 --dbms_output.put_line('Create_Resource_Access TBL: Entering API - ' || to_char(l_TerrRsc_Access_Tbl_Count));
2086
2087 -- Debug Message
2088 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2089 THEN
2090 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2091 fnd_message.set_name ('PROC_NAME', l_api_name);
2092 FND_MSG_PUB.Add;
2093 END IF;
2094
2095
2096 -- Initialize API return status to success
2097 x_return_status := FND_API.G_RET_STS_SUCCESS;
2098
2099 -- -- Call overloaded Create_Terr_Qualifier procedure
2100 --
2101 FOR l_Counter IN 1 .. l_TerrRsc_Access_Tbl_Count LOOP
2102 --
2103 --dbms_output.put_line('Inside Create_Resource_Access - ' || to_char(P_TerrRsc_Access_Tbl(l_counter).QUALIFIER_TBL_INDEX) );
2104 Create_Resource_Access(P_TerrRsc_Id => p_TerrRsc_Id,
2105 P_TerrRsc_Access_Rec => P_TerrRsc_Access_Tbl(l_counter),
2106 p_api_version_number => p_api_version_number,
2107 p_init_msg_list => p_init_msg_list,
2108 p_commit => p_commit,
2109 p_validation_level => p_validation_level,
2110 x_return_status => l_return_status,
2111 x_msg_count => x_msg_count,
2112 x_msg_data => x_msg_data,
2113 x_TerrRsc_Access_Out_Rec => l_TerrRsc_Access_Out_Rec);
2114 --
2115 --If there is a major error
2116 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2117 --dbms_output.put_line('Create_Resource _Access REC: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
2118 -- Save the terr_usg_id and
2119 X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := NULL;
2120 -- If save the ERROR status for the record
2121 X_TerrRsc_Access_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2122 ELSE
2123 --dbms_output.put_line('Create_Resource _Access REC: l_return_status = FND_API.G_RET_STS_SUCCESS');
2124 -- Save the terr_usg_id and
2125 X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := l_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID;
2126
2127 -- If successful then save the success status for the record
2128 X_TerrRsc_Access_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_SUCCESS;
2129 END IF;
2130 --
2131 END LOOP;
2132
2133 --Get the API overall return status
2134 --Initialize API return status to success
2135 x_return_status := FND_API.G_RET_STS_SUCCESS;
2136
2137 --Get number of records in the ouput table
2138 l_TerrRscAcc_Out_Tbl_Count := X_TerrRsc_Access_Out_Tbl.Count;
2139
2140 FOR l_Counter IN 1 .. l_TerrRscAcc_Out_Tbl_Count LOOP
2141 If x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
2142 x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
2143 THEN
2144 X_return_status := FND_API.G_RET_STS_ERROR;
2145 END IF;
2146 END LOOP;
2147
2148 -- Debug Message
2149 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2150 THEN
2151 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2152 fnd_message.set_name ('PROC_NAME', l_api_name);
2153 FND_MSG_PUB.Add;
2154 END IF;
2155
2156 --
2157 End Create_Resource_Access;
2158
2159
2160
2161
2162 --
2163 -- *******************************************************
2164 -- Start of Comments
2165 -- *******************************************************
2166 -- API Name: Delete_TerrRsc_Access
2167 -- Type : PRIVATE
2168 -- Pre-Req :
2169 -- Parameters:
2170 -- IN
2171 -- Parameter Name Data Type Default
2172 -- P_Api_Version_Number IN NUMBER,
2173 -- P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
2174 -- P_Commit IN VARCHAR2 := FND_API.G_FALSE
2175 -- P_TerrRsc_Id IN NUMBER
2176 --
2177 -- Optional:
2178 --
2179 -- OUT:
2180 -- Parameter Name Data Type Default
2181 -- X_Return_Status VARCHAR2
2182 --
2183 -- Note:
2184 --
2185 -- End of Comments
2186 --
2187
2188 PROCEDURE Delete_TerrRsc_Access
2189 (
2190 P_Api_Version_Number IN NUMBER,
2191 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2192 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2193 P_TerrRsc_Access_Id IN NUMBER,
2194 X_Return_Status OUT NOCOPY VARCHAR2,
2195 X_Msg_Count OUT NOCOPY VARCHAR2,
2196 X_Msg_Data OUT NOCOPY VARCHAR2
2197 )
2198 AS
2199 l_row_count NUMBER;
2200 l_api_name CONSTANT VARCHAR2(30) := 'Delete_TerrRsc_Access';
2201 l_api_version_number CONSTANT NUMBER := 1.0;
2202 l_return_status VARCHAR2(1);
2203
2204 BEGIN
2205 -- Standard start of PAI savepoint
2206 SAVEPOINT DELETE_TERRRSC_ACCESS_PVT;
2207
2208 -- Standard call to check for call compatibility.
2209 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2210 p_api_version_number,
2211 l_api_name,
2212 G_PKG_NAME)
2213 THEN
2214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2215 END IF;
2216
2217 -- Initialize message list if p_init_msg_list is set to TRUE.
2218 IF FND_API.to_Boolean( p_init_msg_list )
2219 THEN
2220 FND_MSG_PUB.initialize;
2221 END IF;
2222
2223 -- Debug Message
2224 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2225 THEN
2226 FND_MESSAGE.Set_Name('JTF', 'Delete TerrRscAccess : Start');
2227 FND_MSG_PUB.Add;
2228 END IF;
2229
2230 --Initialize the return status to success
2231 x_return_status := FND_API.G_RET_STS_SUCCESS;
2232 --
2233 JTF_TERR_RSC_ACCESS_PKG.Delete_Row(x_TERR_RSC_ACCESS_ID => P_TerrRsc_Access_Id );
2234 --
2235 --Prepare message name
2236 FND_MESSAGE.SET_NAME('JTF','TERR_RSCACCESSES_DELETED');
2237
2238 IF SQL%FOUND THEN
2239 x_return_status := FND_API.G_RET_STS_SUCCESS;
2240 l_row_count := SQL%ROWCOUNT;
2241 END IF;
2242
2243 --Prepare message token
2244 FND_MESSAGE.SET_NAME('ITEMS_DELETED', l_row_count);
2245 --Add message to API message list
2246 FND_MSG_PUB.ADD();
2247
2248 -- Debug Message
2249 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2250 THEN
2251 FND_MESSAGE.Set_Name('JTF', 'Delete TerrRscAccess: End');
2252 FND_MSG_PUB.Add;
2253 END IF;
2254
2255
2256 FND_MSG_PUB.Count_And_Get
2257 ( p_count => x_msg_count,
2258 p_data => x_msg_data
2259 );
2260
2261 -- Standard check for p_commit
2262 IF FND_API.to_Boolean( p_commit )
2263 THEN
2264 COMMIT WORK;
2265 END IF;
2266 --
2267 EXCEPTION
2268 WHEN NO_DATA_FOUND THEN
2269 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2270 l_row_count := 0;
2271 --Prepare message token
2272 FND_MESSAGE.SET_NAME('ITEMS_DELETED', l_row_count);
2273 --Add message to API message list
2274 FND_MSG_PUB.ADD();
2275 --
2276 FND_MSG_PUB.Count_And_Get
2277 ( p_count => x_msg_count,
2278 p_data => x_msg_data
2279 );
2280
2281 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2282 ROLLBACK TO DELETE_TERRRSC_ACCESS_PVT;
2283 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2284 FND_MSG_PUB.Count_And_Get
2285 ( p_count => x_msg_count,
2286 p_data => x_msg_data
2287 );
2288
2289 WHEN OTHERS THEN
2290 ROLLBACK TO DELETE_TERRRSC_ACCESS_PVT;
2291 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2292 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2293 THEN
2294 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Delete error inside Delete_TerrRsc_Access');
2295 END IF;
2296 END Delete_TerrRsc_Access;
2297
2298
2299
2300
2301 --
2302 -- *******************************************************
2303 -- Start of Comments
2304 -- *******************************************************
2305 -- API Name: Delete_TerrResource
2306 -- Type : PRIVATE
2307 -- Pre-Req :
2308 -- Parameters:
2309 -- IN
2310 -- Required:
2311 -- Parameter Name Data Type Default
2312 -- P_Api_Version_Number IN NUMBER,
2313 -- P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
2314 -- P_Commit IN VARCHAR2 := FND_API.G_FALSE
2315 -- P_TerrRsc_Id IN NUMBER
2316 --
2317 -- Optional:
2318 -- OUT:
2319 -- Parameter Name Data Type Default
2320 -- X_Return_Status VARCHAR2
2321 --
2322 -- Note:
2323 --
2324 -- End of Comments
2325 --
2326
2327 PROCEDURE Delete_TerrResource
2328 (
2329 P_Api_Version_Number IN NUMBER,
2330 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2331 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2332 P_TerrRsc_Id IN NUMBER,
2333 X_Return_Status OUT NOCOPY VARCHAR2,
2334 X_Msg_Count OUT NOCOPY VARCHAR2,
2335 X_Msg_Data OUT NOCOPY VARCHAR2
2336 )
2337 AS
2338 l_row_count NUMBER;
2339 l_api_name CONSTANT VARCHAR2(30) := 'Delete_TerrResource';
2340 l_api_version_number CONSTANT NUMBER := 1.0;
2341 l_return_status VARCHAR2(1);
2342
2343 BEGIN
2344 -- Standard start of PAI savepoint
2345 SAVEPOINT DELETE_TERRRSC_PVT;
2346
2347 -- Standard call to check for call compatibility.
2348 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2349 p_api_version_number,
2350 l_api_name,
2351 G_PKG_NAME)
2352 THEN
2353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2354 END IF;
2355 -- Initialize message list if p_init_msg_list is set to TRUE.
2356 IF FND_API.to_Boolean( p_init_msg_list )
2357 THEN
2358 FND_MSG_PUB.initialize;
2359 END IF;
2360
2361 -- Debug Message
2362 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2363 THEN
2364 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2365 fnd_message.set_name ('PROC_NAME', l_api_name);
2366 FND_MSG_PUB.Add;
2367 END IF;
2368
2369 --Initialize the return status to success
2370 x_return_status := FND_API.G_RET_STS_SUCCESS;
2371 --
2372 JTF_TERR_RSC_PKG.Delete_Row(x_TERR_RSC_ID => P_TerrRsc_Id);
2373 --
2374 --Prepare message name
2375 FND_MESSAGE.SET_NAME('JTF','TERR_RESOURCE_DELETED');
2376
2377 IF SQL%FOUND THEN
2378 x_return_status := FND_API.G_RET_STS_SUCCESS;
2379 l_row_count := SQL%ROWCOUNT;
2380 END IF;
2381
2382 --Prepare message token
2383 FND_MESSAGE.SET_TOKEN('ITEMS_DELETED', l_row_count);
2384
2385 --Add message to API message list
2386 FND_MSG_PUB.ADD();
2387
2388 -- Debug Message
2389 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2390 THEN
2391 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2392 fnd_message.set_name ('PROC_NAME', l_api_name);
2393 FND_MSG_PUB.Add;
2394 END IF;
2395
2396
2397 FND_MSG_PUB.Count_And_Get
2398 ( p_count => x_msg_count,
2399 p_data => x_msg_data
2400 );
2401
2402 -- Standard check for p_commit
2403 IF FND_API.to_Boolean( p_commit )
2404 THEN
2405 COMMIT WORK;
2406 END IF;
2407 --
2408 EXCEPTION
2409 WHEN NO_DATA_FOUND THEN
2410 ROLLBACK TO DELETE_TERRRSC_PVT;
2411 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2412 l_row_count := 0;
2413
2414 --Prepare message token
2415 FND_MESSAGE.SET_NAME('ITEMS_DELETED', l_row_count);
2416
2417 --Add message to API message list
2418 FND_MSG_PUB.ADD();
2419 --
2420 FND_MSG_PUB.Count_And_Get
2421 ( p_count => x_msg_count,
2422 p_data => x_msg_data
2423 );
2424
2425 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2426 ROLLBACK TO DELETE_TERRRSC_PVT;
2427 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2428 FND_MSG_PUB.Count_And_Get
2429 ( p_count => x_msg_count,
2430 p_data => x_msg_data
2431 );
2432
2433 WHEN OTHERS THEN
2434 ROLLBACK TO DELETE_TERRRSC_PVT;
2435 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2436 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2437 THEN
2438 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Delete error inside Delete_Territory_Resource');
2439 END IF;
2440 --
2441 End Delete_TerrResource;
2442 --
2443
2444
2445
2446
2447 --
2448 -- ***************************************************
2449 -- start of comments
2450 -- ***************************************************
2451 -- API name : Update_Terr_Resource
2452 -- Type : PRIVATE
2453 -- Function : To update Territories resource
2454 --
2455 --
2456 -- Pre-reqs :
2457 -- Parameters:
2458 -- IN :
2459 -- Required
2460 -- Parameter Name Data Type Default
2461 -- P_TerrRsc_Rec TerrResource_tbl_type
2462 --
2463 -- OUT NOCOPY :
2464 -- Parameter Name Data Type
2465 -- X_Return_Status VARCHAR2(1)
2466 -- X_TerrRsc_Out_Rec TerrResource_out_tbl_type
2467 --
2468 -- Notes: This is a an overloaded procedure
2469 --
2470 --
2471 -- End of Comments
2472 --
2473
2474 PROCEDURE Update_Terr_Resource (
2475 P_TerrRsc_Rec IN TerrResource_Rec_type,
2476 p_Api_Version_Number IN NUMBER,
2477 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2478 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2479 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2480 x_Return_Status OUT NOCOPY VARCHAR2,
2481 x_Msg_Count OUT NOCOPY NUMBER,
2482 x_Msg_Data OUT NOCOPY VARCHAR2,
2483 x_TerrRsc_Out_Rec OUT NOCOPY TerrResource_out_Rec_type) AS
2484
2485 Cursor C_GetTerrResource(l_TerrRsc_id Number) IS
2486 Select Rowid, TERR_RSC_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2487 LAST_UPDATE_LOGIN, TERR_ID, RESOURCE_ID, RESOURCE_TYPE, ROLE, PRIMARY_CONTACT_FLAG, ORG_ID
2488 From jtf_terr_rsc_ALL
2489 Where TERR_RSC_ID = l_TerrRsc_id
2490 FOR Update NOWAIT;
2491
2492 --Local variable declaration
2493 l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Resource';
2494 l_rowid VARCHAR2(50);
2495 l_api_version_number CONSTANT NUMBER := 1.0;
2496 l_return_status VARCHAR2(1);
2497 l_ref_TerrRsc_Rec TerrResource_Rec_type;
2498
2499 BEGIN
2500
2501 -- Initialize API return status to SUCCESS
2502 x_return_status := FND_API.G_RET_STS_SUCCESS;
2503
2504 --Vai: Bug # 3520561
2505 Check_for_duplicate2_updates(P_TerrRsc_Rec => P_TerrRsc_Rec,
2506 x_Return_Status => l_return_status,
2507 x_msg_count => x_msg_count,
2508 x_Msg_Data => x_Msg_Data);
2509
2510 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2511 RAISE FND_API.G_EXC_ERROR;
2512 END IF;
2513
2514 --Validate the incomming record
2515 IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE) THEN
2516 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2517 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
2518 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Rsc');
2519 FND_MSG_PUB.Add;
2520 END IF;
2521
2522 -- Invoke validation procedures
2523 Validate_Terr_Rsc(p_init_msg_list => FND_API.G_FALSE,
2524 x_Return_Status => x_return_status,
2525 x_msg_count => x_msg_count,
2526 x_msg_data => x_msg_data,
2527 p_TerrRsc_Rec => P_TerrRsc_Rec);
2528
2529 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2530 RAISE FND_API.G_EXC_ERROR;
2531 END IF;
2532 END IF;
2533
2534 OPEN C_GetTerrResource( P_TerrRsc_Rec.TERR_RSC_ID);
2535 Fetch C_GetTerrResource into l_Rowid, l_ref_TerrRsc_Rec.TERR_RSC_ID, l_ref_TerrRsc_Rec.LAST_UPDATE_DATE,
2536 l_ref_TerrRsc_Rec.LAST_UPDATED_BY, l_ref_TerrRsc_Rec.CREATION_DATE, l_ref_TerrRsc_Rec.CREATED_BY,
2537 l_ref_TerrRsc_Rec.LAST_UPDATE_LOGIN, l_ref_TerrRsc_Rec.TERR_ID, l_ref_TerrRsc_Rec.RESOURCE_ID,
2538 l_ref_TerrRsc_Rec.RESOURCE_TYPE, l_ref_TerrRsc_Rec.ROLE, l_ref_TerrRsc_Rec.PRIMARY_CONTACT_FLAG,
2539 l_ref_TerrRsc_Rec.ORG_ID;
2540
2541 If (C_GetTerrResource%NOTFOUND) Then
2542 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2543 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
2544 FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
2545 FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_TerrRsc_Rec.TERR_RSC_ID));
2546 FND_MSG_PUB.Add;
2547 END IF;
2548 raise FND_API.G_EXC_ERROR;
2549 End if;
2550 CLOSE C_GetTerrResource;
2551
2552 JTF_TERR_RSC_PKG.Update_Row(x_Rowid => l_rowid,
2553 x_TERR_RSC_ID => P_TerrRsc_Rec.Terr_Rsc_Id,
2554 x_LAST_UPDATE_DATE => P_TerrRsc_Rec.LAST_UPDATE_DATE,
2555 x_LAST_UPDATED_BY => P_TerrRsc_Rec.LAST_UPDATED_BY ,
2556 x_CREATION_DATE => P_TerrRsc_Rec.CREATION_DATE,
2557 x_CREATED_BY => P_TerrRsc_Rec.CREATED_BY,
2558 x_LAST_UPDATE_LOGIN => P_TerrRsc_Rec.LAST_UPDATE_LOGIN ,
2559 x_TERR_ID => P_TerrRsc_Rec.TERR_ID,
2560 x_RESOURCE_ID => P_TerrRsc_Rec.RESOURCE_ID,
2561 x_GROUP_ID => P_TerrRsc_Rec.GROUP_ID,
2562 x_RESOURCE_TYPE => P_TerrRsc_Rec.RESOURCE_TYPE,
2563 x_ROLE => P_TerrRsc_Rec.ROLE,
2564 x_PRIMARY_CONTACT_FLAG => P_TerrRsc_Rec.PRIMARY_CONTACT_FLAG,
2565 X_START_DATE_ACTIVE => P_TerrRsc_Rec.START_DATE_ACTIVE,
2566 X_END_DATE_ACTIVE => P_TerrRsc_Rec.END_DATE_ACTIVE,
2567 X_FULL_ACCESS_FLAG => P_TerrRsc_Rec.FULL_ACCESS_FLAG,
2568 X_ORG_ID => P_TerrRsc_Rec.ORG_ID );
2569
2570 -- Save the terr_usg_id and
2571 X_TerrRsc_Out_Rec.TERR_RSC_ID := P_TerrRsc_Rec.Terr_Rsc_Id;
2572
2573 -- If successful then save the success status for the record
2574 X_TerrRsc_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
2575
2576 EXCEPTION
2577 WHEN FND_API.G_EXC_ERROR THEN
2578 x_return_status := FND_API.G_RET_STS_ERROR ;
2579 FND_MSG_PUB.Count_And_Get(P_count => x_msg_count,
2580 P_data => x_msg_data);
2581
2582 WHEN OTHERS THEN
2583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2584 x_TerrRsc_Out_Rec.TERR_RSC_ID := NULL;
2585 x_TerrRsc_Out_Rec.return_status := x_return_status;
2586 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2587 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others exception in Update_Territory_Resources');
2588 END IF;
2589
2590 End Update_Terr_Resource;
2591
2592
2593
2594
2595 --
2596 --
2597 -- ***************************************************
2598 -- start of comments
2599 -- ***************************************************
2600 -- API name : Update_Terr_Resource
2601 -- Type : PRIVATE
2602 -- Function : To create Territories qualifier
2603 --
2604 -- Pre-reqs :
2605 -- Parameter Name Data Type Default
2606 -- P_TerrRsc_Tbl TerrResource_tbl_type
2607 --
2608 -- OUT :
2609 -- Parameter Name Data Type
2610 -- X_Return_Status VARCHAR2(1)
2611 -- X_TerrRsc_Out_Tbl TerrResource_out_tbl_type
2612 --
2613 -- Notes: This is a an overloaded procedure. This one
2614 -- will call the overloade procedure for records
2615 -- creation
2616 --
2617 --
2618 -- End of Comments
2619 --
2620
2621 PROCEDURE Update_Terr_Resource
2622 (
2623 P_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
2624 p_Api_Version_Number IN NUMBER,
2625 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2626 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2627 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2628 x_Return_Status OUT NOCOPY VARCHAR2,
2629 x_Msg_Count OUT NOCOPY NUMBER,
2630 x_Msg_Data OUT NOCOPY VARCHAR2,
2631 X_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type
2632 )
2633 AS
2634 l_return_Status VARCHAR2(1);
2635 l_TerrRsc_Tbl_Count NUMBER := P_TerrRsc_Tbl.Count;
2636 l_TerrRsc_out_Tbl_Count NUMBER;
2637 l_TerrRsc_Out_Tbl TerrResource_out_tbl_type;
2638 l_TerrRsc_Out_Rec TerrResource_out_Rec_type;
2639
2640 l_Counter NUMBER;
2641
2642 BEGIN
2643 --dbms_output.put_line('Update_Terr_Resource TBL: Entering API');
2644
2645 -- Initialize API return status to success
2646 x_return_status := FND_API.G_RET_STS_SUCCESS;
2647
2648 -- Call overloaded Create_Terr_Qualifier procedure
2649 --
2650 FOR l_Counter IN 1 .. l_TerrRsc_Tbl_Count LOOP
2651 --
2652 --dbms_output.put_line('Update_Terr_Resource TBL: Before Calling Create_Terr_Resource PVT');
2653
2654 Update_Terr_Resource(P_TerrRsc_Rec => P_TerrRsc_Tbl(l_counter),
2655 p_api_version_number => p_api_version_number,
2656 p_init_msg_list => p_init_msg_list,
2657 p_commit => p_commit,
2658 p_validation_level => p_validation_level,
2659 x_return_status => l_return_status,
2660 x_msg_count => x_msg_count,
2661 x_msg_data => x_msg_data,
2662 X_TerrRsc_Out_Rec => l_TerrRsc_Out_Rec);
2663 --
2664 --If there is a major error
2665 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2666 --dbms_output.put_line('Update_Terr_Resource TBL: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
2667 -- Save the terr_usg_id and
2668 X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := NULL;
2669
2670 -- If save the ERROR status for the record
2671 X_TerrRsc_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2672 ELSE
2673 --dbms_output.put_line('Update_Terr_Resource TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
2674 -- Save the terr_usg_id and
2675 X_TerrRsc_Out_Tbl(l_counter).TERR_RSC_ID := l_TerrRsc_Out_Rec.TERR_RSC_ID;
2676 -- If successful then save the success status for the record
2677 X_TerrRsc_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_SUCCESS;
2678 END IF;
2679 --
2680 END LOOP;
2681
2682 --Get the API overall return status
2683 -- Initialize API return status to success
2684 x_return_status := FND_API.G_RET_STS_SUCCESS;
2685
2686 --Get number of records in the ouput table
2687 l_TerrRsc_Out_Tbl_Count := X_TerrRsc_Out_Tbl.Count;
2688
2689 FOR l_Counter IN 1 .. l_TerrRsc_Out_Tbl_Count LOOP
2690 If x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
2691 x_TerrRsc_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
2692 THEN
2693 X_return_status := FND_API.G_RET_STS_ERROR;
2694 END IF;
2695 END LOOP;
2696 --dbms_output.put_line('Update_Terr_Resource TBL: Exiting API');
2697 --
2698 End Update_Terr_Resource;
2699
2700
2701
2702
2703 --
2704 -- ***************************************************
2705 -- start of comments
2706 -- ***************************************************
2707 -- API name : Update_Resource_Access
2708 -- Type : PUBLIC
2709 -- Function : To Update Territories resource Access
2710 --
2711 --
2712 -- Pre-reqs :
2713 -- Parameters:
2714 -- IN :
2715 -- Required
2716 -- Parameter Name Data Type Default
2717 -- P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC
2718 --
2719 -- OUT :
2720 -- Parameter Name Data Type
2721 -- X_Return_Status VARCHAR2(1)
2722 -- X_TerrRsc_Access_Out_Rec TerrRsc_Access_out_rec_type
2723 --
2724 -- Notes: This is a an overloaded procedure
2725 --
2726 --
2727 -- End of Comments
2728 --
2729
2730 PROCEDURE Update_Resource_Access
2731 (
2732 P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC,
2733 p_Api_Version_Number IN NUMBER,
2734 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2735 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2736 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2737 x_Return_Status OUT NOCOPY VARCHAR2,
2738 x_Msg_Count OUT NOCOPY NUMBER,
2739 x_Msg_Data OUT NOCOPY VARCHAR2,
2740 X_TerrRsc_Access_Out_Rec OUT NOCOPY TerrRsc_Access_out_rec_type
2741 )
2742 AS
2743 Cursor C_GetTerrResAccess(l_TerrRsc_Access_id Number) IS
2744 Select Rowid,
2745 TERR_RSC_ACCESS_ID,
2746 LAST_UPDATE_DATE,
2747 LAST_UPDATED_BY,
2748 CREATION_DATE,
2749 CREATED_BY,
2750 LAST_UPDATE_LOGIN,
2751 TERR_RSC_ID,
2752 ACCESS_TYPE,
2753 TRANS_ACCESS_CODE,
2754 ORG_ID
2755 From jtf_terr_rsc_access_ALL
2756 Where TERR_RSC_ACCESS_ID = l_TerrRsc_Access_id
2757 FOR Update NOWAIT;
2758
2759 --Local variable declaration
2760 l_api_name CONSTANT VARCHAR2(30) := 'Update_Resource_Access';
2761 l_rowid VARCHAR2(50);
2762 l_api_version_number CONSTANT NUMBER := 1.0;
2763 l_return_status VARCHAR2(1);
2764 l_ref_TerrRsc_Access_Rec TerrRsc_Access_rec_type;
2765
2766 BEGIN
2767 --dbms_output.put_line('Update_Resource_Access REC: Entering API');
2768
2769 -- Initialize API return status to SUCCESS
2770 x_return_status := FND_API.G_RET_STS_SUCCESS;
2771
2772 IF ( P_validation_level > FND_API.G_VALID_LEVEL_NONE)
2773 THEN
2774 -- Debug message
2775 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2776 THEN
2777 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_VALIDATE_MSG');
2778 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Terr_Rsc_Access');
2779 FND_MSG_PUB.Add;
2780 END IF;
2781 --
2782 -- Invoke validation procedures
2783 Validate_Terr_Rsc_Access(p_init_msg_list => FND_API.G_FALSE,
2784 x_Return_Status => l_return_status,
2785 x_msg_count => x_msg_count,
2786 x_msg_data => x_msg_data,
2787 p_TerrRsc_Id => P_TerrRsc_Access_Rec.terr_rsc_id,
2788 p_TerrRsc_Access_Rec => P_TerrRsc_Access_Rec);
2789
2790 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2791 x_return_status := l_return_status;
2792 RAISE FND_API.G_EXC_ERROR;
2793 END IF;
2794 --
2795 END IF;
2796
2797 OPEN C_GetTerrResAccess( P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID);
2798 Fetch C_GetTerrResAccess into
2799 l_Rowid,
2800 l_ref_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID,
2801 l_ref_TerrRsc_Access_Rec.LAST_UPDATE_DATE,
2802 l_ref_TerrRsc_Access_Rec. LAST_UPDATED_BY,
2803 l_ref_TerrRsc_Access_Rec.CREATION_DATE,
2804 l_ref_TerrRsc_Access_Rec.CREATED_BY,
2805 l_ref_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN,
2806 l_ref_TerrRsc_Access_Rec.TERR_RSC_ID,
2807 l_ref_TerrRsc_Access_Rec.ACCESS_TYPE,
2808 l_ref_TerrRsc_Access_Rec.TRANS_ACCESS_CODE,
2809 l_ref_TerrRsc_Access_Rec.ORG_ID;
2810
2811 If ( C_GetTerrResAccess%NOTFOUND) Then
2812 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2813 THEN
2814 --dbms_output.put_line('Update_Resource_Access REC: DATA-NOT-FOUND');
2815 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
2816 FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC_ACCESS');
2817 FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID));
2818 FND_MSG_PUB.Add;
2819 END IF;
2820 raise FND_API.G_EXC_ERROR;
2821 End if;
2822 CLOSE C_GetTerrResAccess;
2823
2824 -- jdochert 09/09
2825 -- check for Unique Key constraint violation
2826 validate_terr_rsc_access_UK(
2827 p_Terr_Rsc_Id => p_TerrRsc_access_rec.terr_rsc_id,
2828 p_Access_Type => p_TerrRsc_access_rec.access_type,
2829 p_init_msg_list => FND_API.G_FALSE,
2830 x_Return_Status => x_return_status,
2831 x_msg_count => x_msg_count,
2832 x_msg_data => x_msg_data );
2833
2834 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2835 RAISE FND_API.G_EXC_ERROR;
2836 END IF;
2837
2838 --
2839 --dbms_output.put_line('Update_Resource_Access REC: Calling JTF_TERR_RSC_ACCESS_PKG.Insert_Row');
2840 JTF_TERR_RSC_ACCESS_PKG.Update_Row(x_Rowid => l_rowid,
2841 x_TERR_RSC_ACCESS_ID => P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID,
2842 x_LAST_UPDATE_DATE => P_TerrRsc_Access_Rec.LAST_UPDATE_DATE,
2843 x_LAST_UPDATED_BY => P_TerrRsc_Access_Rec.LAST_UPDATED_BY,
2844 x_CREATION_DATE => P_TerrRsc_Access_Rec.CREATION_DATE,
2845 x_CREATED_BY => P_TerrRsc_Access_Rec.CREATED_BY,
2846 x_LAST_UPDATE_LOGIN => P_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN,
2847 x_TERR_RSC_ID => P_TerrRsc_Access_Rec.TERR_RSC_ID,
2848 x_ACCESS_TYPE => P_TerrRsc_Access_Rec.ACCESS_TYPE,
2849 x_TRANS_ACCESS_CODE => P_TerrRsc_Access_Rec.TRANS_ACCESS_CODE,
2850 X_ORG_ID => P_TerrRsc_Access_Rec.ORG_ID );
2851
2852 -- Save the terr_usg_id and
2853 X_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID := P_TerrRsc_Access_Rec.TERR_RSC_ACCESS_ID;
2854 -- If successful then save the success status for the record
2855 X_TerrRsc_Access_Out_Rec.return_status := FND_API.G_RET_STS_SUCCESS;
2856
2857 --dbms_output.put_line('Update_Resource_Access REC: Exiting API');
2858 EXCEPTION
2859 WHEN FND_API.G_EXC_ERROR THEN
2860 --dbms_output.put_line('Update_Resource_Access: FND_API.G_EXC_ERROR');
2861 x_return_status := FND_API.G_RET_STS_ERROR ;
2862 FND_MSG_PUB.Count_And_Get
2863 ( P_count => x_msg_count,
2864 P_data => x_msg_data
2865 );
2866
2867 WHEN OTHERS THEN
2868 --dbms_output.put_line('Others exception in Update_Territory_Qualifiers');
2869 --
2870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2871 --
2872 X_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID := NULL;
2873 X_TerrRsc_Access_Out_Rec.return_status := x_return_status;
2874 --
2875 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2876 THEN
2877 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others exception in Update_Resource _Access');
2878 END IF;
2879 --
2880 End Update_Resource_Access;
2881
2882
2883
2884
2885 --
2886 --
2887 -- ***************************************************
2888 -- start of comments
2889 -- ***************************************************
2890 -- API name : Update_Resource _Access
2891 -- Type : PUBLIC
2892 -- Function : To create Territories resource Access
2893 --
2894 --
2895 -- Pre-reqs :
2896 -- Parameters:
2897 -- IN :
2898 -- Required
2899 -- Parameter Name Data Type Default
2900 -- P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_TBL
2901 --
2902 -- OUT :
2903 -- Parameter Name Data Type
2904 -- X_Return_Status VARCHAR2(1)
2905 -- X_TerrRsc_Access_Out_Rec TerrRsc_Access_out_rec_type
2906 --
2907 -- Notes: This is a an overloaded procedure. This one
2908 -- will call the overloade procedure for records
2909 -- creation
2910 --
2911 -- End of Comments
2912 --
2913
2914 PROCEDURE Update_Resource_Access
2915 (
2916 P_TerrRsc_Access_Tbl TerrRsc_Access_Tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
2917 p_Api_Version_Number IN NUMBER,
2918 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2919 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2920 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2921 x_Return_Status OUT NOCOPY VARCHAR2,
2922 x_Msg_Count OUT NOCOPY NUMBER,
2923 x_Msg_Data OUT NOCOPY VARCHAR2,
2924 X_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_Tbl_type
2925 )
2926 AS
2927 l_terr_value_id NUMBER;
2928
2929
2930 l_return_Status VARCHAR2(1);
2931 l_TerrRsc_Access_Tbl_Count NUMBER := P_TerrRsc_Access_Tbl.Count;
2932
2933 l_TerrRscAcc_Out_Tbl_Count NUMBER;
2934 l_TerrRsc_Access_Out_Tbl TerrRsc_Access_out_Tbl_type;
2935 l_TerrRsc_Access_Out_Rec TerrRsc_Access_out_Rec_type;
2936
2937 l_Counter NUMBER;
2938
2939 BEGIN
2940 --dbms_output.put_line('Update_Resource _Access REC: Entering API');
2941
2942 -- Initialize API return status to success
2943 x_return_status := FND_API.G_RET_STS_SUCCESS;
2944
2945 -- -- Call overloaded Update_Terr_Qualifier procedure
2946 --
2947 FOR l_Counter IN 1 .. l_TerrRsc_Access_Tbl_Count LOOP
2948 --
2949 --dbms_output.put_line('Inside Update_Resource_Access - ' || to_char(P_TerrRsc_Access_Tbl(l_counter).QUALIFIER_TBL_INDEX) );
2950 Update_Resource_Access(P_TerrRsc_Access_Rec => P_TerrRsc_Access_Tbl(l_counter),
2951 p_api_version_number => p_api_version_number,
2952 p_init_msg_list => p_init_msg_list,
2953 p_commit => p_commit,
2954 p_validation_level => p_validation_level,
2955 x_return_status => l_return_status,
2956 x_msg_count => x_msg_count,
2957 x_msg_data => x_msg_data,
2958 x_TerrRsc_Access_Out_Rec => l_TerrRsc_Access_Out_Rec);
2959 --
2960 --If there is a major error
2961 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2962 --dbms_output.put_line('Update_Resource _Access REC: l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR');
2963 -- Save the terr_usg_id and
2964 X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := NULL;
2965 -- If save the ERROR status for the record
2966 X_TerrRsc_Access_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2967 ELSE
2968 --dbms_output.put_line('Update_Resource _Access REC: l_return_status = FND_API.G_RET_STS_SUCCESS');
2969 -- Save the terr_usg_id and
2970 X_TerrRsc_Access_Out_Tbl(l_counter).TERR_RSC_ACCESS_ID := l_TerrRsc_Access_Out_Rec.TERR_RSC_ACCESS_ID;
2971 -- If successful then save the success status for the record
2972 X_TerrRsc_Access_Out_Tbl(l_counter).return_status := FND_API.G_RET_STS_SUCCESS;
2973 END IF;
2974 --
2975 END LOOP;
2976
2977 --Get the API overall return status
2978 --Initialize API return status to success
2979 x_return_status := FND_API.G_RET_STS_SUCCESS;
2980
2981 --Get number of records in the ouput table
2982 l_TerrRscAcc_Out_Tbl_Count := X_TerrRsc_Access_Out_Tbl.Count;
2983
2984 FOR l_Counter IN 1 .. l_TerrRscAcc_Out_Tbl_Count LOOP
2985 If x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
2986 x_TerrRsc_Access_Out_Tbl(l_Counter).return_status = FND_API.G_RET_STS_ERROR
2987 THEN
2988 X_return_status := FND_API.G_RET_STS_ERROR;
2989 END IF;
2990 END LOOP;
2991 --
2992 End Update_Resource_Access;
2993 --
2994
2995
2996 PROCEDURE Validate_Foreign_Keys
2997 (
2998 p_TerrRsc_Tbl IN TerrResource_tbl_type_wflex,
2999 x_Return_Status OUT NOCOPY VARCHAR2,
3000 x_msg_count OUT NOCOPY NUMBER,
3001 x_msg_data OUT NOCOPY VARCHAR2
3002 )
3003 AS
3004 l_index NUMBER := 0;
3005 l_Res_Counter NUMBER := 0;
3006 l_Temp VARCHAR2(1);
3007 l_Terr_Id NUMBER;
3008
3009 BEGIN
3010 --
3011 --dbms_output.put_line('Inside Validate_Foreign_Keys');
3012
3013 -- Initialize API return status to success
3014 x_return_status := FND_API.G_RET_STS_SUCCESS;
3015
3016 For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
3017 --
3018 l_Terr_Id := p_TerrRsc_Tbl(l_res_counter).Terr_Id;
3019 --
3020 --dbms_output.put_line('Inside the for loop. Before validating Terr_Id');
3021 Select 'X' into l_temp
3022 from JTF_TERR
3023 where TERR_ID = p_TerrRsc_Tbl(l_res_counter).Terr_Id;
3024 --
3025 --
3026 End LOOP;
3027 --
3028 EXCEPTION
3029 --
3030 WHEN NO_DATA_FOUND THEN
3031 --dbms_output.put_line('Validate_Foreign_Keys: NO_DATA_FOUND');
3032 x_return_status := FND_API.G_RET_STS_ERROR ;
3033 fnd_message.set_name('JTF', 'JTF_TERR_INVALID_TERRITORY');
3034 fnd_message.Set_Token('TERR_ID', to_char(l_Terr_Id) );
3035 FND_MSG_PUB.ADD;
3036 FND_MSG_PUB.Count_And_Get
3037 ( P_count => x_msg_count,
3038 P_data => x_msg_data
3039 );
3040
3041 WHEN OTHERS THEN
3042 --dbms_output.put_line('Validate_Foreign_Keys: OTHERS - ' || SQLERRM);
3043 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3044 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3045 THEN
3046 FND_MSG_PUB.Add_Exc_Msg
3047 ( G_PKG_NAME,
3048 'Others Exception in Validate_Foreign_Keys ' || SQLERRM
3049 );
3050 END IF;
3051 --
3052 END Validate_Foreign_Keys;
3053
3054
3055
3056 -- ***************************************************
3057 -- start of comments
3058 -- ***************************************************
3059 -- API name : Validate_Foreign_Keys
3060 -- Type : PUBLIC
3061 -- Function : Validate Territory Resources
3062 --
3063 -- Pre-reqs :
3064 -- Parameters:
3065 -- IN :
3066 -- Required
3067 -- Parameter Name Data Type Default
3068 -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
3069 --
3070 -- Optional
3071 -- Parameter Name Data Type Default
3072 --
3073 -- OUT :
3074 -- Parameter Name Data Type
3075 -- x_Return_Status VARCHAR2(1)
3076 -- Notes:
3077 --
3078 --
3079 -- End of Comments
3080 --
3081
3082 PROCEDURE Validate_Foreign_Keys
3083 (
3084 p_TerrRsc_Tbl IN TerrResource_tbl_type,
3085 x_Return_Status OUT NOCOPY VARCHAR2,
3086 x_msg_count OUT NOCOPY NUMBER,
3087 x_msg_data OUT NOCOPY VARCHAR2
3088 )
3089 AS
3090 l_index NUMBER := 0;
3091 l_Res_Counter NUMBER := 0;
3092 l_Temp VARCHAR2(1);
3093 l_Terr_Id NUMBER;
3094
3095 BEGIN
3096 --
3097 --dbms_output.put_line('Inside Validate_Foreign_Keys');
3098
3099 -- Initialize API return status to success
3100 x_return_status := FND_API.G_RET_STS_SUCCESS;
3101
3102 For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
3103 --
3104 l_Terr_Id := p_TerrRsc_Tbl(l_res_counter).Terr_Id;
3105 --
3106 --dbms_output.put_line('Inside the for loop. Before validating Terr_Id');
3107 Select 'X' into l_temp
3108 from JTF_TERR
3109 where TERR_ID = p_TerrRsc_Tbl(l_res_counter).Terr_Id;
3110 --
3111 --
3112 End LOOP;
3113 --
3114 EXCEPTION
3115 --
3116 WHEN NO_DATA_FOUND THEN
3117 --dbms_output.put_line('Validate_Foreign_Keys: NO_DATA_FOUND');
3118 x_return_status := FND_API.G_RET_STS_ERROR ;
3119 fnd_message.set_name('JTF', 'JTF_TERR_INVALID_TERRITORY');
3120 fnd_message.Set_Token('TERR_ID', to_char(l_Terr_Id) );
3121 FND_MSG_PUB.ADD;
3122 FND_MSG_PUB.Count_And_Get
3123 ( P_count => x_msg_count,
3124 P_data => x_msg_data
3125 );
3126
3127 WHEN OTHERS THEN
3128 --dbms_output.put_line('Validate_Foreign_Keys: OTHERS - ' || SQLERRM);
3129 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3130 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3131 THEN
3132 FND_MSG_PUB.Add_Exc_Msg
3133 ( G_PKG_NAME,
3134 'Others Exception in Validate_Foreign_Keys ' || SQLERRM
3135 );
3136 END IF;
3137 --
3138 END Validate_Foreign_Keys;
3139
3140
3141 PROCEDURE Validate_Primary_Flag
3142 (
3143 p_TerrRsc_Tbl IN TerrResource_tbl_type_wflex,
3144 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type,
3145 x_msg_count OUT NOCOPY NUMBER,
3146 x_msg_data OUT NOCOPY VARCHAR2,
3147 x_Return_Status OUT NOCOPY VARCHAR2
3148 )
3149 AS
3150 l_Primary_Count NUMBER := 0;
3151 l_Res_Counter NUMBER := 0;
3152 l_Res_Access_Counter NUMBER := 0;
3153
3154 BEGIN
3155 --
3156 --Initialize API return status to success
3157 x_return_status := FND_API.G_RET_STS_SUCCESS;
3158
3159 For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
3160 --
3161 If p_TerrRsc_Tbl(l_Res_Counter).PRIMARY_CONTACT_FLAG = 'Y' Then
3162 l_Primary_Count := l_Primary_Count + 1;
3163 End If;
3164 --
3165 End LOOP;
3166
3167 -- Cannot have more than one Primary flag
3168 If l_Primary_Count > 1 Then
3169 --
3170 fnd_message.set_name('JTF', 'JTF_TERR_MULTIPLE_PRIMARY_FLAG');
3171 FND_MSG_PUB.ADD;
3172 raise FND_API.G_EXC_ERROR;
3173 --
3174 End If;
3175 --
3176 EXCEPTION
3177 --
3178 WHEN FND_API.G_EXC_ERROR THEN
3179 --dbms_output.put_line('Validate_Primary_Flag: FND_API.G_EXC_ERROR');
3180 x_return_status := FND_API.G_RET_STS_ERROR ;
3181 FND_MSG_PUB.Count_And_Get
3182 ( P_count => x_msg_count,
3183 P_data => x_msg_data
3184 );
3185
3186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3187 --dbms_output.put_line('Validate_Primary_Flag: FND_API.G_EXC_UNEXPECTED_ERROR');
3188 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3189 FND_MSG_PUB.Count_And_Get
3190 ( P_count => x_msg_count,
3191 P_data => x_msg_data
3192 );
3193
3194 WHEN OTHERS THEN
3195 --dbms_output.put_line('Validate_Primary_Flag: OTHERS - ' || SQLERRM);
3196 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3197 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3198 THEN
3199 FND_MSG_PUB.Add_Exc_Msg
3200 ( G_PKG_NAME,
3201 'Others Exception in Validate_Primary_Flag ' || SQLERRM
3202 );
3203 END IF;
3204 --
3205 END Validate_Primary_Flag;
3206
3207
3208
3209 -- ***************************************************
3210 -- start of comments
3211 -- ***************************************************
3212 -- API name : Validate_Resorce_Object
3213 -- Type : PUBLIC
3214 -- Function : Validate Territory Resources
3215 --
3216 -- Pre-reqs :
3217 -- Parameters:
3218 -- IN :
3219 -- Required
3220 -- Parameter Name Data Type Default
3221 -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
3222 -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
3223 --
3224 -- Optional
3225 -- Parameter Name Data Type Default
3226 --
3227 -- OUT :
3228 -- Parameter Name Data Type
3229 -- x_Return_Status VARCHAR2(1)
3230 -- Notes:
3231 --
3232 --
3233 -- End of Comments
3234 --
3235
3236 PROCEDURE Validate_Primary_Flag
3237 (
3238 p_TerrRsc_Tbl IN TerrResource_tbl_type,
3239 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type,
3240 x_msg_count OUT NOCOPY NUMBER,
3241 x_msg_data OUT NOCOPY VARCHAR2,
3242 x_Return_Status OUT NOCOPY VARCHAR2
3243 )
3244 AS
3245 l_Primary_Count NUMBER := 0;
3246 l_Res_Counter NUMBER := 0;
3247 l_Res_Access_Counter NUMBER := 0;
3248
3249 BEGIN
3250 --
3251 --Initialize API return status to success
3252 x_return_status := FND_API.G_RET_STS_SUCCESS;
3253
3254 For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
3255 --
3256 If p_TerrRsc_Tbl(l_Res_Counter).PRIMARY_CONTACT_FLAG = 'Y' Then
3257 l_Primary_Count := l_Primary_Count + 1;
3258 End If;
3259 --
3260 End LOOP;
3261
3262 -- Cannot have more than one Primary flag
3263 If l_Primary_Count > 1 Then
3264 --
3265 fnd_message.set_name('JTF', 'JTF_TERR_MULTIPLE_PRIMARY_FLAG');
3266 FND_MSG_PUB.ADD;
3267 raise FND_API.G_EXC_ERROR;
3268 --
3269 End If;
3270 --
3271 EXCEPTION
3272 --
3273 WHEN FND_API.G_EXC_ERROR THEN
3274 --dbms_output.put_line('Validate_Primary_Flag: FND_API.G_EXC_ERROR');
3275 x_return_status := FND_API.G_RET_STS_ERROR ;
3276 FND_MSG_PUB.Count_And_Get
3277 ( P_count => x_msg_count,
3278 P_data => x_msg_data
3279 );
3280
3281 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3282 --dbms_output.put_line('Validate_Primary_Flag: FND_API.G_EXC_UNEXPECTED_ERROR');
3283 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3284 FND_MSG_PUB.Count_And_Get
3285 ( P_count => x_msg_count,
3286 P_data => x_msg_data
3287 );
3288
3289 WHEN OTHERS THEN
3290 --dbms_output.put_line('Validate_Primary_Flag: OTHERS - ' || SQLERRM);
3291 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3292 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3293 THEN
3294 FND_MSG_PUB.Add_Exc_Msg
3295 ( G_PKG_NAME,
3296 'Others Exception in Validate_Primary_Flag ' || SQLERRM
3297 );
3298 END IF;
3299 --
3300 END Validate_Primary_Flag;
3301
3302
3303 -- ***************************************************
3304 -- start of comments
3305 -- ***************************************************
3306 -- API name : Validate_TerrResource_Data
3307 -- Type : PUBLIC
3308 -- Function : Validate Territory Resources
3309 --
3310 -- Pre-reqs :
3311 -- Parameters:
3312 -- IN :
3313 -- Required
3314 -- Parameter Name Data Type Default
3315 -- p_TerrRsc_Tbl TerrResource_tbl_type_wflex := G_MISS_TERRRESOURCE_TBL_WFLEX
3316 -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
3317 --
3318 -- Optional
3319 -- Parameter Name Data Type Default
3320 --
3321 -- OUT :
3322 -- Parameter Name Data Type
3323 -- x_Return_Status VARCHAR2(1)
3324 -- Notes:
3325 --
3326 --
3327 -- End of Comments
3328 --
3329
3330 PROCEDURE Validate_TerrResource_Data
3331 (
3332 p_TerrRsc_Tbl IN TerrResource_tbl_type_wflex,
3333 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type,
3334 x_msg_count OUT NOCOPY NUMBER,
3335 x_msg_data OUT NOCOPY VARCHAR2,
3336 x_Return_Status OUT NOCOPY VARCHAR2
3337 )
3338 AS
3339 l_Return_Status VARCHAR2(01);
3340
3341 BEGIN
3342 --
3343 --dbms_output.put_line('Inside Validate_TerrResource_Data');
3344
3345 -- Initialize API return status to success
3346 x_return_status := FND_API.G_RET_STS_SUCCESS;
3347
3348 --ARPATEL: bug#2849410
3349 Check_for_duplicate (p_TerrRsc_Tbl => p_TerrRsc_Tbl,
3350 x_Return_Status => l_return_status,
3351 x_msg_count => x_msg_count,
3352 x_Msg_Data => x_Msg_Data);
3353
3354 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3355 x_return_status := l_return_status;
3356 END IF;
3357
3358 -- If the territory Usage records is missing
3359 If (p_TerrRsc_Tbl.count = 0 ) Then
3360 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3361 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERRRES_REC');
3362 FND_MSG_PUB.ADD;
3363 END IF;
3364 x_return_status := FND_API.G_RET_STS_ERROR;
3365 raise FND_API.G_EXC_ERROR;
3366 END IF;
3367
3368 -- Initialize API return status to success
3369 x_return_status := FND_API.G_RET_STS_SUCCESS;
3370
3371
3372 --dbms_output.put_line('Before Calling Validate_Primary_Flag');
3373 Validate_Primary_Flag(p_TerrRsc_Tbl => p_TerrRsc_Tbl ,
3374 p_TerrRsc_Access_Tbl => p_TerrRsc_Access_Tbl,
3375 x_Return_Status => l_Return_Status,
3376 x_msg_count => x_msg_count,
3377 x_Msg_Data => x_Msg_Data);
3378
3379 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3380 x_return_status := l_return_status;
3381 END IF;
3382
3383 --dbms_output.put_line('Before Calling Validate_Foreign_Keys');
3384 Validate_Foreign_Keys(p_TerrRsc_Tbl => p_TerrRsc_Tbl,
3385 x_Return_Status => l_return_status,
3386 x_msg_count => x_msg_count,
3387 x_Msg_Data => x_Msg_Data);
3388
3389 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3390 x_return_status := l_return_status;
3391 END IF;
3392 --
3393 EXCEPTION
3394 --
3395 WHEN FND_API.G_EXC_ERROR THEN
3396 --dbms_output.put_line('Validate_TerrResource_Data: FND_API.G_EXC_ERROR');
3397 x_return_status := FND_API.G_RET_STS_ERROR ;
3398 FND_MSG_PUB.Count_And_Get
3399 ( P_count => x_msg_count,
3400 P_data => x_msg_data
3401 );
3402
3403 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3404 --dbms_output.put_line('Validate_TerrResource_Data: FND_API.G_EXC_UNEXPECTED_ERROR');
3405 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3406 FND_MSG_PUB.Count_And_Get
3407 ( P_count => x_msg_count,
3408 P_data => x_msg_data
3409 );
3410
3411 WHEN OTHERS THEN
3412 --dbms_output.put_line('Validate_TerrResource_Data: OTHERS - ' || SQLERRM);
3413 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3414 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3415 THEN
3416 FND_MSG_PUB.Add_Exc_Msg
3417 ( G_PKG_NAME,
3418 'Others Exception in Validate_TerrResource_Data ' || SQLERRM
3419 );
3420 END IF;
3421
3422 --
3423 END Validate_TerrResource_Data;
3424
3425
3426
3427 -- ***************************************************
3428 -- start of comments
3429 -- ***************************************************
3430 -- API name : Validate_TerrResource_Data
3431 -- Type : PUBLIC
3432 -- Function : Validate Territory Resources
3433 --
3434 -- Pre-reqs :
3435 -- Parameters:
3436 -- IN :
3437 -- Required
3438 -- Parameter Name Data Type Default
3439 -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
3440 -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
3441 --
3442 -- Optional
3443 -- Parameter Name Data Type Default
3444 --
3445 -- OUT :
3446 -- Parameter Name Data Type
3447 -- x_Return_Status VARCHAR2(1)
3448 -- Notes:
3449 --
3450 --
3451 -- End of Comments
3452 --
3453
3454 PROCEDURE Validate_TerrResource_Data
3455 (
3456 p_TerrRsc_Tbl IN TerrResource_tbl_type,
3457 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type,
3458 x_msg_count OUT NOCOPY NUMBER,
3459 x_msg_data OUT NOCOPY VARCHAR2,
3460 x_Return_Status OUT NOCOPY VARCHAR2
3461 )
3462 AS
3463 l_Return_Status VARCHAR2(01);
3464
3465 BEGIN
3466 --
3467 --dbms_output.put_line('Inside Validate_TerrResource_Data');
3468
3469 -- Initialize API return status to success
3470 x_return_status := FND_API.G_RET_STS_SUCCESS;
3471
3472 --ARPATEL: bug#2849410
3473 Check_for_duplicate (p_TerrRsc_Tbl => p_TerrRsc_Tbl,
3474 x_Return_Status => l_return_status,
3475 x_msg_count => x_msg_count,
3476 x_Msg_Data => x_Msg_Data);
3477
3478 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3479 x_return_status := l_return_status;
3480 END IF;
3481
3482 -- If the territory Usage records is missing
3483 If (p_TerrRsc_Tbl.count = 0 ) Then
3484 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3485 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_TERRRES_REC');
3486 FND_MSG_PUB.ADD;
3487 END IF;
3488 x_return_status := FND_API.G_RET_STS_ERROR;
3489 raise FND_API.G_EXC_ERROR;
3490 END IF;
3491
3492 -- Initialize API return status to success
3493 x_return_status := FND_API.G_RET_STS_SUCCESS;
3494
3495
3496 --dbms_output.put_line('Before Calling Validate_Primary_Flag');
3497 Validate_Primary_Flag(p_TerrRsc_Tbl => p_TerrRsc_Tbl ,
3498 p_TerrRsc_Access_Tbl => p_TerrRsc_Access_Tbl,
3499 x_Return_Status => l_Return_Status,
3500 x_msg_count => x_msg_count,
3501 x_Msg_Data => x_Msg_Data);
3502
3503 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3504 x_return_status := l_return_status;
3505 END IF;
3506
3507 --dbms_output.put_line('Before Calling Validate_Foreign_Keys');
3508 Validate_Foreign_Keys(p_TerrRsc_Tbl => p_TerrRsc_Tbl,
3509 x_Return_Status => l_return_status,
3510 x_msg_count => x_msg_count,
3511 x_Msg_Data => x_Msg_Data);
3512
3513 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3514 x_return_status := l_return_status;
3515 END IF;
3516 --
3517 EXCEPTION
3518 --
3519 WHEN FND_API.G_EXC_ERROR THEN
3520 --dbms_output.put_line('Validate_TerrResource_Data: FND_API.G_EXC_ERROR');
3521 x_return_status := FND_API.G_RET_STS_ERROR ;
3522 FND_MSG_PUB.Count_And_Get
3523 ( P_count => x_msg_count,
3524 P_data => x_msg_data
3525 );
3526
3527 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3528 --dbms_output.put_line('Validate_TerrResource_Data: FND_API.G_EXC_UNEXPECTED_ERROR');
3529 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3530 FND_MSG_PUB.Count_And_Get
3531 ( P_count => x_msg_count,
3532 P_data => x_msg_data
3533 );
3534
3535 WHEN OTHERS THEN
3536 --dbms_output.put_line('Validate_TerrResource_Data: OTHERS - ' || SQLERRM);
3537 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3538 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3539 THEN
3540 FND_MSG_PUB.Add_Exc_Msg
3541 ( G_PKG_NAME,
3542 'Others Exception in Validate_TerrResource_Data ' || SQLERRM
3543 );
3544 END IF;
3545
3546 --
3547 END Validate_TerrResource_Data;
3548
3549
3550
3551 PROCEDURE Validate_Terr_Rsc
3552 (
3553 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3554 x_Return_Status OUT NOCOPY VARCHAR2,
3555 x_msg_count OUT NOCOPY NUMBER,
3556 x_msg_data OUT NOCOPY VARCHAR2,
3557 p_TerrRsc_Rec IN TerrResource_Rec_type_wflex
3558 )
3559 AS
3560 l_Validate_id NUMBER;
3561 l_dummy NUMBER;
3562
3563 BEGIN
3564 --dbms_output.put_line('Inside Validate_Terr_Rsc: Entering API');
3565
3566 -- Initialize the status to success
3567 x_return_status := FND_API.G_RET_STS_SUCCESS;
3568
3569 -- Validate the territory Id
3570 l_Validate_id := p_TerrRsc_Rec.Terr_Id;
3571 If l_Validate_id IS NOT NULL Then
3572 -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
3573 If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR') <> FND_API.G_TRUE Then
3574 --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3575 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3576 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3577 FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3578 FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
3579 FND_MSG_PUB.ADD;
3580 END IF;
3581 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3582 End If;
3583 Else
3584 -- Invalid Territory Id specified
3585 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3586 FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3587 FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
3588 FND_MSG_PUB.ADD;
3589 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3590 End If;
3591
3592 --Check last update login
3593 If ( p_TerrRsc_Rec.RESOURCE_TYPE is NULL OR
3594 p_TerrRsc_Rec.RESOURCE_TYPE = FND_API.G_MISS_CHAR ) THEN
3595 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3596 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3597 FND_MESSAGE.Set_Token('COL_NAME', 'RESOURCE_TYPE' );
3598 FND_MSG_PUB.ADD;
3599 END IF;
3600 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3601 End If;
3602
3603 --Check created by
3604 IF ( p_TerrRsc_Rec.CREATED_BY is NULL OR
3605 p_TerrRsc_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
3606 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3607 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3608 FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
3609 FND_MSG_PUB.ADD;
3610 END IF;
3611 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3612 End If;
3613
3614 --Check creation date
3615 If ( p_TerrRsc_Rec.CREATION_DATE is NULL OR
3616 p_TerrRsc_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
3617 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3618 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3619 FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
3620 FND_MSG_PUB.ADD;
3621 END IF;
3622 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3623 End If;
3624
3625 -- Validate last updated by
3626 IF ( p_TerrRsc_Rec.LAST_UPDATED_BY is NULL OR
3627 p_TerrRsc_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
3628 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3629 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3630 FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
3631 FND_MSG_PUB.ADD;
3632 END IF;
3633 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3634 End If;
3635
3636 -- Check last update date
3637 If ( p_TerrRsc_Rec.LAST_UPDATE_DATE IS NULL OR
3638 p_TerrRsc_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
3639 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3640 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3641 FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
3642 FND_MSG_PUB.ADD;
3643 END IF;
3644 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3645 End If;
3646
3647 --Check last update login
3648 If ( p_TerrRsc_Rec.LAST_UPDATE_LOGIN is NULL OR
3649 p_TerrRsc_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
3650 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3651 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3652 FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
3653 FND_MSG_PUB.ADD;
3654 END IF;
3655 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3656 End If;
3657
3658 --
3659 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
3660 p_data => x_msg_data);
3661 EXCEPTION
3662 --
3663 WHEN FND_API.G_EXC_ERROR THEN
3664 --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_ERROR');
3665 x_return_status := FND_API.G_RET_STS_ERROR ;
3666 FND_MSG_PUB.Count_And_Get
3667 ( P_count => x_msg_count,
3668 P_data => x_msg_data
3669 );
3670
3671 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3672 --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR');
3673 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3674 FND_MSG_PUB.Count_And_Get
3675 ( P_count => x_msg_count,
3676 P_data => x_msg_data
3677 );
3678
3679 WHEN OTHERS THEN
3680 --dbms_output.put_line('Validate_Terr_Qtype_Usage: OTHERS - ' || SQLERRM);
3681 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3682 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3683 THEN
3684 FND_MSG_PUB.Add_Exc_Msg
3685 ( G_PKG_NAME,
3686 'Others Exception in Validate_Terr_Rsc ' || SQLERRM
3687 );
3688 END IF;
3689 --
3690 END Validate_Terr_Rsc;
3691
3692
3693 ---------------------------------------------------------------------
3694 -- Validate Territory Resource
3695 ---------------------------------------------------------------------
3696 -- Columns Validated
3697 -- Make sure a Resource and Resource_Type is specified
3698 -- Make sure the Territory Id is valid
3699 ---------------------------------------------------------------------
3700
3701 PROCEDURE Validate_Terr_Rsc
3702 (
3703 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3704 x_Return_Status OUT NOCOPY VARCHAR2,
3705 x_msg_count OUT NOCOPY NUMBER,
3706 x_msg_data OUT NOCOPY VARCHAR2,
3707 p_TerrRsc_Rec IN TerrResource_Rec_type
3708 )
3709 AS
3710 l_Validate_id NUMBER;
3711 l_dummy NUMBER;
3712
3713 BEGIN
3714 --dbms_output.put_line('Inside Validate_Terr_Rsc: Entering API');
3715
3716 -- Initialize the status to success
3717 x_return_status := FND_API.G_RET_STS_SUCCESS;
3718
3719 -- Validate the territory Id
3720 l_Validate_id := p_TerrRsc_Rec.Terr_Id;
3721 If l_Validate_id IS NOT NULL Then
3722 -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
3723 If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_ID', 'JTF_TERR') <> FND_API.G_TRUE Then
3724 --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3725 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3726 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3727 FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3728 FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
3729 FND_MSG_PUB.ADD;
3730 END IF;
3731 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3732 End If;
3733 Else
3734 -- Invalid Territory Id specified
3735 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3736 FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR');
3737 FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_ID');
3738 FND_MSG_PUB.ADD;
3739 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3740 End If;
3741
3742 --Check last update login
3743 If ( p_TerrRsc_Rec.RESOURCE_TYPE is NULL OR
3744 p_TerrRsc_Rec.RESOURCE_TYPE = FND_API.G_MISS_CHAR ) THEN
3745 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3746 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3747 FND_MESSAGE.Set_Token('COL_NAME', 'RESOURCE_TYPE' );
3748 FND_MSG_PUB.ADD;
3749 END IF;
3750 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3751 End If;
3752
3753 --Check created by
3754 IF ( p_TerrRsc_Rec.CREATED_BY is NULL OR
3755 p_TerrRsc_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
3756 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3757 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3758 FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
3759 FND_MSG_PUB.ADD;
3760 END IF;
3761 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3762 End If;
3763
3764 --Check creation date
3765 If ( p_TerrRsc_Rec.CREATION_DATE is NULL OR
3766 p_TerrRsc_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
3767 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3768 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3769 FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
3770 FND_MSG_PUB.ADD;
3771 END IF;
3772 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3773 End If;
3774
3775 -- Validate last updated by
3776 IF ( p_TerrRsc_Rec.LAST_UPDATED_BY is NULL OR
3777 p_TerrRsc_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
3778 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3779 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3780 FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
3781 FND_MSG_PUB.ADD;
3782 END IF;
3783 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3784 End If;
3785
3786 -- Check last update date
3787 If ( p_TerrRsc_Rec.LAST_UPDATE_DATE IS NULL OR
3788 p_TerrRsc_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
3789 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3790 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3791 FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
3792 FND_MSG_PUB.ADD;
3793 END IF;
3794 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3795 End If;
3796
3797 --Check last update login
3798 If ( p_TerrRsc_Rec.LAST_UPDATE_LOGIN is NULL OR
3799 p_TerrRsc_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
3800 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3801 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3802 FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
3803 FND_MSG_PUB.ADD;
3804 END IF;
3805 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3806 End If;
3807
3808 --
3809 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
3810 p_data => x_msg_data);
3811 EXCEPTION
3812 --
3813 WHEN FND_API.G_EXC_ERROR THEN
3814 --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_ERROR');
3815 x_return_status := FND_API.G_RET_STS_ERROR ;
3816 FND_MSG_PUB.Count_And_Get
3817 ( P_count => x_msg_count,
3818 P_data => x_msg_data
3819 );
3820
3821 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3822 --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR');
3823 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3824 FND_MSG_PUB.Count_And_Get
3825 ( P_count => x_msg_count,
3826 P_data => x_msg_data
3827 );
3828
3829 WHEN OTHERS THEN
3830 --dbms_output.put_line('Validate_Terr_Qtype_Usage: OTHERS - ' || SQLERRM);
3831 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3832 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3833 THEN
3834 FND_MSG_PUB.Add_Exc_Msg
3835 ( G_PKG_NAME,
3836 'Others Exception in Validate_Terr_Rsc ' || SQLERRM
3837 );
3838 END IF;
3839 --
3840 END Validate_Terr_Rsc;
3841
3842
3843
3844
3845 ---------------------------------------------------------------------
3846 -- Validate Territory Resource Access record
3847 ---------------------------------------------------------------------
3848 -- Columns Validated
3849 -- Make sure a TERR_RSC_ID is valid
3850 -- Make sure the ACCESS_TYPE is valid
3851 ---------------------------------------------------------------------
3852
3853 PROCEDURE Validate_Terr_Rsc_Access
3854 (
3855 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3856 x_Return_Status OUT NOCOPY VARCHAR2,
3857 x_msg_count OUT NOCOPY NUMBER,
3858 x_msg_data OUT NOCOPY VARCHAR2,
3859 p_TerrRsc_Id IN NUMBER,
3860 p_TerrRsc_Access_Rec IN TerrRsc_Access_Rec_type
3861 )
3862 AS
3863 l_Temp VARCHAR2(01);
3864 l_Validate_id NUMBER;
3865 l_dummy NUMBER;
3866
3867 BEGIN
3868 --dbms_output.put_line('Inside Validate_Terr_Rsc_Access: TERR_RSC_ID ' || to_char(p_TerrRsc_Access_Rec.TERR_RSC_ID) );
3869
3870 -- Initialize the status to success
3871 x_return_status := FND_API.G_RET_STS_SUCCESS;
3872
3873 -- Validate the territory Id
3874 l_Validate_id := p_TerrRsc_Id;
3875 If l_Validate_id IS NOT NULL Then
3876 -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
3877 If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_RSC_ID', 'JTF_TERR_RSC') <> FND_API.G_TRUE Then
3878 --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3879 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3880 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3881 FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
3882 FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_RSC_ID');
3883 FND_MSG_PUB.ADD;
3884 END IF;
3885 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3886 End If;
3887 Else
3888 -- Invalid Territory Id specified
3889 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3890 FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
3891 FND_MESSAGE.Set_Token('COLUMN_NAME', 'TERR_RSC_ID');
3892 FND_MSG_PUB.ADD;
3893 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3894 End If;
3895
3896 -- Validate the access type
3897 BEGIN
3898 select 'x' into l_Temp
3899 from JTF_QUAL_TYPES jqt
3900 Where jqt.NAME = p_TerrRsc_Access_Rec.ACCESS_TYPE;
3901
3902 EXCEPTION
3903 WHEN NO_DATA_FOUND THEN
3904 -- Invalid Territory Id specified
3905 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3906 FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_QUAL_TYPES');
3907 FND_MESSAGE.Set_Token('COLUMN_NAME', 'ACCESS_TYPE');
3908 FND_MSG_PUB.ADD;
3909 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3910 END;
3911
3912 --Check created by
3913 IF ( p_TerrRsc_Access_Rec.CREATED_BY is NULL OR
3914 p_TerrRsc_Access_Rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
3915 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3916 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3917 FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
3918 FND_MSG_PUB.ADD;
3919 END IF;
3920 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3921 End If;
3922
3923 --Check creation date
3924 If ( p_TerrRsc_Access_Rec.CREATION_DATE is NULL OR
3925 p_TerrRsc_Access_Rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
3926 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3927 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3928 FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
3929 FND_MSG_PUB.ADD;
3930 END IF;
3931 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3932 End If;
3933
3934 -- Validate last updated by
3935 IF ( p_TerrRsc_Access_Rec.LAST_UPDATED_BY is NULL OR
3936 p_TerrRsc_Access_Rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) Then
3937 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3938 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3939 FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
3940 FND_MSG_PUB.ADD;
3941 END IF;
3942 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3943 End If;
3944
3945 -- Check last update date
3946 If ( p_TerrRsc_Access_Rec.LAST_UPDATE_DATE IS NULL OR
3947 p_TerrRsc_Access_Rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
3948 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3949 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3950 FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
3951 FND_MSG_PUB.ADD;
3952 END IF;
3953 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3954 End If;
3955
3956 --Check last update login
3957 If ( p_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN is NULL OR
3958 p_TerrRsc_Access_Rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
3959 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3960 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
3961 FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
3962 FND_MSG_PUB.ADD;
3963 END IF;
3964 x_Return_Status := FND_API.G_RET_STS_ERROR ;
3965 End If;
3966
3967 --
3968 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
3969 p_data => x_msg_data);
3970 EXCEPTION
3971 --
3972 WHEN FND_API.G_EXC_ERROR THEN
3973 --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_ERROR');
3974 x_return_status := FND_API.G_RET_STS_ERROR ;
3975 FND_MSG_PUB.Count_And_Get
3976 ( P_count => x_msg_count,
3977 P_data => x_msg_data
3978 );
3979
3980 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3981 --dbms_output.put_line('Validate_Terr_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR');
3982 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3983 FND_MSG_PUB.Count_And_Get
3984 ( P_count => x_msg_count,
3985 P_data => x_msg_data
3986 );
3987
3988 WHEN OTHERS THEN
3989 --dbms_output.put_line('Validate_Terr_Qtype_Usage: OTHERS - ' || SQLERRM);
3990 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3991 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3992 THEN
3993 FND_MSG_PUB.Add_Exc_Msg
3994 ( G_PKG_NAME,
3995 'Others Exception in Validate_Terr_Rsc ' || SQLERRM
3996 );
3997 END IF;
3998 --
3999 END Validate_Terr_Rsc_Access;
4000
4001
4002
4003 -- This function will build the rule expression
4004
4005 FUNCTION BuildRuleExpression
4006 (
4007 p_Terr_Id NUMBER,
4008 p_qual_type_id NUMBER
4009 ) return VARCHAR2
4010 AS
4011 v_Terr_Qual_Id number;
4012
4013 CURSOR c_Terr_ResQual IS
4014 SELECT JTQ.TERR_QUAL_ID
4015 FROM jtf_seeded_qual_usgs_v jsquv, JTF_TERR_QUAL JTQ
4016 WHERE JTQ.terr_id = p_Terr_id and
4017 JTQ.qual_usg_id = jsquv.qual_usg_id and
4018 jsquv.qual_type_id = -1001 and
4019 jsquv.qual_type_id in ( select related_id
4020 from JTF_QUAL_TYPE_DENORM_V
4021 where qual_type_id = p_qual_type_id);
4022
4023 CURSOR c_Values IS
4024 Select JTV.COMPARISON_OPERATOR, JTV.INCLUDE_FLAG, jsquv.QUAL_COL1,
4025 jsquv.QUAL_COL1_TABLE, jsquv.QUAL_COL1_ALIAS, jsquv.PRIM_INT_CDE_COL_ALIAS,
4026 jsquv.SEC_INT_CDE_COL_ALIAS, jtv.low_value_char,jtv.high_value_char,
4027 jtv.low_value_number, jtv.high_value_number,
4028 jtv.INTEREST_TYPE_ID, jtv.PRIMARY_INTEREST_CODE_ID,
4029 jtv.SECONDARY_INTEREST_CODE_ID, jsquv.DISPLAY_TYPE, jsquv.CONVERT_TO_ID_FLAG,
4030 jtv.ID_USED_FLAG, jtv.CURRENCY_CODE, jtv.LOW_VALUE_CHAR_ID
4031 from jtf_seeded_qual_usgs_v jsquv, jtf_terr_values jtv, jtf_terr_qual jtq
4032 where jtv.terr_qual_id = v_Terr_Qual_Id and
4033 jtv.terr_qual_id = jtq.terr_qual_id and
4034 jtq.qual_usg_id = jsquv.qual_usg_id;
4035
4036 Type t_Pkgname IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
4037 l_Expr_Tbl t_Pkgname;
4038 l_Expr_Pointer NUMBER := 0;
4039 l_Record JTF_TERR_ENGINE_GEN_PVT.Terr_Value_Rec_Type;
4040
4041 v_Expression VARCHAR2(5000);
4042 v_Temp VARCHAR2(256);
4043 v_State BOOLEAN;
4044 v_Counter NUMBER := 0;
4045 l_Row NUMBER := 0;
4046
4047 BEGIN
4048 -- Reinitialize the v_Temp to NULL
4049 v_Temp := NULL;
4050
4051 FOR C IN c_Terr_ResQual LOOP
4052 l_Expr_Pointer := l_Expr_Pointer + 1;
4053 v_Terr_Qual_Id := C.terr_qual_Id;
4054
4055 --dbms_output.put_line( '[1] Inside BuildRuleExpression -> Terr_Qual_Id - ' || to_char(v_Terr_Qual_Id) );
4056 v_Counter := 1;
4057
4058 If v_Counter = 1 and l_Expr_Pointer = 1 Then
4059 l_Expr_Tbl(l_Expr_Pointer) := ' (( ';
4060 -- Inside the loop for the first qualifer
4061 ElsIf v_Counter = 1 Then
4062 l_Expr_Tbl(l_Expr_Pointer) := ' AND (( ';
4063 End If;
4064
4065 Open c_Values;
4066
4067 LOOP
4068 Fetch c_Values INTO l_Record;
4069
4070 Exit WHEN c_Values%NOTFOUND;
4071
4072 --for second set of qualifer value
4073 If l_Expr_Pointer > 1 and v_counter <> 1 Then
4074 l_Expr_Pointer := l_Expr_Pointer + 1;
4075 l_Expr_Tbl(l_Expr_Pointer) := ' OR ( ';
4076 End If;
4077
4078 v_Counter := v_Counter + 1;
4079 l_Expr_Pointer := l_Expr_Pointer + 1;
4080
4081 --dbms_output.put_line('Values -> l_Expr_Pointer ' || to_char( l_Expr_Pointer) );
4082
4083 -- --dbms_output.put_line( '[1] Inside the VALUES loop - '|| l_Record.DISPLAY_TYPE);
4084 -- Do all the special processing for for interest Category/Primary Intererst/ Secondary types
4085 IF l_Record.DISPLAY_TYPE = 'INTEREST_TYPE' Then
4086 --dbms_output.put_line( 'Inside first if interest_type');
4087 v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_Interest_Type(l_Record => l_record);
4088 -- This display type is only for resource qualifer (competence/competence level)
4089 ELSIf l_Record.DISPLAY_TYPE = 'COMPETENCE' Then
4090 --dbms_output.put_line( 'Inside first if competence');
4091 v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_COMPETENCE(l_Record => l_record);
4092 ELSIf l_Record.DISPLAY_TYPE = 'NUMERIC' Then
4093 --dbms_output.put_line( 'Inside char number if');
4094 v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_NUMERIC(l_Record => l_record);
4095 ELSIf l_Record.DISPLAY_TYPE like 'CHAR' Then
4096 --dbms_output.put_line( 'Inside char number if');
4097 v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_CHAR(l_Record => l_record);
4098 ELSIf l_Record.DISPLAY_TYPE = 'SPECIAL_FUNCTION' Then
4099 -- Need to add this
4100 null;
4101 ELSIF l_Record.DISPLAY_TYPE = 'CURRENCY' Then
4102 v_Temp := JTF_TERRITORY_RESOURCE_PVT.Get_Expression_CURRENCY(l_Record => l_record);
4103 End If;
4104 --
4105 l_Expr_Tbl(l_Expr_Pointer) := v_Temp;
4106
4107 l_Expr_Pointer := l_Expr_Pointer + 1;
4108 l_Expr_Tbl(l_Expr_Pointer) := ' ) ';
4109 --
4110 END LOOP;
4111
4112 Close c_Values;
4113 --
4114 l_Expr_Pointer := l_Expr_Pointer + 1;
4115 l_Expr_Tbl(l_Expr_Pointer) := ' ) ';
4116 END LOOP;
4117 --
4118 FOR l_Row IN 1 .. l_Expr_Tbl.Count LOOP
4119 If l_Row = l_Expr_Pointer and rtrim(l_Expr_Tbl(l_Row)) is not NULL Then
4120 v_Expression := v_Expression || rtrim(l_Expr_Tbl(l_Row));
4121 --dbms_output.put_line(l_Expr_Tbl(l_Row) );
4122 ElsIf rtrim(l_Expr_Tbl(l_Row)) is not NULL Then
4123 v_Expression := v_Expression || rtrim(l_Expr_Tbl(l_Row));
4124 --dbms_output.put_line(l_Expr_Tbl(l_Row) );
4125 End If;
4126 End LOOP;
4127 return v_Expression;
4128 EXCEPTION
4129 WHEN OTHERS Then
4130 v_Expression := NULL;
4131 return v_Expression;
4132 END BuildRuleExpression;
4133
4134
4135
4136
4137 FUNCTION Get_Expression_Interest_Type
4138 (
4139 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
4140 ) RETURN VARCHAR2
4141 AS
4142 l_Expression VARCHAR2(1000);
4143
4144 BEGIN
4145 If l_Record.COMPARISON_OPERATOR = '=' Then
4146 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' = ';
4147 -- If the following condition is NOT true then it is a bug/data problem
4148 If l_Record.INTEREST_TYPE_ID IS NOT NULL Then
4149 l_Expression := l_Expression || to_char(l_Record.INTEREST_TYPE_ID) || ' ';
4150 If l_Record.PRIMARY_INTEREST_CODE_ID is NOT NULL Then
4151 l_Expression := l_Expression || 'AND P_RECORD.' ||
4152 l_Record.PRIM_INT_CDE_COL_ALIAS || ' = ' ||
4153 to_char(l_Record.PRIMARY_INTEREST_CODE_ID) || ' ';
4154 If l_Record.SECONDARY_INTEREST_CODE_ID IS NOT NULL Then
4155 l_Expression := l_Expression || 'AND P_RECORD.' ||
4156 l_Record.SEC_INT_CDE_COL_ALIAS || ' = ' ||
4157 to_char(l_Record.SECONDARY_INTEREST_CODE_ID) || ' ';
4158 End If;
4159 End If;
4160 -- If the interest type id is NULL. This is actually data error
4161 Else
4162 l_Expression := NULL;
4163 End If;
4164 -- For interest type Other operator should be invalid
4165 Else
4166 l_Expression := NULL;
4167 End If;
4168 --dbms_output.put_line(l_Expression);
4169
4170 return l_Expression;
4171 END Get_Expression_Interest_Type;
4172
4173
4174
4175
4176 -- NUMERIC Display Type
4177
4178 FUNCTION Get_Expression_NUMERIC
4179 (
4180 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
4181 ) RETURN VARCHAR2
4182 AS
4183 l_Expression VARCHAR2(1000);
4184
4185 BEGIN
4186 --process between operator
4187 If l_Record.COMPARISON_OPERATOR IN ('BETWEEN', 'NOT BETWEEN') Then
4188 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4189 ' ' || to_char(l_Record.LOW_VALUE_NUMBER) || ' AND ' ||
4190 to_char(l_Record.HIGH_VALUE_NUMBER) || ' ';
4191 -- Process like operator
4192 ElsIf l_Record.COMPARISON_OPERATOR IN ('NOT LIKE', 'LIKE') Then
4193 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4194 ' ''' || to_char(l_Record.LOW_VALUE_NUMBER) || '%''';
4195
4196 --Other operator like <, >, <=, >=, <>, <, =
4197 Else
4198 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4199 ' ' || to_char(l_Record.LOW_VALUE_NUMBER);
4200 End If;
4201 return l_Expression;
4202
4203 --dbms_output.put_line(l_Expression);
4204 END Get_Expression_NUMERIC;
4205
4206
4207
4208
4209 -- CURRENCY Display Type
4210
4211 FUNCTION Get_Expression_CURRENCY
4212 (
4213 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
4214 ) RETURN VARCHAR2
4215 AS
4216 l_Expression VARCHAR2(1000);
4217
4218 BEGIN
4219 -----------------------------------------------------------
4220 -- Need to add the currency convertion routine
4221 ------------------------------------------------------------
4222
4223 --process between operator
4224 If l_Record.COMPARISON_OPERATOR IN ('BETWEEN', 'NOT BETWEEN') Then
4225 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4226 ' ' || to_char(l_Record.LOW_VALUE_NUMBER) || ' AND ' ||
4227 to_char(l_Record.HIGH_VALUE_NUMBER) || ' ';
4228 --Other operator like <, >, <=, >=, <>, <, =
4229 Else
4230 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4231 ' ' || to_char(l_Record.LOW_VALUE_NUMBER);
4232 End If;
4233 --dbms_output.put_line(l_Expression);
4234
4235 return l_Expression;
4236
4237 END Get_Expression_CURRENCY;
4238
4239
4240
4241
4242 -- VARCHAR2 Display Type
4243
4244 FUNCTION Get_Expression_CHAR
4245 (
4246 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
4247 ) RETURN VARCHAR2
4248 AS
4249 l_Expression VARCHAR2(1000);
4250
4251 BEGIN
4252 If nvl(l_Record.CONVERT_TO_ID_FLAG, 'N') = 'N' Then
4253 --process between operator
4254 If l_Record.COMPARISON_OPERATOR IN ('BETWEEN', 'NOT BETWEEN') Then
4255 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4256 ' ''' || l_Record.LOW_VALUE_CHAR || ''' AND ''' || l_Record.HIGH_VALUE_CHAR || '''';
4257
4258 -- Process like operator
4259 ElsIf l_Record.COMPARISON_OPERATOR IN ('NOT LIKE', 'LIKE') Then
4260 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4261 '''' || l_Record.LOW_VALUE_CHAR || '%''';
4262
4263 --Other operator like <, >, <=, >=, <>, <, =
4264 Else
4265 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4266 ' ''' || l_Record.LOW_VALUE_CHAR || ''' ';
4267
4268 End If;
4269 --
4270 --If the Convert to ID flag is Turned off
4271 Else
4272 If nvl(l_Record.ID_USED_FLAG, 'N') = 'N' Then
4273 If l_Record.COMPARISON_OPERATOR IN ('BETWEEN', 'NOT BETWEEN') Then
4274 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4275 '''' || l_Record.LOW_VALUE_CHAR || ''' AND ''' || l_Record.HIGH_VALUE_CHAR || '''';
4276
4277 End If;
4278 Else
4279 -- Process like operator
4280 If l_Record.COMPARISON_OPERATOR IN ('NOT LIKE', 'LIKE') Then
4281 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4282 '''' || l_Record.LOW_VALUE_CHAR || '%'' ';
4283
4284 --Other operator like <, >, <=, >=, <>, <, =
4285 Else
4286 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' ' || l_Record.COMPARISON_OPERATOR ||
4287 ' ' || to_char(l_Record.LOW_VALUE_CHAR_ID) || ' ';
4288 End If;
4289 end if;
4290 End If;
4291 --dbms_output.put_line(l_Expression);
4292 return l_Expression;
4293 --
4294 END Get_Expression_CHAR;
4295
4296
4297
4298
4299 FUNCTION Get_Expression_Competence
4300 (
4301 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
4302 ) RETURN VARCHAR2
4303 AS
4304 l_Expression VARCHAR2(1000);
4305
4306 BEGIN
4307 If l_Record.COMPARISON_OPERATOR = '=' Then
4308 l_Expression := ' P_RECORD.' || l_Record.QUAL_COL1_ALIAS || ' = ';
4309
4310 -- If the following condition is NOT true then it is a bug/data problem
4311 If l_Record.INTEREST_TYPE_ID IS NOT NULL Then
4312 l_Expression := l_Expression || to_char(l_Record.INTEREST_TYPE_ID) || ' ';
4313 If l_Record.PRIMARY_INTEREST_CODE_ID is NOT NULL Then
4314 l_Expression := l_Expression || 'AND P_RECORD.' ||
4315 l_Record.PRIM_INT_CDE_COL_ALIAS || ' = ' ||
4316 to_char(l_Record.PRIMARY_INTEREST_CODE_ID) || ' ';
4317 End If;
4318 -- If the competence id is NULL. This is actually data error
4319 Else
4320 l_Expression := NULL;
4321 End If;
4322
4323 -- For interest type Other operator should be invalid
4324 Else
4325 l_Expression := NULL;
4326 End If;
4327 --dbms_output.put_line(l_Expression);
4328 return l_Expression;
4329 --
4330 END Get_Expression_Competence;
4331
4332
4333
4334
4335 -- Function used in JTF_TERR_RESOURCES_V to return
4336 -- the group_name for the group_id of a resource
4337
4338 FUNCTION get_group_name
4339 (
4340 p_group_id NUMBER
4341 ) RETURN VARCHAR2 IS
4342
4343 x_group_name VARCHAR2(60);
4344
4345 /* cursor to get group_name */
4346
4347 CURSOR c_get_group_name (p_group_id NUMBER) IS
4348 SELECT jrgv.group_name
4349 FROM jtf_rs_groups_vl jrgv
4350 WHERE jrgv.group_id = p_group_id
4351 AND rownum < 2;
4352
4353 BEGIN
4354
4355 IF ( p_group_id = FND_API.G_MISS_NUM OR
4356 p_group_id IS NULL) THEN
4357
4358 /* no group_id so return NULL */
4359 RETURN NULL;
4360
4361 ELSE /* get group_name */
4362
4363 OPEN c_get_group_name(p_group_id);
4364 FETCH c_get_group_name INTO x_group_name;
4365 CLOSE c_get_group_name;
4366
4367 RETURN x_group_name;
4368
4369 END IF;
4370
4371 EXCEPTION
4372 WHEN NO_DATA_FOUND THEN
4373 RETURN NULL;
4374
4375 END get_group_name;
4376
4377 /* 10/12/00 JDOCHERT */
4378 -- Function used in views to return
4379 -- the resource name
4380 FUNCTION get_resource_name ( p_resource_id NUMBER
4381 , p_resource_type VARCHAR2)
4382 RETURN VARCHAR2 IS
4383
4384 lx_resource_name VARCHAR2(240);
4385
4386 /* cursor to get resource type name */
4387 CURSOR csr_get_rs_name ( lp_resource_id NUMBER
4388 , lp_resource_type VARCHAR2) IS
4389 SELECT jv.resource_name
4390 FROM jtf_rs_resources_vl jv
4391 WHERE jv.resource_id = lp_resource_id
4392 AND jv.resource_type = lp_resource_type
4393 AND rownum < 2;
4394
4395 lx_rs_type_code VARCHAR2(60);
4396
4397 BEGIN
4398
4399 lx_rs_type_code := p_resource_type;
4400
4401 /* 3/19/02: JDOCHERT: 2144381 + 2195839 bug fixes */
4402 IF lx_rs_type_code = 'RS_SUPPLIER' THEN
4403 lx_rs_type_code := 'RS_SUPPLIER_CONTACT';
4404 END IF;
4405
4406 /* get resource type name */
4407 OPEN csr_get_rs_name(p_resource_id, lx_rs_type_code);
4408 FETCH csr_get_rs_name INTO lx_resource_name;
4409 CLOSE csr_get_rs_name;
4410
4411 RETURN lx_resource_name;
4412
4413 EXCEPTION
4414 WHEN NO_DATA_FOUND THEN
4415 RETURN NULL;
4416
4417 END get_resource_name;
4418
4419 /* 10/04/00 JDOCHERT */
4420 -- Function used in views to return
4421 -- the resource type name for the resource type code
4422 -- of a resource
4423 FUNCTION get_rs_type_name (p_rs_type_code VARCHAR2)
4424 RETURN VARCHAR2 IS
4425
4426 lx_rs_type_name VARCHAR2(60);
4427
4428 /* cursor to get resource type name */
4429 CURSOR csr_get_rs_type_name (lp_rs_type_code VARCHAR2) IS
4430 SELECT jo.name
4431 FROM jtf_objects_vl jo
4432 WHERE jo.object_code = lp_rs_type_code
4433 AND rownum < 2;
4434
4435 BEGIN
4436
4437 OPEN csr_get_rs_type_name(p_rs_type_code);
4438 FETCH csr_get_rs_type_name INTO lx_rs_type_name;
4439 CLOSE csr_get_rs_type_name;
4440
4441 RETURN lx_rs_type_name;
4442
4443 EXCEPTION
4444 WHEN NO_DATA_FOUND THEN
4445 RETURN NULL;
4446
4447 END get_rs_type_name;
4448
4449
4450 /* 09/16/00 VVUYYURU */
4451 -- ***************************************************
4452 -- start of comments
4453 -- ***************************************************
4454 -- API name : Copy_Terr_Resources
4455 -- Type : PUBLIC
4456 -- Function : Copy Territory Resources and Resource Access
4457 --
4458 -- Pre-reqs :
4459 -- Parameters:
4460 -- IN :
4461 -- Required
4462 -- Parameter Name Data Type Default
4463 -- p_Api_Version_Number IN NUMBER,
4464 -- p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4465 -- p_Commit IN VARCHAR2 := FND_API.G_FALSE,
4466 -- p_source_terr_id NUMBER := G_MISS_NUM
4467 -- p_dest_terr_id NUMBER := G_MISS_NUM
4468 --
4469 -- Optional
4470 -- Parameter Name Data Type Default
4471 --
4472 -- OUT :
4473 -- Parameter Name Data Type
4474 -- x_Return_Status VARCHAR2
4475 -- x_msg_count NUMBER
4476 -- x_msg_data VARCHAR2
4477 -- Notes:
4478 --
4479 --
4480 -- End of Comments
4481 --
4482 PROCEDURE Copy_Terr_Resources
4483 (
4484 p_Api_Version_Number IN NUMBER,
4485 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4486 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
4487 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4488 p_source_terr_id IN NUMBER,
4489 p_dest_terr_id IN NUMBER,
4490 x_msg_count OUT NOCOPY NUMBER,
4491 x_msg_data OUT NOCOPY VARCHAR2,
4492 x_return_status OUT NOCOPY VARCHAR2
4493 )
4494 IS
4495
4496 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Terr_Resources';
4497 l_api_version_number CONSTANT NUMBER := 1.0;
4498 l_return_status VARCHAR2(1);
4499 l_msg_count NUMBER;
4500 l_msg_data VARCHAR2(2000);
4501
4502 l_dest_terr_rsc_id NUMBER;
4503 l_source_terr_rsc_id NUMBER;
4504
4505 l_TerrRsc_rec TerrResource_rec_type;
4506 l_TerrRsc_Access_rec TerrRsc_Access_rec_type;
4507 l_TerrRsc_Out_rec TerrResource_out_rec_type;
4508 l_TerrRsc_Access_Out_rec TerrRsc_Access_out_rec_type;
4509
4510
4511 CURSOR csr_rsc_all (lp_terr_id NUMBER) IS
4512 SELECT terr_rsc_id,
4513 last_update_date,
4514 last_updated_by,
4515 creation_date,
4516 created_by,
4517 last_update_login,
4518 --terr_id,
4519 resource_id,
4520 group_id,
4521 resource_type,
4522 role,
4523 primary_contact_flag,
4524 start_date_active,
4525 end_date_active,
4526 full_access_flag,
4527 org_id
4528 FROM jtf_terr_rsc_ALL
4529 WHERE terr_id = lp_terr_id;
4530
4531
4532 CURSOR csr_rsc_access_all (lp_terr_rsc_id NUMBER) IS
4533 SELECT
4534 --terr_rsc_access_id,
4535 last_update_date,
4536 last_updated_by,
4537 creation_date,
4538 created_by,
4539 last_update_login,
4540 --terr_rsc_id,
4541 access_type,
4542 org_id
4543 FROM jtf_terr_rsc_access_ALL
4544 WHERE terr_rsc_id = lp_terr_rsc_id;
4545
4546
4547 BEGIN
4548
4549 -- Standard Start of API savepoint
4550 SAVEPOINT COPY_TERR_RESOURCES;
4551
4552 /*
4553 -- Standard call to check for call compatibility.
4554 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4555 p_api_version_number,
4556 l_api_name,
4557 G_PKG_NAME)
4558 THEN
4559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4560 END IF;
4561
4562
4563 -- Initialize message list if p_init_msg_list is set to TRUE.
4564 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4565 FND_MSG_PUB.initialize;
4566 END IF;
4567 */
4568
4569
4570 -- Debug Message
4571 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4572 THEN
4573 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
4574 fnd_message.set_name ('PROC_NAME', l_api_name);
4575 FND_MSG_PUB.Add;
4576 END IF;
4577
4578 -- Initialize API return status to success
4579 x_return_status := FND_API.G_RET_STS_SUCCESS;
4580
4581
4582 -- API Body starts here
4583
4584 OPEN csr_rsc_all (p_source_terr_id);
4585 LOOP
4586 FETCH csr_rsc_all INTO
4587 l_source_terr_rsc_id,
4588 l_TerrRsc_rec.last_update_date,
4589 l_TerrRsc_rec.last_updated_by,
4590 l_TerrRsc_rec.creation_date,
4591 l_TerrRsc_rec.created_by,
4592 l_TerrRsc_rec.last_update_login,
4593 --l_TerrRsc_rec.terr_id,
4594 l_TerrRsc_rec.resource_id,
4595 l_TerrRsc_rec.group_id,
4596 l_TerrRsc_rec.resource_type,
4597 l_TerrRsc_rec.role,
4598 l_TerrRsc_rec.primary_contact_flag,
4599 l_TerrRsc_rec.start_date_active,
4600 l_TerrRsc_rec.end_date_active,
4601 l_TerrRsc_rec.full_access_flag,
4602 l_TerrRsc_rec.org_id;
4603
4604 l_TerrRsc_rec.terr_id := p_dest_terr_id;
4605
4606 EXIT WHEN csr_rsc_all%NOTFOUND;
4607
4608 JTF_TERRITORY_RESOURCE_PVT.Create_Terr_Resource
4609 (
4610 p_TerrRsc_Rec => l_TerrRsc_rec,
4611 p_Api_Version_Number => l_api_version_number,
4612 p_Init_Msg_List => NULL,
4613 p_Commit => NULL,
4614 p_validation_level => NULL,
4615 x_Return_Status => x_Return_Status,
4616 x_Msg_Count => x_Msg_Count,
4617 x_Msg_Data => x_Msg_Data,
4618 x_TerrRsc_Out_Rec => l_TerrRsc_Out_rec
4619 );
4620
4621 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
4622
4623 /*
4624 dbms_output.put_line(
4625 'Unexpected Execution Error from call to Create Terr Resource API');
4626 */
4627
4628 fnd_message.set_name('JTF', 'JTF_ERROR_TERRRSC_API');
4629 fnd_msg_pub.add;
4630 RAISE fnd_api.g_exc_unexpected_error;
4631
4632 END IF;
4633
4634 l_dest_terr_rsc_id := l_TerrRsc_Out_rec.terr_rsc_id;
4635
4636 OPEN csr_rsc_access_all(l_source_terr_rsc_id);
4637 LOOP
4638 FETCH csr_rsc_access_all INTO
4639 --l_TerrRsc_Access_rec.terr_rsc_access_id,
4640 l_TerrRsc_Access_rec.last_update_date,
4641 l_TerrRsc_Access_rec.last_updated_by,
4642 l_TerrRsc_Access_rec.creation_date,
4643 l_TerrRsc_Access_rec.created_by,
4644 l_TerrRsc_Access_rec.last_update_login,
4645 --l_TerrRsc_Access_rec.terr_rsc_id,
4646 l_TerrRsc_Access_rec.access_type,
4647 l_TerrRsc_Access_rec.org_id;
4648
4649 l_TerrRsc_Access_rec.terr_rsc_id := l_dest_terr_rsc_id;
4650
4651 EXIT WHEN csr_rsc_access_all%NOTFOUND;
4652
4653 --dbms_output.put_line('Terr Resource ID : '||l_TerrRsc_Access_rec.terr_rsc_id);
4654
4655 JTF_TERRITORY_RESOURCE_PVT.Create_Resource_Access
4656 (
4657 p_TerrRsc_Id => l_dest_terr_rsc_id,
4658 p_TerrRsc_Access_Rec => l_TerrRsc_Access_rec,
4659 p_Api_Version_Number => l_api_version_number,
4660 p_Init_Msg_List => FND_API.G_FALSE,
4661 p_Commit => FND_API.G_FALSE,
4662 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4663 x_Return_Status => x_Return_Status,
4664 x_Msg_Count => x_Msg_Count,
4665 x_Msg_Data => x_Msg_Data,
4666 x_TerrRsc_Access_Out_Rec => l_TerrRsc_Access_Out_rec
4667 );
4668
4669 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
4670 /*
4671 dbms_output.put_line(
4672 'Unexpected Execution Error from call to Create Terr Resource Access API');
4673 */
4674 fnd_message.set_name('JTF', 'JTF_ERROR_TERRRSCACCESS_API');
4675 fnd_msg_pub.add;
4676 RAISE fnd_api.g_exc_unexpected_error;
4677 END IF;
4678
4679 END LOOP;
4680 CLOSE csr_rsc_access_all;
4681
4682 END LOOP;
4683 CLOSE csr_rsc_all;
4684
4685 /*
4686 x_Return_Status := l_Return_Status;
4687 x_Msg_Count := l_Msg_Count;
4688 x_Msg_Data := l_Msg_Data;
4689 */
4690
4691
4692 /* Standard call to get message count and
4693 the message information */
4694
4695 FND_MSG_PUB.Count_And_Get
4696 (
4697 p_count => x_msg_count,
4698 p_data => x_msg_data
4699 );
4700
4701
4702 EXCEPTION
4703 WHEN FND_API.G_EXC_ERROR THEN
4704 --dbms_output.put_line('Copy Territory Resources : FND_API.G_EXC_ERROR');
4705 x_return_status := FND_API.G_RET_STS_ERROR ;
4706 FND_MSG_PUB.Count_And_Get
4707 ( P_count => x_msg_count,
4708 P_data => x_msg_data
4709 );
4710
4711 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4712 --dbms_output.put_line('Copy Territory Resources : FND_API.G_EXC_UNEXPECTED_ERROR');
4713 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4714 FND_MSG_PUB.Count_And_Get
4715 ( P_count => x_msg_count,
4716 P_data => x_msg_data
4717 );
4718
4719 WHEN OTHERS THEN
4720 --dbms_output.put_line('Copy Territory Resources : OTHERS - ' || SQLERRM);
4721 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4722 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
4723 THEN
4724 FND_MSG_PUB.Add_Exc_Msg
4725 ( G_PKG_NAME,
4726 'Others Exception in Copy_Terr_Resources ' || SQLERRM
4727 );
4728 END IF;
4729
4730 END Copy_Terr_Resources;
4731
4732
4733
4734 /* procedure to check that UK constraint is not
4735 ** being violated on JTF_TERR_RSC_ALL table
4736 ** -- jdochert 09/19
4737 */
4738 PROCEDURE validate_terr_rsc_access_UK(
4739 p_Terr_Rsc_Id IN NUMBER,
4740 p_Access_Type IN VARCHAR2,
4741 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4742 x_Return_Status OUT NOCOPY VARCHAR2,
4743 x_msg_count OUT NOCOPY NUMBER,
4744 x_msg_data OUT NOCOPY VARCHAR2 )
4745 AS
4746
4747 -- cursor to check that Unique Key constraint not violated
4748 CURSOR csr_chk_uk_violation ( lp_terr_rsc_id NUMBER
4749 , lp_access_type VARCHAR2) IS
4750 SELECT 'X'
4751 FROM JTF_TERR_RSC_ACCESS_ALL
4752 WHERE terr_rsc_id = lp_terr_rsc_id
4753 AND access_type = lp_access_type;
4754
4755 l_return_csr VARCHAR2(1);
4756
4757 BEGIN
4758
4759 --dbms_output('Validate_Unique_Key: Entering API');
4760 -- Initialize the status to success
4761 x_return_status := FND_API.G_RET_STS_SUCCESS;
4762
4763 /* check that Unique Key constraint not violated */
4764 IF ( p_terr_rsc_id IS NOT NULL AND p_terr_rsc_id <> FND_API.G_MISS_NUM AND
4765 p_access_type IS NOT NULL AND p_access_type <> FND_API.G_MISS_CHAR ) THEN
4766
4767 /* check if rec already exists */
4768 OPEN csr_chk_uk_violation ( p_terr_rsc_id
4769 , p_access_type);
4770 FETCH csr_chk_uk_violation INTO l_return_csr;
4771
4772 IF csr_chk_uk_violation%FOUND THEN
4773
4774 x_return_status := FND_API.G_RET_STS_ERROR;
4775
4776 /* Debug message */
4777 --arpatel bug#1500581 (part of fix)
4778 --IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4779 FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_RSC_ACCESS_UK_CON');
4780 --FND_MESSAGE.Set_Token ('TABLE', 'JTF_TERR_RSC_ACCESS_ALL');
4781 FND_MSG_PUB.ADD;
4782 --END IF;
4783
4784 END IF; /* c_chk_uk_violation%FOUND */
4785 CLOSE csr_chk_uk_violation;
4786
4787 END IF;
4788
4789 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
4790 p_data => x_msg_data);
4791
4792 EXCEPTION
4793
4794 WHEN OTHERS THEN
4795 --dbms_output('Validate_Foreign_Key: Others exception' || SQLERRM);
4796 X_return_status := FND_API.G_RET_STS_ERROR;
4797 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
4798 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Unique_Key' );
4799 FND_MESSAGE.Set_Token('ERROR', sqlerrm );
4800 FND_MSG_PUB.ADD;
4801 FND_MSG_PUB.Count_And_Get
4802 ( p_count => x_msg_count,
4803 p_data => x_msg_data
4804 );
4805
4806 END validate_terr_rsc_access_UK;
4807
4808
4809
4810 /* 09/19/00 JDOCHERT */
4811 -- ***************************************************
4812 -- start of comments
4813 -- ***************************************************
4814 -- API name : Transfer_Resource_Territories
4815 -- Type : PUBLIC
4816 -- Function : Transfer one Resource's Territories to another resource
4817 --
4818 -- Pre-reqs :
4819 -- Parameters:
4820 -- IN :
4821 -- Required
4822 -- Parameter Name Data Type Default
4823 -- p_Api_Version_Number IN NUMBER,
4824 -- p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4825 -- p_Commit IN VARCHAR2 := FND_API.G_FALSE,
4826 -- p_source_resource_rec TerrResource_Rec_type
4827 -- p_p_dest_resource_rec TerrResource_Rec_type
4828 -- p_all_terr_flag IN VARCHAR2 := 'Y',
4829 -- p_terr_ids_tbl IN Terr_Ids_Tbl_Type,
4830 -- p_replace_flag IN VARCHAR2 := 'Y',
4831 -- p_add_flag IN VARCHAR2 := 'N',
4832 -- p_delete_flag IN VARCHAR2 := 'Y',
4833 --
4834 -- Optional
4835 -- Parameter Name Data Type Default
4836 --
4837 -- OUT :
4838 -- Parameter Name Data Type
4839 -- x_Return_Status VARCHAR2
4840 -- x_msg_count NUMBER
4841 -- x_msg_data VARCHAR2
4842 -- Notes:
4843 --
4844 --
4845 -- End of Comments
4846 --
4847 PROCEDURE Transfer_Resource_Territories
4848 (
4849 p_Api_Version_Number IN NUMBER,
4850 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4851 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
4852 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4853 p_source_resource_rec IN TerrResource_Rec_type,
4854 p_dest_resource_rec IN TerrResource_Rec_type,
4855 p_all_terr_flag IN VARCHAR2 := 'Y',
4856 p_terr_ids_tbl IN Terr_Ids_Tbl_Type,
4857 p_replace_flag IN VARCHAR2 := 'Y',
4858 p_add_flag IN VARCHAR2 := 'N',
4859 p_delete_flag IN VARCHAR2 := 'Y',
4860 x_msg_count OUT NOCOPY NUMBER,
4861 x_msg_data OUT NOCOPY VARCHAR2,
4862 x_return_status OUT NOCOPY VARCHAR2
4863 ) AS
4864
4865 l_api_name CONSTANT VARCHAR2(30) := 'Transfer_Resource_Territories';
4866 l_api_version_number CONSTANT NUMBER := 1.0;
4867 l_terr_ids_tbl Terr_Ids_Tbl_Type;
4868
4869 CURSOR csr_terr_rsc(l_resource_id NUMBER, l_resource_type VARCHAR) IS
4870 select j.terr_id
4871 from jtf_terr_rsc_ALL j, jtf_terr_ALL jt
4872 where j.resource_id = l_resource_id
4873 and j.resource_type = l_resource_type
4874 and j.terr_id = jt.terr_id
4875 and jt.template_flag = 'N'
4876 and jt.escalation_territory_flag = 'N'
4877 --ARPATEL: bug#2897391
4878 and ( jt.terr_group_flag is null OR jt.terr_group_flag = 'N' )
4879 and not jt.terr_id = 1;
4880
4881 CURSOR csr_unassigned_terrs IS
4882 select terr_id
4883 from JTF_TERR_ALL jt
4884 where NOT EXISTS (select jtr.terr_id
4885 from jtf_terr_rsc_ALL jtr
4886 where jt.terr_id = jtr.terr_id
4887 )
4888 and jt.template_flag = 'N'
4889 and jt.escalation_territory_flag = 'N'
4890 --ARPATEL: bug#2897391
4891 and ( jt.terr_group_flag is null OR jt.terr_group_flag = 'N' )
4892 and not jt.terr_id = 1;
4893
4894 BEGIN
4895 -- Standard Start of API savepoint
4896 SAVEPOINT TRANSFER_TERR_RES;
4897
4898
4899 -- Standard call to check for call compatibility.
4900 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4901 p_api_version_number,
4902 l_api_name,
4903 G_PKG_NAME)
4904 THEN
4905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4906 END IF;
4907
4908
4909 -- Initialize message list if p_init_msg_list is set to TRUE.
4910 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4911 FND_MSG_PUB.initialize;
4912 END IF;
4913
4914
4915
4916 -- Debug Message
4917 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4918 THEN
4919 fnd_message.set_name ('JTF', 'JTF_TERRITORY_START_MSG');
4920 fnd_message.set_name ('PROC_NAME', l_api_name);
4921 FND_MSG_PUB.Add;
4922 END IF;
4923
4924 -- Initialize API return status to success
4925 x_return_status := FND_API.G_RET_STS_SUCCESS;
4926
4927
4928 -- API Body starts here
4929 -- create list of the source resource territories
4930
4931 IF p_all_terr_flag = 'Y' and p_source_resource_rec.resource_id is not null
4932 THEN
4933 OPEN csr_terr_rsc(p_source_resource_rec.resource_id, p_source_resource_rec.resource_type);
4934 FETCH csr_terr_rsc
4935 BULK COLLECT INTO l_terr_ids_tbl;
4936 CLOSE csr_terr_rsc;
4937 ELSIF p_all_terr_flag = 'Y' and p_source_resource_rec.resource_id is null
4938 THEN
4939 OPEN csr_unassigned_terrs;
4940 FETCH csr_unassigned_terrs
4941 BULK COLLECT INTO l_terr_ids_tbl;
4942 CLOSE csr_unassigned_terrs;
4943 ELSE
4944 l_terr_ids_tbl := p_terr_ids_tbl;
4945 END IF;
4946
4947
4948 IF p_add_flag = 'Y'
4949 THEN
4950 FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4951 INSERT INTO JTF_TERR_RSC_ALL(
4952 TERR_RSC_ID,
4953 LAST_UPDATE_DATE,
4954 LAST_UPDATED_BY,
4955 CREATION_DATE,
4956 CREATED_BY,
4957 LAST_UPDATE_LOGIN,
4958 TERR_ID,
4959 RESOURCE_ID,
4960 GROUP_ID,
4961 RESOURCE_TYPE,
4962 ROLE,
4963 PRIMARY_CONTACT_FLAG,
4964 START_DATE_ACTIVE,
4965 END_DATE_ACTIVE,
4966 FULL_ACCESS_FLAG,
4967 ORG_ID
4968 ) VALUES (
4969 JTF_TERR_RSC_s.nextval,
4970 decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
4971 decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
4972 decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
4973 decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
4974 decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
4975 decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
4976 decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
4977 decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
4978 decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
4979 decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
4980 decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
4981 decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
4982 decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
4983 decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
4984 decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
4985 );
4986
4987
4988 ELSIF p_replace_flag = 'Y'
4989 THEN
4990 IF p_delete_flag = 'Y'
4991 THEN
4992
4993 FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4994 INSERT INTO JTF_TERR_RSC_ALL(
4995 TERR_RSC_ID,
4996 LAST_UPDATE_DATE,
4997 LAST_UPDATED_BY,
4998 CREATION_DATE,
4999 CREATED_BY,
5000 LAST_UPDATE_LOGIN,
5001 TERR_ID,
5002 RESOURCE_ID,
5003 GROUP_ID,
5004 RESOURCE_TYPE,
5005 ROLE,
5006 PRIMARY_CONTACT_FLAG,
5007 START_DATE_ACTIVE,
5008 END_DATE_ACTIVE,
5009 FULL_ACCESS_FLAG,
5010 ORG_ID
5011 ) VALUES (
5012 JTF_TERR_RSC_s.nextval,
5013 decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
5014 decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
5015 decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
5016 decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
5017 decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
5018 decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
5019 decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
5020 decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
5021 decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
5022 decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
5023 decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
5024 decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
5025 decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
5026 decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
5027 decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
5028 );
5029
5030 --ARPATEL: 11/06/2003 BUG#2798581 START
5031 FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5032 INSERT INTO JTF_TERR_RSC_ACCESS_ALL(
5033 TERR_RSC_ACCESS_ID,
5034 LAST_UPDATE_DATE,
5035 LAST_UPDATED_BY,
5036 CREATION_DATE,
5037 CREATED_BY,
5038 LAST_UPDATE_LOGIN,
5039 TERR_RSC_ID,
5040 ACCESS_TYPE,
5041 ORG_ID
5042 )
5043 SELECT
5044 JTF_TERR_RSC_ACCESS_s.nextval,
5045 SYSDATE,
5046 G_USER_ID,
5047 SYSDATE,
5048 G_USER_ID,
5049 G_LOGIN_ID,
5050 ntra.terr_rsc_id, -- needs to be the newly created terr_rsc_id from above
5051 raa.access_type,
5052 p_dest_resource_rec.ORG_ID
5053 FROM
5054 JTF_TERR_RSC_ACCESS_ALL raa
5055 ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
5056 ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
5057 WHERE
5058 tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5059 AND tra.resource_id = p_source_resource_rec.resource_id
5060 AND ntra.terr_id = tra.terr_id
5061 AND ntra.resource_id = p_dest_resource_rec.resource_id
5062 AND tra.terr_id = l_terr_ids_tbl(i)
5063 ;
5064
5065 --Do all the deleting of old records at the end
5066
5067 FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5068 DELETE from jtf_terr_rsc_ALL
5069 where terr_id = l_terr_ids_tbl(i)
5070 and resource_id = p_source_resource_rec.resource_id;
5071
5072
5073 --ARPATEL: 11/06/2003 BUG#2798581 END
5074
5075
5076
5077 ELSE
5078
5079 FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5080 INSERT INTO JTF_TERR_RSC_ALL(
5081 TERR_RSC_ID,
5082 LAST_UPDATE_DATE,
5083 LAST_UPDATED_BY,
5084 CREATION_DATE,
5085 CREATED_BY,
5086 LAST_UPDATE_LOGIN,
5087 TERR_ID,
5088 RESOURCE_ID,
5089 GROUP_ID,
5090 RESOURCE_TYPE,
5091 ROLE,
5092 PRIMARY_CONTACT_FLAG,
5093 START_DATE_ACTIVE,
5094 END_DATE_ACTIVE,
5095 FULL_ACCESS_FLAG,
5096 ORG_ID
5097 ) VALUES (
5098 JTF_TERR_RSC_s.nextval,
5099 decode( p_dest_resource_rec.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.LAST_UPDATE_DATE),
5100 decode( p_dest_resource_rec.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATED_BY),
5101 decode( p_dest_resource_rec.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),p_dest_resource_rec.CREATION_DATE),
5102 decode( p_dest_resource_rec.CREATED_BY, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.CREATED_BY),
5103 decode( p_dest_resource_rec.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.LAST_UPDATE_LOGIN),
5104 decode( l_terr_ids_tbl(i), FND_API.G_MISS_NUM, NULL,l_terr_ids_tbl(i)),
5105 decode( p_dest_resource_rec.RESOURCE_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.RESOURCE_ID),
5106 decode( p_dest_resource_rec.GROUP_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.GROUP_ID),
5107 decode( p_dest_resource_rec.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.RESOURCE_TYPE),
5108 decode( p_dest_resource_rec.ROLE, FND_API.G_MISS_CHAR, NULL, p_dest_resource_rec.ROLE),
5109 decode( p_dest_resource_rec.PRIMARY_CONTACT_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.PRIMARY_CONTACT_FLAG),
5110 decode( p_dest_resource_rec.START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.START_DATE_ACTIVE),
5111 decode( p_dest_resource_rec.END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,p_dest_resource_rec.END_DATE_ACTIVE),
5112 decode( p_dest_resource_rec.FULL_ACCESS_FLAG, FND_API.G_MISS_CHAR, NULL,p_dest_resource_rec.FULL_ACCESS_FLAG),
5113 decode( p_dest_resource_rec.ORG_ID, FND_API.G_MISS_NUM, NULL,p_dest_resource_rec.ORG_ID)
5114 );
5115
5116 --ARPATEL: 11/06/2003 BUG#2798581 START
5117 FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5118 INSERT INTO JTF_TERR_RSC_ACCESS_ALL(
5119 TERR_RSC_ACCESS_ID,
5120 LAST_UPDATE_DATE,
5121 LAST_UPDATED_BY,
5122 CREATION_DATE,
5123 CREATED_BY,
5124 LAST_UPDATE_LOGIN,
5125 TERR_RSC_ID,
5126 ACCESS_TYPE,
5127 ORG_ID
5128 )
5129 SELECT
5130 JTF_TERR_RSC_ACCESS_s.nextval,
5131 SYSDATE,
5132 G_USER_ID,
5133 SYSDATE,
5134 G_USER_ID,
5135 G_LOGIN_ID,
5136 ntra.terr_rsc_id, -- needs to be the newly created terr_rsc_id from above
5137 raa.access_type,
5138 p_dest_resource_rec.ORG_ID
5139 FROM
5140 JTF_TERR_RSC_ACCESS_ALL raa
5141 ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
5142 ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
5143 WHERE
5144 tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5145 AND tra.resource_id = p_source_resource_rec.resource_id
5146 AND ntra.terr_id = tra.terr_id
5147 AND ntra.resource_id = p_dest_resource_rec.resource_id
5148 AND tra.terr_id = l_terr_ids_tbl(i)
5149 ;
5150
5151 --UPDATE old rsc to soft delete - end date
5152 FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5153 UPDATE jtf_terr_rsc_all j
5154 SET j.end_date_active = SYSDATE
5155 WHERE j.resource_id = p_source_resource_rec.RESOURCE_ID
5156 AND j.resource_type = p_source_resource_rec.RESOURCE_TYPE
5157 AND j.terr_id = l_terr_ids_tbl(i);
5158
5159 --ARPATEL: 11/06/2003 BUG#2798581 END
5160
5161
5162 END IF;
5163
5164 END IF;
5165
5166 --dbms_output.put_line('Value of l_terr_ids_tbl.first='||TO_CHAR(l_terr_ids_tbl.first));
5167 --dbms_output.put_line('Value of l_terr_ids_tbl.last='||TO_CHAR(l_terr_ids_tbl.last));
5168
5169 -- Debug Message
5170 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5171 THEN
5172 fnd_message.set_name ('JTF', 'JTF_TERRITORY_END_MSG');
5173 fnd_message.set_name ('PROC_NAME', l_api_name);
5174 FND_MSG_PUB.Add;
5175 END IF;
5176
5177 FND_MSG_PUB.Count_And_Get
5178 ( p_count => x_msg_count,
5179 p_data => x_msg_data
5180 );
5181
5182 -- Standard check for p_commit
5183 IF FND_API.to_Boolean( p_commit )
5184 THEN
5185 COMMIT WORK;
5186 END IF;
5187
5188 --dbms_output.put_line('Transfer_Resource_Territories: Exiting API');
5189 EXCEPTION
5190 --
5191 WHEN FND_API.G_EXC_ERROR THEN
5192 --dbms_output.put_line('Transfer_Resource_Territories: FND_API.G_EXC_ERROR');
5193 ROLLBACK TO TRANSFER_TERR_RES;
5194 x_return_status := FND_API.G_RET_STS_ERROR ;
5195 FND_MSG_PUB.Count_And_Get
5196 ( p_count => x_msg_count,
5197 p_data => x_msg_data
5198 );
5199
5200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5201 --dbms_output.put_line('Transfer_Resource_Territories: FND_API.G_EXC_UNEXPECTED_ERROR');
5202 ROLLBACK TO TRANSFER_TERR_RES;
5203 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5204 FND_MSG_PUB.Count_And_Get
5205 ( p_count => x_msg_count,
5206 p_data => x_msg_data
5207 );
5208
5209 WHEN OTHERS THEN
5210 --dbms_output.put_line('Transfer_Resource_Territories PVT: OTHERS - ' || SQLERRM);
5211 ROLLBACK TO TRANSFER_TERR_RES;
5212 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5213 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
5214 THEN
5215 fnd_msg_pub.add_exc_msg (
5216 g_pkg_name,
5217 'Error inside Transfer_Resource_Territories ' || sqlerrm);
5218 END IF;
5219
5220 END Transfer_Resource_Territories;
5221
5222 END JTF_TERRITORY_RESOURCE_PVT;