[Home] [Help]
PACKAGE: APPS.JTF_TERRITORY_PVT
Source
1 Package JTF_TERRITORY_PVT AUTHID CURRENT_USER AS
2 /* $Header: jtfvters.pls 120.2 2005/09/06 00:29:54 spai ship $ */
3 -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TERRITORY_PVT
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- Joint task force core territory manager private api's.
10 -- This package is a public API for inserting territory
11 -- related information IN to information into JTF tables.
12 -- It contains specification for pl/sql records and tables
13 -- and the private territory related API's.
14 --
15 -- Procedures:
16 -- (see below for specification)
17 --
18 -- NOTES
19 -- This package is for private use
20 --
21 -- HISTORY
22 -- 06/09/99 VNEDUNGA Created
23 -- 07/15/99 JDOCHERT Updated existing APIs and
24 -- added new APIs
25 -- 06/09/99 VNEDUNGA Commenting OUT NOCOPY FND_G_MISS for records def
26 -- because of the bug in forms PL/SQL
27 -- 01/31/00 VNEDUNGA Adding code for overlap check
28 -- 03/15/00 VNEDUNGA Changing the some validation routine specs
29 -- 04/04/00 EIHSU Added Gen_Duplicate_Territory and relevant procs
30 -- 06/14/00 VNEDUNGA Changing the overlap exists function and
31 -- added rownum < 2 to function to return desc
32 -- 07/08/00 JDOCHERT Adding default values for flag, for data migration
33 -- 09/09/00 jdochert Added Unique validation for JTF_TERR_USGS_ALL + JTF_TERR_QTYPE_USGS_AL
34 -- 09/17/00 JDOCHERT BUG# 1408610 FIX: Added NUM_WINNERS to TERR_ALL_REC_TYPE
35 -- 10/04/00 jdochert Added validation for NUM_WINNERS
36 -- 10/04/00 jdochert Added NUM_QUAL to JTF_TERR_ALL_REC_TYPE record definition
37 -- 10/04/01 arpatel Added first_terr_node flag to Copy Territory
38 -- 04/12/01 jdochert Added PROCEDURE chk_num_copy_terr
39 -- 04/20/01 arpatel Added PROCEDURE Concurrent_Copy_Territory and Write_Log
40 -- 04/28/01 arpatel Added function conc_req_copy_terr returning number (concurrent request ID)
41 -- 12/03/04 achanda Added value4_id : bug # 3726007
42 --
43 -- End of Comments
44 --
45 --*******************************************************
46 -- Composite Types
47 --*******************************************************
48 --
49 -- Start of Comments
50 ---------------------------------------------------------
51
52 -- Winning Territory Record: WinningTerr_rec_type
53 -- ---------------------------------------------------
54 -- Parameters:
55 -- Required:
56 -- Defaults:
57 -- Note:
58 --
59 -- End of Comments
60
61 TYPE WinningTerr_rec_type IS RECORD
62 (
63 TERR_ID NUMBER := FND_API.G_MISS_NUM,
64 RANK VARCHAR2(30) := FND_API.G_MISS_CHAR,
65 ORG_ID NUMBER := FND_API.G_MISS_NUM,
66 PARENT_TERRITORY_ID NUMBER := FND_API.G_MISS_NUM,
67 TEMPLATE_TERRITORY_ID NUMBER := FND_API.G_MISS_NUM,
68 ESCALATION_TERRITORY_ID NUMBER := FND_API.G_MISS_NUM
69 );
70
71 G_MISS_WINNINGTERR_REC WinningTerr_rec_type;
72
73 TYPE WinningTerr_tbl_type IS TABLE OF WinningTerr_rec_type
74 INDEX BY BINARY_INTEGER;
75
76 G_MISS_WINNINGTERR_TBL WinningTerr_tbl_type;
77
78
79 -- ***************************************************
80 -- Start of Comments
81 -- ---------------------------------------------------
82 -- Territory record type: Terr_All_Rec_Type
83 -- ---------------------------------------------------
84 -- Attributes:
85 -- TERR_ID
86 -- LAST_UPDATE_DATE
87 -- LAST_UPDATED_BY
88 -- CREATION_DATE
89 -- CREATED_BY
90 -- LAST_UPDATE_LOGIN
91 -- APPLICATION_SHORT_NAME
92 -- NAME
93 -- ENABLED_FLAG
94 -- REQUEST_ID
95 -- PROGRAM_APPLICATION_ID
96 -- PROGRAM_ID
97 -- PROGRAM_UPDATE_DATE
98 -- START_DATE_ACTIVE
99 -- RANK
100 -- END_DATE_ACTIVE
101 -- DESCRIPTION
102 -- ORG_ID
103 -- UPDATE_FLAG
104 -- AUTO_ASSIGN_MEMBERS_FLAG
105 -- PLANNED_FLAG
106 -- TERRITORY_TYPE_ID
107 -- PARENT_TERRITORY_ID
108 -- TEMPLATE_FLAG
109 -- TEMPLATE_TERRITORY_ID
110 -- ESCALATION_TERRITORY_FLAG
111 -- ESCALATION_TERRITORY_ID
112 -- OVERLAp_ALLOWED_FLAG
113 -- ATTRIBUTE_CATEGORY
114 -- ATTRIBUTE1
115 -- ATTRIBUTE2
116 -- ATTRIBUTE3
117 -- ATTRIBUTE4
118 -- ATTRIBUTE5
119 -- ATTRIBUTE6
120 -- ATTRIBUTE7
121 -- ATTRIBUTE8
122 -- ATTRIBUTE9
123 -- ATTRIBUTE10
124 -- ATTRIBUTE11
125 -- ATTRIBUTE12
126 -- ATTRIBUTE13
127 -- ATTRIBUTE14
128 -- ATTRIBUTE15
129 -- NUM_WINNERS
130 -- NUM_QUAL
131 --
132 -- Notes
133 --
134 --
135 -- End of Comments
136 --
137 TYPE Terr_All_Rec_Type IS RECORD
138 (TERR_ID NUMBER , -- := FND_API.G_MISS_NUM,
139 LAST_UPDATE_DATE DATE , -- := FND_API.G_MISS_DATE,
140 LAST_UPDATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
141 CREATION_DATE DATE , -- := FND_API.G_MISS_DATE,
142 CREATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
143 LAST_UPDATE_LOGIN NUMBER , -- := FND_API.G_MISS_NUM,
144 APPLICATION_SHORT_NAME VARCHAR2(50) , -- := FND_API.G_MISS_CHAR,
145 NAME VARCHAR2(2000) , -- := FND_API.G_MISS_CHAR,
146 ENABLED_FLAG VARCHAR2(1) , -- := FND_API.G_MISS_CHAR,
147 REQUEST_ID NUMBER , -- := FND_API.G_MISS_NUM,
148 PROGRAM_APPLICATION_ID NUMBER , -- := FND_API.G_MISS_NUM,
149 PROGRAM_ID NUMBER , -- := FND_API.G_MISS_NUM,
150 PROGRAM_UPDATE_DATE DATE , -- := FND_API.G_MISS_DATE,
151 START_DATE_ACTIVE DATE , -- := FND_API.G_MISS_DATE,
152 RANK NUMBER , -- := FND_API.G_MISS_NUM,
153 END_DATE_ACTIVE DATE , -- := FND_API.G_MISS_DATE,
154 DESCRIPTION VARCHAR2(240) , -- := FND_API.G_MISS_CHAR,
155 UPDATE_FLAG VARCHAR2(1) := 'Y', -- := FND_API.G_MISS_CHAR,
156 AUTO_ASSIGN_RESOURCES_FLAG VARCHAR2(1) , -- := FND_API.G_MISS_CHAR,
157 PLANNED_FLAG VARCHAR2(1) , -- := FND_API.G_MISS_CHAR,
158 TERRITORY_TYPE_ID NUMBER , -- := FND_API.G_MISS_NUM,
159 PARENT_TERRITORY_ID NUMBER , -- := FND_API.G_MISS_NUM,
160 TEMPLATE_FLAG VARCHAR2(1) := 'N', -- := FND_API.G_MISS_CHAR,
161 TEMPLATE_TERRITORY_ID NUMBER , -- := FND_API.G_MISS_NUM,
162 ESCALATION_TERRITORY_FLAG VARCHAR2(1) := 'N', -- := FND_API.G_MISS_CHAR,
163 ESCALATION_TERRITORY_ID NUMBER , -- := FND_API.G_MISS_NUM,
164 OVERLAp_ALLOWED_FLAG VARCHAR2(1) , -- := FND_API.G_MISS_CHAR,
165 ATTRIBUTE_CATEGORY VARCHAR2(30) , -- := FND_API.G_MISS_CHAR,
166 ATTRIBUTE1 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR,
167 ATTRIBUTE2 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR,
168 ATTRIBUTE3 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR,
169 ATTRIBUTE4 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR,
170 ATTRIBUTE5 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR,
171 ATTRIBUTE6 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR,
172 ATTRIBUTE7 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR,
173 ATTRIBUTE8 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR,
174 ATTRIBUTE9 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR,
175 ATTRIBUTE10 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR,
176 ATTRIBUTE11 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR,
177 ATTRIBUTE12 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR,
178 ATTRIBUTE13 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR,
179 ATTRIBUTE14 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR,
180 ATTRIBUTE15 VARCHAR2(150) , -- := FND_API.G_MISS_CHAR
181 ORG_ID NUMBER , -- := FND_API.G_MISS_NUM,
182 NUM_WINNERS NUMBER,
183 NUM_QUAL NUMBER := 0,
184 TERR_CREATION_FLAG VARCHAR2(1) := NULL,
185 TERRITORY_GROUP_ID NUMBER :=NULL
186 );
187
188 TYPE Terr_All_Tbl_Type IS TABLE OF Terr_All_Rec_Type
189 INDEX BY BINARY_INTEGER;
190
191 G_MISS_Terr_All_Rec Terr_All_Rec_Type;
192
193 G_MISS_Terr_All_Tbl Terr_All_Tbl_Type;
194
195 -- ***************************************************
196 -- Start of Comments
197 -- ---------------------------------------------------
198 --
199 -- Terr_Qual_Rec_Type
200 -- Attributes:
201 -- TERR_QUAL_ID
202 -- TERR_ID
203 -- SEEDED_QUAL_ID
204 -- LAST_UPDATE_DATE
205 -- LAST_UPDATED_BY
206 -- CREATION_DATE
207 -- CREATED_BY
208 -- LAST_UPDATE_LOGIN
209 -- GENERATE_FLAG
210 -- NAME_FLAG
211 -- OVERLAp_ALLOWED_FLAG
212 --
213 -- Notes
214 --
215 --
216 -- End of Comments
217 --
218 TYPE Terr_Qual_Rec_Type IS RECORD
219 ( Rowid VARCHAR2(50) , -- := FND_API.G_MISS_CHAR,
220 TERR_QUAL_ID NUMBER , -- := FND_API.G_MISS_NUM,
221 LAST_UPDATE_DATE DATE , -- := FND_API.G_MISS_DATE,
222 LAST_UPDATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
223 CREATION_DATE DATE , -- := FND_API.G_MISS_DATE,
224 CREATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
225 LAST_UPDATE_LOGIN NUMBER , -- := FND_API.G_MISS_NUM,
226 TERR_ID NUMBER , -- := FND_API.G_MISS_NUM,
227 QUAL_USG_ID NUMBER , -- := FND_API.G_MISS_NUM,
228 USE_TO_NAME_FLAG VARCHAR2(1) , -- := FND_API.G_MISS_CHAR,
229 GENERATE_FLAG VARCHAR2(1) , -- := FND_API.G_MISS_CHAR,
230 OVERLAP_ALLOWED_FLAG VARCHAR2(1) := 'Y', -- := FND_API.G_MISS_CHAR,
231 QUALIFIER_MODE VARCHAR2(30) , -- := FND_API.G_MISS_CHAR,
232 ORG_ID NUMBER -- := FND_API.G_MISS_NUM
233 );
234
235 TYPE Terr_Qual_Tbl_Type IS TABLE OF Terr_Qual_Rec_Type
236 INDEX BY BINARY_INTEGER;
237
238 G_MISS_Terr_Qual_Rec Terr_Qual_Rec_Type;
239
240 G_MISS_Terr_Qual_Tbl Terr_Qual_Tbl_Type;
241
242 -- ***************************************************
243 -- Start of Comments
244 -- ---------------------------------------------------
245 -- Territory values record type: Terr_Values_Rec_Type
246 -- ---------------------------------------------------
247 -- Attributes:
248 -- TERR_VALUE_ID
249 -- LAST_UPDATE_DATE
250 -- LAST_UPDATED_BY
251 -- CREATION_DATE
252 -- CREATED_BY
253 -- LAST_UPDATE_LOGIN
254 -- TERR_QUAL_ID
255 -- INCLUDE_FLAG
256 -- COMPARISON_OPERATOR
257 -- LOW_VALUE_CHAR
258 -- HIGH_VALUE_CHAR
259 -- LOW_VALUE_NUMBER
260 -- HIGH_VALUE_NUMBER
261 -- INTEREST_TYPE_ID
262 -- PRIMARY_INTEREST_CODE_ID
263 -- SECONDARY_INTEREST_CODE_ID
264 -- ORG_ID
265 -- CURRENCY_CODE
266 -- VALUE_SET
267 --
268 --
269 -- Notes
270 --
271 --
272 -- End of Comments
273 --
274 TYPE Terr_Values_Rec_Type IS RECORD
275 (TERR_VALUE_ID NUMBER , -- := FND_API.G_MISS_NUM,
276 LAST_UPDATE_DATE DATE , -- := FND_API.G_MISS_DATE,
277 LAST_UPDATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
278 CREATION_DATE DATE , -- := FND_API.G_MISS_DATE,
279 CREATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
280 LAST_UPDATE_LOGIN NUMBER , -- := FND_API.G_MISS_NUM,
281 TERR_QUAL_ID NUMBER , -- := FND_API.G_MISS_NUM,
282 INCLUDE_FLAG VARCHAR2(15) , -- := FND_API.G_MISS_CHAR,
283 COMPARISON_OPERATOR VARCHAR2(30) , -- := FND_API.G_MISS_CHAR,
284 LOW_VALUE_CHAR VARCHAR2(360) , -- := FND_API.G_MISS_CHAR,
285 HIGH_VALUE_CHAR VARCHAR2(360) , -- := FND_API.G_MISS_CHAR,
286 LOW_VALUE_NUMBER NUMBER , -- := FND_API.G_MISS_NUM,
287 HIGH_VALUE_NUMBER NUMBER , -- := FND_API.G_MISS_NUM,
288 VALUE_SET NUMBER , -- := FND_API.G_MISS_NUM,
289 INTEREST_TYPE_ID NUMBER , -- := FND_API.G_MISS_NUM,
290 PRIMARY_INTEREST_CODE_ID NUMBER , -- := FND_API.G_MISS_NUM,
291 SECONDARY_INTEREST_CODE_ID NUMBER , -- := FND_API.G_MISS_NUM,
292 CURRENCY_CODE VARCHAR2(15) , -- := FND_API.G_MISS_CHAR,
293 ID_USED_FLAG VARCHAR2(1) , -- := FND_API.G_MISS_CHAR,
294 LOW_VALUE_CHAR_ID NUMBER , -- := FND_API.G_MISS_NUM,
295 QUALIFIER_TBL_INDEX NUMBER , -- := FND_API.G_MISS_NUM,
296 ORG_ID NUMBER , -- := FND_API.G_MISS_NUM
297 CNR_GROUP_ID NUMBER ,
298 VALUE1_ID NUMBER ,
299 VALUE2_ID NUMBER ,
300 VALUE3_ID NUMBER ,
301 VALUE4_ID NUMBER
302 );
303
304 TYPE Terr_Values_Tbl_Type IS TABLE OF Terr_Values_Rec_Type
305 INDEX BY BINARY_INTEGER;
306
307 G_MISS_Terr_Values_Rec Terr_Values_Rec_Type;
308
309 G_MISS_Terr_Values_Tbl Terr_Values_Tbl_Type;
310
311
312 -- ***************************************************
313 -- Start of Comments
314 -- ---------------------------------------------------
315 -- Territory source Record: terr_Usgs_rec_type
316 -- ---------------------------------------------------
317 -- Parameters:
318 --
319 -- Required:
320 -- TERR_USG_ID -- primary key sequence
321 -- SOURCE_ID -- Source identifier
322 -- TERR_ID -- Territory identifier
323 -- LAST_UPDATE_DATE -- Part of std who columns
324 -- LAST_UPDATED_BY -- Part of std who columns
325 -- CREATION_DATE -- Part of std who columns
326 -- CREATED_BY -- Part of std who columns
327 -- LAST_UPDATE_LOGIN -- Part of std who columns
328 --
329 -- Defaults:
330 -- Note:
331 --
332 -- End of Comments
333
334 TYPE terr_usgs_rec_type IS RECORD
335 (
336 TERR_USG_ID NUMBER , -- := FND_API.G_MISS_NUM,
337 SOURCE_ID NUMBER , -- := FND_API.G_MISS_NUM,
338 TERR_ID NUMBER , -- := FND_API.G_MISS_NUM,
339 LAST_UPDATE_DATE DATE , -- := FND_API.G_MISS_DATE,
340 LAST_UPDATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
341 CREATION_DATE DATE , -- := FND_API.G_MISS_DATE,
342 CREATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
343 LAST_UPDATE_LOGIN NUMBER , -- := FND_API.G_MISS_NUM,
344 ORG_ID NUMBER -- := FND_API.G_MISS_NUM
345
346 );
347
351 INDEX BY BINARY_INTEGER;
348 G_MISS_TERR_USGS_REC terr_usgs_rec_type;
349
350 TYPE terr_usgs_tbl_type IS TABLE OF terr_usgs_rec_type
352
353 G_MISS_TERR_USGS_TBL terr_usgs_tbl_type;
354
355
356 -- *************************************************************
357 -- Start of Comments
358 -- -------------------------------------------------------------
359 -- Territory qualifier Type Record: TerrQualTypeUsgs_rec_type
360 -- -------------------------------------------------------------
361 -- Parameters:
362 --
363 -- Required:
364 -- TYPE_QUAL_TYPE_USG_ID -- Primary Key sequence
365 -- TERR_TYPE_USG_ID -- Source source id
366 -- QUAL_TYPE_USG_ID -- Type identifier
367 -- LAST_UPDATE_DATE -- Part of std who columns
368 -- LAST_UPDATED_BY -- Part of std who columns
369 -- CREATION_DATE -- Part of std who columns
370 -- CREATED_BY -- Part of std who columns
371 -- LAST_UPDATE_LOGIN -- Part of std who columns
372 --
373 -- Defaults:
374 -- Note:
375 --
376 -- End of Comments
377
378 TYPE terr_qualtypeusgs_rec_type IS RECORD
379 (
380 TERR_QUAL_TYPE_USG_ID NUMBER , -- := FND_API.G_MISS_NUM,
381 TERR_ID NUMBER , -- := FND_API.G_MISS_NUM,
382 QUAL_TYPE_USG_ID NUMBER , -- := FND_API.G_MISS_NUM,
383 LAST_UPDATE_DATE DATE , -- := FND_API.G_MISS_DATE,
384 LAST_UPDATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
385 CREATION_DATE DATE , -- := FND_API.G_MISS_DATE,
386 CREATED_BY NUMBER , -- := FND_API.G_MISS_NUM,
387 LAST_UPDATE_LOGIN NUMBER , -- := FND_API.G_MISS_NUM,
388 ORG_ID NUMBER -- := FND_API.G_MISS_NUM
389 );
390
391 G_MISS_TERR_QUALTYPEUSGS_REC terr_qualtypeusgs_rec_type;
392
393 TYPE terr_qualtypeusgs_tbl_type IS TABLE OF terr_qualtypeusgs_rec_type
394 INDEX BY BINARY_INTEGER;
395
396 G_MISS_TERR_QUALTYPEUSGS_TBL Terr_QualTypeUsgs_tbl_type;
397
398
399 -- ***********************************************************
400 -- Start of Comments
401 -- -----------------------------------------------------------
402 -- Territory OUT NOCOPY Record: terr_all_out_rec
403 -- -----------------------------------------------------------
404 -- Parameters:
405 --
406 -- Required:
407 -- Defaults:
408 -- Note:
409 --
410 -- End of Comments
411
412 TYPE terr_all_out_rec_type IS RECORD
413 (
414 TERR_ID NUMBER ,--:= FND_API.G_MISS_NUM,
415 RETURN_STATUS VARCHAR2(01) --:= FND_API.G_MISS_CHAR
416 );
417
418 G_MISS_TERR_ALL_OUT_REC terr_all_out_rec_type;
419
420
421 TYPE Terr_All_out_tbl_type IS TABLE OF terr_all_out_rec_type
422 INDEX BY BINARY_INTEGER;
423
424 G_MISS_TERR_ALL_OUT_TBL Terr_All_out_tbl_type;
425
426
427 -- ***********************************************************
428 -- Start of Comments
429 -- -----------------------------------------------------------
430 -- Territory usages OUT NOCOPY Record: terr_usgs_out_rec_type
431 -- -----------------------------------------------------------
432 -- Parameters:
433 --
434 -- Required:
435 -- Defaults:
436 -- Note:
437 --
438 -- End of Comments
439
440 TYPE Terr_Usgs_out_rec_type IS RECORD
441 (
442 TERR_USG_ID NUMBER ,--:= FND_API.G_MISS_NUM,
443 RETURN_STATUS VARCHAR2(01) --:= FND_API.G_MISS_CHAR
444 );
445
446 G_MISS_TERR_USGS_OUT_REC terr_usgs_out_rec_type;
447
448 TYPE Terr_Usgs_out_tbl_type IS TABLE OF terr_usgs_out_rec_type
449 INDEX BY BINARY_INTEGER;
450 G_MISS_TERR_USGS_OUT_TBL Terr_Usgs_out_tbl_type;
451
452
453 -- ****************************************************************
454 -- Start of Comments
455 -- ----------------------------------------------------------------
456 -- Territory qualifier type OUT NOCOPY Record: terr_QualTypeUsgs_out_rec
457 -- ----------------------------------------------------------------
458 -- Parameters:
459 --
460 -- Required:
461 -- Defaults:
462 -- Note:
463 --
464 -- End of Comments
465
466 TYPE terr_QualTypeUsgs_out_rec_type IS RECORD
467 (
468 TERR_QUAL_TYPE_USG_ID NUMBER ,--:= FND_API.G_MISS_NUM,
469 RETURN_STATUS VARCHAR2(01) --:= FND_API.G_MISS_CHAR
470 );
471
472 G_MISS_TERRQUALTYPUSGS_OUT_REC terr_QualTypeUsgs_out_rec_type;
473
474 TYPE Terr_QualTypeUsgs_Out_Tbl_Type IS TABLE OF terr_QualTypeUsgs_out_rec_type
475 INDEX BY BINARY_INTEGER;
476 G_MISS_TERRQUALTYPUSGS_OUT_TBL Terr_QualTypeUsgs_Out_Tbl_Type;
477
478 -- ***********************************************************
479 -- Start of Comments
480 -- -----------------------------------------------------------
484 --
481 -- Territory qualifiers OUT NOCOPY Record: terr_Oual_out_rec_Type
482 -- -----------------------------------------------------------
483 -- Parameters:
485 -- Required:
486 -- Defaults:
487 -- Note:
488 --
489 -- End of Comments
490
491 TYPE Terr_Qual_out_rec_type IS RECORD
492 (
493 TERR_QUAL_ID NUMBER ,--:= FND_API.G_MISS_NUM,
494 RETURN_STATUS VARCHAR2(01) --:= FND_API.G_MISS_CHAR
495 );
496
497 G_MISS_TERR_QUAL_OUT_REC Terr_Qual_out_rec_type;
498
499 TYPE Terr_Qual_out_tbl_type IS TABLE OF Terr_Qual_out_rec_type
500 INDEX BY BINARY_INTEGER;
501 G_MISS_TERR_QUAL_OUT_TBL Terr_Qual_Out_tbl_type;
502
503
504 -- ***********************************************************
505 -- Start of Comments
506 -- -----------------------------------------------------------
507 -- Territory values OUT NOCOPY Record: terr_values_out_rec_type
508 -- -----------------------------------------------------------
509 -- Parameters:
510 --
511 -- Required:
512 -- Defaults:
513 -- Note:
514 --
515 -- End of Comments
516
517 TYPE Terr_Values_out_rec_type IS RECORD
518 (
519 TERR_VALUE_ID NUMBER ,-- := FND_API.G_MISS_NUM,
520 RETURN_STATUS VARCHAR2(01) -- := FND_API.G_MISS_CHAR
521 );
522
523 G_MISS_TERR_VALUES_OUT_REC terr_values_out_rec_type;
524
525
526 TYPE Terr_Values_out_tbl_type IS TABLE OF terr_values_out_rec_type
527 INDEX BY BINARY_INTEGER;
528
529 G_MISS_TERR_VALUES_OUT_TBL Terr_Values_out_tbl_type;
530
531
532 -- ***************************************************
533 -- start of comments
534 -- ***************************************************
535 -- API name : Create_Territory
536 -- Type : PUBLIC
537 -- Function : To create Territories - which inludes the creation of following
538 -- Territory Header, Territory Qualifier, terr Usages, qualifier type usages
539 -- Territory Qualifier Values and Assign Resources
540 -- Pre-reqs :
541 -- Parameters:
542 -- IN :
543 -- Required
544 -- Parameter Name Data Type Default
545 -- p_Api_Version_Number NUMBER
546 -- p_Terr_All_Rec Terr_All_Rec_Type := G_Miss_Terr_All_Rec
547 -- p_Terr_Usgs_Tbl Terr_Usgs_Tbl_Type := G_MISS_Terr_Usgs_Tbl
548 -- p_Terr_QualTypeUsgs_Tbl Terr_QualTypeUsgs_Tbl_Type := G_Miss_Terr_QualTypeUsgs_Tbl
549 -- p_Terr_Qual_Tbl Terr_Qual_Tbl_Type := G_Miss_Terr_Qual_Tbl
550 -- p_Terr_Values_Tbl Terr_Values_Tbl_Type := G_Miss_Terr_Values_Tbl
551 --
552 -- Optional
553 -- Parameter Name Data Type Default
554 -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
555 -- p_Commit VARCHAR2 := FND_API.G_FALSE
556 --
557 -- OUT NOCOPY :
558 -- Parameter Name Data Type
559 -- x_Return_Status VARCHAR2(1)
560 -- x_Msg_Count NUMBER
561 -- x_Msg_Data VARCHAR2(2000)
562 -- x_Terr_Id NUMBER
563 -- x_Terr_Usgs_Out_Tbl Terr_Usgs_Out_Tbl,
564 -- x_Terr_QualTypeUsgs_Out_Tbl Terr_QualTypeUsgs_Out_Tbl,
565 -- x_Terr_Qual_Out_Tbl Terr_Qual_Out_Tbl,
566 -- x_Terr_Values_Out_Tbl Terr_Values_Out_Tbl
567 --
568 --
569 -- Notes:
570 --
571 --
572 -- End of Comments
573 --
574 PROCEDURE Create_Territory
575 (p_Api_Version_Number IN NUMBER,
576 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
577 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
578 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
579 x_Return_Status OUT NOCOPY VARCHAR2,
580 x_Msg_Count OUT NOCOPY NUMBER,
581 x_Msg_Data OUT NOCOPY VARCHAR2,
582 p_Terr_All_Rec IN Terr_All_Rec_Type := G_Miss_Terr_All_Rec,
583 p_Terr_Usgs_Tbl IN Terr_Usgs_Tbl_Type := G_MISS_Terr_Usgs_Tbl,
584 p_Terr_QualTypeUsgs_Tbl IN Terr_QualTypeUsgs_Tbl_Type := G_Miss_Terr_QualTypeUsgs_Tbl,
585 p_Terr_Qual_Tbl IN Terr_Qual_Tbl_Type := G_Miss_Terr_Qual_Tbl,
586 p_Terr_Values_Tbl IN Terr_Values_Tbl_Type := G_Miss_Terr_Values_Tbl,
587 x_Terr_Id OUT NOCOPY NUMBER,
588 x_Terr_Usgs_Out_Tbl OUT NOCOPY Terr_Usgs_Out_Tbl_Type,
589 x_Terr_QualTypeUsgs_Out_Tbl OUT NOCOPY Terr_QualTypeUsgs_Out_Tbl_Type,
590 x_Terr_Qual_Out_Tbl OUT NOCOPY Terr_Qual_Out_Tbl_Type,
591 x_Terr_Values_Out_Tbl OUT NOCOPY Terr_Values_Out_Tbl_Type);
592
593
594 -- ***************************************************
595 -- start of comments
596 -- ***************************************************
597 --
598 -- API name : Delete_Territory
599 -- Type : PUBLIC
600 -- Function : To delete Territories - which would also delete
601 -- Territory Header, Territory Qualifier,
605 -- Parameters:
602 -- Territory Qualifier Values and Resources.
603 --
604 -- Pre-reqs :
606 -- IN :
607 -- Required
608 -- Parameter Name Data Type Default
609 -- p_Api_Version_Number NUMBER
610 -- p_Terr_Id NUMBER
611 --
612 -- Optional
613 -- Parameter Name Data Type Default
614 -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
615 -- p_Commit VARCHAR2 FND_API.G_FALSE
616 --
617 -- OUT NOCOPY :
618 -- Parameter Name Data Type
619 -- X_Return_Status VARCHAR2(1)
620 -- X_Msg_Count NUMBER
621 -- X_Msg_Data VARCHAR2(2000)
622 --
623 --
624 -- Notes:
625 -- Rules for deletion have to be very strict.
626 --
627 -- End of Comments
628 --
629 PROCEDURE Delete_Territory
630 (p_Api_Version_Number IN NUMBER,
631 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
632 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
633 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
634 X_Return_Status OUT NOCOPY VARCHAR2,
635 X_Msg_Count OUT NOCOPY NUMBER,
636 X_Msg_Data OUT NOCOPY VARCHAR2,
637 p_Terr_Id IN NUMBER);
638
639
640 -- ***************************************************
641 -- start of comments
642 -- ***************************************************
643 -- API name : Update_Territory
644 -- Type : PUBLIC
645 -- Function : To update existINg Territories - which includes updates to the following tables
646 -- Territory Header, Territory Qualifier, terr Usages, qualifier type usages
647 -- Territory Qualifier Values and Assign Resources
648 -- Pre-reqs :
649 -- Parameters:
650 -- IN :
651 -- Required
652 -- Parameter Name Data Type Default
653 -- p_Api_Version_Number NUMBER
654 -- p_Terr_All_Rec Terr_All_Rec_Type := G_Miss_Terr_All_Rec
655 -- p_Terr_Usgs_Tbl Terr_Usgs_Tbl_Type := G_MISS_Terr_Usgs_Tbl
656 -- p_Terr_QualTypeUsgs_Tbl Terr_QualTypeUsgs_Tbl_Type := G_Miss_Terr_QualTypeUsgs_Tbl
657 -- p_Terr_Qual_Tbl Terr_Qual_Tbl_Type := G_Miss_Terr_Qual_Tbl
658 -- p_Terr_Values_Tbl Terr_Values_Tbl_Type := G_Miss_Terr_Values_Tbl
659 --
660 -- Optional
661 -- Parameter Name Data Type Default
662 -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
663 -- p_Commit VARCHAR2 := FND_API.G_FALSE
664 --
665 -- OUT NOCOPY :
666 -- Parameter Name Data Type
667 -- X_Return_Status VARCHAR2(1)
668 -- X_Msg_Count NUMBER
669 -- X_Msg_Data VARCHAR2(2000)
670 -- X_Terr_All_Out_Rec Terr_All_Out_Rec
671 -- X_Terr_Usgs_Out_Tbl Terr_Usgs_Out_Tbl,
672 -- X_Terr_QualTypeUsgs_Out_Tbl Terr_QualTypeUsgs_Out_Tbl,
673 -- X_Terr_Qual_Out_Tbl Terr_Qual_Out_Tbl,
674 -- X_Terr_Values_Out_Tbl Terr_Values_Out_Tbl
675 --
676 --
677 -- Notes:
678 --
679 --
680 -- End of Comments
681 --
682 PROCEDURE Update_Territory
683 (p_Api_Version_Number IN NUMBER,
684 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
685 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
686 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
687 X_Return_Status OUT NOCOPY VARCHAR2,
688 X_Msg_Count OUT NOCOPY NUMBER,
689 X_Msg_Data OUT NOCOPY VARCHAR2,
690 p_Terr_All_Rec IN Terr_All_Rec_Type := G_Miss_Terr_All_Rec,
691 p_Terr_Usgs_Tbl IN Terr_Usgs_Tbl_Type := G_MISS_Terr_Usgs_Tbl,
692 p_Terr_QualTypeUsgs_Tbl IN Terr_QualTypeUsgs_Tbl_Type := G_Miss_Terr_QualTypeUsgs_Tbl,
693 p_Terr_Qual_Tbl IN Terr_Qual_Tbl_Type := G_Miss_Terr_Qual_Tbl,
694 p_Terr_Values_Tbl IN Terr_Values_Tbl_Type := G_Miss_Terr_Values_Tbl,
695 X_Terr_All_Out_Rec OUT NOCOPY Terr_All_Out_Rec_Type,
696 X_Terr_Usgs_Out_Tbl OUT NOCOPY Terr_Usgs_Out_Tbl_Type,
697 X_Terr_QualTypeUsgs_Out_Tbl OUT NOCOPY Terr_QualTypeUsgs_Out_Tbl_Type,
698 X_Terr_Qual_Out_Tbl OUT NOCOPY Terr_Qual_Out_Tbl_Type,
699 X_Terr_Values_Out_Tbl OUT NOCOPY Terr_Values_Out_Tbl_Type);
700
701
702 -- ***************************************************
703 -- start of comments
704 -- ***************************************************
705 --
706 -- API name : Deactivate_Territory
707 -- Type : PUBLIC
708 -- Function : To deactivate Territories - this API also deactivates
709 -- any sub-territories of this territory.
710 --
711 -- Pre-reqs :
712 -- Parameters:
713 -- IN :
714 -- Required
715 -- Parameter Name Data Type Default
716 -- p_Api_Version_Number NUMBER
720 -- Parameter Name Data Type Default
717 -- p_Terr_Id NUMBER
718 --
719 -- Optional
721 -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
722 -- p_Commit VARCHAR2 FND_API.G_FALSE
723 --
724 -- OUT NOCOPY :
725 -- Parameter Name Data Type
726 -- x_Return_Status VARCHAR2(1)
727 -- x_Msg_Count NUMBER
728 -- x_Msg_Data VARCHAR2(2000)
729 --
730 --
731 -- Notes:
732 --
733 --
734 -- End of Comments
735 --
736 PROCEDURE Deactivate_Territory
737 (p_api_version_number IN NUMBER,
738 p_INit_msg_list IN VARCHAR2 := FND_API.G_FALSE,
739 p_commit IN VARCHAR2 := FND_API.G_FALSE,
740 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
741 x_return_status OUT NOCOPY VARCHAR2,
742 x_msg_count OUT NOCOPY NUMBER,
743 x_msg_data OUT NOCOPY VARCHAR2,
744 P_terr_id IN NUMBER);
745
746 --
747 -- ***************************************************
748 -- start of comments
749 -- ***************************************************
750 -- API name : Create_Terr_Header
751 -- Type : PUBLIC
752 -- Function : To create Territories - which inludes the creation of following
753 -- Territory Header, Territory Usages, Territory qualifier type usages
754 -- table.
755 -- Pre-reqs :
756 -- Parameters:
757 -- IN :
758 -- Required
759 -- Parameter Name Data Type Default
760 -- P_Api_Version_Number NUMBER
761 -- P_Terr_All_Rec Terr_All_Rec_Type := G_Miss_Terr_All_Rec
762 -- P_Terr_Usgs_Tbl Terr_Usgs_Tbl_Type := G_MISS_Terr_Usgs_Tbl
763 -- P_Terr_QualTypeUsgs_Tbl Terr_QualTypeUsgs_Tbl_Type := G_Miss_Terr_QualTypeUsgs_Tbl
764 --
765 -- Optional
766 -- Parameter Name Data Type Default
767 -- P_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
768 -- P_Commit VARCHAR2 := FND_API.G_FALSE
769 --
770 -- OUT NOCOPY :
771 -- Parameter Name Data Type
772 -- X_Return_Status VARCHAR2(1)
773 -- X_Msg_Count NUMBER
774 -- X_Msg_Data VARCHAR2(2000)
775 -- X_Terr_All_Out_Rec Terr_All_Out_Rec
776 -- X_Terr_Usgs_Out_Tbl Terr_Usgs_Out_Tbl,
777 -- X_Terr_QualTypeUsgs_Out_Tbl Terr_QualTypeUsgs_Out_Tbl,
778 --
779 --
780 -- Notes:
781 --
782 --
783 -- End of Comments
784 --
785 PROCEDURE Create_Territory_Header
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 P_Terr_All_Rec IN Terr_All_Rec_Type := G_Miss_Terr_All_Rec,
791 P_Terr_Usgs_Tbl IN Terr_Usgs_Tbl_Type := G_MISS_Terr_Usgs_Tbl,
792 P_Terr_QualTypeUsgs_Tbl IN Terr_QualTypeUsgs_Tbl_Type := G_Miss_Terr_QualTypeUsgs_Tbl,
793 X_Return_Status OUT NOCOPY VARCHAR2,
794 X_Msg_Count OUT NOCOPY NUMBER,
795 X_Msg_Data OUT NOCOPY VARCHAR2,
796 X_Terr_All_Out_Rec OUT NOCOPY Terr_All_Out_Rec_Type,
797 X_Terr_Usgs_Out_Tbl OUT NOCOPY Terr_Usgs_Out_Tbl_Type,
798 X_Terr_QualTypeUsgs_Out_Tbl OUT NOCOPY Terr_QualTypeUsgs_Out_Tbl_Type);
799
800 PROCEDURE Create_Territory_Record
801 ( P_Api_Version_Number IN NUMBER,
802 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
803 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
804 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
805 P_Terr_All_Rec IN Terr_All_Rec_Type := G_Miss_Terr_All_Rec,
806 X_Return_Status OUT NOCOPY VARCHAR2,
807 X_Msg_Count OUT NOCOPY NUMBER,
808 X_Msg_Data OUT NOCOPY VARCHAR2,
809 X_Terr_Id OUT NOCOPY NUMBER,
810 X_Terr_All_Out_Rec OUT NOCOPY Terr_All_Out_Rec_Type
811 );
812
813
814 PROCEDURE Create_Territory_Usages
815 ( P_Api_Version_Number IN NUMBER,
816 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
817 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
818 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
819 P_Terr_Id IN NUMBER,
820 P_Terr_Usgs_Tbl IN Terr_Usgs_Tbl_Type := G_MISS_Terr_Usgs_Tbl,
821 X_Return_Status OUT NOCOPY VARCHAR2,
822 X_Msg_Count OUT NOCOPY NUMBER,
823 X_Msg_Data OUT NOCOPY VARCHAR2,
824 X_Terr_Usgs_Out_Tbl OUT NOCOPY Terr_Usgs_Out_Tbl_Type
825 );
826
827
828 PROCEDURE Create_Terr_QualType_Usage
832 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
829 (P_Api_Version_Number IN NUMBER,
830 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
831 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
833 P_Terr_Id IN NUMBER,
834 P_Terr_QualTypeUsgs_Rec IN Terr_QualTypeUsgs_Rec_Type := G_Miss_Terr_QualTypeUsgs_Rec,
835 X_Return_Status OUT NOCOPY VARCHAR2,
836 X_Msg_Count OUT NOCOPY NUMBER,
837 X_Msg_Data OUT NOCOPY VARCHAR2,
838 X_Terr_QualTypeUsgs_Id OUT NOCOPY NUMBER,
839 X_Terr_QualTypeUsgs_Out_Rec OUT NOCOPY Terr_QualTypeUsgs_Out_Rec_Type);
840
841
842 PROCEDURE Create_Terr_QualType_Usage
843 (P_Api_Version_Number IN NUMBER,
844 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
845 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
846 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
847 P_Terr_Id IN NUMBER,
848 P_Terr_QualTypeUsgs_Tbl IN Terr_QualTypeUsgs_Tbl_Type := G_Miss_Terr_QualTypeUsgs_Tbl,
849 X_Return_Status OUT NOCOPY VARCHAR2,
850 X_Msg_Count OUT NOCOPY NUMBER,
851 X_Msg_Data OUT NOCOPY VARCHAR2,
852 X_Terr_QualTypeUsgs_Out_Tbl OUT NOCOPY Terr_QualTypeUsgs_Out_Tbl_Type);
853
854
855 PROCEDURE Create_Terr_Qualifier
856 (P_Api_Version_Number IN NUMBER,
857 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
858 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
859 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
860 P_Terr_Id IN NUMBER,
861 P_Terr_Qual_Rec IN Terr_Qual_Rec_Type := G_Miss_Terr_Qual_Rec,
862 X_Return_Status OUT NOCOPY VARCHAR2,
863 X_Msg_Count OUT NOCOPY NUMBER,
864 X_Msg_Data OUT NOCOPY VARCHAR2,
865 X_Terr_Qual_Id OUT NOCOPY NUMBER,
866 X_Terr_Qual_Out_Rec OUT NOCOPY Terr_Qual_Out_Rec_Type);
867
868
869
870 PROCEDURE Create_Terr_Qualifier
871 (P_Api_Version_Number IN NUMBER,
872 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
873 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
874 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
875 P_Terr_Id IN NUMBER,
876 P_Terr_Qual_Tbl IN Terr_Qual_Tbl_Type := G_Miss_Terr_Qual_Tbl,
877 X_Return_Status OUT NOCOPY VARCHAR2,
878 X_Msg_Count OUT NOCOPY NUMBER,
879 X_Msg_Data OUT NOCOPY VARCHAR2,
880 X_Terr_Qual_Out_Tbl OUT NOCOPY Terr_Qual_Out_Tbl_Type);
881
882 PROCEDURE Create_Terr_Value
883 (P_Api_Version_Number IN NUMBER,
884 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
885 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
886 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
887 P_Terr_Id IN NUMBER,
888 p_terr_qual_id IN NUMBER,
889 P_Terr_Value_Rec IN Terr_Values_Rec_Type := G_Miss_Terr_Values_Rec,
890 X_Return_Status OUT NOCOPY VARCHAR2,
891 X_Msg_Count OUT NOCOPY NUMBER,
892 X_Msg_Data OUT NOCOPY VARCHAR2,
893 X_Terr_Value_Id OUT NOCOPY NUMBER,
894 X_Terr_Value_Out_Rec OUT NOCOPY Terr_Values_Out_Rec_Type);
895
896
897 PROCEDURE Create_Terr_Value
898 (P_Api_Version_Number IN NUMBER,
899 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
900 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
901 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
902 P_Terr_Id IN NUMBER,
903 p_terr_qual_id IN NUMBER,
904 P_Terr_Value_Tbl IN Terr_Values_Tbl_Type := G_Miss_Terr_Values_Tbl,
905 X_Return_Status OUT NOCOPY VARCHAR2,
906 X_Msg_Count OUT NOCOPY NUMBER,
907 X_Msg_Data OUT NOCOPY VARCHAR2,
908 X_Terr_Value_Out_Tbl OUT NOCOPY Terr_Values_Out_Tbl_Type);
909
910
911 PROCEDURE Update_Territory_Record
912 ( P_Api_Version_Number IN NUMBER,
913 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
914 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
915 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
916 P_Terr_All_Rec IN Terr_All_Rec_Type := G_Miss_Terr_All_Rec,
917 X_Return_Status OUT NOCOPY VARCHAR2,
918 X_Msg_Count OUT NOCOPY NUMBER,
919 X_Msg_Data OUT NOCOPY VARCHAR2,
920 X_Terr_All_Out_Rec OUT NOCOPY Terr_All_Out_Rec_Type
921 );
922
923 PROCEDURE Update_Territory_Usages
924 ( P_Api_Version_Number IN NUMBER,
928 P_Terr_Usgs_Rec IN Terr_Usgs_Rec_Type := G_MISS_TERR_USGS_REC,
925 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
926 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
927 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
929 X_Return_Status OUT NOCOPY VARCHAR2,
930 X_Msg_Count OUT NOCOPY NUMBER,
931 X_Msg_Data OUT NOCOPY VARCHAR2,
932 X_Terr_Usgs_Out_Rec OUT NOCOPY Terr_Usgs_Out_Rec_Type);
933
934 PROCEDURE Update_Territory_Usages
935 ( P_Api_Version_Number IN NUMBER,
936 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
937 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
938 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
939 P_Terr_Usgs_Tbl IN Terr_Usgs_Tbl_Type := G_MISS_Terr_Usgs_Tbl,
940 X_Return_Status OUT NOCOPY VARCHAR2,
941 X_Msg_Count OUT NOCOPY NUMBER,
942 X_Msg_Data OUT NOCOPY VARCHAR2,
943 X_Terr_Usgs_Out_Tbl OUT NOCOPY Terr_Usgs_Out_Tbl_Type
944 );
945
946 PROCEDURE Update_Terr_QualType_Usage
947 ( P_Api_Version_Number IN NUMBER,
948 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
949 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
950 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
951 P_Terr_QualTypeUsgs_Rec IN Terr_QualTypeUsgs_Rec_Type := G_Miss_Terr_QualTypeUsgs_Rec,
952 X_Return_Status OUT NOCOPY VARCHAR2,
953 X_Msg_Count OUT NOCOPY NUMBER,
954 X_Msg_Data OUT NOCOPY VARCHAR2,
955 X_Terr_QualTypeUsgs_Out_Rec OUT NOCOPY Terr_QualTypeUsgs_Out_Rec_Type
956 );
957
958 PROCEDURE Update_Terr_QualType_Usage
959 ( P_Api_Version_Number IN NUMBER,
960 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
961 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
962 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
963 P_Terr_QualTypeUsgs_Tbl IN Terr_QualTypeUsgs_Tbl_Type := G_Miss_Terr_QualTypeUsgs_Tbl,
964 X_Return_Status OUT NOCOPY VARCHAR2,
965 X_Msg_Count OUT NOCOPY NUMBER,
966 X_Msg_Data OUT NOCOPY VARCHAR2,
967 X_Terr_QualTypeUsgs_Out_Tbl OUT NOCOPY Terr_QualTypeUsgs_Out_Tbl_Type
968 );
969
970 PROCEDURE Update_Terr_Qualifier
971 ( P_Api_Version_Number IN NUMBER,
972 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
973 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
974 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
975 P_Terr_Qual_Rec IN Terr_Qual_Rec_Type := G_Miss_Terr_Qual_Rec,
976 X_Return_Status OUT NOCOPY VARCHAR2,
977 X_Msg_Count OUT NOCOPY NUMBER,
978 X_Msg_Data OUT NOCOPY VARCHAR2,
979 X_Terr_Qual_Out_Rec OUT NOCOPY Terr_Qual_Out_Rec_Type
980 );
981
982 PROCEDURE Update_Terr_Qualifier
983 ( P_Api_Version_Number IN NUMBER,
984 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
985 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
986 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
987 P_Terr_Qual_Tbl IN Terr_Qual_Tbl_Type := G_Miss_Terr_Qual_Tbl,
988 X_Return_Status OUT NOCOPY VARCHAR2,
989 X_Msg_Count OUT NOCOPY NUMBER,
990 X_Msg_Data OUT NOCOPY VARCHAR2,
991 X_Terr_Qual_Out_Tbl OUT NOCOPY Terr_Qual_Out_Tbl_Type
992 );
993
994 PROCEDURE Update_Terr_Value
995 ( P_Api_Version_Number IN NUMBER,
996 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
997 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
998 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
999 P_Terr_Value_Rec IN Terr_Values_Rec_Type := G_Miss_Terr_Values_Rec,
1000 X_Return_Status OUT NOCOPY VARCHAR2,
1001 X_Msg_Count OUT NOCOPY NUMBER,
1002 X_Msg_Data OUT NOCOPY VARCHAR2,
1003 X_Terr_Value_Out_Rec OUT NOCOPY Terr_Values_Out_Rec_Type
1004 );
1005
1006
1007 --Overloaded to take table type input parameter
1008 PROCEDURE Update_Terr_Value
1009 ( P_Api_Version_Number IN NUMBER,
1010 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1011 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1012 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1013 P_Terr_Value_Tbl IN Terr_Values_Tbl_Type := G_Miss_Terr_Values_Tbl,
1014 X_Return_Status OUT NOCOPY VARCHAR2,
1015 X_Msg_Count OUT NOCOPY NUMBER,
1016 X_Msg_Data OUT NOCOPY VARCHAR2,
1020
1017 X_Terr_Value_Out_Tbl OUT NOCOPY Terr_Values_Out_Tbl_Type
1018 );
1019
1021 PROCEDURE Delete_territory_Record
1022 (P_Api_Version_Number IN NUMBER,
1023 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1024 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1025 P_Terr_Id IN NUMBER,
1026 X_Return_Status OUT NOCOPY VARCHAR2,
1027 X_Msg_Count OUT NOCOPY VARCHAR2,
1028 X_Msg_Data OUT NOCOPY VARCHAR2);
1029
1030 PROCEDURE Delete_Territory_Usages
1031 (P_Api_Version_Number IN NUMBER,
1032 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1033 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1034 P_Terr_usg_Id IN NUMBER,
1035 X_Return_Status OUT NOCOPY VARCHAR2,
1036 X_Msg_Count OUT NOCOPY VARCHAR2,
1037 X_Msg_Data OUT NOCOPY VARCHAR2);
1038
1039 PROCEDURE Delete_Terr_QualType_Usage
1040 (P_Api_Version_Number IN NUMBER,
1041 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1042 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1043 P_Terr_Qual_Type_Usg_Id IN NUMBER,
1044 X_Return_Status OUT NOCOPY VARCHAR2,
1045 X_Msg_Count OUT NOCOPY VARCHAR2,
1046 X_Msg_Data OUT NOCOPY VARCHAR2);
1047
1048 PROCEDURE Delete_Terr_Qualifier
1049 (P_Api_Version_Number IN NUMBER,
1050 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1051 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1052 P_Terr_Qual_Id IN NUMBER,
1053 X_Return_Status OUT NOCOPY VARCHAR2,
1054 X_Msg_Count OUT NOCOPY VARCHAR2,
1055 X_Msg_Data OUT NOCOPY VARCHAR2);
1056
1057
1058 PROCEDURE Delete_Terr_Value
1059 (P_Api_Version_Number IN NUMBER,
1060 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1061 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1062 P_Terr_Value_Id IN NUMBER,
1063 X_Return_Status OUT NOCOPY VARCHAR2,
1064 X_Msg_Count OUT NOCOPY VARCHAR2,
1065 X_Msg_Data OUT NOCOPY VARCHAR2);
1066 ----------------------------------------------------------------------
1067 -- A generic routine to validate foreign keys
1068 ----------------------------------------------------------------------
1069 PROCEDURE Validate_Foreign_Key
1070 (p_Terr_All_Rec IN Terr_All_Rec_Type := G_Miss_Terr_All_Rec,
1071 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1072 x_Return_Status OUT NOCOPY VARCHAR2,
1073 x_msg_count OUT NOCOPY NUMBER,
1074 x_msg_data OUT NOCOPY VARCHAR2 );
1075
1076 ----------------------------------------------------------------------
1077 -- This procedure will check whether the qualifiers passed are
1078 -- valid.
1079 ----------------------------------------------------------------------
1080 PROCEDURE Validate_Qualifier
1081 (p_Terr_Id IN NUMBER,
1082 P_Terr_Qual_Rec IN Terr_Qual_Rec_Type := G_Miss_Terr_Qual_Rec,
1083 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1084 x_Return_Status OUT NOCOPY VARCHAR2,
1085 x_msg_count OUT NOCOPY NUMBER,
1086 x_msg_data OUT NOCOPY VARCHAR2 );
1087
1088 ---------------------------------------------------------------------
1089 -- Validae the Territory record
1090 ---------------------------------------------------------------------
1091 -- Columns Validated
1092 -- Validate the foreign key if specified
1093 -- Validate to make sure the territory name is specified
1094 -- Validate end date
1095 ---------------------------------------------------------------------
1096 PROCEDURE Validate_Territory_Record
1097 (p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1098 x_Return_Status OUT NOCOPY VARCHAR2,
1099 x_msg_count OUT NOCOPY NUMBER,
1100 x_msg_data OUT NOCOPY VARCHAR2,
1101 p_Terr_All_Rec IN Terr_All_Rec_Type := G_Miss_Terr_All_Rec);
1102
1103 ---------------------------------------------------------------------
1104 -- Validae the Territory Usage
1105 ---------------------------------------------------------------------
1106 -- Columns Validated
1107 -- Make sure a Territory Usage is specified
1108 -- Make sure the Territory Id is valid
1109 -- Make sure the territory usage Id is Valid
1110 ---------------------------------------------------------------------
1111 PROCEDURE Validate_Territory_Usage
1112 (p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1113 x_Return_Status OUT NOCOPY VARCHAR2,
1114 x_msg_count OUT NOCOPY NUMBER,
1115 x_msg_data OUT NOCOPY VARCHAR2,
1116 p_Terr_Usgs_Rec IN Terr_Usgs_Rec_Type := G_MISS_Terr_Usgs_Rec,
1117 p_Terr_Id IN NUMBER);
1118
1119
1120 ---------------------------------------------------------------------
1121 -- Validate the Territory Qualifer Type Usage
1122 ---------------------------------------------------------------------
1126 -- Make sure the QUAL_TYPE_USG_ID is valid
1123 -- Columns Validated
1124 -- Make sure a Territory Qual Type Usage is specified
1125 -- Make sure the Territory Id is valid
1127 ---------------------------------------------------------------------
1128 PROCEDURE Validate_Terr_Qtype_Usage
1129 (p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1130 x_Return_Status OUT NOCOPY VARCHAR2,
1131 x_msg_count OUT NOCOPY NUMBER,
1132 x_msg_data OUT NOCOPY VARCHAR2,
1133 p_Terr_QualTypeUsgs_Rec IN Terr_QualTypeUsgs_Rec_Type := G_Miss_Terr_QualTypeUsgs_Rec,
1134 p_Terr_Id IN NUMBER);
1135
1136 ---------------------------------------------------------------------
1137 -- Validate the Territory Qualifer Values passed in
1138 ---------------------------------------------------------------------
1139 -- Columns Validated
1140 -- Make sure the values are in the right columns as per the
1141 -- qualifer setup
1142 -- Eg:
1143 -- If the qualifer, diplay_type = 'CHAR' and
1144 -- col1_data_type = 'NUMBER'
1145 -- then make sure the ID is passed in LOW_VALUE_CHAR_ID
1146 --
1147 --
1148 ---------------------------------------------------------------------
1149 PROCEDURE Validate_terr_Value_Rec
1150 (p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1151 x_Return_Status OUT NOCOPY VARCHAR2,
1152 x_msg_count OUT NOCOPY NUMBER,
1153 x_msg_data OUT NOCOPY VARCHAR2,
1154 p_terr_qual_id IN NUMBER,
1155 p_Terr_Value_Rec IN Terr_Values_Rec_Type := G_Miss_Terr_Values_Rec);
1156
1157
1158 ---------------------------------------------------------------------
1159 -- Get_Max_Rank
1160 ---------------------------------------------------------------------
1161 --
1162 -- Gets the maximum rank of a particular Level
1163 ---------------------------------------------------------------------
1164 PROCEDURE Get_Max_Rank
1165 (p_Parent_Terr_Id IN NUMBER,
1166 p_Source_Id IN NUMBER,
1167 X_Rank OUT NOCOPY NUMBER);
1168
1169
1170 ---------------------------------------------------------------------
1171 -- Procedure
1172 -- Gen_Template_Territories
1173 --
1174 -- Purpose
1175 -- Generates territories for a template.
1176 --
1177 -- Argument: p_terr_template_id
1178 -- If the template id is passed, all territories which have been previously
1179 -- generated from this template and not updated manually will be deleted
1180 -- first.
1181 PROCEDURE Gen_Template_Territories (
1182 p_Api_Version_Number IN NUMBER,
1183 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1184 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1185 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1186 p_Template_Terr_Id IN NUMBER,
1187 x_Return_Status OUT NOCOPY VARCHAR2,
1188 x_Msg_Count OUT NOCOPY NUMBER,
1189 x_Msg_Data OUT NOCOPY VARCHAR2,
1190 x_num_gen_terr OUT NOCOPY NUMBER
1191 );
1192 ---------------------------------------------------------------------
1193 -- Procedure
1194 -- Copy_Territory
1195 --
1196 -- Purpose
1197 -- Makes a copy of a territory.
1198 --
1199 -- Argument: p_copy_source_terr_id - terr_id of territory to be copied
1200 -- p_new_terr_name - name of the new copied territory
1201 -- p_copy_rsc_flag - indicates whether resources assigned
1202 -- to territory are to be copied
1203 -- p_copy_hierarchy_flag - indicates whether the territory hierarchy
1204 -- below this territory are to be copied
1205 -- p_first_terr_node_flag - indicates whether this is the first node to be copied
1206
1207 PROCEDURE Copy_Territory (
1208 p_Api_Version_Number IN NUMBER,
1209 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1210 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1211 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1212 p_copy_source_terr_Id IN NUMBER,
1213 p_new_terr_rec IN Terr_All_Rec_Type,
1214 p_copy_rsc_flag IN VARCHAR2 := 'N',
1215 p_copy_hierarchy_flag IN VARCHAR2 := 'N',
1216 p_first_terr_node_flag IN VARCHAR2 := 'N',
1217 x_Return_Status OUT NOCOPY VARCHAR2,
1218 x_Msg_Count OUT NOCOPY NUMBER,
1219 x_Msg_Data OUT NOCOPY VARCHAR2,
1220 x_Terr_Id OUT NOCOPY NUMBER
1221 );
1222
1223
1224 ---------------------------------------------------------------------
1225 -- Overlap Exists
1226 ---------------------------------------------------------------------
1227 --
1228 -- Check whether any OVERLAP exists for a particular
1229 -- qualifier usage value passed of a particular parent
1230 -- territory. This si because we only check for overlap
1231 -- under a single parent
1232 ---------------------------------------------------------------------
1233 FUNCTION Overlap_Exists(p_Parent_Terr_Id IN NUMBER,
1237
1234 p_Qual_Usg_Id IN NUMBER,
1235 p_terr_value_record IN jtf_terr_values%ROWTYPE )
1236 RETURN VARCHAR2;
1238
1239 -- jdochert 09/09
1240 -- check for Unique Key constraint violation on JTF_TERR_USGS table
1241 PROCEDURE validate_terr_usgs_UK(
1242 p_Terr_Id IN NUMBER,
1243 p_Source_Id IN NUMBER,
1244 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1245 x_Return_Status OUT NOCOPY VARCHAR2,
1246 x_msg_count OUT NOCOPY NUMBER,
1247 x_msg_data OUT NOCOPY VARCHAR2 );
1248
1249 -- jdochert 09/09
1250 -- check for Unique Key constraint violation on JTF_TERR_QTYPE_USGS table
1251 PROCEDURE validate_terr_qtype_usgs_UK(
1252 p_Terr_Id IN NUMBER,
1253 p_Qual_Type_Usg_Id IN NUMBER,
1254 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1255 x_Return_Status OUT NOCOPY VARCHAR2,
1256 x_msg_count OUT NOCOPY NUMBER,
1257 x_msg_data OUT NOCOPY VARCHAR2 );
1258
1259 -- jdochert 10/04
1260 -- check that Number of Winners is valid for this territory
1261 PROCEDURE validate_num_winners(
1262 p_Terr_All_Rec IN Terr_All_Rec_Type := G_Miss_Terr_All_Rec,
1263 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1264 x_Return_Status OUT NOCOPY VARCHAR2,
1265 x_msg_count OUT NOCOPY NUMBER,
1266 x_msg_data OUT NOCOPY VARCHAR2,
1267 x_reason OUT NOCOPY VARCHAR2 );
1268
1269 -- jdochert 06/08/01
1270 -- check that parent is not already a
1271 -- child of this territory: circular reference check
1272 PROCEDURE validate_parent(
1273 p_Terr_All_Rec IN Terr_All_Rec_Type := G_Miss_Terr_All_Rec,
1274 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1275 x_Return_Status OUT NOCOPY VARCHAR2,
1276 x_msg_count OUT NOCOPY NUMBER,
1277 x_msg_data OUT NOCOPY VARCHAR2 );
1278
1279 /* JDOCHERT - 041201 */
1280 PROCEDURE chk_num_copy_terr( p_node_terr_id IN NUMBER,
1281 p_limit_num IN NUMBER := 10,
1282 x_Return_Status OUT NOCOPY VARCHAR2,
1283 x_msg_count OUT NOCOPY NUMBER,
1284 x_msg_data OUT NOCOPY VARCHAR2,
1285 x_terr_num OUT NOCOPY NUMBER,
1286 x_copy_status OUT NOCOPY VARCHAR2 );
1287 -- Package spec
1288
1289 /* ARPATEL - 041701 */
1290 PROCEDURE Concurrent_Copy_Territory (
1291 errbuf OUT NOCOPY VARCHAR2,
1292 retcode OUT NOCOPY VARCHAR2,
1293 p_copy_source_terr_Id IN NUMBER,
1294 p_name IN VARCHAR2,
1295 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
1296 p_rank IN NUMBER := FND_API.G_MISS_NUM,
1297 p_start_date IN DATE,
1298 p_end_date IN DATE := FND_API.G_MISS_DATE,
1299 p_copy_rsc_flag IN VARCHAR2 := 'N',
1300 p_copy_hierarchy_flag IN VARCHAR2 := 'N',
1301 p_first_terr_node_flag IN VARCHAR2 := 'N',
1302 p_debug_flag IN VARCHAR2 := 'N',
1303 p_sql_trace IN VARCHAR2 := 'N' );
1304
1305 PROCEDURE Write_Log(which number, mssg varchar2 );
1306
1307 FUNCTION conc_req_copy_terr (
1308 p_copy_source_terr_Id IN NUMBER,
1309 p_name IN VARCHAR2,
1310 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
1311 p_rank IN NUMBER := FND_API.G_MISS_NUM,
1312 p_start_date IN DATE,
1313 p_end_date IN DATE := FND_API.G_MISS_DATE,
1314 p_copy_rsc_flag IN VARCHAR2 := 'N',
1315 p_copy_hierarchy_flag IN VARCHAR2 := 'N',
1316 p_first_terr_node_flag IN VARCHAR2 := 'N'
1317 )
1318 RETURN NUMBER;
1319
1320 /* Function used in JTF_TERR_VALUES_DESC_V to return
1321 ** descriptive values for ids and lookup_codes
1322 */
1323 FUNCTION get_terr_value_desc (
1324 p_convert_to_id_flag VARCHAR2
1325 , p_display_type VARCHAR2
1326 , p_column_count NUMBER := FND_API.G_MISS_NUM
1327 , p_display_sql VARCHAR2 := FND_API.G_MISS_CHAR
1328 , p_terr_value1 VARCHAR2
1329 , p_terr_value2 VARCHAR2 := FND_API.G_MISS_CHAR
1330 )
1331 RETURN VARCHAR2;
1332
1333
1334 END JTF_TERRITORY_PVT;