[Home] [Help]
PACKAGE BODY: APPS.JTF_TAE_GEN_PVT
Source
1 PACKAGE BODY jtf_tae_gen_pvt AS
2 /* $Header: jtfvtaeb.pls 120.0 2005/06/02 18:22:34 appldev ship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTF_TAE_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 TAE 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 -- 02/25/02 SBEHERA Created
20 -- 04/09/02 SBEHERA changed spec for generate_api
21 -- 04/16/02 SBEHERA added distinct
22 -- 05/02/02 SBEHERA added AND in the statement
23 -- 05/15/02 SBEHERA append was modified
24 -- 06/03/02 SBEHERA added code for relation_product 73(cnr group)
25 -- 06/03/03 EIHSU worker_id conditions added
26 -- 06/04/03 EIHSU worker_id removed from ILV1
27 -- 06/20/03 EIHSU worker_id order in sel
28 -- 03/08/04 ACHANDA Bug 3380047
29 -- 04/15/04 ARPATEL Added static SQL for qual_relation_product=353393
30 -- 06/25/04 ACHANDA Bug 3718223
31 -- 08/17/04 ACHANDA Bug 3835831
32 -- 12/08/04 ACHANDA Bug 4048033 : added special processing for qual comb 61950277 and 62598971
33 -- 02/24/05 ACHANDA Bug 4192854 : added special processing for qual comb 934313 and 924631
34 -- 04/12/05 ACHANDA Bug 4307593 : remove the worker_id condition from NMC_DYN package
35 -- 05/17/05 ACHANDA Bug 4385668 : modify the procedure append_inline_view so that new mode inline view
36 -- contains NO_MERGE hint and DISTINCT clause
37 -- End of Comments
38 --
39
40 /*------------------------------------------------------
41
42 ---------------------------------------------------------*/
43
44 --------------------------------------------------
45 --- GLOBAL Declarations Starts here -----
46 --------------------------------------------------
47
48 -- Stores the org_id for use in package Names
49 g_cached_org_append VARCHAR2(15);
50 --
51 -- Identifies the Package associated a
52 -- a territory with child nodes
53 g_terr_pkgspec terr_pkgspec_tbl_type;
54
55 -- Stores the position with the table spec
56 g_stack_pointer NUMBER := 0;
57
58 -- Store the information passed as
59 -- Concurrent program parameters
60 -- Module that uses Territories
61 g_source_id NUMBER := 0;
62
63 g_abs_source_id NUMBER := 0;
64
65 -- Type of transaction for which the
66 -- the package is being generated
67 g_qualifier_type VARCHAR2(60);
68
69 -- Id of the corresponding transaction type
70 g_qual_type_id NUMBER := 0;
71
72 TYPE t_pkgname IS TABLE OF VARCHAR2(256)
73 INDEX BY BINARY_INTEGER;
74
75 g_pkgname_tbl t_pkgname;
76 g_Pointer NUMBER := 0;
77 G_Debug BOOLEAN := FALSE;
78 g_ProgramStatus NUMBER := 0;
79
80 /* jdochert: 05/01/02 */
81 G_DYN_PKG_NAME VARCHAR2(30) := NULL;
82
83 /* jdochert: 07/31/02 */
84 G_NEWLINE VARCHAR2(30) := FND_GLOBAL.Local_Chr(10);
85 G_INDENT VARCHAR2(30) := ' ';
86 G_INDENT1 VARCHAR2(30) := ' ';
87
88 /* dblee: 08/20/03 - define global variable for holding select clause which differs
89 between 'full' mode and 'new mode' TAP */
90 k_select_list_fm CONSTANT VARCHAR2(240) :=
91 'SELECT a.trans_object_id,a.trans_detail_object_id,a.worker_id,a.header_id1,a.header_id2,'
92 || 'ILV.terr_id,ILV.absolute_rank,ILV.top_level_terr_id ,ILV.num_winners,ILV.org_id ';
93
94 k_select_list_nm CONSTANT VARCHAR2(240) :=
95 'SELECT A.*,ILV.terr_id,ILV.absolute_rank,ILV.top_level_terr_id ,ILV.num_winners ';
96
97 g_select_list_1 VARCHAR2(240) := k_select_list_fm;
98
99 --------------------------------------------------------------------
100 -- Logging PROCEDURE
101 --
102 -- which = 1. write to log
103 -- which = 2, write to output
104 --------------------------------------------------------------------
105 --
106 PROCEDURE Write_Log(which number, mssg varchar2 )
107 IS
108 BEGIN
109 --
110 --dbms_output.put_line(' LOG: ' || mssg );
111 FND_FILE.put(which, mssg);
112 FND_FILE.NEW_LINE(which, 1);
113 --
114 -- If the output message and if debug flag is set then also write
115 -- to the log file
116 --
117 IF Which = 2 THEN
118 IF G_Debug THEN
119 FND_FILE.put(1, mssg);
120 FND_FILE.NEW_LINE(1, 1);
121 END IF;
122 END IF;
123 --
124 END Write_Log;
125
126 FUNCTION build_predicate_for_operator(
127 op_common_where VARCHAR2
128 ,op_eql VARCHAR2
129 ,op_lss_thn VARCHAR2
130 ,op_lss_thn_eql VARCHAR2
131 ,op_grtr_thn VARCHAR2
132 ,op_grtr_thn_eql VARCHAR2
133 ,op_like VARCHAR2
134 ,op_between VARCHAR2
135 ,l_newline VARCHAR2)
136 RETURN VARCHAR2
137 AS
138 l_result VARCHAR2(32767);
139
140 BEGIN
141 --dbms_output.put_line('Inside Call_Create_Package PACKAGE_NAME - ' || 'x' || ' g_pointer - ' || to_char(g_pointer - 1) || 'x' || 'x');
142 IF G_Debug THEN
143 Write_Log(1, 'INSIDE function JTF_TAE_GEN_PVT.build_predicate_for_operator: ' );
144 END IF;
145
146 l_result := op_common_where;
147
148 IF op_eql IS NOT NULL THEN
149 l_result := 'AND ' || l_result || l_newline || 'AND ( ' || op_eql;
150 END IF;
151
152 IF op_lss_thn IS NOT NULL THEN
153 IF l_result = op_common_where THEN
154 l_result := 'AND ' || l_result || l_newline || 'AND ( ' || op_lss_thn;
155 ELSE
156 l_result := l_result || l_newline || ' OR ' || l_newline || op_lss_thn;
157 END IF;
158 END IF;
159
160 IF op_lss_thn_eql IS NOT NULL THEN
161 IF l_result = op_common_where THEN
162 l_result := 'AND ' || l_result || l_newline || 'AND ( ' || op_lss_thn_eql;
163 ELSE
164 l_result := l_result || l_newline || ' OR ' || l_newline || op_lss_thn_eql;
165 END IF;
166 END IF;
167
168 IF op_grtr_thn IS NOT NULL THEN
169 IF l_result = op_common_where THEN
170 l_result := 'AND ' || l_result || l_newline || 'AND ( ' || op_grtr_thn;
171 ELSE
172 l_result := l_result || l_newline || ' OR ' || l_newline || op_grtr_thn;
173 END IF;
174 END IF;
175
176 IF op_grtr_thn_eql IS NOT NULL THEN
177 IF l_result = op_common_where THEN
178 l_result := 'AND ' || l_result || l_newline || 'AND ( ' || op_grtr_thn_eql;
179 ELSE
180 l_result := l_result || l_newline || ' OR ' || l_newline || op_grtr_thn_eql;
181 END IF;
182 END IF;
183
184 IF op_like IS NOT NULL THEN
185 IF l_result = op_common_where THEN
186 l_result := 'AND ' || l_result || l_newline || 'AND ( ' || op_like;
187 ELSE
188 l_result := l_result || l_newline || ' OR ' || l_newline|| op_like;
189 END IF;
190 END IF;
191
192 IF op_between IS NOT NULL THEN
193 IF l_result = op_common_where THEN
194 l_result := 'AND ' || l_result || l_newline || 'AND ( ' || op_between;
195 ELSE
196 l_result := l_result || l_newline || ' OR ' || l_newline || op_between;
197 END IF;
198 END IF;
199
200 l_result := l_result || l_newline || ' )' || l_newline;
201
202 RETURN l_result;
203
204 EXCEPTION
205 WHEN OTHERS THEN
206 IF G_Debug THEN
207 g_ProgramStatus := 1;
208 Write_Log(2, 'Program terminated with OTHERS exception. ' || SQLERRM);
209 END IF;
210 END build_predicate_for_operator;
211
212
213 ----------------------------------------------------------------
214 -- Store the Line for the package to a table
215 ----------------------------------------------------------------
216 PROCEDURE Add_To_PackageTable(p_statement IN VARCHAR2)
217 AS
218 BEGIN
219 --dbms_output.put_line( p_statement );
220
221 ad_ddl.build_package(p_statement, g_pointer);
222
223 --Increment the counters
224 g_pointer := g_pointer + 1;
225
226 EXCEPTION
227 WHEN Others THEN
228 NULL;
229 END Add_To_PackageTable;
230
231
232 ----------------------------------------------------------------
233 -- Create the package using AD_DDL command
234 ----------------------------------------------------------------
235 FUNCTION Call_Create_Package(
236 is_package_body VARCHAR2,
237 package_name VARCHAR2)
238 RETURN BOOLEAN
239 AS
240 l_result BOOLEAN;
241 l_status VARCHAR2(10);
242 l_industry VARCHAR2(10);
243 l_applsys_schema VARCHAR2(30);
244
245 BEGIN
246
247 --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);
248 IF G_Debug THEN
249 Write_Log(1, 'INSIDE PROCEDURE JTF_TAE_GEN_PVT.Call_Create_Package: PACKAGE_NAME = ' || package_name );
250 END IF;
251
252 l_result := fnd_installation.get_app_info('FND',
253 l_status,
254 l_industry,
255 l_applsys_schema);
256
257 ad_ddl.create_package(l_applsys_schema,
258 'JTF',
259 package_name,
260 is_package_body,
261 0,
262 (g_pointer - 1));
263
264 -- Reset the global pointer.
265 g_Pointer := 0;
266
267 RETURN TRUE;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 /* ACHANDA 03/08/2004 : Bug 3380047 : if the package is not created successfully */
272 /* because of lock , the program should write an informative message to the log */
273 /* file but it should not error out */
274 write_log(1, 'Package ' || package_name || ' NOT CREATED SUCCESSFULLY ');
275 write_log(1, SQLERRM);
276 g_pointer := 0;
277 RETURN FALSE;
278 /* the following code is commented out as the logic should be executed */
279 /* irrespective of the fact whether the debug is set to Yes or No */
280 /*
281 If G_Debug Then
282 g_ProgramStatus := 1;
283 Write_Log(2, 'Program terminated with OTHERS exception. ' || SQLERRM);
284 g_Pointer := 0;
285 RETURN FALSE;
286 End If;
287 */
288 END Call_Create_Package;
289
290
291 /*---------------------------------------------------------------
292 This procedure will generate the PACKAGE
293 SPEC or BODY controlled by a parameter
294
295 eg: CREATE OR REPLACE PACKAGE JTF_TERR_1001_LEAD_1_240 or
296 CREATE OR REPLACE PACKAGE BODY JTF_TERR_1001_LEAD_1_240
297 ---------------------------------------------------------------*/
298 PROCEDURE generate_package_header(
299 p_package_name VARCHAR2,
300 p_description VARCHAR2,
301 p_object_type VARCHAR2
302 )
303 AS
304 v_package_name VARCHAR2(100);
305 BEGIN
306
307 v_package_name := LOWER(p_package_name);
308
309 IF G_Debug THEN
310 Write_Log(1, 'INSIDE PROCEDURE JTF_TAE_GEN_PVT.generate_package_header: v_package_name = ' || v_package_name );
311 END IF;
312
313 /* -- The description was commented out as part of AD_DDL error
314 -- that caused others exception.
315 -- ORA-20000: Unknown or unsupported object type in create_plsql_object()
316 --
317 -- Add_To_PackageTable (p_description);
318 */
319
320 IF p_object_type = 'PKS' THEN
321
322 /* create package spec */
323 --Add_To_PackageTable (p_description);
324 Add_To_PackageTable (
325 'CREATE OR REPLACE PACKAGE ' || v_package_name || ' AS '
326 );
327 Add_To_PackageTable (' ');
328
329 ELSE
330
331 /* create package body */
332 --Add_To_PackageTable (p_description);
333 Add_To_PackageTable (
334 'CREATE OR REPLACE PACKAGE BODY ' || v_package_name || ' AS '
335 );
336 Add_To_PackageTable ('--');
337 Add_To_PackageTable (' ');
338
339 END IF;
340
341 END generate_package_header;
342
343 /*----------------------------------------------------------
344 This procedure will add the the END package
345 statement for the package name passed in as
346 parameter
347
348 eg: END JTF_TERR_1001_LEAD_1_240;
349 Note: 1001 - Source Id
350 1 - Package Count
351 240 - Org Id
352 ----------------------------------------------------------*/
353 PROCEDURE generate_end_of_package(p_package_name VARCHAR2, is_package_body VARCHAR2)
354 AS
355 v_package_name VARCHAR2(100);
356 l_Status BOOLEAN;
357 BEGIN
358
359 v_package_name := LOWER (p_package_name);
360
361 IF G_Debug THEN
362 Write_Log(1, 'INSIDE PROCEDURE JTF_TAE_GEN_PVT.generate_end_of_package: v_package_name = ' || v_package_name );
363 END IF;
364
365 Add_To_PackageTable (' ' );
366 Add_To_PackageTable ('END ' || v_package_name || ';');
367 Add_To_PackageTable ('/* End of package ' || v_package_name || ' */');
368
369 /* Call the procedure to create the package using AD_DDL */
370 l_Status := Call_Create_Package(is_package_body, v_package_name);
371
372 END generate_end_of_package;
373
374 /*----------------------------------------------------------
375 This procedure will add the END procedure
376 statement for the procedure name passed in as
377 parameter, e.g., END SEARCH_TERR_RULES;
378 ----------------------------------------------------------*/
379 PROCEDURE generate_end_of_procedure ( p_procedure_name VARCHAR2
380 , p_source_id NUMBER
381 , p_target_type VARCHAR2)
382
383 AS
384
385 lp_pkg_name VARCHAR2(30);
386
387 BEGIN
388
389 IF G_Debug THEN
390 Write_Log(1, 'INSIDE PROCEDURE JTF_TAE_GEN_PVT.generate_end_of_procedure: p_procedure_name = ' || p_procedure_name );
391 END IF;
392
393 lp_pkg_name := 'JTF_TAE_'|| TO_CHAR(ABS(p_source_id)) ||'_' || p_target_type || '_DYN';
394
395 --Add_To_PackageTable (' ');
396 --Add_To_PackageTable (' /*--------------------------------------');
397 --Add_To_PackageTable (' ** When no territories, have NULL ');
398 --Add_To_PackageTable (' ** so that package is not invalid' );
399 --Add_To_PackageTable (' ** when it is created ');
400 --Add_To_PackageTable (' **--------------------------------------*/ ');
401 --Add_To_PackageTable (' NULL;');
402 Add_To_PackageTable (' ');
403 Add_To_PackageTable ('EXCEPTION ');
404 Add_To_PackageTable (' ');
405 Add_To_PackageTable (' WHEN VALUE_ERROR THEN ');
406 Add_To_PackageTable (' x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;');
407 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', ''EXCEPTION: VALUE_ERROR''); ');
408 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', SQLERRM); ');
409 Add_To_PackageTable (' JTF_TAE_CONTROL_PVT.WRITE_LOG(2, x_Msg_Data);');
410 Add_To_PackageTable (' ROLLBACK TO JTF_TAE_MATCHING_TRANSACTION; ');
411 Add_To_PackageTable (' RAISE; ');
412 Add_To_PackageTable (' ');
413 Add_To_PackageTable (' WHEN NO_DATA_FOUND THEN ');
414 Add_To_PackageTable (' x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;');
415 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', ''EXCEPTION: NO_DATA_FOUND''); ');
416 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', SQLERRM); ');
417 Add_To_PackageTable (' JTF_TAE_CONTROL_PVT.WRITE_LOG(2, x_Msg_Data);');
418 Add_To_PackageTable (' ROLLBACK TO JTF_TAE_MATCHING_TRANSACTION; ');
419 Add_To_PackageTable (' RAISE; ');
420 Add_To_PackageTable (' ');
421 Add_To_PackageTable (' WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN ');
422 Add_To_PackageTable (' x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;');
423 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', ''EXCEPTION: FND_API.G_RET_STS_UNEXP_ERROR''); ');
424 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', SQLERRM); ');
425 Add_To_PackageTable (' JTF_TAE_CONTROL_PVT.WRITE_LOG(2, x_Msg_Data);');
426 Add_To_PackageTable (' ROLLBACK TO JTF_TAE_MATCHING_TRANSACTION; ');
427 Add_To_PackageTable (' RAISE; ');
428 Add_To_PackageTable (' ');
429
430 Add_To_PackageTable (' WHEN OTHERS THEN ');
431 Add_To_PackageTable (' x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;');
432 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', ''EXCEPTION: OTHERS''); ');
433 Add_To_PackageTable (' FND_MSG_PUB.Add_Exc_Msg(''' || lp_pkg_name || ''', ''' || p_procedure_name || ''', SQLERRM); ');
434 Add_To_PackageTable (' JTF_TAE_CONTROL_PVT.WRITE_LOG(2, x_Msg_Data);');
435 Add_To_PackageTable (' ROLLBACK TO JTF_TAE_MATCHING_TRANSACTION; ');
436 Add_To_PackageTable (' RAISE; ');
437
438 Add_To_PackageTable (' ');
439 Add_To_PackageTable ('END ' || p_procedure_name || ';');
440 Add_To_PackageTable ('/* End of procedure ' || p_procedure_name || ' */');
441 Add_To_PackageTable (' ');
442
443 END generate_end_of_procedure;
444
445 /*----------------------------------------------------------
446 This procedure will create the SPEC and BODY
447 for PROCEDURE/FUNCTION
448
449 eg: PROCEDURE TERR_RULE_1;
450 Note: 1 is the Territory Id
451 ----------------------------------------------------------*/
452 PROCEDURE generate_object_definition (
453 procedure_name VARCHAR2,
454 description VARCHAR2,
455 parameter_list1 VARCHAR2,
456 parameter_list2 VARCHAR2,
457 procedure_type VARCHAR2,
458 return_type VARCHAR2,
459 object_type VARCHAR2
460 )
461 IS
462 BEGIN
463
464 IF G_Debug THEN
465 Write_Log(1, 'INSIDE PROCEDURE JTF_TAE_GEN_PVT.generate_object_definition: procedure_name = ' || procedure_name );
466 END IF;
467
468 -- Generate procedure header and parameters in both spec and body
469 IF (procedure_type = 'P')
470 THEN
471 Add_To_PackageTable ('PROCEDURE ' || LOWER (procedure_name));
472 ELSIF (procedure_type = 'F')
473 THEN
474 Add_To_PackageTable ('FUNCTION ' || LOWER (procedure_name));
475 END IF;
476
477 IF (parameter_list1 IS NOT NULL)
478 THEN
479 Add_To_PackageTable (' (' || parameter_list1 );
480 Add_To_PackageTable ( parameter_list2 || ')');
481 END IF;
482
483 IF (procedure_type = 'F')
484 THEN
485 Add_To_PackageTable (' RETURN ' || return_type);
486 END IF;
487
488 IF (object_type = 'PKS')
489 THEN
490 Add_To_PackageTable (';');
491 ELSE
492 Add_To_PackageTable (' AS');
493 END IF;
494 END generate_object_definition;
495
496
497 /* dblee/eihsu: 08/15/03 added p_new_mode_fetch flag */
498 FUNCTION append_inlineview(p_input_string IN VARCHAR2,
499 p_new_mode_fetch IN CHAR)
500 RETURN VARCHAR2 AS
501 BEGIN
502 IF p_new_mode_fetch <> 'Y' THEN
503
504 RETURN p_input_string || g_newline || g_newline ||
505 G_INDENT || ' , /* INLINE VIEW */' || g_newline ||
506 G_INDENT || ' ( SELECT /*+ NO_MERGE */ ' || g_newline ||
507 G_INDENT || ' jtdr.terr_id ' || g_newline ||
508 G_INDENT || ' , jtdr.source_id ' || g_newline ||
509 G_INDENT || ' , jtdr.qual_type_id ' || g_newline ||
510 G_INDENT || ' , jtdr.top_level_terr_id ' || g_newline ||
511 G_INDENT || ' , jtdr.absolute_rank ' || g_newline ||
512 G_INDENT || ' , jtdr.num_winners ' || g_newline ||
513 G_INDENT || ' , jtdr.org_id ' || g_newline ||
514 G_INDENT || ' FROM jtf_terr_denorm_rules_all jtdr ' || g_newline ||
515 G_INDENT || ' ,jtf_terr_qtype_usgs_all jtqu ' || g_newline ||
516 G_INDENT || ' ,jtf_qual_type_usgs_all jqtu ' || g_newline ||
517 G_INDENT || ' WHERE jtdr.source_id = p_source_id ' || g_newline ||
518 G_INDENT || ' AND jtdr.terr_id= jtdr.related_terr_id' || g_newline ||
519
520 G_INDENT || ' AND jqtu.source_id = jtdr.source_id ' || g_newline ||
521 G_INDENT || ' AND jqtu.qual_type_id = p_trans_object_type_id ' || g_newline ||
522 G_INDENT || ' AND jtdr.terr_id = jtqu.terr_id ' || g_newline ||
523 G_INDENT || ' AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ' || g_newline ||
524
525 G_INDENT || ' AND jtdr.resource_exists_flag = ''Y'' '|| g_newline ||
526 G_INDENT || ' AND jtqu.qual_relation_product = lp_qual_combination_tbl(i) ' || g_newline ||
527 G_INDENT || ' ) ILV'||g_newline;
528
529 ELSE
530
531 RETURN p_input_string || g_newline || g_newline ||
532 G_INDENT || ' , /* INLINE VIEW */' || g_newline ||
533 G_INDENT || ' ( SELECT /*+ NO_MERGE */ DISTINCT ' || g_newline ||
534 G_INDENT || ' jtdr.terr_id ' || g_newline ||
535 G_INDENT || ' , jtdr.source_id ' || g_newline ||
536 G_INDENT || ' , jtdr.qual_type_id ' || g_newline ||
537 G_INDENT || ' , jtdr.top_level_terr_id ' || g_newline ||
538 G_INDENT || ' , jtdr.absolute_rank ' || g_newline ||
539 G_INDENT || ' , jtdr.num_winners ' || g_newline ||
540 G_INDENT || ' , jtdr.org_id ' || g_newline ||
541 G_INDENT || ' FROM jtf_terr_denorm_rules_all jtdr ' || g_newline ||
542 G_INDENT || ' ,jtf_changed_terr_all jct ' || g_newline ||
543 G_INDENT || ' ,jtf_terr_qtype_usgs_all jtqu ' || g_newline ||
544 G_INDENT || ' ,jtf_qual_type_usgs_all jqtu ' || g_newline ||
545 G_INDENT || ' WHERE jqtu.source_id = p_source_id ' || g_newline ||
546 G_INDENT || ' AND jtdr.terr_id= jct.terr_id ' || g_newline ||
547 G_INDENT || ' AND jtdr.terr_id= jtdr.related_terr_id' || g_newline ||
548
549 G_INDENT || ' AND jqtu.source_id = jtdr.source_id ' || g_newline ||
550 G_INDENT || ' AND jqtu.qual_type_id = p_trans_object_type_id ' || g_newline ||
551 G_INDENT || ' AND jct.terr_id = jtqu.terr_id ' || g_newline ||
552 G_INDENT || ' AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ' || g_newline ||
553
554 G_INDENT || ' AND jtdr.resource_exists_flag = ''Y'' '|| g_newline ||
555 G_INDENT || ' AND jtqu.qual_relation_product = lp_qual_combination_tbl(i) ' || g_newline ||
556 G_INDENT || ' ) ILV'||g_newline;
557
558 END IF;
559
560 EXCEPTION
561 WHEN OTHERS THEN
562 g_ProgramStatus := 1;
563 Add_To_PackageTable ('-- Program failed in inlineview procedure ');
564
565 END append_inlineview;
566
567 /* dblee: 08/15/03 */
568 PROCEDURE add_insert_nmtrans(p_match_table_name IN VARCHAR2)
569 AS
570 BEGIN
571
572 Add_To_PackageTable (' INSERT INTO '|| p_match_table_name || ' i');
573 Add_To_PackageTable (' (' );
574 Add_To_PackageTable (' TRANS_OBJECT_ID');
575 Add_To_PackageTable (' , TRANS_DETAIL_OBJECT_ID');
576 Add_To_PackageTable (' , HEADER_ID1');
577 Add_To_PackageTable (' , HEADER_ID2');
578 Add_To_PackageTable (' , SOURCE_ID');
579 Add_To_PackageTable (' , TRANS_OBJECT_TYPE_ID');
580 Add_To_PackageTable (' , LAST_UPDATE_DATE');
581 Add_To_PackageTable (' , LAST_UPDATED_BY');
582 Add_To_PackageTable (' , CREATION_DATE');
583 Add_To_PackageTable (' , CREATED_BY');
584 Add_To_PackageTable (' , LAST_UPDATE_LOGIN');
585 Add_To_PackageTable (' , REQUEST_ID');
586 Add_To_PackageTable (' , PROGRAM_APPLICATION_ID');
587 Add_To_PackageTable (' , PROGRAM_ID');
588 Add_To_PackageTable (' , PROGRAM_UPDATE_DATE');
589 Add_To_PackageTable (' , SQUAL_FC01');
590 Add_To_PackageTable (' , SQUAL_FC02');
591 Add_To_PackageTable (' , SQUAL_FC03');
592 Add_To_PackageTable (' , SQUAL_FC04');
593 Add_To_PackageTable (' , SQUAL_FC05');
594 Add_To_PackageTable (' , SQUAL_CURC01');
595 Add_To_PackageTable (' , SQUAL_CURC02');
596 Add_To_PackageTable (' , SQUAL_CURC03');
597 Add_To_PackageTable (' , SQUAL_CURC04');
598 Add_To_PackageTable (' , SQUAL_CURC05');
599 Add_To_PackageTable (' , SQUAL_CURC06');
600 Add_To_PackageTable (' , SQUAL_CURC07');
601 Add_To_PackageTable (' , SQUAL_CURC08');
602 Add_To_PackageTable (' , SQUAL_CURC09');
603 Add_To_PackageTable (' , SQUAL_CURC10');
604 Add_To_PackageTable (' , SQUAL_CHAR01');
605 Add_To_PackageTable (' , SQUAL_CHAR02');
606 Add_To_PackageTable (' , SQUAL_CHAR03');
607 Add_To_PackageTable (' , SQUAL_CHAR04');
608 Add_To_PackageTable (' , SQUAL_CHAR05');
609 Add_To_PackageTable (' , SQUAL_CHAR06');
610 Add_To_PackageTable (' , SQUAL_CHAR07');
611 Add_To_PackageTable (' , SQUAL_CHAR08');
612 Add_To_PackageTable (' , SQUAL_CHAR09');
613 Add_To_PackageTable (' , SQUAL_CHAR10');
614 Add_To_PackageTable (' , SQUAL_CHAR11');
615 Add_To_PackageTable (' , SQUAL_CHAR12');
616 Add_To_PackageTable (' , SQUAL_CHAR13');
617 Add_To_PackageTable (' , SQUAL_CHAR14');
618 Add_To_PackageTable (' , SQUAL_CHAR15');
619 Add_To_PackageTable (' , SQUAL_CHAR16');
620 Add_To_PackageTable (' , SQUAL_CHAR17');
621 Add_To_PackageTable (' , SQUAL_CHAR18');
622 Add_To_PackageTable (' , SQUAL_CHAR19');
623 Add_To_PackageTable (' , SQUAL_CHAR20');
624 Add_To_PackageTable (' , SQUAL_CHAR21');
625 Add_To_PackageTable (' , SQUAL_CHAR22');
626 Add_To_PackageTable (' , SQUAL_CHAR23');
627 Add_To_PackageTable (' , SQUAL_CHAR24');
628 Add_To_PackageTable (' , SQUAL_CHAR25');
629 Add_To_PackageTable (' , SQUAL_CHAR26');
630 Add_To_PackageTable (' , SQUAL_CHAR27');
631 Add_To_PackageTable (' , SQUAL_CHAR28');
632 Add_To_PackageTable (' , SQUAL_CHAR30');
633 Add_To_PackageTable (' , SQUAL_CHAR31');
634 Add_To_PackageTable (' , SQUAL_CHAR32');
635 Add_To_PackageTable (' , SQUAL_CHAR33');
636 Add_To_PackageTable (' , SQUAL_CHAR34');
637 Add_To_PackageTable (' , SQUAL_CHAR35');
638 Add_To_PackageTable (' , SQUAL_CHAR36');
639 Add_To_PackageTable (' , SQUAL_CHAR37');
640 Add_To_PackageTable (' , SQUAL_CHAR38');
641 Add_To_PackageTable (' , SQUAL_CHAR39');
642 Add_To_PackageTable (' , SQUAL_CHAR40');
643 Add_To_PackageTable (' , SQUAL_CHAR41');
644 Add_To_PackageTable (' , SQUAL_CHAR42');
645 Add_To_PackageTable (' , SQUAL_CHAR43');
646 Add_To_PackageTable (' , SQUAL_CHAR44');
647 Add_To_PackageTable (' , SQUAL_CHAR45');
648 Add_To_PackageTable (' , SQUAL_CHAR46');
649 Add_To_PackageTable (' , SQUAL_CHAR47');
650 Add_To_PackageTable (' , SQUAL_CHAR48');
651 Add_To_PackageTable (' , SQUAL_CHAR49');
652 Add_To_PackageTable (' , SQUAL_CHAR50');
653 Add_To_PackageTable (' , SQUAL_CHAR51');
654 Add_To_PackageTable (' , SQUAL_CHAR52');
655 Add_To_PackageTable (' , SQUAL_CHAR53');
656 Add_To_PackageTable (' , SQUAL_CHAR54');
657 Add_To_PackageTable (' , SQUAL_CHAR55');
658 Add_To_PackageTable (' , SQUAL_CHAR56');
659 Add_To_PackageTable (' , SQUAL_CHAR57');
660 Add_To_PackageTable (' , SQUAL_CHAR58');
661 Add_To_PackageTable (' , SQUAL_CHAR59');
662 Add_To_PackageTable (' , SQUAL_CHAR60');
663 Add_To_PackageTable (' , SQUAL_NUM01');
664 Add_To_PackageTable (' , SQUAL_NUM02');
665 Add_To_PackageTable (' , SQUAL_NUM03');
666 Add_To_PackageTable (' , SQUAL_NUM04');
667 Add_To_PackageTable (' , SQUAL_NUM05');
668 Add_To_PackageTable (' , SQUAL_NUM06');
669 Add_To_PackageTable (' , SQUAL_NUM07');
670 Add_To_PackageTable (' , SQUAL_NUM08');
671 Add_To_PackageTable (' , SQUAL_NUM09');
672 Add_To_PackageTable (' , SQUAL_NUM10');
673 Add_To_PackageTable (' , SQUAL_NUM11');
674 Add_To_PackageTable (' , SQUAL_NUM12');
675 Add_To_PackageTable (' , SQUAL_NUM13');
676 Add_To_PackageTable (' , SQUAL_NUM14');
677 Add_To_PackageTable (' , SQUAL_NUM15');
678 Add_To_PackageTable (' , SQUAL_NUM16');
679 Add_To_PackageTable (' , SQUAL_NUM17');
680 Add_To_PackageTable (' , SQUAL_NUM18');
681 Add_To_PackageTable (' , SQUAL_NUM19');
682 Add_To_PackageTable (' , SQUAL_NUM20');
683 Add_To_PackageTable (' , SQUAL_NUM21');
684 Add_To_PackageTable (' , SQUAL_NUM22');
685 Add_To_PackageTable (' , SQUAL_NUM23');
686 Add_To_PackageTable (' , SQUAL_NUM24');
687 Add_To_PackageTable (' , SQUAL_NUM25');
688 Add_To_PackageTable (' , SQUAL_NUM26');
689 Add_To_PackageTable (' , SQUAL_NUM27');
690 Add_To_PackageTable (' , SQUAL_NUM28');
691 Add_To_PackageTable (' , SQUAL_NUM29');
692 Add_To_PackageTable (' , SQUAL_NUM30');
693 Add_To_PackageTable (' , SQUAL_NUM31');
694 Add_To_PackageTable (' , SQUAL_NUM32');
695 Add_To_PackageTable (' , SQUAL_NUM33');
696 Add_To_PackageTable (' , SQUAL_NUM34');
697 Add_To_PackageTable (' , SQUAL_NUM35');
698 Add_To_PackageTable (' , SQUAL_NUM36');
699 Add_To_PackageTable (' , SQUAL_NUM37');
700 Add_To_PackageTable (' , SQUAL_NUM38');
701 Add_To_PackageTable (' , SQUAL_NUM39');
702 Add_To_PackageTable (' , SQUAL_NUM40');
703 Add_To_PackageTable (' , SQUAL_NUM41');
704 Add_To_PackageTable (' , SQUAL_NUM42');
705 Add_To_PackageTable (' , SQUAL_NUM43');
706 Add_To_PackageTable (' , SQUAL_NUM44');
707 Add_To_PackageTable (' , SQUAL_NUM45');
708 Add_To_PackageTable (' , SQUAL_NUM46');
709 Add_To_PackageTable (' , SQUAL_NUM47');
710 Add_To_PackageTable (' , SQUAL_NUM48');
711 Add_To_PackageTable (' , SQUAL_NUM49');
712 Add_To_PackageTable (' , SQUAL_NUM50');
713 Add_To_PackageTable (' , SQUAL_NUM51');
714 Add_To_PackageTable (' , SQUAL_NUM52');
715 Add_To_PackageTable (' , SQUAL_NUM53');
716 Add_To_PackageTable (' , SQUAL_NUM54');
717 Add_To_PackageTable (' , SQUAL_NUM55');
718 Add_To_PackageTable (' , SQUAL_NUM56');
719 Add_To_PackageTable (' , SQUAL_NUM57');
720 Add_To_PackageTable (' , SQUAL_NUM58');
721 Add_To_PackageTable (' , SQUAL_NUM59');
722 Add_To_PackageTable (' , SQUAL_NUM60');
723 Add_To_PackageTable (' , ASSIGNED_FLAG');
724 Add_To_PackageTable (' , PROCESSED_FLAG');
725 Add_To_PackageTable (' , ORG_ID');
726 Add_To_PackageTable (' , SECURITY_GROUP_ID');
727 Add_To_PackageTable (' , OBJECT_VERSION_NUMBER');
728 Add_To_PackageTable (' , WORKER_ID');
729 Add_To_PackageTable (' )' );
730
731 EXCEPTION
732 WHEN OTHERS THEN
733 g_ProgramStatus := 1;
734 Add_To_PackageTable ('-- Program failed in add_insert_nmtrans procedure ');
735
736 END add_insert_nmtrans;
737
738 /* dblee: 08/15/03 */
739 PROCEDURE add_select_nmtrans(p_match_table_name IN VARCHAR2)
740 AS
741 BEGIN
742
743 Add_To_PackageTable (' SELECT DISTINCT ');
744 Add_To_PackageTable (' A.TRANS_OBJECT_ID');
745 Add_To_PackageTable (' , A.TRANS_DETAIL_OBJECT_ID');
746 Add_To_PackageTable (' , A.HEADER_ID1');
747 Add_To_PackageTable (' , A.HEADER_ID2');
748 Add_To_PackageTable (' , p_source_id');
749 Add_To_PackageTable (' , p_trans_object_type_id');
750 Add_To_PackageTable (' , l_sysdate');
751 Add_To_PackageTable (' , L_USER_ID');
752 Add_To_PackageTable (' , l_sysdate');
753 Add_To_PackageTable (' , L_USER_ID');
754 Add_To_PackageTable (' , L_USER_ID');
755 Add_To_PackageTable (' , L_REQUEST_ID');
756 Add_To_PackageTable (' , L_PROGRAM_APPL_ID');
757 Add_To_PackageTable (' , L_PROGRAM_ID');
758 Add_To_PackageTable (' , l_sysdate');
759 Add_To_PackageTable (' , A.SQUAL_FC01');
760 Add_To_PackageTable (' , A.SQUAL_FC02');
761 Add_To_PackageTable (' , A.SQUAL_FC03');
762 Add_To_PackageTable (' , A.SQUAL_FC04');
763 Add_To_PackageTable (' , A.SQUAL_FC05');
764 Add_To_PackageTable (' , A.SQUAL_CURC01');
765 Add_To_PackageTable (' , A.SQUAL_CURC02');
766 Add_To_PackageTable (' , A.SQUAL_CURC03');
767 Add_To_PackageTable (' , A.SQUAL_CURC04');
768 Add_To_PackageTable (' , A.SQUAL_CURC05');
769 Add_To_PackageTable (' , A.SQUAL_CURC06');
770 Add_To_PackageTable (' , A.SQUAL_CURC07');
771 Add_To_PackageTable (' , A.SQUAL_CURC08');
772 Add_To_PackageTable (' , A.SQUAL_CURC09');
773 Add_To_PackageTable (' , A.SQUAL_CURC10');
774 Add_To_PackageTable (' , A.SQUAL_CHAR01');
775 Add_To_PackageTable (' , A.SQUAL_CHAR02');
776 Add_To_PackageTable (' , A.SQUAL_CHAR03');
777 Add_To_PackageTable (' , A.SQUAL_CHAR04');
778 Add_To_PackageTable (' , A.SQUAL_CHAR05');
779 Add_To_PackageTable (' , A.SQUAL_CHAR06');
780 Add_To_PackageTable (' , A.SQUAL_CHAR07');
781 Add_To_PackageTable (' , A.SQUAL_CHAR08');
782 Add_To_PackageTable (' , A.SQUAL_CHAR09');
783 Add_To_PackageTable (' , A.SQUAL_CHAR10');
784 Add_To_PackageTable (' , A.SQUAL_CHAR11');
785 Add_To_PackageTable (' , A.SQUAL_CHAR12');
786 Add_To_PackageTable (' , A.SQUAL_CHAR13');
787 Add_To_PackageTable (' , A.SQUAL_CHAR14');
788 Add_To_PackageTable (' , A.SQUAL_CHAR15');
789 Add_To_PackageTable (' , A.SQUAL_CHAR16');
790 Add_To_PackageTable (' , A.SQUAL_CHAR17');
791 Add_To_PackageTable (' , A.SQUAL_CHAR18');
792 Add_To_PackageTable (' , A.SQUAL_CHAR19');
793 Add_To_PackageTable (' , A.SQUAL_CHAR20');
794 Add_To_PackageTable (' , A.SQUAL_CHAR21');
795 Add_To_PackageTable (' , A.SQUAL_CHAR22');
796 Add_To_PackageTable (' , A.SQUAL_CHAR23');
797 Add_To_PackageTable (' , A.SQUAL_CHAR24');
798 Add_To_PackageTable (' , A.SQUAL_CHAR25');
799 Add_To_PackageTable (' , A.SQUAL_CHAR26');
800 Add_To_PackageTable (' , A.SQUAL_CHAR27');
801 Add_To_PackageTable (' , A.SQUAL_CHAR28');
802 Add_To_PackageTable (' , A.SQUAL_CHAR30');
803 Add_To_PackageTable (' , A.SQUAL_CHAR31');
804 Add_To_PackageTable (' , A.SQUAL_CHAR32');
805 Add_To_PackageTable (' , A.SQUAL_CHAR33');
806 Add_To_PackageTable (' , A.SQUAL_CHAR34');
807 Add_To_PackageTable (' , A.SQUAL_CHAR35');
808 Add_To_PackageTable (' , A.SQUAL_CHAR36');
809 Add_To_PackageTable (' , A.SQUAL_CHAR37');
810 Add_To_PackageTable (' , A.SQUAL_CHAR38');
811 Add_To_PackageTable (' , A.SQUAL_CHAR39');
812 Add_To_PackageTable (' , A.SQUAL_CHAR40');
813 Add_To_PackageTable (' , A.SQUAL_CHAR41');
814 Add_To_PackageTable (' , A.SQUAL_CHAR42');
815 Add_To_PackageTable (' , A.SQUAL_CHAR43');
816 Add_To_PackageTable (' , A.SQUAL_CHAR44');
817 Add_To_PackageTable (' , A.SQUAL_CHAR45');
818 Add_To_PackageTable (' , A.SQUAL_CHAR46');
819 Add_To_PackageTable (' , A.SQUAL_CHAR47');
820 Add_To_PackageTable (' , A.SQUAL_CHAR48');
821 Add_To_PackageTable (' , A.SQUAL_CHAR49');
822 Add_To_PackageTable (' , A.SQUAL_CHAR50');
823 Add_To_PackageTable (' , A.SQUAL_CHAR51');
824 Add_To_PackageTable (' , A.SQUAL_CHAR52');
825 Add_To_PackageTable (' , A.SQUAL_CHAR53');
826 Add_To_PackageTable (' , A.SQUAL_CHAR54');
827 Add_To_PackageTable (' , A.SQUAL_CHAR55');
828 Add_To_PackageTable (' , A.SQUAL_CHAR56');
829 Add_To_PackageTable (' , A.SQUAL_CHAR57');
830 Add_To_PackageTable (' , A.SQUAL_CHAR58');
831 Add_To_PackageTable (' , A.SQUAL_CHAR59');
832 Add_To_PackageTable (' , A.SQUAL_CHAR60');
833 Add_To_PackageTable (' , A.SQUAL_NUM01');
834 Add_To_PackageTable (' , A.SQUAL_NUM02');
835 Add_To_PackageTable (' , A.SQUAL_NUM03');
836 Add_To_PackageTable (' , A.SQUAL_NUM04');
837 Add_To_PackageTable (' , A.SQUAL_NUM05');
838 Add_To_PackageTable (' , A.SQUAL_NUM06');
839 Add_To_PackageTable (' , A.SQUAL_NUM07');
840 Add_To_PackageTable (' , A.SQUAL_NUM08');
841 Add_To_PackageTable (' , A.SQUAL_NUM09');
842 Add_To_PackageTable (' , A.SQUAL_NUM10');
843 Add_To_PackageTable (' , A.SQUAL_NUM11');
844 Add_To_PackageTable (' , A.SQUAL_NUM12');
845 Add_To_PackageTable (' , A.SQUAL_NUM13');
846 Add_To_PackageTable (' , A.SQUAL_NUM14');
847 Add_To_PackageTable (' , A.SQUAL_NUM15');
848 Add_To_PackageTable (' , A.SQUAL_NUM16');
849 Add_To_PackageTable (' , A.SQUAL_NUM17');
850 Add_To_PackageTable (' , A.SQUAL_NUM18');
851 Add_To_PackageTable (' , A.SQUAL_NUM19');
852 Add_To_PackageTable (' , A.SQUAL_NUM20');
853 Add_To_PackageTable (' , A.SQUAL_NUM21');
854 Add_To_PackageTable (' , A.SQUAL_NUM22');
855 Add_To_PackageTable (' , A.SQUAL_NUM23');
856 Add_To_PackageTable (' , A.SQUAL_NUM24');
857 Add_To_PackageTable (' , A.SQUAL_NUM25');
858 Add_To_PackageTable (' , A.SQUAL_NUM26');
859 Add_To_PackageTable (' , A.SQUAL_NUM27');
860 Add_To_PackageTable (' , A.SQUAL_NUM28');
861 Add_To_PackageTable (' , A.SQUAL_NUM29');
862 Add_To_PackageTable (' , A.SQUAL_NUM30');
863 Add_To_PackageTable (' , A.SQUAL_NUM31');
864 Add_To_PackageTable (' , A.SQUAL_NUM32');
865 Add_To_PackageTable (' , A.SQUAL_NUM33');
866 Add_To_PackageTable (' , A.SQUAL_NUM34');
867 Add_To_PackageTable (' , A.SQUAL_NUM35');
868 Add_To_PackageTable (' , A.SQUAL_NUM36');
869 Add_To_PackageTable (' , A.SQUAL_NUM37');
870 Add_To_PackageTable (' , A.SQUAL_NUM38');
871 Add_To_PackageTable (' , A.SQUAL_NUM39');
872 Add_To_PackageTable (' , A.SQUAL_NUM40');
873 Add_To_PackageTable (' , A.SQUAL_NUM41');
874 Add_To_PackageTable (' , A.SQUAL_NUM42');
875 Add_To_PackageTable (' , A.SQUAL_NUM43');
876 Add_To_PackageTable (' , A.SQUAL_NUM44');
877 Add_To_PackageTable (' , A.SQUAL_NUM45');
878 Add_To_PackageTable (' , A.SQUAL_NUM46');
879 Add_To_PackageTable (' , A.SQUAL_NUM47');
880 Add_To_PackageTable (' , A.SQUAL_NUM48');
881 Add_To_PackageTable (' , A.SQUAL_NUM49');
882 Add_To_PackageTable (' , A.SQUAL_NUM50');
883 Add_To_PackageTable (' , A.SQUAL_NUM51');
884 Add_To_PackageTable (' , A.SQUAL_NUM52');
885 Add_To_PackageTable (' , A.SQUAL_NUM53');
886 Add_To_PackageTable (' , A.SQUAL_NUM54');
887 Add_To_PackageTable (' , A.SQUAL_NUM55');
888 Add_To_PackageTable (' , A.SQUAL_NUM56');
889 Add_To_PackageTable (' , A.SQUAL_NUM57');
890 Add_To_PackageTable (' , A.SQUAL_NUM58');
891 Add_To_PackageTable (' , A.SQUAL_NUM59');
892 Add_To_PackageTable (' , A.SQUAL_NUM60');
893 Add_To_PackageTable (' , A.ASSIGNED_FLAG');
894 Add_To_PackageTable (' , A.PROCESSED_FLAG');
895 Add_To_PackageTable (' , A.ORG_ID');
896 Add_To_PackageTable (' , A.SECURITY_GROUP_ID');
897 Add_To_PackageTable (' , A.OBJECT_VERSION_NUMBER');
898 Add_To_PackageTable (' , A.WORKER_ID');
899 Add_To_PackageTable (' FROM ');
900
901 EXCEPTION
902 WHEN OTHERS THEN
903 g_ProgramStatus := 1;
904 Add_To_PackageTable ('-- Program failed in add_select_nmtrans procedure ');
905
906 END add_select_nmtrans;
907
908
909 PROCEDURE write_buffer_content(
910 l_qual_rules VARCHAR2
911 )
912 IS
913 l_str_len NUMBER;
914 l_start NUMBER;
915 l_get_nchar NUMBER;
916 l_next_newline NUMBER;
917 l_rule_str VARCHAR2(256);
918 l_newline VARCHAR2(2);
919 l_indent VARCHAR2(30);
920
921 BEGIN
922
923 l_newline := FND_GLOBAL.Local_Chr(10); /* newline character */
924 l_indent := ' ';
925 l_start := 1;
926 l_next_newline := 0;
927
928 WHILE (TRUE) LOOP
929
930 l_next_newline := INSTR(l_qual_rules, l_newline, l_start, 1);
931
932 IF l_next_newline = 0 THEN
933 /* no new line characters => end of string */
934 l_get_nchar := l_str_len;
935
936 ELSE
937 /* set of characters up to next newline */
938 l_get_nchar := l_next_newline - l_start;
939 END IF;
940
941 l_rule_str := substr(l_qual_rules, l_start, l_get_nchar);
942
943 --dbms_output.put_line(l_rule_str);
944 Add_To_PackageTable(l_indent || l_rule_str);
945
946 EXIT WHEN l_next_newline = 0;
947
948 l_rule_str := NULL;
949 l_start := l_next_newline + 1;
950
951 END LOOP;
952
953 EXCEPTION
954 WHEN OTHERS THEN
955 g_ProgramStatus := 1;
956 Add_To_PackageTable ('-- Program failed in write_buffer_content ');
957 Add_To_PackageTable (substr(sqlerrm,1,200));
958
959 END write_buffer_content;
960
961 PROCEDURE build_ilv1(
962 p_source_id IN NUMBER,
963 p_qual_type_id IN NUMBER,
964 p_relation_product IN NUMBER,
965 p_relation_factor IN NUMBER,
966 -- dblee 08/26/03 added p_new_mode_fetch flag argument
967 p_new_mode_fetch IN CHAR,
968 p_ilv1sql OUT NOCOPY VARCHAR2)
969 IS
970 l_from_str VARCHAR2(32767);
971 l_where_str VARCHAR2(32767);
972 l_predicate VARCHAR2(32767);
973 l_select VARCHAR2(32767);
974
975 l_newline VARCHAR2(2);
976 l_idx_hint VARCHAR2(255);
977
978 CURSOR c_rel_prod_detail IS
979 SELECT distinct jtqp.relation_product
980 ,jtqf.qual_usg_id
981 ,jqu.alias_rule1
982 ,jqu.alias_op_like
983 ,jqu.alias_op_between
984 ,jqu.op_eql
985 ,jqu.op_not_eql
986 ,jqu.op_lss_thn
987 ,jqu.op_lss_thn_eql
988 ,jqu.op_grtr_thn
989 ,jqu.op_grtr_thn_eql
990 ,jqu.op_like
991 ,jqu.op_not_like
992 ,jqu.op_between
993 ,jqu.op_not_between
994 ,jqu.op_common_where
995 FROM jtf_qual_usgs_all jqu,
996 jtf_tae_qual_factors jtqf,
997 jtf_tae_qual_products jtqp,
998 jtf_tae_qual_prod_factors jtpf
999 WHERE jqu.org_id = -3113
1000 AND jqu.qual_usg_id = jtqf.qual_usg_id
1001 and jtpf.qual_factor_id = jtqf.qual_factor_id
1002 and jtqf.relation_factor = p_relation_factor
1003 and jtqp.qual_product_id = jtpf.qual_product_id
1004 and jtqp.relation_product = p_relation_product
1005 and jtqp.source_id = p_source_id
1006 and jtqp.trans_object_type_id = p_qual_type_id
1007 and jqu.op_not_eql IS NULL
1008 and jqu.op_not_like IS NULL
1009 and jqu.op_not_between IS NULL
1010 ORDER BY jtqf.qual_usg_id;
1011
1012 BEGIN
1013
1014 l_newline := FND_GLOBAL.Local_Chr(10);
1015
1016 FOR JTF_csr IN c_rel_prod_detail LOOP
1017
1018 --l_idx_hint := '/*+ INDEX(' || JTF_csr.alias_rule1 ||' JTF_TERR_QUAL_RULES_MV_N10) */';
1019
1020 IF mod(JTF_csr.relation_product,79) = 0 THEN
1021 l_select := G_INDENT || 'SELECT ' || l_idx_hint || G_NEWLINE ||
1022 G_INDENT || ' AI.customer_id' || l_newline ||
1023 G_INDENT || ' , AI.address_id' || l_newline;
1024 ELSIF mod(JTF_csr.relation_product,137) = 0 THEN
1025 l_select := G_INDENT || 'SELECT ' || l_idx_hint || G_NEWLINE ||
1026 G_INDENT || ' ASLLP.sales_lead_id' || l_newline ||
1027 G_INDENT || ' , ASLLP.sales_lead_line_id' || l_newline;
1028 ELSIF mod(JTF_csr.relation_product,113) = 0 THEN
1029 l_select := G_INDENT || 'SELECT ' || l_idx_hint || G_NEWLINE ||
1030 G_INDENT || ' ASLL.sales_lead_id' || l_newline ||
1031 G_INDENT || ' , ASLL.sales_lead_line_id' || l_newline;
1032 ELSIF mod(JTF_csr.relation_product,131) = 0 THEN
1033 l_select := G_INDENT || 'SELECT ' || l_idx_hint || G_NEWLINE ||
1034 G_INDENT || ' ASLLI.sales_lead_id' || l_newline ||
1035 G_INDENT || ' , ASLLI.sales_lead_line_id' || l_newline;
1036 ELSIF mod(JTF_csr.relation_product,139) = 0 THEN
1037 l_select := G_INDENT || 'SELECT ' || l_idx_hint || G_NEWLINE ||
1038 G_INDENT || ' ALLP.lead_id' || l_newline ||
1039 G_INDENT || ' , ALLP.lead_line_id' || l_newline;
1040 ELSIF mod(JTF_csr.relation_product,163) = 0 THEN
1041 l_select := G_INDENT || 'SELECT ' || l_idx_hint || G_NEWLINE ||
1042 G_INDENT || ' ALLI.lead_id' || l_newline ||
1043 G_INDENT || ' , ALLI.lead_line_id' || l_newline;
1044 ELSIF mod(JTF_csr.relation_product,167) = 0 THEN
1045 l_select := G_INDENT || 'SELECT ' || l_idx_hint || G_NEWLINE ||
1046 G_INDENT || ' OAI.lead_id' || l_newline ;
1047 --G_INDENT || ' , ALLI.lead_line_id' || l_newline;
1048 END IF;
1049
1050 l_select := l_select ||
1051 G_INDENT || ' , ILV.terr_id ' || l_newline ||
1052 G_INDENT || ' , ILV.top_level_terr_id ' || l_newline ||
1053 G_INDENT || ' , ILV.absolute_rank ' || l_newline ||
1054 G_INDENT || ' , ILV.num_winners ' || l_newline ||
1055 G_INDENT || ' , ILV.org_id ' || l_newline;
1056
1057 l_from_str := G_INDENT || 'FROM ' || JTF_csr.alias_rule1 ;
1058
1059 -- dblee/eihsu: 08/15/03 added p_new_mode_fetch flag
1060 l_from_str := append_inlineview(l_from_str, p_new_mode_fetch);
1061
1062 l_where_str := l_newline || G_INDENT || 'WHERE 1 = 1 ' ;
1063 -- eihsu: worker_id added 06/09/2003
1064 l_where_str := l_where_str || l_newline || '--AND a.worker_id = p_worker_id ';
1065
1066 l_predicate := l_newline
1067 || build_predicate_for_operator(JTF_csr.op_common_where
1068 ,JTF_csr.op_eql
1069 ,JTF_csr.op_lss_thn
1070 ,JTF_csr.op_lss_thn_eql
1071 ,JTF_csr.op_grtr_thn
1072 ,JTF_csr.op_grtr_thn_eql
1073 ,JTF_csr.op_like
1074 ,JTF_csr.op_between
1075 ,l_newline);
1076
1077 IF mod(JTF_csr.relation_product,79) = 0 THEN
1078 l_predicate := replace(l_predicate,'( A.SQUAL_NUM02 IS NULL AND AI.address_id IS NULL )','');
1079 l_predicate := replace(l_predicate,'OR ( A.SQUAL_NUM02 = AI.address_id )' , '1=1');
1080 l_predicate := replace(l_predicate,'A.SQUAL_NUM01 = AI.customer_id','1=1');
1081 ELSIF mod(JTF_csr.relation_product,137) = 0 THEN
1082 l_predicate := replace(l_predicate,'A.TRANS_OBJECT_ID = ASLLP.SALES_LEAD_ID','1=1');
1083 ELSIF mod(JTF_csr.relation_product,113) = 0 THEN
1084 l_predicate := replace(l_predicate,'ASLL.SALES_LEAD_ID = A.TRANS_OBJECT_ID','1=1');
1085 l_predicate := replace(l_predicate,'a.squal_curc03 = Q1022R1.currency_code','1=1');
1086 ELSIF mod(JTF_csr.relation_product,131) = 0 THEN
1087 l_predicate := replace(l_predicate,'A.TRANS_OBJECT_ID = ASLLI.SALES_LEAD_ID','1=1');
1088 ELSIF mod(JTF_csr.relation_product,139) = 0 THEN
1089 l_predicate := replace(l_predicate,'A.TRANS_OBJECT_ID = ALLP.LEAD_ID','1=1');
1090 ELSIF mod(JTF_csr.relation_product,163) = 0 THEN
1091 l_predicate := replace(l_predicate,'A.TRANS_OBJECT_ID = ALLI.LEAD_ID','1=1');
1092 ELSIF mod(JTF_csr.relation_product,167) = 0 THEN
1093 l_predicate := replace(l_predicate,'A.TRANS_OBJECT_ID = OAI.LEAD_ID','1=1');
1094 END IF;
1095
1096 p_ilv1sql :=
1097 l_select || l_newline ||
1098 l_from_str || l_newline ||
1099 l_where_str || l_newline ||
1100 l_predicate;
1101
1102 EXIT;
1103 END LOOP;
1104
1105 EXCEPTION
1106 WHEN OTHERS THEN
1107 g_ProgramStatus := 1;
1108 Add_To_PackageTable ('-- Program failed in build_ilv1 ');
1109
1110 END build_ilv1;
1111
1112 PROCEDURE build_ilv2(
1113 p_source_id IN NUMBER,
1114 p_qual_type_id IN NUMBER,
1115 p_relation_product IN NUMBER,
1116 p_relation_factor IN NUMBER,
1117 p_input_table_name IN VARCHAR2,
1118 -- dblee/eihsu: 08/15/03 added p_new_mode_fetch flag
1119 p_new_mode_fetch IN CHAR,
1120 p_sql OUT NOCOPY VARCHAR2,
1121 p_ilv2eq OUT NOCOPY VARCHAR2,
1122 p_ilv2lk OUT NOCOPY VARCHAR2,
1123 p_ilv2lkp OUT NOCOPY VARCHAR2,
1124 p_ilv2btwn OUT NOCOPY VARCHAR2)
1125 AS
1126
1127 CURSOR c_rel_prod_detail
1128 IS
1129 SELECT DISTINCT jtqp.relation_product
1130 ,jtqf.qual_usg_id
1131 ,jqu.alias_rule1
1132 ,jqu.alias_op_like
1133 ,jqu.alias_op_between
1134 ,jqu.op_eql
1135 ,jqu.op_not_eql
1136 ,jqu.op_lss_thn
1137 ,jqu.op_lss_thn_eql
1138 ,jqu.op_grtr_thn
1139 ,jqu.op_grtr_thn_eql
1140 ,jqu.op_like
1141 ,jqu.op_not_like
1142 ,jqu.op_between
1143 ,jqu.op_not_between
1144 ,jqu.op_common_where
1145 FROM jtf_qual_usgs_all jqu,
1146 jtf_tae_qual_factors jtqf,
1147 jtf_tae_qual_products jtqp,
1148 jtf_tae_qual_prod_factors jtpf
1149 WHERE jqu.org_id = -3113
1150 AND jqu.qual_usg_id = jtqf.qual_usg_id
1151 AND jtpf.qual_factor_id= jtqf.qual_factor_id
1152 AND jtqf.relation_factor <> p_relation_factor
1153 AND jtqp.qual_product_id = jtpf.qual_product_id
1154 AND jtqp.relation_product = p_relation_product
1155 AND jtqp.source_id = p_source_id
1156 AND jtqp.trans_object_type_id= p_qual_type_id
1157 AND jqu.op_not_eql IS NULL
1158 AND jqu.op_not_like IS NULL
1159 AND jqu.op_not_between IS NULL
1160 ORDER BY jtqf.qual_usg_id;
1161
1162 l_qual_usg_id NUMBER;
1163 l_qual_rules VARCHAR2(32767);
1164 l_rule VARCHAR2(32767);
1165 l_counter NUMBER := 1;
1166 l_newline VARCHAR2(2);
1167 l_indent VARCHAR2(30);
1168 l_sysdate DATE;
1169 l_str_len NUMBER;
1170 l_start NUMBER;
1171 l_get_nchar NUMBER;
1172 l_next_newline NUMBER;
1173 l_rule_str VARCHAR2(256);
1174 l_from_str VARCHAR2(32767);
1175 l_from_str_eq VARCHAR2(32767);
1176 l_from_str_like VARCHAR2(32767);
1177 l_from_str_btw VARCHAR2(32767);
1178 l_where_str VARCHAR2(32767);
1179 l_predicate VARCHAR2(32767);
1180 l_predicate_eq VARCHAR2(32767);
1181 l_predicate_like VARCHAR2(32767);
1182 l_predicate_btw VARCHAR2(32767);
1183 l_select_eq VARCHAR2(32767);
1184 l_select_like VARCHAR2(32767);
1185 l_select_btw VARCHAR2(32767);
1186 l_ilv2eq VARCHAR2(32767);
1187 l_ilv2lk VARCHAR2(32767);
1188 l_ilv2lkp VARCHAR2(32767);
1189 l_ilv2btwn VARCHAR2(32767);
1190 l_relation_product number;
1191
1192 BEGIN
1193
1194 --dbms_output.put_line('Inside build_rule_expression ');
1195 l_relation_product := p_relation_product/p_relation_factor;
1196
1197 l_newline := FND_GLOBAL.Local_Chr(10); /* newline character */
1198 l_sysdate := SYSDATE;
1199
1200 FOR JTF_csr IN c_rel_prod_detail LOOP
1201
1202 IF mod(l_relation_product,67) <> 0 and mod(l_relation_product,73) <> 0 THEN
1203 IF l_counter = 1 THEN
1204 l_from_str := l_newline || G_INDENT || ' FROM '|| p_input_table_name || ' A' || l_newline
1205 || ',' || JTF_csr.alias_rule1;
1206 l_where_str := l_newline || G_INDENT || 'WHERE 1 = 1' ;
1207 -- eihsu: worker_id added 06/09/2003
1208 -- bug # 4213107 : worker_id condition should not be added to NMC packages
1209 IF (p_new_mode_fetch <> 'Y') THEN
1210 l_where_str := l_where_str || l_newline || 'AND a.worker_id = p_worker_id ';
1211 END IF;
1212 l_predicate := l_newline
1213 || build_predicate_for_operator(JTF_csr.op_common_where
1214 ,JTF_csr.op_eql
1215 ,JTF_csr.op_lss_thn
1216 ,JTF_csr.op_lss_thn_eql
1217 ,JTF_csr.op_grtr_thn
1218 ,JTF_csr.op_grtr_thn_eql
1219 ,JTF_csr.op_like
1220 ,JTF_csr.op_between
1221 ,l_newline);
1222 ELSE -- l_counter <> 1
1223 l_from_str := l_from_str || l_newline || ',' || JTF_csr.alias_rule1;
1224 l_predicate := l_predicate || l_newline
1225 || build_predicate_for_operator(JTF_csr.op_common_where
1226 ,JTF_csr.op_eql
1227 ,JTF_csr.op_lss_thn
1228 ,JTF_csr.op_lss_thn_eql
1229 ,JTF_csr.op_grtr_thn
1230 ,JTF_csr.op_grtr_thn_eql
1231 ,JTF_csr.op_like
1232 ,JTF_csr.op_between
1233 ,l_newline);
1234 END IF;
1235 ELSE
1236 IF (l_counter = 1) THEN
1237 IF (JTF_csr.qual_usg_id = -1012 or JTF_csr.qual_usg_id = -1102) THEN
1238 -- dblee: 08/20/03 replaced select clause literal w/ g_select_list_1 variable
1239 l_select_eq := l_newline || g_select_list_1;
1240 l_select_like := l_newline || g_select_list_1;
1241 l_select_btw := l_newline || g_select_list_1;
1242 /* bug 3835831 */
1243 IF ((mod(p_relation_product, 79) = 0) AND (p_new_mode_fetch <> 'Y')) THEN
1244 l_select_eq := l_select_eq || ' ,A.SQUAL_NUM01, A.SQUAL_NUM02 ';
1245 l_select_like := l_select_like || ' ,A.SQUAL_NUM01, A.SQUAL_NUM02 ';
1246 l_select_btw := l_select_btw || ' ,A.SQUAL_NUM01, A.SQUAL_NUM02 ';
1247 END IF;
1248
1249 l_from_str_eq := l_newline || 'FROM '|| p_input_table_name ||' A' || l_newline ||
1250 ',' || JTF_csr.alias_rule1;
1251
1252 l_from_str_like := l_newline || 'FROM '|| p_input_table_name ||' A'|| l_newline ||
1253 ',' || JTF_csr.alias_op_like;
1254
1255 l_from_str_btw := l_newline || 'FROM '|| p_input_table_name ||' A' || l_newline ||
1256 ',' || JTF_csr.alias_op_between;
1257
1258 l_where_str := l_newline || 'WHERE 1 = 1 ' ;
1259 -- eihsu: worker_id added 06/09/2003
1260 -- bug # 4213107 : worker_id condition should not be added to NMC packages
1261 IF (p_new_mode_fetch <> 'Y') THEN
1262 l_where_str := l_where_str || l_newline || 'AND a.worker_id = p_worker_id ';
1263 END IF;
1264
1265 l_predicate_eq := l_newline || 'AND ' || JTF_csr.op_eql;
1266 l_predicate_like := l_newline ||'AND ' || JTF_csr.op_like;
1267 l_predicate_btw := l_newline || 'AND ' || JTF_csr.op_between;
1268 ELSE -- JTF_csr.qual_usg_id NOT IN (-1012, -1102)
1269 l_from_str_eq := l_newline || 'FROM '|| p_input_table_name ||' A' || l_newline
1270 || ',' || JTF_csr.alias_rule1;
1271 l_where_str := l_newline || 'WHERE 1 = 1' ;
1272 -- eihsu: worker_id added 06/09/2003
1273 -- bug # 4213107 : worker_id condition should not be added to NMC packages
1274 IF (p_new_mode_fetch <> 'Y') THEN
1275 l_where_str := l_where_str || l_newline || 'AND a.worker_id = p_worker_id ';
1276 END IF;
1277 l_predicate_eq := l_newline
1278 || build_predicate_for_operator(JTF_csr.op_common_where
1279 ,JTF_csr.op_eql
1280 ,JTF_csr.op_lss_thn
1281 ,JTF_csr.op_lss_thn_eql
1282 ,JTF_csr.op_grtr_thn
1283 ,JTF_csr.op_grtr_thn_eql
1284 ,JTF_csr.op_like
1285 ,JTF_csr.op_between
1286 ,l_newline);
1287 END IF; -- JTF_csr.qual_usg_id = -1012 or JTF_csr.qual_usg_id = -1102
1288
1289 ELSE /* counter > 1*/
1290 IF (JTF_csr.qual_usg_id = -1012 or JTF_csr.qual_usg_id = -1102) THEN
1291 -- dblee: 08/20/03 replaced select clause literal w/ g_select_list_1 variable
1292 l_select_eq := l_newline || g_select_list_1;
1293 l_select_like := l_newline || g_select_list_1;
1294 l_select_btw := l_newline || g_select_list_1;
1295 /* bug 3835831 */
1296 IF ((mod(p_relation_product, 79) = 0) AND (p_new_mode_fetch <> 'Y')) THEN
1297 l_select_eq := l_select_eq || ' ,A.SQUAL_NUM01, A.SQUAL_NUM02 ';
1298 l_select_like := l_select_like || ' ,A.SQUAL_NUM01, A.SQUAL_NUM02 ';
1299 l_select_btw := l_select_btw || ' ,A.SQUAL_NUM01, A.SQUAL_NUM02 ';
1300 END IF;
1301
1302 l_from_str_like := l_from_str_eq || l_newline || ',' || JTF_csr.alias_op_like;
1303 l_from_str_btw := l_from_str_eq || l_newline || ',' || JTF_csr.alias_op_between;
1304 l_from_str_eq := l_from_str_eq ||l_newline || ',' || JTF_csr.alias_rule1;
1305
1306 l_where_str := l_newline || 'WHERE 1 = 1' ;
1307 -- eihsu: worker_id added 06/09/2003
1308 -- bug # 4213107 : worker_id condition should not be added to NMC packages
1309 IF (p_new_mode_fetch <> 'Y') THEN
1310 l_where_str := l_where_str || l_newline || 'AND a.worker_id = p_worker_id ';
1311 END IF;
1312 /* sbehera added AND 05/02/2002 */
1313
1314 l_predicate_like := l_predicate_eq || l_newline || 'AND '|| JTF_csr.op_like;
1315 l_predicate_btw := l_predicate_eq || l_newline || 'AND '|| JTF_csr.op_between;
1316 l_predicate_eq := l_predicate_eq || l_newline || 'AND '|| JTF_csr.op_eql;
1317
1318 ELSE -- JTF_csr.qual_usg_id not in (-1012 or -1102)
1319 l_from_str_eq := l_from_str_eq || l_newline || ',' || JTF_csr.alias_rule1;
1320 l_from_str_like := l_from_str_like || l_newline || ',' || JTF_csr.alias_rule1;
1321 l_from_str_btw := l_from_str_btw || l_newline || ',' || JTF_csr.alias_rule1;
1322
1323 l_predicate_eq := l_predicate_eq || l_newline
1324 || build_predicate_for_operator(JTF_csr.op_common_where
1325 ,JTF_csr.op_eql
1326 ,JTF_csr.op_lss_thn
1327 ,JTF_csr.op_lss_thn_eql
1328 ,JTF_csr.op_grtr_thn
1329 ,JTF_csr.op_grtr_thn_eql
1330 ,JTF_csr.op_like
1331 ,JTF_csr.op_between
1332 ,l_newline);
1333
1334 l_predicate_like := l_predicate_like || l_newline
1335 || build_predicate_for_operator(JTF_csr.op_common_where
1336 ,JTF_csr.op_eql
1337 ,JTF_csr.op_lss_thn
1338 ,JTF_csr.op_lss_thn_eql
1339 ,JTF_csr.op_grtr_thn
1340 ,JTF_csr.op_grtr_thn_eql
1341 ,JTF_csr.op_like
1342 ,JTF_csr.op_between
1343 ,l_newline);
1344
1345 l_predicate_btw := l_predicate_btw || l_newline
1346 || build_predicate_for_operator(JTF_csr.op_common_where
1347 ,JTF_csr.op_eql
1348 ,JTF_csr.op_lss_thn
1349 ,JTF_csr.op_lss_thn_eql
1350 ,JTF_csr.op_grtr_thn
1351 ,JTF_csr.op_grtr_thn_eql
1352 ,JTF_csr.op_like
1353 ,JTF_csr.op_between
1354 ,l_newline);
1355 END IF; -- JTF_csr.qual_usg_id = -1012 or JTF_csr.qual_usg_id = -1102
1356
1357 END IF; /* counter */
1358
1359 END IF; /* mod */
1360
1361 l_counter := l_counter + 1;
1362 END LOOP;
1363
1364 /* for account classification we need to add AS_INTERESTS in the from clause */
1365 IF mod(l_relation_product,79) = 0 and mod(l_relation_product,67) <> 0 THEN
1366 l_from_str := l_from_str || l_newline; /* jdochert 04/24/02 ||', AS_INTERESTS ai ' ;*/
1367 ELSIF mod(l_relation_product,79) = 0 and mod(l_relation_product,67) = 0 THEN
1368 l_from_str := l_from_str || l_newline; /* jdochert 04/24/02 ||', AS_INTERESTS ai ' ;*/
1369 l_from_str_like := l_from_str_like || l_newline; /* jdochert 04/24/02 ||', AS_INTERESTS ai ' ;*/
1370 l_from_str_btw := l_from_str_btw || l_newline; /* jdochert 04/24/02 ||', AS_INTERESTS ai ' ;*/
1371 END IF;
1372
1373 /* for lead expected purchase we need to add AS_SALES_LEAD_LINES in the from clause */
1374 IF mod(l_relation_product,137) = 0 and mod(l_relation_product,67) <> 0 THEN
1375 l_from_str := l_from_str || l_newline; /* jdochert 04/24/02 ||', AS_SALES_LEAD_LINES asl ' ; */
1376 ELSIF mod(l_relation_product,137) = 0 and mod(l_relation_product,67) = 0 THEN
1377 l_from_str := l_from_str || l_newline; /* jdochert 04/24/02 ||', AS_SALES_LEAD_LINES asl ' ; */
1378 l_from_str_like := l_from_str_like || l_newline; /* jdochert 04/24/02 ||', AS_SALES_LEAD_LINES asl ' ; */
1379 l_from_str_btw := l_from_str_btw || l_newline; /* jdochert 04/24/02 ||', AS_SALES_LEAD_LINES asl ' ; */
1380 END IF;
1381
1382 /* for opportunity expected purchase we need to add AS_SALES_LEAD_LINES in the from clause */
1383 IF mod(l_relation_product,139) = 0 and mod(l_relation_product,67) <> 0 THEN
1384 l_from_str := l_from_str || l_newline; /* jdochert 04/24/02 ||', AS_LEAD_LINES al ' ; */
1385 ELSIF mod(l_relation_product,139) = 0 and mod(l_relation_product,67) = 0 THEN
1386 l_from_str := l_from_str || l_newline; /* jdochert 04/24/02 ||', AS_LEAD_LINES al ' ; */
1387 l_from_str_like := l_from_str_like || l_newline; /* jdochert 04/24/02 ||', AS_LEAD_LINES al ' ; */
1388 l_from_str_btw := l_from_str_btw || l_newline; /* jdochert 04/24/02 ||', AS_LEAD_LINES al ' ; */
1389 END IF;
1390
1391 /* construct union statement for qual_usg_id=-1012 */
1392 IF mod(l_relation_product, 67) = 0 or mod(l_relation_product, 73) = 0 THEN
1393 -- dblee/eihsu: 08/15/03 added p_new_mode_fetch flag
1394 l_from_str_eq := append_inlineview(l_from_str_eq, p_new_mode_fetch);
1395 l_from_str_like := append_inlineview(l_from_str_like, p_new_mode_fetch);
1396 l_from_str_btw := append_inlineview(l_from_str_btw, p_new_mode_fetch);
1397
1398 IF l_relation_product <> 324347 THEN
1399 l_qual_rules :=
1400 l_select_eq || l_newline ||
1401 l_from_str_eq || l_newline ||
1402 l_where_str || l_newline ||
1403 l_predicate_eq || l_newline ||
1404 'UNION ALL' || l_newline ||
1405 l_select_like || l_newline ||
1406 l_from_str_like || l_newline ||
1407 l_where_str || l_newline
1408 || l_predicate_like || l_newline ||
1409 'UNION ALL' || l_newline ||
1410 l_select_like || l_newline ||
1411 l_from_str_like || l_newline ||
1412 l_where_str || l_newline ||
1413 replace(l_predicate_like,'a.squal_fc01 = Q1012LK.first_char',
1414 'Q1012LK.first_char= ''%''') || l_newline ||
1415 'UNION ALL' || l_newline ||
1416 l_select_btw || l_newline ||
1417 l_from_str_btw || l_newline ||
1418 l_where_str || l_newline ||
1419 l_predicate_btw || l_newline;
1420
1421 l_ilv2eq :=
1422 l_select_eq || l_newline ||
1423 l_from_str_eq || l_newline ||
1424 l_where_str || l_newline ||
1425 l_predicate_eq|| l_newline;
1426
1427 l_ilv2lk := l_newline ||
1428 l_select_like || l_newline ||
1429 l_from_str_like || l_newline ||
1430 l_where_str || l_newline ||
1431 l_predicate_like || l_newline;
1432
1433 l_ilv2lkp := l_newline ||
1434 l_select_like || l_newline ||
1435 l_from_str_like || l_newline ||
1436 l_where_str || l_newline ||
1437 replace(l_predicate_like,'a.squal_fc01 = Q1012LK.first_char',
1438 'Q1012LK.first_char= ''%''') || l_newline;
1439
1440 l_ilv2btwn := l_newline ||
1441 l_select_btw || l_newline ||
1442 l_from_str_btw || l_newline ||
1443 l_where_str || l_newline ||
1444 l_predicate_btw || l_newline;
1445
1446 ELSE
1447 /* added the join condition for tables */
1448 -- dblee: 08/20/03 replace select clause literal w/ g_select_list_1 variable
1449 l_select_like := l_newline || g_select_list_1;
1450
1451 l_qual_rules :=
1452 l_select_eq || l_newline ||
1453 l_from_str_eq || l_newline ||
1454 l_where_str || l_newline ||
1455 l_predicate_eq || l_newline ||
1456 'and Q1012R1.terr_id = Q1003R1.terr_id' || l_newline ||
1457 'UNION ALL' || l_newline ||
1458 l_select_like || l_newline ||
1459 l_from_str_like || l_newline ||
1460 l_where_str || l_newline ||
1461 l_predicate_like || l_newline ||
1462 'and Q1012LK.terr_id = Q1007R1.terr_id' || l_newline ||
1463 'UNION ALL' || l_newline ||
1464 l_select_like || l_newline ||
1465 l_from_str_like || l_newline ||
1466 l_where_str || l_newline ||
1467 replace(l_predicate_like,'a.squal_fc01 = Q1012LK.first_char',
1468 'Q1012LK.first_char= ''%''') || l_newline ||
1469 'UNION ALL' || l_newline ||
1470 l_select_btw || l_newline ||
1471 l_from_str_btw || l_newline ||
1472 l_where_str || l_newline ||
1473 l_predicate_btw || l_newline ||
1474 'and Q1012BT.terr_id = Q1003R1.terr_id' || l_newline;
1475
1476 l_ilv2eq :=
1477 l_select_eq || l_newline ||
1478 l_from_str_eq || l_newline ||
1479 l_where_str || l_newline ||
1480 l_predicate_eq || l_newline ||
1481 'and Q1012R1.terr_id = Q1003R1.terr_id' || l_newline ||
1482 'and Q1012R1.terr_id = Q1007R1.terr_id'|| l_newline;
1483
1484 l_ilv2lk := l_newline ||
1485 l_select_like || l_newline ||
1486 l_from_str_like || l_newline ||
1487 l_where_str || l_newline ||
1488 l_predicate_like || l_newline ||
1489 'and Q1012LK.terr_id = Q1003R1.terr_id' || l_newline ||
1490 'and Q1012LK.terr_id = Q1007R1.terr_id'|| l_newline;
1491
1492 l_ilv2lkp := l_newline ||
1493 l_select_like || l_newline ||
1494 l_from_str_like || l_newline ||
1495 l_where_str || l_newline ||
1496 replace(l_predicate_like,'a.squal_fc01 = Q1012LK.first_char',
1497 'Q1012LK.first_char= ''%''') || l_newline ||
1498 'and Q1012LK.terr_id = Q1003R1.terr_id' || l_newline ||
1499 'and Q1012LK.terr_id = Q1007R1.terr_id'|| l_newline;
1500
1501 l_ilv2btwn := l_newline ||
1502 l_select_btw || l_newline ||
1503 l_from_str_btw || l_newline ||
1504 l_where_str || l_newline ||
1505 l_predicate_btw || l_newline || l_newline ||
1506 'and Q1012BT.terr_id = Q1003R1.terr_id' || l_newline ||
1507 'and Q1012BT.terr_id = Q1007R1.terr_id' || l_newline;
1508
1509 END IF;
1510
1511 ELSE
1512 -- dblee/eihsu: 08/15/03 added p_new_mode_fetch flag
1513 l_from_str := append_inlineview(l_from_str, p_new_mode_fetch);
1514
1515 IF l_relation_product <> 382439 THEN
1516 l_qual_rules :=
1517 l_from_str || l_newline ||
1518 l_where_str || l_newline ||
1519 l_predicate || l_newline || ';' ;
1520
1521 -- dblee: 08/20/03 replaced select clause literal w/ g_select_list_1 variable
1522
1523 /* ARPATEL BUG#3455772 03/11/2004 */
1524 IF p_relation_product = 672899
1525 THEN
1526 IF p_new_mode_fetch = 'Y'
1527 THEN
1528 p_sql :=
1529 'SELECT /*+ use_concat no_merge */ A.*,ILV.terr_id,ILV.absolute_rank,ILV.top_level_terr_id ,ILV.num_winners ';
1530
1531 ELSE
1532 p_sql :=
1533 'SELECT /*+ use_concat no_merge */ a.trans_object_id,a.trans_detail_object_id,a.worker_id,a.header_id1,a.header_id2,' ||
1534 'ILV.terr_id,ILV.absolute_rank,ILV.top_level_terr_id ,ILV.num_winners,ILV.org_id ' ;
1535 /* bug 3835831 */
1536 IF (mod(p_relation_product, 79) = 0) THEN
1537 p_sql := p_sql || ' ,A.SQUAL_NUM01, A.SQUAL_NUM02 ';
1538 END IF;
1539 END IF;
1540
1541 p_sql := p_sql ||
1542 l_from_str || l_newline ||
1543 l_where_str || l_newline ||
1544 l_predicate || l_newline;
1545 ELSE
1546 p_sql := g_select_list_1;
1547
1548 /* bug 3835831 */
1549 IF ((mod(p_relation_product, 79) = 0) AND (p_new_mode_fetch <> 'Y')) THEN
1550 p_sql := p_sql || ' ,A.SQUAL_NUM01, A.SQUAL_NUM02 ';
1551 END IF;
1552
1553 p_sql := p_sql || l_newline ||
1554 l_from_str || l_newline ||
1555 l_where_str || l_newline ||
1556 l_predicate || l_newline;
1557
1558 END IF;
1559
1560 ELSE
1561 /* table join condition for 382439 relation_product */
1562 l_qual_rules :=
1563 l_from_str || l_newline ||
1564 l_where_str || l_newline ||
1565 l_predicate || l_newline ||
1566 'AND Q1004R1.terr_id = Q1003R1.terr_id AND Q1004R1.terr_id = Q1007R1.terr_id'
1567 || l_newline || ';';
1568
1569 -- dblee: 08/20/03 - replaced select clause literal w/ g_select_list_1 variable
1570 p_sql := g_select_list_1;
1571
1572 /* bug 3835831 */
1573 IF ((mod(p_relation_product, 79) = 0) AND (p_new_mode_fetch <> 'Y')) THEN
1574 p_sql := p_sql || ' ,A.SQUAL_NUM01, A.SQUAL_NUM02 ';
1575 END IF;
1576
1577 p_sql := p_sql || l_newline ||
1578 l_from_str || l_newline || -- dblee: 08/23/03 added from clause
1579 l_where_str || l_newline ||
1580 l_predicate || l_newline ||
1581 'AND Q1004R1.terr_id = Q1003R1.terr_id AND Q1004R1.terr_id = Q1007R1.terr_id' || l_newline;
1582
1583 END IF;
1584 END IF;
1585
1586 p_ilv2eq := l_ilv2eq;
1587 p_ilv2lk := l_ilv2lk;
1588 p_ilv2lkp := l_ilv2lkp;
1589 p_ilv2btwn := l_ilv2btwn;
1590
1591 EXCEPTION
1592 WHEN OTHERS THEN
1593 g_ProgramStatus := 1;
1594 Add_To_PackageTable ('-- Program failed in build_ilv2 ');
1595
1596 END build_ilv2;
1597
1598
1599 /*************************************************
1600 ** Gets all the qualifier combinations that are used by this
1601 ** Usage and builds the SQL statement to check the
1602 ** qualifier rules
1603 **************************************************/
1604 PROCEDURE build_qualifier_rules(
1605 p_source_id IN NUMBER,
1606 p_qual_type_id IN NUMBER,
1607 p_relation_product IN NUMBER,
1608 p_input_table_name IN VARCHAR2,
1609 p_print_flag IN VARCHAR2,
1610 -- dblee/eihsu 08/15/03 added new mode flag
1611 p_new_mode_fetch IN CHAR,
1612 p_sql OUT NOCOPY VARCHAR2,
1613 p_ilv2eq OUT NOCOPY VARCHAR2,
1614 p_ilv2lk OUT NOCOPY VARCHAR2,
1615 p_ilv2lkp OUT NOCOPY VARCHAR2,
1616 p_ilv2btwn OUT NOCOPY VARCHAR2)
1617 AS
1618
1619 CURSOR c_rel_prod_detail
1620 IS
1621 SELECT DISTINCT jtqp.relation_product
1622 ,jtqf.qual_usg_id
1623 ,jqu.alias_rule1
1624 ,jqu.alias_op_like
1625 ,jqu.alias_op_between
1626 ,jqu.op_eql
1627 ,jqu.op_not_eql
1628 ,jqu.op_lss_thn
1629 ,jqu.op_lss_thn_eql
1630 ,jqu.op_grtr_thn
1631 ,jqu.op_grtr_thn_eql
1632 ,jqu.op_like
1633 ,jqu.op_not_like
1634 ,jqu.op_between
1635 ,jqu.op_not_between
1636 ,jqu.op_common_where
1637 FROM jtf_qual_usgs_all jqu,
1638 jtf_tae_qual_factors jtqf,
1639 jtf_tae_qual_products jtqp,
1640 jtf_tae_qual_prod_factors jtpf
1641 WHERE jqu.org_id = -3113
1642 AND jqu.qual_usg_id = jtqf.qual_usg_id
1643 and jtpf.qual_factor_id= jtqf.qual_factor_id
1644 and jtqp.qual_product_id = jtpf.qual_product_id
1645 and jtqp.relation_product = p_relation_product
1646 and jtqp.source_id = p_source_id
1647 and jtqp.trans_object_type_id= p_qual_type_id
1648 and jqu.op_not_eql is NULL
1649 and jqu.op_not_like is NULL
1650 and jqu.op_not_between is NULL
1651 ORDER BY jtqf.qual_usg_id;
1652
1653 l_qual_usg_id NUMBER;
1654 l_qual_rules VARCHAR2(32767);
1655 l_rule VARCHAR2(32767);
1656 l_counter NUMBER := 1;
1657 l_newline VARCHAR2(2);
1658 l_indent VARCHAR2(30);
1659 l_sysdate DATE;
1660 l_str_len NUMBER;
1661 l_start NUMBER;
1662 l_get_nchar NUMBER;
1663 l_next_newline NUMBER;
1664 l_rule_str VARCHAR2(256);
1665 l_from_str VARCHAR2(32767);
1666 l_from_str_eq VARCHAR2(32767);
1667 l_from_str_like VARCHAR2(32767);
1668 l_from_str_btw VARCHAR2(32767);
1669 l_where_str VARCHAR2(32767);
1670 l_predicate VARCHAR2(32767);
1671 l_predicate_eq VARCHAR2(32767);
1672 l_predicate_like VARCHAR2(32767);
1673 l_predicate_btw VARCHAR2(32767);
1674 l_select_eq VARCHAR2(32767);
1675 l_select_like VARCHAR2(32767);
1676 l_select_btw VARCHAR2(32767);
1677 l_ilv2eq VARCHAR2(32767);
1678 l_ilv2lk VARCHAR2(32767);
1679 l_ilv2lkp VARCHAR2(32767);
1680 l_ilv2btwn VARCHAR2(32767);
1681
1682 BEGIN
1683
1684 --dbms_output.put_line('Inside build_rule_expression ');
1685
1686 l_newline := FND_GLOBAL.Local_Chr(10); /* newline character */
1687 l_sysdate := SYSDATE;
1688
1689 FOR JTF_csr IN c_rel_prod_detail LOOP
1690
1691 IF G_Debug THEN
1692 Write_Log(2, ' ');
1693 Write_Log(2, '/*----------------------------------------*/');
1694 Write_Log(2, 'PACKAGE RULE #' || l_counter );
1695 Write_Log(2, 'QUAL_USG_ID: ' || TO_CHAR(JTF_csr.qual_usg_id) );
1696 Write_Log(2, ' ');
1697
1698 Write_Log(2, '/*----------------------------------------*/');
1699 Write_Log(2, ' ');
1700 END IF;
1701
1702 --IF (l_counter > 1) THEN
1703 -- l_qual_rules := l_newline || l_qual_rules || l_newline ||
1704 -- ' UNION ALL ';
1705 --END IF;
1706 IF mod(p_relation_product,67) <> 0 and mod(p_relation_product,73) <> 0 THEN
1707
1708 IF l_counter = 1 THEN
1709
1710 --l_from_str := l_newline || 'jtf_tae_trans_objs A' || l_newline || ',' || JTF_csr.alias_rule1;
1711 l_from_str := l_newline
1712 || p_input_table_name || ' A' || l_newline
1713 || ',' || JTF_csr.alias_rule1;
1714
1715 l_where_str := l_newline || 'WHERE 1 = 1' ;
1716 -- eihsu: worker_id added 06/09/2003
1717 -- bug # 4213107 : worker_id condition should not be added to NMC packages
1718 IF (p_new_mode_fetch <> 'Y') THEN
1719 l_where_str := l_where_str || l_newline || 'AND a.worker_id = p_worker_id ';
1720 END IF;
1721
1722 l_predicate := l_newline
1723 || build_predicate_for_operator(JTF_csr.op_common_where
1724 ,JTF_csr.op_eql
1725 ,JTF_csr.op_lss_thn
1726 ,JTF_csr.op_lss_thn_eql
1727 ,JTF_csr.op_grtr_thn
1728 ,JTF_csr.op_grtr_thn_eql
1729 ,JTF_csr.op_like
1730 ,JTF_csr.op_between
1731 ,l_newline);
1732
1733 ELSE -- l_counter > 1
1734
1735 l_from_str := l_from_str || l_newline ||',' || JTF_csr.alias_rule1;
1736 l_predicate := l_predicate || l_newline
1737 || build_predicate_for_operator(JTF_csr.op_common_where
1738 ,JTF_csr.op_eql
1739 ,JTF_csr.op_lss_thn
1740 ,JTF_csr.op_lss_thn_eql
1741 ,JTF_csr.op_grtr_thn
1742 ,JTF_csr.op_grtr_thn_eql
1743 ,JTF_csr.op_like
1744 ,JTF_csr.op_between
1745 ,l_newline);
1746 END IF; -- l_counter = 1
1747
1748 ELSE -- mod(p_relation_product,67) = 0 or mod(p_relation_product,73) = 0
1749
1750 IF l_counter = 1 THEN
1751
1752 IF JTF_csr.qual_usg_id = -1012 or JTF_csr.qual_usg_id = -1102 THEN
1753 -- dblee: 08/20/03 replaced select clause literal w/ g_select_list_1 variable
1754 l_select_eq := l_newline || g_select_list_1;
1755 l_select_like := l_newline || g_select_list_1;
1756 l_select_btw := l_newline || g_select_list_1;
1757
1758 l_from_str_eq := l_newline || 'FROM '|| p_input_table_name ||' A' || l_newline
1759 || ',' || JTF_csr.alias_rule1;
1760 l_from_str_like := l_newline || 'FROM '|| p_input_table_name ||' A'|| l_newline
1761 || ',' || JTF_csr.alias_op_like;
1762 l_from_str_btw := l_newline || 'FROM '|| p_input_table_name ||' A' || l_newline
1763 || ',' || JTF_csr.alias_op_between;
1764
1765 l_where_str := l_newline || 'WHERE 1 = 1 ' ;
1766 -- eihsu: worker_id added 06/09/2003
1767 -- bug # 4213107 : worker_id condition should not be added to NMC packages
1768 IF (p_new_mode_fetch <> 'Y') THEN
1769 l_where_str := l_where_str || l_newline || 'AND a.worker_id = p_worker_id ';
1770 END IF;
1771
1772 l_predicate_eq := l_newline || 'AND ' || JTF_csr.op_eql;
1773 l_predicate_like := l_newline ||'AND ' || JTF_csr.op_like;
1774 l_predicate_btw := l_newline || 'AND ' || JTF_csr.op_between;
1775
1776 ELSE
1777
1778 l_from_str_eq := l_newline || 'FROM '|| p_input_table_name ||' A' || l_newline
1779 || ',' || JTF_csr.alias_rule1;
1780 l_where_str := l_newline || 'WHERE 1 = 1' ;
1781 -- eihsu: worker_id added 06/09/2003
1782 -- bug # 4213107 : worker_id condition should not be added to NMC packages
1783 IF (p_new_mode_fetch <> 'Y') THEN
1784 l_where_str := l_where_str || l_newline || 'AND a.worker_id = p_worker_id ';
1785 END IF;
1786 l_predicate_eq := l_newline
1787 || build_predicate_for_operator(JTF_csr.op_common_where
1788 ,JTF_csr.op_eql
1789 ,JTF_csr.op_lss_thn
1790 ,JTF_csr.op_lss_thn_eql
1791 ,JTF_csr.op_grtr_thn
1792 ,JTF_csr.op_grtr_thn_eql
1793 ,JTF_csr.op_like
1794 ,JTF_csr.op_between
1795 ,l_newline);
1796
1797 END IF; -- JTF_csr.qual_usg_id = -1012 or JTF_csr.qual_usg_id = -1102
1798
1799 ELSE /* counter > 1*/
1800
1801 IF JTF_csr.qual_usg_id = -1012 or JTF_csr.qual_usg_id = -1102 THEN
1802 -- dblee: 08/20/03 replaced select clause literal w/ g_select_list_1 variable
1803 l_select_eq := l_newline || g_select_list_1;
1804 l_select_like := l_newline || g_select_list_1;
1805 l_select_btw := l_newline || g_select_list_1;
1806
1807 l_from_str_like := l_from_str_eq || l_newline || ',' || JTF_csr.alias_op_like;
1808 l_from_str_btw := l_from_str_eq || l_newline || ',' || JTF_csr.alias_op_between;
1809 l_from_str_eq := l_from_str_eq ||l_newline || ',' || JTF_csr.alias_rule1;
1810
1811 l_where_str := l_newline || 'WHERE 1 = 1' ;
1812 -- eihsu: worker_id added 06/09/2003
1813 -- bug # 4213107 : worker_id condition should not be added to NMC packages
1814 IF (p_new_mode_fetch <> 'Y') THEN
1815 l_where_str := l_where_str || l_newline || 'AND a.worker_id = p_worker_id ';
1816 END IF;
1817
1818 /* sbehera added AND 05/02/2002 */
1819 l_predicate_like := l_predicate_eq|| l_newline || 'AND '|| JTF_csr.op_like;
1820 l_predicate_btw := l_predicate_eq || l_newline || 'AND '|| JTF_csr.op_between;
1821 l_predicate_eq := l_predicate_eq || l_newline || 'AND '|| JTF_csr.op_eql;
1822
1823 ELSE -- JTF_csr.qual_usg_id not in (-1012, -1102)
1824 l_from_str_eq := l_from_str_eq || l_newline || ',' || JTF_csr.alias_rule1;
1825 l_from_str_like := l_from_str_like || l_newline || ',' || JTF_csr.alias_rule1;
1826 l_from_str_btw := l_from_str_btw || l_newline || ',' || JTF_csr.alias_rule1;
1827
1828 l_predicate_eq := l_predicate_eq || l_newline
1829 || build_predicate_for_operator(JTF_csr.op_common_where
1830 ,JTF_csr.op_eql
1831 ,JTF_csr.op_lss_thn
1832 ,JTF_csr.op_lss_thn_eql
1833 ,JTF_csr.op_grtr_thn
1834 ,JTF_csr.op_grtr_thn_eql
1835 ,JTF_csr.op_like
1836 ,JTF_csr.op_between
1837 ,l_newline);
1838
1839 l_predicate_like := l_predicate_like || l_newline
1840 || build_predicate_for_operator(JTF_csr.op_common_where
1841 ,JTF_csr.op_eql
1842 ,JTF_csr.op_lss_thn
1843 ,JTF_csr.op_lss_thn_eql
1844 ,JTF_csr.op_grtr_thn
1845 ,JTF_csr.op_grtr_thn_eql
1846 ,JTF_csr.op_like
1847 ,JTF_csr.op_between
1848 ,l_newline);
1849
1850 l_predicate_btw := l_predicate_btw || l_newline
1851 || build_predicate_for_operator(JTF_csr.op_common_where
1852 ,JTF_csr.op_eql
1853 ,JTF_csr.op_lss_thn
1854 ,JTF_csr.op_lss_thn_eql
1855 ,JTF_csr.op_grtr_thn
1856 ,JTF_csr.op_grtr_thn_eql
1857 ,JTF_csr.op_like
1858 ,JTF_csr.op_between
1859 ,l_newline);
1860 END IF; -- JTF_csr.qual_usg_id = -1012 or JTF_csr.qual_usg_id = -1102
1861
1862 END IF; /* counter */
1863
1864 END IF; /* mod */
1865
1866 l_counter := l_counter + 1;
1867 END LOOP;
1868
1869 /* for account classification we need to add AS_INTERESTS in the from clause */
1870 IF (mod(p_relation_product,79) = 0 and mod(p_relation_product,67) <> 0) THEN
1871 l_from_str := l_from_str || l_newline; /* jdochert 04/24/02 ||', AS_INTERESTS ai ' ;*/
1872 ELSIF (mod(p_relation_product,79) = 0 and mod(p_relation_product,67) = 0) THEN
1873 l_from_str := l_from_str || l_newline; /* jdochert 04/24/02 ||', AS_INTERESTS ai ' ;*/
1874 l_from_str_like := l_from_str_like || l_newline; /* jdochert 04/24/02 ||', AS_INTERESTS ai ' ;*/
1875 l_from_str_btw := l_from_str_btw || l_newline; /* jdochert 04/24/02 ||', AS_INTERESTS ai ' ;*/
1876 END IF;
1877
1878 /* for lead expected purchase we need to add AS_SALES_LEAD_LINES in the from clause */
1879 IF (mod(p_relation_product,137) = 0 and mod(p_relation_product,67) <> 0) THEN
1880 l_from_str := l_from_str || l_newline; /* jdochert 04/24/02 ||', AS_SALES_LEAD_LINES asl ' ; */
1881 ELSIF (mod(p_relation_product,137) = 0 and mod(p_relation_product,67) = 0) THEN
1882 l_from_str := l_from_str || l_newline; /* jdochert 04/24/02 ||', AS_SALES_LEAD_LINES asl ' ; */
1883 l_from_str_like := l_from_str_like || l_newline; /* jdochert 04/24/02 ||', AS_SALES_LEAD_LINES asl ' ; */
1884 l_from_str_btw := l_from_str_btw || l_newline; /* jdochert 04/24/02 ||', AS_SALES_LEAD_LINES asl ' ; */
1885 END IF;
1886
1887 /* for opportunity expected purchase we need to add AS_SALES_LEAD_LINES in the from clause */
1888 IF (mod(p_relation_product,139) = 0 and mod(p_relation_product,67) <> 0) THEN
1889 l_from_str := l_from_str || l_newline; /* jdochert 04/24/02 ||', AS_LEAD_LINES al ' ; */
1890 ELSIF (mod(p_relation_product,139) = 0 and mod(p_relation_product,67) = 0) THEN
1891 l_from_str := l_from_str || l_newline; /* jdochert 04/24/02 ||', AS_LEAD_LINES al ' ; */
1892 l_from_str_like := l_from_str_like || l_newline; /* jdochert 04/24/02 ||', AS_LEAD_LINES al ' ; */
1893 l_from_str_btw := l_from_str_btw || l_newline; /* jdochert 04/24/02 ||', AS_LEAD_LINES al ' ; */
1894 END IF;
1895
1896 /* construct union statement for qual_usg_id = -1012 */
1897 IF mod(p_relation_product,67) = 0 or mod(p_relation_product,73) = 0 THEN
1898
1899 -- dblee/eihsu: 08/15/03 added p_new_mode_fetch flag
1900 l_from_str_eq := append_inlineview(l_from_str_eq, p_new_mode_fetch);
1901 l_from_str_like := append_inlineview(l_from_str_like, p_new_mode_fetch);
1902 l_from_str_btw := append_inlineview(l_from_str_btw, p_new_mode_fetch);
1903
1904 IF p_relation_product <> 324347 THEN
1905 l_qual_rules :=
1906 l_select_eq || l_newline ||
1907 l_from_str_eq || l_newline ||
1908 l_where_str || l_newline ||
1909 l_predicate_eq || l_newline ||
1910 'UNION ALL' || l_newline ||
1911 l_select_like || l_newline ||
1912 l_from_str_like || l_newline ||
1913 l_where_str || l_newline ||
1914 l_predicate_like || l_newline ||
1915 'UNION ALL' || l_newline ||
1916 l_select_like || l_newline ||
1917 l_from_str_like || l_newline ||
1918 l_where_str || l_newline ||
1919 replace(l_predicate_like,'a.squal_fc01 = Q1012LK.first_char',
1920 'Q1012LK.first_char= ''%''') || l_newline ||
1921 'UNION ALL' || l_newline ||
1922 l_select_btw || l_newline ||
1923 l_from_str_btw || l_newline ||
1924 l_where_str || l_newline ||
1925 l_predicate_btw || l_newline;
1926
1927 l_ilv2eq :=
1928 l_select_eq || l_newline ||
1929 l_from_str_eq || l_newline ||
1930 l_where_str || l_newline ||
1931 l_predicate_eq|| l_newline;
1932
1933 l_ilv2lk := l_newline ||
1934 l_select_like || l_newline ||
1935 l_from_str_like || l_newline ||
1936 l_where_str || l_newline ||
1937 l_predicate_like || l_newline;
1938
1939 l_ilv2lkp := l_newline ||
1940 l_select_like || l_newline ||
1941 l_from_str_like || l_newline ||
1942 l_where_str || l_newline ||
1943 replace(l_predicate_like,'a.squal_fc01 = Q1012LK.first_char',
1944 'Q1012LK.first_char= ''%''') || l_newline;
1945
1946 l_ilv2btwn := l_newline ||
1947 l_select_btw || l_newline ||
1948 l_from_str_btw || l_newline ||
1949 l_where_str || l_newline ||
1950 l_predicate_btw || l_newline;
1951
1952 ELSE -- p_relation_product = 324347
1953 /* added the join condition for tables */
1954 -- dblee: 08/20/03 replaced select clause literal w/ g_select_list_1 variable
1955 l_select_like := l_newline || g_select_list_1;
1956
1957 l_qual_rules :=
1958 l_select_eq || l_newline ||
1959 l_from_str_eq || l_newline ||
1960 l_where_str || l_newline ||
1961 l_predicate_eq || l_newline ||
1962 'and Q1012R1.terr_id = Q1003R1.terr_id' || l_newline ||
1963 'UNION ALL' || l_newline ||
1964 l_select_like ||l_newline ||
1965 l_from_str_like || l_newline ||
1966 l_where_str || l_newline ||
1967 l_predicate_like || l_newline ||
1968 'and Q1012LK.terr_id = Q1007R1.terr_id' || l_newline ||
1969 'UNION ALL' || l_newline ||
1970 l_select_like || l_newline ||
1971 l_from_str_like || l_newline ||
1972 l_where_str || l_newline ||
1973 replace(l_predicate_like,'a.squal_fc01 = Q1012LK.first_char',
1974 'Q1012LK.first_char= ''%''') || l_newline ||
1975 'UNION ALL' || l_newline ||
1976 l_select_btw || l_newline ||
1977 l_from_str_btw || l_newline ||
1978 l_where_str || l_newline ||
1979 l_predicate_btw || l_newline ||
1980 'and Q1012BT.terr_id = Q1003R1.terr_id' || l_newline;
1981
1982 l_ilv2eq :=
1983 l_select_eq || l_newline ||
1984 l_from_str_eq || l_newline ||
1985 l_where_str || l_newline ||
1986 l_predicate_eq || l_newline ||
1987 'and Q1012R1.terr_id = Q1003R1.terr_id' || l_newline ||
1988 'and Q1012R1.terr_id = Q1007R1.terr_id'|| l_newline;
1989
1990 l_ilv2lk := l_newline ||
1991 l_select_like || l_newline ||
1992 l_from_str_like || l_newline ||
1993 l_where_str || l_newline ||
1994 l_predicate_like || l_newline ||
1995 'and Q1012LK.terr_id = Q1003R1.terr_id' || l_newline ||
1996 'and Q1012LK.terr_id = Q1007R1.terr_id' || l_newline;
1997
1998 l_ilv2lkp := l_newline ||
1999 l_select_like || l_newline ||
2000 l_from_str_like || l_newline ||
2001 l_where_str || l_newline ||
2002 replace(l_predicate_like,'a.squal_fc01 = Q1012LK.first_char',
2003 'Q1012LK.first_char= ''%''') || l_newline ||
2004 'and Q1012LK.terr_id = Q1003R1.terr_id' || l_newline ||
2005 'and Q1012LK.terr_id = Q1007R1.terr_id'||l_newline ;
2006
2007 l_ilv2btwn := l_newline ||
2008 l_select_btw || l_newline ||
2009 l_from_str_btw || l_newline ||
2010 l_where_str || l_newline ||
2011 l_predicate_btw || l_newline || l_newline ||
2012 'and Q1012BT.terr_id = Q1003R1.terr_id' || l_newline ||
2013 'and Q1012BT.terr_id = Q1007R1.terr_id'|| l_newline;
2014
2015 END IF; -- p_relation_product <> 324347
2016
2017 ELSE -- p_relation_product not divisible by 67 or 73
2018
2019 -- dblee/eihsu: 08/15/03 added p_new_mode_fetch flag
2020 l_from_str := append_inlineview(l_from_str, p_new_mode_fetch);
2021
2022 IF p_relation_product <> 382439 THEN
2023 l_qual_rules := -- where's the 'select' clause?
2024 l_from_str || l_newline ||
2025 l_where_str || l_newline ||
2026 l_predicate || l_newline || ';' ;
2027
2028 -- dblee: 08/20/03 replaced select clause literal w/ g_select_list_1 variable
2029 p_sql :=
2030 g_select_list_1 ||
2031 l_from_str || l_newline ||
2032 l_where_str || l_newline ||
2033 l_predicate || l_newline;
2034 ELSE /* table join condition for 382439 relation_product */
2035 l_qual_rules := -- where's the 'select' clause?
2036 l_from_str || l_newline ||
2037 l_where_str || l_newline ||
2038 l_predicate || l_newline ||
2039 'AND Q1004R1.terr_id = Q1003R1.terr_id AND Q1004R1.terr_id = Q1007R1.terr_id'
2040 || l_newline || ';';
2041
2042 -- dblee: 08/20/03 replaced select clause literal w/ g_select_list_1 variable
2043 p_sql :=
2044 g_select_list_1 || l_newline || -- where's the 'from' clause?
2045 l_where_str || l_newline ||
2046 l_predicate || l_newline ||
2047 'AND Q1004R1.terr_id = Q1003R1.terr_id AND Q1004R1.terr_id = Q1007R1.terr_id'
2048 || l_newline;
2049 END IF; -- p_relation_product <> 382439
2050
2051 END IF; -- p_relation_product divisible by 67 or 73
2052
2053 IF p_print_flag= 'N' THEN
2054 write_buffer_content(l_qual_rules);
2055 ELSE
2056 p_ilv2eq := l_ilv2eq;
2057 p_ilv2lk := l_ilv2lk;
2058 p_ilv2lkp := l_ilv2lkp;
2059 p_ilv2btwn := l_ilv2btwn;
2060 --p_sql := l_qual_rules;
2061 END IF;
2062
2063 EXCEPTION
2064 WHEN OTHERS THEN
2065 g_ProgramStatus := 1;
2066 Add_To_PackageTable ('-- Program encountered invalid territory ');
2067
2068 END build_qualifier_rules;
2069
2070
2071 /*************************************************
2072 ** Gets all the qualifier combinations that are used by this
2073 ** Usage and builds the SQL statement to check the
2074 ** qualifier rules. This is applicable for
2075 ** account qualifier,opportunutiy expected purchase and lead expected purchase
2076 ** but no customer name range
2077 ** opp inventort item
2078 **************************************************/
2079 PROCEDURE build_qualifier_rules1(
2080 p_source_id IN NUMBER,
2081 p_qual_type_id IN NUMBER,
2082 p_relation_product IN NUMBER,
2083 p_input_table_name IN VARCHAR2,
2084 p_print_flag IN VARCHAR2,
2085 -- dblee: 08/26/03 added p_new_mode_fetch flag argument
2086 p_new_mode_fetch IN CHAR,
2087 p_sql OUT NOCOPY VARCHAR2)
2088 AS
2089 l_ilv1sql VARCHAR2(32767);
2090 l_rel_prod1 NUMBER;
2091 l_rel_prod2 NUMBER;
2092 l_ilv2eq VARCHAR2(32767);
2093 l_ilv2lk VARCHAR2(32767);
2094 l_ilv2lkp VARCHAR2(32767);
2095 l_ilv2btwn VARCHAR2(32767);
2096 l_sql VARCHAR2(32767);
2097
2098 BEGIN
2099
2100 IF mod(p_relation_product,79) = 0 THEN
2101 l_rel_prod1 := 79;
2102 ELSIF mod(p_relation_product,137) = 0 THEN
2103 l_rel_prod1 := 137;
2104 ELSIF mod(p_relation_product,113) = 0 THEN
2105 l_rel_prod1 := 113;
2106 ELSIF mod(p_relation_product,131) = 0 THEN
2107 l_rel_prod1 := 131;
2108 ELSIF mod(p_relation_product,139) = 0 THEN
2109 l_rel_prod1 := 139;
2110 ELSIF mod(p_relation_product,163) = 0 THEN
2111 l_rel_prod1 := 163;
2112 ELSIF mod(p_relation_product,167) = 0 THEN
2113 l_rel_prod1 := 167;
2114 END IF;
2115
2116 IF p_new_mode_fetch = 'Y'
2117 THEN
2118 Add_To_PackageTable (G_INDENT || 'SELECT /*+ NO_MERGE(ILV1) NO_MERGE(ILV2) ' );
2119 Add_To_PackageTable (G_INDENT || ' USE_HASH(ILV1 ILV2) */' );
2120 Add_To_PackageTable (G_INDENT || ' ILV2.TRANS_OBJECT_ID' );
2121 Add_To_PackageTable (G_INDENT || ' , ILV2.TRANS_DETAIL_OBJECT_ID' );
2122 Add_To_PackageTable (G_INDENT || ' , ILV2.HEADER_ID1' );
2123 Add_To_PackageTable (G_INDENT || ' , ILV2.HEADER_ID2' );
2124 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC01' );
2125 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC02' );
2126 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC03' );
2127 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC04' );
2128 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC05' );
2129 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC01' );
2130 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC02' );
2131 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC03' );
2132 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC04' );
2133 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC05' );
2134 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC06' );
2135 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC07' );
2136 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC08' );
2137 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC09' );
2138 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC10' );
2139 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR01' );
2140 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR02' );
2141 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR03' );
2142 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR04' );
2143 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR05' );
2144 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR06' );
2145 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR07' );
2146 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR08' );
2147 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR09' );
2148 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR10' );
2149 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR11' );
2150 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR12' );
2151 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR13' );
2152 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR14' );
2153 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR15' );
2154 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR16' );
2155 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR17' );
2156 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR18' );
2157 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR19' );
2158 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR20' );
2159 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR21' );
2160 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR22' );
2161 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR23' );
2162 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR24' );
2163 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR25' );
2164 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR26' );
2165 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR27' );
2166 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR28' );
2167 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR30' );
2168 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR31' );
2169 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR32' );
2170 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR33' );
2171 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR34' );
2172 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR35' );
2173 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR36' );
2174 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR37' );
2175 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR38' );
2176 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR39' );
2177 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR40' );
2178 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR41' );
2179 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR42' );
2180 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR43' );
2181 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR44' );
2182 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR45' );
2183 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR46' );
2184 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR47' );
2185 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR48' );
2186 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR49' );
2187 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR50' );
2188 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR51' );
2189 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR52' );
2190 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR53' );
2191 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR54' );
2192 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR55' );
2193 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR56' );
2194 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR57' );
2195 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR58' );
2196 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR59' );
2197 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR60' );
2198 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM01' );
2199 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM02' );
2200 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM03' );
2201 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM04' );
2202 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM05' );
2203 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM06' );
2204 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM07' );
2205 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM08' );
2206 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM09' );
2207 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM10' );
2208 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM11' );
2209 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM12' );
2210 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM13' );
2211 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM14' );
2212 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM15' );
2213 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM16' );
2214 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM17' );
2215 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM18' );
2216 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM19' );
2217 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM20' );
2218 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM21' );
2219 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM22' );
2220 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM23' );
2221 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM24' );
2222 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM25' );
2223 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM26' );
2224 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM27' );
2225 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM28' );
2226 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM29' );
2227 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM30' );
2228 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM31' );
2229 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM32' );
2230 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM33' );
2231 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM34' );
2232 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM35' );
2233 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM36' );
2234 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM37' );
2235 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM38' );
2236 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM39' );
2237 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM40' );
2238 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM41' );
2239 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM42' );
2240 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM43' );
2241 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM44' );
2242 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM45' );
2243 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM46' );
2244 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM47' );
2245 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM48' );
2246 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM49' );
2247 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM50' );
2248 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM51' );
2249 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM52' );
2250 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM53' );
2251 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM54' );
2252 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM55' );
2253 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM56' );
2254 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM57' );
2255 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM58' );
2256 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM59' );
2257 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM60' );
2258 Add_To_PackageTable (G_INDENT || ' , ILV2.ASSIGNED_FLAG' );
2259 Add_To_PackageTable (G_INDENT || ' , ILV2.PROCESSED_FLAG' );
2260 Add_To_PackageTable (G_INDENT || ' , ILV2.ORG_ID' );
2261 Add_To_PackageTable (G_INDENT || ' , ILV2.SECURITY_GROUP_ID' );
2262 Add_To_PackageTable (G_INDENT || ' , ILV2.OBJECT_VERSION_NUMBER' );
2263 Add_To_PackageTable (G_INDENT || ' , ILV2.WORKER_ID' );
2264 Add_To_PackageTable (G_INDENT || ' , ILV2.TERR_ID' );
2265 Add_To_PackageTable (G_INDENT || ' , ILV2.ABSOLUTE_RANK' );
2266 Add_To_PackageTable (G_INDENT || ' , ILV2.TOP_LEVEL_TERR_ID' );
2267 Add_To_PackageTable (G_INDENT || ' , ILV2.NUM_WINNERS' );
2268
2269 ELSE
2270 Add_To_PackageTable (G_INDENT || ' SELECT /*+ NO_MERGE(ILV1) NO_MERGE(ILV2) ');
2271 Add_To_PackageTable (G_INDENT || ' USE_HASH(ILV1 ILV2) */');
2272 Add_To_PackageTable (G_INDENT || ' ILV2.trans_object_id');
2273 Add_To_PackageTable (G_INDENT || ' , ILV2.trans_detail_object_id');
2274 -- eihsu 06/19/2003 worker_id
2275 Add_To_PackageTable (G_INDENT || ' , ILV2.worker_id');
2276 Add_To_PackageTable (G_INDENT || ' , ILV2.header_id1');
2277 Add_To_PackageTable (G_INDENT || ' , ILV2.header_id2');
2278 Add_To_PackageTable (G_INDENT || ' , ILV2.terr_id');
2279 Add_To_PackageTable (G_INDENT || ' , ILV2.absolute_rank');
2280 Add_To_PackageTable (G_INDENT || ' , ILV2.top_level_terr_id ');
2281 Add_To_PackageTable (G_INDENT || ' , ILV2.num_winners');
2282 Add_To_PackageTable (G_INDENT || ' , ILV2.org_id');
2283 END IF;
2284
2285 Add_To_PackageTable (G_INDENT || ' FROM');
2286 Add_To_PackageTable (G_INDENT || ' ( /* INLINE VIEW1 */');
2287
2288 --get the content of ILV1
2289 -- dblee: 08/26/03 added p_new_mode_fetch flag argument
2290 build_ilv1(p_source_id, p_qual_type_id, p_relation_product, l_rel_prod1, p_new_mode_fetch, l_ilv1sql);
2291 write_buffer_content(l_ilv1sql);
2292
2293 Add_To_PackageTable (G_INDENT || ' ) ILV1, ');
2294 Add_To_PackageTable (' ');
2295 Add_To_PackageTable (G_INDENT || ' ( /* INLINE VIEW2 */');
2296
2297 --get the content of ILV2
2298 -- dblee/eihsu: 08/26/03 added p_new_mode_fetch flag argument
2299 Build_ilv2(p_source_id, p_qual_type_id, p_relation_product, l_rel_prod1,
2300 p_input_table_name, p_new_mode_fetch, l_sql, l_ilv2eq, l_ilv2lk, l_ilv2lkp, l_ilv2btwn);
2301
2302 write_buffer_content(l_sql);
2303
2304 Add_To_PackageTable (G_INDENT || ' ) ILV2 ');
2305 Add_To_PackageTable (G_INDENT || ' WHERE ILV1.terr_id = ILV2.terr_id ');
2306
2307 IF l_rel_prod1 = 79 THEN
2308 /* bug 3835831 */
2309 Add_To_PackageTable(G_INDENT || ' AND ILV1.customer_id = ILV2.squal_num01');
2310 Add_To_PackageTable(G_INDENT || ' AND ( (ILV1.address_id IS NULL )');
2311 Add_To_PackageTable(G_INDENT || ' OR ');
2312 Add_To_PackageTable(G_INDENT || ' (ILV1.address_id= ILV2.squal_num02)');
2313 Add_To_PackageTable(G_INDENT || ' )');
2314 /*
2315 Add_To_PackageTable(G_INDENT || ' AND ILV1.customer_id = ILV2.trans_object_id');
2316 Add_To_PackageTable(G_INDENT || ' AND ( (ILV1.address_id IS NULL AND ILV2.trans_detail_object_id IS NULL)');
2317 Add_To_PackageTable(G_INDENT || ' OR ');
2318 Add_To_PackageTable(G_INDENT || ' (ILV1.address_id= ILV2.trans_detail_object_id)');
2319 Add_To_PackageTable(G_INDENT || ' )');
2320 */
2321 ELSIF l_rel_prod1 = 137 THEN
2322 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
2323 ELSIF l_rel_prod1 = 113 THEN
2324 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
2325 ELSIF l_rel_prod1 = 131 THEN
2326 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
2327 ELSIF l_rel_prod1 = 139 THEN
2328 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
2329 ELSIF l_rel_prod1 = 163 THEN
2330 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
2331 ELSIF l_rel_prod1 = 167 THEN
2332 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
2333 END IF;
2334
2335 EXCEPTION
2336 WHEN OTHERS THEN
2337 g_ProgramStatus := 1;
2338 Add_To_PackageTable ('-- Program encountered invalid territory ');
2339
2340 END build_qualifier_rules1;
2341
2342
2343 /*************************************************
2344 ** Gets all the qualifier combinations that are used by this
2345 ** Usage and builds the SQL statement to check the
2346 ** qualifier rules. This is applicable for customer namerange with
2347 ** account qualifier,opportunutiy expected purchase and lead expected purchase
2348 ** opp inventory item
2349 **************************************************/
2350 PROCEDURE build_qualifier_rules2(
2351 p_source_id IN NUMBER,
2352 p_qual_type_id IN NUMBER,
2353 p_relation_product IN NUMBER,
2354 p_input_table_name IN VARCHAR2,
2355 p_print_flag IN VARCHAR2,
2356 -- dblee: 08/26/03: added p_new_mode_fetch flag argument
2357 p_new_mode_fetch IN CHAR,
2358 p_sql OUT NOCOPY VARCHAR2)
2359 AS
2360 l_ilv1sql VARCHAR2(32767);
2361 l_rel_prod1 NUMBER;
2362 l_rel_prod2 NUMBER;
2363 l_ilv2eq VARCHAR2(32767);
2364 l_ilv2lk VARCHAR2(32767);
2365 l_ilv2lkp VARCHAR2(32767);
2366 l_ilv2btwn VARCHAR2(32767);
2367
2368 BEGIN
2369
2370 IF mod(p_relation_product,79) = 0 THEN
2371 l_rel_prod1 := 79;
2372 ELSIF mod(p_relation_product,137) = 0 THEN
2373 l_rel_prod1 := 137;
2374 ELSIF mod(p_relation_product,113) = 0 THEN
2375 l_rel_prod1 := 113;
2376 ELSIF mod(p_relation_product,131) = 0 THEN
2377 l_rel_prod1 := 131;
2378 ELSIF mod(p_relation_product,139) = 0 THEN
2379 l_rel_prod1 := 139;
2380 ELSIF mod(p_relation_product,163) = 0 THEN
2381 l_rel_prod1 := 163;
2382 ELSIF mod(p_relation_product,167) = 0 THEN
2383 l_rel_prod1 := 167;
2384 END IF;
2385
2386 IF p_new_mode_fetch = 'Y'
2387 THEN
2388 Add_To_PackageTable (G_INDENT || 'SELECT /*+ NO_MERGE(ILV1) NO_MERGE(ILV2) ' );
2389 Add_To_PackageTable (G_INDENT || ' USE_HASH(ILV1 ILV2) */' );
2390 Add_To_PackageTable (G_INDENT || ' ILV2.TRANS_OBJECT_ID' );
2391 Add_To_PackageTable (G_INDENT || ' , ILV2.TRANS_DETAIL_OBJECT_ID' );
2392 Add_To_PackageTable (G_INDENT || ' , ILV2.HEADER_ID1' );
2393 Add_To_PackageTable (G_INDENT || ' , ILV2.HEADER_ID2' );
2394 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC01' );
2395 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC02' );
2396 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC03' );
2397 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC04' );
2398 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC05' );
2399 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC01' );
2400 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC02' );
2401 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC03' );
2402 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC04' );
2403 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC05' );
2404 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC06' );
2405 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC07' );
2406 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC08' );
2407 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC09' );
2408 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC10' );
2409 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR01' );
2410 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR02' );
2411 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR03' );
2412 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR04' );
2413 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR05' );
2414 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR06' );
2415 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR07' );
2416 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR08' );
2417 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR09' );
2418 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR10' );
2419 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR11' );
2420 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR12' );
2421 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR13' );
2422 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR14' );
2423 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR15' );
2424 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR16' );
2425 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR17' );
2426 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR18' );
2427 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR19' );
2428 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR20' );
2429 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR21' );
2430 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR22' );
2431 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR23' );
2432 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR24' );
2433 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR25' );
2434 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR26' );
2435 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR27' );
2436 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR28' );
2437 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR30' );
2438 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR31' );
2439 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR32' );
2440 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR33' );
2441 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR34' );
2442 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR35' );
2443 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR36' );
2444 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR37' );
2445 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR38' );
2446 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR39' );
2447 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR40' );
2448 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR41' );
2449 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR42' );
2450 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR43' );
2451 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR44' );
2452 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR45' );
2453 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR46' );
2454 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR47' );
2455 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR48' );
2456 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR49' );
2457 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR50' );
2458 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR51' );
2459 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR52' );
2460 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR53' );
2461 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR54' );
2462 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR55' );
2463 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR56' );
2464 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR57' );
2465 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR58' );
2466 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR59' );
2467 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR60' );
2468 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM01' );
2469 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM02' );
2470 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM03' );
2471 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM04' );
2472 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM05' );
2473 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM06' );
2474 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM07' );
2475 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM08' );
2476 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM09' );
2477 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM10' );
2478 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM11' );
2479 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM12' );
2480 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM13' );
2481 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM14' );
2482 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM15' );
2483 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM16' );
2484 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM17' );
2485 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM18' );
2486 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM19' );
2487 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM20' );
2488 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM21' );
2489 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM22' );
2490 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM23' );
2491 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM24' );
2492 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM25' );
2493 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM26' );
2494 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM27' );
2495 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM28' );
2496 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM29' );
2497 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM30' );
2498 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM31' );
2499 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM32' );
2500 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM33' );
2501 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM34' );
2502 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM35' );
2503 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM36' );
2504 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM37' );
2505 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM38' );
2506 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM39' );
2507 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM40' );
2508 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM41' );
2509 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM42' );
2510 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM43' );
2511 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM44' );
2512 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM45' );
2513 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM46' );
2514 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM47' );
2515 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM48' );
2516 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM49' );
2517 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM50' );
2518 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM51' );
2519 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM52' );
2520 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM53' );
2521 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM54' );
2522 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM55' );
2523 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM56' );
2524 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM57' );
2525 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM58' );
2526 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM59' );
2527 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM60' );
2528 Add_To_PackageTable (G_INDENT || ' , ILV2.ASSIGNED_FLAG' );
2529 Add_To_PackageTable (G_INDENT || ' , ILV2.PROCESSED_FLAG' );
2530 Add_To_PackageTable (G_INDENT || ' , ILV2.ORG_ID' );
2531 Add_To_PackageTable (G_INDENT || ' , ILV2.SECURITY_GROUP_ID' );
2532 Add_To_PackageTable (G_INDENT || ' , ILV2.OBJECT_VERSION_NUMBER' );
2533 Add_To_PackageTable (G_INDENT || ' , ILV2.WORKER_ID' );
2534 Add_To_PackageTable (G_INDENT || ' , ILV2.TERR_ID' );
2535 Add_To_PackageTable (G_INDENT || ' , ILV2.ABSOLUTE_RANK' );
2536 Add_To_PackageTable (G_INDENT || ' , ILV2.TOP_LEVEL_TERR_ID' );
2537 Add_To_PackageTable (G_INDENT || ' , ILV2.NUM_WINNERS' );
2538 ELSE
2539 Add_To_PackageTable (G_INDENT || ' SELECT /*+ NO_MERGE(ILV1) NO_MERGE(ILV2) ');
2540 Add_To_PackageTable (G_INDENT || ' USE_HASH(ILV1 ILV2) */');
2541 Add_To_PackageTable (G_INDENT || ' ILV2.trans_object_id');
2542 Add_To_PackageTable (G_INDENT || ' , ILV2.trans_detail_object_id');
2543 -- eihsu 06/19/2003 worker_id
2544 Add_To_PackageTable (G_INDENT || ' , ILV2.worker_id');
2545 Add_To_PackageTable (G_INDENT || ' , ILV2.header_id1');
2546 Add_To_PackageTable (G_INDENT || ' , ILV2.header_id2');
2547 Add_To_PackageTable (G_INDENT || ' , ILV2.terr_id');
2548 Add_To_PackageTable (G_INDENT || ' , ILV2.absolute_rank');
2549 Add_To_PackageTable (G_INDENT || ' , ILV2.top_level_terr_id ');
2550 Add_To_PackageTable (G_INDENT || ' , ILV2.num_winners');
2551 Add_To_PackageTable (G_INDENT || ' , ILV2.org_id');
2552 END IF;
2553
2554 Add_To_PackageTable (G_INDENT || ' FROM');
2555 Add_To_PackageTable (G_INDENT || ' ( /* INLINE VIEW1 */');
2556
2557 --get the content of ILV1
2558 -- dblee: 08/26/03 added p_new_mode_fetch flag argument
2559 build_ilv1(p_source_id, p_qual_type_id, p_relation_product, l_rel_prod1, p_new_mode_fetch, l_ilv1sql);
2560 write_buffer_content(l_ilv1sql);
2561
2562 Add_To_PackageTable (G_INDENT || ' ) ILV1, ');
2563 Add_To_PackageTable (' ');
2564 Add_To_PackageTable (G_INDENT || ' ( /* INLINE VIEW2 */');
2565
2566 --get the content of ILV2
2567 -- dblee/eihsu: 08/15/03 added p_new_mode_fetch flag argument
2568 Build_ilv2(p_source_id,p_qual_type_id,p_relation_product,l_rel_prod1,
2569 p_input_table_name,p_new_mode_fetch,p_sql,l_ilv2eq,l_ilv2lk,l_ilv2lkp,l_ilv2btwn);
2570
2571 write_buffer_content(l_ilv2eq);
2572
2573 Add_To_PackageTable (G_INDENT || ' ) ILV2 ');
2574 Add_To_PackageTable (G_INDENT || ' WHERE ILV1.terr_id = ILV2.terr_id ');
2575
2576 IF l_rel_prod1 = 79 THEN
2577 /* bug 3835831 */
2578 Add_To_PackageTable(G_INDENT || ' AND ILV1.customer_id = ILV2.squal_num01');
2579 Add_To_PackageTable(G_INDENT || ' AND ( (ILV1.address_id IS NULL )');
2580 Add_To_PackageTable(G_INDENT || ' OR ');
2581 Add_To_PackageTable(G_INDENT || ' (ILV1.address_id= ILV2.squal_num02)');
2582 Add_To_PackageTable(G_INDENT || ' )');
2583 /*
2584 Add_To_PackageTable(G_INDENT || ' AND ILV1.customer_id = ILV2.trans_object_id');
2585 Add_To_PackageTable(G_INDENT || ' AND ( (ILV1.address_id IS NULL AND ILV2.trans_detail_object_id IS NULL)');
2586 Add_To_PackageTable(G_INDENT || ' OR ');
2587 Add_To_PackageTable(G_INDENT || ' (ILV1.address_id= ILV2.trans_detail_object_id)');
2588 Add_To_PackageTable(G_INDENT || ' )');
2589 */
2590 ELSIF l_rel_prod1 = 137 THEN
2591 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
2592 ELSIF l_rel_prod1 = 113 THEN
2593 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
2594 ELSIF l_rel_prod1 = 131 THEN
2595 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
2596 ELSIF l_rel_prod1 = 139 THEN
2597 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
2598 ELSIF l_rel_prod1 = 163 THEN
2599 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
2600 ELSIF l_rel_prod1 = 167 THEN
2601 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
2602 END IF;
2603
2604 Add_To_PackageTable ('UNION ALL');
2605 Add_To_PackageTable (' ');
2606
2607 IF p_new_mode_fetch = 'Y'
2608 THEN
2609 Add_To_PackageTable (G_INDENT || 'SELECT /*+ NO_MERGE(ILV1) NO_MERGE(ILV2) ' );
2610 Add_To_PackageTable (G_INDENT || ' USE_HASH(ILV1 ILV2) */' );
2611 Add_To_PackageTable (G_INDENT || ' ILV2.TRANS_OBJECT_ID' );
2612 Add_To_PackageTable (G_INDENT || ' , ILV2.TRANS_DETAIL_OBJECT_ID' );
2613 Add_To_PackageTable (G_INDENT || ' , ILV2.HEADER_ID1' );
2614 Add_To_PackageTable (G_INDENT || ' , ILV2.HEADER_ID2' );
2615 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC01' );
2616 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC02' );
2617 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC03' );
2618 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC04' );
2619 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC05' );
2620 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC01' );
2621 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC02' );
2622 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC03' );
2623 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC04' );
2624 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC05' );
2625 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC06' );
2626 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC07' );
2627 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC08' );
2628 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC09' );
2629 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC10' );
2630 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR01' );
2631 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR02' );
2632 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR03' );
2633 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR04' );
2634 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR05' );
2635 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR06' );
2636 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR07' );
2637 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR08' );
2638 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR09' );
2639 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR10' );
2640 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR11' );
2641 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR12' );
2642 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR13' );
2643 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR14' );
2644 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR15' );
2645 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR16' );
2646 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR17' );
2647 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR18' );
2648 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR19' );
2649 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR20' );
2650 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR21' );
2651 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR22' );
2652 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR23' );
2653 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR24' );
2654 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR25' );
2655 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR26' );
2656 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR27' );
2657 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR28' );
2658 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR30' );
2659 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR31' );
2660 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR32' );
2661 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR33' );
2662 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR34' );
2663 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR35' );
2664 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR36' );
2665 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR37' );
2666 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR38' );
2667 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR39' );
2668 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR40' );
2669 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR41' );
2670 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR42' );
2671 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR43' );
2672 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR44' );
2673 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR45' );
2674 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR46' );
2675 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR47' );
2676 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR48' );
2677 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR49' );
2678 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR50' );
2679 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR51' );
2680 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR52' );
2681 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR53' );
2682 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR54' );
2683 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR55' );
2684 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR56' );
2685 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR57' );
2686 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR58' );
2687 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR59' );
2688 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR60' );
2689 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM01' );
2690 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM02' );
2691 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM03' );
2692 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM04' );
2693 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM05' );
2694 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM06' );
2695 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM07' );
2696 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM08' );
2697 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM09' );
2698 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM10' );
2699 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM11' );
2700 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM12' );
2701 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM13' );
2702 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM14' );
2703 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM15' );
2704 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM16' );
2705 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM17' );
2706 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM18' );
2707 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM19' );
2708 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM20' );
2709 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM21' );
2710 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM22' );
2711 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM23' );
2712 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM24' );
2713 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM25' );
2714 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM26' );
2715 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM27' );
2716 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM28' );
2717 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM29' );
2718 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM30' );
2719 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM31' );
2720 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM32' );
2721 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM33' );
2722 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM34' );
2723 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM35' );
2724 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM36' );
2725 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM37' );
2726 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM38' );
2727 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM39' );
2728 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM40' );
2729 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM41' );
2730 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM42' );
2731 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM43' );
2732 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM44' );
2733 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM45' );
2734 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM46' );
2735 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM47' );
2736 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM48' );
2737 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM49' );
2738 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM50' );
2739 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM51' );
2740 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM52' );
2741 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM53' );
2742 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM54' );
2743 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM55' );
2744 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM56' );
2745 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM57' );
2746 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM58' );
2747 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM59' );
2748 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM60' );
2749 Add_To_PackageTable (G_INDENT || ' , ILV2.ASSIGNED_FLAG' );
2750 Add_To_PackageTable (G_INDENT || ' , ILV2.PROCESSED_FLAG' );
2751 Add_To_PackageTable (G_INDENT || ' , ILV2.ORG_ID' );
2752 Add_To_PackageTable (G_INDENT || ' , ILV2.SECURITY_GROUP_ID' );
2753 Add_To_PackageTable (G_INDENT || ' , ILV2.OBJECT_VERSION_NUMBER' );
2754 Add_To_PackageTable (G_INDENT || ' , ILV2.WORKER_ID' );
2755 Add_To_PackageTable (G_INDENT || ' , ILV2.TERR_ID' );
2756 Add_To_PackageTable (G_INDENT || ' , ILV2.ABSOLUTE_RANK' );
2757 Add_To_PackageTable (G_INDENT || ' , ILV2.TOP_LEVEL_TERR_ID' );
2758 Add_To_PackageTable (G_INDENT || ' , ILV2.NUM_WINNERS' );
2759 ELSE
2760 Add_To_PackageTable (G_INDENT || ' SELECT /*+ NO_MERGE(ILV1) NO_MERGE(ILV2) ');
2761 Add_To_PackageTable (G_INDENT || ' USE_HASH(ILV1 ILV2) */');
2762 Add_To_PackageTable (G_INDENT || ' ILV2.trans_object_id');
2763 Add_To_PackageTable (G_INDENT || ' , ILV2.trans_detail_object_id');
2764 -- eihsu 06/19/2003 worker_id
2765 Add_To_PackageTable (G_INDENT || ' , ILV2.worker_id');
2766 Add_To_PackageTable (G_INDENT || ' , ILV2.header_id1');
2767 Add_To_PackageTable (G_INDENT || ' , ILV2.header_id2');
2768 Add_To_PackageTable (G_INDENT || ' , ILV2.terr_id');
2769 Add_To_PackageTable (G_INDENT || ' , ILV2.absolute_rank');
2770 Add_To_PackageTable (G_INDENT || ' , ILV2.top_level_terr_id ');
2771 Add_To_PackageTable (G_INDENT || ' , ILV2.num_winners');
2772 Add_To_PackageTable (G_INDENT || ' , ILV2.org_id');
2773 END IF;
2774
2775 Add_To_PackageTable (G_INDENT || ' FROM');
2776 Add_To_PackageTable (G_INDENT || ' ( /* INLINE VIEW1 */');
2777
2778 --get the content of ILV1 for like
2779 write_buffer_content(l_ilv1sql);
2780
2781 Add_To_PackageTable (G_INDENT || ' ) ILV1, ');
2782 Add_To_PackageTable (' ');
2783 Add_To_PackageTable (G_INDENT || ' ( /* INLINE VIEW2 */');
2784
2785 --get the content of ILV2 for like
2786 write_buffer_content(l_ilv2lk);
2787
2788 Add_To_PackageTable (G_INDENT || ' ) ILV2 ');
2789 Add_To_PackageTable (G_INDENT || ' WHERE ILV1.terr_id = ILV2.terr_id ');
2790
2791 IF l_rel_prod1 = 79 THEN
2792 /* bug 3835831 */
2793 Add_To_PackageTable(G_INDENT || ' AND ILV1.customer_id = ILV2.squal_num01');
2794 Add_To_PackageTable(G_INDENT || ' AND ( (ILV1.address_id IS NULL )');
2795 Add_To_PackageTable(G_INDENT || ' OR ');
2796 Add_To_PackageTable(G_INDENT || ' (ILV1.address_id= ILV2.squal_num02)');
2797 Add_To_PackageTable(G_INDENT || ' )');
2798 /*
2799 Add_To_PackageTable(G_INDENT || ' AND ILV1.customer_id = ILV2.trans_object_id');
2800 Add_To_PackageTable(G_INDENT || ' AND ( (ILV1.address_id IS NULL AND ILV2.trans_detail_object_id IS NULL)');
2801 Add_To_PackageTable(G_INDENT || ' OR ');
2802 Add_To_PackageTable(G_INDENT || ' (ILV1.address_id= ILV2.trans_detail_object_id)');
2803 Add_To_PackageTable(G_INDENT || ' )');
2804 */
2805 ELSIF l_rel_prod1 = 137 THEN
2806 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
2807 ELSIF l_rel_prod1 = 113 THEN
2808 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
2809 ELSIF l_rel_prod1 = 131 THEN
2810 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
2811 ELSIF l_rel_prod1 = 139 THEN
2812 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
2813 ELSIF l_rel_prod1 = 163 THEN
2814 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
2815 ELSIF l_rel_prod1 = 167 THEN
2816 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
2817 END IF;
2818
2819 Add_To_PackageTable ('UNION ALL');
2820 Add_To_PackageTable (' ');
2821
2822 IF p_new_mode_fetch = 'Y'
2823 THEN
2824 Add_To_PackageTable (G_INDENT || 'SELECT /*+ NO_MERGE(ILV1) NO_MERGE(ILV2) ' );
2825 Add_To_PackageTable (G_INDENT || ' USE_HASH(ILV1 ILV2) */' );
2826 Add_To_PackageTable (G_INDENT || ' ILV2.TRANS_OBJECT_ID' );
2827 Add_To_PackageTable (G_INDENT || ' , ILV2.TRANS_DETAIL_OBJECT_ID' );
2828 Add_To_PackageTable (G_INDENT || ' , ILV2.HEADER_ID1' );
2829 Add_To_PackageTable (G_INDENT || ' , ILV2.HEADER_ID2' );
2830 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC01' );
2831 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC02' );
2832 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC03' );
2833 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC04' );
2834 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC05' );
2835 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC01' );
2836 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC02' );
2837 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC03' );
2838 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC04' );
2839 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC05' );
2840 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC06' );
2841 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC07' );
2842 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC08' );
2843 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC09' );
2844 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC10' );
2845 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR01' );
2846 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR02' );
2847 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR03' );
2848 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR04' );
2849 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR05' );
2850 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR06' );
2851 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR07' );
2852 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR08' );
2853 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR09' );
2854 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR10' );
2855 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR11' );
2856 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR12' );
2857 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR13' );
2858 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR14' );
2859 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR15' );
2860 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR16' );
2861 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR17' );
2862 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR18' );
2863 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR19' );
2864 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR20' );
2865 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR21' );
2866 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR22' );
2867 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR23' );
2868 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR24' );
2869 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR25' );
2870 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR26' );
2871 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR27' );
2872 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR28' );
2873 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR30' );
2874 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR31' );
2875 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR32' );
2876 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR33' );
2877 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR34' );
2878 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR35' );
2879 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR36' );
2880 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR37' );
2881 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR38' );
2882 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR39' );
2883 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR40' );
2884 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR41' );
2885 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR42' );
2886 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR43' );
2887 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR44' );
2888 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR45' );
2889 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR46' );
2890 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR47' );
2891 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR48' );
2892 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR49' );
2893 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR50' );
2894 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR51' );
2895 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR52' );
2896 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR53' );
2897 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR54' );
2898 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR55' );
2899 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR56' );
2900 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR57' );
2901 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR58' );
2902 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR59' );
2903 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR60' );
2904 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM01' );
2905 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM02' );
2906 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM03' );
2907 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM04' );
2908 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM05' );
2909 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM06' );
2910 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM07' );
2911 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM08' );
2912 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM09' );
2913 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM10' );
2914 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM11' );
2915 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM12' );
2916 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM13' );
2917 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM14' );
2918 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM15' );
2919 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM16' );
2920 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM17' );
2921 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM18' );
2922 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM19' );
2923 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM20' );
2924 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM21' );
2925 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM22' );
2926 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM23' );
2927 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM24' );
2928 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM25' );
2929 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM26' );
2930 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM27' );
2931 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM28' );
2932 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM29' );
2933 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM30' );
2934 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM31' );
2935 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM32' );
2936 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM33' );
2937 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM34' );
2938 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM35' );
2939 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM36' );
2940 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM37' );
2941 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM38' );
2942 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM39' );
2943 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM40' );
2944 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM41' );
2945 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM42' );
2946 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM43' );
2947 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM44' );
2948 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM45' );
2949 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM46' );
2950 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM47' );
2951 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM48' );
2952 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM49' );
2953 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM50' );
2954 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM51' );
2955 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM52' );
2956 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM53' );
2957 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM54' );
2958 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM55' );
2959 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM56' );
2960 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM57' );
2961 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM58' );
2962 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM59' );
2963 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM60' );
2964 Add_To_PackageTable (G_INDENT || ' , ILV2.ASSIGNED_FLAG' );
2965 Add_To_PackageTable (G_INDENT || ' , ILV2.PROCESSED_FLAG' );
2966 Add_To_PackageTable (G_INDENT || ' , ILV2.ORG_ID' );
2967 Add_To_PackageTable (G_INDENT || ' , ILV2.SECURITY_GROUP_ID' );
2968 Add_To_PackageTable (G_INDENT || ' , ILV2.OBJECT_VERSION_NUMBER' );
2969 Add_To_PackageTable (G_INDENT || ' , ILV2.WORKER_ID' );
2970 Add_To_PackageTable (G_INDENT || ' , ILV2.TERR_ID' );
2971 Add_To_PackageTable (G_INDENT || ' , ILV2.ABSOLUTE_RANK' );
2972 Add_To_PackageTable (G_INDENT || ' , ILV2.TOP_LEVEL_TERR_ID' );
2973 Add_To_PackageTable (G_INDENT || ' , ILV2.NUM_WINNERS' );
2974 ELSE
2975 Add_To_PackageTable (G_INDENT || ' SELECT /*+ NO_MERGE(ILV1) NO_MERGE(ILV2) ');
2976 Add_To_PackageTable (G_INDENT || ' USE_HASH(ILV1 ILV2) */');
2977 Add_To_PackageTable (G_INDENT || ' ILV2.trans_object_id');
2978 Add_To_PackageTable (G_INDENT || ' , ILV2.trans_detail_object_id');
2979 -- eihsu 06/19/2003 worker_id
2980 Add_To_PackageTable (G_INDENT || ' , ILV2.worker_id');
2981 Add_To_PackageTable (G_INDENT || ' , ILV2.header_id1');
2982 Add_To_PackageTable (G_INDENT || ' , ILV2.header_id2');
2983 Add_To_PackageTable (G_INDENT || ' , ILV2.terr_id');
2984 Add_To_PackageTable (G_INDENT || ' , ILV2.absolute_rank');
2985 Add_To_PackageTable (G_INDENT || ' , ILV2.top_level_terr_id ');
2986 Add_To_PackageTable (G_INDENT || ' , ILV2.num_winners');
2987 Add_To_PackageTable (G_INDENT || ' , ILV2.org_id');
2988 END IF;
2989
2990 Add_To_PackageTable (G_INDENT || ' FROM');
2991 Add_To_PackageTable (G_INDENT || ' ( /* INLINE VIEW1 */');
2992
2993 --get the content of ILV1 for like first char
2994 write_buffer_content(l_ilv1sql);
2995
2996 Add_To_PackageTable (G_INDENT || ' ) ILV1, ');
2997 Add_To_PackageTable (' ');
2998 Add_To_PackageTable (G_INDENT || ' ( /* INLINE VIEW2 */');
2999
3000 --get the content of ILV2 for like first char
3001 write_buffer_content(l_ilv2lkp);
3002
3003 Add_To_PackageTable (G_INDENT || ' ) ILV2 ');
3004 Add_To_PackageTable (G_INDENT || ' WHERE ILV1.terr_id = ILV2.terr_id ');
3005
3006 IF l_rel_prod1 = 79 THEN
3007 /* bug 3835831 */
3008 Add_To_PackageTable(G_INDENT || ' AND ILV1.customer_id = ILV2.squal_num01');
3009 Add_To_PackageTable(G_INDENT || ' AND ( (ILV1.address_id IS NULL )');
3010 Add_To_PackageTable(G_INDENT || ' OR ');
3011 Add_To_PackageTable(G_INDENT || ' (ILV1.address_id= ILV2.squal_num02)');
3012 Add_To_PackageTable(G_INDENT || ' )');
3013 /*
3014 Add_To_PackageTable(G_INDENT || ' AND ILV1.customer_id = ILV2.trans_object_id');
3015 Add_To_PackageTable(G_INDENT || ' AND ( (ILV1.address_id IS NULL AND ILV2.trans_detail_object_id IS NULL)');
3016 Add_To_PackageTable(G_INDENT || ' OR ');
3017 Add_To_PackageTable(G_INDENT || ' (ILV1.address_id= ILV2.trans_detail_object_id)');
3018 Add_To_PackageTable(G_INDENT || ' )');
3019 */
3020 ELSIF l_rel_prod1 = 137 THEN
3021 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
3022 ELSIF l_rel_prod1 = 113 THEN
3023 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
3024 ELSIF l_rel_prod1 = 131 THEN
3025 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
3026 ELSIF l_rel_prod1 = 139 THEN
3027 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
3028 ELSIF l_rel_prod1 = 163 THEN
3029 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
3030 ELSIF l_rel_prod1 = 167 THEN
3031 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
3032 END IF;
3033
3034 Add_To_PackageTable ('UNION ALL');
3035 Add_To_PackageTable (' ');
3036
3037 IF p_new_mode_fetch = 'Y'
3038 THEN
3039 Add_To_PackageTable (G_INDENT || 'SELECT /*+ NO_MERGE(ILV1) NO_MERGE(ILV2) ' );
3040 Add_To_PackageTable (G_INDENT || ' USE_HASH(ILV1 ILV2) */' );
3041 Add_To_PackageTable (G_INDENT || ' ILV2.TRANS_OBJECT_ID' );
3042 Add_To_PackageTable (G_INDENT || ' , ILV2.TRANS_DETAIL_OBJECT_ID' );
3043 Add_To_PackageTable (G_INDENT || ' , ILV2.HEADER_ID1' );
3044 Add_To_PackageTable (G_INDENT || ' , ILV2.HEADER_ID2' );
3045 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC01' );
3046 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC02' );
3047 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC03' );
3048 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC04' );
3049 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_FC05' );
3050 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC01' );
3051 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC02' );
3052 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC03' );
3053 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC04' );
3054 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC05' );
3055 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC06' );
3056 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC07' );
3057 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC08' );
3058 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC09' );
3059 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CURC10' );
3060 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR01' );
3061 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR02' );
3062 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR03' );
3063 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR04' );
3064 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR05' );
3065 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR06' );
3066 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR07' );
3067 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR08' );
3068 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR09' );
3069 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR10' );
3070 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR11' );
3071 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR12' );
3072 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR13' );
3073 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR14' );
3074 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR15' );
3075 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR16' );
3076 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR17' );
3077 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR18' );
3078 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR19' );
3079 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR20' );
3080 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR21' );
3081 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR22' );
3082 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR23' );
3083 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR24' );
3084 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR25' );
3085 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR26' );
3086 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR27' );
3087 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR28' );
3088 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR30' );
3089 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR31' );
3090 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR32' );
3091 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR33' );
3092 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR34' );
3093 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR35' );
3094 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR36' );
3095 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR37' );
3096 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR38' );
3097 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR39' );
3098 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR40' );
3099 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR41' );
3100 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR42' );
3101 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR43' );
3102 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR44' );
3103 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR45' );
3104 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR46' );
3105 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR47' );
3106 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR48' );
3107 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR49' );
3108 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR50' );
3109 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR51' );
3110 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR52' );
3111 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR53' );
3112 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR54' );
3113 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR55' );
3114 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR56' );
3115 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR57' );
3116 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR58' );
3117 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR59' );
3118 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_CHAR60' );
3119 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM01' );
3120 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM02' );
3121 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM03' );
3122 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM04' );
3123 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM05' );
3124 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM06' );
3125 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM07' );
3126 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM08' );
3127 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM09' );
3128 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM10' );
3129 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM11' );
3130 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM12' );
3131 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM13' );
3132 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM14' );
3133 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM15' );
3134 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM16' );
3135 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM17' );
3136 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM18' );
3137 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM19' );
3138 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM20' );
3139 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM21' );
3140 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM22' );
3141 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM23' );
3142 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM24' );
3143 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM25' );
3144 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM26' );
3145 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM27' );
3146 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM28' );
3147 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM29' );
3148 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM30' );
3149 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM31' );
3150 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM32' );
3151 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM33' );
3152 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM34' );
3153 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM35' );
3154 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM36' );
3155 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM37' );
3156 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM38' );
3157 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM39' );
3158 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM40' );
3159 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM41' );
3160 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM42' );
3161 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM43' );
3162 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM44' );
3163 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM45' );
3164 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM46' );
3165 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM47' );
3166 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM48' );
3167 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM49' );
3168 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM50' );
3169 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM51' );
3170 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM52' );
3171 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM53' );
3172 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM54' );
3173 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM55' );
3174 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM56' );
3175 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM57' );
3176 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM58' );
3177 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM59' );
3178 Add_To_PackageTable (G_INDENT || ' , ILV2.SQUAL_NUM60' );
3179 Add_To_PackageTable (G_INDENT || ' , ILV2.ASSIGNED_FLAG' );
3180 Add_To_PackageTable (G_INDENT || ' , ILV2.PROCESSED_FLAG' );
3181 Add_To_PackageTable (G_INDENT || ' , ILV2.ORG_ID' );
3182 Add_To_PackageTable (G_INDENT || ' , ILV2.SECURITY_GROUP_ID' );
3183 Add_To_PackageTable (G_INDENT || ' , ILV2.OBJECT_VERSION_NUMBER' );
3184 Add_To_PackageTable (G_INDENT || ' , ILV2.WORKER_ID' );
3185 Add_To_PackageTable (G_INDENT || ' , ILV2.TERR_ID' );
3186 Add_To_PackageTable (G_INDENT || ' , ILV2.ABSOLUTE_RANK' );
3187 Add_To_PackageTable (G_INDENT || ' , ILV2.TOP_LEVEL_TERR_ID' );
3188 Add_To_PackageTable (G_INDENT || ' , ILV2.NUM_WINNERS' );
3189 ELSE
3190 Add_To_PackageTable (G_INDENT || ' SELECT /*+ NO_MERGE(ILV1) NO_MERGE(ILV2) ');
3191 Add_To_PackageTable (G_INDENT || ' USE_HASH(ILV1 ILV2) */');
3192 Add_To_PackageTable (G_INDENT || ' ILV2.trans_object_id');
3193 Add_To_PackageTable (G_INDENT || ' , ILV2.trans_detail_object_id');
3194 -- eihsu 06/19/2003 worker_id
3195 Add_To_PackageTable (G_INDENT || ' , ILV2.worker_id');
3196 Add_To_PackageTable (G_INDENT || ' , ILV2.header_id1');
3197 Add_To_PackageTable (G_INDENT || ' , ILV2.header_id2');
3198 Add_To_PackageTable (G_INDENT || ' , ILV2.terr_id');
3199 Add_To_PackageTable (G_INDENT || ' , ILV2.absolute_rank');
3200 Add_To_PackageTable (G_INDENT || ' , ILV2.top_level_terr_id ');
3201 Add_To_PackageTable (G_INDENT || ' , ILV2.num_winners');
3202 Add_To_PackageTable (G_INDENT || ' , ILV2.org_id');
3203 END IF;
3204
3205 Add_To_PackageTable (G_INDENT || ' FROM');
3206 Add_To_PackageTable (G_INDENT || ' ( /* INLINE VIEW1 */');
3207
3208 --get the content of ILV1 for between
3209 write_buffer_content(l_ilv1sql);
3210
3211 Add_To_PackageTable (G_INDENT || ' ) ILV1, ');
3212 Add_To_PackageTable (' ');
3213 Add_To_PackageTable (G_INDENT || ' ( /* INLINE VIEW2 */');
3214
3215 --get the content of ILV2 for between
3216 write_buffer_content(l_ilv2btwn);
3217
3218 Add_To_PackageTable (G_INDENT || ' ) ILV2 ');
3219 Add_To_PackageTable (G_INDENT || ' WHERE ILV1.terr_id = ILV2.terr_id ');
3220
3221 IF l_rel_prod1 = 79 THEN
3222 /* bug 3835831 */
3223 Add_To_PackageTable(G_INDENT || ' AND ILV1.customer_id = ILV2.squal_num01');
3224 Add_To_PackageTable(G_INDENT || ' AND ( (ILV1.address_id IS NULL )');
3225 Add_To_PackageTable(G_INDENT || ' OR ');
3226 Add_To_PackageTable(G_INDENT || ' (ILV1.address_id= ILV2.squal_num02)');
3227 Add_To_PackageTable(G_INDENT || ' )');
3228 /*
3229 Add_To_PackageTable(G_INDENT || ' AND ILV1.customer_id = ILV2.trans_object_id');
3230 Add_To_PackageTable(G_INDENT || ' AND ( (ILV1.address_id IS NULL AND ILV2.trans_detail_object_id IS NULL)');
3231 Add_To_PackageTable(G_INDENT || ' OR ');
3232 Add_To_PackageTable(G_INDENT || ' (ILV1.address_id= ILV2.trans_detail_object_id)');
3233 Add_To_PackageTable(G_INDENT || ' )');
3234 */
3235 ELSIF l_rel_prod1 = 137 THEN
3236 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
3237 ELSIF l_rel_prod1 = 113 THEN
3238 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
3239 ELSIF l_rel_prod1 = 131 THEN
3240 Add_To_PackageTable(G_INDENT || ' AND ILV1.sales_lead_id = ILV2.trans_object_id');
3241 ELSIF l_rel_prod1 = 139 THEN
3242 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
3243 ELSIF l_rel_prod1 = 163 THEN
3244 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
3245 ELSIF l_rel_prod1 = 167 THEN
3246 Add_To_PackageTable(G_INDENT || ' AND ILV1.lead_id = ILV2.trans_object_id');
3247 END IF;
3248
3249 EXCEPTION
3250 WHEN OTHERS THEN
3251 g_ProgramStatus := 1;
3252 Add_To_PackageTable ('-- Program encountered invalid territory in rules2 ');
3253
3254 END build_qualifier_rules2;
3255
3256
3257 PROCEDURE gen_details_for_terr_change (
3258 p_source_id IN NUMBER,
3259 p_qual_type_id IN NUMBER,
3260 p_view_name IN VARCHAR2,
3261 p_sql OUT NOCOPY terrsql_tbl_type
3262 )
3263 AS
3264
3265 l_relation_product NUMBER := 0;
3266 l_sql VARCHAR2(32767):=NULL;
3267 i NUMBER := 0;
3268 l_ilv2eq VARCHAR2(32767);
3269 l_ilv2lk VARCHAR2(32767);
3270 l_ilv2lkp VARCHAR2(32767);
3271 l_ilv2btwn VARCHAR2(32767);
3272 l_new_mode_fetch CHAR := 'N';
3273
3274 /* ARPATEL: 01/06/2004 bug#3337382 */
3275 CURSOR c_terr_rel_prod is
3276 SELECT jtdr.terr_id, jtqu.qual_relation_product
3277 FROM jtf_terr_denorm_rules_all jtdr
3278 ,jtf_terr_qtype_usgs_all jtqu
3279 ,jtf_qual_type_usgs_all jqtu
3280 ,jtf_changed_terr_all b
3281 WHERE jtdr.terr_id = jtdr.related_terr_id
3282 AND jtdr.terr_id= b.terr_id
3283 AND jqtu.source_id = jtdr.source_id
3284 AND jqtu.qual_type_id = p_qual_type_id
3285 AND jtdr.terr_id = jtqu.terr_id
3286 AND jtdr.source_id = p_source_id
3287 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id;
3288
3289 BEGIN
3290
3291 IF p_view_name IS NULL THEN
3292
3293 RETURN;
3294
3295 END IF;
3296
3297 /* get all the territory,relation_product from jtf_changed_terr_all,jtf_terr_denorm_rules_all
3298 for the given qual_type_id */
3299 FOR JTF_csr in c_terr_rel_prod LOOP
3300
3301 i := i + 1;
3302
3303 IF l_relation_product <> JTF_csr.qual_relation_product THEN
3304
3305 /* call the Build_Qualifier_Rules, passing p_print_flag='Y' to get the sql */
3306 /* p_view_name is the name of the view from which deatils to be selected */
3307 Build_Qualifier_Rules(p_source_id,p_qual_type_id,JTF_csr.qual_relation_product,p_view_name,'N',l_new_mode_fetch, l_sql,l_ilv2eq,l_ilv2lk,l_ilv2lkp,l_ilv2btwn);
3308 l_relation_product := JTF_csr.qual_relation_product;
3309
3310 /* build the final sql and pass to the caller */
3311 l_sql := replace(l_sql,'JTF_csr.terr_id',JTF_csr.terr_id);
3312
3313 END IF;
3314
3315 p_sql(i).terr_id := JTF_csr.terr_id;
3316 p_sql(i).terr_sql := l_sql;
3317
3318 END LOOP;
3319
3320 END gen_details_for_terr_change;
3321
3322
3323 PROCEDURE gen_terr_rules_recurse(
3324 p_terr_id IN NUMBER,
3325 p_source_id IN NUMBER,
3326 p_qualifier_type_id IN NUMBER,
3327 p_target_type IN VARCHAR2,
3328 p_input_table_name IN VARCHAR2,
3329 p_match_table_name IN VARCHAR2,
3330 p_search_name IN VARCHAR2 := 'SEARCH_TERR_RULES',
3331 -- dblee/eihsu: 08/15/03 added p_new_mode_fetch flag
3332 p_new_mode_fetch IN CHAR := 'N')
3333 AS
3334
3335 CURSOR c_rel_prod( lp_source_id NUMBER
3336 , lp_qual_type_id NUMBER)
3337 IS
3338 SELECT DISTINCT jtqp.relation_product
3339 FROM jtf_tae_qual_products jtqp
3340 WHERE jtqp.source_id = lp_source_id
3341 AND jtqp.trans_object_type_id= lp_qual_type_id
3342 ORDER BY jtqp.relation_product DESC;
3343
3344 l_procedure_name VARCHAR2(30);
3345 l_procedure_desc VARCHAR2(255);
3346 l_parameter_list1 VARCHAR2(255);
3347 l_parameter_list2 VARCHAR2(360);
3348 l_qual_rules VARCHAR2(32767);
3349
3350 l_str_len NUMBER;
3351 l_start NUMBER;
3352 l_get_nchar NUMBER;
3353 l_next_newline NUMBER;
3354 l_rule_str VARCHAR2(256);
3355 l_newline VARCHAR2(2) := FND_GLOBAL.Local_Chr(10); /* newline character */
3356 l_indent VARCHAR2(30);
3357 p_sql VARCHAR2(32767) := NULL;
3358 --l_input_table_name VARCHAR2(30) := 'jtf_tae_trans_objs';
3359 l_ilv2eq VARCHAR2(32767);
3360 l_ilv2lk VARCHAR2(32767);
3361 l_ilv2lkp VARCHAR2(32767);
3362 l_ilv2btwn VARCHAR2(32767);
3363 l_sql VARCHAR2(32767) := NULL;
3364
3365 BEGIN
3366 --dbms_output.put_line('gen_terr_rules_recurse.p_search_name: ' || p_search_name);
3367
3368 IF G_Debug THEN
3369 Write_Log(1, 'INSIDE PROCEDURE JTF_TAE_GEN_PVT.gen_terr_rules_recurse');
3370 END IF;
3371
3372 --dbms_output.put_line('Value of p_qualifier_type_id='|| l_indent||TO_CHAR(p_qualifier_type_id));
3373
3374 l_str_len := LENGTH(l_qual_rules);
3375
3376 --dbms_output.put_line('After Build_Qualifier_Rules');
3377
3378 l_procedure_name := p_search_name;
3379 l_procedure_desc := '/* Territory rules for Usage/Transaction: ' || TO_CHAR(p_source_id) || ' */';
3380
3381 IF p_source_id = -1001 and p_search_name = 'SEARCH_TERR_RULES' THEN
3382
3383 l_parameter_list1 :=
3384 ' p_source_id IN NUMBER' || l_newline ||
3385 ' , p_trans_object_type_id IN NUMBER' || l_newline ||
3386 ' , x_Return_Status OUT VARCHAR2' || l_newline||
3387 ' , x_Msg_Count OUT NUMBER' || l_newline||
3388 ' , x_Msg_Data OUT VARCHAR2'|| l_newline||
3389 ' , p_worker_id IN NUMBER := 1'|| l_newline ;
3390
3391 END IF;
3392
3393
3394 generate_object_definition( l_procedure_name, l_procedure_desc,
3395 l_parameter_list1, l_parameter_list2, 'P', 'BOOLEAN', 'PKB' );
3396
3397 Add_To_PackageTable (' ');
3398 Add_To_PackageTable (' L_REQUEST_ID NUMBER := FND_GLOBAL.CONC_REQUEST_ID();');
3399 Add_To_PackageTable (' L_PROGRAM_APPL_ID NUMBER := FND_GLOBAL.PROG_APPL_ID();');
3400 Add_To_PackageTable (' L_PROGRAM_ID NUMBER := FND_GLOBAL.CONC_PROGRAM_ID();');
3401 Add_To_PackageTable (' L_USER_ID NUMBER := FND_GLOBAL.USER_ID();');
3402 Add_To_PackageTable (' l_sysdate DATE := SYSDATE;');
3403 Add_To_PackageTable (' ');
3404 Add_To_PackageTable (' l_cursor NUMBER;');
3405 Add_To_PackageTable (' l_dyn_str VARCHAR2(32767);');
3406 Add_To_PackageTable (' l_num_rows NUMBER;');
3407 /* ARPATEL Bug#3489240 */
3408 Add_To_PackageTable (' l_num_workers NUMBER;');
3409
3410 Add_To_PackageTable (' indx NUMBER := 1;');
3411 Add_To_PackageTable (' lp_qual_combination_tbl jtf_terr_number_list := jtf_terr_number_list();');
3412 Add_To_PackageTable (' ');
3413 Add_To_PackageTable (' l_counter NUMBER; ');
3414 Add_To_PackageTable (' ');
3415 Add_To_PackageTable (' j NUMBER:=0; ');
3416 Add_To_PackageTable (' i NUMBER:=0; ');
3417 Add_To_PackageTable (' ');
3418 Add_To_PackageTable (' CURSOR c_get_qualrel_prod IS ');
3419 Add_To_PackageTable (' SELECT jtqp.relation_product ');
3420 Add_To_PackageTable (' FROM jtf_tae_qual_products jtqp ');
3421 Add_To_PackageTable (' WHERE jtqp.source_id = p_source_id ');
3422 Add_To_PackageTable (' AND jtqp.trans_object_type_id = p_trans_object_type_id');
3423 Add_To_PackageTable (' ORDER BY jtqp.relation_product DESC ;');
3424 Add_To_PackageTable (' ');
3425 Add_To_PackageTable (' CURSOR c_get_terr(l_qual_rel_prod number) IS');
3426 Add_To_PackageTable (' SELECT distinct jtdr.terr_id ');
3427 Add_To_PackageTable (' , jtdr.source_id ');
3428 Add_To_PackageTable (' , jtdr.qual_type_id');
3429 Add_To_PackageTable (' , jtdr.top_level_terr_id');
3430 Add_To_PackageTable (' , jtdr.absolute_rank ');
3431 Add_To_PackageTable (' , jtdr.num_winners ');
3432 Add_To_PackageTable (' , jtdr.org_id ');
3433 Add_To_PackageTable (' FROM jtf_terr_denorm_rules_all jtdr ');
3434 Add_To_PackageTable (' ,jtf_terr_qtype_usgs_all jtqu ');
3435 Add_To_PackageTable (' ,jtf_qual_type_usgs_all jqtu ');
3436 Add_To_PackageTable (' WHERE jtdr.source_id = p_source_id ');
3437 Add_To_PackageTable (' AND jtdr.resource_exists_flag= ''Y'' ');
3438
3439 Add_To_PackageTable (' AND jqtu.source_id = jtdr.source_id ');
3440 Add_To_PackageTable (' AND jqtu.qual_type_id = p_trans_object_type_id ');
3441 Add_To_PackageTable (' AND jtdr.terr_id = jtqu.terr_id ');
3442 Add_To_PackageTable (' AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ');
3443
3444 Add_To_PackageTable (' AND jtdr.terr_id = jtdr.related_terr_id ');
3445 Add_To_PackageTable (' AND jtqu.qual_relation_product= l_qual_rel_prod;');
3446 Add_To_PackageTable (' ');
3447 Add_To_PackageTable ('BEGIN');
3448 Add_To_PackageTable (' ');
3449 Add_To_PackageTable (' SAVEPOINT JTF_TAE_MATCHING_TRANSACTION; ');
3450 Add_To_PackageTable (' ');
3451 Add_To_PackageTable (' FOR JTF_csr IN c_get_qualrel_prod LOOP ');
3452 Add_To_PackageTable (' lp_qual_combination_tbl.EXTEND;');
3453 Add_To_PackageTable (' j := j + 1;');
3454 Add_To_PackageTable (' lp_qual_combination_tbl(j) := JTF_csr.relation_product;');
3455 Add_To_PackageTable (' END LOOP;');
3456 Add_To_PackageTable (' ');
3457 --Add_To_PackageTable (' lp_source_id := ' || TO_CHAR(p_source_id) || ';');
3458
3459 --IF ( l_str_len > 0 ) THEN
3460 Add_To_PackageTable (' i := lp_qual_combination_tbl.FIRST;');
3461
3462 --Add_To_PackageTable (' FOR i IN lp_qual_combination_tbl.FIRST..lp_qual_combination_tbl.LAST LOOP ');
3463 Add_To_PackageTable (' WHILE i <= lp_qual_combination_tbl.LAST LOOP ');
3464
3465 FOR JTF_csr in c_rel_prod(p_source_id, p_qualifier_type_id) LOOP
3466 Add_To_PackageTable (' ');
3467 Add_To_PackageTable (' BEGIN ');
3468 Add_To_PackageTable (' ');
3469 Add_To_PackageTable (' IF lp_qual_combination_tbl(i) = ' || JTF_csr.relation_product || ' THEN');
3470 Add_To_PackageTable (' ');
3471 --Add_To_PackageTable (' FOR JTF_csr in c_get_terr(lp_qual_combination_tbl(i)) LOOP ' );
3472 Add_To_PackageTable (' ');
3473
3474 -- dblee: 08/15/03 added switch for new mode
3475 IF p_new_mode_fetch = 'Y' AND JTF_csr.relation_product <> 4841 THEN
3476 add_insert_nmtrans(p_match_table_name);
3477 ELSE
3478 /* ARPATEL BUG#3489240 03/10/2004 */
3479 IF JTF_csr.relation_product <> 4841
3480 THEN
3481 Add_To_PackageTable (' INSERT INTO '|| p_match_table_name || ' i');
3482 Add_To_PackageTable (' (' );
3483 Add_To_PackageTable (' trans_object_id');
3484 Add_To_PackageTable (' , trans_detail_object_id');
3485 -- eihsu worker_id 06/05/2003
3486 Add_To_PackageTable (' , worker_id');
3487 Add_To_PackageTable (' , header_id1');
3488 Add_To_PackageTable (' , header_id2');
3489 Add_To_PackageTable (' , source_id');
3490 Add_To_PackageTable (' , trans_object_type_id');
3491 Add_To_PackageTable (' , last_update_date');
3492 Add_To_PackageTable (' , last_updated_by');
3493 Add_To_PackageTable (' , creation_date');
3494 Add_To_PackageTable (' , created_by');
3495 Add_To_PackageTable (' , last_update_login');
3496 Add_To_PackageTable (' , request_id');
3497 Add_To_PackageTable (' , program_application_id');
3498 Add_To_PackageTable (' , program_id');
3499 Add_To_PackageTable (' , program_update_date');
3500 Add_To_PackageTable (' , terr_id');
3501 Add_To_PackageTable (' , absolute_rank');
3502 Add_To_PackageTable (' , top_level_terr_id');
3503 Add_To_PackageTable (' , num_winners');
3504 Add_To_PackageTable (' , org_id');
3505 Add_To_PackageTable (' )' );
3506 END IF;
3507 END IF; -- p_new_mode_fetch = 'Y'
3508
3509 -- IF mod(JTF_csr.relation_product,79) = 0 THEN
3510 /* for account classification */
3511 -- Add_To_PackageTable (' SELECT ');
3512 --ELSIF mod(JTF_csr.relation_product,137) = 0 THEN
3513 /* for lead expected purchase */
3514 -- Add_To_PackageTable (' SELECT ');
3515 --ELSIF mod(JTF_csr.relation_product,139) = 0 THEN
3516 /* for opportunity expected purchase */
3517 -- Add_To_PackageTable (' SELECT ');
3518 --ELSE
3519 -- Add_To_PackageTable (' SELECT ');
3520 --END IF;
3521
3522 IF JTF_csr.relation_product= 4841 or JTF_csr.relation_product= 324347 or
3523 JTF_csr.relation_product= 45084233 or JTF_csr.relation_product= 44435539 or
3524 JTF_csr.relation_product= 62598971 or JTF_csr.relation_product= 61950277 or
3525 JTF_csr.relation_product= 924631 or JTF_csr.relation_product= 934313
3526 or JTF_csr.relation_product = 663217 /* bug#3508485 */
3527 or JTF_csr.relation_product = 353393
3528 THEN
3529
3530 JTF_TAE_SQL_LIB_PVT.get_qual_comb_sql (
3531 JTF_csr.relation_product,
3532 p_source_id,
3533 p_qualifier_type_id,
3534 p_input_table_name,
3535 /* ARPATEL 03/11/2004 BUG#3489240 */
3536 p_match_table_name,
3537 -- dblee: 08/26/03: added p_new_mode_fetch flag argument
3538 p_new_mode_fetch,
3539 l_sql);
3540
3541 write_buffer_content(l_sql);
3542
3543 ELSE -- JTF_csr.relation_product not in (4841, 324347, 353393, 663217, 45084233, 44435539, 62598971, 61950277, 924631, 934313)
3544
3545 -- dblee: 08/15/03 added switch for new mode
3546 IF p_new_mode_fetch = 'Y' THEN
3547 add_select_nmtrans(p_match_table_name);
3548 ELSE
3549
3550 IF (mod(JTF_csr.relation_product,79) = 0 and JTF_csr.relation_product/79 <> 1) or
3551 (mod(JTF_csr.relation_product,137) = 0 and JTF_csr.relation_product/137 <> 1) or
3552 (mod(JTF_csr.relation_product,113) = 0 and JTF_csr.relation_product/113 <> 1) or
3553 (mod(JTF_csr.relation_product,131) = 0 and JTF_csr.relation_product/131 <> 1) or
3554 (mod(JTF_csr.relation_product,163) = 0 and JTF_csr.relation_product/163 <> 1) or
3555 (mod(JTF_csr.relation_product,167) = 0 and JTF_csr.relation_product/167 <> 1) or
3556 (mod(JTF_csr.relation_product,139) = 0 and JTF_csr.relation_product/139 <> 1) THEN
3557
3558 /* account classification,lead expected purchase,opportunity expected purchase */
3559 Add_To_PackageTable (' SELECT /*+ USE_CONCAT */ DISTINCT ');
3560 Add_To_PackageTable (' ILV2.trans_object_id');
3561 Add_To_PackageTable (' , ILV2.trans_detail_object_id');
3562 -- eihsu worker_id 06/05/2003
3563 Add_To_PackageTable (' , ILV2.worker_id');
3564 Add_To_PackageTable (' , ILV2.header_id1');
3565 Add_To_PackageTable (' , ILV2.header_id2');
3566 Add_To_PackageTable (' , p_source_id');
3567 Add_To_PackageTable (' , p_trans_object_type_id');
3568 Add_To_PackageTable (' , l_sysdate');
3569 Add_To_PackageTable (' , L_USER_ID');
3570 Add_To_PackageTable (' , l_sysdate');
3571 Add_To_PackageTable (' , L_USER_ID');
3572 Add_To_PackageTable (' , L_USER_ID');
3573 Add_To_PackageTable (' , L_REQUEST_ID');
3574 Add_To_PackageTable (' , L_PROGRAM_APPL_ID');
3575 Add_To_PackageTable (' , L_PROGRAM_ID');
3576 Add_To_PackageTable (' , l_sysdate');
3577 Add_To_PackageTable (' , ILV2.terr_id');
3578 Add_To_PackageTable (' , ILV2.absolute_rank');
3579 Add_To_PackageTable (' , ILV2.top_level_terr_id');
3580 Add_To_PackageTable (' , ILV2.num_winners');
3581 Add_To_PackageTable (' , ILV2.org_id');
3582 Add_To_PackageTable (' FROM ');
3583 ELSE
3584 Add_To_PackageTable (' SELECT /*+ USE_CONCAT */ DISTINCT ');
3585 Add_To_PackageTable (' trans_object_id');
3586 Add_To_PackageTable (' , trans_detail_object_id');
3587 -- eihsu worker_id 06/05/2003
3588 Add_To_PackageTable (' , worker_id');
3589 Add_To_PackageTable (' , header_id1');
3590 Add_To_PackageTable (' , header_id2');
3591 Add_To_PackageTable (' , p_source_id');
3592 Add_To_PackageTable (' , p_trans_object_type_id');
3593 Add_To_PackageTable (' , l_sysdate');
3594 Add_To_PackageTable (' , L_USER_ID');
3595 Add_To_PackageTable (' , l_sysdate');
3596 Add_To_PackageTable (' , L_USER_ID');
3597 Add_To_PackageTable (' , L_USER_ID');
3598 Add_To_PackageTable (' , L_REQUEST_ID');
3599 Add_To_PackageTable (' , L_PROGRAM_APPL_ID');
3600 Add_To_PackageTable (' , L_PROGRAM_ID');
3601 Add_To_PackageTable (' , l_sysdate');
3602 Add_To_PackageTable (' , ILV.terr_id');
3603 Add_To_PackageTable (' , ILV.absolute_rank');
3604 Add_To_PackageTable (' , ILV.top_level_terr_id');
3605 Add_To_PackageTable (' , ILV.num_winners');
3606 Add_To_PackageTable (' , ILV.org_id');
3607 Add_To_PackageTable (' FROM ');
3608 END IF;
3609 END IF; -- p_new_mode_fetch = 'Y'
3610
3611 l_newline := FND_GLOBAL.Local_Chr(10); /* newline character */
3612 l_indent := ' ';
3613 l_start := 1;
3614 l_next_newline := 0;
3615
3616 IF mod(JTF_csr.relation_product,67) = 0 or mod(JTF_csr.relation_product,73) = 0 THEN
3617
3618 /* brackets are needed after FROM clause because of union generated for qual_usg_id=-1012 */
3619 IF mod(JTF_csr.relation_product,79) = 0 or
3620 mod(JTF_csr.relation_product,137) = 0 or
3621 mod(JTF_csr.relation_product,113) = 0 or
3622 mod(JTF_csr.relation_product,131) = 0 or
3623 mod(JTF_csr.relation_product,163) = 0 or
3624 mod(JTF_csr.relation_product,167) = 0 or
3625 mod(JTF_csr.relation_product,139) = 0 THEN
3626 Add_To_PackageTable (' ( ');
3627 -- dblee: 08/26/03: added p_new_mode_fetch flag argument
3628 Build_Qualifier_Rules2(p_source_id, p_qualifier_type_id, JTF_csr.relation_product,
3629 p_input_table_name, 'N', p_new_mode_fetch, p_sql);
3630
3631 -- dblee: 08/23/03: accommodate the form of the new mode select list
3632 IF p_new_mode_fetch = 'Y' THEN
3633 Add_To_PackageTable (' ) A ; ');
3634 ELSE
3635 Add_To_PackageTable (' ) ILV2 ; ');
3636 END IF;
3637 ELSE
3638 Add_To_PackageTable (' ( ');
3639 Build_Qualifier_Rules(p_source_id,p_qualifier_type_id, JTF_csr.relation_product ,p_input_table_name,'N',p_new_mode_fetch,p_sql,l_ilv2eq,l_ilv2lk,l_ilv2lkp,l_ilv2btwn);
3640
3641 -- dblee: 08/23/03: accommodate the form of the new mode select list
3642 IF p_new_mode_fetch = 'Y' THEN
3643 Add_To_PackageTable (' ) A ; ');
3644 ELSE
3645 Add_To_PackageTable (' ) ILV ; ');
3646 END IF;
3647 END IF;
3648
3649 ELSE -- JTF_csr.relation_product not divisible by 67 or 73
3650
3651 IF (mod(JTF_csr.relation_product,79) = 0 and JTF_csr.relation_product/79 <> 1) or
3652 (mod(JTF_csr.relation_product,137) = 0 and JTF_csr.relation_product/137 <> 1) or
3653 (mod(JTF_csr.relation_product,113) = 0 and JTF_csr.relation_product/113 <> 1) or
3654 (mod(JTF_csr.relation_product,131) = 0 and JTF_csr.relation_product/131 <> 1) or
3655 (mod(JTF_csr.relation_product,163) = 0 and JTF_csr.relation_product/163 <> 1) or
3656 (mod(JTF_csr.relation_product,167) = 0 and JTF_csr.relation_product/167 <> 1) or
3657 (mod(JTF_csr.relation_product,139) = 0 and JTF_csr.relation_product/139 <> 1) THEN
3658
3659 /* need bracket */
3660 Add_To_PackageTable (' ( ');
3661 -- dblee: 08/26/03: added p_new_mode_fetch flag argument
3662 Build_Qualifier_Rules1(p_source_id, p_qualifier_type_id, JTF_csr.relation_product,
3663 p_input_table_name, 'N', p_new_mode_fetch, p_sql);
3664
3665 -- dblee: 08/23/03: accommodate the form of the new mode select list
3666 IF p_new_mode_fetch = 'Y' THEN
3667 Add_To_PackageTable (' ) A ; ');
3668 ELSE
3669 Add_To_PackageTable (' ) ILV2 ; ');
3670 END IF;
3671 ELSE
3672 /* brackets are not needed after FROM clause */
3673 Build_Qualifier_Rules( p_source_id,p_qualifier_type_id,JTF_csr.relation_product,p_input_table_name,'N',p_new_mode_fetch, p_sql,l_ilv2eq,l_ilv2lk,l_ilv2lkp,l_ilv2btwn);
3674 --gen_details_for_terr_change (-1001,-1002,'ABC',p_sql );
3675 Add_To_PackageTable (' ; ');
3676 END IF;
3677
3678 END IF; -- JTF_csr.relation_product divisible by 67 or 73
3679
3680 END IF; -- JTF_csr.relation_product in (4841, 324347, 663217, 353393, 45084233, 44435539, 62598971, 61950277, 924631, 934313)
3681 --Add_To_PackageTable ('END LOOP; ');
3682
3683
3684 Add_To_PackageTable (' ');
3685 Add_To_PackageTable (' x_Msg_Data := ''' || G_DYN_PKG_NAME || '.SEARCH_TERR_RULES: ''');
3686 Add_To_PackageTable (' || ''Qualifier Combination = ''');
3687 Add_To_PackageTable (' || lp_qual_combination_tbl(i)');
3688 Add_To_PackageTable (' || '': # OF ROWS INSERTED = '' || SQL%ROWCOUNT;');
3689 Add_To_PackageTable (' ');
3690 Add_To_PackageTable (' JTF_TAE_CONTROL_PVT.WRITE_LOG(2, x_Msg_Data);');
3691
3692 Add_To_PackageTable (' END IF; ');
3693 Add_To_PackageTable (' ');
3694 Add_To_PackageTable (' EXCEPTION ');
3695 Add_To_PackageTable (G_INDENT1 || ' WHEN NO_DATA_FOUND THEN');
3696 Add_To_PackageTable (' ');
3697 Add_To_PackageTable (G_INDENT1 || ' x_Msg_Data := ''' || G_DYN_PKG_NAME || '.SEARCH_TERR_RULES: ''');
3698 Add_To_PackageTable (G_INDENT1 || ' || ''Qualifier Combination = ''');
3699 Add_To_PackageTable (G_INDENT1 || ' || lp_qual_combination_tbl(i)');
3700 Add_To_PackageTable (G_INDENT1 || ' || ''NO_DATA_FOUND: NO ROWS INSERTED.'';');
3701 Add_To_PackageTable (G_INDENT1 || ' JTF_TAE_CONTROL_PVT.WRITE_LOG(2, x_Msg_Data);');
3702 Add_To_PackageTable (' ');
3703 Add_To_PackageTable (G_INDENT1 || ' WHEN OTHERS THEN');
3704 Add_To_PackageTable (' ');
3705 Add_To_PackageTable (G_INDENT1 || ' x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;');
3706 Add_To_PackageTable (G_INDENT1 || ' x_Msg_Data := ''' || G_DYN_PKG_NAME || '.SEARCH_TERR_RULES: ''');
3707 Add_To_PackageTable (G_INDENT1 || ' || ''Qualifier Combination = ''');
3708 Add_To_PackageTable (G_INDENT1 || ' || lp_qual_combination_tbl(i)');
3709 Add_To_PackageTable (G_INDENT1 || ' || '' OTHERS: Program terminated with OTHERS exception: '' || SQLERRM;');
3710 Add_To_PackageTable (G_INDENT1 || ' JTF_TAE_CONTROL_PVT.WRITE_LOG(2, x_Msg_Data);');
3711 Add_To_PackageTable (' ');
3712 Add_To_PackageTable (G_INDENT1 || ' RAISE FND_API.G_EXC_UNEXPECTED_ERROR;');
3713 Add_To_PackageTable (' ');
3714 Add_To_PackageTable (' END; ');
3715
3716 END LOOP;
3717
3718 Add_To_PackageTable (' COMMIT; ');
3719 Add_To_PackageTable (' i := i + 1; ');
3720 Add_To_PackageTable ('');
3721 Add_To_PackageTable (' END LOOP; ');
3722
3723 /* generate END of PROCEDURE */
3724 generate_end_of_procedure(l_procedure_name, p_source_id, p_target_type);
3725
3726 EXCEPTION
3727 WHEN NO_DATA_FOUND THEN
3728 g_ProgramStatus := 1;
3729
3730 Add_To_PackageTable ('--TERR_RULE_GEN: Unhandled exception NO_DATA_FOUND');
3731 RETURN;
3732
3733 END gen_terr_rules_recurse;
3734
3735
3736 /* ----------------------------------------------------------------
3737 This procedure will generate the Package
3738 called by The Get_WinningTerrMembers API
3739 -----------------------------------------------------------------*/
3740 PROCEDURE generate_nm_api(
3741 errbuf OUT NOCOPY VARCHAR2,
3742 retcode OUT NOCOPY VARCHAR2,
3743 p_source_id IN NUMBER,
3744 p_trans_object_type_id IN NUMBER,
3745 p_debug_flag IN VARCHAR2,
3746 p_sql_trace IN VARCHAR2)
3747 AS
3748 num_of_combination NUMBER(15);
3749 package_name VARCHAR2(30);
3750 package_desc VARCHAR2(100);
3751
3752 l_terr_id NUMBER;
3753
3754 l_abs_source_id NUMBER;
3755
3756
3757 l_package_name VARCHAR2(30);
3758
3759 l_Retcode VARCHAR2(10);
3760 l_message VARCHAR2(2000);
3761
3762 lp_sysdate DATE := SYSDATE;
3763
3764 query_str VARCHAR2(255);
3765 l_object_name VARCHAR2(128);
3766 l_object_type VARCHAR2(18);
3767 l_created DATE;
3768 l_last_ddl_time DATE;
3769 l_timestamp VARCHAR2(19);
3770 l_status VARCHAR2(7);
3771
3772 l_denorm_count NUMBER;
3773 l_mv1_count NUMBER;
3774 l_mv2_count NUMBER;
3775 l_mv3_count NUMBER;
3776 l_mv4_count NUMBER;
3777 l_mv5_count NUMBER;
3778 l_mv6_count NUMBER;
3779 l_target_type VARCHAR2(30);
3780 l_input_table_name VARCHAR2(30);
3781 l_match_table_name VARCHAR2(30);
3782 l_Return_Status VARCHAR2(1);
3783 l_Msg_Count NUMBER;
3784 l_Msg_Data VARCHAR2(2000);
3785
3786 BEGIN
3787
3788 --dbms_output.put_line('JTF_TAE_GEN_PVT.generate_ap: BEGIN');
3789 -- Initialize Global variables
3790
3791 G_Debug := FALSE;
3792
3793 -- dblee: 09/02/03 set select clause global for new mode TAP
3794 g_select_list_1 := k_select_list_fm;
3795
3796 l_abs_source_id := ABS(p_source_id);
3797 IF p_source_id = -1001 THEN
3798 IF p_trans_object_type_id = -1002 THEN
3799 l_target_type := 'ACCOUNT';
3800 l_input_table_name := 'JTF_TAE_1001_ACCOUNT_NM_TRANS';
3801 l_match_table_name := 'JTF_TAE_1001_ACCOUNT_MATCHES';
3802
3803 ELSIF p_trans_object_type_id = -1003 THEN
3804 l_target_type := 'LEAD';
3805 l_input_table_name := 'JTF_TAE_1001_LEAD_NM_TRANS';
3806 l_match_table_name := 'JTF_TAE_1001_LEAD_MATCHES';
3807
3808 ELSIF p_trans_object_type_id = -1004 THEN
3809 l_target_type := 'OPPOR';
3810 l_input_table_name := 'JTF_TAE_1001_OPPOR_NM_TRANS';
3811 l_match_table_name := 'JTF_TAE_1001_OPPOR_MATCHES';
3812 END IF;
3813 END IF;
3814
3815 /* If the SQL trace flag is turned on, then turn on the trace */
3816 /* ARPATEL: 12/15/2003: Bug#3305019 fix */
3817 --IF UPPER(p_SQL_Trace) = 'Y' THEN
3818 -- dbms_session.set_sql_trace(TRUE);
3819 --END IF;
3820
3821 /* call procedure to populate prod_relation */
3822 /* ARPATEL: not needed for NM as it has been done for this aource_id/trans_id combo
3823 JTF_TAE_CONTROL_PVT.Decompose_Terr_Defns
3824 (p_Api_Version_Number => 1.0,
3825 p_Init_Msg_List => FND_API.G_FALSE,
3826 p_Commit => FND_API.G_FALSE,
3827 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3828 x_Return_Status => l_return_status,
3829 x_Msg_Count => l_Msg_Count,
3830 x_Msg_Data => l_Msg_Data,
3831 p_run_mode => 'FULL',
3832 p_classify_terr_comb => 'Y',
3833 p_process_tx_oin_sel => 'N',
3834 p_generate_indexes => 'N',
3835 p_source_id => p_source_id,
3836 p_trans_id => p_trans_object_type_id,
3837 errbuf => ERRBUF,
3838 retcode => RETCODE );
3839 */
3840
3841 /* If the debug flag is set, THEN turn on the debug message logging */
3842 IF UPPER( rtrim(p_Debug_Flag) ) = 'Y' THEN
3843 G_Debug := TRUE;
3844 END IF;
3845
3846 /* Check for territories for this Usage/Transaction Type */
3847 BEGIN
3848
3849 SELECT COUNT(*)
3850 INTO num_of_combination
3851 FROM jtf_tae_qual_products jtqp
3852 WHERE jtqp.source_id = p_source_id
3853 AND jtqp.trans_object_type_id = p_trans_object_type_id;
3854
3855 --dbms_output.put_line('JTF_TAE_GEN_PVT:' || num_of_combination);
3856
3857 EXCEPTION
3858 WHEN NO_DATA_FOUND THEN
3859 num_of_combination := 0;
3860 END;
3861
3862 IF G_Debug THEN
3863
3864 Write_Log(2, ' ');
3865 Write_Log(2, '/***************** BEGIN: BATCH TAE: TERRITORY STATUS *********************/');
3866 Write_Log(2, ' ');
3867 Write_Log(2, 'Inside Generate_API initialize');
3868 Write_Log(2, 'source_id - ' || TO_CHAR(p_Source_Id) );
3869 Write_Log(2, 'Number of valid Qualifier Combinations: ' || num_of_combination );
3870 Write_Log(2, ' ');
3871 Write_Log(2, '/***************** END: BATCH TAE: TERRITORY STATUS *********************/');
3872 Write_Log(2, ' ');
3873
3874 END IF;
3875
3876 IF G_Debug THEN
3877
3878 Write_Log(2, ' ');
3879 Write_Log(2, '/***************** BEGIN: BATCH TAE: PACKAGE STATUS *********************/');
3880 Write_Log(2, ' ');
3881
3882 END IF;
3883
3884 /* territories exist for this USAGE combination */
3885 IF (num_of_combination > 0) THEN
3886
3887 /* Catch-All territory */
3888 l_terr_id := 1;
3889
3890 /* Generate Package NAME */
3891 -- dblee: 09/02/03: new mode matching phase, not new mode fetch
3892 l_package_name := 'JTF_TAE_' || TO_CHAR (l_abs_source_id) ||'_' || l_target_type|| '_NM_DYN';
3893 G_DYN_PKG_NAME := l_package_name;
3894
3895 /* Generate Package BODY */
3896 package_desc := '/* Auto Generated Package */';
3897 generate_package_header(l_package_name, package_desc, 'PKB');
3898
3899
3900 /* generate individual SQL statements territories */
3901 gen_terr_rules_recurse (
3902 p_terr_id => l_terr_id,
3903 p_source_id => p_source_id,
3904 p_qualifier_type_id => p_trans_object_type_id,
3905 p_target_type => l_target_type,
3906 p_input_table_name => l_input_table_name,
3907 p_match_table_name => l_match_table_name,
3908 p_search_name => 'SEARCH_TERR_RULES',
3909 -- dblee: 09/02/03: new mode matching phase, not new mode fetch
3910 p_new_mode_fetch => 'N' );
3911
3912 --dbms_output.put_line('NEW ENGINE: Value of l_package_name='||l_package_name);
3913
3914 /* generate end of package BODY */
3915
3916 generate_end_of_package(l_package_name, 'TRUE');
3917
3918 ELSE
3919
3920 IF (G_Debug) THEN
3921 Write_Log(2, 'PACKAGE ' || l_package_name || ' NOT CREATED SUCCESSFULLY: no territories exist for this Usage/Transaction combination. ');
3922 END IF;
3923
3924 g_ProgramStatus := 1;
3925
3926 END IF; /* num_of_combination > 0 */
3927
3928 /* check status of DYNAMICALLY CREATED PACKAGE */
3929 BEGIN
3930
3931 query_str :=
3932 ' SELECT uo.object_name, uo.object_type, uo.created, uo.last_ddl_time, uo.timestamp, uo.status' ||
3933 ' FROM user_objects uo' ||
3934 ' WHERE uo.object_type = ''PACKAGE BODY'' AND uo.object_name = :b1 and rownum < 2';
3935
3936 EXECUTE IMMEDIATE query_str
3937 INTO l_object_name
3938 , l_object_type
3939 , l_created
3940 , l_last_ddl_time
3941 , l_timestamp
3942 , l_status
3943 USING l_package_name ;
3944
3945 IF l_status = 'INVALID' THEN
3946
3947 /* ACHANDA 03/08/2004 : Bug 3380047 : if the package is created in invalid status the message is */
3948 /* written to the log file irrespective of wthether debug flag is set to true or false */
3949 Write_Log(1, 'Status of the package ' || l_package_name || ' IS INVALID. ');
3950 IF G_Debug THEN
3951 Write_Log(2, ' ');
3952 Write_Log(2, 'PACKAGE ' || l_package_name || ' NOT CREATED SUCCESSFULLY: cannot be compiled. ');
3953 Write_Log(2, ' ');
3954 END IF;
3955
3956 /* ACHANDA 03/08/2004 : Bug 3380047 : set the value to 2 to */
3957 /* distinguish this exception from other exceptions in the program */
3958 g_ProgramStatus := 2;
3959
3960 END IF;
3961
3962 EXCEPTION
3963 WHEN others THEN
3964 NULL;
3965 END;
3966
3967 IF G_Debug THEN
3968
3969 Write_Log(2, ' ');
3970 Write_Log(2, l_object_type || ': ' || l_package_name);
3971 Write_Log(2, 'Created: ' || TO_CHAR(l_created) );
3972 Write_Log(2, 'Last DDL Time: ' || TO_CHAR(l_last_ddl_time) );
3973 Write_Log(2, 'Timestamp: ' || l_timestamp );
3974 Write_Log(2, 'Status: ' || l_status );
3975 Write_Log(2, ' ');
3976 Write_Log(2, '/***************** END: BATCH TAE: PACKAGE STATUS *********************/');
3977 Write_Log(2, ' ');
3978
3979 END IF;
3980
3981
3982 EXCEPTION
3983 WHEN utl_file.invalid_path OR utl_file.invalid_mode OR
3984 utl_file.invalid_filehandle OR utl_file.invalid_operation OR
3985 utl_file.write_error THEN
3986 ERRBUF := 'Program terminated with exception. Error writing to output file.';
3987 RETCODE := 2;
3988
3989 WHEN OTHERS THEN
3990 IF G_Debug THEN
3991 Write_Log(1,'Program terminated with OTHERS exception. ' || SQLERRM);
3992 END IF;
3993 ERRBUF := 'Program terminated with OTHERS exception. ' || SQLERRM;
3994 RETCODE := 2;
3995 END generate_nm_api;
3996
3997
3998 /* ----------------------------------------------------------------
3999 This procedure will generate the Package
4000 called by [step 5 in the NM TAP process - populate NM_TRANS]
4001 dblee: 08/15/03
4002 -----------------------------------------------------------------*/
4003 PROCEDURE generate_nm0_api (
4004 errbuf OUT NOCOPY VARCHAR2,
4005 retcode OUT NOCOPY VARCHAR2,
4006 p_source_id IN NUMBER,
4007 p_trans_object_type_id IN NUMBER,
4008 p_debug_flag IN VARCHAR2,
4009 p_sql_trace IN VARCHAR2
4010 )
4011 AS
4012 num_of_combination NUMBER(15);
4013 package_name VARCHAR2(30);
4014 package_desc VARCHAR2(100);
4015
4016 l_terr_id NUMBER;
4017
4018 l_abs_source_id NUMBER;
4019
4020
4021 l_package_name VARCHAR2(30);
4022
4023 l_Retcode VARCHAR2(10);
4024 l_message VARCHAR2(2000);
4025
4026 lp_sysdate DATE := SYSDATE;
4027
4028 query_str VARCHAR2(255);
4029 l_object_name VARCHAR2(128);
4030 l_object_type VARCHAR2(18);
4031 l_created DATE;
4032 l_last_ddl_time DATE;
4033 l_timestamp VARCHAR2(19);
4034 l_status VARCHAR2(7);
4035
4036 l_denorm_count NUMBER;
4037 l_mv1_count NUMBER;
4038 l_mv2_count NUMBER;
4039 l_mv3_count NUMBER;
4040 l_mv4_count NUMBER;
4041 l_mv5_count NUMBER;
4042 l_mv6_count NUMBER;
4043 l_target_type VARCHAR2(30);
4044 l_input_table_name VARCHAR2(30);
4045 l_match_table_name VARCHAR2(30);
4046 l_Return_Status VARCHAR2(1);
4047 l_Msg_Count NUMBER;
4048 l_Msg_Data VARCHAR2(2000);
4049
4050 BEGIN
4051 -- dbms_output.put_line('JTF_TAE_GEN_PVT.generate_ap: BEGIN');
4052
4053 -- Initialize Global variables
4054 G_Debug := FALSE;
4055
4056 -- dblee: 08/22/03 set select clause global for new mode TAP
4057 g_select_list_1 := k_select_list_nm;
4058
4059 l_abs_source_id := ABS(p_source_id);
4060 IF p_source_id = -1001 THEN
4061 IF p_trans_object_type_id = -1002 THEN
4062 l_target_type := 'ACCOUNT';
4063 l_input_table_name := 'JTF_TAE_1001_ACCOUNT_TRANS';
4064 l_match_table_name := 'JTF_TAE_1001_ACCOUNT_NM_TRANS';
4065
4066 ELSIF p_trans_object_type_id = -1003 THEN
4067 l_target_type := 'LEAD';
4068 l_input_table_name := 'JTF_TAE_1001_LEAD_TRANS';
4069 l_match_table_name := 'JTF_TAE_1001_LEAD_NM_TRANS';
4070
4071 ELSIF p_trans_object_type_id = -1004 THEN
4072 l_target_type := 'OPPOR';
4073 l_input_table_name := 'JTF_TAE_1001_OPPOR_TRANS';
4074 l_match_table_name := 'JTF_TAE_1001_OPPOR_NM_TRANS';
4075 END IF;
4076 END IF;
4077
4078 /* If the SQL trace flag is turned on, then turn on the trace */
4079 /* ARPATEL: 12/15/2003: Bug#3305019 fix */
4080 --IF UPPER(p_SQL_Trace) = 'Y' THEN
4081 -- dbms_session.set_sql_trace(TRUE);
4082 --END IF;
4083
4084 /* call procedure to populate prod_relation */
4085 /* ARPATEL: this is not needed for NM as it has already been executed for this source_id/trans_id
4086 JTF_TAE_CONTROL_PVT.Decompose_Terr_Defns(
4087 p_Api_Version_Number => 1.0,
4088 p_Init_Msg_List => FND_API.G_FALSE,
4089 p_Commit => FND_API.G_FALSE,
4090 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4091 x_Return_Status => l_return_status,
4092 x_Msg_Count => l_Msg_Count,
4093 x_Msg_Data => l_Msg_Data,
4094 p_run_mode => 'FULL',
4095 p_classify_terr_comb => 'Y',
4096 p_process_tx_oin_sel => 'N',
4097 p_generate_indexes => 'N',
4098 p_source_id => p_source_id,
4099 p_trans_id => p_trans_object_type_id,
4100 errbuf => ERRBUF,
4101 retcode => RETCODE );
4102 */
4103
4104 /* If the debug flag is set, Then turn on the debug message logging */
4105 IF UPPER(RTRIM(p_Debug_Flag)) = 'Y' THEN
4106 G_Debug := TRUE;
4107 END IF;
4108
4109 /* Check for territories for this Usage/Transaction Type */
4110 BEGIN
4111
4112 SELECT COUNT(*)
4113 INTO num_of_combination
4114 FROM jtf_tae_qual_products jtqp
4115 WHERE jtqp.source_id = p_source_id
4116 AND jtqp.trans_object_type_id = p_trans_object_type_id;
4117
4118 --dbms_output.put_line('JTF_TAE_GEN_PVT:' || num_of_combination);
4119
4120 EXCEPTION
4121 WHEN NO_DATA_FOUND THEN
4122 num_of_combination := 0;
4123 END;
4124
4125 IF G_Debug THEN
4126 Write_Log(2, ' ');
4127 Write_Log(2, '/***************** BEGIN: BATCH TAE: TERRITORY STATUS *********************/');
4128 Write_Log(2, ' ');
4129 Write_Log(2, 'Inside Generate_API initialize');
4130 Write_Log(2, 'source_id - ' || TO_CHAR(p_Source_Id) );
4131 Write_Log(2, 'Number of valid Qualifier Combinations: ' || num_of_combination );
4132 Write_Log(2, ' ');
4133 Write_Log(2, '/***************** END: BATCH TAE: TERRITORY STATUS *********************/');
4134 Write_Log(2, ' ');
4135 END IF;
4136
4137 IF G_Debug THEN
4138 Write_Log(2, ' ');
4139 Write_Log(2, '/***************** BEGIN: BATCH TAE: PACKAGE STATUS *********************/');
4140 Write_Log(2, ' ');
4141 END IF;
4142
4143 /* territories exist for this USAGE combination */
4144 IF num_of_combination > 0 THEN
4145 /* Catch-All territory */
4146 l_terr_id := 1;
4147
4148 /* Generate Package NAME */
4149 -- dblee: 09/02/03: new mode matching phase, not new mode fetch
4150 l_package_name := 'JTF_TAE_' || TO_CHAR(l_abs_source_id) || '_' || l_target_type || '_NMC_DYN';
4151 G_DYN_PKG_NAME := l_package_name;
4152
4153 /* Generate Package BODY */
4154 package_desc := '/* Auto Generated Package */';
4155 generate_package_header(l_package_name, package_desc, 'PKB');
4156
4157 /* generate individual SQL statements territories */
4158 gen_terr_rules_recurse(
4159 p_terr_id => l_terr_id,
4160 p_source_id => p_source_id,
4161 p_qualifier_type_id => p_trans_object_type_id,
4162 p_target_type => l_target_type,
4163 p_input_table_name => l_input_table_name,
4164 p_match_table_name => l_match_table_name,
4165 p_search_name => 'SEARCH_TERR_RULES',
4166 p_new_mode_fetch => 'Y' );
4167
4168 --dbms_output.put_line('NEW ENGINE: Value of l_package_name='||l_package_name);
4169
4170 /* generate end of package BODY */
4171 generate_end_of_package(l_package_name, 'TRUE');
4172
4173 ELSE -- num_of_combination = 0
4174
4175 IF G_Debug THEN
4176 Write_Log(2, 'PACKAGE ' || l_package_name
4177 || ' NOT CREATED SUCCESSFULLY: no territories exist for this Usage/Transaction combination. ');
4178 END IF;
4179
4180 g_ProgramStatus := 1;
4181 END IF; /* num_of_combination > 0 */
4182
4183 /* check status of DYNAMICALLY CREATED PACKAGE */
4184 BEGIN
4185
4186 query_str :=
4187 ' SELECT uo.object_name, uo.object_type, uo.created, uo.last_ddl_time, uo.timestamp, uo.status' ||
4188 ' FROM user_objects uo' ||
4189 ' WHERE uo.object_type = ''PACKAGE BODY'' AND uo.object_name = :b1 and rownum < 2';
4190
4191 EXECUTE IMMEDIATE query_str
4192 INTO l_object_name
4193 , l_object_type
4194 , l_created
4195 , l_last_ddl_time
4196 , l_timestamp
4197 , l_status
4198 USING l_package_name ;
4199
4200 IF l_status = 'INVALID' THEN
4201 /* ACHANDA 03/08/2004 : Bug 3380047 : if the package is created in invalid status the message is */
4202 /* written to the log file irrespective of wthether debug flag is set to true or false */
4203 Write_Log(1, 'Status of the package ' || l_package_name || ' IS INVALID. ');
4204 IF G_Debug THEN
4205 Write_Log(2, ' ');
4206 Write_Log(2, 'PACKAGE ' || l_package_name || ' NOT CREATED SUCCESSFULLY: cannot be compiled. ');
4207 Write_Log(2, ' ');
4208 END IF;
4209
4210 /* ACHANDA 03/08/2004 : Bug 3380047 : set the value to 2 to */
4211 /* distinguish this exception from other exceptions in the program */
4212 g_ProgramStatus := 2;
4213 END IF;
4214
4215 EXCEPTION
4216 WHEN others THEN
4217 NULL;
4218 END;
4219
4220 IF G_Debug THEN
4221 Write_Log(2, ' ');
4222 Write_Log(2, l_object_type || ': ' || l_package_name);
4223 Write_Log(2, 'Created: ' || TO_CHAR(l_created) );
4224 Write_Log(2, 'Last DDL Time: ' || TO_CHAR(l_last_ddl_time) );
4225 Write_Log(2, 'Timestamp: ' || l_timestamp );
4226 Write_Log(2, 'Status: ' || l_status );
4227 Write_Log(2, ' ');
4228 Write_Log(2, '/***************** END: BATCH TAE: PACKAGE STATUS *********************/');
4229 Write_Log(2, ' ');
4230 END IF;
4231
4232 EXCEPTION
4233 WHEN utl_file.invalid_path OR utl_file.invalid_mode OR
4234 utl_file.invalid_filehandle OR utl_file.invalid_operation OR
4235 utl_file.write_error THEN
4236 ERRBUF := 'Program terminated with exception. Error writing to output file.';
4237 RETCODE := 2;
4238
4239 WHEN OTHERS THEN
4240 IF G_Debug THEN
4241 Write_Log(1,'Program terminated with OTHERS exception. ' || SQLERRM);
4242 END IF;
4243 ERRBUF := 'Program terminated with OTHERS exception. ' || SQLERRM;
4244 RETCODE := 2;
4245 END generate_nm0_api;
4246
4247 --Sales Credit
4248 PROCEDURE generate_sc_api (
4249 errbuf OUT NOCOPY VARCHAR2,
4250 retcode OUT NOCOPY VARCHAR2,
4251 p_source_id IN NUMBER,
4252 p_trans_object_type_id IN NUMBER,
4253 p_debug_flag IN VARCHAR2,
4254 p_sql_trace IN VARCHAR2
4255 )
4256 AS
4257
4258
4259 num_of_combination NUMBER(15);
4260 package_name VARCHAR2(30);
4261 package_desc VARCHAR2(100);
4262
4263 --l_index NUMBER;
4264 l_terr_id NUMBER;
4265
4266 l_abs_source_id NUMBER;
4267
4268
4269 l_package_name VARCHAR2(30);
4270
4271 l_Retcode VARCHAR2(10);
4272 l_message VARCHAR2(2000);
4273
4274 lp_sysdate DATE := SYSDATE;
4275
4276 query_str VARCHAR2(255);
4277 l_object_name VARCHAR2(128);
4278 l_object_type VARCHAR2(18);
4279 l_created DATE;
4280 l_last_ddl_time DATE;
4281 l_timestamp VARCHAR2(19);
4282 l_status VARCHAR2(7);
4283
4284 l_denorm_count NUMBER;
4285 l_mv1_count NUMBER;
4286 l_mv2_count NUMBER;
4287 l_mv3_count NUMBER;
4288 l_mv4_count NUMBER;
4289 l_mv5_count NUMBER;
4290 l_mv6_count NUMBER;
4291 l_target_type VARCHAR2(30);
4292 l_input_table_name VARCHAR2(30);
4293 l_match_table_name VARCHAR2(30);
4294 l_Return_Status VARCHAR2(1);
4295 l_Msg_Count NUMBER;
4296 l_Msg_Data VARCHAR2(2000);
4297
4298 BEGIN
4299
4300 --dbms_output.put_line('JTF_TAE_GEN_PVT.generate_ap: BEGIN');
4301 -- Initialize Global variables
4302
4303 G_Debug := FALSE;
4304
4305 -- dblee: 09/02/03 set select clause global for full mode TAP
4306 g_select_list_1 := k_select_list_fm;
4307
4308 l_abs_source_id := ABS(p_source_id);
4309
4310 l_target_type := 'SCREDIT';
4311 l_input_table_name := 'JTF_TAE_1001_SC_TRANS';
4312 l_match_table_name := 'JTF_TAE_1001_SC_MATCHES';
4313
4314 /* If the SQL trace flag is turned on, then turn on the trace */
4315 /* ARPATEL: 12/15/2003: Bug#3305019 fix */
4316 --IF UPPER(p_SQL_Trace) = 'Y' THEN
4317 -- dbms_session.set_sql_trace(TRUE);
4318 --END IF;
4319
4320 /* call procedure to populate prod_relation */
4321 /* ARPATEL: this is not needed as SC is based on ACCOUNT
4322 JTF_TAE_CONTROL_PVT.Decompose_Terr_Defns
4323 (p_Api_Version_Number => 1.0,
4324 p_Init_Msg_List => FND_API.G_FALSE,
4325 p_Commit => FND_API.G_FALSE,
4326 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4327 x_Return_Status => l_return_status,
4328 x_Msg_Count => l_Msg_Count,
4329 x_Msg_Data => l_Msg_Data,
4330 p_run_mode => 'FULL',
4331 p_classify_terr_comb => 'Y',
4332 p_process_tx_oin_sel => 'N',
4333 p_generate_indexes => 'N',
4334 p_source_id => p_source_id,
4335 p_trans_id => p_trans_object_type_id,
4336 errbuf => ERRBUF,
4337 retcode => RETCODE );
4338 */
4339
4340 /* If the debug flag is set, THEN turn on the debug message logging */
4341 IF UPPER( rtrim(p_Debug_Flag) ) = 'Y' THEN
4342 G_Debug := TRUE;
4343 END IF;
4344
4345 /* Check for territories for this Usage/Transaction Type */
4346 BEGIN
4347
4348 SELECT COUNT(*)
4349 INTO num_of_combination
4350 FROM jtf_tae_qual_products jtqp
4351 WHERE jtqp.source_id = p_source_id
4352 AND jtqp.trans_object_type_id = p_trans_object_type_id;
4353
4354 --dbms_output.put_line('JTF_TAE_GEN_PVT:' || num_of_combination);
4355
4356 EXCEPTION
4357 WHEN NO_DATA_FOUND THEN
4358 num_of_combination := 0;
4359 END;
4360
4361 IF G_Debug THEN
4362
4363 Write_Log(2, ' ');
4364 Write_Log(2, '/***************** BEGIN: BATCH TAE: TERRITORY STATUS *********************/');
4365 Write_Log(2, ' ');
4366 Write_Log(2, 'Inside Generate_API initialize');
4367 Write_Log(2, 'source_id - ' || TO_CHAR(p_Source_Id) );
4368 Write_Log(2, 'Number of valid Qualifier Combinations: ' || num_of_combination );
4369 Write_Log(2, ' ');
4370 Write_Log(2, '/***************** END: BATCH TAE: TERRITORY STATUS *********************/');
4371 Write_Log(2, ' ');
4372
4373 END IF;
4374
4375 IF G_Debug THEN
4376
4377 Write_Log(2, ' ');
4378 Write_Log(2, '/***************** BEGIN: BATCH TAE: PACKAGE STATUS *********************/');
4379 Write_Log(2, ' ');
4380
4381 END IF;
4382
4383 /* territories exist for this USAGE combination */
4384 IF (num_of_combination > 0) THEN
4385
4386 /* Catch-All territory */
4387 l_terr_id := 1;
4388
4389 /* Generate Package NAME */
4390 l_package_name := 'JTF_TAE_' || TO_CHAR (l_abs_source_id) ||'_' || l_target_type|| '_DYN';
4391 G_DYN_PKG_NAME := l_package_name;
4392
4393 /* Generate Package BODY */
4394 package_desc := '/* Auto Generated Package */';
4395 generate_package_header(l_package_name, package_desc, 'PKB');
4396
4397 /* generate individual SQL statements territories */
4398 gen_terr_rules_recurse (
4399 p_terr_id => l_terr_id,
4400 p_source_id => p_source_id,
4401 p_qualifier_type_id => p_trans_object_type_id,
4402 p_target_type => l_target_type,
4403 p_input_table_name => l_input_table_name,
4404 p_match_table_name => l_match_table_name,
4405 p_search_name => 'SEARCH_TERR_RULES' );
4406
4407 --dbms_output.put_line('NEW ENGINE: Value of l_package_name='||l_package_name);
4408
4409 /* generate end of package BODY */
4410 generate_end_of_package(l_package_name, 'TRUE');
4411
4412 ELSE
4413
4414 IF (G_Debug) THEN
4415 Write_Log(2, 'PACKAGE ' || l_package_name || ' NOT CREATED SUCCESSFULLY: no territories exist for this Usage/Transaction combination. ');
4416 END IF;
4417
4418 g_ProgramStatus := 1;
4419
4420 END IF; /* num_of_combination > 0 */
4421
4422 /* check status of DYNAMICALLY CREATED PACKAGE */
4423 BEGIN
4424
4425 query_str :=
4426 ' SELECT uo.object_name, uo.object_type, uo.created, uo.last_ddl_time, uo.timestamp, uo.status' ||
4427 ' FROM user_objects uo' ||
4428 ' WHERE uo.object_type = ''PACKAGE BODY'' AND uo.object_name = :b1 and rownum < 2';
4429
4430 EXECUTE IMMEDIATE query_str
4431 INTO l_object_name
4432 , l_object_type
4433 , l_created
4434 , l_last_ddl_time
4435 , l_timestamp
4436 , l_status
4437 USING l_package_name ;
4438
4439 IF l_status = 'INVALID' THEN
4440
4441 /* ACHANDA 03/08/2004 : Bug 3380047 : if the package is created in invalid status the message is */
4442 /* written to the log file irrespective of wthether debug flag is set to true or false */
4443 Write_Log(1, 'Status of the package ' || l_package_name || ' IS INVALID. ');
4444 IF G_Debug THEN
4445 Write_Log(2, ' ');
4446 Write_Log(2, 'PACKAGE ' || l_package_name || ' NOT CREATED SUCCESSFULLY: cannot be compiled. ');
4447 Write_Log(2, ' ');
4448 END IF;
4449
4450 /* ACHANDA 03/08/2004 : Bug 3380047 : set the value to 2 to */
4451 /* distinguish this exception from other exceptions in the program */
4452 g_ProgramStatus := 2;
4453
4454 END IF;
4455
4456 EXCEPTION
4457 WHEN others THEN
4458 NULL;
4459 END;
4460
4461 IF G_Debug THEN
4462
4463 Write_Log(2, ' ');
4464 Write_Log(2, l_object_type || ': ' || l_package_name);
4465 Write_Log(2, 'Created: ' || TO_CHAR(l_created) );
4466 Write_Log(2, 'Last DDL Time: ' || TO_CHAR(l_last_ddl_time) );
4467 Write_Log(2, 'Timestamp: ' || l_timestamp );
4468 Write_Log(2, 'Status: ' || l_status );
4469 Write_Log(2, ' ');
4470 Write_Log(2, '/***************** END: BATCH TAE: PACKAGE STATUS *********************/');
4471 Write_Log(2, ' ');
4472
4473 END IF;
4474
4475
4476 EXCEPTION
4477 WHEN utl_file.invalid_path OR utl_file.invalid_mode OR
4478 utl_file.invalid_filehandle OR utl_file.invalid_operation OR
4479 utl_file.write_error THEN
4480 ERRBUF := 'Program terminated with exception. Error writing to output file.';
4481 RETCODE := 2;
4482
4483 WHEN OTHERS THEN
4484 IF G_Debug THEN
4485 Write_Log(1,'Program terminated with OTHERS exception. ' || SQLERRM);
4486 END IF;
4487 ERRBUF := 'Program terminated with OTHERS exception. ' || SQLERRM;
4488 RETCODE := 2;
4489 END generate_sc_api;
4490
4491
4492 /* ----------------------------------------------------------------
4493 This procedure will generate the Package
4494 called by The Get_WinningTerrMembers API
4495 -----------------------------------------------------------------*/
4496 PROCEDURE generate_api (
4497 errbuf OUT NOCOPY VARCHAR2,
4498 retcode OUT NOCOPY VARCHAR2,
4499 p_source_id IN NUMBER,
4500 p_trans_object_type_id IN NUMBER,
4501 p_target_type IN VARCHAR2,
4502 p_debug_flag IN VARCHAR2,
4503 p_sql_trace IN VARCHAR2
4504 )
4505 AS
4506
4507
4508 num_of_combination NUMBER(15);
4509 package_name VARCHAR2(30);
4510 package_desc VARCHAR2(100);
4511
4512 --l_index NUMBER;
4513 l_terr_id NUMBER;
4514
4515 l_abs_source_id NUMBER;
4516
4517
4518 l_package_name VARCHAR2(30);
4519
4520 l_Retcode VARCHAR2(10);
4521 l_message VARCHAR2(2000);
4522
4523 lp_sysdate DATE := SYSDATE;
4524
4525 query_str VARCHAR2(255);
4526 l_object_name VARCHAR2(128);
4527 l_object_type VARCHAR2(18);
4528 l_created DATE;
4529 l_last_ddl_time DATE;
4530 l_timestamp VARCHAR2(19);
4531 l_status VARCHAR2(7);
4532
4533 l_denorm_count NUMBER;
4534 l_mv1_count NUMBER;
4535 l_mv2_count NUMBER;
4536 l_mv3_count NUMBER;
4537 l_mv4_count NUMBER;
4538 l_mv5_count NUMBER;
4539 l_mv6_count NUMBER;
4540 l_target_type VARCHAR2(30);
4541 l_input_table_name VARCHAR2(30);
4542 l_match_table_name VARCHAR2(30);
4543 l_Return_Status VARCHAR2(1);
4544 l_Msg_Count NUMBER;
4545 l_Msg_Data VARCHAR2(2000);
4546
4547 --arpatel 09/03/2003
4548 l_nm0_message VARCHAR2(2000);
4549 l_nm0_Retcode VARCHAR2(10);
4550 l_nm_message VARCHAR2(2000);
4551 l_nm_Retcode VARCHAR2(10);
4552 l_sc_message VARCHAR2(2000);
4553 l_sc_Retcode VARCHAR2(10);
4554
4555 BEGIN
4556
4557 --dbms_output.put_line('JTF_TAE_GEN_PVT.generate_ap: BEGIN');
4558 -- Initialize Global variables
4559
4560 G_Debug := FALSE;
4561
4562 /* ACHANDA 03/08/2004 : Bug 3380047 : some of the dynamic packages are */
4563 /* created in "INVALID" status as g_pointer is not properly initialized */
4564 g_pointer := 0;
4565
4566 -- dblee: 09/02/03 set select clause global for full mode TAP
4567 g_select_list_1 := k_select_list_fm;
4568
4569 l_abs_source_id := ABS(p_source_id);
4570 IF p_source_id = -1001 THEN
4571
4572 IF p_target_type = 'RPT' THEN
4573 l_target_type := p_target_type;
4574 l_input_table_name := 'JTF_TAE_1001_RPT_TRANS';
4575 l_match_table_name := 'JTF_TAE_1001_RPT_MATCHES';
4576
4577 ELSE
4578 IF p_trans_object_type_id = -1002 THEN
4579 l_target_type := 'ACCOUNT';
4580 l_input_table_name := 'JTF_TAE_1001_ACCOUNT_TRANS';
4581 l_match_table_name := 'JTF_TAE_1001_ACCOUNT_MATCHES';
4582
4583 ELSIF p_trans_object_type_id = -1003 THEN
4584 l_target_type := 'LEAD';
4585 l_input_table_name := 'JTF_TAE_1001_LEAD_TRANS';
4586 l_match_table_name := 'JTF_TAE_1001_LEAD_MATCHES';
4587
4588 ELSIF p_trans_object_type_id = -1004 THEN
4589 l_target_type := 'OPPOR';
4590 l_input_table_name := 'JTF_TAE_1001_OPPOR_TRANS';
4591 l_match_table_name := 'JTF_TAE_1001_OPPOR_MATCHES';
4592
4593 END IF;
4594 END IF;
4595 END IF;
4596
4597 /* If the SQL trace flag is turned on, then turn on the trace */
4598 /* ARPATEL: 12/15/2003: Bug#3305019 fix */
4599 --IF UPPER(p_SQL_Trace) = 'Y' THEN
4600 -- dbms_session.set_sql_trace(TRUE);
4601 --END IF;
4602
4603 /* call procedure to populate prod_relation */
4604 JTF_TAE_CONTROL_PVT.Decompose_Terr_Defns
4605 (p_Api_Version_Number => 1.0,
4606 p_Init_Msg_List => FND_API.G_FALSE,
4607 p_Commit => FND_API.G_FALSE,
4608 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4609 x_Return_Status => l_return_status,
4610 x_Msg_Count => l_Msg_Count,
4611 x_Msg_Data => l_Msg_Data,
4612 p_run_mode => 'FULL',
4613 p_classify_terr_comb => 'Y',
4614 p_process_tx_oin_sel => 'N',
4615 p_generate_indexes => 'N',
4616 p_source_id => p_source_id,
4617 p_trans_id => p_trans_object_type_id,
4618 errbuf => ERRBUF,
4619 retcode => RETCODE );
4620
4621 /* If the debug flag is set, THEN turn on the debug message logging */
4622 IF UPPER( rtrim(p_Debug_Flag) ) = 'Y' THEN
4623 G_Debug := TRUE;
4624 END IF;
4625
4626 if p_source_id = -1001 and p_trans_object_type_id = -1002
4627 then
4628 --ARPATEL 09/04/2003
4629 --create sales credit dyn package
4630 generate_sc_api (
4631 errbuf => l_sc_message ,
4632 retcode => l_sc_Retcode ,
4633 p_source_id => p_source_id,
4634 p_trans_object_type_id => p_trans_object_type_id,
4635 p_debug_flag => p_debug_flag,
4636 p_sql_trace => p_sql_trace
4637 );
4638 end if;
4639
4640 /* Check for territories for this Usage/Transaction Type */
4641 BEGIN
4642
4643 SELECT COUNT(*)
4644 INTO num_of_combination
4645 FROM jtf_tae_qual_products jtqp
4646 WHERE jtqp.source_id = p_source_id
4647 AND jtqp.trans_object_type_id = p_trans_object_type_id;
4648
4649 --dbms_output.put_line('JTF_TAE_GEN_PVT:' || num_of_combination);
4650
4651 EXCEPTION
4652 WHEN NO_DATA_FOUND THEN
4653 num_of_combination := 0;
4654 END;
4655
4656 IF G_Debug THEN
4657
4658 Write_Log(2, ' ');
4659 Write_Log(2, '/***************** BEGIN: BATCH TAE: TERRITORY STATUS *********************/');
4660 Write_Log(2, ' ');
4661 Write_Log(2, 'Inside Generate_API initialize');
4662 Write_Log(2, 'source_id - ' || TO_CHAR(p_Source_Id) );
4663 Write_Log(2, 'Number of valid Qualifier Combinations: ' || num_of_combination );
4664 Write_Log(2, ' ');
4665 Write_Log(2, '/***************** END: BATCH TAE: TERRITORY STATUS *********************/');
4666 Write_Log(2, ' ');
4667
4668 END IF;
4669
4670 IF G_Debug THEN
4671
4672 Write_Log(2, ' ');
4673 Write_Log(2, '/***************** BEGIN: BATCH TAE: PACKAGE STATUS *********************/');
4674 Write_Log(2, ' ');
4675
4676 END IF;
4677
4678 /* territories exist for this USAGE combination */
4679 IF (num_of_combination > 0) THEN
4680
4681 /* Catch-All territory */
4682 l_terr_id := 1;
4683
4684 /* Generate Package NAME */
4685 l_package_name := 'JTF_TAE_' || TO_CHAR (l_abs_source_id) ||'_' || l_target_type|| '_DYN';
4686 G_DYN_PKG_NAME := l_package_name;
4687
4688 /* Generate Package BODY */
4689 package_desc := '/* Auto Generated Package */';
4690 generate_package_header(l_package_name, package_desc, 'PKB');
4691
4692 /* generate individual SQL statements territories */
4693 gen_terr_rules_recurse (
4694 p_terr_id => l_terr_id,
4695 p_source_id => p_source_id,
4696 p_qualifier_type_id => p_trans_object_type_id,
4697 p_target_type => l_target_type,
4698 p_input_table_name => l_input_table_name,
4699 p_match_table_name => l_match_table_name,
4700 p_search_name => 'SEARCH_TERR_RULES' );
4701
4702 --dbms_output.put_line('NEW ENGINE: Value of l_package_name='||l_package_name);
4703
4704 /* generate end of package BODY */
4705 generate_end_of_package(l_package_name, 'TRUE');
4706
4707 ELSE
4708
4709 IF (G_Debug) THEN
4710 Write_Log(2, 'PACKAGE ' || l_package_name || ' NOT CREATED SUCCESSFULLY: no territories exist for this Usage/Transaction combination. ');
4711 END IF;
4712
4713 g_ProgramStatus := 1;
4714
4715 END IF; /* num_of_combination > 0 */
4716
4717 /* check status of DYNAMICALLY CREATED PACKAGE */
4718 BEGIN
4719
4720 query_str :=
4721 ' SELECT uo.object_name, uo.object_type, uo.created, uo.last_ddl_time, uo.timestamp, uo.status' ||
4722 ' FROM user_objects uo' ||
4723 ' WHERE uo.object_type = ''PACKAGE BODY'' AND uo.object_name = :b1 and rownum < 2';
4724
4725 EXECUTE IMMEDIATE query_str
4726 INTO l_object_name
4727 , l_object_type
4728 , l_created
4729 , l_last_ddl_time
4730 , l_timestamp
4731 , l_status
4732 USING l_package_name ;
4733
4734 IF l_status = 'INVALID' THEN
4735
4736 /* ACHANDA 03/08/2004 : Bug 3380047 : if the package is created in invalid status the message is */
4737 /* written to the log file irrespective of wthether debug flag is set to true or false */
4738 Write_Log(1, 'Status of the package ' || l_package_name || ' IS INVALID. ');
4739 IF G_Debug THEN
4740 Write_Log(2, ' ');
4741 Write_Log(2, 'PACKAGE ' || l_package_name || ' NOT CREATED SUCCESSFULLY: cannot be compiled. ');
4742 Write_Log(2, ' ');
4743 END IF;
4744
4745 /* ACHANDA 03/08/2004 : Bug 3380047 : set the value to 2 to */
4746 /* distinguish this exception from other exceptions in the program */
4747 g_ProgramStatus := 2;
4748
4749 END IF;
4750
4751 EXCEPTION
4752 WHEN others THEN
4753 NULL;
4754 END;
4755
4756 --ARPATEL 09/03/2003 - Add calls create new mode init (fetch) dyn package and new mode dyn assignment package
4757
4758 BEGIN
4759
4760 /* ACHANDA 03/08/2004 : bug 3380047 : initialize g_pointer to 0 */
4761 g_pointer := 0;
4762
4763 generate_nm0_api (
4764 errbuf => l_nm0_message ,
4765 retcode => l_nm0_Retcode ,
4766 p_source_id => p_source_id,
4767 p_trans_object_type_id => p_trans_object_type_id,
4768 p_debug_flag => p_debug_flag,
4769 p_sql_trace => p_sql_trace
4770 );
4771
4772 /* ACHANDA 03/08/2004 : bug 3380047 : initialize g_pointer to 0 */
4773 g_pointer := 0;
4774
4775 generate_nm_api (
4776 errbuf => l_nm_message ,
4777 retcode => l_nm_Retcode ,
4778 p_source_id => p_source_id,
4779 p_trans_object_type_id => p_trans_object_type_id,
4780 p_debug_flag => p_debug_flag,
4781 p_sql_trace => p_sql_trace
4782 );
4783
4784 END;
4785
4786
4787 IF G_Debug THEN
4788
4789 Write_Log(2, ' ');
4790 Write_Log(2, l_object_type || ': ' || l_package_name);
4791 Write_Log(2, 'Created: ' || TO_CHAR(l_created) );
4792 Write_Log(2, 'Last DDL Time: ' || TO_CHAR(l_last_ddl_time) );
4793 Write_Log(2, 'Timestamp: ' || l_timestamp );
4794 Write_Log(2, 'Status: ' || l_status );
4795 Write_Log(2, ' ');
4796 Write_Log(2, '/***************** END: BATCH TAE: PACKAGE STATUS *********************/');
4797 Write_Log(2, ' ');
4798
4799 END IF;
4800
4801 IF g_ProgramStatus = 1 Then
4802 ERRBUF := 'Program Completed WITH EXCEPTIONS';
4803 RetCODE := 1;
4804 ElsIf g_ProgramStatus = 0 Then
4805 ERRBUF := 'Program completed SUCCESSFULLY.';
4806 RetCode := 0;
4807 /* ACHANDA : 03/08/2004 : Added to handle the case of the package getting created in invalid status */
4808 ElsIf g_ProgramStatus = 2 Then
4809 ERRBUF := 'Package is created in invalid status.';
4810 RetCode := 2;
4811 End If;
4812
4813
4814 EXCEPTION
4815 WHEN utl_file.invalid_path OR utl_file.invalid_mode OR
4816 utl_file.invalid_filehandle OR utl_file.invalid_operation OR
4817 utl_file.write_error THEN
4818 ERRBUF := 'Program terminated with exception. Error writing to output file.';
4819 RETCODE := 2;
4820
4821 WHEN OTHERS THEN
4822 IF G_Debug THEN
4823 Write_Log(1,'Program terminated with OTHERS exception. ' || SQLERRM);
4824 END IF;
4825 ERRBUF := 'Program terminated with OTHERS exception. ' || SQLERRM;
4826 RETCODE := 2;
4827 END generate_api;
4828
4829 END JTF_TAE_GEN_PVT;