[Home] [Help]
35: raw_data
36: );
37:
38: IF dbms_lob.ISOPEN(temp_blob)=1 THEN
39: hr_utility.trace('Closing temp_lob' );
40: dbms_lob.close(temp_blob);
41: END IF;
42:
43: return temp_blob;
47: RETURN VARCHAR2 IS
48: l_data VARCHAR2(250);
49: BEGIN
50: IF nvl(p_data,0) <=0 THEN
51: hr_utility.trace('Negative/zero value '||p_data);
52: l_data := '';
53: ELSE
54: l_data := p_data;
55: END IF;
84:
85: PROCEDURE check_prior_local IS
86: BEGIN
87: --{ Check for prior local
88: hr_utility.trace('In check_prior_local,l_prior_local '||l_prior_local);
89: p_write_state := FALSE;
90: IF l_prior_local IS NOT NULL THEN
91:
92: --hr_utility.trace('Statecode of state is LESS than Local state Code and prior local is not null');
88: hr_utility.trace('In check_prior_local,l_prior_local '||l_prior_local);
89: p_write_state := FALSE;
90: IF l_prior_local IS NOT NULL THEN
91:
92: --hr_utility.trace('Statecode of state is LESS than Local state Code and prior local is not null');
93: hr_utility.trace('State Code of current state '||l_state_tab(l_curr_state).state_code);
94: -- hr_utility.trace('State Code of current local '||l_local_tab(l_curr_local).state_code);
95: hr_utility.trace('State Code of prior local '||l_local_tab(l_prior_local).state_code);
96:
89: p_write_state := FALSE;
90: IF l_prior_local IS NOT NULL THEN
91:
92: --hr_utility.trace('Statecode of state is LESS than Local state Code and prior local is not null');
93: hr_utility.trace('State Code of current state '||l_state_tab(l_curr_state).state_code);
94: -- hr_utility.trace('State Code of current local '||l_local_tab(l_curr_local).state_code);
95: hr_utility.trace('State Code of prior local '||l_local_tab(l_prior_local).state_code);
96:
97: /* If the state code of prior local is same as current state
90: IF l_prior_local IS NOT NULL THEN
91:
92: --hr_utility.trace('Statecode of state is LESS than Local state Code and prior local is not null');
93: hr_utility.trace('State Code of current state '||l_state_tab(l_curr_state).state_code);
94: -- hr_utility.trace('State Code of current local '||l_local_tab(l_curr_local).state_code);
95: hr_utility.trace('State Code of prior local '||l_local_tab(l_prior_local).state_code);
96:
97: /* If the state code of prior local is same as current state
98: then move the index of the current state */
91:
92: --hr_utility.trace('Statecode of state is LESS than Local state Code and prior local is not null');
93: hr_utility.trace('State Code of current state '||l_state_tab(l_curr_state).state_code);
94: -- hr_utility.trace('State Code of current local '||l_local_tab(l_curr_local).state_code);
95: hr_utility.trace('State Code of prior local '||l_local_tab(l_prior_local).state_code);
96:
97: /* If the state code of prior local is same as current state
98: then move the index of the current state */
99: IF (l_state_tab(l_curr_state).state_code <>
97: /* If the state code of prior local is same as current state
98: then move the index of the current state */
99: IF (l_state_tab(l_curr_state).state_code <>
100: l_local_tab(l_prior_local).state_code) THEN
101: hr_utility.trace('State Code of prior local matches current state code so setting p_write_state TRUE');
102: p_write_state := TRUE;
103: ELSE
104: p_write_state := FALSE;
105: END IF;
111:
112: PROCEDURE write_state_only IS
113: BEGIN
114:
115: hr_utility.trace('Writing state without local ');
116:
117: l_stloc_tcnt := l_state_local_tab.count;
118: l_state_local_tab(l_stloc_tcnt).state_code
119: := l_state_tab(l_curr_state).state_code ;
136: l_stloc_tcnt := 0;
137:
138: LOOP
139:
140: hr_utility.trace('l_state_tab.COUNT '||l_state_tab.COUNT);
141: hr_utility.trace('l_local_tab.COUNT '||l_local_tab.COUNT);
142: hr_utility.trace('l_curr_state '||l_curr_state);
143: hr_utility.trace('l_curr_local '||l_curr_local);
144:
137:
138: LOOP
139:
140: hr_utility.trace('l_state_tab.COUNT '||l_state_tab.COUNT);
141: hr_utility.trace('l_local_tab.COUNT '||l_local_tab.COUNT);
142: hr_utility.trace('l_curr_state '||l_curr_state);
143: hr_utility.trace('l_curr_local '||l_curr_local);
144:
145: EXIT WHEN (l_curr_state > l_state_tab.COUNT and
138: LOOP
139:
140: hr_utility.trace('l_state_tab.COUNT '||l_state_tab.COUNT);
141: hr_utility.trace('l_local_tab.COUNT '||l_local_tab.COUNT);
142: hr_utility.trace('l_curr_state '||l_curr_state);
143: hr_utility.trace('l_curr_local '||l_curr_local);
144:
145: EXIT WHEN (l_curr_state > l_state_tab.COUNT and
146: l_curr_local > l_local_tab.COUNT)
139:
140: hr_utility.trace('l_state_tab.COUNT '||l_state_tab.COUNT);
141: hr_utility.trace('l_local_tab.COUNT '||l_local_tab.COUNT);
142: hr_utility.trace('l_curr_state '||l_curr_state);
143: hr_utility.trace('l_curr_local '||l_curr_local);
144:
145: EXIT WHEN (l_curr_state > l_state_tab.COUNT and
146: l_curr_local > l_local_tab.COUNT)
147: OR (l_curr_state > l_state_tab.COUNT and
147: OR (l_curr_state > l_state_tab.COUNT and
148: l_curr_local IS NULL);
149:
150: l_prior_local := l_local_tab.PRIOR(l_curr_local);
151: hr_utility.trace('l_prior_local '||l_local_tab.PRIOR(l_curr_local));
152:
153: IF (l_curr_state IS NOT NULL AND
154: l_curr_local IS NOT NULL ) AND
155: (l_curr_state <= l_state_tab.COUNT ) AND
154: l_curr_local IS NOT NULL ) AND
155: (l_curr_state <= l_state_tab.COUNT ) AND
156: (l_curr_local <= l_local_tab.COUNT) THEN
157:
158: hr_utility.trace('l_state_tab(l_curr_state).state_code '||l_state_tab(l_curr_state).state_code);
159: hr_utility.trace('l_local_tab(l_curr_local).state_code '||l_local_tab(l_curr_local).state_code);
160: hr_utility.trace('l_curr_state '||l_curr_state);
161:
162: IF (l_state_tab(l_curr_state).state_code =
155: (l_curr_state <= l_state_tab.COUNT ) AND
156: (l_curr_local <= l_local_tab.COUNT) THEN
157:
158: hr_utility.trace('l_state_tab(l_curr_state).state_code '||l_state_tab(l_curr_state).state_code);
159: hr_utility.trace('l_local_tab(l_curr_local).state_code '||l_local_tab(l_curr_local).state_code);
160: hr_utility.trace('l_curr_state '||l_curr_state);
161:
162: IF (l_state_tab(l_curr_state).state_code =
163: l_local_tab(l_curr_local).state_code ) THEN
156: (l_curr_local <= l_local_tab.COUNT) THEN
157:
158: hr_utility.trace('l_state_tab(l_curr_state).state_code '||l_state_tab(l_curr_state).state_code);
159: hr_utility.trace('l_local_tab(l_curr_local).state_code '||l_local_tab(l_curr_local).state_code);
160: hr_utility.trace('l_curr_state '||l_curr_state);
161:
162: IF (l_state_tab(l_curr_state).state_code =
163: l_local_tab(l_curr_local).state_code ) THEN
164:
161:
162: IF (l_state_tab(l_curr_state).state_code =
163: l_local_tab(l_curr_local).state_code ) THEN
164:
165: hr_utility.trace('Statecode of state is EQUAL to Local state Code');
166:
167: l_stloc_tcnt := l_state_local_tab.count;
168:
169: hr_utility.trace('l_state_tab(l_curr_state).state_ein '||l_state_tab(l_curr_state).state_ein);
165: hr_utility.trace('Statecode of state is EQUAL to Local state Code');
166:
167: l_stloc_tcnt := l_state_local_tab.count;
168:
169: hr_utility.trace('l_state_tab(l_curr_state).state_ein '||l_state_tab(l_curr_state).state_ein);
170: hr_utility.trace('l_state_tab(l_curr_state).state_wages '||l_state_tab(l_curr_state).state_wages);
171: hr_utility.trace('l_state_tab(l_curr_state).state_tax '||l_state_tab(l_curr_state).state_tax);
172: /* Check to see if the state code of prior local is same as current state */
173:
166:
167: l_stloc_tcnt := l_state_local_tab.count;
168:
169: hr_utility.trace('l_state_tab(l_curr_state).state_ein '||l_state_tab(l_curr_state).state_ein);
170: hr_utility.trace('l_state_tab(l_curr_state).state_wages '||l_state_tab(l_curr_state).state_wages);
171: hr_utility.trace('l_state_tab(l_curr_state).state_tax '||l_state_tab(l_curr_state).state_tax);
172: /* Check to see if the state code of prior local is same as current state */
173:
174: check_prior_local;
167: l_stloc_tcnt := l_state_local_tab.count;
168:
169: hr_utility.trace('l_state_tab(l_curr_state).state_ein '||l_state_tab(l_curr_state).state_ein);
170: hr_utility.trace('l_state_tab(l_curr_state).state_wages '||l_state_tab(l_curr_state).state_wages);
171: hr_utility.trace('l_state_tab(l_curr_state).state_tax '||l_state_tab(l_curr_state).state_tax);
172: /* Check to see if the state code of prior local is same as current state */
173:
174: check_prior_local;
175:
227: ELSIF (l_state_tab(l_curr_state).state_code <
228: l_local_tab(l_curr_local).state_code ) THEN
229: check_prior_local;
230: IF p_write_state THEN
231: hr_utility.trace('current state doesnot match with prior state, so write current state only');
232: write_state_only;
233: ELSE
234: hr_utility.trace('current state matches with prior state, move to next state');
235: l_curr_state := l_curr_state + 1;
230: IF p_write_state THEN
231: hr_utility.trace('current state doesnot match with prior state, so write current state only');
232: write_state_only;
233: ELSE
234: hr_utility.trace('current state matches with prior state, move to next state');
235: l_curr_state := l_curr_state + 1;
236: END IF;
237: --}
238: ELSE
235: l_curr_state := l_curr_state + 1;
236: END IF;
237: --}
238: ELSE
239: hr_utility.trace('Statecode of state is greater than Local state Code');
240: l_stloc_tcnt := l_state_local_tab.count;
241:
242: l_state_local_tab(l_stloc_tcnt).state_code
243: := l_local_tab(l_curr_local).state_code ;
267: ELSIF (l_curr_state IS NULL and l_curr_local IS NOT NULL)
268: OR (l_curr_state > l_state_tab.COUNT AND
269: l_curr_local <= l_local_tab.COUNT) THEN
270:
271: hr_utility.trace('Current state is null and curr local is NOT null');
272: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
273: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality);
274: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax);
275: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages);
268: OR (l_curr_state > l_state_tab.COUNT AND
269: l_curr_local <= l_local_tab.COUNT) THEN
270:
271: hr_utility.trace('Current state is null and curr local is NOT null');
272: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
273: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality);
274: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax);
275: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages);
276:
269: l_curr_local <= l_local_tab.COUNT) THEN
270:
271: hr_utility.trace('Current state is null and curr local is NOT null');
272: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
273: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality);
274: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax);
275: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages);
276:
277:
270:
271: hr_utility.trace('Current state is null and curr local is NOT null');
272: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
273: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality);
274: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax);
275: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages);
276:
277:
278: l_stloc_tcnt := l_state_local_tab.count;
271: hr_utility.trace('Current state is null and curr local is NOT null');
272: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
273: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality);
274: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax);
275: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages);
276:
277:
278: l_stloc_tcnt := l_state_local_tab.count;
279: l_state_local_tab(l_stloc_tcnt).state_code
302: ELSIF (l_curr_state IS NOT NULL and l_curr_local IS NULL)
303: OR (l_curr_state <= l_state_tab.COUNT AND
304: l_curr_local > l_local_tab.COUNT) THEN
305:
306: hr_utility.trace('Current state is not null and curr local is null');
307: hr_utility.trace('l_curr_state '||l_curr_state);
308: hr_utility.trace('l_curr_local '||l_curr_local);
309:
310: check_prior_local;
303: OR (l_curr_state <= l_state_tab.COUNT AND
304: l_curr_local > l_local_tab.COUNT) THEN
305:
306: hr_utility.trace('Current state is not null and curr local is null');
307: hr_utility.trace('l_curr_state '||l_curr_state);
308: hr_utility.trace('l_curr_local '||l_curr_local);
309:
310: check_prior_local;
311: IF p_write_state THEN
304: l_curr_local > l_local_tab.COUNT) THEN
305:
306: hr_utility.trace('Current state is not null and curr local is null');
307: hr_utility.trace('l_curr_state '||l_curr_state);
308: hr_utility.trace('l_curr_local '||l_curr_local);
309:
310: check_prior_local;
311: IF p_write_state THEN
312: hr_utility.trace('current state doesnot match with prior state, so write current state only');
308: hr_utility.trace('l_curr_local '||l_curr_local);
309:
310: check_prior_local;
311: IF p_write_state THEN
312: hr_utility.trace('current state doesnot match with prior state, so write current state only');
313: write_state_only;
314: ELSE
315: hr_utility.trace('current state matches with prior state, move to next state');
316: l_curr_state := l_curr_state + 1;
311: IF p_write_state THEN
312: hr_utility.trace('current state doesnot match with prior state, so write current state only');
313: write_state_only;
314: ELSE
315: hr_utility.trace('current state matches with prior state, move to next state');
316: l_curr_state := l_curr_state + 1;
317:
318: END IF;
319: ELSE
316: l_curr_state := l_curr_state + 1;
317:
318: END IF;
319: ELSE
320: hr_utility.trace('Completed populating all states and locals');
321: exit;
322: END IF;
323: END LOOP;
324:
537: pay_us_archive_util.get_archive_value(p_asg_action_id,
538: 'A_ARCHIVE_DATE',
539: p_tax_unit_id));
540:
541: hr_utility.trace('Archive Date : ' || l_corrected_date);
542: /* l_profile_option := fnd_profile.value('HR_VIEW_ONLINE_W2');
543: IF (l_profile_option is null) or (l_profile_option = '') THEN
544: l_profile_date := fnd_date.canonical_to_date('4712/12/31');
545: ELSE
556: profile option date else continue using the old logic of appending year,
557:
558: */
559: l_live_profile_option := fnd_profile.value('HR_VIEW_ONLINE_W2');
560: hr_utility.trace('View Online W2 Profile date'||l_live_profile_option);
561:
562: IF (l_live_profile_option is null) or (l_live_profile_option = '') THEN
563: l_profile_date := fnd_date.canonical_to_date('4712/12/31');
564: ELSE
568: pay_us_archive_util.get_archive_value(l_payroll_action_id,
569: 'A_VIEW_ONLINE_W2',
570: p_tax_unit_id);
571:
572: hr_utility.trace('l_profile_date '||l_profile_date_string);
573:
574: IF (l_profile_date_string is null) or (l_profile_date_string = '') THEN
575: l_profile_date := fnd_date.canonical_to_date(p_year+1||'/'||l_live_profile_option);
576: hr_utility.trace('l_profile_date was null , setting to '||l_profile_date);
572: hr_utility.trace('l_profile_date '||l_profile_date_string);
573:
574: IF (l_profile_date_string is null) or (l_profile_date_string = '') THEN
575: l_profile_date := fnd_date.canonical_to_date(p_year+1||'/'||l_live_profile_option);
576: hr_utility.trace('l_profile_date was null , setting to '||l_profile_date);
577: ELSE
578: l_profile_date := -- bug 5656018 fnd_date.chardate_to_date
579: fnd_date.canonical_to_date(l_profile_date_string);
580:
577: ELSE
578: l_profile_date := -- bug 5656018 fnd_date.chardate_to_date
579: fnd_date.canonical_to_date(l_profile_date_string);
580:
581: hr_utility.trace('l_profile_date was not null , setting to '||l_profile_date);
582:
583: END IF;
584: END IF;
585:
586: l_w2_corrected :=pay_us_archive_util.get_archive_value(p_asg_action_id,
587: 'A_W2_CORRECTED',
588: p_tax_unit_id);
589:
590: hr_utility.trace('View Online W2 Profile date'||l_live_profile_option);
591:
592: /* If the profile option is blank for fixing bug 4947964 and archive
593: item , A_W2_CORRECTED is not archived for an employee then it
594: will never print 'CORRECTED' which may be incorrect for W-2s which
619: FETCH c_get_business_group_id
620: INTO l_business_group_id;
621: CLOSE c_get_business_group_id;
622:
623: hr_utility.trace('Business Group id ' ||l_business_group_id);
624:
625: IF l_business_group_id is not null THEN
626: hr_us_w2_rep.get_agent_tax_unit_id(l_business_group_id
627: ,p_year
628: ,l_agent_tax_unit_id
629: ,l_error_msg);
630: END IF;
631:
632: hr_utility.trace('Agent Tax unit id ' ||l_agent_tax_unit_id);
633: hr_utility.trace('l_error_msg ' ||l_error_msg);
634: /* If l_erro_msg is not null then throw error else get remaining data for W2 */
635: IF l_error_msg IS NOT NULL THEN
636: p_error_msg := l_error_msg;
629: ,l_error_msg);
630: END IF;
631:
632: hr_utility.trace('Agent Tax unit id ' ||l_agent_tax_unit_id);
633: hr_utility.trace('l_error_msg ' ||l_error_msg);
634: /* If l_erro_msg is not null then throw error else get remaining data for W2 */
635: IF l_error_msg IS NOT NULL THEN
636: p_error_msg := l_error_msg;
637: ELSE
642: l_w2_fields.employer_name,
643: l_w2_fields.employer_address;
644: CLOSE c_get_employer_info;
645:
646: hr_utility.trace('l_w2_fields.federal_ein ' ||l_w2_fields.federal_ein);
647: hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
648: hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
649:
650: IF l_agent_tax_unit_id IS NOT NULL THEN
643: l_w2_fields.employer_address;
644: CLOSE c_get_employer_info;
645:
646: hr_utility.trace('l_w2_fields.federal_ein ' ||l_w2_fields.federal_ein);
647: hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
648: hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
649:
650: IF l_agent_tax_unit_id IS NOT NULL THEN
651:
644: CLOSE c_get_employer_info;
645:
646: hr_utility.trace('l_w2_fields.federal_ein ' ||l_w2_fields.federal_ein);
647: hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
648: hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
649:
650: IF l_agent_tax_unit_id IS NOT NULL THEN
651:
652: hr_utility.trace('p_tax_unit_id ' ||p_tax_unit_id);
648: hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
649:
650: IF l_agent_tax_unit_id IS NOT NULL THEN
651:
652: hr_utility.trace('p_tax_unit_id ' ||p_tax_unit_id);
653:
654: OPEN c_get_employer_info(p_tax_unit_id,p_year);
655: FETCH c_get_employer_info
656: INTO l_org_federal_ein,
656: INTO l_org_federal_ein,
657: l_org_employer_name,
658: l_org_address;
659: CLOSE c_get_employer_info;
660: hr_utility.trace('l_org_federal_ein ' ||l_org_federal_ein);
661: hr_utility.trace('l_org_employer_name ' ||l_org_employer_name);
662: hr_utility.trace('l_org_address ' ||l_org_address);
663:
664: l_w2_fields.employer_address
657: l_org_employer_name,
658: l_org_address;
659: CLOSE c_get_employer_info;
660: hr_utility.trace('l_org_federal_ein ' ||l_org_federal_ein);
661: hr_utility.trace('l_org_employer_name ' ||l_org_employer_name);
662: hr_utility.trace('l_org_address ' ||l_org_address);
663:
664: l_w2_fields.employer_address
665: := 'Agent For ' ||substr(l_org_employer_name,1,44)||
658: l_org_address;
659: CLOSE c_get_employer_info;
660: hr_utility.trace('l_org_federal_ein ' ||l_org_federal_ein);
661: hr_utility.trace('l_org_employer_name ' ||l_org_employer_name);
662: hr_utility.trace('l_org_address ' ||l_org_address);
663:
664: l_w2_fields.employer_address
665: := 'Agent For ' ||substr(l_org_employer_name,1,44)||
666: pay_us_w2_info_pkg.EOL ||
666: pay_us_w2_info_pkg.EOL ||
667: l_w2_fields.employer_address;
668: END IF;
669: /* Bug 5575567 */
670: hr_utility.trace(' sysdate ' || sysdate);
671: hr_utility.trace(' end of year ' || fnd_date.canonical_to_date(p_year||'/12/31'));
672:
673: IF (trunc(sysdate) <
674: fnd_date.canonical_to_date(p_year||'/12/31')) THEN
667: l_w2_fields.employer_address;
668: END IF;
669: /* Bug 5575567 */
670: hr_utility.trace(' sysdate ' || sysdate);
671: hr_utility.trace(' end of year ' || fnd_date.canonical_to_date(p_year||'/12/31'));
672:
673: IF (trunc(sysdate) <
674: fnd_date.canonical_to_date(p_year||'/12/31')) THEN
675: p_effective_date := fnd_date.canonical_to_date(p_year||'/12/31');
677: p_effective_date := sysdate;
678: END IF;
679:
680: --p_effective_date := sysdate; Bug 6443139
681: hr_utility.trace(' p_effective_date ' || p_effective_date);
682:
683: OPEN c_get_emp_info(p_asg_action_id,p_tax_unit_id, p_year) ;
684: FETCH c_get_emp_info
685: INTO l_w2_fields.control_number,
705: -- bug 5575567
706: l_person_id ,
707: p_assignment_id ;
708:
709: hr_utility.trace('EMP NAME ' ||l_w2_fields.emp_name);
710: hr_utility.trace('Control Number ' ||l_w2_fields.control_number);
711: IF c_get_emp_info%NOTFOUND THEN
712: hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_asg_action_id));
713: CLOSE c_get_emp_info;
706: l_person_id ,
707: p_assignment_id ;
708:
709: hr_utility.trace('EMP NAME ' ||l_w2_fields.emp_name);
710: hr_utility.trace('Control Number ' ||l_w2_fields.control_number);
711: IF c_get_emp_info%NOTFOUND THEN
712: hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_asg_action_id));
713: CLOSE c_get_emp_info;
714: raise NO_DATA_FOUND;
708:
709: hr_utility.trace('EMP NAME ' ||l_w2_fields.emp_name);
710: hr_utility.trace('Control Number ' ||l_w2_fields.control_number);
711: IF c_get_emp_info%NOTFOUND THEN
712: hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_asg_action_id));
713: CLOSE c_get_emp_info;
714: raise NO_DATA_FOUND;
715: END IF;
716: CLOSE c_get_emp_info;
783: --bug 6874650
784: -- ,nvl(l_w2_fields.non_qual_plan,0)) <= 0 THEN
785: ,nvl(l_w2_fields.non_qual_plan,0)) < 0 THEN
786: --bug 6874650
787: hr_utility.trace('Negative values for box1-11 ');
788: p_error_msg := 'Negative values for box1-11';
789: END IF;
790: --changes for bug 6821345 starts here
791: -- IF nvl(l_w2_fields.wages_tips_compensation,0) <=0 THEN
790: --changes for bug 6821345 starts here
791: -- IF nvl(l_w2_fields.wages_tips_compensation,0) <=0 THEN
792: IF nvl(l_w2_fields.wages_tips_compensation,0) <0 THEN
793: --changes for bug 6821345 ends here
794: hr_utility.trace('Negative/zero value for box1 ');
795: p_error_msg := 'Negative values for box1';
796: END IF;
797:
798: l_sl_total_count := 0;
797:
798: l_sl_total_count := 0;
799: OPEN c_get_state_info(p_asg_action_id) ;
800: LOOP
801: hr_utility.trace('In state loop ' );
802: l_sl_total_count := l_sl_total_count + 1;
803: FETCH c_get_state_info
804: INTO l_state_tab(l_sl_total_count).state_code,
805: l_state_tab(l_sl_total_count).state_ein,
805: l_state_tab(l_sl_total_count).state_ein,
806: l_state_tab(l_sl_total_count).state_wages,
807: l_state_tab(l_sl_total_count).state_tax;
808: EXIT WHEN c_get_state_info%NOTFOUND;
809: hr_utility.trace('State_code '|| l_state_tab(l_sl_total_count).state_code);
810: hr_utility.trace('State_EIN '|| l_state_tab(l_sl_total_count).state_ein);
811:
812: IF l_state_tab(l_sl_total_count).state_code = 'NJ' THEN
813: l_nj_state_printed := 'Y';
806: l_state_tab(l_sl_total_count).state_wages,
807: l_state_tab(l_sl_total_count).state_tax;
808: EXIT WHEN c_get_state_info%NOTFOUND;
809: hr_utility.trace('State_code '|| l_state_tab(l_sl_total_count).state_code);
810: hr_utility.trace('State_EIN '|| l_state_tab(l_sl_total_count).state_ein);
811:
812: IF l_state_tab(l_sl_total_count).state_code = 'NJ' THEN
813: l_nj_state_printed := 'Y';
814: END IF;
822:
823: l_local_total_count := 0;
824: OPEN c_get_local_info(p_asg_action_id) ;
825: LOOP
826: hr_utility.trace('In local loop ' );
827: FETCH c_get_local_info
828: INTO l_locality,
829: l_locality_wages,
830: l_locality_tax,
831: l_jurisdiction,
832: l_state_code,
833: l_tax_type;
834:
835: hr_utility.trace('l_locality is '||l_locality);
836:
837: EXIT WHEN c_get_local_info%NOTFOUND;
838: IF l_locality_tax > 0 THEN
839: -- l_local_total_count := l_local_total_count + 1;
839: -- l_local_total_count := l_local_total_count + 1;
840: /* populate the locality table only if the jurisdiction code <> 18-015-000
841: as this needs to be reported in box 14 as occupational and mental health tax*/
842: IF ( l_tax_type = 'COUNTY' and l_jurisdiction = '18-015-0000') THEN
843: hr_utility.trace('Jurisdiction is 18-015-0000 and tax_type is County');
844: IF (l_locality_tax > 0 and l_locality_wages > 0) THEN
845: hr_utility.trace('Locality tax withheld > 0 for KY, Boone county');
846:
847: l_b14_total_count := l_box14_tab.count+1;
841: as this needs to be reported in box 14 as occupational and mental health tax*/
842: IF ( l_tax_type = 'COUNTY' and l_jurisdiction = '18-015-0000') THEN
843: hr_utility.trace('Jurisdiction is 18-015-0000 and tax_type is County');
844: IF (l_locality_tax > 0 and l_locality_wages > 0) THEN
845: hr_utility.trace('Locality tax withheld > 0 for KY, Boone county');
846:
847: l_b14_total_count := l_box14_tab.count+1;
848: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
849:
844: IF (l_locality_tax > 0 and l_locality_wages > 0) THEN
845: hr_utility.trace('Locality tax withheld > 0 for KY, Boone county');
846:
847: l_b14_total_count := l_box14_tab.count+1;
848: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
849:
850: IF (g_mh_tax_rate IS NULL OR g_mh_tax_limit IS NULL
851: OR g_occ_tax_rate IS NULL OR g_occ_mh_tax_limit IS NULL
852: OR g_occ_mh_wage_limit IS NULL OR g_mh_tax_wage_limit IS NULL )
850: IF (g_mh_tax_rate IS NULL OR g_mh_tax_limit IS NULL
851: OR g_occ_tax_rate IS NULL OR g_occ_mh_tax_limit IS NULL
852: OR g_occ_mh_wage_limit IS NULL OR g_mh_tax_wage_limit IS NULL )
853: THEN
854: hr_utility.trace('Getting Mental health and Occupational tax limits');
855: hr_us_w2_rep.get_county_tax_info('18-015-0000',
856: p_year,
857: g_occ_tax_rate,
858: g_mh_tax_rate,
869: END IF;
870:
871: l_box14_tab(l_b14_total_count).box14_code := 'BOONMH';
872: l_box14_tab(l_b14_total_count).box14_meaning := l_box14_boonmh_value;
873: hr_utility.trace('l_box14_tab(l_b14_total_count).box14_meaning '||l_box14_tab(l_b14_total_count).box14_meaning);
874:
875: l_b14_total_count := l_box14_tab.count+1;
876: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
877:
872: l_box14_tab(l_b14_total_count).box14_meaning := l_box14_boonmh_value;
873: hr_utility.trace('l_box14_tab(l_b14_total_count).box14_meaning '||l_box14_tab(l_b14_total_count).box14_meaning);
874:
875: l_b14_total_count := l_box14_tab.count+1;
876: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
877:
878: l_box14_tab(l_b14_total_count).box14_code := 'BOONOCC';
879: l_box14_tab(l_b14_total_count).box14_meaning :=
880: l_locality_tax - l_box14_boonmh_value;
887: l_local_tab(l_local_total_count).jurisdiction := l_jurisdiction;
888: l_local_tab(l_local_total_count).state_code := l_state_code;
889: l_local_tab(l_local_total_count).tax_type := l_tax_type;
890:
891: hr_utility.trace('Locality_code '|| l_local_tab(l_local_total_count).locality);
892: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code);
893:
894: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction);
895: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax);
888: l_local_tab(l_local_total_count).state_code := l_state_code;
889: l_local_tab(l_local_total_count).tax_type := l_tax_type;
890:
891: hr_utility.trace('Locality_code '|| l_local_tab(l_local_total_count).locality);
892: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code);
893:
894: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction);
895: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax);
896: hr_utility.trace('Locality Tax Type '|| l_local_tab(l_local_total_count).tax_type);
890:
891: hr_utility.trace('Locality_code '|| l_local_tab(l_local_total_count).locality);
892: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code);
893:
894: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction);
895: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax);
896: hr_utility.trace('Locality Tax Type '|| l_local_tab(l_local_total_count).tax_type);
897:
898:
891: hr_utility.trace('Locality_code '|| l_local_tab(l_local_total_count).locality);
892: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code);
893:
894: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction);
895: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax);
896: hr_utility.trace('Locality Tax Type '|| l_local_tab(l_local_total_count).tax_type);
897:
898:
899: IF (nvl(l_local_tab(l_local_total_count).locality_tax,0) > 0) THEN
892: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code);
893:
894: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction);
895: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax);
896: hr_utility.trace('Locality Tax Type '|| l_local_tab(l_local_total_count).tax_type);
897:
898:
899: IF (nvl(l_local_tab(l_local_total_count).locality_tax,0) > 0) THEN
900: IF (l_local_tab(l_local_total_count).tax_type = 'CITY SCHOOL' or
899: IF (nvl(l_local_tab(l_local_total_count).locality_tax,0) > 0) THEN
900: IF (l_local_tab(l_local_total_count).tax_type = 'CITY SCHOOL' or
901: l_local_tab(l_local_total_count).tax_type = 'COUNTY SCHOOL' ) THEN
902:
903: hr_utility.trace('Locality Tax Type is County/city school');
904:
905: if l_local_tab(l_local_total_count).state_code = 'OH' then
906:
907: hr_utility.trace('Locality state code is OH');
903: hr_utility.trace('Locality Tax Type is County/city school');
904:
905: if l_local_tab(l_local_total_count).state_code = 'OH' then
906:
907: hr_utility.trace('Locality state code is OH');
908:
909: l_local_tab(l_local_total_count).locality
910: := substr(l_local_tab(l_local_total_count).jurisdiction,5,4)
911: ||' '||substr(l_local_tab(l_local_total_count).locality,1,8);
909: l_local_tab(l_local_total_count).locality
910: := substr(l_local_tab(l_local_total_count).jurisdiction,5,4)
911: ||' '||substr(l_local_tab(l_local_total_count).locality,1,8);
912: elsif l_local_tab(l_local_total_count).state_code = 'KY' then
913: hr_utility.trace('Locality state code is KY');
914: l_local_tab(l_local_total_count).locality
915: := substr(l_local_tab(l_local_total_count).jurisdiction,7,2)
916: ||' '||substr(l_local_tab(l_local_total_count).locality,1,10);
917: else
914: l_local_tab(l_local_total_count).locality
915: := substr(l_local_tab(l_local_total_count).jurisdiction,7,2)
916: ||' '||substr(l_local_tab(l_local_total_count).locality,1,10);
917: else
918: hr_utility.trace('Locality state code neither OH nor KY');
919: l_local_tab(l_local_total_count).locality
920: := substr(l_local_tab(l_local_total_count).jurisdiction,4,5)
921: ||' '||substr(l_local_tab(l_local_total_count).locality,1,7);
922: end if;
922: end if;
923: END IF;
924: END IF;
925:
926: hr_utility.trace('l_local_tab(l_local_total_count).locality is '||l_local_tab(l_local_total_count).locality);
927: IF (l_local_tab(l_local_total_count).state_code = 'IN'
928: and l_local_tab(l_local_total_count).tax_type = 'COUNTY') THEN
929: BEGIN
930: select nvl(value,'N') into l_nr_flag
972: INTO l_box12_tab(l_b12_total_count).box12_code,
973: l_box12_tab(l_b12_total_count).box12_meaning;
974:
975: EXIT WHEN c_get_box12_info%NOTFOUND;
976: hr_utility.trace('In box12 loop '||l_box12_tab(l_b12_total_count).box12_code );
977: END LOOP;
978: CLOSE c_get_box12_info ;
979:
980: -- l_b14_total_count := l_box14_tab.count;
980: -- l_b14_total_count := l_box14_tab.count;
981: OPEN c_get_box14_info (p_asg_action_id) ;
982: LOOP
983: l_b14_total_count := l_box14_tab.count+1;
984: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
985:
986: FETCH c_get_box14_info
987: INTO l_box14_tab(l_b14_total_count).box14_code,
988: l_box14_tab(l_b14_total_count).box14_meaning;
987: INTO l_box14_tab(l_b14_total_count).box14_code,
988: l_box14_tab(l_b14_total_count).box14_meaning;
989:
990: EXIT WHEN c_get_box14_info%NOTFOUND;
991: hr_utility.trace('In box14 loop ' ||l_box14_tab(l_b14_total_count).box14_code);
992:
993: END LOOP;
994: CLOSE c_get_box14_info ;
995: /*
995: /*
996: OPEN c_get_box14_boonocc (p_asg_action_id) ;
997: LOOP
998: l_b14_total_count := l_box14_tab.count+1;
999: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1000:
1001: FETCH c_get_box14_boonocc
1002: INTO l_box14_tab(l_b14_total_count).box14_code,
1003: l_box14_tab(l_b14_total_count).box14_meaning;
1002: INTO l_box14_tab(l_b14_total_count).box14_code,
1003: l_box14_tab(l_b14_total_count).box14_meaning;
1004:
1005: EXIT WHEN c_get_box14_boonocc%NOTFOUND;
1006: hr_utility.trace('In c_get_box14_boonocc loop ' ||l_box14_tab(l_b14_total_count).box14_code);
1007:
1008: END LOOP;
1009: CLOSE c_get_box14_boonocc ;
1010:
1011:
1012: OPEN c_get_box14_boonmh (p_asg_action_id) ;
1013: LOOP
1014: l_b14_total_count := l_box14_tab.count+1;
1015: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1016:
1017: FETCH c_get_box14_boonmh
1018: INTO l_box14_tab(l_b14_total_count).box14_code,
1019: l_box14_tab(l_b14_total_count).box14_meaning;
1018: INTO l_box14_tab(l_b14_total_count).box14_code,
1019: l_box14_tab(l_b14_total_count).box14_meaning;
1020:
1021: EXIT WHEN c_get_box14_boonmh%NOTFOUND;
1022: hr_utility.trace('In c_get_box14_boonmh loop ' ||l_box14_tab(l_b14_total_count).box14_code);
1023:
1024: END LOOP;
1025: CLOSE c_get_box14_boonmh ;
1026: */
1047: l_nj_planid := pay_us_archive_util.get_archive_value(p_asg_action_id,
1048: 'A_SCL_ASG_US_NJ_PLAN_ID', --A_EXTRA_ASSIGNMENT_INFORMATION_PAY_US_DISABILITY_PLAN_INFO_DF_PLAN_ID'
1049: p_tax_unit_id) ;
1050: If l_nj_planid IS NOT NULL then
1051: hr_utility.trace('NJ DIPP plan id: ' || l_nj_planid);
1052: l_b14_total_count := l_box14_tab.count + 1;
1053: l_box14_tab(l_b14_total_count).box14_code := 'DI P.P. # '||l_nj_planid ;
1054: l_box14_tab(l_b14_total_count).box14_meaning:='';
1055: end if;
1083: is_temp varchar2(10);
1084: text_size NUMBER;
1085: raw_data RAW(32767);
1086: begin
1087: hr_utility.trace('In create XML string ' );
1088: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1089: IF (g_print_instr IS NULL) OR (g_print_instr = '') THEN
1090: g_print_instr := 'Y';
1091: END IF;
1151: ''||EOL;
1152:
1153:
1154:
1155: hr_utility.trace('one set XML string ' ||l_xml_string);
1156: is_temp := dbms_lob.istemporary(l_xml_blob);
1157: hr_utility.trace('Istemporary(l_xml_blob) ' ||is_temp );
1158:
1159: IF is_temp = 1 THEN
1153:
1154:
1155: hr_utility.trace('one set XML string ' ||l_xml_string);
1156: is_temp := dbms_lob.istemporary(l_xml_blob);
1157: hr_utility.trace('Istemporary(l_xml_blob) ' ||is_temp );
1158:
1159: IF is_temp = 1 THEN
1160: DBMS_LOB.FREETEMPORARY(l_xml_blob);
1161: END IF;
1161: END IF;
1162:
1163: dbms_lob.createtemporary(l_xml_blob,false,DBMS_LOB.CALL);
1164: dbms_lob.open(l_xml_blob,dbms_lob.lob_readwrite);
1165: hr_utility.trace('OPENED l_xml_blob ' );
1166:
1167: raw_data:=utl_raw.cast_to_raw(l_xml_string);
1168: text_size:=utl_raw.length(raw_data);
1169:
1168: text_size:=utl_raw.length(raw_data);
1169:
1170: dbms_lob.writeappend(l_xml_blob,text_size,raw_data);
1171:
1172: hr_utility.trace('Get Length l_xml_clob ' ||dbms_lob.getlength(l_xml_blob) );
1173: dbms_lob.close(l_xml_blob);
1174: return l_xml_blob;
1175: exception
1176: when OTHERS then
1174: return l_xml_blob;
1175: exception
1176: when OTHERS then
1177: dbms_lob.close(l_xml_blob);
1178: HR_UTILITY.TRACE('sqleerm ' || sqlerrm);
1179: HR_UTILITY.RAISE_ERROR;
1180:
1181: end create_xml_string;
1182: --} end create_xml_string
1175: exception
1176: when OTHERS then
1177: dbms_lob.close(l_xml_blob);
1178: HR_UTILITY.TRACE('sqleerm ' || sqlerrm);
1179: HR_UTILITY.RAISE_ERROR;
1180:
1181: end create_xml_string;
1182: --} end create_xml_string
1183:
1233: l_w2_set_cnt number;
1234: l_is_temp_xml_string VARCHAR2(2);
1235:
1236: begin
1237: hr_utility.trace('In Fetch w2 xml loop ' );
1238:
1239: l_b14_total_count := 0;
1240: l_b14_count_completed := 0;
1241: l_b12_total_count := 0;
1252: get_w2_data(p_assignment_action_id,p_tax_unit_id,p_year,p_error_msg);
1253:
1254: IF p_error_msg IS NULL THEN
1255:
1256: hr_utility.trace('After get W2 data' );
1257:
1258: l_b14_total_count := l_box14_tab.count;
1259: l_b14_count_completed := 0;
1260: l_b12_total_count := l_box12_tab.count;
1266: l_state_local_total_count := l_state_local_tab.count;
1267: l_state_local_count_completed := 0;
1268:
1269: l_is_temp_xml_string := dbms_lob.istemporary(l_xml_blob);
1270: hr_utility.trace('Istemporary(l_xml_blob) ' ||l_is_temp_xml_string );
1271:
1272: IF l_is_temp_xml_string = 1 THEN
1273: DBMS_LOB.FREETEMPORARY(l_xml_blob);
1274: END IF;
1280: (l_b12_total_count = 0) AND
1281: --(l_sl_total_count = 0)
1282: (l_state_local_total_count = 0) THEN
1283:
1284: hr_utility.trace('In l_b14_total_count and other counts =0 ' );
1285: -- dbms_lob.append(l_final_xml,p_xml_string);
1286: l_xml_blob := create_xml_string(l_w2_fields,
1287: l_box14_codea,l_box14_meaninga,
1288: l_box14_codeb,l_box14_meaningb,
1298: l_state2_code,l_state2_ein,
1299: l_state2_wages, l_state2_tax,
1300: l_local2_wages,l_local2_tax,
1301: l_locality2,p_year);
1302: hr_utility.trace('after getting XML Blob ' );
1303:
1304: ELSE
1305:
1306: --{
1305:
1306: --{
1307: LOOP
1308:
1309: hr_utility.trace('In loop to get XML ' );
1310: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1311: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
1312: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1313: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1306: --{
1307: LOOP
1308:
1309: hr_utility.trace('In loop to get XML ' );
1310: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1311: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
1312: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1313: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1314:
1307: LOOP
1308:
1309: hr_utility.trace('In loop to get XML ' );
1310: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1311: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
1312: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1313: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1314:
1315: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1308:
1309: hr_utility.trace('In loop to get XML ' );
1310: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1311: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
1312: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1313: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1314:
1315: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1316: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1309: hr_utility.trace('In loop to get XML ' );
1310: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1311: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
1312: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1313: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1314:
1315: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1316: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1317: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1311: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
1312: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1313: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1314:
1315: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1316: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1317: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1318: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1319: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1312: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1313: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1314:
1315: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1316: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1317: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1318: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1319: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1320: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1313: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1314:
1315: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1316: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1317: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1318: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1319: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1320: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1321: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
1314:
1315: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1316: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1317: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1318: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1319: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1320: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1321: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
1322:
1315: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1316: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1317: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1318: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1319: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1320: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1321: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
1322:
1323: EXIT WHEN
1316: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1317: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1318: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1319: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1320: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1321: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
1322:
1323: EXIT WHEN
1324: ((l_b14_total_count = 0) AND
1317: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1318: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1319: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1320: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1321: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
1322:
1323: EXIT WHEN
1324: ((l_b14_total_count = 0) AND
1325: (l_b12_total_count = 0 ) AND
1332: l_box14_codec := '';
1333: l_box14_meaningc := '';
1334:
1335: IF l_b14_total_count > 0 THEN
1336: hr_utility.trace('1. l_b14_total_count >0 ' ||l_b14_total_count);
1337: l_b14_count_completed := l_b14_count_completed + 1 ;
1338: hr_utility.trace('1. l_b14_count_completed ' ||l_b14_count_completed);
1339: l_box14_codea := l_box14_tab(l_b14_count_completed).box14_code;
1340: l_box14_meaninga := l_box14_tab(l_b14_count_completed).box14_meaning;
1334:
1335: IF l_b14_total_count > 0 THEN
1336: hr_utility.trace('1. l_b14_total_count >0 ' ||l_b14_total_count);
1337: l_b14_count_completed := l_b14_count_completed + 1 ;
1338: hr_utility.trace('1. l_b14_count_completed ' ||l_b14_count_completed);
1339: l_box14_codea := l_box14_tab(l_b14_count_completed).box14_code;
1340: l_box14_meaninga := l_box14_tab(l_b14_count_completed).box14_meaning;
1341: l_b14_total_count := l_b14_total_count -1;
1342: hr_utility.trace('l_box14_codea ' ||l_box14_codea);
1338: hr_utility.trace('1. l_b14_count_completed ' ||l_b14_count_completed);
1339: l_box14_codea := l_box14_tab(l_b14_count_completed).box14_code;
1340: l_box14_meaninga := l_box14_tab(l_b14_count_completed).box14_meaning;
1341: l_b14_total_count := l_b14_total_count -1;
1342: hr_utility.trace('l_box14_codea ' ||l_box14_codea);
1343: hr_utility.trace('l_box14_meaninga ' ||l_box14_meaninga);
1344:
1345: END IF;
1346:
1339: l_box14_codea := l_box14_tab(l_b14_count_completed).box14_code;
1340: l_box14_meaninga := l_box14_tab(l_b14_count_completed).box14_meaning;
1341: l_b14_total_count := l_b14_total_count -1;
1342: hr_utility.trace('l_box14_codea ' ||l_box14_codea);
1343: hr_utility.trace('l_box14_meaninga ' ||l_box14_meaninga);
1344:
1345: END IF;
1346:
1347: IF l_b14_total_count > 0 THEN
1344:
1345: END IF;
1346:
1347: IF l_b14_total_count > 0 THEN
1348: hr_utility.trace('2. l_b14_total_count >0 ' ||l_b14_total_count);
1349: l_b14_count_completed := l_b14_count_completed + 1 ;
1350: hr_utility.trace('2. l_b14_count_completed ' ||l_b14_count_completed);
1351: l_box14_codeb := l_box14_tab(l_b14_count_completed).box14_code;
1352: l_box14_meaningb := l_box14_tab(l_b14_count_completed).box14_meaning;
1346:
1347: IF l_b14_total_count > 0 THEN
1348: hr_utility.trace('2. l_b14_total_count >0 ' ||l_b14_total_count);
1349: l_b14_count_completed := l_b14_count_completed + 1 ;
1350: hr_utility.trace('2. l_b14_count_completed ' ||l_b14_count_completed);
1351: l_box14_codeb := l_box14_tab(l_b14_count_completed).box14_code;
1352: l_box14_meaningb := l_box14_tab(l_b14_count_completed).box14_meaning;
1353: l_b14_total_count := l_b14_total_count - 1;
1354: hr_utility.trace('l_box14_codeb ' ||l_box14_codeb);
1350: hr_utility.trace('2. l_b14_count_completed ' ||l_b14_count_completed);
1351: l_box14_codeb := l_box14_tab(l_b14_count_completed).box14_code;
1352: l_box14_meaningb := l_box14_tab(l_b14_count_completed).box14_meaning;
1353: l_b14_total_count := l_b14_total_count - 1;
1354: hr_utility.trace('l_box14_codeb ' ||l_box14_codeb);
1355: hr_utility.trace('l_box14_meaningb ' ||l_box14_meaningb);
1356:
1357: END IF;
1358:
1351: l_box14_codeb := l_box14_tab(l_b14_count_completed).box14_code;
1352: l_box14_meaningb := l_box14_tab(l_b14_count_completed).box14_meaning;
1353: l_b14_total_count := l_b14_total_count - 1;
1354: hr_utility.trace('l_box14_codeb ' ||l_box14_codeb);
1355: hr_utility.trace('l_box14_meaningb ' ||l_box14_meaningb);
1356:
1357: END IF;
1358:
1359: IF l_b14_total_count > 0 THEN
1356:
1357: END IF;
1358:
1359: IF l_b14_total_count > 0 THEN
1360: hr_utility.trace('3. l_b14_total_count >0 ' ||l_b14_total_count);
1361:
1362: l_b14_count_completed := l_b14_count_completed + 1 ;
1363: hr_utility.trace('3. l_b14_count_completed ' ||l_b14_count_completed);
1364: l_box14_codec := l_box14_tab(l_b14_count_completed).box14_code;
1359: IF l_b14_total_count > 0 THEN
1360: hr_utility.trace('3. l_b14_total_count >0 ' ||l_b14_total_count);
1361:
1362: l_b14_count_completed := l_b14_count_completed + 1 ;
1363: hr_utility.trace('3. l_b14_count_completed ' ||l_b14_count_completed);
1364: l_box14_codec := l_box14_tab(l_b14_count_completed).box14_code;
1365: l_box14_meaningc := l_box14_tab(l_b14_count_completed).box14_meaning;
1366: l_b14_total_count := l_b14_total_count -1;
1367: hr_utility.trace('l_box14_codec ' ||l_box14_codec);
1363: hr_utility.trace('3. l_b14_count_completed ' ||l_b14_count_completed);
1364: l_box14_codec := l_box14_tab(l_b14_count_completed).box14_code;
1365: l_box14_meaningc := l_box14_tab(l_b14_count_completed).box14_meaning;
1366: l_b14_total_count := l_b14_total_count -1;
1367: hr_utility.trace('l_box14_codec ' ||l_box14_codec);
1368: hr_utility.trace('l_box14_meaningc ' ||l_box14_meaningc);
1369:
1370: END IF;
1371:
1364: l_box14_codec := l_box14_tab(l_b14_count_completed).box14_code;
1365: l_box14_meaningc := l_box14_tab(l_b14_count_completed).box14_meaning;
1366: l_b14_total_count := l_b14_total_count -1;
1367: hr_utility.trace('l_box14_codec ' ||l_box14_codec);
1368: hr_utility.trace('l_box14_meaningc ' ||l_box14_meaningc);
1369:
1370: END IF;
1371:
1372: l_box12_codea := '';
1378: l_box12_coded := '';
1379: l_box12_meaningd := '';
1380:
1381: IF l_b12_total_count > 0 THEN
1382: hr_utility.trace('1. l_b12_total_count >0 ' ||l_b12_total_count);
1383: l_b12_count_completed := l_b12_count_completed + 1 ;
1384: l_box12_codea := l_box12_tab(l_b12_count_completed).box12_code;
1385: l_box12_meaninga := l_box12_tab(l_b12_count_completed).box12_meaning;
1386: l_b12_total_count := l_b12_total_count -1;
1387:
1388: END IF;
1389:
1390: IF l_b12_total_count > 0 THEN
1391: hr_utility.trace('2. l_b12_total_count >0 ' ||l_b12_total_count);
1392:
1393: l_b12_count_completed := l_b12_count_completed + 1 ;
1394: l_box12_codeb := l_box12_tab(l_b12_count_completed).box12_code;
1395: l_box12_meaningb := l_box12_tab(l_b12_count_completed).box12_meaning;
1396: l_b12_total_count := l_b12_total_count -1;
1397: END IF;
1398:
1399: IF l_b12_total_count > 0 THEN
1400: hr_utility.trace('3. l_b12_total_count >0 ' ||l_b12_total_count);
1401:
1402: l_b12_count_completed := l_b12_count_completed + 1 ;
1403: l_box12_codec := l_box12_tab(l_b12_count_completed).box12_code;
1404: l_box12_meaningc := l_box12_tab(l_b12_count_completed).box12_meaning;
1405: l_b12_total_count := l_b12_total_count -1;
1406: END IF;
1407:
1408: IF l_b12_total_count > 0 THEN
1409: hr_utility.trace('4. l_b12_total_count >0 ' ||l_b12_total_count);
1410:
1411: l_b12_count_completed := l_b12_count_completed + 1 ;
1412: l_box12_coded := l_box12_tab(l_b12_count_completed).box12_code;
1413: l_box12_meaningd := l_box12_tab(l_b12_count_completed).box12_meaning;
1431: l_locality2 := '';
1432:
1433:
1434: IF l_state_local_count_completed < l_state_local_total_count THEN
1435: hr_utility.trace('1. l_state_local_total_count >0 ' ||l_state_local_total_count);
1436:
1437: l_state1_code:= l_state_local_tab(l_state_local_count_completed).state_code;
1438: l_state1_ein := l_state_local_tab(l_state_local_count_completed).state_ein;
1439: l_state1_wages:= l_state_local_tab(l_state_local_count_completed).state_wages;
1446:
1447: END IF;
1448:
1449:
1450: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1451: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1452:
1453: IF l_state_local_count_completed < l_state_local_total_count THEN
1454: hr_utility.trace('2. l_state_local_total_count >0 ' ||l_state_local_total_count);
1447: END IF;
1448:
1449:
1450: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1451: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1452:
1453: IF l_state_local_count_completed < l_state_local_total_count THEN
1454: hr_utility.trace('2. l_state_local_total_count >0 ' ||l_state_local_total_count);
1455: hr_utility.trace('2. l_state_local_total_count >0,l_state_local_count_completed ' ||l_state_local_total_count);
1450: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1451: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1452:
1453: IF l_state_local_count_completed < l_state_local_total_count THEN
1454: hr_utility.trace('2. l_state_local_total_count >0 ' ||l_state_local_total_count);
1455: hr_utility.trace('2. l_state_local_total_count >0,l_state_local_count_completed ' ||l_state_local_total_count);
1456:
1457: l_state2_code:= l_state_local_tab(l_state_local_count_completed).state_code;
1458: l_state2_ein := l_state_local_tab(l_state_local_count_completed).state_ein;
1451: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1452:
1453: IF l_state_local_count_completed < l_state_local_total_count THEN
1454: hr_utility.trace('2. l_state_local_total_count >0 ' ||l_state_local_total_count);
1455: hr_utility.trace('2. l_state_local_total_count >0,l_state_local_count_completed ' ||l_state_local_total_count);
1456:
1457: l_state2_code:= l_state_local_tab(l_state_local_count_completed).state_code;
1458: l_state2_ein := l_state_local_tab(l_state_local_count_completed).state_ein;
1459: l_state2_wages:= l_state_local_tab(l_state_local_count_completed).state_wages;
1465: l_state_local_count_completed := l_state_local_count_completed + 1 ;
1466:
1467: END IF;
1468:
1469: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1470: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1471: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1472: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1473:
1466:
1467: END IF;
1468:
1469: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1470: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1471: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1472: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1473:
1474: l_w2_set_cnt := l_w2_set_cnt +1;
1467: END IF;
1468:
1469: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1470: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1471: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1472: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1473:
1474: l_w2_set_cnt := l_w2_set_cnt +1;
1475: IF l_w2_set_cnt > 1 THEN
1468:
1469: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1470: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1471: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1472: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1473:
1474: l_w2_set_cnt := l_w2_set_cnt +1;
1475: IF l_w2_set_cnt > 1 THEN
1476: l_w2_fields.wages_tips_compensation := '';
1507: l_state2_tax,
1508: l_local2_wages,l_local2_tax,
1509: l_locality2,p_year);
1510:
1511: hr_utility.trace('After l_out_create_xml, length of LOB ' ||
1512: dbms_lob.getlength(l_out_create_xml));
1513: -- IF l_xml_string is not NULL and l_out_create_xml IS NOT NULL THEN
1514:
1515: dbms_lob.append(l_xml_blob,l_out_create_xml);
1512: dbms_lob.getlength(l_out_create_xml));
1513: -- IF l_xml_string is not NULL and l_out_create_xml IS NOT NULL THEN
1514:
1515: dbms_lob.append(l_xml_blob,l_out_create_xml);
1516: hr_utility.trace('Length of l_xml_blob ' ||dbms_lob.getlength(l_xml_blob));
1517:
1518: /* ELSE
1519: dbms_lob.writeappend(l_xml_string,dbms_lobamount,l_out_create_xml);;*/
1520: -- END IF;
1522: END LOOP;
1523: END IF;
1524: --}
1525: END IF ; /* p_error_msg is null */
1526: hr_utility.trace('XML String is ');
1527:
1528: --hr_utility.trace(dbms_lob.substr(l_xml_string,,1));
1529:
1530: return l_xml_blob;
1524: --}
1525: END IF ; /* p_error_msg is null */
1526: hr_utility.trace('XML String is ');
1527:
1528: --hr_utility.trace(dbms_lob.substr(l_xml_string,,1));
1529:
1530: return l_xml_blob;
1531: EXCEPTION
1532: WHEN OTHERS then
1529:
1530: return l_xml_blob;
1531: EXCEPTION
1532: WHEN OTHERS then
1533: HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1534: END fetch_w2_xml;
1535:
1536: FUNCTION get_final_xml (p_assignment_action_id Number,
1537: p_tax_unit_id NUMBER,
1549: l_is_temp_final_xml VARCHAR2(2);
1550: l_temp_blob BLOB;
1551:
1552: BEGIN
1553: -- hr_utility.trace_on(null,'w2');
1554: hr_utility.trace('Deleting PL/SQL tables');
1555: l_state_local_tab.delete;
1556: l_state_tab.delete;
1557: l_local_tab.delete;
1550: l_temp_blob BLOB;
1551:
1552: BEGIN
1553: -- hr_utility.trace_on(null,'w2');
1554: hr_utility.trace('Deleting PL/SQL tables');
1555: l_state_local_tab.delete;
1556: l_state_tab.delete;
1557: l_local_tab.delete;
1558: l_box12_tab.delete;
1566: p_tax_unit_id ,
1567: p_year,
1568: p_error_msg , true);
1569:
1570: hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
1571:
1572: IF p_error_msg IS NULL THEN
1573: hr_utility.trace('In final XML p_xml_string ');
1574: /* hr_utility.trace('XML String '||
1569:
1570: hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
1571:
1572: IF p_error_msg IS NULL THEN
1573: hr_utility.trace('In final XML p_xml_string ');
1574: /* hr_utility.trace('XML String '||
1575: dbms_lob.substr(p_xml_string,dbms_lob.getlength(p_xml_string),1));
1576: */
1577: l_final_xml_string :=
1570: hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
1571:
1572: IF p_error_msg IS NULL THEN
1573: hr_utility.trace('In final XML p_xml_string ');
1574: /* hr_utility.trace('XML String '||
1575: dbms_lob.substr(p_xml_string,dbms_lob.getlength(p_xml_string),1));
1576: */
1577: l_final_xml_string :=
1578: ''|| EOL||
1586: '
1587: -- '
1588:
1589:
1590: hr_utility.trace('1. final 1. XML l_final_xml '||
1591: dbms_lob.substr(l_final_xml,dbms_lob.getlength(l_final_xml),1));
1592:
1593: l_last_xml_string := '
1594: -- '
1600: ''||EOL||
1601: '';
1602:
1603: l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
1604: hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
1605:
1606: IF l_is_temp_final_xml = 1 THEN
1607: DBMS_LOB.FREETEMPORARY(l_final_xml);
1608: END IF;
1611: dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
1612: l_final_xml := append_to_lob(l_final_xml_string);
1613: --dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
1614:
1615: hr_utility.trace('Get Length l_final_xml ' ||dbms_lob.getlength(l_final_xml) );
1616:
1617: dbms_lob.append(l_final_xml,p_xml_blob);
1618:
1619: --dbms_lob.writeappend(l_final_xml,length(l_last_xml_string),l_last_xml_string);
1623: dbms_lob.append(l_final_xml,l_temp_blob);
1624:
1625: /* Added ISOPEN condition for bug 3899583 */
1626: IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
1627: hr_utility.trace('Closing l_final_xml' );
1628: dbms_lob.close(l_final_xml);
1629: END IF;
1630: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1631: hr_utility.trace('Closing p_xml_blob' );
1627: hr_utility.trace('Closing l_final_xml' );
1628: dbms_lob.close(l_final_xml);
1629: END IF;
1630: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1631: hr_utility.trace('Closing p_xml_blob' );
1632: dbms_lob.close(p_xml_blob);
1633: END IF;
1634: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1635: hr_utility.trace('Closing l_temp_blob' );
1631: hr_utility.trace('Closing p_xml_blob' );
1632: dbms_lob.close(p_xml_blob);
1633: END IF;
1634: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1635: hr_utility.trace('Closing l_temp_blob' );
1636: dbms_lob.close(l_temp_blob);
1637: END IF;
1638: ELSE
1639: dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
1639: dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
1640: dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
1641: l_final_xml := append_to_lob(p_error_msg);
1642:
1643: hr_utility.trace(' get final cml, p_error_msg '||p_error_msg);
1644:
1645: END IF ; /* p_error_msg is null */
1646: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
1647:
1642:
1643: hr_utility.trace(' get final cml, p_error_msg '||p_error_msg);
1644:
1645: END IF ; /* p_error_msg is null */
1646: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
1647:
1648: return l_final_xml;
1649: EXCEPTION
1650: WHEN OTHERS then
1649: EXCEPTION
1650: WHEN OTHERS then
1651: /* Added ISOPEN condition for bug 3899583 */
1652: IF dbms_lob.ISOPEN(l_final_xml)=1 THEN
1653: hr_utility.trace('Raising exception and Closing l_final_xml' );
1654: dbms_lob.close(l_final_xml);
1655: END IF;
1656: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1657: hr_utility.trace('Raising exception and Closing p_xml_string' );
1653: hr_utility.trace('Raising exception and Closing l_final_xml' );
1654: dbms_lob.close(l_final_xml);
1655: END IF;
1656: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1657: hr_utility.trace('Raising exception and Closing p_xml_string' );
1658: dbms_lob.close(p_xml_blob);
1659: END IF;
1660: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1661: hr_utility.trace('Closing l_temp_blob' );
1657: hr_utility.trace('Raising exception and Closing p_xml_string' );
1658: dbms_lob.close(p_xml_blob);
1659: END IF;
1660: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1661: hr_utility.trace('Closing l_temp_blob' );
1662: dbms_lob.close(l_temp_blob);
1663: END IF;
1664:
1665:
1662: dbms_lob.close(l_temp_blob);
1663: END IF;
1664:
1665:
1666: HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1667: raise;
1668: END get_final_xml;
1669:
1670:
1713: and ppa1.action_type = 'X'
1714: and ppa1.action_status = 'C'
1715: and ppa1.effective_date = ppa.effective_date;
1716: BEGIN
1717: --hr_utility.trace_on(null,'w2');
1718: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1719: hr_utility.trace('In fetch_w2_xm');
1720:
1721: hr_utility.trace('Deleting PL/SQL tables');
1715: and ppa1.effective_date = ppa.effective_date;
1716: BEGIN
1717: --hr_utility.trace_on(null,'w2');
1718: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1719: hr_utility.trace('In fetch_w2_xm');
1720:
1721: hr_utility.trace('Deleting PL/SQL tables');
1722: l_state_local_tab.delete;
1723: l_state_tab.delete;
1717: --hr_utility.trace_on(null,'w2');
1718: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1719: hr_utility.trace('In fetch_w2_xm');
1720:
1721: hr_utility.trace('Deleting PL/SQL tables');
1722: l_state_local_tab.delete;
1723: l_state_tab.delete;
1724: l_local_tab.delete;
1725: l_box12_tab.delete;
1734: CLOSE c_get_params;
1735:
1736: l_output_location := get_outfile;
1737:
1738: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
1739: hr_utility.trace('l_main_assignment_action_id ' ||l_main_assignment_action_id);
1740: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
1741: hr_utility.trace('l_year ' ||l_year);
1742: hr_utility.trace('l_output_location ' ||l_output_location);
1735:
1736: l_output_location := get_outfile;
1737:
1738: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
1739: hr_utility.trace('l_main_assignment_action_id ' ||l_main_assignment_action_id);
1740: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
1741: hr_utility.trace('l_year ' ||l_year);
1742: hr_utility.trace('l_output_location ' ||l_output_location);
1743: hr_utility.trace('l_instr_template ' ||l_instr_template);
1736: l_output_location := get_outfile;
1737:
1738: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
1739: hr_utility.trace('l_main_assignment_action_id ' ||l_main_assignment_action_id);
1740: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
1741: hr_utility.trace('l_year ' ||l_year);
1742: hr_utility.trace('l_output_location ' ||l_output_location);
1743: hr_utility.trace('l_instr_template ' ||l_instr_template);
1744:
1737:
1738: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
1739: hr_utility.trace('l_main_assignment_action_id ' ||l_main_assignment_action_id);
1740: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
1741: hr_utility.trace('l_year ' ||l_year);
1742: hr_utility.trace('l_output_location ' ||l_output_location);
1743: hr_utility.trace('l_instr_template ' ||l_instr_template);
1744:
1745:
1738: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
1739: hr_utility.trace('l_main_assignment_action_id ' ||l_main_assignment_action_id);
1740: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
1741: hr_utility.trace('l_year ' ||l_year);
1742: hr_utility.trace('l_output_location ' ||l_output_location);
1743: hr_utility.trace('l_instr_template ' ||l_instr_template);
1744:
1745:
1746: lc_emp_blob := fetch_w2_xml(l_assignment_action_id,
1739: hr_utility.trace('l_main_assignment_action_id ' ||l_main_assignment_action_id);
1740: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
1741: hr_utility.trace('l_year ' ||l_year);
1742: hr_utility.trace('l_output_location ' ||l_output_location);
1743: hr_utility.trace('l_instr_template ' ||l_instr_template);
1744:
1745:
1746: lc_emp_blob := fetch_w2_xml(l_assignment_action_id,
1747: l_tax_unit_id,
1746: lc_emp_blob := fetch_w2_xml(l_assignment_action_id,
1747: l_tax_unit_id,
1748: l_year,
1749: l_error_msg, false);
1750: hr_utility.trace('XML String is ');
1751: IF ((dbms_lob.getlength(lc_emp_blob) >0) and (l_error_msg IS NULL) )THEN
1752:
1753: --hr_utility.trace(dbms_lob.substr(lc_emp_blob,dbms_lob.getlength(lc_emp_blob),1));
1754:
1749: l_error_msg, false);
1750: hr_utility.trace('XML String is ');
1751: IF ((dbms_lob.getlength(lc_emp_blob) >0) and (l_error_msg IS NULL) )THEN
1752:
1753: --hr_utility.trace(dbms_lob.substr(lc_emp_blob,dbms_lob.getlength(lc_emp_blob),1));
1754:
1755: l_final_xml_string :=
1756: ''|| EOL ||
1757: '
1777: ''||EOL;
1778:
1779:
1780: l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
1781: hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
1782:
1783: IF l_is_temp_final_xml = 1 THEN
1784: DBMS_LOB.FREETEMPORARY(l_final_xml);
1785: END IF;
1803: dbms_lob.append(l_final_xml,l_temp_blob);
1804:
1805: --dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
1806:
1807: hr_utility.trace('Get Length l_final_xml ' ||dbms_lob.getlength(l_final_xml) );
1808:
1809: dbms_lob.append(l_final_xml,lc_emp_blob);
1810:
1811: raw_data:=utl_raw.cast_to_raw(l_last_xml_string);
1823: dbms_lob.append(l_final_xml,l_temp_blob);
1824: --dbms_lob.writeappend(l_final_xml,length(l_last_xml_string),l_last_xml_string);
1825:
1826: IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
1827: hr_utility.trace('Closing l_final_xml' );
1828: dbms_lob.close(l_final_xml);
1829: END IF;
1830: IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
1831: hr_utility.trace('Closing lc_emp_blob' );
1827: hr_utility.trace('Closing l_final_xml' );
1828: dbms_lob.close(l_final_xml);
1829: END IF;
1830: IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
1831: hr_utility.trace('Closing lc_emp_blob' );
1832: dbms_lob.close(lc_emp_blob);
1833: END IF;
1834: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1835: hr_utility.trace('Closing l_temp_blob' );
1831: hr_utility.trace('Closing lc_emp_blob' );
1832: dbms_lob.close(lc_emp_blob);
1833: END IF;
1834: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1835: hr_utility.trace('Closing l_temp_blob' );
1836: dbms_lob.close(l_temp_blob);
1837: END IF;
1838:
1839: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
1835: hr_utility.trace('Closing l_temp_blob' );
1836: dbms_lob.close(l_temp_blob);
1837: END IF;
1838:
1839: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
1840:
1841: pay_core_files.write_to_magtape_lob(l_final_xml);
1842: -- hr_utility.trace('Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
1843:
1838:
1839: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
1840:
1841: pay_core_files.write_to_magtape_lob(l_final_xml);
1842: -- hr_utility.trace('Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
1843:
1844: END IF; /*dbms_lob.getlength(lc_emp_blob) >0*/
1845: -- l_is_temp_xml_string := dbms_lob.istemporary(pay_mag_tape.g_clob_value);
1846:
1847: EXCEPTION
1848: WHEN OTHERS then
1849: /* Added ISOPEN condition for bug 3899583 */
1850: IF dbms_lob.ISOPEN(l_final_xml)=1 THEN
1851: hr_utility.trace('Raising exception and Closing l_final_xml' ||sqlerrm);
1852: dbms_lob.close(l_final_xml);
1853: END IF;
1854: IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
1855: hr_utility.trace('Raising exception and Closing p_xml_string' );
1851: hr_utility.trace('Raising exception and Closing l_final_xml' ||sqlerrm);
1852: dbms_lob.close(l_final_xml);
1853: END IF;
1854: IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
1855: hr_utility.trace('Raising exception and Closing p_xml_string' );
1856: dbms_lob.close(lc_emp_blob);
1857: END IF;
1858: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1859: hr_utility.trace('Closing l_temp_blob' );
1855: hr_utility.trace('Raising exception and Closing p_xml_string' );
1856: dbms_lob.close(lc_emp_blob);
1857: END IF;
1858: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1859: hr_utility.trace('Closing l_temp_blob' );
1860: dbms_lob.close(l_temp_blob);
1861: END IF;
1862:
1863: HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1859: hr_utility.trace('Closing l_temp_blob' );
1860: dbms_lob.close(l_temp_blob);
1861: END IF;
1862:
1863: HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1864: raise;
1865: END;
1866:
1867: PROCEDURE get_footers IS
1873: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1874: l_last_xml_string :=
1875: ''||EOL;
1876: l_is_temp_final_xml := dbms_lob.istemporary(l_footer_xml);
1877: hr_utility.trace('Istemporary(l_footer_xml) ' ||l_is_temp_final_xml );
1878:
1879: IF l_is_temp_final_xml = 1 THEN
1880: DBMS_LOB.FREETEMPORARY(l_footer_xml);
1881: END IF;
1883: dbms_lob.createtemporary(l_footer_xml,false,DBMS_LOB.CALL);
1884: dbms_lob.open(l_footer_xml,dbms_lob.lob_readwrite);
1885: dbms_lob.writeappend(l_footer_xml,length(l_last_xml_string),l_last_xml_string);
1886:
1887: hr_utility.trace('In Get footers,Length of length(l_footer_xml) ' ||dbms_lob.getlength(l_footer_xml));
1888:
1889: --dbms_lob.append(pay_mag_tape.g_clob_value,l_footer_xml);
1890: pay_core_files.write_to_magtape_lob(l_last_xml_string);
1891: --pay_core_files.write_to_magtape_lob(dbms_lob.substr(l_footer_xml,dbms_lob.getlength(l_footer_xml),1));
1889: --dbms_lob.append(pay_mag_tape.g_clob_value,l_footer_xml);
1890: pay_core_files.write_to_magtape_lob(l_last_xml_string);
1891: --pay_core_files.write_to_magtape_lob(dbms_lob.substr(l_footer_xml,dbms_lob.getlength(l_footer_xml),1));
1892:
1893: --hr_utility.trace('In Get footers,Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
1894: END;
1895:
1896: PROCEDURE get_headers IS
1897:
1904: ''|| EOL||
1905: '
1906:
1907: l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
1908: hr_utility.trace('Istemporary(l_final_xml) ' ||l_is_temp_final_xml );
1909:
1910: IF l_is_temp_final_xml = 1 THEN
1911: DBMS_LOB.FREETEMPORARY(l_final_xml);
1912: END IF;
1916: dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
1917: --dbms_lob.append(pay_mag_tape.g_clob_value,l_final_xml);
1918: pay_core_files.write_to_magtape_lob(l_final_xml_string);
1919: --pay_core_files.write_to_magtape_lob(dbms_lob.substr(l_final_xml_string,dbms_lob.getlength(l_final_xml_string),1));
1920: --hr_utility.trace('Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
1921: END;
1922:
1923: function get_outfile return VARCHAR2 is
1924: TEMP_UTL varchar2(512);
1924: TEMP_UTL varchar2(512);
1925: l_log varchar2(100);
1926: l_out varchar2(100);
1927: begin
1928: hr_utility.trace('In get_out_file,g_temp_dir ' ||g_temp_dir );
1929:
1930: if g_temp_dir is null then
1931: -- use first entry of utl_file_dir as the g_temp_dir
1932: select translate(ltrim(value),',',' ')
1945: if (TEMP_UTL is null or g_temp_dir is null ) then
1946: raise no_data_found;
1947: end if;
1948: end if;
1949: hr_utility.trace('In get_out_file,g_temp_dir ' ||g_temp_dir );
1950:
1951: FND_FILE.get_names(l_log,l_out);
1952:
1953: l_out := g_temp_dir ||'/'||l_out;
1950:
1951: FND_FILE.get_names(l_log,l_out);
1952:
1953: l_out := g_temp_dir ||'/'||l_out;
1954: hr_utility.trace('In get_out_file,l_out ' ||l_out );
1955:
1956: return l_out;
1957:
1958: exception