/*NÃO EXECUTAR NA DELFA*/

CREATE INDEX ITBMETA_05
ON TBMETA (FAMILIA_ID);

CREATE INDEX ITBMETA_06
ON TBMETA (DATA_INICIO);

CREATE INDEX ITBMETA_07
ON TBMETA (DATA_FIM);

CREATE GLOBAL TEMPORARY TABLE TMPMETA_FATURAMENTO (
    REPRESENTANTE_ID integer,
    FAMILIA_ID integer,
    VALOR_FATURAMENTO numeric(18,5),
    VALOR_META numeric(18,2),
    PERCENTUAL_ATINGIDO numeric(18,9),
    COMISSAO_META numeric(18,2)
) ON COMMIT DELETE ROWS;


create or alter procedure SPC_COMISSAO_META_ATINGIDA (
    DATA_INICIAL date,
    DATA_FINAL date)
returns (
    REPRESENTANTE_ID integer,
    FAMILIA_ID integer,
    VALOR_FATURAMENTO numeric(18,5),
    VALOR_META numeric(18,2),
    PERCENTUAL_ATINGIDO numeric(18,9),
    COMISSAO_META numeric(18,2))
as
BEGIN

FOR SELECT
REPRESENTANTE_ID,
FAMILIA_ID,
VALOR_FATURAMENTO,
VALOR_META,
ROUND(VALOR_FATURAMENTO / VALOR_META * 100.00 ,2) AS PERCENTUAL_ATINGIDO,
COALESCE(
(SELECT FIRST 1 MF.PERCENTUAL_COMISSAO FROM TBMETA_FATURAMENTO MF WHERE MF.META_ID = Z.META_ID
    AND ROUND(VALOR_FATURAMENTO / VALOR_META * 100.00,2) BETWEEN MF.PERCENTUAL_FATURAMENTO_INICIAL AND MF.PERCENTUAL_FATURAMENTO_FINAL),0.00) COMISSAO_META
FROM (

        SELECT
        REPRESENTANTE_ID,
        FAMILIA_ID,
        META_ID,
        VALOR_META,
        SUM(VALOR_TOTAL - VALOR_DESCONTO - VALOR_TROCA) AS VALOR_FATURAMENTO
        FROM (
         
                SELECT
                X.REPRESENTANTE_ID,
                X.DATA_EMISSAO,
                SUM(X.VALOR_TOTAL) AS VALOR_TOTAL,
                SUM(X.VALOR_DESCONTO) AS VALOR_DESCONTO,
                COALESCE(
                          IIF(TIPO_DOCUMENTO='NF',
                                        (SELECT SUM(P.VALOR) 
                                           FROM TBNFS S, TBNFS_PARCELAMENTO P, TBPAGAMENTO_FORMA F
                                          WHERE S.CONTROLE = P.NFS_CONTROLE
                                            AND F.CODIGO = P.PAGAMENTO_FORMA
                                            AND COALESCE(F.PARCELAS_SEM_SALDO,'0') = '1'
                                            AND F.PDV_TIPO <> 7 / <> BRNDE | = CREDITO LOJA /
                                            AND S.CONTROLE = X.DOCUMENTO_ID) ,
                          IIF(TIPO_DOCUMENTO='CF',
                                        (SELECT SUM(P.VALOR) 
                                           FROM TBPDV_CFE A, TBPDV_CFE_FORMA_PAGAMENTO B, TBNFS_PARCELAMENTO P, TBPAGAMENTO_FORMA F
                                          WHERE A.ID = B.PDV_CFE_ID
                                            AND B.ID = P.PDV_CFE_FORMA_PAGAMENTO_ID
                                            AND F.CODIGO = P.PAGAMENTO_FORMA
                                            AND COALESCE(F.PARCELAS_SEM_SALDO,'0') = '1'
                                            AND F.PDV_TIPO <> 7 / <> BRNDE | = CREDITO LOJA /
                                            AND A.ID = X.DOCUMENTO_ID) , 0.00)) , 0.00) VALOR_TROCA,
                M.VALOR AS VALOR_META,
                M.ID AS META_ID,
                M.FAMILIA_ID
        
              FROM (
                    SELECT
                         N.REPRESENTANTE_CODIGO AS REPRESENTANTE_ID,
                         B.MFE_ITEM_ID AS ID,
                         N.DATA_EMISSAO,
                         CAST(B.QUANTIDADE * B.VALOR_UNITARIO AS NUMERIC(15,2)) VALOR_TOTAL,
                         CAST(B.VALOR_DESCONTO AS NUMERIC(15,2)) VALOR_DESCONTO,
                         N.MFE_ID AS DOCUMENTO_ID,
                         'CF' AS TIPO_DOCUMENTO,
                         P.FAMILIA_ALTERNATIVA AS FAMILIA_ID
                    FROM VWMFE N,
                         VWMFE_ITEM B,
                         TBOPERACAO O,
                         TBPRODUTO  P
                    WHERE N.MFE_ID = B.MFE_ID 
                      AND N.NATUREZA = '1'
                      AND N.SITUACAO = '2'
                      AND B.OPERACAO_CODIGO = O.CODIGO
                      AND P.CODIGO = B.PRODUTO_CODIGO
                      AND O.CONTROLE_FATURAMENTO = '1'
                      AND COALESCE(B.BRINDE,0) = 0
                      AND N.DATA_EMISSAO BETWEEN :DATA_INICIAL AND :DATA_FINAL
        
                    UNION

                    SELECT
                         N.REPRESENTANTE_CODIGO AS REPRESENTANTE_ID,
                         B.CONTROLE AS ID,
                         N.DATA_EMISSAO,
                         CAST(B.QUANTIDADE * (B.VALOR_UNITARIO + B.MARGEM) AS NUMERIC(15,2)) VALOR_TOTAL,
                         CAST( (B.VALOR_DESCONTO + COALESCE(B.MARGEM_DESCONTO,0.00) + COALESCE(B.VALOR_SUFRAMA,0.00) ) AS NUMERIC(15,2)) VALOR_DESCONTO,
                         N.CONTROLE AS DOCUMENTO_ID,
                         'NF' AS TIPO_DOCUMENTO,
                         P.FAMILIA_ALTERNATIVA AS FAMILIA_ID
                    FROM TBNFS N,
                         TBNFS_ITEM B,
                         TBOPERACAO O,
                         TBPRODUTO  P
                    WHERE  N.CONTROLE       = B.NFS_CONTROLE
                      AND  B.OPERACAO_CODIGO = O.CODIGO
                      AND  O.CONTROLE_FATURAMENTO = '1'
                      AND  P.CODIGO = B.PRODUTO_CODIGO 
                      AND ((N.NATUREZA = '1' AND N.SITUACAO = '2') OR
                           (N.NATUREZA = '2' AND N.SITUACAO = '1'))
                      AND N.DATA_EMISSAO BETWEEN :DATA_INICIAL AND :DATA_FINAL

                    ) X, TBMETA M
                    WHERE X.REPRESENTANTE_ID = M.REPRESENTANTE_ID
                      AND X.DATA_EMISSAO BETWEEN M.DATA_INICIO AND M.DATA_FIM
                      AND X.FAMILIA_ID = M.FAMILIA_ID
                      AND M.MES = CAST(EXTRACT(MONTH FROM X.DATA_EMISSAO) AS INTEGER)
                      AND M.ANO = CAST(EXTRACT(YEAR  FROM X.DATA_EMISSAO) AS INTEGER)
                      AND M.VALOR > 0
        
                GROUP BY
                REPRESENTANTE_ID,
                DATA_EMISSAO,
                DOCUMENTO_ID,
                TIPO_DOCUMENTO,
                M.VALOR,
                M.ID,
                M.FAMILIA_ID
             )
        
        GROUP BY REPRESENTANTE_ID,META_ID,VALOR_META,FAMILIA_ID
      ) Z
INTO :REPRESENTANTE_ID,:FAMILIA_ID,:VALOR_FATURAMENTO,:VALOR_META,:PERCENTUAL_ATINGIDO,:COMISSAO_META
DO
  SUSPEND;
END;