IF EXISTS (SELECT * FROM SYSOBJECTS O, SYSUSERS U
           WHERE O.NAME = 'CONTCTBL_FORNECEDOR' AND
                 TYPE = 'P' 	         AND
	              U.UID = O.UID      AND
	              U.NAME = USER)
DROP PROCEDURE CONTCTBL_FORNECEDOR
GO


CREATE PROCEDURE CONTCTBL_FORNECEDOR(
         @P_CDEMPRESA     VARCHAR(2),
         @P_TIPOOPER      VARCHAR(2),
	 @P_IDENTRSAID    VARCHAR,
	 @P_FILIAL        VARCHAR(4),
	 @P_FORNECED      VARCHAR(21),
         @P_NRLANCTONF    VARCHAR(10),
         @P_CDCONTCTBL    VARCHAR OUTPUT,
         @P_STCONTCTBLP   VARCHAR OUTPUT,
         @P_STCONTDIFE    VARCHAR OUTPUT,
         @P_STCDHISTPADR  VARCHAR OUTPUT)
AS
BEGIN
    DECLARE @V_CDCONTTOPER   VARCHAR(20);
    DECLARE @V_CDCONTFORCLI  VARCHAR(20);
    DECLARE @V_CDHISTPADR    VARCHAR(4);
    DECLARE @V_CDCONTPCTBL   VARCHAR(20);
    DECLARE @V_CDCONTPFILI   VARCHAR(20);
    DECLARE @V_CDCONTPEMPR   VARCHAR(20);
    DECLARE @V_CDCONTPROD    VARCHAR(20);
    DECLARE @V_CDCONTDIFE    VARCHAR(20);
    DECLARE @V_STCONTCTBL    VARCHAR(20);
    DECLARE @V_STCONTCTBLP   VARCHAR(20);
    DECLARE @V_STCDHISTPADR  VARCHAR(4);
    DECLARE @V_IDORIGDESTOP  VARCHAR(1); 
                                                  
    SELECT
       @V_CDCONTTOPER  = CDCONTTOPER,
       @V_CDCONTFORCLI = CDCONTFORCLI,
       @V_CDHISTPADR   = CDHISTPADR,
       @V_IDORIGDESTOP = IDORIGDESTOP,
       @V_CDCONTPCTBL  = CDCONTPCTBL,
       @V_CDCONTPFILI  = CDCONTPFILI,
       @V_CDCONTPEMPR  = CDCONTPEMPR,
       @V_CDCONTPROD   = CDCONTPROD,
       @V_CDCONTDIFE   = CDCONTDIFE
    FROM
      (SELECT 
          CDCONTFORCLI AS CDCONTTOPER, T.CDHISTPADR, IDORIGDESTOP, CDCONTPROD
       FROM 
          TIPOOPERAC T
       WHERE
          CDTIPOOPER   = @P_TIPOOPER AND
          IDENTRSAIDOP = @P_IDENTRSAID) A,
                                                     
      (SELECT
          F.CDCONTCTBL AS CDCONTFORCLI, CDCONTPCTBL, CDCONTPFILI, CDCONTPEMPR, '' AS CDCONTDIFE
       FROM
          FORNECEDOR F,
                                           
          (SELECT 
             MAX(CDCONTFORN) AS CDCONTPCTBL 
           FROM  PARAMCTBL) PC,
                                           
          (SELECT 
             MAX(CDCONFORNEM) AS CDCONTPEMPR
           FROM  PARAMEMPRESA
           WHERE CDEMPRESA = @P_CDEMPRESA) PE,
                                           
          (SELECT
             MAX(CDCONFORNFI) AS CDCONTPFILI FROM PARAMFILIAL
           WHERE CDFILIAL = @P_FILIAL) PF
                                     
       WHERE CDFORNECED = @P_FORNECED ) B;

       IF @V_CDCONTTOPER IS NOT NULL BEGIN
          SET @P_CDCONTCTBL = @V_CDCONTTOPER;
       END
       ELSE IF @V_CDCONTFORCLI IS NOT NULL BEGIN
               SET @P_CDCONTCTBL = @V_CDCONTFORCLI;
            END
            ELSE IF @V_CDCONTPFILI IS NOT NULL BEGIN
                    SET @P_CDCONTCTBL = @V_CDCONTPFILI;
                 END
                 ELSE IF @V_CDCONTPEMPR IS NOT NULL BEGIN
                         SET @P_CDCONTCTBL = @V_CDCONTPEMPR; 
                      END
                      ELSE IF @V_CDCONTPCTBL IS NOT NULL BEGIN
                              SET @P_CDCONTCTBL = @V_CDCONTPCTBL;
                           END;
                                         
       SET @P_STCONTDIFE   = @V_CDCONTDIFE;
       SET @P_STCONTCTBLP  = @V_CDCONTPROD;
       SET @P_STCDHISTPADR = @V_CDHISTPADR; 

END

GO