CREATE PROC [dbo].[sp_GetClaimResults]
(
	@surfaceItemId INT
)
AS

--DECLARE @surfaceItemId INT = 1017
SELECT	A.invoiceNo
INTO	#AccountTable
FROM	pal_Account A
WHERE	A.surfaceItemId = @surfaceItemId
		AND A.claimSend = 1
		AND A.claimSent = 1
GROUP BY A.invoiceNo

DECLARE @invoiceNo INT

DECLARE @Results TABLE
(
	surfaceItemId INT
	,claimLogId INT
	,invoiceNo INT
	,dateOfService DATETIME
	,patientName VARCHAR(2000)
	,patientSurname VARCHAR(2000)
	,icd1 VARCHAR(150)
	,procedures VARCHAR(2000)
	--,modifier VARCHAR(150)
	,liablePat DECIMAL(18,2)
	,liableMed DECIMAL(18,2)
	,amount DECIMAL(18,2)
	,response VARCHAR(2000)
	,responseParty VARCHAR(2)
	,resultCode VARCHAR(2)
	,result VARCHAR(50)
	,msReversalAllowed BIT
)

WHILE (SELECT COUNT(1) FROM #AccountTable) > 0
BEGIN
	SELECT TOP 1 @invoiceNo = invoiceNo FROM #AccountTable

	INSERT INTO @Results
	SELECT	DISTINCT A.surfaceItemId,C.claimLogId,A.invoiceNo,A.dateOfService,A.patientName,A.patientSurname,A.icd1
			--,A.procedureCode+': '+A.procedureDescription+CASE WHEN A.modifier1<>'' THEN ' ('+A.modifier1+')' ELSE '' END
			,''
			,0,0,0
			--,A.liablePat,A.liableMed,A.amount
			,ISNULL(
				STUFF((
					SELECT '<br/> ' + Code + ': ' + Description 
					FROM pal_MSClaimResponseLine L
					WHERE (L.responseTreatmentId = MIN(RL.responseTreatmentId))
					FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
					,1,5,'') 
				,CASE TR.msDeliveryTypeInd
					WHEN '03' THEN 'Queued'
					END) AS response
			,C.respondingParty responseParty
			,TR.responseResultCode resultCode
			,CASE TR.responseResultCode
					WHEN '01' THEN 'Treatment Accepted for delivery'
					WHEN '02' THEN 'Treatment Accepted for processing'
					WHEN '03' THEN 'Treatment Rejected'
					WHEN '04' THEN 'Treatment Approved for Payment'
					WHEN '05' THEN 'Treatment Approved for Part Payment'
					WHEN '06' THEN 'Treatment Reversal Accepted'
					WHEN '07' THEN 'Treatment Reversal Rejected'
					END AS result
			,MA.msReversalAllowed
	FROM	pal_Account A
			JOIN pal_MedicalScheme MS
				ON MS.code = A.medCode
			JOIN pal_MedicalAdministrator MA
				ON MA.msDestinationCode = MS.msDestinationCode
			LEFT JOIN (
				SELECT	TOP 1 C.recId,C.claimRefNo,C.claimLogId
						,CR.recId responseRecId,CR.respondingParty
				FROM	pal_MSClaim C
						JOIN pal_MSClaimResponse CR
							ON CR.claimLogId = C.claimLogId
				WHERE C.claimRefNo = @invoiceNo ORDER BY CR.recId DESC,C.recId DESC
				) C
				ON C.claimRefNo = A.invoiceNo
			LEFT JOIN pal_MSClaimTreatment T
				ON T.claimId = C.recId
			LEFT JOIN pal_MSClaimDiagnosis D
				ON D.treatmentID = T.recId
			LEFT JOIN pal_MSClaimResponseTreatment TR
				ON TR.claimResponseId = C.responseRecId
			LEFT JOIN pal_MSClaimResponseLine RL
				ON RL.responseTreatmentId = TR.recId
	WHERE	A.invoiceNo = @invoiceNo
	GROUP BY A.surfaceItemId,C.claimLogId,A.invoiceNo,A.dateOfService,A.patientName,A.patientSurname,A.icd1
			,A.procedureCode+': '+A.procedureDescription+CASE WHEN A.modifier1<>'' THEN ' ('+A.modifier1+')' ELSE '' END
			,A.liablePat,A.liableMed,A.amount
			,TR.msDeliveryTypeInd,C.respondingParty,TR.responseResultCode
			,MA.msReversalAllowed

	DELETE FROM #AccountTable WHERE invoiceNo = @invoiceNo
END

DROP TABLE #AccountTable

SELECT	*
FROM	@Results

--SELECT	*
--FROM	pal_MSClaimResponse
--WHERE	claimRefNo = 97