SET VERIFY OFF
SET FEEDBACK OFF
SET SCAN OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  --
  -- Local constants and variables
  --
  l_SERVICE CONSTANT xxas_com_service_repository.service_name%TYPE
                       := 'OtlTimecardHistory';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'OtlTimecardHistory'
    ,p_path               => 'OtlTimecardHistory'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'ViewTimecardHistory'
    ,p_description        => 'Gets log history of given log entry'
    ,p_methods            => 'GET'
    ,p_path               => '/view-timecard-history'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
SELECT 
            XMLElement 
				("data" ,XMLAttributes 
									('true' as "json:force-array" 
									,'http://json.org/' as "xmlns:json"
									) 
									,XMLAgg 
									(XMLElement 
											("item" 
												,XMLForest ( 'note' "type" 
															,t.actioned_by_user_name "author"
															,t.log_entry_id "objectId" 
															, '&lt;div class=\"pre\"&gt;'||t.action_timestamp||'&lt;/div&gt;' "body"
															,t.Action_type "noteType"
															,to_char(t.action_duration) "dateCreatedFormatted" 
															,t.log_entry_id "objectId" 
															,XMLElement("image"
																,XMLForest 
																	(
																		xxas_util_pkg.get_initials(img.first_name, img.last_name, img.known_as)  "initials"
																		,xxas_com_rest_util_pkg.uri_from_path('people/' || nvl(img.person_id, -1) || '/photo') "self"
                                                                        ,xxas_per_image_pkg.url(img.person_id, img.last_update_date, 'SMALL') "small"
																		,xxas_per_image_pkg.url(img.person_id, img.last_update_date, 'MEDIUM') "medium"
																		,xxas_per_image_pkg.url(img.person_id, img.last_update_date, 'FULL_SIZE') "fullSize"
																	)
																	
															)	 "linked"

															
															) 
											) 
									) 
										
									
				) "data" 
FROM (SELECT
    1               sort_order,
    hts.timecard_id log_entry_id,
    hts.resource_id logged_person_id,
    xxas_util_pkg.get_formatted_name(papf.full_name,papf.global_name,papf.local_name,papf.first_name,papf.last_name,papf.known_as) actioned_by_user_name
,
    CASE
            WHEN htbb_detail.approval_status = 'WORKING'   THEN xxas_com_msg_pkg.get_string('XXAS_MP_LOGGED_ENTRY')
            WHEN htbb_detail.approval_status = 'SUBMITTED' THEN xxas_com_msg_pkg.get_string('XXAS_MP_SUBMIT_FOR_APPROVAL')
            WHEN htbb_detail.approval_status = 'APPROVED'  THEN xxas_com_msg_pkg.get_string('XXAS_MP_APPROVED')
            WHEN htbb_detail.approval_status = 'ERROR'  THEN xxas_com_msg_pkg.get_string('XXAS_MP_ERROR')
        END
    action_type,
    substr(xxas_util_pkg.date_to_httpdate(htbb_detail.creation_date),1,22) action_timestamp,
    htbb_detail.creation_date act_timestamp,
    htbb_detail.approval_status act_code,
    xxas_util_pkg.date_to_age(htbb_detail.creation_date) action_duration,
    htbb_detail.created_by actioned_by_user_id
FROM
    hxc_timecard_summary hts,
    hxc_time_building_blocks htbb_time,
    hxc_time_building_blocks htbb_day,
    hxc_time_building_blocks htbb_detail,
    per_all_people_f papf,
    fnd_user fu
WHERE
    hts.timecard_id = htbb_time.time_building_block_id
    AND htbb_time.time_building_block_id = htbb_day.parent_building_block_id
    AND htbb_time.object_version_number = htbb_day.parent_building_block_ovn
    AND htbb_time.scope = 'TIMECARD'
    AND htbb_time.resource_type = 'PERSON'
    AND htbb_day.time_building_block_id = htbb_detail.parent_building_block_id
    AND htbb_day.object_version_number = htbb_detail.parent_building_block_ovn
    AND htbb_day.scope = 'DAY'
    AND htbb_detail.scope = 'DETAIL'
    AND htbb_detail.time_building_block_id =  nvl({requestParam.logEntryId},0)
    AND fu.user_id = htbb_detail.created_by
    AND papf.person_id = fu.employee_id
    AND trunc(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
    
UNION
SELECT
    1                sort_order
    ,hts.timecard_id log_entry_id,
    hts.resource_id logged_person_id,
    NULL actioned_by_user_name,
    CASE
            WHEN hts.approval_status = 'WORKING'   THEN xxas_com_msg_pkg.get_string('XXAS_MP_LOGGED_ENTRY')
            WHEN hts.approval_status = 'SUBMITTED' THEN xxas_com_msg_pkg.get_string('XXAS_MP_SUBMIT_FOR_APPROVAL')
            WHEN hts.approval_status = 'APPROVED'
                 AND hts.transferred_to IS NOT NULL THEN xxas_com_msg_pkg.get_string('XXAS_MP_TRANSFERRED')
            WHEN hts.approval_status = 'APPROVED'  THEN xxas_com_msg_pkg.get_string('XXAS_MP_APPROVED')
        END
    action_type,
    substr(xxas_util_pkg.date_to_httpdate(hts.submission_date),1,22) action_timestamp,
    hts.submission_date act_timestamp,
    hts.approval_status act_code,
    xxas_util_pkg.date_to_age(hts.submission_date) action_duration,
    -1 actioned_by_user_id
FROM
    hxc_timecard_summary hts,
    hxc_time_building_blocks htbb_time,
    hxc_time_building_blocks htbb_day,
    hxc_time_building_blocks htbb_detail
WHERE
     hts.timecard_id = htbb_time.time_building_block_id
    AND hts.timecard_ovn = htbb_time.object_version_number
    AND htbb_time.time_building_block_id = htbb_day.parent_building_block_id
    AND htbb_time.object_version_number = htbb_day.parent_building_block_ovn
    AND htbb_time.scope = 'TIMECARD'
    AND htbb_time.resource_type = 'PERSON'
    AND htbb_day.time_building_block_id = htbb_detail.parent_building_block_id
    AND htbb_day.object_version_number = htbb_detail.parent_building_block_ovn
    AND htbb_day.scope = 'DAY'
    AND htbb_detail.scope = 'DETAIL'
    AND htbb_detail.time_building_block_id =  nvl({requestParam.logEntryId},0)
    AND hts.approval_status &lt;&gt; htbb_time.approval_status

UNION
SELECT
        2                            sort_order
        ,xtpc.person_id               logged_person_id
        ,htau.time_building_block_id  log_entry_id
        ,xxas_util_pkg.get_formatted_name(papf.full_name,papf.global_name,papf.local_name,papf.first_name,papf.last_name,papf.known_as) actioned_by_user_name
        ,  CASE
                WHEN xca.action_code = 'S' THEN xxas_com_msg_pkg.get_string('XXAS_MP_SUBMIT_FOR_APPROVAL')
                WHEN xca.action_code = 'T' THEN xxas_com_msg_pkg.get_string('XXAS_MP_TRANSFERRED')
                WHEN xca.action_code = 'R' THEN xxas_com_msg_pkg.get_string('XXAS_MP_RETURN_FOR_CORRECTION')
                WHEN xca.action_code = '-' THEN xxas_com_msg_pkg.get_string('XXAS_MP_DELETED')
                 WHEN xca.action_code IN ('D','C')
                     AND xcda.ins_upd_del = 'U' THEN xxas_com_msg_pkg.get_string('XXAS_MP_UPDATED_LOG_ENTRY')
                WHEN xca.action_code = 'D'
                     AND xtpc.pending_details.extract('//TimeClock/LunchIn') IS NOT NULL THEN xxas_com_msg_pkg.get_string('XXAS_MP_LUNCH_IN')
				WHEN xca.action_code = 'D'
                     AND xtpc.pending_details.extract('//TimeClock/ClockIn') IS NOT NULL THEN xxas_com_msg_pkg.get_string('XXAS_MP_CLOCK_IN')
                WHEN xca.action_code = 'O' THEN xxas_com_msg_pkg.get_string('XXAS_MP_WAITING_OUT')
                WHEN xca.action_code = 'D' THEN xxas_com_msg_pkg.get_string('XXAS_MP_LOGGED_IN')
                WHEN xca.action_code = 'C' THEN decode(substr(lower(to_char(''||xcda.data_action_details)),2,7),'clockin',xxas_com_msg_pkg.get_string('XXAS_MP_CLOCK_IN'),'clockou',xxas_com_msg_pkg.get_string('XXAS_MP_CLOCK_OUT'),'lunchin',xxas_com_msg_pkg.get_string('XXAS_MP_LUNCH_IN'),'lunchou',xxas_com_msg_pkg.get_string('XXAS_MP_LUNCH_OUT'),xxas_com_msg_pkg.get_string('XXAS_MP_LOGGED_IN'))
				WHEN xca.action_code = 'F' THEN decode(substr(lower(to_char(''||xcda.data_action_details)),13,7),'in/&gt;',xxas_com_msg_pkg.get_string('XXAS_MP_FIXED_IN'),'out/&gt;',xxas_com_msg_pkg.get_string('XXAS_MP_FIXED_MISSED_OUT'),'lunchin',xxas_com_msg_pkg.get_string('XXAS_MP_FIXED_MISSED_LUNCH_IN'),'lunchou',xxas_com_msg_pkg.get_string('XXAS_MP_FIXED_MISSED_LUNCH_OUT'),xxas_com_msg_pkg.get_string('XXAS_MP_FIXED_MISSED_PUNCH')) 
                WHEN xca.action_code = 'M' THEN decode(substr(lower(to_char(''||xcda.data_action_details)),8,7),'in/&gt;',xxas_com_msg_pkg.get_string('XXAS_MP_MISSED_IN'),'out/&gt;',xxas_com_msg_pkg.get_string('XXAS_MP_MISSED_OUT'),'lunchin',xxas_com_msg_pkg.get_string('XXAS_MP_MISSED_LUNCH_IN'),'lunchou',xxas_com_msg_pkg.get_string('XXAS_MP_MISSED_LUNCH_OUT'),xxas_com_msg_pkg.get_string('XXAS_MP_MISSED_PUNCH')) 
            END  Action_type
        , substr(xxas_util_pkg.date_to_httpdate(xca.action_timestamp),1,22) action_timestamp
         , xca.action_timestamp act_timestamp
         , xca.action_code act_code
        ,xxas_util_pkg.date_to_age(xca.action_timestamp) action_duration
        ,xca.actioned_by_user_id actioned_by_user_id

    FROM
        xxas_com_data xcd,
        xxas_com_data_actions xcda,
        xxas_com_actions xca,
        xxas_tcm_pending_clocks_v xtpc,
        hxc_time_attribute_usages htau,
        per_all_people_f papf,
        fnd_user fu
    WHERE
        xcd.data_type = 10
        and htau.time_building_block_id = nvl({requestParam.logEntryId},0)
        AND xcd.data_id = xtpc.pending_clock_id
        and xtpc.pending_clock_id =(SELECT substr(ATTRIBUTE1,20) pending_clock_id
                                    FROM HXC_TIME_ATTRIBUTES 
                                    WHERE TIME_ATTRIBUTE_ID in( SELECT TIME_ATTRIBUTE_ID 
                                                                FROM hxc_time_attribute_usages 
                                                                WHERE time_building_block_id ={requestParam.logEntryId}
                                                                )
                                    AND ATTRIBUTE_CATEGORY LIKE 'Pending%' 
                                    AND ROWNUM = 1 )
        AND xcda.internal_data_id = xcd.internal_data_id
        AND xca.action_id = xcda.action_id
        AND fu.user_id = xca.actioned_by_user_id
        AND papf.person_id = fu.employee_id
        AND trunc(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND xca.action_code IN (
            'C',
            'M',
            'F',
            'D',
            'S',
            'T',
            'R',
            'O',
            '-'
        
    )

    ORDER BY
        sort_order,act_timestamp DESC,act_code desc ) t 
,(SELECT usr.user_name
				,usr.employee_id
                ,usr.user_id
                ,xpt.person_id
                ,xpt.last_update_date
                ,papf.first_name
                ,papf.last_name
                ,papf.known_as
          FROM   fnd_user usr
                ,xxas_per_thumbnails xpt
                ,per_all_people_f papf
          WHERE  usr.employee_id = xpt.person_id(+)
          AND    usr.employee_id = papf.person_id(+)
          AND    trunc(sysdate) BETWEEN
                 papf.effective_start_date(+) AND papf.effective_end_date(+))img
where  img.user_id = t.actioned_by_user_id
               ]'
    );
    
END;
/
commit;
exit;

