SHARE
TWEET

Untitled

a guest Aug 20th, 2019 74 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top