-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2018-01-04
-- Description:	Get all medical aid invoices with claim status
-- =============================================
CREATE PROCEDURE sp_GetMedicalAidInvoices 
	@startDate AS DATETIME, 
	@endDate AS DATETIME
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

		/*
	01 = Claim Accepted for delivery
	02 = Claim Accepted for processing
	03 = Claim Rejected
	04 = Claim Approved for Payment
	05 = Claim Approved for Part Payment
	06 = Claim Reversal Accepted
	07 = Claim Reversal Rejected
	07, 03
	*/

	SELECT A.patientNumber AS PatientNumber, A.patientName AS PatientName, A.patientSurname AS PatientSurname, S.name as 'MedicalAid', A.invoiceNo AS InvoiceNo, A.dateOfService AS DateOfService, 
				   STUFF ((SELECT        '<br/> ' + procedureCode + ': ' + procedureDescription + CASE WHEN modifier1 <> '' THEN ' (' + modifier1 + ')' ELSE '' END
				FROM            pal_Account B
				WHERE        B.invoiceNo = A.invoiceNo AND isVisible = 1 FOR XML PATH(''), TYPE ).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 5, '') AS [Procedures],
			SUM(A.amount) as InvoiceAmount, SUM(CASE WHEN A.claimSent = 0 THEN A.liableMed ELSE 0 END) as Claimable,    
			SUM(CASE WHEN A.claimSent = 1 THEN A.liableMed ELSE 0 END) as ClaimedNotPaid,     
			SUM(dbo.GetAccountPaidByMedicalAidAmount(A.allocatedReference)) PaidByMedicalAid,
			SUM(A.allocated - dbo.GetAccountPaidByMedicalAidAmount(A.allocatedReference)) TotalPaid,
			A.claimSent AS ClaimSent, 
			CASE WHEN A.claimSent = 1 THEN LEFT(CONVERT(VARCHAR(150), A.claimDate, 120), 10) ELSE '' END AS ClaimDate, A.claimNumber AS ClaimNumber, 
			ISNULL(CASE R.responseResultCode 
				WHEN '01' THEN 'Claim Accepted for delivery' 
				WHEN '02' THEN 'Claim Accepted for processing' 
				WHEN '03' THEN 'Claim Rejected' 
				WHEN '04' THEN 'Claim Approved for Payment' 
				WHEN '05' THEN 'Claim Approved for Part Payment' 
				WHEN '06' THEN 'Claim Reversal Accepted' 
				WHEN '07' THEN 'Claim Reversal Rejected' 
				WHEN '' THEN 'Variant Response' 
			END, '') AS ResponseResult, 
			ISNULL(CASE R.respondingParty WHEN '01' THEN 'MediSwitch' WHEN '02' THEN 'Medical Scheme / Administrator' END, '') AS RespondingParty, 
			ISNULL(CASE R.msDeliveryTypeInd WHEN '01' THEN 'Real-Time' WHEN '02' THEN 'Batched' WHEN '03' THEN 'Queued' WHEN '04' THEN 'Rejected' END, '') AS DeliveryType, 
			CAST(CASE WHEN M.msReversalAllowed = 1 AND R.respondingParty = '02' AND R.responseResultCode NOT IN ('06', '07') THEN 1 ELSE 0 END AS BIT) AS AllowReversal, 
			CAST(CASE WHEN R.msDeliveryTypeInd = '03' THEN 1 ELSE 0 END AS BIT) AS AllowForceFetch, 
				  CAST(CASE WHEN R.respondingParty = '01' AND R.responseResultCode = '03' THEN 1 ELSE 0 END AS BIT) AS AllowResubmit
	FROM    dbo.pal_Account AS A 
	OUTER APPLY
	(SELECT TOP 1 * FROM dbo.pal_MSClaimResponse WHERE claimLogId = A.claimNumber AND claimRefNo = A.invoiceNo ORDER BY recId DESC) R 
	LEFT OUTER JOIN dbo.pal_MedicalScheme AS S ON S.code = A.medCode 
	LEFT OUTER JOIN dbo.pal_MedicalAdministrator AS M ON M.msDestinationCode = S.msDestinationCode
	WHERE A.procedureType = 'TI' AND  A.claimSend = 1 AND A.isVisible = 1 AND (A.claimSent = 1 OR A.liableMed > 0) 
	AND A.dateOfService BETWEEN @startDate AND @endDate
	GROUP BY A.patientNumber, A.patientName, A.patientSurname, S.name, A.invoiceNo, A.dateOfService, A.claimSent,
		   CASE WHEN A.claimSent = 1 THEN LEFT(CONVERT(VARCHAR(150), A.claimDate, 120), 10) ELSE '' END, 
		   A.claimNumber,R.responseResultCode, 
		   ISNULL(CASE R.respondingParty WHEN '01' THEN 'MediSwitch' WHEN '02' THEN 'Medical Scheme / Administrator' END, ''),
		   ISNULL(CASE R.msDeliveryTypeInd WHEN '01' THEN 'Real-Time' WHEN '02' THEN 'Batched' WHEN '03' THEN 'Queued' WHEN '04' THEN 'Rejected' END, ''),
		   CAST(CASE WHEN M.msReversalAllowed = 1 AND R.respondingParty = '02' AND R.responseResultCode NOT IN ('06', '07') THEN 1 ELSE 0 END AS BIT),
		   CAST(CASE WHEN R.msDeliveryTypeInd = '03' THEN 1 ELSE 0 END AS BIT), 
		   CAST(CASE WHEN R.respondingParty = '01' AND R.responseResultCode = '03' THEN 1 ELSE 0 END AS BIT)


END
GO
