[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_ENGINE_GEN2_PVT
Source
1 Package Body JTF_TERR_ENGINE_GEN2_PVT AS
2 /* $Header: jtfvtseb.pls 120.0 2005/06/02 18:23:03 appldev ship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTF_TERR_ENGINE_GEN_PVT
6 -- ---------------------------------------------------
7 -- PURPOSE
8 -- Joint task force core territory manager public api's.
9 -- This package is used to generate the complete territory
10 -- Engine based on tha data setup in the JTF territory tables
11 --
12 -- Procedures:
13 -- (see below for specification)
14 --
15 -- NOTES
16 -- This package is available for private use only
17 --
18 -- HISTORY
19 -- 03/11/00 JDOCHERT Created
20 -- 06/27/01 EIHSU Added Trade Management logic
21 -- 07/17/01 EIHSU fixed bug 1887176
22 -- 09/26/01 JDOCHERT BUG#2008850
23 -- 02/14/02 SP Changed Paameter List for Contracts
24 -- 10/11/2002 jradhakr Changed the record declaration and access control for Collections
25 -- bug 1677560
26 -- 03/08/04 ACHANDA Bug 3380047
27 -- 03/09/04 ARPATEL Bug 3470748
28 -- End of Comments
29 --
30
31 /*------------------------------------------------------
32
33 ---------------------------------------------------------*/
34
35 --------------------------------------------------
36 --- GLOBAL Declarations Starts here -----
37 --------------------------------------------------
38
39 -- Stores the org_id for use in package Names
40 g_cached_org_append VARCHAR2(15);
41 --
42 -- Identifies the Package associated a
43 -- a territory with child nodes
44 g_terr_pkgspec terr_pkgspec_tbl_type;
45
46 -- Stores the position with the table spec
47 g_stack_pointer NUMBER := 0;
48
49 -- Store the information passed as
50 -- Concurrent program parameters
51 -- Module that uses Territories
52 g_source_id NUMBER := 0;
53
54 g_abs_source_id NUMBER := 0;
55
56 -- Type of transaction for which the
57 -- the package is being generated
58 g_qualifier_type VARCHAR2(60);
59
60 -- Id of the corresponding transaction type
61 g_qual_type_id NUMBER := 0;
62
63 TYPE t_pkgname IS TABLE OF VARCHAR2(256)
64 INDEX BY BINARY_INTEGER;
65
66 g_pkgname_tbl t_pkgname;
67 g_Pointer NUMBER := 0;
68 G_Debug BOOLEAN := FALSE;
69 g_ProgramStatus NUMBER := 0;
70
71 --------------------------------------------------------------------
72 -- Logging PROCEDURE
73 --
74 -- which = 1. write to log
75 -- which = 2, write to output
76 --------------------------------------------------------------------
77 --
78 PROCEDURE Write_Log(which number, mssg varchar2 )
79 IS
80 BEGIN
81 --
82 --dbms_output.put_line(' LOG: ' || mssg );
83 FND_FILE.put(which, mssg);
84 FND_FILE.NEW_LINE(which, 1);
85 --
86 -- If the output message and if debug flag is set then also write
87 -- to the log file
88 --
89 If Which = 2 Then
90 If G_Debug Then
91 FND_FILE.put(1, mssg);
92 FND_FILE.NEW_LINE(1, 1);
93 End If;
94 End IF;
95 --
96 END Write_Log;
97
98
99 ----------------------------------------------------------------
100 -- Store the Line for the package to a table
101 ----------------------------------------------------------------
102 PROCEDURE Add_To_PackageTable(P_statement IN VARCHAR2)
103 AS
104 BEGIN
105 --dbms_output.put_line( P_statement );
106
107 ad_ddl.build_package(P_statement, g_pointer);
108
109 --Increment the counters
110 g_pointer := g_pointer + 1;
111 Exception
112 WHEN Others Then
113 NULL;
114 END Add_To_PackageTable;
115
116
117 ----------------------------------------------------------------
118 -- Create the package using AD_DDL command
119 ----------------------------------------------------------------
120 FUNCTION Call_Create_Package(is_package_body VARCHAR2,
121 package_name VARCHAR2) RETURN BOOLEAN
122 AS
123 l_result BOOLEAN;
124 l_status varchar2(10);
125 l_industry varchar2(10);
126
127 l_applsys_schema VARCHAR2(30);
128
129 BEGIN
130 --dbms_output.put_line('Inside Call_Create_Package PACKAGE_NAME - ' || package_name || ' g_pointer - ' || to_char(g_pointer - 1) || is_package_body || is_package_body);
131 If G_Debug Then
132 Write_Log(1, 'INSIDE PROCEDURE JTF_TERR_ENGINE_GEN2_PVT.Call_Create_Package: PACKAGE_NAME = ' || package_name );
133 End If;
134
135
136 l_result := fnd_installation.get_app_info('FND',
137 l_status,
138 l_industry,
139 l_applsys_schema);
140
141 ad_ddl.create_package(l_applsys_schema,
142 'JTF',
143 package_name,
144 is_package_body,
145 0,
146 (g_pointer - 1));
147
148 -- Reset the global pointer.
149 g_Pointer := 0;
150 return TRUE;
151 Exception
152 WHEN OTHERS THEN
153 /* ACHANDA 03/08/2004 : Bug 3380047 : if the package is not created successfully */
154 /* because of lock , the program should write an informative message to the log */
155 /* file but it should not error out */
156 write_log(1, 'Package ' || package_name || ' NOT CREATED SUCCESSFULLY ');
157 write_log(1, SQLERRM);
158 g_pointer := 0;
159 RETURN FALSE;
160 /* the following code is commented out as the logic should be executed */
161 /* irrespective of the fact whether the debug is set to Yes or No */
162 /*
163 If G_Debug Then
164 g_ProgramStatus := 1;
165 Write_Log(2, 'Program terminated with OTHERS exception. ' || SQLERRM);
166 g_Pointer := 0;
167 RETURN FALSE;
168 End If;
169 */
170 End Call_Create_Package;
171
172
173
174
175
176 /*---------------------------------------------------------------
177 This procedure will generate the PACKAGE
178 SPEC or BODY controlled by a parameter
179
180 eg: CREATE OR REPLACE PACKAGE JTF_TERR_1001_LEAD_1_240 or
181 CREATE OR REPLACE PACKAGE BODY JTF_TERR_1001_LEAD_1_240
182 ---------------------------------------------------------------*/
183 PROCEDURE generate_package_header (
184 p_package_name VARCHAR2,
185 p_description VARCHAR2,
186 p_object_type VARCHAR2
187 )
188 AS
189 v_package_name VARCHAR2(100);
190 BEGIN
191
192 v_package_name := LOWER (p_package_name);
193
194 If G_Debug Then
195 Write_Log(1, 'INSIDE PROCEDURE JTF_TERR_ENGINE_GEN2_PVT.generate_package_header: v_package_name = ' || v_package_name );
196 End If;
197
198 /* -- The description was commented out as part of AD_DDL error
199 -- that caused others exception.
200 -- ORA-20000: Unknown or unsupported object type in create_plsql_object()
201 --
202 -- Add_To_PackageTable (p_description);
203 */
204
205 IF (p_object_type = 'PKS')
206 THEN
207
208 /* create package spec */
209 --Add_To_PackageTable (p_description);
210 Add_To_PackageTable (
211 'CREATE OR REPLACE PACKAGE ' || v_package_name || ' AS '
212 );
213 Add_To_PackageTable (' ');
214
215 ELSE
216
217 /* create package body */
218 --Add_To_PackageTable (p_description);
219 Add_To_PackageTable (
220 'CREATE OR REPLACE PACKAGE BODY ' || v_package_name || ' AS '
221 );
222 Add_To_PackageTable ('--');
223 Add_To_PackageTable (' ');
224
225 END IF;
226
227 END generate_package_header;
228
229 /*----------------------------------------------------------
230 This procedure will add the the END package
231 statement for the package name passed in as
232 parameter
233
234 eg: END JTF_TERR_1001_LEAD_1_240;
235 Note: 1001 - Source Id
236 1 - Package Count
237 240 - Org Id
238 ----------------------------------------------------------*/
239 PROCEDURE generate_end_of_package (p_package_name VARCHAR2, is_package_body VARCHAR2)
240 AS
241 v_package_name VARCHAR2(100);
242 l_Status BOOLEAN;
243 BEGIN
244
245 v_package_name := LOWER (p_package_name);
246
247 If G_Debug Then
248 Write_Log(1, 'INSIDE PROCEDURE JTF_TERR_ENGINE_GEN2_PVT.generate_end_of_package: v_package_name = ' || v_package_name );
249 End If;
250
251 Add_To_PackageTable (' ' );
252 Add_To_PackageTable ('END ' || v_package_name || ';');
253 Add_To_PackageTable ('/* End of package ' || v_package_name || ' */');
254
255 /* Call the procedure to create the package using AD_DDL */
256 l_Status := Call_Create_Package(is_package_body, v_package_name);
257
258 END generate_end_of_package;
259
260 /*----------------------------------------------------------
261 This procedure will add the END procedure
262 statement for the procedure name passed in as
263 parameter, e.g., END SEARCH_TERR_RULES;
264 ----------------------------------------------------------*/
265 PROCEDURE generate_end_of_procedure ( p_procedure_name VARCHAR2
266 , p_source_id NUMBER
267 , p_qual_type VARCHAR2 )
268 AS
269
270 lp_pkg_name VARCHAR2(30);
271
272 BEGIN
273
274 If G_Debug Then
275 Write_Log(1, 'INSIDE PROCEDURE JTF_TERR_ENGINE_GEN2_PVT.generate_end_of_procedure: p_procedure_name = ' || p_procedure_name );
276 End If;
277
278 lp_pkg_name := 'JTF_TERR_' || TO_CHAR(ABS(p_source_id)) || '_' || p_qual_type || '_DYN';
279
280 Add_To_PackageTable (' ');
281 Add_To_PackageTable (' /*--------------------------------------');
282 Add_To_PackageTable (' ** When no territories, have NULL ');
283 Add_To_PackageTable (' ** so that package is not invalid' );
284 Add_To_PackageTable (' ** when it is created ');
285 Add_To_PackageTable (' **--------------------------------------*/ ');
286 Add_To_PackageTable (' NULL;');
287 Add_To_PackageTable (' ');
288 Add_To_PackageTable ('EXCEPTION ');
289 Add_To_PackageTable (' ');
290 Add_To_PackageTable (' WHEN COLLECTION_IS_NULL THEN ');
291 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', ''EXCEPTION: COLLECTION_IS_NULL''); ');
292 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', SQLERRM); ');
293 Add_To_PackageTable (' ROLLBACK TO JTF_TERR_ASSIGN_TRANSACTION; ');
294 Add_To_PackageTable (' --dbms_output.put_line( ''SQLERRM: '' || SQLERRM ); ');
295 Add_To_PackageTable (' RAISE; ');
296 Add_To_PackageTable (' ');
297 Add_To_PackageTable (' WHEN SUBSCRIPT_BEYOND_COUNT THEN ');
298 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', ''EXCEPTION: SUBSCRIPT_BEYOND_COUNT''); ');
299 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', SQLERRM); ');
300 Add_To_PackageTable (' ROLLBACK TO JTF_TERR_ASSIGN_TRANSACTION; ');
301 Add_To_PackageTable (' --dbms_output.put_line( ''SQLERRM: '' || SQLERRM ); ');
302 Add_To_PackageTable (' RAISE; ');
303 Add_To_PackageTable (' ');
304 Add_To_PackageTable (' WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN ');
305 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', ''EXCEPTION: SUBSCRIPT_OUTSIDE_LIMIT''); ');
306 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', SQLERRM); ');
307 Add_To_PackageTable (' ROLLBACK TO JTF_TERR_ASSIGN_TRANSACTION; ');
308 Add_To_PackageTable (' --dbms_output.put_line( ''SQLERRM: '' || SQLERRM ); ');
309 Add_To_PackageTable (' RAISE; ');
310 Add_To_PackageTable (' ');
311 Add_To_PackageTable (' WHEN VALUE_ERROR THEN ');
312 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', ''EXCEPTION: VALUE_ERROR''); ');
313 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', SQLERRM); ');
314 Add_To_PackageTable (' ROLLBACK TO JTF_TERR_ASSIGN_TRANSACTION; ');
315 Add_To_PackageTable (' --dbms_output.put_line( ''SQLERRM: '' || SQLERRM ); ');
316 Add_To_PackageTable (' RAISE; ');
317 Add_To_PackageTable (' ');
318 Add_To_PackageTable (' WHEN NO_DATA_FOUND THEN ');
319 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', ''EXCEPTION: NO_DATA_FOUND''); ');
320 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', SQLERRM); ');
321 Add_To_PackageTable (' ROLLBACK TO JTF_TERR_ASSIGN_TRANSACTION; ');
322 Add_To_PackageTable (' --dbms_output.put_line( ''SQLERRM: '' || SQLERRM ); ');
323 Add_To_PackageTable (' RAISE; ');
324 Add_To_PackageTable (' ');
325 Add_To_PackageTable (' WHEN OTHERS THEN ');
326 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', ''EXCEPTION: OTHERS''); ');
327 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', SQLERRM); ');
328 Add_To_PackageTable (' ROLLBACK TO JTF_TERR_ASSIGN_TRANSACTION; ');
329 Add_To_PackageTable (' --dbms_output.put_line( ''SQLERRM: '' || SQLERRM ); ');
330 Add_To_PackageTable (' RAISE; ');
331
332 Add_To_PackageTable (' ');
333 Add_To_PackageTable ('END ' || p_procedure_name || ';');
334 Add_To_PackageTable ('/* End of procedure ' || p_procedure_name || ' */');
335 Add_To_PackageTable (' ');
336
337 END generate_end_of_procedure;
338
339 /*----------------------------------------------------------
340 This procedure will create the SPEC and BODY
341 for PROCEDURE/FUNCTION
342
343 eg: PROCEDURE TERR_RULE_1;
344 Note: 1 is the Territory Id
345 ----------------------------------------------------------*/
346 PROCEDURE generate_object_definition (
347 procedure_name VARCHAR2,
348 description VARCHAR2,
349 parameter_list1 VARCHAR2,
350 parameter_list2 VARCHAR2,
351 procedure_type VARCHAR2,
352 return_type VARCHAR2,
353 object_type VARCHAR2
354 )
355 IS
356 BEGIN
357
358 If G_Debug Then
359 Write_Log(1, 'INSIDE PROCEDURE JTF_TERR_ENGINE_GEN2_PVT.generate_object_definition: procedure_name = ' || procedure_name );
360 End If;
361
362 -- Generate procedure header and parameters in both spec and body
363 IF (procedure_type = 'P')
364 THEN
365 Add_To_PackageTable ('PROCEDURE ' || LOWER (procedure_name));
366 ELSIF (procedure_type = 'F')
367 THEN
368 Add_To_PackageTable ('FUNCTION ' || LOWER (procedure_name));
369 END IF;
370
371 IF (parameter_list1 IS NOT NULL)
372 THEN
373 Add_To_PackageTable (' (' || parameter_list1 );
374 Add_To_PackageTable ( parameter_list2 || ')');
375 END IF;
376
377 IF (procedure_type = 'F')
378 THEN
379 Add_To_PackageTable (' RETURN ' || return_type);
380 END IF;
381
382 IF (object_type = 'PKS')
383 THEN
384 Add_To_PackageTable (';');
385 ELSE
386 Add_To_PackageTable (' AS');
387 END IF;
388 END generate_object_definition;
389
390 /*************************************************
391 ** Gets all the qualifiers that are used by this
392 ** Usage/Transaction type combination
393 ** and builds the SQL statement to check the
394 ** qualifier rules
395 **************************************************/
396 PROCEDURE build_qualifier_rules(
397 p_source_id IN NUMBER,
398 p_qual_type_id IN NUMBER,
399 x_qual_rules OUT NOCOPY VARCHAR2 )
400 AS
401
402 CURSOR c_terr_qual( lp_source_id NUMBER
403 , lp_qual_type_id NUMBER
404 , lp_sysdate DATE ) IS
405
406 SELECT jqu.qual_usg_id, jqu.rule1
407 FROM jtf_qual_usgs_all jqu
408 , jtf_qual_type_usgs jqtu
409 , jtf_qual_type_denorm_v v
410 WHERE --jqu.enabled_flag = 'Y'
411 jqu.org_id = -3113
412 AND jqu.qual_type_usg_id = jqtu.qual_type_usg_id
413 AND jqtu.source_id = lp_source_id
414 AND jqtu.qual_type_id = v.related_id
415 AND jqu.rule1 IS NOT NULL
416 AND v.qual_type_id = lp_qual_type_id
417 AND EXISTS ( SELECT jtq.terr_id
418 FROM jtf_terr_qtype_usgs_all jtqu
419 , jtf_terr_all jt
420 , jtf_terr_qual_all jtq
421 , jtf_qual_type_usgs jqtu
422 WHERE NVL(jt.end_date_active, lp_sysdate + 1) > lp_sysdate
423 AND NVL(jt.start_date_active, lp_sysdate - 1) < lp_sysdate
424 AND jtqu.terr_id = jt.terr_id
425 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
426 AND jqtu.qual_type_id = lp_qual_type_id
427 AND jtqu.terr_id = jtq.terr_id
428 AND jtq.qual_usg_id = jqu.qual_usg_id )
429 UNION
430
431 SELECT jqu.qual_usg_id, jqu.rule2
432 FROM jtf_qual_usgs_all jqu
433 , jtf_qual_type_usgs_all jqtu
434 , jtf_qual_type_denorm_v v
435 WHERE --jqu.enabled_flag = 'Y'
436 jqu.org_id = -3113
437 AND jqu.qual_type_usg_id = jqtu.qual_type_usg_id
438 AND jqtu.source_id = lp_source_id
439 AND jqtu.qual_type_id = v.related_id
440 AND jqu.rule2 IS NOT NULL
441 AND v.qual_type_id = lp_qual_type_id
442 AND EXISTS ( SELECT jtq.terr_id
443 FROM jtf_terr_values_all jtv
444 , jtf_terr_qtype_usgs_all jtqu
445 , jtf_terr_all jt
446 , jtf_terr_qual_all jtq
447 , jtf_qual_type_usgs jqtu
448 WHERE NVL(jt.end_date_active, lp_sysdate + 1) > lp_sysdate
449 AND NVL(jt.start_date_active, lp_sysdate - 1) < lp_sysdate
450 AND jtqu.terr_id = jt.terr_id
451 AND jtv.terr_qual_id = jtq.terr_qual_id
452 AND jtv.comparison_operator IN ('<>', 'NOT LIKE', 'NOT BETWEEN')
453 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
454 AND jqtu.qual_type_id = lp_qual_type_id
455 AND jtqu.terr_id = jtq.terr_id
456 AND jtq.qual_usg_id = jqu.qual_usg_id ) ;
457
458 l_qual_usg_id NUMBER;
459 l_qual_rules VARCHAR2(32767);
460 l_rule VARCHAR2(32767);
461 l_counter NUMBER := 1;
462 l_newline VARCHAR2(2);
463 l_indent VARCHAR2(30);
464 l_sysdate DATE;
465
466 BEGIN
467
468 --dbms_output.put_line('Inside build_rule_expression ');
469
470 If G_Debug Then
471 Write_Log(2, ' ');
472 Write_Log(2, 'INSIDE PROCEDURE JTF_TERR_ENGINE_GEN2_PVT.build_qualifier_rules:');
473 Write_Log(2, ' source_id - ' || TO_CHAR(p_Source_Id) );
474 Write_Log(2, ' qual_type_id - ' || TO_CHAR(p_qual_type_id) );
475 Write_Log(2, ' ');
476 End If;
477
478 l_newline := FND_GLOBAL.Local_Chr(10); /* newline character */
479 l_sysdate := SYSDATE;
480
481 OPEN c_terr_qual(p_source_id, p_qual_type_id, l_sysdate);
482
483 LOOP
484
485 FETCH c_terr_qual INTO l_qual_usg_id, l_rule;
486 EXIT WHEN c_terr_qual%NOTFOUND;
487
488
489 If G_Debug Then
490 Write_Log(2, ' ');
491 Write_Log(2, '/*----------------------------------------*/');
492 Write_Log(2, 'PACKAGE RULE #' || l_counter );
493 Write_Log(2, 'QUAL_USG_ID: ' || TO_CHAR(l_qual_usg_id) );
494 Write_Log(2, ' ');
495 Write_Log(2, 'RULE: ' ||l_rule );
496 Write_Log(2, ' ');
497 Write_Log(2, '/*----------------------------------------*/');
498 Write_Log(2, ' ');
499 End If;
500
501 --IF (l_counter > 1) THEN
502 -- l_qual_rules := l_newline || l_qual_rules || l_newline ||
503 -- ' UNION ALL ';
504 --END IF;
505
506 IF (l_counter = 1) THEN
507
508 l_qual_rules := l_newline || l_rule ;
509
510 ELSE
511
512 l_qual_rules := l_qual_rules || l_newline || l_newline ||
513 'UNION ALL ' || l_newline || l_newline || l_rule;
514
515 END IF;
516
517 l_counter := l_counter + 1;
518 END LOOP;
519
520 CLOSE c_terr_qual;
521
522 x_qual_rules := l_qual_rules;
523 --dbms_output.put_line('Leaving build_rule_expression ');
524
525 EXCEPTION
526 WHEN OTHERS THEN
527 g_ProgramStatus := 1;
528 Add_To_PackageTable ('-- Program encountered invalid territory ');
529
530 END build_qualifier_rules;
531
532
533
534 PROCEDURE gen_terr_rules_recurse (
535 p_terr_id IN NUMBER,
536 p_source_id IN NUMBER,
537 p_qualifier_type_id IN NUMBER,
538 p_qualifier_type IN VARCHAR2,
539 p_search_name IN VARCHAR2 := 'SEARCH_TERR_RULES'
540 )
541 AS
542 l_procedure_name VARCHAR2(30);
543 l_procedure_desc VARCHAR2(255);
544 l_parameter_list1 VARCHAR2(360);
545 l_parameter_list2 VARCHAR2(360);
546 l_qual_rules VARCHAR2(32767);
547
548 l_str_len NUMBER;
549 l_start NUMBER;
550 l_get_nchar NUMBER;
551 l_next_newline NUMBER;
552 l_rule_str VARCHAR2(256);
553 l_newline VARCHAR2(2) := FND_GLOBAL.Local_Chr(10); /* newline character */
554 l_indent VARCHAR2(30);
555
556
557 BEGIN
558 --dbms_output.put_line('gen_terr_rules_recurse.p_search_name: ' || p_search_name);
559
560 IF G_Debug THEN
561 Write_Log(1, 'INSIDE PROCEDURE JTF_TERR_ENGINE_GEN2_PVT.gen_terr_rules_recurse');
562 END IF;
563
564 --dbms_output.put_line('Value of p_qualifier_type_id='|| l_indent||TO_CHAR(p_qualifier_type_id));
565
566 Build_Qualifier_Rules(p_source_id, p_qualifier_type_id, l_qual_rules);
567
568 l_str_len := LENGTH(l_qual_rules);
569
570 --dbms_output.put_line('After Build_Qualifier_Rules');
571
572 l_procedure_name := p_search_name;
573 l_procedure_desc := '/* Territory rules for Usage/Transaction: ' ||
574 TO_CHAR(p_source_id) || ' / ' || p_qualifier_type || ' */';
575
576 IF ( p_source_id = -1001 AND
577 p_qualifier_type_id = -1002) THEN
578
579 IF (p_search_name = 'SEARCH_TERR_RULES') THEN
580
581 l_parameter_list1 :=
582 ' p_rec IN JTF_TERRITORY_PUB.jtf_' || p_qualifier_type || '_bulk_rec_type ' || l_newline ||
583 ' , x_rec OUT NOCOPY JTF_TERRITORY_PUB.Winning_Bulk_Rec_Type ' || l_newline ||
584 ' , p_top_level_terr_id IN NUMBER := FND_API.G_MISS_NUM ';
585
586 l_parameter_list2 := ' , p_num_winners IN NUMBER := FND_API.G_MISS_NUM ';
587
588 ELSIF (p_search_name = 'SEARCH_TERR_RULES_ALL') THEN
589
590 --
591 -- 03/27 JDOCHERT 11.5.4.0.2 code
592 -- x_rec is of different type that for SEARCH_TERR_RULES procedure above
593 --
594 l_parameter_list1 :=
595 ' p_rec IN JTF_TERRITORY_PUB.jtf_' || p_qualifier_type ||'_bulk_rec_type ' || l_newline ||
596 ' , x_rec OUT NOCOPY JTF_TERR_LOOKUP_PUB.win_rsc_tbl_type ' ;
597 END IF;
598
599 ELSIF ( p_source_id = -1001 AND
600 p_qualifier_type_id IN (-1003, -1004) ) THEN /* Opportunity + Lead */
601
602 l_parameter_list1 :=
603 ' p_rec IN JTF_TERRITORY_PUB.jtf_' || p_qualifier_type ||'_bulk_rec_type ' || l_newline ||
604 ' , x_rec OUT NOCOPY JTF_TERRITORY_PUB.Winning_Bulk_Rec_Type ';
605
606 ELSIF ( p_source_id = -1001 AND
607 p_qualifier_type_id IN (-1105) ) THEN /* Quote*/
608
609 l_parameter_list1 :=
610 ' p_rec IN JTF_TERRITORY_PUB.jtf_ACCOUNT_bulk_rec_type ' || l_newline ||
611 ' , x_rec OUT NOCOPY JTF_TERRITORY_PUB.Winning_Bulk_Rec_Type ';
612
613 ELSIF ( p_source_id IN (-1003, -1500, -1600, -1700) ) THEN
614
615 l_parameter_list1 :=
616 ' p_rec IN JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type ' || l_newline ||
617 ' , x_rec OUT NOCOPY JTF_TERR_ASSIGN_PUB.bulk_winners_rec_type ';
618
619 ELSE /* Defects/Service */
620
621 l_parameter_list1 :=
622 ' p_rec IN JTF_TERRITORY_PUB.jtf_bulk_trans_rec_type ' || l_newline ||
623 ' , x_rec OUT NOCOPY JTF_TERRITORY_PUB.Winning_Bulk_Rec_Type ';
624 END IF;
625
626 l_parameter_list2 := l_parameter_list2 || l_newline ||
627 ' , p_role IN VARCHAR2 := FND_API.G_MISS_CHAR ' || l_newline ||
628 ' , p_resource_type IN VARCHAR2 := FND_API.G_MISS_CHAR ';
629
630 IF (p_search_name = 'SEARCH_TERR_RULES_ALL') THEN
631 l_parameter_list2 := NULL;
632 END IF;
633
634 generate_object_definition( l_procedure_name, l_procedure_desc,
635 l_parameter_list1, l_parameter_list2, 'P', 'BOOLEAN', 'PKB' );
636
637 Add_To_PackageTable (' ');
638 Add_To_PackageTable (' l_return_status VARCHAR2(10);');
639 Add_To_PackageTable (' l_result_id NUMBER;');
640 Add_To_PackageTable (' l_sysdate DATE;');
641 --Add_To_PackageTable (' l_top_level_terr_id_lst jtf_terr_number_list := jtf_terr_number_list();');
642 --Add_To_PackageTable (' l_num_win_lst jtf_terr_number_list := jtf_terr_number_list();');
643 Add_To_PackageTable (' ');
644 Add_To_PackageTable (' lp_top_level_terr_id NUMBER;');
645 Add_To_PackageTable (' lp_num_winners NUMBER;');
646 Add_To_PackageTable (' lp_role VARCHAR2(60);');
647 Add_To_PackageTable (' lp_resource_type VARCHAR2(60);');
648 Add_To_PackageTable (' ');
649 Add_To_PackageTable (' lp_qual_type_id NUMBER;');
650 Add_To_PackageTable (' lp_source_id NUMBER;');
651 Add_To_PackageTable (' ');
652 Add_To_PackageTable (' l_cursor NUMBER;');
653 Add_To_PackageTable (' l_dyn_str VARCHAR2(32767);');
654 Add_To_PackageTable (' l_num_rows NUMBER;');
655 Add_To_PackageTable (' indx NUMBER := 1;');
656 Add_To_PackageTable (' lp_trans_id_tbl jtf_terr_number_list := jtf_terr_number_list();');
657 Add_To_PackageTable (' ');
658 Add_To_PackageTable (' l_result_id_arr DBMS_SQL.NUMBER_TABLE; ');
659 Add_To_PackageTable (' l_trans_id_arr DBMS_SQL.NUMBER_TABLE; ');
660 Add_To_PackageTable (' l_trans_object_id_arr DBMS_SQL.NUMBER_TABLE; ');
661 Add_To_PackageTable (' l_trans_object_detail_id_arr DBMS_SQL.NUMBER_TABLE; ');
662 Add_To_PackageTable (' l_absolute_rank_arr DBMS_SQL.NUMBER_TABLE; ');
663 Add_To_PackageTable (' l_terr_id_arr DBMS_SQL.NUMBER_TABLE; ');
664 Add_To_PackageTable (' l_top_level_terr_id_arr DBMS_SQL.NUMBER_TABLE; ');
665 Add_To_PackageTable (' l_num_winners_arr DBMS_SQL.NUMBER_TABLE; ');
666 Add_To_PackageTable (' l_terr_rsc_id_arr DBMS_SQL.NUMBER_TABLE; ');
667 Add_To_PackageTable (' l_resource_id_arr DBMS_SQL.NUMBER_TABLE; ');
668 Add_To_PackageTable (' l_resource_type_arr DBMS_SQL.VARCHAR2_TABLE; ');
669 Add_To_PackageTable (' l_group_id_arr DBMS_SQL.NUMBER_TABLE; ');
670 Add_To_PackageTable (' l_role_arr DBMS_SQL.VARCHAR2_TABLE; ');
671 Add_To_PackageTable (' l_full_access_flag_arr DBMS_SQL.VARCHAR2_TABLE; ');
672 Add_To_PackageTable (' l_primary_contact_flag_arr DBMS_SQL.VARCHAR2_TABLE; ');
673 Add_To_PackageTable (' ');
674 Add_To_PackageTable (' l_counter NUMBER; ');
675 Add_To_PackageTable (' ');
676
677 IF (p_search_name = 'SEARCH_TERR_RULES_ALL') THEN
678
679 Add_To_PackageTable (' CURSOR csr_get_rsc ( lp_terr_id NUMBER ');
680 Add_To_PackageTable (' , lp_sysdate DATE) IS ');
681 Add_To_PackageTable (' SELECT /*+ ORDERED ' );
682 Add_To_PackageTable (' INDEX (jtr JTF_TERR_RSC_N3) */ ' );
683 Add_To_PackageTable (' DISTINCT ' );
684 Add_To_PackageTable (' rsc.resource_name resource_name ' );
685 Add_To_PackageTable (' , rsc.source_job_title resource_job_title ' );
686 Add_To_PackageTable (' , rsc.source_phone resource_phone ' );
687 Add_To_PackageTable (' , rsc.source_email resource_email ' );
688 Add_To_PackageTable (' , rsc.source_mgr_name resource_mgr_name ' );
689 Add_To_PackageTable (' , mgr.source_phone resource_mgr_phone ' );
690 Add_To_PackageTable (' , mgr.source_email resource_mgr_email ' );
691 Add_To_PackageTable (' , lp_terr_id terr_id ' );
692 Add_To_PackageTable (' FROM ' );
693 Add_To_PackageTable (' jtf_terr_rsc_all jtr ' );
694 Add_To_PackageTable (' , jtf_terr_rsc_access_all jtra ' );
695 Add_To_PackageTable (' , jtf_rs_resource_extns_vl rsc ' );
696 Add_To_PackageTable (' , jtf_rs_resource_extns_vl mgr ' );
697 Add_To_PackageTable (' WHERE mgr.source_id (+) = rsc.source_mgr_id ' );
698 Add_To_PackageTable (' AND rsc.resource_id = jtr.resource_id ' );
699 Add_To_PackageTable (' AND DECODE( rsc.category ' );
700 Add_To_PackageTable (' , ''EMPLOYEE'', ''RS_EMPLOYEE'' ' );
701 Add_To_PackageTable (' , ''PARTNER'', ''RS_PARTNER'' ' );
702 Add_To_PackageTable (' , ''SUPPLIER_CONTACT'', ''RS_SUPPLIER'' ' );
703 Add_To_PackageTable (' , ''PARTY'', ''RS_PARTY'' ' );
704 Add_To_PackageTable (' , ''OTHER'', ''RS_OTHER'' ' );
705 Add_To_PackageTable (' , ''TBH'', ''RS_TBH'') = jtr.resource_type ' );
706 Add_To_PackageTable (' AND( jtra.ACCESS_TYPE IN (''ACCOUNT'', ''OPPOR'', ''LEAD'') OR jtra.ACCESS_TYPE IS NULL ) ' );
707 Add_To_PackageTable (' AND jtr.terr_rsc_id = jtra.terr_rsc_id (+) ' );
708 Add_To_PackageTable (' AND NVL(jtr.end_date_active, lp_sysdate+1) > lp_sysdate ');
709 Add_To_PackageTable (' AND NVL(jtr.start_date_active, lp_sysdate-1) < lp_sysdate ');
710 Add_To_PackageTable (' AND jtr.terr_id = lp_terr_id ; ' );
711 Add_To_PackageTable (' ');
712 Add_To_PackageTable (' l_rsc_counter NUMBER; ');
713
714 END IF;
715
716
717 Add_To_PackageTable ('BEGIN');
718 Add_To_PackageTable (' ');
719 Add_To_PackageTable (' SAVEPOINT JTF_TERR_ASSIGN_TRANSACTION; ');
720 Add_To_PackageTable (' ');
721
722
723 /* JDOCHERT: 07/18/03: bug#3020630 */
724 --
725 -- now in jtftrmvc.sql
726 --Add_To_PackageTable (' ');
727 --Add_To_PackageTable (' JTF_TERR_ASSIGN_PUB.create_matches_GT_tbls( ');
728 --Add_To_PackageTable (' p_source_id => ' || TO_CHAR(p_source_id) );
729 --Add_To_PackageTable (' , p_trans_object_type_id => ' || TO_CHAR(p_qualifier_type_id) );
730 --Add_To_PackageTable (' , x_return_status => l_return_status ' );
731 --Add_To_PackageTable (' ); ');
732 --Add_To_PackageTable (' ');
733 --
734
735 /* ARPATEL: 01/20/04: bug#3348954 */
736 Add_To_PackageTable (' DELETE FROM jtf_terr_results_GT_MT; ');
737 Add_To_PackageTable (' ');
738
739 IF ( p_source_id = -1001 AND
740 p_qualifier_type_id IN (-1002, -1003, -1004) ) THEN
741 Add_To_PackageTable (' FOR j IN p_rec.party_id.FIRST..p_rec.party_id.LAST LOOP ');
742 ELSE
743 Add_To_PackageTable (' FOR j IN p_rec.trans_object_id.FIRST..p_rec.trans_object_id.LAST LOOP ');
744 END IF;
745
746 Add_To_PackageTable (' lp_trans_id_tbl.EXTEND;');
747 Add_To_PackageTable (' lp_trans_id_tbl(j) := j;');
748 Add_To_PackageTable (' END LOOP;');
749 Add_To_PackageTable (' ');
750
751
752 --
753 --Add_To_PackageTable (' SELECT JTF_TERR_RESULTS_S.NEXTVAL ');
754 --Add_To_PackageTable (' INTO l_result_id ');
755 --Add_To_PackageTable (' FROM dual;');
756 --Add_To_PackageTable (' ');
757 --Add_To_PackageTable (' --dbms_output.put_line(''Value of l_result_id=''||TO_CHAR(l_result_id)); ');
758 --
759
760 Add_To_PackageTable (' ');
761 Add_To_PackageTable (' l_sysdate := SYSDATE;');
762 Add_To_PackageTable (' lp_qual_type_id := ' || TO_CHAR(p_qualifier_type_id) || ';');
763 Add_To_PackageTable (' lp_source_id := ' || TO_CHAR(p_source_id) || ';');
764 Add_To_PackageTable (' ');
765
766 IF ( l_str_len > 0 ) THEN
767
768 IF ( p_search_name = 'SEARCH_TERR_RULES' ) THEN
769
770
771 IF (p_source_id = -1001 AND p_qualifier_type_id = -1002) THEN
772
773 Add_To_PackageTable (' lp_top_level_terr_id := p_top_level_terr_id; ');
774 Add_To_PackageTable (' lp_num_winners := p_num_winners; ');
775 Add_To_PackageTable (' ');
776 Add_To_PackageTable (' IF ( lp_top_level_terr_id = FND_API.G_MISS_NUM ) THEN ');
777 Add_To_PackageTable (' lp_top_level_terr_id := NULL; ');
778 Add_To_PackageTable (' lp_num_winners := NULL; ');
779 Add_To_PackageTable (' END IF; ');
780 Add_To_PackageTable (' ');
781
782 END IF;
783
784 Add_To_PackageTable (' lp_role := p_role; ');
785 Add_To_PackageTable (' ');
786 Add_To_PackageTable (' IF ( lp_role = FND_API.G_MISS_CHAR ) THEN ');
787 Add_To_PackageTable (' lp_role := NULL; ');
788 Add_To_PackageTable (' END IF; ');
789 Add_To_PackageTable (' ');
790 Add_To_PackageTable (' lp_resource_type := p_resource_type; ');
791 Add_To_PackageTable (' ');
792 Add_To_PackageTable (' IF ( lp_resource_type = FND_API.G_MISS_CHAR ) THEN ');
793 Add_To_PackageTable (' lp_resource_type := NULL; ');
794 Add_To_PackageTable (' END IF; ');
795 Add_To_PackageTable (' ');
796
797
798 END IF; /* ( p_search_name = 'SEARCH_TERR_RULES' ) */
799
800 Add_To_PackageTable (' ');
801
802
803 -- JDOCHERT - 05/03/01
804 IF ( p_source_id = -1001 AND
805 p_qualifier_type_id IN (-1002, -1003, -1004) ) THEN
806 -- JDOCHERT - 06/05/01
807 Add_To_PackageTable (' FORALL i IN lp_trans_id_tbl.FIRST..lp_trans_id_tbl.LAST ');
808 ELSE
809 Add_To_PackageTable (' FORALL i IN p_rec.trans_object_id.FIRST..p_rec.trans_object_id.LAST ');
810 END IF;
811
812 Add_To_PackageTable (' ');
813 Add_To_PackageTable (' INSERT INTO jtf_terr_results_GT_MT jtr ');
814 Add_To_PackageTable (' ( ');
815 Add_To_PackageTable (' result_id');
816 Add_To_PackageTable (' , trans_id');
817 Add_To_PackageTable (' , trans_object_id');
818 Add_To_PackageTable (' , trans_detail_object_id');
819 Add_To_PackageTable (' , terr_id');
820 Add_To_PackageTable (' , absolute_rank');
821 Add_To_PackageTable (' , top_level_terr_id');
822 Add_To_PackageTable (' , worker_id');
823
824 --
825 -- JDOCHERT: 08/03/03: NUM WINNERS NOT NEEDED
826 -- BUG#3020630
827 --Add_To_PackageTable (' , num_winners');
828 --
829
830 Add_To_PackageTable (' ) ');
831 Add_To_PackageTable (' SELECT /*+ ORDERED */');
832 Add_To_PackageTable (' l_result_id');
833 Add_To_PackageTable (' , lp_trans_id_tbl(i)');
834
835 IF ( p_source_id = -1001 ) THEN
836
837 IF (p_qualifier_type_id = -1002) THEN
838
839 Add_To_PackageTable (' , p_rec.party_id(i) ');
840 Add_To_PackageTable (' , p_rec.party_site_id(i) ');
841
842 ELSIF (p_qualifier_type_id = -1003) THEN
843
844 Add_To_PackageTable (' , p_rec.sales_lead_id(i) ');
845 Add_To_PackageTable (' , p_rec.sales_lead_line_id(i) ');
846
847 ELSIF (p_qualifier_type_id = -1004) THEN
848
849 Add_To_PackageTable (' , p_rec.lead_id(i) ');
850 Add_To_PackageTable (' , p_rec.lead_line_id(i) ');
851
852 ELSIF (p_qualifier_type_id = -1105) THEN
853 Add_To_PackageTable (' , p_rec.trans_object_id(i) ');
854 Add_To_PackageTable (' , 1 TRANS_DETAIL_OBJECT_ID ');
855 END IF;
856
857 ELSE
858 Add_To_PackageTable (' , p_rec.trans_object_id(i) ');
859 Add_To_PackageTable (' , p_rec.trans_detail_object_id(i) ');
860 END IF;
861
862 Add_To_PackageTable (' , terr_id ');
863 Add_To_PackageTable (' , absolute_rank ');
864 Add_To_PackageTable (' , top_level_terr_id');
865 Add_To_PackageTable (' , 1 WORKER_ID');
866
867 --
868 -- JDOCHERT: 08/03/03: NUM WINNERS NOT NEEDED
869 -- BUG#3020630
870 --Add_To_PackageTable (' , num_winners');
871 --
872
873 Add_To_PackageTable (' FROM ');
874 Add_To_PackageTable (' ( /* START OF DYNAMIC PART */ ');
875
876
877 --
878 --IF ( p_search_name = 'SEARCH_TERR_RULES' ) THEN
879 --
880 -- Add_To_PackageTable (' /* START OF INLINE VIEW# OUTER */ ');
881 -- Add_To_PackageTable (' ( SELECT ILV.terr_id terr_id, ILV.absolute_rank absolute_rank, ILV.top_level_terr_id ');
882 -- Add_To_PackageTable (' FROM ( /* START OF DYNAMIC PART */ ');
883 --
884 --ELSIF ( p_search_name = 'SEARCH_TERR_RULES_ALL' ) THEN
885 --
886 -- Add_To_PackageTable (' ( /* START OF DYNAMIC PART */ ');
887 --
888 --END IF;
889 --
890
891 l_newline := FND_GLOBAL.Local_Chr(10); /* newline character */
892 l_indent := ' ';
893 l_start := 1;
894 l_next_newline := 0;
895
896
897 --dbms_output.put_line('BEFORE LOOP: Value of l_next_newline='||TO_CHAR(l_next_newline));
898 --dbms_output.put_line('Value of LENGTH(l_qual_rules)= ' || TO_CHAR(l_str_len) );
899
900 WHILE (TRUE) LOOP
901
902 l_next_newline := INSTR(l_qual_rules, l_newline, l_start, 1);
903
904 IF (l_next_newline = 0) THEN
905 /* no new line characters => end of string */
906 l_get_nchar := l_str_len;
907 ELSE
908 /* set of characters up to next newline */
909 l_get_nchar := l_next_newline - l_start;
910 END IF;
911
912
913 --dbms_output.put_line('Value of lstart='|| TO_CHAR(l_start) || ' l_next_newline='||TO_CHAR(l_next_newline) || ' l_get_nchar='||TO_CHAR(l_get_nchar));
914
915 l_rule_str := substr(l_qual_rules, l_start, l_get_nchar);
916
917 --dbms_output.put_line(l_rule_str);
918 Add_To_PackageTable(l_indent || l_rule_str);
919
920 EXIT WHEN l_next_newline = 0;
921
922 l_rule_str := NULL;
923 l_start := l_next_newline + 1;
924
925 END LOOP;
926
927
928 Add_To_PackageTable (' ');
929 Add_To_PackageTable (' /* END OF DYNAMIC PART */ ) ILV ');
930
931 --
932 -- JDOCHERT: 08/03/03: NUM WINNERS NOT NEEDED
933 -- BUG#3020630
934 --Add_To_PackageTable (' GROUP BY ILV.terr_id, ilv.absolute_rank, ilv.top_level_terr_id, ilv.num_winners ');
935 --
936 Add_To_PackageTable (' GROUP BY ILV.terr_id, ilv.absolute_rank, ilv.top_level_terr_id ');
937 --
938
939 /* ARPATEL: 12/03/2003 for Oracle Sales we now use num_qual in jtf_terr_qtype_usgs_all */
940 IF p_source_id = -1001
941 THEN
942 Add_To_PackageTable (' HAVING (ILV.terr_id, COUNT(*)) IN ( ');
943 Add_To_PackageTable (' SELECT ');
944 Add_To_PackageTable (' jtw.terr_id ');
945 Add_To_PackageTable (' , jua.num_qual ' );
946 Add_To_PackageTable (' FROM jtf_terr_denorm_rules_all jtw ');
947 Add_To_PackageTable (' ,jtf_terr_qtype_usgs_all jua ');
948 Add_To_PackageTable (' ,jtf_qual_type_usgs_all jqa ');
949 Add_To_PackageTable (' WHERE jtw.source_id = lp_source_id');
950 Add_To_PackageTable (' AND jqa.source_id = jtw.source_id ');
951 Add_To_PackageTable (' AND jqa.qual_type_id = lp_qual_type_id ');
952 Add_To_PackageTable (' AND jtw.resource_exists_flag = ''Y'' ');
953 Add_To_PackageTable (' AND jtw.terr_id = jua.terr_id ');
954 Add_To_PackageTable (' AND jua.qual_type_usg_id = jqa.qual_type_usg_id ');
955 Add_To_PackageTable (' AND jtw.related_terr_id = ilv.terr_id ');
956 Add_To_PackageTable (' AND jtw.terr_id = ilv.terr_id ); ');
957 Add_To_PackageTable (' ');
958 Add_To_PackageTable (' ');
959 ELSE
960 Add_To_PackageTable (' HAVING (ILV.terr_id, COUNT(*)) IN ( ');
961 Add_To_PackageTable (' SELECT ');
962 Add_To_PackageTable (' jtw.terr_id ');
963 Add_To_PackageTable (' , jtw.num_qual ' );
964 Add_To_PackageTable (' FROM jtf_terr_denorm_rules_all jtw ');
965 Add_To_PackageTable (' WHERE jtw.source_id = lp_source_id');
966 Add_To_PackageTable (' AND jtw.qual_type_id = lp_qual_type_id ');
967 Add_To_PackageTable (' AND jtw.resource_exists_flag = ''Y'' ');
968 Add_To_PackageTable (' AND jtw.related_terr_id = ilv.terr_id ');
969 Add_To_PackageTable (' AND jtw.terr_id = ilv.terr_id ); ');
970 Add_To_PackageTable (' ');
971 Add_To_PackageTable (' ');
972 END IF;
973
974
975 IF ( p_search_name = 'SEARCH_TERR_RULES' AND
976 p_source_id = -1001 ) THEN
977
978
979 Add_To_PackageTable (' ');
980 Add_To_PackageTable (' JTF_TERR_ASSIGN_PUB.get_winning_resources ( ');
981 Add_To_PackageTable (' p_source_id => lp_source_id, ');
982 Add_To_PackageTable (' p_trans_object_type_id => lp_qual_type_id, ');
983 Add_To_PackageTable (' x_return_status => l_Return_Status, ');
984 Add_To_PackageTable (' x_winners_rec => x_rec ); ');
985 Add_To_PackageTable (' ');
986
987 ELSIF ( p_source_id <> -1001 AND
988 p_search_name = 'SEARCH_TERR_RULES' ) THEN
989
990
991 Add_To_PackageTable (' l_cursor := DBMS_SQL.OPEN_CURSOR; ');
992 Add_To_PackageTable (' ');
993 Add_To_PackageTable (' l_dyn_str :=');
994 Add_To_PackageTable (' '' SELECT /*+ ORDERED INDEX (jtra JTF_TERR_RSC_N1) '' || ' );
995 Add_To_PackageTable (' '' INDEX (jtraa JTF_TERR_RSC_ACCESS_N1)*/ '' || ' );
996 Add_To_PackageTable (' '' DISTINCT '' || ' );
997 Add_To_PackageTable (' '' WT.result_id '' || ' );
998 Add_To_PackageTable (' '' , WT.trans_id '' || ' );
999 Add_To_PackageTable (' '' , WT.trans_object_id '' || ' );
1000 Add_To_PackageTable (' '' , WT.trans_detail_object_id '' || ' );
1001 Add_To_PackageTable (' '' , WT.terr_id '' || ' );
1002 Add_To_PackageTable (' '' , WT.absolute_rank '' || ' );
1003 Add_To_PackageTable (' '' , jtra.terr_rsc_id '' || ' );
1004 Add_To_PackageTable (' '' , jtra.resource_id '' || ' );
1005 Add_To_PackageTable (' '' , jtra.resource_type '' || ' );
1006 Add_To_PackageTable (' '' , jtra.group_id'' || ' );
1007 Add_To_PackageTable (' '' , jtra.role '' || ' );
1008 Add_To_PackageTable (' '' , jtra.full_access_flag '' || ' );
1009 Add_To_PackageTable (' '' , jtra.primary_contact_flag '' || ' );
1010 Add_To_PackageTable (' '' FROM '' || ' );
1011
1012 Add_To_PackageTable (' '' ( SELECT '' || ' );
1013 Add_To_PackageTable (' '' o.result_id '' || ' );
1014 Add_To_PackageTable (' '' , o.trans_id '' || ' );
1015 Add_To_PackageTable (' '' , o.trans_object_id '' || ' );
1016 Add_To_PackageTable (' '' , o.trans_detail_object_id '' || ' );
1017 Add_To_PackageTable (' '' , o.terr_id '' || ' );
1018 Add_To_PackageTable (' '' , o.absolute_rank '' || ' );
1019 Add_To_PackageTable (' '' FROM '' || ' );
1020 Add_To_PackageTable (' '' ( SELECT '' || ' );
1021 Add_To_PackageTable (' '' i.result_id '' || ' );
1022 Add_To_PackageTable (' '' , i.trans_id '' || ' );
1023 Add_To_PackageTable (' '' , i.trans_object_id '' || ' );
1024 Add_To_PackageTable (' '' , i.trans_detail_object_id '' || ' );
1025 Add_To_PackageTable (' '' , i.terr_id '' || ' );
1026 Add_To_PackageTable (' '' , i.absolute_rank '' || ' );
1027 Add_To_PackageTable (' '' , i.top_level_terr_id '' || ' );
1028
1029 --
1030 -- JDOCHERT: 08/03/03: NUM WINNERS NOT NEEDED
1031 -- BUG#3020630
1032 --Add_To_PackageTable (' '' , NVL(i.num_winners, 1) num_winners '' || ' );
1033 --
1034
1035 Add_To_PackageTable (' '' , RANK() OVER ( PARTITION BY '' || ' );
1036 Add_To_PackageTable (' '' i.trans_id '' || ' );
1037 Add_To_PackageTable (' '' , i.trans_object_id '' || ' );
1038 Add_To_PackageTable (' '' , i.trans_detail_object_id '' || ' );
1039 Add_To_PackageTable (' '' , i.top_level_terr_id '' || ' );
1040 Add_To_PackageTable (' '' ORDER BY i.absolute_rank DESC, i.terr_id) AS TERR_RANK '' || ' );
1041 Add_To_PackageTable (' '' FROM jtf_terr_results_GT_MT i '' || ' );
1042
1043 --
1044 -- Not required now that Global Temporary Table is being used
1045 --Add_To_PackageTable (' '' WHERE i.result_id = :b1.result_id '' || ' );
1046 --
1047 --
1048
1049 Add_To_PackageTable (' '' ) o '' || ' );
1050 Add_To_PackageTable (' '' WHERE o.TERR_RANK <= (SELECT NVL(t.num_winners, 1) FROM jtf_terr_all t WHERE t.terr_id = o.top_level_terr_id) '' || ' );
1051 Add_To_PackageTable (' '' ) WT '' || ' );
1052 Add_To_PackageTable (' '' , jtf_terr_rsc_all jtra '' || ' );
1053 Add_To_PackageTable (' '' , jtf_terr_rsc_access_all jtraa '' || ' );
1054 Add_To_PackageTable (' '' WHERE '' || ' );
1055
1056 IF (p_qualifier_type_id = -1002) THEN
1057
1058 Add_To_PackageTable (' '' ( jtraa.ACCESS_TYPE = ''''ACCOUNT'''' )'' || ' );
1059
1060 ELSIF (p_qualifier_type_id = -1003) THEN
1061
1062 Add_To_PackageTable (' '' ( jtraa.ACCESS_TYPE = ''''LEAD'''' )'' || ' );
1063
1064 ELSIF (p_qualifier_type_id = -1004) THEN
1065
1066 Add_To_PackageTable (' '' ( jtraa.ACCESS_TYPE = ''''OPPOR'''' )'' || ' );
1067
1068 ELSIF (p_qualifier_type_id = -1105) THEN
1069
1070 Add_To_PackageTable (' '' ( jtraa.ACCESS_TYPE = ''''QUOTE'''' )'' || ' );
1071
1072 ELSIF (p_qualifier_type_id = -1005) THEN
1073
1074 /* Service: Service Request */
1075 Add_To_PackageTable (' '' ( jtraa.ACCESS_TYPE IN (''''SERV_REQ'''', ''''ACCOUNT'''') OR jtraa.ACCESS_TYPE IS NULL )'' || ' );
1076
1077 ELSIF (p_qualifier_type_id = -1006) THEN
1078
1079 /* Service: Task */
1080 Add_To_PackageTable (' '' ( jtraa.ACCESS_TYPE = ''''TASK'''' OR jtraa.ACCESS_TYPE IS NULL )'' || ' );
1081
1082 ELSIF (p_qualifier_type_id = -1009) THEN
1083
1084 /* Service: Service Request and Task */
1085 Add_To_PackageTable (' '' ( jtraa.ACCESS_TYPE = ''''SRV_TASK'''' OR jtraa.ACCESS_TYPE IS NULL )'' || ' );
1086
1087 ELSIF (p_qualifier_type_id = -1010) THEN
1088
1089 /* Defect Management: Defect or Enhancement */
1090 Add_To_PackageTable (' '' ( jtraa.ACCESS_TYPE = ''''DEF_MGMT'''' OR jtraa.ACCESS_TYPE IS NULL )'' || ' );
1091
1092 ELSIF (p_qualifier_type_id = -1501) THEN
1093
1094 /* Service: Contract Renewal */
1095 Add_To_PackageTable (' '' ( jtraa.ACCESS_TYPE = ''''KREN'''' OR jtraa.ACCESS_TYPE IS NULL )'' || ' );
1096
1097 ELSIF (p_qualifier_type_id = -1601) THEN
1098
1099 /* Collections: Delinquency */
1100 Add_To_PackageTable (' '' ( jtraa.ACCESS_TYPE = ''''DELQCY'''' OR jtraa.ACCESS_TYPE IS NULL )'' || ' );
1101
1102 ELSIF (p_qualifier_type_id = -1007) THEN
1103
1104 /* Trade Management: Offer */
1105 Add_To_PackageTable (' '' ( jtraa.ACCESS_TYPE = ''''OFFER'''' OR jtraa.ACCESS_TYPE IS NULL )'' || ' );
1106
1107 ELSIF (p_qualifier_type_id = -1302) THEN
1108
1109 /* Trade Management: Claim */
1110 Add_To_PackageTable (' '' ( jtraa.ACCESS_TYPE = ''''CLAIM'''' OR jtraa.ACCESS_TYPE IS NULL )'' || ' );
1111
1112 -- ARPATEL bug#3146288 change -1700 to -1701
1113 ELSIF (p_qualifier_type_id = -1701) THEN
1114
1115 /* Partner Management: Partner */
1116 Add_To_PackageTable (' '' ( jtraa.ACCESS_TYPE = ''''PARTNER'''' OR jtraa.ACCESS_TYPE IS NULL )'' || ' );
1117
1118 END IF;
1119
1120 Add_To_PackageTable (' '' AND jtra.terr_rsc_id = jtraa.terr_rsc_id (+) '' || ' );
1121 Add_To_PackageTable (' '' AND ( ( jtra.end_date_active IS NULL OR jtra.end_date_active >= :b1_sysdate ) AND '' || ' );
1122 Add_To_PackageTable (' '' ( jtra.start_date_active IS NULL OR jtra.start_date_active <= :b2_sysdate ) '' || ' );
1123 Add_To_PackageTable (' '' ) '' || ' );
1124 Add_To_PackageTable (' '' AND ( ( :b1_role IS NULL ) OR ( jtra.role = :b2_role ) )'' || ' );
1125 Add_To_PackageTable (' '' AND ( ( :b1_resource_type IS NULL) OR ( jtra.resource_type = :b2_resource_type ) )'' || ' );
1126 Add_To_PackageTable (' '' AND jtra.terr_id = WT.TERR_ID ''; ' );
1127 Add_To_PackageTable (' ');
1128 Add_To_PackageTable (' ');
1129 Add_To_PackageTable (' DBMS_SQL.PARSE ( l_Cursor, l_dyn_str, DBMS_SQL.NATIVE ); ');
1130 Add_To_PackageTable (' ');
1131 Add_To_PackageTable (' /* bind all input variables */ ');
1132
1133 --
1134 -- Not required now that Global Temporary Table is being used
1135 --Add_To_PackageTable (' DBMS_SQL.BIND_VARIABLE (l_cursor, '':b1_result_id'', l_result_id); ');
1136 --
1137
1138 Add_To_PackageTable (' DBMS_SQL.BIND_VARIABLE (l_cursor, '':b1_sysdate'', l_sysdate, 32767); ');
1139 Add_To_PackageTable (' DBMS_SQL.BIND_VARIABLE (l_cursor, '':b2_sysdate'', l_sysdate, 32767); ');
1140 Add_To_PackageTable (' DBMS_SQL.BIND_VARIABLE (l_cursor, '':b1_role'', lp_role, 32767 ); ');
1141 Add_To_PackageTable (' DBMS_SQL.BIND_VARIABLE (l_cursor, '':b2_role'', lp_role, 32767 ); ');
1142 Add_To_PackageTable (' DBMS_SQL.BIND_VARIABLE (l_cursor, '':b1_resource_type'', lp_resource_type, 32767 ); ');
1143 Add_To_PackageTable (' DBMS_SQL.BIND_VARIABLE (l_cursor, '':b2_resource_type'', lp_resource_type, 32767 ); ');
1144
1145 -- 07/17/03 JDOCHERT: FOLLOWING IS NOW OBSOLETE
1146 -- DUES TO MULTI-LEVEL WINNERS PROCESSING
1147 -- FOR ORACLE SALES BUG#3020630
1148 --
1149 --IF (p_source_id = -1001 AND p_qualifier_type_id = -1002) THEN
1150 --
1151 -- Add_To_PackageTable (' DBMS_SQL.BIND_VARIABLE (l_cursor, '':b1_num_winners'', lp_num_winners ); ');
1152 -- Add_To_PackageTable (' DBMS_SQL.BIND_VARIABLE (l_cursor, '':b2_num_winners'', lp_num_winners ); ');
1153 -- Add_To_PackageTable (' DBMS_SQL.BIND_VARIABLE (l_cursor, '':b1_top_level_terr_id'', lp_top_level_terr_id ); ');
1154 -- Add_To_PackageTable (' DBMS_SQL.BIND_VARIABLE (l_cursor, '':b2_top_level_terr_id'', lp_top_level_terr_id ); ');
1155 --
1156 --END IF;
1157 --
1158
1159 Add_To_PackageTable (' ');
1160 Add_To_PackageTable (' /* bind all output variables */ ');
1161 Add_To_PackageTable (' DBMS_SQL.DEFINE_ARRAY(l_cursor, 1, l_result_id_arr, 32767, indx ); ');
1162 Add_To_PackageTable (' DBMS_SQL.DEFINE_ARRAY(l_cursor, 2, l_trans_id_arr, 32767, indx ); ');
1163 Add_To_PackageTable (' DBMS_SQL.DEFINE_ARRAY(l_cursor, 3, l_trans_object_id_arr, 32767, indx ); ');
1164 Add_To_PackageTable (' DBMS_SQL.DEFINE_ARRAY(l_cursor, 4, l_trans_object_detail_id_arr, 32767, indx ); ');
1165 Add_To_PackageTable (' DBMS_SQL.DEFINE_ARRAY(l_cursor, 5, l_terr_id_arr, 32767, indx ); ');
1166 Add_To_PackageTable (' DBMS_SQL.DEFINE_ARRAY(l_cursor, 6, l_absolute_rank_arr, 32767, indx ); ');
1167 Add_To_PackageTable (' DBMS_SQL.DEFINE_ARRAY(l_cursor, 7, l_terr_rsc_id_arr, 32767, indx ); ');
1168 Add_To_PackageTable (' DBMS_SQL.DEFINE_ARRAY(l_cursor, 8, l_resource_id_arr, 32767, indx ); ');
1169 Add_To_PackageTable (' DBMS_SQL.DEFINE_ARRAY(l_cursor, 9, l_resource_type_arr, 32767, indx ); ');
1170 Add_To_PackageTable (' DBMS_SQL.DEFINE_ARRAY(l_cursor, 10, l_group_id_arr, 32767, indx ); ');
1171 Add_To_PackageTable (' DBMS_SQL.DEFINE_ARRAY(l_cursor, 11, l_role_arr, 32767, indx ); ');
1172 Add_To_PackageTable (' DBMS_SQL.DEFINE_ARRAY(l_cursor, 12, l_full_access_flag_arr, 32767, indx ); ');
1173 Add_To_PackageTable (' DBMS_SQL.DEFINE_ARRAY(l_cursor, 13, l_primary_contact_flag_arr, 32767, indx ); ');
1174 Add_To_PackageTable (' ');
1175 Add_To_PackageTable (' /* Execute the procedure call */ ');
1176 Add_To_PackageTable (' l_num_rows := DBMS_SQL.EXECUTE_AND_FETCH( l_cursor ); ');
1177 Add_To_PackageTable (' ');
1178 Add_To_PackageTable (' DBMS_SQL.COLUMN_VALUE (l_cursor, 1, l_result_id_arr); ');
1179 Add_To_PackageTable (' DBMS_SQL.COLUMN_VALUE (l_cursor, 2, l_trans_id_arr); ');
1180 Add_To_PackageTable (' DBMS_SQL.COLUMN_VALUE (l_cursor, 3, l_trans_object_id_arr); ');
1181 Add_To_PackageTable (' DBMS_SQL.COLUMN_VALUE (l_cursor, 4, l_trans_object_detail_id_arr); ');
1182 Add_To_PackageTable (' DBMS_SQL.COLUMN_VALUE (l_cursor, 5, l_terr_id_arr); ');
1183 Add_To_PackageTable (' DBMS_SQL.COLUMN_VALUE (l_cursor, 6, l_absolute_rank_arr); ');
1184 Add_To_PackageTable (' DBMS_SQL.COLUMN_VALUE (l_cursor, 7, l_terr_rsc_id_arr); ');
1185 Add_To_PackageTable (' DBMS_SQL.COLUMN_VALUE (l_cursor, 8, l_resource_id_arr); ');
1186 Add_To_PackageTable (' DBMS_SQL.COLUMN_VALUE (l_cursor, 9, l_resource_type_arr); ');
1187 Add_To_PackageTable (' DBMS_SQL.COLUMN_VALUE (l_cursor, 10, l_group_id_arr); ');
1188 Add_To_PackageTable (' DBMS_SQL.COLUMN_VALUE (l_cursor, 11, l_role_arr); ');
1189 Add_To_PackageTable (' DBMS_SQL.COLUMN_VALUE (l_cursor, 12, l_full_access_flag_arr); ');
1190 Add_To_PackageTable (' DBMS_SQL.COLUMN_VALUE (l_cursor, 13, l_primary_contact_flag_arr); ');
1191 Add_To_PackageTable (' ');
1192 Add_To_PackageTable (' DBMS_SQL.CLOSE_CURSOR(l_cursor); ');
1193 Add_To_PackageTable (' ');
1194 Add_To_PackageTable (' l_counter := l_terr_id_arr.FIRST; ');
1195 Add_To_PackageTable (' ');
1196 Add_To_PackageTable (' WHILE (l_counter <= l_terr_id_arr.last) LOOP ');
1197 Add_To_PackageTable (' ');
1198 Add_To_PackageTable (' --dbms_output.put_line( ''Winning Terr: '' || l_terr_id_arr(l_counter) ); ');
1199 Add_To_PackageTable (' ');
1200
1201 IF (p_source_id = -1001) THEN
1202
1203 Add_To_PackageTable (' x_rec.PARTY_ID.EXTEND; ');
1204 Add_To_PackageTable (' x_rec.PARTY_SITE_ID.EXTEND; ');
1205
1206 END IF;
1207
1208 Add_To_PackageTable (' x_rec.TRANS_OBJECT_ID.EXTEND; ');
1209 Add_To_PackageTable (' x_rec.TRANS_DETAIL_OBJECT_ID.EXTEND; ');
1210 Add_To_PackageTable (' x_rec.ABSOLUTE_RANK.EXTEND; ');
1211 Add_To_PackageTable (' x_rec.terr_id.EXTEND; ');
1212 Add_To_PackageTable (' x_rec.terr_rsc_id.EXTEND; ');
1213 Add_To_PackageTable (' x_rec.resource_id.EXTEND; ');
1214 Add_To_PackageTable (' x_rec.resource_type.EXTEND; ');
1215 Add_To_PackageTable (' x_rec.group_id.EXTEND; ');
1216 Add_To_PackageTable (' x_rec.role.EXTEND; ');
1217 Add_To_PackageTable (' x_rec.full_access_flag.EXTEND; ');
1218 Add_To_PackageTable (' x_rec.primary_contact_flag.EXTEND; ');
1219 Add_To_PackageTable (' ');
1220
1221 IF (p_source_id = -1001) THEN
1222
1223 /* For Oracle Sales/Accounts */
1224 Add_To_PackageTable (' x_rec.PARTY_ID(l_counter) := l_trans_object_id_arr(l_counter); ');
1225 Add_To_PackageTable (' x_rec.PARTY_SITE_ID(l_counter) := l_trans_object_detail_id_arr(l_counter); ');
1226
1227 END IF;
1228
1229 /* All other transactions */
1230 Add_To_PackageTable (' x_rec.TRANS_OBJECT_ID(l_counter) := l_trans_object_id_arr(l_counter); ');
1231 Add_To_PackageTable (' x_rec.TRANS_DETAIL_OBJECT_ID(l_counter) := l_trans_object_detail_id_arr(l_counter); ');
1232 Add_To_PackageTable (' x_rec.ABSOLUTE_RANK(l_counter) := l_absolute_rank_arr(l_counter); ');
1233 Add_To_PackageTable (' x_rec.terr_id(l_counter) := l_terr_id_arr(l_counter); ');
1234 Add_To_PackageTable (' x_rec.terr_rsc_id(l_counter) := l_terr_rsc_id_arr(l_counter); ');
1235 Add_To_PackageTable (' x_rec.resource_id(l_counter) := l_resource_id_arr(l_counter); ');
1236 Add_To_PackageTable (' x_rec.resource_type(l_counter) := l_resource_type_arr(l_counter); ');
1237 Add_To_PackageTable (' x_rec.group_id(l_counter) := l_group_id_arr(l_counter); ');
1238 Add_To_PackageTable (' x_rec.role(l_counter) := l_role_arr(l_counter); ');
1239 Add_To_PackageTable (' x_rec.full_access_flag(l_counter) := l_full_access_flag_arr(l_counter); ');
1240 Add_To_PackageTable (' x_rec.primary_contact_flag(l_counter) := l_primary_contact_flag_arr(l_counter); ');
1241 Add_To_PackageTable (' ');
1242 Add_To_PackageTable (' l_counter := l_counter + 1; ');
1243 Add_To_PackageTable (' ');
1244 Add_To_PackageTable (' END LOOP; ');
1245
1246
1247 ELSIF ( p_search_name = 'SEARCH_TERR_RULES_ALL' ) THEN
1248
1249 --
1250 --Add_To_PackageTable (' AND jtw.related_terr_id = ilv.terr_id ');
1251 --Add_To_PackageTable (' AND jtw.terr_id = ilv.terr_id ) ; ');
1252 --
1253 Add_To_PackageTable (' l_cursor := DBMS_SQL.OPEN_CURSOR; ');
1254 Add_To_PackageTable (' ');
1255 Add_To_PackageTable (' l_dyn_str :=');
1256 Add_To_PackageTable (' '' SELECT column_value terr_id '' || ' );
1257 Add_To_PackageTable (' '' FROM '' || ' );
1258 Add_To_PackageTable (' '' jtf_terr_results jtr '' || ' );
1259 Add_To_PackageTable (' '' , TABLE ( CAST ( MULTISET ( SELECT ilv1.terr_id '' || ' );
1260 Add_To_PackageTable (' '' FROM ( SELECT /*+ INDEX (j JTF_TERR_RESULTS_U1) */ '' || ' );
1261 Add_To_PackageTable (' '' j.terr_id '' || ' );
1262 Add_To_PackageTable (' '' , j.top_level_terr_id '' || ' );
1263 Add_To_PackageTable (' '' , j.absolute_rank '' || ' );
1264 Add_To_PackageTable (' '' , j.trans_id '' || ' );
1265 Add_To_PackageTable (' '' FROM jtf_terr_results j '' || ' );
1266 Add_To_PackageTable (' '' WHERE j.result_id = :b1_result_id '' || ' );
1267 Add_To_PackageTable (' '' ORDER BY j.trans_id, j.top_level_terr_id, j.absolute_rank DESC '' || ' );
1268 Add_To_PackageTable (' '' ) ilv1 '' || ' );
1269 Add_To_PackageTable (' '' WHERE ilv1.top_level_terr_id = jtr.top_level_terr_id '' || ' );
1270 Add_To_PackageTable (' '' AND ilv1.trans_id = jtr.trans_id '' || ' );
1271 Add_To_PackageTable (' '' AND ( '' || ' );
1272 Add_To_PackageTable (' '' (ROWNUM <= (SELECT NVL(t.num_winners, 1) FROM jtf_terr_all t WHERE t.terr_id = ilv1.top_level_terr_id) ) '' || ' );
1273 Add_To_PackageTable (' '' ) '' || ' );
1274 Add_To_PackageTable (' '' ) AS JTF_TERR_NUMBER_LIST ) ) '' || ' );
1275 Add_To_PackageTable (' '' WHERE 1 = 1 '' || ' );
1276 Add_To_PackageTable (' '' AND jtr.terr_id = COLUMN_VALUE '' || ' );
1277 Add_To_PackageTable (' '' AND jtr.result_id = :b2_result_id ''; ' );
1278 Add_To_PackageTable (' ');
1279 Add_To_PackageTable (' ');
1280 Add_To_PackageTable (' DBMS_SQL.PARSE ( l_Cursor, l_dyn_str, DBMS_SQL.NATIVE ); ');
1281 Add_To_PackageTable (' ');
1282 Add_To_PackageTable (' /* bind all input variables */ ');
1283 Add_To_PackageTable (' DBMS_SQL.BIND_VARIABLE (l_cursor, '':b1_result_id'', l_result_id ); ');
1284 Add_To_PackageTable (' DBMS_SQL.BIND_VARIABLE (l_cursor, '':b2_result_id'', l_result_id ); ');
1285 Add_To_PackageTable (' ');
1286 Add_To_PackageTable (' /* bind all output variables */ ');
1287 Add_To_PackageTable (' DBMS_SQL.DEFINE_ARRAY(l_cursor, 1, l_terr_id_arr, 32767, indx ); ');
1288 Add_To_PackageTable (' ');
1289 Add_To_PackageTable (' /* Execute the procedure call */ ');
1290 Add_To_PackageTable (' l_num_rows := DBMS_SQL.EXECUTE_AND_FETCH( l_cursor ); ');
1291 Add_To_PackageTable (' ');
1292 Add_To_PackageTable (' DBMS_SQL.COLUMN_VALUE (l_cursor, 1, l_terr_id_arr); ');
1293 Add_To_PackageTable (' ');
1294 Add_To_PackageTable (' DBMS_SQL.CLOSE_CURSOR(l_cursor); ');
1295 Add_To_PackageTable (' ');
1296 Add_To_PackageTable (' l_counter := l_terr_id_arr.FIRST; ');
1297 Add_To_PackageTable (' l_rsc_counter := 1; ');
1298 Add_To_PackageTable (' ');
1299 Add_To_PackageTable (' ');
1300 Add_To_PackageTable (' WHILE (l_counter <= l_terr_id_arr.last) LOOP ');
1301 Add_To_PackageTable (' ');
1302 Add_To_PackageTable (' --dbms_output.put_line( ''Winning Terr: '' || l_terr_id_arr(l_counter) ); ');
1303 Add_To_PackageTable (' ');
1304 Add_To_PackageTable (' FOR cgr IN csr_get_rsc( l_terr_id_arr(l_counter), l_sysdate ) LOOP ');
1305 Add_To_PackageTable (' ');
1306 Add_To_PackageTable (' x_rec(l_rsc_counter).resource_name := cgr.resource_name; ');
1307 Add_To_PackageTable (' x_rec(l_rsc_counter).resource_job_title := cgr.resource_job_title; ');
1308 Add_To_PackageTable (' x_rec(l_rsc_counter).resource_phone := cgr.resource_phone; ');
1309 Add_To_PackageTable (' x_rec(l_rsc_counter).resource_email := cgr.resource_email; ');
1310 Add_To_PackageTable (' x_rec(l_rsc_counter).resource_mgr_name := cgr.resource_mgr_name; ');
1311 Add_To_PackageTable (' x_rec(l_rsc_counter).resource_mgr_phone := cgr.resource_mgr_phone; ');
1312 Add_To_PackageTable (' x_rec(l_rsc_counter).resource_mgr_email := cgr.resource_mgr_email; ');
1313 Add_To_PackageTable (' x_rec(l_rsc_counter).terr_id := cgr.terr_id; ');
1314 Add_To_PackageTable (' ');
1315 Add_To_PackageTable (' --dbms_output.put_line(''Value of x_rec(l_rsc_counter).resource_name = '' || x_rec(l_rsc_counter).resource_name); ');
1316 Add_To_PackageTable (' --dbms_output.put_line(''Value of l_rsc_counter = '' ||TO_CHAR(l_rsc_counter)); ');
1317 Add_To_PackageTable (' ');
1318 Add_To_PackageTable (' l_rsc_counter := l_rsc_counter + 1; ');
1319 Add_To_PackageTable (' ');
1320 Add_To_PackageTable (' END LOOP; ');
1321 Add_To_PackageTable (' ');
1322 Add_To_PackageTable (' l_counter := l_counter + 1; ');
1323 Add_To_PackageTable (' ');
1324 Add_To_PackageTable (' END LOOP; ');
1325 Add_To_PackageTable (' ');
1326
1327 END IF; /* p_search_name = 'SEARCH_TERR_RULES' */
1328
1329
1330 --
1331 -- JDOCHERT: 07/23/03: bug#3020630
1332 --Add_To_PackageTable (' ');
1333 --Add_To_PackageTable (' DELETE FROM jtf_terr_results jtrs');
1334 --Add_To_PackageTable (' WHERE jtrs.result_id = l_result_id; ');
1335 --Add_To_PackageTable (' ');
1336 --
1337
1338
1339 END IF; /* ( l_str_len > 0 ) */
1340
1341 /* generate END of PROCEDURE */
1342 generate_end_of_procedure(l_procedure_name, p_source_id, p_qualifier_type);
1343
1344 EXCEPTION
1345 WHEN NO_DATA_FOUND
1346 THEN
1347 g_ProgramStatus := 1;
1348 Add_To_PackageTable (
1349 '--TERR_RULE_GEN: Unhandled exception NO_DATA_FOUND'
1350 );
1351 RETURN;
1352 END gen_terr_rules_recurse;
1353
1354
1355 /* ----------------------------------------------------------------
1356 This procedure will generate the Package
1357 called by The Get_WinningTerrMembers API
1358 -----------------------------------------------------------------*/
1359 PROCEDURE generate_api (
1360 errbuf OUT NOCOPY VARCHAR2,
1361 retcode OUT NOCOPY VARCHAR2,
1362 p_source_id IN NUMBER,
1363 p_qualifier_type_id IN NUMBER,
1364 p_debug_flag IN VARCHAR2,
1365 p_sql_trace IN VARCHAR2
1366 )
1367 AS
1368
1369 num_of_terr NUMBER(15);
1370 package_name VARCHAR2(30);
1371 package_desc VARCHAR2(100);
1372
1373 --l_index NUMBER;
1374 l_terr_id NUMBER;
1375
1376 l_abs_source_id NUMBER;
1377
1378 l_qualifier_type VARCHAR2(30);
1379 l_package_name VARCHAR2(30);
1380
1381 l_Retcode VARCHAR2(10);
1382 l_message VARCHAR2(2000);
1383
1384 lp_sysdate DATE := SYSDATE;
1385
1386 query_str VARCHAR2(255);
1387 l_object_name VARCHAR2(128);
1388 l_object_type VARCHAR2(18);
1389 l_created DATE;
1390 l_last_ddl_time DATE;
1391 l_timestamp VARCHAR2(19);
1392 l_status VARCHAR2(7);
1393
1394 l_denorm_count NUMBER;
1395 l_mv1_count NUMBER;
1396 l_mv2_count NUMBER;
1397 l_mv3_count NUMBER;
1398 l_mv4_count NUMBER;
1399 l_mv5_count NUMBER;
1400 l_mv6_count NUMBER;
1401
1402 BEGIN
1403 --dbms_output.put_line('JTF_TERR_ENGINE_GEN2_PVT.generate_api ');
1404 -- Initialize Global variables
1405 G_Debug := FALSE;
1406
1407 /* ACHANDA 03/08/2004 : Bug 3380047 : some of the dynamic packages are */
1408 /* created in "INVALID" status as g_pointer is not properly initialized */
1409 g_pointer := 0;
1410
1411 l_abs_source_id := ABS(p_source_id);
1412
1413 /* Initialize */
1414 SELECT j.name
1415 INTO l_qualifier_type
1416 FROM jtf_qual_types j
1417 WHERE j.qual_type_id = p_qualifier_type_id;
1418
1419 /* ARPATEL: 12/15/2003: Bug#3305019 */
1420 /* If the SQL trace flag is turned on, then turn on the trace */
1421 --If UPPER(p_SQL_Trace) = 'Y' Then
1422 -- dbms_session.set_sql_trace(TRUE);
1423 --End If;
1424
1425 /* If the debug flag is set, Then turn on the debug message logging */
1426 If UPPER( rtrim(p_Debug_Flag) ) = 'Y' Then
1427 G_Debug := TRUE;
1428 End If;
1429
1430 /* Check for territories for this Usage/Transaction Type */
1431 BEGIN
1432
1433 SELECT COUNT(*)
1434 INTO num_of_terr
1435 FROM jtf_terr_qtype_usgs_all jtqu
1436 , jtf_terr_usgs_all jtu
1437 , jtf_terr_all jt1
1438 , jtf_qual_type_usgs jqtu
1439 WHERE jtqu.terr_id = jt1.terr_id
1440 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
1441 AND jqtu.qual_type_id = p_qualifier_type_id
1442 AND jtu.source_id = p_source_id
1443 AND jtu.terr_id = jt1.terr_id
1444 AND NVL(jt1.end_date_active, lp_sysdate) >= lp_sysdate
1445 AND jt1.start_date_active <= lp_sysdate
1446 AND EXISTS (
1447 SELECT jtrs.terr_rsc_id
1448 FROM jtf_terr_rsc_all jtrs
1449 WHERE NVL(jtrs.end_date_active, lp_sysdate) >= lp_sysdate
1450 AND NVL(jtrs.start_date_active, lp_sysdate) <= lp_sysdate
1451 AND jtrs.terr_id = jt1.terr_id )
1452 AND NOT EXISTS (
1453 SELECT jt.terr_id
1454 FROM jtf_terr_all jt
1455 WHERE NVL(jt.end_date_active, lp_sysdate + 1) < lp_sysdate
1456 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
1457 START WITH jt.terr_id = jt1.terr_id)
1458 AND jqtu.qual_type_id <> -1001
1459 AND rownum < 2;
1460
1461 --dbms_output.put_line('JTF_TERR_ENGINE_GEN2_PVT:' || num_of_terr);
1462 EXCEPTION
1463 WHEN NO_DATA_FOUND Then
1464 num_of_terr := 0;
1465 END;
1466
1467 IF G_Debug THEN
1468
1469 /* Check for territories for this Usage/Transaction Type */
1470 BEGIN
1471
1472 SELECT COUNT(*)
1473 INTO num_of_terr
1474 FROM jtf_terr_qtype_usgs_all jtqu
1475 , jtf_terr_usgs_all jtu
1476 , jtf_terr_all jt1
1477 , jtf_qual_type_usgs jqtu
1478 WHERE jtqu.terr_id = jt1.terr_id
1479 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
1480 AND jqtu.qual_type_id = p_qualifier_type_id
1481 AND jtu.source_id = p_source_id
1482 AND jtu.terr_id = jt1.terr_id
1483 AND NVL(jt1.end_date_active, lp_sysdate) >= lp_sysdate
1484 AND jt1.start_date_active <= lp_sysdate
1485 AND EXISTS (
1486 SELECT jtrs.terr_rsc_id
1487 FROM jtf_terr_rsc_all jtrs
1488 WHERE NVL(jtrs.end_date_active, lp_sysdate) >= lp_sysdate
1489 AND NVL(jtrs.start_date_active, lp_sysdate) <= lp_sysdate
1490 AND jtrs.terr_id = jt1.terr_id )
1491 AND NOT EXISTS (
1492 SELECT jt.terr_id
1493 FROM jtf_terr_all jt
1494 WHERE NVL(jt.end_date_active, lp_sysdate + 1) < lp_sysdate
1495 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
1496 START WITH jt.terr_id = jt1.terr_id)
1497 AND jqtu.qual_type_id <> -1001;
1498
1499 --dbms_output.put_line('JTF_TERR_ENGINE_GEN2_PVT:' || num_of_terr);
1500 EXCEPTION
1501 WHEN NO_DATA_FOUND Then
1502 num_of_terr := 0;
1503 END;
1504
1505 Write_Log(2, ' ');
1506 Write_Log(2, '/***************** BEGIN: TERRITORY STATUS *********************/');
1507 Write_Log(2, ' ');
1508 Write_Log(2, 'Inside Generate_API initialize');
1509 Write_Log(2, 'source_id - ' || TO_CHAR(p_Source_Id) );
1510 Write_Log(2, 'qualifier_type_id - ' || TO_CHAR(p_qualifier_type_id) );
1511 Write_Log(2, 'Number of valid territories with resources for this Transaction: ' || num_of_terr );
1512 Write_Log(2, ' ');
1513 Write_Log(2, '/***************** END: TERRITORY STATUS *********************/');
1514 Write_Log(2, ' ');
1515
1516 END IF;
1517
1518
1519 IF G_Debug THEN
1520
1521 Write_Log(2, ' ');
1522 Write_Log(2, '/***************** BEGIN: PACKAGE STATUS *********************/');
1523 Write_Log(2, ' ');
1524
1525 END IF;
1526
1527 /* territories exist for this USAGE/TRANSACTION TYPE combination */
1528 IF (num_of_terr > 0) THEN
1529
1530 /* Catch-All territory */
1531 l_terr_id := 1;
1532
1533 /* Generate Package NAME */
1534 l_package_name := 'JTF_TERR_' || TO_CHAR (l_abs_source_id) || '_' ||
1535 l_qualifier_type || '_DYN';
1536
1537 /* Generate Package BODY */
1538 package_desc := '/* Auto Generated Package */';
1539 generate_package_header(l_package_name, package_desc, 'PKB');
1540
1541 /* generate individual SQL statements territories */
1542 gen_terr_rules_recurse (
1543 p_terr_id => l_terr_id,
1544 p_source_id => p_source_id,
1545 p_qualifier_type_id => p_qualifier_type_id,
1546 p_qualifier_type => l_qualifier_type,
1547 p_search_name => 'SEARCH_TERR_RULES' );
1548
1549 --dbms_output.put_line('NEW ENGINE: Value of l_package_name='||l_package_name);
1550
1551 /* Also generate Search across all orgs for Oracle Sales/Accounts*/
1552 IF ( p_source_id = -1001 AND
1553 p_qualifier_type_id = -1002 ) THEN
1554
1555 /* generate individual SQL statements territories */
1556 gen_terr_rules_recurse (
1557 p_terr_id => l_terr_id,
1558 p_source_id => p_source_id,
1559 p_qualifier_type_id => p_qualifier_type_id,
1560 p_qualifier_type => l_qualifier_type,
1561 p_search_name => 'SEARCH_TERR_RULES_ALL' );
1562
1563 END IF;
1564
1565 --dbms_output.put_line('[1]Value of p_qualifier_type_id = '||l_package_name);
1566
1567 /* generate end of package BODY */
1568 generate_end_of_package(l_package_name, 'TRUE');
1569
1570 --dbms_output.put_line('[2]Value of p_qualifier_type_id='||TO_CHAR(p_qualifier_type_id));
1571
1572 ELSE
1573
1574 IF (G_Debug) THEN
1575 Write_Log(2, 'PACKAGE ' || l_package_name || ' NOT CREATED SUCCESSFULLY: no territories with resources exist for this Usage/Transaction combination. ');
1576 END IF;
1577
1578 g_ProgramStatus := 1;
1579
1580 END IF; /* num_of_terr > 0 */
1581
1582
1583 /* check status of DYNAMICALLY CREATED PACKAGE */
1584 BEGIN
1585
1586 query_str :=
1587 ' SELECT uo.object_name, uo.object_type, uo.created, uo.last_ddl_time, uo.timestamp, uo.status' ||
1588 ' FROM user_objects uo' ||
1589 ' WHERE uo.object_type = ''PACKAGE BODY'' AND uo.object_name = :b1 and rownum < 2';
1590
1591 EXECUTE IMMEDIATE query_str
1592 INTO l_object_name
1593 , l_object_type
1594 , l_created
1595 , l_last_ddl_time
1596 , l_timestamp
1597 , l_status
1598 USING l_package_name ;
1599
1600 IF (l_status = 'INVALID') THEN
1601
1602 /* ACHANDA 03/08/2004 : Bug 3380047 : if the package is created in invalid status the message is */
1603 /* written to the log file irrespective of wthether debug flag is set to true or false */
1604 Write_Log(1, 'Status of the package ' || l_package_name || ' IS INVALID. ');
1605 IF G_Debug THEN
1606 Write_Log(2, ' ');
1607 Write_Log(2, 'PACKAGE ' || l_package_name || ' NOT CREATED SUCCESSFULLY: cannot be compiled. ');
1608 Write_Log(2, ' ');
1609 END IF;
1610
1611 /* ACHANDA 03/08/2004 : Bug 3380047 : set the value to 2 to */
1612 /* distinguish this exception from other exceptions in the program */
1613 g_ProgramStatus := 2;
1614
1615 END IF;
1616
1617 EXCEPTION
1618 WHEN others THEN
1619 NULL;
1620 END;
1621
1622
1623 IF G_Debug THEN
1624
1625 Write_Log(2, ' ');
1626 Write_Log(2, l_object_type || ': ' || l_package_name);
1627 Write_Log(2, 'Created: ' || TO_CHAR(l_created) );
1628 Write_Log(2, 'Last DDL Time: ' || TO_CHAR(l_last_ddl_time) );
1629 Write_Log(2, 'Timestamp: ' || l_timestamp );
1630 Write_Log(2, 'Status: ' || l_status );
1631 Write_Log(2, ' ');
1632 Write_Log(2, '/***************** END: PACKAGE STATUS *********************/');
1633 Write_Log(2, ' ');
1634
1635 END IF;
1636
1637
1638 IF g_ProgramStatus = 1 Then
1639 ERRBUF := 'Program Completed WITH EXCEPTIONS';
1640 RetCODE := 1;
1641 ElsIf g_ProgramStatus = 0 Then
1642 ERRBUF := 'Program completed SUCCESSFULLY.';
1643 RetCode := 0;
1644 /* ACHANDA : 03/08/2004 : Added to handle the case of the package getting created in invalid status */
1645 ElsIf g_ProgramStatus = 2 Then
1646 ERRBUF := 'Package is created in invalid status.';
1647 RetCode := 2;
1648 End If;
1649
1650 Write_Log(2,ERRBUF);
1651
1652
1653 EXCEPTION
1654 WHEN utl_file.invalid_path OR utl_file.invalid_mode OR
1655 utl_file.invalid_filehandle OR utl_file.invalid_operation OR
1656 utl_file.write_error Then
1657 ERRBUF := 'Program terminated with exception. Error writing to output file.';
1658 RETCODE := 2;
1659
1660 WHEN OTHERS THEN
1661 If G_Debug Then
1662 Write_Log(1,'Program terminated with OTHERS exception. ' || SQLERRM);
1663 End If;
1664 ERRBUF := 'Program terminated with OTHERS exception. ' || SQLERRM;
1665 RETCODE := 2;
1666 END generate_api;
1667
1668 END JTF_TERR_ENGINE_GEN2_PVT;