[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
2248: pay_payroll_actions pa2,
2249: pay_action_classifications pcl,
2250: pay_pre_payments ppp,
2251: per_all_assignments_f as2,
2252: pay_org_payment_methods_f opm
2253: where pa1.payroll_action_id = pactid
2254: and pa2.consolidation_set_id = pa1.consolidation_set_id /* moved +0, bug 6522667 */
2255: and pa2.effective_date between
2256: pa1.start_date and pa1.effective_date
2478: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2479: INDEX(as1 PER_ASSIGNMENTS_N4)
2480: INDEX(as2 PER_ASSIGNMENTS_F_PK)
2481: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2482: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2483: USE_NL(pop pos ppp opm as1 act as2) */
2484: act.assignment_action_id,
2485: act.assignment_id,
2486: act.tax_unit_id,
2493: per_all_assignments_f as1,
2494: pay_assignment_actions act,
2495: per_all_assignments_f as2,
2496: pay_pre_payments ppp,
2497: pay_org_payment_methods_f opm
2498: where pa1.payroll_action_id = pactid
2499: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2500: and pa2.effective_date between
2501: pa1.start_date and pa1.effective_date
2567: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2568: INDEX(as1 PER_ASSIGNMENTS_N4)
2569: INDEX(as2 PER_ASSIGNMENTS_F_PK)
2570: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2571: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2572: USE_NL(pos ppp opm as1 act as2) */
2573: act.assignment_action_id,
2574: act.assignment_id,
2575: act.tax_unit_id,
2581: per_all_assignments_f as1,
2582: pay_assignment_actions act,
2583: per_all_assignments_f as2,
2584: pay_pre_payments ppp,
2585: pay_org_payment_methods_f opm
2586: where pa1.payroll_action_id = pactid
2587: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2588: and pa2.effective_date between
2589: pa1.start_date and pa1.effective_date
2652: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2653: INDEX(as1 PER_ASSIGNMENTS_N4)
2654: INDEX(as2 PER_ASSIGNMENTS_F_PK)
2655: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2656: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2657: USE_NL(pos pop ppp opm as1 act as2) */
2658: act.assignment_action_id,
2659: act.assignment_id,
2660: act.tax_unit_id,
2667: pay_payroll_actions pa2,
2668: pay_action_classifications pcl,
2669: per_all_assignments_f as2,
2670: pay_pre_payments ppp,
2671: pay_org_payment_methods_f opm
2672: where pa1.payroll_action_id = pactid
2673: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2674: and pa2.effective_date between
2675: pa1.start_date and pa1.effective_date
3471: and ((pa2.action_type in ('P', 'U')
3472: and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3473: and exists (select 1
3474: from pay_pre_payments ppp,
3475: pay_org_payment_methods_f pom
3476: where ppp.assignment_action_id = act.assignment_action_id
3477: and pom.org_payment_method_id = ppp.org_payment_method_id
3478: and pom.cost_payment = 'Y'
3479: and pa2.effective_date between
3481: or (pa2.action_type in ('H', 'M')
3482: and pa1.batch_process_mode in ('CLEARED', 'ALL')
3483: and exists (select 1
3484: from pay_pre_payments ppp,
3485: pay_org_payment_methods_f pom,
3486: pay_ce_reconciled_payments crp
3487: where ppp.pre_payment_id = act.pre_payment_id
3488: and pom.org_payment_method_id = ppp.org_payment_method_id
3489: and pom.cost_cleared_payment = 'Y'
3493: or (pa2.action_type = 'E'
3494: and pa1.batch_process_mode in ('CLEARED', 'ALL')
3495: and exists (select 1
3496: from pay_pre_payments ppp,
3497: pay_org_payment_methods_f pom,
3498: pay_ce_reconciled_payments crp
3499: where ppp.pre_payment_id = act.pre_payment_id
3500: and pom.org_payment_method_id = ppp.org_payment_method_id
3501: and pom.cost_cleared_payment = 'Y'
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,
3514: pay_ce_reconciled_payments crp
3515: where int.locking_action_id = act.assignment_action_id
3516: and chq.assignment_action_id = int.locked_action_id
3517: and pcq.payroll_action_id = chq.payroll_action_id
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
3532: where int.locking_action_id = act.assignment_action_id
3533: and chq.assignment_action_id = int.locked_action_id
3534: and pcq.payroll_action_id = chq.payroll_action_id
3535: and pcq.action_type = 'H'
3607: and ((pa2.action_type in ('P', 'U')
3608: and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3609: and exists (select 1
3610: from pay_pre_payments ppp,
3611: pay_org_payment_methods_f pom
3612: where ppp.assignment_action_id = act.assignment_action_id
3613: and pom.org_payment_method_id = ppp.org_payment_method_id
3614: and pom.cost_payment = 'Y'
3615: and pa2.effective_date between
3617: or (pa2.action_type in ('H', 'M')
3618: and pa1.batch_process_mode in ('CLEARED', 'ALL')
3619: and exists (select 1
3620: from pay_pre_payments ppp,
3621: pay_org_payment_methods_f pom,
3622: pay_ce_reconciled_payments crp
3623: where ppp.pre_payment_id = act.pre_payment_id
3624: and pom.org_payment_method_id = ppp.org_payment_method_id
3625: and pom.cost_cleared_payment = 'Y'
3629: or (pa2.action_type = 'E'
3630: and pa1.batch_process_mode in ('CLEARED', 'ALL')
3631: and exists (select 1
3632: from pay_pre_payments ppp,
3633: pay_org_payment_methods_f pom,
3634: pay_ce_reconciled_payments crp
3635: where ppp.pre_payment_id = act.pre_payment_id
3636: and pom.org_payment_method_id = ppp.org_payment_method_id
3637: and pom.cost_cleared_payment = 'Y'
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,
3650: pay_ce_reconciled_payments crp
3651: where int.locking_action_id = act.assignment_action_id
3652: and chq.assignment_action_id = int.locked_action_id
3653: and pcq.payroll_action_id = chq.payroll_action_id
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
3668: where int.locking_action_id = act.assignment_action_id
3669: and chq.assignment_action_id = int.locked_action_id
3670: and pcq.payroll_action_id = chq.payroll_action_id
3671: and pcq.action_type = 'H'
3747: and ((pa2.action_type in ('P', 'U')
3748: and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3749: and exists (select 1
3750: from pay_pre_payments ppp,
3751: pay_org_payment_methods_f pom
3752: where ppp.assignment_action_id = act.assignment_action_id
3753: and pom.org_payment_method_id = ppp.org_payment_method_id
3754: and pom.cost_payment = 'Y'
3755: and pa2.effective_date between
3757: or (pa2.action_type in ('H', 'M')
3758: and pa1.batch_process_mode in ('CLEARED', 'ALL')
3759: and exists (select 1
3760: from pay_pre_payments ppp,
3761: pay_org_payment_methods_f pom,
3762: pay_ce_reconciled_payments crp
3763: where ppp.pre_payment_id = act.pre_payment_id
3764: and pom.org_payment_method_id = ppp.org_payment_method_id
3765: and pom.cost_cleared_payment = 'Y'
3769: or (pa2.action_type = 'E'
3770: and pa1.batch_process_mode in ('CLEARED', 'ALL')
3771: and exists (select 1
3772: from pay_pre_payments ppp,
3773: pay_org_payment_methods_f pom,
3774: pay_ce_reconciled_payments crp
3775: where ppp.pre_payment_id = act.pre_payment_id
3776: and pom.org_payment_method_id = ppp.org_payment_method_id
3777: and pom.cost_cleared_payment = 'Y'
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,
3790: pay_ce_reconciled_payments crp
3791: where int.locking_action_id = act.assignment_action_id
3792: and chq.assignment_action_id = int.locked_action_id
3793: and pcq.payroll_action_id = chq.payroll_action_id
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
3808: where int.locking_action_id = act.assignment_action_id
3809: and chq.assignment_action_id = int.locked_action_id
3810: and pcq.payroll_action_id = chq.payroll_action_id
3811: and pcq.action_type = 'H'
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
4522: and opm.org_payment_method_id = ppp.org_payment_method_id
4523: and opm.cost_payment = 'Y'
4714: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4715: INDEX(as1 PER_ASSIGNMENTS_N4)
4716: INDEX(as2 PER_ASSIGNMENTS_F_PK)
4717: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4718: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4719: USE_NL(pop pos ppp opm as1 act as2) */
4720: act.assignment_action_id,
4721: act.assignment_id,
4722: act.tax_unit_id,
4729: per_all_assignments_f as1,
4730: pay_assignment_actions act,
4731: per_all_assignments_f as2,
4732: pay_pre_payments ppp,
4733: pay_org_payment_methods_f opm
4734: where pa1.payroll_action_id = pactid
4735: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
4736: and pa2.effective_date between
4737: pa1.start_date and pa1.effective_date
4803: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4804: INDEX(as1 PER_ASSIGNMENTS_N4)
4805: INDEX(as2 PER_ASSIGNMENTS_F_PK)
4806: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4807: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4808: USE_NL(pos ppp opm as1 act as2) */
4809: act.assignment_action_id,
4810: act.assignment_id,
4811: act.tax_unit_id,
4817: per_all_assignments_f as1,
4818: pay_assignment_actions act,
4819: per_all_assignments_f as2,
4820: pay_pre_payments ppp,
4821: pay_org_payment_methods_f opm
4822: where pa1.payroll_action_id = pactid
4823: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
4824: and pa2.effective_date between
4825: pa1.start_date and pa1.effective_date
4888: INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4889: INDEX(as1 PER_ASSIGNMENTS_N4)
4890: INDEX(as2 PER_ASSIGNMENTS_F_PK)
4891: INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4892: index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4893: USE_NL(pos pop ppp opm as1 act as2) */
4894: act.assignment_action_id,
4895: act.assignment_id,
4896: act.tax_unit_id,
4903: pay_payroll_actions pa2,
4904: pay_action_classifications pcl,
4905: per_all_assignments_f as2,
4906: pay_pre_payments ppp,
4907: pay_org_payment_methods_f opm
4908: where pa1.payroll_action_id = pactid
4909: and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
4910: and pa2.effective_date between
4911: pa1.start_date and pa1.effective_date