[Home] [Help]
187:
188: lv_sql_string :=
189: 'select distinct paf.person_id
190: from pay_payroll_actions ppa_arch,
191: pay_assignment_actions paa_arch,
192: per_assignments_f paf,
193: pay_payroll_actions ppa
194: where paa_arch.assignment_id = paf.assignment_id
195: and ppa.payroll_action_id = :payroll_action_id
206:
207: lv_sql_string :=
208: 'select distinct paf.person_id
209: from pay_payroll_actions ppa_arch,
210: pay_assignment_actions paa_arch,
211: per_assignments_f paf,
212: pay_payroll_actions ppa
213: where paa_arch.assignment_id = paf.assignment_id
214: and ppa.payroll_action_id = :payroll_action_id
262: ln_asg_set NUMBER := 0 ;
263: lv_print varchar2(10);
264:
265: lv_report_type pay_payroll_actions.report_type%TYPE ;
266: ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
267:
268: lv_serial_number VARCHAR2(30);
269: lv_employee_number per_people_f.employee_number%type;
270: lv_message varchar2(100):= null;
269: lv_employee_number per_people_f.employee_number%type;
270: lv_message varchar2(100):= null;
271: lv_full_name per_people_f.full_name%type;
272: lv_record_name varchar2(100);
273: ln_serial_number pay_assignment_actions.serial_number%TYPE;
274: lv_ppr_report_type varchar2(50);
275:
276: CURSOR c_selected_asg_set(cp_start_person in number
277: ,cp_end_person in number
280: select distinct paf.person_id
281: from hr_assignment_set_amendments asgset,
282: per_assignments_f paf,
283: pay_payroll_actions ppa_arch,
284: pay_assignment_actions paa_arch
285: where asgset.assignment_set_id = cp_asg_set
286: and asgset.include_or_exclude = 'I'
287: and paf.assignment_id = asgset.assignment_id
288: and paf.person_id between cp_start_person
313: AND hoi.org_information_context = 'Prov Reporting Est'
314: AND hoi.org_information4 = 'P02'
315: AND exists ( SELECT 1
316: FROM pay_payroll_actions ppa ,
317: pay_assignment_actions paa
318: WHERE ppa.report_type = 'CAEOY_RL2_AMEND_PP'
319: AND ppa.report_qualifier = 'CAEOY_RL2_AMEND_PPQ'
320: AND ppa.business_group_id = cp_bg_id
321: AND ppa.effective_date = cp_eff_date
329: cp_start_person number,
330: cp_end_person number) is
331: select distinct paa_arch.serial_number
332: from pay_payroll_actions ppa_arch,
333: pay_assignment_actions paa_arch
334: where ppa_arch.business_group_id = cp_bg_id
335: and ppa_arch.report_type = 'CAEOY_RL2_AMEND_PP'
336: AND ppa_arch.report_qualifier = 'CAEOY_RL2_AMEND_PPQ'
337: and ppa_arch.effective_date = cp_eff_date
357: paa.assignment_action_id,
358: pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
359: ppa.legislative_parameters)
360: from pay_payroll_actions ppa,
361: pay_assignment_actions paa
362: where paa.serial_number = to_char(cp_person_id)
363: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
364: ppa.legislative_parameters) =
365: nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
388: paa.assignment_action_id,
389: pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
390: ppa.legislative_parameters)
391: from pay_payroll_actions ppa,
392: pay_assignment_actions paa
393: where paa.serial_number = to_char(cp_person_id)
394: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
395: ppa.legislative_parameters) =
396: nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
517: /* Create an assignment action for this person */
518: hr_utility.trace('get_latest_rl2_amend_dtls found ');
519: hr_utility.trace('Report Type: '||lv_report_type);
520:
521: select pay_assignment_actions_s.nextval
522: into ln_rl2_amend_reg_asg_action
523: from dual;
524:
525: hr_utility.trace('New RL2 Amend Action = ' ||
524:
525: hr_utility.trace('New RL2 Amend Action = ' ||
526: to_char(ln_rl2_amend_reg_asg_action));
527:
528: /* Insert into pay_assignment_actions. */
529: hr_nonrun_asact.insact(ln_rl2_amend_reg_asg_action,
530: ln_primary_assignment_id,
531: p_payroll_action_id,
532: p_chunk,
546: ln_serial_number := 'QC'||
547: lpad(to_char(ln_asg_act_to_lock),14,0)||
548: lpad(to_char(ln_yepp_pact_id),14,0);
549:
550: update pay_assignment_actions aa
551: set aa.serial_number = ln_serial_number
552: where aa.assignment_action_id = ln_rl2_amend_reg_asg_action;
553:
554: hr_utility.trace('Province: '||substr(ln_serial_number,1,2));
622: hr_utility.trace('Report Type: '||lv_report_type);
623:
624: /* Create an assignment action for this person */
625:
626: select pay_assignment_actions_s.nextval
627: into ln_rl2_amend_reg_asg_action
628: from dual;
629:
630: hr_utility.trace('New RL2 Amend Action = ' || ln_rl2_amend_reg_asg_action);
628: from dual;
629:
630: hr_utility.trace('New RL2 Amend Action = ' || ln_rl2_amend_reg_asg_action);
631:
632: /* Insert into pay_assignment_actions. */
633: hr_nonrun_asact.insact(ln_rl2_amend_reg_asg_action,
634: ln_primary_assignment_id,
635: p_payroll_action_id,
636: p_chunk,
654: hr_utility.trace('Province: '||substr(ln_serial_number,1,2));
655: hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,3,14));
656: hr_utility.trace('Archiver PactID :'||substr(ln_serial_number,17,14));
657:
658: update pay_assignment_actions aa
659: set aa.serial_number = ln_serial_number
660: where aa.assignment_action_id = ln_rl2_amend_reg_asg_action;
661:
662: end if; -- lv_amend_flag = 'Y' condition
815: hr_all_organization_units hou,
816: hr_locations_all loc,
817: per_all_people_f ppf,
818: per_all_assignments_f paf,
819: pay_assignment_actions paa1,
820: pay_payroll_actions ppa1
821: where ppa1.payroll_action_id = :pactid
822: and paa1.payroll_action_id = ppa1.payroll_action_id
823: and paa1.assignment_id = paf.assignment_id