CREATE VIEW [dbo].[pal_vAccountsClaimable]
AS
SELECT	A.surfaceItemId
		,0 claimLogId
		,A.invoiceNo
		,A.dateOfService
		,A.patientName
		,A.patientSurname
		,A.medCode
		,S.name [MedPlan]
		,A.icd1
		,I.icdDescription
		,STUFF((
			SELECT '<br/> ' + procedureCode + ': ' + procedureDescription + 
					CASE WHEN modifier1<>'' THEN ' ('+modifier1+')' ELSE '' END
			FROM	pal_Account B
			WHERE	B.invoiceNo = A.invoiceNo
					AND isVisible = 1
					AND claimSend = 1
					AND claimSent = 0
					AND liableMed > 0
			FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
		  ,1,5,'') AS procedures
		--,modifier1 modifier
		,SUM(liablePat) liablePat
		,SUM(liableMed) liableMed
		,SUM(amount) amount
		,'' response
		,'' comment
		,'' responseParty
		,'' resultCode
		,'' result
		,CAST(0 AS BIT) msReversalAllowed
FROM	pal_Account A
		JOIN pal_MedicalICD10 I
			ON I.icdCode = A.icd1
		JOIN pal_MedicalScheme S
			ON S.code = A.medCode
WHERE	claimSend = 1
		AND isVisible = 1
		AND claimSent = 0
		AND liableMed > 0
		AND msDestinationCode IS NOT NULL
GROUP BY A.surfaceItemId
		,A.invoiceNo
		,A.dateOfService
		,A.patientName
		,A.patientSurname
		,A.medCode
		,S.name
		,A.icd1
		,I.icdDescription
		--,modifier1

GO


