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

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

---*************************************

(SELECT TOP 1 DRTCKIMLIKNO FROM VWMUAYENE WHERE VWMUAYENE.HMUAYENEID=USS274.HMUAYENEID) FORMU_DOLDURAN_HEKIM_KIMLIK_NUMARASI,
--USS274.ID
(SELECT TOP 1 HESAPISLID FROM VWHESAPISLDRM WHERE VWHESAPISLDRM.IPTAL=0 AND VWHESAPISLDRM.SNOgoNDERILDI=1 AND  VWHESAPISLDRM.HDOSYAID=USS274.HDOSYAID AND  VWHESAPISLDRM.ITEMKODU2 LIKE '%700944' ) ISLEM_REFERANS_NUMARASI,
STS_SKORU,
AORT_KAPAGI_PEAK_GRADIYENT,
AORT_KAPAGI_ORTALAMA_GRADIYENT,
AORT_KAPAK_ALANI,
SISTOLIK_PULMONER_ARTER_BASINCI,
SOL_VENTRIKUL_EJEKSIYON_FRAKSIYONU,
AORTIK_ANULER_OLCUM_YONTEMI_DEGERI,


USS274.SIGARA_ICIYOR_MU AS SIGARA_ICIYOR_MU_ID,
TSIGARA_ICIYOR_MU.AD AS SIGARA_ICIYOR_MU_ADI,

USS274.FIZIKSEL_AKTIVITE_DUZEYI_AZALMIS_MI AS FIZIKSEL_AKTIVITE_DUZEYI_AZALMIS_MI_ID,
TFIZIKSEL_AKTIVITE_DUZEYI_AZALMIS_MI.AD AS FIZIKSEL_AKTIVITE_DUZEYI_AZALMIS_MI_ADI,

USS274.NOROLOJIK_HASTALIK_OYKUSU AS NOROLOJIK_HASTALIK_OYKUSU_ID,
TNOROLOJIK_HASTALIK_OYKUSU.AD AS NOROLOJIK_HASTALIK_OYKUSU_ADI,

USS274.MALIGNENSI_OYKUSU AS MALIGNENSI_OYKUSU_ID,
TMALIGNENSI_OYKUSU.AD AS MALIGNENSI_OYKUSU_ADI,

USS274.GECIRILMIS_KALP_AMELIYATI AS GECIRILMIS_KALP_AMELIYATI_ID,
TGECIRILMIS_KALP_AMELIYATI.AD AS GECIRILMIS_KALP_AMELIYATI_ADI,

USS274.ONCEKI_PERKUTAN_AORTIK_KAPAK_GIRISIMI AS ONCEKI_PERKUTAN_AORTIK_KAPAK_GIRISIMI_ID,
TONCEKI_PERKUTAN_AORTIK_KAPAK_GIRISIMI.AD AS ONCEKI_PERKUTAN_AORTIK_KAPAK_GIRISIMI_ADI,

USS274.GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_VAR_MI AS GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_VAR_MI_ID,
TGIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_VAR_MI.AD AS GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_VAR_MI_ADI,

USS274.ONCEKI_PERKUTAN_KORONER_ISLEM AS ONCEKI_PERKUTAN_KORONER_ISLEM_ID,
TONCEKI_PERKUTAN_KORONER_ISLEM.AD AS ONCEKI_PERKUTAN_KORONER_ISLEM_ADI,

USS274.TAVI_VUCUT_KITLE_INDEXI AS TAVI_VUCUT_KITLE_INDEXI_ID,
TTAVI_VUCUT_KITLE_INDEXI.AD AS TAVI_VUCUT_KITLE_INDEXI_ADI,

USS274.RITIM_BOZUKLUGU_ISLEM_OYKUSU AS RITIM_BOZUKLUGU_ISLEM_OYKUSU_ID,
TRITIM_BOZUKLUGU_ISLEM_OYKUSU.AD AS RITIM_BOZUKLUGU_ISLEM_OYKUSU_ADI,

USS274.NEW_YORK_HEART_ASSOCIATION_DISPNE_DURUMU AS NEW_YORK_HEART_ASSOCIATION_DISPNE_DURUMU_ID,
TNEW_YORK_HEART_ASSOCIATION_DISPNE_DURUMU.AD AS NEW_YORK_HEART_ASSOCIATION_DISPNE_DURUMU_ADI,

USS274.TAVI_FIZIKSEL_AKTIVITE AS TAVI_FIZIKSEL_AKTIVITE_ID,
TTAVI_FIZIKSEL_AKTIVITE.AD AS TAVI_FIZIKSEL_AKTIVITE_ADI,

USS274.ISLEM_ONCESI_KALP_RITMI AS ISLEM_ONCESI_KALP_RITMI_ID,
TISLEM_ONCESI_KALP_RITMI.AD AS ISLEM_ONCESI_KALP_RITMI_ADI,

USS274.GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM AS GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_ID,
TGIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM.AD AS GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_ADI,

USS274.KORONER_ARTER_HASTALIK_BILGISI AS KORONER_ARTER_HASTALIK_BILGISI_ID,
TKORONER_ARTER_HASTALIK_BILGISI.AD AS KORONER_ARTER_HASTALIK_BILGISI_ADI,

USS274.SOL_ANA_KORONER_DAMAR_HASTALIGI_BILGISI AS SOL_ANA_KORONER_DAMAR_HASTALIGI_BILGISI_ID,
TSOL_ANA_KORONER_DAMAR_HASTALIGI_BILGISI.AD AS SOL_ANA_KORONER_DAMAR_HASTALIGI_BILGISI_ADI,

USS274.KORONER_ARTER_HASTALIGI_VAR_MI AS KORONER_ARTER_HASTALIGI_VAR_MI_ID,
TKORONER_ARTER_HASTALIGI_VAR_MI.AD AS KORONER_ARTER_HASTALIGI_VAR_MI_ADI,

USS274.AORT_DARLIGININ_TIPI AS AORT_DARLIGININ_TIPI_ID,
TAORT_DARLIGININ_TIPI.AD AS AORT_DARLIGININ_TIPI_ADI,

USS274.AORT_KAPAGININ_YAPISI AS AORT_KAPAGININ_YAPISI_ID,
TAORT_KAPAGININ_YAPISI.AD AS AORT_KAPAGININ_YAPISI_ADI,

USS274.SOL_ANA_KORONER_DAMAR_HASTALIGI_VAR_MI AS SOL_ANA_KORONER_DAMAR_HASTALIGI_VAR_MI_ID,
TSOL_ANA_KORONER_DAMAR_HASTALIGI_VAR_MI.AD AS SOL_ANA_KORONER_DAMAR_HASTALIGI_VAR_MI_ADI,

USS274.TRIKUSPIT_KAPAK_PATOLOJISI AS TRIKUSPIT_KAPAK_PATOLOJISI_ID,
TTRIKUSPIT_KAPAK_PATOLOJISI.AD AS TRIKUSPIT_KAPAK_PATOLOJISI_ADI,

USS274.AORTIK_ANNULER_OLCUM_YONTEMI_BILGISI AS AORTIK_ANNULER_OLCUM_YONTEMI_BILGISI_ID,
TAORTIK_ANNULER_OLCUM_YONTEMI_BILGISI.AD AS AORTIK_ANNULER_OLCUM_YONTEMI_BILGISI_ADI,

USS274.AORT_KAPAK_PATOLOJISI_BILGISI AS AORT_KAPAK_PATOLOJISI_BILGISI_ID,
TAORT_KAPAK_PATOLOJISI_BILGISI.AD AS AORT_KAPAK_PATOLOJISI_BILGISI_ADI,

USS274.AORT_KAPAK_PATOLOJISININ_ETIYOLOJISI_BILGISI AS AORT_KAPAK_PATOLOJISININ_ETIYOLOJISI_BILGISI_ID,
TAORT_KAPAK_PATOLOJISININ_ETIYOLOJISI_BILGISI.AD AS AORT_KAPAK_PATOLOJISININ_ETIYOLOJISI_BILGISI_ADI,

USS274.MITRAL_KAPAK_PATOLOJISI AS MITRAL_KAPAK_PATOLOJISI_ID,
TMITRAL_KAPAK_PATOLOJISI.AD AS MITRAL_KAPAK_PATOLOJISI_ADI,

USS274.MITRAL_KAPAK_PATOLOJI_DERECESI AS MITRAL_KAPAK_PATOLOJI_DERECESI_ID,
TMITRAL_KAPAK_PATOLOJI_DERECESI.AD AS MITRAL_KAPAK_PATOLOJI_DERECESI_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 USS274_TAVI_KAYIT AS USS274
INNER JOIN HDOSYA ON HDOSYA.HDOSYAID = USS274.HDOSYAID
LEFT JOIN HKIMLIK ON HKIMLIK.HKIMLIKID=HDOSYA.HKIMLIKID
LEFT JOIN	SUBELER ON SUBELER.SUBEID=HDOSYA.SUBEID
LEFT JOIN 
(
/**<SIGARA_ICIYOR_MU version="1" codeSystemGuid="c9d56fee-d143-4602-ad7b-ba131ef92ad9" code="Code" value="SIGARA_ICIYOR_MU" />*/
SELECT 'EVET' AS AD, 1 AS ID
UNION ALL
SELECT 'HAYIR' AS AD, 2 AS ID

) TSIGARA_ICIYOR_MU ON TSIGARA_ICIYOR_MU.ID = USS274.SIGARA_ICIYOR_MU


LEFT JOIN 
(
/**<FIZIKSEL_AKTIVITE_DUZEYI_AZALMIS_MI version="1" codeSystemGuid="c9d56fee-d143-4602-ad7b-ba131ef92ad9" code="Code" value="FIZIKSEL_AKTIVITE_DUZEYI_AZALMIS_MI" />*/

SELECT 'EVET' AS AD, 1 AS ID
UNION ALL
SELECT 'HAYIR' AS AD, 2 AS ID
) TFIZIKSEL_AKTIVITE_DUZEYI_AZALMIS_MI ON TFIZIKSEL_AKTIVITE_DUZEYI_AZALMIS_MI.ID = USS274.FIZIKSEL_AKTIVITE_DUZEYI_AZALMIS_MI


LEFT JOIN 
(
/**<NOROLOJIK_HASTALIK_OYKUSU version="1" codeSystemGuid="c9d56fee-d143-4602-ad7b-ba131ef92ad9" code="Code" value="NOROLOJIK_HASTALIK_OYKUSU" />*/
SELECT 'EVET' AS AD, 1 AS ID
UNION ALL
SELECT 'HAYIR' AS AD, 2 AS ID
) TNOROLOJIK_HASTALIK_OYKUSU ON TNOROLOJIK_HASTALIK_OYKUSU.ID = USS274.NOROLOJIK_HASTALIK_OYKUSU


LEFT JOIN 
(
/**<MALIGNENSI_OYKUSU version="1" codeSystemGuid="c9d56fee-d143-4602-ad7b-ba131ef92ad9" code="Code" value="MALIGNENSI_OYKUSU" />*/
SELECT 'EVET' AS AD, 1 AS ID
UNION ALL
SELECT 'HAYIR' AS AD, 2 AS ID
) TMALIGNENSI_OYKUSU ON TMALIGNENSI_OYKUSU.ID = USS274.MALIGNENSI_OYKUSU


LEFT JOIN 
(
/**<GECIRILMIS_KALP_AMELIYATI version="1" codeSystemGuid="c9d56fee-d143-4602-ad7b-ba131ef92ad9" code="Code" value="GECIRILMIS_KALP_AMELIYATI" />*/
SELECT 'EVET' AS AD, 1 AS ID
UNION ALL
SELECT 'HAYIR' AS AD, 2 AS ID
) TGECIRILMIS_KALP_AMELIYATI ON TGECIRILMIS_KALP_AMELIYATI.ID = USS274.GECIRILMIS_KALP_AMELIYATI


LEFT JOIN 
(
/**<ONCEKI_PERKUTAN_AORTIK_KAPAK_GIRISIMI version="1" codeSystemGuid="c9d56fee-d143-4602-ad7b-ba131ef92ad9" code="Code" value="ONCEKI_PERKUTAN_AORTIK_KAPAK_GIRISIMI" />*/
SELECT 'EVET' AS AD, 1 AS ID
UNION ALL
SELECT 'HAYIR' AS AD, 2 AS ID
) TONCEKI_PERKUTAN_AORTIK_KAPAK_GIRISIMI ON TONCEKI_PERKUTAN_AORTIK_KAPAK_GIRISIMI.ID = USS274.ONCEKI_PERKUTAN_AORTIK_KAPAK_GIRISIMI


LEFT JOIN 
(
/**<GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_VAR_MI version="1" codeSystemGuid="c9d56fee-d143-4602-ad7b-ba131ef92ad9" code="Code" value="GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_VAR_MI" />*/
SELECT 'EVET' AS AD, 1 AS ID
UNION ALL
SELECT 'HAYIR' AS AD, 2 AS ID
) TGIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_VAR_MI ON TGIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_VAR_MI.ID = USS274.GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_VAR_MI


LEFT JOIN 
(
/**<ONCEKI_PERKUTAN_KORONER_ISLEM version="1" codeSystemGuid="c9d56fee-d143-4602-ad7b-ba131ef92ad9" code="Code" value="ONCEKI_PERKUTAN_KORONER_ISLEM" />*/
SELECT 'EVET' AS AD, 1 AS ID
UNION ALL
SELECT 'HAYIR' AS AD, 2 AS ID
) TONCEKI_PERKUTAN_KORONER_ISLEM ON TONCEKI_PERKUTAN_KORONER_ISLEM.ID = USS274.ONCEKI_PERKUTAN_KORONER_ISLEM


LEFT JOIN 
(
/**<TAVI_VUCUT_KITLE_INDEXI version="1" codeSystemGuid="e1108e6f-fbaa-49cf-bf90-d09b5c1ee001" code="Code" value="TAVI_VUCUT_KITLE_INDEXI" />*/
SELECT '>18.50 (kg/m2)' AS AD, 1 AS ID
UNION ALL
SELECT '18,50-24,99 (kg/m2)' AS AD, 2 AS ID
UNION ALL
SELECT '25,00-29,99 (kg/m2)' AS AD, 3 AS ID
UNION ALL
SELECT '30,00-34,99 (kg/m2)' AS AD, 4 AS ID
UNION ALL
SELECT '>=35,00 (kg/m2)' AS AD, 5 AS ID

) TTAVI_VUCUT_KITLE_INDEXI ON TTAVI_VUCUT_KITLE_INDEXI.ID = USS274.TAVI_VUCUT_KITLE_INDEXI


LEFT JOIN 
(
/**<RITIM_BOZUKLUGU_ISLEM_OYKUSU version="1" codeSystemGuid="c5e469f2-9146-4438-9890-5d7169822ff2" code="Code" value="RITIM_BOZUKLUGU_ISLEM_OYKUSU" />*/
SELECT 'KALICI KALP PİLİ UYGULAMASI' AS AD, 1 AS ID
UNION ALL
SELECT 'İMPLANTE EDİLEBİLİR KARDİYAK DEFİBRİLATÖR (ICD) UYGULAMASI' AS AD, 2 AS ID
UNION ALL
SELECT 'KARDİYAK RESENKRONİZASYON TERAPİSİ (CRT-D)' AS AD, 3 AS ID
UNION ALL
SELECT 'HİÇBİRİ' AS AD, 4 AS ID

) TRITIM_BOZUKLUGU_ISLEM_OYKUSU ON TRITIM_BOZUKLUGU_ISLEM_OYKUSU.ID = USS274.RITIM_BOZUKLUGU_ISLEM_OYKUSU


LEFT JOIN 
(
/**<NEW_YORK_HEART_ASSOCIATION_DISPNE_DURUMU version="1" codeSystemGuid="761be295-bafa-4660-8d7a-5b20f0b945f1" code="Code" value="NEW_YORK_HEART_ASSOCIATION_DISPNE_DURUMU" />*/
SELECT 'SINIF I' AS AD, 1 AS ID
UNION ALL
SELECT 'SINIF II' AS AD, 2 AS ID
UNION ALL
SELECT 'SINIF III' AS AD, 3 AS ID
UNION ALL
SELECT 'SINIF IV' AS AD, 4 AS ID

) TNEW_YORK_HEART_ASSOCIATION_DISPNE_DURUMU ON TNEW_YORK_HEART_ASSOCIATION_DISPNE_DURUMU.ID = USS274.NEW_YORK_HEART_ASSOCIATION_DISPNE_DURUMU


LEFT JOIN 
(
/**<TAVI_FIZIKSEL_AKTIVITE version="1" codeSystemGuid="138f919b-80b0-44eb-93ec-a8d342aa5776" code="Code" value="TAVI_FIZIKSEL_AKTIVITE" />*/
SELECT 'YATAK YA DA SANDALYE BAĞIMLI' AS AD, 1 AS ID
UNION ALL
SELECT 'YATAKTAN YA DA SANDALYEDEN ÇIKABİLİYOR AMA EVDEN DIŞARI ÇIKAMIYOR' AS AD, 2 AS ID
UNION ALL
SELECT 'EVDEN DIŞARI ÇIKABİLİYOR' AS AD, 3 AS ID

) TTAVI_FIZIKSEL_AKTIVITE ON TTAVI_FIZIKSEL_AKTIVITE.ID = USS274.TAVI_FIZIKSEL_AKTIVITE


LEFT JOIN 
(
/**<ISLEM_ONCESI_KALP_RITMI version="1" codeSystemGuid="2c1651f1-67c6-49ea-9db6-f09b5a5d9ffa" code="Code" value="ISLEM_ONCESI_KALP_RITMI" />*/
SELECT 'SİNÜS RİTMİ' AS AD, 1 AS ID
UNION ALL
SELECT 'ATRİYAL FİBRİLASYON / FLUTTER' AS AD, 2 AS ID
UNION ALL
SELECT 'BİRİNCİ DERECE KALP BLOĞU' AS AD, 3 AS ID
UNION ALL
SELECT 'SAĞ DAL BLOĞU (RBBB)' AS AD, 4 AS ID
UNION ALL
SELECT 'SOL DAL BLOĞU (LBBB)' AS AD, 5 AS ID
UNION ALL
SELECT 'İKİNCİ DERECE KALP BLOĞU' AS AD, 6 AS ID
UNION ALL
SELECT 'TAM KALP BLOĞU' AS AD, 7 AS ID
UNION ALL
SELECT 'PİL RİTMİ' AS AD, 8 AS ID
UNION ALL
SELECT 'VENTRİKÜLER TAŞİKARDİ' AS AD, 9 AS ID

) TISLEM_ONCESI_KALP_RITMI ON TISLEM_ONCESI_KALP_RITMI.ID = USS274.ISLEM_ONCESI_KALP_RITMI


LEFT JOIN 
(
/**<GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM version="1" codeSystemGuid="1c50c4e8-1f90-4fb6-bfda-a4c7bab1848f" code="Code" value="GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM" />*/
SELECT 'OPTİMAL MEDİKAL TEDAVİYE RAĞMEN ANSTABİL ANJİNA OLMASI' AS AD, 1 AS ID
UNION ALL
SELECT 'VENTRİKÜLER TAŞİKARDİ VEYA VENTRİKÜLER FİBRİLASYON OLMASI' AS AD, 2 AS ID
UNION ALL
SELECT 'YENİ OLUŞMUŞ AKUT RENAL YETMEZLİK' AS AD, 3 AS ID
UNION ALL
SELECT 'VENTİLATÖRE BAĞLI OLMASI' AS AD, 4 AS ID
UNION ALL
SELECT 'İŞLEM ÖNCESİ KARDİYOJENİK ŞOK OLMASI' AS AD, 5 AS ID
UNION ALL
SELECT 'İNTRA AORTİK BALON POMPASI OLMASI' AS AD, 6 AS ID
UNION ALL
SELECT 'ANESTEZİ ÖNCESİ POZİTİF İNOTROP ALMASI' AS AD, 7 AS ID

) TGIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM ON TGIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM.ID = USS274.GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM


LEFT JOIN 
(
/**<KORONER_ARTER_HASTALIK_BILGISI version="1" codeSystemGuid="ff7f9c06-bfec-48f1-aa29-f9b1ced4b1b9" code="Code" value="KORONER_ARTER_HASTALIK_BILGISI" />*/
SELECT 'İLAÇ TEDAVİSİ İLE KONTROL ALTINDA' AS AD, 1 AS ID
UNION ALL
SELECT 'REVASKÜLARİZASYON GEREKTİREN' AS AD, 2 AS ID


) TKORONER_ARTER_HASTALIK_BILGISI ON TKORONER_ARTER_HASTALIK_BILGISI.ID = USS274.KORONER_ARTER_HASTALIK_BILGISI


LEFT JOIN 
(
/**<SOL_ANA_KORONER_DAMAR_HASTALIGI_BILGISI version="1" codeSystemGuid="1ec4f05c-6c9f-4718-9d5e-f4673671b72e" code="Code" value="SOL_ANA_KORONER_DAMAR_HASTALIGI_BILGISI" />*/
SELECT 'DARLIK <= %50' AS AD, 1 AS ID
UNION ALL
SELECT 'DARLIK > %50' AS AD, 2 AS ID

) TSOL_ANA_KORONER_DAMAR_HASTALIGI_BILGISI ON TSOL_ANA_KORONER_DAMAR_HASTALIGI_BILGISI.ID = USS274.SOL_ANA_KORONER_DAMAR_HASTALIGI_BILGISI


LEFT JOIN 
(
/**<KORONER_ARTER_HASTALIGI_VAR_MI version="1" codeSystemGuid="c9d56fee-d143-4602-ad7b-ba131ef92ad9" code="Code" value="KORONER_ARTER_HASTALIGI_VAR_MI" />*/
SELECT 'EVET' AS AD, 1 AS ID
UNION ALL
SELECT 'HAYIR' AS AD, 2 AS ID
) TKORONER_ARTER_HASTALIGI_VAR_MI ON TKORONER_ARTER_HASTALIGI_VAR_MI.ID = USS274.KORONER_ARTER_HASTALIGI_VAR_MI


LEFT JOIN 
(
/**<AORT_DARLIGININ_TIPI version="1" codeSystemGuid="cc4afbe7-8c0a-47a8-afc5-030ded109b6c" code="Code" value="AORT_DARLIGININ_TIPI" />*/
SELECT 'YÜKSEK GRADİYENTLİ' AS AD, 1 AS ID
UNION ALL
SELECT 'DÜŞÜK GRADİYENTLİ, DÜŞÜK AKIMLI, DÜŞÜK EJEKSİYON FRAKSİYONLU' AS AD, 2 AS ID
UNION ALL
SELECT 'DÜŞÜK GRADİYENTLİ, DÜŞÜK AKIMLI, YÜKSEK EJEKSİYON FRAKSİYONLU' AS AD, 3 AS ID

) TAORT_DARLIGININ_TIPI ON TAORT_DARLIGININ_TIPI.ID = USS274.AORT_DARLIGININ_TIPI


LEFT JOIN 
(
/**<AORT_KAPAGININ_YAPISI version="1" codeSystemGuid="a66a70a8-3f12-4e98-a897-6accc3eb23c9" code="Code" value="AORT_KAPAGININ_YAPISI" />*/
SELECT 'UNİKÜSPİT KAPAK' AS AD, 1 AS ID
UNION ALL
SELECT 'BİKÜSPİT KAPAK' AS AD, 2 AS ID
UNION ALL
SELECT 'TRİKÜSPİT KAPAK' AS AD, 3 AS ID

) TAORT_KAPAGININ_YAPISI ON TAORT_KAPAGININ_YAPISI.ID = USS274.AORT_KAPAGININ_YAPISI


LEFT JOIN 
(
/**<SOL_ANA_KORONER_DAMAR_HASTALIGI_VAR_MI version="1" codeSystemGuid="c9d56fee-d143-4602-ad7b-ba131ef92ad9" code="Code" value="SOL_ANA_KORONER_DAMAR_HASTALIGI_VAR_MI" />*/
SELECT 'EVET' AS AD, 1 AS ID
UNION ALL
SELECT 'HAYIR' AS AD, 2 AS ID
) TSOL_ANA_KORONER_DAMAR_HASTALIGI_VAR_MI ON TSOL_ANA_KORONER_DAMAR_HASTALIGI_VAR_MI.ID = USS274.SOL_ANA_KORONER_DAMAR_HASTALIGI_VAR_MI


LEFT JOIN 
(
/**<TRIKUSPIT_KAPAK_PATOLOJISI version="1" codeSystemGuid="e396ee6f-d024-4c95-9955-f26a3156525f" code="Code" value="TRIKUSPIT_KAPAK_PATOLOJISI" />*/
SELECT 'YOK' AS AD, 1 AS ID
UNION ALL
SELECT 'HAFİF' AS AD, 2 AS ID
UNION ALL
SELECT 'ORTA' AS AD, 3 AS ID
UNION ALL
SELECT 'ŞİDDETLİ' AS AD, 4 AS ID

) TTRIKUSPIT_KAPAK_PATOLOJISI ON TTRIKUSPIT_KAPAK_PATOLOJISI.ID = USS274.TRIKUSPIT_KAPAK_PATOLOJISI


LEFT JOIN 
(
/**<AORTIK_ANNULER_OLCUM_YONTEMI_BILGISI version="1" codeSystemGuid="25d4ac0f-9475-4f33-8301-f1ed556ce035" code="Code" value="AORTIK_ANNULER_OLCUM_YONTEMI_BILGISI" />*/
SELECT 'TRANSTORASTİK EKOKARDİYOGRAFİ (TTE) İLE' AS AD, 1 AS ID
UNION ALL
SELECT 'TRANSÖZEFAJİYAL EKOKARDİYOGRAFİ (TEE) İLE' AS AD, 2 AS ID
UNION ALL
SELECT '3 BOYUTLU EKOKARDİYOGRAFİ İLE' AS AD, 3 AS ID
UNION ALL
SELECT 'BİLGİSAYARLI TOMOGRAFİ İLE' AS AD, 4 AS ID
UNION ALL
SELECT 'MANYETİK REZONANS GÖRÜNTÜLEME İLE' AS AD, 5 AS ID

) TAORTIK_ANNULER_OLCUM_YONTEMI_BILGISI ON TAORTIK_ANNULER_OLCUM_YONTEMI_BILGISI.ID = USS274.AORTIK_ANNULER_OLCUM_YONTEMI_BILGISI


LEFT JOIN 
(
/**<AORT_KAPAK_PATOLOJISI_BILGISI version="1" codeSystemGuid="41e3d27a-ee4f-477b-abc3-17e235387867" code="Code" value="AORT_KAPAK_PATOLOJISI_BILGISI" />*/
SELECT 'DARLIK' AS AD, 1 AS ID
UNION ALL
SELECT 'YETERSİZLİK' AS AD, 2 AS ID

) TAORT_KAPAK_PATOLOJISI_BILGISI ON TAORT_KAPAK_PATOLOJISI_BILGISI.ID = USS274.AORT_KAPAK_PATOLOJISI_BILGISI


LEFT JOIN 
(
/**<AORT_KAPAK_PATOLOJISININ_ETIYOLOJISI_BILGISI version="1" codeSystemGuid="f624a701-736e-47ad-885f-667e106adc45" code="Code" value="AORT_KAPAK_PATOLOJISININ_ETIYOLOJISI_BILGISI" />*/
SELECT 'DEJENERATİF' AS AD, 1 AS ID
UNION ALL
SELECT 'ROMATİZMAL' AS AD, 2 AS ID
UNION ALL
SELECT 'BİYOPROTEZ, AVR' AS AD, 3 AS ID
UNION ALL
SELECT 'TAVİ' AS AD, 4 AS ID


) TAORT_KAPAK_PATOLOJISININ_ETIYOLOJISI_BILGISI ON TAORT_KAPAK_PATOLOJISININ_ETIYOLOJISI_BILGISI.ID = USS274.AORT_KAPAK_PATOLOJISININ_ETIYOLOJISI_BILGISI


LEFT JOIN 
(
/**<MITRAL_KAPAK_PATOLOJISI version="1" codeSystemGuid="41e3d27a-ee4f-477b-abc3-17e235387867" code="Code" value="MITRAL_KAPAK_PATOLOJISI" />*/
SELECT 'DARLIK' AS AD, 1 AS ID
UNION ALL
SELECT 'YETERSİZLİK' AS AD, 2 AS ID
) TMITRAL_KAPAK_PATOLOJISI ON TMITRAL_KAPAK_PATOLOJISI.ID = USS274.MITRAL_KAPAK_PATOLOJISI


LEFT JOIN 
(
/**<MITRAL_KAPAK_PATOLOJI_DERECESI version="1" codeSystemGuid="e396ee6f-d024-4c95-9955-f26a3156525f" code="Code" value="MITRAL_KAPAK_PATOLOJI_DERECESI" />*/
SELECT 'YOK' AS AD, 1 AS ID
UNION ALL
SELECT 'HAFİF' AS AD, 2 AS ID
UNION ALL
SELECT 'ORTA' AS AD, 3 AS ID
UNION ALL
SELECT 'ŞİDDETLİ' AS AD, 4 AS ID

) TMITRAL_KAPAK_PATOLOJI_DERECESI ON TMITRAL_KAPAK_PATOLOJI_DERECESI.ID = USS274.MITRAL_KAPAK_PATOLOJI_DERECESI



--**************************************


WHERE USS274.HMUAYENEID= @HMUAYENEID AND USS274.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 --SPUSS_274_TAVI_KAYIT
										   NEWID() AS 'messageGuid/@value',
										   '1' AS 'messageType/@version',
										   '0a9ba485-e7e0-4abb-9c86-0a14fd364bb8' as 'messageType/@codeSystemGuid',
										   '274' AS 'messageType/@code',
										   'TAVI Kayıt 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
														

														   FORMU_DOLDURAN_HEKIM_KIMLIK_NUMARASI AS 'FORMU_DOLDURAN_HEKIM_KIMLIK_NUMARASI/@value',
														   ISLEM_REFERANS_NUMARASI AS 'ISLEM_REFERANS_NUMARASI/@value',
														   (
															SELECT
        
																-- STS_SKORU (numeric - sadece value)
																STS_SKORU AS 'STS_SKORU/@value',

																-- SIGARA_ICIYOR_MU
																(
																	SELECT
																		'1' AS '@version',
																		'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS '@codeSystemGuid',
																		SIGARA_ICIYOR_MU_ID AS '@code',
																		SIGARA_ICIYOR_MU_ADI AS '@value'
																	FOR XML PATH('SIGARA_ICIYOR_MU'), TYPE
																),

																-- FIZIKSEL_AKTIVITE_DUZEYI_AZALMIS_MI
																(
																	SELECT
																		'1' AS '@version',
																		'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS '@codeSystemGuid',
																		FIZIKSEL_AKTIVITE_DUZEYI_AZALMIS_MI_ID AS '@code',
																		FIZIKSEL_AKTIVITE_DUZEYI_AZALMIS_MI_ADI AS '@value'
																	FOR XML PATH('FIZIKSEL_AKTIVITE_DUZEYI_AZALMIS_MI'), TYPE
																),

																-- NOROLOJIK_HASTALIK_OYKUSU
																(
																	SELECT
																		'1' AS '@version',
																		'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS '@codeSystemGuid',
																		NOROLOJIK_HASTALIK_OYKUSU_ID AS '@code',
																		NOROLOJIK_HASTALIK_OYKUSU_ADI AS '@value'
																	FOR XML PATH('NOROLOJIK_HASTALIK_OYKUSU'), TYPE
																),

																-- MALIGNENSI_OYKUSU
																(
																	SELECT
																		'1' AS '@version',
																		'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS '@codeSystemGuid',
																		MALIGNENSI_OYKUSU_ID AS '@code',
																		MALIGNENSI_OYKUSU_ADI AS '@value'
																	FOR XML PATH('MALIGNENSI_OYKUSU'), TYPE
																),

																-- GECIRILMIS_KALP_AMELIYATI
																(
																	SELECT
																		'1' AS '@version',
																		'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS '@codeSystemGuid',
																		GECIRILMIS_KALP_AMELIYATI_ID AS '@code',
																		GECIRILMIS_KALP_AMELIYATI_ADI AS '@value'
																	FOR XML PATH('GECIRILMIS_KALP_AMELIYATI'), TYPE
																),

																-- ONCEKI_PERKUTAN_AORTIK_KAPAK_GIRISIMI
																(
																	SELECT
																		'1' AS '@version',
																		'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS '@codeSystemGuid',
																		ONCEKI_PERKUTAN_AORTIK_KAPAK_GIRISIMI_ID AS '@code',
																		ONCEKI_PERKUTAN_AORTIK_KAPAK_GIRISIMI_ADI AS '@value'
																	FOR XML PATH('ONCEKI_PERKUTAN_AORTIK_KAPAK_GIRISIMI'), TYPE
																),

																-- GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_VAR_MI
																(
																	SELECT
																		'1' AS '@version',
																		'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS '@codeSystemGuid',
																		GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_VAR_MI_ID AS '@code',
																		GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_VAR_MI_ADI AS '@value'
																	FOR XML PATH('GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_VAR_MI'), TYPE
																),

																-- GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_BILGISI
																(
																	SELECT
																		(
																			SELECT
																				'1' AS '@version',
																				'1c50c4e8-1f90-4fb6-bfda-a4c7bab1848f' AS '@codeSystemGuid',
																				GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_ID AS '@code',
																				GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_ADI AS '@value'
																			FOR XML PATH('GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM'), TYPE
																		)
																	FOR XML PATH('GIRISIM_ONCESI_ESLIK_EDEN_CIDDI_DURUM_BILGISI'), TYPE
																),

																-- ONCEKI_PERKUTAN_KORONER_ISLEM
																(
																	SELECT
																		'1' AS '@version',
																		'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS '@codeSystemGuid',
																		ONCEKI_PERKUTAN_KORONER_ISLEM_ID AS '@code',
																		ONCEKI_PERKUTAN_KORONER_ISLEM_ADI AS '@value'
																	FOR XML PATH('ONCEKI_PERKUTAN_KORONER_ISLEM'), TYPE
																),

																-- TAVI_VUCUT_KITLE_INDEXI
																(
																	SELECT
																		'1' AS '@version',
																		'e1108e6f-fbaa-49cf-bf90-d09b5c1ee001' AS '@codeSystemGuid',
																		TAVI_VUCUT_KITLE_INDEXI_ID AS '@code',
																		TAVI_VUCUT_KITLE_INDEXI_ADI AS '@value'
																	FOR XML PATH('TAVI_VUCUT_KITLE_INDEXI'), TYPE
																),

																-- RITIM_BOZUKLUGU_ISLEM_OYKUSU
																(
																	SELECT
																		'1' AS '@version',
																		'c5e469f2-9146-4438-9890-5d7169822ff2' AS '@codeSystemGuid',
																		RITIM_BOZUKLUGU_ISLEM_OYKUSU_ID AS '@code',
																		RITIM_BOZUKLUGU_ISLEM_OYKUSU_ADI AS '@value'
																	FOR XML PATH('RITIM_BOZUKLUGU_ISLEM_OYKUSU'), TYPE
																),

																-- NEW_YORK_HEART_ASSOCIATION_DISPNE_DURUMU
																(
																	SELECT
																		'1' AS '@version',
																		'761be295-bafa-4660-8d7a-5b20f0b945f1' AS '@codeSystemGuid',
																		NEW_YORK_HEART_ASSOCIATION_DISPNE_DURUMU_ID AS '@code',
																		NEW_YORK_HEART_ASSOCIATION_DISPNE_DURUMU_ADI AS '@value'
																	FOR XML PATH('NEW_YORK_HEART_ASSOCIATION_DISPNE_DURUMU'), TYPE
																),

																-- FIZIKSEL_AKTIVITE_DUZEY_BILGISI
																(
																	SELECT
																		(
																			SELECT
																				'1' AS '@version',
																				'138f919b-80b0-44eb-93ec-a8d342aa5776' AS '@codeSystemGuid',
																				TAVI_FIZIKSEL_AKTIVITE_ID AS '@code',
																				TAVI_FIZIKSEL_AKTIVITE_ADI AS '@value'
																			FOR XML PATH('TAVI_FIZIKSEL_AKTIVITE'), TYPE
																		)
																	FOR XML PATH('FIZIKSEL_AKTIVITE_DUZEY_BILGISI'), TYPE
																),
																-- ISLEM_ONCESI_KALP_RITMI_BILGISI
																(
																	SELECT
																		(
																			SELECT
																				'1' AS '@version',
																				'2c1651f1-67c6-49ea-9db6-f09b5a5d9ffa' AS '@codeSystemGuid',
																				ISLEM_ONCESI_KALP_RITMI_ID AS '@code',
																				ISLEM_ONCESI_KALP_RITMI_ADI AS '@value'
																			FOR XML PATH('ISLEM_ONCESI_KALP_RITMI'), TYPE
																		)
																	FOR XML PATH('ISLEM_ONCESI_KALP_RITMI_BILGISI'), TYPE
																)

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

														(
															SELECT

																-- KORONER_ARTER_HASTALIK_BILGISI
																(
																	SELECT
																		'1' AS '@version',
																		'ff7f9c06-bfec-48f1-aa29-f9b1ced4b1b9' AS '@codeSystemGuid',
																		KORONER_ARTER_HASTALIK_BILGISI_ID AS '@code',
																		KORONER_ARTER_HASTALIK_BILGISI_ADI AS '@value'
																	FOR XML PATH('KORONER_ARTER_HASTALIK_BILGISI'), TYPE
																),

																-- SOL_ANA_KORONER_DAMAR_HASTALIGI_BILGISI
																(
																	SELECT
																		'1' AS '@version',
																		'1ec4f05c-6c9f-4718-9d5e-f4673671b72e' AS '@codeSystemGuid',
																		SOL_ANA_KORONER_DAMAR_HASTALIGI_BILGISI_ID AS '@code',
																		SOL_ANA_KORONER_DAMAR_HASTALIGI_BILGISI_ADI AS '@value'
																	FOR XML PATH('SOL_ANA_KORONER_DAMAR_HASTALIGI_BILGISI'), TYPE
																),

																-- KORONER_ARTER_HASTALIGI_VAR_MI
																(
																	SELECT
																		'1' AS '@version',
																		'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS '@codeSystemGuid',
																		KORONER_ARTER_HASTALIGI_VAR_MI_ID AS '@code',
																		KORONER_ARTER_HASTALIGI_VAR_MI_ADI AS '@value'
																	FOR XML PATH('KORONER_ARTER_HASTALIGI_VAR_MI'), TYPE
																),

																-- Numeric alanlar (sadece value)
																AORT_KAPAGI_PEAK_GRADIYENT AS 'AORT_KAPAGI_PEAK_GRADIYENT/@value',
																AORT_KAPAGI_ORTALAMA_GRADIYENT AS 'AORT_KAPAGI_ORTALAMA_GRADIYENT/@value',
																AORT_KAPAK_ALANI AS 'AORT_KAPAK_ALANI/@value',
																SISTOLIK_PULMONER_ARTER_BASINCI AS 'SISTOLIK_PULMONER_ARTER_BASINCI/@value',
																SOL_VENTRIKUL_EJEKSIYON_FRAKSIYONU AS 'SOL_VENTRIKUL_EJEKSIYON_FRAKSIYONU/@value',

																-- AORT_DARLIGININ_TIPI
																(
																	SELECT
																		'1' AS '@version',
																		'cc4afbe7-8c0a-47a8-afc5-030ded109b6c' AS '@codeSystemGuid',
																		AORT_DARLIGININ_TIPI_ID AS '@code',
																		AORT_DARLIGININ_TIPI_ADI AS '@value'
																	FOR XML PATH('AORT_DARLIGININ_TIPI'), TYPE
																),

																-- AORT_KAPAGININ_YAPISI
																(
																	SELECT
																		'1' AS '@version',
																		'a66a70a8-3f12-4e98-a897-6accc3eb23c9' AS '@codeSystemGuid',
																		AORT_KAPAGININ_YAPISI_ID AS '@code',
																		AORT_KAPAGININ_YAPISI_ADI AS '@value'
																	FOR XML PATH('AORT_KAPAGININ_YAPISI'), TYPE
																),

																-- SOL_ANA_KORONER_DAMAR_HASTALIGI_VAR_MI
																(
																	SELECT
																		'1' AS '@version',
																		'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS '@codeSystemGuid',
																		SOL_ANA_KORONER_DAMAR_HASTALIGI_VAR_MI_ID AS '@code',
																		SOL_ANA_KORONER_DAMAR_HASTALIGI_VAR_MI_ADI AS '@value'
																	FOR XML PATH('SOL_ANA_KORONER_DAMAR_HASTALIGI_VAR_MI'), TYPE
																),

																-- TRIKUSPIT_KAPAK_PATOLOJISI
																(
																	SELECT
																		'1' AS '@version',
																		'e396ee6f-d024-4c95-9955-f26a3156525f' AS '@codeSystemGuid',
																		TRIKUSPIT_KAPAK_PATOLOJISI_ID AS '@code',
																		TRIKUSPIT_KAPAK_PATOLOJISI_ADI AS '@value'
																	FOR XML PATH('TRIKUSPIT_KAPAK_PATOLOJISI'), TYPE
																),

																-- AORTIK_ANNULER_OLCUM_YONTEMI
																(
																	SELECT
																		(
																			SELECT
																				'1' AS '@version',
																				'25d4ac0f-9475-4f33-8301-f1ed556ce035' AS '@codeSystemGuid',
																				AORTIK_ANNULER_OLCUM_YONTEMI_BILGISI_ID AS '@code',
																				AORTIK_ANNULER_OLCUM_YONTEMI_BILGISI_ADI AS '@value'
																			FOR XML PATH('AORTIK_ANNULER_OLCUM_YONTEMI_BILGISI'), TYPE
																		),
																		AORTIK_ANULER_OLCUM_YONTEMI_DEGERI AS 'AORTIK_ANULER_OLCUM_YONTEMI_DEGERI/@value'
																	FOR XML PATH('AORTIK_ANNULER_OLCUM_YONTEMI'), TYPE
																),

																-- AORT_KAPAK_PATOLOJISI
																(
																	SELECT
																		(
																			SELECT
																				'1' AS '@version',
																				'41e3d27a-ee4f-477b-abc3-17e235387867' AS '@codeSystemGuid',
																				AORT_KAPAK_PATOLOJISI_BILGISI_ID AS '@code',
																				AORT_KAPAK_PATOLOJISI_BILGISI_ADI AS '@value'
																			FOR XML PATH('AORT_KAPAK_PATOLOJISI_BILGISI'), TYPE
																		)
																	FOR XML PATH('AORT_KAPAK_PATOLOJISI'), TYPE
																),

																-- AORT_KAPAK_PATOLOJISININ_ETIYOLOJISI
																(
																	SELECT
																		(
																			SELECT
																				'1' AS '@version',
																				'f624a701-736e-47ad-885f-667e106adc45' AS '@codeSystemGuid',
																				AORT_KAPAK_PATOLOJISININ_ETIYOLOJISI_BILGISI_ID AS '@code',
																				AORT_KAPAK_PATOLOJISININ_ETIYOLOJISI_BILGISI_ADI AS '@value'
																			FOR XML PATH('AORT_KAPAK_PATOLOJISININ_ETIYOLOJISI_BILGISI'), TYPE
																		)
																	FOR XML PATH('AORT_KAPAK_PATOLOJISININ_ETIYOLOJISI'), TYPE
																),

																-- MITRAL_KAPAK_PATOLOJISI_BILGISI
																(
																	SELECT
																		(
																			SELECT
																				'1' AS '@version',
																				'41e3d27a-ee4f-477b-abc3-17e235387867' AS '@codeSystemGuid',
																				MITRAL_KAPAK_PATOLOJISI_ID AS '@code',
																				MITRAL_KAPAK_PATOLOJISI_ADI AS '@value'
																			FOR XML PATH('MITRAL_KAPAK_PATOLOJISI'), TYPE
																		),
																		(
																			SELECT
																				'1' AS '@version',
																				'e396ee6f-d024-4c95-9955-f26a3156525f' AS '@codeSystemGuid',
																				MITRAL_KAPAK_PATOLOJI_DERECESI_ID AS '@code',
																				MITRAL_KAPAK_PATOLOJI_DERECESI_ADI AS '@value'
																			FOR XML PATH('MITRAL_KAPAK_PATOLOJI_DERECESI'), TYPE
																		)
																	FOR XML PATH('MITRAL_KAPAK_PATOLOJISI_BILGISI'), TYPE
																)

															FOR XML PATH('KARDIYAK_DEGERLENDIRME'), TYPE
														)



														  

														
														 FROM #TMPUSSDIGER
														 FOR XML PATH('TAVI_KAYIT_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
