select

    'update or insert into TBPEDIDO ( ESTABELECIMENTO_CODIGO, PEDIDO, PEDIDO_CLIENTE, PEDIDO_INTEGRACAO_ID, DATA, DATA_CLIENTE, CLIENTE_CODIGO, FRETE, VALOR_FRETE, PAGAMENTO_FORMA, PAGAMENTO_CONDICAO, FAMILIA_CODIGO, OBSERVACAO, TIPO, VENDEDOR_ID, USUARIO_CODIGO, FORMA_ANALISE, CLASSIFICACAO, DESCONTO1, SITUACAO, TRANSPORTADORA_CODIGO, ECOMMERCE, CLIENTE_TIPO_ID, CLIENTE_SUB_TIPO_ID, ORIGEM_PRECO_ID, REPRESENTANTE_CODIGO , REPRESENTANTE_COMISSAO) VALUES ('||
        1||','||
        coalesce(a.id,'0')||','''||
        coalesce(a.cod_pedidov,'0')||''','||
        coalesce(a.id,'0')||','''||
        coalesce(a.DATA_EMISSAO,'')||''','''||
        coalesce(a.DATA_ENTREGA,'')||''','||
        coalesce(a.cliente_id,'0')||','||
        iif(a.CIF_FOB = 'C',0,1)||','||
        0||','||
        0||','||
        coalesce(a.cond_pag,'0')||','||
        327||','''||
        ''||''','||
        1||','||
        0||','||
        5||','||
        0||',''PEDIDO'','||
        0||','||
        1||','||
        coalesce(a.transportadora_id,'0')||','||
        0||','||
        1||','||
        1||','||
        1||','||
        a.representante_id||','||
        coalesce(a.COMISSAO_R,'0')||
    ') matching (ESTABELECIMENTO_CODIGO, PEDIDO);' as sql

from(
    select

        
        (select
            sum(coalesce(p.quantidade,0))
        from PRODUTO_PEDIDOV p where p.pedidov = v.pedidov) as QTD_PEDIDO,
    

        coalesce((select
            sum(coalesce(p.quantidade,0)) -
            sum(coalesce(p.entregue,0))
        from PRODUTO_PEDIDOV p where p.pedidov = v.pedidov),0) as QTD_ENTREGAR,

        (coalesce((select
            sum(coalesce(p.entregue,0))
        from PRODUTO_PEDIDOV p where p.pedidov = v.pedidov),0)) as QTD_ENTREGUE,
        j.cod_filial,
        i.id as cliente_id,
        iif(i2.id > 0, i2.id, coalesce(cast(f.id + 30000 as integer),0)) as transportadora_id,
        iif(i3.id > 0, i3.id, coalesce(cast(g.id + 10000 as integer),0)) as representante_id,
        --v.cod_pedidov,
        v.*,
        r.id as cond_pag
    
    from PEDIDO_VENDA v
    left join clientes i        on i.cliente            = v.cliente
    left join transportadoras f on f.transportadora     = v.transportadora
    left join representantes  g on g.representante      = v.representante
    left join CLIENTES i2       on i2.cpfcnpj           = f.cpfcnpj and i2.cpfcnpj is not null
    left join CLIENTES i3       on i3.cpfcnpj           = g.cpfcnpj and i3.cpfcnpj is not null


    left join filiais  j        on j.filial                 = v.filial
    left join CONDICOES_PGTO  r on r.condicoes_pgto         = v.condicoes_pgto
    where  v.cod_pedidov in (
        '00103187',
        '00103498',
    '00103499',
    '00103500',
    '00103501',
    '00103502',
    '00103518',
    '00103524',
    '00103525',
    '00103529',
    '00103534',
    '00103537',
    '00103540',
    '00103542',
    '00103544',
    '00103546',
    '00103548',
    '00103554',
    '00103555',
    '00103556',
    '00103557',
    '00103558',
    '00103582',
    '00103583',
    '00103605',
    '00103612',
    '00103615',
    '00103624',
    '00103656',
    '00103674',
    '00103677',
    '00103691',
    '00103692',
    '00103694',
    '00103701',
    '00103703',
    '00103704',
    '00103708',
    '00103712',
    '00103723',
    '00103728',
    '00103731',
    '00103732',
    '00103733',
    '00103734',
    '00103735',
    '00103736',
    '00103737',
    '00103738',
    '00103739',
    '00103740',
    '00103742',
    '00103744',
    '00103745',
    '00103747',
    '00103748',
    '00103749',
    '00103750',
    '00103751',
    '00103752',
    '00103753',
    '00103755',
    '00103757',
    '00103769',
    'K9211-1',
    'K9274-2',
    'K9274-3',
    'K9369-1',
    'K9373-1',
    'K9449-2',
    'K9461-1',
    'K9461-3',
    'K9483-1',
    'K9483-2',
    'K9491-4',
    'K9491-6',
    'K9496-2',
    'K9496-3',
    'K9507-3',
    'K9517-1',
    'K9528-1',
    'K9528-2',
    'K9530-1',
    'K9531-1',
    'K9532-1',
    'K9532-3',
    'K9532-4',
    'K9535-2',
    'K9543-2',
    'K9543-3',
    'K9551-1',
    'K9553-1',
    'K9553-3',
    'K9555-1',
    'K9574-1',
    'K9574-2',
    'K9577-2',
    'K9577-4',
    'K9578-2',
    'K9579-2',
    'K9581-1',
    'K9589-1',
    'K9590-1',
    'K9594-1',
    'K9600-2',
    'K9601-1',
    'K9601-2',
    'K9605-1',
    'K9608-2',
    'K9612-1',
    'K9612-3',
    'K9613-1',
    'K9613-2',
    'K9614-1',
    'K9614-2',
    'K9614-3',
    'K9614-4',
    'K9614-5',
    'K9614-6',
    'K9614-7',
    'K9614-9',
    'K9615-1',
    'K9615-2',
    'K9615-3',
    'K9615-4',
    'K9615-5',
    'K9617-1',
    'K9620-1',
    'K9620-2',
    'K9620-3',
    'K9620-4',
    'K9623-1',
    'K9628-1',
    'K9630-1',
    'K9630-2',
    'K9631-1',
    'K9631-10',
    'K9631-11',
    'K9631-12',
    'K9631-2',
    'K9631-3',
    'K9631-4',
    'K9631-5',
    'K9631-6',
    'K9631-7',
    'K9631-8',
    'K9631-9',
    'K9632-1',
    'K9633-1',
    'K9636-1',
    'K9638-2',
    'K9640-1',
    'K9640-2',
    'K9640-3',
    'K9643-1',
    'K9643-2',
    'K9646-1',
    'K9646-2',
    'K9646-3',
    'K9646-4',
    'K9646-5',
    'K9646-6',
    'K9646-7',
    'K9649-2',
    'K9651-1',
    'K9653-1',
    'K9654-1',
    'VL450',
    'VL452',
    'VL460',
    'VL462',
    'VL464'
)

) a --where ENTREGUE3 > 0
