[Home] [Help]
39: into l_date_earned
40: from pay_payroll_actions locked_pact,
41: pay_assignment_actions locked,
42: pay_assignment_actions locking,
43: pay_action_interlocks locks
44: where locking.payroll_action_id = p_payroll_action_id
45: and locking.assignment_action_id = locks.locking_action_id
46: and locked.assignment_action_id = locks.locked_action_id
47: and locked.payroll_action_id = locked_pact.payroll_action_id;
1140: where paa.source_action_id = p_run_act
1141: and paa.assignment_id = p_asg_id
1142: and paa.payroll_action_id = p_pact_id
1143: and not exists (select ''
1144: from pay_action_interlocks pai2
1145: where pai2.locking_action_id = p_pre_act
1146: and pai2.locked_action_id = paa.assignment_action_id
1147: );
1148: --
1151: select paa.assignment_action_id
1152: from pay_action_classifications pcl,
1153: pay_payroll_actions pac,
1154: pay_assignment_actions paa,
1155: pay_action_interlocks pai
1156: where pai.locked_action_id = p_run_act
1157: and pai.locking_action_id = paa.assignment_action_id
1158: and paa.assignment_action_id <> p_cost_act
1159: and pac.payroll_action_id = paa.payroll_action_id
1159: and pac.payroll_action_id = paa.payroll_action_id
1160: and pcl.action_type = pac.action_type
1161: and pcl.classification_name = 'TRANSGL'
1162: and not exists (select ''
1163: from pay_action_interlocks pai2
1164: where pai2.locking_action_id = p_cost_act
1165: and pai2.locked_action_id = paa.assignment_action_id
1166: );
1167: --
1214: is
1215: select paa.assignment_action_id,
1216: paa.assignment_id,
1217: paa.payroll_action_id
1218: from pay_action_interlocks pai,
1219: pay_assignment_actions paa
1220: where pai.locking_action_id = p_act
1221: and pai.locked_action_id = paa.assignment_action_id
1222: and paa.source_action_id is null;
1245: lockingactid in number,
1246: lockedactid in number
1247: ) is
1248: begin
1249: insert into pay_action_interlocks (
1250: locking_action_id,
1251: locked_action_id)
1252: values (lockingactid,
1253: lockedactid);
1465: or pa1.org_payment_method_id is null)
1466: and not exists (
1467: select null
1468: from pay_assignment_actions ac2,
1469: pay_action_interlocks int
1470: where int.locked_action_id = act.assignment_action_id
1471: and ac2.assignment_action_id = int.locking_action_id
1472: and ac2.pre_payment_id = ppp.pre_payment_id)
1473: and not exists (
1539: or pa1.org_payment_method_id is null)
1540: and not exists (
1541: select null
1542: from pay_assignment_actions ac2,
1543: pay_action_interlocks int
1544: where int.locked_action_id = act.assignment_action_id
1545: and ac2.assignment_action_id = int.locking_action_id
1546: and ac2.pre_payment_id = ppp.pre_payment_id)
1547: and not exists (
1615: or pa1.org_payment_method_id is null)
1616: and not exists (
1617: select null
1618: from pay_assignment_actions ac2,
1619: pay_action_interlocks int
1620: where int.locked_action_id = act.assignment_action_id
1621: and ac2.assignment_action_id = int.locking_action_id
1622: and ac2.pre_payment_id = ppp.pre_payment_id)
1623: and not exists (
2104: or pa1.org_payment_method_id is null)
2105: and not exists (
2106: select /*+ ORDERED*/
2107: null
2108: from pay_action_interlocks int,
2109: pay_assignment_actions ac2
2110: where int.locked_action_id = act.assignment_action_id
2111: and ac2.assignment_action_id = int.locking_action_id
2112: and ac2.pre_payment_id = ppp.pre_payment_id
2112: and ac2.pre_payment_id = ppp.pre_payment_id
2113: and not exists (
2114: select null
2115: from pay_assignment_actions paa_void,
2116: pay_action_interlocks pai_void,
2117: pay_payroll_actions ppa_void
2118: where pai_void.locked_action_id = ac2.assignment_action_id
2119: and pai_void.locking_action_id = paa_void.assignment_action_id
2120: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2193: or pa1.org_payment_method_id is null)
2194: and not exists (
2195: select /*+ ORDERED*/
2196: null
2197: from pay_action_interlocks int,
2198: pay_assignment_actions ac2
2199: where int.locked_action_id = act.assignment_action_id
2200: and ac2.assignment_action_id = int.locking_action_id
2201: and ac2.pre_payment_id = ppp.pre_payment_id
2201: and ac2.pre_payment_id = ppp.pre_payment_id
2202: and not exists (
2203: select null
2204: from pay_assignment_actions paa_void,
2205: pay_action_interlocks pai_void,
2206: pay_payroll_actions ppa_void
2207: where pai_void.locked_action_id = ac2.assignment_action_id
2208: and pai_void.locking_action_id = paa_void.assignment_action_id
2209: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2286: or pa1.org_payment_method_id is null)
2287: and not exists (
2288: select /*+ ORDERED*/
2289: null
2290: from pay_action_interlocks int,
2291: pay_assignment_actions ac2
2292: where int.locked_action_id = act.assignment_action_id
2293: and ac2.assignment_action_id = int.locking_action_id
2294: and ac2.pre_payment_id = ppp.pre_payment_id
2294: and ac2.pre_payment_id = ppp.pre_payment_id
2295: and not exists (
2296: select null
2297: from pay_assignment_actions paa_void,
2298: pay_action_interlocks pai_void,
2299: pay_payroll_actions ppa_void
2300: where pai_void.locked_action_id = ac2.assignment_action_id
2301: and pai_void.locking_action_id = paa_void.assignment_action_id
2302: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2347: and hasa.include_or_exclude = 'E')
2348: -- Ensure there exists a voided check for this payment.
2349: and exists
2350: (select 1
2351: from pay_action_interlocks lck1,
2352: pay_assignment_actions chk_paa,
2353: pay_payroll_actions chk_ppa,
2354: pay_action_interlocks lck2,
2355: pay_assignment_actions vd_paa,
2350: (select 1
2351: from pay_action_interlocks lck1,
2352: pay_assignment_actions chk_paa,
2353: pay_payroll_actions chk_ppa,
2354: pay_action_interlocks lck2,
2355: pay_assignment_actions vd_paa,
2356: pay_payroll_actions vd_ppa
2357: where lck1.locked_action_id = plockedid
2358: and lck1.locking_action_id = chk_paa.assignment_action_id
2541: and as3.person_id = as2.person_id)
2542: and not exists (
2543: select /*+ ORDERED*/
2544: null
2545: from pay_action_interlocks int,
2546: pay_assignment_actions ac2
2547: where int.locked_action_id = act.assignment_action_id
2548: and ac2.assignment_action_id = int.locking_action_id
2549: and ac2.pre_payment_id = ppp.pre_payment_id
2549: and ac2.pre_payment_id = ppp.pre_payment_id
2550: and not exists (
2551: select null
2552: from pay_assignment_actions paa_void,
2553: pay_action_interlocks pai_void,
2554: pay_payroll_actions ppa_void
2555: where pai_void.locked_action_id = ac2.assignment_action_id
2556: and pai_void.locking_action_id = paa_void.assignment_action_id
2557: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2627: and as3.person_id = as2.person_id)
2628: and not exists (
2629: select /*+ ORDERED*/
2630: null
2631: from pay_action_interlocks int,
2632: pay_assignment_actions ac2
2633: where int.locked_action_id = act.assignment_action_id
2634: and ac2.assignment_action_id = int.locking_action_id
2635: and ac2.pre_payment_id = ppp.pre_payment_id
2635: and ac2.pre_payment_id = ppp.pre_payment_id
2636: and not exists (
2637: select null
2638: from pay_assignment_actions paa_void,
2639: pay_action_interlocks pai_void,
2640: pay_payroll_actions ppa_void
2641: where pai_void.locked_action_id = ac2.assignment_action_id
2642: and pai_void.locking_action_id = paa_void.assignment_action_id
2643: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2715: and as3.person_id = as2.person_id)
2716: and not exists (
2717: select /*+ ORDERED*/
2718: null
2719: from pay_action_interlocks int,
2720: pay_assignment_actions ac2
2721: where int.locked_action_id = act.assignment_action_id
2722: and ac2.assignment_action_id = int.locking_action_id
2723: and ac2.pre_payment_id = ppp.pre_payment_id
2723: and ac2.pre_payment_id = ppp.pre_payment_id
2724: and not exists (
2725: select null
2726: from pay_assignment_actions paa_void,
2727: pay_action_interlocks pai_void,
2728: pay_payroll_actions ppa_void
2729: where pai_void.locked_action_id = ac2.assignment_action_id
2730: and pai_void.locking_action_id = paa_void.assignment_action_id
2731: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2871: and not exists (
2872: select null
2873: from pay_assignment_actions ac2,
2874: pay_payroll_actions pa3,
2875: pay_action_interlocks int
2876: where int.locked_action_id = act.assignment_action_id
2877: and ac2.assignment_action_id = int.locking_action_id
2878: and pa3.payroll_action_id = ac2.payroll_action_id
2879: and pa3.action_type in ('P', 'U'))
2941: and not exists (
2942: select null
2943: from pay_assignment_actions ac2,
2944: pay_payroll_actions pa3,
2945: pay_action_interlocks int
2946: where int.locked_action_id = act.assignment_action_id
2947: and ac2.assignment_action_id = int.locking_action_id
2948: and pa3.payroll_action_id = ac2.payroll_action_id
2949: and pa3.action_type in ('P', 'U'))
3015: and not exists (
3016: select null
3017: from pay_assignment_actions ac2,
3018: pay_payroll_actions pa3,
3019: pay_action_interlocks int
3020: where int.locked_action_id = act.assignment_action_id
3021: and ac2.assignment_action_id = int.locking_action_id
3022: and pa3.payroll_action_id = ac2.payroll_action_id
3023: and pa3.action_type in ('P', 'U'))
3182: and not exists (
3183: select null
3184: from pay_assignment_actions ac2,
3185: pay_payroll_actions pa3,
3186: pay_action_interlocks int
3187: where int.locked_action_id = act.assignment_action_id
3188: and ac2.assignment_action_id = int.locking_action_id
3189: and pa3.payroll_action_id = ac2.payroll_action_id
3190: and pa3.action_type in ('C', 'S'))
3247: and not exists (
3248: select null
3249: from pay_assignment_actions ac2,
3250: pay_payroll_actions pa3,
3251: pay_action_interlocks int
3252: where int.locked_action_id = act.assignment_action_id
3253: and ac2.assignment_action_id = int.locking_action_id
3254: and pa3.payroll_action_id = ac2.payroll_action_id
3255: and pa3.action_type in ('C', 'S'))
3315: and not exists (
3316: select null
3317: from pay_assignment_actions ac2,
3318: pay_payroll_actions pa3,
3319: pay_action_interlocks int
3320: where int.locked_action_id = act.assignment_action_id
3321: and ac2.assignment_action_id = int.locking_action_id
3322: and pa3.payroll_action_id = ac2.payroll_action_id
3323: and pa3.action_type in ('C', 'S'))
3458: and not exists (
3459: select null
3460: from pay_assignment_actions ac2,
3461: pay_payroll_actions pa3,
3462: pay_action_interlocks int
3463: where int.locked_action_id = act.assignment_action_id
3464: and ac2.assignment_action_id = int.locking_action_id
3465: and pa3.payroll_action_id = ac2.payroll_action_id
3466: and pa3.action_type = 'CP')
3513: pom.effective_start_date and pom.effective_end_date))
3514: or (pa2.action_type = 'D'
3515: and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3516: and exists (select 1
3517: from pay_action_interlocks int,
3518: pay_assignment_actions chq,
3519: pay_payroll_actions pcq,
3520: pay_pre_payments ppp,
3521: pay_org_payment_methods_f pom,
3531: and pa2.effective_date between
3532: pom.effective_start_date and pom.effective_end_date))
3533: or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3534: and exists (select 1
3535: from pay_action_interlocks int,
3536: pay_assignment_actions chq,
3537: pay_payroll_actions pcq,
3538: pay_pre_payments ppp,
3539: pay_org_payment_methods_f pom
3594: and not exists (
3595: select null
3596: from pay_assignment_actions ac2,
3597: pay_payroll_actions pa3,
3598: pay_action_interlocks int
3599: where int.locked_action_id = act.assignment_action_id
3600: and ac2.assignment_action_id = int.locking_action_id
3601: and pa3.payroll_action_id = ac2.payroll_action_id
3602: and pa3.action_type = 'CP')
3649: pom.effective_start_date and pom.effective_end_date))
3650: or (pa2.action_type = 'D'
3651: and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3652: and exists (select 1
3653: from pay_action_interlocks int,
3654: pay_assignment_actions chq,
3655: pay_payroll_actions pcq,
3656: pay_pre_payments ppp,
3657: pay_org_payment_methods_f pom,
3667: and pa2.effective_date between
3668: pom.effective_start_date and pom.effective_end_date))
3669: or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3670: and exists (select 1
3671: from pay_action_interlocks int,
3672: pay_assignment_actions chq,
3673: pay_payroll_actions pcq,
3674: pay_pre_payments ppp,
3675: pay_org_payment_methods_f pom
3734: and not exists (
3735: select null
3736: from pay_assignment_actions ac2,
3737: pay_payroll_actions pa3,
3738: pay_action_interlocks int
3739: where int.locked_action_id = act.assignment_action_id
3740: and ac2.assignment_action_id = int.locking_action_id
3741: and pa3.payroll_action_id = ac2.payroll_action_id
3742: and pa3.action_type = 'CP')
3789: pom.effective_start_date and pom.effective_end_date))
3790: or (pa2.action_type = 'D'
3791: and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3792: and exists (select 1
3793: from pay_action_interlocks int,
3794: pay_assignment_actions chq,
3795: pay_payroll_actions pcq,
3796: pay_pre_payments ppp,
3797: pay_org_payment_methods_f pom,
3807: and pa2.effective_date between
3808: pom.effective_start_date and pom.effective_end_date))
3809: or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3810: and exists (select 1
3811: from pay_action_interlocks int,
3812: pay_assignment_actions chq,
3813: pay_payroll_actions pcq,
3814: pay_pre_payments ppp,
3815: pay_org_payment_methods_f pom
4260: and not exists (
4261: select null
4262: from pay_assignment_actions ac3,
4263: pay_payroll_actions pa3,
4264: pay_action_interlocks in3
4265: where in3.locked_action_id = ac2.assignment_action_id
4266: and ac3.assignment_action_id = in3.locking_action_id
4267: and pa3.payroll_action_id = ac3.payroll_action_id
4268: and pa3.action_type = pa.action_type)
4328: and not exists (
4329: select null
4330: from pay_assignment_actions ac3,
4331: pay_payroll_actions pa3,
4332: pay_action_interlocks in3
4333: where in3.locked_action_id = ac2.assignment_action_id
4334: and ac3.assignment_action_id = in3.locking_action_id
4335: and pa3.payroll_action_id = ac3.payroll_action_id
4336: and pa3.action_type = pa.action_type)
4398: and not exists (
4399: select null
4400: from pay_assignment_actions ac3,
4401: pay_payroll_actions pa3,
4402: pay_action_interlocks in3
4403: where in3.locked_action_id = ac2.assignment_action_id
4404: and ac3.assignment_action_id = in3.locking_action_id
4405: and pa3.payroll_action_id = ac3.payroll_action_id
4406: and pa3.action_type = pa.action_type)
4424: pactid number,
4425: costactid number
4426: ) is
4427: select ac1.assignment_action_id
4428: from pay_action_interlocks in2,
4429: pay_assignment_actions ac1,
4430: pay_payroll_actions pa1,
4431: pay_action_classifications pcl1,
4432: per_all_assignments_f as1,
4511: if (actype = 'CP') then
4512:
4513: select distinct(pa.action_type)
4514: into pmnt_act_type
4515: from pay_action_interlocks int,
4516: pay_assignment_actions aa,
4517: pay_payroll_actions pa
4518: where int.locking_action_id = lockedactid
4519: and aa.assignment_action_id = int.locked_action_id
4523: -- Bug 6919216 - Fixed query to consider only payments
4524: -- that are costed and needed be to transferred to GL.
4525: select count(*)
4526: into not_paid
4527: from pay_action_interlocks int,
4528: pay_pre_payments ppp,
4529: pay_org_payment_methods_f opm,
4530: pay_assignment_actions paa,
4531: pay_payroll_actions ppa
4786: and as3.person_id = as2.person_id)
4787: and not exists (
4788: select /*+ ORDERED*/
4789: null
4790: from pay_action_interlocks int,
4791: pay_assignment_actions ac2
4792: where int.locked_action_id = act.assignment_action_id
4793: and ac2.assignment_action_id = int.locking_action_id
4794: and ac2.pre_payment_id = ppp.pre_payment_id
4794: and ac2.pre_payment_id = ppp.pre_payment_id
4795: and not exists (
4796: select null
4797: from pay_assignment_actions paa_void,
4798: pay_action_interlocks pai_void,
4799: pay_payroll_actions ppa_void
4800: where pai_void.locked_action_id = ac2.assignment_action_id
4801: and pai_void.locking_action_id = paa_void.assignment_action_id
4802: and paa_void.payroll_action_id = ppa_void.payroll_action_id
4872: and as3.person_id = as2.person_id)
4873: and not exists (
4874: select /*+ ORDERED*/
4875: null
4876: from pay_action_interlocks int,
4877: pay_assignment_actions ac2
4878: where int.locked_action_id = act.assignment_action_id
4879: and ac2.assignment_action_id = int.locking_action_id
4880: and ac2.pre_payment_id = ppp.pre_payment_id
4880: and ac2.pre_payment_id = ppp.pre_payment_id
4881: and not exists (
4882: select null
4883: from pay_assignment_actions paa_void,
4884: pay_action_interlocks pai_void,
4885: pay_payroll_actions ppa_void
4886: where pai_void.locked_action_id = ac2.assignment_action_id
4887: and pai_void.locking_action_id = paa_void.assignment_action_id
4888: and paa_void.payroll_action_id = ppa_void.payroll_action_id
4960: and as3.person_id = as2.person_id)
4961: and not exists (
4962: select /*+ ORDERED*/
4963: null
4964: from pay_action_interlocks int,
4965: pay_assignment_actions ac2
4966: where int.locked_action_id = act.assignment_action_id
4967: and ac2.assignment_action_id = int.locking_action_id
4968: and ac2.pre_payment_id = ppp.pre_payment_id
4968: and ac2.pre_payment_id = ppp.pre_payment_id
4969: and not exists (
4970: select null
4971: from pay_assignment_actions paa_void,
4972: pay_action_interlocks pai_void,
4973: pay_payroll_actions ppa_void
4974: where pai_void.locked_action_id = ac2.assignment_action_id
4975: and pai_void.locking_action_id = paa_void.assignment_action_id
4976: and paa_void.payroll_action_id = ppa_void.payroll_action_id