[Home] [Help]
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;
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;
251:
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;
251:
252: close c_sel;
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:
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);
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;
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;
300:
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;
300:
301: -- To get the YREND ppa ID
304: fetch c_yrend_ppa into ln_yrend_ppa_id;
305:
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;
305:
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;
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:
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:
315: close c_yrend_ppa;
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:
315: close c_yrend_ppa;
316:
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);
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);
332: hr_utility.trace('The report type from get_selection_information is: '||lv_report_type);
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);
332: hr_utility.trace('The report type from get_selection_information is: '||lv_report_type);
333: hr_utility.trace('The business group id from get_selection_information is: '||to_char(ln_business_group_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);
332: hr_utility.trace('The report type from get_selection_information is: '||lv_report_type);
333: hr_utility.trace('The business group id from get_selection_information is: '||to_char(ln_business_group_id));
334: hr_utility.trace('The tax unit id from get_selection_information is: '||lv_tax_unit_id);
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);
332: hr_utility.trace('The report type from get_selection_information is: '||lv_report_type);
333: hr_utility.trace('The business group id from get_selection_information is: '||to_char(ln_business_group_id));
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);
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);
332: hr_utility.trace('The report type from get_selection_information is: '||lv_report_type);
333: hr_utility.trace('The business group id from get_selection_information is: '||to_char(ln_business_group_id));
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));
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);
332: hr_utility.trace('The report type from get_selection_information is: '||lv_report_type);
333: hr_utility.trace('The business group id from get_selection_information is: '||to_char(ln_business_group_id));
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:
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);
332: hr_utility.trace('The report type from get_selection_information is: '||lv_report_type);
333: hr_utility.trace('The business group id from get_selection_information is: '||to_char(ln_business_group_id));
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');
331: hr_utility.trace('The state abbrev from get_selection_information is: '||lv_report_qualifier);
332: hr_utility.trace('The report type from get_selection_information is: '||lv_report_type);
333: hr_utility.trace('The business group id from get_selection_information is: '||to_char(ln_business_group_id));
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:
332: hr_utility.trace('The report type from get_selection_information is: '||lv_report_type);
333: hr_utility.trace('The business group id from get_selection_information is: '||to_char(ln_business_group_id));
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;
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;
381: open c_jurisdiction(p_state_abbrev);
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;
387:
388: pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
389: end if;
390:
391: hr_utility.trace(p_balance_name);
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
388: pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
389: end if;
390:
391: hr_utility.trace(p_balance_name);
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);
389: end if;
390:
391: hr_utility.trace(p_balance_name);
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:
390:
391: hr_utility.trace(p_balance_name);
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
391: hr_utility.trace(p_balance_name);
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,
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:
608:
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;
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;
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;
627: close c_chk_cntc_info; -- The 'T' record contact information is
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;
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:
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;
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;
644: end if;
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;
644: end if;
645: close c_chk_vnd_info; -- The 'T' record Vendor information exists.
646: end if;
647: close c_chk_vnd_type;
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
656: -- Pre-process
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));
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
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
668: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',81);
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;
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;
727: hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check', 240);
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;
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');
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
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
732: hr_utility.trace(ln_curr_gre || 'GRE not archived');
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
732: hr_utility.trace(ln_curr_gre || 'GRE not archived');
733: lv_message_text := 'Please Archive GRE With ID := ' || to_char(ln_curr_gre) ;
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
732: hr_utility.trace(ln_curr_gre || 'GRE not archived');
733: lv_message_text := 'Please Archive GRE With ID := ' || to_char(ln_curr_gre) ;
734: /*
735: * Commenting the code
736: pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA_WARNING','P');
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
749: -- a payroll action id of the YREND archiver for the specific GRE.
750: -- Otherwise it will never be set.
751:
752: if ln_no_of_gres_picked = 0 then
753: hr_utility.set_location( 'pay_us_mag_1099r_reporting.preprocess_check', 415);
754: hr_utility.set_message(801, 'PAY_ARCH_GRE_NOT_FOUND');
755: hr_utility.raise_error;
756: else
757: if p_report_type = '1099R_FED' then
750: -- Otherwise it will never be set.
751:
752: if ln_no_of_gres_picked = 0 then
753: hr_utility.set_location( 'pay_us_mag_1099r_reporting.preprocess_check', 415);
754: hr_utility.set_message(801, 'PAY_ARCH_GRE_NOT_FOUND');
755: hr_utility.raise_error;
756: else
757: if p_report_type = '1099R_FED' then
758: hr_utility.trace('The number of GREs that have been archived ' ||
751:
752: if ln_no_of_gres_picked = 0 then
753: hr_utility.set_location( 'pay_us_mag_1099r_reporting.preprocess_check', 415);
754: hr_utility.set_message(801, 'PAY_ARCH_GRE_NOT_FOUND');
755: hr_utility.raise_error;
756: else
757: if p_report_type = '1099R_FED' then
758: hr_utility.trace('The number of GREs that have been archived ' ||
759: 'for the federal mag are: '||to_char(ln_no_of_gres_picked));
754: hr_utility.set_message(801, 'PAY_ARCH_GRE_NOT_FOUND');
755: hr_utility.raise_error;
756: else
757: if p_report_type = '1099R_FED' then
758: hr_utility.trace('The number of GREs that have been archived ' ||
759: 'for the federal mag are: '||to_char(ln_no_of_gres_picked));
760: else
761: hr_utility.trace('The number of GREs that have been archived ' ||
762: 'for the state mag are: ' ||to_char(ln_no_of_gres_picked));
757: if p_report_type = '1099R_FED' then
758: hr_utility.trace('The number of GREs that have been archived ' ||
759: 'for the federal mag are: '||to_char(ln_no_of_gres_picked));
760: else
761: hr_utility.trace('The number of GREs that have been archived ' ||
762: 'for the state mag are: ' ||to_char(ln_no_of_gres_picked));
763: end if;
764: end if;
765:
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:
790:
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 (
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
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
836: from --hr_soft_coding_keyflex hsck,
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,
899: from hr_organization_information hoi
900: where hoi.org_information_context = ''1099R Magnetic Report Rules'')
901: order by paf.person_id';
902:
903: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor', 80);
904:
905: end if;
906:
907: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',90);
903: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor', 80);
904:
905: end if;
906:
907: hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',90);
908:
909: p_sql_string := lv_sql_string;
910:
911: end if;
909: p_sql_string := lv_sql_string;
910:
911: end if;
912:
913: hr_utility.trace('Exiting pay_us_1099r_mag_reporting.range_cursor');
914:
915: end range_cursor;
916:
917:
1096: ln_create_assignment number;
1097:
1098: begin
1099:
1100: hr_utility.trace('Entering pay_us_1099r_mag_reporting.mag_1099r_action_creation');
1101:
1102: -- Get the report parameters. These define the report being run.
1103: hr_utility.set_location(
1104: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation',10);
1099:
1100: hr_utility.trace('Entering pay_us_1099r_mag_reporting.mag_1099r_action_creation');
1101:
1102: -- Get the report parameters. These define the report being run.
1103: hr_utility.set_location(
1104: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation',10);
1105:
1106: get_selection_information (
1107: p_payroll_action_id,
1118:
1119:
1120: --Open the appropriate cursor
1121:
1122: hr_utility.set_location(
1123: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation',20);
1124:
1125: if lv_report_type = '1099R_FED' then
1126: open c_federal(p_payroll_action_id,
1141: ld_effective_end_date,
1142: ln_assignment_id,
1143: ln_box1;
1144:
1145: hr_utility.set_location(
1146: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 30);
1147:
1148: if c_federal%notfound then
1149: exit;
1156: ln_assignment_id,
1157: ln_box_17,
1158: ln_box_18;
1159:
1160: hr_utility.set_location(
1161: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 40);
1162:
1163: if c_state%notfound then
1164: exit;
1179: or
1180: (ln_person_id <> nvl(ln_prev_person_id, -1)) then
1181:
1182: --Create the assignment action for the record
1183: hr_utility.trace('Assignment Fetched - ');
1184: hr_utility.trace('Assignment Id : '|| to_char(ln_assignment_id));
1185: hr_utility.trace('Person Id : '|| to_char(ln_person_id));
1186: hr_utility.trace('tax unit id : '|| to_char(ln_tax_unit_id));
1187: hr_utility.trace('Effective End Date : '|| to_char(ld_effective_end_date));
1180: (ln_person_id <> nvl(ln_prev_person_id, -1)) then
1181:
1182: --Create the assignment action for the record
1183: hr_utility.trace('Assignment Fetched - ');
1184: hr_utility.trace('Assignment Id : '|| to_char(ln_assignment_id));
1185: hr_utility.trace('Person Id : '|| to_char(ln_person_id));
1186: hr_utility.trace('tax unit id : '|| to_char(ln_tax_unit_id));
1187: hr_utility.trace('Effective End Date : '|| to_char(ld_effective_end_date));
1188:
1181:
1182: --Create the assignment action for the record
1183: hr_utility.trace('Assignment Fetched - ');
1184: hr_utility.trace('Assignment Id : '|| to_char(ln_assignment_id));
1185: hr_utility.trace('Person Id : '|| to_char(ln_person_id));
1186: hr_utility.trace('tax unit id : '|| to_char(ln_tax_unit_id));
1187: hr_utility.trace('Effective End Date : '|| to_char(ld_effective_end_date));
1188:
1189: if lv_report_type = '1099R_FED' then
1182: --Create the assignment action for the record
1183: hr_utility.trace('Assignment Fetched - ');
1184: hr_utility.trace('Assignment Id : '|| to_char(ln_assignment_id));
1185: hr_utility.trace('Person Id : '|| to_char(ln_person_id));
1186: hr_utility.trace('tax unit id : '|| to_char(ln_tax_unit_id));
1187: hr_utility.trace('Effective End Date : '|| to_char(ld_effective_end_date));
1188:
1189: if lv_report_type = '1099R_FED' then
1190: if ln_box1 > 0 then
1183: hr_utility.trace('Assignment Fetched - ');
1184: hr_utility.trace('Assignment Id : '|| to_char(ln_assignment_id));
1185: hr_utility.trace('Person Id : '|| to_char(ln_person_id));
1186: hr_utility.trace('tax unit id : '|| to_char(ln_tax_unit_id));
1187: hr_utility.trace('Effective End Date : '|| to_char(ld_effective_end_date));
1188:
1189: if lv_report_type = '1099R_FED' then
1190: if ln_box1 > 0 then
1191: ln_create_assignment := 1;
1239:
1240: select pay_assignment_actions_s.nextval
1241: into ln_lockingactid from dual;
1242:
1243: hr_utility.set_location(
1244: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 60);
1245:
1246: hr_nonrun_asact.insact(ln_lockingactid, ln_assignment_id, p_payroll_action_id,
1247: p_chunk, ln_tax_unit_id);
1245:
1246: hr_nonrun_asact.insact(ln_lockingactid, ln_assignment_id, p_payroll_action_id,
1247: p_chunk, ln_tax_unit_id);
1248:
1249: hr_utility.set_location(
1250: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 70);
1251:
1252: --Create Interlock
1253: select assignment_action_id into ln_lockedactid
1269:
1270: --insert into pay_action_interlocks
1271: hr_nonrun_asact.insint(ln_lockingactid, ln_lockedactid);
1272:
1273: hr_utility.set_location(
1274: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 90);
1275: hr_utility.trace('Interlock Created - ');
1276: hr_utility.trace('Locking Action : '|| to_char(ln_lockingactid));
1277: hr_utility.trace('Locked Action : '|| to_char(ln_lockedactid));
1271: hr_nonrun_asact.insint(ln_lockingactid, ln_lockedactid);
1272:
1273: hr_utility.set_location(
1274: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 90);
1275: hr_utility.trace('Interlock Created - ');
1276: hr_utility.trace('Locking Action : '|| to_char(ln_lockingactid));
1277: hr_utility.trace('Locked Action : '|| to_char(ln_lockedactid));
1278:
1279: --Store the current person/GRE for comparision during the
1272:
1273: hr_utility.set_location(
1274: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 90);
1275: hr_utility.trace('Interlock Created - ');
1276: hr_utility.trace('Locking Action : '|| to_char(ln_lockingactid));
1277: hr_utility.trace('Locked Action : '|| to_char(ln_lockedactid));
1278:
1279: --Store the current person/GRE for comparision during the
1280: --next iteration.
1273: hr_utility.set_location(
1274: 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 90);
1275: hr_utility.trace('Interlock Created - ');
1276: hr_utility.trace('Locking Action : '|| to_char(ln_lockingactid));
1277: hr_utility.trace('Locked Action : '|| to_char(ln_lockedactid));
1278:
1279: --Store the current person/GRE for comparision during the
1280: --next iteration.
1281: ln_prev_person_id := ln_person_id;
1293: elsif lv_report_type = '1099R_STATE' then
1294: close c_state;
1295: end if;
1296:
1297: hr_utility.trace('Exiting pay_us_1099r_mag_reporting.mag_1099r_action_creation');
1298:
1299: end mag_1099r_action_creation;
1300:
1301: