[Home] [Help]
633: hr_utility.set_location(gv_package || lv_procedure_name, 20);
634:
635: lv_sql_string :=
636: 'select distinct paf.person_id
637: from pay_assignment_actions paa,
638: pay_payroll_actions ppa,
639: per_assignments_f paf
640: where ppa.business_group_id = ''' || ln_business_group_id || '''
641: and ppa.effective_date between fnd_date.canonical_to_date(''' ||
657: and not exists
658: (select /*+ ORDERED */
659: 1
660: from pay_action_interlocks pai,
661: pay_assignment_actions paa1,
662: pay_payroll_actions ppa1
663: where pai.locked_action_id = paa.assignment_action_id
664: and paa1.assignment_action_id = pai.locking_action_id
665: and ppa1.payroll_action_id = paa1.payroll_action_id
699: ,cp_end_date in date
700: ) is
701: select /*+ INDEX(PAF PER_ASSIGNMENTS_F_N12)
702: INDEX(PPA PAY_PAYROLL_ACTIONS_N50)
703: INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51) */
704: paa.assignment_id,
705: paa.tax_unit_id,
706: ppa.effective_date,
707: ppa.date_earned,
708: ppa.action_type,
709: paa.assignment_action_id,
710: paa.payroll_action_id
711: from pay_payroll_actions ppa,
712: pay_assignment_actions paa,
713: per_assignments_f paf
714: where paf.person_id between cp_start_person_id
715: and cp_end_person_id
716: and paa.assignment_id = paf.assignment_id
730: and paa.source_action_id is null
731: and not exists
732: (select 'x'
733: from pay_action_interlocks pai1,
734: pay_assignment_actions paa1,
735: pay_payroll_actions ppa1
736: where pai1.locked_action_id = paa.assignment_action_id
737: and paa1.assignment_action_id = pai1.locking_action_id
738: and ppa1.payroll_action_id = paa1.payroll_action_id
751: ) is
752: select /*+ INDEX(PPR PAY_POPULATION_RANGES_N4)
753: INDEX(PAF PER_ASSIGNMENTS_F_N12)
754: INDEX(PPA PAY_PAYROLL_ACTIONS_N50)
755: INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51) */
756: paa.assignment_id,
757: paa.tax_unit_id,
758: ppa.effective_date,
759: ppa.date_earned,
760: ppa.action_type,
761: paa.assignment_action_id,
762: paa.payroll_action_id
763: from pay_payroll_actions ppa,
764: pay_assignment_actions paa,
765: per_assignments_f paf,
766: pay_population_ranges ppr
767: where ppr.payroll_action_id = cp_payroll_action_id
768: and ppr.chunk_number = cp_chunk_number
784: and paa.source_action_id is null
785: and not exists
786: (select 'x'
787: from pay_action_interlocks pai1,
788: pay_assignment_actions paa1,
789: pay_payroll_actions ppa1
790: where pai1.locked_action_id = paa.assignment_action_id
791: and paa1.assignment_action_id = pai1.locking_action_id
792: and ppa1.payroll_action_id = paa1.payroll_action_id
796:
797: cursor c_master_action(cp_prepayment_action_id number) is
798: select max(paa.assignment_action_id)
799: from pay_payroll_actions ppa,
800: pay_assignment_actions paa,
801: pay_action_interlocks pai
802: where pai.locking_action_Id = cp_prepayment_action_id
803: and paa.assignment_action_id = pai.locked_action_id
804: and paa.source_action_id is null
926: else
927: hr_utility.set_location(gv_package || lv_procedure_name, 60);
928: hr_utility.trace('Action_type = '||lv_action_type );
929:
930: select pay_assignment_actions_s.nextval
931: into ln_xfr_action_id
932: from dual;
933:
934: -- insert into pay_assignment_actions.
930: select pay_assignment_actions_s.nextval
931: into ln_xfr_action_id
932: from dual;
933:
934: -- insert into pay_assignment_actions.
935: hr_nonrun_asact.insact(ln_xfr_action_id,
936: ln_assignment_id,
937: p_payroll_action_id,
938: p_chunk,
960: ********************************************************/
961: lv_serial_number := lv_action_type || 'N' ||
962: ln_asg_action_id;
963:
964: update pay_assignment_actions
965: set serial_number = lv_serial_number
966: where assignment_action_id = ln_xfr_action_id;
967:
968: hr_utility.set_location(gv_package || lv_procedure_name, 100);
1881:
1882: /*Bug# 16318258 - Start */
1883: cursor c_pymt_eff_date(cp_run_action_id in number) is
1884: select ppa.effective_date
1885: from pay_assignment_actions paa,
1886: pay_assignment_actions paa1,
1887: pay_payroll_actions ppa
1888: where paa.assignment_action_id = cp_run_action_id
1889: and paa1.assignment_action_id=to_number(substr(paa.serial_number,3))
1882: /*Bug# 16318258 - Start */
1883: cursor c_pymt_eff_date(cp_run_action_id in number) is
1884: select ppa.effective_date
1885: from pay_assignment_actions paa,
1886: pay_assignment_actions paa1,
1887: pay_payroll_actions ppa
1888: where paa.assignment_action_id = cp_run_action_id
1889: and paa1.assignment_action_id=to_number(substr(paa.serial_number,3))
1890: and paa1.payroll_action_id=ppa.payroll_action_id;
6254: pay_element_entries_f peef,
6255: pay_run_results prr_att,
6256: pay_run_results prr_pay,
6257: pay_payroll_actions ppa,
6258: pay_assignment_actions paa,
6259: pay_action_interlocks pai,
6260: pay_pre_payments ppp,
6261: pay_input_values_f piv_pay,
6262: pay_run_result_values prrv_att,
6338: pay_element_entries_f peef,
6339: pay_run_results prr_att,
6340: pay_run_results prr_pay,
6341: pay_payroll_actions ppa,
6342: pay_assignment_actions paa,
6343: pay_action_interlocks pai,
6344: pay_pre_payments ppp,
6345: pay_input_values_f piv_pay,
6346: pay_run_result_values prrv_att,
6445: pay_input_values_f piv_att,
6446: pay_element_entries_f peef,
6447: pay_run_results prr_att,
6448: pay_payroll_actions ppa,
6449: pay_assignment_actions paa,
6450: pay_action_interlocks pai,
6451: pay_pre_payments ppp,
6452: pay_run_result_values prrv_att,
6453: pay_element_types_f pet,
6777:
6778: cursor c_child_action (cp_pre_pay_action_id in number
6779: ,cp_assignment_id in number) is
6780: select paa.assignment_action_id
6781: from pay_assignment_actions paa
6782: where paa.source_action_id = cp_pre_pay_action_id
6783: and paa.assignment_id = cp_assignment_id
6784: and paa.action_status = 'C';
6785:
6794: popm.org_payment_method_name,
6795: pppm.personal_payment_method_id,
6796: pppm.payee_id,
6797: pppm.payee_type
6798: from pay_assignment_actions paa,
6799: pay_pre_payments ppp,
6800: pay_org_payment_methods_f popm ,
6801: pay_personal_payment_methods_f pppm,
6802: pay_payment_types ppt
6816: and cp_ppp_source_action_id is null
6817: and exists (
6818: select ''
6819: from pay_run_types_f prt,
6820: pay_assignment_actions paa_run,
6821: pay_payroll_actions ppa_run
6822: where paa_run.assignment_action_id
6823: = ppp.source_action_id
6824: and paa_run.payroll_action_id
7186: cursor c_ytd_aaid(cp_prepayment_action_id in number
7187: ,cp_assignment_id in number
7188: ,cp_sepchk_run_type in number) is
7189: select paa.assignment_action_id
7190: from pay_assignment_actions paa,
7191: pay_action_interlocks pai,
7192: pay_payroll_actions ppa
7193: where pai.locking_action_id = cp_prepayment_action_id
7194: and paa.assignment_action_id = pai.locked_action_id
7203: -- ppa.date_earned,
7204: nvl(ppa.date_earned,ppa.effective_date),
7205: --bug 7379102
7206: ppa.effective_date
7207: from pay_assignment_actions paa,
7208: pay_payroll_actions ppa,
7209: per_time_periods ptp
7210: where paa.assignment_action_id = cp_run_assignment_action
7211: and ppa.payroll_action_id = paa.payroll_action_id
7216: --bug 7379102
7217:
7218: cursor c_chk_act_type(cp_last_xfr_act_id number) is
7219: select substr(serial_number,1,1)
7220: from pay_assignment_actions paa
7221: where paa.assignment_action_id = cp_last_xfr_act_id;
7222:
7223: -- added below cursor for 13029995
7224: cursor cur_busgrp is
7642: paa.assignment_id,
7643: paa.tax_unit_id,
7644: paa.serial_number,
7645: paa.chunk_number
7646: from pay_assignment_actions paa
7647: where paa.assignment_action_id = cp_assignment_action;
7648:
7649: cursor c_legislation (cp_business_group in number) is
7650: select org_information9
7660:
7661: cursor c_assignment_run (cp_prepayment_action_id in number) is
7662: select distinct paa.assignment_id
7663: from pay_action_interlocks pai,
7664: pay_assignment_actions paa,
7665: pay_payroll_actions ppa
7666: where pai.locking_action_id = cp_prepayment_action_id
7667: and paa.assignment_action_id = pai.locked_action_id
7668: and ppa.payroll_action_id = paa.payroll_action_id
7676: cp_assignment_id in number) is
7677: select paa.assignment_action_id, paa.payroll_action_id,
7678: ppa.action_type
7679: from pay_payroll_actions ppa,
7680: pay_assignment_actions paa,
7681: pay_action_interlocks pai
7682: where pai.locking_action_id = cp_prepayment_action_id
7683: and pai.locked_action_id = paa.assignment_action_id
7684: and paa.assignment_id = cp_assignment_id
7688:
7689: cursor c_pymt_eff_date(cp_prepayment_action_id in number) is
7690: select ppa.effective_date
7691: from pay_payroll_actions ppa,
7692: pay_assignment_actions paa
7693: where ppa.payroll_action_id = paa.payroll_action_id
7694: and paa.assignment_action_id = cp_prepayment_action_id;
7695:
7696: cursor c_check_pay_action( cp_payroll_action_id in number) is
7713: ,cp_assignment_id number
7714: ,cp_tax_unit_id number) is
7715: select paa.assignment_action_id
7716: ,paa.source_action_id
7717: from pay_assignment_actions paa
7718: ,pay_action_interlocks pai
7719: where pai.locking_action_id = cp_pp_asg_act_id
7720: and paa.assignment_action_id = pai.locked_action_id
7721: and paa.assignment_id = cp_assignment_id
7730:
7731: cursor c_get_prepay_aaid_for_sepchk( cp_asg_act_id number,
7732: cp_source_act_id number ) is
7733: select ppp.assignment_action_id
7734: from pay_assignment_actions paa
7735: ,pay_pre_payments ppp
7736: where ( paa.assignment_action_id = cp_asg_act_id OR
7737: paa.source_action_id = cp_asg_act_id )
7738: and ppp.assignment_action_id = paa.assignment_action_id
7749: select /*+ ORDERED */
7750: paf1.assignment_id,
7751: paa.assignment_action_id
7752: from per_all_assignments_f paf1
7753: ,pay_assignment_actions paa
7754: ,pay_payroll_actions ppa
7755: where paf1.person_id = (select /*+ PUSH_SUBQ */ person_id
7756: from per_all_assignments_f START_ASS
7757: where START_ASS.assignment_id = cp_assignment_id
7792: ,cp_assignment_id number
7793: ,cp_tax_unit_id number) is
7794: select paa.assignment_action_id
7795: ,paa.source_action_id
7796: from pay_assignment_actions paa
7797: ,pay_action_interlocks pai
7798: where pai.locking_action_id = cp_pp_asg_act_id
7799: and paa.assignment_action_id = pai.locked_action_id
7800: and paa.assignment_id = cp_assignment_id
7805: ,cp_assignment_id in number
7806: ,cp_tax_unit_id in number
7807: ,cp_sepchk_run_type in number) is
7808: select paa.assignment_action_id
7809: from pay_assignment_actions paa,
7810: pay_action_interlocks pai
7811: where pai.locking_action_id = cp_pp_asg_act_id
7812: and paa.assignment_action_id = pai.locked_action_id
7813: and paa.assignment_id = cp_assignment_id
8039: fetch c_get_prepay_aaid_for_sepchk into ln_asg_action_id;
8040: close c_get_prepay_aaid_for_sepchk;
8041:
8042: ln_step := 12;
8043: select pay_assignment_actions_s.nextval
8044: into ln_child_xfr_action_id
8045: from dual;
8046:
8047: hr_utility.set_location(gv_package || lv_procedure_name, 30);
8045: from dual;
8046:
8047: hr_utility.set_location(gv_package || lv_procedure_name, 30);
8048:
8049: -- insert into pay_assignment_actions.
8050: ln_step := 13;
8051: hr_nonrun_asact.insact(ln_child_xfr_action_id,
8052: ln_assignment_id,
8053: ln_xfr_payroll_action_id,
8073: lv_master_sepcheck_flag || ln_source_action_id;
8074:
8075: ln_step := 16;
8076:
8077: update pay_assignment_actions
8078: set serial_number = lv_serial_number
8079: where assignment_action_id = ln_child_xfr_action_id;
8080:
8081: hr_utility.trace('Processing Child action ' ||
8396: lv_procedure_name VARCHAR2(100) := '.check_alien';
8397:
8398: cursor c_get_assignment_id (cp_assignment_action_id in number) is
8399: select assignment_id
8400: from pay_assignment_actions
8401: where assignment_action_id = cp_assignment_action_id;
8402:
8403: BEGIN
8404: hr_utility.trace('opened c_get_assignment_id');