SET TERM ^ ;

CREATE OR ALTER procedure SP_SALDO_COLAB_PRE_PEDIDO (
    PRE_PEDIDO_ID integer,
    ACAO integer = 1)
as
declare variable COLABORADOR_ID integer;
declare variable NFE_TIPOPAG varchar(2);
declare variable VALOR_LIQUIDO_TOTAL numeric(15,4);
BEGIN
   colaborador_id = coalesce((SELECT FIRST 1 C.COLABORADOR_ID FROM TBPDV_CONFERENCIA C WHERE C.id = :PRE_PEDIDO_ID AND C.COLABORADOR_ID > 0),0);
   IF(colaborador_id > 0)THEN
   BEGIN
        IF(:ACAO = 1) THEN
        BEGIN
       /*BAIXAR O SALDO PARA O CRÉDITO DO COLABORADOR*/
        FOR
         SELECT
            COALESCE((
                SELECT
                        SUM((Z.TOTAL-(CAST(Z.TOTAL AS DOUBLE PRECISION)*(CAST(Z.PERC_DESCONTO AS DOUBLE PRECISION)/100.00)))) AS VALOR_LIQUIDO_TOTAL
                        FROM(
                        SELECT
                            (CAST(IX.QUANTIDADE AS DOUBLE PRECISION) * CAST(X.VALOR_UNITARIO AS DOUBLE PRECISION)) as total,
                            X.VALOR_DESCONTO,
                            IIF(X.VALOR_DESCONTO > 0,
                            (CAST(X.VALOR_DESCONTO AS DOUBLE PRECISION)*100.00)/(CAST(IX.QUANTIDADE AS DOUBLE PRECISION) * CAST(X.VALOR_UNITARIO AS DOUBLE PRECISION)),0.00) AS PERC_DESCONTO
                            FROM TBPDV_CONFERENCIA_ITEM X , TBPDV_CONFERENCIA_ITEM_SALDO IX, TBPDV_CONFERENCIA XP, TBPAGAMENTO_FORMA FP
                            WHERE  X.PDV_CONFERENCIA_ID    = XP.ID
                            AND XP.ID                      = :PRE_PEDIDO_ID
                            AND FP.CODIGO                  = XP.PAGAMENTO_FORMA
                            AND FP.NFE_TIPPAG              = '80'
                            AND IX.PDV_CONFERENCIA_ITEM_ID = X.ID
                            AND  X.PDV_CONFERENCIA_ID      = IX.PDV_CONFERENCIA_ID
                            AND IX.QUANTIDADE              > 0
                            AND (X.SITUACAO <> 3 AND X.SITUACAO <> 4)
                        ) Z
                ),0.00) as VALOR_LIQUIDO_TOTAL
            FROM RDB$DATABASE
          INTO :VALOR_LIQUIDO_TOTAL DO
          BEGIN
            --EXCEPTION "..." 'VALOR LIQUIDO:'||:VALOR_LIQUIDO_TOTAL;
             UPDATE TBCOLABORADOR_CREDITO CRED SET CRED.SALDO = CRED.SALDO - (:VALOR_LIQUIDO_TOTAL) WHERE CRED.COLABORADOR_ID = :COLABORADOR_ID;
          END
        END
    
        ELSE IF(:ACAO = 2) THEN
        BEGIN
            /*DEVOLVE O SALDO PARA O CRÉDITO DO COLABORADOR*/
            FOR
             SELECT
                COALESCE((
                    SELECT
                            SUM((Z.TOTAL-(CAST(Z.TOTAL AS DOUBLE PRECISION)*(CAST(Z.PERC_DESCONTO AS DOUBLE PRECISION)/100.00)))) AS VALOR_LIQUIDO_TOTAL
                            FROM(
                            SELECT
                                (CAST(IX.QUANTIDADE AS DOUBLE PRECISION) * CAST(X.VALOR_UNITARIO AS DOUBLE PRECISION)) as total,
                                X.VALOR_DESCONTO,
                                IIF(X.VALOR_DESCONTO > 0,
                                (CAST(X.VALOR_DESCONTO AS DOUBLE PRECISION)*100.00)/(CAST(IX.QUANTIDADE AS DOUBLE PRECISION) * CAST(X.VALOR_UNITARIO AS DOUBLE PRECISION)),0.00) AS PERC_DESCONTO
                                FROM TBPDV_CONFERENCIA_ITEM X , TBPDV_CONFERENCIA_ITEM_SALDO IX, TBPDV_CONFERENCIA XP, TBPAGAMENTO_FORMA FP
                                WHERE  X.PDV_CONFERENCIA_ID    = XP.ID
                                AND XP.ID                      = :PRE_PEDIDO_ID
                                AND FP.CODIGO                  = XP.PAGAMENTO_FORMA
                                AND FP.NFE_TIPPAG              = '80'
                                AND IX.PDV_CONFERENCIA_ITEM_ID = X.ID
                                AND  X.PDV_CONFERENCIA_ID      = IX.PDV_CONFERENCIA_ID
                                AND IX.QUANTIDADE              > 0
                                AND (X.SITUACAO <> 3 AND X.SITUACAO <> 4)
                            ) Z
                    ),0.00) as VALOR_LIQUIDO_TOTAL
                FROM RDB$DATABASE
              INTO :VALOR_LIQUIDO_TOTAL DO
              BEGIN
                --EXCEPTION "..." 'VALOR LIQUIDO:'||:VALOR_LIQUIDO_TOTAL;
                 UPDATE TBCOLABORADOR_CREDITO CRED SET CRED.SALDO = CRED.SALDO + (:VALOR_LIQUIDO_TOTAL) WHERE CRED.COLABORADOR_ID = :COLABORADOR_ID;
              END
        END
    END
END^

^ SET TERM ; ^