[Home] [Help]
1419: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
1420: INDEX(as1 PER_ASSIGNMENTS_N4)
1421: INDEX(as2 PER_ASSIGNMENTS_F_PK)
1422: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
1423: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
1424: USE_NL(pop pos ppp opm as1 act as2) */
1425: act.assignment_action_id,
1426: act.assignment_id,
1427: act.tax_unit_id,
1434: per_all_assignments_f as1,
1435: pay_assignment_actions act,
1436: per_all_assignments_f as2,
1437: pay_pre_payments ppp,
1438: pay_org_payment_methods_f opm
1439: where pa1.payroll_action_id = pactid
1440: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
1441: and pa2.effective_date between
1442: pa1.start_date and pa1.effective_date
1496: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
1497: INDEX(as1 PER_ASSIGNMENTS_N4)
1498: INDEX(as2 PER_ASSIGNMENTS_F_PK)
1499: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
1500: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
1501: USE_NL(pos ppp opm as1 act as2) */
1502: act.assignment_action_id,
1503: act.assignment_id,
1504: act.tax_unit_id,
1510: per_all_assignments_f as1,
1511: pay_assignment_actions act,
1512: per_all_assignments_f as2,
1513: pay_pre_payments ppp,
1514: pay_org_payment_methods_f opm
1515: where pa1.payroll_action_id = pactid
1516: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
1517: and pa2.effective_date between
1518: pa1.start_date and pa1.effective_date
1569: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
1570: INDEX(as1 PER_ASSIGNMENTS_N4)
1571: INDEX(as2 PER_ASSIGNMENTS_F_PK)
1572: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
1573: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
1574: USE_NL(pos pop ppp opm as1 act as2) */
1575: act.assignment_action_id,
1576: act.assignment_id,
1577: act.tax_unit_id,
1584: pay_payroll_actions pa2,
1585: pay_action_classifications pcl,
1586: per_all_assignments_f as2,
1587: pay_pre_payments ppp,
1588: pay_org_payment_methods_f opm
1589: where pa1.payroll_action_id = pactid
1590: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
1591: and pa1.effective_date between
1592: pa1.start_date and pa1.effective_date
1917: pay_action_classifications pcl,
1918: pay_population_ranges pop,
1919: hr_organization_units hou,
1920: pay_pre_payments ppp,
1921: pay_org_payment_methods_f opm,
1922: pay_contributing_payments pcp
1923: where pa1.payroll_action_id = pactid
1924: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
1925: and pa2.effective_date between
2055: SELECT /*+ ORDERED
2056: index(pa2 PAY_PAYROLL_ACTIONS_N5)
2057: index(as1 PER_ASSIGNMENTS_F_N4)
2058: index(as2 PER_ASSIGNMENTS_F_PK)
2059: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2060: USE_NL(pop pos as1 as2) */
2061: act.assignment_action_id,
2062: act.assignment_id,
2063: act.tax_unit_id,
2072: per_all_assignments_f as1,
2073: pay_assignment_actions act,
2074: pay_pre_payments ppp,
2075: per_all_assignments_f as2,
2076: pay_org_payment_methods_f opm
2077: where pa1.payroll_action_id = pactid
2078: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2079: and pa2.effective_date between
2080: pa1.start_date and pa1.effective_date
2147: SELECT /*+ ORDERED
2148: index(pa2 PAY_PAYROLL_ACTIONS_N5)
2149: index(as1 PER_ASSIGNMENTS_F_N4)
2150: index(as2 PER_ASSIGNMENTS_F_PK)
2151: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2152: USE_NL(pos as1 as2) */
2153: act.assignment_action_id,
2154: act.assignment_id,
2155: act.tax_unit_id,
2163: per_all_assignments_f as1,
2164: pay_assignment_actions act,
2165: pay_pre_payments ppp,
2166: per_all_assignments_f as2,
2167: pay_org_payment_methods_f opm
2168: where pa1.payroll_action_id = pactid
2169: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2170: and pa2.effective_date between
2171: pa1.start_date and pa1.effective_date
2237: index(pos PER_PERIODS_OF_SERVICE_N3)
2238: index(as1 PER_ASSIGNMENTS_F_N4)
2239: index(act PAY_ASSIGNMENT_ACTIONS_N51)
2240: index(as2 PER_ASSIGNMENTS_F_PK)
2241: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2242: USE_NL(pos pop act pa2 as1 as2) */
2243: act.assignment_action_id,
2244: act.assignment_id,
2245: act.tax_unit_id,
2254: pay_payroll_actions pa2,
2255: pay_action_classifications pcl,
2256: pay_pre_payments ppp,
2257: per_all_assignments_f as2,
2258: pay_org_payment_methods_f opm
2259: where pa1.payroll_action_id = pactid
2260: and pa2.consolidation_set_id + 0 = pa1.consolidation_set_id
2261: and pa2.effective_date between
2262: pa1.start_date and pa1.effective_date
2484: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2485: INDEX(as1 PER_ASSIGNMENTS_N4)
2486: INDEX(as2 PER_ASSIGNMENTS_F_PK)
2487: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2488: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2489: USE_NL(pop pos ppp opm as1 act as2) */
2490: act.assignment_action_id,
2491: act.assignment_id,
2492: act.tax_unit_id,
2499: per_all_assignments_f as1,
2500: pay_assignment_actions act,
2501: per_all_assignments_f as2,
2502: pay_pre_payments ppp,
2503: pay_org_payment_methods_f opm
2504: where pa1.payroll_action_id = pactid
2505: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2506: and pa2.effective_date between
2507: pa1.start_date and pa1.effective_date
2573: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2574: INDEX(as1 PER_ASSIGNMENTS_N4)
2575: INDEX(as2 PER_ASSIGNMENTS_F_PK)
2576: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2577: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2578: USE_NL(pos ppp opm as1 act as2) */
2579: act.assignment_action_id,
2580: act.assignment_id,
2581: act.tax_unit_id,
2587: per_all_assignments_f as1,
2588: pay_assignment_actions act,
2589: per_all_assignments_f as2,
2590: pay_pre_payments ppp,
2591: pay_org_payment_methods_f opm
2592: where pa1.payroll_action_id = pactid
2593: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2594: and pa2.effective_date between
2595: pa1.start_date and pa1.effective_date
2658: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2659: INDEX(as1 PER_ASSIGNMENTS_N4)
2660: INDEX(as2 PER_ASSIGNMENTS_F_PK)
2661: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2662: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2663: USE_NL(pos pop ppp opm as1 act as2) */
2664: act.assignment_action_id,
2665: act.assignment_id,
2666: act.tax_unit_id,
2673: pay_payroll_actions pa2,
2674: pay_action_classifications pcl,
2675: per_all_assignments_f as2,
2676: pay_pre_payments ppp,
2677: pay_org_payment_methods_f opm
2678: where pa1.payroll_action_id = pactid
2679: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2680: and pa2.effective_date between
2681: pa1.start_date and pa1.effective_date
3479: and ((pa2.action_type in ('P', 'U')
3480: and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3481: and exists (select 1
3482: from pay_pre_payments ppp,
3483: pay_org_payment_methods_f pom
3484: where ppp.assignment_action_id = act.assignment_action_id
3485: and pom.org_payment_method_id = ppp.org_payment_method_id
3486: and pom.cost_payment = 'Y'
3487: and pa2.effective_date between
3489: or (pa2.action_type in ('H', 'M')
3490: and pa1.batch_process_mode in ('CLEARED', 'ALL')
3491: and exists (select 1
3492: from pay_pre_payments ppp,
3493: pay_org_payment_methods_f pom,
3494: pay_ce_reconciled_payments crp
3495: where ppp.pre_payment_id = act.pre_payment_id
3496: and pom.org_payment_method_id = ppp.org_payment_method_id
3497: and pom.cost_cleared_payment = 'Y'
3501: or (pa2.action_type = 'E'
3502: and pa1.batch_process_mode in ('CLEARED', 'ALL')
3503: and exists (select 1
3504: from pay_pre_payments ppp,
3505: pay_org_payment_methods_f pom,
3506: pay_ce_reconciled_payments crp
3507: where ppp.pre_payment_id = act.pre_payment_id
3508: and pom.org_payment_method_id = ppp.org_payment_method_id
3509: and pom.cost_cleared_payment = 'Y'
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,
3522: pay_ce_reconciled_payments crp
3523: where int.locking_action_id = act.assignment_action_id
3524: and chq.assignment_action_id = int.locked_action_id
3525: and pcq.payroll_action_id = chq.payroll_action_id
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
3540: where int.locking_action_id = act.assignment_action_id
3541: and chq.assignment_action_id = int.locked_action_id
3542: and pcq.payroll_action_id = chq.payroll_action_id
3543: and pcq.action_type = 'H'
3615: and ((pa2.action_type in ('P', 'U')
3616: and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3617: and exists (select 1
3618: from pay_pre_payments ppp,
3619: pay_org_payment_methods_f pom
3620: where ppp.assignment_action_id = act.assignment_action_id
3621: and pom.org_payment_method_id = ppp.org_payment_method_id
3622: and pom.cost_payment = 'Y'
3623: and pa2.effective_date between
3625: or (pa2.action_type in ('H', 'M')
3626: and pa1.batch_process_mode in ('CLEARED', 'ALL')
3627: and exists (select 1
3628: from pay_pre_payments ppp,
3629: pay_org_payment_methods_f pom,
3630: pay_ce_reconciled_payments crp
3631: where ppp.pre_payment_id = act.pre_payment_id
3632: and pom.org_payment_method_id = ppp.org_payment_method_id
3633: and pom.cost_cleared_payment = 'Y'
3637: or (pa2.action_type = 'E'
3638: and pa1.batch_process_mode in ('CLEARED', 'ALL')
3639: and exists (select 1
3640: from pay_pre_payments ppp,
3641: pay_org_payment_methods_f pom,
3642: pay_ce_reconciled_payments crp
3643: where ppp.pre_payment_id = act.pre_payment_id
3644: and pom.org_payment_method_id = ppp.org_payment_method_id
3645: and pom.cost_cleared_payment = 'Y'
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,
3658: pay_ce_reconciled_payments crp
3659: where int.locking_action_id = act.assignment_action_id
3660: and chq.assignment_action_id = int.locked_action_id
3661: and pcq.payroll_action_id = chq.payroll_action_id
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
3676: where int.locking_action_id = act.assignment_action_id
3677: and chq.assignment_action_id = int.locked_action_id
3678: and pcq.payroll_action_id = chq.payroll_action_id
3679: and pcq.action_type = 'H'
3755: and ((pa2.action_type in ('P', 'U')
3756: and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3757: and exists (select 1
3758: from pay_pre_payments ppp,
3759: pay_org_payment_methods_f pom
3760: where ppp.assignment_action_id = act.assignment_action_id
3761: and pom.org_payment_method_id = ppp.org_payment_method_id
3762: and pom.cost_payment = 'Y'
3763: and pa2.effective_date between
3765: or (pa2.action_type in ('H', 'M')
3766: and pa1.batch_process_mode in ('CLEARED', 'ALL')
3767: and exists (select 1
3768: from pay_pre_payments ppp,
3769: pay_org_payment_methods_f pom,
3770: pay_ce_reconciled_payments crp
3771: where ppp.pre_payment_id = act.pre_payment_id
3772: and pom.org_payment_method_id = ppp.org_payment_method_id
3773: and pom.cost_cleared_payment = 'Y'
3777: or (pa2.action_type = 'E'
3778: and pa1.batch_process_mode in ('CLEARED', 'ALL')
3779: and exists (select 1
3780: from pay_pre_payments ppp,
3781: pay_org_payment_methods_f pom,
3782: pay_ce_reconciled_payments crp
3783: where ppp.pre_payment_id = act.pre_payment_id
3784: and pom.org_payment_method_id = ppp.org_payment_method_id
3785: and pom.cost_cleared_payment = 'Y'
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,
3798: pay_ce_reconciled_payments crp
3799: where int.locking_action_id = act.assignment_action_id
3800: and chq.assignment_action_id = int.locked_action_id
3801: and pcq.payroll_action_id = chq.payroll_action_id
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
3816: where int.locking_action_id = act.assignment_action_id
3817: and chq.assignment_action_id = int.locked_action_id
3818: and pcq.payroll_action_id = chq.payroll_action_id
3819: and pcq.action_type = 'H'
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
4532: where int.locking_action_id = lockedactid
4533: and ppp.assignment_action_id = int.locked_action_id
4729: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4730: INDEX(as1 PER_ASSIGNMENTS_N4)
4731: INDEX(as2 PER_ASSIGNMENTS_F_PK)
4732: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4733: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4734: USE_NL(pop pos ppp opm as1 act as2) */
4735: act.assignment_action_id,
4736: act.assignment_id,
4737: act.tax_unit_id,
4744: per_all_assignments_f as1,
4745: pay_assignment_actions act,
4746: per_all_assignments_f as2,
4747: pay_pre_payments ppp,
4748: pay_org_payment_methods_f opm
4749: where pa1.payroll_action_id = pactid
4750: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
4751: and pa2.effective_date between
4752: pa1.start_date and pa1.effective_date
4818: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4819: INDEX(as1 PER_ASSIGNMENTS_N4)
4820: INDEX(as2 PER_ASSIGNMENTS_F_PK)
4821: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4822: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4823: USE_NL(pos ppp opm as1 act as2) */
4824: act.assignment_action_id,
4825: act.assignment_id,
4826: act.tax_unit_id,
4832: per_all_assignments_f as1,
4833: pay_assignment_actions act,
4834: per_all_assignments_f as2,
4835: pay_pre_payments ppp,
4836: pay_org_payment_methods_f opm
4837: where pa1.payroll_action_id = pactid
4838: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
4839: and pa2.effective_date between
4840: pa1.start_date and pa1.effective_date
4903: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4904: INDEX(as1 PER_ASSIGNMENTS_N4)
4905: INDEX(as2 PER_ASSIGNMENTS_F_PK)
4906: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4907: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4908: USE_NL(pos pop ppp opm as1 act as2) */
4909: act.assignment_action_id,
4910: act.assignment_id,
4911: act.tax_unit_id,
4918: pay_payroll_actions pa2,
4919: pay_action_classifications pcl,
4920: per_all_assignments_f as2,
4921: pay_pre_payments ppp,
4922: pay_org_payment_methods_f opm
4923: where pa1.payroll_action_id = pactid
4924: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
4925: and pa2.effective_date between
4926: pa1.start_date and pa1.effective_date