[Home] [Help]
373: --Cursor to check if there is a W2 Mag Tape run for the business group and
374: --if there are actions which have been picked up for the GRE for which
375: --this process is run in the mag tape run
376:
377: /*---- Cursor modified as per bug 3263078. Removed join with pay_assignment_actions. ----------*/
378: -- This cursor checks existence of 1099R magtape runs for 1099R GRE and W2 magtape runs for W2 GRE.
379: cursor c_w2_magtape_run_exists(cp_effective_date date,
380: cp_business_group_id number,
381: cp_gre_type varchar2)
535: paf.person_id
536: from hr_assignment_set_amendments has,
537: per_assignments_f paf,
538: per_assignments_f paf1,
539: pay_assignment_actions paa,
540: pay_payroll_actions ppa,
541: pay_action_classifications pac
542: where has.assignment_set_id = cp_assign_set_id
543: and has.include_or_exclude = 'I'
566: and paa.run_type_id is not null
567: and paa.source_action_id is null))
568: and not exists( SELECT 1
569: FROM pay_payroll_actions ppa1, -- Year End
570: pay_assignment_actions paa1 -- Year End
571: WHERE ppa1.report_type = 'YREND'
572: AND ppa1.action_status = 'C'
573: AND ppa1.effective_date = add_months(cp_effective_date, 12) - 1
574: AND to_number(substr(legislative_parameters,
603: cp_gre_id number)
604: is
605: select paa.assignment_id,
606: paa.assignment_action_id
607: from pay_assignment_actions paa,
608: hr_assignment_set_amendments has
609: where paa.payroll_action_id = cp_payroll_action_id
610: and paa.action_status = 'M'
611: and paa.tax_unit_id = cp_gre_id
612: and has.assignment_set_id = cp_assignment_set_id
613: and paa.assignment_id = has.assignment_id
614: and nvl(has.include_or_exclude,'I') = 'I';
615:
616: l_bal_aaid pay_assignment_actions.assignment_action_id%type;
617: l_assignment_id per_all_assignments_f.assignment_id%type;
618: l_person_id per_all_assignments_f.person_id%type;
619: l_yepp_payroll_action_id pay_payroll_actions.payroll_action_id%type;
620: l_value number;
617: l_assignment_id per_all_assignments_f.assignment_id%type;
618: l_person_id per_all_assignments_f.person_id%type;
619: l_yepp_payroll_action_id pay_payroll_actions.payroll_action_id%type;
620: l_value number;
621: lockingactid pay_assignment_actions.assignment_action_id%type;
622: l_prev_person_id per_all_assignments_f.person_id%type; -- bug 3315082
623:
624: /* we should always be stamping the primary assignment_id, even if the
625: assignment selected in the assignment set is secondary */
723: /* Create the assignment action to represnt the person / tax unit
724: combination. */
725: hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 110);
726:
727: select pay_assignment_actions_s.nextval
728: into lockingactid
729: from dual;
730:
731: open c_get_asg_id(l_person_id);
736: else
737: close c_get_asg_id;
738: end if;
739:
740: /* Insert into pay_assignment_actions. */
741: hr_utility.trace('creating asg action');
742:
743: hr_nonrun_asact.insact(lockingactid => lockingactid,
744: assignid => l_assignment_id,
760: /* Bug No : 3137858 Update the serial number column with the person id
761: So that retry us payroll process archives balance values*/
762: hr_utility.trace('updating asg action');
763:
764: update pay_assignment_actions aa
765: set aa.serial_number = to_char(l_person_id)
766: where aa.assignment_action_id = lockingactid;
767:
768:
836: and paf.primary_flag = 'Y'
837: and exists
838: ( select '1'
839: FROM pay_payroll_actions ppa, -- Year End
840: pay_assignment_actions paa -- Year End
841: WHERE ppa.report_type = 'YREND'
842: AND ppa.action_status = 'C'
843: AND ppa.effective_date = add_months(cp_effective_date, 12) - 1
844: AND instr(ppa.legislative_parameters, cp_gre_id)>0
857: where has.assignment_set_id = cp_assignment_set_id
858: and paf.assignment_id = has.assignment_id
859: and exists( SELECT 1
860: FROM pay_payroll_actions ppa1, -- Year End
861: pay_assignment_actions paa1 -- Year End
862: WHERE ppa1.report_type = 'YREND'
863: AND ppa1.action_status = 'C'
864: AND ppa1.effective_date = add_months(cp_effective_date, 12) - 1
865: AND to_number(substr(legislative_parameters,