update conf set confrelease = '13414';
------------------------------------------------------------------------------------------------

-- novos campos
alter table est add estqtdepedmin numeric (14,4);
?ALTER TABLE public.forn ADD for_freq_compra int4 DEFAULT 0 NULL;
ALTER TABLE public.forn ALTER COLUMN for_freq_compra SET STORAGE PLAIN;

-- view est
-- sd.vw_est fonte

CREATE OR REPLACE VIEW sd.vw_est
AS SELECT est.estprocod AS est_pro_cod,
    pro.prodes AS est_pro_des,
    pro.proref AS est_pro_ref,
    ( SELECT uve.uvedes
           FROM uve
          WHERE uve.uveprocod = pro.procod AND uve.uvetipo = 'P'::bpchar
         LIMIT 1) AS est_uve_des,
    ( SELECT uve.uveun
           FROM uve
          WHERE uve.uveprocod = pro.procod AND uve.uvetipo = 'P'::bpchar
         LIMIT 1) AS est_uve_un,
    ( SELECT uve.uvecod
           FROM uve
          WHERE uve.uveprocod = pro.procod AND uve.uvetipo = 'P'::bpchar
         LIMIT 1) AS est_uve_cod,
    est.estempparcod AS est_emp_cod,
    e.pardes AS est_emp_des,
    e.parfan AS est_emp_fan,
    round(est.estqtde, 4) AS est_qt,
    round(est.estqtdecont, 4) AS est_qtcont,
    round(est.estqtdebloq, 4) AS est_qtbloq,
    round(est.estqtderes, 4) AS est_qtres,
    round(est.estqtdediverg, 4) AS est_qtdiverg,
    round(est.estqtdepend, 4) AS est_qtpend,
    round(COALESCE(est.estqtde, 0::numeric) - COALESCE(est.estqtdebloq, 0::numeric) - COALESCE(est.estqtderes, 0::numeric) - COALESCE(est.estqtdediverg, 0::numeric) - COALESCE(est.estqtdepend, 0::numeric), 4) AS est_qtdisp,
    round(est.estqtdecompra, 4) AS est_qtcompra,
    est.estqtdemin AS est_qtmin,
    round(est.estcustocompra, 4) AS est_custocompra,
    round(est.estcustoreal, 4) AS est_custoreal,
    round(est.estcustoreal + pr.vlvenda * pr.stp_cmv / 100::numeric, 4) AS est_cmv,
    round(est.estcustoreal + pr.vlvenda * pr.stp / 100::numeric, 4) AS est_pmz,
    round(pr.vlvenda, 4) AS est_vl,
    round(
        CASE
            WHEN est.estcustoreal > 0::numeric THEN (pr.vlvenda - est.estcustoreal) / est.estcustoreal * 100::numeric
            ELSE 0.0
        END, 2) AS est_markup,
    round(
        CASE
            WHEN pr.vlvenda > 0::numeric THEN (pr.vlvenda - round(est.estcustoreal + pr.vlvenda * pr.stp / 100::numeric, 4)) / pr.vlvenda * 100::numeric
            ELSE 0::numeric
        END, 2) AS est_marg,
    pr.indicest AS est_indicest,
    COALESCE(round(pr.vlvenda * (1::numeric + pr.indicest), 4), 0::numeric) AS est_vlvenst,
    COALESCE(round(pr.vlvenda * (1::numeric + pr.indicest) - pr.vlvenda, 4), 0::numeric) AS est_vlst,
    COALESCE(round(pr.vlvenda * est.estaliqipi / 100::numeric, 4), 0::numeric) AS est_vlipi,
    round(
        CASE
            WHEN ven.numdiatrim > 0 THEN ven.qtgiro / ven.numdiatrim::numeric
            ELSE 0.0
        END, 2) AS est_girodia,
    round(
        CASE
            WHEN
            CASE
                WHEN ven.numdiatrim > 0 THEN ven.qtgiro / ven.numdiatrim::numeric
                ELSE 0.0
            END > 0::numeric THEN COALESCE(est.estqtde, 0::numeric) /
            CASE
                WHEN ven.numdiatrim > 0 THEN ven.qtgiro / ven.numdiatrim::numeric
                ELSE 0.0
            END
            ELSE 0.0
        END, 2) AS est_duracao,
    round(
        CASE
            WHEN
            CASE
                WHEN ven.numdiatrim > 0 THEN ven.qtgiro / ven.numdiatrim::numeric
                ELSE 0.0
            END > 0::numeric THEN (COALESCE(est.estqtde, 0::numeric) - COALESCE(est.estqtdebloq, 0::numeric) - COALESCE(est.estqtderes, 0::numeric) - COALESCE(est.estqtdediverg, 0::numeric) - COALESCE(est.estqtdepend, 0::numeric)) /
            CASE
                WHEN ven.numdiatrim > 0 THEN ven.qtgiro / ven.numdiatrim::numeric
                ELSE 0.0
            END
            ELSE 0.0
        END, 2) AS est_duracaodisp,
    est.estdtultentr AS est_dtultentr,
    est.estqtdeultentr AS est_qtultentr,
    est.estvlnfultentr AS est_vlultentr,
    est.estnfultentr AS est_nfultentr,
    est.estforparcodultentr AS est_forcodultentr,
    ( SELECT fpe.fpeqtmaster
           FROM fpe
          WHERE fpe.fpetipo = 'P'::bpchar AND fpe.fpeprocod = est.estprocod AND fpe.fpeempparcod = est.estempparcod
          ORDER BY fpe.fpeforparcod DESC
         LIMIT 1) AS est_qtmaster,
    ( SELECT fpe.fpeforparcod
           FROM fpe
          WHERE fpe.fpetipo = 'P'::bpchar AND fpe.fpeprocod = est.estprocod AND fpe.fpeempparcod = est.estempparcod
          ORDER BY fpe.fpeforparcod DESC
         LIMIT 1) AS est_forn_cod,
    ( SELECT par.pardes
           FROM fpe
             JOIN par ON fpe.fpeforparcod = par.parcod
          WHERE fpe.fpetipo = 'P'::bpchar AND fpe.fpeprocod = est.estprocod AND fpe.fpeempparcod = est.estempparcod
          ORDER BY fpe.fpeforparcod DESC
         LIMIT 1) AS est_forn_des,
    ( SELECT par.parfan
           FROM fpe
             JOIN par ON fpe.fpeforparcod = par.parcod
          WHERE fpe.fpetipo = 'P'::bpchar AND fpe.fpeprocod = est.estprocod AND fpe.fpeempparcod = est.estempparcod
          ORDER BY fpe.fpeforparcod DESC
         LIMIT 1) AS est_forn_fan,
    depp.deppcod AS est_depp_cod,
    depp.deppdes AS est_depp_des,
    secp.secpcod AS est_secp_cod,
    secp.secpdes AS est_secp_des,
    cat.catcod AS est_cat_cod,
    cat.catdes AS est_cat_des,
    scat.scatcod AS est_scat_cod,
    scat.scatdes AS est_scat_des,
    marcas.marcascod AS est_marcas_cod,
    marcas.marcasdes AS est_marcas_des,
    fam.famcod AS est_fam_cod,
    fam.famdes AS est_fam_des,
    ce.cecod AS est_ce_cod,
    ce.cedes AS est_ce_des,
    app.appcod AS est_app_cod,
    app.appdes AS est_app_des,
    app.appsigla AS est_app_sigla,
    est.estdispven AS est_dispven,
    est.estdispfv AS est_dispfv,
        CASE
            WHEN btrim(est.estforalinha::text) = ''::text THEN 'N'::text
            WHEN est.estforalinha IS NULL THEN 'N'::text
            WHEN est.estforalinha = 'N'::bpchar THEN 'N'::text
            WHEN est.estforalinha = 'S'::bpchar THEN 'S'::text
            ELSE NULL::text
        END AS est_foralinha,
    est.estaprovcredicms AS est_aprovcredicms,
    0.0 AS est_cmvvldespvenda,
    0.0 AS est_cmvvlimposto,
    est.estqtdepontoped AS est_qtpontoped,
    est.estqtdepedmin AS est_qtpedmin
   FROM pro
     JOIN est ON est.estprocod = pro.procod
     JOIN app ON est.estappcod = app.appcod
     JOIN par e ON est.estempparcod = e.parcod
     LEFT JOIN depp ON pro.prodeppcod = depp.deppcod
     LEFT JOIN secp ON pro.prosecpcod = secp.secpcod
     LEFT JOIN cat ON pro.procatcod = cat.catcod
     LEFT JOIN scat ON pro.proscatcod = scat.scatcod
     LEFT JOIN marcas ON pro.promarcascod = marcas.marcascod
     LEFT JOIN fam ON pro.profamcod = fam.famcod
     LEFT JOIN ce ON est.estcecod = ce.cecod
     LEFT JOIN ( SELECT vw_fat.fat_emp_cod,
            vw_fat.fat_pro_cod,
            sum(COALESCE(vw_fat.fat_qtmult, 0::numeric)) AS qtgiro,
            (( SELECT f_data.fdtultimodiames
                   FROM f_data(date(date(now()) - '1 mon'::interval)) f_data(fdtprimeirodiames, fdtultimodiames))) - (( SELECT f_data.fdtprimeirodiames
                   FROM f_data(date(date(now()) - '3 mons'::interval)) f_data(fdtprimeirodiames, fdtultimodiames))) AS numdiatrim
           FROM sd.vw_fat
          WHERE vw_fat.fat_nf_sta <> 'EX'::bpchar AND (vw_fat.fat_opnftipo_cod = ANY (ARRAY['VE'::bpchar, 'VI'::bpchar, 'VV'::bpchar])) AND vw_fat.fat_nf_dtemi >= (( SELECT f_data.fdtprimeirodiames
                   FROM f_data(date(date(now()) - '3 mons'::interval)) f_data(fdtprimeirodiames, fdtultimodiames))) AND vw_fat.fat_nf_dtemi <= (( SELECT f_data.fdtultimodiames
                   FROM f_data(date(date(now()) - '1 mon'::interval)) f_data(fdtprimeirodiames, fdtultimodiames)))
          GROUP BY vw_fat.fat_emp_cod, vw_fat.fat_pro_cod) ven ON est.estprocod = ven.fat_pro_cod AND est.estempparcod = ven.fat_emp_cod
     LEFT JOIN ( SELECT est_1.estempparcod,
            preco.precoprocod,
            gt.gtstaliqicms,
            gt.gtstpercredbc,
            gt.gtstiva,
            gt.gtaliqicms,
                CASE
                    WHEN (( SELECT plpg.plpgpercfin
                       FROM plpg
                      WHERE plpg.plpgtipo = 'P'::bpchar AND plpg.plpgcol = 1
                     LIMIT 1)) > 0::numeric THEN preco.precovlvendaatual * (( SELECT plpg.plpgpercfin
                       FROM plpg
                      WHERE plpg.plpgtipo = 'P'::bpchar AND plpg.plpgcol = 1
                     LIMIT 1))
                    ELSE preco.precovlvendaatual
                END AS vlvenda,
                CASE
                    WHEN gt.gtstaliqicms > 0::numeric THEN ((100::numeric - 100::numeric * COALESCE(gt.gtstpercredbc, 0::numeric) / 100::numeric + (100::numeric - 100::numeric * COALESCE(gt.gtstpercredbc, 0::numeric) / 100::numeric) / 100::numeric * gt.gtstiva) / 100::numeric * gt.gtstaliqicms - (100 / 100)::numeric * gt.gtaliqicms) / 100::numeric
                    ELSE 0.0
                END AS indicest,
            COALESCE(preco.precoperccom, 0::numeric) + COALESCE(lp.lppercfrete, 0::numeric) + COALESCE(preco.precoperccomercial, 0::numeric) + COALESCE(preco.precopercoutrasdespvenda, 0::numeric) + COALESCE(gt.gtaliqefet, 0::numeric) + COALESCE(cstpiscofins.cstpiscofins_aliq_pis, 0::numeric) + COALESCE(cstpiscofins.cstpiscofins_aliq_cofins, 0::numeric) + COALESCE(emp.emppercdespfixa, 0::numeric) + COALESCE(emp.emppercirpj, 0::numeric) + COALESCE(emp.empperccsll, 0::numeric) AS stp,
            COALESCE(preco.precoperccom, 0::numeric) + COALESCE(lp.lppercfrete, 0::numeric) + COALESCE(preco.precoperccomercial, 0::numeric) + COALESCE(preco.precopercoutrasdespvenda, 0::numeric) + COALESCE(gt.gtaliqefet, 0::numeric) + COALESCE(cstpiscofins.cstpiscofins_aliq_pis, 0::numeric) + COALESCE(cstpiscofins.cstpiscofins_aliq_cofins, 0::numeric) AS stp_cmv
           FROM pro pro_1
             JOIN est est_1 ON pro_1.procod = est_1.estprocod
             JOIN lp ON lp.lpcod = (( SELECT conf.conflpcod
                   FROM conf
                  WHERE conf.confempparcod = est_1.estempparcod))
             JOIN preco ON pro_1.procod = preco.precoprocod AND lp.lpcod = preco.precolpcod
             LEFT JOIN emp ON est_1.estempparcod = emp.empparcod
             LEFT JOIN regtrib ON emp.empregtribcod = regtrib.regtribcod
             LEFT JOIN tribpro ON regtrib.regtribcod = tribpro.tribproregtribcod AND tribpro.tribprouforig = (( SELECT mun.munufsigla
                   FROM par
                     JOIN mun ON par.parmuncod = mun.muncod
                  WHERE par.parcod = lp.lpempparcod)) AND tribpro.tribproufdest = lp.lpuf AND tribpro.tribproprocod = preco.precoprocod
             LEFT JOIN trib ON regtrib.regtribcod = trib.tribregtribcod AND trib.tribuforig = (( SELECT mun.munufsigla
                   FROM par
                     JOIN mun ON par.parmuncod = mun.muncod
                  WHERE par.parcod = lp.lpempparcod)) AND trib.tribufdest = lp.lpuf AND trib.tribncm = pro_1.proncm::text
             LEFT JOIN gt ON gt.gtcod = COALESCE(tribpro.tribprogtcod, trib.tribgtcod)
             LEFT JOIN cstpiscofins ON regtrib.regtribcod = cstpiscofins.cstpiscofins_regtribcod AND est_1.estcstpiscofinscod::text = cstpiscofins.cstpiscofins_cod::text) pr ON pro.procod = pr.precoprocod AND est.estempparcod = pr.estempparcod;

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------