select
--f.XML_ITEM,
--f.ID_ITEM,
'coalesce((select first 1 s.controle from TBNFS s where s.numero_notafiscal = '||f.numero_nota||' and s.empresa_codigo = '||f.EMPRESA_CODIGO||' and s.data_emissao = '''||FN_DATA(f.data)||'''),0)'                                   NFS_CONTROLE,
f.cod_filial                        ESTABELECIMENTO_CODIGO,
f.EMPRESA_CODIGO                    EMPRESA_CODIGO,
f.numero_nota                       NUMERO_NOTAFISCAL,
FN_DATA(f.data)                     DATA_EMISSAO,
2                                   NF_SITUACAO,
1                                   NF_NATUREZA,
0                                   NF_CONSIGNACAO,
0                                   NF_CONSIGNACAO_ITEM,
0                                   NFE,
0                                   NFE_ITEM_CONTROLE,
0                                   PEDIDO,
0                                   PEDIDO_ITEM_PE_CONTROLE,
0                                   NF_EXTERNO,
0                                   NF_EXTERNO_ITEM_CONTROLE,
f.ID_ITEM                           SEQUENCIA,
--f.cod_operacao                      OPERACAO_CODIGO,
FN_CHAVE_XML('CFOP',f.XML_ITEM,0)     CFOP_CODIGO,
0                               PRODUTO_CODIGO,
substring(FN_CHAVE_XML('xProd',f.XML_ITEM,0) from 1 for 100) PRODUTO_DESCRICAO,
FN_CHAVE_XML('uCom',f.XML_ITEM,0)     PRODUTO_UNIDADEMEDIDA,
FN_CHAVE_XML('NCM',f.XML_ITEM,0)      NCM_CODIGO,
                            --      TRIBUTACAO_ORIGEM,
                            --      TRIBUTACAO_IPI,
                            --      TRIBUTACAO_ICMS,
                           --       TRIBUTACAO_PIS,
                            --      TRIBUTACAO_COFINS,
                            --      LOCALIZACAO_CODIGO,
FN_CHAVE_XML('qCom',f.XML_ITEM,0)     QUANTIDADE,
FN_CHAVE_XML('vUnCom',f.XML_ITEM,0)   VALOR_UNITARIO,
--e.preco * e.quantidade            VALOR_TOTAL,
FN_CHAVE_XML('ICMS.vBC',f.XML_ITEM,0) BASECALCULO_ICMS,
FN_CHAVE_XML('ICMS.pICMS',f.XML_ITEM,0) PERCENTUAL_ICMS,
FN_CHAVE_XML('IPI.pIPI',f.XML_ITEM,0) PERCENTUAL_IPI,
FN_CHAVE_XML('IPI.vIPI',f.XML_ITEM,0) VALOR_IPI,
FN_CHAVE_XML('PIS.pPIS',f.XML_ITEM,0) PERCENTUAL_PIS,
FN_CHAVE_XML('PIS.vPIS',f.XML_ITEM,0) VALOR_PIS,
FN_CHAVE_XML('COFINS.pCOFINS',f.XML_ITEM,0) PERCENTUAL_COFINS,
FN_CHAVE_XML('COFINS.vCOFINS',f.XML_ITEM,0) VALOR_COFINS,
iif(f.ptamanho = 1, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T01,
iif(f.ptamanho = 2, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T02,
iif(f.ptamanho = 3, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T03,
iif(f.ptamanho = 4, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T04,
iif(f.ptamanho = 5, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T05,
iif(f.ptamanho = 6, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T06,
iif(f.ptamanho = 7, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T07,
iif(f.ptamanho = 8, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T08,
iif(f.ptamanho = 9, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T09,
iif(f.ptamanho =10, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T10,
iif(f.ptamanho =11, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T11,
iif(f.ptamanho =12, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T12,
iif(f.ptamanho =13, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T13,
iif(f.ptamanho =14, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T14,
iif(f.ptamanho =15, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T15,
iif(f.ptamanho =16, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T16,
iif(f.ptamanho =17, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T17,
iif(f.ptamanho =18, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T18,
iif(f.ptamanho =19, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T19,
iif(f.ptamanho =20, FN_CHAVE_XML('qCom',f.XML_ITEM,0), 0) T20,
0                                     TECELAGEM,
FN_CHAVE_XML('ICMS.vICMS',f.XML_ITEM,0) VALOR_ICMS,
FN_CHAVE_XML('IPI.vBC',f.XML_ITEM,0)    BASECALCULO_IPI,
FN_CHAVE_XML('PIS.vBC',f.XML_ITEM,0)    BASECALCULO_PIS,
FN_CHAVE_XML('COFINS.vBC',f.XML_ITEM,0) BASECALCULO_COFINS,
0                       VALOR_ACRESCIMO,
0                       VALOR_FRETE,
0                       VALOR_SEGURO,
0                       PESO_LIQUIDO,
0                       PESO_BRUTO,
0                       CUBAGEM,
0                       VOLUMES,
0                       VALOR_DESCONTO,
                                    --FISCAL_ISENTAS_IPI,
                                    --FISCAL_ISENTAS_ICMS,
                                    --FISCAL_OUTRAS_IPI,
                                    --FISCAL_OUTRAS_ICMS,
FN_CHAVE_XML('ICMS.vICMS',f.XML_ITEM,0) FISCAL_VALOR_ICMS,
FN_CHAVE_XML('IPI.vIPI',f.XML_ITEM,0) FISCAL_VALOR_IPI,
FN_CHAVE_XML('PIS.vPIS',f.XML_ITEM,0) FISCAL_VALOR_PIS,
FN_CHAVE_XML('COFINS.vCOFINS',f.XML_ITEM,0) FISCAL_VALOR_COFINS,
FN_CHAVE_XML('ICMS.pICMS',f.XML_ITEM,0) FISCAL_PERCENTUAL_ICMS,
FN_CHAVE_XML('IPI.pIPI',f.XML_ITEM,0) FISCAL_PERCENTUAL_IPI,
FN_CHAVE_XML('PIS.pPIS',f.XML_ITEM,0) FISCAL_PERCENTUAL_PIS,
FN_CHAVE_XML('COFINS.pCOFINS',f.XML_ITEM,0) FISCAL_PERCENTUAL_COFINS,
FN_CHAVE_XML('ICMS.vBC',f.XML_ITEM,0) FISCAL_BASECALCULO_ICMS,
FN_CHAVE_XML('IPI.vBC',f.XML_ITEM,0) FISCAL_BASECALCULO_IPI,
FN_CHAVE_XML('PIS.vBC',f.XML_ITEM,0) FISCAL_BASECALCULO_PIS,
FN_CHAVE_XML('COFINS.vBC',f.XML_ITEM,0) FISCAL_BASECALCULO_COFINS,
                                  /*  VALOR_CONTABIL,
                                    VALOR_BASECALCULO_ICMS,
                                    FISCAL_CFOP_CODIGO,
                                    FISCAL_TRIBUTACAO_ORIGEM,
                                    FISCAL_TRIBUTACAO_ICMS,
                                    FISCAL_TRIBUTACAO_IPI,
                                    FISCAL_TRIBUTACAO_PIS,
                                    FISCAL_TRIBUTACAO_COFINS,
                                    MODALIDADE_ICMS,
                                    MODALIDADE_ICMS_ST,
                                    MARGEM_ICMS_ST,
                                    REDUCAO_ICMS_ST,
                                    BASECALCULO_ICMS_ST,
                                    ALIQUOTA_ICMS_ST,
                                    VALOR_ICMS_ST,
                                    FISCAL_REDUCAO_ICMS_ST,
                                    FISCAL_BASECALCULO_ICMS_ST,
                                    FISCAL_ALIQUOTA_ICMS_ST,
                                    FISCAL_VALOR_ICMS_ST,
                                    PEDIDO_CLIENTE,
                                    INFORMACOES_COMPLEMENTARES,
                                    FCI_MENSAGEM,
                                    FCI_PARCELA_IMPORTACAO,
                                    FCI_CONTEUDO_IMPORTACAO,
                                    FCI_NUMERO,
                                    CCUSTO,
                                    CONTA_CONTABIL,
                                    ENQUADRAMENTO_IPI,
                                    QUANTIDADE_ALTERNATIVA,
                                    UM_ALTERNATIVA,
                                    VALOR_ALTERNATIVO,
                                    REDUCAO_BASE_PISCOF,
                                    TIPO_DESCONTO,
                                    GC_WEB,
                                    MARGEM,
                                    PDV_CONFERENCIA_ITEM_ID,
                                    PDV_CONFERENCIA_ID,
                                    PERCENTUAL_SUFRAMA,
                                    VALOR_SUFRAMA,
                                    PERCENTUAL_ICMS_OPERACAO*/
--f.XML_ITEM
MODELO_ID,
COR_ID,
1 imp

from(
    select
        fn_tamanho(y.codigo, f.tamanho) as ptamanho,
        p.id as MODELO_ID,
        l.id as COR_ID,
        f.*,
        iif(PROX is null,
            substring(f.ITENS from (position('<det nItem="'||ID_ITEM||'">' in f.ITENS) + char_length('<det nItem="'||ID_ITEM||'">')) for (char_length(f.ITENS) - 6)),
           substring(f.ITENS from (position('<det nItem="'||ID_ITEM||'">' in f.ITENS) + char_length('<det nItem="'||ID_ITEM||'">')) for (position('<det nItem="'||PROX||'">' in f.ITENS) - (position('<det nItem="'||ID_ITEM||'">' in f.ITENS) + char_length('<det nItem="'||ID_ITEM||'">'))))
        ) as XML_ITEM
    from(
        select

            lead(ID_ITEM) over(partition by f.nota,  f.cod_operacao order by f.nota,  f.cod_operacao, ID_ITEM) as PROX,
            f.*
        from(
            select
                coalesce( E.id,0) AS EMPRESA_CODIGO,
                cast(replace(replace(replace(substring(ITENS from (position(cod_nota in ITENS) - 18) for 5), '"',''), '>',''), '=','') as integer) as ID_ITEM,
                substring(ITENS from (position(cod_nota in ITENS) - 18) for 5) as BB,
                f.*
            from(
                select
                    a.cod_filial,
                    e.cod_nota,
                    e.tamanho,
                    e.produto,
                    e.cor,
                    FN_REMOVER(coalesce(FN_CHAVE_XML('dest.CPF',D.conteudo), FN_CHAVE_XML('dest.CNPJ',D.conteudo)))   EMPRESA_CNPJ,
                    f.*,
                    d.conteudo as XML,
                    substring(D.conteudo from position('<det nItem="1' in D.conteudo) for (position('<total>' in D.conteudo) - position('<det nItem="1' in D.conteudo))) as ITENS
                from PRODUTOS_EVENTOS e, nf f
                left join FILIAIS a on a.filial = f.filial
                LEFT JOIN DOCUMENTOS D ON D.nota = F.nota AND D.conteudo IS NOT NULL AND D.idnota = F.id
                
                where f.cancelada = 'F'
                and f.tipo_operacao = 'S'
                --and f.numero_nota = '23996'
                and f.status = '100'
                and f.tipo_gerador_ig = 'C'
                and f.data between '01.08.2021' and '31.08.2021'
                and e.nota = f.nota
                and e.cod_operacao_ref = f.cod_operacao
            ) f
            LEFT JOIN CLIENTES E ON E.cnpj2 = f.EMPRESA_CNPJ

        )  F order by f.nota,  f.cod_operacao, ID_ITEM
    ) f,

    produtos p, cores l, grades g
    left join TBGRADE_FIXA y on y.codigo = g.cod_gradei
    where f.produto = p.produto
    and g.grade = p.grade
    and f.cor = l.cor

)  F