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