[Home] [Help]
159: lv_sort3 VARCHAR2(60);
160: ld_session_date DATE;
161:
162: BEGIN
163: hr_utility.trace('Entered get_payroll_action_info');
164: ln_tax_unit_id := 0;
165: ln_person_id := 0;
166: ln_asg_set := 0;
167: open c_payroll_action_info(p_payroll_action_id);
177: lv_sort3,
178: ld_session_date;
179: close c_payroll_action_info;
180:
181: hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
182: hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
183: hr_utility.trace('ln_tax_unit_id = '|| to_char(ln_tax_unit_id));
184: hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
185: hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
178: ld_session_date;
179: close c_payroll_action_info;
180:
181: hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
182: hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
183: hr_utility.trace('ln_tax_unit_id = '|| to_char(ln_tax_unit_id));
184: hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
185: hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
186:
179: close c_payroll_action_info;
180:
181: hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
182: hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
183: hr_utility.trace('ln_tax_unit_id = '|| to_char(ln_tax_unit_id));
184: hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
185: hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
186:
187: p_end_date := ld_end_date;
180:
181: hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
182: hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
183: hr_utility.trace('ln_tax_unit_id = '|| to_char(ln_tax_unit_id));
184: hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
185: hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
186:
187: p_end_date := ld_end_date;
188: p_start_date := ld_start_date;
181: hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
182: hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
183: hr_utility.trace('ln_tax_unit_id = '|| to_char(ln_tax_unit_id));
184: hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
185: hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
186:
187: p_end_date := ld_end_date;
188: p_start_date := ld_start_date;
189: p_business_group_id := ln_business_group_id;
195: p_sort_option2 := lv_sort2;
196: p_sort_option3 := lv_sort3;
197: p_session_date := ld_session_date;
198:
199: hr_utility.trace('Leaving get_payroll_action_info');
200:
201: EXCEPTION
202: when others then
203: hr_utility.trace('Error in ' || gv_procedure_name ||
199: hr_utility.trace('Leaving get_payroll_action_info');
200:
201: EXCEPTION
202: when others then
203: hr_utility.trace('Error in ' || gv_procedure_name ||
204: to_char(sqlcode) || '-' || sqlerrm);
205: raise hr_utility.hr_error;
206:
207: END get_payroll_action_info;
201: EXCEPTION
202: when others then
203: hr_utility.trace('Error in ' || gv_procedure_name ||
204: to_char(sqlcode) || '-' || sqlerrm);
205: raise hr_utility.hr_error;
206:
207: END get_payroll_action_info;
208:
209: /******************************************************************
236: ln_agent_tax_unit_id pay_assignment_actions.tax_unit_id%type;
237: ln_year number;
238:
239: BEGIN
240: hr_utility.trace('Entered w2crpt_range_cursor');
241: ln_person_id := 0;
242: ln_asg_set := 0;
243: lv_print := null;
244: hr_utility.trace('p_payroll_action_id = ' ||
240: hr_utility.trace('Entered w2crpt_range_cursor');
241: ln_person_id := 0;
242: ln_asg_set := 0;
243: lv_print := null;
244: hr_utility.trace('p_payroll_action_id = ' ||
245: to_char(p_payroll_action_id));
246:
247: get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
248: ,p_start_date => ld_start_date
260: -- Bug 3601799 - Added condition.
261: IF ln_person_id IS NOT NULL OR ln_asg_set IS NOT NULL THEN
262: ln_year := to_number(to_char(ld_end_date,'YYYY'));
263:
264: hr_utility.trace('Checking for Preprocess Agent GRE setup');
265: hr_us_w2_rep.get_agent_tax_unit_id(ln_business_group_id
266: ,ln_year
267: ,ln_agent_tax_unit_id
268: ,lv_error_mesg ) ;
291: pay_core_utils.push_token('record_name',' ');
292: pay_core_utils.push_token('description',substr(lv_error_mesg,136,45));
293: end if;
294:
295: hr_utility.raise_error;
296:
297: end if;
298:
299:
304: from per_all_assignments_f asg
305: where person_id = ' || ln_person_id ||
306: ' and :p_payroll_action_id is not null ';
307:
308: hr_utility.trace('Range for person_id not null');
309:
310: elsif ln_asg_set is not null then
311:
312: lv_sql_string :=
317: and asgset.assignment_id = paf.assignment_id
318: and asgset.include_or_exclude = ''I''
319: and :payroll_action_id is not null order by paf.person_id';
320:
321: hr_utility.trace('Range for asg_set not null');
322: end if;
323:
324: -- Bug 3601799
325: -- This query string will be executed when for All parameter is passed.
342: and paa.tax_unit_id = ' || ln_tax_unit_id || '
343: and :payroll_action_id is not null
344: order by paa.serial_number';
345:
346: hr_utility.trace('Range for all the persons.');
347: END IF;
348:
349: p_sqlstr := lv_sql_string;
350: hr_utility.trace('p_sqlstr = ' ||p_sqlstr);
346: hr_utility.trace('Range for all the persons.');
347: END IF;
348:
349: p_sqlstr := lv_sql_string;
350: hr_utility.trace('p_sqlstr = ' ||p_sqlstr);
351: hr_utility.trace('Leaving w2crpt_range_cursor');
352: END w2crpt_range_cursor;
353:
354:
347: END IF;
348:
349: p_sqlstr := lv_sql_string;
350: hr_utility.trace('p_sqlstr = ' ||p_sqlstr);
351: hr_utility.trace('Leaving w2crpt_range_cursor');
352: END w2crpt_range_cursor;
353:
354:
355: /************************************************************
541: ,ln_tax_unit_id
542: ,ld_end_date
543: ,ld_start_date);
544:
545: hr_utility.trace('value of p_person_id is:'||p_person_id);
546: hr_utility.trace('value of ln_tax_unit_id is:'||ln_tax_unit_id);
547: hr_utility.trace('value of ld_end_date is:'||ld_end_date);
548: hr_utility.trace('value of ld_start_date is:'||ld_start_date);
549:
542: ,ld_end_date
543: ,ld_start_date);
544:
545: hr_utility.trace('value of p_person_id is:'||p_person_id);
546: hr_utility.trace('value of ln_tax_unit_id is:'||ln_tax_unit_id);
547: hr_utility.trace('value of ld_end_date is:'||ld_end_date);
548: hr_utility.trace('value of ld_start_date is:'||ld_start_date);
549:
550: fetch get_prev_w2c_dtls into lv_report_type
543: ,ld_start_date);
544:
545: hr_utility.trace('value of p_person_id is:'||p_person_id);
546: hr_utility.trace('value of ln_tax_unit_id is:'||ln_tax_unit_id);
547: hr_utility.trace('value of ld_end_date is:'||ld_end_date);
548: hr_utility.trace('value of ld_start_date is:'||ld_start_date);
549:
550: fetch get_prev_w2c_dtls into lv_report_type
551: ,ln_primary_assignment_id
544:
545: hr_utility.trace('value of p_person_id is:'||p_person_id);
546: hr_utility.trace('value of ln_tax_unit_id is:'||ln_tax_unit_id);
547: hr_utility.trace('value of ld_end_date is:'||ld_end_date);
548: hr_utility.trace('value of ld_start_date is:'||ld_start_date);
549:
550: fetch get_prev_w2c_dtls into lv_report_type
551: ,ln_primary_assignment_id
552: ,ln_asg_act_to_lock;
571: /* Create an assignment action for this person */
572: select pay_assignment_actions_s.nextval
573: into ln_w2c_asg_action
574: from dual;
575: hr_utility.trace('New w2c Action = ' || ln_w2c_asg_action);
576:
577: /* Insert into pay_assignment_actions. */
578: hr_nonrun_asact.insact(ln_w2c_asg_action
579: ,ln_primary_assignment_id
593: ,ln_prev_w2c_action_id;
594: if get_interlocked_action%notfound then
595:
596: close get_interlocked_action;
597: hr_utility.raise_error;
598: end if;
599: close get_interlocked_action;
600:
601: if lv_prev_report_type = 'YREND' then
617:
618: /***************************************************************
619: ** Interlock last w2c archive action with current w2c rep action
620: ***************************************************************/
621: hr_utility.trace('Locking Action'||ln_w2c_asg_action);
622: hr_utility.trace('Locked Action' || ln_asg_act_to_lock);
623: hr_nonrun_asact.insint(ln_w2c_asg_action
624: ,ln_asg_act_to_lock);
625:
618: /***************************************************************
619: ** Interlock last w2c archive action with current w2c rep action
620: ***************************************************************/
621: hr_utility.trace('Locking Action'||ln_w2c_asg_action);
622: hr_utility.trace('Locked Action' || ln_asg_act_to_lock);
623: hr_nonrun_asact.insint(ln_w2c_asg_action
624: ,ln_asg_act_to_lock);
625:
626: elsif lv_report_type = 'W-2C PAPER' OR lv_report_type = 'W2C_XML' then
668: /* Create an assignment action for this person */
669: select pay_assignment_actions_s.nextval
670: into ln_w2c_asg_action
671: from dual;
672: hr_utility.trace('New w2c Action = ' || ln_w2c_asg_action);
673:
674: /* Insert into pay_assignment_actions. */
675: hr_nonrun_asact.insact(ln_w2c_asg_action
676: ,ln_primary_assignment_id
689: ,ln_prev_yepp_lock_action
690: ,ln_prev_w2c_action_id;
691: if get_interlocked_action%notfound then
692: close get_interlocked_action;
693: hr_utility.raise_error;
694: end if;
695: close get_interlocked_action;
696:
697: if lv_prev_report_type = 'YREND' then
718:
719: END action_creation;
720:
721: BEGIN
722: hr_utility.trace('Entered action_creation ');
723: ln_assignment_id := 0;
724: ln_tax_unit_id := 0;
725: ln_asg_action_id := 0;
726: ln_primary_assignment_id := 0;
733: ln_set_person_id := 0 ;
734: ln_asg_set := 0 ;
735: lv_message := null;
736:
737: hr_utility.trace('p_payroll_action_id = '|| to_char(p_payroll_action_id));
738: hr_utility.trace('p_start_person_id ='|| to_char(p_start_person_id));
739: hr_utility.trace('p_end_person_id = '|| to_char(p_end_person_id));
740: hr_utility.trace('p_chunk = ' || to_char(p_chunk));
741:
734: ln_asg_set := 0 ;
735: lv_message := null;
736:
737: hr_utility.trace('p_payroll_action_id = '|| to_char(p_payroll_action_id));
738: hr_utility.trace('p_start_person_id ='|| to_char(p_start_person_id));
739: hr_utility.trace('p_end_person_id = '|| to_char(p_end_person_id));
740: hr_utility.trace('p_chunk = ' || to_char(p_chunk));
741:
742: get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
735: lv_message := null;
736:
737: hr_utility.trace('p_payroll_action_id = '|| to_char(p_payroll_action_id));
738: hr_utility.trace('p_start_person_id ='|| to_char(p_start_person_id));
739: hr_utility.trace('p_end_person_id = '|| to_char(p_end_person_id));
740: hr_utility.trace('p_chunk = ' || to_char(p_chunk));
741:
742: get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
743: ,p_start_date => ld_start_date
736:
737: hr_utility.trace('p_payroll_action_id = '|| to_char(p_payroll_action_id));
738: hr_utility.trace('p_start_person_id ='|| to_char(p_start_person_id));
739: hr_utility.trace('p_end_person_id = '|| to_char(p_end_person_id));
740: hr_utility.trace('p_chunk = ' || to_char(p_chunk));
741:
742: get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
743: ,p_start_date => ld_start_date
744: ,p_end_date => ld_end_date
757: action_creation(p_start_person_id);
758:
759: /* ASSIGNMENT SET ID IS NOT NULL */
760: elsif ln_asg_set is not null then
761: hr_utility.trace('Entered Asg Set logic');
762: hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
763: hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
764: hr_utility.trace('End Person ='||to_char(p_end_person_id));
765:
758:
759: /* ASSIGNMENT SET ID IS NOT NULL */
760: elsif ln_asg_set is not null then
761: hr_utility.trace('Entered Asg Set logic');
762: hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
763: hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
764: hr_utility.trace('End Person ='||to_char(p_end_person_id));
765:
766: open c_selected_asg_set(p_start_person_id
759: /* ASSIGNMENT SET ID IS NOT NULL */
760: elsif ln_asg_set is not null then
761: hr_utility.trace('Entered Asg Set logic');
762: hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
763: hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
764: hr_utility.trace('End Person ='||to_char(p_end_person_id));
765:
766: open c_selected_asg_set(p_start_person_id
767: ,p_end_person_id
760: elsif ln_asg_set is not null then
761: hr_utility.trace('Entered Asg Set logic');
762: hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
763: hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
764: hr_utility.trace('End Person ='||to_char(p_end_person_id));
765:
766: open c_selected_asg_set(p_start_person_id
767: ,p_end_person_id
768: ,ln_asg_set);
765:
766: open c_selected_asg_set(p_start_person_id
767: ,p_end_person_id
768: ,ln_asg_set);
769: hr_utility.trace('Opened cusor c_selected_asg_set');
770: loop
771: fetch c_selected_asg_set into ln_set_person_id;
772: if c_selected_asg_set%notfound then
773: hr_utility.trace('No Person found for reporting in this chunk');
769: hr_utility.trace('Opened cusor c_selected_asg_set');
770: loop
771: fetch c_selected_asg_set into ln_set_person_id;
772: if c_selected_asg_set%notfound then
773: hr_utility.trace('No Person found for reporting in this chunk');
774: exit;
775: end if;
776:
777: action_creation(ln_set_person_id);
781:
782: -- Bug 3601799 -- Added this elsif if the report is run for All.
783: /* PERSON ID and ASSIGNMENT SET ID are NULL */
784: elsif ln_person_id is null and ln_asg_set is null then
785: hr_utility.trace('Report run for All persons Logic.');
786: open c_select_all_person(p_start_person_id
787: ,p_end_person_id
788: ,ld_start_date
789: ,ln_business_group_id
787: ,p_end_person_id
788: ,ld_start_date
789: ,ln_business_group_id
790: ,ln_tax_unit_id);
791: hr_utility.trace('Opened cusor c_select_all_person');
792: loop
793: fetch c_select_all_person into ln_person_id;
794: if c_select_all_person%notfound then
795: hr_utility.trace('No Person found for reporting in this chunk.');
791: hr_utility.trace('Opened cusor c_select_all_person');
792: loop
793: fetch c_select_all_person into ln_person_id;
794: if c_select_all_person%notfound then
795: hr_utility.trace('No Person found for reporting in this chunk.');
796: exit;
797: end if;
798:
799: action_creation(ln_person_id);
851: end if;
852:
853: select to_char(ld_end_date,'YYYY') into l_year from dual;
854:
855: hr_utility.trace('Beginning of the sort_action cursor');
856: p_sql_string :=
857: 'select mt.rowid
858: from hr_organization_units hou, hr_locations_all hl,
859: per_periods_of_service pps, per_all_assignments_f paf,
903: ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
904: ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1))';
905:
906: p_sql_length := length(p_sql_string); -- return the length of the string.
907: hr_utility.trace('End of the sort_Action cursor');
908: END sort_action;
909:
910: FUNCTION append_to_lob(p_text in varchar)
911: RETURN BLOB IS
926: raw_data
927: );
928:
929: IF dbms_lob.ISOPEN(temp_blob)=1 THEN
930: hr_utility.trace('Closing temp_lob' );
931: dbms_lob.close(temp_blob);
932: END IF;
933:
934: return temp_blob;
941:
942: IF p_data = '-0-' then
943: l_data := p_data;
944: elsif nvl(p_data,0) <=0 THEN
945: hr_utility.trace('Negative/zero value '||p_data);
946: l_data := '';
947: ELSE
948: l_data := p_data;
949: END IF;
978:
979: PROCEDURE check_prior_local IS
980: BEGIN
981: --{ Check for prior local
982: hr_utility.trace('In check_prior_local,l_prior_local '||l_prior_local);
983: p_write_state := FALSE;
984: IF l_prior_local IS NOT NULL THEN
985:
986: --hr_utility.trace('Statecode of state is LESS than Local state Code and prior local is not null');
982: hr_utility.trace('In check_prior_local,l_prior_local '||l_prior_local);
983: p_write_state := FALSE;
984: IF l_prior_local IS NOT NULL THEN
985:
986: --hr_utility.trace('Statecode of state is LESS than Local state Code and prior local is not null');
987: hr_utility.trace('State Code of current state '||l_state_tab(l_curr_state).state_code_c);
988: -- hr_utility.trace('State Code of current local '||l_local_tab(l_curr_local).state_code);
989: hr_utility.trace('State Code of prior local '||l_local_tab(l_prior_local).state_code_c);
990:
983: p_write_state := FALSE;
984: IF l_prior_local IS NOT NULL THEN
985:
986: --hr_utility.trace('Statecode of state is LESS than Local state Code and prior local is not null');
987: hr_utility.trace('State Code of current state '||l_state_tab(l_curr_state).state_code_c);
988: -- hr_utility.trace('State Code of current local '||l_local_tab(l_curr_local).state_code);
989: hr_utility.trace('State Code of prior local '||l_local_tab(l_prior_local).state_code_c);
990:
991: /* If the state code of prior local is same as current state
984: IF l_prior_local IS NOT NULL THEN
985:
986: --hr_utility.trace('Statecode of state is LESS than Local state Code and prior local is not null');
987: hr_utility.trace('State Code of current state '||l_state_tab(l_curr_state).state_code_c);
988: -- hr_utility.trace('State Code of current local '||l_local_tab(l_curr_local).state_code);
989: hr_utility.trace('State Code of prior local '||l_local_tab(l_prior_local).state_code_c);
990:
991: /* If the state code of prior local is same as current state
992: then move the index of the current state */
985:
986: --hr_utility.trace('Statecode of state is LESS than Local state Code and prior local is not null');
987: hr_utility.trace('State Code of current state '||l_state_tab(l_curr_state).state_code_c);
988: -- hr_utility.trace('State Code of current local '||l_local_tab(l_curr_local).state_code);
989: hr_utility.trace('State Code of prior local '||l_local_tab(l_prior_local).state_code_c);
990:
991: /* If the state code of prior local is same as current state
992: then move the index of the current state */
993: IF (l_state_tab(l_curr_state).state_code_c <>
991: /* If the state code of prior local is same as current state
992: then move the index of the current state */
993: IF (l_state_tab(l_curr_state).state_code_c <>
994: l_local_tab(l_prior_local).state_code_c) THEN
995: hr_utility.trace('State Code of prior local matches current state code so setting p_write_state TRUE');
996: p_write_state := TRUE;
997: ELSE
998: p_write_state := FALSE;
999: END IF;
1005:
1006: PROCEDURE write_state_only IS
1007: BEGIN
1008:
1009: hr_utility.trace('Writing state without local ');
1010:
1011: l_stloc_tcnt := l_state_local_tab.count;
1012: l_state_local_tab(l_stloc_tcnt).state_code_c
1013: := l_state_tab(l_curr_state).state_code_c ;
1038: l_stloc_tcnt := 0;
1039:
1040: LOOP
1041:
1042: hr_utility.trace('l_state_tab.COUNT '||l_state_tab.COUNT);
1043: hr_utility.trace('l_local_tab.COUNT '||l_local_tab.COUNT);
1044: hr_utility.trace('l_curr_state '||l_curr_state);
1045: hr_utility.trace('l_curr_local '||l_curr_local);
1046:
1039:
1040: LOOP
1041:
1042: hr_utility.trace('l_state_tab.COUNT '||l_state_tab.COUNT);
1043: hr_utility.trace('l_local_tab.COUNT '||l_local_tab.COUNT);
1044: hr_utility.trace('l_curr_state '||l_curr_state);
1045: hr_utility.trace('l_curr_local '||l_curr_local);
1046:
1047: EXIT WHEN (l_curr_state > l_state_tab.COUNT and
1040: LOOP
1041:
1042: hr_utility.trace('l_state_tab.COUNT '||l_state_tab.COUNT);
1043: hr_utility.trace('l_local_tab.COUNT '||l_local_tab.COUNT);
1044: hr_utility.trace('l_curr_state '||l_curr_state);
1045: hr_utility.trace('l_curr_local '||l_curr_local);
1046:
1047: EXIT WHEN (l_curr_state > l_state_tab.COUNT and
1048: l_curr_local > l_local_tab.COUNT)
1041:
1042: hr_utility.trace('l_state_tab.COUNT '||l_state_tab.COUNT);
1043: hr_utility.trace('l_local_tab.COUNT '||l_local_tab.COUNT);
1044: hr_utility.trace('l_curr_state '||l_curr_state);
1045: hr_utility.trace('l_curr_local '||l_curr_local);
1046:
1047: EXIT WHEN (l_curr_state > l_state_tab.COUNT and
1048: l_curr_local > l_local_tab.COUNT)
1049: OR (l_curr_state > l_state_tab.COUNT and
1053: OR (l_curr_state IS NULL AND
1054: l_curr_local > l_local_tab.COUNT);
1055:
1056: l_prior_local := l_local_tab.PRIOR(l_curr_local);
1057: hr_utility.trace('l_prior_local '||l_local_tab.PRIOR(l_curr_local));
1058:
1059: IF (l_curr_state IS NOT NULL AND
1060: l_curr_local IS NOT NULL ) AND
1061: (l_curr_state <= l_state_tab.COUNT ) AND
1060: l_curr_local IS NOT NULL ) AND
1061: (l_curr_state <= l_state_tab.COUNT ) AND
1062: (l_curr_local <= l_local_tab.COUNT) THEN
1063:
1064: hr_utility.trace('l_state_tab(l_curr_state).state_code '||l_state_tab(l_curr_state).state_code_c);
1065: hr_utility.trace('l_local_tab(l_curr_local).state_code '||l_local_tab(l_curr_local).state_code_c);
1066: hr_utility.trace('l_curr_state '||l_curr_state);
1067:
1068: IF (l_state_tab(l_curr_state).state_code_c =
1061: (l_curr_state <= l_state_tab.COUNT ) AND
1062: (l_curr_local <= l_local_tab.COUNT) THEN
1063:
1064: hr_utility.trace('l_state_tab(l_curr_state).state_code '||l_state_tab(l_curr_state).state_code_c);
1065: hr_utility.trace('l_local_tab(l_curr_local).state_code '||l_local_tab(l_curr_local).state_code_c);
1066: hr_utility.trace('l_curr_state '||l_curr_state);
1067:
1068: IF (l_state_tab(l_curr_state).state_code_c =
1069: l_local_tab(l_curr_local).state_code_c ) THEN
1062: (l_curr_local <= l_local_tab.COUNT) THEN
1063:
1064: hr_utility.trace('l_state_tab(l_curr_state).state_code '||l_state_tab(l_curr_state).state_code_c);
1065: hr_utility.trace('l_local_tab(l_curr_local).state_code '||l_local_tab(l_curr_local).state_code_c);
1066: hr_utility.trace('l_curr_state '||l_curr_state);
1067:
1068: IF (l_state_tab(l_curr_state).state_code_c =
1069: l_local_tab(l_curr_local).state_code_c ) THEN
1070:
1067:
1068: IF (l_state_tab(l_curr_state).state_code_c =
1069: l_local_tab(l_curr_local).state_code_c ) THEN
1070:
1071: hr_utility.trace('Statecode of state is EQUAL to Local state Code');
1072:
1073: l_stloc_tcnt := l_state_local_tab.count;
1074:
1075: hr_utility.trace('l_state_tab(l_curr_state).state_ein '||l_state_tab(l_curr_state).state_ein_c);
1071: hr_utility.trace('Statecode of state is EQUAL to Local state Code');
1072:
1073: l_stloc_tcnt := l_state_local_tab.count;
1074:
1075: hr_utility.trace('l_state_tab(l_curr_state).state_ein '||l_state_tab(l_curr_state).state_ein_c);
1076: hr_utility.trace('l_state_tab(l_curr_state).state_wages '||l_state_tab(l_curr_state).state_wages_c);
1077: hr_utility.trace('l_state_tab(l_curr_state).state_tax '||l_state_tab(l_curr_state).state_tax_c);
1078: /* Check to see if the state code of prior local is same as current state */
1079:
1072:
1073: l_stloc_tcnt := l_state_local_tab.count;
1074:
1075: hr_utility.trace('l_state_tab(l_curr_state).state_ein '||l_state_tab(l_curr_state).state_ein_c);
1076: hr_utility.trace('l_state_tab(l_curr_state).state_wages '||l_state_tab(l_curr_state).state_wages_c);
1077: hr_utility.trace('l_state_tab(l_curr_state).state_tax '||l_state_tab(l_curr_state).state_tax_c);
1078: /* Check to see if the state code of prior local is same as current state */
1079:
1080: check_prior_local;
1073: l_stloc_tcnt := l_state_local_tab.count;
1074:
1075: hr_utility.trace('l_state_tab(l_curr_state).state_ein '||l_state_tab(l_curr_state).state_ein_c);
1076: hr_utility.trace('l_state_tab(l_curr_state).state_wages '||l_state_tab(l_curr_state).state_wages_c);
1077: hr_utility.trace('l_state_tab(l_curr_state).state_tax '||l_state_tab(l_curr_state).state_tax_c);
1078: /* Check to see if the state code of prior local is same as current state */
1079:
1080: check_prior_local;
1081:
1166: ELSIF (l_state_tab(l_curr_state).state_code_c <
1167: l_local_tab(l_curr_local).state_code_c ) THEN
1168: check_prior_local;
1169: IF p_write_state THEN
1170: hr_utility.trace('current state doesnot match with prior state, so write current state only');
1171: write_state_only;
1172: ELSE
1173: hr_utility.trace('current state matches with prior state, move to next state');
1174: l_curr_state := l_curr_state + 1;
1169: IF p_write_state THEN
1170: hr_utility.trace('current state doesnot match with prior state, so write current state only');
1171: write_state_only;
1172: ELSE
1173: hr_utility.trace('current state matches with prior state, move to next state');
1174: l_curr_state := l_curr_state + 1;
1175: END IF;
1176: --}
1177: ELSE
1174: l_curr_state := l_curr_state + 1;
1175: END IF;
1176: --}
1177: ELSE
1178: hr_utility.trace('Statecode of state is greater than Local state Code');
1179: l_stloc_tcnt := l_state_local_tab.count;
1180:
1181: l_state_local_tab(l_stloc_tcnt).state_code_c
1182: := l_local_tab(l_curr_local).state_code_c ;
1221: ELSIF (l_curr_state IS NULL and l_curr_local IS NOT NULL)
1222: OR (l_curr_state > l_state_tab.COUNT AND
1223: l_curr_local <= l_local_tab.COUNT) THEN
1224:
1225: hr_utility.trace('Current state is null and curr local is NOT null');
1226: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
1227: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality_c);
1228: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax_c);
1229: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages_c);
1222: OR (l_curr_state > l_state_tab.COUNT AND
1223: l_curr_local <= l_local_tab.COUNT) THEN
1224:
1225: hr_utility.trace('Current state is null and curr local is NOT null');
1226: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
1227: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality_c);
1228: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax_c);
1229: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages_c);
1230:
1223: l_curr_local <= l_local_tab.COUNT) THEN
1224:
1225: hr_utility.trace('Current state is null and curr local is NOT null');
1226: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
1227: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality_c);
1228: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax_c);
1229: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages_c);
1230:
1231:
1224:
1225: hr_utility.trace('Current state is null and curr local is NOT null');
1226: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
1227: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality_c);
1228: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax_c);
1229: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages_c);
1230:
1231:
1232: l_stloc_tcnt := l_state_local_tab.count;
1225: hr_utility.trace('Current state is null and curr local is NOT null');
1226: hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
1227: hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality_c);
1228: hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax_c);
1229: hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages_c);
1230:
1231:
1232: l_stloc_tcnt := l_state_local_tab.count;
1233: l_state_local_tab(l_stloc_tcnt).state_code_c
1273: ELSIF (l_curr_state IS NOT NULL and l_curr_local IS NULL)
1274: OR (l_curr_state <= l_state_tab.COUNT AND
1275: l_curr_local > l_local_tab.COUNT) THEN
1276:
1277: hr_utility.trace('Current state is not null and curr local is null');
1278: hr_utility.trace('l_curr_state '||l_curr_state);
1279: hr_utility.trace('l_curr_local '||l_curr_local);
1280:
1281: check_prior_local;
1274: OR (l_curr_state <= l_state_tab.COUNT AND
1275: l_curr_local > l_local_tab.COUNT) THEN
1276:
1277: hr_utility.trace('Current state is not null and curr local is null');
1278: hr_utility.trace('l_curr_state '||l_curr_state);
1279: hr_utility.trace('l_curr_local '||l_curr_local);
1280:
1281: check_prior_local;
1282: IF p_write_state THEN
1275: l_curr_local > l_local_tab.COUNT) THEN
1276:
1277: hr_utility.trace('Current state is not null and curr local is null');
1278: hr_utility.trace('l_curr_state '||l_curr_state);
1279: hr_utility.trace('l_curr_local '||l_curr_local);
1280:
1281: check_prior_local;
1282: IF p_write_state THEN
1283: hr_utility.trace('current state doesnot match with prior state, so write current state only');
1279: hr_utility.trace('l_curr_local '||l_curr_local);
1280:
1281: check_prior_local;
1282: IF p_write_state THEN
1283: hr_utility.trace('current state doesnot match with prior state, so write current state only');
1284: write_state_only;
1285: ELSE
1286: hr_utility.trace('current state matches with prior state, move to next state');
1287: l_curr_state := l_curr_state + 1;
1282: IF p_write_state THEN
1283: hr_utility.trace('current state doesnot match with prior state, so write current state only');
1284: write_state_only;
1285: ELSE
1286: hr_utility.trace('current state matches with prior state, move to next state');
1287: l_curr_state := l_curr_state + 1;
1288:
1289: END IF;
1290: ELSE
1287: l_curr_state := l_curr_state + 1;
1288:
1289: END IF;
1290: ELSE
1291: hr_utility.trace('Completed populating all states and locals');
1292: exit;
1293: END IF;
1294: END LOOP;
1295:
1602:
1603: return(lv_ein);
1604: EXCEPTION
1605: when others then
1606: hr_utility.trace('EIN not found ('||p_jurisdiction||')');
1607: return(null);
1608: END;
1609:
1610: --{ begin get_w2c_data
1618: FETCH c_get_business_group_id
1619: INTO l_business_group_id;
1620: CLOSE c_get_business_group_id;
1621:
1622: hr_utility.trace('Business Group id ' ||l_business_group_id);
1623: hr_utility.trace('TRANSFER_PAYROLL_ACTION_ID ' ||pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1624:
1625: IF l_business_group_id is not null THEN
1626: hr_us_w2_rep.get_agent_tax_unit_id(l_business_group_id
1619: INTO l_business_group_id;
1620: CLOSE c_get_business_group_id;
1621:
1622: hr_utility.trace('Business Group id ' ||l_business_group_id);
1623: hr_utility.trace('TRANSFER_PAYROLL_ACTION_ID ' ||pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1624:
1625: IF l_business_group_id is not null THEN
1626: hr_us_w2_rep.get_agent_tax_unit_id(l_business_group_id
1627: ,p_year
1628: ,l_agent_tax_unit_id
1629: ,l_error_msg);
1630: END IF;
1631:
1632: hr_utility.trace('Agent Tax unit id ' ||l_agent_tax_unit_id);
1633: hr_utility.trace('l_error_msg ' ||l_error_msg);
1634: /* If l_erro_msg is not null then throw error else get remaining data for W2 */
1635: IF l_error_msg IS NOT NULL THEN
1636: p_error_msg := l_error_msg;
1629: ,l_error_msg);
1630: END IF;
1631:
1632: hr_utility.trace('Agent Tax unit id ' ||l_agent_tax_unit_id);
1633: hr_utility.trace('l_error_msg ' ||l_error_msg);
1634: /* If l_erro_msg is not null then throw error else get remaining data for W2 */
1635: IF l_error_msg IS NOT NULL THEN
1636: p_error_msg := l_error_msg;
1637: ELSE
1645: l_w2c_fields_c.employer_address;
1646:
1647: CLOSE c_get_employer_info;
1648:
1649: hr_utility.trace('l_w2c_fields.federal_ein ' ||l_w2c_fields_c.federal_ein);
1650: hr_utility.trace('l_w2c_fields.employer_name ' ||l_w2c_fields_c.employer_name);
1651: hr_utility.trace('l_w2c_fields.employer_name ' ||l_w2c_fields_c.employer_name);
1652:
1653: IF l_agent_tax_unit_id IS NOT NULL THEN
1646:
1647: CLOSE c_get_employer_info;
1648:
1649: hr_utility.trace('l_w2c_fields.federal_ein ' ||l_w2c_fields_c.federal_ein);
1650: hr_utility.trace('l_w2c_fields.employer_name ' ||l_w2c_fields_c.employer_name);
1651: hr_utility.trace('l_w2c_fields.employer_name ' ||l_w2c_fields_c.employer_name);
1652:
1653: IF l_agent_tax_unit_id IS NOT NULL THEN
1654:
1647: CLOSE c_get_employer_info;
1648:
1649: hr_utility.trace('l_w2c_fields.federal_ein ' ||l_w2c_fields_c.federal_ein);
1650: hr_utility.trace('l_w2c_fields.employer_name ' ||l_w2c_fields_c.employer_name);
1651: hr_utility.trace('l_w2c_fields.employer_name ' ||l_w2c_fields_c.employer_name);
1652:
1653: IF l_agent_tax_unit_id IS NOT NULL THEN
1654:
1655: hr_utility.trace('p_tax_unit_id ' ||p_tax_unit_id);
1651: hr_utility.trace('l_w2c_fields.employer_name ' ||l_w2c_fields_c.employer_name);
1652:
1653: IF l_agent_tax_unit_id IS NOT NULL THEN
1654:
1655: hr_utility.trace('p_tax_unit_id ' ||p_tax_unit_id);
1656:
1657: OPEN c_get_employer_info(p_tax_unit_id,p_year);
1658: FETCH c_get_employer_info
1659: INTO l_org_federal_ein,
1659: INTO l_org_federal_ein,
1660: l_org_employer_name,
1661: l_org_address;
1662: CLOSE c_get_employer_info;
1663: hr_utility.trace('l_org_federal_ein ' ||l_org_federal_ein);
1664: hr_utility.trace('l_org_employer_name ' ||l_org_employer_name);
1665: hr_utility.trace('l_org_address ' ||l_org_address);
1666:
1667: l_w2c_fields_c.employer_address
1660: l_org_employer_name,
1661: l_org_address;
1662: CLOSE c_get_employer_info;
1663: hr_utility.trace('l_org_federal_ein ' ||l_org_federal_ein);
1664: hr_utility.trace('l_org_employer_name ' ||l_org_employer_name);
1665: hr_utility.trace('l_org_address ' ||l_org_address);
1666:
1667: l_w2c_fields_c.employer_address
1668: := 'Agent For ' ||substr(l_org_employer_name,1,44)||
1661: l_org_address;
1662: CLOSE c_get_employer_info;
1663: hr_utility.trace('l_org_federal_ein ' ||l_org_federal_ein);
1664: hr_utility.trace('l_org_employer_name ' ||l_org_employer_name);
1665: hr_utility.trace('l_org_address ' ||l_org_address);
1666:
1667: l_w2c_fields_c.employer_address
1668: := 'Agent For ' ||substr(l_org_employer_name,1,44)||
1669: pay_us_w2c_rpt.EOL ||
1670: l_w2c_fields_c.employer_address;
1671:
1672: END IF;
1673: /* Bug 5575567 */
1674: hr_utility.trace(' sysdate ' || sysdate);
1675: hr_utility.trace(' end of year ' || fnd_date.canonical_to_date(p_year||'/12/31'));
1676:
1677: /* Start : Bug # 8353425
1678: Considering the Session Date instead of System Date while fetching employee's
1671:
1672: END IF;
1673: /* Bug 5575567 */
1674: hr_utility.trace(' sysdate ' || sysdate);
1675: hr_utility.trace(' end of year ' || fnd_date.canonical_to_date(p_year||'/12/31'));
1676:
1677: /* Start : Bug # 8353425
1678: Considering the Session Date instead of System Date while fetching employee's
1679: name. The report will now take the employee's current name as of the application
1702: p_effective_date := fnd_date.canonical_to_date(p_year||'/12/31');
1703: ELSE
1704: p_effective_date := trunc(nvl(c_get_session_date_rec.session_date,sysdate)); -- Bug 9073693
1705: END IF;
1706: hr_utility.trace(' p_effective_date ' || p_effective_date);
1707:
1708: OPEN c_get_emp_info(p_asg_action_id,p_tax_unit_id, p_year) ;
1709:
1710: FETCH c_get_emp_info
1731: l_person_id ,
1732: p_assignment_id ;
1733:
1734:
1735: hr_utility.trace('EMP NAME ' ||l_w2c_fields_c.emp_name);
1736: hr_utility.trace('Control Number ' ||l_w2c_fields_c.control_number);
1737: IF c_get_emp_info%NOTFOUND THEN
1738: hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_asg_action_id));
1739: CLOSE c_get_emp_info;
1732: p_assignment_id ;
1733:
1734:
1735: hr_utility.trace('EMP NAME ' ||l_w2c_fields_c.emp_name);
1736: hr_utility.trace('Control Number ' ||l_w2c_fields_c.control_number);
1737: IF c_get_emp_info%NOTFOUND THEN
1738: hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_asg_action_id));
1739: CLOSE c_get_emp_info;
1740: raise NO_DATA_FOUND;
1734:
1735: hr_utility.trace('EMP NAME ' ||l_w2c_fields_c.emp_name);
1736: hr_utility.trace('Control Number ' ||l_w2c_fields_c.control_number);
1737: IF c_get_emp_info%NOTFOUND THEN
1738: hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_asg_action_id));
1739: CLOSE c_get_emp_info;
1740: raise NO_DATA_FOUND;
1741: END IF;
1742: CLOSE c_get_emp_info;
1782: l_person_id ,
1783: p_assignment_id ;
1784:
1785:
1786: hr_utility.trace('EMP NAME ' ||l_w2c_fields_o.emp_name);
1787: hr_utility.trace('Control Number ' ||l_w2c_fields_o.control_number);
1788: IF c_get_emp_info%NOTFOUND THEN
1789: hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_prev_asg_action_id));
1790: CLOSE c_get_emp_info;
1783: p_assignment_id ;
1784:
1785:
1786: hr_utility.trace('EMP NAME ' ||l_w2c_fields_o.emp_name);
1787: hr_utility.trace('Control Number ' ||l_w2c_fields_o.control_number);
1788: IF c_get_emp_info%NOTFOUND THEN
1789: hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_prev_asg_action_id));
1790: CLOSE c_get_emp_info;
1791: raise NO_DATA_FOUND;
1785:
1786: hr_utility.trace('EMP NAME ' ||l_w2c_fields_o.emp_name);
1787: hr_utility.trace('Control Number ' ||l_w2c_fields_o.control_number);
1788: IF c_get_emp_info%NOTFOUND THEN
1789: hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_prev_asg_action_id));
1790: CLOSE c_get_emp_info;
1791: raise NO_DATA_FOUND;
1792: END IF;
1793: CLOSE c_get_emp_info;
1803: select l_first_name||decode(l_middle_name,null,' ',
1804: ' '||substr(l_middle_name,1,1)||' ') ||
1805: hr_us_w2_rep.get_per_item(p_prev_asg_action_id,
1806: 'A_PER_PREFIX' ) into l_w2c_fields_o.emp_name from dual;
1807: hr_utility.trace('EMP NAME2 ' ||l_w2c_fields_o.emp_name);
1808: exception when others then null;
1809: end ;*/
1810:
1811: lr_employee_addr :=
1849: l_state_zero_flag := 'Y' ;
1850: l_sl_total_count := 0; --Starting from 0, for the Bug 16391213
1851: OPEN c_get_state_info(p_asg_action_id) ;
1852: LOOP
1853: hr_utility.trace('In state loop ' );
1854:
1855:
1856: FETCH c_get_state_info
1857: INTO l_dummy,
1973:
1974: l_local_total_count := 0; --Starting from 0, for the Bug 16391213
1975: OPEN c_get_local_info(p_asg_action_id) ;
1976: LOOP
1977: hr_utility.trace('In local loop ' );
1978: FETCH c_get_local_info
1979: INTO l_locality,
1980: l_locality_wages_c,
1981: l_locality_tax_c,
1982: l_jurisdiction,
1983: l_state_code,
1984: l_tax_type;
1985:
1986: hr_utility.trace('l_locality is '||l_locality);
1987: hr_utility.trace('l_locality_wages_c is '||l_locality_wages_c);
1988: hr_utility.trace('l_locality_tax_c is '||l_locality_tax_c);
1989: EXIT WHEN c_get_local_info%NOTFOUND;
1990:
1983: l_state_code,
1984: l_tax_type;
1985:
1986: hr_utility.trace('l_locality is '||l_locality);
1987: hr_utility.trace('l_locality_wages_c is '||l_locality_wages_c);
1988: hr_utility.trace('l_locality_tax_c is '||l_locality_tax_c);
1989: EXIT WHEN c_get_local_info%NOTFOUND;
1990:
1991: OPEN c_get_local_info_o(p_prev_asg_action_id,l_tax_type,l_jurisdiction);
1984: l_tax_type;
1985:
1986: hr_utility.trace('l_locality is '||l_locality);
1987: hr_utility.trace('l_locality_wages_c is '||l_locality_wages_c);
1988: hr_utility.trace('l_locality_tax_c is '||l_locality_tax_c);
1989: EXIT WHEN c_get_local_info%NOTFOUND;
1990:
1991: OPEN c_get_local_info_o(p_prev_asg_action_id,l_tax_type,l_jurisdiction);
1992:
1993: FETCH c_get_local_info_o
1994: INTO l_locality_wages_o,
1995: l_locality_tax_o;
1996:
1997: hr_utility.trace('l_locality_wages_o is '||l_locality_wages_o);
1998: hr_utility.trace('l_locality_tax_o is '||l_locality_tax_o);
1999: IF c_get_local_info_o%NOTFOUND then
2000:
2001: l_local_total_count := l_local_total_count + 1;
1994: INTO l_locality_wages_o,
1995: l_locality_tax_o;
1996:
1997: hr_utility.trace('l_locality_wages_o is '||l_locality_wages_o);
1998: hr_utility.trace('l_locality_tax_o is '||l_locality_tax_o);
1999: IF c_get_local_info_o%NOTFOUND then
2000:
2001: l_local_total_count := l_local_total_count + 1;
2002:
2012: -- l_local_total_count := l_local_total_count + 1;
2013: /* populate the locality table only if the jurisdiction code <> 18-015-000
2014: as this needs to be reported in box 14 as occupational and mental health tax*/
2015: IF ( l_tax_type = 'COUNTY' and l_jurisdiction = '18-015-0000') THEN
2016: hr_utility.trace('Jurisdiction is 18-015-0000 and tax_type is County');
2017: IF (l_locality_tax_c > 0 and l_locality_wages_c > 0)
2018: OR (l_locality_wages_o > 0 and l_locality_tax_o > 0) THEN
2019:
2020: hr_utility.trace('Locality tax withheld > 0 for KY, Boone county');
2016: hr_utility.trace('Jurisdiction is 18-015-0000 and tax_type is County');
2017: IF (l_locality_tax_c > 0 and l_locality_wages_c > 0)
2018: OR (l_locality_wages_o > 0 and l_locality_tax_o > 0) THEN
2019:
2020: hr_utility.trace('Locality tax withheld > 0 for KY, Boone county');
2021:
2022: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
2023:
2024: l_box14_boonmh_value_c := hr_us_w2_rep.get_w2_arch_bal(p_asg_action_id, 'A_MISC1_COUNTY_TAX_WITHHELD_PER_JD_GRE_YTD' ,
2018: OR (l_locality_wages_o > 0 and l_locality_tax_o > 0) THEN
2019:
2020: hr_utility.trace('Locality tax withheld > 0 for KY, Boone county');
2021:
2022: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
2023:
2024: l_box14_boonmh_value_c := hr_us_w2_rep.get_w2_arch_bal(p_asg_action_id, 'A_MISC1_COUNTY_TAX_WITHHELD_PER_JD_GRE_YTD' ,
2025: p_tax_unit_id, '18-015-0000', 6);
2026:
2032: l_box14_tab(l_b14_total_count+1).box14_code_c := 'BOONMH';
2033:
2034: select decode(l_box14_boonmh_value_c,0,'-0-',l_box14_boonmh_value_c) into l_box14_tab(l_b14_total_count+1).box14_meaning_c from dual;
2035:
2036: hr_utility.trace('l_box14_tab(l_b14_total_count).box14_meaning_c '||l_box14_tab(l_b14_total_count+1).box14_meaning_c);
2037:
2038: select decode(l_box14_boonmh_value_o,0,'-0-',l_box14_boonmh_value_o) into l_box14_tab(l_b14_total_count+1).box14_meaning_o from dual;
2039:
2040: hr_utility.trace('l_box14_tab(l_b14_total_count).box14_meaning_o '||l_box14_tab(l_b14_total_count).box14_meaning_o);
2036: hr_utility.trace('l_box14_tab(l_b14_total_count).box14_meaning_c '||l_box14_tab(l_b14_total_count+1).box14_meaning_c);
2037:
2038: select decode(l_box14_boonmh_value_o,0,'-0-',l_box14_boonmh_value_o) into l_box14_tab(l_b14_total_count+1).box14_meaning_o from dual;
2039:
2040: hr_utility.trace('l_box14_tab(l_b14_total_count).box14_meaning_o '||l_box14_tab(l_b14_total_count).box14_meaning_o);
2041:
2042: l_b14_total_count := l_box14_tab.count+1;
2043: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
2044:
2039:
2040: hr_utility.trace('l_box14_tab(l_b14_total_count).box14_meaning_o '||l_box14_tab(l_b14_total_count).box14_meaning_o);
2041:
2042: l_b14_total_count := l_box14_tab.count+1;
2043: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
2044:
2045: l_box14_tab(l_b14_total_count+1).box14_code_c := 'BOONOCC';
2046: l_box14_tab(l_b14_total_count+1).box14_meaning_c :=
2047: l_locality_tax_c - l_box14_boonmh_value_c;
2046: l_box14_tab(l_b14_total_count+1).box14_meaning_c :=
2047: l_locality_tax_c - l_box14_boonmh_value_c;
2048:
2049:
2050: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
2051:
2052: l_box14_tab(l_b14_total_count+1).box14_meaning_o :=
2053: l_local_tab(l_local_total_count+1).locality_wages_o - l_box14_boonmh_value_o;
2054:
2075: l_local_tab(l_local_total_count).jurisdiction_c := l_jurisdiction;
2076: l_local_tab(l_local_total_count).state_code_c := l_state_code;
2077: l_local_tab(l_local_total_count).tax_type_c := l_tax_type;
2078:
2079: hr_utility.trace('Locality_code '|| l_local_tab(l_local_total_count).locality_c);
2080: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code_c);
2081:
2082: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction_c);
2083: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax_c);
2076: l_local_tab(l_local_total_count).state_code_c := l_state_code;
2077: l_local_tab(l_local_total_count).tax_type_c := l_tax_type;
2078:
2079: hr_utility.trace('Locality_code '|| l_local_tab(l_local_total_count).locality_c);
2080: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code_c);
2081:
2082: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction_c);
2083: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax_c);
2084: hr_utility.trace('Locality Tax Type '|| l_local_tab(l_local_total_count).tax_type_c);
2078:
2079: hr_utility.trace('Locality_code '|| l_local_tab(l_local_total_count).locality_c);
2080: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code_c);
2081:
2082: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction_c);
2083: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax_c);
2084: hr_utility.trace('Locality Tax Type '|| l_local_tab(l_local_total_count).tax_type_c);
2085:
2086:
2079: hr_utility.trace('Locality_code '|| l_local_tab(l_local_total_count).locality_c);
2080: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code_c);
2081:
2082: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction_c);
2083: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax_c);
2084: hr_utility.trace('Locality Tax Type '|| l_local_tab(l_local_total_count).tax_type_c);
2085:
2086:
2087: IF (nvl(l_locality_tax_c,0) > 0) OR (nvl(l_locality_tax_o,0) > 0) THEN
2080: hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code_c);
2081:
2082: hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction_c);
2083: hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax_c);
2084: hr_utility.trace('Locality Tax Type '|| l_local_tab(l_local_total_count).tax_type_c);
2085:
2086:
2087: IF (nvl(l_locality_tax_c,0) > 0) OR (nvl(l_locality_tax_o,0) > 0) THEN
2088: IF (l_local_tab(l_local_total_count).tax_type_c = 'CITY SCHOOL' or
2087: IF (nvl(l_locality_tax_c,0) > 0) OR (nvl(l_locality_tax_o,0) > 0) THEN
2088: IF (l_local_tab(l_local_total_count).tax_type_c = 'CITY SCHOOL' or
2089: l_local_tab(l_local_total_count).tax_type_c = 'COUNTY SCHOOL' ) THEN
2090:
2091: hr_utility.trace('Locality Tax Type is County/city school');
2092:
2093: if l_local_tab(l_local_total_count).state_code_c = 'OH' then
2094:
2095: hr_utility.trace('Locality state code is OH');
2091: hr_utility.trace('Locality Tax Type is County/city school');
2092:
2093: if l_local_tab(l_local_total_count).state_code_c = 'OH' then
2094:
2095: hr_utility.trace('Locality state code is OH');
2096:
2097: l_local_tab(l_local_total_count).locality_c
2098: := substr(l_local_tab(l_local_total_count).jurisdiction_c,5,4)
2099: ||' '||substr(l_local_tab(l_local_total_count).locality_c,1,8);
2098: := substr(l_local_tab(l_local_total_count).jurisdiction_c,5,4)
2099: ||' '||substr(l_local_tab(l_local_total_count).locality_c,1,8);
2100:
2101: elsif l_local_tab(l_local_total_count).state_code_c = 'KY' then
2102: hr_utility.trace('Locality state code is KY');
2103: l_local_tab(l_local_total_count).locality_c
2104: := substr(l_local_tab(l_local_total_count).jurisdiction_c,7,2)
2105: ||' '||substr(l_local_tab(l_local_total_count).locality_c,1,10);
2106: else
2103: l_local_tab(l_local_total_count).locality_c
2104: := substr(l_local_tab(l_local_total_count).jurisdiction_c,7,2)
2105: ||' '||substr(l_local_tab(l_local_total_count).locality_c,1,10);
2106: else
2107: hr_utility.trace('Locality state code neither OH nor KY');
2108: l_local_tab(l_local_total_count).locality_c
2109: := substr(l_local_tab(l_local_total_count).jurisdiction_c,4,5)
2110: ||' '||substr(l_local_tab(l_local_total_count).locality_c,1,7);
2111: end if;
2111: end if;
2112: END IF;
2113: END IF;
2114:
2115: hr_utility.trace('l_local_tab(l_local_total_count).locality_c is '||l_local_tab(l_local_total_count).locality_c);
2116: IF (l_local_tab(l_local_total_count).state_code_c = 'IN'
2117: and l_local_tab(l_local_total_count).tax_type_c = 'COUNTY') THEN
2118: BEGIN
2119: select nvl(value,'N') into l_nr_flag
2182: ELSE
2183:
2184: IF NVL(box12_meaning_o,'XX') <> NVL(box12_meaning_c,'XX') then
2185:
2186: hr_utility.trace('In box12 loop '||box12_code_c );
2187:
2188: l_box12_tab(l_b12_total_count+1).box12_code_c := box12_code_c;
2189: l_box12_tab(l_b12_total_count+1).box12_meaning_c := nvl(box12_meaning_c,'-0-');
2190: l_box12_tab(l_b12_total_count+1).box12_meaning_o := nvl(box12_meaning_o,'-0-');
2202: -- l_b14_total_count := l_box14_tab.count;
2203: OPEN c_get_box14_info (p_asg_action_id) ;
2204: LOOP
2205:
2206: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
2207:
2208: FETCH c_get_box14_info
2209: INTO box14_code_c,
2210: box14_meaning_c;
2215: FETCH c_get_box14_info_o
2216: INTO box14_code_c,
2217: box14_meaning_o;
2218:
2219: hr_utility.trace('In box14 loop ' ||box14_code_c);
2220:
2221: IF c_get_box14_info_o%NOTFOUND then
2222:
2223: l_box14_tab(l_b14_total_count+1).box14_code_c := box14_code_c;
2242: CLOSE c_get_box14_info ;
2243:
2244: /* Note: Need to understand the requirement for the state 'HI' and 'NJ', and will modify the code accordingly
2245:
2246: hr_utility.trace('l_locality_tax :' || l_locality_tax );
2247: hr_utility.trace('l_hi_state_printed' || l_hi_state_printed );
2248: If l_hi_state_printed = 'Y' and l_locality_tax > 0 then /* 6519495
2249:
2250: l_b14_total_count := l_box14_tab.count + 1;
2243:
2244: /* Note: Need to understand the requirement for the state 'HI' and 'NJ', and will modify the code accordingly
2245:
2246: hr_utility.trace('l_locality_tax :' || l_locality_tax );
2247: hr_utility.trace('l_hi_state_printed' || l_hi_state_printed );
2248: If l_hi_state_printed = 'Y' and l_locality_tax > 0 then /* 6519495
2249:
2250: l_b14_total_count := l_box14_tab.count + 1;
2251:
2265: l_nj_planid := pay_us_archive_util.get_archive_value(p_asg_action_id,
2266: 'A_SCL_ASG_US_NJ_PLAN_ID', --A_EXTRA_ASSIGNMENT_INFORMATION_PAY_US_DISABILITY_PLAN_INFO_DF_PLAN_ID'
2267: p_tax_unit_id) ;
2268: If l_nj_planid IS NOT NULL then
2269: hr_utility.trace('NJ DIPP plan id: ' || l_nj_planid);
2270: --Bug 7361496 Formatting DI P.P. # for last 10 characters to appear in Employee W-2 PDF
2271: l_nj_planid := substr(l_nj_planid,length(l_nj_planid)-10+1,length(l_nj_planid));
2272: l_b14_total_count := l_box14_tab.count + 1;
2273: l_box14_tab(l_b14_total_count).box14_code := 'DI P.P. # '||l_nj_planid ;
2290: ,nvl(l_w2c_fields.allocated_tips,0)
2291: ,nvl(l_w2c_fields.eic_payment,0)
2292: ,nvl(l_w2c_fields.dependent_care,0)
2293: ,nvl(l_w2c_fields.non_qual_plan,0)) < 0 THEN
2294: hr_utility.trace('Negative values for atleast one of box1-11 ');
2295: p_error_msg := 'Negative values for atleast one of box1-11';
2296: END IF;
2297:
2298:
2295: p_error_msg := 'Negative values for atleast one of box1-11';
2296: END IF;
2297:
2298:
2299: hr_utility.trace('Before 0 check ');
2300: IF nvl(l_w2c_fields.wages_tips_compensation,0)=0 and
2301: nvl(l_w2c_fields.fit_withheld,0)=0 and
2302: nvl(l_w2c_fields.ss_wages,0)=0 and
2303: nvl(l_w2c_fields.ss_withheld,0)=0 and
2308: nvl(l_w2c_fields.eic_payment,0)=0 and
2309: nvl(l_w2c_fields.dependent_care,0)=0 and
2310: nvl(l_w2c_fields.non_qual_plan,0)=0 and l_state_zero_flag = 'Y'
2311: and l_b12_total_count = 0 THEN
2312: hr_utility.trace('Zero values for box1-12 and state wages/withheld');
2313: p_error_msg := 'Zero values for box1-12 and state wages/withheld ';
2314: END IF; */
2315:
2316: END IF; /* l_error_msg is not null */
2346: text_size NUMBER;
2347: raw_data RAW(32767);
2348:
2349: begin
2350: hr_utility.trace('In create XML string ' );
2351: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
2352: IF (g_print_instr IS NULL) OR (g_print_instr = '') THEN
2353: g_print_instr := 'Y';
2354: END IF;
2736: ''||EOL;
2737:
2738:
2739:
2740: hr_utility.trace('one set XML string ' ||l_xml_string);
2741: is_temp := dbms_lob.istemporary(l_xml_blob);
2742: hr_utility.trace('Istemporary(l_xml_blob) ' ||is_temp );
2743:
2744: IF is_temp = 1 THEN
2738:
2739:
2740: hr_utility.trace('one set XML string ' ||l_xml_string);
2741: is_temp := dbms_lob.istemporary(l_xml_blob);
2742: hr_utility.trace('Istemporary(l_xml_blob) ' ||is_temp );
2743:
2744: IF is_temp = 1 THEN
2745: DBMS_LOB.FREETEMPORARY(l_xml_blob);
2746: END IF;
2746: END IF;
2747:
2748: dbms_lob.createtemporary(l_xml_blob,false,DBMS_LOB.CALL);
2749: dbms_lob.open(l_xml_blob,dbms_lob.lob_readwrite);
2750: hr_utility.trace('OPENED l_xml_blob ' );
2751:
2752: raw_data:=utl_raw.cast_to_raw(l_xml_string);
2753: text_size:=utl_raw.length(raw_data);
2754:
2753: text_size:=utl_raw.length(raw_data);
2754:
2755: dbms_lob.writeappend(l_xml_blob,text_size,raw_data);
2756:
2757: hr_utility.trace('Get Length l_xml_clob ' ||dbms_lob.getlength(l_xml_blob) );
2758: dbms_lob.close(l_xml_blob);
2759: return l_xml_blob;
2760: exception
2761: when OTHERS then
2759: return l_xml_blob;
2760: exception
2761: when OTHERS then
2762: dbms_lob.close(l_xml_blob);
2763: hr_utility.trace('sqleerm ' || sqlerrm);
2764: HR_UTILITY.RAISE_ERROR;
2765:
2766: end create_xml_string;
2767: --} end create_xml_string
2760: exception
2761: when OTHERS then
2762: dbms_lob.close(l_xml_blob);
2763: hr_utility.trace('sqleerm ' || sqlerrm);
2764: HR_UTILITY.RAISE_ERROR;
2765:
2766: end create_xml_string;
2767: --} end create_xml_string
2768:
3037:
3038: end compare_data;
3039:
3040: begin
3041: hr_utility.trace('In Fetch w2c xml loop ' );
3042:
3043: l_b14_total_count := 0;
3044: l_b14_count_completed := 0;
3045: l_b12_total_count := 0;
3066: into lv_full_name from sys.dual;
3067:
3068: IF p_error_msg IS NULL THEN
3069:
3070: hr_utility.trace('After get W2c data' );
3071: --Calling the compare_data to compare the data till Box 11
3072:
3073: compare_data(l_w2c_fields_o,l_w2c_fields_c);
3074:
3083: l_state_local_total_count := l_state_local_tab.count;
3084: l_state_local_count_completed := 0;
3085:
3086: l_is_temp_xml_string := dbms_lob.istemporary(l_xml_blob);
3087: hr_utility.trace('Istemporary(l_xml_blob) ' ||l_is_temp_xml_string );
3088:
3089: IF l_is_temp_xml_string = 1 THEN
3090: DBMS_LOB.FREETEMPORARY(l_xml_blob);
3091: END IF;
3099: (l_state_local_total_count = 0) THEN
3100:
3101: IF (g_no_corrections > 0) THEN
3102:
3103: hr_utility.trace('In l_b14_total_count and other counts =0 ' );
3104: -- dbms_lob.append(l_final_xml,p_xml_string);
3105: l_xml_blob := create_xml_string(--l_w2c_fields_o,
3106: l_w2c_fields_c,
3107: l_box14_codea,l_box14_meaninga_c,l_box14_meaninga_o,
3123: l_local2_wages_c,l_local2_tax_c,
3124: l_local2_wages_o,l_local2_tax_o,
3125: l_locality2,
3126: p_year);
3127: hr_utility.trace('after getting XML Blob ' );
3128:
3129: ELSE --Do not create the XML, as there are no corrections, Bug 16571508
3130:
3131: lv_record_name := 'W2C Report';
3144:
3145: --{
3146: LOOP
3147:
3148: hr_utility.trace('In loop to get XML ' );
3149: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
3150: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
3151: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
3152: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
3145: --{
3146: LOOP
3147:
3148: hr_utility.trace('In loop to get XML ' );
3149: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
3150: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
3151: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
3152: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
3153:
3146: LOOP
3147:
3148: hr_utility.trace('In loop to get XML ' );
3149: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
3150: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
3151: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
3152: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
3153:
3154: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
3147:
3148: hr_utility.trace('In loop to get XML ' );
3149: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
3150: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
3151: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
3152: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
3153:
3154: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
3155: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
3148: hr_utility.trace('In loop to get XML ' );
3149: hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
3150: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
3151: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
3152: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
3153:
3154: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
3155: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
3156: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
3150: hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
3151: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
3152: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
3153:
3154: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
3155: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
3156: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
3157: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
3158: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3151: hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
3152: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
3153:
3154: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
3155: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
3156: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
3157: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
3158: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3159: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3152: hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
3153:
3154: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
3155: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
3156: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
3157: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
3158: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3159: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3160: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
3153:
3154: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
3155: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
3156: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
3157: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
3158: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3159: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3160: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
3161:
3154: hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
3155: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
3156: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
3157: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
3158: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3159: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3160: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
3161:
3162: EXIT WHEN
3155: hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
3156: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
3157: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
3158: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3159: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3160: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
3161:
3162: EXIT WHEN
3163: ((l_b14_total_count = 0) AND
3156: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
3157: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
3158: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3159: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3160: hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
3161:
3162: EXIT WHEN
3163: ((l_b14_total_count = 0) AND
3164: (l_b12_total_count = 0 ) AND
3174: l_box14_meaningc_c := '';
3175: l_box14_meaningc_o := '';
3176:
3177: IF l_b14_total_count > 0 THEN
3178: hr_utility.trace('1. l_b14_total_count >0 ' ||l_b14_total_count);
3179: l_b14_count_completed := l_b14_count_completed + 1 ;
3180: hr_utility.trace('1. l_b14_count_completed ' ||l_b14_count_completed);
3181:
3182: l_box14_codea := l_box14_tab(l_b14_count_completed).box14_code_c;
3176:
3177: IF l_b14_total_count > 0 THEN
3178: hr_utility.trace('1. l_b14_total_count >0 ' ||l_b14_total_count);
3179: l_b14_count_completed := l_b14_count_completed + 1 ;
3180: hr_utility.trace('1. l_b14_count_completed ' ||l_b14_count_completed);
3181:
3182: l_box14_codea := l_box14_tab(l_b14_count_completed).box14_code_c;
3183: l_box14_meaninga_c := l_box14_tab(l_b14_count_completed).box14_meaning_c;
3184: l_box14_meaninga_o := l_box14_tab(l_b14_count_completed).box14_meaning_o;
3183: l_box14_meaninga_c := l_box14_tab(l_b14_count_completed).box14_meaning_c;
3184: l_box14_meaninga_o := l_box14_tab(l_b14_count_completed).box14_meaning_o;
3185:
3186: l_b14_total_count := l_b14_total_count -1;
3187: hr_utility.trace('l_box14_codea ' ||l_box14_codea);
3188: hr_utility.trace('l_box14_meaninga_c ' ||l_box14_meaninga_c);
3189: hr_utility.trace('l_box14_meaninga_o ' ||l_box14_meaninga_o);
3190: END IF;
3191:
3184: l_box14_meaninga_o := l_box14_tab(l_b14_count_completed).box14_meaning_o;
3185:
3186: l_b14_total_count := l_b14_total_count -1;
3187: hr_utility.trace('l_box14_codea ' ||l_box14_codea);
3188: hr_utility.trace('l_box14_meaninga_c ' ||l_box14_meaninga_c);
3189: hr_utility.trace('l_box14_meaninga_o ' ||l_box14_meaninga_o);
3190: END IF;
3191:
3192: IF l_b14_total_count > 0 THEN
3185:
3186: l_b14_total_count := l_b14_total_count -1;
3187: hr_utility.trace('l_box14_codea ' ||l_box14_codea);
3188: hr_utility.trace('l_box14_meaninga_c ' ||l_box14_meaninga_c);
3189: hr_utility.trace('l_box14_meaninga_o ' ||l_box14_meaninga_o);
3190: END IF;
3191:
3192: IF l_b14_total_count > 0 THEN
3193: hr_utility.trace('2. l_b14_total_count >0 ' ||l_b14_total_count);
3189: hr_utility.trace('l_box14_meaninga_o ' ||l_box14_meaninga_o);
3190: END IF;
3191:
3192: IF l_b14_total_count > 0 THEN
3193: hr_utility.trace('2. l_b14_total_count >0 ' ||l_b14_total_count);
3194: l_b14_count_completed := l_b14_count_completed + 1 ;
3195: hr_utility.trace('2. l_b14_count_completed ' ||l_b14_count_completed);
3196:
3197: l_box14_codeb := l_box14_tab(l_b14_count_completed).box14_code_c;
3191:
3192: IF l_b14_total_count > 0 THEN
3193: hr_utility.trace('2. l_b14_total_count >0 ' ||l_b14_total_count);
3194: l_b14_count_completed := l_b14_count_completed + 1 ;
3195: hr_utility.trace('2. l_b14_count_completed ' ||l_b14_count_completed);
3196:
3197: l_box14_codeb := l_box14_tab(l_b14_count_completed).box14_code_c;
3198: l_box14_meaningb_c := l_box14_tab(l_b14_count_completed).box14_meaning_c;
3199: l_box14_meaningb_o := l_box14_tab(l_b14_count_completed).box14_meaning_o;
3198: l_box14_meaningb_c := l_box14_tab(l_b14_count_completed).box14_meaning_c;
3199: l_box14_meaningb_o := l_box14_tab(l_b14_count_completed).box14_meaning_o;
3200:
3201: l_b14_total_count := l_b14_total_count - 1;
3202: hr_utility.trace('l_box14_codeb ' ||l_box14_codeb);
3203: hr_utility.trace('l_box14_meaningb_c ' ||l_box14_meaningb_c);
3204: hr_utility.trace('l_box14_meaningb_o ' ||l_box14_meaningb_o);
3205:
3206: END IF;
3199: l_box14_meaningb_o := l_box14_tab(l_b14_count_completed).box14_meaning_o;
3200:
3201: l_b14_total_count := l_b14_total_count - 1;
3202: hr_utility.trace('l_box14_codeb ' ||l_box14_codeb);
3203: hr_utility.trace('l_box14_meaningb_c ' ||l_box14_meaningb_c);
3204: hr_utility.trace('l_box14_meaningb_o ' ||l_box14_meaningb_o);
3205:
3206: END IF;
3207:
3200:
3201: l_b14_total_count := l_b14_total_count - 1;
3202: hr_utility.trace('l_box14_codeb ' ||l_box14_codeb);
3203: hr_utility.trace('l_box14_meaningb_c ' ||l_box14_meaningb_c);
3204: hr_utility.trace('l_box14_meaningb_o ' ||l_box14_meaningb_o);
3205:
3206: END IF;
3207:
3208: IF l_b14_total_count > 0 THEN
3205:
3206: END IF;
3207:
3208: IF l_b14_total_count > 0 THEN
3209: hr_utility.trace('3. l_b14_total_count >0 ' ||l_b14_total_count);
3210:
3211: l_b14_count_completed := l_b14_count_completed + 1 ;
3212: hr_utility.trace('3. l_b14_count_completed ' ||l_b14_count_completed);
3213:
3208: IF l_b14_total_count > 0 THEN
3209: hr_utility.trace('3. l_b14_total_count >0 ' ||l_b14_total_count);
3210:
3211: l_b14_count_completed := l_b14_count_completed + 1 ;
3212: hr_utility.trace('3. l_b14_count_completed ' ||l_b14_count_completed);
3213:
3214: l_box14_codec := l_box14_tab(l_b14_count_completed).box14_code_c;
3215: l_box14_meaningc_c := l_box14_tab(l_b14_count_completed).box14_meaning_c;
3216: l_box14_meaningc_o := l_box14_tab(l_b14_count_completed).box14_meaning_o;
3215: l_box14_meaningc_c := l_box14_tab(l_b14_count_completed).box14_meaning_c;
3216: l_box14_meaningc_o := l_box14_tab(l_b14_count_completed).box14_meaning_o;
3217:
3218: l_b14_total_count := l_b14_total_count -1;
3219: hr_utility.trace('l_box14_codec ' ||l_box14_codec);
3220: hr_utility.trace('l_box14_meaningc_c ' ||l_box14_meaningc_c);
3221: hr_utility.trace('l_box14_meaningc_o ' ||l_box14_meaningc_o);
3222:
3223: END IF;
3216: l_box14_meaningc_o := l_box14_tab(l_b14_count_completed).box14_meaning_o;
3217:
3218: l_b14_total_count := l_b14_total_count -1;
3219: hr_utility.trace('l_box14_codec ' ||l_box14_codec);
3220: hr_utility.trace('l_box14_meaningc_c ' ||l_box14_meaningc_c);
3221: hr_utility.trace('l_box14_meaningc_o ' ||l_box14_meaningc_o);
3222:
3223: END IF;
3224:
3217:
3218: l_b14_total_count := l_b14_total_count -1;
3219: hr_utility.trace('l_box14_codec ' ||l_box14_codec);
3220: hr_utility.trace('l_box14_meaningc_c ' ||l_box14_meaningc_c);
3221: hr_utility.trace('l_box14_meaningc_o ' ||l_box14_meaningc_o);
3222:
3223: END IF;
3224:
3225: l_box12_codea := '';
3235: l_box12_meaningd_c := '';
3236: l_box12_meaningd_o := '';
3237:
3238: IF l_b12_total_count > 0 THEN
3239: hr_utility.trace('1. l_b12_total_count >0 ' ||l_b12_total_count);
3240: l_b12_count_completed := l_b12_count_completed + 1 ;
3241:
3242: l_box12_codea := l_box12_tab(l_b12_count_completed).box12_code_c;
3243: l_box12_meaninga_c := l_box12_tab(l_b12_count_completed).box12_meaning_c;
3246: l_b12_total_count := l_b12_total_count -1;
3247: END IF;
3248:
3249: IF l_b12_total_count > 0 THEN
3250: hr_utility.trace('2. l_b12_total_count >0 ' ||l_b12_total_count);
3251:
3252: l_b12_count_completed := l_b12_count_completed + 1 ;
3253:
3254: l_box12_codeb := l_box12_tab(l_b12_count_completed).box12_code_c;
3258: l_b12_total_count := l_b12_total_count -1;
3259: END IF;
3260:
3261: IF l_b12_total_count > 0 THEN
3262: hr_utility.trace('3. l_b12_total_count >0 ' ||l_b12_total_count);
3263:
3264: l_b12_count_completed := l_b12_count_completed + 1 ;
3265:
3266: l_box12_codec := l_box12_tab(l_b12_count_completed).box12_code_c;
3270: l_b12_total_count := l_b12_total_count -1;
3271: END IF;
3272:
3273: IF l_b12_total_count > 0 THEN
3274: hr_utility.trace('4. l_b12_total_count >0 ' ||l_b12_total_count);
3275:
3276: l_b12_count_completed := l_b12_count_completed + 1 ;
3277:
3278: l_box12_coded := l_box12_tab(l_b12_count_completed).box12_code_c;
3309: l_locality2 := '';
3310:
3311:
3312: IF l_state_local_count_completed < l_state_local_total_count THEN
3313: hr_utility.trace('1. l_state_local_total_count >0 ' ||l_state_local_total_count);
3314:
3315: l_state1_code:= l_state_local_tab(l_state_local_count_completed).state_code_c;
3316:
3317: l_state1_ein_c := l_state_local_tab(l_state_local_count_completed).state_ein_c;
3334:
3335: END IF;
3336:
3337:
3338: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3339: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3340:
3341: IF l_state_local_count_completed < l_state_local_total_count THEN
3342: hr_utility.trace('2. l_state_local_total_count >0 ' ||l_state_local_total_count);
3335: END IF;
3336:
3337:
3338: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3339: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3340:
3341: IF l_state_local_count_completed < l_state_local_total_count THEN
3342: hr_utility.trace('2. l_state_local_total_count >0 ' ||l_state_local_total_count);
3343: hr_utility.trace('2. l_state_local_total_count >0,l_state_local_count_completed ' ||l_state_local_total_count);
3338: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3339: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3340:
3341: IF l_state_local_count_completed < l_state_local_total_count THEN
3342: hr_utility.trace('2. l_state_local_total_count >0 ' ||l_state_local_total_count);
3343: hr_utility.trace('2. l_state_local_total_count >0,l_state_local_count_completed ' ||l_state_local_total_count);
3344:
3345: l_state2_code:= l_state_local_tab(l_state_local_count_completed).state_code_c;
3346:
3339: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3340:
3341: IF l_state_local_count_completed < l_state_local_total_count THEN
3342: hr_utility.trace('2. l_state_local_total_count >0 ' ||l_state_local_total_count);
3343: hr_utility.trace('2. l_state_local_total_count >0,l_state_local_count_completed ' ||l_state_local_total_count);
3344:
3345: l_state2_code:= l_state_local_tab(l_state_local_count_completed).state_code_c;
3346:
3347: l_state2_ein_c := l_state_local_tab(l_state_local_count_completed).state_ein_c;
3363: l_state_local_count_completed := l_state_local_count_completed + 1 ;
3364:
3365: END IF;
3366:
3367: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3368: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3369: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
3370: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
3371:
3364:
3365: END IF;
3366:
3367: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3368: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3369: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
3370: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
3371:
3372:
3365: END IF;
3366:
3367: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3368: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3369: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
3370: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
3371:
3372:
3373: l_w2c_set_cnt := l_w2c_set_cnt +1;
3366:
3367: hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3368: hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3369: hr_utility.trace('l_local_total_count ' ||l_local_total_count);
3370: hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
3371:
3372:
3373: l_w2c_set_cnt := l_w2c_set_cnt +1;
3374: IF l_w2c_set_cnt > 1 THEN
3426: l_local2_wages_o,l_local2_tax_o,
3427: l_locality2,
3428: p_year);
3429:
3430: hr_utility.trace('After l_out_create_xml, length of LOB ' ||
3431: dbms_lob.getlength(l_out_create_xml));
3432: --IF l_xml_string is not NULL and l_out_create_xml IS NOT NULL THEN
3433:
3434: dbms_lob.append(l_xml_blob,l_out_create_xml);
3431: dbms_lob.getlength(l_out_create_xml));
3432: --IF l_xml_string is not NULL and l_out_create_xml IS NOT NULL THEN
3433:
3434: dbms_lob.append(l_xml_blob,l_out_create_xml);
3435: hr_utility.trace('Length of l_xml_blob ' ||dbms_lob.getlength(l_xml_blob));
3436:
3437: --ELSE
3438: -- dbms_lob.writeappend(l_xml_string,dbms_lobamount,l_out_create_xml);
3439: --END IF;
3441: END LOOP;
3442: END IF;
3443: --}
3444: END IF ; /* p_error_msg is null */
3445: hr_utility.trace('XML String is ');
3446:
3447: --hr_utility.trace(dbms_lob.substr(l_xml_string,,1));
3448:
3449: return l_xml_blob;
3443: --}
3444: END IF ; /* p_error_msg is null */
3445: hr_utility.trace('XML String is ');
3446:
3447: --hr_utility.trace(dbms_lob.substr(l_xml_string,,1));
3448:
3449: return l_xml_blob;
3450: EXCEPTION
3451: WHEN OTHERS then
3448:
3449: return l_xml_blob;
3450: EXCEPTION
3451: WHEN OTHERS then
3452: hr_utility.trace('sqleerm ' || SQLERRM);
3453: END fetch_w2c_xml;
3454:
3455: FUNCTION get_final_xml (p_assignment_action_id Number,
3456: p_prev_assignment_action_id Number,
3469: l_is_temp_final_xml VARCHAR2(2);
3470: l_temp_blob BLOB;
3471:
3472: BEGIN
3473: --hr_utility.trace_on(null,'w2');
3474: hr_utility.trace('Deleting PL/SQL tables');
3475:
3476: l_state_local_tab.delete;
3477: l_state_tab.delete;
3470: l_temp_blob BLOB;
3471:
3472: BEGIN
3473: --hr_utility.trace_on(null,'w2');
3474: hr_utility.trace('Deleting PL/SQL tables');
3475:
3476: l_state_local_tab.delete;
3477: l_state_tab.delete;
3478: l_local_tab.delete;
3488: p_tax_unit_id ,
3489: p_year,
3490: p_error_msg , true);
3491:
3492: hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
3493:
3494: IF p_error_msg IS NULL THEN
3495: hr_utility.trace('In final XML p_xml_string ');
3496: /* hr_utility.trace('XML String '||
3491:
3492: hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
3493:
3494: IF p_error_msg IS NULL THEN
3495: hr_utility.trace('In final XML p_xml_string ');
3496: /* hr_utility.trace('XML String '||
3497: dbms_lob.substr(p_xml_string,dbms_lob.getlength(p_xml_string),1));
3498: */
3499: l_final_xml_string :=
3492: hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
3493:
3494: IF p_error_msg IS NULL THEN
3495: hr_utility.trace('In final XML p_xml_string ');
3496: /* hr_utility.trace('XML String '||
3497: dbms_lob.substr(p_xml_string,dbms_lob.getlength(p_xml_string),1));
3498: */
3499: l_final_xml_string :=
3500: -- ''|| EOL|| Bug 6712851
3509: '
3510: -- '
3511:
3512:
3513: hr_utility.trace('1. final 1. XML l_final_xml '||
3514: dbms_lob.substr(l_final_xml,dbms_lob.getlength(l_final_xml),1));
3515:
3516: l_last_xml_string := '
3517: -- '
3524: ''||EOL||
3525: '';
3526:
3527: l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
3528: hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
3529:
3530: IF l_is_temp_final_xml = 1 THEN
3531: DBMS_LOB.FREETEMPORARY(l_final_xml);
3532: END IF;
3535: dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
3536: l_final_xml := append_to_lob(l_final_xml_string);
3537: --dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
3538:
3539: hr_utility.trace('Get Length l_final_xml ' ||dbms_lob.getlength(l_final_xml) );
3540:
3541: dbms_lob.append(l_final_xml,p_xml_blob);
3542:
3543: --dbms_lob.writeappend(l_final_xml,length(l_last_xml_string),l_last_xml_string);
3547: dbms_lob.append(l_final_xml,l_temp_blob);
3548:
3549: /* Added ISOPEN condition for bug 3899583 */
3550: IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
3551: hr_utility.trace('Closing l_final_xml' );
3552: dbms_lob.close(l_final_xml);
3553: END IF;
3554: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
3555: hr_utility.trace('Closing p_xml_blob' );
3551: hr_utility.trace('Closing l_final_xml' );
3552: dbms_lob.close(l_final_xml);
3553: END IF;
3554: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
3555: hr_utility.trace('Closing p_xml_blob' );
3556: dbms_lob.close(p_xml_blob);
3557: END IF;
3558: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3559: hr_utility.trace('Closing l_temp_blob' );
3555: hr_utility.trace('Closing p_xml_blob' );
3556: dbms_lob.close(p_xml_blob);
3557: END IF;
3558: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3559: hr_utility.trace('Closing l_temp_blob' );
3560: dbms_lob.close(l_temp_blob);
3561: END IF;
3562: ELSE
3563: dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
3563: dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
3564: dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
3565: l_final_xml := append_to_lob(p_error_msg);
3566:
3567: hr_utility.trace(' get final cml, p_error_msg '||p_error_msg);
3568:
3569: END IF ; /* p_error_msg is null */
3570: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
3571:
3566:
3567: hr_utility.trace(' get final cml, p_error_msg '||p_error_msg);
3568:
3569: END IF ; /* p_error_msg is null */
3570: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
3571:
3572: return l_final_xml;
3573: EXCEPTION
3574: WHEN OTHERS then
3573: EXCEPTION
3574: WHEN OTHERS then
3575: /* Added ISOPEN condition for bug 3899583 */
3576: IF dbms_lob.ISOPEN(l_final_xml)=1 THEN
3577: hr_utility.trace('Raising exception and Closing l_final_xml' );
3578: dbms_lob.close(l_final_xml);
3579: END IF;
3580: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
3581: hr_utility.trace('Raising exception and Closing p_xml_string' );
3577: hr_utility.trace('Raising exception and Closing l_final_xml' );
3578: dbms_lob.close(l_final_xml);
3579: END IF;
3580: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
3581: hr_utility.trace('Raising exception and Closing p_xml_string' );
3582: dbms_lob.close(p_xml_blob);
3583: END IF;
3584: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3585: hr_utility.trace('Closing l_temp_blob' );
3581: hr_utility.trace('Raising exception and Closing p_xml_string' );
3582: dbms_lob.close(p_xml_blob);
3583: END IF;
3584: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3585: hr_utility.trace('Closing l_temp_blob' );
3586: dbms_lob.close(l_temp_blob);
3587: END IF;
3588:
3589:
3586: dbms_lob.close(l_temp_blob);
3587: END IF;
3588:
3589:
3590: hr_utility.trace('sqleerm ' || SQLERRM);
3591: raise;
3592: END get_final_xml;
3593:
3594:
3629: where ppa.payroll_action_id = paa.payroll_action_id
3630: and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
3631:
3632: BEGIN
3633: --hr_utility.trace_on(null,'w2');
3634: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
3635: hr_utility.trace('In fetch_w2c_xm');
3636:
3637: hr_utility.trace('Deleting PL/SQL tables');
3631:
3632: BEGIN
3633: --hr_utility.trace_on(null,'w2');
3634: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
3635: hr_utility.trace('In fetch_w2c_xm');
3636:
3637: hr_utility.trace('Deleting PL/SQL tables');
3638: l_state_local_tab.delete;
3639: l_state_tab.delete;
3633: --hr_utility.trace_on(null,'w2');
3634: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
3635: hr_utility.trace('In fetch_w2c_xm');
3636:
3637: hr_utility.trace('Deleting PL/SQL tables');
3638: l_state_local_tab.delete;
3639: l_state_tab.delete;
3640: l_local_tab.delete;
3641: l_box12_tab.delete;
3651: CLOSE c_get_params;
3652:
3653: l_output_location := get_outfile;
3654:
3655: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
3656: hr_utility.trace('l_prev_assignment_action_id ' ||l_prev_assignment_action_id);
3657: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
3658: hr_utility.trace('l_year ' ||l_year);
3659: hr_utility.trace('l_output_location ' ||l_output_location);
3652:
3653: l_output_location := get_outfile;
3654:
3655: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
3656: hr_utility.trace('l_prev_assignment_action_id ' ||l_prev_assignment_action_id);
3657: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
3658: hr_utility.trace('l_year ' ||l_year);
3659: hr_utility.trace('l_output_location ' ||l_output_location);
3660: hr_utility.trace('l_instr_template ' ||l_instr_template);
3653: l_output_location := get_outfile;
3654:
3655: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
3656: hr_utility.trace('l_prev_assignment_action_id ' ||l_prev_assignment_action_id);
3657: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
3658: hr_utility.trace('l_year ' ||l_year);
3659: hr_utility.trace('l_output_location ' ||l_output_location);
3660: hr_utility.trace('l_instr_template ' ||l_instr_template);
3661:
3654:
3655: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
3656: hr_utility.trace('l_prev_assignment_action_id ' ||l_prev_assignment_action_id);
3657: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
3658: hr_utility.trace('l_year ' ||l_year);
3659: hr_utility.trace('l_output_location ' ||l_output_location);
3660: hr_utility.trace('l_instr_template ' ||l_instr_template);
3661:
3662:
3655: hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
3656: hr_utility.trace('l_prev_assignment_action_id ' ||l_prev_assignment_action_id);
3657: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
3658: hr_utility.trace('l_year ' ||l_year);
3659: hr_utility.trace('l_output_location ' ||l_output_location);
3660: hr_utility.trace('l_instr_template ' ||l_instr_template);
3661:
3662:
3663: lc_emp_blob := fetch_w2c_xml(l_assignment_action_id,
3656: hr_utility.trace('l_prev_assignment_action_id ' ||l_prev_assignment_action_id);
3657: hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
3658: hr_utility.trace('l_year ' ||l_year);
3659: hr_utility.trace('l_output_location ' ||l_output_location);
3660: hr_utility.trace('l_instr_template ' ||l_instr_template);
3661:
3662:
3663: lc_emp_blob := fetch_w2c_xml(l_assignment_action_id,
3664: l_prev_assignment_action_id,
3664: l_prev_assignment_action_id,
3665: l_tax_unit_id,
3666: l_year,
3667: l_error_msg, false);
3668: hr_utility.trace('XML String is ');
3669: IF ((dbms_lob.getlength(lc_emp_blob) >0) and (l_error_msg IS NULL) )THEN
3670:
3671: --hr_utility.trace(dbms_lob.substr(lc_emp_blob,dbms_lob.getlength(lc_emp_blob),1));
3672:
3667: l_error_msg, false);
3668: hr_utility.trace('XML String is ');
3669: IF ((dbms_lob.getlength(lc_emp_blob) >0) and (l_error_msg IS NULL) )THEN
3670:
3671: --hr_utility.trace(dbms_lob.substr(lc_emp_blob,dbms_lob.getlength(lc_emp_blob),1));
3672:
3673: l_final_xml_string := /* 6712851 ''|| EOL|| */
3674: '
3675: '
3696: ''||EOL;
3697:
3698:
3699: l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
3700: hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
3701:
3702: IF l_is_temp_final_xml = 1 THEN
3703: DBMS_LOB.FREETEMPORARY(l_final_xml);
3704: END IF;
3722: dbms_lob.append(l_final_xml,l_temp_blob);
3723:
3724: --dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
3725:
3726: hr_utility.trace('Get Length l_final_xml ' ||dbms_lob.getlength(l_final_xml) );
3727:
3728: dbms_lob.append(l_final_xml,lc_emp_blob);
3729:
3730: raw_data:=utl_raw.cast_to_raw(l_last_xml_string);
3742: dbms_lob.append(l_final_xml,l_temp_blob);
3743: --dbms_lob.writeappend(l_final_xml,length(l_last_xml_string),l_last_xml_string);
3744:
3745: IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
3746: hr_utility.trace('Closing l_final_xml' );
3747: dbms_lob.close(l_final_xml);
3748: END IF;
3749: IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
3750: hr_utility.trace('Closing lc_emp_blob' );
3746: hr_utility.trace('Closing l_final_xml' );
3747: dbms_lob.close(l_final_xml);
3748: END IF;
3749: IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
3750: hr_utility.trace('Closing lc_emp_blob' );
3751: dbms_lob.close(lc_emp_blob);
3752: END IF;
3753: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3754: hr_utility.trace('Closing l_temp_blob' );
3750: hr_utility.trace('Closing lc_emp_blob' );
3751: dbms_lob.close(lc_emp_blob);
3752: END IF;
3753: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3754: hr_utility.trace('Closing l_temp_blob' );
3755: dbms_lob.close(l_temp_blob);
3756: END IF;
3757:
3758: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
3754: hr_utility.trace('Closing l_temp_blob' );
3755: dbms_lob.close(l_temp_blob);
3756: END IF;
3757:
3758: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
3759:
3760: pay_core_files.write_to_magtape_lob(l_final_xml);
3761: -- hr_utility.trace('Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
3762:
3757:
3758: hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
3759:
3760: pay_core_files.write_to_magtape_lob(l_final_xml);
3761: -- hr_utility.trace('Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
3762:
3763: END IF; /*dbms_lob.getlength(lc_emp_blob) >0*/
3764: -- l_is_temp_xml_string := dbms_lob.istemporary(pay_mag_tape.g_clob_value);
3765:
3768: EXCEPTION
3769: WHEN OTHERS then
3770: /* Added ISOPEN condition for bug 3899583 */
3771: IF dbms_lob.ISOPEN(l_final_xml)=1 THEN
3772: hr_utility.trace('Raising exception and Closing l_final_xml' ||sqlerrm);
3773: dbms_lob.close(l_final_xml);
3774: END IF;
3775: IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
3776: hr_utility.trace('Raising exception and Closing p_xml_string' );
3772: hr_utility.trace('Raising exception and Closing l_final_xml' ||sqlerrm);
3773: dbms_lob.close(l_final_xml);
3774: END IF;
3775: IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
3776: hr_utility.trace('Raising exception and Closing p_xml_string' );
3777: dbms_lob.close(lc_emp_blob);
3778: END IF;
3779: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3780: hr_utility.trace('Closing l_temp_blob' );
3776: hr_utility.trace('Raising exception and Closing p_xml_string' );
3777: dbms_lob.close(lc_emp_blob);
3778: END IF;
3779: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3780: hr_utility.trace('Closing l_temp_blob' );
3781: dbms_lob.close(l_temp_blob);
3782: END IF;
3783:
3784: hr_utility.trace('sqleerm ' || SQLERRM);
3780: hr_utility.trace('Closing l_temp_blob' );
3781: dbms_lob.close(l_temp_blob);
3782: END IF;
3783:
3784: hr_utility.trace('sqleerm ' || SQLERRM);
3785:
3786: raise;
3787:
3788: END;
3796: EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
3797: l_last_xml_string :=
3798: ''||EOL;
3799: l_is_temp_final_xml := dbms_lob.istemporary(l_footer_xml);
3800: hr_utility.trace('Istemporary(l_footer_xml) ' ||l_is_temp_final_xml );
3801:
3802: IF l_is_temp_final_xml = 1 THEN
3803: DBMS_LOB.FREETEMPORARY(l_footer_xml);
3804: END IF;
3806: dbms_lob.createtemporary(l_footer_xml,false,DBMS_LOB.CALL);
3807: dbms_lob.open(l_footer_xml,dbms_lob.lob_readwrite);
3808: dbms_lob.writeappend(l_footer_xml,length(l_last_xml_string),l_last_xml_string);
3809:
3810: hr_utility.trace('In Get footers,Length of length(l_footer_xml) ' ||dbms_lob.getlength(l_footer_xml));
3811:
3812: --dbms_lob.append(pay_mag_tape.g_clob_value,l_footer_xml);
3813: pay_core_files.write_to_magtape_lob(l_last_xml_string);
3814: --pay_core_files.write_to_magtape_lob(dbms_lob.substr(l_footer_xml,dbms_lob.getlength(l_footer_xml),1));
3812: --dbms_lob.append(pay_mag_tape.g_clob_value,l_footer_xml);
3813: pay_core_files.write_to_magtape_lob(l_last_xml_string);
3814: --pay_core_files.write_to_magtape_lob(dbms_lob.substr(l_footer_xml,dbms_lob.getlength(l_footer_xml),1));
3815:
3816: --hr_utility.trace('In Get footers,Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
3817: END;
3818:
3819: PROCEDURE get_headers IS
3820:
3827: -- ''|| EOL|| Bug 6712851
3828: '
3829:
3830: l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
3831: hr_utility.trace('Istemporary(l_final_xml) ' ||l_is_temp_final_xml );
3832:
3833: IF l_is_temp_final_xml = 1 THEN
3834: DBMS_LOB.FREETEMPORARY(l_final_xml);
3835: END IF;
3839: dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
3840: --dbms_lob.append(pay_mag_tape.g_clob_value,l_final_xml);
3841: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3842: --pay_core_files.write_to_magtape_lob(dbms_lob.substr(l_final_xml_string,dbms_lob.getlength(l_final_xml_string),1));
3843: --hr_utility.trace('Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
3844: END;
3845:
3846: function get_outfile return VARCHAR2 is
3847: TEMP_UTL varchar2(512);
3847: TEMP_UTL varchar2(512);
3848: l_log varchar2(100);
3849: l_out varchar2(100);
3850: begin
3851: hr_utility.trace('In get_out_file,g_temp_dir ' ||g_temp_dir );
3852:
3853: if g_temp_dir is null then
3854: -- use first entry of utl_file_dir as the g_temp_dir
3855: select translate(ltrim(value),',',' ')
3868: if (TEMP_UTL is null or g_temp_dir is null ) then
3869: raise no_data_found;
3870: end if;
3871: end if;
3872: hr_utility.trace('In get_out_file,g_temp_dir ' ||g_temp_dir );
3873:
3874: FND_FILE.get_names(l_log,l_out);
3875:
3876: l_out := g_temp_dir ||'/'||l_out;
3873:
3874: FND_FILE.get_names(l_log,l_out);
3875:
3876: l_out := g_temp_dir ||'/'||l_out;
3877: hr_utility.trace('In get_out_file,l_out ' ||l_out );
3878:
3879: return l_out;
3880:
3881: exception
3885: return null;
3886: end get_outfile;
3887:
3888: BEGIN
3889: -- hr_utility.trace_on(null,'W2CRPT');
3890: gv_package := 'pay_us_w2c_rpt';
3891:
3892: END pay_us_w2c_rpt;