Database

SELECT     
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'C'
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'R'
		WHEN PTT.POSTransactionTypeCode = 'I' THEN 'C' 
	END AS TransactionType, 
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A'
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'A'
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'A'
		WHEN PTT.POSTransactionTypeCode = 'I' THEN 'I' 
	END AS ProviderStatus, 
-- right format. uncomment when the PIN numbers are fixed.
'00000' + C.ProviderNbr + SPACE(1) AS ContractNbr,
-- right format. uncomment when sending the provider numbers that were added wrongly.
--'000000' + C.ContractNbr AS ContractNbr,
	'01' AS Other, 
	'000000' AS PIN, 
	UPPER(LEFT(C.ProviderName, 28)) AS ContractName,
	'PROGRAM DIRECTOR' + SPACE(8) AS ContractAddressAttention,
	UPPER(LEFT(A.StreetAddress1, 24)) AS Address1, 
	UPPER(LEFT(A.StreetAddress2, 24)) AS Address2,
	UPPER(LEFT(A.City, 17)) AS City,
	UPPER(S.PK_Geographic_Location_Code) AS State,
	LEFT(A.Zip1, 9) AS ZipCd,
	SPACE(12) AS ErrorCode2,
	SPACE(8) AS Filler
FROM         
	POSPINTransaction AS E INNER JOIN
	Provider AS C ON E.FKProviderId = C.PKProviderId INNER JOIN
	ProviderDate AS PD ON dbo.Udf_GetPKProviderDate(C.PKProviderId) = PD.PKContractDate INNER JOIN
	ProviderAddress AS PA ON PA.FKProviderId=C.PKProviderId INNER JOIN
	[Address] AS A ON A.PKAddressId = PA.FKAddressId INNER JOIN
	--ParentTypeLKP AS PT ON A.FKParentType = PT.PKParentType AND PT.ParentTypeCd = 'usrContract' INNER JOIN
	[State] AS S ON A.FK_State = S.PKStateId INNER JOIN
	POSTransactionTypeLKP PTT ON E.FKPOSTransactionTypeId=PTT.PKPOSTransactionTypeId
WHERE     
	(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (E.FKPOSTransactionTypeId IN (SELECT PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode IN ('A', 'C', 'R'))) OR
	(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (E.FKPOSTransactionTypeId = (SELECT Top 1 PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode = 'I')) AND (PD.EffectiveDateTo IS NULL) OR
	(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (PD.EffectiveDateTo <= GETDATE())
UNION
SELECT     
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'C' 
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'R' 
		WHEN PTT.POSTransactionTypeCode= 'I' THEN 'C' 
	END AS TransactionType, 
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'I' THEN 'I' 
	END AS ProviderStatus, SP.NPINumber AS ContractNbr,
	'01' AS Other,
	'000000' AS PIN,
	UPPER(LEFT(SP.FirstName, 28)) AS ContractName,
	'PROGRAM DIRECTOR' + SPACE(8) AS ContractAddressAttention,
	UPPER(LEFT(ADDR.StreetAddress1, 24)) AS Address1, 
	UPPER(LEFT(ADDR.StreetAddress2, 24)) AS Address2,
	UPPER(LEFT(ADDR.City, 17)) AS City,
	UPPER(ST.PK_Geographic_Location_Code) AS State,
	LEFT(ADDR.Zip1, 9) AS ZipCd,
	SPACE(12) AS ErrorCode2,
	SPACE(8) AS Filler
FROM         
	POSPINTransaction AS EDS INNER JOIN
	NPIAssociation NA ON NA.FKParentId=EDS.FKProviderId AND NA.FKParentTypeLKPId = (SELECT Min(PKParentTypeLKPId) FROM ParentTypeLKP WHERE ParentCode='PRV') INNER JOIN 
	NPI AS SP ON SP.PKNPIId = NA.FKNPIId LEFT OUTER JOIN
	POSTransactionError AS EDSERR ON EDSERR.FKPOSPINTransactionId = EDS.PKPOSPINTransactionId LEFT OUTER JOIN
	POSTransactionErrorLKP AS EDSERRCD ON EDSERRCD.PKPOSTransactionErrorLKPId = EDSERR.FKPOSTransactionErrorLKPId LEFT OUTER JOIN
	NPIAddress AS NPIADDR ON NPIADDR.FKNPIId = SP.PKNPIId AND NPIADDR.IsCurrentAddress = 1 INNER JOIN
	[Address] AS ADDR ON ADDR.PKAddressId=NPIADDR.FKAddressId INNER JOIN
	[State] AS ST ON ADDR.FK_State = ST.PKStateId INNER JOIN
	County AS CO ON CO.PKCountyId = ADDR.FK_County INNER JOIN
	POSTransactionTypeLKP PTT ON EDS.FKPOSTransactionTypeId=PTT.PKPOSTransactionTypeId
WHERE     
	(EDS.TransactionSentDate IS NULL) AND (EDS.TransactionReceivedDate IS NULL) AND 
	(EDS.FKPOSTransactionTypeId IN (SELECT PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode IN ('A', 'C', 'R')) 
	OR	EDS.FKPOSTransactionTypeId = (SELECT Top 1 PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode = 'I'))