CREATE PROC [dbo].[sp_RemittanceListingByDate]
(

	@DateFrom DATETIME
	,@DateTo DATETIME 
	,@EraStatusFilter VARCHAR(1000)
	,@ProcessedStatusFilter VARCHAR(1000) 
)
AS

DECLARE @query  AS NVARCHAR(MAX), @journalWhere as VARCHAR(MAX)

--recursive function to split the comma delimited eraClaimItemId over multiple rows with the corresponding recId
set @query = N'
with pal_MSEraClaimItemAccountSplit(recId, singleEraClaimItemId, eraClaimItemId) as (
select recId, LEFT(eraClaimItemId, CHARINDEX('','',eraClaimItemId+'','')-1),
    STUFF(eraClaimItemId, 1, CHARINDEX('','',eraClaimItemId+'',''), '''')
from pal_MSEraClaimItemAccount
union all
select recId, LEFT(eraClaimItemId, CHARINDEX('','',eraClaimItemId+'','')-1),
    STUFF(eraClaimItemId, 1, CHARINDEX('','',eraClaimItemId+'',''), '''')
from pal_MSEraClaimItemAccountSplit
where eraClaimItemId > ''''
)  '

--select from result of recursive function joined with other era tables
set @query = @query + N' 
select LEFT(CONVERT(VARCHAR(150),l.originalProcessDate, 120), 10) AS Date,
CASE i.claimResponseResultCode WHEN ''03'' THEN ''Claim Rejected'' WHEN ''08'' THEN ''Claim Adjusted'' WHEN ''09'' THEN ''Claim Paid in Full'' WHEN ''10'' THEN ''Claim Part Paid'' ELSE '''' END AS EraStatus,
e.eraReferenceNumber AS EraReferenceNumber,
CASE WHEN l.deleted = 1 THEN ''Deleted'' WHEN l.rejected = 1 THEN ''Rejected'' WHEN l.accountId = 0 AND i.totalItemPaidAmount < 0 THEN ''Unprocessed - Payment Reversal'' WHEN l.accountId = 0 THEN ''Unprocessed'' 
ELSE CASE 
WHEN l.manualProcessed = 1 THEN ''Processed by '' + ISNULL(u.name, '''') 
WHEN a.allocatedReference = '''' THEN ''Processed - Unallocated'' 
WHEN l.manualAllocated = 1 THEN ''Processed by ERA, Allocated by '' + ISNULL(u1.name, '''')
ELSE ''Processed by ERA'' END END AS ProcessedStatus, 
                         CASE WHEN l.rejected = 1 OR a.recId IS NULL THEN '''' ELSE ''REC'' + CONVERT(VARCHAR(2), RIGHT(dbo.GetFiscalYear(A.dateOfTransaction), 2)) + REPLACE(STR(A.receiptNo, 6), SPACE(1), ''0'') END AS ReceiptNumber, ISNULL(a.patientNumber, '''') 
                         AS PatientNumber, CASE WHEN a.recId IS NULL THEN m.patientFullName + '' '' + m.patientSurname ELSE A.patientName + '' '' + A.patientSurname END AS Patient, 
						 LEFT(CONVERT(VARCHAR(150),m.patientDOB, 120), 10) AS patientDOB, m.patientIdNumber, ISNULL(a.medAidName, 
                         e.medicalSchemeName) AS MedicalAid, 
						 ISNULL(r.responseDescription, '''') AS ResponseDescription, ISNULL(g.generalComments,'''') AS GeneralComments, 
						 e.disclaimer AS Disclaimer,
						 i.itemClaimAmount AS ClaimAmount, i.totalItemPaidAmount AS PaidAmount
from pal_MSEraClaimItemAccountSplit s 
left outer join pal_MSEraClaimItemAccount l on l.recId = s.recId 
left outer join pal_MSEraClaimItem i on i.recId = s.singleEraClaimItemId
left outer join pal_MSEraMember m on m.recId = i.eraMemberId
left outer join pal_MSEra e on e.recId = m.eraId
left outer join pal_Account a on a.recId = l.accountId
left outer join pal_vUserShared u on l.manualProcessUserId = u.recId
left outer join pal_vUserShared u1 on l.manualAllocateUserId = u1.recId
left outer join (SELECT DISTINCT r2.eraClaimItemId, 
		SUBSTRING(
			(
				SELECT '', ''+ r1.responseDescription AS [text()]
				FROM pal_MSEraClaimItemResponse r1
				WHERE r1.eraClaimItemId = r2.eraClaimItemId
				FOR XML PATH ('''')
			), 3, 4000) AS responseDescription
		FROM pal_MSEraClaimItemResponse r2 
		) r ON r.eraClaimItemId = s.singleEraClaimItemId 
left outer join (SELECT DISTINCT g2.eraClaimItemId,
		SUBSTRING(
			(
				SELECT '', ''+ g1.generalComments AS [text()]
				FROM pal_MSEraClaimItemGeneralComments g1
				WHERE g1.eraClaimItemId = g2.eraClaimItemId
				FOR XML PATH ('''')
			), 3, 4000) AS generalComments
		FROM pal_MSEraClaimItemGeneralComments g2 ) g ON g.eraClaimItemId = i.recId 
where LEFT(CONVERT(VARCHAR(50), l.originalProcessDate, 120) , 10)  
			BETWEEN ''' + LEFT(CONVERT(VARCHAR(50), @DateFrom, 120) , 10) + ''' AND ''' + LEFT(CONVERT(VARCHAR(50), @DateTo, 120) , 10) + ''' '

IF @EraStatusFilter <> ''
	set @query = @query + N' AND CASE i.claimResponseResultCode WHEN ''03'' THEN ''Claim Rejected'' WHEN ''08'' THEN ''Claim Adjusted'' WHEN ''09'' THEN ''Claim Paid in Full'' WHEN ''10'' THEN ''Claim Part Paid'' ELSE '''' END IN (' + @EraStatusFilter + ') '

IF @ProcessedStatusFilter <> '' 
	set @query = @query + N' AND CASE WHEN l.deleted = 1 THEN ''Deleted'' WHEN l.rejected = 1 THEN ''Rejected'' WHEN l.accountId = 0 AND i.totalItemPaidAmount < 0 THEN ''Unprocessed - Payment Reversal'' WHEN l.accountId = 0 THEN ''Unprocessed'' 
								ELSE CASE 
								WHEN l.manualProcessed = 1 THEN ''Processed by '' + ISNULL(u.name, '''') 
								WHEN a.allocatedReference = '''' THEN ''Processed - Unallocated'' 
								WHEN l.manualAllocated = 1 THEN ''Processed by ERA, Allocated by '' + ISNULL(u1.name, '''')
								ELSE ''Processed by ERA'' END END
							IN (' + @ProcessedStatusFilter + ') '

set @query = @query + N'

union all

select LEFT(CONVERT(VARCHAR(150),j.journalDate, 120), 10) AS Date, ''eRA Journal'' AS EraStatus, e.eraReferenceNumber AS EraReferenceNumber, ''Unprocessed - Journal'' AS ProcessedStatus, 
'''' AS ReceiptNumber, '''' AS PatientNumber, '''' AS Patient, '''' AS patientDOB, '''' AS patientIdNumber, e.medicalSchemeName AS MedicalAid, 
j.journalDescription AS ResponseDescription, '''' AS GeneralComments, 
e.disclaimer AS Disclaimer,
0 AS ClaimAmount, j.journalAmount AS PaidAmount
from pal_MSEraJournal j  
left outer join pal_MSEra e on e.recId = j.eraId  
where LEFT(CONVERT(VARCHAR(50), j.journalDate, 120) , 10)  
			BETWEEN ''' + LEFT(CONVERT(VARCHAR(50), @DateFrom, 120) , 10) + ''' AND ''' + LEFT(CONVERT(VARCHAR(50), @DateTo, 120) , 10) + ''' '

IF @EraStatusFilter <> ''
	set @journalWhere = N' AND ''eRA Journal'' IN (' + @EraStatusFilter + ') '

IF @ProcessedStatusFilter <> '' 
	set @journalWhere = N' AND ''Unprocessed - Journal'' IN (' + @ProcessedStatusFilter + ') '



set @query = @query + ISNULL(@journalWhere, '') + N' ORDER BY Date '

--print @query

exec sp_executesql @query





GO


