[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
2252: per_all_assignments_f as1,
2253: pay_assignment_actions act,
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
2498: per_periods_of_service pos,
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
2586: per_periods_of_service pos,
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
2672: pay_assignment_actions act,
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
3478: and as3.person_id = as2.person_id)
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'
3488: pom.effective_start_date and pom.effective_end_date))
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
3500: pom.effective_start_date and pom.effective_end_date))
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
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,
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
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
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
3614: and as3.person_id = as2.person_id)
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'
3624: pom.effective_start_date and pom.effective_end_date))
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
3636: pom.effective_start_date and pom.effective_end_date))
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
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,
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
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
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
3754: and as3.person_id = as2.person_id)
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'
3764: pom.effective_start_date and pom.effective_end_date))
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
3776: pom.effective_start_date and pom.effective_end_date))
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
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,
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
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
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
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
4532: where int.locking_action_id = lockedactid
4743: per_periods_of_service pos,
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
4831: per_periods_of_service pos,
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
4917: pay_assignment_actions act,
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