IF EXISTS (select * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[ng_cas_audit_data_load]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE ng_cas_audit_data_load
GO
      
CREATE  PROCEDURE ng_cas_audit_data_load 
	@pi_enterprise_id CHAR(5),
	@pi_practice_id CHAR(4),
    @pi_skip_size INT,
    @pi_page_size INT,
    @pi_audit_event_from_date DATETIME,
    @pi_audit_event_to_date DATETIME         

AS   
--{6.2.0.XX}
----------------------
-- $Change: e81fcc5d $
-- $DateTime: 2024/01/02 12:55:14 $
-- $Author: Dsingh $
---------------------- 
/************************************************************************************************************************************              
[- purpose: To fetch audit records of given page size.-]                
[- details: To fetch audit records of given page size for a specific enterprise, practice, and for a specific date.-]                
[- type: Advanced Audit-]              
[- object: Stored Procedure -]              
[- created: 20240102 QC:BAR-37993 -]  
[- version:	date:		    qc item:			programmer:		    comment -]
6.2.0.XX    01/02/2024      BAR-37993			Devakishore Singh	Created the procedure.
[- Example: 

	exec sproc_cas_audit_data_load '00001', '0001', 0, 10000, '2023-09-01 00:00:00', '2023-09-02 00:00:00'

************************************************************************************************************************************/ 
BEGIN

SET NOCOUNT ON  

	SELECT 
			-- event_audit_mstr table columns
			ae.EventId,
			ae.CategoryId,
			ae.ActionId,
			ae.EventTimestamp,
			ae.StationId,
			ae.EnterpriseId,
			ae.PracticeId,
			ae.PersonId,
			ae.EventMessage,
			ae.LocationId,
			ae.EventTimestampTimezone,
			ae.ActionType,
			ae.SourceProductDescription,
			ae.SiteId,
			ae.ProcessId,
			ae.HashCode,
			ae.CreatedBy,
			ae.HashVersion,

			-- Extent1 - event_account table
			Extent1.event_id AS EventAccountEventId, 
			Extent1.acct_id AS EventAccountAccountId, 
			Extent1.encounter_id AS EventAccountEncounterId, 

			-- Extent2 - event_application_access table
			Extent2.event_id AS EventApplicationAccessEventId, 
			Extent2.attempt_number AS EventApplicationAccessAttemptNumber, 
			Extent2.successful_ind AS EventApplicationAccessSuccessfulInd, 
			Extent2.invalid_pin_id AS EventApplicationAccessInvalidPinId, 
			Extent2.invalid_login_id AS EventApplicationAccessInvalidLoginId, 
			Extent2.invalid_password AS EventApplicationAccessInvalidPassword, 

			-- Extent3 - event_chart_summary table
			Extent3.event_id AS EventChartSummaryEventId, 
			Extent3.unique_id AS EventChartSummaryId, 

			-- Extent4 - event_encounter table
			Extent4.event_id AS EventEncounterEventId, 
			Extent4.encounter_id AS EventEncounterId, 

			-- Extent5 - event_ics table
			Extent5.event_id AS EventIcsEventId, 
			Extent5.document_id AS EventIcsDocumentId, 
			Extent5.page_id AS EventIcsPageId, 

			-- Extent6 - event_interface_holding_tank table
			Extent6.event_id AS EventInterfaceHoldingTankEventId, 
			Extent6.message_type AS EventInterfaceHoldingTankMessageType, 
			Extent6.match_type AS EventInterfaceHoldingTankMatchType, 
			Extent6.holding_tank_reason AS EventInterfaceHoldingTankHoldingTankReason, 
			Extent6.queue_entry_id AS EventInterfaceHoldingTankQueueEntryId, 

			-- Extent7 - event_interoperability table
			Extent7.event_id AS EventInteroperabilityEventId, 
			Extent7.external_system_id AS EventInteroperabilityExternalSystemId, 
			Extent7.external_enterprise_id AS EventInteroperabilityExternalEnterpriseId, 
			Extent7.external_practice_id AS EventInteroperabilityExternalPracticeId, 
			Extent7.external_patient_id AS EventInteroperabilityExternalPatientId, 
			Extent7.external_user_id AS EventInteroperabilityExternalUserId, 
			Extent7.cqm_provider_id AS EventInteroperabilityCqmProviderId, 
			Extent7.message AS EventInteroperabilityMessage, 

			-- Extent8 - event_account table
			Extent8.event_id AS EventPaqEventId, 
			Extent8.unique_id AS EventPaqId, 
			Extent8.provider_id AS EventPaqProviderId, 

			-- Extent9 - event_patient_education table
			Extent9.event_id AS EventPatientEducationEventId, 
			Extent9.provider_id AS EventPatientEducationProviderId, 

			-- Extent10 - event_rosetta table
			Extent10.event_id AS EventRosettaEventId, 
			Extent10.agent_name AS EventRosettaAgentName, 
			Extent10.agent_type AS EventRosettaAgentType, 

			-- Extent11 - event_security table
			Extent11.event_id AS EventSecurityEventId, 
			Extent11.allowed_ind AS EventSecurityAllowedInd, 
			Extent11.group_id AS EventSecurityGroupId, 
			Extent11.item_type AS EventSecurityItemType, 
			Extent11.permission_type AS EventSecurityPermissionType, 
			Extent11.perm_pos AS EventSecurityPermPos, 

			-- Extent12 - event_template table
			Extent12.event_id AS EventTemplateEventId, 
			Extent12.template_id AS EventTemplateTemplateId, 
			Extent12.encounter_id AS EventTemplateEncounterId, 
			Extent12.case_id AS EventTemplateCaseId, 
			Extent12.group_id AS EventTemplateGroupId, 
			Extent12.group_encounter_id AS EventTemplateGroupEncounterId, 
			Extent12.extended_popup_id AS EventTemplateExtendedPopupId, 
			Extent12.sequence_id AS EventTemplateSequenceId, 
			Extent12.practice_id AS EventTemplatePracticeId,

			-- event_data_trail table columns
			edt.event_id AS EventDataTrailEventId,
			edt.location AS Location,
			edt.field AS Field,
			edt.old_value AS OldValue,
			edt.new_value AS NewValue,
			edt.description AS Description
	FROM 
		(SELECT 
			eam.event_id AS EventId, 
			eam.category_id AS CategoryId, 
			eam.action_id AS ActionId, 
			eam.event_timestamp AS EventTimestamp, 
			eam.station_id AS StationId, 
			eam.enterprise_id AS EnterpriseId, 
			eam.practice_id AS PracticeId, 
			eam.person_id AS PersonId, 
			eam.event_message AS EventMessage, 
			eam.location_id AS LocationId, 
			eam.event_timestamp_timezone AS EventTimestampTimezone, 
			eam.action_type AS ActionType, 
			eam.source_product_description AS SourceProductDescription, 
			eam.site_id AS SiteId, 
			eam.process_id AS ProcessId, 
			eam.hash_code AS HashCode, 
			eam.created_by AS CreatedBy, 
			eam.hash_version AS HashVersion,
			ROW_NUMBER() OVER (ORDER BY eam.event_timestamp ASC) AS RowNum
		FROM event_audit_mstr AS eam WITH(NOLOCK)
		WHERE eam.enterprise_id = @pi_enterprise_id 
			AND eam.practice_id = @pi_practice_id 
			AND eam.event_timestamp >= @pi_audit_event_from_date 
			AND eam.event_timestamp < @pi_audit_event_to_date) AS ae
	LEFT OUTER JOIN event_account AS Extent1 WITH(NOLOCK) ON ae.EventId = Extent1.event_id
	LEFT OUTER JOIN event_application_access AS Extent2 WITH(NOLOCK) ON ae.EventId = Extent2.event_id
	LEFT OUTER JOIN event_chart_summary AS Extent3 WITH(nolock) ON ae.EventId = Extent3.event_id
	LEFT OUTER JOIN event_encounter AS Extent4 WITH(nolock) ON ae.EventId = Extent4.event_id
	LEFT OUTER JOIN event_ics AS Extent5 WITH(nolock) ON ae.EventId = Extent5.event_id
	LEFT OUTER JOIN event_interface_holding_tank AS Extent6 WITH(nolock) ON ae.EventId = Extent6.event_id
	LEFT OUTER JOIN event_interoperability AS Extent7 WITH(nolock) ON ae.EventId = Extent7.event_id
	LEFT OUTER JOIN event_paq AS Extent8 WITH(nolock) ON ae.EventId = Extent8.event_id
	LEFT OUTER JOIN event_patient_education AS Extent9 WITH(nolock) ON ae.EventId = Extent9.event_id
	LEFT OUTER JOIN event_rosetta AS Extent10 WITH(nolock) ON ae.EventId = Extent10.event_id
	LEFT OUTER JOIN event_security AS Extent11 WITH(nolock) ON ae.EventId = Extent11.event_id
	LEFT OUTER JOIN event_template AS Extent12 WITH(nolock) ON ae.EventId = Extent12.event_id
	LEFT OUTER JOIN event_data_trail AS edt ON ae.EventId = edt.event_id
	WHERE ae.RowNum > @pi_skip_size AND ae.RowNum <= (@pi_skip_size + @pi_page_size)
	ORDER BY ae.EventTimestamp DESC;

END
SET NOCOUNT OFF

GO