[Home] [Help]
201:
202: lv_sql_string :=
203: 'select distinct paf.person_id
204: from pay_payroll_actions ppa_arch,
205: pay_assignment_actions paa_arch,
206: per_assignments_f paf,
207: pay_payroll_actions ppa
208: where paa_arch.assignment_id = paf.assignment_id
209: and ppa.payroll_action_id = :payroll_action_id
220:
221: lv_sql_string :=
222: 'select distinct paf.person_id
223: from pay_payroll_actions ppa_arch,
224: pay_assignment_actions paa_arch,
225: per_assignments_f paf,
226: pay_payroll_actions ppa
227: where paa_arch.assignment_id = paf.assignment_id
228: and ppa.payroll_action_id = :payroll_action_id
278: ln_asg_set NUMBER := 0 ;
279: lv_print varchar2(10);
280:
281: lv_report_type pay_payroll_actions.report_type%TYPE ;
282: ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
283:
284: lv_serial_number VARCHAR2(30);
285: lv_employee_number per_people_f.employee_number%type;
286: lv_message varchar2(100):= null;
285: lv_employee_number per_people_f.employee_number%type;
286: lv_message varchar2(100):= null;
287: lv_full_name per_people_f.full_name%type;
288: lv_record_name varchar2(100);
289: ln_serial_number pay_assignment_actions.serial_number%TYPE;
290: lv_ppr_report_type varchar2(50);
291:
292: CURSOR c_selected_asg_set(cp_start_person in number
293: ,cp_end_person in number
296: select distinct paf.person_id
297: from hr_assignment_set_amendments asgset,
298: per_assignments_f paf,
299: pay_payroll_actions ppa_arch,
300: pay_assignment_actions paa_arch
301: where asgset.assignment_set_id = cp_asg_set
302: and asgset.include_or_exclude = 'I'
303: and paf.assignment_id = asgset.assignment_id
304: and paf.person_id between cp_start_person
329: AND hoi.org_information_context = 'Prov Reporting Est'
330: AND hoi.org_information4 = 'P01'
331: AND exists ( SELECT 1
332: FROM pay_payroll_actions ppa ,
333: pay_assignment_actions paa
334: WHERE ppa.report_type = 'CAEOY_RL1_AMEND_PP'
335: AND ppa.report_qualifier = 'CAEOY_RL1_AMEND_PPQ'
336: AND ppa.business_group_id = cp_bg_id
337: AND ppa.effective_date = cp_eff_date
345: cp_start_person number,
346: cp_end_person number) is
347: select distinct paa_arch.serial_number
348: from pay_payroll_actions ppa_arch,
349: pay_assignment_actions paa_arch
350: where ppa_arch.business_group_id = cp_bg_id
351: and ppa_arch.report_type = 'CAEOY_RL1_AMEND_PP'
352: AND ppa_arch.report_qualifier = 'CAEOY_RL1_AMEND_PPQ'
353: and ppa_arch.effective_date = cp_eff_date
373: paa.assignment_action_id,
374: pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
375: ppa.legislative_parameters)
376: from pay_payroll_actions ppa,
377: pay_assignment_actions paa
378: where paa.serial_number = to_char(cp_person_id)
379: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
380: ppa.legislative_parameters) =
381: nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
404: paa.assignment_action_id,
405: pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
406: ppa.legislative_parameters)
407: from pay_payroll_actions ppa,
408: pay_assignment_actions paa
409: where paa.serial_number = to_char(cp_person_id)
410: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
411: ppa.legislative_parameters) =
412: nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
558: /* Create an assignment action for this person */
559: hr_utility.trace('get_latest_rl1_amend_dtls found ');
560: hr_utility.trace('Report Type: '||lv_report_type);
561:
562: select pay_assignment_actions_s.nextval
563: into ln_rl1_amend_reg_asg_action
564: from dual;
565:
566: hr_utility.trace('New RL1 Amend Action = ' ||
565:
566: hr_utility.trace('New RL1 Amend Action = ' ||
567: to_char(ln_rl1_amend_reg_asg_action));
568:
569: /* Insert into pay_assignment_actions. */
570: hr_nonrun_asact.insact(ln_rl1_amend_reg_asg_action
571: ,ln_primary_assignment_id
572: ,p_payroll_action_id
573: ,p_chunk
587: ln_serial_number := lv_prov_of_emp||
588: lpad(to_char(ln_asg_act_to_lock),14,0)||
589: lpad(to_char(ln_yepp_pact_id),14,0);
590:
591: update pay_assignment_actions aa
592: set aa.serial_number = ln_serial_number
593: where aa.assignment_action_id = ln_rl1_amend_reg_asg_action;
594: hr_utility.trace('lv_prov_of_emp: '||substr(ln_serial_number,1,2));
595: hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,3,14));
668: hr_utility.trace('Report Type: '||lv_report_type);
669:
670: /* Create an assignment action for this person */
671:
672: select pay_assignment_actions_s.nextval
673: into ln_rl1_amend_reg_asg_action
674: from dual;
675: hr_utility.trace('New RL1 Amend Action = ' || ln_rl1_amend_reg_asg_action);
676:
673: into ln_rl1_amend_reg_asg_action
674: from dual;
675: hr_utility.trace('New RL1 Amend Action = ' || ln_rl1_amend_reg_asg_action);
676:
677: /* Insert into pay_assignment_actions. */
678: hr_nonrun_asact.insact(ln_rl1_amend_reg_asg_action
679: ,ln_primary_assignment_id
680: ,p_payroll_action_id
681: ,p_chunk
698: hr_utility.trace('lv_prov_of_emp: '||substr(ln_serial_number,1,2));
699: hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,3,14));
700: hr_utility.trace('Archiver PactID :'||substr(ln_serial_number,17,14));
701:
702: update pay_assignment_actions aa
703: set aa.serial_number = ln_serial_number
704: where aa.assignment_action_id = ln_rl1_amend_reg_asg_action;
705:
706: end if; -- lv_amend_flag = 'Y' condition
860: hr_all_organization_units hou,
861: hr_locations_all loc,
862: per_all_people_f ppf,
863: per_all_assignments_f paf,
864: pay_assignment_actions paa1,
865: pay_payroll_actions ppa1
866: where ppa1.payroll_action_id = :pactid
867: and paa1.payroll_action_id = ppa1.payroll_action_id
868: and paa1.assignment_id = paf.assignment_id