[Home] [Help]
1: PACKAGE BODY pay_us_1099r_mag_reporting AS
2: /* $Header: pyyep99r.pkb 120.3 2006/03/16 22:38:40 pragupta noship $ */
3: /*
4: ******************************************************************
5: * *
16: * 500 Oracle Parkway, Redwood City, CA, 94065. *
17: * *
18: ******************************************************************
19:
20: Name : pay_us_1099r_mag_reporting
21:
22: Description : Generate 1099R end of year magnetic reports according to
23: US legislative requirements.
24:
232: lv_leg_param pay_payroll_actions.legislative_parameters%type;
233:
234: begin
235:
236: hr_utility.trace('Entering pay_us_1099r_mag_reporting.get_selection_information');
237:
238: open c_sel;
239: fetch c_sel into ld_year_start, ld_year_end, ln_business_group_id,
240: lv_report_qualifier, lv_report_type;
238: open c_sel;
239: fetch c_sel into ld_year_start, ld_year_end, ln_business_group_id,
240: lv_report_qualifier, lv_report_type;
241:
242: hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',10);
243:
244: if c_sel%notfound then
245:
246: hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',20);
242: hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',10);
243:
244: if c_sel%notfound then
245:
246: hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',20);
247: hr_utility.set_message(801, 'PAY_ARCH_GRE_NOT_FOUND');
248: hr_utility.raise_error;
249:
250: end if;
250: end if;
251:
252: close c_sel;
253:
254: hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',30);
255:
256: if lv_report_qualifier = 'FED' then
257:
258: lv_state_code := ' ';
266: lv_report_type := '1099R_STATE';
267:
268: end if;
269:
270: hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',40);
271:
272: -- To get the tax unit id from legislative parameters
273: -- Note: The below get_parameter_value returns varchar2 so when we assign lv_tax_unit_id
274: -- to p_tax_unit_id we convert it to a number below.
282: where ppa.payroll_action_id = p_payroll_action_id;
283:
284: lv_tax_unit_id := get_parameter('TRANSFER_TRANS_LEGAL_CO_ID', lv_leg_param);
285:
286: hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',50);
287: hr_utility.trace('The tax unit id is : '||lv_tax_unit_id);
288:
289: -- To get the transmitter control code for the specific tax unit id
290:
291: open tcc_1099R_cur(lv_tax_unit_id, ln_business_group_id);
292: fetch tcc_1099R_cur into lv_trans_cont_code;
293: if tcc_1099R_cur%notfound then
294:
295: hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',60);
296: hr_utility.set_message(801, 'PAY_ARCH_GRE_NOT_FOUND');
297: hr_utility.raise_error;
298: end if;
299: close tcc_1099R_cur;
306: if c_yrend_ppa %notfound then
307:
308: hr_utility.trace('Payroll action id: '||to_char(p_payroll_action_id));
309: hr_utility.trace('Tax unit id: '||lv_tax_unit_id);
310: hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',65);
311: hr_utility.set_message(801, 'PAY_ARCH_GRE_NOT_FOUND');
312: hr_utility.raise_error;
313: end if;
314:
323: p_tax_unit_id := to_number(lv_tax_unit_id);
324: p_trans_cont_code := lv_trans_cont_code;
325: p_yrend_ppa_id := ln_yrend_ppa_id;
326:
327: hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',70);
328: hr_utility.trace('The year start from get_selection_information is: '||to_char(ld_year_start));
329: hr_utility.trace('The year end from get_selection_information is: '||to_char(ld_year_end));
330: hr_utility.trace('The state code from get_selection_information is: '||lv_state_code);
331: hr_utility.trace('The state abbrev from get_selection_information is: '||lv_report_qualifier);
334: hr_utility.trace('The tax unit id from get_selection_information is: '||lv_tax_unit_id);
335: hr_utility.trace('The transmitter control code from get_selection_information is: '||lv_trans_cont_code);
336: hr_utility.trace('The year end payroll action id from get_selection_information is: '||to_char(ln_yrend_ppa_id));
337:
338: hr_utility.trace('Exiting pay_us_1099r_mag_reporting.get_selection_information');
339:
340: end get_selection_information;
341:
342:
366: and ue.creator_type = 'B';
367:
368: begin
369:
370: hr_utility.trace('Entering pay_us_1099r_mag_reporting.get_balance_value');
371:
372: hr_utility.set_location ('pay_us_1099r_mag_reporting.get_balance_value', 10);
373:
374: open c_defined_balance(p_balance_name);
368: begin
369:
370: hr_utility.trace('Entering pay_us_1099r_mag_reporting.get_balance_value');
371:
372: hr_utility.set_location ('pay_us_1099r_mag_reporting.get_balance_value', 10);
373:
374: open c_defined_balance(p_balance_name);
375: fetch c_defined_balance into ln_defined_balance_id;
376: close c_defined_balance;
382: fetch c_jurisdiction into lv_jurisdiction_code;
383: close c_jurisdiction;
384:
385: hr_utility.set_location
386: ('pay_us_1099r_mag_reporting.get_balance_value', 15);
387:
388: pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
389: end if;
390:
392: hr_utility.trace('Context');
393: hr_utility.trace('Tax Unit Id: '|| p_tax_unit_id);
394: hr_utility.trace('Jurisdiction: '|| lv_jurisdiction_code);
395: hr_utility.set_location
396: ('pay_us_1099r_mag_reporting.get_balance_value', 20);
397:
398: ln_balance_value := pay_balance_pkg.get_value
399: (ln_defined_balance_id,
400: p_assignment_id, p_effective_date);
398: ln_balance_value := pay_balance_pkg.get_value
399: (ln_defined_balance_id,
400: p_assignment_id, p_effective_date);
401:
402: hr_utility.trace('Exiting pay_us_1099r_mag_reporting.get_balance_value');
403:
404: return (ln_balance_value);
405:
406:
609: begin
610:
611: /* First check if the transmitter contact information is there for the 'T' record */
612: -- hr_utility.trace_on(NULL, 'MAGR');
613: hr_utility.trace('Entering the pay_us_1099r_mag_reporting.preprocess_check');
614:
615: -- Initialization
616: ln_curr_gre := -9999;
617: lv_message_preprocess := 'Pre-Process check';
618:
619: open c_chk_cntc_info(p_trans_cont_code, p_tax_unit_id);
620: fetch c_chk_cntc_info into lv_contact_chk;
621: if c_chk_cntc_info%NOTFOUND then
622: hr_utility.set_location( 'pay_us_1099r_mag_reporting.preprocess_check', 20);
623: hr_utility.set_message(801, 'PAY_72837_TRSMTR_CNT_NOT_FND');
624: close c_chk_cntc_info;
625: hr_utility.raise_error;
626: end if;
624: close c_chk_cntc_info;
625: hr_utility.raise_error;
626: end if;
627: close c_chk_cntc_info; -- The 'T' record contact information is
628: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',10);
629:
630:
631: /* Check if the transmitter vendor Information exists for 'T' record */
632: open c_chk_vnd_type(p_trans_cont_code, p_tax_unit_id);
633: fetch c_chk_vnd_type into lv_vendor_type;
634: if lv_vendor_type = 'Y' THEN
635: open c_chk_vnd_info(p_trans_cont_code, p_tax_unit_id);
636: fetch c_chk_vnd_info into lv_vendor_chk;
637: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',23);
638: if c_chk_vnd_info%NOTFOUND then
639: hr_utility.set_location( 'pay_us_1099r_mag_reporting.preprocess_check', 24);
640: hr_utility.set_message(801, 'PAY_34980_TRSMTR_VND_NOT_FOUND');
641: CLOSE c_chk_vnd_info;
635: open c_chk_vnd_info(p_trans_cont_code, p_tax_unit_id);
636: fetch c_chk_vnd_info into lv_vendor_chk;
637: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',23);
638: if c_chk_vnd_info%NOTFOUND then
639: hr_utility.set_location( 'pay_us_1099r_mag_reporting.preprocess_check', 24);
640: hr_utility.set_message(801, 'PAY_34980_TRSMTR_VND_NOT_FOUND');
641: CLOSE c_chk_vnd_info;
642: close c_chk_vnd_type;
643: hr_utility.raise_error;
645: close c_chk_vnd_info; -- The 'T' record Vendor information exists.
646: end if;
647: close c_chk_vnd_type;
648:
649: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',30);
650:
651: -- Performance Changes
652: -- Check and see if we have any GREs(within the tcc) which have not been processed
653: -- Open the cursor which will give us all 1099R GREs for a particular
657: open c_1099_fed_gre(p_trans_cont_code);
658:
659: loop -- Main Loop
660: hr_utility.trace('The previous GRE was : '||to_char(ln_curr_gre));
661: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',70);
662: fetch c_1099_fed_gre into ln_curr_gre, lv_transmitter_flag;
663: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',80);
664: hr_utility.trace('The GRE being checked is: '||to_char(ln_curr_gre));
665: if c_1099_fed_gre%NOTFOUND THEN
659: loop -- Main Loop
660: hr_utility.trace('The previous GRE was : '||to_char(ln_curr_gre));
661: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',70);
662: fetch c_1099_fed_gre into ln_curr_gre, lv_transmitter_flag;
663: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',80);
664: hr_utility.trace('The GRE being checked is: '||to_char(ln_curr_gre));
665: if c_1099_fed_gre%NOTFOUND THEN
666: -- This means that the there are no more rows in the cursor
667: -- So lets get out of the loop and continue
664: hr_utility.trace('The GRE being checked is: '||to_char(ln_curr_gre));
665: if c_1099_fed_gre%NOTFOUND THEN
666: -- This means that the there are no more rows in the cursor
667: -- So lets get out of the loop and continue
668: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',81);
669: exit;
670: end if; -- if c_1099_fed_gre%NOTFOUND
671:
672: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',120);
668: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',81);
669: exit;
670: end if; -- if c_1099_fed_gre%NOTFOUND
671:
672: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',120);
673:
674: -- At this point we have the GREs for both Federal and State,
675: -- we do not know if they have been
676: -- archived or not yet. All we know is that they exist.
677: -- Lets see if the GRE has been archived we will set a flag to
678: -- determine if it has been archived or not
679: open c_payroll_action (ln_curr_gre, p_year_start, p_year_end, p_business_group_id);
680: fetch c_payroll_action into ln_payroll_action_id;
681: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',130);
682: if c_payroll_action%notfound then
683: lv_gre_archive := 'N'; -- it has not been archived
684: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',140);
685: else
680: fetch c_payroll_action into ln_payroll_action_id;
681: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',130);
682: if c_payroll_action%notfound then
683: lv_gre_archive := 'N'; -- it has not been archived
684: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',140);
685: else
686: lv_gre_archive := 'Y'; -- it has been archived
687: ln_no_of_gres_picked := ln_no_of_gres_picked + 1;
688: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',150);
684: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',140);
685: else
686: lv_gre_archive := 'Y'; -- it has been archived
687: ln_no_of_gres_picked := ln_no_of_gres_picked + 1;
688: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',150);
689: end if;
690: close c_payroll_action;
691:
692: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',190);
688: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',150);
689: end if;
690: close c_payroll_action;
691:
692: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',190);
693:
694: -- Now if the gre has been archived lets verify that none of the assignments
695: -- have errored and none are marked for retry.
696: -- If they have been marked for error or retry then set a flag
701: open c_check_asg(ln_payroll_action_id, 'R');
702: fetch c_check_asg into lc_asgn_retry;
703: if c_check_asg%found then
704: lc_asgn_retry := 'Y';
705: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',200);
706: end if;
707: close c_check_asg;
708:
709: open c_check_asg(ln_payroll_action_id, 'E');
709: open c_check_asg(ln_payroll_action_id, 'E');
710: fetch c_check_asg into lc_asgn_error;
711: if c_check_asg%found then
712: lc_asgn_error := 'Y';
713: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',210);
714: end if;
715: close c_check_asg;
716:
717: -- If the flag for retry and or error is set to 'Y' then we must
716:
717: -- If the flag for retry and or error is set to 'Y' then we must
718: -- close the cursor and get out
719: if lc_asgn_error = 'Y' then
720: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',220);
721: close c_1099_fed_gre;
722: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check', 230);
723: hr_utility.set_message(801, 'PAY_72729_ASG_NOT_ARCH');
724: hr_utility.raise_error;
718: -- close the cursor and get out
719: if lc_asgn_error = 'Y' then
720: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',220);
721: close c_1099_fed_gre;
722: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check', 230);
723: hr_utility.set_message(801, 'PAY_72729_ASG_NOT_ARCH');
724: hr_utility.raise_error;
725: elsif lc_asgn_retry = 'Y' then
726: close c_1099_fed_gre;
723: hr_utility.set_message(801, 'PAY_72729_ASG_NOT_ARCH');
724: hr_utility.raise_error;
725: elsif lc_asgn_retry = 'Y' then
726: close c_1099_fed_gre;
727: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check', 240);
728: hr_utility.set_message(801, 'PAY_72730_ASG_MARKED_FOR_RETRY');
729: hr_utility.raise_error;
730: end if; -- if lc_asgn_error = 'Y'
731: ELSE
738: pay_core_utils.push_token('description',lv_message_text);
739: */
740: END IF; -- IF lv_gre_archive = 'Y'
741:
742: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',410);
743: END LOOP; --Main Loop
744:
745: -- This is in the scenario if a customer runs the 1099R mag for a/or
746: -- many GREs that are not archived nor should they be archived, but since
764: end if;
765:
766: close c_1099_fed_gre;
767:
768: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',420);
769: hr_utility.trace('Exiting the pay_us_1099r_mag_reporting.preprocess_check');
770: return(TRUE);
771:
772: end preprocess_check;
765:
766: close c_1099_fed_gre;
767:
768: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',420);
769: hr_utility.trace('Exiting the pay_us_1099r_mag_reporting.preprocess_check');
770: return(TRUE);
771:
772: end preprocess_check;
773:
791: lb_pre_process boolean;
792:
793: begin
794: -- hr_utility.trace_on(NULL,'oracle');
795: hr_utility.trace('Entering pay_us_1099r_mag_reporting.range_cursor');
796: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',10);
797:
798: get_selection_information (
799: p_payroll_action_id,
792:
793: begin
794: -- hr_utility.trace_on(NULL,'oracle');
795: hr_utility.trace('Entering pay_us_1099r_mag_reporting.range_cursor');
796: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',10);
797:
798: get_selection_information (
799: p_payroll_action_id,
800: ld_year_start,
806: ln_tax_unit_id,
807: lv_trans_cont_code,
808: ln_yrend_ppa_id);
809:
810: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',20);
811:
812: lb_pre_process := preprocess_check (
813: p_payroll_action_id,
814: ld_year_start,
819: lv_report_type,
820: ln_tax_unit_id,
821: lv_trans_cont_code);
822:
823: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',30);
824:
825: if lb_pre_process then
826:
827: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',40);
823: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',30);
824:
825: if lb_pre_process then
826:
827: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',40);
828:
829: if lv_report_type = '1099R_FED' then
830:
831: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',50);
827: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',40);
828:
829: if lv_report_type = '1099R_FED' then
830:
831: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',50);
832: hr_utility.trace('The transmitter control code is: '||lv_trans_cont_code);
833:
834: lv_sql_string :=
835: 'select distinct paf.person_id
858: from hr_organization_information hoi
859: where hoi.org_information_context = ''1099R Magnetic Report Rules'')
860: order by paf.person_id';
861:
862: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor', 60);
863:
864: elsif lv_report_type = '1099R_STATE' then
865:
866: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',70);
862: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor', 60);
863:
864: elsif lv_report_type = '1099R_STATE' then
865:
866: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',70);
867:
868: lv_sql_string :=
869: 'select distinct paf.person_id
870: from --hr_soft_coding_keyflex hsck,
898: from hr_organization_information hoi
899: where hoi.org_information_context = ''1099R Magnetic Report Rules'')
900: order by paf.person_id';
901:
902: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor', 80);
903:
904: end if;
905:
906: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',90);
902: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor', 80);
903:
904: end if;
905:
906: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',90);
907:
908: p_sql_string := lv_sql_string;
909:
910: end if;
908: p_sql_string := lv_sql_string;
909:
910: end if;
911:
912: hr_utility.trace('Exiting pay_us_1099r_mag_reporting.range_cursor');
913:
914: end range_cursor;
915:
916:
1094: ln_create_assignment number;
1095:
1096: begin
1097:
1098: hr_utility.trace('Entering pay_us_1099r_mag_reporting.mag_1099r_action_creation');
1099:
1100: -- Get the report parameters. These define the report being run.
1101: hr_utility.set_location(
1102: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation',10);
1098: hr_utility.trace('Entering pay_us_1099r_mag_reporting.mag_1099r_action_creation');
1099:
1100: -- Get the report parameters. These define the report being run.
1101: hr_utility.set_location(
1102: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation',10);
1103:
1104: get_selection_information (
1105: p_payroll_action_id,
1106: ld_year_start,
1117:
1118: --Open the appropriate cursor
1119:
1120: hr_utility.set_location(
1121: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation',20);
1122:
1123: if lv_report_type = '1099R_FED' then
1124: open c_federal(p_payroll_action_id,
1125: p_start_person,
1140: ln_assignment_id,
1141: ln_box1;
1142:
1143: hr_utility.set_location(
1144: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 30);
1145:
1146: if c_federal%notfound then
1147: exit;
1148: end if;
1155: ln_box_17,
1156: ln_box_18;
1157:
1158: hr_utility.set_location(
1159: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 40);
1160:
1161: if c_state%notfound then
1162: exit;
1163: end if;
1238: select pay_assignment_actions_s.nextval
1239: into ln_lockingactid from dual;
1240:
1241: hr_utility.set_location(
1242: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 60);
1243:
1244: hr_nonrun_asact.insact(ln_lockingactid, ln_assignment_id, p_payroll_action_id,
1245: p_chunk, ln_tax_unit_id);
1246:
1244: hr_nonrun_asact.insact(ln_lockingactid, ln_assignment_id, p_payroll_action_id,
1245: p_chunk, ln_tax_unit_id);
1246:
1247: hr_utility.set_location(
1248: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 70);
1249:
1250: --Create Interlock
1251: select assignment_action_id into ln_lockedactid
1252: from pay_assignment_actions paa,
1268: --insert into pay_action_interlocks
1269: hr_nonrun_asact.insint(ln_lockingactid, ln_lockedactid);
1270:
1271: hr_utility.set_location(
1272: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 90);
1273: hr_utility.trace('Interlock Created - ');
1274: hr_utility.trace('Locking Action : '|| to_char(ln_lockingactid));
1275: hr_utility.trace('Locked Action : '|| to_char(ln_lockedactid));
1276:
1291: elsif lv_report_type = '1099R_STATE' then
1292: close c_state;
1293: end if;
1294:
1295: hr_utility.trace('Exiting pay_us_1099r_mag_reporting.mag_1099r_action_creation');
1296:
1297: end mag_1099r_action_creation;
1298:
1299:
1296:
1297: end mag_1099r_action_creation;
1298:
1299:
1300: end pay_us_1099r_mag_reporting;