12: search_record
13: INDEX BY BINARY_INTEGER;
14:
15: TYPE application_period is RECORD(
16: approval_status hxc_time_building_blocks.approval_status%type,
17: start_time hxc_time_building_blocks.start_time%type,
18: stop_time hxc_time_building_blocks.stop_time%type
19: );
20:
13: INDEX BY BINARY_INTEGER;
14:
15: TYPE application_period is RECORD(
16: approval_status hxc_time_building_blocks.approval_status%type,
17: start_time hxc_time_building_blocks.start_time%type,
18: stop_time hxc_time_building_blocks.stop_time%type
19: );
20:
21: TYPE app_period_table is TABLE of Application_Period index by binary_integer;
14:
15: TYPE application_period is RECORD(
16: approval_status hxc_time_building_blocks.approval_status%type,
17: start_time hxc_time_building_blocks.start_time%type,
18: stop_time hxc_time_building_blocks.stop_time%type
19: );
20:
21: TYPE app_period_table is TABLE of Application_Period index by binary_integer;
22:
65: BEGIN
66:
67: l_user_list_sql := 'SELECT distinct hta.'
68: || p_flex_segment
69: || ' FROM hxc_time_building_blocks htbb_detail, '
70: || 'hxc_time_attribute_usages htau, '
71: || 'hxc_time_attributes hta'
72: || ' WHERE htbb_detail.resource_id = '
73: || ':p_resource_id'
487: FUNCTION get_timecard_cla_status(bb_id number, bb_ovn number)
488: return varchar2
489: is
490: cursor csr_get_cla_status
491: (p_timecard_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
492: ,p_timecard_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
493: ) is
494: SELECT 'Yes'
495: FROM DUAL
488: return varchar2
489: is
490: cursor csr_get_cla_status
491: (p_timecard_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
492: ,p_timecard_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
493: ) is
494: SELECT 'Yes'
495: FROM DUAL
496: WHERE EXISTS ( SELECT '1'
493: ) is
494: SELECT 'Yes'
495: FROM DUAL
496: WHERE EXISTS ( SELECT '1'
497: FROM hxc_time_building_blocks days,
498: hxc_time_building_blocks details,
499: hxc_time_attribute_usages tau,
500: hxc_time_attributes ta
501: WHERE tau.time_building_block_id =
494: SELECT 'Yes'
495: FROM DUAL
496: WHERE EXISTS ( SELECT '1'
497: FROM hxc_time_building_blocks days,
498: hxc_time_building_blocks details,
499: hxc_time_attribute_usages tau,
500: hxc_time_attributes ta
501: WHERE tau.time_building_block_id =
502: details.time_building_block_id
569: from hxc_timecard_summary
570: where timecard_id = p_timecard_id;
571:
572: cursor c_last_timecard_status
573: (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
574: select approval_status
575: from hxc_time_building_blocks tbb1
576: where tbb1.time_building_block_id = p_timecard_id
577: and tbb1.object_version_number =
571:
572: cursor c_last_timecard_status
573: (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
574: select approval_status
575: from hxc_time_building_blocks tbb1
576: where tbb1.time_building_block_id = p_timecard_id
577: and tbb1.object_version_number =
578: (select max(tbb2.object_version_number)
579: from hxc_time_building_blocks tbb2
575: from hxc_time_building_blocks tbb1
576: where tbb1.time_building_block_id = p_timecard_id
577: and tbb1.object_version_number =
578: (select max(tbb2.object_version_number)
579: from hxc_time_building_blocks tbb2
580: where tbb2.time_building_block_Id = tbb1.time_building_block_id
581: );
582:
583: l_tcstatus hxc_timecard_summary.approval_status%type;
653: return varchar2
654: is
655:
656: cursor c_get_approval_status
657: (p_tc_start_time in HXC_TIME_BUILDING_BLOCKS.START_TIME%TYPE
658: ,p_tc_stop_time in HXC_TIME_BUILDING_BLOCKS.STOP_TIME%TYPE
659: ,p_resource_id in HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
660: ,p_approval_after in HXC_TIME_BUILDING_BLOCKS.CREATION_DATE%TYPE) is
661: select
654: is
655:
656: cursor c_get_approval_status
657: (p_tc_start_time in HXC_TIME_BUILDING_BLOCKS.START_TIME%TYPE
658: ,p_tc_stop_time in HXC_TIME_BUILDING_BLOCKS.STOP_TIME%TYPE
659: ,p_resource_id in HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
660: ,p_approval_after in HXC_TIME_BUILDING_BLOCKS.CREATION_DATE%TYPE) is
661: select
662: hap.approval_status
655:
656: cursor c_get_approval_status
657: (p_tc_start_time in HXC_TIME_BUILDING_BLOCKS.START_TIME%TYPE
658: ,p_tc_stop_time in HXC_TIME_BUILDING_BLOCKS.STOP_TIME%TYPE
659: ,p_resource_id in HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
660: ,p_approval_after in HXC_TIME_BUILDING_BLOCKS.CREATION_DATE%TYPE) is
661: select
662: hap.approval_status
663: ,hap.start_time
656: cursor c_get_approval_status
657: (p_tc_start_time in HXC_TIME_BUILDING_BLOCKS.START_TIME%TYPE
658: ,p_tc_stop_time in HXC_TIME_BUILDING_BLOCKS.STOP_TIME%TYPE
659: ,p_resource_id in HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
660: ,p_approval_after in HXC_TIME_BUILDING_BLOCKS.CREATION_DATE%TYPE) is
661: select
662: hap.approval_status
663: ,hap.start_time
664: ,hap.stop_time
662: hap.approval_status
663: ,hap.start_time
664: ,hap.stop_time
665: from
666: hxc_time_building_blocks hap
667: where
668: hap.scope = 'APPLICATION_PERIOD'
669: and hap.type = 'RANGE'
670: and hap.date_to = hr_general.end_of_time
674: and hap.resource_id = p_resource_id
675: and hap.creation_date >= p_approval_after;
676:
677: cursor csr_get_latest_block
678: (p_timecard_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
679: ,p_timecard_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
680: ) is
681: select max(details.creation_date)
682: from hxc_time_building_blocks details
675: and hap.creation_date >= p_approval_after;
676:
677: cursor csr_get_latest_block
678: (p_timecard_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
679: ,p_timecard_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
680: ) is
681: select max(details.creation_date)
682: from hxc_time_building_blocks details
683: ,hxc_time_building_blocks days
678: (p_timecard_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
679: ,p_timecard_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
680: ) is
681: select max(details.creation_date)
682: from hxc_time_building_blocks details
683: ,hxc_time_building_blocks days
684: where days.parent_building_block_id = p_timecard_id
685: and days.parent_building_block_ovn = p_timecard_ovn
686: and days.scope = 'DAY'
679: ,p_timecard_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
680: ) is
681: select max(details.creation_date)
682: from hxc_time_building_blocks details
683: ,hxc_time_building_blocks days
684: where days.parent_building_block_id = p_timecard_id
685: and days.parent_building_block_ovn = p_timecard_ovn
686: and days.scope = 'DAY'
687: and days.date_to = hr_general.end_of_time
690: and details.date_to = hr_general.end_of_time
691: and details.scope = 'DETAIL';
692:
693: cursor csr_get_latest_day
694: (p_timecard_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
695: ,p_timecard_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
696: ) is
697: select max(creation_date)
698: from hxc_time_building_blocks
691: and details.scope = 'DETAIL';
692:
693: cursor csr_get_latest_day
694: (p_timecard_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
695: ,p_timecard_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
696: ) is
697: select max(creation_date)
698: from hxc_time_building_blocks
699: where parent_building_block_id = p_timecard_id
694: (p_timecard_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
695: ,p_timecard_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
696: ) is
697: select max(creation_date)
698: from hxc_time_building_blocks
699: where parent_building_block_id = p_timecard_id
700: and parent_building_block_ovn = p_timecard_ovn
701: and scope = 'DAY'
702: and date_to = hr_general.end_of_time;
712: l_latest_block_date DATE;
713: l_day_count number;
714: l_day_number number;
715: l_day DATE;
716: l_day_status hxc_time_building_blocks.approval_status%type;
717: l_day_set BOOLEAN;
718: l_app_period_count number;
719:
720: BEGIN
727: l_submitted := 'false';
728:
729: -- Original Timecard Status
730: select approval_status, start_time, stop_time, resource_id into l_tcstatus, l_start_time, l_stop_time, l_resource_id
731: from hxc_time_building_blocks
732: where time_building_block_id = bb_id
733: and object_version_number = bb_ovn;
734:
735: IF l_tcstatus = 'WORKING' or l_tcstatus = 'ERROR'
1040: l_additional_where VARCHAR2(32767);
1041: l_one_where VARCHAR2(32767);
1042: l_complete_sql VARCHAR2(32767);
1043: l_result VARCHAR2(32767);
1044: l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
1045: l_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE;
1046: l_status_code VARCHAR(30);
1047: l_status_meaning VARCHAR(100);
1048: l_period_starts hxc_time_building_blocks.start_time%TYPE;
1041: l_one_where VARCHAR2(32767);
1042: l_complete_sql VARCHAR2(32767);
1043: l_result VARCHAR2(32767);
1044: l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
1045: l_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE;
1046: l_status_code VARCHAR(30);
1047: l_status_meaning VARCHAR(100);
1048: l_period_starts hxc_time_building_blocks.start_time%TYPE;
1049: l_period_ends hxc_time_building_blocks.stop_time%TYPE;
1044: l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
1045: l_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE;
1046: l_status_code VARCHAR(30);
1047: l_status_meaning VARCHAR(100);
1048: l_period_starts hxc_time_building_blocks.start_time%TYPE;
1049: l_period_ends hxc_time_building_blocks.stop_time%TYPE;
1050: l_hours_worked NUMBER;
1051: l_submission_date DATE;
1052: c_sql cur_type;
1045: l_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE;
1046: l_status_code VARCHAR(30);
1047: l_status_meaning VARCHAR(100);
1048: l_period_starts hxc_time_building_blocks.start_time%TYPE;
1049: l_period_ends hxc_time_building_blocks.stop_time%TYPE;
1050: l_hours_worked NUMBER;
1051: l_submission_date DATE;
1052: c_sql cur_type;
1053:
1168: THEN
1169: IF NOT l_detail_join_flag
1170: THEN
1171: l_sql_from := l_sql_from
1172: || ' ,hxc_time_building_blocks htbb_day'
1173: || ' ,hxc_time_building_blocks htbb_detail';
1174:
1175:
1176: l_sql_where := l_sql_where
1169: IF NOT l_detail_join_flag
1170: THEN
1171: l_sql_from := l_sql_from
1172: || ' ,hxc_time_building_blocks htbb_day'
1173: || ' ,hxc_time_building_blocks htbb_detail';
1174:
1175:
1176: l_sql_where := l_sql_where
1177: || ' AND htbb_day.parent_building_block_id = hrt.timecard_id'
1249:
1250: IF NOT l_detail_join_flag
1251: THEN
1252: l_sql_from := l_sql_from
1253: || ' ,hxc_time_building_blocks htbb_day'
1254: || ' ,hxc_time_building_blocks htbb_detail';
1255:
1256:
1257: l_sql_where := l_sql_where
1250: IF NOT l_detail_join_flag
1251: THEN
1252: l_sql_from := l_sql_from
1253: || ' ,hxc_time_building_blocks htbb_day'
1254: || ' ,hxc_time_building_blocks htbb_detail';
1255:
1256:
1257: l_sql_where := l_sql_where
1258: || ' AND htbb_day.parent_building_block_id = hrt.timecard_id'
1411: l_additional_where VARCHAR2(32767);
1412: l_one_where VARCHAR2(32767);
1413: l_complete_sql VARCHAR2(32767);
1414: l_result VARCHAR2(32767);
1415: l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
1416: l_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE;
1417: l_status_code VARCHAR(30);
1418: l_status_meaning VARCHAR(100);
1419: l_period_starts hxc_time_building_blocks.start_time%TYPE;
1412: l_one_where VARCHAR2(32767);
1413: l_complete_sql VARCHAR2(32767);
1414: l_result VARCHAR2(32767);
1415: l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
1416: l_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE;
1417: l_status_code VARCHAR(30);
1418: l_status_meaning VARCHAR(100);
1419: l_period_starts hxc_time_building_blocks.start_time%TYPE;
1420: l_period_ends hxc_time_building_blocks.stop_time%TYPE;
1415: l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
1416: l_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE;
1417: l_status_code VARCHAR(30);
1418: l_status_meaning VARCHAR(100);
1419: l_period_starts hxc_time_building_blocks.start_time%TYPE;
1420: l_period_ends hxc_time_building_blocks.stop_time%TYPE;
1421: l_hours_worked NUMBER;
1422: l_submission_date DATE;
1423: c_sql cur_type;
1416: l_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE;
1417: l_status_code VARCHAR(30);
1418: l_status_meaning VARCHAR(100);
1419: l_period_starts hxc_time_building_blocks.start_time%TYPE;
1420: l_period_ends hxc_time_building_blocks.stop_time%TYPE;
1421: l_hours_worked NUMBER;
1422: l_submission_date DATE;
1423: c_sql cur_type;
1424: