1 Package Body ben_ext_smart_total as
2 /* $Header: benxsttl.pkb 120.1 2006/10/10 20:28:27 tjesumic noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Benefit Extract Smart Totals
13 Purpose
14 This package is for totals in the header/trailer
15 History
16 Date Version Who What?
17 11/16/98 115.0 YRathman/PDas Created.
18 12/09/98 115.1 PDas Modified Calc_Smart_Total
19 Procedure
20 12/28/98 115.2 PDas Added get_value procedure
21 02/03/99 115.3 PDas Deleted date and
22 enabled_flag check for hr_lookups
23 02/08/99 115.4 PDas Moved function get_value to benxutil.pkb
24 02/10/99 115.5 PDas Modified Calc_Smart_Total
25 Procedure
26 - added p_frmt_mask_cd paramater
27 02/16/99 115.6 PDas Modified Calc_Smart_Total
28 08/06/99 115.7 ASen Added messages : Entering, Exiting.
29 10/03/99 115.8 Thayden Rewrote to handle multiple conditions.
30 01/04/99 115.9 Thayden Fixed format mask bugs.
31 02/24/00 115.10 Shdas changed the dynamic sql for calculating
32 smart totals.
33 01/30/01 115.11 tilak error message is changed , the messages is
34 sent instead of the name
35 08/10/01 115.12 ikasire Bug 1928211 changed the l_smart_ttl_string
36 as per the details in bug resolution
37 02/10/02 115.13 tjesumic Fraction Amount is not taken care in
38 SUM function . added in translation - 2012562
39 02/10/02 115.14 tjesumic dbdrv fixed
40 03/13/02 115.15 ikasire UTF8 Changes
41 12/23/02 115.16 lakrish NOCOPY changes
42 12/23/02 115.17 tjesumic closing ')' moved below the lop
43 # 2729093
44 06/14/04 115.18 tjesumic 3691826 fixed by creating str with format mask
45 06/15/04 115.19 tjesumic 3691826
46 03/22/05 115.20 tjesumic new parameter group_val_01,02 added for
47 sub grouping calcaultion for subheader
48 10/10/06 115.21 tjesumic ben_Ext_frmt,format_mask function called to
49 apply the format maks so {} format are taken care
50 */
51 --
52 --
53 -- ----------------------------------------------------------------------------
54 -- |-------------------------< get_where_params >------------------------------|
55 -- ----------------------------------------------------------------------------
56 --
57 -- This procedure finds the necessary column in ben_ext_rslt_dtl
58 -- for where clause
59 --
60 Procedure get_where_params(p_ext_rcd_id in number,
61 p_cond_ext_data_elmt_id in number,
62 p_data_elmt_seq_num out nocopy varchar2
63 ) IS
64 --
65 l_proc varchar2(72) := g_package||'.get_where_params';
66 --
67 l_seq_num number(9);
68 l_seq_num_char varchar2(2);
69 --
70 cursor where_c is
71 SELECT xdr.seq_num
72 FROM ben_ext_data_elmt_in_rcd xdr
73 WHERE xdr.ext_data_elmt_id = p_cond_ext_data_elmt_id
74 and xdr.ext_rcd_id = p_ext_rcd_id;
75 --
76 Begin
77 --
78 hr_utility.set_location('Entering'||l_proc, 5);
79 --
80 open where_c;
81 fetch where_c into l_seq_num;
82 if where_c%found then
83 close where_c;
84 --
85 if l_seq_num <= 0 or l_seq_num > 99 then
86 ben_ext_thread.g_err_name := 'BEN_92081_EXT_DATA_ELMT_SEQ';
87 raise ben_ext_thread.g_ht_error;
88 end if;
89 --
90 if l_seq_num > 9 then
91 l_seq_num_char := to_char(l_seq_num);
92 else
93 l_seq_num_char := '0'||to_char(l_seq_num);
94 end if;
95 p_data_elmt_seq_num := l_seq_num_char;
96 --
97 else
98 ben_ext_thread.g_err_name := 'BEN_92082_EXT_COND_DATA_ELMT';
99 raise ben_ext_thread.g_ht_error;
100 end if;
101 --
102 hr_utility.set_location('Exiting'||l_proc, 15);
103 --
104 Exception
105 when ben_ext_thread.g_ht_error then
106 --
107 raise ben_ext_thread.g_ht_error;
108 --
109 End get_where_params;
110 --
111 --
112 -- ----------------------------------------------------------------------------
113 -- |---------------------------< get_sum_params >------------------------------|
114 -- ----------------------------------------------------------------------------
115 --
116 -- This procedure finds the necessary column in ben_ext_rslt_dtl
117 -- for sum
118 --
119 Procedure get_sum_params(p_ttl_sum_ext_data_elmt_id in number,
120 p_ext_file_id in number,
121 p_business_group_id in number,
122 p_ext_rcd_id in number,
123 p_sum_column out nocopy varchar2,
124 p_frmt_mask out nocopy varchar2
125 ) IS
126 --
127 l_proc varchar2(72) := g_package||'.get_sum_params';
128 --
129 l_seq_num number(9);
130 l_frmt_mask_cd hr_lookups.lookup_code%type;
131 --
132 cursor sum_c is
133 SELECT c.seq_num,
134 d.frmt_mask_cd
135 FROM ben_ext_data_elmt_in_rcd c,
136 ben_ext_data_elmt d
137 WHERE c.ext_rcd_id = p_ext_rcd_id
138 AND c.ext_data_elmt_id = p_ttl_sum_ext_data_elmt_id
139 AND d.ext_data_elmt_id = c.ext_data_elmt_id;
140 --
141 cursor mask_c is
142 SELECT meaning
143 FROM hr_lookups
144 WHERE lookup_type = 'BEN_EXT_FRMT_MASK'
145 AND lookup_code = l_frmt_mask_cd;
146 --
147 Begin
148 --
149 hr_utility.set_location('Entering'||l_proc, 5);
150 --
151 open sum_c;
152 fetch sum_c into l_seq_num, l_frmt_mask_cd;
153 if sum_c%found then
154 close sum_c;
155 --
156 if l_seq_num <= 0 then
157 ben_ext_thread.g_err_name := 'BEN_92081_EXT_DATA_ELMT_SEQ';
158 raise ben_ext_thread.g_ht_error;
159 end if;
160 --
161 if l_seq_num > 9 then
162 p_sum_column := to_char(l_seq_num);
163 else
164 p_sum_column := '0'||to_char(l_seq_num);
165 end if;
166 --
167 if l_frmt_mask_cd is not null then
168 open mask_c;
169 fetch mask_c into p_frmt_mask;
170 if mask_c%notfound then
171 close mask_c;
172 ben_ext_thread.g_err_name := 'BEN_92088_EXT_CORRUPT';
173 raise ben_ext_thread.g_ht_error;
174 else
175 close mask_c;
176 if p_frmt_mask is null then
177 ben_ext_thread.g_err_name := 'BEN_92088_EXT_CORRUPT';
178 raise ben_ext_thread.g_ht_error;
179 end if;
180 end if;
181 end if;
182 --
183 else
184 --
185 ben_ext_thread.g_err_name := 'BEN_92083_EXT_SUM_DATA_ELMT';
186 raise ben_ext_thread.g_ht_error;
187 --
188 end if;
189 --
190 hr_utility.set_location('Exiting'||l_proc, 15);
191 --
192 End get_sum_params;
193 --
194 --
195 -- ----------------------------------------------------------------------------
196 -- |--------------------< build_where_string >-----------------------------------|
197 -- ----------------------------------------------------------------------------
198 --
199 -- This procedure builds the where clause for dynamic sql.
200 --
201 Procedure build_where_string(p_ext_rslt_id in number,
202 p_ext_rcd_id in number,
203 p_ext_data_elmt_id in number,
204 p_group_val_01 in varchar2 default null ,
205 p_group_val_02 in varchar2 default null,
206 p_where_string out nocopy varchar2
207 ) IS
208 --
209 l_proc varchar2(72) := g_package||'.build_where_string';
210 l_data_elmt_seq_num varchar2(2);
211 l_where_string varchar2(2000);
212 l_cnt number := 0;
213 --
214 cursor c_xwc(p_ext_data_elmt_id in number) is
215 select xwc.oper_cd,
216 xwc.val,
217 xwc.and_or_cd,
218 xwc.cond_ext_data_elmt_id
219 from ben_ext_where_clause xwc
220 where xwc.ext_data_elmt_id = p_ext_data_elmt_id
221 order by xwc.seq_num;
222 --
223 Begin
224 --
225 hr_utility.set_location('Entering'||l_proc, 5);
226 --
227 l_where_string := ' where ext_rslt_id = ' || to_char(p_ext_rslt_id);
228 --
229 if p_ext_rcd_id is not null then
230 l_where_string := l_where_string ||
231 ' and ext_rcd_id = ' || to_char(p_ext_rcd_id);
232 --
233 for xwc in c_xwc(p_ext_data_elmt_id) loop
234 l_cnt := l_cnt +1;
235 if l_cnt = 1 then
236 l_where_string := l_where_string || ' and (';
237 end if;
238 --
239 if xwc.oper_cd is null then
240 ben_ext_thread.g_err_name := 'BEN_92084_EXT_COND_OPER';
241 raise ben_ext_thread.g_ht_error;
242 end if;
243 --
244 -- need to get the sequence number of the data element in record so we know
245 -- which bucket (val_XX) to look in.
246 --
247 get_where_params(
248 p_ext_rcd_id => p_ext_rcd_id,
249 p_cond_ext_data_elmt_id => xwc.cond_ext_data_elmt_id,
250 p_data_elmt_seq_num => l_data_elmt_seq_num
251 );
252 --
253 l_where_string := l_where_string ||
254 ' upper(val_' || l_data_elmt_seq_num || ') ' ||
255 xwc.oper_cd || ' ' || upper(xwc.val) || ' ' || xwc.and_or_cd ;
256 -- xwc.oper_cd || ' ' || upper(xwc.val) || ' ' || xwc.and_or_cd || ')';
257 --
258 hr_utility.set_location(upper(xwc.val) || ' '|| xwc.and_or_cd ,999);
259 end loop;
260
261
262 if ltrim(rtrim(p_group_val_01)) is not null then
263
264 l_where_string := l_where_string || ' and group_val_01 = ''' || p_group_val_01 || ''' ' ;
265 if ltrim(rtrim(p_group_val_02)) is not null then
266 l_where_string := l_where_string || ' and group_val_02 = ''' || p_group_val_02 || ''' ' ;
267 end if ;
268
269 end if ;
270 --- closing is moved out of loop 2729093
271 if l_cnt > 0 then
272 hr_utility.set_location(' adding closing ' ,999);
273 l_where_string := l_where_string || ') ' ;
274 end if ;
275
276 --
277 end if;
278 p_where_string := l_where_string;
279 --
280 -- hr_utility.set_location(l_where_string,99999);
281 --
282 hr_utility.set_location('Exiting'||l_proc, 15);
283 --
284 End build_where_string;
285 --
286 -- ----------------------------------------------------------------------------
287 -- |--------------------< calc_smart_total >-----------------------------------|
288 -- ----------------------------------------------------------------------------
289 --
290 -- This procedure calculate smart total
291 --
292 Procedure calc_smart_total(p_ext_rslt_id in number,
293 p_ttl_fnctn_cd in varchar2,
294 p_ttl_sum_ext_data_elmt_id in number,
295 p_ttl_cond_ext_data_elmt_id in number, --ext_rcd_id
296 p_ext_data_elmt_id in number,
297 p_frmt_mask_cd in varchar2, --contains mask, not the cd.
298 p_ext_file_id in number,
299 p_business_group_id in number,
300 p_group_val_01 in varchar2 default null ,
301 p_group_val_02 in varchar2 default null,
302 p_smart_total out nocopy varchar2
303 ) IS
304 --
305 l_proc varchar2(72) := g_package||'.calc_smart_total';
306 --
307 l_cond_rcd_id number(15);
308 l_cond_column varchar2(30);
309 --
310 l_sum_rcd_id number(15);
311 l_sum_column varchar2(30);
312 l_frmt_mask hr_lookups.meaning%TYPE; -- UTF8 varchar2(80);
313 l_sum_col_frmt_mask hr_lookups.meaning%TYPE; -- UTF8 varchar2(80);
314 l_smart_total varchar2(200);
315 --
316 l_where_string varchar2(2000);
317 l_smart_ttl_string varchar2(2000);
318 l_ttl_cond_val varchar2(200);
319 l_oper hr_lookups.meaning%type;
320 --
321 cid integer;
322 res integer;
323 l_ext_rcd_id number;
324 l_err_message varchar2(2000);
325 --
326 Begin
327 --
328 hr_utility.set_location('Entering'||l_proc, 5);
329 --
330 l_smart_ttl_string := null;
331 --
332 -- note: for smart totals, we are now using field p_ttl_cond_ext_data_elmt_id
333 -- for ext_rcd_id. Due to last minute change, it was too late to rename.
334 --
335 l_ext_rcd_id := p_ttl_cond_ext_data_elmt_id;
336 --
337 if p_ext_data_elmt_id is null then
338 ben_ext_thread.g_err_name := 'BEN_92088_EXT_CORRUPT';
339 raise ben_ext_thread.g_ht_error;
340 end if;
341 --
342 build_where_string(
343 p_ext_rslt_id => p_ext_rslt_id, --in
344 p_ext_rcd_id => l_ext_rcd_id, --in
345 p_ext_data_elmt_id => p_ext_data_elmt_id, --in
346 p_group_val_01 => p_group_val_01 ,
347 p_group_val_02 => p_group_val_02,
348 p_where_string => l_where_string); --out
349
350 --
351 if p_ttl_fnctn_cd = 'SUM' then
352 --
353 get_sum_params(p_ttl_sum_ext_data_elmt_id => p_ttl_sum_ext_data_elmt_id, --in
354 p_ext_file_id => p_ext_file_id, --in
355 p_business_group_id => p_business_group_id, --in
356 p_ext_rcd_id => l_ext_rcd_id, --in
357 p_sum_column => l_sum_column, --out
358 p_frmt_mask => l_sum_col_frmt_mask --out
359 );
360 --
361 /* what the following dynamic sql does is just strip out nocopy from the
362 data element(for which we are calculating total) all the characters
363 that are not between 0 and 9 and then sum them.
364 So it first translates all characters of the data element(say $123v30)
365 between 0 and 9 with ;.So we have now $;;;v;;.
366 Now applying a replace we replace all ; with null.So we have $v.
367 Then applying a translate on the data element(i.e $123v30) with 0123456789
368 for 0123456789$v will give us the desired result(i.e 12330).*/
369
370 /*
371 l_smart_ttl_string := 'select to_char(sum(to_number(
372 translate(replace(val_'||l_sum_column||',
373 '';'',''''),''0123456789''||replace(translate(val_'||
374 l_sum_column||',''0123456789'','';''),'';'',''''),
375 ''0123456789''))))'||
376 ' from ben_ext_rslt_dtl'
377 || l_where_string;
378 */
379 -- Bug 1928211 changed the script are the Ty's note in the Bug resolution.
380 --
381 Begin
382 l_smart_ttl_string := 'select to_char(sum(to_number(
383 translate(replace(val_'||l_sum_column||',
384 '';'',''''),''-0123456789.''||replace(translate(val_'||
385 l_sum_column||',''-0123456789.'','';''),'';'',''''),
386 ''-0123456789.''))))'||
387 ' from ben_ext_rslt_dtl'
388 || l_where_string;
389
390 -- the sum does not work for the value with format mask 0999999999999D99S
391 -- - or + signs are added to end of the value so the sum is not recognising
392 -- the sql tested before the sql executed, though it is duplicate it is necdesery for bug 3691826
393 -- if the sql errors and the column defined with format , try to convert the colum with theformat to sum
394 -- value 3691826
395
396 cid := DBMS_SQL.OPEN_CURSOR;
397 DBMS_SQL.PARSE(cid, l_smart_ttl_string, DBMS_SQL.NATIVE);
398 DBMS_SQL.DEFINE_COLUMN(cid, 1, l_smart_total, 200);
399 res := DBMS_SQL.EXECUTE(cid);
400 res := DBMS_SQL.FETCH_ROWS(cid);
401 DBMS_SQL.COLUMN_VALUE(cid, 1, l_smart_total);
402 DBMS_SQL.CLOSE_CURSOR(cid);
403
404
405 exception
406 when Others then
407 hr_utility.set_location('value error ' , 99 );
408 if l_sum_col_frmt_mask is not null then
409
410 l_smart_ttl_string := 'select (sum(to_number(val_'||l_sum_column||', '''|| l_sum_col_frmt_mask|| ''')))'||
411 ' from ben_ext_rslt_dtl'
412 || l_where_string;
413
414
415 end if ;
416
417
418
419 end ;
420 --
421 elsif p_ttl_fnctn_cd = 'CNT' then
422 --
423 l_smart_ttl_string := 'select to_char(count(*)) from ben_ext_rslt_dtl'||
424 l_where_string;
425 --
426 else -- function is not present or invalid.
427 ben_ext_thread.g_err_name := 'BEN_92087_EXT_TTL_FNCTN';
428 raise ben_ext_thread.g_ht_error;
429 end if;
430 --
431 -- dbms_output.put_line(l_smart_ttl_string);
432 --
433 -- This is dynamic SQL part
434 --
435 cid := DBMS_SQL.OPEN_CURSOR;
436 --
437 DBMS_SQL.PARSE(cid, l_smart_ttl_string, DBMS_SQL.NATIVE);
438 --
439 DBMS_SQL.DEFINE_COLUMN(cid, 1, l_smart_total, 200);
440 res := DBMS_SQL.EXECUTE(cid);
441 res := DBMS_SQL.FETCH_ROWS(cid);
442 DBMS_SQL.COLUMN_VALUE(cid, 1, l_smart_total);
443 DBMS_SQL.CLOSE_CURSOR(cid);
444 --
445 if p_frmt_mask_cd is null then
446 p_smart_total := ltrim(l_smart_total);
447 else
448 begin
449 --p_smart_total := ltrim(to_char(to_number(l_smart_total),p_frmt_mask_cd));
450 p_smart_total := ben_Ext_fmt.apply_format_mask(p_value => to_number(l_smart_total),
451 p_format_mask => p_frmt_mask_cd ) ;
452 exception
453 when others then
454 p_smart_total := ltrim(l_smart_total);
455 -- show warning here
456 l_err_message := ben_ext_fmt.get_error_msg(92065,'BEN_92065_EXT_FRMT_INVALID' );
457 ben_ext_util.write_err
458 (p_ext_rslt_id => ben_extract.g_ext_rslt_id,
459 p_err_num => 92065,
460 p_err_name =>l_err_message ,
461 p_typ_cd => 'W',
462 p_person_id => null,
463 p_request_id => ben_extract.g_request_id,
464 p_business_group_id => ben_ext_person.g_business_group_id);
465 end;
466 end if;
467 --
468 --dbms_output.put_line(to_char(l_smart_total));
469 --
470 hr_utility.set_location('Exiting'||l_proc, 15);
471 --
472 Exception
473 --
474 when ben_ext_thread.g_ht_error then
475 raise ben_ext_thread.g_ht_error;
476 --
477 when others then
478 p_smart_total := null;
479 -- this needs replaced with a message for translation.
480 fnd_file.put_line(fnd_file.log,
481 'Error in Smart Totals while processing this dynamic sql statement: ');
482 fnd_file.put_line(fnd_file.log, l_smart_ttl_string);
483 raise; -- such that the error processing in ben_ext_thread occurs.
484
485 --
486 End calc_smart_total;
487 --
488 End ben_ext_smart_total;
[Home] [Help]