View de Pedidos

CREATE OR REPLACE VIEW VIASOFTVTRINA.V_VT_NOTAPEDIDO ("IDNOTA", "NUMDOC", "CHVNFE", "SERIE", "DTRECPROTAUT", "NPROTAUTORIZA", "XML", "IGNORA", "PEDCODIGO", "STATUS") AS 
  SELECT  
		N.IDNOTA,
		N.NUMDOC,
		N.CHVNFE,
		N.SERIE,
		N.DTRECPROTAUT,
		N.NPROTAUTORIZA,
		X.XML,
		WP.IGNORA,
		WP.PEDCODIGO,
		CASE WHEN COALESCE(N.IDNOTA,0) = 0 THEN 'Em Preparação' 
            WHEN (COALESCE(N.IDNOTA,0) > 0) AND (COALESCE(N.NPROTAUTORIZA,'0') = '0') THEN 'Em Processamento' 
            WHEN COALESCE(N.NPROTAUTORIZA,'0') <> '0' THEN 'Faturado' 
		END STATUS
    FROM VIASOFTMCP.WECPEDIDO WP
        LEFT JOIN VIASOFTBASE.NOTA N
            ON WP.IDNOTA = N.IDNOTA 
			AND N.ESTAB = WP.ESTAB
        LEFT JOIN VIASOFTBASE.NOTAXML X
            ON X.IDNOTA = N.IDNOTA 
			AND X.ESTAB = WP.ESTAB;

CREATE OR REPLACE VIEW VIASOFTVTRINA.V_VT_NOTAPEDIDOTRANSP ("IDNOTA", "NUMDOC", "CHVNFE", "SERIE", "DTRECPROTAUT", "NPROTAUTORIZA", "XML", "IGNORA", "PEDCODIGO", "STATUS") AS 
SELECT 
    NF.IDNOTA,
    NF.NUMDOC,
    NF.CHVNFE,
    NF.SERIE,
    NF.DTRECPROTAUT,
    NF.NPROTAUTORIZA,
    NX.XML,
    WP.IGNORA,
    WP.PEDCODIGO,
    CASE WHEN COALESCE(NF.IDNOTA,0) = 0 THEN 'Em Preparação' 
        WHEN (COALESCE(NF.IDNOTA,0) > 0) AND (COALESCE(NF.NPROTAUTORIZA,'0') = '0') THEN 'Em Processamento' 
        WHEN COALESCE(NF.NPROTAUTORIZA,'0') <> '0' THEN 'Faturado' 
    END STATUS
FROM VIASOFTMCP.WECPEDIDO WP
LEFT JOIN VIASOFTMCP.NOTAREFITE NR
    ON NR.IDNOTAREF = WP.IDNOTA
    AND NR.ESTAB = WP.ESTAB
LEFT JOIN VIASOFTBASE.NOTA NF
    ON NF.ESTAB = NR.ESTAB
    AND NF.IDNOTA = NR.IDNOTA
LEFT JOIN VIASOFTBASE.NOTAXML NX
    ON NX.ESTAB = NF.ESTAB
    AND NX.IDNOTA = NF.IDNOTA

CREATE OR REPLACE VIEW VIASOFTVTRINA.V_VT_PEDIDO ("CLICODIGO", "CLINOMERAZAOSOC", "CLICNPJF", "CLIEMAIL", "CLIENDERECO", "CLINUMERO", "CLIBAIRRO", "CLICOMPL", "CLICEP", "CLICIDADE", "CLIESTADO", "CLIPAIS", "CLITEL", "CLICEL", "PEDIDOSTATUS", "PEDATA", "PEDSUBTOTAL", "PEDFORMAENTREGA", "PEDFRETE", "PEDTIPOPAGTO", "PEDFORMAPAGTO", "PEDPARCELAS", "PEDTRANSACAOID", "ENTENDERECO", "ENTNUMERO", "ENTBAIRRO", "ENTCOMPL", "ENTCEP", "ENTCIDADE", "ENTESTADO", "ENTPAIS", "ENTTEL", "ENTCEL", "DATADOWNLOAD", "IDNOTA", "ESTAB", "PEDTIPOCC", "PEDTOTAL", "PEDCODIGO", "XMLPED", "CLIDTNASC", "CLICONCEITO", "CLIGRUPO", "CLISEXO", "OBS", "VENDEDOR", "TRANSP", "IDPARCELA", "PEDDESC", "MARKETPLACE", "PEDMARKET", "DESCACRES", "CLIINSCEST", "IGNORA", "USUARIOMOT", "DESCMOTIVO", "IDOUTROS", "IDPESSINTERMED", "CNPJINTERMED", "IDENTINTERMED", "NOMEINTERMED", "NOSSONUMERO", "IDPORTADOR", "BANCO", "IDCOBCAB", "IDCOBDET", "IDCARTAO", "ESTABPED") AS 
  SELECT 
		WP.CLICODIGO,
		WP.CLINOMERAZAOSOC,
		WP.CLICNPJF,
		WP.CLIEMAIL,
		WP.CLIENDERECO,
		WP.CLINUMERO,
		WP.CLIBAIRRO,
		WP.CLICOMPL,
		WP.CLICEP,
		WP.CLICIDADE,
		WP.CLIESTADO,
		WP.CLIPAIS,
		WP.CLITEL,
		WP.CLICEL,
		WP.PEDIDOSTATUS,
		WP.PEDATA,
		WP.PEDSUBTOTAL,
		WP.PEDFORMAENTREGA,
		WP.PEDFRETE,
		WP.PEDTIPOPAGTO,
		WP.PEDFORMAPAGTO,
		WP.PEDPARCELAS,
		WP.PEDTRANSACAOID,
		WP.ENTENDERECO,
		WP.ENTNUMERO,
		WP.ENTBAIRRO,
		WP.ENTCOMPL,
		WP.ENTCEP,
		WP.ENTCIDADE,
		WP.ENTESTADO,
		WP.ENTPAIS,
		WP.ENTTEL,
		WP.ENTCEL,
		WP.DATADOWNLOAD,
		WP.IDNOTA,
		WP.ESTAB,
		WP.PEDTIPOCC,
		WP.PEDTOTAL,
		WP.PEDCODIGO,
		WP.XMLPED,
		WP.CLIDTNASC,
		WP.CLICONCEITO,
		WP.CLIGRUPO,
		WP.CLISEXO,
		WP.OBS,
		WP.VENDEDOR,
		WP.TRANSP,
		WP.IDPARCELA,
		WP.PEDDESC,
		WP.MARKETPLACE,
		WP.PEDMARKET,
		WP.DESCACRES,
		WP.CLIINSCEST,
		WP.IGNORA,
		WP.USUARIOMOT,
		WP.DESCMOTIVO,
		WP.IDOUTROS,
		WP.IDPESSINTERMED,
		WP.CNPJINTERMED,
		WP.IDENTINTERMED,
		WP.NOMEINTERMED,
		WP.NOSSONUMERO,
		WP.IDPORTADOR,
		WP.BANCO,
		WP.IDCOBCAB,
		WP.IDCOBDET,
		WP.IDCARTAO,
		WP.ESTABPED 
	FROM VIASOFTMCP.WECPEDIDO WP;

CREATE OR REPLACE VIEW VIASOFTVTRINA.V_VT_ITEMPEDIDO ("IDITEM", "DESCRICAO", "SUBTOTAL", "QUANTIDADE", "DESCONTO", "VALORTOTAL", "SEQITEM", "PEDCODIGO", "XMLITEM", "IDLOCALRETIRADA", "IDLOCALRETENTREGA") AS 
  SELECT 
		IDITEM,
		DESCRICAO,
		SUBTOTAL,
		QUANTIDADE,
		DESCONTO,
		VALORTOTAL,
		SEQITEM,
		PEDCODIGO,
		XMLITEM,
		IDLOCALRETIRADA,
		IDLOCALRETENTREGA 
	FROM VIASOFTMCP.WECPEDIDOITEM;