[Home] [Help]
PACKAGE BODY: APPS.PAY_US_SQWL_GRE
Source
1 PACKAGE BODY pay_us_sqwl_gre AS
2 /* $Header: payussqwlgre.pkb 120.12.12020000.3 2013/01/22 11:44:22 schowl ship $*/
3
4 /*===========================================================================+
5 | Copyright (c) 2001 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9
10 Name
11 pay_us_sqwl_gre
12
13 Purpose
14 The purpose of this package is to find the eligible
15 GREs for the SQWL and submit the child SQWL Concurrent Program.
16
17 Notes
18 Referenced By: Package pay_us_sqwl_gre
19
20 History
21
22 24-Feb-2011 sjawid 115.0 10649380 Initial Version
23 03-Mar-2011 sjawid 115.2 10649380 Handled special case for NY SQWL with Quarter 4 sqwl
24 10-Mar-2011 sjawid 115.5 11850208 Corrected Cursor c_get_all_gres
25 11-Mar-2011 sjawid 115.6 11742500 Added new parameters to function
26 Process_sqwl
27 14-Mar-2011 sjawid 115.7 11850208 Modified cursor c_get_all_gres
28 20-Mar-2011 sjawid 115.8 12358087/ Removed the Balance check for
29 115.9 12357156 SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD
30 and added a query to find the count of assignment actions
31 in the given quarter. If the count > 0 then only the child
32 SQWL will be processed.
33 11-Mar-2011 sjawid 115.10 12538535 Added new parameter action_parameter_group
34 28-Jun-2011 rosuri 115.11 Added Language='US' and clause to
35 cursor c_get_format_type.
36 10-Aug-2011 rosuri 115.12 12391841 Added check for SUI State in
37 Tax info Form.
38
39 07-JUN-2012 rmugloo 115.13 9730326 Added new parameter fold_output for SQWL fold
40 22-Jan-2013 schowl 115.14 14456648 Added a new procedure 'process_smwl'.
41 Modified the procedure 'rollback_process' to consider
42 SMWL processes.
43 */
44
45 gv_package_name VARCHAR2(50) := 'pay_us_sqwl_gre';
46
47
48 PROCEDURE process_sqwl (errbuf OUT nocopy VARCHAR2,
49 retcode OUT nocopy NUMBER,
50 p_state IN VARCHAR2,
51 p_quarter IN VARCHAR2,
52 p_effective_date IN VARCHAR2,
53 p_report_option IN VARCHAR2,
54 p_report_option_flag IN VARCHAR2,
55 p_tax_unit_id NUMBER,
56 p_format IN VARCHAR2,
57 p_business_group_id IN NUMBER,
58 p_transmitter_gre IN VARCHAR2,
59 p_Create_Audit_Report_Flag IN VARCHAR2,
60 p_Create_Audit_Report IN VARCHAR2,
61 P_Establishment_Flag IN VARCHAR2,
62 p_Establishment_Hierarchy IN VARCHAR2,
63 p_Hierarchy_Version IN VARCHAR2,
64 p_session_date IN VARCHAR2,
65 p_out_of_state_wage_check_flag IN VARCHAR2,
66 p_out_of_state_wage_check IN VARCHAR2,
67 p_action_parameter_group IN VARCHAR2,
68 p_fold_output IN VARCHAR2)
69
70 IS
71
72 ld_Quater_start_date date;
73 ld_Quater_end_date date;
74 lv_report_category varchar2(20);
75 ln_business_group_id number;
76 l_file_name varchar2(20);
77 lv_year varchar2(20);
78 lv_reporting_quarter varchar2(20);
79 l_req_id fnd_concurrent_requests.request_id%TYPE;
80 l_payroll_action_id number;
81 ln_locking_action_id number;
82
83 l_value number;
84
85 l_phase varchar2(10);
86 l_status varchar2(10);
87 l_dev_phase varchar2(10);
88 l_dev_status varchar2(10);
89 l_message varchar2(240);
90 l_return_val boolean;
91 lv_format_meaning fnd_lookup_values.meaning%TYPE;
92 lv_state_code pay_us_states.state_code%TYPE;
93 l_previous_sqwl_run_check number;
94 l_chk_gre_assign_actions number;
95
96 CURSOR c_get_all_gres is
97 SELECT DISTINCT
98 hoi.organization_id
99 FROM hr_organization_information hoi
100 ,hr_organization_units hou1
101 WHERE hoi.org_information1 = p_state
102 AND hou1.business_group_id = p_business_group_id
103 AND hoi.organization_id = hou1.organization_id
104 AND hoi.org_information_context = 'State Tax Rules'
105 AND nvl (p_tax_unit_id
106 ,hoi.organization_id) = hoi.organization_id
107 AND nvl (hoi.org_information16
108 ,'No') = 'No'
109 AND nvl (hoi.org_information20
110 ,'No') = 'No'
111 AND (
112 (
113 p_state IN ('CA','ME')
114 )
115 OR NOT EXISTS
116 (
117 SELECT 'x'
118 FROM hr_organization_information hoi2
119 ,hr_organization_units hou2
120 WHERE hoi2.organization_id = hoi.organization_id
121 AND hoi2.org_information_context = '1099R Magnetic Report Rules'
122 AND hoi2.org_information2 IS NOT NULL
123 AND hou2.business_group_id = p_business_group_id
124 AND hoi2.organization_id = hou2.organization_id
125 )
126 );
127
128 CURSOR c_get_format_type(cp_state_abbrv IN VARCHAR2,cp_format IN VARCHAR2) IS
129 SELECT flv.meaning
130 FROM fnd_lookup_values flv
131 WHERE flv.lookup_type = cp_state_abbrv||'_SQWL_MEDIA_TYPE'
132 AND flv.lookup_code = cp_format
133 AND flv.enabled_flag = 'Y'
134 AND LANGUAGE='US';
135 /* Before adding this and clause the filenames in the SQWL report were having
136 redundant characters like { ] */
137
138 /*SELECT distinct puar.tax_unit_id
139 FROM hr_organization_information HOI,
140 per_assignments_f ASG,
141 pay_us_asg_reporting puar,
142 pay_state_rules SR
143 WHERE SR.state_code = 'NY' --l_state
144 AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||'%'
145 AND ASG.assignment_id = puar.assignment_id
146 AND ASG.assignment_type = 'E'
147 AND ASG.effective_start_date <= to_date('31-MAR-2010') --l_effective_date
148 AND ASG.effective_end_date >= to_date('01-JAN-2010') --l_start_date
149 AND ASG.business_group_id + 0 = 0 --l_business_group_id
150 AND (('NY' IN ( 'CA','ME'))
151 OR (not exists (select 'x'
152 from hr_organization_information HOI2
153 where HOI2.organization_id = puar.tax_unit_id
154 AND HOI2.ORG_INFORMATION_CONTEXT = '1099R Magnetic Report Rules'
155 AND HOI2.ORG_INFORMATION2 is not null)))
156 AND HOI.organization_id = puar.tax_unit_id
157 AND HOI.ORG_INFORMATION_CONTEXT = 'State Tax Rules'
158 AND HOI.ORG_INFORMATION1 = 'NY' --l_state
159 AND NVL(HOI.ORG_INFORMATION16,'No') = 'No'
160 AND NVL(HOI.ORG_INFORMATION20,'No') = 'No'
161 AND ASG.payroll_id is not null; */
162
163 BEGIN
164
165 /*hr_utility.trace_on(null,'SQWL');*/
166 ld_Quater_end_date:=fnd_date.canonical_to_date(p_effective_date);
167
168 /* Handling special condition for NY state: */
169 IF p_state = 'NY' AND to_char(ld_Quater_end_date,'MM') = '12' THEN
170 ld_Quater_start_date := trunc(ld_Quater_end_date,'Y');
171 ELSE
172 ld_Quater_start_date:=fnd_date.canonical_to_date(p_quarter);
173 END IF;
174
175
176 /* ld_Quater_end_date := add_months(trunc(ld_Quater_start_date,'Q'),3)-1; */
177 lv_report_category :=p_format;
178 lv_year:=to_char(ld_Quater_start_date,'YYYY');
179 lv_reporting_quarter:=to_char(ld_Quater_end_date,'MMYYYY');
180
181 SELECT pay_payroll_actions_s.nextval
182 INTO ln_locking_action_id
183 FROM dual;
184
185 /*g_payroll_action_id:=ln_locking_action_id;*/
186
187 OPEN c_get_format_type(p_state,p_format);
188 FETCH c_get_format_type into lv_format_meaning;
189 CLOSE c_get_format_type;
190
191 hr_utility.trace('p_tax_unit_id: '||p_tax_unit_id);
192 hr_utility.trace('p_effective_date: '||p_effective_date);
193 hr_utility.trace('p_report_option: '||p_report_option);
194
195 hr_utility.trace('inserting payroll action: '||ln_locking_action_id);
196 INSERT
197 INTO pay_payroll_actions
198 (payroll_action_id
199 ,action_type
200 ,business_group_id
201 ,consolidation_set_id
202 ,payroll_id
203 ,action_population_status
204 ,action_status
205 ,effective_date
206 ,comments
207 ,object_version_number
208 ,pay_advice_message
209 ,report_type
210 ,report_qualifier
211 ,request_id
212 ,report_category
213 ,start_date)
214 VALUES
215 (ln_locking_action_id
216 ,'X'
217 ,p_business_group_id
218 ,NULL
219 ,NULL
220 ,'C'
221 ,'C'
222 ,ld_quater_end_date
223 ,NULL
224 ,1
225 ,NULL
226 ,'SQWL_PARENT'
227 ,p_state
228 ,fnd_profile.value ('CONC_REQUEST_ID')
229 ,p_format
230 ,ld_quater_start_date);
231
232
233
234
235 /* getting State Code */
236 IF p_state is NOT NULL THEN
237 SELECT state_code INTO lv_state_code
238 FROM pay_us_states
239 WHERE STATE_ABBREV = p_state;
240 END IF;
241
242 IF p_report_option <> 'SQWL_OLD' THEN --change with lookup code
243 FOR c_get_all_gres_rec IN c_get_all_gres LOOP
244
245 SELECT count(1) into l_previous_sqwl_run_check from pay_payroll_actions
246 WHERE report_type = 'SQWL'
247 AND report_qualifier = p_state
248 AND business_group_id = p_business_group_id
249 AND effective_date = fnd_date.canonical_to_date (p_effective_date)
250 AND pay_core_utils.get_parameter('TRANSFER_GRE',legislative_parameters) = c_get_all_gres_rec.organization_id;
251
252 hr_utility.trace('l_previous_sqwl_run_check: '||l_previous_sqwl_run_check);
253
254 select count(paa.assignment_action_id)
255 into l_chk_gre_assign_actions
256 from pay_assignment_actions paa,
257 pay_payroll_actions pact,
258 pay_payrolls_f ppf,
259 pay_us_emp_fed_tax_rules_f peft
260 where pact.effective_date
261 between ld_Quater_start_date
262 and ld_Quater_end_date
263 and pact.payroll_action_id=paa.payroll_action_id
264 and pact.action_type in ('R', 'Q', 'I','B')
265 and paa.action_status = 'C'
266 and pact.action_status = 'C'
267 and paa.tax_unit_id = c_get_all_gres_rec.organization_id
268 and ppf.payroll_id = pact.payroll_id
269 and ppf.business_group_id =p_business_group_id
270 and peft.ASSIGNMENT_ID = paa.ASSIGNMENT_ID
271 and pact.EFFECTIVE_DATE between
272 peft.EFFECTIVE_START_DATE and peft.EFFECTIVE_END_DATE
273 and peft.SUI_STATE_CODE = lv_state_code;
274 -- BUG 12391841
275 hr_utility.trace('l_chk_gre_assign_actions: '||l_chk_gre_assign_actions);
276
277 IF l_previous_sqwl_run_check = 0 and l_chk_gre_assign_actions > 0 THEN
278
279 SELECT pay_us_reporting_utils_pkg.get_file_name(c_get_all_gres_rec.organization_id,
280 'SQWL',
281 p_state,
282 to_char(ld_Quater_end_date,'YYYY/MM/DD') ||' 00:00:00',
283 substr(lv_format_meaning,1,1) )
284 INTO l_file_name
285 FROM dual;
286
287 hr_utility.trace('c_get_all_gres_rec.organization_id : '||c_get_all_gres_rec.organization_id);
288
289
290 hr_utility.trace(' calling conc request :');
291 hr_utility.trace(' final p_state :'||p_state);
292 hr_utility.trace(' final lv_year :'||lv_year);
293 hr_utility.trace(' final p_business_group_id :'||p_business_group_id);
294 hr_utility.trace(' final lv_report_category :'||lv_report_category);
295 hr_utility.trace(' final c_get_all_gres_rec.organization_id :'||c_get_all_gres_rec.organization_id);
296 BEGIN
297 l_req_id := fnd_request.submit_request(application => 'PAY',
298 program => 'SQWLARCH',
299 argument1 => 'ARCHIVE',
300 argument2 => 'SQWL' ,
301 argument3 => p_state ,
302 argument4 => to_char(ld_Quater_start_date,'YYYY/MM/DD') || ' 00:00:00' ,
303 argument5 => to_char(ld_Quater_end_date,'YYYY/MM/DD') || ' 00:00:00',
304 argument6 => lv_report_category,
305 argument7 => p_business_group_id,
306 argument8 => l_file_name,
307 argument9 => l_file_name,
308 argument10 => p_action_parameter_group,
309 argument11 => 'TRANSFER_STATE='||p_state,
310 argument12 => 'TRANSFER_REPORTING_YEAR=' || lv_year,
311 argument13 => 'TRANSFER_REPORTING_QUARTER=' || lv_reporting_quarter,
312 argument14 => p_business_group_id,
313 argument15 => 'TRANSFER_TRANS_LEGAL_CO_ID=' || p_transmitter_gre,
314 argument16 => 'TRANSFER_DATE=' || to_char(ld_Quater_end_date,'DD-MON-YYYY'),
315 argument17 => 'TRANSFER_REPORT_CATEGORY='|| lv_report_category ,
316 argument18 => 1,
317 argument19 =>'Y',
318 argument20 => 'TRNS_AUDIT=Y',
319 argument21 => p_session_date, --session_date
320 argument22 => P_Establishment_Flag,
321 argument23 => p_Establishment_Hierarchy,
322 argument24 => 'TRANSFER_HIERARCHY_ID='||p_Establishment_Hierarchy,
323 argument25 => p_Hierarchy_Version,
324 argument26 => 'TRANSFER_HIERARCHY_VERSION='||p_Hierarchy_Version,
325 argument27 => c_get_all_gres_rec.organization_id,
326 argument28 => 'TRANSFER_GRE='||c_get_all_gres_rec.organization_id,
327 argument29 => 'TRANSFER_PARENT_PAYROLL_ACTION=' ||ln_locking_action_id,
328 argument30 => 'TRANSFER_FOLD_OUTPUT=' || p_fold_output);
329
330 EXCEPTION
331
332 WHEN OTHERS THEN
333
334 HR_UTILITY.TRACE('SQLERRM = ' || SQLERRM);
335
336 END ;
337
338 commit;
339
340 hr_utility.trace('l_payroll_action_id : '||l_payroll_action_id);
341
342 END IF; /*l_previous_sqwl_run_check*/
343 end loop;
344 ELSE
345
346 SELECT pay_us_reporting_utils_pkg.get_file_name( p_business_group_id,
347 'SQWL',
348 p_state,
349 to_char(ld_Quater_end_date,'YYYY/MM/DD') ||' 00:00:00',
350 substr(lv_format_meaning,1,1) )
351 INTO l_file_name
352 FROM dual;
353
354
355 BEGIN
356 l_req_id := fnd_request.submit_request(application => 'PAY',
357 program => 'SQWLARCH',
358 argument1 => 'ARCHIVE',
359 argument2 => 'SQWL' ,
360 argument3 => p_state ,
361 argument4 => to_char(ld_Quater_start_date,'YYYY/MM/DD') || ' 00:00:00' ,
362 argument5 => to_char(ld_Quater_end_date,'YYYY/MM/DD') || ' 00:00:00',
363 argument6 => lv_report_category,
364 argument7 => p_business_group_id,
365 argument8 => l_file_name,
366 argument9 => l_file_name,
367 argument10 => p_action_parameter_group,
368 argument11=> 'TRANSFER_STATE='||p_state,
369 argument12 => 'TRANSFER_REPORTING_YEAR=' || lv_year,
370 argument13 => 'TRANSFER_REPORTING_QUARTER=' || lv_reporting_quarter,
371 argument14 => p_business_group_id,
372 argument15 => 'TRANSFER_TRANS_LEGAL_CO_ID=' || p_transmitter_gre,
373 argument16 => 'TRANSFER_DATE=' || to_char(ld_Quater_end_date,'DD-MON-YYYY'),
374 argument17 => 'TRANSFER_REPORT_CATEGORY='|| lv_report_category ,
375 argument18 => 1,
376 argument19 =>'Y',
377 argument20 => 'TRNS_AUDIT=Y',
378 argument21 => p_session_date, --session_date
379 argument22 => P_Establishment_Flag,
380 argument23 => p_Establishment_Hierarchy,
381 argument24 => 'TRANSFER_HIERARCHY_ID='||p_Establishment_Hierarchy,
382 argument25 => p_Hierarchy_Version,
383 argument26 => 'TRANSFER_HIERARCHY_VERSION='||p_Hierarchy_Version,
384 argument27 => null, /*Passing gre as null*/
385 argument28 => 'TRANSFER_GRE=',
386 argument29 => 'TRANSFER_PARENT_PAYROLL_ACTION=' ||ln_locking_action_id,
387 argument30 => 'TRANSFER_FOLD_OUTPUT=' || p_fold_output);
388
389 EXCEPTION
390
391 WHEN OTHERS THEN
392
393 HR_UTILITY.TRACE('SQLERRM = ' || SQLERRM);
394
395 END ;
396 commit;
397 NULL;
398 END IF;
399
400 hr_utility.trace_off;
401 END; /* procedure process_sqwl */
402
403 /* Rollback SQWL */
404 PROCEDURE rollback_sqwl(errbuf OUT nocopy VARCHAR2,
405 retcode OUT nocopy NUMBER,
406 p_process_year IN VARCHAR2,
407 p_payroll_action_id IN NUMBER,
408 p_payroll_action_id_hidden IN VARCHAR2
409 ) IS
410
411 CURSOR c_get_child_payroll_actions IS
412 SELECT pact.payroll_action_id
413 FROM pay_payroll_actions pact
414 WHERE action_type = 'X'
415 AND report_type in ('SQWL', 'SMWL') /* Modified for Bug 14456648 */
416 AND pay_us_payroll_utils.get_parameter ('TRANSFER_PARENT_PAYROLL_ACTION'
417 ,pact.legislative_parameters) = p_payroll_action_id;
418
419 CURSOR c_get_report_type IS
420 SELECT report_type
421 FROM pay_payroll_actions
422 WHERE payroll_action_id = p_payroll_action_id;
423
424 CURSOR c_get_child_count(cp_payroll_action_id NUMBER) IS
425 SELECT count (payroll_action_id)
426 FROM pay_payroll_actions p1
427 WHERE pay_us_payroll_utils.get_parameter ('TRANSFER_PARENT_PAYROLL_ACTION'
428 ,p1.legislative_parameters) IN
429 (
430 SELECT pay_us_payroll_utils.get_parameter ('TRANSFER_PARENT_PAYROLL_ACTION'
431 ,legislative_parameters)
432 FROM pay_payroll_actions
433 WHERE payroll_action_id = cp_payroll_action_id
434 );
435
436
437 ln_child_payroll_action_id NUMBER;
438 l_req_id NUMBER;
439 l_report_type pay_payroll_actions.report_type%TYPE;
440 l_child_count NUMBER;
441 l_parent_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
442 BEGIN
443 /* hr_utility.trace_on(null,'SQWL'); */
444 hr_utility.trace('p_process_year : '||p_process_year);
445 hr_utility.trace('p_payroll_action_id : '||p_payroll_action_id);
446 hr_utility.trace('p_payroll_action_id_hidden : '||p_payroll_action_id_hidden);
447
448 SELECT report_type INTO l_report_type
449 FROM pay_payroll_actions
450 WHERE payroll_action_id = p_payroll_action_id;
451
452 IF l_report_type in ('SQWL_PARENT', 'SMWL_PARENT') THEN /* Modified for Bug 14456648 */
453 OPEN c_get_child_payroll_actions;
454
455 LOOP
456 FETCH c_get_child_payroll_actions INTO ln_child_payroll_action_id;
457
458 IF c_get_child_payroll_actions%notfound then
459 hr_utility.trace('payroll_action not found');
460 BEGIN
461 l_req_id := fnd_request.submit_request(application => 'PAY',
462 program => 'US_ROLLBACK_PROCESS', -- */ 'US_RETRY_PROCESS',
463 argument1 => 'ROLLBACK', --*/ 'RERUN',
464 argument2 => '' ,
465 argument3 => '' ,
466 argument4 => p_process_year ,
467 argument5 => 'X',
468 argument6 =>p_payroll_action_id,
469 argument7 => 'PAYROLL_ACTION_ID='||p_payroll_action_id );
470
471 EXCEPTION
472
473 WHEN OTHERS THEN
474
475 HR_UTILITY.TRACE('SQLERRM = ' || SQLERRM);
476
477 END ;
478 commit;
479 /* PY_ROLLBACK_PKG.rollback_payroll_action(p_payroll_action_id);*/
480 hr_utility.trace('rolled back parent payroll action id '||p_payroll_action_id);
481 EXIT;
482 END IF;
483
484 hr_utility.trace('ln_child_payroll_action_id : '||ln_child_payroll_action_id);
485 BEGIN
486 l_req_id := fnd_request.submit_request(application => 'PAY',
487 program => 'US_ROLLBACK_PROCESS', -- */ 'US_RETRY_PROCESS',
488 argument1 => 'ROLLBACK', --*/ 'RERUN',
489 argument2 => '' ,
490 argument3 => '' ,
491 argument4 => p_process_year ,
492 argument5 => 'X',
493 argument6 =>ln_child_payroll_action_id,
494 argument7 => 'PAYROLL_ACTION_ID='||ln_child_payroll_action_id );
495
496 EXCEPTION
497
498 WHEN OTHERS THEN
499
500 HR_UTILITY.TRACE('SQLERRM = ' || SQLERRM);
501
502 END ;
503 COMMIT;
504 /*PY_ROLLBACK_PKG.rollback_payroll_action(ln_child_payroll_action_id);*/
505 hr_utility.trace('rolled back child payroll action id '||ln_child_payroll_action_id);
506
507 END LOOP;
508 ELSIF l_report_type = 'SQWL' OR l_report_type = 'SMWL' THEN /* Modified for Bug 14456648 */
509
510 hr_utility.trace('l_report_type '||l_report_type);
511
512 BEGIN
513 SELECT pay_us_payroll_utils.get_parameter('TRANSFER_PARENT_PAYROLL_ACTION',legislative_parameters)
514 INTO l_parent_payroll_action_id from pay_payroll_actions
515 WHERE payroll_action_id = p_payroll_action_id;
516 EXCEPTION
517 WHEN NO_DATA_FOUND then
518 null;
519 WHEN OTHERS THEN
520 null;
521 END;
522
523 hr_utility.trace('l_parent_payroll_action_id '||l_parent_payroll_action_id);
524 IF l_parent_payroll_action_id is not null then
525 BEGIN
526 SELECT count(payroll_action_id) into l_child_count
527 FROM pay_payroll_actions p1
528 WHERE pay_us_payroll_utils.get_parameter('TRANSFER_PARENT_PAYROLL_ACTION',p1.legislative_parameters)
529 = l_parent_payroll_action_id;
530 EXCEPTION
531 WHEN OTHERS THEN
532 null;
533 END;
534 END IF;
535
536 hr_utility.trace('l_child_count '||l_child_count);
537
538 BEGIN
539 l_req_id := fnd_request.submit_request(application => 'PAY',
540 program => 'US_ROLLBACK_PROCESS', -- */ 'US_RETRY_PROCESS',
541 argument1 => 'ROLLBACK', --*/ 'RERUN',
542 argument2 => '' ,
543 argument3 => '' ,
544 argument4 => p_process_year ,
545 argument5 => 'X',
546 argument6 =>p_payroll_action_id,
547 argument7 => 'PAYROLL_ACTION_ID='||p_payroll_action_id );
548
549 EXCEPTION
550
551 WHEN OTHERS THEN
552
553 HR_UTILITY.TRACE('SQLERRM = ' || SQLERRM);
554
555 END ;
556 COMMIT;
557
558
559
560 IF l_child_count = 1 THEN
561 hr_utility.trace('l_child_count true '||l_child_count);
562 BEGIN
563 l_req_id := fnd_request.submit_request(application => 'PAY',
564 program => 'US_ROLLBACK_PROCESS', -- */ 'US_RETRY_PROCESS',
565 argument1 => 'ROLLBACK', --*/ 'RERUN',
566 argument2 => '' ,
567 argument3 => '' ,
568 argument4 => p_process_year ,
569 argument5 => 'X',
570 argument6 =>l_parent_payroll_action_id,
571 argument7 => 'PAYROLL_ACTION_ID='||l_parent_payroll_action_id );
572
573 EXCEPTION
574
575 WHEN OTHERS THEN
576
577 HR_UTILITY.TRACE('SQLERRM = ' || SQLERRM);
578
579 END ;
580 COMMIT;
581
582 END IF;
583
584
585 END IF;
586 hr_utility.trace_off;
587 END; /* Procedure Rollback */
588
589
590 PROCEDURE process_smwl (errbuf OUT nocopy VARCHAR2,
591 retcode OUT nocopy NUMBER,
592 p_state IN VARCHAR2,
593 p_month IN VARCHAR2,
594 p_effective_date IN VARCHAR2,
595 p_report_option IN VARCHAR2,
596 p_report_option_flag IN VARCHAR2,
597 p_tax_unit_id NUMBER,
598 p_format IN VARCHAR2,
599 p_business_group_id IN NUMBER,
600 p_transmitter_gre IN VARCHAR2,
601 p_Create_Audit_Report_Flag IN VARCHAR2,
602 p_Create_Audit_Report IN VARCHAR2,
603 P_Establishment_Flag IN VARCHAR2,
604 p_Establishment_Hierarchy IN VARCHAR2,
605 p_Hierarchy_Version IN VARCHAR2,
606 p_session_date IN VARCHAR2,
607 p_out_of_state_wage_check_flag IN VARCHAR2,
608 p_out_of_state_wage_check IN VARCHAR2,
609 p_action_parameter_group IN VARCHAR2,
610 p_fold_output IN VARCHAR2)
611
612 IS
613
614 ld_Month_start_date date;
615 ld_Month_end_date date;
616 lv_report_category varchar2(20);
617 ln_business_group_id number;
618 l_file_name varchar2(20);
619 lv_year varchar2(20);
620 lv_reporting_month varchar2(20);
621 l_req_id fnd_concurrent_requests.request_id%TYPE;
622 l_payroll_action_id number;
623 ln_locking_action_id number;
624
625 l_value number;
626
627 l_phase varchar2(10);
628 l_status varchar2(10);
629 l_dev_phase varchar2(10);
630 l_dev_status varchar2(10);
631 l_message varchar2(240);
632 l_return_val boolean;
633 lv_format_meaning fnd_lookup_values.meaning%TYPE;
634 lv_state_code pay_us_states.state_code%TYPE;
635 l_previous_smwl_run_check number;
636 l_chk_gre_assign_actions number;
637
638 CURSOR c_get_all_gres is
639 SELECT DISTINCT
640 hoi.organization_id
641 FROM hr_organization_information hoi
642 ,hr_organization_units hou1
643 WHERE hoi.org_information1 = p_state
644 AND hou1.business_group_id = p_business_group_id
645 AND hoi.organization_id = hou1.organization_id
646 AND hoi.org_information_context = 'State Tax Rules'
647 AND nvl (p_tax_unit_id
648 ,hoi.organization_id) = hoi.organization_id
649 AND nvl (hoi.org_information16
650 ,'No') = 'No'
651 AND nvl (hoi.org_information20
652 ,'No') = 'No'
653 AND (
654 (
655 p_state IN ('CA','ME')
656 )
657 OR NOT EXISTS
658 (
659 SELECT 'x'
660 FROM hr_organization_information hoi2
661 ,hr_organization_units hou2
662 WHERE hoi2.organization_id = hoi.organization_id
663 AND hoi2.org_information_context = '1099R Magnetic Report Rules'
664 AND hoi2.org_information2 IS NOT NULL
665 AND hou2.business_group_id = p_business_group_id
666 AND hoi2.organization_id = hou2.organization_id
667 )
668 );
669
670 CURSOR c_get_format_type(cp_state_abbrv IN VARCHAR2,cp_format IN VARCHAR2) IS
671 SELECT flv.meaning
672 FROM fnd_lookup_values flv
673 WHERE flv.lookup_type = cp_state_abbrv||'_SMWL_MEDIA_TYPE'
674 AND flv.lookup_code = cp_format
675 AND flv.enabled_flag = 'Y'
676 AND LANGUAGE='US';
677 /* Before adding this and clause the filenames in the SQWL report were having
678 redundant characters like { ] */
679
680
681 /*SELECT distinct puar.tax_unit_id
682 FROM hr_organization_information HOI,
683 per_assignments_f ASG,
684 pay_us_asg_reporting puar,
685 pay_state_rules SR
686 WHERE SR.state_code = 'NY' --l_state
687 AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||'%'
688 AND ASG.assignment_id = puar.assignment_id
689 AND ASG.assignment_type = 'E'
690 AND ASG.effective_start_date <= to_date('31-MAR-2010') --l_effective_date
691 AND ASG.effective_end_date >= to_date('01-JAN-2010') --l_start_date
692 AND ASG.business_group_id + 0 = 0 --l_business_group_id
693 AND (('NY' IN ( 'CA','ME'))
694 OR (not exists (select 'x'
695 from hr_organization_information HOI2
696 where HOI2.organization_id = puar.tax_unit_id
697 AND HOI2.ORG_INFORMATION_CONTEXT = '1099R Magnetic Report Rules'
698 AND HOI2.ORG_INFORMATION2 is not null)))
699 AND HOI.organization_id = puar.tax_unit_id
700 AND HOI.ORG_INFORMATION_CONTEXT = 'State Tax Rules'
701 AND HOI.ORG_INFORMATION1 = 'NY' --l_state
702 AND NVL(HOI.ORG_INFORMATION16,'No') = 'No'
703 AND NVL(HOI.ORG_INFORMATION20,'No') = 'No'
704 AND ASG.payroll_id is not null; */
705
706 BEGIN
707
708 /*hr_utility.trace_on(null,'SMWL');*/
709 ld_Month_end_date:=fnd_date.canonical_to_date(p_effective_date);
710
711 /* Handling special condition for NY state: */
712 -- IF p_state = 'NY' AND to_char(ld_Quater_end_date,'MM') = '12' THEN
713 -- ld_Month_start_date := trunc(ld_Month_end_date,'Y');
714 -- ELSE
715 ld_Month_start_date:=fnd_date.canonical_to_date(p_month);
716 -- END IF;
717
718
719 /* ld_Month_end_date := add_months(trunc(ld_Quater_start_date,'Q'),3)-1; */
720 lv_report_category :=p_format;
721 lv_year:=to_char(ld_Month_start_date,'YYYY');
722 lv_reporting_month:=to_char(ld_Month_end_date,'MMYYYY');
723
724 SELECT pay_payroll_actions_s.nextval
725 INTO ln_locking_action_id
726 FROM dual;
727
728 /*g_payroll_action_id:=ln_locking_action_id;*/
729
730 OPEN c_get_format_type(p_state,p_format);
731 FETCH c_get_format_type into lv_format_meaning;
732 CLOSE c_get_format_type;
733
734 hr_utility.trace('p_tax_unit_id: '||p_tax_unit_id);
735 hr_utility.trace('p_effective_date: '||p_effective_date);
736 hr_utility.trace('p_report_option: '||p_report_option);
737
738 hr_utility.trace('inserting payroll action: '||ln_locking_action_id);
739 INSERT
740 INTO pay_payroll_actions
741 (payroll_action_id
742 ,action_type
743 ,business_group_id
744 ,consolidation_set_id
745 ,payroll_id
746 ,action_population_status
747 ,action_status
748 ,effective_date
749 ,comments
750 ,object_version_number
751 ,pay_advice_message
752 ,report_type
753 ,report_qualifier
754 ,request_id
755 ,report_category
756 ,start_date)
757 VALUES
758 (ln_locking_action_id
759 ,'X'
760 ,p_business_group_id
761 ,NULL
762 ,NULL
763 ,'C'
764 ,'C'
765 ,ld_month_end_date
766 ,NULL
767 ,1
768 ,NULL
769 ,'SMWL_PARENT'
770 ,p_state
771 ,fnd_profile.value ('CONC_REQUEST_ID')
772 ,p_format
773 ,ld_month_start_date);
774
775
776
777
778 /* getting State Code */
779 IF p_state is NOT NULL THEN
780 SELECT state_code INTO lv_state_code
781 FROM pay_us_states
782 WHERE STATE_ABBREV = p_state;
783 END IF;
784
785 IF p_report_option <> 'SQWL_OLD' THEN --change with lookup code
786 FOR c_get_all_gres_rec IN c_get_all_gres LOOP
787
788 SELECT count(1) into l_previous_smwl_run_check from pay_payroll_actions
789 WHERE report_type = 'SMWL'
790 AND report_qualifier = p_state
791 AND business_group_id = p_business_group_id
792 AND effective_date = fnd_date.canonical_to_date (p_effective_date)
793 AND pay_core_utils.get_parameter('TRANSFER_GRE',legislative_parameters) = c_get_all_gres_rec.organization_id;
794
795 hr_utility.trace('l_previous_smwl_run_check: '||l_previous_smwl_run_check);
796
797 select count(paa.assignment_action_id)
798 into l_chk_gre_assign_actions
799 from pay_assignment_actions paa,
800 pay_payroll_actions pact,
801 pay_payrolls_f ppf,
802 pay_us_emp_fed_tax_rules_f peft
803 where pact.effective_date
804 between ld_Month_start_date
805 and ld_Month_end_date
806 and pact.payroll_action_id=paa.payroll_action_id
807 and pact.action_type in ('R', 'Q', 'I','B')
808 and paa.action_status = 'C'
809 and pact.action_status = 'C'
810 and paa.tax_unit_id = c_get_all_gres_rec.organization_id
811 and ppf.payroll_id = pact.payroll_id
812 and ppf.business_group_id =p_business_group_id
813 and peft.ASSIGNMENT_ID = paa.ASSIGNMENT_ID
814 and pact.EFFECTIVE_DATE between
815 peft.EFFECTIVE_START_DATE and peft.EFFECTIVE_END_DATE
816 and peft.SUI_STATE_CODE = lv_state_code;
817 -- BUG 12391841
818 hr_utility.trace('l_chk_gre_assign_actions: '||l_chk_gre_assign_actions);
819
820 IF l_previous_smwl_run_check = 0 and l_chk_gre_assign_actions > 0 THEN
821
822 SELECT pay_us_reporting_utils_pkg.get_file_name(c_get_all_gres_rec.organization_id,
823 'SQWL',
824 p_state,
825 to_char(ld_Month_end_date,'YYYY/MM/DD') ||' 00:00:00',
826 substr(lv_format_meaning,1,1) )
827 INTO l_file_name
828 FROM dual;
829
830 hr_utility.trace('c_get_all_gres_rec.organization_id : '||c_get_all_gres_rec.organization_id);
831
832
833 hr_utility.trace(' calling conc request :');
834 hr_utility.trace(' final p_state :'||p_state);
835 hr_utility.trace(' final lv_year :'||lv_year);
836 hr_utility.trace(' final p_business_group_id :'||p_business_group_id);
837 hr_utility.trace(' final lv_report_category :'||lv_report_category);
838 hr_utility.trace(' final c_get_all_gres_rec.organization_id :'||c_get_all_gres_rec.organization_id);
839 BEGIN
840 l_req_id := fnd_request.submit_request(application => 'PAY',
841 program => 'SQWLARCH',
842 argument1 => 'ARCHIVE',
843 argument2 => 'SMWL' ,
844 argument3 => p_state ,
845 argument4 => to_char(ld_Month_start_date,'YYYY/MM/DD') || ' 00:00:00' ,
846 argument5 => to_char(ld_Month_end_date,'YYYY/MM/DD') || ' 00:00:00',
847 argument6 => lv_report_category,
848 argument7 => p_business_group_id,
849 argument8 => l_file_name,
850 argument9 => l_file_name,
851 argument10 => p_action_parameter_group,
852 argument11 => 'TRANSFER_STATE='||p_state,
853 argument12 => 'TRANSFER_REPORTING_YEAR=' || lv_year,
854 argument13 => 'TRANSFER_REPORTING_QUARTER=' || lv_reporting_month,
855 argument14 => p_business_group_id,
856 argument15 => 'TRANSFER_TRANS_LEGAL_CO_ID=' || p_transmitter_gre,
857 argument16 => 'TRANSFER_DATE=' || to_char(ld_Month_end_date,'DD-MON-YYYY'),
858 argument17 => 'TRANSFER_REPORT_CATEGORY='|| lv_report_category ,
859 argument18 => 1,
860 argument19 =>'Y',
861 argument20 => 'TRNS_AUDIT=Y',
862 argument21 => p_session_date, --session_date
863 argument22 => P_Establishment_Flag,
864 argument23 => p_Establishment_Hierarchy,
865 argument24 => 'TRANSFER_HIERARCHY_ID='||p_Establishment_Hierarchy,
866 argument25 => p_Hierarchy_Version,
867 argument26 => 'TRANSFER_HIERARCHY_VERSION='||p_Hierarchy_Version,
868 argument27 => c_get_all_gres_rec.organization_id,
869 argument28 => 'TRANSFER_GRE='||c_get_all_gres_rec.organization_id,
870 argument29 => 'TRANSFER_PARENT_PAYROLL_ACTION=' ||ln_locking_action_id,
871 argument30 => 'TRANSFER_FOLD_OUTPUT=' || p_fold_output);
872
873 EXCEPTION
874
875 WHEN OTHERS THEN
876
877 HR_UTILITY.TRACE('SQLERRM = ' || SQLERRM);
878
879 END ;
880
881 commit;
882
883 hr_utility.trace('l_payroll_action_id : '||l_payroll_action_id);
884
885 END IF; /*l_previous_smwl_run_check*/
886 end loop;
887 ELSE
888
889 SELECT pay_us_reporting_utils_pkg.get_file_name( p_business_group_id,
890 'SQWL',
891 p_state,
892 to_char(ld_Month_end_date,'YYYY/MM/DD') ||' 00:00:00',
893 substr(lv_format_meaning,1,1) )
894 INTO l_file_name
895 FROM dual;
896
897
898 BEGIN
899 l_req_id := fnd_request.submit_request(application => 'PAY',
900 program => 'SQWLARCH',
901 argument1 => 'ARCHIVE',
902 argument2 => 'SMWL' ,
903 argument3 => p_state ,
904 argument4 => to_char(ld_Month_start_date,'YYYY/MM/DD') || ' 00:00:00' ,
905 argument5 => to_char(ld_Month_end_date,'YYYY/MM/DD') || ' 00:00:00',
906 argument6 => lv_report_category,
907 argument7 => p_business_group_id,
908 argument8 => l_file_name,
909 argument9 => l_file_name,
910 argument10 => p_action_parameter_group,
911 argument11=> 'TRANSFER_STATE='||p_state,
912 argument12 => 'TRANSFER_REPORTING_YEAR=' || lv_year,
913 argument13 => 'TRANSFER_REPORTING_QUARTER=' || lv_reporting_month,
914 argument14 => p_business_group_id,
915 argument15 => 'TRANSFER_TRANS_LEGAL_CO_ID=' || p_transmitter_gre,
916 argument16 => 'TRANSFER_DATE=' || to_char(ld_Month_end_date,'DD-MON-YYYY'),
917 argument17 => 'TRANSFER_REPORT_CATEGORY='|| lv_report_category ,
918 argument18 => 1,
919 argument19 =>'Y',
920 argument20 => 'TRNS_AUDIT=Y',
921 argument21 => p_session_date, --session_date
922 argument22 => P_Establishment_Flag,
923 argument23 => p_Establishment_Hierarchy,
924 argument24 => 'TRANSFER_HIERARCHY_ID='||p_Establishment_Hierarchy,
925 argument25 => p_Hierarchy_Version,
926 argument26 => 'TRANSFER_HIERARCHY_VERSION='||p_Hierarchy_Version,
927 argument27 => null, /*Passing gre as null*/
928 argument28 => 'TRANSFER_GRE=',
929 argument29 => 'TRANSFER_PARENT_PAYROLL_ACTION=' ||ln_locking_action_id,
930 argument30 => 'TRANSFER_FOLD_OUTPUT=' || p_fold_output);
931
932 EXCEPTION
933
934 WHEN OTHERS THEN
935
936 HR_UTILITY.TRACE('SQLERRM = ' || SQLERRM);
937
938 END ;
939 commit;
940 NULL;
941 END IF;
942
943 hr_utility.trace_off;
944 END; /* procedure process_smwl */
945
946
947 END pay_us_sqwl_gre;