[Home] [Help]
602: ,cp_curr_pymt_eff_date in date) is
603:
604: select ppa.effective_date, paa.assignment_action_id
605: from pay_payroll_actions ppa,
606: pay_assignment_actions paa
607: where paa.assignment_id = p_assignment_id
608: and ppa.payroll_action_id = paa.payroll_action_id
609: and ppa.action_type in ('U','P')
610: and ppa.effective_date < p_curr_pymt_eff_date
610: and ppa.effective_date < p_curr_pymt_eff_date
611: and ppa.effective_date in
612: ( select max(ppa1.effective_date)
613: from pay_payroll_actions ppa1,
614: pay_assignment_actions paa1
615: where ppa1.payroll_action_id = paa1.payroll_action_id
616: and ppa1.action_type in ('U','P')
617: and paa1.assignment_id = p_assignment_id
618: and ppa1.effective_date < p_curr_pymt_eff_date);
687: cursor c_child_action ( cp_pre_pay_action_id in number
688: ,cp_assignment_id in number
689: ) is
690: select paa.assignment_action_id
691: from pay_assignment_actions paa
692: where paa.source_action_id = cp_pre_pay_action_id
693: and paa.assignment_id = cp_assignment_id
694: and paa.action_status = 'C';
695:
704: popm.org_payment_method_name,
705: pppm.personal_payment_method_id,
706: pppm.payee_id,
707: pppm.payee_type
708: from pay_assignment_actions paa,
709: pay_pre_payments ppp,
710: pay_org_payment_methods_f popm ,
711: pay_personal_payment_methods_f pppm,
712: pay_payment_types ppt
726: and cp_ppp_source_action_id is null
727: and exists (
728: select ''
729: from pay_run_types_f prt,
730: pay_assignment_actions paa_run,
731: pay_payroll_actions ppa_run
732: where paa_run.assignment_action_id
733: = ppp.source_action_id
734: and paa_run.payroll_action_id
821: pay_input_values_f piv_pay,
822: pay_element_types_f pet,
823: pay_run_results prr,
824: pay_payroll_actions run_ppa,
825: pay_assignment_actions run_paa,
826: pay_action_interlocks pai,
827: pay_pre_payments ppp
828: WHERE prrv.result_value = to_char(cp_amount)
829: AND ppp.pre_payment_id = cp_pre_payment_id
1156: cursor c_ytd_aaid(cp_prepayment_action_id in number
1157: ,cp_assignment_id in number
1158: ,cp_sepchk_run_type in number) is
1159: select paa.assignment_action_id
1160: from pay_assignment_actions paa,
1161: pay_action_interlocks pai,
1162: pay_payroll_actions ppa
1163: where pai.locking_action_id = cp_prepayment_action_id
1164: and paa.assignment_action_id = pai.locked_action_id
1179: ,cp_assignment_id in number
1180: ,cp_tax_unit_id in number
1181: ,cp_sepchk_run_type in number) is
1182: select paa.assignment_action_id
1183: from pay_assignment_actions paa,
1184: pay_action_interlocks pai,
1185: pay_payroll_actions ppa
1186: where pai.locking_action_id = cp_prepayment_action_id
1187: and paa.assignment_action_id = pai.locked_action_id
1200: cursor c_time_period(cp_run_assignment_action in number) is
1201: select ptp.time_period_id,
1202: ppa.date_earned,
1203: ppa.effective_date
1204: from pay_assignment_actions paa,
1205: pay_payroll_actions ppa,
1206: per_time_periods ptp
1207: where paa.assignment_action_id = cp_run_assignment_action
1208: and ppa.payroll_action_id = paa.payroll_action_id
1624: cursor c_get_pp_actid_of_multigre(cp_prepay_action_id number
1625: ,cp_assignment_id number
1626: ,cp_tax_unit_id number) is
1627: select assignment_action_id
1628: from pay_assignment_actions
1629: where source_action_id = cp_prepay_action_id
1630: and assignment_id = cp_assignment_id
1631: and tax_unit_id = cp_tax_unit_id;
1632:
1632:
1633: cursor c_get_pp_actid_of_sepchk(cp_source_action_id number) is
1634: select paa.assignment_action_id
1635: from pay_action_interlocks pai
1636: ,pay_assignment_actions paa
1637: ,pay_payroll_actions ppa
1638: where pai.locked_action_id = cp_source_action_id
1639: and paa.assignment_action_id = pai.locking_action_id
1640: and paa.source_action_id is not null
1645: pre-payment assignment action id, 'V'. Bug#3498653 */
1646: cursor c_run_master_aa_id(cp_pp_asg_act_id number
1647: ,cp_assignment_id number) is
1648: select paa.assignment_action_id,ppa_run.action_type
1649: from pay_assignment_actions paa
1650: ,pay_action_interlocks pai
1651: ,pay_payroll_actions ppa_run
1652: where pai.locking_action_id = cp_pp_asg_act_id
1653: and paa.assignment_action_id = pai.locked_action_id
1661: ,cp_assignment_id in number
1662: ,cp_tax_unit_id in number
1663: ,cp_sepchk_run_type in number) is
1664: select paa.assignment_action_id
1665: from pay_assignment_actions paa,
1666: pay_action_interlocks pai
1667: where pai.locking_action_id = cp_pp_asg_act_id
1668: and paa.assignment_action_id = pai.locked_action_id
1669: and paa.assignment_id = cp_assignment_id
1818: *******************************************************************/
1819:
1820: if ( ln_source_action_id <> prev_source_action_id ) then
1821:
1822: select pay_assignment_actions_s.nextval
1823: into ln_child_xfr_action_id
1824: from dual;
1825:
1826: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1824: from dual;
1825:
1826: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1827:
1828: -- insert into pay_assignment_actions.
1829:
1830: ln_step := 3;
1831:
1832: hr_nonrun_asact.insact(ln_child_xfr_action_id,
1859: end if;
1860:
1861: ln_step := 5;
1862:
1863: update pay_assignment_actions
1864: set serial_number = lv_serial_number
1865: where assignment_action_id = ln_child_xfr_action_id;
1866:
1867: hr_utility.trace('Processing Child action for Master for Multi GRE ' ||
1964: cursor c_get_pp_actid_of_multigre(cp_prepay_action_id number
1965: ,cp_assignment_id number
1966: ,cp_tax_unit_id number) is
1967: select assignment_action_id
1968: from pay_assignment_actions
1969: where source_action_id = cp_prepay_action_id
1970: and assignment_id = cp_assignment_id
1971: and tax_unit_id = cp_tax_unit_id;
1972:
1972:
1973: cursor c_get_pp_actid_of_sepchk(cp_source_action_id number) is
1974: select paa.assignment_action_id
1975: from pay_action_interlocks pai
1976: ,pay_assignment_actions paa
1977: ,pay_payroll_actions ppa
1978: where pai.locked_action_id = cp_source_action_id
1979: and paa.assignment_action_id = pai.locking_action_id
1980: and paa.source_action_id is not null
1984: cursor c_run_aa_id(cp_pp_asg_act_id number
1985: ,cp_assignment_id number
1986: ,cp_tax_unit_id number) is
1987: select paa.assignment_action_id, paa.source_action_id
1988: from pay_assignment_actions paa
1989: ,pay_action_interlocks pai
1990: where pai.locking_action_id = cp_pp_asg_act_id
1991: and paa.assignment_action_id = pai.locked_action_id
1992: and paa.assignment_id = cp_assignment_id
1998: ,cp_assignment_id in number
1999: ,cp_tax_unit_id in number
2000: ,cp_sepchk_run_type in number) is
2001: select paa.assignment_action_id
2002: from pay_assignment_actions paa,
2003: pay_action_interlocks pai
2004: where pai.locking_action_id = cp_pp_asg_act_id
2005: and paa.assignment_action_id = pai.locked_action_id
2006: and paa.assignment_id = cp_assignment_id
2294:
2295: if ( ( ln_source_action_id <> prev_source_action_id ) or
2296: ( ln_tax_unit_id <> prev_tax_unit_id ) ) then
2297:
2298: select pay_assignment_actions_s.nextval
2299: into ln_child_xfr_action_id
2300: from dual;
2301:
2302: hr_utility.set_location(gv_package || lv_procedure_name, 30);
2300: from dual;
2301:
2302: hr_utility.set_location(gv_package || lv_procedure_name, 30);
2303:
2304: -- insert into pay_assignment_actions.
2305:
2306: ln_step := 3;
2307:
2308: hr_nonrun_asact.insact(ln_child_xfr_action_id,
2335: end if;
2336:
2337: ln_step := 5;
2338:
2339: update pay_assignment_actions
2340: set serial_number = lv_serial_number
2341: where assignment_action_id = ln_child_xfr_action_id;
2342:
2343: hr_utility.trace('Processing Child action for Master for Multi GRE ' ||
2437: cp_curr_pymt_eff_date date) is
2438: select distinct
2439: paa.assignment_action_id
2440: ,paa.tax_unit_id
2441: from pay_assignment_actions paa
2442: ,pay_pre_payments ppp
2443: ,pay_org_payment_methods popm
2444: where paa.source_action_id = cp_master_prepay_act_id
2445: and ppp.assignment_action_id = paa.assignment_action_id
2455: cp_curr_pymt_eff_date date) is
2456: select distinct
2457: paa.assignment_action_id
2458: ,paa.tax_unit_id
2459: from pay_assignment_actions paa
2460: ,pay_pre_payments ppp
2461: ,pay_org_payment_methods popm
2462: where paa.assignment_action_id = cp_master_prepay_act_id
2463: and ppp.assignment_action_id = paa.assignment_action_id
2469: and popm.effective_end_date;
2470:
2471: cursor c_tax_unit(cp_pp_asg_act_id number) is
2472: select distinct paa.tax_unit_id
2473: from pay_assignment_actions paa
2474: ,pay_action_interlocks pai
2475: where pai.locking_action_id = cp_pp_asg_act_id
2476: and paa.assignment_action_id = pai.locked_action_id
2477: and paa.tax_unit_id is not null;
2477: and paa.tax_unit_id is not null;
2478:
2479: cursor c_run_aa_id(cp_pp_asg_act_id number) is
2480: select paa.assignment_action_id, paa.source_action_id
2481: from pay_assignment_actions paa
2482: ,pay_action_interlocks pai
2483: where pai.locking_action_id = cp_pp_asg_act_id
2484: and paa.assignment_action_id = pai.locked_action_id
2485: and paa.source_action_id is not null
2548:
2549: hr_utility.trace('MG ln_pp_asg_act_id : ' || ln_pp_asg_act_id);
2550: hr_utility.trace('MG ln_tax_unit_id : ' || ln_tax_unit_id);
2551:
2552: select pay_assignment_actions_s.nextval
2553: into ln_child_xfr_action_id
2554: from dual;
2555:
2556: hr_utility.set_location(gv_package || lv_procedure_name, 30);
2554: from dual;
2555:
2556: hr_utility.set_location(gv_package || lv_procedure_name, 30);
2557:
2558: -- insert into pay_assignment_actions.
2559:
2560: ln_step := 3;
2561:
2562: hr_nonrun_asact.insact(ln_child_xfr_action_id,
2583: lv_serial_number := p_master_action_type || 'N' || ln_pp_asg_act_id;
2584:
2585: ln_step := 5;
2586:
2587: update pay_assignment_actions
2588: set serial_number = lv_serial_number
2589: where assignment_action_id = ln_child_xfr_action_id;
2590:
2591: hr_utility.trace('Processing Child action for Master for Multi GRE ' ||
2692: paa.tax_unit_id,
2693: paa.assignment_action_id,
2694: ppp.source_action_id
2695: from pay_payroll_actions ppa
2696: ,pay_assignment_actions paa
2697: ,pay_pre_payments ppp
2698: ,pay_org_payment_methods popm
2699: where ppa.consolidation_set_id
2700: = nvl(cp_cons_set_id,ppa.consolidation_set_id)
2724: paa.tax_unit_id,
2725: paa.assignment_action_id,
2726: ppp.source_action_id
2727: from pay_pre_payments ppp
2728: ,pay_assignment_actions paa
2729: ,pay_org_payment_methods popm
2730: where paa.assignment_action_id = cp_prepayment_action_id
2731: and ppp.assignment_action_id = paa.assignment_action_id
2732: and nvl(ppp.value,0) <> 0
2741: (cp_master_prepay_action_id in number
2742: ,cp_sepchk_run_type_id in number) is
2743: select pai.locked_action_id
2744: from pay_action_interlocks pai,
2745: pay_assignment_actions paa
2746: where pai.locking_action_id = cp_master_prepay_action_id
2747: and paa.assignment_action_id = pai.locked_action_id
2748: and paa.source_action_id is not null
2749: and paa.run_type_id = cp_sepchk_run_type_id;
2749: and paa.run_type_id = cp_sepchk_run_type_id;
2750:
2751: cursor c_tax_unit(cp_source_action_id number) is
2752: select paa.tax_unit_id
2753: from pay_assignment_actions paa
2754: where paa.assignment_action_id = cp_source_action_id;
2755:
2756: ln_assignment_id NUMBER;
2757: ln_tax_unit_id NUMBER;
2855: end if;
2856:
2857: -- create child assignment action
2858: ln_step := 9;
2859: select pay_assignment_actions_s.nextval
2860: into ln_child_xfr_action_id
2861: from dual;
2862:
2863: hr_utility.set_location(gv_package || lv_procedure_name, 90);
2860: into ln_child_xfr_action_id
2861: from dual;
2862:
2863: hr_utility.set_location(gv_package || lv_procedure_name, 90);
2864: -- insert into pay_assignment_actions.
2865: ln_step := 10;
2866: hr_nonrun_asact.insact(ln_child_xfr_action_id,
2867: ln_assignment_id,
2868: p_xfr_payroll_action_id,
2888:
2889: lv_serial_number := p_master_action_type || 'Y' || ln_run_action_id;
2890:
2891: ln_step := 12;
2892: update pay_assignment_actions
2893: set serial_number = lv_serial_number
2894: where assignment_action_id = ln_child_xfr_action_id;
2895:
2896: hr_utility.trace('Processing Child action for Master ' ||
2965: paa.assignment_id,
2966: paa.tax_unit_id,
2967: paa.serial_number,
2968: paa.chunk_number
2969: from pay_assignment_actions paa
2970: where paa.assignment_action_id = cp_assignment_action;
2971:
2972: cursor c_legislation (cp_business_group in number) is
2973: select org_information9
2983:
2984: cursor c_assignment_run (cp_prepayment_action_id in number) is
2985: select distinct paa.assignment_id
2986: from pay_action_interlocks pai,
2987: pay_assignment_actions paa,
2988: pay_payroll_actions ppa
2989: where pai.locking_action_id = cp_prepayment_action_id
2990: and paa.assignment_action_id = pai.locked_action_id
2991: and ppa.payroll_action_id = paa.payroll_action_id
3001: cp_assignment_id in number) is
3002: select paa.assignment_action_id, paa.payroll_action_id,
3003: ppa.action_type
3004: from pay_payroll_actions ppa,
3005: pay_assignment_actions paa,
3006: pay_action_interlocks pai
3007: where pai.locking_action_Id = cp_prepayment_action_id
3008: and pai.locked_action_id = paa.assignment_action_id
3009: and paa.assignment_id = cp_assignment_id
3013:
3014: cursor c_pymt_eff_date(cp_prepayment_action_id in number) is
3015: select effective_date
3016: from pay_payroll_actions ppa,
3017: pay_assignment_actions paa
3018: where ppa.payroll_action_id = paa.payroll_action_id
3019: and paa.assignment_action_id = cp_prepayment_action_id;
3020:
3021: cursor c_check_pay_action( cp_payroll_action_id in number) is
3041: cp_prepayment_action_id in number,
3042: cp_assignment_id in number) is
3043: select distinct ppa.action_type
3044: from pay_payroll_actions ppa,
3045: pay_assignment_actions paa,
3046: pay_action_interlocks pai
3047: where pai.locking_action_Id = cp_prepayment_action_id
3048: and pai.locked_action_id = paa.assignment_action_id
3049: and paa.assignment_id = cp_assignment_id
3603: /* removed the reversal validation from range cursor SQL STMT
3604: ''V'', nvl(ppa.future_process_mode, ''Y''). Bug#3498653 */
3605: lv_sql_string :=
3606: 'select distinct paa.assignment_id
3607: from pay_assignment_actions paa,
3608: pay_payroll_actions ppa
3609: where ppa.business_group_id = ''' || ln_business_group_id || '''
3610: and ppa.effective_date between fnd_date.canonical_to_date(''' ||
3611: fnd_date.date_to_canonical(ld_start_date) || ''')
3623: and paa.source_action_id is null
3624: and not exists
3625: (select ''x''
3626: from pay_action_interlocks pai,
3627: pay_assignment_actions paa1,
3628: pay_payroll_actions ppa1
3629: where pai.locked_action_id = paa.assignment_action_id
3630: and paa1.assignment_action_id = pai.locking_action_id
3631: and ppa1.payroll_action_id = paa1.payroll_action_id
3680: ,cp_business_group_id in number
3681: ,cp_start_date in date
3682: ,cp_end_date in date
3683: ) is
3684: select /*+ INDEX (PAA PAY_ASSIGNMENT_ACTIONS_N50) */
3685: paa.assignment_id,
3686: paa.tax_unit_id,
3687: ppa.effective_date,
3688: ppa.date_earned,
3689: ppa.action_type,
3690: paa.assignment_action_id,
3691: paa.payroll_action_id
3692: from pay_payroll_actions ppa,
3693: pay_assignment_actions paa
3694: where paa.assignment_id between cp_start_assignment_id
3695: and cp_end_assignment_id
3696: and ppa.consolidation_set_id = cp_cons_set_id
3697: and paa.action_status = 'C'
3708: and paa.source_action_id is null
3709: and not exists
3710: (select 'x'
3711: from pay_action_interlocks pai1,
3712: pay_assignment_actions paa1,
3713: pay_payroll_actions ppa1
3714: where pai1.locked_action_id = paa.assignment_action_id
3715: and paa1.assignment_action_id = pai1.locking_action_id
3716: and ppa1.payroll_action_id = paa1.payroll_action_id
3720:
3721: cursor c_master_action(cp_prepayment_action_id number) is
3722: select max(paa.assignment_action_id)
3723: from pay_payroll_actions ppa,
3724: pay_assignment_actions paa,
3725: pay_action_interlocks pai
3726: where pai.locking_action_Id = cp_prepayment_action_id
3727: and pai.locked_action_id = paa.assignment_action_id
3728: and paa.source_action_id is null
3730: and ppa.action_type in ('R', 'Q');
3731:
3732: cursor c_lock_chld_pp_aa(cp_prepay_master_aa_id number) is
3733: select paa.assignment_action_id
3734: from pay_assignment_actions paa
3735: where paa.source_action_id = cp_prepay_master_aa_id;
3736:
3737: ln_assignment_id NUMBER;
3738: ln_tax_unit_id NUMBER;
3854: hr_utility.set_location(gv_package || lv_procedure_name, 60);
3855: hr_utility.trace('Action_type = '||lv_action_type );
3856:
3857: ln_step := 6;
3858: select pay_assignment_actions_s.nextval
3859: into ln_xfr_action_id
3860: from dual;
3861:
3862: -- insert into pay_assignment_actions.
3858: select pay_assignment_actions_s.nextval
3859: into ln_xfr_action_id
3860: from dual;
3861:
3862: -- insert into pay_assignment_actions.
3863: ln_step := 7;
3864: hr_nonrun_asact.insact(ln_xfr_action_id,
3865: ln_assignment_id,
3866: p_payroll_action_id,
3901: lv_serial_number := lv_action_type || 'N' ||
3902: ln_asg_action_id;
3903:
3904: ln_step := 9;
3905: update pay_assignment_actions
3906: set serial_number = lv_serial_number
3907: where assignment_action_id = ln_xfr_action_id;
3908:
3909: hr_utility.set_location(gv_package || lv_procedure_name, 100);