[Home] [Help]
534: hr_utility.set_location(gv_package || lv_procedure_name, 20);
535:
536: lv_sql_string :=
537: 'select distinct paf.person_id
538: from pay_assignment_actions paa,
539: pay_payroll_actions ppa,
540: per_assignments_f paf
541: where ppa.business_group_id = ''' || ln_business_group_id || '''
542: and ppa.effective_date between fnd_date.canonical_to_date(''' ||
558: and not exists
559: (select /*+ ORDERED */
560: 1
561: from pay_action_interlocks pai,
562: pay_assignment_actions paa1,
563: pay_payroll_actions ppa1
564: where pai.locked_action_id = paa.assignment_action_id
565: and paa1.assignment_action_id = pai.locking_action_id
566: and ppa1.payroll_action_id = paa1.payroll_action_id
600: ,cp_end_date in date
601: ) is
602: select /*+ INDEX(PAF PER_ASSIGNMENTS_F_N12)
603: INDEX(PPA PAY_PAYROLL_ACTIONS_N50)
604: INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51) */
605: paa.assignment_id,
606: paa.tax_unit_id,
607: ppa.effective_date,
608: ppa.date_earned,
609: ppa.action_type,
610: paa.assignment_action_id,
611: paa.payroll_action_id
612: from pay_payroll_actions ppa,
613: pay_assignment_actions paa,
614: per_assignments_f paf
615: where paf.person_id between cp_start_person_id
616: and cp_end_person_id
617: and paa.assignment_id = paf.assignment_id
631: and paa.source_action_id is null
632: and not exists
633: (select 'x'
634: from pay_action_interlocks pai1,
635: pay_assignment_actions paa1,
636: pay_payroll_actions ppa1
637: where pai1.locked_action_id = paa.assignment_action_id
638: and paa1.assignment_action_id = pai1.locking_action_id
639: and ppa1.payroll_action_id = paa1.payroll_action_id
652: ) is
653: select /*+ INDEX(PPR PAY_POPULATION_RANGES_N4)
654: INDEX(PAF PER_ASSIGNMENTS_F_N12)
655: INDEX(PPA PAY_PAYROLL_ACTIONS_N50)
656: INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51) */
657: paa.assignment_id,
658: paa.tax_unit_id,
659: ppa.effective_date,
660: ppa.date_earned,
661: ppa.action_type,
662: paa.assignment_action_id,
663: paa.payroll_action_id
664: from pay_payroll_actions ppa,
665: pay_assignment_actions paa,
666: per_assignments_f paf,
667: pay_population_ranges ppr
668: where ppr.payroll_action_id = cp_payroll_action_id
669: and ppr.chunk_number = cp_chunk_number
685: and paa.source_action_id is null
686: and not exists
687: (select 'x'
688: from pay_action_interlocks pai1,
689: pay_assignment_actions paa1,
690: pay_payroll_actions ppa1
691: where pai1.locked_action_id = paa.assignment_action_id
692: and paa1.assignment_action_id = pai1.locking_action_id
693: and ppa1.payroll_action_id = paa1.payroll_action_id
697:
698: cursor c_master_action(cp_prepayment_action_id number) is
699: select max(paa.assignment_action_id)
700: from pay_payroll_actions ppa,
701: pay_assignment_actions paa,
702: pay_action_interlocks pai
703: where pai.locking_action_Id = cp_prepayment_action_id
704: and paa.assignment_action_id = pai.locked_action_id
705: and paa.source_action_id is null
827: else
828: hr_utility.set_location(gv_package || lv_procedure_name, 60);
829: hr_utility.trace('Action_type = '||lv_action_type );
830:
831: select pay_assignment_actions_s.nextval
832: into ln_xfr_action_id
833: from dual;
834:
835: -- insert into pay_assignment_actions.
831: select pay_assignment_actions_s.nextval
832: into ln_xfr_action_id
833: from dual;
834:
835: -- insert into pay_assignment_actions.
836: hr_nonrun_asact.insact(ln_xfr_action_id,
837: ln_assignment_id,
838: p_payroll_action_id,
839: p_chunk,
861: ********************************************************/
862: lv_serial_number := lv_action_type || 'N' ||
863: ln_asg_action_id;
864:
865: update pay_assignment_actions
866: set serial_number = lv_serial_number
867: where assignment_action_id = ln_xfr_action_id;
868:
869: hr_utility.set_location(gv_package || lv_procedure_name, 100);
5186: cursor c_ytd_aaid(cp_prepayment_action_id in number
5187: ,cp_assignment_id in number
5188: ,cp_sepchk_run_type in number) is
5189: select paa.assignment_action_id
5190: from pay_assignment_actions paa,
5191: pay_action_interlocks pai,
5192: pay_payroll_actions ppa
5193: where pai.locking_action_id = cp_prepayment_action_id
5194: and paa.assignment_action_id = pai.locked_action_id
5203: -- ppa.date_earned,
5204: nvl(ppa.date_earned,ppa.effective_date),
5205: --bug 7379102
5206: ppa.effective_date
5207: from pay_assignment_actions paa,
5208: pay_payroll_actions ppa,
5209: per_time_periods ptp
5210: where paa.assignment_action_id = cp_run_assignment_action
5211: and ppa.payroll_action_id = paa.payroll_action_id
5216: --bug 7379102
5217:
5218: cursor c_chk_act_type(cp_last_xfr_act_id number) is
5219: select substr(serial_number,1,1)
5220: from pay_assignment_actions paa
5221: where paa.assignment_action_id = cp_last_xfr_act_id;
5222:
5223: lv_pre_xfr_act_type VARCHAR2(80);
5224:
5582: paa.assignment_id,
5583: paa.tax_unit_id,
5584: paa.serial_number,
5585: paa.chunk_number
5586: from pay_assignment_actions paa
5587: where paa.assignment_action_id = cp_assignment_action;
5588:
5589: cursor c_legislation (cp_business_group in number) is
5590: select org_information9
5600:
5601: cursor c_assignment_run (cp_prepayment_action_id in number) is
5602: select distinct paa.assignment_id
5603: from pay_action_interlocks pai,
5604: pay_assignment_actions paa,
5605: pay_payroll_actions ppa
5606: where pai.locking_action_id = cp_prepayment_action_id
5607: and paa.assignment_action_id = pai.locked_action_id
5608: and ppa.payroll_action_id = paa.payroll_action_id
5616: cp_assignment_id in number) is
5617: select paa.assignment_action_id, paa.payroll_action_id,
5618: ppa.action_type
5619: from pay_payroll_actions ppa,
5620: pay_assignment_actions paa,
5621: pay_action_interlocks pai
5622: where pai.locking_action_id = cp_prepayment_action_id
5623: and pai.locked_action_id = paa.assignment_action_id
5624: and paa.assignment_id = cp_assignment_id
5628:
5629: cursor c_pymt_eff_date(cp_prepayment_action_id in number) is
5630: select ppa.effective_date
5631: from pay_payroll_actions ppa,
5632: pay_assignment_actions paa
5633: where ppa.payroll_action_id = paa.payroll_action_id
5634: and paa.assignment_action_id = cp_prepayment_action_id;
5635:
5636: cursor c_check_pay_action( cp_payroll_action_id in number) is
5653: ,cp_assignment_id number
5654: ,cp_tax_unit_id number) is
5655: select paa.assignment_action_id
5656: ,paa.source_action_id
5657: from pay_assignment_actions paa
5658: ,pay_action_interlocks pai
5659: where pai.locking_action_id = cp_pp_asg_act_id
5660: and paa.assignment_action_id = pai.locked_action_id
5661: and paa.assignment_id = cp_assignment_id
5670:
5671: cursor c_get_prepay_aaid_for_sepchk( cp_asg_act_id number,
5672: cp_source_act_id number ) is
5673: select ppp.assignment_action_id
5674: from pay_assignment_actions paa
5675: ,pay_pre_payments ppp
5676: where ( paa.assignment_action_id = cp_asg_act_id OR
5677: paa.source_action_id = cp_asg_act_id )
5678: and ppp.assignment_action_id = paa.assignment_action_id
5689: select /*+ ORDERED */
5690: paf1.assignment_id,
5691: paa.assignment_action_id
5692: from per_all_assignments_f paf1
5693: ,pay_assignment_actions paa
5694: ,pay_payroll_actions ppa
5695: where paf1.person_id = (select /*+ PUSH_SUBQ */ person_id
5696: from per_all_assignments_f START_ASS
5697: where START_ASS.assignment_id = cp_assignment_id
5731: ,cp_assignment_id number
5732: ,cp_tax_unit_id number) is
5733: select paa.assignment_action_id
5734: ,paa.source_action_id
5735: from pay_assignment_actions paa
5736: ,pay_action_interlocks pai
5737: where pai.locking_action_id = cp_pp_asg_act_id
5738: and paa.assignment_action_id = pai.locked_action_id
5739: and paa.assignment_id = cp_assignment_id
5744: ,cp_assignment_id in number
5745: ,cp_tax_unit_id in number
5746: ,cp_sepchk_run_type in number) is
5747: select paa.assignment_action_id
5748: from pay_assignment_actions paa,
5749: pay_action_interlocks pai
5750: where pai.locking_action_id = cp_pp_asg_act_id
5751: and paa.assignment_action_id = pai.locked_action_id
5752: and paa.assignment_id = cp_assignment_id
5978: fetch c_get_prepay_aaid_for_sepchk into ln_asg_action_id;
5979: close c_get_prepay_aaid_for_sepchk;
5980:
5981: ln_step := 12;
5982: select pay_assignment_actions_s.nextval
5983: into ln_child_xfr_action_id
5984: from dual;
5985:
5986: hr_utility.set_location(gv_package || lv_procedure_name, 30);
5984: from dual;
5985:
5986: hr_utility.set_location(gv_package || lv_procedure_name, 30);
5987:
5988: -- insert into pay_assignment_actions.
5989: ln_step := 13;
5990: hr_nonrun_asact.insact(ln_child_xfr_action_id,
5991: ln_assignment_id,
5992: ln_xfr_payroll_action_id,
6012: lv_master_sepcheck_flag || ln_source_action_id;
6013:
6014: ln_step := 16;
6015:
6016: update pay_assignment_actions
6017: set serial_number = lv_serial_number
6018: where assignment_action_id = ln_child_xfr_action_id;
6019:
6020: hr_utility.trace('Processing Child action ' ||
6335: lv_procedure_name VARCHAR2(100) := '.check_alien';
6336:
6337: cursor c_get_assignment_id (cp_assignment_action_id in number) is
6338: select assignment_id
6339: from pay_assignment_actions
6340: where assignment_action_id = cp_assignment_action_id;
6341:
6342: BEGIN
6343: hr_utility.trace('opened c_get_assignment_id');