

USE [ccrsdb]
GO

/****** Object:  StoredProcedure [dbo].[PMPM_VALIDATE_1]    Script Date: 5/24/2018 10:08:55 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- exec [PMPM_VALIDATE_1]

CREATE  PROCEDURE [dbo].[PMPM_VALIDATE_1]  
AS
BEGIN
	-- Stored Proc to validate PMPM invoice lines against CCRS claim data. First step.
	SET NOCOUNT ON;

	declare @b_key bigint ; 

	declare @i_pmpm_invoice_key bigint, @i_referral_number varchar(100), @i_prior_authorization_number varchar(100), @i_icn varchar(100);
	declare @i_date_of_service date, @i_edipi varchar(100), @i_date_paid date ,@i_amount_paid decimal(18,2);
	declare @i_billed_amount decimal(18,2), @i_ccn_provider_name varchar(500), @i_ccn_provider_npi varchar(100), @i_clin_code varchar(100);

	declare @d_key bigint;
	-- declare @totRecs integer, @totValid integer, @totInvalid integer, @totUnique integer, @totRepeat integer;
	-- declare @header_text varchar(200), @footer_text varchar(200);
	declare @clinCode varchar(30);
	declare @runDate varchar(12) = format(CURRENT_TIMESTAMP, 'MM/dd/yyyy');

	  declare @int1 integer;
	  declare @big1 bigint;

	declare c01 cursor for 
	select pmpm_batch_log_key -- , status as transmission_status, header_history_key, footer_history_key, url 
	from das.PMPM_BATCH_LOG
	where report_timestamp is null;

	-- update das.PMPM_BATCH_LOG set report_timestamp = '2000-01-01 00:00:00' where header_history_key is null;

	open c01;
	
	fetch next from c01 into @b_key;
	while @@FETCH_STATUS = 0
	begin -- loop on c01

	    print 'c01 loop on ' + ltrim(str(@b_key));
	   
		-- NO LONGER UPDATING BATCH_REPORT TABLE. Report Builder can create intermediate info.

		-- during development, we'll NULL out invoice reporting details to make re-runs easier
		update das.PMPM_INVOICE set report_timestamp = NULL, report_status = NULL, report_duplicate_key = NULL
		  where batch_log_key = @b_key;
		
		-- print 'updating invoice lines for batch ' + str(@b_key);

		declare c02 cursor forward_only for 
	      -- select pmpm_invoice_key, icn, edipi, clin_code, status
		  select pmpm_invoice_key, referral_number, prior_authorization_number, icn ,date_of_service ,edipi,
			date_paid ,amount_paid ,billed_amount ,ccn_provider_name ,ccn_provider_npi, clin_code
	       from das.PMPM_INVOICE
	       where report_status is null and batch_log_key = @b_key
		   for update of status, report_status, report_duplicate_key;

		open c02;

        fetch next from c02 into @i_pmpm_invoice_key, @i_referral_number, @i_prior_authorization_number, @i_icn, @i_date_of_service, 
		  @i_edipi, @i_date_paid, @i_amount_paid, @i_billed_amount, @i_ccn_provider_name, @i_ccn_provider_npi, @i_clin_code;
	    while @@FETCH_STATUS = 0
	    begin -- loop on c02
		    -- see if this is valid or a duplicate

			-- copy old TEST002 validations here

			-- NOTE I moved individual record validation checks into a cursor. Since we have to consider each record 
			-- individually when looking for duplicates, and most records should pass the simple validation checks and
			-- lookups, running everything through a cursor avoids creating multiple dynasets if we did checks via set logig.

			-- ICN validation
			-- NOTE ICN/PCN processing is changing. Validation rules will have to follow. 

			set @int1 = NULL;
			select @int1 = patient_key from dbo.DIM_PATIENT where icn = @i_icn;
			if (@int1 is null)
			begin
				-- print '   CLMUF03I on ' + ltrim(str(@i_pmpm_invoice_key));
				update das.PMPM_INVOICE
				   set report_status = 'RptInvalid', status = 'CLMUF03I'
				   where current of c02;
			end
			
			else begin -- NO INDENT -- if ICN found
			-- Referral Number validation
			set @int1 = NULL;
			select @int1 = min(claim_key) from dbo.DIM_VA_CLAIM where referral_number = @i_referral_number;
			if (@int1 is null)
			begin
				-- print '   CLMUF001 on ' + ltrim(str(@i_pmpm_invoice_key));
				update das.PMPM_INVOICE
				   set report_status = 'RptInvalid', status = 'CLMUF001'
				   where current of c02;
			end


	-- TODO: Figure out how PCNs work.  CLMUF03P . Note Build 3 Sprint 1 will change database structure. Work out commented validations in 
	-- new structure.


	--	Select set of DIM_VA_CLAIM rows which match referral_number and (ICN or PCN).
--	Check if Date Paid can be found in column date_paid in ONE of the tables
--  F_DENTAL_CLAIM_DETAILS or F_INSTITUTIONAL_MEDICAL_CLAIM_DETAILS or F_PHARMACY_CLAIM_DETAILS or F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS, 
-- joined on column claim_key. There should be matches in at most one table, though multiple rows in that table could be a match.
-- If no date match on a valid line, set status to CLMUF0607
-- 	Check for Provider in DIM_PROVIDER
--   	If not found, set status to CLMLUF1011
--	Select set of DIM_VA_CLAIM rows which match referral_number and (ICN or PCN) and Provider. If no approved claim is found, set status to CLMLUR1011
--	If all above checks are passed, set status to Verf1OK


-- end old TEST002 code here

			else begin -- NO INDENT -- if Referral Number found

			-- TBD: error put if invalid claim


			-- see if this is a unique record, or duplicate of another.

			
--      IF a Valid Record Lines CLIN on the PMPM_INVOICE does not match another Valid Record Lines CLIN on PMPM_INVOICE 
--          THEN note the lines Valid Status as Y.
--  IF a Valid Record Lines ICN (or PCN) on the PMPM_INVOICE does not match another Valid Record Lines ICN (or PCN) on the PMPM_INVOICE 
--          THEN note the lines Valid Status as Y. 
--  IF a Valid Record Lines Date Paid value on the PMPM_INVOICE is not the same month as another Valid Record Lines Date Paid on the PMPM_INVOICE 
--          THEN note the lines Valid Status as Y. 
--  IF a Valid Record Lines CLIN on the PMPM_INVOICE matches another Valid Record Lines CLIN AND its ICN (or PCN) match 
--    AND their Date Paid value is the same month on the PMPM_INVOICE  
--          THEN note the lines Valid Status as Y-D AND include in the Reject Reason field: Duplicate of Valid Record (Invoice Key: XXX).

	
				-- see if this ICN or PCN already seen TODO: PCN
				set @d_key = NULL;
				print 'icn ' + @i_icn + ' batch_log_key ' + str(@b_key) + ' clin_code ' + @i_clin_code;
				select @d_key = pmpm_invoice_key from das.PMPM_INVOICE
				    where batch_log_key = @b_key and icn = @i_icn and clin_code = @i_clin_code and report_status = 'RptUnique';

				if (@d_key is null)
				begin
					print '   RptUnique on ' + ltrim(str(@i_pmpm_invoice_key));
					update das.PMPM_INVOICE
						set report_status = 'RptUnique', status = 'Verf1OK'
						where current of c02;
				end
				else begin
					print '   RptDupICN on ' + ltrim(str(@i_pmpm_invoice_key));
					update das.PMPM_INVOICE
						set report_status = 'RptDupICN', report_duplicate_key = @d_key, status = 'Verf1OK'
						where current of c02;
				end

			end; -- if Referral Number found
			end; -- if ICN found


			fetch next from c02 into @i_pmpm_invoice_key, @i_referral_number, @i_prior_authorization_number, @i_icn, @i_date_of_service, 
		  @i_edipi, @i_date_paid, @i_amount_paid, @i_billed_amount, @i_ccn_provider_name, @i_ccn_provider_npi, @i_clin_code;
		end; -- loop on c02

		close c02;
	    deallocate c02;

		fetch next from c01 into @b_key;
	end -- loop on c01

	close c01;
	deallocate c01;

END;

-- exec PMPM_TEST003



GO



