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
                       := 'TimecardHistory';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'TimecardHistory'
    ,p_path               => 'timecardHistory'
    ,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
        ,xle.person_id               logged_person_id
        ,xle.log_entry_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 = 'A' THEN xxas_com_msg_pkg.get_string('XXAS_MP_APPROVED')
                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 = 'D'
                     AND xcda.ins_upd_del = 'U' THEN xxas_com_msg_pkg.get_string('XXAS_MP_UPDATED_LOG_ENTRY')
                WHEN xca.action_code = 'D' AND xcda.ins_upd_del = 'I'
                     AND xle.log_entry_details.extract('//TimeClock/LunchIn') IS NOT NULL THEN xxas_com_msg_pkg.get_string('XXAS_MP_LUNCH_IN')
                WHEN xca.action_code = 'D' AND xcda.ins_upd_del = 'I'
                     AND xle.log_entry_details.extract('//TimeClock/ClockIn') IS NOT NULL THEN xxas_com_msg_pkg.get_string('XXAS_MP_CLOCK_IN')
                WHEN xca.action_code = 'D' THEN xxas_com_msg_pkg.get_string('XXAS_MP_LOGGED_ENTRY')
            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_log_entries_v xle,
        per_all_people_f papf,
        fnd_user fu
    WHERE
        xcd.data_type = 1
        AND xcd.data_id = nvl({requestParam.logEntryId},0)
		AND xle.log_entry_id = nvl({requestParam.logEntryId},0)
        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 (
            'D',
            'S',
            'A',
            'T',
            'R',
            '-'
        )

        UNION

   SELECT
         2                            sort_order
        ,xtpc.person_id               logged_person_id
         ,xle.log_entry_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,
        xxas_tcm_log_entries_v xle,
        per_all_people_f papf,
        fnd_user fu
    WHERE
        xcd.data_type               = 10
		AND xle.log_entry_id        = nvl({requestParam.logEntryId},0)
        and xtpc.pending_clock_id =(SELECT pending_clock_id
                                    FROM   XMLTable
                                                ('/PendingDetails'
                                                PASSING  xle.log_entry_details
                                                COLUMNS  xml  XMLType           path '.'
                                                ,pending_clock_id   number            path '//PendingClockId'
                                                ) )
        AND xcd.data_id             = xtpc.pending_clock_id                                                
        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',
            'O',
            'D',
            'S',
            'T',
            'R',
            '-'
        )

    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 t.log_entry_id =nvl({requestParam.logEntryId},0)
      AND img.user_id = t.actioned_by_user_id
               ]'
    );
    
END;
/
commit;
exit;