/*

Title:  sproc_IDDTGeneralReview
Description: Returns claims for clients that have been tagged with an IDDT affilation
				code for a given date range
Tables used: tbldata_claimsextract, tbldata_affilation_extract, tbldata_IDDTProviders
			 (tbldata_IDDTProviders is a support table that stores the UPID and dates of participating
				IDDT providers)

Author: jdm 2/3/2008

Note:  This is a very basic stord procedure used to demonstrate how affiliation codes could be
	   be used to extract IDDT information from an extract.  This stored procedure is basic by 
	   design to make it easy to follow. 

*/

USE Bullfrog;
GO

IF OBJECT_ID('dbo.sproc_IDDTGeneralReview') IS NOT NULL
	DROP PROCEDURE dbo.sproc_IDDTGeneralReview
GO

CREATE PROCEDURE dbo.sproc_IDDTGeneralReview
	@BegServDate datetime,
	@EndServDate datetime
AS

	SELECT c.N_PROV,
	CLAIMNO,
	SUBNO,
	CPLAN,
	PROVNO,
	N_PROV,
	BATCH,
	SUBLINE,
	SERVDATE,
	QUANTITY,
	BILLAMT,
	ALLOWED,
	NOTCOV,
	COPAY,
	DEDUCT,
	OTHCAMT,
	WITHHOLD,
	NETAMT,
	ADJUSTR,
	CLMSTAT,
	PROCSTAT,
	MEDDEF,
	POSTDATE,
	GLREF,
	DKEY,
	[REVERSE],
	IN_FILE ,
	PROV_NPI,
	brdPROCDESC,
	brdMCDNON   
	FROM tbldata_claimsextract c
	WHERE SERVDATE BETWEEN @BegServDate AND @EndServDate
    AND
-- Check to see if provider is participating in IDDT program
-- May want to further filter by checking to see if service date is 
-- within the Provider's program participation dates
	UPID IN (
	SELECT UPID FROM tbldata_IDDTProviders
	)
	AND 
-- checks to see if value exists in Affilation extract for this particular
-- SUBNO between given service dates
	EXISTS
(SELECT SUBNO FROM tbldata_Affiliation_extract a 
 WHERE a.SUBNO = c.SUBNO 
 AND AFFCODE = 'IDDT' AND
 (c.SERVDATE >= a.EFFDATE AND c.SERVDATE <= COALESCE(a.TERMDATE, '9999-01-01')))