[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
2280: or pa1.org_payment_method_id is null)
2281: and not exists (
2282: select /* Bug 6522667, moved ORDERED hint */
2283: null
2284: from pay_action_interlocks int,
2285: pay_assignment_actions ac2
2286: where int.locked_action_id = act.assignment_action_id
2287: and ac2.assignment_action_id = int.locking_action_id
2288: and ac2.pre_payment_id = ppp.pre_payment_id
2288: and ac2.pre_payment_id = ppp.pre_payment_id
2289: and not exists (
2290: select null
2291: from pay_assignment_actions paa_void,
2292: pay_action_interlocks pai_void,
2293: pay_payroll_actions ppa_void
2294: where pai_void.locked_action_id = ac2.assignment_action_id
2295: and pai_void.locking_action_id = paa_void.assignment_action_id
2296: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2341: and hasa.include_or_exclude = 'E')
2342: -- Ensure there exists a voided check for this payment.
2343: and exists
2344: (select 1
2345: from pay_action_interlocks lck1,
2346: pay_assignment_actions chk_paa,
2347: pay_payroll_actions chk_ppa,
2348: pay_action_interlocks lck2,
2349: pay_assignment_actions vd_paa,
2344: (select 1
2345: from pay_action_interlocks lck1,
2346: pay_assignment_actions chk_paa,
2347: pay_payroll_actions chk_ppa,
2348: pay_action_interlocks lck2,
2349: pay_assignment_actions vd_paa,
2350: pay_payroll_actions vd_ppa
2351: where lck1.locked_action_id = plockedid
2352: and lck1.locking_action_id = chk_paa.assignment_action_id
2535: and as3.person_id = as2.person_id)
2536: and not exists (
2537: select /*+ ORDERED*/
2538: null
2539: from pay_action_interlocks int,
2540: pay_assignment_actions ac2
2541: where int.locked_action_id = act.assignment_action_id
2542: and ac2.assignment_action_id = int.locking_action_id
2543: and ac2.pre_payment_id = ppp.pre_payment_id
2543: and ac2.pre_payment_id = ppp.pre_payment_id
2544: and not exists (
2545: select null
2546: from pay_assignment_actions paa_void,
2547: pay_action_interlocks pai_void,
2548: pay_payroll_actions ppa_void
2549: where pai_void.locked_action_id = ac2.assignment_action_id
2550: and pai_void.locking_action_id = paa_void.assignment_action_id
2551: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2621: and as3.person_id = as2.person_id)
2622: and not exists (
2623: select /*+ ORDERED*/
2624: null
2625: from pay_action_interlocks int,
2626: pay_assignment_actions ac2
2627: where int.locked_action_id = act.assignment_action_id
2628: and ac2.assignment_action_id = int.locking_action_id
2629: and ac2.pre_payment_id = ppp.pre_payment_id
2629: and ac2.pre_payment_id = ppp.pre_payment_id
2630: and not exists (
2631: select null
2632: from pay_assignment_actions paa_void,
2633: pay_action_interlocks pai_void,
2634: pay_payroll_actions ppa_void
2635: where pai_void.locked_action_id = ac2.assignment_action_id
2636: and pai_void.locking_action_id = paa_void.assignment_action_id
2637: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2709: and as3.person_id = as2.person_id)
2710: and not exists (
2711: select /*+ ORDERED*/
2712: null
2713: from pay_action_interlocks int,
2714: pay_assignment_actions ac2
2715: where int.locked_action_id = act.assignment_action_id
2716: and ac2.assignment_action_id = int.locking_action_id
2717: and ac2.pre_payment_id = ppp.pre_payment_id
2717: and ac2.pre_payment_id = ppp.pre_payment_id
2718: and not exists (
2719: select null
2720: from pay_assignment_actions paa_void,
2721: pay_action_interlocks pai_void,
2722: pay_payroll_actions ppa_void
2723: where pai_void.locked_action_id = ac2.assignment_action_id
2724: and pai_void.locking_action_id = paa_void.assignment_action_id
2725: and paa_void.payroll_action_id = ppa_void.payroll_action_id
2865: and not exists (
2866: select null
2867: from pay_assignment_actions ac2,
2868: pay_payroll_actions pa3,
2869: pay_action_interlocks int
2870: where int.locked_action_id = act.assignment_action_id
2871: and ac2.assignment_action_id = int.locking_action_id
2872: and pa3.payroll_action_id = ac2.payroll_action_id
2873: and pa3.action_type in ('P', 'U'))
2935: and not exists (
2936: select null
2937: from pay_assignment_actions ac2,
2938: pay_payroll_actions pa3,
2939: pay_action_interlocks int
2940: where int.locked_action_id = act.assignment_action_id
2941: and ac2.assignment_action_id = int.locking_action_id
2942: and pa3.payroll_action_id = ac2.payroll_action_id
2943: and pa3.action_type in ('P', 'U'))
3009: and not exists (
3010: select null
3011: from pay_assignment_actions ac2,
3012: pay_payroll_actions pa3,
3013: pay_action_interlocks int
3014: where int.locked_action_id = act.assignment_action_id
3015: and ac2.assignment_action_id = int.locking_action_id
3016: and pa3.payroll_action_id = ac2.payroll_action_id
3017: and pa3.action_type in ('P', 'U'))
3175: and not exists (
3176: select null
3177: from pay_assignment_actions ac2,
3178: pay_payroll_actions pa3,
3179: pay_action_interlocks int
3180: where int.locked_action_id = act.assignment_action_id
3181: and ac2.assignment_action_id = int.locking_action_id
3182: and pa3.payroll_action_id = ac2.payroll_action_id
3183: and pa3.action_type in ('C', 'S'))
3239: and not exists (
3240: select null
3241: from pay_assignment_actions ac2,
3242: pay_payroll_actions pa3,
3243: pay_action_interlocks int
3244: where int.locked_action_id = act.assignment_action_id
3245: and ac2.assignment_action_id = int.locking_action_id
3246: and pa3.payroll_action_id = ac2.payroll_action_id
3247: and pa3.action_type in ('C', 'S'))
3307: and not exists (
3308: select null
3309: from pay_assignment_actions ac2,
3310: pay_payroll_actions pa3,
3311: pay_action_interlocks int
3312: where int.locked_action_id = act.assignment_action_id
3313: and ac2.assignment_action_id = int.locking_action_id
3314: and pa3.payroll_action_id = ac2.payroll_action_id
3315: and pa3.action_type in ('C', 'S'))
3450: and not exists (
3451: select null
3452: from pay_assignment_actions ac2,
3453: pay_payroll_actions pa3,
3454: pay_action_interlocks int
3455: where int.locked_action_id = act.assignment_action_id
3456: and ac2.assignment_action_id = int.locking_action_id
3457: and pa3.payroll_action_id = ac2.payroll_action_id
3458: and pa3.action_type = 'CP')
3505: pom.effective_start_date and pom.effective_end_date))
3506: or (pa2.action_type = 'D'
3507: and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3508: and exists (select 1
3509: from pay_action_interlocks int,
3510: pay_assignment_actions chq,
3511: pay_payroll_actions pcq,
3512: pay_pre_payments ppp,
3513: pay_org_payment_methods_f pom,
3523: and pa2.effective_date between
3524: pom.effective_start_date and pom.effective_end_date))
3525: or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3526: and exists (select 1
3527: from pay_action_interlocks int,
3528: pay_assignment_actions chq,
3529: pay_payroll_actions pcq,
3530: pay_pre_payments ppp,
3531: pay_org_payment_methods_f pom
3586: and not exists (
3587: select null
3588: from pay_assignment_actions ac2,
3589: pay_payroll_actions pa3,
3590: pay_action_interlocks int
3591: where int.locked_action_id = act.assignment_action_id
3592: and ac2.assignment_action_id = int.locking_action_id
3593: and pa3.payroll_action_id = ac2.payroll_action_id
3594: and pa3.action_type = 'CP')
3641: pom.effective_start_date and pom.effective_end_date))
3642: or (pa2.action_type = 'D'
3643: and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3644: and exists (select 1
3645: from pay_action_interlocks int,
3646: pay_assignment_actions chq,
3647: pay_payroll_actions pcq,
3648: pay_pre_payments ppp,
3649: pay_org_payment_methods_f pom,
3659: and pa2.effective_date between
3660: pom.effective_start_date and pom.effective_end_date))
3661: or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3662: and exists (select 1
3663: from pay_action_interlocks int,
3664: pay_assignment_actions chq,
3665: pay_payroll_actions pcq,
3666: pay_pre_payments ppp,
3667: pay_org_payment_methods_f pom
3726: and not exists (
3727: select null
3728: from pay_assignment_actions ac2,
3729: pay_payroll_actions pa3,
3730: pay_action_interlocks int
3731: where int.locked_action_id = act.assignment_action_id
3732: and ac2.assignment_action_id = int.locking_action_id
3733: and pa3.payroll_action_id = ac2.payroll_action_id
3734: and pa3.action_type = 'CP')
3781: pom.effective_start_date and pom.effective_end_date))
3782: or (pa2.action_type = 'D'
3783: and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3784: and exists (select 1
3785: from pay_action_interlocks int,
3786: pay_assignment_actions chq,
3787: pay_payroll_actions pcq,
3788: pay_pre_payments ppp,
3789: pay_org_payment_methods_f pom,
3799: and pa2.effective_date between
3800: pom.effective_start_date and pom.effective_end_date))
3801: or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3802: and exists (select 1
3803: from pay_action_interlocks int,
3804: pay_assignment_actions chq,
3805: pay_payroll_actions pcq,
3806: pay_pre_payments ppp,
3807: pay_org_payment_methods_f pom
4251: and not exists (
4252: select null
4253: from pay_assignment_actions ac3,
4254: pay_payroll_actions pa3,
4255: pay_action_interlocks in3
4256: where in3.locked_action_id = ac2.assignment_action_id
4257: and ac3.assignment_action_id = in3.locking_action_id
4258: and pa3.payroll_action_id = ac3.payroll_action_id
4259: and pa3.action_type = pa.action_type)
4318: and not exists (
4319: select null
4320: from pay_assignment_actions ac3,
4321: pay_payroll_actions pa3,
4322: pay_action_interlocks in3
4323: where in3.locked_action_id = ac2.assignment_action_id
4324: and ac3.assignment_action_id = in3.locking_action_id
4325: and pa3.payroll_action_id = ac3.payroll_action_id
4326: and pa3.action_type = pa.action_type)
4388: and not exists (
4389: select null
4390: from pay_assignment_actions ac3,
4391: pay_payroll_actions pa3,
4392: pay_action_interlocks in3
4393: where in3.locked_action_id = ac2.assignment_action_id
4394: and ac3.assignment_action_id = in3.locking_action_id
4395: and pa3.payroll_action_id = ac3.payroll_action_id
4396: and pa3.action_type = pa.action_type)
4414: pactid number,
4415: costactid number
4416: ) is
4417: select ac1.assignment_action_id
4418: from pay_action_interlocks in2,
4419: pay_assignment_actions ac1,
4420: pay_payroll_actions pa1,
4421: pay_action_classifications pcl1,
4422: per_all_assignments_f as1,
4501: if (actype = 'CP') then
4502:
4503: select distinct(pa.action_type)
4504: into pmnt_act_type
4505: from pay_action_interlocks int,
4506: pay_assignment_actions aa,
4507: pay_payroll_actions pa
4508: where int.locking_action_id = lockedactid
4509: and aa.assignment_action_id = int.locked_action_id
4513: -- Bug 6919216 - Fixed query to consider only payments
4514: -- that are costed and needed be to transferred to GL.
4515: select count(*)
4516: into not_paid
4517: from pay_action_interlocks int,
4518: pay_pre_payments ppp,
4519: pay_org_payment_methods_f opm
4520: where int.locking_action_id = lockedactid
4521: and ppp.assignment_action_id = int.locked_action_id
4771: and as3.person_id = as2.person_id)
4772: and not exists (
4773: select /*+ ORDERED*/
4774: null
4775: from pay_action_interlocks int,
4776: pay_assignment_actions ac2
4777: where int.locked_action_id = act.assignment_action_id
4778: and ac2.assignment_action_id = int.locking_action_id
4779: and ac2.pre_payment_id = ppp.pre_payment_id
4779: and ac2.pre_payment_id = ppp.pre_payment_id
4780: and not exists (
4781: select null
4782: from pay_assignment_actions paa_void,
4783: pay_action_interlocks pai_void,
4784: pay_payroll_actions ppa_void
4785: where pai_void.locked_action_id = ac2.assignment_action_id
4786: and pai_void.locking_action_id = paa_void.assignment_action_id
4787: and paa_void.payroll_action_id = ppa_void.payroll_action_id
4857: and as3.person_id = as2.person_id)
4858: and not exists (
4859: select /*+ ORDERED*/
4860: null
4861: from pay_action_interlocks int,
4862: pay_assignment_actions ac2
4863: where int.locked_action_id = act.assignment_action_id
4864: and ac2.assignment_action_id = int.locking_action_id
4865: and ac2.pre_payment_id = ppp.pre_payment_id
4865: and ac2.pre_payment_id = ppp.pre_payment_id
4866: and not exists (
4867: select null
4868: from pay_assignment_actions paa_void,
4869: pay_action_interlocks pai_void,
4870: pay_payroll_actions ppa_void
4871: where pai_void.locked_action_id = ac2.assignment_action_id
4872: and pai_void.locking_action_id = paa_void.assignment_action_id
4873: and paa_void.payroll_action_id = ppa_void.payroll_action_id
4945: and as3.person_id = as2.person_id)
4946: and not exists (
4947: select /*+ ORDERED*/
4948: null
4949: from pay_action_interlocks int,
4950: pay_assignment_actions ac2
4951: where int.locked_action_id = act.assignment_action_id
4952: and ac2.assignment_action_id = int.locking_action_id
4953: and ac2.pre_payment_id = ppp.pre_payment_id
4953: and ac2.pre_payment_id = ppp.pre_payment_id
4954: and not exists (
4955: select null
4956: from pay_assignment_actions paa_void,
4957: pay_action_interlocks pai_void,
4958: pay_payroll_actions ppa_void
4959: where pai_void.locked_action_id = ac2.assignment_action_id
4960: and pai_void.locking_action_id = paa_void.assignment_action_id
4961: and paa_void.payroll_action_id = ppa_void.payroll_action_id