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

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

    --GOZLUK_RECETE_TIPI ,
	USS226.GOZLUK_RECETE_TIPI AS GOZLUK_RECETE_TIPI_ID,
    TGOZLUK_RECETE_TIPI.AD AS GOZLUK_RECETE_TIPI_ADI,

	RECETE_NUMARASI ,
	RECETE_TARIHI ,
	HEKIM_KIMLIK_NUMARASI ,

	--GOZLUK_TURU,
	USS226.GOZLUK_TURU AS GOZLUK_TURU_ID,
    TGOZLUK_TURU.AD AS GOZLUK_TURU_ADI,

	--SAG_CAM_BILGILERI_CAM_TIPI ,
	USS226.SAG_CAM_BILGILERI_CAM_TIPI AS SAG_CAM_BILGILERI_CAM_TIPI_ID,
    TSAG_CAM_BILGILERI_CAM_TIPI.AD AS SAG_CAM_BILGILERI_CAM_TIPI_ADI,

	--SAG_CAM_BILGILERI_CAM_RENGI ,
	USS226.SAG_CAM_BILGILERI_CAM_RENGI AS SAG_CAM_BILGILERI_CAM_RENGI_ID,
    TSAG_CAM_BILGILERI_CAM_RENGI.AD AS SAG_CAM_BILGILERI_CAM_RENGI_ADI,

	SAG_CAM_BILGILERI_CAM_SFERIK ,
    SAG_CAM_BILGILERI_CAM_SILINDIRIK , 
	SAG_CAM_BILGILERI_CAM_AKS ,


	--SOL_CAM_BILGILERI_CAM_TIPI ,
	USS226.SOL_CAM_BILGILERI_CAM_TIPI AS SOL_CAM_BILGILERI_CAM_TIPI_ID,
    TSOL_CAM_BILGILERI_CAM_TIPI.AD AS SOL_CAM_BILGILERI_CAM_TIPI_ADI,


	--SOL_CAM_BILGILERI_CAM_RENGI ,
	USS226.SOL_CAM_BILGILERI_CAM_RENGI AS SOL_CAM_BILGILERI_CAM_RENGI_ID,
    TSOL_CAM_BILGILERI_CAM_RENGI.AD AS SOL_CAM_BILGILERI_CAM_RENGI_ADI,

	SOL_CAM_BILGILERI_CAM_SFERIK ,
    SOL_CAM_BILGILERI_CAM_SILINDIRIK , 
	SOL_CAM_BILGILERI_CAM_AKS ,



    SAG_LENS_BILGILERI_CAM_SFERIK ,
    SAG_LENS_BILGILERI_CAM_SILINDIRIK ,
    SAG_LENS_BILGILERI_CAM_AKS ,
    SAG_LENS_BILGILERI_CAM_CAP ,
    SAG_LENS_BILGILERI_CAM_EGIM ,

    --SAG_LENS_BILGILERI_GECICI_MADDE ,
	USS226.SAG_LENS_BILGILERI_GECICI_MADDE AS SAG_LENS_BILGILERI_GECICI_MADDE_ID,
    TSAG_LENS_BILGILERI_GECICI_MADDE.AD AS SAG_LENS_BILGILERI_GECICI_MADDE_ADI,



    SOL_LENS_BILGILERI_CAM_SFERIK ,
    SOL_LENS_BILGILERI_CAM_SILINDIRIK ,
    SOL_LENS_BILGILERI_CAM_AKS ,
    SOL_LENS_BILGILERI_CAM_CAP ,
    SOL_LENS_BILGILERI_CAM_EGIM ,

	--SOL_LENS_BILGILERI_GECICI_MADDE ,
	USS226.SOL_LENS_BILGILERI_GECICI_MADDE AS SOL_LENS_BILGILERI_GECICI_MADDE_ID,
    TSOL_LENS_BILGILERI_GECICI_MADDE.AD AS SOL_LENS_BILGILERI_GECICI_MADDE_ADI,
    

    SAG_KERATAKONUS_BILGILERI_CAM_SFERIK ,
    SAG_KERATAKONUS_BILGILERI_CAM_SILINDIRIK ,
    SAG_KERATAKONUS_BILGILERI_CAM_AKS ,
    SAG_KERATAKONUS_BILGILERI_CAM_CAP ,
    SAG_KERATAKONUS_BILGILERI_CAM_EGIM ,

    SOL_KERATAKONUS_BILGILERI_CAM_SFERIK ,
    SOL_KERATAKONUS_BILGILERI_CAM_SILINDIRIK ,
    SOL_KERATAKONUS_BILGILERI_CAM_AKS ,
    SOL_KERATAKONUS_BILGILERI_CAM_CAP ,
    SOL_KERATAKONUS_BILGILERI_CAM_EGIM ,

	--TELESKOPIK_GOZLUK_TIPI ,
	USS226.TELESKOPIK_GOZLUK_TIPI AS TELESKOPIK_GOZLUK_TIPI_ID,
    TTELESKOPIK_GOZLUK_TIPI.AD AS TELESKOPIK_GOZLUK_TIPI_ADI,

	--TELESKOPIK_GOZLUK_TURU ,
	USS226.TELESKOPIK_GOZLUK_TURU AS TELESKOPIK_GOZLUK_TURU_ID,
    TTELESKOPIK_GOZLUK_TURU.AD AS TELESKOPIK_GOZLUK_TURU_ADI,

	--TELESKOPIK_GOZLUK_SAG_CAM ,
	USS226.TELESKOPIK_GOZLUK_SAG_CAM AS TELESKOPIK_GOZLUK_SAG_CAM_ID,
    TTELESKOPIK_GOZLUK_SAG_CAM.AD AS TELESKOPIK_GOZLUK_SAG_CAM_ADI,

	--TELESKOPIK_GOZLUK_SOL_CAM ,
	USS226.TELESKOPIK_GOZLUK_SOL_CAM AS TELESKOPIK_GOZLUK_SOL_CAM_ID,
    TTELESKOPIK_GOZLUK_SOL_CAM.AD AS TELESKOPIK_GOZLUK_SOL_CAM_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 USS226_GOZLUK_RECETE AS USS226
INNER JOIN HDOSYA ON HDOSYA.HDOSYAID = USS226.HDOSYAID
LEFT JOIN HKIMLIK ON HKIMLIK.HKIMLIKID=HDOSYA.HKIMLIKID
LEFT JOIN	SUBELER ON SUBELER.SUBEID=HDOSYA.SUBEID

LEFT JOIN 
(
SELECT 'NORMAL' AS AD, 1 AS ID
UNION ALL
SELECT 'TELESKOPİK' AS AD, 2 AS ID
UNION ALL
SELECT 'LENS' AS AD, 3 AS ID
UNION ALL
SELECT 'KERATAKONUSLENS' AS AD, 4 AS ID
) TGOZLUK_RECETE_TIPI ON TGOZLUK_RECETE_TIPI.ID=USS226.GOZLUK_RECETE_TIPI

LEFT JOIN 
(
SELECT 'UZAK' AS AD, 1 AS ID
UNION ALL
SELECT 'YAKIN' AS AD, 2 AS ID
) TGOZLUK_TURU ON TGOZLUK_TURU.ID=USS226.GOZLUK_TURU

LEFT JOIN
(
SELECT 'DÜZ' AS AD, 1 AS ID
UNION ALL
SELECT 'ORGANİK' AS AD, 2 AS ID
UNION ALL
SELECT 'BİFOCAL-PROGRESİF' AS AD, 3 AS ID
) TSAG_CAM_BILGILERI_CAM_TIPI ON TSAG_CAM_BILGILERI_CAM_TIPI.ID = USS226.SAG_CAM_BILGILERI_CAM_TIPI

LEFT JOIN
(
SELECT 'COLORMATİK' AS AD, 1 AS ID
UNION ALL
SELECT 'BEYAZ' AS AD, 2 AS ID
) TSAG_CAM_BILGILERI_CAM_RENGI ON TSAG_CAM_BILGILERI_CAM_RENGI.ID = USS226.SAG_CAM_BILGILERI_CAM_RENGI

LEFT JOIN
(
SELECT 'DÜZ' AS AD, 1 AS ID
UNION ALL
SELECT 'ORGANİK' AS AD, 2 AS ID
UNION ALL
SELECT 'BİFOCAL-PROGRESİF' AS AD, 3 AS ID
) TSOL_CAM_BILGILERI_CAM_TIPI ON TSOL_CAM_BILGILERI_CAM_TIPI.ID = USS226.SOL_CAM_BILGILERI_CAM_TIPI

LEFT JOIN
(
SELECT 'COLORMATİK' AS AD, 1 AS ID
UNION ALL
SELECT 'BEYAZ' AS AD, 2 AS ID
) TSOL_CAM_BILGILERI_CAM_RENGI ON TSOL_CAM_BILGILERI_CAM_RENGI.ID = USS226.SOL_CAM_BILGILERI_CAM_RENGI

LEFT JOIN 
(
SELECT 'EVET' AS AD, 1 AS ID
UNION ALL
SELECT 'HAYIR' AS AD, 2 AS ID
) TSAG_LENS_BILGILERI_GECICI_MADDE ON TSAG_LENS_BILGILERI_GECICI_MADDE.ID=USS226.SAG_LENS_BILGILERI_GECICI_MADDE

LEFT JOIN 
(
SELECT 'EVET' AS AD, 1 AS ID
UNION ALL
SELECT 'HAYIR' AS AD, 2 AS ID
) TSOL_LENS_BILGILERI_GECICI_MADDE ON TSOL_LENS_BILGILERI_GECICI_MADDE.ID=USS226.SOL_LENS_BILGILERI_GECICI_MADDE

LEFT JOIN 
(
SELECT 'TEK' AS AD, 1 AS ID
UNION ALL
SELECT 'ÇİFT' AS AD, 2 AS ID
UNION ALL
SELECT 'TEK KAREKOD' AS AD, 3 AS ID
) TTELESKOPIK_GOZLUK_TIPI ON TTELESKOPIK_GOZLUK_TIPI.ID=USS226.TELESKOPIK_GOZLUK_TIPI

LEFT JOIN 
(
SELECT 'UZAK-DAİMİ' AS AD, 1 AS ID
UNION ALL
SELECT 'YAKIN' AS AD, 2 AS ID
UNION ALL
SELECT 'YAKIN OKUMA KEPİ' AS AD, 3 AS ID
) TTELESKOPIK_GOZLUK_TURU ON TTELESKOPIK_GOZLUK_TURU.ID=USS226.TELESKOPIK_GOZLUK_TURU

LEFT JOIN 
(
SELECT 'EVET' AS AD, 1 AS ID
UNION ALL
SELECT 'HAYIR' AS AD, 2 AS ID
) TTELESKOPIK_GOZLUK_SAG_CAM ON TTELESKOPIK_GOZLUK_SAG_CAM.ID=USS226.TELESKOPIK_GOZLUK_SAG_CAM

LEFT JOIN 
(
SELECT 'EVET' AS AD, 1 AS ID
UNION ALL
SELECT 'HAYIR' AS AD, 2 AS ID
) TTELESKOPIK_GOZLUK_SOL_CAM ON TTELESKOPIK_GOZLUK_SOL_CAM.ID=USS226.TELESKOPIK_GOZLUK_SOL_CAM

WHERE USS226.HMUAYENEID= @HMUAYENEID AND USS226.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',
            '226' AS 'messageType/@code',
            'Gözlük Reçete Bilgileri' 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
            ),
            SNOKTSFIRMAKODU as 'firmaKodu/@value' ,
            (
                SELECT 
                    (
                        SELECT 
                            (
                                 -- START: GOZLUK_RECETE_BILGILERI (Orijinal Şablona Sadık Kalındı) --
                                 SELECT 
                                    (SELECT '1' AS '@version', 'ca1c2edc-7030-43cf-8fef-6ff86be2bcce' AS '@codeSystemGuid', GOZLUK_RECETE_TIPI_ID AS '@code', GOZLUK_RECETE_TIPI_ADI AS '@value' FOR XML PATH('GOZLUK_RECETE_TIPI'), TYPE),
                                      RECETE_NUMARASI AS 'RECETE_NUMARASI/@value',
									  LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),RECETE_TARIHI,120),' ',''),'-',''),':',''),12)	 AS 'RECETE_TARIHI/@value',
                                      HEKIM_KIMLIK_NUMARASI AS 'HEKIM_KIMLIK_NUMARASI/@value',
                                    (
                                        SELECT 
                                            (SELECT '1' AS '@version', '7132312f-4bbb-4087-866f-88823f69aa93' AS '@codeSystemGuid', GOZLUK_TURU_ID AS '@code', GOZLUK_TURU_ADI AS '@value' FOR XML PATH('GOZLUK_TURU'), TYPE),
                                            (
                                                SELECT 
                                                    (SELECT '1' AS '@version', '7f9adcbb-4ff2-47b8-b323-f48497dd3ae9' AS '@codeSystemGuid', SAG_CAM_BILGILERI_CAM_TIPI_ID AS '@code', SAG_CAM_BILGILERI_CAM_TIPI_ADI AS '@value' FOR XML PATH('CAM_TIPI'), TYPE),
                                                    (SELECT '1' AS '@version', '13391c91-140f-411a-a9da-b5a28ee00e32' AS '@codeSystemGuid', SAG_CAM_BILGILERI_CAM_RENGI_ID AS '@code', SAG_CAM_BILGILERI_CAM_RENGI_ADI AS '@value' FOR XML PATH('CAM_RENGI'), TYPE),
                                                    SAG_CAM_BILGILERI_CAM_SFERIK AS 'CAM_SFERIK/@value',
                                                    SAG_CAM_BILGILERI_CAM_SILINDIRIK AS 'CAM_SILINDIRIK/@value',
                                                    SAG_CAM_BILGILERI_CAM_AKS AS 'CAM_AKS/@value'
                                                FOR XML PATH('SAG_CAM_BILGILERI'), TYPE
                                            ),
                                            (
                                                SELECT 
                                                    (SELECT '1' AS '@version', '7f9adcbb-4ff2-47b8-b323-f48497dd3ae9' AS '@codeSystemGuid', SOL_CAM_BILGILERI_CAM_TIPI_ID AS '@code', SOL_CAM_BILGILERI_CAM_TIPI_ADI AS '@value' FOR XML PATH('CAM_TIPI'), TYPE),
                                                    (SELECT '1' AS '@version', '13391c91-140f-411a-a9da-b5a28ee00e32' AS '@codeSystemGuid', SOL_CAM_BILGILERI_CAM_RENGI_ID AS '@code', SOL_CAM_BILGILERI_CAM_RENGI_ADI AS '@value' FOR XML PATH('CAM_RENGI'), TYPE),
                                                    SOL_CAM_BILGILERI_CAM_SFERIK AS 'CAM_SFERIK/@value',
                                                    SOL_CAM_BILGILERI_CAM_SILINDIRIK AS 'CAM_SILINDIRIK/@value',
                                                    SOL_CAM_BILGILERI_CAM_AKS AS 'CAM_AKS/@value'
                                                FOR XML PATH('SOL_CAM_BILGILERI'), TYPE
                                            ),
                                            (
                                                SELECT 
                                                    SAG_LENS_BILGILERI_CAM_SFERIK AS 'CAM_SFERIK/@value',
                                                    SAG_LENS_BILGILERI_CAM_SILINDIRIK AS 'CAM_SILINDIRIK/@value',
                                                    SAG_LENS_BILGILERI_CAM_AKS AS 'CAM_AKS/@value',
                                                    SAG_LENS_BILGILERI_CAM_CAP AS 'CAM_CAP/@value',
                                                    SAG_LENS_BILGILERI_CAM_EGIM AS 'CAM_EGIM/@value',
                                                    (SELECT '1' AS '@version', '3e25498d-74e7-466b-8f4d-cb93934ab9ad' AS '@codeSystemGuid', SAG_LENS_BILGILERI_GECICI_MADDE_ID AS '@code', SAG_LENS_BILGILERI_GECICI_MADDE_ADI AS '@value' FOR XML PATH('GECICI_MADDE'), TYPE)
                                                FOR XML PATH('SAG_LENS_BILGILERI'), TYPE
                                            ),
                                            (
                                                SELECT 
                                                    SOL_LENS_BILGILERI_CAM_SFERIK AS 'CAM_SFERIK/@value',
                                                    SOL_LENS_BILGILERI_CAM_SILINDIRIK AS 'CAM_SILINDIRIK/@value',
                                                    SOL_LENS_BILGILERI_CAM_AKS AS 'CAM_AKS/@value',
                                                    SOL_LENS_BILGILERI_CAM_CAP AS 'CAM_CAP/@value',
                                                    SOL_LENS_BILGILERI_CAM_EGIM AS 'CAM_EGIM/@value',
                                                    (SELECT '1' AS '@version', '3e25498d-74e7-466b-8f4d-cb93934ab9ad' AS '@codeSystemGuid', SOL_LENS_BILGILERI_GECICI_MADDE_ID AS '@code', SOL_LENS_BILGILERI_GECICI_MADDE_ADI AS '@value' FOR XML PATH('GECICI_MADDE'), TYPE)
                                                FOR XML PATH('SOL_LENS_BILGILERI'), TYPE
                                            ),
                                            (
                                                SELECT 
                                                    SAG_KERATAKONUS_BILGILERI_CAM_SFERIK AS 'CAM_SFERIK/@value',
                                                    SAG_KERATAKONUS_BILGILERI_CAM_SILINDIRIK AS 'CAM_SILINDIRIK/@value',
                                                    SAG_KERATAKONUS_BILGILERI_CAM_AKS AS 'CAM_AKS/@value',
                                                    SAG_KERATAKONUS_BILGILERI_CAM_CAP AS 'CAM_CAP/@value',
                                                    SAG_KERATAKONUS_BILGILERI_CAM_EGIM AS 'CAM_EGIM/@value'
                                                FOR XML PATH('SAG_KERATAKONUS_BILGILERI'), TYPE
                                            ),
                                            (
                                                SELECT 
                                                    SOL_KERATAKONUS_BILGILERI_CAM_SFERIK AS 'CAM_SFERIK/@value',
                                                    SOL_KERATAKONUS_BILGILERI_CAM_SILINDIRIK AS 'CAM_SILINDIRIK/@value',
                                                    SOL_KERATAKONUS_BILGILERI_CAM_AKS AS 'CAM_AKS/@value',
                                                    SOL_KERATAKONUS_BILGILERI_CAM_CAP AS 'CAM_CAP/@value',
                                                    SOL_KERATAKONUS_BILGILERI_CAM_EGIM AS 'CAM_EGIM/@value'
                                                FOR XML PATH('SOL_KERATAKONUS_BILGILERI'), TYPE
                                            ),
                                            (
                                                SELECT 
                                                    (SELECT '1' AS '@version', 'd18f100b-454e-40a1-919c-ce4c80d8d228' AS '@codeSystemGuid', TELESKOPIK_GOZLUK_TIPI_ID AS '@code', TELESKOPIK_GOZLUK_TIPI_ADI AS '@value' FOR XML PATH('TELESKOPIK_GOZLUK_TIPI'), TYPE),
                                                    (SELECT '1' AS '@version', '63cdd8a2-3b01-4c91-8511-ac26aac649f4' AS '@codeSystemGuid', TELESKOPIK_GOZLUK_TURU_ID AS '@code', TELESKOPIK_GOZLUK_TURU_ADI AS '@value' FOR XML PATH('TELESKOPIK_GOZLUK_TURU'), TYPE),
                                                    (SELECT '1' AS '@version', '3e25498d-74e7-466b-8f4d-cb93934ab9ad' AS '@codeSystemGuid', TELESKOPIK_GOZLUK_SAG_CAM_ID AS '@code', TELESKOPIK_GOZLUK_SAG_CAM_ADI AS '@value' FOR XML PATH('SAG_CAM'), TYPE),
                                                    (SELECT '1' AS '@version', '3e25498d-74e7-466b-8f4d-cb93934ab9ad' AS '@codeSystemGuid', TELESKOPIK_GOZLUK_SOL_CAM_ID AS '@code', TELESKOPIK_GOZLUK_SOL_CAM_ADI AS '@value' FOR XML PATH('SOL_CAM'), TYPE)
                                                FOR XML PATH('TELESKOPIK_GOZLUK_BILGILERI'), TYPE
                                            )
                                        FOR XML PATH('GOZLUK_BILGILERI'), TYPE
                                    )
                                 FOR XML PATH('GOZLUK_RECETE_BILGILERI'), TYPE
                                 -- END: GOZLUK_RECETE_BILGILERI --
                            )
                        FROM #TMPUSSDIGER T2 
                        WHERE T2.USSDIGER_ID = #TMPUSSDIGER.USSDIGER_ID
                        FOR XML PATH('GOZLUK_RECETE_BILGILERI'), 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
