eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.JTY_CUST_QUAL_PKG

Source


1 PACKAGE BODY JTY_CUST_QUAL_PKG AS
2 /* $Header: jtfcusqb.pls 120.2 2006/09/22 22:16:45 chchandr noship $ */
3 --    Start of Comments
4 --    ---------------------------------------------------
5 --    PACKAGE NAME:   JTY_CUST_QUAL_PKG
6 --    ---------------------------------------------------
7 --    PURPOSE
8 --      This package is used to create custom qualifiers
9 --
10 --      Procedures:
11 --         (see below for specification)
12 --
13 --    NOTES
14 --      This package is publicly available for use
15 --
16 --    HISTORY
17 --      09/08/05    ACHANDA         Created
18 --
19 --    End of Comments
20 --
21 
22   G_NEW_LINE    VARCHAR2(02) := fnd_global.local_chr(10);
23 
24 /* this procedure forms the insert and update statement for the columns UPDATE_ATTR_VAL_STMT */
25 /* and UPDATE_ATTR_VAL_STMT in the table jtf_qual_usgs_all                                   */
26 PROCEDURE get_attr_val_stmt(
27   p_comparison_operator        IN VARCHAR2,
28   p_low_value_char             IN VARCHAR2,
29   p_high_value_char            IN VARCHAR2,
30   p_low_value_char_id          IN VARCHAR2,
31   p_low_value_number           IN VARCHAR2,
32   p_high_value_number          IN VARCHAR2,
33   p_interest_type_id           IN VARCHAR2,
34   p_primary_interest_code_id   IN VARCHAR2,
35   p_secondary_interest_code_id IN VARCHAR2,
36   p_value1_id                  IN VARCHAR2,
37   p_value2_id                  IN VARCHAR2,
38   p_value3_id                  IN VARCHAR2,
39   p_value4_id                  IN VARCHAR2,
40   p_first_char                 IN VARCHAR2,
41   p_currency_code              IN VARCHAR2,
42   p_update_stmt                OUT NOCOPY VARCHAR2,
43   p_insert_stmt                OUT NOCOPY VARCHAR2,
44   retcode                      OUT NOCOPY VARCHAR2,
45   errbuf                       OUT NOCOPY VARCHAR2) IS
46 BEGIN
47   p_insert_stmt :=
48     'INSERT into jty_denorm_terr_attr_values_gt( ' || g_new_line ||
49     '   terr_id' || g_new_line ||
50     '  ,start_date' || g_new_line ||
51     '  ,end_date' || g_new_line ||
52     '  ,source_id' || g_new_line ||
53     '  ,trans_type_id' || g_new_line ||
54     '  ,creation_date' || g_new_line ||
55     '  ,created_by' || g_new_line ||
56     '  ,last_update_date' || g_new_line ||
57     '  ,last_updated_by' || g_new_line ||
58     '  ,last_update_login' || g_new_line ||
59     '  ,absolute_rank' || g_new_line ||
60     '  ,top_level_terr_id' || g_new_line ||
61     '  ,program_id' || g_new_line ||
62     '  ,program_login_id' || g_new_line ||
63     '  ,program_application_id' || g_new_line ||
64     '  ,request_id' || g_new_line ||
65     '  ,program_update_date' || g_new_line;
66 
67   p_update_stmt := 'UPDATE jty_denorm_terr_attr_values_gt ' || g_new_line || 'SET ';
68 
69   p_insert_stmt := p_insert_stmt || '  ,' || p_comparison_operator || g_new_line;
70 
71   p_update_stmt := p_update_stmt || p_comparison_operator || ' = :1,' || g_new_line;
72 
73   if (p_low_value_char_id is not null) then
74     p_insert_stmt := p_insert_stmt || '  ,' || p_low_value_char_id || g_new_line;
75 	p_update_stmt := p_update_stmt || p_low_value_char_id || ' = :2,' || g_new_line;
76   else
77     p_insert_stmt := p_insert_stmt || '  ,dummy1' || g_new_line;
78 	p_update_stmt := p_update_stmt || 'dummy1 = :2, ' || g_new_line;
79   end if;
80 
81   if (p_low_value_char is not null) then
82     p_insert_stmt := p_insert_stmt || '  ,' || p_low_value_char || g_new_line;
83 	p_update_stmt := p_update_stmt || p_low_value_char || ' = :3,' || g_new_line;
84   else
85     p_insert_stmt := p_insert_stmt || '  ,dummy2' || g_new_line;
86 	p_update_stmt := p_update_stmt || 'dummy2 = :3, ' || g_new_line;
87   end if;
88 
89   if (p_high_value_char is not null) then
90     p_insert_stmt := p_insert_stmt || '  ,' || p_high_value_char || g_new_line;
91 	p_update_stmt := p_update_stmt || p_high_value_char || ' = :4,' || g_new_line;
92   else
93     p_insert_stmt := p_insert_stmt || '  ,dummy3' || g_new_line;
94 	p_update_stmt := p_update_stmt || 'dummy3 = :4, ' || g_new_line;
95   end if;
96 
97   if (p_low_value_number is not null) then
98     p_insert_stmt := p_insert_stmt || '  ,' || p_low_value_number || g_new_line;
99 	p_update_stmt := p_update_stmt || p_low_value_number || ' = :5,' || g_new_line;
100   else
101     p_insert_stmt := p_insert_stmt || '  ,dummy4' || g_new_line;
102 	p_update_stmt := p_update_stmt || 'dummy4 = :5, ' || g_new_line;
103   end if;
104 
105   if (p_high_value_number is not null) then
106     p_insert_stmt := p_insert_stmt || '  ,' || p_high_value_number || g_new_line;
107 	p_update_stmt := p_update_stmt || p_high_value_number || ' = :6,' || g_new_line;
108   else
109     p_insert_stmt := p_insert_stmt || '  ,dummy5' || g_new_line;
110 	p_update_stmt := p_update_stmt || 'dummy5 = :6, ' || g_new_line;
111   end if;
112 
113   if (p_interest_type_id is not null) then
114     p_insert_stmt := p_insert_stmt || '  ,' || p_interest_type_id || g_new_line;
115 	p_update_stmt := p_update_stmt || p_interest_type_id || ' = :7,' || g_new_line;
116   else
117     p_insert_stmt := p_insert_stmt || '  ,dummy6' || g_new_line;
118 	p_update_stmt := p_update_stmt || 'dummy6 = :7, ' || g_new_line;
119   end if;
120 
121   if (p_primary_interest_code_id is not null) then
122     p_insert_stmt := p_insert_stmt || '  ,' || p_primary_interest_code_id || g_new_line;
123 	p_update_stmt := p_update_stmt || p_primary_interest_code_id || ' = :8,' || g_new_line;
124   else
125     p_insert_stmt := p_insert_stmt || '  ,dummy7' || g_new_line;
126 	p_update_stmt := p_update_stmt || 'dummy7 = :8, ' || g_new_line;
127   end if;
128 
129   if (p_secondary_interest_code_id is not null) then
130     p_insert_stmt := p_insert_stmt || '  ,' || p_secondary_interest_code_id || g_new_line;
131 	p_update_stmt := p_update_stmt || p_secondary_interest_code_id || ' = :9,' || g_new_line;
132   else
133     p_insert_stmt := p_insert_stmt || '  ,dummy8' || g_new_line;
134 	p_update_stmt := p_update_stmt || 'dummy8 = :9, ' || g_new_line;
135   end if;
136 
137   if (p_currency_code is not null) then
138     p_insert_stmt := p_insert_stmt || '  ,' || p_currency_code || g_new_line;
139 	p_update_stmt := p_update_stmt || p_currency_code || ' = :10,' || g_new_line;
140   else
141     p_insert_stmt := p_insert_stmt || '  ,dummy9' || g_new_line;
142 	p_update_stmt := p_update_stmt || 'dummy9 = :10, ' || g_new_line;
143   end if;
144 
145   if (p_value1_id is not null) then
146     p_insert_stmt := p_insert_stmt || '  ,' || p_value1_id || g_new_line;
147 	p_update_stmt := p_update_stmt || p_value1_id || ' = :11,' || g_new_line;
148   else
149     p_insert_stmt := p_insert_stmt || '  ,dummy10' || g_new_line;
150 	p_update_stmt := p_update_stmt || 'dummy10 = :11, ' || g_new_line;
151   end if;
152 
153   if (p_value2_id is not null) then
154     p_insert_stmt := p_insert_stmt || '  ,' || p_value2_id || g_new_line;
155 	p_update_stmt := p_update_stmt || p_value2_id || ' = :12,' || g_new_line;
156   else
157     p_insert_stmt := p_insert_stmt || '  ,dummy11' || g_new_line;
158 	p_update_stmt := p_update_stmt || 'dummy11 = :12, ' || g_new_line;
159   end if;
160 
161   if (p_value3_id is not null) then
162     p_insert_stmt := p_insert_stmt || '  ,' || p_value3_id || g_new_line;
163 	p_update_stmt := p_update_stmt || p_value3_id || ' = :13,' || g_new_line;
164   else
165     p_insert_stmt := p_insert_stmt || '  ,dummy12' || g_new_line;
166 	p_update_stmt := p_update_stmt || 'dummy12 = :13, ' || g_new_line;
167   end if;
168 
169   if (p_value4_id is not null) then
170     p_insert_stmt := p_insert_stmt || '  ,' || p_value4_id || g_new_line;
171 	p_update_stmt := p_update_stmt || p_value4_id || ' = :14,' || g_new_line;
172   else
173     p_insert_stmt := p_insert_stmt || '  ,dummy13' || g_new_line;
174 	p_update_stmt := p_update_stmt || 'dummy13 = :14, ' || g_new_line;
175   end if;
176 
177   if (p_first_char is not null) then
178     p_insert_stmt := p_insert_stmt || '  ,' || p_first_char || g_new_line;
179 	p_update_stmt := p_update_stmt || p_first_char || ' = :15' || g_new_line;
180   else
181     p_insert_stmt := p_insert_stmt || '  ,dummy14' || g_new_line;
182 	p_update_stmt := p_update_stmt || 'dummy14 = :15 ' || g_new_line;
183   end if;
184 
185   p_insert_stmt := p_insert_stmt || ')' || g_new_line;
186   p_insert_stmt := p_insert_stmt || 'VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, ' ||
187     ':16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32)';
188 
189   p_update_stmt := p_update_stmt || 'where terr_id = :16' || g_new_line ||
190                                     'and source_id = :17' || g_new_line ||
191                                     'and trans_type_id = :18';
192 
193   retcode := 0;
194   errbuf  := null;
195 EXCEPTION
196   WHEN OTHERS THEN
197     retcode := 2;
198     errbuf  := 'Error in generating insert and update attribute values statement';
199 END get_attr_val_stmt;
200 
201 PROCEDURE create_qual(
202   p_seeded_qual_id             IN NUMBER,
203   p_name                       IN VARCHAR2,
204   p_description                IN VARCHAR2,
205   p_language                   IN VARCHAR2,
206   p_source_id                  IN NUMBER,
207   p_trans_type_id              IN NUMBER,
208   p_enabled_flag               IN VARCHAR2,
209   p_qual_col1                  IN VARCHAR2,
210   p_convert_to_id_flag         IN VARCHAR2,
211   p_display_type               IN VARCHAR2,
212   p_alias_rule1                IN VARCHAR2,
213   p_op_eql                     IN VARCHAR2,
214   p_op_like                    IN VARCHAR2,
215   p_op_between                 IN VARCHAR2,
216   p_op_common_where            IN VARCHAR2,
217   p_qual_relation_factor       IN NUMBER,
218   p_comparison_operator        IN VARCHAR2,
219   p_low_value_char             IN VARCHAR2,
220   p_high_value_char            IN VARCHAR2,
221   p_low_value_char_id          IN VARCHAR2,
222   p_low_value_number           IN VARCHAR2,
223   p_high_value_number          IN VARCHAR2,
224   p_interest_type_id           IN VARCHAR2,
225   p_primary_interest_code_id   IN VARCHAR2,
226   p_sec_interest_code_id       IN VARCHAR2,
227   p_value1_id                  IN VARCHAR2,
228   p_value2_id                  IN VARCHAR2,
229   p_value3_id                  IN VARCHAR2,
230   p_value4_id                  IN VARCHAR2,
231   p_first_char                 IN VARCHAR2,
232   p_currency_code              IN VARCHAR2,
233   p_real_time_select           IN VARCHAR2,
234   p_real_time_where            IN VARCHAR2,
235   p_real_time_from             IN VARCHAR2,
236   p_html_lov_sql1              IN VARCHAR2,
237   p_html_lov_sql2              IN VARCHAR2,
238   p_html_lov_sql3              IN VARCHAR2,
239   p_display_sql1               IN VARCHAR2,
240   p_display_sql2               IN VARCHAR2,
241   p_display_sql3               IN VARCHAR2,
242   p_hierarchy_type             IN VARCHAR2,
243   p_equal_flag                 IN VARCHAR2,
244   p_like_flag                  IN VARCHAR2,
245   p_between_flag               IN VARCHAR2,
246   retcode                      OUT NOCOPY VARCHAR2,
247   errbuf                       OUT NOCOPY VARCHAR2) IS
248 
249   l_count            NUMBER;
250   l_insert_stmt      VARCHAR2(2000);
251   l_update_stmt      VARCHAR2(2000);
252   l_qual_type_usg_id NUMBER;
253 
254   l_user_id          NUMBER;
255   l_login_id         NUMBER;
256   l_sysdate          DATE;
257 
258   l_success_flag     VARCHAR2(250);
259   l_error_code       VARCHAR2(250);
260 BEGIN
261   l_user_id  := FND_GLOBAL.USER_ID;
262   l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
263   l_sysdate  := sysdate;
264 
265   /* Check to see if the unique id is alreday present in jtf_seeded_qual_all_b */
266   SELECT count(*)
267   INTO   l_count
268   FROM   jtf_seeded_qual_all_b
269   WHERE  seeded_qual_id = p_seeded_qual_id;
270 
271   IF (l_count > 0) THEN
272     retcode := 2;
273     errbuf  := 'Unique ID alreday present in jtf_seeded_qual_all_b';
274     RAISE FND_API.G_EXC_ERROR;
275   END IF;
276 
277   /* Check to see if the unique id is alreday present in jtf_qual_usgs_all */
278   SELECT count(*)
279   INTO   l_count
280   FROM   jtf_qual_usgs_all
281   WHERE  qual_usg_id = p_seeded_qual_id;
282 
283   IF (l_count > 0) THEN
284     retcode := 2;
285     errbuf  := 'Unique ID alreday present in jtf_qual_usgs_all';
286     RAISE FND_API.G_EXC_ERROR;
287   END IF;
288 
289   /* get the qual_type_usg_id corr to source and transaction type */
290   BEGIN
291     SELECT qual_type_usg_id
292     INTO   l_qual_type_usg_id
293     FROM   jtf_qual_type_usgs_all
294     WHERE  source_id = p_source_id
295     AND    qual_type_id = p_trans_type_id;
296   EXCEPTION
297     WHEN OTHERS THEN
298       retcode := 2;
299       errbuf  := 'Error in getting qual_type_usg_id from source_id and trans_type_id';
300       RAISE FND_API.G_EXC_ERROR;
301   END;
302 
303   /* get the attribute value insert and update statement */
304   get_attr_val_stmt(
305     p_comparison_operator        => p_comparison_operator,
306     p_low_value_char             => p_low_value_char,
307     p_high_value_char            => p_high_value_char,
308     p_low_value_char_id          => p_low_value_char_id,
309     p_low_value_number           => p_low_value_number,
310     p_high_value_number          => p_high_value_number,
311     p_interest_type_id           => p_interest_type_id,
312     p_primary_interest_code_id   => p_primary_interest_code_id,
313     p_secondary_interest_code_id => p_sec_interest_code_id,
314     p_value1_id                  => p_value1_id,
315     p_value2_id                  => p_value2_id,
316     p_value3_id                  => p_value3_id,
317     p_value4_id                  => p_value4_id,
318     p_first_char                 => p_first_char,
319     p_currency_code              => p_currency_code,
320     p_update_stmt                => l_update_stmt,
321     p_insert_stmt                => l_insert_stmt,
322     retcode                      => retcode,
323     errbuf                       => errbuf);
324 
325   IF (retcode <> 0) THEN
326     RAISE FND_API.G_EXC_ERROR;
327   END IF;
328 
329   /* Check if a qualifier exists with the same name */
330   SELECT count(*)
331   INTO   l_count
332   FROM   jtf_seeded_qual_all_b
333   WHERE  upper(name) = upper(p_name);
334 
335   IF (l_count > 0) THEN
336     retcode := 2;
337     errbuf  := 'Qualifier exist with the same name';
338     RAISE FND_API.G_EXC_ERROR;
339   END IF;
340 
341   INSERT INTO JTF_SEEDED_QUAL_ALL_B (
342     SEEDED_QUAL_ID,
343     LAST_UPDATE_DATE,
344     LAST_UPDATED_BY,
345     CREATION_DATE,
346     CREATED_BY,
347     LAST_UPDATE_LOGIN,
348     NAME,
349     DESCRIPTION,
350     ORG_ID,
351     SECURITY_GROUP_ID )
352   VALUES (
353     p_seeded_qual_id,
354     l_sysdate,
355     l_user_id,
356     l_sysdate,
357     l_user_id,
358     l_login_id,
359     p_name,
360     p_description,
361     null,
362     null);
363 
364   INSERT INTO JTF_SEEDED_QUAL_ALL_TL (
365     SEEDED_QUAL_ID,
366     LAST_UPDATE_DATE,
367     LAST_UPDATED_BY,
368     CREATION_DATE,
369     CREATED_BY,
370     LAST_UPDATE_LOGIN,
371     LANGUAGE,
372     SOURCE_LANG,
373     NAME,
374     DESCRIPTION,
375     ORG_ID,
376     SECURITY_GROUP_ID )
377   VALUES (
378     p_seeded_qual_id,
379     l_sysdate,
380     l_user_id,
381     l_sysdate,
382     l_user_id,
383     l_login_id,
384     p_language,
385     p_language,
386     p_name,
387     p_description,
388     null,
389     null);
390 
391   INSERT INTO JTF_QUAL_USGS_ALL (
392     QUAL_USG_ID,
393     LAST_UPDATE_DATE,
394     LAST_UPDATED_BY,
395     CREATION_DATE,
396     CREATED_BY,
397     LAST_UPDATE_LOGIN,
398     APPLICATION_SHORT_NAME,
399     SEEDED_QUAL_ID,
400     QUAL_TYPE_USG_ID,
401     ENABLED_FLAG,
402     QUAL_COL1,
403     QUAL_COL1_ALIAS,
404     SEEDED_FLAG,
405     CONVERT_TO_ID_FLAG,
406     DISPLAY_TYPE,
407     ORG_ID,
408     ALIAS_RULE1,
409     OP_EQL,
410     OP_LIKE,
411     OP_BETWEEN,
412     OP_COMMON_WHERE,
413     QUAL_RELATION_FACTOR,
414     OBJECT_VERSION_NUMBER,
415     COMPARISON_OPERATOR,
416     LOW_VALUE_CHAR,
417     HIGH_VALUE_CHAR,
418     LOW_VALUE_CHAR_ID,
419     LOW_VALUE_NUMBER,
420     HIGH_VALUE_NUMBER,
421     INTEREST_TYPE_ID,
422     PRIMARY_INTEREST_CODE_ID,
423     SECONDARY_INTEREST_CODE_ID,
424     VALUE1_ID,
425     VALUE2_ID,
426     VALUE3_ID,
427     VALUE4_ID,
428     FIRST_CHAR,
429     CURRENCY_CODE,
430     REAL_TIME_SELECT,
431     REAL_TIME_WHERE,
432     REAL_TIME_FROM,
433     UPDATE_ATTR_VAL_STMT,
434     INSERT_ATTR_VAL_STMT,
435     HTML_LOV_SQL1,
436     HTML_LOV_SQL2,
437     HTML_LOV_SQL3,
438     DISPLAY_SQL1,
439     DISPLAY_SQL2,
440     DISPLAY_SQL3,
441     HIERARCHY_TYPE,
442     EQUAL_FLAG,
443     LIKE_FLAG,
444     BETWEEN_FLAG )
445   VALUES (
446     p_seeded_qual_id,
447     l_sysdate,
448     l_user_id,
449     l_sysdate,
450     l_user_id,
451     l_login_id,
452     'JTF',
453     p_seeded_qual_id,
454     l_qual_type_usg_id,
455     p_enabled_flag,
456     p_qual_col1,
457     p_qual_col1,
458     'N',
459     p_convert_to_id_flag,
460     p_display_type,
461     -3113,
462     p_alias_rule1,
463     p_op_eql,
464     p_op_like,
465     p_op_between,
466     p_op_common_where,
467     p_qual_relation_factor,
468     null,
469     p_comparison_operator,
470     p_low_value_char,
471     p_high_value_char,
472     p_low_value_char_id,
473     p_low_value_number,
474     p_high_value_number,
475     p_interest_type_id,
476     p_primary_interest_code_id,
477     p_sec_interest_code_id,
478     p_value1_id,
479     p_value2_id,
480     p_value3_id,
481     p_value4_id,
482     p_first_char,
483     p_currency_code,
484     p_real_time_select,
485     p_real_time_where,
486     p_real_time_from,
487     l_update_stmt,
488     l_insert_stmt,
489     p_html_lov_sql1,
490     p_html_lov_sql2,
491     p_html_lov_sql3,
492     p_display_sql1,
493     p_display_sql2,
494     p_display_sql3,
495     p_hierarchy_type,
496     p_equal_flag,
497     p_like_flag,
498     p_between_flag);
499 
500   /* if the qualifier is enabled , then add the columns to the denorm tables */
501   IF (p_enabled_flag = 'Y') THEN
502     JTY_MISC_UTILS_PKG.alter_qual_denorm_tables (
503       x_success_flag               => l_success_flag,
504       x_err_code                   => l_error_code,
505       p_qual_usg_id		   => p_seeded_qual_id,
506       p_comp_op_col                => p_comparison_operator,
507       p_low_value_char_col         => p_low_value_char,
508       p_high_value_char_col        => p_high_value_char,
509       p_low_value_char_id_col      => p_low_value_char_id,
510       p_low_value_number_col       => p_low_value_number,
511       p_high_value_number_col      => p_high_value_number,
512       p_interest_type_id_col       => p_interest_type_id,
513       p_primary_int_code_id_col    => p_primary_interest_code_id,
514       p_secondary_int_code_id_col  => p_sec_interest_code_id,
515       p_value1_id_col              => p_value1_id,
516       p_value2_id_col              => p_value2_id,
517       p_value3_id_col              => p_value3_id,
518       p_value4_id_col              => p_value4_id,
519       p_first_char_col             => p_first_char,
520       p_cur_code_col               => p_currency_code);
521 
522     IF (l_success_flag <> 'Y') THEN
523       retcode := 2;
524       errbuf  := 'Error adding columns to the denorm value tables';
525       RAISE FND_API.G_EXC_ERROR;
526     END IF;
527   END IF;
528 
529   retcode := 0;
530   errbuf  := null;
531 EXCEPTION
532   WHEN FND_API.G_EXC_ERROR THEN
533     NULL;
534 
535   WHEN OTHERS THEN
536     retcode := 2;
537     errbuf  := SQLCODE || ' : ' || SQLERRM;
538 END create_qual;
539 END JTY_CUST_QUAL_PKG;