[Home] [Help]
232: lv_sql_string VARCHAR2(32000);
233: ln_eoy_pactid number;
234: lv_print varchar2(10);
235: lv_error_mesg varchar2(100);
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');
325: -- This query string will be executed when for All parameter is passed.
326: ELSE
327: lv_sql_string :=
328: 'select distinct paa.serial_number
329: from pay_assignment_actions paa,
330: pay_payroll_actions ppa
331: where ppa.report_type = ''W2C_PRE_PROCESS''
332: and ppa.effective_date = add_months(''' || ld_start_date || ''',12) -1
333: and ppa.business_group_id+0 = ' || ln_business_group_id ||'
390: lv_sort3 VARCHAR2(60);
391: ld_session_date DATE;
392:
393: lv_report_type pay_payroll_actions.report_type%TYPE ;
394: ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
395: ln_second_last_arch_action pay_assignment_actions.assignment_action_id%TYPE;
396: ln_prev_yepp_lock_action pay_assignment_actions.assignment_action_id%TYPE;
397:
398:
391: ld_session_date DATE;
392:
393: lv_report_type pay_payroll_actions.report_type%TYPE ;
394: ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
395: ln_second_last_arch_action pay_assignment_actions.assignment_action_id%TYPE;
396: ln_prev_yepp_lock_action pay_assignment_actions.assignment_action_id%TYPE;
397:
398:
399: lv_serial_number VARCHAR2(30);
392:
393: lv_report_type pay_payroll_actions.report_type%TYPE ;
394: ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
395: ln_second_last_arch_action pay_assignment_actions.assignment_action_id%TYPE;
396: ln_prev_yepp_lock_action pay_assignment_actions.assignment_action_id%TYPE;
397:
398:
399: lv_serial_number VARCHAR2(30);
400: lv_employee_number per_all_people_f.employee_number%type;
401: lv_message varchar2(50);
402: lv_full_name per_all_people_f.full_name%type;
403: lv_record_name varchar2(50);
404: lv_prev_report_type pay_payroll_actions.report_type%TYPE;
405: ln_prev_lock_action pay_assignment_actions.assignment_action_id%TYPE;
406: ln_prev_w2c_action_id pay_assignment_actions.assignment_action_id%TYPE;
407: ln_serial_number pay_assignment_actions.serial_number%TYPE;
408:
409: CURSOR c_selected_asg_set(cp_start_person in number
402: lv_full_name per_all_people_f.full_name%type;
403: lv_record_name varchar2(50);
404: lv_prev_report_type pay_payroll_actions.report_type%TYPE;
405: ln_prev_lock_action pay_assignment_actions.assignment_action_id%TYPE;
406: ln_prev_w2c_action_id pay_assignment_actions.assignment_action_id%TYPE;
407: ln_serial_number pay_assignment_actions.serial_number%TYPE;
408:
409: CURSOR c_selected_asg_set(cp_start_person in number
410: ,cp_end_person in number
403: lv_record_name varchar2(50);
404: lv_prev_report_type pay_payroll_actions.report_type%TYPE;
405: ln_prev_lock_action pay_assignment_actions.assignment_action_id%TYPE;
406: ln_prev_w2c_action_id pay_assignment_actions.assignment_action_id%TYPE;
407: ln_serial_number pay_assignment_actions.serial_number%TYPE;
408:
409: CURSOR c_selected_asg_set(cp_start_person in number
410: ,cp_end_person in number
411: ,cp_asg_set in number) is
426: cp_start_date in date,
427: cp_business_group_id in number,
428: cp_tax_unit_id in number) IS
429: select distinct paf.person_id
430: from pay_assignment_actions paa,
431: pay_payroll_actions ppa,
432: per_all_assignments_f paf
433: where ppa.report_type = 'W-2C PAPER'
434: and ppa.report_category = 'REPORT'
457: ,cp_start_date in date) is
458: select ppa.report_type, paa.assignment_id,
459: paa.assignment_action_id
460: from pay_payroll_actions ppa,
461: pay_assignment_actions paa,
462: per_all_assignments_f paf
463: where paa.assignment_id = paf.assignment_id
464: and paf.person_id = cp_person_id
465: and paf.effective_start_date <= cp_effective_date
483: ,cp_business_group_id in number) is
484: select ppa.report_type, paa.assignment_id,
485: paa.assignment_action_id
486: from pay_payroll_actions ppa,
487: pay_assignment_actions paa,
488: per_all_assignments_f paf
489: where paa.assignment_id = paf.assignment_id
490: and paf.person_id = cp_person_id
491: and paf.effective_start_date <= cp_effective_date
504: WHERE paf1.assignment_id = paf.assignment_id
505: AND paf1.effective_start_date <= ppa.effective_date)
506: and exists (select 1
507: from pay_action_interlocks pai,
508: pay_assignment_actions paa_paper,
509: pay_payroll_Actions ppa_paper
510: where pai.locked_action_id = paa.assignment_action_id
511: and paa_paper.assignment_Action_id = pai.locking_action_id
512: and ppa_paper.payroll_Action_id = paa_paper.payroll_Action_id
521: select ppa.report_type,
522: paa.assignment_action_id,
523: substr(paa.serial_number, 1,15) prev_action_id
524: from pay_payroll_actions ppa,
525: pay_assignment_actions paa,
526: pay_action_interlocks pai
527: where pai.locking_action_id = cp_w2cpp_action
528: and paa.assignment_action_id = pai.locked_action_id
529: and ppa.payroll_action_id = paa.payroll_action_id;
568: if get_prev_w2c_dtls%found then
569:
570: if lv_report_type in ('W2C_PRE_PROCESS') then
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:
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
580: ,p_payroll_action_id
581: ,p_chunk
610: ***************************************************************/
611: ln_serial_number := lpad(ln_asg_act_to_lock,15,0)||
612: lpad(ln_second_last_arch_action,15,0);
613:
614: update pay_assignment_actions aa
615: set aa.serial_number = ln_serial_number
616: where aa.assignment_action_id = ln_w2c_asg_action;
617:
618: /***************************************************************
665: exit;
666: end if;
667:
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:
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
677: ,p_payroll_action_id
678: ,p_chunk
706: ***************************************************************/
707: ln_serial_number := lpad(ln_asg_act_to_lock,15,0)||
708: lpad(ln_second_last_arch_action,15,0);
709:
710: update pay_assignment_actions aa
711: set aa.serial_number = ln_serial_number
712: where aa.assignment_action_id = ln_w2c_asg_action;
713:
714: end loop;
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,
860: pay_assignment_actions mt
861: where mt.payroll_action_id = :p_payroll_action_id
862: and paf.assignment_id = mt.assignment_id -- Bug 3679317 ( +0 removed)
863: and paf.effective_start_date = (select max(paf2.effective_start_date)
864: from per_all_assignments_f paf2 where paf2.assignment_id = paf.assignment_id
1548: where organization_id = p_tax_unit_id;
1549:
1550: CURSOR c_get_payroll_action (p_asg_action_id NUMBER)IS
1551: select payroll_action_id
1552: from pay_assignment_actions
1553: where assignment_action_id = p_asg_action_id;
1554:
1555: CURSOR c_get_session_date IS
1556: SELECT NVL(TO_DATE(TO_CHAR(TO_DATE(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',
3623: hr_us_w2_mt.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
3624: hr_us_w2_mt.get_parameter('Year',ppa.legislative_parameters),
3625: hr_us_w2_mt.get_parameter('p_instr_template',ppa.legislative_parameters),
3626: hr_us_w2_mt.get_parameter('print_instrunction',ppa.legislative_parameters)
3627: FROM pay_assignment_actions paa,
3628: pay_payroll_actions ppa
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: