USE [Arizona];
GO
BEGIN TRANSACTION;
SET XACT_ABORT ON;

IF OBJECT_ID('tempdb..#pat')IS NOT NULL BEGIN;
	DROP TABLE #pat;
END;

CREATE TABLE #pat(patient_id INT NOT NULL);

INSERT INTO [#pat] ([patient_id])
SELECT DISTINCT [c].[PatientId]
  FROM [ActivePos_read].[dbo].[IIIPerson]                  [p]
  INNER JOIN [ActivePos_read].[dbo].[IIICustomerToPerson]  [ctp]
      ON [ctp].[AddressId]  = [p].[AddressId]
      AND [ctp].[Type]      = 1
  INNER JOIN [ActivePos_read].[dbo].[IIICustomer]          [c]
      ON [c].[CustomerId]   = [ctp].[CustomerId]
  INNER JOIN [ActivePos_read].[dbo].[IIIPatient_Insurance] [ci]
      ON [ci].[CustomerId]  = [c].[CustomerId]
  INNER JOIN [ActivePos_read].[dbo].[IIIInsurance_Master]  [i]
      ON [i].[Insurance_Id] = [ci].[Insurance_Guid]
 WHERE ([c].[BlockDossierTax] > 0 OR [c].[BlockLoaTax] > 0)
 AND [i].[Insurance_No_IFAC]  = '1542000';
;

SELECT DISTINCT
       [c].[PatientId]
      ,[p].*
  FROM [ActivePos_read].[dbo].[IIIPerson]                  [p]
  INNER JOIN [ActivePos_read].[dbo].[IIICustomerToPerson]  [ctp]
      ON [ctp].[AddressId]  = [p].[AddressId]
      AND [ctp].[Type]      = 1
  INNER JOIN [ActivePos_read].[dbo].[IIICustomer]          [c]
      ON [c].[CustomerId]   = [ctp].[CustomerId]
  INNER JOIN [#pat] [pat] ON pat.[patient_id] = c.[PatientId]
  INNER JOIN [ActivePos_read].[dbo].[IIIPatient_Insurance] [ci]
      ON [ci].[CustomerId]  = [c].[CustomerId]
  INNER JOIN [ActivePos_read].[dbo].[IIIInsurance_Master]  [i]
      ON [i].[Insurance_Id] = [ci].[Insurance_Guid]
 WHERE ([c].[BlockDossierTax] > 0 OR [c].[BlockLoaTax] > 0)
 AND [i].[Insurance_No_IFAC]  = '1542000';
;


DELETE FROM pl
FROM Arizona.dbo.PH_patient_tax_link pl
    JOIN [#pat] [p] ON p.[patient_id] = PHPTL_patient 
;

ROLLBACK TRANSACTION