[Home] [Help]
186: lv_sql_string VARCHAR2(32000);
187: ln_eoy_pactid number;
188: lv_print varchar2(10);
189: lv_error_mesg varchar2(100);
190: ln_agent_tax_unit_id pay_assignment_actions.tax_unit_id%type;
191: ln_year number;
192:
193: BEGIN
194: hr_utility.trace('Entered w2crpt_range_cursor');
279: -- This query string will be executed when for All parameter is passed.
280: ELSE
281: lv_sql_string :=
282: 'select distinct paa.serial_number
283: from pay_assignment_actions paa,
284: pay_payroll_actions ppa
285: where ppa.report_type = ''W2C_PRE_PROCESS''
286: and ppa.effective_date = add_months(''' || ld_start_date || ''',12) -1
287: and ppa.business_group_id+0 = ' || ln_business_group_id ||'
344: lv_sort3 VARCHAR2(60);
345: ld_session_date DATE;
346:
347: lv_report_type pay_payroll_actions.report_type%TYPE ;
348: ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
349: ln_second_last_arch_action pay_assignment_actions.assignment_action_id%TYPE;
350: ln_prev_yepp_lock_action pay_assignment_actions.assignment_action_id%TYPE;
351:
352:
345: ld_session_date DATE;
346:
347: lv_report_type pay_payroll_actions.report_type%TYPE ;
348: ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
349: ln_second_last_arch_action pay_assignment_actions.assignment_action_id%TYPE;
350: ln_prev_yepp_lock_action pay_assignment_actions.assignment_action_id%TYPE;
351:
352:
353: lv_serial_number VARCHAR2(30);
346:
347: lv_report_type pay_payroll_actions.report_type%TYPE ;
348: ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
349: ln_second_last_arch_action pay_assignment_actions.assignment_action_id%TYPE;
350: ln_prev_yepp_lock_action pay_assignment_actions.assignment_action_id%TYPE;
351:
352:
353: lv_serial_number VARCHAR2(30);
354: lv_employee_number per_all_people_f.employee_number%type;
355: lv_message varchar2(50);
356: lv_full_name per_all_people_f.full_name%type;
357: lv_record_name varchar2(50);
358: lv_prev_report_type pay_payroll_actions.report_type%TYPE;
359: ln_prev_lock_action pay_assignment_actions.assignment_action_id%TYPE;
360: ln_prev_w2c_action_id pay_assignment_actions.assignment_action_id%TYPE;
361: ln_serial_number pay_assignment_actions.serial_number%TYPE;
362:
363: CURSOR c_selected_asg_set(cp_start_person in number
356: lv_full_name per_all_people_f.full_name%type;
357: lv_record_name varchar2(50);
358: lv_prev_report_type pay_payroll_actions.report_type%TYPE;
359: ln_prev_lock_action pay_assignment_actions.assignment_action_id%TYPE;
360: ln_prev_w2c_action_id pay_assignment_actions.assignment_action_id%TYPE;
361: ln_serial_number pay_assignment_actions.serial_number%TYPE;
362:
363: CURSOR c_selected_asg_set(cp_start_person in number
364: ,cp_end_person in number
357: lv_record_name varchar2(50);
358: lv_prev_report_type pay_payroll_actions.report_type%TYPE;
359: ln_prev_lock_action pay_assignment_actions.assignment_action_id%TYPE;
360: ln_prev_w2c_action_id pay_assignment_actions.assignment_action_id%TYPE;
361: ln_serial_number pay_assignment_actions.serial_number%TYPE;
362:
363: CURSOR c_selected_asg_set(cp_start_person in number
364: ,cp_end_person in number
365: ,cp_asg_set in number) is
380: cp_start_date in date,
381: cp_business_group_id in number,
382: cp_tax_unit_id in number) IS
383: select distinct paf.person_id
384: from pay_assignment_actions paa,
385: pay_payroll_actions ppa,
386: per_all_assignments_f paf
387: where ppa.report_type = 'W-2C PAPER'
388: and ppa.report_category = 'REPORT'
411: ,cp_start_date in date) is
412: select ppa.report_type, paa.assignment_id,
413: paa.assignment_action_id
414: from pay_payroll_actions ppa,
415: pay_assignment_actions paa,
416: per_all_assignments_f paf
417: where paa.assignment_id = paf.assignment_id
418: and paf.person_id = cp_person_id
419: and paf.effective_start_date <= cp_effective_date
437: ,cp_business_group_id in number) is
438: select ppa.report_type, paa.assignment_id,
439: paa.assignment_action_id
440: from pay_payroll_actions ppa,
441: pay_assignment_actions paa,
442: per_all_assignments_f paf
443: where paa.assignment_id = paf.assignment_id
444: and paf.person_id = cp_person_id
445: and paf.effective_start_date <= cp_effective_date
458: WHERE paf1.assignment_id = paf.assignment_id
459: AND paf1.effective_start_date <= ppa.effective_date)
460: and exists (select 1
461: from pay_action_interlocks pai,
462: pay_assignment_actions paa_paper,
463: pay_payroll_Actions ppa_paper
464: where pai.locked_action_id = paa.assignment_action_id
465: and paa_paper.assignment_Action_id = pai.locking_action_id
466: and ppa_paper.payroll_Action_id = paa_paper.payroll_Action_id
475: select ppa.report_type,
476: paa.assignment_action_id,
477: substr(paa.serial_number, 1,15) prev_action_id
478: from pay_payroll_actions ppa,
479: pay_assignment_actions paa,
480: pay_action_interlocks pai
481: where pai.locking_action_id = cp_w2cpp_action
482: and paa.assignment_action_id = pai.locked_action_id
483: and ppa.payroll_action_id = paa.payroll_action_id;
515:
516: if get_prev_w2c_dtls%found then
517: if lv_report_type in ('W2C_PRE_PROCESS') then
518: /* Create an assignment action for this person */
519: select pay_assignment_actions_s.nextval
520: into ln_w2c_asg_action
521: from dual;
522: hr_utility.trace('New w2c Action = ' || ln_w2c_asg_action);
523:
520: into ln_w2c_asg_action
521: from dual;
522: hr_utility.trace('New w2c Action = ' || ln_w2c_asg_action);
523:
524: /* Insert into pay_assignment_actions. */
525: hr_nonrun_asact.insact(ln_w2c_asg_action
526: ,ln_primary_assignment_id
527: ,p_payroll_action_id
528: ,p_chunk
556: ***************************************************************/
557: ln_serial_number := lpad(ln_asg_act_to_lock,15,0)||
558: lpad(ln_second_last_arch_action,15,0);
559:
560: update pay_assignment_actions aa
561: set aa.serial_number = ln_serial_number
562: where aa.assignment_action_id = ln_w2c_asg_action;
563:
564: /***************************************************************
610: exit;
611: end if;
612:
613: /* Create an assignment action for this person */
614: select pay_assignment_actions_s.nextval
615: into ln_w2c_asg_action
616: from dual;
617: hr_utility.trace('New w2c Action = ' || ln_w2c_asg_action);
618:
615: into ln_w2c_asg_action
616: from dual;
617: hr_utility.trace('New w2c Action = ' || ln_w2c_asg_action);
618:
619: /* Insert into pay_assignment_actions. */
620: hr_nonrun_asact.insact(ln_w2c_asg_action
621: ,ln_primary_assignment_id
622: ,p_payroll_action_id
623: ,p_chunk
651: ***************************************************************/
652: ln_serial_number := lpad(ln_asg_act_to_lock,15,0)||
653: lpad(ln_second_last_arch_action,15,0);
654:
655: update pay_assignment_actions aa
656: set aa.serial_number = ln_serial_number
657: where aa.assignment_action_id = ln_w2c_asg_action;
658:
659: end loop;
798: p_sql_string :=
799: 'select mt.rowid
800: from hr_organization_units hou, hr_locations_all hl,
801: per_periods_of_service pps, per_all_assignments_f paf,
802: pay_assignment_actions mt
803: where mt.payroll_action_id = :p_payroll_action_id
804: and paf.assignment_id = mt.assignment_id -- Bug 3679317 ( +0 removed)
805: and paf.effective_start_date = (select max(paf2.effective_start_date)
806: from per_all_assignments_f paf2 where paf2.assignment_id = paf.assignment_id