Guest User

Untitled

a guest
Aug 20th, 2019
81
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [FATURA]
  2. GO
  3. /****** Object: StoredProcedure [dbo].[EFATURA_SAVE_REQUEST] Script Date: 8/20/2019 11:46:33 AM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. ALTER PROCEDURE [dbo].[EFATURA_SAVE_REQUEST] (@FILENAME varchar(199) = null
  9. ,@STATUS int = null,@ERRORCODE int = null,@ERRORMSG varchar(500) = null,@RECEIVERIDENTIFIER varchar(100) = null
  10. ,@RECEIVERVKN varchar(100) = null,@RECEIVERUNVAN varchar(100)=null,@REFERENCE_ID varchar(50)=NULL,@FATURATURU varchar(100)=NULL)
  11. AS
  12. declare @REQUEST_ID int
  13. if CHARINDEX('.', @FILENAME)>0
  14. begin
  15. set @FILENAME=LEFT (@FILENAME,len(@FILENAME)-4)
  16. end
  17.  
  18. declare @SIMDI datetime;
  19. select @SIMDI = GETDATE();
  20. /* ********************* LOGGING ********************************* */
  21. INSERT INTO [dbo].[EFATURA_REQUEST_LOG]
  22. ([FATURANO]
  23. ,[EFATURA_GUID]
  24. ,[STATUS]
  25. ,[REQUESTRESULTCODE]
  26. ,[ERRORMSG]
  27. ,[CREADATE]
  28. ,[MODFDATE]
  29. ,[RECEIVERIDENTIFIER]
  30. ,[RECEIVERVKN]
  31. ,[RECEIVERUNVAN]
  32. ,[REFERENCE_ID]
  33. ,[FATURATURU]
  34. )
  35. VALUES
  36. (0
  37. ,@FILENAME
  38. ,@STATUS
  39. ,@ERRORCODE
  40. ,@ERRORMSG
  41. ,@SIMDI
  42. ,@SIMDI
  43. ,@RECEIVERIDENTIFIER
  44. ,@RECEIVERVKN
  45. ,@RECEIVERUNVAN
  46. ,@REFERENCE_ID
  47. ,@FATURATURU
  48. )
  49. /* ********************* LOGGING ********************************* */
  50. -- (ISNULL(REFERENCE_ID,'')='' and EFATURA_GUID=@REFERENCE_ID) OR
  51. if not exists(select * from EFATURA_REQUEST(nolock) where (ISNULL(REFERENCE_ID,'')='' and EFATURA_GUID=@FILENAME) OR(ISNULL(REFERENCE_ID,'')!='' and ISNULL(REFERENCE_ID,'')=ISNULL(@REFERENCE_ID,'')) OR (EFATURA_GUID=@FILENAME and ISNULL(@REFERENCE_ID,'')='')) --([FILENAME]=@FILENAME AND (isnull(@REFERENCE_ID,'')='' or STATUS=1) ) or (isnull(@REFERENCE_ID,'')!='' AND isnull(REFERENCE_ID,'')=@REFERENCE_ID and STATUS!=1))
  52. begin
  53. --select @REQUEST_ID = EFATURA_REQUEST_ID from EFATURA_REQUEST (nolock) where [EFATURA_GUID]=@FILENAME;
  54. INSERT INTO [FATURA].[dbo].[EFATURA_REQUEST](
  55. EFATURA_GUID
  56. ,STATUS
  57. ,REQUESTRESULTCODE
  58. ,ERRORMSG
  59. ,CREADATE
  60. ,MODFDATE
  61. ,RECEIVERIDENTIFIER
  62. ,RECEIVERVKN
  63. ,RECEIVERUNVAN
  64. ,REFERENCE_ID
  65. ,FATURATURU
  66. )
  67. VALUES(
  68. @FILENAME
  69. ,@STATUS
  70. ,@ERRORCODE
  71. ,@ERRORMSG
  72. ,@SIMDI
  73. ,@SIMDI
  74. ,@RECEIVERIDENTIFIER
  75. ,@RECEIVERVKN
  76. ,@RECEIVERUNVAN
  77. ,@REFERENCE_ID
  78. ,@FATURATURU
  79. )
  80. end
  81. else
  82. begin
  83. if ( @STATUS IN (5,6,7) OR @ERRORCODE = '1300' )
  84. begin
  85. BEGIN TRY
  86. declare @FATURANO varchar(100)
  87. DECLARE db_cursor CURSOR FOR
  88. select FATURANO from [FATURA].[dbo].[EFATURA_REQUEST](nolock) R inner join [FATURA].[dbo].[EFATURA_REQUEST_FATURA](nolock) D on R.EFATURA_REQUEST_ID =D.EFATURA_REQUEST_ID
  89. where (ISNULL(R.REFERENCE_ID,'')='' and R.EFATURA_GUID=@FILENAME) OR(ISNULL(R.REFERENCE_ID,'')!='' and ISNULL(R.REFERENCE_ID,'')=ISNULL(@REFERENCE_ID,'')) OR (R.EFATURA_GUID=@FILENAME and ISNULL(@REFERENCE_ID,'')='');
  90. OPEN db_cursor
  91. FETCH NEXT FROM db_cursor INTO @FATURANO
  92. WHILE @@FETCH_STATUS = 0
  93. BEGIN
  94. exec FATURA.dbo.[EFATURA_UPDATE_PRINTCOUNT] @FATURANO
  95. FETCH NEXT FROM db_cursor INTO @FATURANO
  96. END
  97. CLOSE db_cursor
  98. DEALLOCATE db_cursor
  99. END TRY
  100. BEGIN CATCH
  101. print 'Error'
  102. END CATCH;
  103. end
  104.  
  105. update R set
  106. --R.FATURANO = isnull(@FATURANO,R.FATURANO)
  107. --,R.EFATURA_GUID = @FILENAME
  108. R.STATUS= case when isnull(@STATUS,R.STATUS)=-1 then R.STATUS else isnull(@STATUS,R.STATUS) end
  109. ,R.REQUESTRESULTCODE=isnull(@ERRORCODE,R.REQUESTRESULTCODE)
  110. ,R.ERRORMSG=isnull(@ERRORMSG,R.ERRORMSG)
  111. ,R.MODFDATE = @SIMDI
  112. ,RECEIVERIDENTIFIER=case when isnull(@RECEIVERIDENTIFIER,'')!='' then @RECEIVERIDENTIFIER else RECEIVERIDENTIFIER end
  113. ,RECEIVERVKN=case when isnull(@RECEIVERVKN,'')!='' then @RECEIVERVKN else RECEIVERVKN end
  114. ,RECEIVERUNVAN=case when isnull(@RECEIVERUNVAN,'')!='' then @RECEIVERUNVAN else RECEIVERUNVAN end
  115. ,REFERENCE_ID=case when @status = 6 then @REFERENCE_ID else REFERENCE_ID end
  116. ,FATURATURU=case when isnull(@FATURATURU,'')!='' then @FATURATURU else FATURATURU end
  117. from [FATURA].[dbo].[EFATURA_REQUEST](nolock) R --ISNULL(REFERENCE_ID,'')='' and EFATURA_GUID=@REFERENCE_ID) OR
  118. where (ISNULL(REFERENCE_ID,'')='' and EFATURA_GUID=@FILENAME) OR(ISNULL(REFERENCE_ID,'')!='' and ISNULL(REFERENCE_ID,'')=ISNULL(@REFERENCE_ID,'')) OR (EFATURA_GUID=@FILENAME and ISNULL(@REFERENCE_ID,'')='');
  119. --where (ISNULL(REFERENCE_ID,'')='' and [EFATURA_GUID]=@FILENAME) OR(ISNULL(REFERENCE_ID,'')!='' and ISNULL(REFERENCE_ID,'')=ISNULL(@REFERENCE_ID,'')) OR ([EFATURA_GUID]=@FILENAME and ISNULL(@REFERENCE_ID,'')='')
  120.  
  121. end
RAW Paste Data