[Home] [Help]
588: ,cp_curr_pymt_eff_date in date) is
589:
590: select ppa.effective_date, paa.assignment_action_id
591: from pay_payroll_actions ppa,
592: pay_assignment_actions paa
593: where paa.assignment_id = p_assignment_id
594: and ppa.payroll_action_id = paa.payroll_action_id
595: and ppa.action_type in ('U','P')
596: and ppa.effective_date < p_curr_pymt_eff_date
596: and ppa.effective_date < p_curr_pymt_eff_date
597: and ppa.effective_date in
598: ( select max(ppa1.effective_date)
599: from pay_payroll_actions ppa1,
600: pay_assignment_actions paa1
601: where ppa1.payroll_action_id = paa1.payroll_action_id
602: and ppa1.action_type in ('U','P')
603: and paa1.assignment_id = p_assignment_id
604: and ppa1.effective_date < p_curr_pymt_eff_date);
688: cursor c_ytd_aaid(cp_prepayment_action_id in number
689: ,cp_assignment_id in number
690: ,cp_sepchk_run_type in number) is
691: select paa.assignment_action_id
692: from pay_assignment_actions paa,
693: pay_action_interlocks pai,
694: pay_payroll_actions ppa
695: where pai.locking_action_id = cp_prepayment_action_id
696: and paa.assignment_action_id = pai.locked_action_id
711: ,cp_assignment_id in number
712: ,cp_tax_unit_id in number
713: ,cp_sepchk_run_type in number) is
714: select paa.assignment_action_id
715: from pay_assignment_actions paa,
716: pay_action_interlocks pai,
717: pay_payroll_actions ppa
718: where pai.locking_action_id = cp_prepayment_action_id
719: and paa.assignment_action_id = pai.locked_action_id
732: cursor c_time_period(cp_run_assignment_action in number) is
733: select ptp.time_period_id,
734: ppa.date_earned,
735: ppa.effective_date
736: from pay_assignment_actions paa,
737: pay_payroll_actions ppa,
738: per_time_periods ptp
739: where paa.assignment_action_id = cp_run_assignment_action
740: and ppa.payroll_action_id = paa.payroll_action_id
1124: cursor c_get_pp_actid_of_multigre(cp_prepay_action_id number
1125: ,cp_assignment_id number
1126: ,cp_tax_unit_id number) is
1127: select assignment_action_id
1128: from pay_assignment_actions
1129: where source_action_id = cp_prepay_action_id
1130: and assignment_id = cp_assignment_id
1131: and tax_unit_id = cp_tax_unit_id;
1132:
1132:
1133: cursor c_get_pp_actid_of_sepchk(cp_source_action_id number) is
1134: select paa.assignment_action_id
1135: from pay_action_interlocks pai
1136: ,pay_assignment_actions paa
1137: ,pay_payroll_actions ppa
1138: where pai.locked_action_id = cp_source_action_id
1139: and paa.assignment_action_id = pai.locking_action_id
1140: and paa.source_action_id is not null
1145: pre-payment assignment action id, 'V'. Bug#3498653 */
1146: cursor c_run_master_aa_id(cp_pp_asg_act_id number
1147: ,cp_assignment_id number) is
1148: select paa.assignment_action_id,ppa_run.action_type
1149: from pay_assignment_actions paa
1150: ,pay_action_interlocks pai
1151: ,pay_payroll_actions ppa_run
1152: where pai.locking_action_id = cp_pp_asg_act_id
1153: and paa.assignment_action_id = pai.locked_action_id
1161: ,cp_assignment_id in number
1162: ,cp_tax_unit_id in number
1163: ,cp_sepchk_run_type in number) is
1164: select paa.assignment_action_id
1165: from pay_assignment_actions paa,
1166: pay_action_interlocks pai
1167: where pai.locking_action_id = cp_pp_asg_act_id
1168: and paa.assignment_action_id = pai.locked_action_id
1169: and paa.assignment_id = cp_assignment_id
1316: *******************************************************************/
1317:
1318: if ( ln_source_action_id <> prev_source_action_id ) then
1319:
1320: select pay_assignment_actions_s.nextval
1321: into ln_child_xfr_action_id
1322: from dual;
1323:
1324: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1322: from dual;
1323:
1324: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1325:
1326: -- insert into pay_assignment_actions.
1327:
1328: ln_step := 3;
1329:
1330: hr_nonrun_asact.insact(ln_child_xfr_action_id,
1357: end if;
1358:
1359: ln_step := 5;
1360:
1361: update pay_assignment_actions
1362: set serial_number = lv_serial_number
1363: where assignment_action_id = ln_child_xfr_action_id;
1364:
1365: hr_utility.trace('Processing Child action for Master for Multi GRE ' ||
1460: cursor c_get_pp_actid_of_multigre(cp_prepay_action_id number
1461: ,cp_assignment_id number
1462: ,cp_tax_unit_id number) is
1463: select assignment_action_id
1464: from pay_assignment_actions
1465: where source_action_id = cp_prepay_action_id
1466: and assignment_id = cp_assignment_id
1467: and tax_unit_id = cp_tax_unit_id;
1468:
1468:
1469: cursor c_get_pp_actid_of_sepchk(cp_source_action_id number) is
1470: select paa.assignment_action_id
1471: from pay_action_interlocks pai
1472: ,pay_assignment_actions paa
1473: ,pay_payroll_actions ppa
1474: where pai.locked_action_id = cp_source_action_id
1475: and paa.assignment_action_id = pai.locking_action_id
1476: and paa.source_action_id is not null
1480: cursor c_run_aa_id(cp_pp_asg_act_id number
1481: ,cp_assignment_id number
1482: ,cp_tax_unit_id number) is
1483: select paa.assignment_action_id, paa.source_action_id
1484: from pay_assignment_actions paa
1485: ,pay_action_interlocks pai
1486: where pai.locking_action_id = cp_pp_asg_act_id
1487: and paa.assignment_action_id = pai.locked_action_id
1488: and paa.assignment_id = cp_assignment_id
1494: ,cp_assignment_id in number
1495: ,cp_tax_unit_id in number
1496: ,cp_sepchk_run_type in number) is
1497: select paa.assignment_action_id
1498: from pay_assignment_actions paa,
1499: pay_action_interlocks pai
1500: where pai.locking_action_id = cp_pp_asg_act_id
1501: and paa.assignment_action_id = pai.locked_action_id
1502: and paa.assignment_id = cp_assignment_id
1783:
1784: if ( ( ln_source_action_id <> prev_source_action_id ) or
1785: ( ln_tax_unit_id <> prev_tax_unit_id ) ) then
1786:
1787: select pay_assignment_actions_s.nextval
1788: into ln_child_xfr_action_id
1789: from dual;
1790:
1791: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1789: from dual;
1790:
1791: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1792:
1793: -- insert into pay_assignment_actions.
1794:
1795: ln_step := 3;
1796:
1797: hr_nonrun_asact.insact(ln_child_xfr_action_id,
1824: end if;
1825:
1826: ln_step := 5;
1827:
1828: update pay_assignment_actions
1829: set serial_number = lv_serial_number
1830: where assignment_action_id = ln_child_xfr_action_id;
1831:
1832: hr_utility.trace('Processing Child action for Master for Multi GRE ' ||
1926: cp_curr_pymt_eff_date date) is
1927: select distinct
1928: paa.assignment_action_id
1929: ,paa.tax_unit_id
1930: from pay_assignment_actions paa
1931: ,pay_pre_payments ppp
1932: ,pay_org_payment_methods popm
1933: where paa.source_action_id = cp_master_prepay_act_id
1934: and ppp.assignment_action_id = paa.assignment_action_id
1944: cp_curr_pymt_eff_date date) is
1945: select distinct
1946: paa.assignment_action_id
1947: ,paa.tax_unit_id
1948: from pay_assignment_actions paa
1949: ,pay_pre_payments ppp
1950: ,pay_org_payment_methods popm
1951: where paa.assignment_action_id = cp_master_prepay_act_id
1952: and ppp.assignment_action_id = paa.assignment_action_id
1958: and popm.effective_end_date;
1959:
1960: cursor c_tax_unit(cp_pp_asg_act_id number) is
1961: select distinct paa.tax_unit_id
1962: from pay_assignment_actions paa
1963: ,pay_action_interlocks pai
1964: where pai.locking_action_id = cp_pp_asg_act_id
1965: and paa.assignment_action_id = pai.locked_action_id
1966: and paa.tax_unit_id is not null;
1966: and paa.tax_unit_id is not null;
1967:
1968: cursor c_run_aa_id(cp_pp_asg_act_id number) is
1969: select paa.assignment_action_id, paa.source_action_id
1970: from pay_assignment_actions paa
1971: ,pay_action_interlocks pai
1972: where pai.locking_action_id = cp_pp_asg_act_id
1973: and paa.assignment_action_id = pai.locked_action_id
1974: and paa.source_action_id is not null
2037:
2038: hr_utility.trace('MG ln_pp_asg_act_id : ' || ln_pp_asg_act_id);
2039: hr_utility.trace('MG ln_tax_unit_id : ' || ln_tax_unit_id);
2040:
2041: select pay_assignment_actions_s.nextval
2042: into ln_child_xfr_action_id
2043: from dual;
2044:
2045: hr_utility.set_location(gv_package || lv_procedure_name, 30);
2043: from dual;
2044:
2045: hr_utility.set_location(gv_package || lv_procedure_name, 30);
2046:
2047: -- insert into pay_assignment_actions.
2048:
2049: ln_step := 3;
2050:
2051: hr_nonrun_asact.insact(ln_child_xfr_action_id,
2072: lv_serial_number := p_master_action_type || 'N' || ln_pp_asg_act_id;
2073:
2074: ln_step := 5;
2075:
2076: update pay_assignment_actions
2077: set serial_number = lv_serial_number
2078: where assignment_action_id = ln_child_xfr_action_id;
2079:
2080: hr_utility.trace('Processing Child action for Master for Multi GRE ' ||
2181: paa.tax_unit_id,
2182: paa.assignment_action_id,
2183: ppp.source_action_id
2184: from pay_payroll_actions ppa
2185: ,pay_assignment_actions paa
2186: ,pay_pre_payments ppp
2187: ,pay_org_payment_methods popm
2188: where ppa.consolidation_set_id
2189: = nvl(cp_cons_set_id,ppa.consolidation_set_id)
2213: paa.tax_unit_id,
2214: paa.assignment_action_id,
2215: ppp.source_action_id
2216: from pay_pre_payments ppp
2217: ,pay_assignment_actions paa
2218: ,pay_org_payment_methods popm
2219: where paa.assignment_action_id = cp_prepayment_action_id
2220: and ppp.assignment_action_id = paa.assignment_action_id
2221: and nvl(ppp.value,0) <> 0
2230: (cp_master_prepay_action_id in number
2231: ,cp_sepchk_run_type_id in number) is
2232: select pai.locked_action_id
2233: from pay_action_interlocks pai,
2234: pay_assignment_actions paa
2235: where pai.locking_action_id = cp_master_prepay_action_id
2236: and paa.assignment_action_id = pai.locked_action_id
2237: and paa.source_action_id is not null
2238: and paa.run_type_id = cp_sepchk_run_type_id;
2238: and paa.run_type_id = cp_sepchk_run_type_id;
2239:
2240: cursor c_tax_unit(cp_source_action_id number) is
2241: select paa.tax_unit_id
2242: from pay_assignment_actions paa
2243: where paa.assignment_action_id = cp_source_action_id;
2244:
2245: ln_assignment_id NUMBER;
2246: ln_tax_unit_id NUMBER;
2344: end if;
2345:
2346: -- create child assignment action
2347: ln_step := 9;
2348: select pay_assignment_actions_s.nextval
2349: into ln_child_xfr_action_id
2350: from dual;
2351:
2352: hr_utility.set_location(gv_package || lv_procedure_name, 90);
2349: into ln_child_xfr_action_id
2350: from dual;
2351:
2352: hr_utility.set_location(gv_package || lv_procedure_name, 90);
2353: -- insert into pay_assignment_actions.
2354: ln_step := 10;
2355: hr_nonrun_asact.insact(ln_child_xfr_action_id,
2356: ln_assignment_id,
2357: p_xfr_payroll_action_id,
2377:
2378: lv_serial_number := p_master_action_type || 'Y' || ln_run_action_id;
2379:
2380: ln_step := 12;
2381: update pay_assignment_actions
2382: set serial_number = lv_serial_number
2383: where assignment_action_id = ln_child_xfr_action_id;
2384:
2385: hr_utility.trace('Processing Child action for Master ' ||
2454: paa.assignment_id,
2455: paa.tax_unit_id,
2456: paa.serial_number,
2457: paa.chunk_number
2458: from pay_assignment_actions paa
2459: where paa.assignment_action_id = cp_assignment_action;
2460:
2461: cursor c_legislation (cp_business_group in number) is
2462: select org_information9
2472:
2473: cursor c_assignment_run (cp_prepayment_action_id in number) is
2474: select distinct paa.assignment_id
2475: from pay_action_interlocks pai,
2476: pay_assignment_actions paa,
2477: pay_payroll_actions ppa
2478: where pai.locking_action_id = cp_prepayment_action_id
2479: and paa.assignment_action_id = pai.locked_action_id
2480: and ppa.payroll_action_id = paa.payroll_action_id
2490: cp_assignment_id in number) is
2491: select paa.assignment_action_id, paa.payroll_action_id,
2492: ppa.action_type
2493: from pay_payroll_actions ppa,
2494: pay_assignment_actions paa,
2495: pay_action_interlocks pai
2496: where pai.locking_action_Id = cp_prepayment_action_id
2497: and pai.locked_action_id = paa.assignment_action_id
2498: and paa.assignment_id = cp_assignment_id
2502:
2503: cursor c_pymt_eff_date(cp_prepayment_action_id in number) is
2504: select effective_date
2505: from pay_payroll_actions ppa,
2506: pay_assignment_actions paa
2507: where ppa.payroll_action_id = paa.payroll_action_id
2508: and paa.assignment_action_id = cp_prepayment_action_id;
2509:
2510: cursor c_check_pay_action( cp_payroll_action_id in number) is
2530: cp_prepayment_action_id in number,
2531: cp_assignment_id in number) is
2532: select distinct ppa.action_type
2533: from pay_payroll_actions ppa,
2534: pay_assignment_actions paa,
2535: pay_action_interlocks pai
2536: where pai.locking_action_Id = cp_prepayment_action_id
2537: and pai.locked_action_id = paa.assignment_action_id
2538: and paa.assignment_id = cp_assignment_id
3092: /* removed the reversal validation from range cursor SQL STMT
3093: ''V'', nvl(ppa.future_process_mode, ''Y''). Bug#3498653 */
3094: lv_sql_string :=
3095: 'select distinct paa.assignment_id
3096: from pay_assignment_actions paa,
3097: pay_payroll_actions ppa
3098: where ppa.business_group_id = ''' || ln_business_group_id || '''
3099: and ppa.effective_date between fnd_date.canonical_to_date(''' ||
3100: fnd_date.date_to_canonical(ld_start_date) || ''')
3112: and paa.source_action_id is null
3113: and not exists
3114: (select ''x''
3115: from pay_action_interlocks pai,
3116: pay_assignment_actions paa1,
3117: pay_payroll_actions ppa1
3118: where pai.locked_action_id = paa.assignment_action_id
3119: and paa1.assignment_action_id = pai.locking_action_id
3120: and ppa1.payroll_action_id = paa1.payroll_action_id
3169: ,cp_business_group_id in number
3170: ,cp_start_date in date
3171: ,cp_end_date in date
3172: ) is
3173: select /*+ INDEX (PAA PAY_ASSIGNMENT_ACTIONS_N50) */
3174: paa.assignment_id,
3175: paa.tax_unit_id,
3176: ppa.effective_date,
3177: ppa.date_earned,
3178: ppa.action_type,
3179: paa.assignment_action_id,
3180: paa.payroll_action_id
3181: from pay_payroll_actions ppa,
3182: pay_assignment_actions paa
3183: where paa.assignment_id between cp_start_assignment_id
3184: and cp_end_assignment_id
3185: and ppa.consolidation_set_id = cp_cons_set_id
3186: and paa.action_status = 'C'
3197: and paa.source_action_id is null
3198: and not exists
3199: (select 'x'
3200: from pay_action_interlocks pai1,
3201: pay_assignment_actions paa1,
3202: pay_payroll_actions ppa1
3203: where pai1.locked_action_id = paa.assignment_action_id
3204: and paa1.assignment_action_id = pai1.locking_action_id
3205: and ppa1.payroll_action_id = paa1.payroll_action_id
3209:
3210: cursor c_master_action(cp_prepayment_action_id number) is
3211: select max(paa.assignment_action_id)
3212: from pay_payroll_actions ppa,
3213: pay_assignment_actions paa,
3214: pay_action_interlocks pai
3215: where pai.locking_action_Id = cp_prepayment_action_id
3216: and pai.locked_action_id = paa.assignment_action_id
3217: and paa.source_action_id is null
3219: and ppa.action_type in ('R', 'Q');
3220:
3221: cursor c_lock_chld_pp_aa(cp_prepay_master_aa_id number) is
3222: select paa.assignment_action_id
3223: from pay_assignment_actions paa
3224: where paa.source_action_id = cp_prepay_master_aa_id;
3225:
3226: ln_assignment_id NUMBER;
3227: ln_tax_unit_id NUMBER;
3343: hr_utility.set_location(gv_package || lv_procedure_name, 60);
3344: hr_utility.trace('Action_type = '||lv_action_type );
3345:
3346: ln_step := 6;
3347: select pay_assignment_actions_s.nextval
3348: into ln_xfr_action_id
3349: from dual;
3350:
3351: -- insert into pay_assignment_actions.
3347: select pay_assignment_actions_s.nextval
3348: into ln_xfr_action_id
3349: from dual;
3350:
3351: -- insert into pay_assignment_actions.
3352: ln_step := 7;
3353: hr_nonrun_asact.insact(ln_xfr_action_id,
3354: ln_assignment_id,
3355: p_payroll_action_id,
3390: lv_serial_number := lv_action_type || 'N' ||
3391: ln_asg_action_id;
3392:
3393: ln_step := 9;
3394: update pay_assignment_actions
3395: set serial_number = lv_serial_number
3396: where assignment_action_id = ln_xfr_action_id;
3397:
3398: hr_utility.set_location(gv_package || lv_procedure_name, 100);