[Home] [Help]
PACKAGE BODY: APPS.PQP_EXR_SWI
Source
1 Package Body pqp_exr_swi As
2 /* $Header: pqexrswi.pkb 120.4.12020000.3 2013/03/07 15:16:59 rgottipa ship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'pqp_exr_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_exception_report >-----------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_exception_report
12 (p_validate in number default hr_api.g_false_num
13 ,p_exception_report_name in varchar2
14 ,p_legislation_code in varchar2 default null
15 ,p_business_group_id in number default null
16 ,p_currency_code in varchar2 default null
17 ,p_balance_type_id in number default null
18 ,p_balance_dimension_id in number default null
19 ,p_variance_type in varchar2 default null
20 ,p_variance_value in number default null
21 ,p_comparison_type in varchar2 default null
22 ,p_comparison_value in number default null
23 ,p_language_code in varchar2 default null
24 ,p_exception_report_id out nocopy number
25 ,p_object_version_number out nocopy number
26 ,p_output_format_type in varchar2
27 ,p_variance_operator in varchar2
28 ,p_return_status out nocopy varchar2
29 ) is
30 --
31 -- Variables for API Boolean parameters
32 l_validate boolean;
33 --
34 -- Variables for IN/OUT parameters
35 --
36 -- Other variables
37 l_proc varchar2(72) := g_package ||'create_exception_report';
38 Begin
39 hr_utility.set_location(' Entering:' || l_proc,10);
40 --
41 -- Issue a savepoint
42 --
43 savepoint create_exception_report_swi;
44 --
45 -- Initialise Multiple Message Detection
46 --
47 hr_multi_message.enable_message_list;
48 --
49 -- Remember IN OUT parameter IN values
50 --
51 --
52 -- Convert constant values to their corresponding boolean value
53 --
54 l_validate :=
55 hr_api.constant_to_boolean
56 (p_constant_value => p_validate);
57 --
58 -- Register Surrogate ID or user key values
59 --
60 --
61 -- Call API
62 --
63 pqp_exr_api.create_exception_report
64 (p_validate => l_validate
65 ,p_exception_report_name => p_exception_report_name
66 ,p_legislation_code => p_legislation_code
67 ,p_business_group_id => p_business_group_id
68 ,p_currency_code => p_currency_code
69 ,p_balance_type_id => p_balance_type_id
70 ,p_balance_dimension_id => p_balance_dimension_id
71 ,p_variance_type => p_variance_type
72 ,p_variance_value => p_variance_value
73 ,p_comparison_type => p_comparison_type
74 ,p_comparison_value => p_comparison_value
75 ,p_language_code => p_language_code
76 ,p_exception_report_id => p_exception_report_id
77 ,p_object_version_number => p_object_version_number
78 ,p_output_format_type => p_output_format_type
79 ,p_variance_operator => p_variance_operator
80 );
81 --
82 -- Convert API warning boolean parameter values to specific
83 -- messages and add them to Multiple Message List
84 --
85 --
86 -- Convert API non-warning boolean parameter values
87 --
88 --
89 -- Derive the API return status value based on whether
90 -- messages of any type exist in the Multiple Message List.
91 -- Also disable Multiple Message Detection.
92 --
93 p_return_status := hr_multi_message.get_return_status_disable;
94 hr_utility.set_location(' Leaving:' || l_proc,20);
95 --
96 exception
97 when hr_multi_message.error_message_exist then
98 --
99 -- Catch the Multiple Message List exception which
100 -- indicates API processing has been aborted because
101 -- at least one message exists in the list.
102 --
103 rollback to create_exception_report_swi;
104 --
105 -- Reset IN OUT parameters and set OUT parameters
106 --
107 p_exception_report_id := null;
108 p_object_version_number := null;
109 p_return_status := hr_multi_message.get_return_status_disable;
110 hr_utility.set_location(' Leaving:' || l_proc, 30);
111 when others then
112 --
113 -- When Multiple Message Detection is enabled catch
114 -- any Application specific or other unexpected
115 -- exceptions. Adding appropriate details to the
116 -- Multiple Message List. Otherwise re-raise the
117 -- error.
118 --
119 rollback to create_exception_report_swi;
120 if hr_multi_message.unexpected_error_add(l_proc) then
121 hr_utility.set_location(' Leaving:' || l_proc,40);
122 raise;
123 end if;
124 --
125 -- Reset IN OUT and set OUT parameters
126 --
127 p_exception_report_id := null;
128 p_object_version_number := null;
129 p_return_status := hr_multi_message.get_return_status_disable;
130 hr_utility.set_location(' Leaving:' || l_proc,50);
131 end create_exception_report;
132 -- ----------------------------------------------------------------------------
133 -- |------------------------< delete_exception_report >-----------------------|
134 -- ----------------------------------------------------------------------------
135 PROCEDURE delete_exception_report
136 (p_validate in number default hr_api.g_false_num
137 ,p_exception_report_id in number
138 ,p_object_version_number in number
139 ,p_return_status out nocopy varchar2
140 ) is
141 --
142 -- Variables for API Boolean parameters
143 l_validate boolean;
144 --
145 -- Variables for IN/OUT parameters
146 --
147 -- Other variables
148 l_proc varchar2(72) := g_package ||'delete_exception_report';
149 Begin
150 hr_utility.set_location(' Entering:' || l_proc,10);
151 --
152 -- Issue a savepoint
153 --
154 savepoint delete_exception_report_swi;
155 --
156 -- Initialise Multiple Message Detection
157 --
158 hr_multi_message.enable_message_list;
159 --
160 -- Remember IN OUT parameter IN values
161 --
162 --
163 -- Convert constant values to their corresponding boolean value
164 --
165 l_validate :=
166 hr_api.constant_to_boolean
167 (p_constant_value => p_validate);
168 --
169 -- Register Surrogate ID or user key values
170 --
171 --
172 -- Call API
173 --
174 pqp_exr_api.delete_exception_report
175 (p_validate => l_validate
176 ,p_exception_report_id => p_exception_report_id
177 ,p_object_version_number => p_object_version_number
178 );
179 --
180 -- Convert API warning boolean parameter values to specific
181 -- messages and add them to Multiple Message List
182 --
183 --
184 -- Convert API non-warning boolean parameter values
185 --
186 --
187 -- Derive the API return status value based on whether
188 -- messages of any type exist in the Multiple Message List.
189 -- Also disable Multiple Message Detection.
190 --
191 p_return_status := hr_multi_message.get_return_status_disable;
192 hr_utility.set_location(' Leaving:' || l_proc,20);
193 --
194 exception
195 when hr_multi_message.error_message_exist then
196 --
197 -- Catch the Multiple Message List exception which
198 -- indicates API processing has been aborted because
199 -- at least one message exists in the list.
200 --
201 rollback to delete_exception_report_swi;
202 --
203 -- Reset IN OUT parameters and set OUT parameters
204 --
205 p_return_status := hr_multi_message.get_return_status_disable;
206 hr_utility.set_location(' Leaving:' || l_proc, 30);
207 when others then
208 --
209 -- When Multiple Message Detection is enabled catch
210 -- any Application specific or other unexpected
211 -- exceptions. Adding appropriate details to the
212 -- Multiple Message List. Otherwise re-raise the
213 -- error.
214 --
215 rollback to delete_exception_report_swi;
216 if hr_multi_message.unexpected_error_add(l_proc) then
217 hr_utility.set_location(' Leaving:' || l_proc,40);
218 raise;
219 end if;
220 --
221 -- Reset IN OUT and set OUT parameters
222 --
223 p_return_status := hr_multi_message.get_return_status_disable;
224 hr_utility.set_location(' Leaving:' || l_proc,50);
225 end delete_exception_report;
226 -- ----------------------------------------------------------------------------
227 -- |------------------------< update_exception_report >-----------------------|
228 -- ----------------------------------------------------------------------------
229 PROCEDURE update_exception_report
230 (p_validate in number default hr_api.g_false_num
231 ,p_exception_report_name in varchar2 default hr_api.g_varchar2
232 ,p_legislation_code in varchar2 default hr_api.g_varchar2
233 ,p_business_group_id in number default hr_api.g_number
234 ,p_currency_code in varchar2 default hr_api.g_varchar2
235 ,p_balance_type_id in number default hr_api.g_number
236 ,p_balance_dimension_id in number default hr_api.g_number
237 ,p_variance_type in varchar2 default hr_api.g_varchar2
238 ,p_variance_value in number default hr_api.g_number
239 ,p_comparison_type in varchar2 default hr_api.g_varchar2
240 ,p_comparison_value in number default hr_api.g_number
241 ,p_exception_report_id in number default hr_api.g_number
242 ,p_language_code in varchar2 default hr_api.g_varchar2
243 ,p_object_version_number in out nocopy number
244 ,p_output_format_type in varchar2 default hr_api.g_varchar2
245 ,p_variance_operator in varchar2 default hr_api.g_varchar2
246 ,p_return_status out nocopy varchar2
247 ) is
248 --
249 -- Variables for API Boolean parameters
250 l_validate boolean;
251 --
252 -- Variables for IN/OUT parameters
253 l_object_version_number number;
254 --
255 -- Other variables
256 l_proc varchar2(72) := g_package ||'update_exception_report';
257 Begin
258 hr_utility.set_location(' Entering:' || l_proc,10);
259 --
260 -- Issue a savepoint
261 --
262 savepoint update_exception_report_swi;
263 --
264 -- Initialise Multiple Message Detection
265 --
266 hr_multi_message.enable_message_list;
267 --
268 -- Remember IN OUT parameter IN values
269 --
270 l_object_version_number := p_object_version_number;
271 --
272 -- Convert constant values to their corresponding boolean value
273 --
274 l_validate :=
275 hr_api.constant_to_boolean
276 (p_constant_value => p_validate);
277 --
278 -- Register Surrogate ID or user key values
279 --
280 --
281 -- Call API
282 --
283 pqp_exr_api.update_exception_report
284 (p_validate => l_validate
285 ,p_exception_report_name => p_exception_report_name
286 ,p_legislation_code => p_legislation_code
287 ,p_business_group_id => p_business_group_id
288 ,p_currency_code => p_currency_code
289 ,p_balance_type_id => p_balance_type_id
290 ,p_balance_dimension_id => p_balance_dimension_id
291 ,p_variance_type => p_variance_type
292 ,p_variance_value => p_variance_value
293 ,p_comparison_type => p_comparison_type
294 ,p_comparison_value => p_comparison_value
295 ,p_exception_report_id => p_exception_report_id
296 --,p_language_code => p_language_code
297 ,p_object_version_number => p_object_version_number
298 ,p_output_format_type => p_output_format_type
299 ,p_variance_operator => p_variance_operator
300 );
301 --
302 -- Convert API warning boolean parameter values to specific
303 -- messages and add them to Multiple Message List
304 --
305 --
306 -- Convert API non-warning boolean parameter values
307 --
308 --
309 -- Derive the API return status value based on whether
310 -- messages of any type exist in the Multiple Message List.
311 -- Also disable Multiple Message Detection.
312 --
313 p_return_status := hr_multi_message.get_return_status_disable;
314 hr_utility.set_location(' Leaving:' || l_proc,20);
315 --
316 exception
317 when hr_multi_message.error_message_exist then
318 --
319 -- Catch the Multiple Message List exception which
320 -- indicates API processing has been aborted because
321 -- at least one message exists in the list.
322 --
323 rollback to update_exception_report_swi;
324 --
325 -- Reset IN OUT parameters and set OUT parameters
326 --
327 p_object_version_number := l_object_version_number;
328 p_return_status := hr_multi_message.get_return_status_disable;
329 hr_utility.set_location(' Leaving:' || l_proc, 30);
330 when others then
331 --
332 -- When Multiple Message Detection is enabled catch
333 -- any Application specific or other unexpected
334 -- exceptions. Adding appropriate details to the
335 -- Multiple Message List. Otherwise re-raise the
336 -- error.
337 --
338 rollback to update_exception_report_swi;
339 if hr_multi_message.unexpected_error_add(l_proc) then
340 hr_utility.set_location(' Leaving:' || l_proc,40);
341 raise;
342 end if;
343 --
344 -- Reset IN OUT and set OUT parameters
345 --
346 p_object_version_number := l_object_version_number;
347 p_return_status := hr_multi_message.get_return_status_disable;
348 hr_utility.set_location(' Leaving:' || l_proc,50);
349 end update_exception_report;
350
351
352 -- ----------------------------------------------------------------------------
353 -- |------------------------< submit_request_set >-----------------------|
354 -- ----------------------------------------------------------------------------
355 procedure submit_request_set(
356 p_validate in number default hr_api.g_false_num
357 ,p_exception_report_id in number
358 ,p_legislation_code in varchar2 default null
359 ,p_business_group_id in number default null
360 ,p_consolidation_set_id in number default null
361 ,p_payroll_id in number default null
362 ,p_variance_type in varchar2 default null
363 ,p_variance_value in number default null
364 ,p_request_type in varchar2 default null
365 ,p_gre_id in number default null
366 ,p_state_code in number default null
367 ,p_exception_grp_name in varchar2 default null
368 ,p_exception_rep_name in varchar2 default null
369 ,p_exception_group_id in number
370 ,p_effective_date in date
371 ,p_output_format in varchar2 default null
372 ,p_template_name in varchar2 default null
373 ,p_component_id in number default null
374 ,p_request_id out nocopy number
375 ,p_return_status out nocopy varchar2
376 )
377 is
378 success boolean:= true;
379 request_id NUMBER:= 0;
380 submit_failed EXCEPTION;
381 req_id number;
382 l_request_name varchar2(35);
383 l_grp_request_code varchar(20);
384 l_rep_request_code varchar(20);
385 l_rep_dmy_place_holder1 varchar(20);
386 l_grp_dmy_place_holder1 varchar(20);
387 l_ppa_finder varchar2(20);
388 l_trnsfer_rep_arg17 varchar(50);
389 l_trnsfer_grp_arg14 varchar2(50);
390 l_payroll_dummy varchar2(50);
391 l_variance_type_dummy varchar2(50);
392 l_variance_value_dummy varchar2(50);
393 l_gre_id_dummy varchar2(50);
394 l_state_code_dummy varchar2(50);
395 l_component_id_dummy varchar2(50);
396 l_product_release VARCHAR2(50);
397 l_proc varchar2(72) := g_package||'submit_request_set';
398
399 l_temp_id number;
400
401 -- bug 16421152
402 CURSOR c_template_code is
403 SELECT xtv.template_id FROM xdo_templates_vl xtv
404 where xtv.application_id=8303
405 and xtv.data_source_code = 'PQPRPEXR'
406 and xtv.template_name = p_template_name;
407
408 begin
409 hr_utility.set_location('Entering:'|| l_proc, 1);
410
411 --PPA finder value
412 -- Bug 14260329: Modified the Code to use sequence instead of
413 -- To_char(sysdate,'HHSSSS')
414 SELECT PAY_US_RPT_TOTALS_S.NEXTVAL INTO l_ppa_finder
415 FROM DUAL;
416 --Starting the request set
417 success := fnd_submit.set_request_set('PAY', 'PAYEXREP');
418 --Hard coded the values based on report or group
419 IF p_request_type = 'ER' then
420 l_request_name := 'Exception Report';
421 l_grp_request_code :=NULL;
422 l_rep_request_code :='ER';
423 l_rep_dmy_place_holder1 :=p_exception_report_id||'A';
424 l_grp_dmy_place_holder1 :='A';
425 l_trnsfer_rep_arg17 := 'TRANSFER_REPORT='||p_exception_report_id;
426 l_trnsfer_grp_arg14 := NULL;
427 ELSE
428 l_request_name := 'Exception Group';
429 l_rep_request_code :=NULL;
430 l_grp_request_code :='EG';
431 l_rep_dmy_place_holder1 :='A';
432 l_grp_dmy_place_holder1 :=p_exception_group_id||'A';
433 l_trnsfer_rep_arg17 := NULL;
434 l_trnsfer_grp_arg14 := 'TRANSFER_GROUP='||p_exception_group_id;
435 END IF;
436
437 --Checking the payroll value
438 IF p_payroll_id IS NULL THEN
439 l_payroll_dummy := NULL;
440 ELSE
441 l_payroll_dummy := 'TRANSFER_PAYROLL='||p_payroll_id;
442 END IF;
443
444 --Checking the p_variance_type
445 IF p_variance_type IS NULL THEN
446 l_variance_type_dummy := NULL;
447 ELSE
448 l_variance_type_dummy := 'TRANSFER_VARTYPE='||p_variance_type;
449 END IF;
450
451 --Checking the p_variance_value
452 IF p_variance_value IS NULL THEN
453 l_variance_value_dummy := NULL;
454 ELSE
455 l_variance_value_dummy := 'TRANSFER_VARVALUE='||p_variance_value;
456 END IF;
457
458 --Checking the p_variance_value
459 IF p_gre_id IS NULL THEN
460 l_gre_id_dummy := NULL;
461 ELSE
462 l_gre_id_dummy := 'TRANSFER_GRE='||p_gre_id;
463 END IF;
464
465 --Checking the p_variance_value
466 IF p_state_code IS NULL THEN
467 l_state_code_dummy := NULL;
468 ELSE
469 l_state_code_dummy := 'TRANSFER_JD='||p_state_code;
470 END IF;
471
472 --Checking the p_component_id
473 IF p_component_id IS NULL THEN
474 l_component_id_dummy := NULL;
475 ELSE
476 l_component_id_dummy := 'TRANSFER_COMP='||p_component_id;
477 END IF;
478
479 SELECT substr(p.product_version,1,2) into l_product_release
480 FROM fnd_application a, fnd_application_tl t, fnd_product_installations p
481 WHERE a.application_id = p.application_id
482 AND a.application_id = t.application_id
483 AND t.language = Userenv ('LANG')
484 AND Substr (a.application_short_name, 1, 5) = 'PAY';
485
486 --Starting point to stage starts
487 -- 'Exception Report Preprocessor'
488 if ( success ) then
489 /* submit program PQPEXREP which is in stage STAGE1 */
490 IF TO_NUMBER(l_product_release) = 11 THEN
491 success := fnd_submit.submit_program('PQP','PQPEXREP','Exception Report Preprocessor'
492 ,argument1=>'ARCHIVE'
493 ,argument2=>'EXPREP'
494 ,argument3=>'DEFAULT'
495 ,argument4=> fnd_date.date_to_canonical(p_effective_date)
496 ,argument5=> fnd_date.date_to_canonical(p_effective_date)
497 ,argument6=> 'REPORT'
498 ,argument7=> p_business_group_id
499 ,argument8=> NULL
500 ,argument9=> NULL
501 ,argument10=> p_legislation_code
502 ,argument11=> l_request_name --'Exception Report'/'Exception Group'
503 ,argument12=> l_grp_request_code --'EG' if exception group else NULL
504 ,argument13=> p_exception_group_id -- Exception Group Name
505 ,argument14=> l_trnsfer_grp_arg14 --Exception Group Name Dummy
506 ,argument15=> l_rep_request_code--'ER' if 'report' else NULL
507 ,argument16=> p_exception_report_id
508 ,argument17=> l_trnsfer_rep_arg17--'TRANSFER_REPORT='||p_exception_report_id
509 ,argument18=> l_rep_dmy_place_holder1--'A' or Null based on report id
510 ,argument19=> l_grp_dmy_place_holder1--l_grp_dmy_place_holder1--'A' or null based on group name
511 ,argument20=> p_variance_type
512 ,argument21=> l_variance_type_dummy --Over Ride Variance Type Dummy
513 ,argument22=> p_variance_value
514 ,argument23=> l_variance_value_dummy --Over ride Variance Value Dummy
515 ,argument24=> p_payroll_id
516 ,argument25=> l_payroll_dummy --Payroll Dummy
517 ,argument26=> p_consolidation_set_id
518 ,argument27=>'TRANSFER_CONC_SET='||p_consolidation_set_id
519 ,argument28=>p_gre_id
520 ,argument29=>l_gre_id_dummy
521 ,argument30=>p_state_code
522 ,argument31=>l_state_code_dummy
523 ,argument37=>p_component_id
524 ,argument38=>l_component_id_dummy
525 ,argument32=>l_ppa_finder
526 ,argument33=>'TRANSFER_PPA_FINDER='||l_ppa_finder
527 ,argument34=>'TRANSFER_DATE='||fnd_date.date_to_canonical(p_effective_date)
528 ,argument35=>p_template_name
529 ,argument36=>p_output_format
530 );
531 ELSE
532 OPEN c_template_code;
533 FETCH c_template_code into l_temp_id;
534 CLOSE c_template_code;
535
536 success := fnd_submit.submit_program('PQP','PQPEXREP','Exception Report Preprocessor'
537 ,argument1=>'ARCHIVE'
538 ,argument2=>'EXPREP'
539 ,argument3=>'DEFAULT'
540 ,argument4=> fnd_date.date_to_canonical(p_effective_date)
541 ,argument5=> fnd_date.date_to_canonical(p_effective_date)
542 ,argument6=> 'REPORT'
543 ,argument7=> p_business_group_id
544 ,argument8=> NULL
545 ,argument9=> NULL
546 ,argument10=> p_legislation_code
547 ,argument11=> l_request_name --'Exception Report'/'Exception Group'
548 ,argument12=> l_grp_request_code --'EG' if exception group else NULL
549 ,argument13=> p_exception_group_id -- Exception Group Name
550 ,argument14=> l_trnsfer_grp_arg14 --Exception Group Name Dummy
551 ,argument15=> l_rep_request_code--'ER' if 'report' else NULL
552 ,argument16=> p_exception_report_id
553 ,argument17=> l_trnsfer_rep_arg17--'TRANSFER_REPORT='||p_exception_report_id
554 ,argument18=> l_rep_dmy_place_holder1--'A' or Null based on report id
555 ,argument19=> l_grp_dmy_place_holder1--l_grp_dmy_place_holder1--'A' or null based on group name
556 ,argument20=> p_variance_type
557 ,argument21=> l_variance_type_dummy --Over Ride Variance Type Dummy
558 ,argument22=> p_variance_value
559 ,argument23=> l_variance_value_dummy --Over ride Variance Value Dummy
560 ,argument24=> p_variance_type --OverRide Variance Type Dummy2
561 ,argument25=> p_payroll_id
562 ,argument26=> l_payroll_dummy --Payroll Dummy
563 ,argument27=> p_consolidation_set_id
564 ,argument28=>'TRANSFER_CONC_SET='||p_consolidation_set_id
565 ,argument29=>p_gre_id
566 ,argument30=>l_gre_id_dummy
567 ,argument31=>p_state_code
568 ,argument32=>l_state_code_dummy
569 ,argument33=>p_component_id
570 ,argument34=>l_component_id_dummy
571 ,argument35=>l_ppa_finder
572 ,argument36=>'TRANSFER_PPA_FINDER='||l_ppa_finder
573 ,argument37=>'TRANSFER_DATE='||fnd_date.date_to_canonical(p_effective_date)
574 ,argument38=>l_temp_id
575 ,argument39=>p_output_format
576 /*,argument39=> NULL --OverRide Variance Type Dummy2
577 ,argument24=> p_payroll_id
578 ,argument25=> l_payroll_dummy --Payroll Dummy
579 ,argument26=> p_consolidation_set_id
580 ,argument27=>'TRANSFER_CONC_SET='||p_consolidation_set_id
581 ,argument28=>p_gre_id
582 ,argument29=>l_gre_id_dummy
583 ,argument30=>p_state_code
584 ,argument31=>l_state_code_dummy
585 ,argument37=>p_component_id
586 ,argument38=>l_component_id_dummy
587 ,argument32=>l_ppa_finder
588 ,argument33=>'TRANSFER_PPA_FINDER='||l_ppa_finder
589 ,argument34=>'TRANSFER_DATE='||fnd_date.date_to_canonical(p_effective_date)
590 ,argument35=>p_template_name
591 ,argument36=>p_output_format*/
592 );
593 END IF;
594
595 if ( not success ) then
596 hr_utility.set_location('Exception Report Preprocessor Fail',50);
597 raise submit_failed;
598 ELSE
599 hr_utility.set_location('Exception Report Preprocessor Sucess',50);
600 end if;
601
602 -- 'Exception Report'
603 /* submit program PQPRPEXR which is in stage STAGE2 */
604 success := fnd_submit.submit_program('PQP','PQPXMLLAY', 'Payroll Exception Report'
605 ,argument1=>l_ppa_finder
606 ,argument2=>NULL
607 ,argument3=>p_business_group_id
608 ,argument4=>l_request_name --Select Report or Group
609 ,argument5=>l_grp_request_code--'EG' if exception group else NULL
610 ,argument6=>p_exception_grp_name
611 ,argument7=>l_rep_request_code--'ER' if exception group else NULL
612 ,argument8=>p_exception_report_id
613 ,argument9=>p_variance_type
614 ,argument10=>p_variance_value
615 ,argument11=>p_payroll_id
616 ,argument12=>p_consolidation_set_id
617 ,argument13=>fnd_date.date_to_canonical(p_effective_date)
618 ,argument14=>p_template_name
619 ,argument15=>p_output_format
620 );
621 if ( not success ) then
622 hr_utility.set_location('Stage2 first submit fail' ,50);
623 raise submit_failed;
624 ELSE
625 hr_utility.set_location('Stage2 first submit success' ,50);
626 end if;
627 /* Submit the Request set */
628 req_id := fnd_submit.submit_set(null,FALSE);
629 hr_utility.set_location('Request ID:'||req_id ,50);
630 end if;
631 p_request_id := req_id;
632 p_return_status := 'S';
633 hr_utility.set_location(' Leaving:'||l_proc, 70);
634 exception
635 when submit_failed THEN
636 hr_utility.set_location('error Leaving:'||l_proc, 80);
637 p_return_status := 'E';
638 raise;
639 when others then
640 hr_utility.set_location(' error Leaving:'||l_proc, 80);
641 p_return_status := 'E';
642 raise;
643 end submit_request_set;
644
645 -- ----------------------------------------------------------------------------
646 -- |------------------------< exception_report_xml_process >-----------------------|
647 -- ----------------------------------------------------------------------------
648 FUNCTION get_legislation_code (p_business_group_id IN NUMBER)
649 RETURN VARCHAR2
650 IS
651 l_legislation_code_l per_business_groups.legislation_code%TYPE;
652 BEGIN
653 hr_utility.trace('Enter Legislation code');
654 SELECT legislation_code
655 INTO l_legislation_code_l
656 FROM per_business_groups
657 WHERE business_group_id =p_business_group_id;
658
659 RETURN (l_legislation_code_l);
660 hr_utility.trace('Leaving Legislation code' );
661 EXCEPTION
662 ---------
663 WHEN OTHERS THEN
664 RETURN(NULL);
665
666 END;
667
668 -- ----------------------------------------------------------------------------
669 -- |------------------------< exception_report_xml_process >-----------------------|
670 -- ----------------------------------------------------------------------------
671 procedure exception_report_xml_process(
672 errbuf OUT NOCOPY VARCHAR2
673 ,retcode OUT NOCOPY VARCHAR2
674 ,p_ppa_finder in varchar2 default null
675 ,p_DES_TYPE in varchar2 default null
676 ,p_business_group_id in varchar2 default null
677 ,p_request_name in varchar2 default null
678 ,p_grp_request_code in varchar2 default null
679 ,p_exception_grp_name in varchar2 default null
680 ,p_rep_request_code in varchar2 default null
681 ,p_exception_report_id in varchar2 default null
682 ,p_variance_type in varchar2 default null
683 ,p_variance_value in number default null
684 ,p_payroll_id in number default null
685 ,p_consolidation_set_id in number default null
686 ,p_effective_date in varchar2 default null
687 ,p_template_name in varchar2 default null
688 ,p_output_format in varchar2 default null
689 )
690 is
691 success boolean:= true;
692 request_id NUMBER:= 0;
693 submit_failed EXCEPTION;
694 req_id number;
695 l_request_name varchar2(35);
696 l_grp_request_code varchar(20);
697 l_rep_request_code varchar(20);
698 l_rep_dmy_place_holder1 varchar(20);
699 l_grp_dmy_place_holder1 varchar(20);
700 l_ppa_finder varchar2(20);
701 l_trnsfer_rep_arg17 varchar(50);
702 l_trnsfer_grp_arg14 varchar2(50);
703 l_payroll_dummy varchar2(50);
704 l_variance_type varchar2(50);
705 l_variance_value_dummy varchar2(50);
706 l_gre_id_dummy varchar2(50);
707 l_state_code_dummy varchar2(50);
708 l_proc varchar2(72) := g_package||'exception_report_xml_process';
709 l_request_id number;
710 l_app_short_name varchar2(10);
711 l_template_code varchar2(40);
712 l_default_lang varchar2(10);
713 l_default_territory varchar2(10);
714 l_temp varchar2(500);
715 l_exception_report_id varchar2(40);
716 l_legislation_code per_business_groups.legislation_code%TYPE ;
717
718
719 cursor c_rtf_data(c_template_name in varchar2) is
720 Select application_short_name,template_code,default_language,default_territory
721 from xdo_templates_vl
722 where template_name =c_template_name
723 and application_id=8303;
724
725 CURSOR c_rep_id (c_exception_report_id IN VARCHAR2
726 ,c_business_group_id IN VARCHAR2
727 ,c_legislation_code IN VARCHAR2)
728 IS
729 SELECT exception_report_id
730 FROM pqp_exception_reports
731 WHERE exception_report_name=c_exception_report_id
732 AND (business_group_id =c_business_group_id
733 OR business_group_id IS NULL)
734 AND (legislation_code=c_legislation_code
735 OR legislation_code IS NULL);
736
737 begin
738 IF p_request_name = 'Exception Report' THEN
739 l_rep_request_code :='ER';
740 ELSE
741 l_grp_request_code :='EG';
742 END IF;
743 --Varience value check
744 IF p_variance_type = 'Amount' THEN
745 l_variance_type :='A' ;
746 ELSIF p_variance_type = 'Amount' THEN
747 l_variance_type :='P' ;
748 ELSE
749 l_variance_type :=p_variance_type ;
750 END IF;
751 --Gettign the report ID
752 IF p_exception_report_id IS NOT NULL THEN
753 l_legislation_code:=get_legislation_code(p_business_group_id);
754 open c_rep_id(c_exception_report_id => p_exception_report_id
755 ,c_business_group_id => p_business_group_id
756 ,c_legislation_code => l_legislation_code);
757 fetch c_rep_id into l_exception_report_id;
758 close c_rep_id;
759
760 IF l_exception_report_id IS NULL THEN
761 l_exception_report_id :=p_exception_report_id;
762 END IF;
763
764 END IF;
765
766 hr_utility.set_location('Entering:'|| l_proc, 1);
767
768 open c_rtf_data(c_template_name => p_template_name);
769 fetch c_rtf_data into l_app_short_name,l_template_code,l_default_lang,l_default_territory;
770 close c_rtf_data;
771
772
773 IF p_output_format = 'HTML' THEN
774 success := FND_REQUEST.add_layout(l_app_short_name,l_template_code,l_default_lang,l_default_territory,'HTML');
775 ELSIF p_output_format = 'PDF' or p_output_format ='TXT'THEN
776 success := FND_REQUEST.add_layout(l_app_short_name,l_template_code,l_default_lang,l_default_territory,'PDF');
777 ELSIF p_output_format = 'EXCEL' or p_output_format ='CSV' THEN
778 success := FND_REQUEST.add_layout(l_app_short_name,l_template_code,l_default_lang,l_default_territory,'EXCEL');
779 ELSIF p_output_format = 'RTF' THEN
780 success := FND_REQUEST.add_layout(l_app_short_name,l_template_code,l_default_lang,l_default_territory,'RTF');
781 END IF;
782
783 if ( not success ) then
784 hr_utility.set_location('XML submit fail' ,50);
785 raise submit_failed;
786 ELSE
787 hr_utility.set_location('XML submit success' ,50);
788 end if;
789
790 l_request_id := FND_REQUEST.submit_request(
791 'PQP'
792 ,'PQPRPEXR'
793 ,NULL
794 ,NULL
795 ,NULL
796 ,p_ppa_finder
797 ,NULL
798 ,p_business_group_id
799 ,p_request_name --Select Report or Group
800 ,l_grp_request_code--'EG' if exception group else NULL
801 ,p_exception_grp_name
802 ,l_rep_request_code --p_rep_request_code--'ER' if exception group else NULL
803 ,l_exception_report_id
804 ,l_variance_type
805 ,p_variance_value
806 ,p_payroll_id
807 ,p_consolidation_set_id
808 ,p_effective_date--fnd_date.date_to_canonical(p_effective_date)
809 );
810
811 hr_utility.set_location('Current l_request_id:'||l_request_id, 80);
812 hr_utility.set_location(' Leaving:'||l_proc, 70);
813 exception
814 when submit_failed THEN
815 hr_utility.set_location('error Leaving:'||l_proc, 80);
816 raise;
817 when others then
818 hr_utility.set_location(' error Leaving:'||l_proc, 80);
819 raise;
820 end exception_report_xml_process;
821
822
823 end pqp_exr_swi;