[Home] [Help]
PACKAGE BODY: APPS.JTF_TERRITORY_GET_PUB
Source
1 PACKAGE BODY JTF_TERRITORY_GET_PUB AS
2 /* $Header: jtfptrgb.pls 120.3 2005/11/30 17:14:31 achanda ship $ */
3 ---------------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TERRITORY_GET_PUB
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- Joint task force core territory manager private api's.
10 -- This package is a public API for retrieving
11 -- related information from JTF tables.
12 -- It contains specification for pl/sql records and tables
13 -- and the Public territory related API's.
14 --
15 -- Procedures:
16 --
17 --
18 -- NOTES
19 -- This package is available for private use only
20 --
21 -- HISTORY
22 -- 07/15/99 JDOCHERT Created
23 -- 12/22/99 VNEDUNGA Making changes to confirm
24 -- to JTF_TERR_RSC
25 -- 05/03/00 VNEDUNGA Fxing get_esclation and get_Parent
26 -- API's
27 -- 05/03/01 ARPATEL Specify table entries for
28 -- x_QualifyingRsc_out_tbl in proc
29 -- Get_Escalation_TerrMembers
30 -- 07/16/01 ARPATEL Changed to a 'for loop' construct in Get_Escalation_TerrMembers
31 -- 08/22/01 ARPATEL Added JTF_TERR_ALL start/end date checks in cursor C_GetTerrRsc
32 --
33 -- End of Comments
34 G_FILE_NAME CONSTANT VARCHAR2(12) := 'jtfptrgb.pls';
35
36 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
37 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
38
39
40 PROCEDURE gen_init_sql ( x_select_clause OUT NOCOPY VARCHAR2
41 , x_from_clause OUT NOCOPY VARCHAR2
42 , x_where_clause OUT NOCOPY VARCHAR2 )
43 IS
44 l_proc_name VARCHAR2(30) := 'Gen_Init_SQL';
45
46 BEGIN
47
48 /* Debug Message */
49
50 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
51 THEN
52 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
53 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
54 FND_MSG_PUB.Add;
55 END IF;
56
57
58 /* build initial part of select clause */
59 x_select_clause := 'SELECT ' ||
60 'JTOV.TERR_ID, ' ||
61 'JTOV.NAME, ' ||
62 'JTOV.TERR_USAGE, ' ||
63 'JTOV.START_DATE_ACTIVE, ' ||
64 'JTOV.END_DATE_ACTIVE, ' ||
65 'JTOV.TEMPLATE_FLAG, ' ||
66 'JTOV.ESCALATION_TERRITORY_FLAG, ' ||
67 'JTOV.PARENT_TERR_NAME, ' ||
68 'JTOV.TERR_TYPE_NAME ';
69
70 /* build initial part of FROM clause
71 ** (smallest tables first from right-hand side)
72 */
73 x_from_clause := 'JTF_TERR_USGS JTUA, ' ||
74 'JTF_TERR_OVERVIEW_V JTOV, ' ||
75 'JTF_SOURCES JSE ';
76
77 /* build WHERE clause */
78 x_where_clause := ' WHERE JTUA.TERR_ID = JTOV.TERR_ID ' ||
79 ' AND JTUA.SOURCE_ID = JSE.SOURCE_ID ';
80
81 /* Debug Message */
82
83 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
84 THEN
85 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
86 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
87 FND_MSG_PUB.Add;
88 END IF;
89
90
91 END gen_init_sql;
92
93
94 PROCEDURE define_dsql_columns ( p_dsql_csr IN NUMBER,
95 p_terr_header_rec IN Terr_Header_Rec_Type )
96 IS
97 l_proc_name VARCHAR2(30) := 'Define_DSQL_Columns';
98
99 BEGIN
100
101 /* Debug Message */
102 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
103 THEN
104 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
105 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
106 FND_MSG_PUB.Add;
107 END IF;
108
109 /* define all columns */
110 dbms_sql.define_column(p_dsql_csr, 1, p_terr_header_rec.TERR_ID);
111 dbms_sql.define_column(p_dsql_csr, 2, p_terr_header_rec.TERR_NAME, 2000);
112 dbms_sql.define_column(p_dsql_csr, 3, p_terr_header_rec.TERR_USAGE, 30);
113 dbms_sql.define_column(p_dsql_csr, 4, p_terr_header_rec.START_DATE_ACTIVE);
114 dbms_sql.define_column(p_dsql_csr, 5, p_terr_header_rec.END_DATE_ACTIVE);
115 dbms_sql.define_column(p_dsql_csr, 6, p_terr_header_rec.TEMPLATE_FLAG, 1);
116 dbms_sql.define_column(p_dsql_csr, 7, p_terr_header_rec.ESCALATION_TERRITORY_FLAG, 1);
117 dbms_sql.define_column(p_dsql_csr, 8, p_terr_header_rec.PARENT_TERR_NAME, 2000);
118 dbms_sql.define_column(p_dsql_csr, 9, p_terr_header_rec.TERR_TYPE_NAME, 60);
119
120 /* Debug Message */
121 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
122 THEN
123 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
124 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
125 FND_MSG_PUB.Add;
126 END IF;
127
128 END define_dsql_columns;
129
130
131 PROCEDURE get_dsql_column_values ( p_dsql_csr IN NUMBER,
132 x_terr_header_rec OUT NOCOPY Terr_Header_Rec_Type )
133 IS
134 l_proc_name VARCHAR2(30) := 'Get_DSQL_Column_Values';
135
136 BEGIN
137
138 /* Debug Message */
139 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
140 THEN
141 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
142 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
143 FND_MSG_PUB.Add;
144 END IF;
145
146 /* get all columns */
147 dbms_sql.column_value(p_dsql_csr, 1, x_terr_header_rec.TERR_ID);
148 dbms_sql.column_value(p_dsql_csr, 2, x_terr_header_rec.TERR_NAME);
149 dbms_sql.column_value(p_dsql_csr, 3, x_terr_header_rec.TERR_USAGE);
150 dbms_sql.column_value(p_dsql_csr, 4, x_terr_header_rec.START_DATE_ACTIVE);
151 dbms_sql.column_value(p_dsql_csr, 5, x_terr_header_rec.END_DATE_ACTIVE);
152 dbms_sql.column_value(p_dsql_csr, 6, x_terr_header_rec.TEMPLATE_FLAG);
153 dbms_sql.column_value(p_dsql_csr, 7, x_terr_header_rec.ESCALATION_TERRITORY_FLAG);
154 dbms_sql.column_value(p_dsql_csr, 8, x_terr_header_rec.PARENT_TERR_NAME);
155 dbms_sql.column_value(p_dsql_csr, 9, x_terr_header_rec.TERR_TYPE_NAME);
156
157
158 /* Debug Message */
159 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
160 THEN
161 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
162 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
163 FND_MSG_PUB.Add;
164 END IF;
165
166 END get_dsql_column_values;
167
168
169
170 PROCEDURE gen_where_clause (
171 p_dsql_csr IN NUMBER,
172 p_Terr_Rec IN Terr_Rec_Type,
173 x_where_clause IN OUT NOCOPY VARCHAR2,
174 x_use_flag IN OUT NOCOPY VARCHAR2
175 )
176 IS
177
178 l_proc_name VARCHAR2(30) := 'Gen_Where: Terr_Rec_Type';
179
180 /* cursors to check if wildcard values '%' and '_' have been passed as item values */
181 CURSOR c_chk_str1 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '%', 1, 1) FROM DUAL;
182 CURSOR c_chk_str2 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '_', 1, 1) FROM DUAL;
183
184 /* return values from cursors */
185 str_csr1 NUMBER;
186 str_csr2 NUMBER;
187
188 l_operator VARCHAR2(10);
189 l_where_clause VARCHAR2(2000);
190
191 BEGIN
192
193
194 /* Debug Message */
195 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
196 THEN
197 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
198 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
199 FND_MSG_PUB.Add;
200 END IF;
201
202 --dbms_output.put_line('Value of p_TERR_rec.TERR_ID = '|| p_TERR_rec.terr_id);
203
204 /* Hint: more search criteria can be added here to
205 ** dynamically construct where clause at run time
206 */
207 IF ( (p_TERR_rec.TERR_ID IS NOT NULL) AND
208 (p_TERR_rec.TERR_ID <> FND_API.G_MISS_NUM) ) THEN
209
210 x_use_flag := 'Y';
211 l_where_clause := l_where_clause || 'AND JTOV.TERR_ID = :p_TERR_1 ';
212
213 END IF;
214
215 IF ( (p_TERR_rec.NAME IS NOT NULL) AND
216 (p_TERR_rec.NAME <> FND_API.G_MISS_CHAR) ) THEN
217
218 --dbms_output.put_line('[2] Value of p_TERR_rec.NAME='|| p_TERR_rec.NAME);
219
220 -- check if item value contains '%' wildcard
221 OPEN c_chk_str1 ( p_TERR_rec.NAME);
222 FETCH c_chk_str1 INTO str_csr1;
223 CLOSE c_chk_str1;
224
225 IF ( str_csr1 <> 0 ) THEN
226 l_operator := 'LIKE';
227 ELSE
228
229 -- check if item value contains '_' wildcard
230 OPEN c_chk_str2 ( p_TERR_rec.NAME);
231 FETCH c_chk_str2 INTO str_csr2;
232 CLOSE c_chk_str2;
233
234 IF ( str_csr2 <> 0 ) THEN
235 l_operator := 'LIKE';
236 ELSE
237 l_operator := '=';
238 END IF;
239
240 END IF;
241
242 x_use_flag := 'Y';
243 l_where_clause := l_where_clause || 'AND JTOV.NAME ' || l_operator || ' :p_TERR_12 ';
244
245 END IF;
246
247
248 IF ( (p_TERR_rec.START_DATE_ACTIVE IS NOT NULL) AND
249 (p_TERR_rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE) ) THEN
250
251 -- check if item value contains '%' wildcard
252 OPEN c_chk_str1 ( p_TERR_rec.START_DATE_ACTIVE);
253 FETCH c_chk_str1 INTO str_csr1;
254 CLOSE c_chk_str1;
255
256 IF ( str_csr1 <> 0 ) THEN
257 l_operator := 'LIKE';
258 ELSE
259
260 -- check if item value contains '_' wildcard
261 OPEN c_chk_str2 ( p_TERR_rec.START_DATE_ACTIVE);
262 FETCH c_chk_str2 INTO str_csr2;
263 CLOSE c_chk_str2;
264
265 IF ( str_csr2 <> 0 ) THEN
266 l_operator := 'LIKE';
267 ELSE
268 l_operator := '=';
269 END IF;
270
271 END IF;
272
273 x_use_flag := 'Y';
274 l_where_clause := l_where_clause || 'AND JTOV.START_DATE_ACTIVE ' ||
275 l_operator || ' :p_TERR_14 ';
276
277 END IF;
278
279 IF ( (p_TERR_rec.END_DATE_ACTIVE IS NOT NULL) AND
280 (p_TERR_rec.END_DATE_ACTIVE <> FND_API.G_MISS_DATE) ) THEN
281
282 -- check if item value contains '%' wildcard
283 OPEN c_chk_str1 ( p_TERR_rec.END_DATE_ACTIVE);
284 FETCH c_chk_str1 INTO str_csr1;
285 CLOSE c_chk_str1;
286
287 IF ( str_csr1 <> 0 ) THEN
288 l_operator := 'LIKE';
289 ELSE
290
291 -- check if item value contains '_' wildcard
292 OPEN c_chk_str2 ( p_TERR_rec.END_DATE_ACTIVE);
293 FETCH c_chk_str2 INTO str_csr2;
294 CLOSE c_chk_str2;
295
296 IF ( str_csr2 <> 0 ) THEN
297 l_operator := 'LIKE';
298 ELSE
299 l_operator := '=';
300 END IF;
301 END IF;
302
303 x_use_flag := 'Y';
304 l_where_clause := l_where_clause || 'AND JTOV.END_DATE_ACTIVE ' ||
305 l_operator || ' :p_TERR_15 ';
306
307 END IF;
308
309
310 IF ( (p_TERR_rec.PARENT_TERRITORY_ID IS NOT NULL) AND
311 (p_TERR_rec.PARENT_TERRITORY_ID <> FND_API.G_MISS_NUM) ) THEN
312
313 x_use_flag := 'Y';
314 l_where_clause := l_where_clause || 'AND JTOV.PARENT_TERRITORY_ID = :p_TERR_17 ';
315
316 END IF;
317
318 IF ( (p_TERR_rec.TERRITORY_TYPE_ID IS NOT NULL) AND
319 (p_TERR_rec.TERRITORY_TYPE_ID <> FND_API.G_MISS_NUM) ) THEN
320
321 x_use_flag := 'Y';
322 l_where_clause := l_where_clause || 'AND JTOV.TERRITORY_TYPE_ID = :p_TERR_18 ';
323
324 END IF;
325
326 IF ( (p_TERR_rec.TEMPLATE_TERRITORY_ID IS NOT NULL) AND
327 (p_TERR_rec.TEMPLATE_TERRITORY_ID <> FND_API.G_MISS_NUM) ) THEN
328
329 x_use_flag := 'Y';
330 l_where_clause := l_where_clause || 'AND JTOV.TEMPLATE_TERRITORY_ID = :p_TERR_19 ';
331
332 END IF;
333
334 IF ( (p_TERR_rec.TEMPLATE_FLAG IS NOT NULL) AND
335 (p_TERR_rec.TEMPLATE_FLAG <> FND_API.G_MISS_CHAR) ) THEN
336
337 x_use_flag := 'Y';
338 l_where_clause := l_where_clause || 'AND JTOV.TEMPLATE_FLAG ' ||
339 l_operator || ' :p_TERR_20 ';
340
341 END IF;
342
343 IF ( (p_TERR_rec.ESCALATION_TERRITORY_ID IS NOT NULL) AND
344 (p_TERR_rec.ESCALATION_TERRITORY_ID <> FND_API.G_MISS_NUM) ) THEN
345
346 x_use_flag := 'Y';
347 l_where_clause := l_where_clause || 'AND JTOV.ESCALATION_TERRITORY_ID = :p_TERR_21 ';
348
349 END IF;
350
351 IF ( (p_TERR_rec.ESCALATION_TERRITORY_FLAG IS NOT NULL) AND
352 (p_TERR_rec.ESCALATION_TERRITORY_FLAG <> FND_API.G_MISS_CHAR) ) THEN
353
354 x_use_flag := 'Y';
355 l_where_clause := l_where_clause || 'AND JTOV.ESCALATION_TERRITORY_FLAG ' ||
356 l_operator || ' :p_TERR_22 ';
357
358 END IF;
359
360 IF ( (p_TERR_rec.OVERLAP_ALLOWED_FLAG IS NOT NULL) AND
361 (p_TERR_rec.OVERLAP_ALLOWED_FLAG <> FND_API.G_MISS_CHAR) ) THEN
362
363 x_use_flag := 'Y';
364 l_where_clause := l_where_clause || 'AND JTOV.OVERLAP_ALLOWED_FLAG ' ||
365 l_operator || ' :p_TERR_23 ';
366
367 END IF;
368
369 IF ( (p_TERR_rec.RANK IS NOT NULL) AND
370 (p_TERR_rec.RANK <> FND_API.G_MISS_NUM) ) THEN
371
372 x_use_flag := 'Y';
373 l_where_clause := l_where_clause || 'AND JTOV.RANK = :p_TERR_24 ';
374
375 END IF;
376
377 IF ( (p_TERR_rec.DESCRIPTION IS NOT NULL) AND
378 (p_TERR_rec.DESCRIPTION <> FND_API.G_MISS_CHAR) ) THEN
379
380 -- check if item value contains '%' wildcard
381 OPEN c_chk_str1 ( p_TERR_rec.DESCRIPTION);
382 FETCH c_chk_str1 INTO str_csr1;
383 CLOSE c_chk_str1;
384
385 IF ( str_csr1 <> 0 ) THEN
386 l_operator := 'LIKE';
387 ELSE
388
389 -- check if item value contains '_' wildcard
390 OPEN c_chk_str2 ( p_TERR_rec.DESCRIPTION);
391 FETCH c_chk_str2 INTO str_csr2;
392 CLOSE c_chk_str2;
393
394 IF ( str_csr2 <> 0 ) THEN
395 l_operator := 'LIKE';
396 ELSE
397 l_operator := '=';
398 END IF;
399
400 END IF;
401
402 x_use_flag := 'Y';
403 l_where_clause := l_where_clause || 'AND JTOV.DESCRIPTION ' ||
404 l_operator || ' :p_TERR_25 ';
405
406 END IF;
407
408 IF ( (p_TERR_rec.PARENT_TERR_NAME IS NOT NULL) AND
409 (p_TERR_rec.PARENT_TERR_NAME <> FND_API.G_MISS_CHAR) ) THEN
410
411 -- check if item value contains '%' wildcard
412 OPEN c_chk_str1 ( p_TERR_rec.PARENT_TERR_NAME);
413 FETCH c_chk_str1 INTO str_csr1;
414 CLOSE c_chk_str1;
415
416 IF ( str_csr1 <> 0 ) THEN
417 l_operator := 'LIKE';
418 ELSE
419
420 -- check if item value contains '_' wildcard
421 OPEN c_chk_str2 ( p_TERR_rec.PARENT_TERR_NAME);
422 FETCH c_chk_str2 INTO str_csr2;
423 CLOSE c_chk_str2;
424
425 IF ( str_csr2 <> 0 ) THEN
426 l_operator := 'LIKE';
427 ELSE
428 l_operator := '=';
429 END IF;
430
431 END IF;
432
433
434 x_use_flag := 'Y';
435 l_where_clause := l_where_clause || 'AND JTOV.PARENT_TERR_NAME ' ||
436 l_operator || ' :p_TERR_26 ';
437
438 END IF;
439
440 IF ( (p_TERR_rec.ESCALATION_TERR_NAME IS NOT NULL) AND
441 (p_TERR_rec.ESCALATION_TERR_NAME <> FND_API.G_MISS_CHAR) ) THEN
442
443 -- check if item value contains '%' wildcard
444 OPEN c_chk_str1 ( p_TERR_rec.ESCALATION_TERR_NAME);
445 FETCH c_chk_str1 INTO str_csr1;
446 CLOSE c_chk_str1;
447
448 IF ( str_csr1 <> 0 ) THEN
449 l_operator := 'LIKE';
450 ELSE
451
452 -- check if item value contains '_' wildcard
453 OPEN c_chk_str2 ( p_TERR_rec.ESCALATION_TERR_NAME);
454 FETCH c_chk_str2 INTO str_csr2;
455 CLOSE c_chk_str2;
456
457 IF ( str_csr2 <> 0 ) THEN
458 l_operator := 'LIKE';
459 ELSE
460 l_operator := '=';
461 END IF;
462 END IF;
463
464 x_use_flag := 'Y';
465 l_where_clause := l_where_clause || 'AND JTOV.ESCALATION_TERR_NAME ' ||
466 l_operator || ' :p_TERR_27 ';
467
468 END IF;
469
470 IF ( (p_TERR_rec.TEMPLATE_TERR_NAME IS NOT NULL) AND
471 (p_TERR_rec.TEMPLATE_TERR_NAME <> FND_API.G_MISS_CHAR) ) THEN
472
473 -- check if item value contains '%' wildcard
474 OPEN c_chk_str1 ( p_TERR_rec.TEMPLATE_TERR_NAME);
475 FETCH c_chk_str1 INTO str_csr1;
476 CLOSE c_chk_str1;
477
478 IF ( str_csr1 <> 0 ) THEN
479 l_operator := 'LIKE';
480 ELSE
481
482 -- check if item value contains '_' wildcard
483 OPEN c_chk_str2 ( p_TERR_rec.TEMPLATE_TERR_NAME);
484 FETCH c_chk_str2 INTO str_csr2;
485 CLOSE c_chk_str2;
486
487 IF ( str_csr2 <> 0 ) THEN
488 l_operator := 'LIKE';
489 ELSE
490 l_operator := '=';
491 END IF;
492 END IF;
493
494 x_use_flag := 'Y';
495 l_where_clause := l_where_clause || 'AND JTOV.TEMPLATE_TERR_NAME ' ||
496 l_operator || ' :p_TERR_28 ';
497
498 END IF;
499
500 x_where_clause := x_where_clause || l_where_clause;
501
502 /* Debug Message */
503 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
504 THEN
505 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
506 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
507 FND_MSG_PUB.Add;
508 END IF;
509
510 END gen_where_clause;
511
512
513 PROCEDURE gen_bind (
514 p_dsql_csr IN OUT NOCOPY NUMBER,
515 p_Terr_Rec IN Terr_Rec_Type
516 )
517 IS
518 l_proc_name VARCHAR2(30) := 'Gen_Bind: Terr_Rec_Type';
519 BEGIN
520
521 /* Debug Message */
522 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
523 THEN
524 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
525 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
526 FND_MSG_PUB.Add;
527 END IF;
528
529
530 /* Hint: more search criteria can be added here to
531 ** dynamically construct binds at run time
532 */
533 IF ( (p_TERR_rec.TERR_ID IS NOT NULL) AND
534 (p_TERR_rec.TERR_ID <> FND_API.G_MISS_NUM) ) THEN
535
536 --dbms_output.put_line('Binding p_TERR_rec.TERR_ID to :p_Terr_1');
537
538 -- bind the input variables
539 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_1', p_TERR_rec.TERR_ID);
540
541 END IF;
542
543 IF ( (p_TERR_rec.NAME IS NOT NULL) AND
544 (p_TERR_rec.NAME <> FND_API.G_MISS_CHAR) ) THEN
545
546 -- bind the input variables
547 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_12', p_TERR_rec.NAME);
548
549 END IF;
550
551 IF ( (p_TERR_rec.START_DATE_ACTIVE IS NOT NULL) AND
552 (p_TERR_rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE) ) THEN
553
554 -- bind the input variables
555 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_14', p_TERR_rec.START_DATE_ACTIVE);
556
557 END IF;
558
559 IF ( (p_TERR_rec.END_DATE_ACTIVE IS NOT NULL) AND
560 (p_TERR_rec.END_DATE_ACTIVE <> FND_API.G_MISS_DATE) ) THEN
561
562 -- bind the input variables
563 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_15', p_TERR_rec.END_DATE_ACTIVE);
564
565 END IF;
566
567
568 IF ( (p_TERR_rec.PARENT_TERRITORY_ID IS NOT NULL) AND
569 (p_TERR_rec.PARENT_TERRITORY_ID <> FND_API.G_MISS_NUM) ) THEN
570
571 -- bind the input variables
572 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_17', p_TERR_rec.PARENT_TERRITORY_ID);
573
574 END IF;
575
576 IF ( (p_TERR_rec.TERRITORY_TYPE_ID IS NOT NULL) AND
577 (p_TERR_rec.TERRITORY_TYPE_ID <> FND_API.G_MISS_NUM) ) THEN
578
579 -- bind the input variables
580 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_18', p_TERR_rec.TERRITORY_TYPE_ID);
581
582 END IF;
583
584 IF ( (p_TERR_rec.TEMPLATE_TERRITORY_ID IS NOT NULL) AND
585 (p_TERR_rec.TEMPLATE_TERRITORY_ID <> FND_API.G_MISS_NUM) ) THEN
586
587 -- bind the input variables
588 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_19', p_TERR_rec.TEMPLATE_TERRITORY_ID);
589
590 END IF;
591
592 IF ( (p_TERR_rec.TEMPLATE_FLAG IS NOT NULL) AND
593 (p_TERR_rec.TEMPLATE_FLAG <> FND_API.G_MISS_CHAR) ) THEN
594
595 -- bind the input variables
596 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_20', p_TERR_rec.TEMPLATE_FLAG);
597
598 END IF;
599
600 IF ( (p_TERR_rec.ESCALATION_TERRITORY_ID IS NOT NULL) AND
601 (p_TERR_rec.ESCALATION_TERRITORY_ID <> FND_API.G_MISS_NUM) ) THEN
602
603 -- bind the input variables
604 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_21', p_TERR_rec.ESCALATION_TERRITORY_ID);
605
606 END IF;
607
608 IF ( (p_TERR_rec.ESCALATION_TERRITORY_FLAG IS NOT NULL) AND
609 (p_TERR_rec.ESCALATION_TERRITORY_FLAG <> FND_API.G_MISS_CHAR) ) THEN
610
611 -- bind the input variables
612 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_22', p_TERR_rec.ESCALATION_TERRITORY_FLAG);
613
614 END IF;
615
616 IF ( (p_TERR_rec.OVERLAP_ALLOWED_FLAG IS NOT NULL) AND
617 (p_TERR_rec.OVERLAP_ALLOWED_FLAG <> FND_API.G_MISS_CHAR) ) THEN
618
619 -- bind the input variables
620 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_23', p_TERR_rec.OVERLAP_ALLOWED_FLAG);
621
622 END IF;
623
624 IF ( (p_TERR_rec.RANK IS NOT NULL) AND
625 (p_TERR_rec.RANK <> FND_API.G_MISS_NUM) ) THEN
626
627 -- bind the input variables
628 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_24', p_TERR_rec.RANK);
629
630 END IF;
631
632 IF ( (p_TERR_rec.DESCRIPTION IS NOT NULL) AND
633 (p_TERR_rec.DESCRIPTION <> FND_API.G_MISS_CHAR) ) THEN
634
635 -- bind the input variables
636 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_25', p_TERR_rec.DESCRIPTION);
637
638 END IF;
639
640 IF ( (p_TERR_rec.PARENT_TERR_NAME IS NOT NULL) AND
641 (p_TERR_rec.PARENT_TERR_NAME <> FND_API.G_MISS_CHAR) ) THEN
642
643 -- bind the input variables
644 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_26', p_TERR_rec.PARENT_TERR_NAME);
645
646 END IF;
647
648 IF ( (p_TERR_rec.ESCALATION_TERR_NAME IS NOT NULL) AND
649 (p_TERR_rec.ESCALATION_TERR_NAME <> FND_API.G_MISS_CHAR) ) THEN
650
651 -- bind the input variables
652 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_27', p_TERR_rec.ESCALATION_TERR_NAME);
653
654 END IF;
655
656 IF ( (p_TERR_rec.TEMPLATE_TERR_NAME IS NOT NULL) AND
657 (p_TERR_rec.TEMPLATE_TERR_NAME <> FND_API.G_MISS_CHAR) ) THEN
658
659 -- bind the input variables
660 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_28', p_TERR_rec.TEMPLATE_TERR_NAME);
661
662 END IF;
663
664 /* Debug Message */
665 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
666 THEN
667 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
668 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
669 FND_MSG_PUB.Add;
670 END IF;
671
672 END gen_bind;
673
674 -----------------------------------------------------------------------
675 -- Territory Usages
676 -----------------------------------------------------------------------
677 PROCEDURE gen_where_clause (
678 p_dsql_csr IN NUMBER,
679 p_Terr_Usgs_Rec IN Terr_Usgs_Rec_Type,
680 x_where_clause IN OUT NOCOPY VARCHAR2,
681 x_use_flag IN OUT NOCOPY VARCHAR2
682 )
683 IS
684
685 l_proc_name VARCHAR2(30) := 'Gen_Where: Terr_Usgs';
686
687 /* cursors to check if wildcard values '%' and '_' have been passed as item values */
688 CURSOR c_chk_str1 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '%', 1, 1) FROM DUAL;
689 CURSOR c_chk_str2 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '_', 1, 1) FROM DUAL;
690
691 /* return values from cursors */
692 str_csr1 NUMBER;
693 str_csr2 NUMBER;
694
695 l_operator VARCHAR2(10);
696 l_where_clause VARCHAR2(2000);
697
698 BEGIN
699
700 /* Debug Message */
701 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
702 THEN
703 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
704 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
705 FND_MSG_PUB.Add;
706 END IF;
707
708 IF ( (p_TERR_USGS_rec.TERR_USG_ID IS NOT NULL) AND
709 (p_TERR_USGS_rec.TERR_USG_ID <> FND_API.G_MISS_NUM) ) THEN
710
711 x_use_flag := 'Y';
712 l_where_clause := l_where_clause || 'AND JTUA.TERR_USG_ID = :p_TERR_USGS_1 ';
713
714 END IF;
715
716 IF ( (p_TERR_USGS_rec.TERR_ID IS NOT NULL) AND
717 (p_TERR_USGS_rec.TERR_ID <> FND_API.G_MISS_NUM) ) THEN
718
719 x_use_flag := 'Y';
720 l_where_clause := l_where_clause || 'AND JTUA.TERR_ID = :p_TERR_USGS_7 ';
721
722 END IF;
723
724 IF ( (p_TERR_USGS_rec.SOURCE_ID IS NOT NULL) AND
725 (p_TERR_USGS_rec.SOURCE_ID <> FND_API.G_MISS_NUM) ) THEN
726
727 x_use_flag := 'Y';
728 l_where_clause := l_where_clause || 'AND JTUA.SOURCE_ID = :p_TERR_USGS_8 ';
729
730 END IF;
731
732 IF ( (p_TERR_USGS_rec.USAGE IS NOT NULL) AND
733 (p_TERR_USGS_rec.USAGE <> FND_API.G_MISS_CHAR) ) THEN
734
735 -- check if item value contains '%' wildcard
736 OPEN c_chk_str1 ( p_TERR_USGS_rec.USAGE);
737 FETCH c_chk_str1 INTO str_csr1;
738 CLOSE c_chk_str1;
739
740 --dbms_output.put_line('str_csr1 = ' || TO_CHAR(str_csr1));
741
742 IF ( str_csr1 <> 0 ) THEN
743 l_operator := 'LIKE';
744 ELSE
745 -- check if item value contains '_' wildcard
746 OPEN c_chk_str2 ( p_TERR_USGS_rec.USAGE);
747 FETCH c_chk_str2 INTO str_csr2;
748 CLOSE c_chk_str2;
749
750 IF ( str_csr2 <> 0 ) THEN
751 l_operator := 'LIKE';
752 ELSE
753 l_operator := '=';
754 END IF;
755
756 END IF;
757
758 x_use_flag := 'Y';
759 l_where_clause := l_where_clause || 'AND JSE.MEANING ' ||
760 l_operator ||' :p_TERR_USGS_9 ';
761
762 END IF;
763
764 x_where_clause := x_where_clause || l_where_clause;
765
766 /* Debug Message */
767 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
768 THEN
769 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
770 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
771 FND_MSG_PUB.Add;
772 END IF;
773
774 END gen_where_clause;
775
776
777 PROCEDURE gen_bind (
778 p_dsql_csr IN NUMBER,
779 p_Terr_Usgs_Rec IN Terr_Usgs_Rec_Type
780 )
781 IS
782 l_proc_name VARCHAR2(30) := 'Gen_Bind: Terr_Usgs';
783
784 BEGIN
785
786 /* Debug Message */
787 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
788 THEN
789 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
790 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
791 FND_MSG_PUB.Add;
792 END IF;
793
794 IF ( (p_TERR_USGS_rec.TERR_USG_ID IS NOT NULL) AND
795 (p_TERR_USGS_rec.TERR_USG_ID <> FND_API.G_MISS_NUM) ) THEN
796
797 -- bind the input variables
798 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_USGS_1', p_TERR_USGS_rec.TERR_USG_ID);
799
800 END IF;
801
802 IF ( (p_TERR_USGS_rec.TERR_ID IS NOT NULL) AND
803 (p_TERR_USGS_rec.TERR_ID <> FND_API.G_MISS_NUM) ) THEN
804
805 -- bind the input variables
806 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_USGS_7', p_TERR_USGS_rec.TERR_ID);
807
808 END IF;
809
810 IF ( (p_TERR_USGS_rec.SOURCE_ID IS NOT NULL) AND
811 (p_TERR_USGS_rec.SOURCE_ID <> FND_API.G_MISS_NUM) ) THEN
812
813 -- bind the input variables
814 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_USGS_8', p_TERR_USGS_rec.SOURCE_ID);
815
816 END IF;
817
818 IF ( (p_TERR_USGS_rec.USAGE IS NOT NULL) AND
819 (p_TERR_USGS_rec.USAGE <> FND_API.G_MISS_CHAR) ) THEN
820
821 -- bind the input variables
822 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_USGS_9', p_TERR_USGS_rec.USAGE);
823
824 END IF;
825
826 /* Debug Message */
827 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
828 THEN
829 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
830 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
831 FND_MSG_PUB.Add;
832 END IF;
833
834 END gen_bind;
835
836
837 -----------------------------------------------------------------------
838 -- Territory Types
839 -----------------------------------------------------------------------
840 PROCEDURE gen_where_clause (
841 p_dsql_csr IN NUMBER,
842 p_Terr_Types_Rec IN Terr_Type_Rec_Type,
843 x_where_clause IN OUT NOCOPY VARCHAR2,
844 x_use_flag IN OUT NOCOPY VARCHAR2
845 )
846 IS
847
848 l_proc_name VARCHAR2(30) := 'Gen_Where: Terr_Type';
849
850 /* cursors to check if wildcard values '%' and '_' have been passed as item values */
851 CURSOR c_chk_str1 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '%', 1, 1) FROM DUAL;
852 CURSOR c_chk_str2 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '_', 1, 1) FROM DUAL;
853
854 /* return values from cursors */
855 str_csr1 NUMBER;
856 str_csr2 NUMBER;
857
858 l_operator VARCHAR2(10);
859 l_where_clause VARCHAR2(2000);
860
861 BEGIN
862
863 /* Debug Message */
864 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
865 THEN
866 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
867 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
868 FND_MSG_PUB.Add;
869 END IF;
870
871 IF ( (p_TERR_TYPES_rec.TERR_TYPE_ID IS NOT NULL) AND
872 (p_TERR_TYPES_rec.TERR_TYPE_ID <> FND_API.G_MISS_NUM) ) THEN
873
874 x_use_flag := 'Y';
875 l_where_clause := l_where_clause || 'AND JTTA.TERR_TYPE_ID = :p_TERR_TYPES_1 ';
876
877 END IF;
878
879 IF ( (p_TERR_TYPES_rec.NAME IS NOT NULL) AND
880 (p_TERR_TYPES_rec.NAME <> FND_API.G_MISS_CHAR) ) THEN
881
882 -- check if item value contains '%' wildcard
883 OPEN c_chk_str1 ( p_TERR_TYPES_rec.NAME);
884 FETCH c_chk_str1 INTO str_csr1;
885 CLOSE c_chk_str1;
886
887 IF ( str_csr1 <> 0 ) THEN
888 l_operator := 'LIKE';
889 ELSE
890
891 -- check if item value contains '_' wildcard
892 OPEN c_chk_str2 ( p_TERR_TYPES_rec.NAME);
893 FETCH c_chk_str2 INTO str_csr2;
894 CLOSE c_chk_str2;
895
896 IF ( str_csr2 <> 0 ) THEN
897 l_operator := 'LIKE';
898 ELSE
899 l_operator := '=';
900 END IF;
901 END IF;
902
903 x_use_flag := 'Y';
904 l_where_clause := l_where_clause || 'AND JTTA.NAME ' ||
905 l_operator || ' :p_TERR_TYPES_8 ';
906
907 END IF;
908
909 IF ( (p_TERR_TYPES_rec.DESCRIPTION IS NOT NULL) AND
910 (p_TERR_TYPES_rec.DESCRIPTION <> FND_API.G_MISS_CHAR) ) THEN
911
912 -- check if item value contains '%' wildcard
913 OPEN c_chk_str1 ( p_TERR_TYPES_rec.DESCRIPTION);
914 FETCH c_chk_str1 INTO str_csr1;
915 CLOSE c_chk_str1;
916
917 IF ( str_csr1 <> 0 ) THEN
918 l_operator := 'LIKE';
919 ELSE
920
921 -- check if item value contains '_' wildcard
922 OPEN c_chk_str2 ( p_TERR_TYPES_rec.DESCRIPTION);
923 FETCH c_chk_str2 INTO str_csr2;
924 CLOSE c_chk_str2;
925
926 IF ( str_csr2 <> 0 ) THEN
927 l_operator := 'LIKE';
928 ELSE
929 l_operator := '=';
930 END IF;
931 END IF;
932
933 x_use_flag := 'Y';
934 l_where_clause := l_where_clause || 'AND JTTA.DESCRIPTION ' ||
935 l_operator || ' :p_TERR_TYPES_10 ';
936
937 END IF;
938
939 IF ( (p_TERR_TYPES_rec.START_DATE_ACTIVE IS NOT NULL) AND
940 (p_TERR_TYPES_rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE) ) THEN
941
942 -- check if item value contains '%' wildcard
943 OPEN c_chk_str1 ( p_TERR_TYPES_rec.START_DATE_ACTIVE);
944 FETCH c_chk_str1 INTO str_csr1;
945 CLOSE c_chk_str1;
946
947 IF ( str_csr1 <> 0 ) THEN
948 l_operator := 'LIKE';
949 ELSE
950
951 -- check if item value contains '_' wildcard
952 OPEN c_chk_str2 ( p_TERR_TYPES_rec.START_DATE_ACTIVE);
953 FETCH c_chk_str2 INTO str_csr2;
954 CLOSE c_chk_str2;
955
956 IF ( str_csr2 <> 0 ) THEN
957 l_operator := 'LIKE';
958 ELSE
959 l_operator := '=';
960 END IF;
961
962 END IF;
963
964 x_use_flag := 'Y';
965 l_where_clause := l_where_clause || 'AND JTTA.START_DATE_ACTIVE ' ||
966 l_operator || ' :p_TERR_TYPES_11 ';
967
968
969 END IF;
970
971 IF ( (p_TERR_TYPES_rec.END_DATE_ACTIVE IS NOT NULL) AND
972 (p_TERR_TYPES_rec.END_DATE_ACTIVE <> FND_API.G_MISS_DATE) ) THEN
973
974 -- check if item value contains '%' wildcard
975 OPEN c_chk_str1 ( p_TERR_TYPES_rec.END_DATE_ACTIVE);
976 FETCH c_chk_str1 INTO str_csr1;
977 CLOSE c_chk_str1;
978
979 IF ( str_csr1 <> 0 ) THEN
980 l_operator := 'LIKE';
981 ELSE
982
983 -- check if item value contains '_' wildcard
984 OPEN c_chk_str2 ( p_TERR_TYPES_rec.END_DATE_ACTIVE);
985 FETCH c_chk_str2 INTO str_csr2;
986 CLOSE c_chk_str2;
987
988 IF ( str_csr2 <> 0 ) THEN
989 l_operator := 'LIKE';
990 ELSE
991 l_operator := '=';
992 END IF;
993
994 END IF;
995
996 x_use_flag := 'Y';
997 l_where_clause := l_where_clause || 'AND JTTA.END_DATE_ACTIVE ' ||
998 l_operator || ' :p_TERR_TYPES_12 ';
999
1000 END IF;
1001
1002 x_where_clause := x_where_clause || l_where_clause;
1003
1004 /* Debug Message */
1005 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1006 THEN
1007 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
1008 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
1009 FND_MSG_PUB.Add;
1010 END IF;
1011
1012 END gen_where_clause;
1013
1014
1015 PROCEDURE gen_bind (
1016 p_dsql_csr IN NUMBER,
1017 p_Terr_Types_Rec IN Terr_Type_Rec_Type
1018 )
1019 IS
1020 l_proc_name VARCHAR2(30) := 'Gen_Bind: Terr_Type';
1021
1022 BEGIN
1023
1024 /* Debug Message */
1025 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1026 THEN
1027 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1028 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
1029 FND_MSG_PUB.Add;
1030 END IF;
1031
1032 IF ( (p_TERR_TYPES_rec.TERR_TYPE_ID IS NOT NULL) AND
1033 (p_TERR_TYPES_rec.TERR_TYPE_ID <> FND_API.G_MISS_NUM) ) THEN
1034
1035 -- bind the input variables
1036 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_TYPES_1', p_TERR_TYPES_rec.TERR_TYPE_ID);
1037
1038 END IF;
1039
1040
1041 IF ( (p_TERR_TYPES_rec.NAME IS NOT NULL) AND
1042 (p_TERR_TYPES_rec.NAME <> FND_API.G_MISS_CHAR) ) THEN
1043
1044 -- bind the input variables
1045 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_TYPES_8', p_TERR_TYPES_rec.NAME);
1046
1047 END IF;
1048
1049
1050 IF ( (p_TERR_TYPES_rec.DESCRIPTION IS NOT NULL) AND
1051 (p_TERR_TYPES_rec.DESCRIPTION <> FND_API.G_MISS_CHAR) ) THEN
1052
1053 -- bind the input variables
1054 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_TYPES_10', p_TERR_TYPES_rec.DESCRIPTION);
1055
1056 END IF;
1057
1058 IF ( (p_TERR_TYPES_rec.START_DATE_ACTIVE IS NOT NULL) AND
1059 (p_TERR_TYPES_rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE) ) THEN
1060
1061 -- bind the input variables
1062 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_TYPES_11', p_TERR_TYPES_rec.START_DATE_ACTIVE);
1063
1064 END IF;
1065
1066 IF ( (p_TERR_TYPES_rec.END_DATE_ACTIVE IS NOT NULL) AND
1067 (p_TERR_TYPES_rec.END_DATE_ACTIVE <> FND_API.G_MISS_DATE) ) THEN
1068
1069 -- bind the input variables
1070 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_TYPES_12', p_TERR_TYPES_rec.END_DATE_ACTIVE);
1071
1072 END IF;
1073
1074 /* Debug Message */
1075 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1076 THEN
1077 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
1078 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
1079 FND_MSG_PUB.Add;
1080 END IF;
1081
1082 END gen_bind;
1083
1084
1085 -----------------------------------------------------------------------
1086 -- Territory Qualifier Type Usages
1087 -----------------------------------------------------------------------
1088 PROCEDURE gen_where_clause (
1089 p_dsql_csr IN NUMBER,
1090 p_Terr_QType_Usgs_Rec IN Terr_QType_Usgs_Rec_Type,
1091 x_where_clause IN OUT NOCOPY VARCHAR2,
1092 x_use_flag IN OUT NOCOPY VARCHAR2
1093 )
1094 IS
1095
1096 l_proc_name VARCHAR2(30) := 'Gen_Where: Terr_QType_Usgs';
1097
1098 /* cursors to check if wildcard values '%' and '_' have been passed as item values */
1099 CURSOR c_chk_str1 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '%', 1, 1) FROM DUAL;
1100 CURSOR c_chk_str2 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '_', 1, 1) FROM DUAL;
1101
1102 /* return values from cursors */
1103 str_csr1 NUMBER;
1104 str_csr2 NUMBER;
1105
1106 l_operator VARCHAR2(10);
1107 l_where_clause VARCHAR2(2000);
1108
1109 BEGIN
1110
1111 /* Debug Message */
1112 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1113 THEN
1114 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1115 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
1116 FND_MSG_PUB.Add;
1117 END IF;
1118
1119 IF ( (p_TERR_QTYPE_USGS_REC.TERR_QTYPE_USG_ID IS NOT NULL) AND
1120 (p_TERR_QTYPE_USGS_REC.TERR_QTYPE_USG_ID <> FND_API.G_MISS_NUM) ) THEN
1121
1122 x_use_flag := 'Y';
1123 l_where_clause := l_where_clause || 'AND JTTV.TERR_QTYPE_USG_ID = :p_TERR_QTYPE_USGS_1 ';
1124
1125 END IF;
1126
1127 IF ( (p_TERR_QTYPE_USGS_REC.TERR_ID IS NOT NULL) AND
1128 (p_TERR_QTYPE_USGS_REC.TERR_ID <> FND_API.G_MISS_NUM) ) THEN
1129
1130 x_use_flag := 'Y';
1131 l_where_clause := l_where_clause || 'AND JTTV.TERR_ID = :p_TERR_QTYPE_USGS_7 ';
1132
1133 END IF;
1134
1135 IF ( (p_TERR_QTYPE_USGS_REC.QUAL_TYPE_USG_ID IS NOT NULL) AND
1136 (p_TERR_QTYPE_USGS_REC.QUAL_TYPE_USG_ID <> FND_API.G_MISS_NUM) ) THEN
1137
1138 x_use_flag := 'Y';
1139 l_where_clause := l_where_clause || 'AND JTTV.TERR_ID = :p_TERR_QTYPE_USGS_8 ';
1140
1141 END IF;
1142
1143 IF ( (p_TERR_QTYPE_USGS_REC.SOURCE_ID IS NOT NULL) AND
1144 (p_TERR_QTYPE_USGS_REC.SOURCE_ID <> FND_API.G_MISS_NUM) ) THEN
1145
1146 x_use_flag := 'Y';
1147 l_where_clause := l_where_clause || 'AND JTTV.SOURCE_ID = :p_TERR_QTYPE_USGS_10 ';
1148
1149 END IF;
1150
1151 IF ( (p_TERR_QTYPE_USGS_REC.QUAL_TYPE_ID IS NOT NULL) AND
1152 (p_TERR_QTYPE_USGS_REC.QUAL_TYPE_ID <> FND_API.G_MISS_NUM) ) THEN
1153
1154 x_use_flag := 'Y';
1155 l_where_clause := l_where_clause || 'AND JTTV.QUAL_TYPE_ID = :p_TERR_QTYPE_USGS_11 ';
1156
1157 END IF;
1158
1159 IF ( (p_TERR_QTYPE_USGS_REC.QUALIFIER_TYPE_NAME IS NOT NULL) AND
1160 (p_TERR_QTYPE_USGS_REC.QUALIFIER_TYPE_NAME <> FND_API.G_MISS_CHAR) ) THEN
1161
1162 -- check if item value contains '%' wildcard
1163 OPEN c_chk_str1 ( p_TERR_QTYPE_USGS_REC.QUALIFIER_TYPE_NAME);
1164 FETCH c_chk_str1 INTO str_csr1;
1165 CLOSE c_chk_str1;
1166
1167 IF ( str_csr1 <> 0 ) THEN
1168 l_operator := 'LIKE';
1169 ELSE
1170
1171 -- check if item value contains '_' wildcard
1172 OPEN c_chk_str2 ( p_TERR_QTYPE_USGS_REC.QUALIFIER_TYPE_NAME);
1173 FETCH c_chk_str2 INTO str_csr2;
1174 CLOSE c_chk_str2;
1175
1176 IF ( str_csr2 <> 0 ) THEN
1177 l_operator := 'LIKE';
1178 ELSE
1179 l_operator := '=';
1180 END IF;
1181
1182 END IF;
1183
1184 x_use_flag := 'Y';
1185 l_where_clause := l_where_clause ||
1186 'AND JTTV.QUALIFIER_TYPE_NAME = :p_TERR_QTYPE_USGS_12 ';
1187
1188 END IF;
1189
1190 IF ( (p_TERR_QTYPE_USGS_REC.QUALIFIER_TYPE_DESCRIPTION IS NOT NULL) AND
1191 (p_TERR_QTYPE_USGS_REC.QUALIFIER_TYPE_DESCRIPTION <> FND_API.G_MISS_CHAR) ) THEN
1192
1193 -- check if item value contains '%' wildcard
1194 OPEN c_chk_str1 ( p_TERR_QTYPE_USGS_REC.QUALIFIER_TYPE_DESCRIPTION);
1195 FETCH c_chk_str1 INTO str_csr1;
1196 CLOSE c_chk_str1;
1197
1198 IF ( str_csr1 <> 0 ) THEN
1199 l_operator := 'LIKE';
1200 ELSE
1201
1202 -- check if item value contains '_' wildcard
1203 OPEN c_chk_str2 ( p_TERR_QTYPE_USGS_REC.QUALIFIER_TYPE_DESCRIPTION);
1204 FETCH c_chk_str2 INTO str_csr2;
1205 CLOSE c_chk_str2;
1206
1207 IF ( str_csr2 <> 0 ) THEN
1208 l_operator := 'LIKE';
1209 ELSE
1210 l_operator := '=';
1211 END IF;
1212 END IF;
1213
1214 x_use_flag := 'Y';
1215 l_where_clause := l_where_clause ||
1216 'AND JTTV.QUALIFIER_TYPE_DESCRIPTION = :p_TERR_QTYPE_USGS_13 ';
1217
1218 END IF;
1219
1220 x_where_clause := x_where_clause || l_where_clause;
1221
1222 /* Debug Message */
1223 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1224 THEN
1225 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
1226 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
1227 FND_MSG_PUB.Add;
1228 END IF;
1229
1230 END gen_where_clause;
1231
1232
1233 PROCEDURE gen_bind (
1234 p_dsql_csr IN NUMBER,
1235 p_Terr_QType_Usgs_Rec IN Terr_QType_Usgs_Rec_Type
1236 )
1237 IS
1238
1239 l_proc_name VARCHAR2(30) := 'Gen_Bind: Terr_QType_Usgs';
1240
1241 BEGIN
1242
1243 /* Debug Message */
1244 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1245 THEN
1246 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1247 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
1248 FND_MSG_PUB.Add;
1249 END IF;
1250
1251
1252 IF ( (p_TERR_QTYPE_USGS_REC.TERR_QTYPE_USG_ID IS NOT NULL) AND
1253 (p_TERR_QTYPE_USGS_REC.TERR_QTYPE_USG_ID <> FND_API.G_MISS_NUM) ) THEN
1254
1255 -- bind the input variables
1256 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QTYPE_USGS_1', p_TERR_QTYPE_USGS_rec.TERR_QTYPE_USG_ID);
1257
1258 END IF;
1259
1260 IF ( (p_TERR_QTYPE_USGS_REC.TERR_ID IS NOT NULL) AND
1261 (p_TERR_QTYPE_USGS_REC.TERR_ID <> FND_API.G_MISS_NUM) ) THEN
1262
1263 -- bind the input variables
1264 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QTYPE_USGS_7', p_TERR_QTYPE_USGS_rec.TERR_ID);
1265
1266 END IF;
1267
1268 IF ( (p_TERR_QTYPE_USGS_REC.QUAL_TYPE_USG_ID IS NOT NULL) AND
1269 (p_TERR_QTYPE_USGS_REC.QUAL_TYPE_USG_ID <> FND_API.G_MISS_NUM) ) THEN
1270
1271 -- bind the input variables
1272 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QTYPE_USGS_8', p_TERR_QTYPE_USGS_rec.QUAL_TYPE_USG_ID);
1273
1274 END IF;
1275
1276 IF ( (p_TERR_QTYPE_USGS_REC.SOURCE_ID IS NOT NULL) AND
1277 (p_TERR_QTYPE_USGS_REC.SOURCE_ID <> FND_API.G_MISS_NUM) ) THEN
1278
1279 -- bind the input variables
1280 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QTYPE_USGS_10', p_TERR_QTYPE_USGS_rec.SOURCE_ID);
1281
1282 END IF;
1283
1284 IF ( (p_TERR_QTYPE_USGS_REC.QUAL_TYPE_ID IS NOT NULL) AND
1285 (p_TERR_QTYPE_USGS_REC.QUAL_TYPE_ID <> FND_API.G_MISS_NUM) ) THEN
1286
1287 -- bind the input variables
1288 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QTYPE_USGS_11', p_TERR_QTYPE_USGS_rec.QUAL_TYPE_ID);
1289
1290 END IF;
1291
1292 IF ( (p_TERR_QTYPE_USGS_REC.QUALIFIER_TYPE_NAME IS NOT NULL) AND
1293 (p_TERR_QTYPE_USGS_REC.QUALIFIER_TYPE_NAME <> FND_API.G_MISS_CHAR) ) THEN
1294
1295 -- bind the input variables
1296 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QTYPE_USGS_12', p_TERR_QTYPE_USGS_rec.QUALIFIER_TYPE_NAME);
1297
1298 END IF;
1299
1300 IF ( (p_TERR_QTYPE_USGS_REC.QUALIFIER_TYPE_DESCRIPTION IS NOT NULL) AND
1301 (p_TERR_QTYPE_USGS_REC.QUALIFIER_TYPE_DESCRIPTION <> FND_API.G_MISS_CHAR) ) THEN
1302
1303 -- bind the input variables
1304 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QTYPE_USGS_13', p_TERR_QTYPE_USGS_rec.QUALIFIER_TYPE_DESCRIPTION);
1305
1306 END IF;
1307
1308
1309 /* Debug Message */
1310 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1311 THEN
1312 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
1313 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
1314 FND_MSG_PUB.Add;
1315 END IF;
1316
1317 END gen_bind;
1318
1319 -----------------------------------------------------------------------
1320 -- Territory Qualifiers
1321 -----------------------------------------------------------------------
1322 PROCEDURE gen_where_clause (
1323 p_dsql_csr IN NUMBER,
1324 p_Terr_Qual_Rec IN Terr_Qual_Rec_Type,
1325 x_where_clause IN OUT NOCOPY VARCHAR2,
1326 x_use_flag IN OUT NOCOPY VARCHAR2
1327 )
1328 IS
1329
1330 l_proc_name VARCHAR2(30) := 'Gen_Where: Terr_Qual';
1331
1332 /* cursors to check if wildcard values '%' and '_' have been passed as item values */
1333 CURSOR c_chk_str1 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '%', 1, 1) FROM DUAL;
1334 CURSOR c_chk_str2 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '_', 1, 1) FROM DUAL;
1335
1336 /* return values from cursors */
1337 str_csr1 NUMBER;
1338 str_csr2 NUMBER;
1339
1340 l_operator VARCHAR2(10);
1341 l_where_clause VARCHAR2(2000);
1342
1343 BEGIN
1344
1345 /* Debug Message */
1346 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1347 THEN
1348 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1349 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
1350 FND_MSG_PUB.Add;
1351 END IF;
1352
1353
1354 IF ( (p_TERR_QUAL_rec.TERR_QUAL_ID IS NOT NULL) AND
1355 (p_TERR_QUAL_rec.TERR_QUAL_ID <> FND_API.G_MISS_NUM) ) THEN
1356
1357 x_use_flag := 'Y';
1358 l_where_clause := l_where_clause || 'AND JTQV.TERR_QUAL_ID = :p_TERR_QUAL_1 ';
1359
1360 END IF;
1361
1362 IF ( (p_TERR_QUAL_rec.TERR_ID IS NOT NULL) AND
1363 (p_TERR_QUAL_rec.TERR_ID <> FND_API.G_MISS_NUM) ) THEN
1364
1365 x_use_flag := 'Y';
1366 l_where_clause := l_where_clause || 'AND JTQV.TERR_ID = :p_TERR_QUAL_7 ';
1367
1368 END IF;
1369
1370 IF ( (p_TERR_QUAL_rec.QUAL_USG_ID IS NOT NULL) AND
1371 (p_TERR_QUAL_rec.QUAL_USG_ID <> FND_API.G_MISS_NUM) ) THEN
1372
1373 x_use_flag := 'Y';
1374 l_where_clause := l_where_clause || 'AND JTQV.QUAL_USG_ID = :p_TERR_QUAL_8 ';
1375
1376 END IF;
1377
1378
1379 IF ( (p_TERR_QUAL_rec.OVERLAP_ALLOWED_FLAG IS NOT NULL) AND
1380 (p_TERR_QUAL_rec.OVERLAP_ALLOWED_FLAG <> FND_API.G_MISS_CHAR) ) THEN
1381
1382 x_use_flag := 'Y';
1383 l_where_clause := l_where_clause || 'AND JTQV.OVERLAP_ALLOWED_FLAG ' ||
1384 l_operator || ' :p_TERR_QUAL_11 ';
1385
1386 END IF;
1387
1388 IF ( (p_TERR_QUAL_rec.QUALIFIER_MODE IS NOT NULL) AND
1389 (p_TERR_QUAL_rec.QUALIFIER_MODE <> FND_API.G_MISS_CHAR) ) THEN
1390
1391 -- check if item value contains '%' wildcard
1392 OPEN c_chk_str1 ( p_TERR_QUAL_rec.QUALIFIER_MODE);
1393 FETCH c_chk_str1 INTO str_csr1;
1394 CLOSE c_chk_str1;
1395
1396 IF ( str_csr1 <> 0 ) THEN
1397 l_operator := 'LIKE';
1398 ELSE
1399
1400 -- check if item value contains '_' wildcard
1401 OPEN c_chk_str2 ( p_TERR_QUAL_rec.QUALIFIER_MODE);
1402 FETCH c_chk_str2 INTO str_csr2;
1403 CLOSE c_chk_str2;
1404
1405 IF ( str_csr2 <> 0 ) THEN
1406 l_operator := 'LIKE';
1407 ELSE
1408 l_operator := '=';
1409 END IF;
1410
1411 END IF;
1412
1413 x_use_flag := 'Y';
1414 l_where_clause := l_where_clause || 'AND JTQV.QUALIFIER_MODE '
1415 || l_operator || ' :p_TERR_QUAL_13 ';
1416
1417 END IF;
1418
1419
1420 IF ( (p_TERR_QUAL_rec.DISPLAY_TYPE IS NOT NULL) AND
1421 (p_TERR_QUAL_rec.DISPLAY_TYPE <> FND_API.G_MISS_CHAR) ) THEN
1422
1423 -- check if item value contains '%' wildcard
1424 OPEN c_chk_str1 ( p_TERR_QUAL_rec.DISPLAY_TYPE);
1425 FETCH c_chk_str1 INTO str_csr1;
1426 CLOSE c_chk_str1;
1427
1428 IF ( str_csr1 <> 0 ) THEN
1429 l_operator := 'LIKE';
1430 ELSE
1431
1432 -- check if item value contains '_' wildcard
1433 OPEN c_chk_str2 ( p_TERR_QUAL_rec.DISPLAY_TYPE);
1434 FETCH c_chk_str2 INTO str_csr2;
1435 CLOSE c_chk_str2;
1436
1437 IF ( str_csr2 <> 0 ) THEN
1438 l_operator := 'LIKE';
1439 ELSE
1440 l_operator := '=';
1441 END IF;
1442
1443 END IF;
1444
1445 x_use_flag := 'Y';
1446 l_where_clause := l_where_clause || 'AND JTQV.DISPLAY_TYPE '
1447 || l_operator || ' :p_TERR_QUAL_14 ';
1448
1449 END IF;
1450
1451 IF ( (p_TERR_QUAL_rec.LOV_SQL IS NOT NULL) AND
1452 (p_TERR_QUAL_rec.LOV_SQL <> FND_API.G_MISS_CHAR) ) THEN
1453
1454 -- check if item value contains '%' wildcard
1455 OPEN c_chk_str1 ( p_TERR_QUAL_rec.LOV_SQL);
1456 FETCH c_chk_str1 INTO str_csr1;
1457 CLOSE c_chk_str1;
1458
1459 IF ( str_csr1 <> 0 ) THEN
1460 l_operator := 'LIKE';
1461 ELSE
1462
1463 -- check if item value contains '_' wildcard
1464 OPEN c_chk_str2 ( p_TERR_QUAL_rec.LOV_SQL);
1465 FETCH c_chk_str2 INTO str_csr2;
1466 CLOSE c_chk_str2;
1467
1468 IF ( str_csr2 <> 0 ) THEN
1469 l_operator := 'LIKE';
1470 ELSE
1471 l_operator := '=';
1472 END IF;
1473
1474 END IF;
1475
1476 x_use_flag := 'Y';
1477 l_where_clause := l_where_clause || 'AND JTQV.LOV_SQL '
1478 || l_operator || ' :p_TERR_QUAL_15 ';
1479
1480 END IF;
1481
1482
1483 IF ( (p_TERR_QUAL_rec.CONVERT_TO_ID_FLAG IS NOT NULL) AND
1484 (p_TERR_QUAL_rec.CONVERT_TO_ID_FLAG <> FND_API.G_MISS_CHAR) ) THEN
1485
1486 x_use_flag := 'Y';
1487 l_where_clause := l_where_clause || 'AND JTQV.CONVERT_TO_ID_FLAG = :p_TERR_QUAL_16 ';
1488
1489 END IF;
1490
1491 IF ( (p_TERR_QUAL_rec.QUAL_TYPE_ID IS NOT NULL) AND
1492 (p_TERR_QUAL_rec.QUAL_TYPE_ID <> FND_API.G_MISS_NUM) ) THEN
1493
1494 x_use_flag := 'Y';
1495 l_where_clause := l_where_clause || 'AND JTQV.QUAL_TYPE_ID = :p_TERR_QUAL_17 ';
1496
1497 END IF;
1498
1499 IF ( (p_TERR_QUAL_rec.QUALIFIER_TYPE_NAME IS NOT NULL) AND
1500 (p_TERR_QUAL_rec.QUALIFIER_TYPE_NAME <> FND_API.G_MISS_CHAR) ) THEN
1501
1502 -- check if item value contains '%' wildcard
1503 OPEN c_chk_str1 ( p_TERR_QUAL_rec.QUALIFIER_TYPE_NAME);
1504 FETCH c_chk_str1 INTO str_csr1;
1505 CLOSE c_chk_str1;
1506
1507 IF ( str_csr1 <> 0 ) THEN
1508 l_operator := 'LIKE';
1509 ELSE
1510
1511 -- check if item value contains '_' wildcard
1512 OPEN c_chk_str2 ( p_TERR_QUAL_rec.QUALIFIER_TYPE_NAME);
1513 FETCH c_chk_str2 INTO str_csr2;
1514 CLOSE c_chk_str2;
1515
1516 IF ( str_csr2 <> 0 ) THEN
1517 l_operator := 'LIKE';
1518 ELSE
1519 l_operator := '=';
1520 END IF;
1521
1522 END IF;
1523
1524 x_use_flag := 'Y';
1525 l_where_clause := l_where_clause || 'AND JTQV.QUALIFIER_TYPE_NAME '
1526 || l_operator || ' :p_TERR_QUAL_18 ';
1527
1528 END IF;
1529
1530 IF ( (p_Terr_Qual_Rec.QUALIFIER_TYPE_DESCRIPTION IS NOT NULL) AND
1531 (p_Terr_Qual_Rec.QUALIFIER_TYPE_DESCRIPTION <> FND_API.G_MISS_CHAR) ) THEN
1532
1533 -- check if item value contains '%' wildcard
1534 OPEN c_chk_str1 ( p_TERR_QUAL_rec.QUALIFIER_TYPE_DESCRIPTION);
1535 FETCH c_chk_str1 INTO str_csr1;
1536 CLOSE c_chk_str1;
1537
1538 IF ( str_csr1 <> 0 ) THEN
1539 l_operator := 'LIKE';
1540 ELSE
1541
1542 -- check if item value contains '_' wildcard
1543 OPEN c_chk_str2 ( p_TERR_QUAL_rec.QUALIFIER_TYPE_DESCRIPTION);
1544 FETCH c_chk_str2 INTO str_csr2;
1545 CLOSE c_chk_str2;
1546
1547 IF ( str_csr2 <> 0 ) THEN
1548 l_operator := 'LIKE';
1549 ELSE
1550 l_operator := '=';
1551 END IF;
1552
1553 END IF;
1554
1555 x_use_flag := 'Y';
1556 l_where_clause := l_where_clause || 'AND JTQV.QUALIFIER_TYPE_DESCRIPTION '
1557 || l_operator || ' :p_TERR_QUAL_19 ';
1558
1559 END IF;
1560
1561 IF ( (p_TERR_QUAL_rec.QUALIFIER_NAME IS NOT NULL) AND
1562 (p_TERR_QUAL_rec.QUALIFIER_NAME <> FND_API.G_MISS_CHAR) ) THEN
1563
1564 -- check if item value contains '%' wildcard
1565 OPEN c_chk_str1 ( p_TERR_QUAL_rec.QUALIFIER_NAME);
1566 FETCH c_chk_str1 INTO str_csr1;
1567 CLOSE c_chk_str1;
1568
1569 --dbms_output.put_line('Value of str_csr1='||TO_CHAR(str_csr1));
1570
1571 IF ( str_csr1 <> 0 ) THEN
1572 l_operator := 'LIKE';
1573 ELSE
1574
1575 -- check if item value contains '_' wildcard
1576 OPEN c_chk_str2 ( p_TERR_QUAL_rec.QUALIFIER_NAME);
1577 FETCH c_chk_str2 INTO str_csr2;
1578 CLOSE c_chk_str2;
1579
1580 IF ( str_csr2 <> 0 ) THEN
1581 l_operator := 'LIKE';
1582 ELSE
1583 l_operator := '=';
1584 END IF;
1585
1586 END IF;
1587
1588 x_use_flag := 'Y';
1589 l_where_clause := l_where_clause || 'AND JTQV.QUALIFIER_NAME '
1590 || l_operator || ' :p_TERR_QUAL_20 ';
1591
1592 END IF;
1593
1594 x_where_clause := x_where_clause || l_where_clause;
1595
1596 /* Debug Message */
1597 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1598 THEN
1599 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
1600 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
1601 FND_MSG_PUB.Add;
1602 END IF;
1603
1604
1605 END gen_where_clause;
1606
1607
1608 PROCEDURE gen_bind (
1609 p_dsql_csr IN NUMBER,
1610 p_Terr_Qual_Rec IN Terr_Qual_Rec_Type
1611 )
1612 IS
1613 l_proc_name VARCHAR2(30) := 'Gen_Bind: Terr_Qual';
1614 BEGIN
1615
1616
1617 /* Debug Message */
1618 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1619 THEN
1620 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1621 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
1622 FND_MSG_PUB.Add;
1623 END IF;
1624
1625 IF ( (p_TERR_QUAL_rec.TERR_QUAL_ID IS NOT NULL) AND
1626 (p_TERR_QUAL_rec.TERR_QUAL_ID <> FND_API.G_MISS_NUM) ) THEN
1627
1628 -- bind the input variables
1629 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QUAL_1', p_TERR_QUAL_rec.TERR_QUAL_ID);
1630
1631 END IF;
1632
1633 IF ( (p_TERR_QUAL_rec.TERR_ID IS NOT NULL) AND
1634 (p_TERR_QUAL_rec.TERR_ID <> FND_API.G_MISS_NUM) ) THEN
1635
1636 -- bind the input variables
1637 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QUAL_7', p_TERR_QUAL_rec.TERR_ID);
1638
1639 END IF;
1640
1641 IF ( (p_TERR_QUAL_rec.QUAL_USG_ID IS NOT NULL) AND
1642 (p_TERR_QUAL_rec.QUAL_USG_ID <> FND_API.G_MISS_NUM) ) THEN
1643
1644 -- bind the input variables
1645 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QUAL_8', p_TERR_QUAL_rec.QUAL_USG_ID);
1646
1647 END IF;
1648
1649 IF ( (p_TERR_QUAL_rec.OVERLAP_ALLOWED_FLAG IS NOT NULL) AND
1650 (p_TERR_QUAL_rec.OVERLAP_ALLOWED_FLAG <> FND_API.G_MISS_CHAR) ) THEN
1651
1652 -- bind the input variables
1653 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QUAL_11', p_TERR_QUAL_rec.OVERLAP_ALLOWED_FLAG);
1654
1655 END IF;
1656
1657 IF ( (p_TERR_QUAL_rec.QUALIFIER_MODE IS NOT NULL) AND
1658 (p_TERR_QUAL_rec.QUALIFIER_MODE <> FND_API.G_MISS_CHAR) ) THEN
1659
1660 -- bind the input variables
1661 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QUAL_13', p_TERR_QUAL_rec.QUALIFIER_MODE);
1662
1663 END IF;
1664
1665 IF ( (p_TERR_QUAL_rec.DISPLAY_TYPE IS NOT NULL) AND
1666 (p_TERR_QUAL_rec.DISPLAY_TYPE <> FND_API.G_MISS_CHAR) ) THEN
1667
1668 -- bind the input variables
1669 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QUAL_14', p_TERR_QUAL_rec.DISPLAY_TYPE);
1670
1671 END IF;
1672
1673 IF ( (p_TERR_QUAL_rec.LOV_SQL IS NOT NULL) AND
1674 (p_TERR_QUAL_rec.LOV_SQL <> FND_API.G_MISS_CHAR) ) THEN
1675
1676 -- bind the input variables
1677 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QUAL_15', p_TERR_QUAL_rec.LOV_SQL);
1678
1679 END IF;
1680
1681 IF ( (p_TERR_QUAL_rec.CONVERT_TO_ID_FLAG IS NOT NULL) AND
1682 (p_TERR_QUAL_rec.CONVERT_TO_ID_FLAG <> FND_API.G_MISS_CHAR) ) THEN
1683
1684
1685 -- bind the input variables
1686 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QUAL_16', p_TERR_QUAL_rec.CONVERT_TO_ID_FLAG);
1687
1688 END IF;
1689
1690 IF ( (p_TERR_QUAL_rec.QUAL_TYPE_ID IS NOT NULL) AND
1691 (p_TERR_QUAL_rec.QUAL_TYPE_ID <> FND_API.G_MISS_NUM) ) THEN
1692
1693 -- bind the input variables
1694 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QUAL_17', p_TERR_QUAL_rec.QUAL_TYPE_ID);
1695
1696 END IF;
1697
1698 IF ( (p_TERR_QUAL_rec.QUALIFIER_TYPE_NAME IS NOT NULL) AND
1699 (p_TERR_QUAL_rec.QUALIFIER_TYPE_NAME <> FND_API.G_MISS_CHAR) ) THEN
1700
1701 -- bind the input variables
1702 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QUAL_18', p_TERR_QUAL_rec.QUALIFIER_TYPE_NAME);
1703
1704 END IF;
1705
1706 IF ( (p_TERR_QUAL_rec.QUALIFIER_TYPE_DESCRIPTION IS NOT NULL) AND
1707 (p_TERR_QUAL_rec.QUALIFIER_TYPE_DESCRIPTION <> FND_API.G_MISS_CHAR) ) THEN
1708
1709 -- bind the input variables
1710 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QUAL_19', p_TERR_QUAL_rec.QUALIFIER_TYPE_DESCRIPTION);
1711
1712 END IF;
1713
1714 IF ( (p_TERR_QUAL_rec.QUALIFIER_NAME IS NOT NULL) AND
1715 (p_TERR_QUAL_rec.QUALIFIER_NAME <> FND_API.G_MISS_CHAR) ) THEN
1716
1717 -- bind the input variables
1718 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_QUAL_20', p_TERR_QUAL_rec.QUALIFIER_NAME);
1719
1720 END IF;
1721
1722 /* Debug Message */
1723 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1724 THEN
1725 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
1726 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
1727 FND_MSG_PUB.Add;
1728 END IF;
1729
1730 END gen_bind;
1731
1732
1733 -----------------------------------------------------------------------
1734 -- Territory Values
1735 -----------------------------------------------------------------------
1736 PROCEDURE gen_where_clause (
1737 p_dsql_csr IN NUMBER,
1738 p_Terr_Values_Rec IN Terr_Values_Rec_Type,
1739 x_where_clause IN OUT NOCOPY VARCHAR2,
1740 x_use_flag IN OUT NOCOPY VARCHAR2
1741 )
1742 IS
1743
1744 l_proc_name VARCHAR2(30) := 'Gen_Where: Terr_Values';
1745
1746 /* cursors to check if wildcard values '%' and '_' have been passed as item values */
1747 CURSOR c_chk_str1 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '%', 1, 1) FROM DUAL;
1748 CURSOR c_chk_str2 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '_', 1, 1) FROM DUAL;
1749
1750 /* return values from cursors */
1751 str_csr1 NUMBER;
1752 str_csr2 NUMBER;
1753
1754 l_operator VARCHAR2(10);
1755
1756 l_where_clause VARCHAR(2000);
1757
1758 BEGIN
1759
1760 /* Debug Message */
1761 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1762 THEN
1763 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1764 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
1765 FND_MSG_PUB.Add;
1766 END IF;
1767
1768 IF ( (p_TERR_VALUES_rec.TERR_VALUE_ID IS NOT NULL) AND
1769 (p_TERR_VALUES_rec.TERR_VALUE_ID <> FND_API.G_MISS_NUM) ) THEN
1770
1771 x_use_flag := 'Y';
1772 l_where_clause := l_where_clause || 'AND JTV.TERR_VALUE_ID = :p_TERR_VALUES_1 ';
1773
1774 END IF;
1775
1776 IF ( (p_TERR_VALUES_rec.TERR_QUAL_ID IS NOT NULL) AND
1777 (p_TERR_VALUES_rec.TERR_QUAL_ID <> FND_API.G_MISS_NUM) ) THEN
1778
1779 x_use_flag := 'Y';
1780 l_where_clause := l_where_clause || 'AND JTV.TERR_QUAL_ID = :p_TERR_VALUES_7 ';
1781
1782 END IF;
1783
1784 IF ( (p_TERR_VALUES_rec.COMPARISON_OPERATOR IS NOT NULL) AND
1785 (p_TERR_VALUES_rec.COMPARISON_OPERATOR <> FND_API.G_MISS_CHAR) ) THEN
1786
1787 -- check if item value contains '%' wildcard
1788 OPEN c_chk_str1 ( p_TERR_VALUES_rec.COMPARISON_OPERATOR);
1789 FETCH c_chk_str1 INTO str_csr1;
1790 CLOSE c_chk_str1;
1791
1792 IF ( str_csr1 <> 0 ) THEN
1793 l_operator := 'LIKE';
1794 ELSE
1795
1796 -- check if item value contains '_' wildcard
1797 OPEN c_chk_str2 ( p_TERR_VALUES_rec.COMPARISON_OPERATOR);
1798 FETCH c_chk_str2 INTO str_csr2;
1799 CLOSE c_chk_str2;
1800
1801 IF ( str_csr2 <> 0 ) THEN
1802 l_operator := 'LIKE';
1803 ELSE
1804 l_operator := '=';
1805 END IF;
1806
1807 END IF;
1808
1809 x_use_flag := 'Y';
1810 l_where_clause := l_where_clause || 'AND JTV.COMPARISON_OPERATOR ' ||
1811 l_operator || ' :p_TERR_VALUES_9 ';
1812
1813 END IF;
1814
1815 IF ( (p_TERR_VALUES_rec.LOW_VALUE_CHAR IS NOT NULL) AND
1816 (p_TERR_VALUES_rec.LOW_VALUE_CHAR <> FND_API.G_MISS_CHAR) ) THEN
1817
1818 -- check if item value contains '%' wildcard
1819 OPEN c_chk_str1 ( p_TERR_VALUES_rec.LOW_VALUE_CHAR);
1820 FETCH c_chk_str1 INTO str_csr1;
1821 CLOSE c_chk_str1;
1822
1823 IF ( str_csr1 <> 0 ) THEN
1824 l_operator := 'LIKE';
1825 ELSE
1826
1827 -- check if item value contains '_' wildcard
1828 OPEN c_chk_str2 ( p_TERR_VALUES_rec.LOW_VALUE_CHAR);
1829 FETCH c_chk_str2 INTO str_csr2;
1830 CLOSE c_chk_str2;
1831
1832 IF ( str_csr2 <> 0 ) THEN
1833 l_operator := 'LIKE';
1834 ELSE
1835 l_operator := '=';
1836 END IF;
1837
1838 END IF;
1839
1840 x_use_flag := 'Y';
1841 l_where_clause := l_where_clause || 'AND JTV.LOW_VALUE_CHAR ' ||
1842 l_operator || ' :p_TERR_VALUES_10 ';
1843
1844 END IF;
1845
1846 IF ( (p_TERR_VALUES_rec.HIGH_VALUE_CHAR IS NOT NULL) AND
1847 (p_TERR_VALUES_rec.HIGH_VALUE_CHAR <> FND_API.G_MISS_CHAR) ) THEN
1848
1849 -- check if item value contains '%' wildcard
1850 OPEN c_chk_str1 ( p_TERR_VALUES_rec.HIGH_VALUE_CHAR);
1851 FETCH c_chk_str1 INTO str_csr1;
1852 CLOSE c_chk_str1;
1853
1854 IF ( str_csr1 <> 0 ) THEN
1855 l_operator := 'LIKE';
1856 ELSE
1857
1858 -- check if item value contains '_' wildcard
1859 OPEN c_chk_str2 ( p_TERR_VALUES_rec.HIGH_VALUE_CHAR);
1860 FETCH c_chk_str2 INTO str_csr2;
1861 CLOSE c_chk_str2;
1862
1863 IF ( str_csr2 <> 0 ) THEN
1864 l_operator := 'LIKE';
1865 ELSE
1866 l_operator := '=';
1867 END IF;
1868
1869 END IF;
1870
1871 x_use_flag := 'Y';
1872 l_where_clause := l_where_clause || 'AND JTV.HIGH_VALUE_CHAR ' ||
1873 l_operator || ' :p_TERR_VALUES_11 ';
1874
1875 END IF;
1876
1877 IF ( (p_TERR_VALUES_rec.LOW_VALUE_NUMBER IS NOT NULL) AND
1878 (p_TERR_VALUES_rec.LOW_VALUE_NUMBER <> FND_API.G_MISS_NUM) ) THEN
1879
1880 x_use_flag := 'Y';
1881 l_where_clause := l_where_clause || 'AND JTV.LOW_VALUE_NUMBER = :p_TERR_VALUES_12 ';
1882
1883 END IF;
1884
1885 IF ( (p_TERR_VALUES_rec.HIGH_VALUE_NUMBER IS NOT NULL) AND
1886 (p_TERR_VALUES_rec.HIGH_VALUE_NUMBER <> FND_API.G_MISS_NUM) ) THEN
1887
1888 x_use_flag := 'Y';
1889 l_where_clause := l_where_clause || 'AND JTV.HIGH_VALUE_NUMBER = :p_TERR_VALUES_13 ';
1890
1891 END IF;
1892
1893
1894 IF ( (p_TERR_VALUES_rec.INTEREST_TYPE_ID IS NOT NULL) AND
1895 (p_TERR_VALUES_rec.INTEREST_TYPE_ID <> FND_API.G_MISS_NUM) ) THEN
1896
1897 x_use_flag := 'Y';
1898 l_where_clause := l_where_clause || 'AND JTV.INTEREST_TYPE_ID = :p_TERR_VALUES_15 ';
1899
1900 END IF;
1901
1902 IF ( (p_TERR_VALUES_rec.PRIMARY_INTEREST_CODE_ID IS NOT NULL) AND
1903 (p_TERR_VALUES_rec.PRIMARY_INTEREST_CODE_ID <> FND_API.G_MISS_NUM) ) THEN
1904
1905 x_use_flag := 'Y';
1906 l_where_clause := l_where_clause || 'AND JTV.PRIMARY_INTEREST_CODE_ID = :p_TERR_VALUES_16 ';
1907
1908 END IF;
1909
1910 IF ( (p_TERR_VALUES_rec.SECONDARY_INTEREST_CODE_ID IS NOT NULL) AND
1911 (p_TERR_VALUES_rec.SECONDARY_INTEREST_CODE_ID <> FND_API.G_MISS_NUM) ) THEN
1912
1913 x_use_flag := 'Y';
1914 x_where_clause := x_where_clause ||
1915 'AND JTV.SECONDARY_INTEREST_CODE_ID = :p_TERR_VALUES_17 ';
1916
1917 END IF;
1918
1919 IF ( (p_TERR_VALUES_rec.CURRENCY_CODE IS NOT NULL) AND
1920 (p_TERR_VALUES_rec.CURRENCY_CODE <> FND_API.G_MISS_CHAR) ) THEN
1921
1922 -- check if item value contains '%' wildcard
1923 OPEN c_chk_str1 ( p_TERR_VALUES_rec.CURRENCY_CODE);
1924 FETCH c_chk_str1 INTO str_csr1;
1925 CLOSE c_chk_str1;
1926
1927 IF ( str_csr1 <> 0 ) THEN
1928 l_operator := 'LIKE';
1929 ELSE
1930
1931 -- check if item value contains '_' wildcard
1932 OPEN c_chk_str2 ( p_TERR_VALUES_rec.CURRENCY_CODE);
1933 FETCH c_chk_str2 INTO str_csr2;
1934 CLOSE c_chk_str2;
1935
1936 IF ( str_csr2 <> 0 ) THEN
1937 l_operator := 'LIKE';
1938 ELSE
1939 l_operator := '=';
1940 END IF;
1941
1942 END IF;
1943
1944 x_use_flag := 'Y';
1945 l_where_clause := l_where_clause || 'AND JTV.CURRENCY_CODE ' ||
1946 l_operator || ' :p_TERR_VALUES_18 ';
1947
1948 END IF;
1949
1950 IF ( (p_TERR_VALUES_rec.ID_USED_FLAG IS NOT NULL) AND
1951 (p_TERR_VALUES_rec.ID_USED_FLAG <> FND_API.G_MISS_CHAR) ) THEN
1952
1953 x_use_flag := 'Y';
1954 x_where_clause := x_where_clause || 'AND JTV.ID_USED_FLAG = :p_TERR_VALUES_19 ';
1955
1956 END IF;
1957
1958 IF ( (p_TERR_VALUES_rec.LOW_VALUE_CHAR_ID IS NOT NULL) AND
1959 (p_TERR_VALUES_rec.LOW_VALUE_CHAR_ID <> FND_API.G_MISS_NUM) ) THEN
1960
1961 x_use_flag := 'Y';
1962 x_where_clause := x_where_clause || 'AND JTV.LOW_VALUE_CHAR_ID = :p_TERR_VALUES_20 ';
1963
1964 END IF;
1965
1966 x_where_clause := x_where_clause || l_where_clause;
1967
1968 /* Debug Message */
1969 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1970 THEN
1971 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
1972 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
1973 FND_MSG_PUB.Add;
1974 END IF;
1975
1976 END gen_where_clause;
1977
1978
1979 /* bind Territory Value record items */
1980 PROCEDURE gen_bind (
1981 p_dsql_csr IN NUMBER,
1982 p_Terr_Values_Rec IN Terr_Values_Rec_Type
1983 )
1984 IS
1985
1986 l_proc_name VARCHAR2(30) := 'Gen_Bind: Terr_Qual';
1987
1988 BEGIN
1989
1990 /* Debug Message */
1991 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1992 THEN
1993 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
1994 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
1995 FND_MSG_PUB.Add;
1996 END IF;
1997
1998 IF ( (p_TERR_VALUES_rec.TERR_VALUE_ID IS NOT NULL) AND
1999 (p_TERR_VALUES_rec.TERR_VALUE_ID <> FND_API.G_MISS_NUM) ) THEN
2000
2001 -- bind the input variables
2002 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_VALUES_1', p_TERR_VALUES_rec.TERR_VALUE_ID);
2003
2004 END IF;
2005
2006 IF ( (p_TERR_VALUES_rec.TERR_QUAL_ID IS NOT NULL) AND
2007 (p_TERR_VALUES_rec.TERR_QUAL_ID <> FND_API.G_MISS_NUM) ) THEN
2008
2009 -- bind the input variables
2010 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_VALUES_7', p_TERR_VALUES_rec.TERR_QUAL_ID);
2011
2012 END IF;
2013
2014 IF ( (p_TERR_VALUES_rec.COMPARISON_OPERATOR IS NOT NULL) AND
2015 (p_TERR_VALUES_rec.COMPARISON_OPERATOR <> FND_API.G_MISS_CHAR) ) THEN
2016
2017 -- bind the input variables
2018 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_VALUES_9', p_TERR_VALUES_rec.COMPARISON_OPERATOR);
2019
2020 END IF;
2021
2022 IF ( (p_TERR_VALUES_rec.LOW_VALUE_CHAR IS NOT NULL) AND
2023 (p_TERR_VALUES_rec.LOW_VALUE_CHAR <> FND_API.G_MISS_CHAR) ) THEN
2024
2025 --dbms_output.put_line( 'Value of p_TERR_VALUES_rec.LOW_VALUE_CHAR = ' ||
2026 -- p_TERR_VALUES_rec.LOW_VALUE_CHAR);
2027
2028 -- bind the input variables
2029 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_VALUES_10', p_TERR_VALUES_rec.LOW_VALUE_CHAR);
2030
2031 END IF;
2032
2033 IF ( (p_TERR_VALUES_rec.HIGH_VALUE_CHAR IS NOT NULL) AND
2034 (p_TERR_VALUES_rec.HIGH_VALUE_CHAR <> FND_API.G_MISS_CHAR) ) THEN
2035
2036 -- bind the input variables
2037 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_VALUES_11', p_TERR_VALUES_rec.HIGH_VALUE_CHAR);
2038
2039 END IF;
2040
2041 IF ( (p_TERR_VALUES_rec.LOW_VALUE_NUMBER IS NOT NULL) AND
2042 (p_TERR_VALUES_rec.LOW_VALUE_NUMBER <> FND_API.G_MISS_NUM) ) THEN
2043
2044 -- bind the input variables
2045 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_VALUES_12', p_TERR_VALUES_rec.LOW_VALUE_NUMBER);
2046
2047 END IF;
2048
2049 IF ( (p_TERR_VALUES_rec.HIGH_VALUE_NUMBER IS NOT NULL) AND
2050 (p_TERR_VALUES_rec.HIGH_VALUE_NUMBER <> FND_API.G_MISS_NUM) ) THEN
2051
2052 -- bind the input variables
2053 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_VALUES_13', p_TERR_VALUES_rec.HIGH_VALUE_NUMBER);
2054
2055 END IF;
2056
2057
2058 IF ( (p_TERR_VALUES_rec.INTEREST_TYPE_ID IS NOT NULL) AND
2059 (p_TERR_VALUES_rec.INTEREST_TYPE_ID <> FND_API.G_MISS_NUM) ) THEN
2060
2061 -- bind the input variables
2062 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_VALUES_15', p_TERR_VALUES_rec.INTEREST_TYPE_ID);
2063
2064 END IF;
2065
2066 IF ( (p_TERR_VALUES_rec.PRIMARY_INTEREST_CODE_ID IS NOT NULL) AND
2067 (p_TERR_VALUES_rec.PRIMARY_INTEREST_CODE_ID <> FND_API.G_MISS_NUM) ) THEN
2068
2069 -- bind the input variables
2070 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_VALUES_16', p_TERR_VALUES_rec.PRIMARY_INTEREST_CODE_ID);
2071
2072 END IF;
2073
2074 IF ( (p_TERR_VALUES_rec.SECONDARY_INTEREST_CODE_ID IS NOT NULL) AND
2075 (p_TERR_VALUES_rec.SECONDARY_INTEREST_CODE_ID <> FND_API.G_MISS_NUM) ) THEN
2076
2077 -- bind the input variables
2078 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_VALUES_17', p_TERR_VALUES_rec.SECONDARY_INTEREST_CODE_ID);
2079
2080 END IF;
2081
2082 IF ( (p_TERR_VALUES_rec.CURRENCY_CODE IS NOT NULL) AND
2083 (p_TERR_VALUES_rec.CURRENCY_CODE <> FND_API.G_MISS_CHAR) ) THEN
2084
2085 -- bind the input variables
2086 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_VALUES_18', p_TERR_VALUES_rec.CURRENCY_CODE);
2087
2088 END IF;
2089
2090 IF ( (p_TERR_VALUES_rec.ID_USED_FLAG IS NOT NULL) AND
2091 (p_TERR_VALUES_rec.ID_USED_FLAG <> FND_API.G_MISS_CHAR) ) THEN
2092
2093 -- bind the input variables
2094 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_VALUES_19', p_TERR_VALUES_rec.ID_USED_FLAG);
2095
2096 END IF;
2097
2098 IF ( (p_TERR_VALUES_rec.LOW_VALUE_CHAR_ID IS NOT NULL) AND
2099 (p_TERR_VALUES_rec.LOW_VALUE_CHAR_ID <> FND_API.G_MISS_NUM) ) THEN
2100
2101 -- bind the input variables
2102 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_VALUES_20', p_TERR_VALUES_rec.LOW_VALUE_CHAR_ID);
2103
2104 END IF;
2105
2106 /* Debug Message */
2107 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2108 THEN
2109 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2110 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
2111 FND_MSG_PUB.Add;
2112 END IF;
2113
2114 END gen_bind;
2115
2116
2117
2118 -----------------------------------------------------------------------
2119 -- Territory Resources
2120 -----------------------------------------------------------------------
2121 PROCEDURE gen_where_clause (
2122 p_dsql_csr IN NUMBER,
2123 p_Terr_Rsc_Rec IN Terr_Rsc_Rec_Type,
2124 x_where_clause IN OUT NOCOPY VARCHAR2,
2125 x_use_flag IN OUT NOCOPY VARCHAR2
2126 )
2127 IS
2128
2129 l_proc_name VARCHAR2(30) := 'Gen_Where: Terr_Rsc';
2130
2131 /* cursors to check if wildcard values '%' and '_' have been passed as item values */
2132 CURSOR c_chk_str1 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '%', 1, 1) FROM DUAL;
2133 CURSOR c_chk_str2 (p_rec_item VARCHAR2) IS SELECT INSTR(p_rec_item, '_', 1, 1) FROM DUAL;
2134
2135 /* return values from cursors */
2136 str_csr1 NUMBER;
2137 str_csr2 NUMBER;
2138
2139 l_operator VARCHAR2(10);
2140 l_where_clause VARCHAR2(2000);
2141
2142 BEGIN
2143
2144 /* Debug Message */
2145 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2146 THEN
2147 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
2148 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
2149 FND_MSG_PUB.Add;
2150 END IF;
2151
2152 IF ( (p_TERR_RSC_rec.TERR_RSC_ID IS NOT NULL) AND
2153 (p_TERR_RSC_rec.TERR_RSC_ID <> FND_API.G_MISS_NUM) ) THEN
2154
2155 x_use_flag := 'Y';
2156 l_where_clause := l_where_clause || 'AND JTRV.TERR_RSC_ID = :p_TERR_RSC_1 ';
2157
2158 END IF;
2159
2160 IF ( (p_TERR_RSC_rec.TERR_ID IS NOT NULL) AND
2161 (p_TERR_RSC_rec.TERR_ID <> FND_API.G_MISS_NUM) ) THEN
2162
2163 x_use_flag := 'Y';
2164 l_where_clause := l_where_clause || 'AND JTRV.TERR_ID = :p_TERR_RSC_7 ';
2165
2166 END IF;
2167
2168 IF ( (p_TERR_RSC_rec.RESOURCE_ID IS NOT NULL) AND
2169 (p_TERR_RSC_rec.RESOURCE_ID <> FND_API.G_MISS_NUM) ) THEN
2170
2171 x_use_flag := 'Y';
2172 l_where_clause := l_where_clause || 'AND JTRV.RESOURCE_ID = :p_TERR_RSC_8 ';
2173
2174 END IF;
2175
2176 IF ( (p_TERR_RSC_rec.RESOURCE_TYPE IS NOT NULL) AND
2177 (p_TERR_RSC_rec.RESOURCE_TYPE <> FND_API.G_MISS_CHAR) ) THEN
2178
2179 -- check if item value contains '%' wildcard
2180 OPEN c_chk_str1 ( p_TERR_RSC_rec.RESOURCE_TYPE);
2181 FETCH c_chk_str1 INTO str_csr1;
2182 CLOSE c_chk_str1;
2183
2184 IF ( str_csr1 <> 0 ) THEN
2185 l_operator := 'LIKE';
2186 ELSE
2187
2188 -- check if item value contains '_' wildcard
2189 OPEN c_chk_str2 ( p_TERR_RSC_rec.RESOURCE_TYPE);
2190 FETCH c_chk_str2 INTO str_csr2;
2191 CLOSE c_chk_str2;
2192
2193 IF ( str_csr2 <> 0 ) THEN
2194 l_operator := 'LIKE';
2195 ELSE
2196 l_operator := '=';
2197 END IF;
2198
2199 END IF;
2200
2201 x_use_flag := 'Y';
2202 l_where_clause := l_where_clause || 'AND JTRV.RESOURCE_TYPE ' ||
2203 l_operator || ' :p_TERR_RSC_9 ';
2204
2205 END IF;
2206
2207 IF ( (p_TERR_RSC_rec.ROLE IS NOT NULL) AND
2208 (p_TERR_RSC_rec.ROLE <> FND_API.G_MISS_CHAR) ) THEN
2209
2210 -- check if item value contains '%' wildcard
2211 OPEN c_chk_str1 ( p_TERR_RSC_rec.ROLE);
2212 FETCH c_chk_str1 INTO str_csr1;
2213 CLOSE c_chk_str1;
2214
2215 IF ( str_csr1 <> 0 ) THEN
2216 l_operator := 'LIKE';
2217 ELSE
2218
2219 -- check if item value contains '_' wildcard
2220 OPEN c_chk_str2 ( p_TERR_RSC_rec.ROLE);
2221 FETCH c_chk_str2 INTO str_csr2;
2222 CLOSE c_chk_str2;
2223
2224 IF ( str_csr2 <> 0 ) THEN
2225 l_operator := 'LIKE';
2226 ELSE
2227 l_operator := '=';
2228 END IF;
2229
2230 END IF;
2231
2232 x_use_flag := 'Y';
2233 l_where_clause := l_where_clause || 'AND JTRV.ROLE ' ||
2234 l_operator || ' :p_TERR_RSC_10 ';
2235
2236
2237 END IF;
2238
2239 IF ( (p_TERR_RSC_rec.PRIMARY_CONTACT_FLAG IS NOT NULL) AND
2240 (p_TERR_RSC_rec.PRIMARY_CONTACT_FLAG <> FND_API.G_MISS_CHAR) ) THEN
2241
2242 x_use_flag := 'Y';
2243 l_where_clause := l_where_clause || 'AND JTRV.PRIMARY_CONTACT_FLAG = :p_TERR_RSC_11';
2244
2245 END IF;
2246
2247 IF ( (p_TERR_RSC_rec.START_DATE_ACTIVE IS NOT NULL) AND
2248 (p_TERR_RSC_rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE) ) THEN
2249
2250 -- check if item value contains '%' wildcard
2251 OPEN c_chk_str1 ( p_TERR_RSC_rec.START_DATE_ACTIVE);
2252 FETCH c_chk_str1 INTO str_csr1;
2253 CLOSE c_chk_str1;
2254
2255 IF ( str_csr1 <> 0 ) THEN
2256 l_operator := 'LIKE';
2257 ELSE
2258
2259 -- check if item value contains '_' wildcard
2260 OPEN c_chk_str2 ( p_TERR_RSC_rec.START_DATE_ACTIVE);
2261 FETCH c_chk_str2 INTO str_csr2;
2262 CLOSE c_chk_str2;
2263
2264 IF ( str_csr2 <> 0 ) THEN
2265 l_operator := 'LIKE';
2266 ELSE
2267 l_operator := '=';
2268 END IF;
2269
2270 END IF;
2271
2272 x_use_flag := 'Y';
2273 l_where_clause := l_where_clause || 'AND JTRV.START_DATE_ACTIVE ' ||
2274 l_operator || ' :p_TERR_RSC_12 ';
2275
2276 END IF;
2277
2278 IF ( (p_TERR_RSC_rec.END_DATE_ACTIVE IS NOT NULL) AND
2279 (p_TERR_RSC_rec.END_DATE_ACTIVE <> FND_API.G_MISS_DATE) ) THEN
2280
2281 -- check if item value contains '%' wildcard
2282 OPEN c_chk_str1 ( p_TERR_RSC_rec.END_DATE_ACTIVE);
2283 FETCH c_chk_str1 INTO str_csr1;
2284 CLOSE c_chk_str1;
2285
2286 IF ( str_csr1 <> 0 ) THEN
2287 l_operator := 'LIKE';
2288 ELSE
2289
2290 -- check if item value contains '_' wildcard
2291 OPEN c_chk_str2 ( p_TERR_RSC_rec.END_DATE_ACTIVE);
2292 FETCH c_chk_str2 INTO str_csr2;
2293 CLOSE c_chk_str2;
2294
2295 IF ( str_csr2 <> 0 ) THEN
2296 l_operator := 'LIKE';
2297 ELSE
2298 l_operator := '=';
2299 END IF;
2300
2301 END IF;
2302
2303 x_use_flag := 'Y';
2304 l_where_clause := l_where_clause || 'AND JTRV.END_DATE_ACTIVE ' ||
2305 l_operator || ' :p_TERR_RSC_13 ';
2306
2307 END IF;
2308
2309 IF ( (p_TERR_RSC_rec.FULL_ACCESS_FLAG IS NOT NULL) AND
2310 (p_TERR_RSC_rec.FULL_ACCESS_FLAG <> FND_API.G_MISS_CHAR) ) THEN
2311
2312 x_use_flag := 'Y';
2313 l_where_clause := l_where_clause || 'AND JTRV.FULL_ACCESS_FLAG = :p_TERR_RSC_14';
2314
2315 END IF;
2316
2317 x_where_clause := x_where_clause || l_where_clause;
2318
2319 /* Debug Message */
2320 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2321 THEN
2322 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2323 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
2324 FND_MSG_PUB.Add;
2325 END IF;
2326
2327
2328 END gen_where_clause;
2329
2330
2331 PROCEDURE gen_bind (
2332 p_dsql_csr IN NUMBER,
2333 p_Terr_Rsc_Rec IN Terr_Rsc_Rec_Type
2334 )
2335 IS
2336 l_proc_name VARCHAR2(30) := 'Gen_Bind: Terr_Rsc';
2337
2338 BEGIN
2339
2340 /* Debug Message */
2341 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2342 THEN
2343 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
2344 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
2345 FND_MSG_PUB.Add;
2346 END IF;
2347
2348 IF ( (p_TERR_RSC_rec.TERR_RSC_ID IS NOT NULL) AND
2349 (p_TERR_RSC_rec.TERR_RSC_ID <> FND_API.G_MISS_NUM) ) THEN
2350
2351 -- bind the input variables
2352 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_RSC_1', p_TERR_RSC_rec.TERR_RSC_ID);
2353
2354 END IF;
2355
2356 IF ( (p_TERR_RSC_rec.TERR_ID IS NOT NULL) AND
2357 (p_TERR_RSC_rec.TERR_ID <> FND_API.G_MISS_NUM) ) THEN
2358
2359 -- bind the input variables
2360 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_RSC_7', p_TERR_RSC_rec.TERR_ID);
2361
2362 END IF;
2363
2364 IF ( (p_TERR_RSC_rec.RESOURCE_ID IS NOT NULL) AND
2365 (p_TERR_RSC_rec.RESOURCE_ID <> FND_API.G_MISS_NUM) ) THEN
2366
2367 -- bind the input variables
2368 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_RSC_8', p_TERR_RSC_rec.RESOURCE_ID);
2369
2370 END IF;
2371
2372 IF ( (p_TERR_RSC_rec.RESOURCE_TYPE IS NOT NULL) AND
2373 (p_TERR_RSC_rec.RESOURCE_TYPE <> FND_API.G_MISS_CHAR) ) THEN
2374
2375 -- bind the input variables
2376 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_RSC_9', p_TERR_RSC_rec.RESOURCE_TYPE);
2377
2378 END IF;
2379
2380 IF ( (p_TERR_RSC_rec.ROLE IS NOT NULL) AND
2381 (p_TERR_RSC_rec.ROLE <> FND_API.G_MISS_CHAR) ) THEN
2382
2383 -- bind the input variables
2384 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_RSC_10', p_TERR_RSC_rec.ROLE);
2385
2386 END IF;
2387
2388 IF ( (p_TERR_RSC_rec.PRIMARY_CONTACT_FLAG IS NOT NULL) AND
2389 (p_TERR_RSC_rec.PRIMARY_CONTACT_FLAG <> FND_API.G_MISS_CHAR) ) THEN
2390
2391 -- bind the input variables
2392 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_RSC_11', p_TERR_RSC_rec.PRIMARY_CONTACT_FLAG);
2393
2394 END IF;
2395
2396 IF ( (p_TERR_RSC_rec.START_DATE_ACTIVE IS NOT NULL) AND
2397 (p_TERR_RSC_rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE) ) THEN
2398
2399 -- bind the input variables
2400 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_RSC_12', p_TERR_RSC_rec.START_DATE_ACTIVE);
2401
2402 END IF;
2403
2404 IF ( (p_TERR_RSC_rec.END_DATE_ACTIVE IS NOT NULL) AND
2405 (p_TERR_RSC_rec.END_DATE_ACTIVE <> FND_API.G_MISS_DATE) ) THEN
2406
2407 -- bind the input variables
2408 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_RSC_13', p_TERR_RSC_rec.END_DATE_ACTIVE);
2409
2410 END IF;
2411
2412 IF ( (p_TERR_RSC_rec.FULL_ACCESS_FLAG IS NOT NULL) AND
2413 (p_TERR_RSC_rec.FULL_ACCESS_FLAG <> FND_API.G_MISS_CHAR) ) THEN
2414
2415 -- bind the input variables
2416 DBMS_SQL.BIND_VARIABLE(p_dsql_csr, ':p_TERR_RSC_14', p_TERR_RSC_rec.FULL_ACCESS_FLAG);
2417
2418 END IF;
2419
2420 /* Debug Message */
2421 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2422 THEN
2423 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2424 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
2425 FND_MSG_PUB.Add;
2426 END IF;
2427
2428 END gen_bind;
2429
2430
2431
2432 -----------------------------------------------------------------------
2433 -- ORDER BY CLAUSE
2434 -----------------------------------------------------------------------
2435 PROCEDURE gen_order_by_clause(
2436 x_return_status OUT NOCOPY VARCHAR2,
2437 x_msg_count OUT NOCOPY NUMBER,
2438 x_msg_data OUT NOCOPY VARCHAR2,
2439 p_order_by_rec IN order_by_rec_type,
2440 x_order_by_clause OUT NOCOPY VARCHAR2
2441 )
2442 IS
2443
2444 l_proc_name VARCHAR2(30) := 'Gen_Order_By_Clause';
2445
2446 l_order_by_clause VARCHAR2(1000) := FND_API.G_MISS_CHAR;
2447
2448 l_util_order_by_tbl JTF_CTM_UTILITY_PVT.Util_order_by_tbl_type;
2449
2450 BEGIN
2451
2452 /* Debug Message */
2453 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2454 THEN
2455 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
2456 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
2457 FND_MSG_PUB.Add;
2458 END IF;
2459
2460 -- Hint: Developer should add more statements according to sort_rec_type
2461 -- Ex:
2462 -- l_util_order_by_tbl(1).col_choice := p_order_by_rec.customer_name;
2463 -- l_util_order_by_tbl(1).col_name := 'Customer_Name';
2464
2465 /* Debug Message */
2466 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2467 THEN
2468 FND_MESSAGE.Set_Name('JTF', 'Invoke Translate_OrderBy');
2469 FND_MSG_PUB.Add;
2470 END IF;
2471
2472
2473 JTF_CTM_UTILITY_PVT.Translate_OrderBy (
2474 p_api_version => 1.0,
2475 p_init_msg_list => FND_API.G_FALSE,
2476 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2477 x_return_status => x_return_status,
2478 x_msg_count => x_msg_count,
2479 x_msg_data => x_msg_data,
2480 p_order_by_tbl => l_util_order_by_tbl,
2481 x_order_by_clause => l_order_by_clause );
2482
2483 IF (l_order_by_clause IS NOT NULL) THEN
2484 x_order_by_clause := 'ORDER BY ' || l_order_by_clause;
2485 ELSE
2486 x_order_by_clause := NULL;
2487 END IF;
2488
2489 /* Debug Message */
2490 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2491 THEN
2492 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2493 FND_MESSAGE.Set_Token('PROC_NAME', l_proc_name);
2494 FND_MSG_PUB.Add;
2495 END IF;
2496
2497 END gen_order_by_clause;
2498
2499
2500
2501
2502 -- ***************************************************
2503 -- start of comments
2504 -- ***************************************************
2505 --
2506 -- API name : Get_Territory_Header
2507 -- Type : PUBLIC
2508 -- Function : To get a list of territory headers
2509 --
2510 --
2511 -- Pre-reqs :
2512 -- Parameters:
2513 -- IN :
2514 -- Required
2515 -- Parameter Name Data Type Default
2516 -- p_Api_Version_Number NUMBER
2517 --
2518 -- Optional
2519 -- Parameter Name Data Type Default
2520 -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
2521 -- p_terr_rec Terr_Rec_Type G_MISS_TERR_REC
2522 -- p_terr_type_rec Terr_Type_Rec_Type G_MISS_TERR_TYPE_REC
2523 -- p_terr_usg_rec Terr_Usgs_Rec_Type G_MISS_TERR_USGS_REC
2524 -- p_terr_rsc_rec Terr_Rsc_Rec_Type G_MISS_TERR_RSC_REC
2525 -- p_terr_qual_tbl Terr_Qual_Tbl_Type G_MISS_TERR_QUAL_TBL
2526 -- p_terr_values_tbl Terr_Values_Tbl_Type G_MISS_TERR_VALUES_TBL
2527 -- p_order_by_rec order_by_rec_type G_MISS_ORDER_BY_REC
2528 -- p_return_all_rec VARCHAR2 FND_API.G_FALSE
2529 -- p_num_rec_requested NUMBER 30
2530 -- p_start_rec_num NUMBER 1
2531 --
2532 -- OUT :
2533 -- Parameter Name Data Type
2534 -- x_Return_Status VARCHAR2(1)
2535 -- x_Msg_Count NUMBER
2536 -- x_Msg_Data VARCHAR2(2000)
2537 -- x_terr_header_tbl Terr_Header_Tbl_Type
2538 -- x_num_rec_returned NUMBER
2539 -- x_next_rec_num NUMBER
2540 -- x_total_num_rec NUMBER
2541 --
2542 --
2543 -- Notes:
2544 --
2545 --
2546 -- End of Comments
2547 --
2548 PROCEDURE Get_Territory_Header (
2549 p_Api_Version IN NUMBER,
2550 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2551 x_return_status OUT NOCOPY VARCHAR2,
2552 x_msg_count OUT NOCOPY NUMBER,
2553 x_msg_data OUT NOCOPY VARCHAR2,
2554 p_terr_rec IN Terr_Rec_Type := G_MISS_TERR_REC,
2555 p_terr_type_rec IN Terr_Type_Rec_Type := G_MISS_TERR_TYPE_REC,
2556 p_terr_usg_rec IN Terr_Usgs_Rec_Type := G_MISS_TERR_USGS_REC,
2557 -- p_terr_qtype_usgs_tbl IN Terr_QType_Usgs_Tbl_Type := G_MISS_TERR_QTYPE_USGS_TBL,
2558 p_terr_rsc_rec IN Terr_Rsc_Rec_Type := G_MISS_TERR_RSC_REC,
2559 p_terr_qual_tbl IN Terr_Qual_Tbl_Type := G_MISS_TERR_QUAL_TBL,
2560 p_terr_values_tbl IN Terr_Values_Tbl_Type := G_MISS_TERR_VALUES_TBL,
2561 p_order_by_rec IN order_by_rec_type := G_MISS_ORDER_BY_REC,
2562 p_return_all_rec IN VARCHAR2 := FND_API.G_FALSE,
2563 p_num_rec_requested IN NUMBER := 30,
2564 p_start_rec_num IN NUMBER := 1,
2565 x_terr_header_tbl OUT NOCOPY Terr_Header_Tbl_Type,
2566 x_num_rec_returned OUT NOCOPY NUMBER,
2567 x_next_rec_num OUT NOCOPY NUMBER,
2568 x_total_num_rec OUT NOCOPY NUMBER
2569 )
2570 IS
2571
2572 l_api_name CONSTANT VARCHAR2(30) := 'Get_Territory_Header';
2573 l_api_version CONSTANT NUMBER := 1.0;
2574
2575 /* Local record counters */
2576 l_qtype_usgs_counter NUMBER := 0;
2577 l_qual_counter NUMBER := 0;
2578 l_val_counter NUMBER := 0;
2579 l_num_rec_returned NUMBER := 0; /* number of records returned by this call to API */
2580 l_next_rec_num NUMBER := 1;
2581
2582 /* TOTAL number of records returned by API */
2583 l_total_num_rec_returned NUMBER := 0;
2584
2585 /* Status local variable */
2586 l_return_status VARCHAR2(1);
2587
2588 /* Dynamic SQL statement elements */
2589 l_dsql_csr NUMBER;
2590 l_dummy NUMBER;
2591 l_dsql_str VARCHAR2(32767);
2592 l_select_clause VARCHAR2(32767);
2593 l_from_clause VARCHAR2(32767);
2594 l_where_clause VARCHAR2(32767);
2595 l_order_by_clause VARCHAR2(32767);
2596
2597 /* Local scratch records */
2598 l_terr_header_rec Terr_Header_Rec_Type;
2599
2600 /* flag variables */
2601 l_use_terr_flag VARCHAR2(1) := 'N';
2602 l_use_terr_type_flag VARCHAR2(1) := 'N';
2603 l_use_terr_usg_flag VARCHAR2(1) := 'N';
2604 l_use_terr_qtype_usg_flag VARCHAR2(1) := 'N';
2605 l_use_terr_qual_flag VARCHAR2(1) := 'N';
2606 l_use_terr_values_flag VARCHAR2(1) := 'N';
2607 l_use_terr_rsc_flag VARCHAR2(1) := 'N';
2608
2609
2610 BEGIN
2611
2612 --dbms_output.put_line('at API BEGIN');
2613
2614
2615 /* Standard call to check for call compatibility */
2616 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2617 p_api_version,
2618 l_api_name,
2619 G_PKG_NAME)
2620 THEN
2621 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2622 END IF;
2623
2624 /* Initialize message list if p_init_msg_list is set to TRUE */
2625 IF FND_API.to_Boolean( p_init_msg_list )
2626 THEN
2627 FND_MSG_PUB.initialize;
2628 END IF;
2629
2630 /* Debug Message */
2631 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2632 THEN
2633 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
2634 FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2635 FND_MSG_PUB.Add;
2636 END IF;
2637
2638 /**********************************************************************************************
2639 ** API BODY START
2640 ***********************************************************************************************/
2641
2642 /* Initialize API return status to SUCCESS */
2643 l_return_status := FND_API.G_RET_STS_SUCCESS;
2644
2645 --dbms_output.put_line('at API body start, l_return_status = '|| l_return_status);
2646
2647
2648 /* *************************************************
2649 ** Generate Dynamic SQL based on criteria passed in.
2650 ** Doing this for performance. Indexes are disabled
2651 ** when using NVL within static SQL statement.
2652 ** Ignore condition when criteria is NULL
2653 */
2654
2655 /* Debug Message */
2656 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2657 THEN
2658 FND_MESSAGE.Set_Name('JTF', 'Open Cursor for Dynamic SQL');
2659 FND_MSG_PUB.Add;
2660 END IF;
2661
2662 /******************************/
2663 /* OPEN CURSOR for processing */
2664 /******************************/
2665 l_dsql_csr := dbms_sql.open_cursor;
2666
2667 --dbms_output.put_line('after open dsql cursor');
2668
2669
2670 /* Generate initial SQL statement: SELECT, FROM, and WHERE clauses
2671 ** Hint: Developer should modify gen_init_sql procedure.
2672 */
2673 gen_init_sql( l_select_clause, l_from_clause, l_where_clause );
2674
2675 --dbms_output.put_line('[1] l_select_clause');
2676 --dbms_output.put_line('[1] l_from_clause');
2677 --dbms_output.put_line('[1] l_where_clause');
2678
2679 --dbms_output.put_line('Dynamic creation of WHERE clause Start');
2680
2681 /******************************************************/
2682 /* generate where clause using the following entities */
2683 /******************************************************/
2684
2685 /* Hint: Developer should modify and implement
2686 ** the gen_where_clause procedure.
2687 ** This is an overloaded procedure.
2688 */
2689
2690 /***********************/
2691 /* territory */
2692 /***********************/
2693 gen_where_clause(l_dsql_csr, p_terr_rec, l_where_clause, l_use_terr_flag);
2694 --dbms_output.put_line('[2] l_where_clause = '|| l_where_clause);
2695
2696 /* Hint: if master/detail relationship, generate Where clause for lines level criteria */
2697
2698 /***********************/
2699 /* territory usages */
2700 /***********************/
2701 gen_where_clause(l_dsql_csr, p_terr_usg_rec, l_where_clause, l_use_terr_usg_flag);
2702 --dbms_output.put_line('[3] l_where_clause = '|| l_where_clause);
2703 --dbms_output.put_line('[3] l_from_clause = '|| l_from_clause);
2704
2705 /***********************/
2706 /* territory type */
2707 /***********************/
2708 gen_where_clause(l_dsql_csr, p_terr_type_rec, l_where_clause, l_use_terr_type_flag);
2709 --dbms_output.put_line('[4] l_where_clause = '|| l_where_clause);
2710 --dbms_output.put_line('[4] l_from_clause = '|| l_from_clause);
2711
2712 IF ( l_use_terr_type_flag = 'Y' ) THEN
2713 l_from_clause := 'JTF_TERR_TYPES JTTA, ' || l_from_clause;
2714 l_where_clause := l_where_clause ||
2715 ' AND JTOV.TERRITORY_TYPE_ID = JTTA.TERR_TYPE_ID ';
2716 END IF;
2717
2718
2719 /*****************************************************************/
2720 /* territory qualifier type usages (transactions + resources) */
2721 /*****************************************************************/
2722
2723 --IF ( p_terr_qtype_usgs_tbl.COUNT <> 0 ) THEN
2724 -- FOR l_qtype_usgs_counter IN p_terr_qtype_usgs_tbl.FIRST .. p_terr_qual_tbl.LAST LOOP
2725 --
2726 -- /* for each record in table */
2727 -- gen_where_clause( l_dsql_csr, p_terr_qtype_usgs_tbl(l_qtype_usgs_counter), l_where_clause
2728 -- , l_use_terr_qtype_usg_flag);
2729 --
2730 -- END LOOP;
2731 --END IF;
2732 --
2733 --IF ( l_use_terr_qtype_usg_flag = 'Y' ) THEN
2734 -- l_from_clause := 'JTF_TERR_TRANSACTIONS_V JTTV, ' || l_from_clause;
2735 -- l_where_clause := l_where_clause || ' AND JTTV.TERR_ID = JTOV.TERR_ID ';
2736 --END IF;
2737
2738 --dbms_output.put_line('[5] l_where_clause = '|| l_where_clause);
2739 --dbms_output.put_line('[5] l_from_clause = '|| l_from_clause);
2740
2741 /***************************/
2742 /* territory qualifiers */
2743 /***************************/
2744
2745 IF ( p_terr_qual_tbl.COUNT > 0 ) THEN
2746 FOR l_qual_counter IN p_terr_qual_tbl.FIRST .. p_terr_qual_tbl.LAST LOOP
2747
2748 --dbms_output.put_line('[6] l_where_clause = '|| l_where_clause);
2749
2750 /* for each record in table */
2751 gen_where_clause(l_dsql_csr, p_terr_qual_tbl(l_qual_counter), l_where_clause, l_use_terr_qual_flag);
2752
2753 END LOOP;
2754 END IF;
2755
2756 IF ( l_use_terr_qual_flag = 'Y' ) THEN
2757 l_from_clause := 'JTF_TERR_QUALIFIERS_V JTQV, ' || l_from_clause;
2758 l_where_clause := l_where_clause || ' AND JTQV.TERR_ID = JTOV.TERR_ID ';
2759 END IF;
2760
2761
2762 --dbms_output.put_line('[6] l_from_clause = '|| l_from_clause);
2763
2764 /***************************/
2765 /* territory values */
2766 /***************************/
2767
2768 IF ( p_terr_values_tbl.COUNT > 0 ) THEN
2769 FOR l_val_counter IN p_terr_values_tbl.FIRST .. p_terr_values_tbl.LAST LOOP
2770
2771 /* for each record in table */
2772 gen_where_clause( l_dsql_csr, p_terr_values_tbl(l_val_counter),
2773 l_where_clause, l_use_terr_values_flag);
2774
2775 END LOOP;
2776 END IF;
2777
2778 IF ( l_use_terr_values_flag = 'Y' ) THEN
2779
2780 /* join jtf_terr_qualifiers with jtf_terr
2781 ** and then jtf_terr_values with jtf_terr_qualifiers
2782 */
2783 IF ( l_use_terr_qual_flag <> 'Y' ) THEN
2784 l_from_clause := 'JTF_TERR_QUALIFIERS_V JTQV, ' || l_from_clause;
2785 l_where_clause := l_where_clause || ' AND JTQV.TERR_ID = JTOV.TERR_ID ';
2786 END IF;
2787
2788 l_from_clause := 'JTF_TERR_VALUES JTV, ' || l_from_clause;
2789 l_where_clause := l_where_clause || ' AND JTV.TERR_QUAL_ID = JTQV.TERR_QUAL_ID ';
2790 END IF;
2791
2792 --dbms_output.put_line('[7] l_where_clause = '|| l_where_clause);
2793 --dbms_output.put_line('[7] l_from_clause = '|| l_from_clause);
2794
2795 /***************************/
2796 /* territory resources */
2797 /***************************/
2798
2799 gen_where_clause(l_dsql_csr, p_terr_rsc_rec, l_where_clause, l_use_terr_rsc_flag);
2800
2801 IF ( l_use_terr_rsc_flag = 'Y' ) THEN
2802 l_from_clause := 'JTF_TERR_RESOURCES_V JTRV, ' || l_from_clause;
2803 l_where_clause := l_where_clause || ' AND JTRV.TERR_ID = JTOV.TERR_ID ';
2804 END IF;
2805
2806
2807 /* the FROM clause has been built right to left
2808 ** to make the SQL statement more efficient
2809 */
2810 l_from_clause := ' FROM ' || l_from_clause;
2811
2812 --dbms_output.put_line('[8] l_select_clause = '|| l_select_clause);
2813 --dbms_output.put_line('[8] l_from_clause = '|| l_from_clause);
2814 --dbms_output.put_line('[8] l_where_clause = '|| l_where_clause);
2815
2816 /* Generate order by clause */
2817 gen_order_by_clause ( l_return_status,
2818 x_msg_count,
2819 x_msg_data,
2820 p_order_by_rec,
2821 l_order_by_clause );
2822
2823 /* build string that will be used by dynamic SQL */
2824 l_dsql_str := l_select_clause || l_from_clause || l_where_clause || l_order_by_clause;
2825
2826
2827 --dbms_output.put_line('[9] about to parse query');
2828 /************************/
2829 /* parse the query */
2830 /************************/
2831 DBMS_SQL.PARSE(l_dsql_csr, l_dsql_str, DBMS_SQL.V7);
2832
2833 /************************/
2834 /* bind input variables */
2835 /************************/
2836
2837 IF (l_use_terr_flag = 'Y') THEN
2838 --dbms_output.put_line('[10] binding terr_rec variables');
2839 /* this is an overloaded procedure */
2840 gen_bind(l_dsql_csr, p_terr_rec);
2841 END IF;
2842
2843 /* Hint: if master/detail relationship, generate binds for lines level criteria */
2844
2845 IF (l_use_terr_usg_flag = 'Y') THEN
2846 --dbms_output.put_line('[11] binding terr_usg_rec variables');
2847 gen_bind(l_dsql_csr, p_terr_usg_rec);
2848 END IF;
2849
2850 IF (l_use_terr_type_flag = 'Y') THEN
2851 --dbms_output.put_line('[12] binding terr_type_rec variables');
2852 gen_bind(l_dsql_csr, p_terr_type_rec);
2853 END IF;
2854
2855 --IF (l_use_terr_qtype_usg_flag = 'Y') THEN
2856 -- FOR l_qtype_usgs_counter IN p_terr_qtype_usgs_tbl.FIRST .. p_terr_qtype_usgs_tbl.LAST LOOP
2857 -- /* for each record in table */
2858 -- gen_bind(l_dsql_csr, p_terr_qtype_usgs_tbl(l_qtype_usgs_counter));
2859 -- END LOOP;
2860 --END IF;
2861
2862 IF (l_use_terr_qual_flag = 'Y') THEN
2863 --dbms_output.put_line('[14] binding terr_qual_rec variables');
2864 FOR l_qual_counter IN 1 .. p_terr_qual_tbl.COUNT LOOP
2865 /* for each record in table */
2866 gen_bind(l_dsql_csr, p_terr_qual_tbl(l_qual_counter));
2867 END LOOP;
2868 END IF;
2869
2870 IF (l_use_terr_values_flag = 'Y') THEN
2871 --dbms_output.put_line('[15] binding terr_value_rec variables');
2872 FOR l_val_counter IN p_terr_values_tbl.FIRST .. p_terr_values_tbl.LAST LOOP
2873 /* for each record in table */
2874 gen_bind(l_dsql_csr, p_terr_values_tbl(l_val_counter));
2875 END LOOP;
2876 END IF;
2877
2878 IF (l_use_terr_rsc_flag = 'Y') THEN
2879 --dbms_output.put_line('[16] binding terr_rsc_rec variables');
2880 gen_bind(l_dsql_csr, p_terr_rsc_rec);
2881 END IF;
2882
2883 --dbms_output.put_line('[17] defining SQL columns');
2884 /* define columns */
2885 define_dsql_columns ( l_dsql_csr, l_terr_header_rec );
2886
2887 --dbms_output.put_line('[18] executing query');
2888 /* execute query */
2889 l_dummy := DBMS_SQL.EXECUTE ( l_dsql_csr );
2890
2891 /* Fetch rows into buffer, and then put in PL/SQL table */
2892 LOOP
2893
2894 /* exit at last row in cursor */
2895 IF (DBMS_SQL.FETCH_ROWS (l_dsql_csr) = 0 ) THEN
2896 EXIT;
2897 END IF;
2898
2899 /* all records are to be returned OR number of records requested is null
2900 ** OR number of records returned less than number of records requested
2901 */
2902 IF ( p_return_all_rec = FND_API.G_TRUE OR
2903 p_num_rec_requested = FND_API.G_MISS_NUM OR
2904 l_num_rec_returned < p_num_rec_requested ) THEN
2905
2906 /* retrieve values into columns */
2907 get_dsql_column_values ( l_dsql_csr, l_terr_header_rec);
2908
2909 /* increment total number of records returned counter */
2910 l_total_num_rec_returned := l_total_num_rec_returned + 1;
2911
2912 --dbms_output.put_line('fetching row ' || TO_CHAR(l_total_num_rec_returned));
2913
2914 /* TOTAL number of records returned is >= record to start returning from
2915 ** AND number of records returned is < number of records requested
2916 */
2917 IF ( l_total_num_rec_returned >= p_start_rec_num ) THEN
2918
2919 l_num_rec_returned := l_num_rec_returned + 1;
2920
2921 --dbms_output.put_line('returning row ' || TO_CHAR(l_num_rec_returned));
2922
2923 x_terr_header_tbl ( l_num_rec_returned ) := l_terr_header_rec;
2924
2925 --dbms_output.put_line( 'fetched header ' ||
2926 -- x_terr_header_tbl(l_num_rec_returned).terr_name);
2927
2928 END IF;
2929
2930 l_next_rec_num := l_total_num_rec_returned + 1;
2931
2932 END IF;
2933
2934 END LOOP;
2935
2936 /* close cursor */
2937 DBMS_SQL.CLOSE_CURSOR(l_dsql_csr);
2938
2939 /* save return variables */
2940 x_num_rec_returned := l_num_rec_returned;
2941 x_next_rec_num := l_next_rec_num;
2942 x_total_num_rec := l_total_num_rec_returned;
2943
2944 /* save return status */
2945 x_return_status := l_return_status;
2946
2947 /**********************************************************************************************
2948 ** API BODY END
2949 ***********************************************************************************************/
2950
2951 /* Debug Message */
2952 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2953 THEN
2954 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
2955 FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
2956 FND_MSG_PUB.Add;
2957 END IF;
2958
2959 /* Standard call to get message count and if count is 1, get message info. */
2960 FND_MSG_PUB.Count_And_Get
2961 ( p_count => x_msg_count,
2962 p_data => x_msg_data
2963 );
2964
2965 EXCEPTION
2966 WHEN FND_API.G_EXC_ERROR THEN
2967 x_return_status := FND_API.G_RET_STS_ERROR ;
2968 --dbms_output.put_line( 'FND_API.G_EXC_ERROR: return_status = '|| l_return_status );
2969
2970 FND_MSG_PUB.Count_And_Get
2971 ( p_count => x_msg_count,
2972 p_data => x_msg_data
2973 );
2974
2975
2976 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2977 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2978 --dbms_output.put_line( 'FND_API.G_EXC_UNEXPECTED_ERROR: return_status = '|| l_return_status );
2979
2980 FND_MSG_PUB.Count_And_Get
2981 ( p_count => x_msg_count,
2982 p_data => x_msg_data
2983 );
2984
2985 WHEN OTHERS THEN
2986 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2987
2988 --dbms_output.put_line( 'OTHERS: return_status = '|| l_return_status );
2989
2990 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2991 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2992 END IF;
2993
2994 FND_MSG_PUB.Count_And_Get
2995 ( p_count => x_msg_count,
2996 p_data => x_msg_data
2997 );
2998
2999 End Get_Territory_Header;
3000
3001
3002 -- ***************************************************
3003 -- start of comments
3004 -- ***************************************************
3005 --
3006 -- API name : Get_Territory_Details
3007 -- Type : PUBLIC
3008 -- Function : To get a territory's details
3009 --
3010 --
3011 -- Pre-reqs :
3012 -- Parameters:
3013 -- IN :
3014 -- Required
3015 -- Parameter Name Data Type Default
3016 -- p_Api_Version_Number NUMBER
3017 -- p_terr_id NUMBER FND_API.G_MISS_NUM
3018 --
3019 -- Optional
3020 -- Parameter Name Data Type Default
3021 -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
3022 --
3023 -- OUT :
3024 -- Parameter Name Data Type
3025 -- x_Return_Status VARCHAR2(1)
3026 -- x_Msg_Count NUMBER
3027 -- x_Msg_Data VARCHAR2(2000)
3028 -- x_terr_rec Terr_Rec_Type
3029 -- x_terr_type_rec Terr_Type_Rec_Type
3030 -- x_terr_sub_terr_tbl Terr_Tbl_Type
3031 -- x_terr_usgs_tbl Terr_Usgs_Tbl_Type
3032 -- x_terr_qtype_usgs_tbl Terr_QType_Usgs_Tbl_Type
3033 -- x_terr_qual_tbl Terr_Qual_Tbl_Type
3034 -- x_terr_values_tbl Terr_Values_Tbl_Type
3035 -- x_terr_rsc_tbl Terr_Rsc_Tbl_Type
3036 --
3037 --
3038 -- Notes:
3039 --
3040 --
3041 -- End of Comments
3042 --
3043 PROCEDURE Get_Territory_Details (
3044 p_Api_Version IN NUMBER,
3045 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3046 x_return_status OUT NOCOPY VARCHAR2,
3047 x_msg_count OUT NOCOPY NUMBER,
3048 x_msg_data OUT NOCOPY VARCHAR2,
3049 p_terr_id IN NUMBER := FND_API.G_MISS_NUM,
3050 x_terr_rec OUT NOCOPY Terr_Rec_Type,
3051 x_terr_type_rec OUT NOCOPY Terr_Type_Rec_Type,
3052 x_terr_sub_terr_tbl OUT NOCOPY Terr_Tbl_Type,
3053 x_terr_usgs_tbl OUT NOCOPY Terr_Usgs_Tbl_Type,
3054 x_terr_qtype_usgs_tbl OUT NOCOPY Terr_QType_Usgs_Tbl_Type,
3055 x_terr_qual_tbl OUT NOCOPY Terr_Qual_Tbl_Type,
3056 x_terr_values_tbl OUT NOCOPY Terr_Values_Tbl_Type,
3057 x_terr_rsc_tbl OUT NOCOPY Terr_Rsc_Tbl_Type
3058 )
3059 IS
3060
3061 l_api_name CONSTANT VARCHAR2(30) := 'Get_Territory_Details';
3062 l_api_version CONSTANT NUMBER := 1.0;
3063
3064 /**********************/
3065 /* cursor definitions */
3066 /**********************/
3067
3068 /* return territory record */
3069 CURSOR get_terr ( p_terr_id NUMBER) IS
3070 SELECT
3071 jtov.TERR_ID
3072 , jtov.LAST_UPDATE_DATE
3073 , jtov.LAST_UPDATED_BY
3074 , jtov.CREATION_DATE
3075 , jtov.CREATED_BY
3076 , jtov.LAST_UPDATE_LOGIN
3077 , jtov.REQUEST_ID
3078 , jtov.PROGRAM_APPLICATION_ID
3079 , jtov.PROGRAM_ID
3080 , jtov.PROGRAM_UPDATE_DATE
3081 , jtov.APPLICATION_SHORT_NAME
3082 , jtov.NAME
3083 , jtov.ENABLED_FLAG
3084 , jtov.START_DATE_ACTIVE
3085 , jtov.END_DATE_ACTIVE
3086 , jtov.PLANNED_FLAG
3087 , jtov.PARENT_TERRITORY_ID
3088 , jtov.TERRITORY_TYPE_ID
3089 , jtov.TEMPLATE_TERRITORY_ID
3090 , jtov.TEMPLATE_FLAG
3091 , jtov.ESCALATION_TERRITORY_ID
3092 , jtov.ESCALATION_TERRITORY_FLAG
3093 , jtov.OVERLAP_ALLOWED_FLAG
3094 , jtov.RANK
3095 , jtov.DESCRIPTION
3096 , jtov.UPDATE_FLAG
3097 , jtov.AUTO_ASSIGN_RESOURCES_FLAG
3098 , jtov.ATTRIBUTE_CATEGORY
3099 , jtov.ATTRIBUTE1
3100 , jtov.ATTRIBUTE2
3101 , jtov.ATTRIBUTE3
3102 , jtov.ATTRIBUTE4
3103 , jtov.ATTRIBUTE5
3104 , jtov.ATTRIBUTE6
3105 , jtov.ATTRIBUTE7
3106 , jtov.ATTRIBUTE8
3107 , jtov.ATTRIBUTE9
3108 , jtov.ATTRIBUTE10
3109 , jtov.ATTRIBUTE11
3110 , jtov.ATTRIBUTE12
3111 , jtov.ATTRIBUTE13
3112 , jtov.ATTRIBUTE14
3113 , jtov.ATTRIBUTE15
3114 , jtov.ORG_ID
3115 , jtov.TERR_TYPE_NAME
3116 , jtov.PARENT_TERR_NAME
3117 , jtov.ESCALATION_TERR_NAME
3118 , jtov.TEMPLATE_TERR_NAME
3119 , jtov.TERR_USG_ID
3120 , jtov.SOURCE_ID
3121 , jtov.TERR_USAGE
3122 FROM JTF_TERR_OVERVIEW_V jtov
3123 WHERE jtov.TERR_ID = p_terr_id;
3124
3125 /* return territory type */
3126 CURSOR get_terr_type ( p_terr_type_id NUMBER ) IS
3127 SELECT
3128 jtta.TERR_TYPE_ID
3129 , jtta.LAST_UPDATED_BY
3130 , jtta.LAST_UPDATE_DATE
3131 , jtta.CREATED_BY
3132 , jtta.CREATION_DATE
3133 , jtta.LAST_UPDATE_LOGIN
3134 , jtta.APPLICATION_SHORT_NAME
3135 , jtta.NAME
3136 , jtta.ENABLED_FLAG
3137 , jtta.DESCRIPTION
3138 , jtta.START_DATE_ACTIVE
3139 , jtta.END_DATE_ACTIVE
3140 , jtta.ATTRIBUTE_CATEGORY
3141 , jtta.ATTRIBUTE1
3142 , jtta.ATTRIBUTE2
3143 , jtta.ATTRIBUTE3
3144 , jtta.ATTRIBUTE4
3145 , jtta.ATTRIBUTE5
3146 , jtta.ATTRIBUTE6
3147 , jtta.ATTRIBUTE7
3148 , jtta.ATTRIBUTE8
3149 , jtta.ATTRIBUTE9
3150 , jtta.ATTRIBUTE10
3151 , jtta.ATTRIBUTE11
3152 , jtta.ATTRIBUTE12
3153 , jtta.ATTRIBUTE13
3154 , jtta.ATTRIBUTE14
3155 , jtta.ATTRIBUTE15
3156 , jtta.ORG_ID
3157 FROM JTF_TERR_TYPES jtta
3158 WHERE terr_type_id = p_terr_type_id;
3159
3160 /* return territory's (child) sub-territories */
3161 CURSOR get_sub_terr ( p_terr_id NUMBER) IS
3162 SELECT
3163 jtov.TERR_ID
3164 , jtov.LAST_UPDATE_DATE
3165 , jtov.LAST_UPDATED_BY
3166 , jtov.CREATION_DATE
3167 , jtov.CREATED_BY
3168 , jtov.LAST_UPDATE_LOGIN
3169 , jtov.REQUEST_ID
3170 , jtov.PROGRAM_APPLICATION_ID
3171 , jtov.PROGRAM_ID
3172 , jtov.PROGRAM_UPDATE_DATE
3173 , jtov.APPLICATION_SHORT_NAME
3174 , jtov.NAME
3175 , jtov.ENABLED_FLAG
3176 , jtov.START_DATE_ACTIVE
3177 , jtov.END_DATE_ACTIVE
3178 , jtov.PLANNED_FLAG
3179 , jtov.PARENT_TERRITORY_ID
3180 , jtov.TERRITORY_TYPE_ID
3181 , jtov.TEMPLATE_TERRITORY_ID
3182 , jtov.TEMPLATE_FLAG
3183 , jtov.ESCALATION_TERRITORY_ID
3184 , jtov.ESCALATION_TERRITORY_FLAG
3185 , jtov.OVERLAP_ALLOWED_FLAG
3186 , jtov.RANK
3187 , jtov.DESCRIPTION
3188 , jtov.UPDATE_FLAG
3189 , jtov.AUTO_ASSIGN_RESOURCES_FLAG
3190 , jtov.ATTRIBUTE_CATEGORY
3191 , jtov.ATTRIBUTE1
3192 , jtov.ATTRIBUTE2
3193 , jtov.ATTRIBUTE3
3194 , jtov.ATTRIBUTE4
3195 , jtov.ATTRIBUTE5
3196 , jtov.ATTRIBUTE6
3197 , jtov.ATTRIBUTE7
3198 , jtov.ATTRIBUTE8
3199 , jtov.ATTRIBUTE9
3200 , jtov.ATTRIBUTE10
3201 , jtov.ATTRIBUTE11
3202 , jtov.ATTRIBUTE12
3203 , jtov.ATTRIBUTE13
3204 , jtov.ATTRIBUTE14
3205 , jtov.ATTRIBUTE15
3206 , jtov.ORG_ID
3207 , jtov.TERR_TYPE_NAME
3208 , jtov.PARENT_TERR_NAME
3209 , jtov.ESCALATION_TERR_NAME
3210 , jtov.TEMPLATE_TERR_NAME
3211 , jtov.TERR_USG_ID
3212 , jtov.SOURCE_ID
3213 , jtov.TERR_USAGE
3214 FROM JTF_TERR_OVERVIEW_V jtov
3215 WHERE jtov.parent_territory_id = p_terr_id;
3216
3217 /* return territory usages */
3218 CURSOR get_terr_usgs ( p_terr_id NUMBER) IS
3219 SELECT
3220 jtua.TERR_USG_ID
3221 , jtua.LAST_UPDATE_DATE
3222 , jtua.LAST_UPDATED_BY
3223 , jtua.CREATION_DATE
3224 , jtua.CREATED_BY
3225 , jtua.LAST_UPDATE_LOGIN
3226 , jtua.TERR_ID
3227 , jtua.SOURCE_ID
3228 , jtua.ORG_ID
3229 , jse.MEANING USAGE
3230 FROM JTF_TERR_USGS jtua, JTF_SOURCES jse
3231 WHERE jtua.terr_id = p_terr_id
3232 AND jtua.SOURCE_ID = jse.SOURCE_ID;
3233
3234 /* return territory qualifier type usages and descriptions */
3235 CURSOR get_terr_qtype_usgs ( p_terr_id NUMBER) IS
3236 SELECT
3237 jttv.TERR_QTYPE_USG_ID
3238 , jttv.LAST_UPDATED_BY
3239 , jttv.LAST_UPDATE_DATE
3240 , jttv.CREATED_BY
3241 , jttv.CREATION_DATE
3242 , jttv.LAST_UPDATE_LOGIN
3243 , jttv.TERR_ID
3244 , jttv.QUAL_TYPE_USG_ID
3245 , jttv.ORG_ID
3246 , jttv.SOURCE_ID
3247 , jttv.QUAL_TYPE_ID
3248 , jttv.QUALIFIER_TYPE_NAME
3249 , jttv.QUALIFIER_TYPE_DESCRIPTION
3250 FROM JTF_TERR_TRANSACTIONS_V jttv
3251 WHERE jttv.terr_id = p_terr_id;
3252
3253
3254 /* return territory qualifiers and descriptions */
3255 CURSOR get_terr_qual ( p_terr_id NUMBER) IS
3256 SELECT
3257 jtqv.TERR_QUAL_ID
3258 , jtqv.LAST_UPDATE_DATE
3259 , jtqv.LAST_UPDATED_BY
3260 , jtqv.CREATION_DATE
3261 , jtqv.CREATED_BY
3262 , jtqv.LAST_UPDATE_LOGIN
3263 , jtqv.TERR_ID
3264 , jtqv.QUAL_USG_ID
3265 , jtqv.USE_TO_NAME_FLAG
3266 , jtqv.GENERATE_FLAG
3267 , jtqv.OVERLAP_ALLOWED_FLAG
3268 , jtqv.QUALIFIER_MODE
3269 , jtqv.ORG_ID
3270 , jtqv.DISPLAY_TYPE
3271 , jtqv.LOV_SQL
3272 , jtqv.CONVERT_TO_ID_FLAG
3273 , jtqv.QUAL_TYPE_ID
3274 , jtqv.QUALIFIER_TYPE_NAME
3275 , jtqv.QUALIFIER_TYPE_DESCRIPTION
3276 , jtqv.QUALIFIER_NAME
3277 FROM JTF_TERR_QUALIFIERS_V jtqv
3278 WHERE jtqv.terr_id = p_terr_id;
3279
3280 /* return territory qualifier values */
3281 CURSOR get_terr_values ( p_terr_id NUMBER) IS
3282 SELECT
3283 j1.TERR_VALUE_ID
3284 , j1.LAST_UPDATED_BY
3285 , j1.LAST_UPDATE_DATE
3286 , j1.CREATED_BY
3287 , j1.CREATION_DATE
3288 , j1.LAST_UPDATE_LOGIN
3289 , j1.TERR_QUAL_ID
3290 , j1.INCLUDE_FLAG
3291 , j1.COMPARISON_OPERATOR
3292 , j3.CONVERT_TO_ID_FLAG ID_USED_FLAG -- modified for bug # 4691184
3293 , j1.LOW_VALUE_CHAR_ID
3294 , j1.LOW_VALUE_CHAR
3295 , j1.HIGH_VALUE_CHAR
3296 , j1.LOW_VALUE_NUMBER
3297 , j1.HIGH_VALUE_NUMBER
3298 , j1.VALUE_SET
3299 , j1.INTEREST_TYPE_ID
3300 , j1.PRIMARY_INTEREST_CODE_ID
3301 , j1.SECONDARY_INTEREST_CODE_ID
3302 , j1.CURRENCY_CODE
3303 , j1.ORG_ID
3304 FROM JTF_TERR_VALUES j1, JTF_TERR_QUAL j2, JTF_QUAL_USGS j3
3305 WHERE j1.terr_qual_id = j2.terr_qual_id
3306 AND j2.terr_id = p_terr_id
3307 AND j3.qual_usg_id = j2.qual_usg_id;
3308
3309
3310 /* return territory resources */
3311 CURSOR get_terr_rsc ( p_terr_id NUMBER) IS
3312 SELECT
3313 JTRV.TERR_RSC_ID
3314 , JTRV.LAST_UPDATE_DATE
3315 , JTRV.LAST_UPDATED_BY
3316 , JTRV.CREATION_DATE
3317 , JTRV.CREATED_BY
3318 , JTRV.LAST_UPDATE_LOGIN
3319 , JTRV.TERR_ID
3320 , JTRV.RESOURCE_ID
3321 , JTRV.RESOURCE_TYPE
3322 , JTRV.ROLE
3323 , JTRV.PRIMARY_CONTACT_FLAG
3324 , JTRV.START_DATE_ACTIVE
3325 , JTRV.END_DATE_ACTIVE
3326 , JTRV.FULL_ACCESS_FLAG
3327 , JTRV.ORG_ID
3328 , JTRV.RESOURCE_NAME
3329 FROM JTF_TERR_RESOURCES_V JTRV
3330 WHERE terr_id = p_terr_id;
3331
3332
3333 /* Status local variable */
3334 l_return_status VARCHAR2(1);
3335
3336 /* Local scratch records */
3337 l_terr_rec Terr_Rec_Type;
3338 l_terr_type_rec Terr_Type_Rec_Type;
3339 l_terr_sub_terr_tbl Terr_Tbl_Type;
3340 l_terr_usgs_tbl Terr_Usgs_Tbl_Type;
3341 l_terr_qtype_usgs_tbl Terr_QType_Usgs_Tbl_Type;
3342 l_terr_qual_tbl Terr_Qual_Tbl_Type;
3343 l_terr_values_tbl Terr_Values_Tbl_Type;
3344 l_terr_rsc_tbl Terr_Rsc_Tbl_Type;
3345
3346 /* table counter */
3347 counter NUMBER := 0;
3348
3349 BEGIN
3350
3351 /* Standard call to check for call compatibility */
3352 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3353 p_api_version,
3354 l_api_name,
3355 G_PKG_NAME)
3356 THEN
3357 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3358 END IF;
3359
3360 /* Initialize message list if p_init_msg_list is set to TRUE */
3361 IF FND_API.to_Boolean( p_init_msg_list )
3362 THEN
3363 FND_MSG_PUB.initialize;
3364 END IF;
3365
3366 /* Debug Message */
3367 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3368 THEN
3369 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
3370 FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
3371 FND_MSG_PUB.Add;
3372 END IF;
3373
3374
3375 /**********************************************************************************************
3376 ** API BODY START
3377 ***********************************************************************************************/
3378
3379
3380 /* Initialize API return status to SUCCESS */
3381 l_return_status := FND_API.G_RET_STS_SUCCESS;
3382
3383 /****************************/
3384 /* get the territory record */
3385 /****************************/
3386 OPEN get_terr ( p_terr_id );
3387 FETCH get_terr INTO
3388 l_terr_rec.TERR_ID
3389 , l_terr_rec.LAST_UPDATE_DATE
3390 , l_terr_rec.LAST_UPDATED_BY
3391 , l_terr_rec.CREATION_DATE
3392 , l_terr_rec.CREATED_BY
3393 , l_terr_rec.LAST_UPDATE_LOGIN
3394 , l_terr_rec.REQUEST_ID
3395 , l_terr_rec.PROGRAM_APPLICATION_ID
3396 , l_terr_rec.PROGRAM_ID
3397 , l_terr_rec.PROGRAM_UPDATE_DATE
3398 , l_terr_rec.APPLICATION_SHORT_NAME
3399 , l_terr_rec.NAME
3400 , l_terr_rec.ENABLED_FLAG
3401 , l_terr_rec.START_DATE_ACTIVE
3402 , l_terr_rec.END_DATE_ACTIVE
3403 , l_terr_rec.PLANNED_FLAG
3404 , l_terr_rec.PARENT_TERRITORY_ID
3405 , l_terr_rec.TERRITORY_TYPE_ID
3406 , l_terr_rec.TEMPLATE_TERRITORY_ID
3407 , l_terr_rec.TEMPLATE_FLAG
3408 , l_terr_rec.ESCALATION_TERRITORY_ID
3409 , l_terr_rec.ESCALATION_TERRITORY_FLAG
3410 , l_terr_rec.OVERLAP_ALLOWED_FLAG
3411 , l_terr_rec.RANK
3412 , l_terr_rec.DESCRIPTION
3413 , l_terr_rec.UPDATE_FLAG
3414 , l_terr_rec.AUTO_ASSIGN_RESOURCES_FLAG
3415 , l_terr_rec.ATTRIBUTE_CATEGORY
3416 , l_terr_rec.ATTRIBUTE1
3417 , l_terr_rec.ATTRIBUTE2
3418 , l_terr_rec.ATTRIBUTE3
3419 , l_terr_rec.ATTRIBUTE4
3420 , l_terr_rec.ATTRIBUTE5
3421 , l_terr_rec.ATTRIBUTE6
3422 , l_terr_rec.ATTRIBUTE7
3423 , l_terr_rec.ATTRIBUTE8
3424 , l_terr_rec.ATTRIBUTE9
3425 , l_terr_rec.ATTRIBUTE10
3426 , l_terr_rec.ATTRIBUTE11
3427 , l_terr_rec.ATTRIBUTE12
3428 , l_terr_rec.ATTRIBUTE13
3429 , l_terr_rec.ATTRIBUTE14
3430 , l_terr_rec.ATTRIBUTE15
3431 , l_terr_rec.ORG_ID
3432 , l_terr_rec.TERR_TYPE_NAME
3433 , l_terr_rec.PARENT_TERR_NAME
3434 , l_terr_rec.ESCALATION_TERR_NAME
3435 , l_terr_rec.TEMPLATE_TERR_NAME
3436 , l_terr_rec.TERR_USG_ID
3437 , l_terr_rec.SOURCE_ID
3438 , l_terr_rec.TERR_USAGE;
3439
3440 IF get_terr%NOTFOUND THEN
3441
3442 l_return_status := FND_API.G_RET_STS_ERROR;
3443
3444 /* Debug message */
3445 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3446 FND_MESSAGE.Set_Name ('JTF', G_PKG_NAME || ': Territory record not found');
3447 FND_MSG_PUB.ADD;
3448 END IF;
3449 END IF;
3450
3451 CLOSE get_terr;
3452
3453 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3454 RAISE FND_API.G_EXC_ERROR;
3455 END IF;
3456
3457 --dbms_output.put_line( 'get_terr: return_status = '|| l_return_status ||
3458 -- ' terr_name = ' || l_terr_rec.name);
3459
3460 /*************************************************************************/
3461 /* if this territory has a territory type, get the territory type record */
3462 /*************************************************************************/
3463 IF (l_terr_rec.territory_type_id IS NOT NULL) THEN
3464
3465 OPEN get_terr_type ( l_terr_rec.territory_type_id );
3466 FETCH get_terr_type INTO
3467 l_terr_type_rec.TERR_TYPE_ID
3468 , l_terr_type_rec.LAST_UPDATED_BY
3469 , l_terr_type_rec.LAST_UPDATE_DATE
3470 , l_terr_type_rec.CREATED_BY
3471 , l_terr_type_rec.CREATION_DATE
3472 , l_terr_type_rec.LAST_UPDATE_LOGIN
3473 , l_terr_type_rec.APPLICATION_SHORT_NAME
3474 , l_terr_type_rec.NAME
3475 , l_terr_type_rec.ENABLED_FLAG
3476 , l_terr_type_rec.DESCRIPTION
3477 , l_terr_type_rec.START_DATE_ACTIVE
3478 , l_terr_type_rec.END_DATE_ACTIVE
3479 , l_terr_type_rec.ATTRIBUTE_CATEGORY
3480 , l_terr_type_rec.ATTRIBUTE1
3481 , l_terr_type_rec.ATTRIBUTE2
3482 , l_terr_type_rec.ATTRIBUTE3
3483 , l_terr_type_rec.ATTRIBUTE4
3484 , l_terr_type_rec.ATTRIBUTE5
3485 , l_terr_type_rec.ATTRIBUTE6
3486 , l_terr_type_rec.ATTRIBUTE7
3487 , l_terr_type_rec.ATTRIBUTE8
3488 , l_terr_type_rec.ATTRIBUTE9
3489 , l_terr_type_rec.ATTRIBUTE10
3490 , l_terr_type_rec.ATTRIBUTE11
3491 , l_terr_type_rec.ATTRIBUTE12
3492 , l_terr_type_rec.ATTRIBUTE13
3493 , l_terr_type_rec.ATTRIBUTE14
3494 , l_terr_type_rec.ATTRIBUTE15
3495 , l_terr_type_rec.ORG_ID;
3496
3497 IF get_terr_type%NOTFOUND THEN
3498
3499 /* Debug message */
3500 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3501 FND_MESSAGE.Set_Name ('JTF', G_PKG_NAME || ': Terr Type record not found');
3502 FND_MSG_PUB.ADD;
3503 END IF;
3504 END IF;
3505
3506 CLOSE get_terr_type;
3507 END IF;
3508
3509 --dbms_output.put_line( 'get_terr_type: return_status = '|| l_return_status ||
3510 -- ' terr_type_name = ' || l_terr_type_rec.name);
3511
3512 /***********************/
3513 /* get sub territories */
3514 /***********************/
3515 counter := 0;
3516
3517 OPEN get_sub_terr ( p_terr_id );
3518 LOOP
3519 FETCH get_sub_terr INTO
3520 l_terr_sub_terr_tbl(counter).TERR_ID
3521 , l_terr_sub_terr_tbl(counter).LAST_UPDATE_DATE
3522 , l_terr_sub_terr_tbl(counter).LAST_UPDATED_BY
3523 , l_terr_sub_terr_tbl(counter).CREATION_DATE
3524 , l_terr_sub_terr_tbl(counter).CREATED_BY
3525 , l_terr_sub_terr_tbl(counter).LAST_UPDATE_LOGIN
3526 , l_terr_sub_terr_tbl(counter).REQUEST_ID
3527 , l_terr_sub_terr_tbl(counter).PROGRAM_APPLICATION_ID
3528 , l_terr_sub_terr_tbl(counter).PROGRAM_ID
3529 , l_terr_sub_terr_tbl(counter).PROGRAM_UPDATE_DATE
3530 , l_terr_sub_terr_tbl(counter).APPLICATION_SHORT_NAME
3531 , l_terr_sub_terr_tbl(counter).NAME
3532 , l_terr_sub_terr_tbl(counter).ENABLED_FLAG
3533 , l_terr_sub_terr_tbl(counter).START_DATE_ACTIVE
3534 , l_terr_sub_terr_tbl(counter).END_DATE_ACTIVE
3535 , l_terr_sub_terr_tbl(counter).PLANNED_FLAG
3536 , l_terr_sub_terr_tbl(counter).PARENT_TERRITORY_ID
3537 , l_terr_sub_terr_tbl(counter).TERRITORY_TYPE_ID
3538 , l_terr_sub_terr_tbl(counter).TEMPLATE_TERRITORY_ID
3539 , l_terr_sub_terr_tbl(counter).TEMPLATE_FLAG
3540 , l_terr_sub_terr_tbl(counter).ESCALATION_TERRITORY_ID
3541 , l_terr_sub_terr_tbl(counter).ESCALATION_TERRITORY_FLAG
3542 , l_terr_sub_terr_tbl(counter).OVERLAP_ALLOWED_FLAG
3543 , l_terr_sub_terr_tbl(counter).RANK
3544 , l_terr_sub_terr_tbl(counter).DESCRIPTION
3545 , l_terr_sub_terr_tbl(counter).UPDATE_FLAG
3546 , l_terr_sub_terr_tbl(counter).AUTO_ASSIGN_RESOURCES_FLAG
3547 , l_terr_sub_terr_tbl(counter).ATTRIBUTE_CATEGORY
3548 , l_terr_sub_terr_tbl(counter).ATTRIBUTE1
3549 , l_terr_sub_terr_tbl(counter).ATTRIBUTE2
3550 , l_terr_sub_terr_tbl(counter).ATTRIBUTE3
3551 , l_terr_sub_terr_tbl(counter).ATTRIBUTE4
3552 , l_terr_sub_terr_tbl(counter).ATTRIBUTE5
3553 , l_terr_sub_terr_tbl(counter).ATTRIBUTE6
3554 , l_terr_sub_terr_tbl(counter).ATTRIBUTE7
3555 , l_terr_sub_terr_tbl(counter).ATTRIBUTE8
3556 , l_terr_sub_terr_tbl(counter).ATTRIBUTE9
3557 , l_terr_sub_terr_tbl(counter).ATTRIBUTE10
3558 , l_terr_sub_terr_tbl(counter).ATTRIBUTE11
3559 , l_terr_sub_terr_tbl(counter).ATTRIBUTE12
3560 , l_terr_sub_terr_tbl(counter).ATTRIBUTE13
3561 , l_terr_sub_terr_tbl(counter).ATTRIBUTE14
3562 , l_terr_sub_terr_tbl(counter).ATTRIBUTE15
3563 , l_terr_sub_terr_tbl(counter).ORG_ID
3564 , l_terr_sub_terr_tbl(counter).TERR_TYPE_NAME
3565 , l_terr_sub_terr_tbl(counter).PARENT_TERR_NAME
3566 , l_terr_sub_terr_tbl(counter).ESCALATION_TERR_NAME
3567 , l_terr_sub_terr_tbl(counter).TEMPLATE_TERR_NAME
3568 , l_terr_sub_terr_tbl(counter).TERR_USG_ID
3569 , l_terr_sub_terr_tbl(counter).SOURCE_ID
3570 , l_terr_sub_terr_tbl(counter).TERR_USAGE;
3571
3572 EXIT WHEN get_sub_terr%NOTFOUND;
3573
3574 --dbms_output.put_line( 'get_sub_terr: counter = '|| counter ||
3575 -- ' terr_name = ' || l_terr_sub_terr_tbl(counter).name);
3576
3577 counter := counter + 1;
3578
3579 END LOOP;
3580 CLOSE get_sub_terr;
3581
3582 --dbms_output.put_line( 'AFTER get_sub_terr: counter = '|| counter );
3583
3584 /************************/
3585 /* get territory usages */
3586 /************************/
3587 counter := 0;
3588
3589 OPEN get_terr_usgs ( p_terr_id );
3590 LOOP
3591 FETCH get_terr_usgs INTO
3592 l_terr_usgs_tbl(counter).TERR_USG_ID
3593 , l_terr_usgs_tbl(counter).LAST_UPDATE_DATE
3594 , l_terr_usgs_tbl(counter).LAST_UPDATED_BY
3595 , l_terr_usgs_tbl(counter).CREATION_DATE
3596 , l_terr_usgs_tbl(counter).CREATED_BY
3597 , l_terr_usgs_tbl(counter).LAST_UPDATE_LOGIN
3598 , l_terr_usgs_tbl(counter).TERR_ID
3599 , l_terr_usgs_tbl(counter).SOURCE_ID
3600 , l_terr_usgs_tbl(counter).ORG_ID
3601 , l_terr_usgs_tbl(counter).USAGE;
3602
3603 EXIT WHEN get_terr_usgs%NOTFOUND;
3604
3605 --dbms_output.put_line( 'get_terr_usgs: counter = '|| counter ||
3606 -- ' terr_usage = ' || l_terr_usgs_tbl(counter).source_id ||
3607 -- ' usage = ' || l_terr_usgs_tbl(counter).usage);
3608
3609 counter := counter + 1;
3610
3611 END LOOP;
3612 CLOSE get_terr_usgs;
3613
3614 --dbms_output.put_line( 'AFTER get_terr_usgs: counter = '|| counter );
3615
3616 /***************************************/
3617 /* get territory qualifier type usages */
3618 /***************************************/
3619 counter := 0;
3620
3621 OPEN get_terr_qtype_usgs ( p_terr_id );
3622 LOOP
3623 FETCH get_terr_qtype_usgs INTO
3624 l_terr_qtype_usgs_tbl(counter).TERR_QTYPE_USG_ID
3625 , l_terr_qtype_usgs_tbl(counter).LAST_UPDATED_BY
3626 , l_terr_qtype_usgs_tbl(counter).LAST_UPDATE_DATE
3627 , l_terr_qtype_usgs_tbl(counter).CREATED_BY
3628 , l_terr_qtype_usgs_tbl(counter).CREATION_DATE
3629 , l_terr_qtype_usgs_tbl(counter).LAST_UPDATE_LOGIN
3630 , l_terr_qtype_usgs_tbl(counter).TERR_ID
3631 , l_terr_qtype_usgs_tbl(counter).QUAL_TYPE_USG_ID
3632 , l_terr_qtype_usgs_tbl(counter).ORG_ID
3633 , l_terr_qtype_usgs_tbl(counter).SOURCE_ID
3634 , l_terr_qtype_usgs_tbl(counter).QUAL_TYPE_ID
3635 , l_terr_qtype_usgs_tbl(counter).QUALIFIER_TYPE_NAME
3636 , l_terr_qtype_usgs_tbl(counter).QUALIFIER_TYPE_DESCRIPTION;
3637
3638 EXIT WHEN get_terr_qtype_usgs%NOTFOUND;
3639
3640 --dbms_output.put_line( 'get_terr_qtype_usgs: counter = '|| counter ||
3641 -- ' terr_qtype_usg = ' ||
3642 -- l_terr_qtype_usgs_tbl(counter).qual_type_usg_id ||
3643 -- ' transaction = ' ||
3644 -- l_terr_qtype_usgs_tbl(counter).qualifier_type_description);
3645
3646 counter := counter + 1;
3647
3648 END LOOP;
3649 CLOSE get_terr_qtype_usgs;
3650
3651 --dbms_output.put_line( 'AFTER get_terr_qtype_usgs: counter = '|| counter );
3652
3653 /****************************/
3654 /* get territory qualifiers */
3655 /****************************/
3656 counter := 0;
3657
3658 OPEN get_terr_qual(p_terr_id);
3659 LOOP
3660 FETCH get_terr_qual INTO
3661 l_terr_qual_tbl(counter).TERR_QUAL_ID
3662 , l_terr_qual_tbl(counter).LAST_UPDATE_DATE
3663 , l_terr_qual_tbl(counter).LAST_UPDATED_BY
3664 , l_terr_qual_tbl(counter).CREATION_DATE
3665 , l_terr_qual_tbl(counter).CREATED_BY
3666 , l_terr_qual_tbl(counter).LAST_UPDATE_LOGIN
3667 , l_terr_qual_tbl(counter).TERR_ID
3668 , l_terr_qual_tbl(counter).QUAL_USG_ID
3669 , l_terr_qual_tbl(counter).USE_TO_NAME_FLAG
3670 , l_terr_qual_tbl(counter).GENERATE_FLAG
3671 , l_terr_qual_tbl(counter).OVERLAP_ALLOWED_FLAG
3672 , l_terr_qual_tbl(counter).QUALIFIER_MODE
3673 , l_terr_qual_tbl(counter).ORG_ID
3674 , l_terr_qual_tbl(counter).DISPLAY_TYPE
3675 , l_terr_qual_tbl(counter).LOV_SQL
3676 , l_terr_qual_tbl(counter).CONVERT_TO_ID_FLAG
3677 , l_terr_qual_tbl(counter).QUAL_TYPE_ID
3678 , l_terr_qual_tbl(counter).QUALIFIER_TYPE_NAME
3679 , l_terr_qual_tbl(counter).QUALIFIER_TYPE_DESCRIPTION
3680 , l_terr_qual_tbl(counter).QUALIFIER_NAME;
3681
3682 EXIT WHEN get_terr_qual%NOTFOUND;
3683
3684 --dbms_output.put_line( 'get_terr_qual: counter = '|| counter ||
3685 -- ' terr_qual = ' || l_terr_qual_tbl(counter).qual_usg_id ||
3686 -- ' qualifier_name = ' || l_terr_qual_tbl(counter).qualifier_name);
3687
3688 counter := counter + 1;
3689
3690 END LOOP;
3691 CLOSE get_terr_qual;
3692
3693 --dbms_output.put_line( 'AFTER get_terr_qual: counter = '|| counter );
3694
3695 /**********************************/
3696 /* get territory qualifier values */
3697 /**********************************/
3698 counter := 0;
3699
3700 OPEN get_terr_values ( p_terr_id );
3701 LOOP
3702 FETCH get_terr_values INTO
3703 l_terr_values_tbl(counter).TERR_VALUE_ID
3704 , l_terr_values_tbl(counter).LAST_UPDATED_BY
3705 , l_terr_values_tbl(counter).LAST_UPDATE_DATE
3706 , l_terr_values_tbl(counter).CREATED_BY
3707 , l_terr_values_tbl(counter).CREATION_DATE
3708 , l_terr_values_tbl(counter).LAST_UPDATE_LOGIN
3709 , l_terr_values_tbl(counter).TERR_QUAL_ID
3710 , l_terr_values_tbl(counter).INCLUDE_FLAG
3711 , l_terr_values_tbl(counter).COMPARISON_OPERATOR
3712 , l_terr_values_tbl(counter).ID_USED_FLAG
3713 , l_terr_values_tbl(counter).LOW_VALUE_CHAR_ID
3714 , l_terr_values_tbl(counter).LOW_VALUE_CHAR
3715 , l_terr_values_tbl(counter).HIGH_VALUE_CHAR
3716 , l_terr_values_tbl(counter).LOW_VALUE_NUMBER
3717 , l_terr_values_tbl(counter).HIGH_VALUE_NUMBER
3718 , l_terr_values_tbl(counter).VALUE_SET
3719 , l_terr_values_tbl(counter).INTEREST_TYPE_ID
3720 , l_terr_values_tbl(counter).PRIMARY_INTEREST_CODE_ID
3721 , l_terr_values_tbl(counter).SECONDARY_INTEREST_CODE_ID
3722 , l_terr_values_tbl(counter).CURRENCY_CODE
3723 , l_terr_values_tbl(counter).ORG_ID;
3724
3725 EXIT WHEN get_terr_values%NOTFOUND;
3726
3727 --dbms_output.put_line( 'get_terr_values: counter = '|| counter ||
3728 -- ' terr_value = ' || l_terr_values_tbl(counter).terr_value_id);
3729
3730 counter := counter + 1;
3731
3732 END LOOP;
3733 CLOSE get_terr_values;
3734
3735 --dbms_output.put_line( 'AFTER get_terr_values: counter = '|| counter );
3736
3737 /***************************/
3738 /* get territory resources */
3739 /***************************/
3740 counter := 0;
3741
3742 OPEN get_terr_rsc ( p_terr_id );
3743 LOOP
3744 FETCH get_terr_rsc INTO
3745 l_terr_rsc_tbl(counter).TERR_RSC_ID
3746 , l_terr_rsc_tbl(counter).LAST_UPDATE_DATE
3747 , l_terr_rsc_tbl(counter).LAST_UPDATED_BY
3748 , l_terr_rsc_tbl(counter).CREATION_DATE
3749 , l_terr_rsc_tbl(counter).CREATED_BY
3750 , l_terr_rsc_tbl(counter).LAST_UPDATE_LOGIN
3751 , l_terr_rsc_tbl(counter).TERR_ID
3752 , l_terr_rsc_tbl(counter).RESOURCE_ID
3753 , l_terr_rsc_tbl(counter).RESOURCE_TYPE
3754 , l_terr_rsc_tbl(counter).ROLE
3755 , l_terr_rsc_tbl(counter).PRIMARY_CONTACT_FLAG
3756 , l_terr_rsc_tbl(counter).START_DATE_ACTIVE
3757 , l_terr_rsc_tbl(counter).END_DATE_ACTIVE
3758 , l_terr_rsc_tbl(counter).FULL_ACCESS_FLAG
3759 , l_terr_rsc_tbl(counter).ORG_ID
3760 , l_terr_rsc_tbl(counter).RESOURCE_NAME;
3761
3762 EXIT WHEN get_terr_rsc%NOTFOUND;
3763
3764 --dbms_output.put_line( 'get_terr_rsc: counter = '|| counter ||
3765 -- ' terr_rsc = ' || l_terr_rsc_tbl(counter).terr_rsc_id ||
3766 -- ' resource_name = ' || l_terr_rsc_tbl(counter).resource_name);
3767
3768 counter := counter + 1;
3769
3770 END LOOP;
3771 CLOSE get_terr_rsc;
3772
3773 --dbms_output.put_line( 'AFTER get_terr_rsc: counter = '|| counter );
3774
3775 /* save return variables */
3776 x_terr_rec := l_terr_rec;
3777 x_terr_type_rec := l_terr_type_rec;
3778 x_terr_sub_terr_tbl := l_terr_sub_terr_tbl;
3779 x_terr_usgs_tbl := l_terr_usgs_tbl;
3780 x_terr_qtype_usgs_tbl := l_terr_qtype_usgs_tbl;
3781 x_terr_qual_tbl := l_terr_qual_tbl;
3782 x_terr_values_tbl := l_terr_values_tbl;
3783 x_terr_rsc_tbl := l_terr_rsc_tbl;
3784
3785 /* save return status */
3786 x_return_status := l_return_status;
3787
3788 --dbms_output.put_line( 'get_terr: END return_status = '|| l_return_status );
3789
3790 /**********************************************************************************************
3791 ** API BODY END
3792 ***********************************************************************************************/
3793
3794 /* Debug Message */
3795 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3796 THEN
3797 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
3798 FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
3799 FND_MSG_PUB.Add;
3800 END IF;
3801
3802 /* Standard call to get message count and if count is 1, get message info. */
3803 FND_MSG_PUB.Count_And_Get
3804 ( p_count => x_msg_count,
3805 p_data => x_msg_data
3806 );
3807
3808 EXCEPTION
3809 WHEN FND_API.G_EXC_ERROR THEN
3810 x_return_status := FND_API.G_RET_STS_ERROR ;
3811 --dbms_output.put_line( 'FND_API.G_EXC_ERROR: return_status = '|| x_return_status );
3812
3813 FND_MSG_PUB.Count_And_Get
3814 ( p_count => x_msg_count,
3815 p_data => x_msg_data
3816 );
3817
3818
3819 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3820 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3821 --dbms_output.put_line( 'FND_API.G_RET_STS_UNEXP_ERROR: return_status = '|| x_return_status );
3822
3823 FND_MSG_PUB.Count_And_Get
3824 ( p_count => x_msg_count,
3825 p_data => x_msg_data
3826 );
3827
3828 WHEN OTHERS THEN
3829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3830 --dbms_output.put_line( 'OTHERS: return_status = '|| x_return_status );
3831 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3832 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3833 END IF;
3834
3835 FND_MSG_PUB.Count_And_Get
3836 ( p_count => x_msg_count,
3837 p_data => x_msg_data
3838 );
3839
3840 END Get_Territory_Details;
3841
3842
3843 -- ***************************************************
3844 -- start of comments
3845 -- ***************************************************
3846 --
3847 -- API name : Get_Escalation_Territory
3848 -- Type : PUBLIC
3849 -- Function : To get a territory's escalation territory
3850 --
3851 --
3852 -- Pre-reqs :
3853 -- Parameters:
3854 -- IN :
3855 -- Required
3856 -- Parameter Name Data Type Default
3857 -- p_Api_Version_Number NUMBER
3858 -- p_Escalation_Terr_Id NUMBER
3859 --
3860 -- Optional
3861 -- Parameter Name Data Type Default
3862 -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
3863 -- p_Commit VARCHAR2 FND_API.G_FALSE
3864 --
3865 -- OUT :
3866 -- Parameter Name Data Type
3867 -- x_Return_Status VARCHAR2(1)
3868 -- x_Msg_Count NUMBER
3869 -- x_Msg_Data VARCHAR2(2000)
3870 --
3871 --
3872 -- Notes:
3873 --
3874 --
3875 -- End of Comments
3876 --
3877 PROCEDURE Get_Escalation_Territory (
3878 p_Api_Version IN NUMBER,
3879 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3880 x_return_status OUT NOCOPY VARCHAR2,
3881 x_msg_count OUT NOCOPY NUMBER,
3882 x_msg_data OUT NOCOPY VARCHAR2,
3883 p_terr_id IN NUMBER := FND_API.G_MISS_NUM,
3884 x_escalation_terr_id OUT NOCOPY NUMBER
3885 )
3886 IS
3887
3888 l_api_name CONSTANT VARCHAR2(30) := 'Get_Escalation_Territory';
3889 l_api_version CONSTANT NUMBER := 1.0;
3890
3891 /* cursor to return escalation terr id */
3892 CURSOR c_esc_terr_id ( p_terr_id NUMBER )
3893 IS
3894 /* modified for R12 as there is no concept of escalation territory for R12 , instead */
3895 /* resources will be assigned to the territory as escalation owner */
3896 /* SELECT jt.escalation_territory_id */
3897 SELECT jt.terr_id
3898 FROM jtf_terr_all jt
3899 WHERE jt.terr_id = p_terr_id
3900 AND NVL(jt.end_date_active, sysdate+1) > sysdate
3901 AND NVL(jt.start_date_active, sysdate-1) < sysdate;
3902
3903 /* cursor return variable */
3904 esc_csr NUMBER;
3905
3906 /* Status local variable */
3907 l_return_status VARCHAR2(1);
3908
3909 /* local scratch variable */
3910 l_escalation_terr_id NUMBER;
3911
3912 BEGIN
3913 /* Standard call to check for call compatibility */
3914 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3915 p_api_version,
3916 l_api_name,
3917 G_PKG_NAME)
3918 THEN
3919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3920 END IF;
3921
3922 /* Initialize message list if p_init_msg_list is set to TRUE */
3923 IF FND_API.to_Boolean( p_init_msg_list )
3924 THEN
3925 FND_MSG_PUB.initialize;
3926 END IF;
3927
3928 /* Debug Message */
3929 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3930 THEN
3931 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
3932 FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
3933 FND_MSG_PUB.Add;
3934 END IF;
3935
3936
3937 /**********************************************************************************************
3938 ** API BODY START
3939 ***********************************************************************************************/
3940
3941 /* Initialize API return status to SUCCESS */
3942 l_return_status := FND_API.G_RET_STS_SUCCESS;
3943
3944 OPEN c_esc_terr_id (p_terr_id);
3945 FETCH c_esc_terr_id INTO esc_csr;
3946 IF c_esc_terr_id%NOTFOUND THEN
3947
3948 l_return_status := FND_API.G_RET_STS_ERROR;
3949
3950 -- Debug message
3951 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3952 FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_ESC_TERR_NOT_FOUND');
3953 FND_MSG_PUB.ADD;
3954 END IF;
3955
3956 ELSE
3957
3958 l_escalation_terr_id := esc_csr;
3959
3960 END IF;
3961
3962 CLOSE c_esc_terr_id;
3963
3964 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3965 RAISE FND_API.G_EXC_ERROR;
3966 END IF;
3967
3968 /* save return variable */
3969 x_escalation_terr_id := l_escalation_terr_id;
3970
3971 /* save return status */
3972 x_return_status := l_return_status;
3973
3974 /**********************************************************************************************
3975 ** API BODY END
3976 ***********************************************************************************************/
3977
3978 /* Debug Message */
3979 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
3980 THEN
3981 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
3982 FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
3983 FND_MSG_PUB.Add;
3984 END IF;
3985
3986 /* Standard call to get message count and if count is 1, get message info. */
3987 FND_MSG_PUB.Count_And_Get
3988 ( p_count => x_msg_count,
3989 p_data => x_msg_data
3990 );
3991
3992 EXCEPTION
3993 WHEN FND_API.G_EXC_ERROR THEN
3994 x_return_status := FND_API.G_RET_STS_ERROR ;
3995
3996 FND_MSG_PUB.Count_And_Get
3997 ( p_count => x_msg_count,
3998 p_data => x_msg_data
3999 );
4000
4001
4002 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4003 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4004
4005 FND_MSG_PUB.Count_And_Get
4006 ( p_count => x_msg_count,
4007 p_data => x_msg_data
4008 );
4009
4010 WHEN OTHERS THEN
4011 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4012
4013 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4014 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4015 END IF;
4016
4017 FND_MSG_PUB.Count_And_Get
4018 ( p_count => x_msg_count,
4019 p_data => x_msg_data
4020 );
4021
4022 END Get_Escalation_Territory;
4023
4024
4025 -- ***************************************************
4026 -- start of comments
4027 -- ***************************************************
4028 --
4029 -- API name : Get_Parent_Territory
4030 -- Type : PUBLIC
4031 -- Function : To get a territory's parent territory
4032 --
4033 --
4034 -- Pre-reqs :
4035 -- Parameters:
4036 -- IN :
4037 -- Required
4038 -- Parameter Name Data Type Default
4039 -- p_Api_Version_Number NUMBER
4040 -- p_Parent_Terr_Id NUMBER
4041 --
4042 -- Optional
4043 -- Parameter Name Data Type Default
4044 -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
4045 -- p_Commit VARCHAR2 FND_API.G_FALSE
4046 --
4047 -- OUT :
4048 -- Parameter Name Data Type
4049 -- x_Return_Status VARCHAR2(1)
4050 -- x_Msg_Count NUMBER
4051 -- x_Msg_Data VARCHAR2(2000)
4052 --
4053 --
4054 -- Notes:
4055 --
4056 --
4057 -- End of Comments
4058 --
4059 PROCEDURE Get_Parent_Territory (
4060 p_Api_Version IN NUMBER,
4061 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4062 x_return_status OUT NOCOPY VARCHAR2,
4063 x_msg_count OUT NOCOPY NUMBER,
4064 x_msg_data OUT NOCOPY VARCHAR2,
4065 p_terr_id IN NUMBER := FND_API.G_MISS_NUM,
4066 x_parent_terr_id OUT NOCOPY NUMBER
4067 )
4068 IS
4069
4070 l_api_name CONSTANT VARCHAR2(30) := 'Get_Parent_Territory';
4071 l_api_version CONSTANT NUMBER := 1.0;
4072
4073 /* cursor to return escalation terr id */
4074 CURSOR c_parent_terr_id ( p_terr_id NUMBER )
4075 IS
4076 SELECT jt.parent_territory_id
4077 FROM jtf_terr_all jt
4078 WHERE jt.terr_id = p_terr_id;
4079
4080 /* cursor return vraiable */
4081 par_csr NUMBER;
4082
4083 /* Status local variable */
4084 l_return_status VARCHAR2(1);
4085
4086 /* local scratch variable */
4087 l_parent_terr_id NUMBER;
4088
4089
4090 BEGIN
4091 /* Standard call to check for call compatibility */
4092 IF NOT FND_API.Compatible_API_Call ( l_api_version,
4093 p_api_version,
4094 l_api_name,
4095 G_PKG_NAME)
4096 THEN
4097 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4098 END IF;
4099
4100 /* Initialize message list if p_init_msg_list is set to TRUE */
4101 IF FND_API.to_Boolean( p_init_msg_list )
4102 THEN
4103 FND_MSG_PUB.initialize;
4104 END IF;
4105
4106 /* Debug Message */
4107 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4108 THEN
4109 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4110 FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
4111 FND_MSG_PUB.Add;
4112 END IF;
4113
4114 /**********************************************************************************************
4115 ** API BODY START
4116 ***********************************************************************************************/
4117
4118 /* Initialize API return status to SUCCESS */
4119 l_return_status := FND_API.G_RET_STS_SUCCESS;
4120
4121 OPEN c_parent_terr_id (p_terr_id);
4122 FETCH c_parent_terr_id INTO par_csr;
4123 IF c_parent_terr_id%NOTFOUND THEN
4124
4125 l_return_status := FND_API.G_RET_STS_ERROR;
4126
4127 -- Debug message
4128 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4129 FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_PARENT_NOT_FOUND');
4130 FND_MSG_PUB.ADD;
4131 END IF;
4132
4133 ELSE
4134
4135 l_parent_terr_id := par_csr;
4136
4137 END IF;
4138
4139 CLOSE c_parent_terr_id;
4140
4141 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4142 RAISE FND_API.G_EXC_ERROR;
4143 END IF;
4144
4145 /* save return variable */
4146 x_parent_terr_id := l_parent_terr_id;
4147
4148 /* save return status */
4149 x_return_status := l_return_status;
4150
4151 /**********************************************************************************************
4152 ** API BODY END
4153 ***********************************************************************************************/
4154
4155 /* Debug Message */
4156 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4157 THEN
4158 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
4159 FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
4160 FND_MSG_PUB.Add;
4161 END IF;
4162
4163 /* Standard call to get message count and if count is 1, get message info. */
4164 FND_MSG_PUB.Count_And_Get
4165 ( p_count => x_msg_count,
4166 p_data => x_msg_data
4167 );
4168
4169 EXCEPTION
4170 WHEN FND_API.G_EXC_ERROR THEN
4171 x_return_status := FND_API.G_RET_STS_ERROR ;
4172
4173 FND_MSG_PUB.Count_And_Get
4174 ( p_count => x_msg_count,
4175 p_data => x_msg_data
4176 );
4177
4178
4179 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4181
4182 FND_MSG_PUB.Count_And_Get
4183 ( p_count => x_msg_count,
4184 p_data => x_msg_data
4185 );
4186
4187 WHEN OTHERS THEN
4188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4189
4190 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4191 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4192 END IF;
4193
4194 FND_MSG_PUB.Count_And_Get
4195 ( p_count => x_msg_count,
4196 p_data => x_msg_data
4197 );
4198
4199 END Get_Parent_Territory;
4200
4201
4202 -- ***************************************************
4203 -- start of comments
4204 -- ***************************************************
4205 --
4206 -- API name : Get_Escalation_TerrMembers
4207 -- Type : PUBLIC
4208 -- Function : To get reosurces attached with a escalation
4209 -- territory
4210 --
4211 --
4212 -- Pre-reqs :
4213 -- Parameters:
4214 -- IN :
4215 -- Required
4216 -- Parameter Name Data Type Default
4217 -- p_Api_Version_Number NUMBER
4218 -- p_Terr_Id NUMBER
4219 --
4220 -- Optional
4221 -- Parameter Name Data Type Default
4222 -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
4223 -- p_Commit VARCHAR2 FND_API.G_FALSE
4224 --
4225 -- OUT :
4226 -- Parameter Name Data Type
4227 -- x_Return_Status VARCHAR2(1)
4228 -- x_Msg_Count NUMBER
4229 -- x_Msg_Data VARCHAR2(2000)
4230 --
4231 --
4232 -- Notes:
4233 --
4234 --
4235 -- End of Comments
4236 --
4237 PROCEDURE Get_Escalation_TerrMembers
4238 (p_api_version_number IN NUMBER,
4239 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4240 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4241 x_return_status OUT NOCOPY VARCHAR2,
4242 x_msg_count OUT NOCOPY NUMBER,
4243 x_msg_data OUT NOCOPY VARCHAR2,
4244 p_terr_id IN NUMBER,
4245 x_QualifyingRsc_out_tbl OUT NOCOPY QualifyingRsc_out_tbl_type)
4246 AS
4247 l_api_name CONSTANT VARCHAR2(30) := 'Get_Escalation_TerrMembers';
4248 l_api_version_number CONSTANT NUMBER := 1.0;
4249 l_return_status VARCHAR2(1);
4250
4251 l_QualifyingRsc_out_rec QualifyingRsc_out_rec_type;
4252
4253 --Declare cursor to get resource accesses
4254 Cursor C_GetTerrRsc IS
4255 Select JTR.TERR_RSC_ID,
4256 JTR.TERR_ID,
4257 JT.NAME,
4258 JTR.RESOURCE_ID,
4259 --JTRA.ACCESS_TYPE,
4260 JTR.RESOURCE_TYPE,
4261 JTR.ROLE,
4262 JTR.PRIMARY_CONTACT_FLAG
4263 From JTF_TERR_RSC_ALL JTR,
4264 --JTF_TERR_RSC_ACCESS JTRA,
4265 JTF_TERR_ALL JT
4266 Where JT.TERR_ID = p_Terr_id
4267 AND JTR.TERR_ID = JT.TERR_ID
4268 --AND JTR.TERR_RSC_ID = JTRA.TERR_RSC_ID (+)
4269 AND NVL(jtr.end_date_active, sysdate+1) > sysdate
4270 AND NVL(jtr.start_date_active, sysdate-1) < sysdate
4271 AND NVL(jt.end_date_active, sysdate+1) > sysdate
4272 AND NVL(jt.start_date_active, sysdate-1) < sysdate
4273 AND EXISTS (
4274 SELECT 1
4275 FROM JTF_TERR_RSC_ACCESS_ALL JTRA
4276 WHERE JTRA.terr_rsc_id = JTR.terr_rsc_id
4277 AND JTRA.trans_access_code = 'ESC_OWNER');
4278
4279
4280 l_Counter NUMBER := 1;
4281
4282 BEGIN
4283 --dbms_output.put_line('Get_Escalation_TerrMembers: Entering the API');
4284
4285 -- Standard call to check for call compatibility.
4286 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4287 p_api_version_number,
4288 l_api_name,
4289 G_PKG_NAME)
4290 THEN
4291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4292 END IF;
4293
4294
4295 -- Initialize message list if p_init_msg_list is set to TRUE.
4296 IF FND_API.to_Boolean( p_init_msg_list )
4297 THEN
4298 FND_MSG_PUB.initialize;
4299 END IF;
4300
4301 /* Debug Message */
4302 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4303 THEN
4304 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_START_MSG');
4305 FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
4306 FND_MSG_PUB.Add;
4307 END IF;
4308 --
4309 -- API body
4310 --
4311 x_return_status := FND_API.G_RET_STS_SUCCESS;
4312
4313 for res_rec in C_GetTerrRsc
4314 loop
4315
4316 x_QualifyingRsc_out_tbl(l_Counter).terr_rsc_id := res_rec.terr_rsc_id;
4317 x_QualifyingRsc_out_tbl(l_Counter).terr_id := res_rec.terr_id;
4318 x_QualifyingRsc_out_tbl(l_Counter).terr_name := res_rec.name;
4319 x_QualifyingRsc_out_tbl(l_Counter).resource_id := res_rec.resource_id;
4320 x_QualifyingRsc_out_tbl(l_Counter).resource_type := res_rec.resource_type;
4321 x_QualifyingRsc_out_tbl(l_Counter).role := res_rec.role;
4322 x_QualifyingRsc_out_tbl(l_Counter).primary_contact_flag := res_rec.primary_contact_flag;
4323
4324 l_Counter := l_Counter + 1;
4325
4326 end loop;
4327 --
4328 If l_Counter = 1 Then
4329 null;
4330 --dbms_output.put_line('No records returned');
4331 End If;
4332 --
4333 /* Debug Message */
4334 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
4335 THEN
4336 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_END_MSG');
4337 FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
4338 FND_MSG_PUB.Add;
4339 END IF;
4340
4341 -- Standard call to get message count and if count is 1, get message info.
4342 FND_MSG_PUB.Count_And_Get
4343 ( p_count => x_msg_count,
4344 p_data => x_msg_data
4345 );
4346 --dbms_output.put_line('Get_Escalation_TerrMembers: Exiting the API');
4347 EXCEPTION
4348 --
4349 WHEN OTHERS THEN
4350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4351 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4352 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4353 END IF;
4354 FND_MSG_PUB.Count_And_Get
4355 ( p_count => x_msg_count,
4356 p_data => x_msg_data
4357 );
4358 --
4359 END Get_Escalation_TerrMembers;
4360
4361 End JTF_TERRITORY_GET_PUB; /* End of Package Body */