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

CREATE PROCEDURE [dbo].[SPUSS_230_INTIHAR_GIRISIMI_VE_KRIZ_TESPIT_KAYIT] 
	@HDOSYAID INT=0,
	@HMUAYENEID INT=0,
	@TUMUNUTEKRAR BIT = 0
	 	 
AS
BEGIN
	--SPUSS_230_INTIHAR_GIRISIMI_VE_KRIZ_TESPIT_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 
 USS230.ID USSDIGER_ID
,USS230.HDOSYAID
,USS230.HMUAYENEID
,USS230.HKIMLIKID
,USS230.HYATISID,


USS230.AILESINDE_INTIHAR_GIRISIMI AS AILESINDE_INTIHAR_GIRISIMI_ID,
TAILESINDE_INTIHAR_GIRISIMI.AD AS AILESINDE_INTIHAR_GIRISIMI_ADI,

USS230.AILESINDE_PSIKIYATRIK_VAKA AS AILESINDE_PSIKIYATRIK_VAKA_ID,
TAILESINDE_PSIKIYATRIK_VAKA.AD AS AILESINDE_PSIKIYATRIK_VAKA_ADI,

USS230.INTIHAR_GIRISIMI_GECMISI AS INTIHAR_GIRISIMI_GECMISI_ID,
TINTIHAR_GIRISIMI_GECMISI.AD AS INTIHAR_GIRISIMI_GECMISI_ADI,

USS230.INTIHAR_KRIZ_VAKA_TURU AS INTIHAR_KRIZ_VAKA_TURU_ID,
TINTIHAR_KRIZ_VAKA_TURU.AD AS INTIHAR_KRIZ_VAKA_TURU_ADI,

OLAY_ZAMANI,

USS230.PSIKIYATRIK_TEDAVI_GECMISI AS PSIKIYATRIK_TEDAVI_GECMISI_ID,
TPSIKIYATRIK_TEDAVI_GECMISI.AD AS PSIKIYATRIK_TEDAVI_GECMISI_ADI,

USS230.INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_1 AS INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_1_ID,
TINTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_1.AD AS INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_1_ADI,

USS230.INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_2 AS INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_2_ID,
TINTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_2.AD AS INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_2_ADI,

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

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

USS230.INTIHAR_KRIZ_VAKA_SONUCU_1 AS INTIHAR_KRIZ_VAKA_SONUCU_1_ID,
TINTIHAR_KRIZ_VAKA_SONUCU_1.AD AS INTIHAR_KRIZ_VAKA_SONUCU_1_ADI,

USS230.INTIHAR_KRIZ_VAKA_SONUCU_2 AS INTIHAR_KRIZ_VAKA_SONUCU_2_ID,
TINTIHAR_KRIZ_VAKA_SONUCU_2.AD AS INTIHAR_KRIZ_VAKA_SONUCU_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 USS230_INTIHAR_GIRISIMI_VE_KRIZ_TESPIT AS USS230
INNER JOIN HDOSYA ON HDOSYA.HDOSYAID = USS230.HDOSYAID
LEFT JOIN HKIMLIK ON HKIMLIK.HKIMLIKID=HDOSYA.HKIMLIKID
LEFT JOIN	SUBELER ON SUBELER.SUBEID=HDOSYA.SUBEID

LEFT JOIN 
(
SELECT 'VAR' AS AD, 2 AS ID
UNION ALL
SELECT 'YOK' AS AD, 97 AS ID
) TAILESINDE_INTIHAR_GIRISIMI ON TAILESINDE_INTIHAR_GIRISIMI.ID=USS230.AILESINDE_INTIHAR_GIRISIMI

LEFT JOIN 
(
SELECT 'VAR' AS AD, 0 AS ID
UNION ALL
SELECT 'YOK' AS AD, 97 AS ID
) TAILESINDE_PSIKIYATRIK_VAKA ON TAILESINDE_PSIKIYATRIK_VAKA.ID=USS230.AILESINDE_PSIKIYATRIK_VAKA

LEFT JOIN 
(
SELECT 'BİR DEFA' AS AD, 2 AS ID
UNION ALL
SELECT 'İKİ DEFA' AS AD, 3 AS ID
UNION ALL
SELECT 'ÜÇ DEFA' AS AD, 4 AS ID
UNION ALL
SELECT 'ÜÇTEN FAZLA' AS AD, 5 AS ID
UNION ALL
SELECT 'YOK' AS AD, 97 AS ID
) TINTIHAR_GIRISIMI_GECMISI ON TINTIHAR_GIRISIMI_GECMISI.ID=USS230.INTIHAR_GIRISIMI_GECMISI

LEFT JOIN
(
SELECT 'İNTİHAR GİRİŞİMİ' AS AD, 1 AS ID
UNION ALL
SELECT 'KRİZ VAKASI' AS AD, 2 AS ID
) TINTIHAR_KRIZ_VAKA_TURU ON TINTIHAR_KRIZ_VAKA_TURU.ID = USS230.INTIHAR_KRIZ_VAKA_TURU

LEFT JOIN
(
SELECT 'VAR' AS AD, 2 AS ID
UNION ALL
SELECT 'YOK' AS AD, 97 AS ID
) TPSIKIYATRIK_TEDAVI_GECMISI ON TPSIKIYATRIK_TEDAVI_GECMISI.ID = USS230.PSIKIYATRIK_TEDAVI_GECMISI

LEFT JOIN
(
SELECT 'AİLE' AS AD, 1 AS ID
UNION ALL
SELECT 'ALKOL VE MADDE BAĞIMLILIĞI' AS AD, 2 AS ID
UNION ALL
SELECT 'CİNSEL SORUNLAR' AS AD, 3 AS ID
UNION ALL
SELECT 'ÇOCUKLAR' AS AD, 4 AS ID
UNION ALL
SELECT 'EBEVEYN ÇATIŞMALARI' AS AD, 5 AS ID
UNION ALL
SELECT 'EKONOMİK' AS AD, 6 AS ID
UNION ALL
SELECT 'EVLİLİK' AS AD, 7 AS ID
UNION ALL
SELECT 'EVSİZ KALMA' AS AD, 8 AS ID
UNION ALL
SELECT 'GELİŞİM DÖNEMİ SORUNLARI' AS AD, 9 AS ID
UNION ALL
SELECT 'HASTALIK' AS AD, 10 AS ID
UNION ALL
SELECT 'İLETİŞİM SORUNLARI' AS AD, 11 AS ID
UNION ALL
SELECT 'İŞ - İŞSİZLİK' AS AD, 12 AS ID
UNION ALL
SELECT 'KARŞI CİNS İLE SORUNLAR' AS AD, 13 AS ID
UNION ALL
SELECT 'OKUL' AS AD, 14 AS ID
UNION ALL
SELECT 'ÖLÜM/KAYIP' AS AD, 15 AS ID
UNION ALL
SELECT 'RUHSAL HASTALIK' AS AD, 16 AS ID
UNION ALL
SELECT 'ŞİDDET (AİLE İÇİ ŞİDDET)' AS AD, 17 AS ID
UNION ALL
SELECT 'SINAV KAYGISI' AS AD, 18 AS ID
UNION ALL
SELECT 'SÜREĞEN HASTALIK' AS AD, 19 AS ID
UNION ALL
SELECT 'TACİZ' AS AD, 20 AS ID
UNION ALL
SELECT 'TECAVÜZ' AS AD, 21 AS ID
UNION ALL
SELECT 'YALNIZLIK' AS AD, 22 AS ID
UNION ALL
SELECT 'DİĞER' AS AD, 98 AS ID
UNION ALL
SELECT 'BELİRTİLMEDİ' AS AD, 99 AS ID
) TINTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_1 ON TINTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_1.ID = USS230.INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_1

LEFT JOIN
(
SELECT 'AİLE' AS AD, 1 AS ID
UNION ALL
SELECT 'ALKOL VE MADDE BAĞIMLILIĞI' AS AD, 2 AS ID
UNION ALL
SELECT 'CİNSEL SORUNLAR' AS AD, 3 AS ID
UNION ALL
SELECT 'ÇOCUKLAR' AS AD, 4 AS ID
UNION ALL
SELECT 'EBEVEYN ÇATIŞMALARI' AS AD, 5 AS ID
UNION ALL
SELECT 'EKONOMİK' AS AD, 6 AS ID
UNION ALL
SELECT 'EVLİLİK' AS AD, 7 AS ID
UNION ALL
SELECT 'EVSİZ KALMA' AS AD, 8 AS ID
UNION ALL
SELECT 'GELİŞİM DÖNEMİ SORUNLARI' AS AD, 9 AS ID
UNION ALL
SELECT 'HASTALIK' AS AD, 10 AS ID
UNION ALL
SELECT 'İLETİŞİM SORUNLARI' AS AD, 11 AS ID
UNION ALL
SELECT 'İŞ - İŞSİZLİK' AS AD, 12 AS ID
UNION ALL
SELECT 'KARŞI CİNS İLE SORUNLAR' AS AD, 13 AS ID
UNION ALL
SELECT 'OKUL' AS AD, 14 AS ID
UNION ALL
SELECT 'ÖLÜM/KAYIP' AS AD, 15 AS ID
UNION ALL
SELECT 'RUHSAL HASTALIK' AS AD, 16 AS ID
UNION ALL
SELECT 'ŞİDDET (AİLE İÇİ ŞİDDET)' AS AD, 17 AS ID
UNION ALL
SELECT 'SINAV KAYGISI' AS AD, 18 AS ID
UNION ALL
SELECT 'SÜREĞEN HASTALIK' AS AD, 19 AS ID
UNION ALL
SELECT 'TACİZ' AS AD, 20 AS ID
UNION ALL
SELECT 'TECAVÜZ' AS AD, 21 AS ID
UNION ALL
SELECT 'YALNIZLIK' AS AD, 22 AS ID
UNION ALL
SELECT 'DİĞER' AS AD, 98 AS ID
UNION ALL
SELECT 'BELİRTİLMEDİ' AS AD, 99 AS ID
) TINTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_2 ON TINTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_2.ID = USS230.INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_2

LEFT JOIN
(
SELECT 'TEK GÖRÜŞME YAPILDI' AS AD, 1 AS ID
UNION ALL
SELECT 'TAKİP GÖRÜŞMELERİNE ALINDI' AS AD, 2 AS ID
UNION ALL
SELECT 'PSİKİYATRİ POLİKLİNİĞİNE SEVK EDİLDİ' AS AD, 3 AS ID
UNION ALL
SELECT 'İLAÇ YAZILDI' AS AD, 4 AS ID
UNION ALL
SELECT 'BAŞKA KURUMA SEVK EDİLDİ' AS AD, 5 AS ID
UNION ALL
SELECT 'GÖRÜŞMEYİ TERK' AS AD, 6 AS ID
) TINTIHAR_KRIZ_VAKA_SONUCU_1 ON TINTIHAR_KRIZ_VAKA_SONUCU_1.ID = USS230.INTIHAR_KRIZ_VAKA_SONUCU_1

LEFT JOIN
(
SELECT 'TEK GÖRÜŞME YAPILDI' AS AD, 1 AS ID
UNION ALL
SELECT 'TAKİP GÖRÜŞMELERİNE ALINDI' AS AD, 2 AS ID
UNION ALL
SELECT 'PSİKİYATRİ POLİKLİNİĞİNE SEVK EDİLDİ' AS AD, 3 AS ID
UNION ALL
SELECT 'İLAÇ YAZILDI' AS AD, 4 AS ID
UNION ALL
SELECT 'BAŞKA KURUMA SEVK EDİLDİ' AS AD, 5 AS ID
UNION ALL
SELECT 'GÖRÜŞMEYİ TERK' AS AD, 6 AS ID
) TINTIHAR_KRIZ_VAKA_SONUCU_2 ON TINTIHAR_KRIZ_VAKA_SONUCU_2.ID = USS230.INTIHAR_KRIZ_VAKA_SONUCU_2

WHERE USS230.HMUAYENEID= @HMUAYENEID AND USS230.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',
										   '230' AS 'messageType/@code',
										   'Intihar Girisimi ve Kriz Tespit 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 

																--<AILESINDE_INTIHAR_GIRISIMI version="1" codeSystemGuid="4e437b5d-4b13-420a-9c34-dc50539aa243" code="Code" value="AILESINDE_INTIHAR_GIRISIMI" />
																		'1' AS 'AILESINDE_INTIHAR_GIRISIMI/@version',
																		'4e437b5d-4b13-420a-9c34-dc50539aa243' AS 'AILESINDE_INTIHAR_GIRISIMI/@codeSystemGuid',
																		ISNULL(AILESINDE_INTIHAR_GIRISIMI_ID,'') AS 'AILESINDE_INTIHAR_GIRISIMI/@code',
																		ISNULL(AILESINDE_INTIHAR_GIRISIMI_ADI,'') AS 'AILESINDE_INTIHAR_GIRISIMI/@value',



																--<AILESINDE_PSIKIYATRIK_VAKA version="1" codeSystemGuid="6e4539cb-e8d8-436f-aacf-406a073886de" code="Code" value="AILESINDE_PSIKIYATRIK_VAKA" />
																		'1' AS 'AILESINDE_PSIKIYATRIK_VAKA/@version',
																		'6e4539cb-e8d8-436f-aacf-406a073886de' AS 'AILESINDE_PSIKIYATRIK_VAKA/@codeSystemGuid',
																		ISNULL(AILESINDE_PSIKIYATRIK_VAKA_ID,'') AS 'AILESINDE_PSIKIYATRIK_VAKA/@code',
																		ISNULL(AILESINDE_PSIKIYATRIK_VAKA_ADI,'') AS 'AILESINDE_PSIKIYATRIK_VAKA/@value',

																--<INTIHAR_GIRISIMI_GECMISI version="1" codeSystemGuid="5ad8d67a-08b9-4952-b348-ed3b53195789" code="Code" value="INTIHAR_GIRISIMI_GECMISI" />
																		'1' AS 'INTIHAR_GIRISIMI_GECMISI/@version',
																		'5ad8d67a-08b9-4952-b348-ed3b53195789' AS 'INTIHAR_GIRISIMI_GECMISI/@codeSystemGuid',
																		ISNULL(INTIHAR_GIRISIMI_GECMISI_ID,'') AS 'INTIHAR_GIRISIMI_GECMISI/@code',
																		ISNULL(INTIHAR_GIRISIMI_GECMISI_ADI,'') AS 'INTIHAR_GIRISIMI_GECMISI/@value',

																--<INTIHAR_KRIZ_VAKA_TURU version="1" codeSystemGuid="bad13b15-cc1b-453b-802c-e7a257d2a7a0" code="Code" value="INTIHAR_KRIZ_VAKA_TURU" />
																		'1' AS 'INTIHAR_KRIZ_VAKA_TURU/@version',
																		'bad13b15-cc1b-453b-802c-e7a257d2a7a0' AS 'INTIHAR_KRIZ_VAKA_TURU/@codeSystemGuid',
																		ISNULL(INTIHAR_KRIZ_VAKA_TURU_ID,'') AS 'INTIHAR_KRIZ_VAKA_TURU/@code',
																		ISNULL(INTIHAR_KRIZ_VAKA_TURU_ADI,'') AS 'INTIHAR_KRIZ_VAKA_TURU/@value',

																--<OLAY_ZAMANI value="OLAY_ZAMANI" />
																LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),OLAY_ZAMANI,120),' ',''),'-',''),':',''),12) AS 'OLAY_ZAMANI/@value',

																--<PSIKIYATRIK_TEDAVI_GECMISI version="1" codeSystemGuid="7cb4bcef-9998-4e0d-a442-8a83800bb614" code="Code" value="PSIKIYATRIK_TEDAVI_GECMISI" />
																		'1' AS 'PSIKIYATRIK_TEDAVI_GECMISI/@version',
																		'7cb4bcef-9998-4e0d-a442-8a83800bb614' AS 'PSIKIYATRIK_TEDAVI_GECMISI/@codeSystemGuid',
																		ISNULL(PSIKIYATRIK_TEDAVI_GECMISI_ID,'') AS 'PSIKIYATRIK_TEDAVI_GECMISI/@code',
																		ISNULL(PSIKIYATRIK_TEDAVI_GECMISI_ADI,'') AS 'PSIKIYATRIK_TEDAVI_GECMISI/@value',
																  																										   

																--<INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENI_BILGISI>
																	--<INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI version="1" codeSystemGuid="e400057e-d8df-482d-9fa5-4d59571ff35b" code="Code" value="INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI" />
																	--<INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI version="1" codeSystemGuid="e400057e-d8df-482d-9fa5-4d59571ff35b" code="Code" value="INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI" />
																--</INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENI_BILGISI>
																	(SELECT 
																		(
																			SELECT
																				'1' AS '@version',
																				'e400057e-d8df-482d-9fa5-4d59571ff35b' AS '@codeSystemGuid',
																				ISNULL(INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_1_ID,'')  AS '@code',
																				ISNULL(INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_1_ADI,'') AS '@value'
																			FOR XML PATH('INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI'), TYPE
																		),

																		(
																			SELECT
																				'1' AS '@version',
																				'e400057e-d8df-482d-9fa5-4d59571ff35b' AS '@codeSystemGuid',
																				INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_2_ID  AS '@code',
																				INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_2_ADI AS '@value'
																				WHERE ISNULL(INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI_2_ID, '')<>''
																			FOR XML PATH('INTIHAR_GIRISIMI_YA_DA_KRIZ_NEDENLERI'), TYPE
																		)

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



																	(SELECT 
																		'1' AS 'INTIHAR_GIRISIMI_YONTEMI/@version',
																		'c3eaabad-8c4c-56ee-e043-14031b0a5530' AS 'INTIHAR_GIRISIMI_YONTEMI/@codeSystemGuid',
																			ISNULL(INTIHAR_GIRISIMI_YONTEMI_ID,'')  AS 'INTIHAR_GIRISIMI_YONTEMI/@code',
																			ISNULL(INTIHAR_GIRISIMI_YONTEMI_ADI,'') AS 'INTIHAR_GIRISIMI_YONTEMI/@value'

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



																	(SELECT 
																		(
																			SELECT
																				'1' AS '@version',
																				'7c9c42e2-ecd0-425b-a1bb-73fe23947338' AS '@codeSystemGuid',
																				ISNULL(INTIHAR_KRIZ_VAKA_SONUCU_1_ID,'')  AS '@code',
																				ISNULL(INTIHAR_KRIZ_VAKA_SONUCU_1_ADI,'') AS '@value'
																				
																			FOR XML PATH('INTIHAR_KRIZ_VAKA_SONUCU'), TYPE
																		),

																		(
																			SELECT
																				'1' AS '@version',
																				'7c9c42e2-ecd0-425b-a1bb-73fe23947338' AS '@codeSystemGuid',
																				INTIHAR_KRIZ_VAKA_SONUCU_2_ID  AS '@code',
																			    INTIHAR_KRIZ_VAKA_SONUCU_2_ADI AS '@value'
																				WHERE ISNULL(INTIHAR_KRIZ_VAKA_SONUCU_2_ID, '')<>''
																			FOR XML PATH('INTIHAR_KRIZ_VAKA_SONUCU'), TYPE
																		)

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

																	(SELECT 
																		'1' AS 'PSIKIYATRIK_TANI_GECMISI/@version',
																		'c3eaabad-8c4c-56ee-e043-14031b0a5530' AS 'PSIKIYATRIK_TANI_GECMISI/@codeSystemGuid',
																			ISNULL(PSIKIYATRIK_TANI_GECMISI_ID,'')  AS 'PSIKIYATRIK_TANI_GECMISI/@code',
																			ISNULL(PSIKIYATRIK_TANI_GECMISI_ADI,'') AS 'PSIKIYATRIK_TANI_GECMISI/@value'
																			FOR XML PATH('PSIKIYATRIK_TANI_GECMISI_BILGISI'),TYPE)
																	
 		
																FROM #TMPUSSDIGER
																
																FOR XML PATH(''),TYPE
															)
														FOR XML PATH('INTIHAR_GIRISIMI_VE_KRIZ_TESPIT'),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
