[Home] [Help]
PACKAGE: APPS.JTF_TERRITORY_RESOURCE_PVT
Source
1 PACKAGE JTF_TERRITORY_RESOURCE_PVT AUTHID CURRENT_USER AS
2 /* $Header: jtfvtrss.pls 120.4 2006/05/24 08:14:30 chchandr ship $ */
3
4 -- ---------------------------------------------------
5 -- Start of Comments
6 -- ---------------------------------------------------
7 -- PACKAGE NAME: JTF_TERRITORY_RESOURCE_PVT
8 -- ---------------------------------------------------
9 -- PURPOSE
10 -- Joint task force core territory resource private api's.
11 -- This package is a private API for inserting territory
12 -- resources into JTF tables. It contains specification
13 -- for pl/sql records and tables related to territory
14 -- resource.
15 --
16 -- PROCEDURES:
17 -- (see below for specification)
18 --
19 -- NOTES
20 -- This package is for PRIVATE USE ONLY use
21 --
22 -- HISTORY
23 -- 06/09/99 VNEDUNGA Created
24 -- 06/09/99 VNEDUNGA Adding full access column
25 -- to resource record
26 -- 06/08/00 VNEDUNGA Adding Full access flag
27 --
28 -- 06/12/00 JDOCHERT Added function (get_group_name)
29 -- to get the name
30 -- of the group that the resource
31 -- belongs to
32 -- 07/08/00 JDOCHERT Added default values for flags, for data migration
33 --
34 -- 09/16/00 VVUYYURU Added the procedure Copy_Terr_Resources
35 --
36 -- 09/19/00 JDOCHERT Added 'validate_terr_rsc_access_UK'
37 -- and 'Transfer_Resource_Territories' procedures
38 --
39 -- 10/04/00 JDOCHERT Added get_rs_type_name function
40 --
41 -- 02/15/01 ARPATEL Adapted 'Transfer_Resource_Territories' to allow mass updates
42 --
43 -- 06/26/02 ARPATEL Adding person_id column to TerrResource_rec_type
44 -- 09/15/05 mhtran added TRANS_ACCESS_CODE
45 -- End of Comments
46 --
47
48
49
50
51 --*******************************************************
52 -- Start of Comments
53 ---------------------------------------------------------
54 -- Territory Resource Record: TerrResource_rec_type
55 -- ---------------------------------------------------
56 -- Parameters:
57 --
58 -- Required:
59 -- TERR_RSC_ID Database sequence
60 -- LAST_UPDATE_DATE Part of std who columns
61 -- LAST_UPDATED_BY Part of std who columns
62 -- CREATION_DATE Part of std who columns
63 -- CREATED_BY Part of std who columns
64 -- LAST_UPDATE_LOGIN Part of std who columns
65 -- TERR_ID Territory associated with resource
66 -- RESOURCE_ID resource id
67 -- GROUP_ID group id
68 -- RESOURCE_TYPE resource type, eg:SALES
69 -- ROLE role
70 -- PRIMARY_CONTACT_FLAG Is this resource a primary contact
71 -- ORG_ID Organization Id
72 -- TRANS_ACCESS_CODE access type
73 -- Defaults:
74 -- Note:
75 --
76 -- End of Comments
77
78 TYPE TerrResource_rec_type IS RECORD
79 (
80 TERR_RSC_ID NUMBER , -- := FND_API.G_MISS_NUM,
81 LAST_UPDATE_DATE DATE , -- := FND_API.G_MISS_DATE,
82 LAST_UPDATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
83 CREATION_DATE DATE , -- := FND_API.G_MISS_DATE,
84 CREATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
85 LAST_UPDATE_LOGIN NUMBER , -- := FND_API.G_MISS_NUM,
86 TERR_ID NUMBER , -- := FND_API.G_MISS_NUM,
87 RESOURCE_ID NUMBER , -- := FND_API.G_MISS_NUM,
88 GROUP_ID NUMBER , -- := FND_API.G_MISS_NUM,
89 RESOURCE_TYPE VARCHAR2(60) , -- := FND_API.G_MISS_CHAR,
90 ROLE VARCHAR2(60) , -- := FND_API.G_MISS_CHAR,
91 PRIMARY_CONTACT_FLAG VARCHAR2(1) := 'N', -- := FND_API.G_MISS_CHAR,
92 START_DATE_ACTIVE DATE , -- := FND_API.G_MISS_DATE,
93 END_DATE_ACTIVE DATE , -- := FND_API.G_MISS_DATE
94 FULL_ACCESS_FLAG VARCHAR2(01) := 'N', -- := FND_API.G_MISS_CHAR,
95 ORG_ID NUMBER, -- := FND_API.G_MISS_NUM,
96 PERSON_ID NUMBER
97 );
98
99 TYPE TerrResource_rec_type_wflex IS RECORD
100 (
101 TERR_RSC_ID NUMBER , -- := FND_API.G_MISS_NUM,
102 LAST_UPDATE_DATE DATE , -- := FND_API.G_MISS_DATE,
103 LAST_UPDATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
104 CREATION_DATE DATE , -- := FND_API.G_MISS_DATE,
105 CREATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
106 LAST_UPDATE_LOGIN NUMBER , -- := FND_API.G_MISS_NUM,
107 TERR_ID NUMBER , -- := FND_API.G_MISS_NUM,
108 RESOURCE_ID NUMBER , -- := FND_API.G_MISS_NUM,
109 GROUP_ID NUMBER , -- := FND_API.G_MISS_NUM,
110 RESOURCE_TYPE VARCHAR2(60) , -- := FND_API.G_MISS_CHAR,
111 ROLE VARCHAR2(60) , -- := FND_API.G_MISS_CHAR,
112 PRIMARY_CONTACT_FLAG VARCHAR2(1) := 'N', -- := FND_API.G_MISS_CHAR,
113 START_DATE_ACTIVE DATE , -- := FND_API.G_MISS_DATE,
114 END_DATE_ACTIVE DATE , -- := FND_API.G_MISS_DATE
115 FULL_ACCESS_FLAG VARCHAR2(01) := 'N', -- := FND_API.G_MISS_CHAR,
116 ORG_ID NUMBER, -- := FND_API.G_MISS_NUM,
117 PERSON_ID NUMBER,
118 ATTRIBUTE_CATEGORY VARCHAR2(30),
119 ATTRIBUTE1 VARCHAR2(150),
120 ATTRIBUTE2 VARCHAR2(150),
121 ATTRIBUTE3 VARCHAR2(150),
122 ATTRIBUTE4 VARCHAR2(150),
123 ATTRIBUTE5 VARCHAR2(150),
124 ATTRIBUTE6 VARCHAR2(150),
125 ATTRIBUTE7 VARCHAR2(150),
126 ATTRIBUTE8 VARCHAR2(150),
127 ATTRIBUTE9 VARCHAR2(150),
128 ATTRIBUTE10 VARCHAR2(150),
129 ATTRIBUTE11 VARCHAR2(150),
130 ATTRIBUTE12 VARCHAR2(150),
131 ATTRIBUTE13 VARCHAR2(150),
132 ATTRIBUTE14 VARCHAR2(150),
133 ATTRIBUTE15 VARCHAR2(150)
134 );
135
136 G_MISS_TERRRESOURCE_REC TerrResource_rec_type;
137 G_MISS_TERRRESOURCE_REC_WFLEX TerrResource_rec_type_wflex;
138
139 TYPE TerrResource_tbl_type IS TABLE OF TerrResource_rec_type
140 INDEX BY BINARY_INTEGER;
141 TYPE TerrResource_tbl_type_wflex IS TABLE OF TerrResource_rec_type_wflex
142 INDEX BY BINARY_INTEGER;
143
144 G_MISS_TERRRESOURCE_TBL TerrResource_tbl_type;
145 G_MISS_TERRRESOURCE_TBL_WFLEX TerrResource_tbl_type_wflex;
146
147
148
149
150 -- ***********************************************************
151 -- Start of Comments
152 -- -----------------------------------------------------------
153 -- Territory Resource out Record: TerrResource_out_rec_type
154 -- -----------------------------------------------------------
155 -- Parameters:
156 -- TERR_RSC_ID Territory resource id
157 -- RETURN_STATUS Status
158 -- Required:
159 -- Defaults:
160 -- Note:
161 --
162 -- End of Comments
163
164
165 TYPE TerrResource_out_rec_type IS RECORD
166 (
167 TERR_RSC_ID NUMBER := FND_API.G_MISS_NUM,
168 RETURN_STATUS VARCHAR2(01) := FND_API.G_MISS_CHAR
169 );
170
171 G_MISS_TERRRESOURCE_OUT_REC TerrResource_out_rec_type;
172
173
174 TYPE TerrResource_out_tbl_type IS TABLE OF TerrResource_out_rec_type
175 INDEX BY BINARY_INTEGER;
176
177 G_MISS_TERRRESOURCE_OUT_TBL TerrResource_out_tbl_type;
178
179
180
181
182 ---------------------------------------------------------
183 -- Territory Resource Record: TerrRsc_Access_type
184 -- ------------------------------------------------------
185 -- Parameters:
186 --
187 -- Required:
188 -- TERR_RSC_ID Database sequence
189 -- LAST_UPDATE_DATE Part of std who columns
190 -- LAST_UPDATED_BY Part of std who columns
191 -- CREATION_DATE Part of std who columns
192 -- CREATED_BY Part of std who columns
193 -- LAST_UPDATE_LOGIN Part of std who columns
194 -- TERR_RSC_ID Territory resource id
195 -- ACCESS_TYPE Resource acces eg: ACCOUNT/LEADS
196 -- ORG_ID NUMBER := FND_API.G_MISS_NUM
197 -- TRANS_ACCESS_CODE access type
198 -- Defaults:
199 -- Note:
200 --
201 -- End of Comments
202
203
204 TYPE TerrRsc_Access_Rec_type IS RECORD
205 (
206 TERR_RSC_ACCESS_ID NUMBER , --:= FND_API.G_MISS_NUM,
207 LAST_UPDATE_DATE DATE , --:= FND_API.G_MISS_DATE,
208 LAST_UPDATED_BY NUMBER , --:= FND_API.G_MISS_NUM,
209 CREATION_DATE DATE , --:= FND_API.G_MISS_DATE,
210 CREATED_BY NUMBER , --:= FND_API.G_MISS_NUM,
211 LAST_UPDATE_LOGIN NUMBER , --:= FND_API.G_MISS_NUM,
212 TERR_RSC_ID NUMBER , --:= FND_API.G_MISS_NUM,
213 ACCESS_TYPE VARCHAR2(30) , --:= FND_API.G_MISS_CHAR,
214 ORG_ID NUMBER , --:= FND_API.G_MISS_NUM,
215 QUALIFIER_TBL_INDEX NUMBER , --:= FND_API.G_MISS_NUM
216 TRANS_ACCESS_CODE VARCHAR2(15)
217 );
218
219
220 G_MISS_TERRRSC_ACCESS_REC TerrRsc_Access_Rec_type;
221
222 TYPE TerrRsc_Access_tbl_type IS TABLE OF TerrRsc_Access_rec_type
223 INDEX BY BINARY_INTEGER;
224
225 G_MISS_TERRRSC_ACCESS_TBL TerrRsc_Access_tbl_type;
226
227
228
229
230 -- ***********************************************************
231 -- Start of Comments
232 -- -----------------------------------------------------------
233 -- Territory Resource access out Record: TerrResource_out_rec_type
234 -- -----------------------------------------------------------
235 -- Parameters:
236 -- TERR_RSC_ID Territory resource id
237 -- RETURN_STATUS Status
238 -- Required:
239 -- Defaults:
240 -- Note:
241 --
242 -- End of Comments
243
244
245 TYPE TerrRsc_Access_Out_rec_type IS RECORD
246 (
247 TERR_RSC_ACCESS_ID NUMBER := FND_API.G_MISS_NUM,
248 RETURN_STATUS VARCHAR2(01) := FND_API.G_MISS_CHAR
249 );
250
251 G_MISS_TERRRSC_ACCESS_OUT_REC TerrRsc_Access_Out_rec_type;
252
253
254 TYPE TerrRsc_Access_out_tbl_type IS TABLE OF TerrRsc_Access_Out_rec_type
255 INDEX BY BINARY_INTEGER;
256
257 G_MISS_TERRRSC_ACCESS_OUT_TBL TerrRsc_Access_out_tbl_type;
258
259
260
261
262
263 -- ***************************************************
264 -- start of comments
265 -- ***************************************************
266 -- API name : Create_TerrResource
267 -- Type : PUBLIC
268 -- Function : To create Territory Resources - which will insert
269 -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
270 -- tables.
271 --
272 -- Pre-reqs :
273 -- Parameters:
274 -- IN :
275 -- Required
276 -- Parameter Name Data Type Default
277 -- p_Api_Version_Number NUMBER
278 -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
279 -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
280 -- p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL,
281 --
282 -- Optional
283 -- Parameter Name Data Type Default
284 -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
285 -- p_Commit VARCHAR2 := FND_API.G_FALSE
286 --
287 -- OUT :
288 -- Parameter Name Data Type
289 -- x_Return_Status VARCHAR2(1)
290 -- x_Msg_Count NUMBER
291 -- x_Msg_Data VARCHAR2(2000)
292 -- x_TerrRsc_Id NUMBER
293 -- x_Terr_Usgs_Out_Tbl TerrResource_out_tbl_type
294 -- x_Terr_QualTypeUsgs_Out_Tbl TerrRes_Access_out_tbl_type
295 --
296 -- Notes:
297 --
298 --
299 -- End of Comments
300 --
301
302 PROCEDURE Create_TerrResource
303 (
304 p_Api_Version_Number IN NUMBER,
305 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
306 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
307 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
308 x_Return_Status OUT NOCOPY VARCHAR2,
309 x_Msg_Count OUT NOCOPY NUMBER,
310 x_Msg_Data OUT NOCOPY VARCHAR2,
311 p_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
312 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
313 x_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type,
314 x_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsC_Access_out_tbl_type
315 );
316
317 PROCEDURE Create_TerrResource
318 (
319 p_Api_Version_Number IN NUMBER,
320 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
321 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
322 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
323 x_Return_Status OUT NOCOPY VARCHAR2,
324 x_Msg_Count OUT NOCOPY NUMBER,
325 x_Msg_Data OUT NOCOPY VARCHAR2,
326 p_TerrRsc_Tbl IN TerrResource_tbl_type_wflex := G_MISS_TERRRESOURCE_TBL_WFLEX,
327 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
328 x_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type,
329 x_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsC_Access_out_tbl_type
330 );
331
332
333
334
335 -- ***************************************************
336 -- start of comments
337 -- ***************************************************
338 --
339 -- API name : Delete_Terr_Resource
340 -- Type : PUBLIC
341 -- Function : To delete resources associated with
342 -- Territories
343 --
344 -- Pre-reqs :
345 -- Parameters:
346 -- IN :
347 -- Required
348 -- Parameter Name Data Type Default
349 -- p_Api_Version_Number NUMBER
350 -- p_TerrRsc_Id NUMBER
351 --
352 -- Optional
353 -- Parameter Name Data Type Default
354 -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
358 -- OUT :
355 -- p_Commit VARCHAR2 FND_API.G_FALSE
356 -- p_validation_level NUMBER FND_API.G_VALID_LEVEL_FULL,
357 --
359 -- Parameter Name Data Type
360 -- X_Return_Status VARCHAR2(1)
361 -- X_Msg_Count NUMBER
362 -- X_Msg_Data VARCHAR2(2000)
363 --
364 --
365 -- Notes:
366 -- Rules for deletion have to be very strict
367 --
368 -- End of Comments
369 --
370
371 PROCEDURE Delete_Terr_Resource
372 (
373 p_Api_Version_Number IN NUMBER,
374 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
375 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
376 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
377 X_Return_Status OUT NOCOPY VARCHAR2,
378 X_Msg_Count OUT NOCOPY NUMBER,
379 X_Msg_Data OUT NOCOPY VARCHAR2,
380 p_TerrRsc_Id IN NUMBER
381 );
382
383
384
385
386 -- ***************************************************
387 -- start of comments
388 -- ***************************************************
389 -- API name : Update_TerrResource
390 -- Type : PUBLIC
391 -- Function : To Update Territory Resources - which will update
392 -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
393 -- tables.
394 --
395 -- Pre-reqs :
396 -- Parameters:
397 -- IN :
398 -- Required
399 -- Parameter Name Data Type Default
400 -- p_Api_Version_Number NUMBER
401 -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
402 -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
403 --
404 -- Optional
405 -- Parameter Name Data Type Default
406 -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
407 -- p_Commit VARCHAR2 := FND_API.G_FALSE
408 --
409 -- OUT :
410 -- Parameter Name Data Type
411 -- x_Return_Status VARCHAR2(1)
412 -- x_Msg_Count NUMBER
413 -- x_Msg_Data VARCHAR2(2000)
414 -- x_TerrRsc_Id NUMBER
415 -- x_Terr_Usgs_Out_Tbl TerrResource_out_tbl_type
416 -- x_Terr_QualTypeUsgs_Out_Tbl TerrRes_Access_out_tbl_type
417 --
418 -- Notes:
419 --
420 --
421 -- End of Comments
422 --
423
424 PROCEDURE Update_TerrResource
425 (
426 p_Api_Version_Number IN NUMBER,
427 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
428 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
429 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
430 x_Return_Status OUT NOCOPY VARCHAR2,
431 x_Msg_Count OUT NOCOPY NUMBER,
432 x_Msg_Data OUT NOCOPY VARCHAR2,
433 p_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
434 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
435 x_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type,
436 x_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_tbl_type
437 );
438
439
440
441
442
443 -- ***************************************************
444 -- API name : Create_Terr_Resource
445 -- Type : PRIVATE
446 -- Function : To create Territories resource
447 --
448 --
449 -- Pre-reqs :
450 -- Parameters:
451 -- IN :
452 -- Required
453 -- Parameter Name Data Type Default
454 -- P_TerrRsc_Rec TerrResource_tbl_type
455 --
456 -- OUT :
457 -- Parameter Name Data Type
458 -- X_Return_Status VARCHAR2(1)
459 -- X_TerrRsc_Out_Rec TerrResource_out_tbl_type
460 --
461 -- Notes: This is a an overloaded procedure
462 --
463 --
464 -- End of Comments
465 --
466
467 PROCEDURE Create_Terr_Resource
468 (
469 P_TerrRsc_Rec IN TerrResource_Rec_type,
470 p_Api_Version_Number IN NUMBER,
471 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
472 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
473 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
474 x_Return_Status OUT NOCOPY VARCHAR2,
475 x_Msg_Count OUT NOCOPY NUMBER,
476 x_Msg_Data OUT NOCOPY VARCHAR2,
477 X_TerrRsc_Out_Rec OUT NOCOPY TerrResource_out_Rec_type
478 );
479
480 PROCEDURE Create_Terr_Resource
481 (
482 P_TerrRsc_Rec IN TerrResource_Rec_type_wflex,
483 p_Api_Version_Number IN NUMBER,
487 x_Return_Status OUT NOCOPY VARCHAR2,
484 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
485 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
486 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
488 x_Msg_Count OUT NOCOPY NUMBER,
489 x_Msg_Data OUT NOCOPY VARCHAR2,
490 X_TerrRsc_Out_Rec OUT NOCOPY TerrResource_out_Rec_type
491 );
492
493
494
495
496 -- ***************************************************
497 -- start of comments
498 -- ***************************************************
499 -- API name : Create_Terr_Resource
500 -- Type : PRIVATE
501 -- Function : To create Territories Resources
502 --
503 -- Pre-reqs :
504 -- Parameter Name Data Type Default
505 -- P_TerrRsc_Tbl TerrResource_tbl_type
506 --
507 -- OUT :
508 -- Parameter Name Data Type
509 -- X_Return_Status VARCHAR2(1)
510 -- X_TerrRsc_Out_Tbl TerrResource_out_tbl_type
511 --
512 -- Notes: This is a an overloaded procedure. This one
513 -- will call the overloade procedure for records
514 -- creation
515 --
516 --
517 -- End of Comments
518 --
519
520 PROCEDURE Create_Terr_Resource
521 (
522 P_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
523 p_Api_Version_Number IN NUMBER,
524 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
525 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
526 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
527 x_Return_Status OUT NOCOPY VARCHAR2,
528 x_Msg_Count OUT NOCOPY NUMBER,
529 x_Msg_Data OUT NOCOPY VARCHAR2,
530 X_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type
531 );
532
533 PROCEDURE Create_Terr_Resource
534 (
535 P_TerrRsc_Tbl IN TerrResource_tbl_type_wflex := G_MISS_TERRRESOURCE_TBL_WFLEX,
536 p_Api_Version_Number IN NUMBER,
537 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
538 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
539 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
540 x_Return_Status OUT NOCOPY VARCHAR2,
541 x_Msg_Count OUT NOCOPY NUMBER,
542 x_Msg_Data OUT NOCOPY VARCHAR2,
543 X_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type
544 );
545
546
547
548
549 -- start of comments
550 -- ***************************************************
551 -- API name : Create_Resource_Access
552 -- Type : PUBLIC
553 -- Function : To create Territories resource Access
554 --
555 --
556 -- Pre-reqs :
557 -- Parameters:
558 -- IN :
559 -- Required
560 -- Parameter Name Data Type Default
561 -- P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC
562 --
563 -- OUT :
564 -- Parameter Name Data Type
565 -- X_Return_Status VARCHAR2(1)
566 -- X_TerrRsc_Access_Out_Rec TerrRsc_Access_out_rec_type
567 --
568 -- Notes: This is a an overloaded procedure
569 --
570 --
571 -- End of Comments
572 --
573
574 PROCEDURE Create_Resource_Access
575 (
576 p_TerrRsc_Id NUMBER,
577 P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC,
578 p_Api_Version_Number IN NUMBER,
579 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
580 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
581 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
582 x_Return_Status OUT NOCOPY VARCHAR2,
583 x_Msg_Count OUT NOCOPY NUMBER,
584 x_Msg_Data OUT NOCOPY VARCHAR2,
585 X_TerrRsc_Access_Out_Rec OUT NOCOPY TerrRsc_Access_out_rec_type
586 );
587
588
589
590
591
592 -- ***************************************************
593 -- start of comments
594 -- ***************************************************
595 -- API name : Create_Resource _Access
596 -- Type : PUBLIC
597 -- Function : To create Territories resource Access
598 --
599 --
600 -- Pre-reqs :
601 -- Parameters:
602 -- IN :
603 -- Required
604 -- Parameter Name Data Type Default
605 -- P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC
606 --
607 -- OUT :
608 -- Parameter Name Data Type
609 -- X_Return_Status VARCHAR2(1)
610 -- X_TerrRsc_Access_Out_Rec TerrRsc_Access_out_rec_type
611 --
612 -- Notes: This is a an overloaded procedure. This one
616 -- End of Comments
613 -- will call the overloade procedure for records
614 -- creation
615 --
617 --
618
619 PROCEDURE Create_Resource_Access
620 (
621 p_TerrRsc_Id NUMBER,
622 P_TerrRsc_Access_Tbl TerrRsc_Access_Tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
623 p_Api_Version_Number IN NUMBER,
624 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
625 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
626 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
627 x_Return_Status OUT NOCOPY VARCHAR2,
628 x_Msg_Count OUT NOCOPY NUMBER,
629 x_Msg_Data OUT NOCOPY VARCHAR2,
630 X_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_Tbl_type
631 );
632
633
634
635
636 --
637 -- *******************************************************
638 -- Start of Comments
639 -- *******************************************************
640 -- API Name: Delete_TerrResource
641 -- Type : PRIVATE
642 -- Pre-Req :
643 -- Parameters:
644 -- IN
645 -- Required:
646 -- Parameter Name Data Type Default
647 -- P_Api_Version_Number IN NUMBER,
648 -- P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
649 -- P_Commit IN VARCHAR2 := FND_API.G_FALSE
650 -- P_TerrRsc_Id IN NUMBER
651 --
652 -- Optional:
653 -- OUT:
654 -- Parameter Name Data Type Default
655 -- X_Return_Status VARCHAR2
656 --
657 -- Note:
658 --
659 -- End of Comments
660 --
661
662 PROCEDURE Delete_TerrResource
663 (
664 P_Api_Version_Number IN NUMBER,
665 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
666 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
667 P_TerrRsc_Id IN NUMBER,
668 X_Return_Status OUT NOCOPY VARCHAR2,
669 X_Msg_Count OUT NOCOPY VARCHAR2,
670 X_Msg_Data OUT NOCOPY VARCHAR2
671 );
672
673
674
675
676
677 -- *******************************************************
678 -- Start of Comments
679 -- *******************************************************
680 -- API Name: Delete_TerrRsc_Access
681 -- Type : PRIVATE
682 -- Pre-Req :
683 -- Parameters:
684 -- IN
685 -- Parameter Name Data Type Default
686 -- P_Api_Version_Number NUMBER,
687 -- P_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
688 -- P_Commit VARCHAR2 := FND_API.G_FALSE
689 -- P_TerrRsc_Access_Id NUMBER
690 --
691 -- Optional:
692 --
693 -- OUT:
694 -- Parameter Name Data Type Default
695 -- X_Return_Status VARCHAR2
696 --
697 -- Note:
698 --
699 -- End of Comments
700 --
701
702 PROCEDURE Delete_TerrRsc_Access
703 (
704 P_Api_Version_Number IN NUMBER,
705 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
706 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
707 P_TerrRsc_Access_Id IN NUMBER,
708 X_Return_Status OUT NOCOPY VARCHAR2,
709 X_Msg_Count OUT NOCOPY VARCHAR2,
710 X_Msg_Data OUT NOCOPY VARCHAR2
711 );
712
713
714
715
716 --
717 -- ***************************************************
718 -- start of comments
719 -- ***************************************************
720 -- API name : Update_Terr_Resource
721 -- Type : PRIVATE
722 -- Function : To update Territories resource
723 --
724 --
725 -- Pre-reqs :
726 -- Parameters:
727 -- IN :
728 -- Required
729 -- Parameter Name Data Type Default
730 -- P_TerrRsc_Rec TerrResource_tbl_type
731 --
732 -- OUT :
733 -- Parameter Name Data Type
734 -- X_Return_Status VARCHAR2(1)
735 -- X_TerrRsc_Out_Rec TerrResource_out_tbl_type
736 --
737 -- Notes: This is a an overloaded procedure
738 --
739 --
740 -- End of Comments
741 --
742
743 PROCEDURE Update_Terr_Resource
744 (
745 P_TerrRsc_Rec IN TerrResource_Rec_type,
746 p_Api_Version_Number IN NUMBER,
747 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
748 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
749 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
750 x_Return_Status OUT NOCOPY VARCHAR2,
751 x_Msg_Count OUT NOCOPY NUMBER,
752 x_Msg_Data OUT NOCOPY VARCHAR2,
753 X_TerrRsc_Out_Rec OUT NOCOPY TerrResource_out_Rec_type
754 );
755
756
760 -- start of comments
757
758
759 -- ***************************************************
761 -- ***************************************************
762 -- API name : Update_Terr_Resource
763 -- Type : PRIVATE
764 -- Function : To create Territories qualifier
765 --
766 -- Pre-reqs :
767 -- Parameter Name Data Type Default
768 -- P_TerrRsc_Tbl TerrResource_tbl_type
769 --
770 -- OUT :
771 -- Parameter Name Data Type
772 -- X_Return_Status VARCHAR2(1)
773 -- X_TerrRsc_Out_Tbl TerrResource_out_tbl_type
774 --
775 -- Notes: This is a an overloaded procedure. This one
776 -- will call the overloade procedure for records
777 -- creation
778 --
779 --
780 -- End of Comments
781 --
782
783 PROCEDURE Update_Terr_Resource
784 (
785 P_TerrRsc_Tbl IN TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL,
786 p_Api_Version_Number IN NUMBER,
787 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
788 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
789 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
790 x_Return_Status OUT NOCOPY VARCHAR2,
791 x_Msg_Count OUT NOCOPY NUMBER,
792 x_Msg_Data OUT NOCOPY VARCHAR2,
793 X_TerrRsc_Out_Tbl OUT NOCOPY TerrResource_out_tbl_type
794 );
795
796
797
798
799
800 --
801 -- ***************************************************
802 -- start of comments
803 -- ***************************************************
804 -- API name : Update_Resource_Access
805 -- Type : PUBLIC
806 -- Function : To Update Territories resource Access
807 --
808 --
809 -- Pre-reqs :
810 -- Parameters:
811 -- IN :
812 -- Required
813 -- Parameter Name Data Type Default
814 -- P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC
815 --
816 -- OUT :
817 -- Parameter Name Data Type
818 -- X_Return_Status VARCHAR2(1)
819 -- X_TerrRsc_Access_Out_Rec TerrRsc_Access_out_rec_type
820 --
821 -- Notes: This is a an overloaded procedure
822 --
823 --
824 -- End of Comments
825 --
826
827 PROCEDURE Update_Resource_Access
828 (
829 P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_REC,
830 p_Api_Version_Number IN NUMBER,
831 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
832 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
833 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
834 x_Return_Status OUT NOCOPY VARCHAR2,
835 x_Msg_Count OUT NOCOPY NUMBER,
836 x_Msg_Data OUT NOCOPY VARCHAR2,
837 X_TerrRsc_Access_Out_Rec OUT NOCOPY TerrRsc_Access_out_rec_type
838 );
839
840
841
842
843
844 --
845 --
846 -- ***************************************************
847 -- start of comments
848 -- ***************************************************
849 -- API name : Update_Resource _Access
850 -- Type : PUBLIC
851 -- Function : To create Territories resource Access
852 --
853 --
854 -- Pre-reqs :
855 -- Parameters:
856 -- IN :
857 -- Required
858 -- Parameter Name Data Type Default
859 -- P_TerrRsc_Access_Rec TerrRsc_Access_rec_type := G_MISS_TERRRSC_ACCESS_TBL
860 --
861 -- OUT :
862 -- Parameter Name Data Type
863 -- X_Return_Status VARCHAR2(1)
864 -- X_TerrRsc_Access_Out_Rec TerrRsc_Access_out_rec_type
865 --
866 -- Notes: This is a an overloaded procedure. This one
867 -- will call the overloade procedure for records
868 -- creation
869 --
870 -- End of Comments
871 --
872
873 PROCEDURE Update_Resource_Access
874 (
875 P_TerrRsc_Access_Tbl TerrRsc_Access_Tbl_type := G_MISS_TERRRSC_ACCESS_TBL,
876 p_Api_Version_Number IN NUMBER,
877 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
878 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
879 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
880 x_Return_Status OUT NOCOPY VARCHAR2,
881 x_Msg_Count OUT NOCOPY NUMBER,
882 x_Msg_Data OUT NOCOPY VARCHAR2,
883 X_TerrRsc_Access_Out_Tbl OUT NOCOPY TerrRsc_Access_out_Tbl_type
884 );
885
886
887
888
889
890 -- ***************************************************
891 -- start of comments
892 -- ***************************************************
893 -- API name : Validate_TerrResource_Data
894 -- Type : PUBLIC
895 -- Function : Validate Territory Resources
896 --
900 -- Required
897 -- Pre-reqs :
898 -- Parameters:
899 -- IN :
901 -- Parameter Name Data Type Default
902 -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
903 -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
904 --
905 -- Optional
906 -- Parameter Name Data Type Default
907 --
908 -- OUT :
909 -- Parameter Name Data Type
910 -- x_Return_Status VARCHAR2(1)
911 -- Notes:
912 --
913 --
914 -- End of Comments
915 --
916
917 PROCEDURE Validate_TerrResource_Data
918 (
919 p_TerrRsc_Tbl IN TerrResource_tbl_type,
920 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type,
921 x_msg_count OUT NOCOPY NUMBER,
922 x_msg_data OUT NOCOPY VARCHAR2,
923 x_Return_Status OUT NOCOPY VARCHAR2
924 );
925
926 PROCEDURE Validate_TerrResource_Data
927 (
928 p_TerrRsc_Tbl IN TerrResource_tbl_type_wflex,
929 p_TerrRsc_Access_Tbl IN TerrRsc_Access_tbl_type,
930 x_msg_count OUT NOCOPY NUMBER,
931 x_msg_data OUT NOCOPY VARCHAR2,
932 x_Return_Status OUT NOCOPY VARCHAR2
933 );
934
935
936
937
938 ---------------------------------------------------------------------
939 -- Validate Territory Resource
940 ---------------------------------------------------------------------
941 -- Columns Validated
942 -- Make sure a Resource and Resource_Type is specified
943 -- Make sure the Territory Id is valid
944 ---------------------------------------------------------------------
945 PROCEDURE Validate_Terr_Rsc
946 (
947 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
948 x_Return_Status OUT NOCOPY VARCHAR2,
949 x_msg_count OUT NOCOPY NUMBER,
950 x_msg_data OUT NOCOPY VARCHAR2,
951 p_TerrRsc_Rec IN TerrResource_Rec_type
952 );
953
954 PROCEDURE Validate_Terr_Rsc
955 (
956 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
957 x_Return_Status OUT NOCOPY VARCHAR2,
958 x_msg_count OUT NOCOPY NUMBER,
959 x_msg_data OUT NOCOPY VARCHAR2,
960 p_TerrRsc_Rec IN TerrResource_Rec_type_wflex
961 );
962
963
964
965
966 ---------------------------------------------------------------------
967 -- Validate Territory Resource Access record
968 ---------------------------------------------------------------------
969 -- Columns Validated
970 -- Make sure a TERR_RSC_ID is valid
971 -- Make sure the ACCESS_TYPE is valid
972 ---------------------------------------------------------------------
973 PROCEDURE Validate_Terr_Rsc_Access
974 (
975 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
976 x_Return_Status OUT NOCOPY VARCHAR2,
977 x_msg_count OUT NOCOPY NUMBER,
978 x_msg_data OUT NOCOPY VARCHAR2,
979 p_TerrRsc_Id IN NUMBER,
980 p_TerrRsc_Access_Rec IN TerrRsc_Access_Rec_type
981 );
982
983
984
985 FUNCTION BuildRuleExpression
986 (
987 p_Terr_Id NUMBER,
988 p_qual_type_id NUMBER
989 ) return VARCHAR2;
990
991
992 FUNCTION Get_Expression_Interest_Type
993 (
994 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
995 ) RETURN VARCHAR2;
996
997
998 FUNCTION Get_Expression_NUMERIC
999 (
1000 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
1001 ) RETURN VARCHAR2;
1002
1003
1004 FUNCTION Get_Expression_CURRENCY
1005 (
1006 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
1007 ) RETURN VARCHAR2;
1008
1009
1010 FUNCTION Get_Expression_CHAR
1011 (
1012 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
1013 ) RETURN VARCHAR2;
1014
1015
1016 FUNCTION Get_Expression_Competence
1017 (
1018 l_Record JTF_TERR_ENGINE_GEN_PVT.TERR_VALUE_REC_TYPE
1019 ) RETURN VARCHAR2;
1020
1021
1022
1023 -- Function used in JTF_TERR_RESOURCES_V to return
1024 -- the group_name for the group_id of a resource
1025 FUNCTION get_group_name
1026 (
1027 p_group_id NUMBER
1028 ) RETURN VARCHAR2;
1029
1030
1031 /* 10/12/00 JDOCHERT */
1032 -- Function used in views to return
1033 -- the resource name
1034 FUNCTION get_resource_name ( p_resource_id NUMBER
1035 , p_resource_type VARCHAR2) RETURN VARCHAR2;
1036
1037
1038 /* 10/04/00 JDOCHERT */
1039 -- Function used in views to return
1040 -- the resource type name for the resource type code
1041 -- of a resource
1042 FUNCTION get_rs_type_name (p_rs_type_code VARCHAR2)
1043 RETURN VARCHAR2;
1044
1045
1046 /* procedure to check that UK constraint is not
1047 ** being violated on JTF_TERR_RSC_ALL table
1048 ** -- jdochert 09/09
1049 */
1050 PROCEDURE validate_terr_rsc_access_UK(
1051 p_Terr_Rsc_Id IN NUMBER,
1052 p_Access_Type IN VARCHAR2,
1053 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1054 x_Return_Status OUT NOCOPY VARCHAR2,
1055 x_msg_count OUT NOCOPY NUMBER,
1056 x_msg_data OUT NOCOPY VARCHAR2 );
1057
1058
1059 /* 09/16/00 VVUYYURU */
1060 -- ***************************************************
1061 -- start of comments
1062 -- ***************************************************
1063 -- API name : Copy_Terr_Resources
1064 -- Type : PUBLIC
1065 -- Function : Copy Territory Resources and Resource Access
1066 --
1067 -- Pre-reqs :
1068 -- Parameters:
1069 -- IN :
1070 -- Required
1071 -- Parameter Name Data Type Default
1072 -- p_Api_Version_Number IN NUMBER,
1073 -- p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1074 -- p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1075 -- p_source_terr_id NUMBER
1076 -- p_dest_terr_id NUMBER
1077 --
1078 -- Optional
1079 -- Parameter Name Data Type Default
1080 --
1081 -- OUT NOCOPY :
1085 -- x_msg_data VARCHAR2
1082 -- Parameter Name Data Type
1083 -- x_Return_Status VARCHAR2
1084 -- x_msg_count NUMBER
1086 -- Notes:
1087 --
1088 --
1089 -- End of Comments
1090 --
1091 PROCEDURE Copy_Terr_Resources (
1092 p_Api_Version_Number IN NUMBER,
1093 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1094 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1095 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1096 p_source_terr_id IN NUMBER,
1097 p_dest_terr_id IN NUMBER,
1098 x_msg_count OUT NOCOPY NUMBER,
1099 x_msg_data OUT NOCOPY VARCHAR2,
1100 x_return_status OUT NOCOPY VARCHAR2
1101 );
1102
1103
1104 /* 09/19/00 JDOCHERT */
1105
1106 TYPE Terr_Ids_Tbl_Type IS TABLE OF NUMBER
1107 INDEX BY BINARY_INTEGER;
1108 G_MISS_TERRID_TBL Terr_Ids_Tbl_Type;
1109 -- ***************************************************
1110 -- start of comments
1111 -- ***************************************************
1112 -- API name : Transfer_Resource_Territories
1113 -- Type : PUBLIC
1114 -- Function : Transfer one Resource's Territories to another resource
1115 --
1116 -- Pre-reqs :
1117 -- Parameters:
1118 -- IN :
1119 -- Required
1120 -- Parameter Name Data Type Default
1121 -- p_Api_Version_Number IN NUMBER,
1122 -- p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1123 -- p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1124 -- p_source_resource_rec TerrResource_Rec_type
1125 -- p_p_dest_resource_recd TerrResource_Rec_type
1126 --
1127 -- Optional
1128 -- Parameter Name Data Type Default
1129 --
1130 -- OUT :
1131 -- Parameter Name Data Type
1132 -- x_Return_Status VARCHAR2
1133 -- x_msg_count NUMBER
1134 -- x_msg_data VARCHAR2
1135 -- Notes:
1136 --
1137 --
1138 -- End of Comments
1139 --
1140 PROCEDURE Transfer_Resource_Territories
1141 (
1142 p_Api_Version_Number IN NUMBER,
1143 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1144 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1145 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1146 p_source_resource_rec IN TerrResource_Rec_type,
1147 p_dest_resource_rec IN TerrResource_Rec_type,
1148 p_all_terr_flag IN VARCHAR2 := 'Y',
1149 p_terr_ids_tbl IN Terr_Ids_Tbl_Type := G_MISS_TERRID_TBL,
1150 p_replace_flag IN VARCHAR2 := 'Y',
1151 p_add_flag IN VARCHAR2 := 'N',
1152 p_delete_flag IN VARCHAR2 := 'Y',
1153 x_msg_count OUT NOCOPY NUMBER,
1154 x_msg_data OUT NOCOPY VARCHAR2,
1155 x_return_status OUT NOCOPY VARCHAR2
1156 );
1157
1158
1159 -- Package spec
1160 END JTF_TERRITORY_RESOURCE_PVT;