-- Procedure creation 
CREATE PROCEDURE SC05475005_02 (
    @IN_FILIAL Char( 002 ) , 
    @IN_CCLIDEV Char( 006 ) , 
    @IN_CLOJDEV Char( 002 ) , 
    @IN_CDOCTMS Char( 001 ) , 
    @IN_CCODPRO Char( 015 ) , 
    @IN_CTIPNFC Char( 001 ) , 
    @IN_CTIPCLI Char( 001 ) , 
    @IN_CSEQINS Char( 002 ) , 
    @IN_CVZSEQINS Char( 002 ) , 
    @IN_CVZTIPCLI Char( 001 ) , 
    @IN_CCLIGEN Char( 006 ) , 
    @IN_CLOJGEN Char( 002 ) , 
    @IN_CTIPFRE Char( 001 ) , 
    @IN_LCONSIG Char( 01 ) , 
    @IN_LCPOCONSIG Char( 01 ) , 
    @IN_CCODPAS Char( 002 ) , 
    @IN_CESTORI Char( 002 ) , 
    @IN_CESTDEV Char( 002 ) , 
    @IN_CESTDES Char( 002 ) , 
    @IN_CATIVID Char( 006 ) , 
    @IN_CESTVEI Char( 002 ) , 
    @IN_NQTPARAM Integer , 
    @OUT_DUG_TES Char( 003 )  output , 
    @OUT_DUG_CODMSG Char( 006 )  output , 
    @OUT_F4_ISS Char( 001 )  output ) AS
 
-- Declaration of variables
DECLARE @cAux Char( 03 )
DECLARE @cFilial_DV1 Char( 002 )
DECLARE @cFilial_DUF Char( 002 )
DECLARE @cFilial_DUG Char( 002 )
DECLARE @cFilial_SF4 Char( 002 )
DECLARE @cREGTRI Char( 002 )
DECLARE @cMaxItem Char( 002 )
DECLARE @cDUG_FILIAL Char( 002 )
DECLARE @cDUG_REGTRI Char( 002 )
DECLARE @cDUG_TIPFRE Char( 001 )
DECLARE @cDUG_ITEM Char( 002 )
DECLARE @cDUG_TES Char( 003 )
DECLARE @cDUG_CODMSG Char( 006 )
DECLARE @cDUG_CODPAS Char( 002 )
DECLARE @cDUG_ESTORI Char( 002 )
DECLARE @cDUG_ESTDEV Char( 002 )
DECLARE @cDUG_ESTDES Char( 002 )
DECLARE @cDUG_SATIV Char( 006 )
DECLARE @cDUG_ESTVEI Char( 002 )
DECLARE @iDUG_ITEM1 Integer
DECLARE @iDUG_ITEM2 Integer
DECLARE @cDUG_ITEMCOMP1 Char( 002 )
DECLARE @cDUG_ITEMCOMP2 Char( 002 )
DECLARE @cDUG_ITEMCO Char( 04 )
DECLARE @cF4_ISS Char( 001 )
DECLARE @cDUG_CONSIG Char( 001 )
DECLARE @cLinha Char( 016 )
DECLARE @cF4_ISSXX Char( 001 )
DECLARE @cDUG_TESXX Char( 003 )
DECLARE @cDUG_CODMSGXX Char( 006 )
DECLARE @cDUG_CODPASGR Char( 002 )
DECLARE @cDUG_ESTORIGR Char( 002 )
DECLARE @cDUG_ESTDEVGR Char( 002 )
DECLARE @cDUG_ESTDESGR Char( 002 )
DECLARE @cDUG_SATIVGR Char( 006 )
DECLARE @cDUG_ESTVEIGR Char( 002 )
DECLARE @iRecnoDV1 Integer
DECLARE @iRecnoDUF Integer
DECLARE @iRecnoTemp Integer
DECLARE @nCount Integer
DECLARE @iTamItem Integer
DECLARE @cDUFTipFre Char( 1 )
BEGIN
   SET @OUT_DUG_TES  = ' ' 
   SET @OUT_DUG_CODMSG  = ' ' 
   SET @OUT_F4_ISS  = ' ' 
   SET @cREGTRI  = ' ' 
   SET @cMaxItem  = ' ' 
   SET @cLinha  = ' ' 
   SET @iRecnoDV1  = 0 
   SET @iRecnoDUF  = 0 
   SET @iRecnoTemp  = 0 
   SET @nCount  = 0 
   SET @cDUG_CODPASGR  = ' ' 
   SET @cDUG_ESTORIGR  = ' ' 
   SET @cDUG_ESTDEVGR  = ' ' 
   SET @cDUG_ESTDESGR  = ' ' 
   SET @cDUG_SATIVGR  = ' ' 
   SET @cDUG_ESTVEIGR  = ' ' 
   SET @cDUG_REGTRI  = ' ' 
   SET @cDUG_TIPFRE  = ' ' 
   SET @cDUG_ITEM  = ' ' 
   SET @cDUG_TES  = ' ' 
   SET @cDUG_CODMSG  = ' ' 
   SET @cDUG_CODPAS  = ' ' 
   SET @cDUG_ESTORI  = ' ' 
   SET @cDUG_ESTDEV  = ' ' 
   SET @cDUG_ESTDES  = ' ' 
   SET @cDUG_SATIV  = ' ' 
   SET @cDUG_ESTVEI  = ' ' 
   SET @cDUG_CONSIG  = ' ' 
   SET @cAux  = 'DV1' 
   EXEC SC05475001_02 @cAux , @IN_FILIAL , @cFilial_DV1 output 
   SET @cAux  = 'DUF' 
   EXEC SC05475001_02 @cAux , @IN_FILIAL , @cFilial_DUF output 
   SET @cAux  = 'DUG' 
   EXEC SC05475001_02 @cAux , @IN_FILIAL , @cFilial_DUG output 
   SET @cAux  = 'SF4' 
   EXEC SC05475001_02 @cAux , @IN_FILIAL , @cFilial_SF4 output 
   DELETE FROM SC054750 
   EXEC SC05475004_02 @cFilial_DV1 , @IN_CCLIDEV , @IN_CLOJDEV , @IN_CDOCTMS , @IN_CCODPRO , @IN_CTIPNFC , @IN_CTIPCLI , 
          @IN_CSEQINS , @IN_CVZSEQINS , @IN_CVZTIPCLI , @IN_CCLIGEN , @IN_CLOJGEN , @cREGTRI output , @iRecnoDV1 output 
   IF @iRecnoDV1  != 0  and @cREGTRI  != ' ' 
   BEGIN 
      IF @IN_CTIPFRE  = '1'  or @IN_CTIPFRE  = '2' 
      BEGIN 
         SELECT @iRecnoDUF  = COALESCE ( R_E_C_N_O_ , 0 ), @cDUFTipFre  = DUF_TIPFRE 
           FROM DUF020 
           WHERE DUF_FILIAL  = @cFilial_DUF  and DUF_REGTRI  = @cREGTRI  and DUF_TIPFRE  = @IN_CTIPFRE  and D_E_L_E_T_  = ' ' 
           
         IF @iRecnoDUF  = 0 
         BEGIN 
            SELECT @iRecnoDUF  = COALESCE ( R_E_C_N_O_ , 0 ), @cDUFTipFre  = DUF_TIPFRE 
              FROM DUF020 
              WHERE DUF_FILIAL  = @cFilial_DUF  and DUF_REGTRI  = @cREGTRI  and DUF_TIPFRE  = '3'  and D_E_L_E_T_  = ' ' 
              
         END 
      END 
      ELSE 
      BEGIN 
         IF @IN_CTIPFRE  = ' '  or @IN_CTIPFRE  = '3' 
         BEGIN 
            SELECT @iRecnoDUF  = COALESCE ( R_E_C_N_O_ , 0 ), @cDUFTipFre  = DUF_TIPFRE 
              FROM DUF020 
              WHERE DUF_FILIAL  = @cFilial_DUF  and DUF_REGTRI  = @cREGTRI  and DUF_TIPFRE  = '3'  and D_E_L_E_T_  = ' ' 
              
         END 
      END 
      IF @iRecnoDUF  != 0 
      BEGIN 
         SELECT @cMaxItem  = COALESCE ( MAX ( DUG_ITEM ), ' ' )
           FROM DUG020 
           WHERE DUG_FILIAL  = @cFilial_DUG  and DUG_REGTRI  = @cREGTRI  and DUG_TIPFRE  = @cDUFTipFre  and D_E_L_E_T_  = ' ' 
           
         SET @cF4_ISSXX  = ' ' 
         SET @cDUG_TESXX  = ' ' 
         SET @cDUG_CODMSGXX  = ' ' 
          
         -- Cursor declaration CUR_TMS010
         DECLARE CUR_TMS010 insensitive  CURSOR FOR 
         SELECT DUG_FILIAL , DUG_REGTRI , DUG_TIPFRE , DUG_ITEM , DUG_TES , DUG_CODMSG , DUG_CODPAS , DUG_ESTORI , DUG_ESTDEV , 
                DUG_ESTDES , DUG_SATIV , DUG_ESTVEI 
           FROM DUG020 
           WHERE DUG_FILIAL  = @cFilial_DUG  and DUG_REGTRI  = @cREGTRI  and DUG_TIPFRE  = @cDUFTipFre  and D_E_L_E_T_  = ' ' 
           
           ORDER BY DUG_FILIAL , DUG_REGTRI , DUG_TIPFRE , DUG_CODPAS , DUG_ESTORI , DUG_ESTDEV , DUG_ESTDES , DUG_SATIV , 
                DUG_ESTVEI 
         FOR READ ONLY 
          
         OPEN CUR_TMS010
         FETCH CUR_TMS010 
          INTO @cDUG_FILIAL , @cDUG_REGTRI , @cDUG_TIPFRE , @cDUG_ITEM , @cDUG_TES , @cDUG_CODMSG , @cDUG_CODPAS , @cDUG_ESTORI , 
                @cDUG_ESTDEV , @cDUG_ESTDES , @cDUG_SATIV , @cDUG_ESTVEI 
         WHILE ( (@@Fetch_status  = 0 ) )
         BEGIN
            SET @cLinha  = @cDUG_CODPAS  + @cDUG_ESTORI  + @cDUG_ESTDEV  + @cDUG_ESTDES  + @cDUG_SATIV  + @cDUG_ESTVEI 
            SELECT @iRecnoTemp  = COALESCE ( MIN ( R_E_C_N_O_ ), 0 )
              FROM SC054750 
              WHERE D_E_L_E_T_  = ' ' 
            IF  (@cLinha  = ' '  and @iRecnoTemp  = 0 ) 
            BEGIN 
               SELECT @cF4_ISSXX  = COALESCE ( F4_ISS , ' ' )
                 FROM SF4020 
                 WHERE F4_FILIAL  = @cFilial_SF4  and F4_CODIGO  = @cDUG_TES  and D_E_L_E_T_  = ' ' 
               SET @cDUG_TESXX  = @cDUG_TES 
               SET @cDUG_CODMSGXX  = @cDUG_CODMSG 
            END 
            IF @cDUG_CODPAS  = @IN_CCODPAS  and @cDUG_ESTORI  = @IN_CESTORI  and @cDUG_ESTDEV  = @IN_CESTDEV  and @cDUG_ESTDES  = @IN_CESTDES 
               and @cDUG_SATIV  = @IN_CATIVID  and @cDUG_ESTVEI  = @IN_CESTVEI 
            BEGIN 
               SET @cLinha  = ' ' 
            END 
            SET @nCount  = 0 
            IF @cLinha  != ' ' 
            BEGIN 
               IF @cDUG_CODPAS  = @IN_CCODPAS 
               BEGIN 
                  SET @nCount  = 1 
               END 
               IF @cDUG_ESTORI  = @IN_CESTORI 
               BEGIN 
                  SET @nCount  = @nCount  + 1 
               END 
               IF @cDUG_ESTDEV  = @IN_CESTDEV 
               BEGIN 
                  SET @nCount  = @nCount  + 1 
               END 
               IF @cDUG_ESTDES  = @IN_CESTDES 
               BEGIN 
                  SET @nCount  = @nCount  + 1 
               END 
               IF @cDUG_SATIV  = @IN_CATIVID 
               BEGIN 
                  SET @nCount  = @nCount  + 1 
               END 
               IF @cDUG_ESTVEI  = @IN_CESTVEI 
               BEGIN 
                  SET @nCount  = @nCount  + 1 
               END 
               IF @nCount  = @IN_NQTPARAM 
               BEGIN 
                  SET @cF4_ISSXX  = ' ' 
                  SET @cDUG_TESXX  = ' ' 
                  SET @cDUG_CODMSGXX  = ' ' 
                  DELETE FROM SC054750 
                  SELECT @cF4_ISSXX  = COALESCE ( F4_ISS , ' ' )
                    FROM SF4020 
                    WHERE F4_FILIAL  = @cFilial_SF4  and F4_CODIGO  = @cDUG_TES  and D_E_L_E_T_  = ' ' 
                  SET @cDUG_TESXX  = @cDUG_TES 
                  SET @cDUG_CODMSGXX  = @cDUG_CODMSG 
               END 
               ELSE 
               BEGIN 
                  SET @cF4_ISSXX  = ' ' 
                  SET @cDUG_TESXX  = ' ' 
                  SET @cDUG_CODMSGXX  = ' ' 
                  SET @iDUG_ITEM1  = CONVERT( Integer ,@cDUG_ITEM )
                  SET @iDUG_ITEM2  = CONVERT( Integer ,@cMaxItem ) - @iDUG_ITEM1 
                  SET @iTamItem  = LEN ( @cDUG_ITEM )
                  SET @cDUG_ITEMCOMP1  = ' ' 
                  SET @cDUG_ITEMCOMP2  = ' ' 
                  EXEC SC05475002_02 @nCount , @iTamItem , @cDUG_ITEMCOMP1 output 
                  EXEC SC05475002_02 @iDUG_ITEM2 , @iTamItem , @cDUG_ITEMCOMP2 output 
                  SET @cDUG_ITEMCO  = @cDUG_ITEMCOMP1  + @cDUG_ITEMCOMP2 
                  SET @cDUG_CODPASGR  = @cDUG_CODPAS 
                  SET @cDUG_ESTORIGR  = @cDUG_ESTORI 
                  SET @cDUG_ESTDEVGR  = @cDUG_ESTDEV 
                  SET @cDUG_ESTDESGR  = @cDUG_ESTDES 
                  SET @cDUG_SATIVGR  = @cDUG_SATIV 
                  SET @cDUG_ESTVEIGR  = @cDUG_ESTVEI 
                  IF @cDUG_CODPAS  != ' ' 
                  BEGIN 
                     SET @cDUG_CODPASGR  = @IN_CCODPAS 
                  END 
                  IF @cDUG_ESTORI  != ' ' 
                  BEGIN 
                     SET @cDUG_ESTORIGR  = @IN_CESTORI 
                  END 
                  IF @cDUG_ESTDEV  != ' ' 
                  BEGIN 
                     SET @cDUG_ESTDEVGR  = @IN_CESTDEV 
                  END 
                  IF @cDUG_ESTDES  != ' ' 
                  BEGIN 
                     SET @cDUG_ESTDESGR  = @IN_CESTDES 
                  END 
                  IF @cDUG_SATIV  != ' ' 
                  BEGIN 
                     SET @cDUG_SATIVGR  = @IN_CATIVID 
                  END 
                  IF @cDUG_ESTVEI  != ' ' 
                  BEGIN 
                     SET @cDUG_ESTVEIGR  = @IN_CESTVEI 
                  END 
                  SELECT @iRecnoTemp  = COALESCE ( MAX ( R_E_C_N_O_ ), 0 )
                    FROM SC054750 
                  SET @iRecnoTemp  = @iRecnoTemp  + 1 
                  begin tran 
                  INSERT INTO SC054750 (DUG_REGTRI , DUG_TIPFRE , DUG_CODPAS , DUG_ESTORI , DUG_ESTDEV , DUG_ESTDES , DUG_SATIV , 
                         DUG_ESTVEI , DUG_ITEM1 , DUG_ITEM2 , NCOUNT , DUG_ITEMCO , R_E_C_N_O_ ) 
                  VALUES (@cDUG_REGTRI , @cDUG_TIPFRE , @cDUG_CODPASGR , @cDUG_ESTORIGR , @cDUG_ESTDEVGR , @cDUG_ESTDESGR , 
                         @cDUG_SATIVGR , @cDUG_ESTVEIGR , @iDUG_ITEM1 , @iDUG_ITEM2 , @nCount , @cDUG_ITEMCO , @iRecnoTemp );
                  Commit Tran 
               END 
            END 
            IF @nCount  = @IN_NQTPARAM 
            BEGIN 
               break
            END 
            FETCH CUR_TMS010 
             INTO @cDUG_FILIAL , @cDUG_REGTRI , @cDUG_TIPFRE , @cDUG_ITEM , @cDUG_TES , @cDUG_CODMSG , @cDUG_CODPAS , @cDUG_ESTORI , 
                   @cDUG_ESTDEV , @cDUG_ESTDES , @cDUG_SATIV , @cDUG_ESTVEIGR 
            SET @cLinha  = ' ' 
            SET @nCount  = 0 
         END 
         CLOSE CUR_TMS010
         DEALLOCATE CUR_TMS010
         IF @cF4_ISSXX  = ' '  and @cDUG_TESXX  = ' '  and @cDUG_CODMSGXX  = ' ' 
         BEGIN 
            SET @cDUG_CODPASGR  = ' '   
            SET @cDUG_ESTORIGR  = ' '   
            SET @cDUG_ESTDEVGR  = ' ' 
            SET @cDUG_ESTDESGR  = ' '   
            SET @cDUG_SATIVGR  = ' '   
            SET @cDUG_ITEMCO  = '0000'   
            SET @cDUG_ESTVEIGR  = ' ' 
            SET @iDUG_ITEM1  = 0   
            SET @iDUG_ITEM2  = 0   
            SET @nCount  = 0 
            SELECT @iRecnoTemp  = COALESCE ( MAX ( R_E_C_N_O_ ), 0 )
              FROM SC054750 
            SET @iRecnoTemp  = @iRecnoTemp  + 1 
            begin tran 
            INSERT INTO SC054750 (DUG_REGTRI , DUG_TIPFRE , DUG_CODPAS , DUG_ESTORI , DUG_ESTDEV , DUG_ESTDES , DUG_SATIV , 
                   DUG_ESTVEI , DUG_ITEM1 , DUG_ITEM2 , NCOUNT , DUG_ITEMCO , R_E_C_N_O_ ) 
            VALUES (@cDUG_REGTRI , @cDUG_TIPFRE , @cDUG_CODPASGR , @cDUG_ESTORIGR , @cDUG_ESTDEVGR , @cDUG_ESTDESGR , @cDUG_SATIVGR , 
                   @cDUG_ESTVEIGR , @iDUG_ITEM1 , @iDUG_ITEM2 , @nCount , @cDUG_ITEMCO , @iRecnoTemp );
            commit tran 
            EXEC SC05475003_02 @IN_FILIAL , @cDUG_REGTRI , @cDUG_TIPFRE , @IN_LCONSIG , @IN_LCPOCONSIG , @cDUG_TESXX output , 
                   @cDUG_CODMSGXX output , @cF4_ISSXX output 
         END 
      END 
   END 
   IF @cDUG_TESXX is null  or @cDUG_TESXX  = '' 
   BEGIN 
      SET @OUT_DUG_TES  = ' ' 
   END 
   ELSE 
   BEGIN 
      SET @OUT_DUG_TES  = @cDUG_TESXX 
   END 
   IF @cDUG_CODMSGXX is null  or @cDUG_CODMSGXX  = '' 
   BEGIN 
      SET @OUT_DUG_CODMSG  = ' ' 
   END 
   ELSE 
   BEGIN 
      SET @OUT_DUG_CODMSG  = @cDUG_CODMSGXX 
   END 
   IF @cF4_ISSXX is null  or @cF4_ISSXX  = '' 
   BEGIN 
      SET @OUT_F4_ISS  = ' ' 
   END 
   ELSE 
   BEGIN 
      SET @OUT_F4_ISS  = @cF4_ISSXX 
   END 
END 