IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SPUSS_220_EVDE_SAGLIK_HIZMETI_IZLEM_KAYIT]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE SPUSS_220_EVDE_SAGLIK_HIZMETI_IZLEM_KAYIT
GO

CREATE PROCEDURE [dbo].[SPUSS_220_EVDE_SAGLIK_HIZMETI_IZLEM_KAYIT] 
	@HDOSYAID INT=0,
	@HMUAYENEID INT=0,
	@TUMUNUTEKRAR BIT = 0
	 	 
AS
BEGIN
	--SPUSS_220_EVDE_SAGLIK_HIZMETI_IZLEM_KAYIT 165788,115370,1
	DECLARE @HATA NVARCHAR(2000) = ''
	DECLARE @MESAJ NVARCHAR(2000) = ''
	DECLARE @SAYIHATA BIT = 0
	DECLARE @XMLBASLIK NVARCHAR(2000) = ''
	DECLARE @SUBEID INT = 1
	DECLARE @goNDERIMTABLOSU TABLE 
		(
			goNDERILECEKXML XML,
			HATA NVARCHAR(2000),
			MESAJ NVARCHAR(2000),	
			XMLBASLIK NVARCHAR(3500),
			TABLOID INT,
			MINID INT,
			MAXID INT
		)
	
	IF OBJECT_ID('tempdb..#TMPUSSDIGER') IS NOT NULL 
	DROP TABLE #TMPUSSDIGER


SELECT 
 USS220.ID USSDIGER_ID
,USS220.HDOSYAID
,USS220.HMUAYENEID
,USS220.HKIMLIKID
,USS220.HYATISID,


(SELECT TOP 1 ICDKOD FROM HDOSYAICD WHERE HDOSYAICD.HDOSYAID=HDOSYA.HDOSYAID ORDER BY ANATANI DESC) PRIMER_TANI_KODU,
(SELECT TOP 1 (SELECT TICD.ICDADI FROM TICD WHERE TICD.ICDKOD=HDOSYAICD.ICDKOD) FROM HDOSYAICD WHERE HDOSYAICD.HDOSYAID=HDOSYA.HDOSYAID ORDER BY ANATANI DESC) PRIMER_TANI_ADI,

(SELECT TOP 1 ICDKOD FROM HDOSYAICD WHERE HDOSYAICD.HDOSYAID=HDOSYA.HDOSYAID ORDER BY ANATANI DESC) BIRLIKTE_GORULEN_EK_HASTALIKLAR_KODU,
(SELECT TOP 1 (SELECT TICD.ICDADI FROM TICD WHERE TICD.ICDKOD=HDOSYAICD.ICDKOD) FROM HDOSYAICD WHERE HDOSYAICD.HDOSYAID=HDOSYA.HDOSYAID ORDER BY ANATANI DESC)BIRLIKTE_GORULEN_EK_HASTALIKLAR_ADI,

USS220.AGRI AS AGRI_ID,
TAGRI.AD AS AGRI_ADI,

USS220.BASI_DEGERLENDIRMESI AS BASI_DEGERLENDIRMESI_ID,
TBASI_DEGERLENDIRMESI.AD AS BASI_DEGERLENDIRMESI_ADI,

USS220.BESLENME AS BESLENME_ID,
TBESLENME.AD AS BESLENME_ADI,

USS220.EVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI AS EVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI_ID,
TEVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI.AD AS EVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI_ADI,

USS220.EVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI AS EVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI_ID,
TEVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI.AD AS EVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI_ADI,

USS220.HIZMET_ALINACAK_IL AS HIZMET_ALINACAK_IL_ID,
THIZMET_ALINACAK_IL.AD AS HIZMET_ALINACAK_IL_ADI,

USS220.BIR_SONRAKI_HIZMET_IHTIYACI AS BIR_SONRAKI_HIZMET_IHTIYACI_ID,
TBIR_SONRAKI_HIZMET_IHTIYACI.AD AS BIR_SONRAKI_HIZMET_IHTIYACI_ADI,

USS220.PSIKOLOJIK_DURUM_DEGERLENDIRMESI_1 AS PSIKOLOJIK_DURUM_DEGERLENDIRMESI_1_ID,
TPSIKOLOJIK_DURUM_DEGERLENDIRMESI_1.AD AS PSIKOLOJIK_DURUM_DEGERLENDIRMESI_1_ADI,

USS220.PSIKOLOJIK_DURUM_DEGERLENDIRMESI_2 AS PSIKOLOJIK_DURUM_DEGERLENDIRMESI_2_ID,
TPSIKOLOJIK_DURUM_DEGERLENDIRMESI_2.AD AS PSIKOLOJIK_DURUM_DEGERLENDIRMESI_2_ADI,

USS220.VERILEN_EGITIMLER_1 AS VERILEN_EGITIMLER_1_ID,
TVERILEN_EGITIMLER_1.AD AS VERILEN_EGITIMLER_1_ADI,

USS220.VERILEN_EGITIMLER_2 AS VERILEN_EGITIMLER_2_ID,
TVERILEN_EGITIMLER_2.AD AS VERILEN_EGITIMLER_2_ADI,

HDOSYA.SUBEID HDOSYA_SUBEID
	  ,SUBELER.SUBEADI SUBELER_SUBEADI
	  ,HDOSYA.SNOTAKIPNO HDOSYA_SNOTAKIPNO
	  ,		 ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='Saglik-Net' AND PROGNAME='MEDISOFT' AND KEYNAME= CONVERT(VARCHAR,HDOSYA.SUBEID)+' numarali subede Yenidoğanların Kimliksiz fatura edilebilir gün sayısı'),'') 	AS SAGLIKNETKIMLIKSIZYENIDOGANGUN,

		CASE WHEN (SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='Saglik-Net' AND PROGNAME='MEDISOFT' AND /* VALUESTR='1' AND*/ KEYNAME LIKE CONVERT(VARCHAR,HDOSYA.SUBEID)+' numaral% %ubede Sa%l%k Net Online test modu aktif')='1' THEN
			'10000038' 
		ELSE
			ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='Saglik-Net' AND PROGNAME='MEDISOFT' AND KEYNAME= CONVERT(VARCHAR,HDOSYA.SUBEID)+' numaralı şubenin kurum numarası'),'') 
		END AS SAGLIKNETKURUMKODU,

			CASE WHEN (SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='Saglik-Net' AND PROGNAME='MEDISOFT' AND /* VALUESTR='1' AND*/ KEYNAME LIKE CONVERT(VARCHAR,HDOSYA.SUBEID)+' numaral% %ubede Sa%l%k Net Online test modu aktif')='1' THEN
			'3bc26a7d-4a19-4edf-ab9f-1413ac85e7d0' /*TEST KTS FİRMA KODU*/
		ELSE
			'C740D0288ED4C45FE0407C0A04162BDD'  /*KTS TALYA*/ 
		END AS SNOKTSFIRMAKODU,

		ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='Saglik-Net' AND PROGNAME='MEDISOFT' AND KEYNAME= CONVERT(VARCHAR,HDOSYA.SUBEID)+' numarali subede Hasta kayıt gönderiminde referans no ön Karakteri'),'') AS REFNOONEK

INTO #TMPUSSDIGER 

FROM USS220_EVDE_SAGLIK_HIZMETI_IZLEM AS USS220
INNER JOIN HDOSYA ON HDOSYA.HDOSYAID = USS220.HDOSYAID
LEFT JOIN HKIMLIK ON HKIMLIK.HKIMLIKID=HDOSYA.HKIMLIKID
LEFT JOIN	SUBELER ON SUBELER.SUBEID=HDOSYA.SUBEID

LEFT JOIN 
(
SELECT 'VAR' AS AD, 1 AS ID
UNION ALL
SELECT 'YOK' AS AD, 97 AS ID
) TAGRI ON TAGRI.ID=USS220.AGRI

LEFT JOIN 
(
SELECT 'VAR' AS AD, 1 AS ID
UNION ALL
SELECT 'YOK' AS AD, 97 AS ID
) TBASI_DEGERLENDIRMESI ON TBASI_DEGERLENDIRMESI.ID=USS220.BASI_DEGERLENDIRMESI

LEFT JOIN 
(
SELECT 'YETERLİ' AS AD, 1 AS ID
UNION ALL
SELECT 'YETERSİZ' AS AD, 2 AS ID
) TBESLENME ON TBESLENME.ID=USS220.BESLENME

LEFT JOIN
(
SELECT 'HASTA VE AİLESİNİN TEDAVİYE UYUMSUZLUĞU' AS AD, 1 AS ID
UNION ALL
SELECT 'HASTA VE AİLESİNİN TEDAVİYİ SONLANDIRMA TALEBİ' AS AD, 2 AS ID
UNION ALL
SELECT 'HASTANIN AİLE HEKİMİ TARAFINDAN TAKİBİNİN UYGUN GÖRÜLMESİ' AS AD, 3 AS ID
UNION ALL
SELECT 'HASTANIN EVDE SAĞLIK BİRİMİ TARAFINDAN TAKİBİNİN UYGUN GÖRÜLMESİ' AS AD, 4 AS ID
UNION ALL
SELECT 'HASTANIN MOBİL EKİP TARAFINDAN TAKİBİNİN UYGUN GÖRÜLMESİ' AS AD, 5 AS ID
UNION ALL
SELECT 'HASTANIN SAĞLIK KURUMUNA YATIRILARAK TEDAVİSİNİN UYGUN GÖRÜLMESİ' AS AD, 6 AS ID
UNION ALL
SELECT 'ADRES DEĞİŞİKLİĞİ' AS AD, 7 AS ID
UNION ALL
SELECT 'İYİLEŞME' AS AD, 8 AS ID
UNION ALL
SELECT 'TEDAVİNİN SAĞLIK PERSONELİ OLMADAN SÜRDÜRÜLEBİLİR HALE GELMESİ' AS AD, 9 AS ID
UNION ALL
SELECT 'HASTA VEFAT ETTI' AS AD, 10 AS ID
UNION ALL
SELECT 'DİĞER' AS AD, 98 AS ID
) TEVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI ON TEVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI.ID = USS220.EVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI

LEFT JOIN
(
SELECT 'VAR' AS AD, 1 AS ID
UNION ALL
SELECT 'YOK' AS AD, 97 AS ID
) TEVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI ON TEVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI.ID = USS220.EVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI

LEFT JOIN
(
SELECT 'ADANA' AS AD, 1 AS ID
UNION ALL
SELECT 'ADIYAMAN' AS AD, 2 AS ID
UNION ALL
SELECT 'AFYONKARAHİSAR' AS AD, 3 AS ID
UNION ALL
SELECT 'AĞRI' AS AD, 4 AS ID
UNION ALL
SELECT 'AMASYA' AS AD, 5 AS ID
UNION ALL
SELECT 'ANKARA' AS AD, 6 AS ID
UNION ALL
SELECT 'ANTALYA' AS AD, 7 AS ID
UNION ALL
SELECT 'ARTVİN' AS AD, 8 AS ID
UNION ALL
SELECT 'AYDIN' AS AD, 9 AS ID
UNION ALL
SELECT 'BALIKESİR' AS AD, 10 AS ID
UNION ALL
SELECT 'BİLECİK' AS AD, 11 AS ID
UNION ALL
SELECT 'BİNGÖL' AS AD, 12 AS ID
UNION ALL
SELECT 'BİTLİS' AS AD, 13 AS ID
UNION ALL
SELECT 'BOLU' AS AD, 14 AS ID
UNION ALL
SELECT 'BURDUR' AS AD, 15 AS ID
UNION ALL
SELECT 'BURSA' AS AD, 16 AS ID
UNION ALL
SELECT 'ÇANAKKALE' AS AD, 17 AS ID
UNION ALL
SELECT 'ÇANKIRI' AS AD, 18 AS ID
UNION ALL
SELECT 'ÇORUM' AS AD, 19 AS ID
UNION ALL
SELECT 'DENİZLİ' AS AD, 20 AS ID
UNION ALL
SELECT 'DİYARBAKIR' AS AD, 21 AS ID
UNION ALL
SELECT 'EDİRNE' AS AD, 22 AS ID
UNION ALL
SELECT 'ELAZIĞ' AS AD, 23 AS ID
UNION ALL
SELECT 'ERZİNCAN' AS AD, 24 AS ID
UNION ALL
SELECT 'ERZURUM' AS AD, 25 AS ID
UNION ALL
SELECT 'ESKİŞEHİR' AS AD, 26 AS ID
UNION ALL
SELECT 'GAZİANTEP' AS AD, 27 AS ID
UNION ALL
SELECT 'GİRESUN' AS AD, 28 AS ID
UNION ALL
SELECT 'GÜMÜŞHANE' AS AD, 29 AS ID
UNION ALL
SELECT 'HAKKARİ' AS AD, 30 AS ID
UNION ALL
SELECT 'HATAY' AS AD, 31 AS ID
UNION ALL
SELECT 'ISPARTA' AS AD, 32 AS ID
UNION ALL
SELECT 'MERSİN' AS AD, 33 AS ID
UNION ALL
SELECT 'İSTANBUL' AS AD, 34 AS ID
UNION ALL
SELECT 'İZMİR' AS AD, 35 AS ID
UNION ALL
SELECT 'KARS' AS AD, 36 AS ID
UNION ALL
SELECT 'KASTAMONU' AS AD, 37 AS ID
UNION ALL
SELECT 'KAYSERİ' AS AD, 38 AS ID
UNION ALL
SELECT 'KIRKLARELİ' AS AD, 39 AS ID
UNION ALL
SELECT 'KIRŞEHİR' AS AD, 40 AS ID
UNION ALL
SELECT 'KOCAELİ' AS AD, 41 AS ID
UNION ALL
SELECT 'KONYA' AS AD, 42 AS ID
UNION ALL
SELECT 'KÜTAHYA' AS AD, 43 AS ID
UNION ALL
SELECT 'MALATYA' AS AD, 44 AS ID
UNION ALL
SELECT 'MANİSA' AS AD, 45 AS ID
UNION ALL
SELECT 'KAHRAMANMARAŞ' AS AD, 46 AS ID
UNION ALL
SELECT 'MARDİN' AS AD, 47 AS ID
UNION ALL
SELECT 'MUĞLA' AS AD, 48 AS ID
UNION ALL
SELECT 'MUŞ' AS AD, 49 AS ID
UNION ALL
SELECT 'NEVŞEHİR' AS AD, 50 AS ID
UNION ALL
SELECT 'NİĞDE' AS AD, 51 AS ID
UNION ALL
SELECT 'ORDU' AS AD, 52 AS ID
UNION ALL
SELECT 'RİZE' AS AD, 53 AS ID
UNION ALL
SELECT 'SAKARYA' AS AD, 54 AS ID
UNION ALL
SELECT 'SAMSUN' AS AD, 55 AS ID
UNION ALL
SELECT 'SİİRT' AS AD, 56 AS ID
UNION ALL
SELECT 'SİNOP' AS AD, 57 AS ID
UNION ALL
SELECT 'SİVAS' AS AD, 58 AS ID
UNION ALL
SELECT 'TEKİRDAĞ' AS AD, 59 AS ID
UNION ALL
SELECT 'TOKAT' AS AD, 60 AS ID
UNION ALL
SELECT 'TRABZON' AS AD, 61 AS ID
UNION ALL
SELECT 'TUNCELİ' AS AD, 62 AS ID
UNION ALL
SELECT 'ŞANLIURFA' AS AD, 63 AS ID
UNION ALL
SELECT 'UŞAK' AS AD, 64 AS ID
UNION ALL
SELECT 'VAN' AS AD, 65 AS ID
UNION ALL
SELECT 'YOZGAT' AS AD, 66 AS ID
UNION ALL
SELECT 'ZONGULDAK' AS AD, 67 AS ID
UNION ALL
SELECT 'AKSARAY' AS AD, 68 AS ID
UNION ALL
SELECT 'BAYBURT' AS AD, 69 AS ID
UNION ALL
SELECT 'KARAMAN' AS AD, 70 AS ID
UNION ALL
SELECT 'KIRIKKALE' AS AD, 71 AS ID
UNION ALL
SELECT 'BATMAN' AS AD, 72 AS ID
UNION ALL
SELECT 'ŞIRNAK' AS AD, 73 AS ID
UNION ALL
SELECT 'BARTIN' AS AD, 74 AS ID
UNION ALL
SELECT 'ARDAHAN' AS AD, 75 AS ID
UNION ALL
SELECT 'IĞDIR' AS AD, 76 AS ID
UNION ALL
SELECT 'YALOVA' AS AD, 77 AS ID
UNION ALL
SELECT 'KARABÜK' AS AD, 78 AS ID
UNION ALL
SELECT 'KİLİS' AS AD, 79 AS ID
UNION ALL
SELECT 'OSMANİYE' AS AD, 80 AS ID
UNION ALL
SELECT 'DÜZCE' AS AD, 81 AS ID
) THIZMET_ALINACAK_IL ON THIZMET_ALINACAK_IL.ID = USS220.HIZMET_ALINACAK_IL

LEFT JOIN
(
SELECT 'AİLE HEKİMİ DEĞERLENDİRİLMESİ' AS AD, 1 AS ID
UNION ALL
SELECT 'AİLE/BAKICI EĞİTİMİ' AS AD, 2 AS ID
UNION ALL
SELECT 'DİYETİSYEN' AS AD, 3 AS ID
UNION ALL
SELECT 'FİZYOTERAPİST' AS AD, 4 AS ID
UNION ALL
SELECT 'HEYET RAPORU' AS AD, 5 AS ID
UNION ALL
SELECT 'SOSYAL ÇALIŞMACI' AS AD, 6 AS ID
UNION ALL
SELECT 'UZMAN HEKİM/KONSULTAN HEKİM DEĞERLENDİRMESİ' AS AD, 7 AS ID
) TBIR_SONRAKI_HIZMET_IHTIYACI ON TBIR_SONRAKI_HIZMET_IHTIYACI.ID = USS220.BIR_SONRAKI_HIZMET_IHTIYACI

LEFT JOIN
(
SELECT 'AŞIRI DALGIN' AS AD, 1 AS ID
UNION ALL
SELECT 'HUZURSUZ-GERGİN' AS AD, 2 AS ID
UNION ALL
SELECT 'KAYITSIZ' AS AD, 3 AS ID
UNION ALL
SELECT 'NORMAL' AS AD, 4 AS ID
UNION ALL
SELECT 'ÜZÜNTÜLÜ' AS AD, 5 AS ID
UNION ALL
SELECT 'DİĞER' AS AD, 98 AS ID
) TPSIKOLOJIK_DURUM_DEGERLENDIRMESI_1 ON TPSIKOLOJIK_DURUM_DEGERLENDIRMESI_1.ID = USS220.PSIKOLOJIK_DURUM_DEGERLENDIRMESI_1

LEFT JOIN
(
SELECT 'AŞIRI DALGIN' AS AD, 1 AS ID
UNION ALL
SELECT 'HUZURSUZ-GERGİN' AS AD, 2 AS ID
UNION ALL
SELECT 'KAYITSIZ' AS AD, 3 AS ID
UNION ALL
SELECT 'NORMAL' AS AD, 4 AS ID
UNION ALL
SELECT 'ÜZÜNTÜLÜ' AS AD, 5 AS ID
UNION ALL
SELECT 'DİĞER' AS AD, 98 AS ID
) TPSIKOLOJIK_DURUM_DEGERLENDIRMESI_2 ON TPSIKOLOJIK_DURUM_DEGERLENDIRMESI_2.ID = USS220.PSIKOLOJIK_DURUM_DEGERLENDIRMESI_2

LEFT JOIN
(
SELECT 'AİLE EVDE BAKIM EĞİTİMİ' AS AD, 1 AS ID
UNION ALL
SELECT 'BESLENME EĞİTİMİ' AS AD, 2 AS ID
UNION ALL
SELECT 'FİZİK TEDAVİ VE REHABİLİTASYON EĞİTİMİ' AS AD, 3 AS ID
UNION ALL
SELECT 'PSİKOSOSYAL EĞİTİM' AS AD, 4 AS ID
UNION ALL
SELECT 'ORAL HİJYEN BAKIM EĞİTİMİ' AS AD, 5 AS ID
) TVERILEN_EGITIMLER_1 ON TVERILEN_EGITIMLER_1.ID = USS220.VERILEN_EGITIMLER_1

LEFT JOIN
(
SELECT 'AİLE EVDE BAKIM EĞİTİMİ' AS AD, 1 AS ID
UNION ALL
SELECT 'BESLENME EĞİTİMİ' AS AD, 2 AS ID
UNION ALL
SELECT 'FİZİK TEDAVİ VE REHABİLİTASYON EĞİTİMİ' AS AD, 3 AS ID
UNION ALL
SELECT 'PSİKOSOSYAL EĞİTİM' AS AD, 4 AS ID
UNION ALL
SELECT 'ORAL HİJYEN BAKIM EĞİTİMİ' AS AD, 5 AS ID
) TVERILEN_EGITIMLER_2 ON TVERILEN_EGITIMLER_2.ID = USS220.VERILEN_EGITIMLER_2

WHERE USS220.HMUAYENEID= @HMUAYENEID AND USS220.HDOSYAID = @HDOSYAID 


	IF (SELECT COUNT(*) FROM #TMPUSSDIGER) = 0 
	BEGIN
		SET @SAYIHATA = 1
		SET @MESAJ = @MESAJ + 'Gönderilecek kayıt bulunamadı! '
	END

	IF (SELECT COUNT(*) FROM #TMPUSSDIGER WHERE HDOSYA_SUBEID IS NULL) > 0 
		SET @HATA = @HATA + 'dosyasının şube kaydı bulunamadı! '

	IF (SELECT COUNT(*) FROM #TMPUSSDIGER WHERE ISNULL(HDOSYA_SNOTAKIPNO,'')='') > 0 
		SET @HATA = @HATA + 'Sağlık Net hasta takip numarası alınmadan USS sistemine kayIT yapılamaz. '

   	IF @HATA='' AND @SAYIHATA=0
	BEGIN		
		SELECT @SUBEID = HDOSYA_SUBEID FROM #TMPUSSDIGER
		DECLARE @TBLXMLBASLIK TABLE (XMLBASLIK NVARCHAR(2000), HATA NVARCHAR(2000),MESAJ NVARCHAR(2000))
		INSERT INTO @TBLXMLBASLIK (XMLBASLIK, HATA, MESAJ) EXEC SPUSS_000_XML_BASLIK @SUBEID		
		SELECT @HATA=HATA, @MESAJ=MESAJ, @XMLBASLIK=XMLBASLIK FROM @TBLXMLBASLIK		
	END



	IF @HATA='' AND @SAYIHATA=0
	BEGIN


		--HER TETKIK KARTI AYRI AYRI goNDER DONGUSU
   		DECLARE @CURUSSDIGERID INT
		DECLARE CURUSSDIGER CURSOR FOR SELECT USSDIGER_ID FROM #TMPUSSDIGER
		OPEN CURUSSDIGER
		FETCH NEXT FROM CURUSSDIGER INTO @CURUSSDIGERID
		WHILE @@FETCH_STATUS =0
		BEGIN
		

	
					SET @HATA = ''
					SET @MESAJ = ''
					SET @SAYIHATA = 0

		
					 
					IF EXISTS(SELECT USSDIGER_ID FROM #TMPUSSDIGER WHERE  ISNULL(USSDIGER_ID,0)=0)
						SET @HATA = @HATA + ' ' + 'Kayıt seçilmemiş. '


					IF (SELECT COUNT(*) FROM #TMPUSSDIGER WHERE USSDIGER_ID=@CURUSSDIGERID) > 0
					BEGIN
						IF @HATA = '' AND @SAYIHATA=0
						BEGIN
							BEGIN TRY 
								INSERT INTO @goNDERIMTABLOSU (goNDERILECEKXML,HATA,MESAJ,XMLBASLIK,TABLOID,MINID,MAXID)
								SELECT							 
									(
									
										SELECT 
										   NEWID() AS 'messageGuid/@value',
										   '1' AS 'messageType/@version',
										   '0a9ba485-e7e0-4abb-9c86-0a14fd364bb8' as 'messageType/@codeSystemGuid',
										   '220' AS 'messageType/@code',
										   'Evde Saglik Hizmeti Izlem Veri Seti' AS 'messageType/@value',
										   LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),GETDATE(),120),' ',''),'-',''),':',''),12) as 'documentGenerationTime/@value',
										   (
											  SELECT	
												 '1' AS 'healthcareProvider/@version',
												 'c3eade04-4f91-5dab-e043-14031b0ac9f9' as 'healthcareProvider/@codeSystemGuid',
												 SAGLIKNETKURUMKODU as 'healthcareProvider/@code',
												 SUBELER_SUBEADI as 'healthcareProvider/@value'  
												 FOR XML PATH('author'), TYPE),
												 --'C740D0288ED4C45FE0407C0A04162BDD' as 'firmaKodu/@value' ,
												 SNOKTSFIRMAKODU as 'firmaKodu/@value' ,
												 (
													SELECT 
														(
														   SELECT 
															  (
																 SELECT 
																   
																--<AGRI version="1" codeSystemGuid="a87d1c7a-36f1-4950-87a3-0cc1b61c1265" code="Code" value="AGRI" />
																		'1' AS 'AGRI/@version',
																		'a87d1c7a-36f1-4950-87a3-0cc1b61c1265' AS 'AGRI/@codeSystemGuid',
																		ISNULL(AGRI_ID,'') AS 'AGRI/@code',
																		ISNULL(AGRI_ADI,'') AS 'AGRI/@value',

																--<BASI_DEGERLENDIRMESI version="1" codeSystemGuid="26f3177d-b761-4e29-bbbc-b4fac7df3322" code="Code" value="BASI_DEGERLENDIRMESI" />
																		'1' AS 'BASI_DEGERLENDIRMESI/@version',
																		'26f3177d-b761-4e29-bbbc-b4fac7df3322' AS 'BASI_DEGERLENDIRMESI/@codeSystemGuid',
																		ISNULL(BASI_DEGERLENDIRMESI_ID,'') AS 'BASI_DEGERLENDIRMESI/@code',
																		ISNULL(BASI_DEGERLENDIRMESI_ADI,'') AS 'BASI_DEGERLENDIRMESI/@value',

																--<BESLENME version="1" codeSystemGuid="6a220e1d-3865-4cab-b98f-b7933d9aa1ba" code="Code" value="BESLENME" />
																		'1' AS 'BESLENME/@version',
																		'6a220e1d-3865-4cab-b98f-b7933d9aa1ba' AS 'BESLENME/@codeSystemGuid',
																		ISNULL(BESLENME_ID,'') AS 'BESLENME/@code',
																		ISNULL(BESLENME_ADI,'') AS 'BESLENME/@value',

																--<EVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI version="1" codeSystemGuid="bb0d92ea-397f-4c8d-989c-f00aa9d5d7ff" code="Code" value="EVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI" />
																		'1' AS 'EVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI/@version',
																		'bb0d92ea-397f-4c8d-989c-f00aa9d5d7ff' AS 'EVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI/@codeSystemGuid',
																		ISNULL(EVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI_ID,'') AS 'EVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI/@code',
																		ISNULL(EVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI_ADI,'') AS 'EVDE_SAGLIK_HIZMETININ_SONLANDIRILMASI/@value',

																--<EVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI version="1" codeSystemGuid="a301b5d0-f467-458b-81e1-27a48a049c99" code="Code" value="EVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI" />
																		'1' AS 'EVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI/@version',
																		'a301b5d0-f467-458b-81e1-27a48a049c99' AS 'EVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI/@codeSystemGuid',
																		ISNULL(EVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI_ID,'') AS 'EVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI/@code',
																		ISNULL(EVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI_ADI,'') AS 'EVDE_SAGLIK_HIZMETLERI_HASTA_NAKLI/@value',

																--<HIZMET_ALINACAK_IL version="1" codeSystemGuid="5bc508fa-782a-4d75-831f-34948e350e72" code="Code" value="HIZMET_ALINACAK_IL" />
																		'1' AS 'HIZMET_ALINACAK_IL/@version',
																		'5bc508fa-782a-4d75-831f-34948e350e72' AS 'HIZMET_ALINACAK_IL/@codeSystemGuid',
																		ISNULL(HIZMET_ALINACAK_IL_ID,'') AS 'HIZMET_ALINACAK_IL/@code',
																		ISNULL(HIZMET_ALINACAK_IL_ADI,'') AS 'HIZMET_ALINACAK_IL/@value',																   
																   


																--<BIR_SONRAKI_HIZMET_IHTIYACI_BILGISI>
																	--<BIR_SONRAKI_HIZMET_IHTIYACI version="1" codeSystemGuid="1ac7dc71-e09d-4ddd-bea5-179735d35246" code="Code" value="BIR_SONRAKI_HIZMET_IHTIYACI" />
																--</BIR_SONRAKI_HIZMET_IHTIYACI_BILGISI>
																	   (SELECT 
																		'1' AS 'BIR_SONRAKI_HIZMET_IHTIYACI/@version',
																		'1ac7dc71-e09d-4ddd-bea5-179735d35246' AS 'BIR_SONRAKI_HIZMET_IHTIYACI/@codeSystemGuid',
																			ISNULL(BIR_SONRAKI_HIZMET_IHTIYACI_ID,'') AS 'BIR_SONRAKI_HIZMET_IHTIYACI/@code',
																			ISNULL(BIR_SONRAKI_HIZMET_IHTIYACI_ADI,'') AS 'BIR_SONRAKI_HIZMET_IHTIYACI/@value'
																			FOR XML PATH('BIR_SONRAKI_HIZMET_IHTIYACI_BILGISI'),TYPE),

																--<PSIKOLOJIK_DURUM_DEGERLENDIRMESI_BILGISI>
																	--<PSIKOLOJIK_DURUM_DEGERLENDIRMESI version="1" codeSystemGuid="8ed2e8ce-b1fe-49f6-91ac-8f3340deac9d" code="Code" value="PSIKOLOJIK_DURUM_DEGERLENDIRMESI" />
																	--<PSIKOLOJIK_DURUM_DEGERLENDIRMESI version="1" codeSystemGuid="8ed2e8ce-b1fe-49f6-91ac-8f3340deac9d" code="Code" value="PSIKOLOJIK_DURUM_DEGERLENDIRMESI" />
																--</PSIKOLOJIK_DURUM_DEGERLENDIRMESI_BILGISI>
																	(SELECT 
																		(
																			SELECT
																				'1' AS '@version',
																				'8ed2e8ce-b1fe-49f6-91ac-8f3340deac9d' AS '@codeSystemGuid',
																				ISNULL(PSIKOLOJIK_DURUM_DEGERLENDIRMESI_1_ID,'')  AS '@code',
																				ISNULL(PSIKOLOJIK_DURUM_DEGERLENDIRMESI_1_ADI,'') AS '@value'
																			FOR XML PATH('PSIKOLOJIK_DURUM_DEGERLENDIRMESI'), TYPE
																		),

																		(
																			SELECT
																				'1' AS '@version',
																				'8ed2e8ce-b1fe-49f6-91ac-8f3340deac9d' AS '@codeSystemGuid',
																				ISNULL(PSIKOLOJIK_DURUM_DEGERLENDIRMESI_2_ID,'')  AS '@code',
																				ISNULL(PSIKOLOJIK_DURUM_DEGERLENDIRMESI_2_ADI,'') AS '@value'
																			FOR XML PATH('PSIKOLOJIK_DURUM_DEGERLENDIRMESI'), TYPE
																		)

																			FOR XML PATH('PSIKOLOJIK_DURUM_DEGERLENDIRMESI_BILGISI'),TYPE),

															
																	
                                                                     (SELECT 
																		(
																			SELECT
																				'1' AS '@version',
																				'79aaa967-1886-4d9d-a5d8-cdde552d94ad' AS '@codeSystemGuid',
																				ISNULL(VERILEN_EGITIMLER_1_ID,'')  AS '@code',
																				ISNULL(VERILEN_EGITIMLER_1_ADI,'') AS '@value'
																			FOR XML PATH('VERILEN_EGITIMLER'), TYPE
																		),

																		(
																			SELECT
																				'1' AS '@version',
																				'79aaa967-1886-4d9d-a5d8-cdde552d94ad' AS '@codeSystemGuid',
																				ISNULL(VERILEN_EGITIMLER_2_ID,'')  AS '@code',
																				ISNULL(VERILEN_EGITIMLER_2_ADI,'') AS '@value'
																			FOR XML PATH('VERILEN_EGITIMLER'), TYPE
																		)

																			FOR XML PATH('VERILEN_EGITIMLER_BILGISI'),TYPE)

																	
 		
																FROM #TMPUSSDIGER
																
																FOR XML PATH(''),TYPE
															)
														FOR XML PATH('EVDE_SAGLIK_HIZMETI_IZLEM'),TYPE
													),					
													   (
														SELECT ISNULL(HDOSYA_SNOTAKIPNO, '' ) AS 'SYSTakipNo/@value'
														FOR XML PATH('HASTA_TAKIP_BILGISI'), TYPE 
													)							
												 FOR XML PATH('recordData'), TYPE
											  )
										   FROM #TMPUSSDIGER WITH(NOLOCK)
										   WHERE USSDIGER_ID = @CURUSSDIGERID
										FOR XML PATH (''), ROOT('SYSMessage')


									) AS goNDERILECEKXML,
									@HATA AS HATA, 
									@MESAJ AS MESAJ,
									@XMLBASLIK AS XMLBASLIK,
									@CURUSSDIGERID AS TABLOID,
									0 AS MINID,
									0 AS MAXID

							END TRY
							BEGIN CATCH
								SET @HATA = ERROR_MESSAGE()
								IF @HATA = ''
									SET @HATA = 'Bilinmeyen bir hata oluştu'

								INSERT INTO @goNDERIMTABLOSU (goNDERILECEKXML,HATA,MESAJ,XMLBASLIK,TABLOID,MINID,MAXID)
								SELECT	'' AS goNDERILECEKXML,
										@HATA AS HATA, 
										@MESAJ AS MESAJ,
										'' AS XMLBASLIK,
										@CURUSSDIGERID AS TABLOID,
										0 AS MINID,
										0 AS MAXID
			

							END CATCH
						END
						ELSE
						BEGIN

							INSERT INTO @goNDERIMTABLOSU (goNDERILECEKXML,HATA,MESAJ,XMLBASLIK,TABLOID,MINID,MAXID)
							SELECT	'' AS goNDERILECEKXML,
									LEFT(@HATA,2000) AS HATA, 
									@MESAJ AS MESAJ,
									'' AS XMLBASLIK,
									@CURUSSDIGERID AS TABLOID,
									0 AS MINID,
									0 AS MAXID

							SET @HATA = ''		 
						END
					END




		FETCH NEXT FROM CURUSSDIGER INTO @CURUSSDIGERID 
		END --son HER TETKIK KARTI AYRI AYRI goNDER DONGUSU
	

		CLOSE CURUSSDIGER
		DEALLOCATE CURUSSDIGER

		IF (SELECT COUNT(*) FROM @GONDERIMTABLOSU)=0
			INSERT INTO @GONDERIMTABLOSU (GONDERILECEKXML,HATA,MESAJ,XMLBASLIK,TABLOID,MINID,MAXID)
			SELECT	'' AS GONDERILECEKXML,
					'' AS HATA, 
					'Tüm KAYITLAR zaten gönderilmiş' AS MESAJ,
					'' AS XMLBASLIK,
					0 AS TABLOID,
					0 AS MINID,
					0 AS MAXID


	END
	ELSE
	BEGIN

		INSERT INTO @goNDERIMTABLOSU (goNDERILECEKXML,HATA,MESAJ,XMLBASLIK,TABLOID,MINID,MAXID)
		SELECT	'' AS goNDERILECEKXML,
				LEFT(@HATA,2000) AS HATA, 
				@MESAJ AS MESAJ,
				'' AS XMLBASLIK,
				@CURUSSDIGERID AS TABLOID,
				0 AS MINID,
				0 AS MAXID

		SET @HATA = ''		 
	END


	
	IF OBJECT_ID('tempdb..#TMPUSSDIGER') IS NOT NULL 
	DROP TABLE #TMPUSSDIGER

	

	SELECT * FROM @goNDERIMTABLOSU


END
