[Home] [Help]
1433: per_periods_of_service pos,
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
1509: per_periods_of_service pos,
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
1583: pay_assignment_actions act,
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
1733: and pos.period_of_service_id = as1.period_of_service_id
1734: and pos.person_id between stperson and endperson
1735: and exists (
1736: select ''
1737: from pay_pre_payments ppp
1738: where ppp.assignment_action_id = act.assignment_action_id
1739: and ppp.organization_id is not null
1740: and nvl(ppp.effective_date, pa2.effective_date)
1741: <= pa1.effective_date
1799: and pop.payroll_action_id = pactid
1800: and pos.person_id = pop.person_id
1801: and exists (
1802: select ''
1803: from pay_pre_payments ppp
1804: where ppp.assignment_action_id = act.assignment_action_id
1805: and ppp.organization_id is not null
1806: and nvl(ppp.effective_date, pa2.effective_date)
1807: <= pa1.effective_date
1916: pay_payroll_actions pa2,
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
1952: )
1953: order by hou.organization_id, ppp.pre_payment_id
1954: for update of hou.organization_id;
1955: --
1956: l_prepayid pay_pre_payments.pre_payment_id%type;
1957: prev_prepayid pay_pre_payments.pre_payment_id%type;
1958: lockedactid pay_assignment_actions.assignment_action_id%type;
1959: lockingactid pay_assignment_actions.assignment_action_id%type;
1960: orgid hr_organization_units.organization_id%type;
1953: order by hou.organization_id, ppp.pre_payment_id
1954: for update of hou.organization_id;
1955: --
1956: l_prepayid pay_pre_payments.pre_payment_id%type;
1957: prev_prepayid pay_pre_payments.pre_payment_id%type;
1958: lockedactid pay_assignment_actions.assignment_action_id%type;
1959: lockingactid pay_assignment_actions.assignment_action_id%type;
1960: orgid hr_organization_units.organization_id%type;
1961: l_cp number;
2070: pay_population_ranges pop,
2071: per_periods_of_service pos,
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
2161: pay_action_classifications pcl,
2162: per_periods_of_service pos,
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
2246: per_all_assignments_f as1,
2247: pay_assignment_actions act,
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 */
2492: per_periods_of_service pos,
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
2580: per_periods_of_service pos,
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
2666: pay_assignment_actions act,
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
3470: and as3.person_id = as2.person_id)
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'
3480: pom.effective_start_date and pom.effective_end_date))
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
3492: pom.effective_start_date and pom.effective_end_date))
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
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,
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
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
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
3606: and as3.person_id = as2.person_id)
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'
3616: pom.effective_start_date and pom.effective_end_date))
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
3628: pom.effective_start_date and pom.effective_end_date))
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
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,
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
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
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
3746: and as3.person_id = as2.person_id)
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'
3756: pom.effective_start_date and pom.effective_end_date))
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
3768: pom.effective_start_date and pom.effective_end_date))
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
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,
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
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
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
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
4522: and opm.org_payment_method_id = ppp.org_payment_method_id
4728: per_periods_of_service pos,
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
4816: per_periods_of_service pos,
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
4902: pay_assignment_actions act,
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