[Home] [Help]
482: STOP_TIME,
483: APPROVAL_STATUS
484: FROM hxc_time_building_blocks
485: WHERE SCOPE = 'TIMECARD'
486: AND DATE_TO = hr_general.end_of_time
487: AND RESOURCE_ID = p_resource_id
488: AND RESOURCE_TYPE = p_resource_type
489: AND STOP_TIME >= p_first_start_date
490: AND START_TIME <= p_last_end_date
1630: )
1631: IS
1632: SELECT pas.ASSIGNMENT_ID,
1633: pas.EFFECTIVE_START_DATE,
1634: NVL(pas.EFFECTIVE_END_DATE, hr_general.end_of_time)
1635: FROM PER_ALL_ASSIGNMENTS_F pas,
1636: per_assignment_status_types typ
1637: ,per_periods_of_service ppos --14596859
1638: WHERE pas.PERSON_ID = p_resource_id
1645: 0 ) >= p_assign_period_limit
1646: -- AND pas.EFFECTIVE_START_DATE <= SYSDATE
1647: AND ppos.person_id = pas.person_id
1648: AND sysdate between trunc(DATE_START)
1649: and trunc(NVL(ppos.ACTUAL_TERMINATION_DATE,hr_general.end_of_time))
1650: AND pas.period_of_service_id = ppos.period_of_service_id
1651: ORDER BY EFFECTIVE_START_DATE;*/
1652:
1653: --Bug 16771643
1655: (p_resource_id IN number
1656: , p_assign_period_limit IN number) IS
1657: SELECT pas.assignment_id
1658: , pas.effective_start_date
1659: , nvl (pas.effective_end_date, hr_general.end_of_time) end_date
1660: FROM per_all_assignments_f pas
1661: , per_assignment_status_types typ
1662: WHERE pas.person_id = p_resource_id
1663: AND pas.assignment_type IN ('E', 'C')
1674: WHERE ppos.person_id = pas.person_id
1675: AND trunc (sysdate)
1676: BETWEEN trunc (date_start)
1677: AND decode (p_assign_period_limit, 0
1678: , trunc (nvl (ppos.final_process_date, hr_general.end_of_time)), trunc (nvl (ppos.actual_termination_date, hr_general.end_of_time)))
1679: AND pas.period_of_service_id = ppos.period_of_service_id
1680: )
1681: OR EXISTS
1682: (
1685: WHERE ppop.person_id = pas.person_id
1686: AND trunc (sysdate)
1687: BETWEEN date_start
1688: AND decode (p_assign_period_limit, 0
1689: , nvl (ppop.final_process_date, hr_general.end_of_time), nvl (ppop.actual_termination_date, hr_general.end_of_time))
1690: AND pas.period_of_placement_date_start
1691: BETWEEN ppop.date_start
1692: AND decode (p_assign_period_limit, 0
1693: , nvl (ppop.final_process_date, hr_general.end_of_time), nvl (ppop.actual_termination_date, hr_general.end_of_time))
1689: , nvl (ppop.final_process_date, hr_general.end_of_time), nvl (ppop.actual_termination_date, hr_general.end_of_time))
1690: AND pas.period_of_placement_date_start
1691: BETWEEN ppop.date_start
1692: AND decode (p_assign_period_limit, 0
1693: , nvl (ppop.final_process_date, hr_general.end_of_time), nvl (ppop.actual_termination_date, hr_general.end_of_time))
1694: )
1695: )
1696: ORDER BY effective_start_date;
1697:
1801: )
1802: IS
1803: SELECT pas.ASSIGNMENT_ID,
1804: pas.EFFECTIVE_START_DATE,
1805: NVL(pas.EFFECTIVE_END_DATE, hr_general.end_of_time)
1806: FROM PER_ALL_ASSIGNMENTS_F pas,
1807: per_assignment_status_types typ
1808: WHERE pas.PERSON_ID = p_resource_id
1809: AND pas.ASSIGNMENT_TYPE = 'E'
1929: IF l_num_past_days IS NOT NULL
1930: THEN
1931: g_past_date_limit := SYSDATE - TO_NUMBER(l_num_past_days);
1932: ELSE
1933: g_past_date_limit := hr_general.START_OF_TIME;
1934: END IF;
1935:
1936: IF l_num_future_days IS NOT NULL
1937: THEN
1936: IF l_num_future_days IS NOT NULL
1937: THEN
1938: g_future_date_limit := SYSDATE + TO_NUMBER(l_num_future_days);
1939: ELSE
1940: g_future_date_limit := hr_general.END_OF_TIME;
1941: END IF;
1942: g_initialized := 'Y';
1943:
1944: IF g_debug THEN
2255: FROM hxc_time_building_blocks
2256: where time_building_block_id = p_timecard_id
2257: and resource_id = p_resource_id
2258: and scope = 'TIMECARD';
2259: --and date_to = hr_general.end_of_time;
2260:
2261:
2262: CURSOR crs_day_info (
2263: p_resource_id IN NUMBER,
2388: and day.object_version_number = detail.parent_building_block_ovn
2389: and day.scope = 'DAY'
2390: and detail.resource_id = l_resource_id
2391: and detail.scope = 'DETAIL'
2392: --and day.date_to = hr_general.end_of_time
2393: --and detail.date_to = hr_general.end_of_time
2394: and day.parent_building_block_id = timecard_id
2395: and day.parent_building_block_ovn = timecard_ovn
2396: and day.resource_id = l_resource_id)
2389: and day.scope = 'DAY'
2390: and detail.resource_id = l_resource_id
2391: and detail.scope = 'DETAIL'
2392: --and day.date_to = hr_general.end_of_time
2393: --and detail.date_to = hr_general.end_of_time
2394: and day.parent_building_block_id = timecard_id
2395: and day.parent_building_block_ovn = timecard_ovn
2396: and day.resource_id = l_resource_id)
2397: UNION
2443: and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
2444: and (au.time_building_block_id,au.time_building_block_ovn) in
2445: (select day.time_building_block_id,day.object_version_number
2446: from hxc_time_building_blocks day
2447: where -- day.date_to = hr_general.end_of_time
2448: day.scope = 'DAY'
2449: and day.parent_building_block_id = timecard_id
2450: and day.parent_building_block_ovn = timecard_ovn
2451: and day.resource_id = l_resource_id)
2498: and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
2499: and (au.time_building_block_id,au.time_building_block_ovn) in
2500: (select time_building_block_id,object_version_number
2501: from hxc_time_building_blocks htbb
2502: where --htbb.date_to = hr_general.end_of_time
2503: htbb.scope = 'TIMECARD'
2504: and htbb.time_building_block_id = timecard_id
2505: and htbb.object_version_number = timecard_ovn
2506: and htbb.resource_id = l_resource_id)
2917: ,hxc_tc_ap_links htal
2918: ,hxc_timecard_summary hts
2919: ,fnd_attached_documents fad
2920: WHERE app.scope = 'APPLICATION_PERIOD'
2921: AND app.date_to = hr_general.end_of_time
2922: AND app.resource_id = to_number(p_resource_id)
2923: AND trunc(app.start_time) < c_tc_end_date
2924: AND trunc(app.stop_time) > c_tc_start_date
2925: AND trunc(app.start_time) <> c_tc_start_date