[Home] [Help]
PACKAGE BODY: APPS.TRR_ENGINE_PKG
Source
1 package body TRR_ENGINE_PKG as
2 /* $Header: pytrreng.pkb 115.10 2002/06/14 10:15:01 pkm ship $ */
3
4 procedure federal_trr(errbuf OUT VARCHAR2,
5 retcode OUT NUMBER,
6 p_business_group number ,
7 p_start_date varchar2,
8 p_end_date varchar2,
9 p_gre number,
10 p_federal varchar2,
11 p_state varchar2,
12 p_dimension varchar2)
13 is
14 --
15 --
16 cursor gre_sizes(c_business_group_id number,
17 c_tax_unit_id number,
18 c_jurisdiction_code varchar2)
19 is
20 select count(*) gre_size, puar.tax_unit_id gre_id, htu.name gre_name
21 from pay_us_asg_reporting puar,
22 hr_tax_units_v htu
23 where puar.tax_unit_id=htu.tax_unit_id
24 and htu.business_group_id=c_business_group_id
25 and substr(puar.jurisdiction_code,1,2)=
26 nvl(c_jurisdiction_code,substr(puar.jurisdiction_code,1,2))
27 and htu.tax_unit_id=nvl(c_tax_unit_id,htu.tax_unit_id)
28 group by puar.tax_unit_id,htu.name
29 order by count(*);
30
31 gre_list gre_info_list;
32 list_index number:=1;
33 start_index number:=1;
34 end_index number:=1;
35 l_req_id number;
36 copies_buffer varchar2(80) := null;
37 print_buffer varchar2(80) := null;
38 printer_buffer varchar2(80) := null;
39 style_buffer varchar2(80) := null;
40 save_buffer boolean := null;
41 save_result varchar2(1) := null;
42 req_id varchar2(80) := null;
43 x boolean;
44
45 l_valid_status varchar2(5);
46 l_program varchar2(100);
47 --
48 --
49 begin
50
51 --hr_utility.trace_on(null,'oracle');
52
53 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
54 retcode := 0;
55 -- get printing info
56 req_id:=fnd_profile.value('CONC_REQUEST_ID');
57 print_buffer:=fnd_profile.value('CONC_PRINT_TOGETHER');
58 if (print_buffer is NULL)
59 then print_buffer:='N';
60 end if;
61
62 select number_of_copies,
63 printer,
64 print_style,
65 save_output_flag
66 into copies_buffer,
67 printer_buffer,
68 style_buffer,
69 save_result
70 from fnd_concurrent_requests
71 where request_id = fnd_number.canonical_to_number(req_id);
72
73
74 if (save_result='Y') then
75 save_buffer:=true;
76 elsif (save_result='N') then
77 save_buffer:=false;
78 else
79 save_buffer:=NULL;
80 end if;
81
82 -- logic to decide which report to fire
83 begin
84 select pdb.run_balance_status
85 into l_valid_status
86 from pay_defined_balances pdb,
87 pay_balance_types pbt,
88 pay_balance_dimensions pbd
89 where pdb.legislation_code = 'US'
90 and pdb.save_run_balance = 'Y'
91 and pdb.run_balance_status is not null
92 and pdb.balance_type_id = pbt.balance_type_id
93 and pbd.balance_dimension_id = pdb.balance_dimension_id
94 and pbt.balance_name = 'SIT Withheld'
95 and pbd.database_item_suffix = '_GRE_JD_RUN';
96
97 if l_valid_status = 'V' then
98 --call the new report
99 l_program := 'PYFEDTRR_RB';
100
101 else
102 -- call the old report
103 l_program := 'PYFEDTRR';
104
105 end if;
106
107 exception when others then
111 -- end logic
108 -- call the old report
109 l_program := 'PYFEDTRR';
110 end;
112
113 -- read data into table
114 for grerec in gre_sizes(p_business_group,p_gre,p_state) loop
115 gre_list(list_index).gre_size :=grerec.gre_size;
116 gre_list(list_index).gre_id :=grerec.gre_id;
117 gre_list(list_index).gre_name :=grerec.gre_name;
118
119
120 list_index:=list_index+1;
121 end loop;
122
123
124 -- get start of list
125 start_index:=1;
126 -- get end of list
127 end_index:=list_index-1;
128 -- loop round from both ends working inwards
129 while (start_index<end_index) loop
130 -- set print options
131 x:=FND_REQUEST.set_print_options(
132 printer => printer_buffer,
133 style => style_buffer,
134 copies => copies_buffer,
135 save_output => save_buffer,
136 print_together => print_buffer);
137
138 -- submit requests for report
139 l_req_id:=fnd_request.submit_request(
140 application => 'PAY',
141 program => l_program,
142 argument1 => gre_list(start_index).gre_name,
143 argument2 => p_business_group,
144 argument3 => p_start_date,
145 argument4 => p_end_date,
146 argument5 => gre_list(start_index).gre_id,
147 argument6 => p_federal,
148 argument7 => p_state,
149 argument8 => p_dimension);
150 -- set print options
151 x:=FND_REQUEST.set_print_options(
152 printer => printer_buffer,
153 style => style_buffer,
154 copies => copies_buffer,
155 save_output => save_buffer,
156 print_together => print_buffer);
157
158 l_req_id:=fnd_request.submit_request(
159 application => 'PAY',
160 program => l_program,
161 argument1 => gre_list(end_index).gre_name,
162 argument2 => p_business_group,
163 argument3 => p_start_date,
164 argument4 => p_end_date,
165 argument5 => gre_list(end_index).gre_id,
166 argument6 => p_federal,
167 argument7 => p_state,
168 argument8 => p_dimension);
169 -- get next values
170 start_index:=start_index+1;
171 end_index:=end_index-1;
172 --
173
174 end loop;
175 -- submit for middle value in list if odd number of gre's
176 if (start_index=end_index) then
177 -- set print options
178 x:=FND_REQUEST.set_print_options(
179 printer => printer_buffer,
180 style => style_buffer,
181 copies => copies_buffer,
182 save_output => save_buffer,
183 print_together => print_buffer);
184
185 l_req_id:=fnd_request.submit_request(
186 application => 'PAY',
187 program => l_program,
188 argument1 => gre_list(start_index).gre_name,
189 argument2 => p_business_group,
190 argument3 => p_start_date,
191 argument4 => p_end_date,
192 argument5 => gre_list(start_index).gre_id,
193 argument6 => p_federal,
194 argument7 => p_state,
195 argument8 => p_dimension);
196 end if;
197 EXCEPTION
198 --
199 WHEN hr_utility.hr_error THEN
200 --
201 -- Set up error message and error return code.
202 --
203 --hr_utility.trace('in the exception');
204 errbuf := hr_utility.get_message;
205 retcode := 2;
206 --
207 --
208 WHEN others THEN
209 --
210 -- Set up error message and return code.
211 --
212 errbuf := sqlerrm;
213 retcode := 2;
214 end federal_trr;
215
216 procedure state_trr
217 is
218 begin
219
220 null;
221 end state_trr;
222 --
223 procedure local_trr(errbuf out varchar2
224 ,retcode out number
225 ,p_business_group number
226 ,p_start_date varchar2
227 ,p_end_date varchar2
228 ,p_gre number
229 ,p_state varchar2
230 ,p_locality_type varchar2
231 ,p_is_city varchar2
232 ,p_city varchar2
233 ,p_is_county varchar2
234 ,p_county varchar2
235 ,p_is_school varchar2
236 ,p_school varchar2
237 ,p_sort_option_1 varchar2
238 ,p_sort_option_2 varchar2
239 ,p_sort_option_3 varchar2
240 ,p_dimension varchar2)
241 is
242 --
243 cursor gre_sizes(c_business_group_id number,
244 c_tax_unit_id number,
245 c_jurisdiction_code varchar2)
246 is
247 select count(*) gre_size,puar.tax_unit_id gre_id,htu.name gre_name
248 from pay_us_asg_reporting puar,
249 hr_tax_units_v htu
250 where puar.tax_unit_id=htu.tax_unit_id
251 and htu.business_group_id=c_business_group_id
252 and substr(puar.jurisdiction_code,1,2)=
253 nvl(c_jurisdiction_code,substr(puar.jurisdiction_code,1,2))
254 and htu.tax_unit_id=nvl(c_tax_unit_id,htu.tax_unit_id)
255 group by puar.tax_unit_id,htu.name
256 order by count(*);
257
258 gre_list gre_info_list;
259 list_index number:=1;
260 start_index number:=1;
261 end_index number:=1;
262 l_req_id number;
263 copies_buffer varchar2(80) := null;
264 print_buffer varchar2(80) := null;
265 printer_buffer varchar2(80) := null;
266 style_buffer varchar2(80) := null;
267 save_buffer boolean := null;
268 save_result varchar2(1) := null;
269 req_id varchar2(80) := null;
270 x boolean;
271
272 --
273 --
274 begin
275 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
276 retcode := 0;
277 -- get printing info
278 req_id:=fnd_profile.value('CONC_REQUEST_ID');
279 print_buffer:=fnd_profile.value('CONC_PRINT_TOGETHER');
280 if (print_buffer is NULL)
281 then print_buffer:='N';
282 end if;
283
284 select number_of_copies,
285 printer,
286 print_style,
287 save_output_flag
288 into copies_buffer,
289 printer_buffer,
290 style_buffer,
291 save_result
292 from fnd_concurrent_requests
293 where request_id = fnd_number.canonical_to_number(req_id);
294
295 if (save_result='Y') then
296 save_buffer:=true;
297 elsif (save_result='N') then
298 save_buffer:=false;
299 else
300 save_buffer:=NULL;
301 end if;
302
303 -- read data into table
304 for grerec in gre_sizes(p_business_group,p_gre,p_state) loop
305 gre_list(list_index).gre_size:=grerec.gre_size;
306 gre_list(list_index).gre_id :=grerec.gre_id;
307 gre_list(list_index).gre_name :=grerec.gre_name;
308
309 list_index:=list_index+1;
310 end loop;
311 -- get start of list
312 start_index:=1;
313 -- get end of list
314 end_index:=list_index-1;
315 -- loop round from both ends working inwards
316 while (start_index<end_index) loop
317 -- set print options
318 x:=FND_REQUEST.set_print_options(
319 printer => printer_buffer,
320 style => style_buffer,
321 copies => copies_buffer,
322 save_output => save_buffer,
323 print_together => print_buffer);
324
325 -- submit requests for report
326 l_req_id:=fnd_request.submit_request(
327 application => 'PAY',
328 program => 'PYLOCTRR',
329 argument1 => gre_list(start_index).gre_name,
330 argument2 => p_business_group,
331 argument3 => p_start_date,
332 argument4 => p_end_date,
333 argument5 => gre_list(start_index).gre_id,
334 argument6 => p_state,
335 argument7 => p_locality_type,
336 argument8 => p_is_city,
337 argument9 => p_city,
338 argument10 => p_is_county,
339 argument11 => p_county,
340 argument12 => p_is_school,
341 argument13 => p_school,
342 argument14 => p_sort_option_1,
343 argument15 => p_sort_option_2,
344 argument16 => p_sort_option_3,
345 argument17 => p_dimension);
346 -- set print options
347 x:=FND_REQUEST.set_print_options(
348 printer => printer_buffer,
349 style => style_buffer,
350 copies => copies_buffer,
351 save_output => save_buffer,
352 print_together => print_buffer);
353
354 l_req_id:=fnd_request.submit_request(
355 application => 'PAY',
356 program => 'PYLOCTRR',
357 argument1 => gre_list(end_index).gre_name,
358 argument2 => p_business_group,
359 argument3 => p_start_date,
360 argument4 => p_end_date,
361 argument5 => gre_list(end_index).gre_id,
362 argument6 => p_state,
363 argument7 => p_locality_type,
364 argument8 => p_is_city,
365 argument9 => p_city,
366 argument10 => p_is_county,
367 argument11 => p_county,
368 argument12 => p_is_school,
369 argument13 => p_school,
370 argument14 => p_sort_option_1,
371 argument15 => p_sort_option_2,
372 argument16 => p_sort_option_3,
373 argument17 => p_dimension);
374 -- get next values
375 start_index:=start_index+1;
376 end_index:=end_index-1;
377 --
378
379 end loop;
380 -- submit for middle value in list if odd number of gre's
381 if (start_index=end_index) then
382 -- set print options
383 x:=FND_REQUEST.set_print_options(
384 printer => printer_buffer,
385 style => style_buffer,
386 copies => copies_buffer,
387 save_output => save_buffer,
388 print_together => print_buffer);
389
390 l_req_id:=fnd_request.submit_request(
391 application => 'PAY',
392 program => 'PYLOCTRR',
393 argument1 => gre_list(start_index).gre_name,
394 argument2 => p_business_group,
395 argument3 => p_start_date,
396 argument4 => p_end_date,
397 argument5 => gre_list(start_index).gre_id,
398 argument6 => p_state,
399 argument7 => p_locality_type,
400 argument8 => p_is_city,
401 argument9 => p_city,
402 argument10 => p_is_county,
403 argument11 => p_county,
404 argument12 => p_is_school,
405 argument13 => p_school,
406 argument14 => p_sort_option_1,
407 argument15 => p_sort_option_2,
408 argument16 => p_sort_option_3,
409 argument17 => p_dimension);
410 end if;
411 EXCEPTION
412 --
413 WHEN hr_utility.hr_error THEN
414 --
415 -- Set up error message and error return code.
416 --
417 errbuf := hr_utility.get_message;
418 retcode := 2;
419 --
420 --
421 --
422 -- Set up error message and return code.
423 --
424 errbuf := sqlerrm;
425 retcode := 2;
426 end local_trr;
427 end TRR_ENGINE_PKG;