[Home] [Help]
581: AND EXISTS
582: (select RR1.run_result_id
583: from pay_run_results RR1
584: , pay_run_results RR2
585: , pay_element_entries_f EE1
586: , pay_element_links_f EL1
587: where RR1.assignment_action_id = pa2.assignment_action_id
588: and RR1.source_id = RR2.run_result_id
589: and RR1.source_type NOT IN (''E'', ''I'', ''V'')
599: CST.run_result_id)
600: UNION ALL
601: select RR1.run_result_id
602: from pay_run_results RR1
603: , pay_element_entries_f EE1
604: , pay_element_links_f EL1
605: where RR1.assignment_action_id = pa2.assignment_action_id
606: and RR1.source_id = EE1.element_entry_id
607: and RR1.source_type NOT IN (''R'', ''I'', ''V'')
626: CST.run_result_id)
627: AND (
628: (NOT EXISTS /* look for deleted element entries */
629: (SELECT null
630: FROM pay_element_entries_f EE1
631: WHERE RR1.source_id = EE1.element_entry_id
632: AND RR1.source_type IN (''E'', ''I'')
633: AND least(nvl(RR1.end_date, ppa.date_earned), ppa.date_earned)
634: BETWEEN EE1.effective_start_date
636: )
637: AND NOT EXISTS
638: (SELECT null
639: FROM pay_run_results RR2,
640: pay_element_entries_f EE1
641: WHERE RR2.source_id = EE1.element_entry_id
642: AND RR1.source_type IN (''R'', ''V'')
643: AND RR1.source_id = RR2.run_result_id
644: AND least(nvl(RR2.end_date, ppa.date_earned), ppa.date_earned)
785: AND EXISTS
786: (select RR1.run_result_id
787: from pay_run_results RR1
788: , pay_run_results RR2
789: , pay_element_entries_f EE1
790: , pay_element_links_f EL1
791: where RR1.assignment_action_id = pa2.assignment_action_id
792: and RR1.source_id = RR2.run_result_id
793: and RR1.source_type NOT IN (''E'', ''I'', ''V'')
803: CST.run_result_id)
804: UNION ALL
805: select RR1.run_result_id
806: from pay_run_results RR1
807: , pay_element_entries_f EE1
808: , pay_element_links_f EL1
809: where RR1.assignment_action_id = pa2.assignment_action_id
810: and RR1.source_id = EE1.element_entry_id
811: and RR1.source_type NOT IN (''R'', ''I'', ''V'')
830: CST.run_result_id)
831: AND (
832: (NOT EXISTS /* look for deleted element entries */
833: (SELECT null
834: FROM pay_element_entries_f EE1
835: WHERE RR1.source_id = EE1.element_entry_id
836: AND RR1.source_type IN (''E'', ''I'')
837: AND least(nvl(RR1.end_date, ppa.date_earned), ppa.date_earned)
838: BETWEEN EE1.effective_start_date
840: )
841: AND NOT EXISTS
842: (SELECT null
843: FROM pay_run_results RR2,
844: pay_element_entries_f EE1
845: WHERE RR2.source_id = EE1.element_entry_id
846: AND RR1.source_type IN (''R'', ''V'')
847: AND RR1.source_id = RR2.run_result_id
848: AND least(nvl(RR2.end_date, ppa.date_earned), ppa.date_earned)
1671: AND EXISTS
1672: (select RR1.run_result_id
1673: from pay_run_results RR1
1674: , pay_run_results RR2
1675: , pay_element_entries_f EE1
1676: , pay_element_links_f EL1
1677: where RR1.assignment_action_id = raa.assignment_action_id
1678: and RR1.source_id = RR2.run_result_id
1679: and RR1.source_type = 'R'
1690: CST.run_result_id)
1691: UNION ALL
1692: select RR1.run_result_id
1693: from pay_run_results RR1
1694: , pay_element_entries_f EE1
1695: , pay_element_links_f EL1
1696: where RR1.assignment_action_id = raa.assignment_action_id
1697: and decode(r_action_type, 'B', nvl(RR1.element_entry_id, RR1.source_id),
1698: RR1.source_id)
1719: CST.run_result_id)
1720: AND (
1721: (NOT EXISTS /* look for deleted element entries */
1722: (SELECT null
1723: FROM pay_element_entries_f EE1
1724: WHERE RR1.source_id = EE1.element_entry_id
1725: AND RR1.source_type IN ('E', 'I')
1726: AND least(nvl(RR1.end_date, ppa.date_earned), ppa.date_earned)
1727: BETWEEN EE1.effective_start_date
1728: AND EE1.effective_end_date
1729: )
1730: AND NOT EXISTS
1731: (SELECT null
1732: FROM pay_element_entries_f EE1,
1733: pay_run_results RR2
1734: WHERE RR2.source_id = EE1.element_entry_id
1735: AND RR1.source_type IN ('R', 'V')
1736: AND RR1.source_id = RR2.run_result_id