[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
151: OR (l_curr_state IS NULL AND
152: l_curr_local > l_local_tab.COUNT);
153:
154: l_prior_local := l_local_tab.PRIOR(l_curr_local);
155: hr_utility.trace('l_prior_local '||l_local_tab.PRIOR(l_curr_local));
156:
157: IF (l_curr_state IS NOT NULL AND
158: l_curr_local IS NOT NULL ) AND
159: (l_curr_state <= l_state_tab.COUNT ) AND
158: l_curr_local IS NOT NULL ) AND
159: (l_curr_state <= l_state_tab.COUNT ) AND
160: (l_curr_local <= l_local_tab.COUNT) THEN
161:
162: hr_utility.trace('l_state_tab(l_curr_state).state_code '||l_state_tab(l_curr_state).state_code);
163: hr_utility.trace('l_local_tab(l_curr_local).state_code '||l_local_tab(l_curr_local).state_code);
164: hr_utility.trace('l_curr_state '||l_curr_state);
165:
166: IF (l_state_tab(l_curr_state).state_code =
159: (l_curr_state <= l_state_tab.COUNT ) AND
160: (l_curr_local <= l_local_tab.COUNT) THEN
161:
162: hr_utility.trace('l_state_tab(l_curr_state).state_code '||l_state_tab(l_curr_state).state_code);
163: hr_utility.trace('l_local_tab(l_curr_local).state_code '||l_local_tab(l_curr_local).state_code);
164: hr_utility.trace('l_curr_state '||l_curr_state);
165:
166: IF (l_state_tab(l_curr_state).state_code =
167: l_local_tab(l_curr_local).state_code ) THEN
160: (l_curr_local <= l_local_tab.COUNT) THEN
161:
162: hr_utility.trace('l_state_tab(l_curr_state).state_code '||l_state_tab(l_curr_state).state_code);
163: hr_utility.trace('l_local_tab(l_curr_local).state_code '||l_local_tab(l_curr_local).state_code);
164: hr_utility.trace('l_curr_state '||l_curr_state);
165:
166: IF (l_state_tab(l_curr_state).state_code =
167: l_local_tab(l_curr_local).state_code ) THEN
168:
165:
166: IF (l_state_tab(l_curr_state).state_code =
167: l_local_tab(l_curr_local).state_code ) THEN
168:
169: hr_utility.trace('Statecode of state is EQUAL to Local state Code');
170:
171: l_stloc_tcnt := l_state_local_tab.count;
172:
173: hr_utility.trace('l_state_tab(l_curr_state).state_ein '||l_state_tab(l_curr_state).state_ein);
169: hr_utility.trace('Statecode of state is EQUAL to Local state Code');
170:
171: l_stloc_tcnt := l_state_local_tab.count;
172:
173: hr_utility.trace('l_state_tab(l_curr_state).state_ein '||l_state_tab(l_curr_state).state_ein);
174: hr_utility.trace('l_state_tab(l_curr_state).state_wages '||l_state_tab(l_curr_state).state_wages);
175: hr_utility.trace('l_state_tab(l_curr_state).state_tax '||l_state_tab(l_curr_state).state_tax);
176: /* Check to see if the state code of prior local is same as current state */
177:
170:
171: l_stloc_tcnt := l_state_local_tab.count;
172:
173: hr_utility.trace('l_state_tab(l_curr_state).state_ein '||l_state_tab(l_curr_state).state_ein);
174: hr_utility.trace('l_state_tab(l_curr_state).state_wages '||l_state_tab(l_curr_state).state_wages);
175: hr_utility.trace('l_state_tab(l_curr_state).state_tax '||l_state_tab(l_curr_state).state_tax);
176: /* Check to see if the state code of prior local is same as current state */
177:
178: check_prior_local;
171: l_stloc_tcnt := l_state_local_tab.count;
172:
173: hr_utility.trace('l_state_tab(l_curr_state).state_ein '||l_state_tab(l_curr_state).state_ein);
174: hr_utility.trace('l_state_tab(l_curr_state).state_wages '||l_state_tab(l_curr_state).state_wages);
175: hr_utility.trace('l_state_tab(l_curr_state).state_tax '||l_state_tab(l_curr_state).state_tax);
176: /* Check to see if the state code of prior local is same as current state */
177:
178: check_prior_local;
179:
231: ELSIF (l_state_tab(l_curr_state).state_code <
232: l_local_tab(l_curr_local).state_code ) THEN
233: check_prior_local;
234: IF p_write_state THEN
235: hr_utility.trace('current state doesnot match with prior state, so write current state only');
236: write_state_only;
237: ELSE
238: hr_utility.trace('current state matches with prior state, move to next state');
239: l_curr_state := l_curr_state + 1;
234: IF p_write_state THEN
235: hr_utility.trace('current state doesnot match with prior state, so write current state only');
236: write_state_only;
237: ELSE
238: hr_utility.trace('current state matches with prior state, move to next state');
239: l_curr_state := l_curr_state + 1;
240: END IF;
241: --}
242: ELSE
239: l_curr_state := l_curr_state + 1;
240: END IF;
241: --}
242: ELSE
243: hr_utility.trace('Statecode of state is greater than Local state Code');
244: l_stloc_tcnt := l_state_local_tab.count;
245:
246: l_state_local_tab(l_stloc_tcnt).state_code
247: := l_local_tab(l_curr_local).state_code ;
271: ELSIF (l_curr_state IS NULL and l_curr_local IS NOT NULL)
272: OR (l_curr_state > l_state_tab.COUNT AND
273: l_curr_local <= l_local_tab.COUNT) THEN
274:
275: hr_utility.trace('Current state is null and curr local is NOT null');
276: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
277: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality);
278: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax);
279: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages);
272: OR (l_curr_state > l_state_tab.COUNT AND
273: l_curr_local <= l_local_tab.COUNT) THEN
274:
275: hr_utility.trace('Current state is null and curr local is NOT null');
276: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
277: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality);
278: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax);
279: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages);
280:
273: l_curr_local <= l_local_tab.COUNT) THEN
274:
275: hr_utility.trace('Current state is null and curr local is NOT null');
276: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
277: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality);
278: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax);
279: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages);
280:
281:
274:
275: hr_utility.trace('Current state is null and curr local is NOT null');
276: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
277: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality);
278: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax);
279: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages);
280:
281:
282: l_stloc_tcnt := l_state_local_tab.count;
275: hr_utility.trace('Current state is null and curr local is NOT null');
276: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
277: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality);
278: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax);
279: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages);
280:
281:
282: l_stloc_tcnt := l_state_local_tab.count;
283: l_state_local_tab(l_stloc_tcnt).state_code
306: ELSIF (l_curr_state IS NOT NULL and l_curr_local IS NULL)
307: OR (l_curr_state <= l_state_tab.COUNT AND
308: l_curr_local > l_local_tab.COUNT) THEN
309:
310: hr_utility.trace('Current state is not null and curr local is null');
311: hr_utility.trace('l_curr_state '||l_curr_state);
312: hr_utility.trace('l_curr_local '||l_curr_local);
313:
314: check_prior_local;
307: OR (l_curr_state <= l_state_tab.COUNT AND
308: l_curr_local > l_local_tab.COUNT) THEN
309:
310: hr_utility.trace('Current state is not null and curr local is null');
311: hr_utility.trace('l_curr_state '||l_curr_state);
312: hr_utility.trace('l_curr_local '||l_curr_local);
313:
314: check_prior_local;
315: IF p_write_state THEN
308: l_curr_local > l_local_tab.COUNT) THEN
309:
310: hr_utility.trace('Current state is not null and curr local is null');
311: hr_utility.trace('l_curr_state '||l_curr_state);
312: hr_utility.trace('l_curr_local '||l_curr_local);
313:
314: check_prior_local;
315: IF p_write_state THEN
316: hr_utility.trace('current state doesnot match with prior state, so write current state only');
312: hr_utility.trace('l_curr_local '||l_curr_local);
313:
314: check_prior_local;
315: IF p_write_state THEN
316: hr_utility.trace('current state doesnot match with prior state, so write current state only');
317: write_state_only;
318: ELSE
319: hr_utility.trace('current state matches with prior state, move to next state');
320: l_curr_state := l_curr_state + 1;
315: IF p_write_state THEN
316: hr_utility.trace('current state doesnot match with prior state, so write current state only');
317: write_state_only;
318: ELSE
319: hr_utility.trace('current state matches with prior state, move to next state');
320: l_curr_state := l_curr_state + 1;
321:
322: END IF;
323: ELSE
320: l_curr_state := l_curr_state + 1;
321:
322: END IF;
323: ELSE
324: hr_utility.trace('Completed populating all states and locals');
325: exit;
326: END IF;
327: END LOOP;
328:
561: pay_us_archive_util.get_archive_value(p_asg_action_id,
562: 'A_ARCHIVE_DATE',
563: p_tax_unit_id));
564:
565: hr_utility.trace('Archive Date : ' || l_corrected_date);
566: /* l_profile_option := fnd_profile.value('HR_VIEW_ONLINE_W2');
567: IF (l_profile_option is null) or (l_profile_option = '') THEN
568: l_profile_date := fnd_date.canonical_to_date('4712/12/31');
569: ELSE
580: profile option date else continue using the old logic of appending year,
581:
582: */
583: l_live_profile_option := fnd_profile.value('HR_VIEW_ONLINE_W2');
584: hr_utility.trace('View Online W2 Profile date'||l_live_profile_option);
585:
586: IF (l_live_profile_option is null) or (l_live_profile_option = '') THEN
587: l_profile_date := fnd_date.canonical_to_date('4712/12/31');
588: ELSE
592: pay_us_archive_util.get_archive_value(l_payroll_action_id,
593: 'A_VIEW_ONLINE_W2',
594: p_tax_unit_id);
595:
596: hr_utility.trace('l_profile_date '||l_profile_date_string);
597:
598: IF (l_profile_date_string is null) or (l_profile_date_string = '') THEN
599: l_profile_date := fnd_date.canonical_to_date(p_year+1||'/'||l_live_profile_option);
600: hr_utility.trace('l_profile_date was null , setting to '||l_profile_date);
596: hr_utility.trace('l_profile_date '||l_profile_date_string);
597:
598: IF (l_profile_date_string is null) or (l_profile_date_string = '') THEN
599: l_profile_date := fnd_date.canonical_to_date(p_year+1||'/'||l_live_profile_option);
600: hr_utility.trace('l_profile_date was null , setting to '||l_profile_date);
601: ELSE
602: l_profile_date := -- bug 5656018 fnd_date.chardate_to_date
603: fnd_date.canonical_to_date(l_profile_date_string);
604:
601: ELSE
602: l_profile_date := -- bug 5656018 fnd_date.chardate_to_date
603: fnd_date.canonical_to_date(l_profile_date_string);
604:
605: hr_utility.trace('l_profile_date was not null , setting to '||l_profile_date);
606:
607: END IF;
608: END IF;
609:
610: l_w2_corrected :=pay_us_archive_util.get_archive_value(p_asg_action_id,
611: 'A_W2_CORRECTED',
612: p_tax_unit_id);
613:
614: hr_utility.trace('View Online W2 Profile date'||l_live_profile_option);
615:
616: /* If the profile option is blank for fixing bug 4947964 and archive
617: item , A_W2_CORRECTED is not archived for an employee then it
618: will never print 'CORRECTED' which may be incorrect for W-2s which
643: FETCH c_get_business_group_id
644: INTO l_business_group_id;
645: CLOSE c_get_business_group_id;
646:
647: hr_utility.trace('Business Group id ' ||l_business_group_id);
648: hr_utility.trace('TRANSFER_PAYROLL_ACTION_ID ' ||pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
649:
650: IF l_business_group_id is not null THEN
651: hr_us_w2_rep.get_agent_tax_unit_id(l_business_group_id
644: INTO l_business_group_id;
645: CLOSE c_get_business_group_id;
646:
647: hr_utility.trace('Business Group id ' ||l_business_group_id);
648: hr_utility.trace('TRANSFER_PAYROLL_ACTION_ID ' ||pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
649:
650: IF l_business_group_id is not null THEN
651: hr_us_w2_rep.get_agent_tax_unit_id(l_business_group_id
652: ,p_year
653: ,l_agent_tax_unit_id
654: ,l_error_msg);
655: END IF;
656:
657: hr_utility.trace('Agent Tax unit id ' ||l_agent_tax_unit_id);
658: hr_utility.trace('l_error_msg ' ||l_error_msg);
659: /* If l_erro_msg is not null then throw error else get remaining data for W2 */
660: IF l_error_msg IS NOT NULL THEN
661: p_error_msg := l_error_msg;
654: ,l_error_msg);
655: END IF;
656:
657: hr_utility.trace('Agent Tax unit id ' ||l_agent_tax_unit_id);
658: hr_utility.trace('l_error_msg ' ||l_error_msg);
659: /* If l_erro_msg is not null then throw error else get remaining data for W2 */
660: IF l_error_msg IS NOT NULL THEN
661: p_error_msg := l_error_msg;
662: ELSE
667: l_w2_fields.employer_name,
668: l_w2_fields.employer_address;
669: CLOSE c_get_employer_info;
670:
671: hr_utility.trace('l_w2_fields.federal_ein ' ||l_w2_fields.federal_ein);
672: hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
673: hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
674:
675: IF l_agent_tax_unit_id IS NOT NULL THEN
668: l_w2_fields.employer_address;
669: CLOSE c_get_employer_info;
670:
671: hr_utility.trace('l_w2_fields.federal_ein ' ||l_w2_fields.federal_ein);
672: hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
673: hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
674:
675: IF l_agent_tax_unit_id IS NOT NULL THEN
676:
669: CLOSE c_get_employer_info;
670:
671: hr_utility.trace('l_w2_fields.federal_ein ' ||l_w2_fields.federal_ein);
672: hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
673: hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
674:
675: IF l_agent_tax_unit_id IS NOT NULL THEN
676:
677: hr_utility.trace('p_tax_unit_id ' ||p_tax_unit_id);
673: hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
674:
675: IF l_agent_tax_unit_id IS NOT NULL THEN
676:
677: hr_utility.trace('p_tax_unit_id ' ||p_tax_unit_id);
678:
679: OPEN c_get_employer_info(p_tax_unit_id,p_year);
680: FETCH c_get_employer_info
681: INTO l_org_federal_ein,
681: INTO l_org_federal_ein,
682: l_org_employer_name,
683: l_org_address;
684: CLOSE c_get_employer_info;
685: hr_utility.trace('l_org_federal_ein ' ||l_org_federal_ein);
686: hr_utility.trace('l_org_employer_name ' ||l_org_employer_name);
687: hr_utility.trace('l_org_address ' ||l_org_address);
688:
689: l_w2_fields.employer_address
682: l_org_employer_name,
683: l_org_address;
684: CLOSE c_get_employer_info;
685: hr_utility.trace('l_org_federal_ein ' ||l_org_federal_ein);
686: hr_utility.trace('l_org_employer_name ' ||l_org_employer_name);
687: hr_utility.trace('l_org_address ' ||l_org_address);
688:
689: l_w2_fields.employer_address
690: := 'Agent For ' ||substr(l_org_employer_name,1,44)||
683: l_org_address;
684: CLOSE c_get_employer_info;
685: hr_utility.trace('l_org_federal_ein ' ||l_org_federal_ein);
686: hr_utility.trace('l_org_employer_name ' ||l_org_employer_name);
687: hr_utility.trace('l_org_address ' ||l_org_address);
688:
689: l_w2_fields.employer_address
690: := 'Agent For ' ||substr(l_org_employer_name,1,44)||
691: pay_us_w2_info_pkg.EOL ||
691: pay_us_w2_info_pkg.EOL ||
692: l_w2_fields.employer_address;
693: END IF;
694: /* Bug 5575567 */
695: hr_utility.trace(' sysdate ' || sysdate);
696: hr_utility.trace(' end of year ' || fnd_date.canonical_to_date(p_year||'/12/31'));
697:
698: /* Start : Bug # 8353425
699: Considering the Session Date instead of System Date while fetching employee's
692: l_w2_fields.employer_address;
693: END IF;
694: /* Bug 5575567 */
695: hr_utility.trace(' sysdate ' || sysdate);
696: hr_utility.trace(' end of year ' || fnd_date.canonical_to_date(p_year||'/12/31'));
697:
698: /* Start : Bug # 8353425
699: Considering the Session Date instead of System Date while fetching employee's
700: name. The report will now take the employee's current name as of the application
715: OPEN c_get_session_date;
716: FETCH c_get_session_date INTO c_get_session_date_rec;
717: CLOSE c_get_session_date;
718:
719: hr_utility.trace('Application Session Date ' || c_get_session_date_rec.session_date);
720:
721: /*Start Bug 9073693: Since Application session date is sysdate in case of selfservice,
722: Replacing c_get_session_date_rec.session_date with sysdate if it is null */
723:
729:
730: /* End : Bug # 8353425 */
731:
732: --p_effective_date := sysdate; Bug 6443139
733: hr_utility.trace(' p_effective_date ' || p_effective_date);
734:
735: OPEN c_get_emp_info(p_asg_action_id,p_tax_unit_id, p_year) ;
736: FETCH c_get_emp_info
737: INTO l_w2_fields.control_number,
757: -- bug 5575567
758: l_person_id ,
759: p_assignment_id ;
760:
761: hr_utility.trace('EMP NAME ' ||l_w2_fields.emp_name);
762: hr_utility.trace('Control Number ' ||l_w2_fields.control_number);
763: IF c_get_emp_info%NOTFOUND THEN
764: hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_asg_action_id));
765: CLOSE c_get_emp_info;
758: l_person_id ,
759: p_assignment_id ;
760:
761: hr_utility.trace('EMP NAME ' ||l_w2_fields.emp_name);
762: hr_utility.trace('Control Number ' ||l_w2_fields.control_number);
763: IF c_get_emp_info%NOTFOUND THEN
764: hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_asg_action_id));
765: CLOSE c_get_emp_info;
766: raise NO_DATA_FOUND;
760:
761: hr_utility.trace('EMP NAME ' ||l_w2_fields.emp_name);
762: hr_utility.trace('Control Number ' ||l_w2_fields.control_number);
763: IF c_get_emp_info%NOTFOUND THEN
764: hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_asg_action_id));
765: CLOSE c_get_emp_info;
766: raise NO_DATA_FOUND;
767: END IF;
768: CLOSE c_get_emp_info;
800: OPEN c_get_session_date;
801: FETCH c_get_session_date INTO c_get_session_date_rec;
802: CLOSE c_get_session_date;
803:
804: hr_utility.trace('Application Session Date ' || c_get_session_date_rec.session_date);
805:
806: IF (trunc(c_get_session_date_rec.session_date) < fnd_date.canonical_to_date(p_year||'/12/31')) THEN
807: p_effective_date := fnd_date.canonical_to_date(p_year||'/12/31');
808: ELSE
807: p_effective_date := fnd_date.canonical_to_date(p_year||'/12/31');
808: ELSE
809: p_effective_date := trunc(c_get_session_date_rec.session_date);
810: END IF;
811: hr_utility.trace('p_effective_date ' || p_effective_date);*/
812:
813: /* Bug # 8689501 : (End) */
814:
815: lr_employee_addr :=
854: l_state_zero_flag := 'Y' ;
855: l_sl_total_count := 0;
856: OPEN c_get_state_info(p_asg_action_id) ;
857: LOOP
858: hr_utility.trace('In state loop ' );
859: l_sl_total_count := l_sl_total_count + 1;
860: FETCH c_get_state_info
861: INTO l_dummy,
862: l_state_tab(l_sl_total_count).state_code,
863: l_state_tab(l_sl_total_count).state_ein,
864: l_state_tab(l_sl_total_count).state_wages,
865: l_state_tab(l_sl_total_count).state_tax;
866: EXIT WHEN c_get_state_info%NOTFOUND;
867: hr_utility.trace('State_code '|| l_state_tab(l_sl_total_count).state_code);
868: hr_utility.trace('State_EIN '|| l_state_tab(l_sl_total_count).state_ein);
869:
870: if l_state_zero_flag = 'Y' and l_state_tab(l_sl_total_count).state_ein <> 'FLI P.P. #'
871: and ( l_state_tab(l_sl_total_count).state_wages > 0 or l_state_tab(l_sl_total_count).state_tax > 0 ) THEN
864: l_state_tab(l_sl_total_count).state_wages,
865: l_state_tab(l_sl_total_count).state_tax;
866: EXIT WHEN c_get_state_info%NOTFOUND;
867: hr_utility.trace('State_code '|| l_state_tab(l_sl_total_count).state_code);
868: hr_utility.trace('State_EIN '|| l_state_tab(l_sl_total_count).state_ein);
869:
870: if l_state_zero_flag = 'Y' and l_state_tab(l_sl_total_count).state_ein <> 'FLI P.P. #'
871: and ( l_state_tab(l_sl_total_count).state_wages > 0 or l_state_tab(l_sl_total_count).state_tax > 0 ) THEN
872: l_state_zero_flag := 'N' ;
890:
891: l_local_total_count := 0;
892: OPEN c_get_local_info(p_asg_action_id) ;
893: LOOP
894: hr_utility.trace('In local loop ' );
895: FETCH c_get_local_info
896: INTO l_locality,
897: l_locality_wages,
898: l_locality_tax,
899: l_jurisdiction,
900: l_state_code,
901: l_tax_type;
902:
903: hr_utility.trace('l_locality is '||l_locality);
904:
905: EXIT WHEN c_get_local_info%NOTFOUND;
906: IF l_locality_tax > 0 THEN
907: -- l_local_total_count := l_local_total_count + 1;
907: -- l_local_total_count := l_local_total_count + 1;
908: /* populate the locality table only if the jurisdiction code <> 18-015-000
909: as this needs to be reported in box 14 as occupational and mental health tax*/
910: IF ( l_tax_type = 'COUNTY' and l_jurisdiction = '18-015-0000') THEN
911: hr_utility.trace('Jurisdiction is 18-015-0000 and tax_type is County');
912: IF (l_locality_tax > 0 and l_locality_wages > 0) THEN
913: hr_utility.trace('Locality tax withheld > 0 for KY, Boone county');
914:
915: l_b14_total_count := l_box14_tab.count+1;
909: as this needs to be reported in box 14 as occupational and mental health tax*/
910: IF ( l_tax_type = 'COUNTY' and l_jurisdiction = '18-015-0000') THEN
911: hr_utility.trace('Jurisdiction is 18-015-0000 and tax_type is County');
912: IF (l_locality_tax > 0 and l_locality_wages > 0) THEN
913: hr_utility.trace('Locality tax withheld > 0 for KY, Boone county');
914:
915: l_b14_total_count := l_box14_tab.count+1;
916: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
917:
912: IF (l_locality_tax > 0 and l_locality_wages > 0) THEN
913: hr_utility.trace('Locality tax withheld > 0 for KY, Boone county');
914:
915: l_b14_total_count := l_box14_tab.count+1;
916: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
917:
918: /* IF (g_mh_tax_rate IS NULL OR g_mh_tax_limit IS NULL
919: OR g_occ_tax_rate IS NULL OR g_occ_mh_tax_limit IS NULL
920: OR g_occ_mh_wage_limit IS NULL OR g_mh_tax_wage_limit IS NULL )
918: /* IF (g_mh_tax_rate IS NULL OR g_mh_tax_limit IS NULL
919: OR g_occ_tax_rate IS NULL OR g_occ_mh_tax_limit IS NULL
920: OR g_occ_mh_wage_limit IS NULL OR g_mh_tax_wage_limit IS NULL )
921: THEN
922: hr_utility.trace('Getting Mental health and Occupational tax limits');
923: hr_us_w2_rep.get_county_tax_info('18-015-0000',
924: p_year,
925: g_occ_tax_rate,
926: g_mh_tax_rate,
942: p_tax_unit_id, '18-015-0000', 6);
943:
944: l_box14_tab(l_b14_total_count).box14_code := 'BOONMH';
945: l_box14_tab(l_b14_total_count).box14_meaning := l_box14_boonmh_value;
946: hr_utility.trace('l_box14_tab(l_b14_total_count).box14_meaning '||l_box14_tab(l_b14_total_count).box14_meaning);
947:
948: l_b14_total_count := l_box14_tab.count+1;
949: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
950:
945: l_box14_tab(l_b14_total_count).box14_meaning := l_box14_boonmh_value;
946: hr_utility.trace('l_box14_tab(l_b14_total_count).box14_meaning '||l_box14_tab(l_b14_total_count).box14_meaning);
947:
948: l_b14_total_count := l_box14_tab.count+1;
949: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
950:
951: l_box14_tab(l_b14_total_count).box14_code := 'BOONOCC';
952: l_box14_tab(l_b14_total_count).box14_meaning :=
953: l_locality_tax - l_box14_boonmh_value;
960: l_local_tab(l_local_total_count).jurisdiction := l_jurisdiction;
961: l_local_tab(l_local_total_count).state_code := l_state_code;
962: l_local_tab(l_local_total_count).tax_type := l_tax_type;
963:
964: hr_utility.trace('Locality_code '|| l_local_tab(l_local_total_count).locality);
965: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code);
966:
967: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction);
968: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax);
961: l_local_tab(l_local_total_count).state_code := l_state_code;
962: l_local_tab(l_local_total_count).tax_type := l_tax_type;
963:
964: hr_utility.trace('Locality_code '|| l_local_tab(l_local_total_count).locality);
965: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code);
966:
967: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction);
968: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax);
969: hr_utility.trace('Locality Tax Type '|| l_local_tab(l_local_total_count).tax_type);
963:
964: hr_utility.trace('Locality_code '|| l_local_tab(l_local_total_count).locality);
965: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code);
966:
967: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction);
968: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax);
969: hr_utility.trace('Locality Tax Type '|| l_local_tab(l_local_total_count).tax_type);
970:
971:
964: hr_utility.trace('Locality_code '|| l_local_tab(l_local_total_count).locality);
965: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code);
966:
967: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction);
968: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax);
969: hr_utility.trace('Locality Tax Type '|| l_local_tab(l_local_total_count).tax_type);
970:
971:
972: IF (nvl(l_local_tab(l_local_total_count).locality_tax,0) > 0) THEN
965: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code);
966:
967: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction);
968: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax);
969: hr_utility.trace('Locality Tax Type '|| l_local_tab(l_local_total_count).tax_type);
970:
971:
972: IF (nvl(l_local_tab(l_local_total_count).locality_tax,0) > 0) THEN
973: IF (l_local_tab(l_local_total_count).tax_type = 'CITY SCHOOL' or
972: IF (nvl(l_local_tab(l_local_total_count).locality_tax,0) > 0) THEN
973: IF (l_local_tab(l_local_total_count).tax_type = 'CITY SCHOOL' or
974: l_local_tab(l_local_total_count).tax_type = 'COUNTY SCHOOL' ) THEN
975:
976: hr_utility.trace('Locality Tax Type is County/city school');
977:
978: if l_local_tab(l_local_total_count).state_code = 'OH' then
979:
980: hr_utility.trace('Locality state code is OH');
976: hr_utility.trace('Locality Tax Type is County/city school');
977:
978: if l_local_tab(l_local_total_count).state_code = 'OH' then
979:
980: hr_utility.trace('Locality state code is OH');
981:
982: l_local_tab(l_local_total_count).locality
983: := substr(l_local_tab(l_local_total_count).jurisdiction,5,4)
984: ||' '||substr(l_local_tab(l_local_total_count).locality,1,8);
982: l_local_tab(l_local_total_count).locality
983: := substr(l_local_tab(l_local_total_count).jurisdiction,5,4)
984: ||' '||substr(l_local_tab(l_local_total_count).locality,1,8);
985: elsif l_local_tab(l_local_total_count).state_code = 'KY' then
986: hr_utility.trace('Locality state code is KY');
987: l_local_tab(l_local_total_count).locality
988: := substr(l_local_tab(l_local_total_count).jurisdiction,7,2)
989: ||' '||substr(l_local_tab(l_local_total_count).locality,1,10);
990: else
987: l_local_tab(l_local_total_count).locality
988: := substr(l_local_tab(l_local_total_count).jurisdiction,7,2)
989: ||' '||substr(l_local_tab(l_local_total_count).locality,1,10);
990: else
991: hr_utility.trace('Locality state code neither OH nor KY');
992: l_local_tab(l_local_total_count).locality
993: := substr(l_local_tab(l_local_total_count).jurisdiction,4,5)
994: ||' '||substr(l_local_tab(l_local_total_count).locality,1,7);
995: end if;
995: end if;
996: END IF;
997: END IF;
998:
999: hr_utility.trace('l_local_tab(l_local_total_count).locality is '||l_local_tab(l_local_total_count).locality);
1000: IF (l_local_tab(l_local_total_count).state_code = 'IN'
1001: and l_local_tab(l_local_total_count).tax_type = 'COUNTY') THEN
1002: BEGIN
1003: select nvl(value,'N') into l_nr_flag
1046:
1047: EXIT WHEN c_get_box12_info%NOTFOUND;
1048: --moved here for fixing bug 10127771
1049: l_b12_total_count := l_b12_total_count + 1;
1050: -- hr_utility.trace('In box12 loop '||l_box12_tab(l_b12_total_count).box12_code );
1051: END LOOP;
1052: CLOSE c_get_box12_info ;
1053:
1054: -- l_b14_total_count := l_box14_tab.count;
1054: -- l_b14_total_count := l_box14_tab.count;
1055: OPEN c_get_box14_info (p_asg_action_id) ;
1056: LOOP
1057: l_b14_total_count := l_box14_tab.count+1;
1058: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1059:
1060: FETCH c_get_box14_info
1061: INTO l_box14_tab(l_b14_total_count).box14_code,
1062: l_box14_tab(l_b14_total_count).box14_meaning;
1061: INTO l_box14_tab(l_b14_total_count).box14_code,
1062: l_box14_tab(l_b14_total_count).box14_meaning;
1063:
1064: EXIT WHEN c_get_box14_info%NOTFOUND;
1065: hr_utility.trace('In box14 loop ' ||l_box14_tab(l_b14_total_count).box14_code);
1066:
1067: END LOOP;
1068: CLOSE c_get_box14_info ;
1069: /*
1069: /*
1070: OPEN c_get_box14_boonocc (p_asg_action_id) ;
1071: LOOP
1072: l_b14_total_count := l_box14_tab.count+1;
1073: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1074:
1075: FETCH c_get_box14_boonocc
1076: INTO l_box14_tab(l_b14_total_count).box14_code,
1077: l_box14_tab(l_b14_total_count).box14_meaning;
1076: INTO l_box14_tab(l_b14_total_count).box14_code,
1077: l_box14_tab(l_b14_total_count).box14_meaning;
1078:
1079: EXIT WHEN c_get_box14_boonocc%NOTFOUND;
1080: hr_utility.trace('In c_get_box14_boonocc loop ' ||l_box14_tab(l_b14_total_count).box14_code);
1081:
1082: END LOOP;
1083: CLOSE c_get_box14_boonocc ;
1084:
1085:
1086: OPEN c_get_box14_boonmh (p_asg_action_id) ;
1087: LOOP
1088: l_b14_total_count := l_box14_tab.count+1;
1089: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1090:
1091: FETCH c_get_box14_boonmh
1092: INTO l_box14_tab(l_b14_total_count).box14_code,
1093: l_box14_tab(l_b14_total_count).box14_meaning;
1092: INTO l_box14_tab(l_b14_total_count).box14_code,
1093: l_box14_tab(l_b14_total_count).box14_meaning;
1094:
1095: EXIT WHEN c_get_box14_boonmh%NOTFOUND;
1096: hr_utility.trace('In c_get_box14_boonmh loop ' ||l_box14_tab(l_b14_total_count).box14_code);
1097:
1098: END LOOP;
1099: CLOSE c_get_box14_boonmh ;
1100: */
1098: END LOOP;
1099: CLOSE c_get_box14_boonmh ;
1100: */
1101:
1102: hr_utility.trace('l_locality_tax :' || l_locality_tax );
1103: hr_utility.trace('l_hi_state_printed' || l_hi_state_printed );
1104: If l_hi_state_printed = 'Y' and l_locality_tax > 0 then /* 6519495 */
1105:
1106: l_b14_total_count := l_box14_tab.count + 1;
1099: CLOSE c_get_box14_boonmh ;
1100: */
1101:
1102: hr_utility.trace('l_locality_tax :' || l_locality_tax );
1103: hr_utility.trace('l_hi_state_printed' || l_hi_state_printed );
1104: If l_hi_state_printed = 'Y' and l_locality_tax > 0 then /* 6519495 */
1105:
1106: l_b14_total_count := l_box14_tab.count + 1;
1107:
1123: l_nj_planid := pay_us_archive_util.get_archive_value(p_asg_action_id,
1124: 'A_SCL_ASG_US_NJ_PLAN_ID', --A_EXTRA_ASSIGNMENT_INFORMATION_PAY_US_DISABILITY_PLAN_INFO_DF_PLAN_ID'
1125: p_tax_unit_id) ;
1126: If l_nj_planid IS NOT NULL then
1127: hr_utility.trace('NJ DIPP plan id: ' || l_nj_planid);
1128: --Bug 7361496 Formatting DI P.P. # for last 10 characters to appear in Employee W-2 PDF
1129: l_nj_planid := substr(l_nj_planid,length(l_nj_planid)-10+1,length(l_nj_planid));
1130: l_b14_total_count := l_box14_tab.count + 1;
1131: l_box14_tab(l_b14_total_count).box14_code := 'DI P.P. # '||l_nj_planid ;
1142: ,nvl(l_w2_fields.allocated_tips,0)
1143: ,nvl(l_w2_fields.eic_payment,0)
1144: ,nvl(l_w2_fields.dependent_care,0)
1145: ,nvl(l_w2_fields.non_qual_plan,0)) < 0 THEN
1146: hr_utility.trace('Negative values for atleast one of box1-11 ');
1147: p_error_msg := 'Negative values for atleast one of box1-11';
1148: END IF;
1149:
1150:
1147: p_error_msg := 'Negative values for atleast one of box1-11';
1148: END IF;
1149:
1150:
1151: hr_utility.trace('Before 0 check ');
1152: IF nvl(l_w2_fields.wages_tips_compensation,0)=0 and
1153: nvl(l_w2_fields.fit_withheld,0)=0 and
1154: nvl(l_w2_fields.ss_wages,0)=0 and
1155: nvl(l_w2_fields.ss_withheld,0)=0 and
1160: nvl(l_w2_fields.eic_payment,0)=0 and
1161: nvl(l_w2_fields.dependent_care,0)=0 and
1162: nvl(l_w2_fields.non_qual_plan,0)=0 and l_state_zero_flag = 'Y'
1163: and l_b12_total_count = 0 THEN
1164: hr_utility.trace('Zero values for box1-12 and state wages/withheld');
1165: p_error_msg := 'Zero values for box1-12 and state wages/withheld ';
1166: END IF;
1167:
1168: /* Code to print Amended/amended date on W-2 */
1193: is_temp varchar2(10);
1194: text_size NUMBER;
1195: raw_data RAW(32767);
1196: begin
1197: hr_utility.trace('In create XML string ' );
1198: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1199: IF (g_print_instr IS NULL) OR (g_print_instr = '') THEN
1200: g_print_instr := 'Y';
1201: END IF;
1261: ''||EOL;
1262:
1263:
1264:
1265: hr_utility.trace('one set XML string ' ||l_xml_string);
1266: is_temp := dbms_lob.istemporary(l_xml_blob);
1267: hr_utility.trace('Istemporary(l_xml_blob) ' ||is_temp );
1268:
1269: IF is_temp = 1 THEN
1263:
1264:
1265: hr_utility.trace('one set XML string ' ||l_xml_string);
1266: is_temp := dbms_lob.istemporary(l_xml_blob);
1267: hr_utility.trace('Istemporary(l_xml_blob) ' ||is_temp );
1268:
1269: IF is_temp = 1 THEN
1270: DBMS_LOB.FREETEMPORARY(l_xml_blob);
1271: END IF;
1271: END IF;
1272:
1273: dbms_lob.createtemporary(l_xml_blob,false,DBMS_LOB.CALL);
1274: dbms_lob.open(l_xml_blob,dbms_lob.lob_readwrite);
1275: hr_utility.trace('OPENED l_xml_blob ' );
1276:
1277: raw_data:=utl_raw.cast_to_raw(l_xml_string);
1278: text_size:=utl_raw.length(raw_data);
1279:
1278: text_size:=utl_raw.length(raw_data);
1279:
1280: dbms_lob.writeappend(l_xml_blob,text_size,raw_data);
1281:
1282: hr_utility.trace('Get Length l_xml_clob ' ||dbms_lob.getlength(l_xml_blob) );
1283: dbms_lob.close(l_xml_blob);
1284: return l_xml_blob;
1285: exception
1286: when OTHERS then
1284: return l_xml_blob;
1285: exception
1286: when OTHERS then
1287: dbms_lob.close(l_xml_blob);
1288: HR_UTILITY.TRACE('sqleerm ' || sqlerrm);
1289: HR_UTILITY.RAISE_ERROR;
1290:
1291: end create_xml_string;
1292: --} end create_xml_string
1285: exception
1286: when OTHERS then
1287: dbms_lob.close(l_xml_blob);
1288: HR_UTILITY.TRACE('sqleerm ' || sqlerrm);
1289: HR_UTILITY.RAISE_ERROR;
1290:
1291: end create_xml_string;
1292: --} end create_xml_string
1293:
1343: l_w2_set_cnt number;
1344: l_is_temp_xml_string VARCHAR2(2);
1345:
1346: begin
1347: hr_utility.trace('In Fetch w2 xml loop ' );
1348:
1349: l_b14_total_count := 0;
1350: l_b14_count_completed := 0;
1351: l_b12_total_count := 0;
1362: get_w2_data(p_assignment_action_id,p_tax_unit_id,p_year,p_error_msg);
1363:
1364: IF p_error_msg IS NULL THEN
1365:
1366: hr_utility.trace('After get W2 data' );
1367:
1368: l_b14_total_count := l_box14_tab.count;
1369: l_b14_count_completed := 0;
1370: l_b12_total_count := l_box12_tab.count;
1376: l_state_local_total_count := l_state_local_tab.count;
1377: l_state_local_count_completed := 0;
1378:
1379: l_is_temp_xml_string := dbms_lob.istemporary(l_xml_blob);
1380: hr_utility.trace('Istemporary(l_xml_blob) ' ||l_is_temp_xml_string );
1381:
1382: IF l_is_temp_xml_string = 1 THEN
1383: DBMS_LOB.FREETEMPORARY(l_xml_blob);
1384: END IF;
1390: (l_b12_total_count = 0) AND
1391: --(l_sl_total_count = 0)
1392: (l_state_local_total_count = 0) THEN
1393:
1394: hr_utility.trace('In l_b14_total_count and other counts =0 ' );
1395: -- dbms_lob.append(l_final_xml,p_xml_string);
1396: l_xml_blob := create_xml_string(l_w2_fields,
1397: l_box14_codea,l_box14_meaninga,
1398: l_box14_codeb,l_box14_meaningb,
1408: l_state2_code,l_state2_ein,
1409: l_state2_wages, l_state2_tax,
1410: l_local2_wages,l_local2_tax,
1411: l_locality2,p_year);
1412: hr_utility.trace('after getting XML Blob ' );
1413:
1414: ELSE
1415:
1416: --{
1415:
1416: --{
1417: LOOP
1418:
1419: hr_utility.trace('In loop to get XML ' );
1420: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1421: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
1422: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1423: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1416: --{
1417: LOOP
1418:
1419: hr_utility.trace('In loop to get XML ' );
1420: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1421: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
1422: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1423: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1424:
1417: LOOP
1418:
1419: hr_utility.trace('In loop to get XML ' );
1420: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1421: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
1422: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1423: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1424:
1425: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1418:
1419: hr_utility.trace('In loop to get XML ' );
1420: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1421: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
1422: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1423: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1424:
1425: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1426: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1419: hr_utility.trace('In loop to get XML ' );
1420: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1421: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
1422: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1423: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1424:
1425: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1426: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1427: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1421: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
1422: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1423: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1424:
1425: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1426: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1427: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1428: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1429: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1422: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1423: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1424:
1425: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1426: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1427: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1428: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1429: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1430: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1423: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1424:
1425: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1426: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1427: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1428: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1429: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1430: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1431: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
1424:
1425: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1426: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1427: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1428: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1429: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1430: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1431: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
1432:
1425: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1426: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1427: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1428: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1429: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1430: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1431: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
1432:
1433: EXIT WHEN
1426: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1427: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1428: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1429: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1430: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1431: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
1432:
1433: EXIT WHEN
1434: ((l_b14_total_count = 0) AND
1427: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1428: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1429: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1430: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1431: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
1432:
1433: EXIT WHEN
1434: ((l_b14_total_count = 0) AND
1435: (l_b12_total_count = 0 ) AND
1442: l_box14_codec := '';
1443: l_box14_meaningc := '';
1444:
1445: IF l_b14_total_count > 0 THEN
1446: hr_utility.trace('1. l_b14_total_count >0 ' ||l_b14_total_count);
1447: l_b14_count_completed := l_b14_count_completed + 1 ;
1448: hr_utility.trace('1. l_b14_count_completed ' ||l_b14_count_completed);
1449: l_box14_codea := l_box14_tab(l_b14_count_completed).box14_code;
1450: l_box14_meaninga := l_box14_tab(l_b14_count_completed).box14_meaning;
1444:
1445: IF l_b14_total_count > 0 THEN
1446: hr_utility.trace('1. l_b14_total_count >0 ' ||l_b14_total_count);
1447: l_b14_count_completed := l_b14_count_completed + 1 ;
1448: hr_utility.trace('1. l_b14_count_completed ' ||l_b14_count_completed);
1449: l_box14_codea := l_box14_tab(l_b14_count_completed).box14_code;
1450: l_box14_meaninga := l_box14_tab(l_b14_count_completed).box14_meaning;
1451: l_b14_total_count := l_b14_total_count -1;
1452: hr_utility.trace('l_box14_codea ' ||l_box14_codea);
1448: hr_utility.trace('1. l_b14_count_completed ' ||l_b14_count_completed);
1449: l_box14_codea := l_box14_tab(l_b14_count_completed).box14_code;
1450: l_box14_meaninga := l_box14_tab(l_b14_count_completed).box14_meaning;
1451: l_b14_total_count := l_b14_total_count -1;
1452: hr_utility.trace('l_box14_codea ' ||l_box14_codea);
1453: hr_utility.trace('l_box14_meaninga ' ||l_box14_meaninga);
1454:
1455: END IF;
1456:
1449: l_box14_codea := l_box14_tab(l_b14_count_completed).box14_code;
1450: l_box14_meaninga := l_box14_tab(l_b14_count_completed).box14_meaning;
1451: l_b14_total_count := l_b14_total_count -1;
1452: hr_utility.trace('l_box14_codea ' ||l_box14_codea);
1453: hr_utility.trace('l_box14_meaninga ' ||l_box14_meaninga);
1454:
1455: END IF;
1456:
1457: IF l_b14_total_count > 0 THEN
1454:
1455: END IF;
1456:
1457: IF l_b14_total_count > 0 THEN
1458: hr_utility.trace('2. l_b14_total_count >0 ' ||l_b14_total_count);
1459: l_b14_count_completed := l_b14_count_completed + 1 ;
1460: hr_utility.trace('2. l_b14_count_completed ' ||l_b14_count_completed);
1461: l_box14_codeb := l_box14_tab(l_b14_count_completed).box14_code;
1462: l_box14_meaningb := l_box14_tab(l_b14_count_completed).box14_meaning;
1456:
1457: IF l_b14_total_count > 0 THEN
1458: hr_utility.trace('2. l_b14_total_count >0 ' ||l_b14_total_count);
1459: l_b14_count_completed := l_b14_count_completed + 1 ;
1460: hr_utility.trace('2. l_b14_count_completed ' ||l_b14_count_completed);
1461: l_box14_codeb := l_box14_tab(l_b14_count_completed).box14_code;
1462: l_box14_meaningb := l_box14_tab(l_b14_count_completed).box14_meaning;
1463: l_b14_total_count := l_b14_total_count - 1;
1464: hr_utility.trace('l_box14_codeb ' ||l_box14_codeb);
1460: hr_utility.trace('2. l_b14_count_completed ' ||l_b14_count_completed);
1461: l_box14_codeb := l_box14_tab(l_b14_count_completed).box14_code;
1462: l_box14_meaningb := l_box14_tab(l_b14_count_completed).box14_meaning;
1463: l_b14_total_count := l_b14_total_count - 1;
1464: hr_utility.trace('l_box14_codeb ' ||l_box14_codeb);
1465: hr_utility.trace('l_box14_meaningb ' ||l_box14_meaningb);
1466:
1467: END IF;
1468:
1461: l_box14_codeb := l_box14_tab(l_b14_count_completed).box14_code;
1462: l_box14_meaningb := l_box14_tab(l_b14_count_completed).box14_meaning;
1463: l_b14_total_count := l_b14_total_count - 1;
1464: hr_utility.trace('l_box14_codeb ' ||l_box14_codeb);
1465: hr_utility.trace('l_box14_meaningb ' ||l_box14_meaningb);
1466:
1467: END IF;
1468:
1469: IF l_b14_total_count > 0 THEN
1466:
1467: END IF;
1468:
1469: IF l_b14_total_count > 0 THEN
1470: hr_utility.trace('3. l_b14_total_count >0 ' ||l_b14_total_count);
1471:
1472: l_b14_count_completed := l_b14_count_completed + 1 ;
1473: hr_utility.trace('3. l_b14_count_completed ' ||l_b14_count_completed);
1474: l_box14_codec := l_box14_tab(l_b14_count_completed).box14_code;
1469: IF l_b14_total_count > 0 THEN
1470: hr_utility.trace('3. l_b14_total_count >0 ' ||l_b14_total_count);
1471:
1472: l_b14_count_completed := l_b14_count_completed + 1 ;
1473: hr_utility.trace('3. l_b14_count_completed ' ||l_b14_count_completed);
1474: l_box14_codec := l_box14_tab(l_b14_count_completed).box14_code;
1475: l_box14_meaningc := l_box14_tab(l_b14_count_completed).box14_meaning;
1476: l_b14_total_count := l_b14_total_count -1;
1477: hr_utility.trace('l_box14_codec ' ||l_box14_codec);
1473: hr_utility.trace('3. l_b14_count_completed ' ||l_b14_count_completed);
1474: l_box14_codec := l_box14_tab(l_b14_count_completed).box14_code;
1475: l_box14_meaningc := l_box14_tab(l_b14_count_completed).box14_meaning;
1476: l_b14_total_count := l_b14_total_count -1;
1477: hr_utility.trace('l_box14_codec ' ||l_box14_codec);
1478: hr_utility.trace('l_box14_meaningc ' ||l_box14_meaningc);
1479:
1480: END IF;
1481:
1474: l_box14_codec := l_box14_tab(l_b14_count_completed).box14_code;
1475: l_box14_meaningc := l_box14_tab(l_b14_count_completed).box14_meaning;
1476: l_b14_total_count := l_b14_total_count -1;
1477: hr_utility.trace('l_box14_codec ' ||l_box14_codec);
1478: hr_utility.trace('l_box14_meaningc ' ||l_box14_meaningc);
1479:
1480: END IF;
1481:
1482: l_box12_codea := '';
1488: l_box12_coded := '';
1489: l_box12_meaningd := '';
1490:
1491: IF l_b12_total_count > 0 THEN
1492: hr_utility.trace('1. l_b12_total_count >0 ' ||l_b12_total_count);
1493: l_b12_count_completed := l_b12_count_completed + 1 ;
1494: l_box12_codea := l_box12_tab(l_b12_count_completed).box12_code;
1495: l_box12_meaninga := l_box12_tab(l_b12_count_completed).box12_meaning;
1496: l_b12_total_count := l_b12_total_count -1;
1496: l_b12_total_count := l_b12_total_count -1;
1497: END IF;
1498:
1499: IF l_b12_total_count > 0 THEN
1500: hr_utility.trace('2. l_b12_total_count >0 ' ||l_b12_total_count);
1501:
1502: l_b12_count_completed := l_b12_count_completed + 1 ;
1503: l_box12_codeb := l_box12_tab(l_b12_count_completed).box12_code;
1504: l_box12_meaningb := l_box12_tab(l_b12_count_completed).box12_meaning;
1505: l_b12_total_count := l_b12_total_count -1;
1506: END IF;
1507:
1508: IF l_b12_total_count > 0 THEN
1509: hr_utility.trace('3. l_b12_total_count >0 ' ||l_b12_total_count);
1510:
1511: l_b12_count_completed := l_b12_count_completed + 1 ;
1512: l_box12_codec := l_box12_tab(l_b12_count_completed).box12_code;
1513: l_box12_meaningc := l_box12_tab(l_b12_count_completed).box12_meaning;
1514: l_b12_total_count := l_b12_total_count -1;
1515: END IF;
1516:
1517: IF l_b12_total_count > 0 THEN
1518: hr_utility.trace('4. l_b12_total_count >0 ' ||l_b12_total_count);
1519:
1520: l_b12_count_completed := l_b12_count_completed + 1 ;
1521: l_box12_coded := l_box12_tab(l_b12_count_completed).box12_code;
1522: l_box12_meaningd := l_box12_tab(l_b12_count_completed).box12_meaning;
1540: l_locality2 := '';
1541:
1542:
1543: IF l_state_local_count_completed < l_state_local_total_count THEN
1544: hr_utility.trace('1. l_state_local_total_count >0 ' ||l_state_local_total_count);
1545:
1546: l_state1_code:= l_state_local_tab(l_state_local_count_completed).state_code;
1547: l_state1_ein := l_state_local_tab(l_state_local_count_completed).state_ein;
1548: l_state1_wages:= l_state_local_tab(l_state_local_count_completed).state_wages;
1555:
1556: END IF;
1557:
1558:
1559: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1560: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1561:
1562: IF l_state_local_count_completed < l_state_local_total_count THEN
1563: hr_utility.trace('2. l_state_local_total_count >0 ' ||l_state_local_total_count);
1556: END IF;
1557:
1558:
1559: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1560: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1561:
1562: IF l_state_local_count_completed < l_state_local_total_count THEN
1563: hr_utility.trace('2. l_state_local_total_count >0 ' ||l_state_local_total_count);
1564: hr_utility.trace('2. l_state_local_total_count >0,l_state_local_count_completed ' ||l_state_local_total_count);
1559: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1560: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1561:
1562: IF l_state_local_count_completed < l_state_local_total_count THEN
1563: hr_utility.trace('2. l_state_local_total_count >0 ' ||l_state_local_total_count);
1564: hr_utility.trace('2. l_state_local_total_count >0,l_state_local_count_completed ' ||l_state_local_total_count);
1565:
1566: l_state2_code:= l_state_local_tab(l_state_local_count_completed).state_code;
1567: l_state2_ein := l_state_local_tab(l_state_local_count_completed).state_ein;
1560: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1561:
1562: IF l_state_local_count_completed < l_state_local_total_count THEN
1563: hr_utility.trace('2. l_state_local_total_count >0 ' ||l_state_local_total_count);
1564: hr_utility.trace('2. l_state_local_total_count >0,l_state_local_count_completed ' ||l_state_local_total_count);
1565:
1566: l_state2_code:= l_state_local_tab(l_state_local_count_completed).state_code;
1567: l_state2_ein := l_state_local_tab(l_state_local_count_completed).state_ein;
1568: l_state2_wages:= l_state_local_tab(l_state_local_count_completed).state_wages;
1574: l_state_local_count_completed := l_state_local_count_completed + 1 ;
1575:
1576: END IF;
1577:
1578: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1579: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1580: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1581: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1582:
1575:
1576: END IF;
1577:
1578: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1579: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1580: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1581: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1582:
1583: l_w2_set_cnt := l_w2_set_cnt +1;
1576: END IF;
1577:
1578: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1579: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1580: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1581: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1582:
1583: l_w2_set_cnt := l_w2_set_cnt +1;
1584: IF l_w2_set_cnt > 1 THEN
1577:
1578: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1579: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1580: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1581: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1582:
1583: l_w2_set_cnt := l_w2_set_cnt +1;
1584: IF l_w2_set_cnt > 1 THEN
1585: l_w2_fields.wages_tips_compensation := '';
1616: l_state2_tax,
1617: l_local2_wages,l_local2_tax,
1618: l_locality2,p_year);
1619:
1620: hr_utility.trace('After l_out_create_xml, length of LOB ' ||
1621: dbms_lob.getlength(l_out_create_xml));
1622: -- IF l_xml_string is not NULL and l_out_create_xml IS NOT NULL THEN
1623:
1624: dbms_lob.append(l_xml_blob,l_out_create_xml);
1621: dbms_lob.getlength(l_out_create_xml));
1622: -- IF l_xml_string is not NULL and l_out_create_xml IS NOT NULL THEN
1623:
1624: dbms_lob.append(l_xml_blob,l_out_create_xml);
1625: hr_utility.trace('Length of l_xml_blob ' ||dbms_lob.getlength(l_xml_blob));
1626:
1627: /* ELSE
1628: dbms_lob.writeappend(l_xml_string,dbms_lobamount,l_out_create_xml);;*/
1629: -- END IF;
1631: END LOOP;
1632: END IF;
1633: --}
1634: END IF ; /* p_error_msg is null */
1635: hr_utility.trace('XML String is ');
1636:
1637: --hr_utility.trace(dbms_lob.substr(l_xml_string,,1));
1638:
1639: return l_xml_blob;
1633: --}
1634: END IF ; /* p_error_msg is null */
1635: hr_utility.trace('XML String is ');
1636:
1637: --hr_utility.trace(dbms_lob.substr(l_xml_string,,1));
1638:
1639: return l_xml_blob;
1640: EXCEPTION
1641: WHEN OTHERS then
1638:
1639: return l_xml_blob;
1640: EXCEPTION
1641: WHEN OTHERS then
1642: HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1643: END fetch_w2_xml;
1644:
1645: FUNCTION get_final_xml (p_assignment_action_id Number,
1646: p_tax_unit_id NUMBER,
1658: l_is_temp_final_xml VARCHAR2(2);
1659: l_temp_blob BLOB;
1660:
1661: BEGIN
1662: --hr_utility.trace_on(null,'w2');
1663: hr_utility.trace('Deleting PL/SQL tables');
1664: l_state_local_tab.delete;
1665: l_state_tab.delete;
1666: l_local_tab.delete;
1659: l_temp_blob BLOB;
1660:
1661: BEGIN
1662: --hr_utility.trace_on(null,'w2');
1663: hr_utility.trace('Deleting PL/SQL tables');
1664: l_state_local_tab.delete;
1665: l_state_tab.delete;
1666: l_local_tab.delete;
1667: l_box12_tab.delete;
1675: p_tax_unit_id ,
1676: p_year,
1677: p_error_msg , true);
1678:
1679: hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
1680:
1681: IF p_error_msg IS NULL THEN
1682: hr_utility.trace('In final XML p_xml_string ');
1683: /* hr_utility.trace('XML String '||
1678:
1679: hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
1680:
1681: IF p_error_msg IS NULL THEN
1682: hr_utility.trace('In final XML p_xml_string ');
1683: /* hr_utility.trace('XML String '||
1684: dbms_lob.substr(p_xml_string,dbms_lob.getlength(p_xml_string),1));
1685: */
1686: l_final_xml_string :=
1679: hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
1680:
1681: IF p_error_msg IS NULL THEN
1682: hr_utility.trace('In final XML p_xml_string ');
1683: /* hr_utility.trace('XML String '||
1684: dbms_lob.substr(p_xml_string,dbms_lob.getlength(p_xml_string),1));
1685: */
1686: l_final_xml_string :=
1687: -- ''|| EOL|| Bug 6712851
1695: '
1696: -- '
1697:
1698:
1699: hr_utility.trace('1. final 1. XML l_final_xml '||
1700: dbms_lob.substr(l_final_xml,dbms_lob.getlength(l_final_xml),1));
1701:
1702: l_last_xml_string := '
1703: -- '
1709: ''||EOL||
1710: '';
1711:
1712: l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
1713: hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
1714:
1715: IF l_is_temp_final_xml = 1 THEN
1716: DBMS_LOB.FREETEMPORARY(l_final_xml);
1717: END IF;
1720: dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
1721: l_final_xml := append_to_lob(l_final_xml_string);
1722: --dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
1723:
1724: hr_utility.trace('Get Length l_final_xml ' ||dbms_lob.getlength(l_final_xml) );
1725:
1726: dbms_lob.append(l_final_xml,p_xml_blob);
1727:
1728: --dbms_lob.writeappend(l_final_xml,length(l_last_xml_string),l_last_xml_string);
1732: dbms_lob.append(l_final_xml,l_temp_blob);
1733:
1734: /* Added ISOPEN condition for bug 3899583 */
1735: IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
1736: hr_utility.trace('Closing l_final_xml' );
1737: dbms_lob.close(l_final_xml);
1738: END IF;
1739: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1740: hr_utility.trace('Closing p_xml_blob' );
1736: hr_utility.trace('Closing l_final_xml' );
1737: dbms_lob.close(l_final_xml);
1738: END IF;
1739: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1740: hr_utility.trace('Closing p_xml_blob' );
1741: dbms_lob.close(p_xml_blob);
1742: END IF;
1743: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1744: hr_utility.trace('Closing l_temp_blob' );
1740: hr_utility.trace('Closing p_xml_blob' );
1741: dbms_lob.close(p_xml_blob);
1742: END IF;
1743: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1744: hr_utility.trace('Closing l_temp_blob' );
1745: dbms_lob.close(l_temp_blob);
1746: END IF;
1747: ELSE
1748: dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
1748: dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
1749: dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
1750: l_final_xml := append_to_lob(p_error_msg);
1751:
1752: hr_utility.trace(' get final cml, p_error_msg '||p_error_msg);
1753:
1754: END IF ; /* p_error_msg is null */
1755: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
1756:
1751:
1752: hr_utility.trace(' get final cml, p_error_msg '||p_error_msg);
1753:
1754: END IF ; /* p_error_msg is null */
1755: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
1756:
1757: return l_final_xml;
1758: EXCEPTION
1759: WHEN OTHERS then
1758: EXCEPTION
1759: WHEN OTHERS then
1760: /* Added ISOPEN condition for bug 3899583 */
1761: IF dbms_lob.ISOPEN(l_final_xml)=1 THEN
1762: hr_utility.trace('Raising exception and Closing l_final_xml' );
1763: dbms_lob.close(l_final_xml);
1764: END IF;
1765: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1766: hr_utility.trace('Raising exception and Closing p_xml_string' );
1762: hr_utility.trace('Raising exception and Closing l_final_xml' );
1763: dbms_lob.close(l_final_xml);
1764: END IF;
1765: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1766: hr_utility.trace('Raising exception and Closing p_xml_string' );
1767: dbms_lob.close(p_xml_blob);
1768: END IF;
1769: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1770: hr_utility.trace('Closing l_temp_blob' );
1766: hr_utility.trace('Raising exception and Closing p_xml_string' );
1767: dbms_lob.close(p_xml_blob);
1768: END IF;
1769: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1770: hr_utility.trace('Closing l_temp_blob' );
1771: dbms_lob.close(l_temp_blob);
1772: END IF;
1773:
1774:
1771: dbms_lob.close(l_temp_blob);
1772: END IF;
1773:
1774:
1775: HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1776: raise;
1777: END get_final_xml;
1778:
1779:
1822: and ppa1.action_type = 'X'
1823: and ppa1.action_status = 'C'
1824: and ppa1.effective_date = ppa.effective_date;
1825: BEGIN
1826: --hr_utility.trace_on(null,'w2');
1827: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1828: hr_utility.trace('In fetch_w2_xm');
1829:
1830: hr_utility.trace('Deleting PL/SQL tables');
1824: and ppa1.effective_date = ppa.effective_date;
1825: BEGIN
1826: --hr_utility.trace_on(null,'w2');
1827: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1828: hr_utility.trace('In fetch_w2_xm');
1829:
1830: hr_utility.trace('Deleting PL/SQL tables');
1831: l_state_local_tab.delete;
1832: l_state_tab.delete;
1826: --hr_utility.trace_on(null,'w2');
1827: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1828: hr_utility.trace('In fetch_w2_xm');
1829:
1830: hr_utility.trace('Deleting PL/SQL tables');
1831: l_state_local_tab.delete;
1832: l_state_tab.delete;
1833: l_local_tab.delete;
1834: l_box12_tab.delete;
1843: CLOSE c_get_params;
1844:
1845: l_output_location := get_outfile;
1846:
1847: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
1848: hr_utility.trace('l_main_assignment_action_id ' ||l_main_assignment_action_id);
1849: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
1850: hr_utility.trace('l_year ' ||l_year);
1851: hr_utility.trace('l_output_location ' ||l_output_location);
1844:
1845: l_output_location := get_outfile;
1846:
1847: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
1848: hr_utility.trace('l_main_assignment_action_id ' ||l_main_assignment_action_id);
1849: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
1850: hr_utility.trace('l_year ' ||l_year);
1851: hr_utility.trace('l_output_location ' ||l_output_location);
1852: hr_utility.trace('l_instr_template ' ||l_instr_template);
1845: l_output_location := get_outfile;
1846:
1847: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
1848: hr_utility.trace('l_main_assignment_action_id ' ||l_main_assignment_action_id);
1849: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
1850: hr_utility.trace('l_year ' ||l_year);
1851: hr_utility.trace('l_output_location ' ||l_output_location);
1852: hr_utility.trace('l_instr_template ' ||l_instr_template);
1853:
1846:
1847: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
1848: hr_utility.trace('l_main_assignment_action_id ' ||l_main_assignment_action_id);
1849: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
1850: hr_utility.trace('l_year ' ||l_year);
1851: hr_utility.trace('l_output_location ' ||l_output_location);
1852: hr_utility.trace('l_instr_template ' ||l_instr_template);
1853:
1854:
1847: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
1848: hr_utility.trace('l_main_assignment_action_id ' ||l_main_assignment_action_id);
1849: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
1850: hr_utility.trace('l_year ' ||l_year);
1851: hr_utility.trace('l_output_location ' ||l_output_location);
1852: hr_utility.trace('l_instr_template ' ||l_instr_template);
1853:
1854:
1855: lc_emp_blob := fetch_w2_xml(l_assignment_action_id,
1848: hr_utility.trace('l_main_assignment_action_id ' ||l_main_assignment_action_id);
1849: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
1850: hr_utility.trace('l_year ' ||l_year);
1851: hr_utility.trace('l_output_location ' ||l_output_location);
1852: hr_utility.trace('l_instr_template ' ||l_instr_template);
1853:
1854:
1855: lc_emp_blob := fetch_w2_xml(l_assignment_action_id,
1856: l_tax_unit_id,
1855: lc_emp_blob := fetch_w2_xml(l_assignment_action_id,
1856: l_tax_unit_id,
1857: l_year,
1858: l_error_msg, false);
1859: hr_utility.trace('XML String is ');
1860: IF ((dbms_lob.getlength(lc_emp_blob) >0) and (l_error_msg IS NULL) )THEN
1861:
1862: --hr_utility.trace(dbms_lob.substr(lc_emp_blob,dbms_lob.getlength(lc_emp_blob),1));
1863:
1858: l_error_msg, false);
1859: hr_utility.trace('XML String is ');
1860: IF ((dbms_lob.getlength(lc_emp_blob) >0) and (l_error_msg IS NULL) )THEN
1861:
1862: --hr_utility.trace(dbms_lob.substr(lc_emp_blob,dbms_lob.getlength(lc_emp_blob),1));
1863:
1864: l_final_xml_string := /* 6712851 ''|| EOL|| */
1865: '
1866: '
1885: ''||EOL;
1886:
1887:
1888: l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
1889: hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
1890:
1891: IF l_is_temp_final_xml = 1 THEN
1892: DBMS_LOB.FREETEMPORARY(l_final_xml);
1893: END IF;
1911: dbms_lob.append(l_final_xml,l_temp_blob);
1912:
1913: --dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
1914:
1915: hr_utility.trace('Get Length l_final_xml ' ||dbms_lob.getlength(l_final_xml) );
1916:
1917: dbms_lob.append(l_final_xml,lc_emp_blob);
1918:
1919: raw_data:=utl_raw.cast_to_raw(l_last_xml_string);
1931: dbms_lob.append(l_final_xml,l_temp_blob);
1932: --dbms_lob.writeappend(l_final_xml,length(l_last_xml_string),l_last_xml_string);
1933:
1934: IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
1935: hr_utility.trace('Closing l_final_xml' );
1936: dbms_lob.close(l_final_xml);
1937: END IF;
1938: IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
1939: hr_utility.trace('Closing lc_emp_blob' );
1935: hr_utility.trace('Closing l_final_xml' );
1936: dbms_lob.close(l_final_xml);
1937: END IF;
1938: IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
1939: hr_utility.trace('Closing lc_emp_blob' );
1940: dbms_lob.close(lc_emp_blob);
1941: END IF;
1942: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1943: hr_utility.trace('Closing l_temp_blob' );
1939: hr_utility.trace('Closing lc_emp_blob' );
1940: dbms_lob.close(lc_emp_blob);
1941: END IF;
1942: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1943: hr_utility.trace('Closing l_temp_blob' );
1944: dbms_lob.close(l_temp_blob);
1945: END IF;
1946:
1947: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
1943: hr_utility.trace('Closing l_temp_blob' );
1944: dbms_lob.close(l_temp_blob);
1945: END IF;
1946:
1947: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
1948:
1949: pay_core_files.write_to_magtape_lob(l_final_xml);
1950: -- hr_utility.trace('Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
1951:
1946:
1947: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
1948:
1949: pay_core_files.write_to_magtape_lob(l_final_xml);
1950: -- hr_utility.trace('Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
1951:
1952: END IF; /*dbms_lob.getlength(lc_emp_blob) >0*/
1953: -- l_is_temp_xml_string := dbms_lob.istemporary(pay_mag_tape.g_clob_value);
1954:
1957: EXCEPTION
1958: WHEN OTHERS then
1959: /* Added ISOPEN condition for bug 3899583 */
1960: IF dbms_lob.ISOPEN(l_final_xml)=1 THEN
1961: hr_utility.trace('Raising exception and Closing l_final_xml' ||sqlerrm);
1962: dbms_lob.close(l_final_xml);
1963: END IF;
1964: IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
1965: hr_utility.trace('Raising exception and Closing p_xml_string' );
1961: hr_utility.trace('Raising exception and Closing l_final_xml' ||sqlerrm);
1962: dbms_lob.close(l_final_xml);
1963: END IF;
1964: IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
1965: hr_utility.trace('Raising exception and Closing p_xml_string' );
1966: dbms_lob.close(lc_emp_blob);
1967: END IF;
1968: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1969: hr_utility.trace('Closing l_temp_blob' );
1965: hr_utility.trace('Raising exception and Closing p_xml_string' );
1966: dbms_lob.close(lc_emp_blob);
1967: END IF;
1968: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1969: hr_utility.trace('Closing l_temp_blob' );
1970: dbms_lob.close(l_temp_blob);
1971: END IF;
1972:
1973: HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1969: hr_utility.trace('Closing l_temp_blob' );
1970: dbms_lob.close(l_temp_blob);
1971: END IF;
1972:
1973: HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1974:
1975: raise;
1976:
1977: END;
1985: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1986: l_last_xml_string :=
1987: ''||EOL;
1988: l_is_temp_final_xml := dbms_lob.istemporary(l_footer_xml);
1989: hr_utility.trace('Istemporary(l_footer_xml) ' ||l_is_temp_final_xml );
1990:
1991: IF l_is_temp_final_xml = 1 THEN
1992: DBMS_LOB.FREETEMPORARY(l_footer_xml);
1993: END IF;
1995: dbms_lob.createtemporary(l_footer_xml,false,DBMS_LOB.CALL);
1996: dbms_lob.open(l_footer_xml,dbms_lob.lob_readwrite);
1997: dbms_lob.writeappend(l_footer_xml,length(l_last_xml_string),l_last_xml_string);
1998:
1999: hr_utility.trace('In Get footers,Length of length(l_footer_xml) ' ||dbms_lob.getlength(l_footer_xml));
2000:
2001: --dbms_lob.append(pay_mag_tape.g_clob_value,l_footer_xml);
2002: pay_core_files.write_to_magtape_lob(l_last_xml_string);
2003: --pay_core_files.write_to_magtape_lob(dbms_lob.substr(l_footer_xml,dbms_lob.getlength(l_footer_xml),1));
2001: --dbms_lob.append(pay_mag_tape.g_clob_value,l_footer_xml);
2002: pay_core_files.write_to_magtape_lob(l_last_xml_string);
2003: --pay_core_files.write_to_magtape_lob(dbms_lob.substr(l_footer_xml,dbms_lob.getlength(l_footer_xml),1));
2004:
2005: --hr_utility.trace('In Get footers,Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
2006: END;
2007:
2008: PROCEDURE get_headers IS
2009:
2016: -- ''|| EOL|| Bug 6712851
2017: '
2018:
2019: l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
2020: hr_utility.trace('Istemporary(l_final_xml) ' ||l_is_temp_final_xml );
2021:
2022: IF l_is_temp_final_xml = 1 THEN
2023: DBMS_LOB.FREETEMPORARY(l_final_xml);
2024: END IF;
2028: dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
2029: --dbms_lob.append(pay_mag_tape.g_clob_value,l_final_xml);
2030: pay_core_files.write_to_magtape_lob(l_final_xml_string);
2031: --pay_core_files.write_to_magtape_lob(dbms_lob.substr(l_final_xml_string,dbms_lob.getlength(l_final_xml_string),1));
2032: --hr_utility.trace('Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
2033: END;
2034:
2035: function get_outfile return VARCHAR2 is
2036: TEMP_UTL varchar2(512);
2036: TEMP_UTL varchar2(512);
2037: l_log varchar2(100);
2038: l_out varchar2(100);
2039: begin
2040: hr_utility.trace('In get_out_file,g_temp_dir ' ||g_temp_dir );
2041:
2042: if g_temp_dir is null then
2043: -- use first entry of utl_file_dir as the g_temp_dir
2044: select translate(ltrim(value),',',' ')
2057: if (TEMP_UTL is null or g_temp_dir is null ) then
2058: raise no_data_found;
2059: end if;
2060: end if;
2061: hr_utility.trace('In get_out_file,g_temp_dir ' ||g_temp_dir );
2062:
2063: FND_FILE.get_names(l_log,l_out);
2064:
2065: l_out := g_temp_dir ||'/'||l_out;
2062:
2063: FND_FILE.get_names(l_log,l_out);
2064:
2065: l_out := g_temp_dir ||'/'||l_out;
2066: hr_utility.trace('In get_out_file,l_out ' ||l_out );
2067:
2068: return l_out;
2069:
2070: exception