Skip to content

Instantly share code, notes, and snippets.

@lucassmacedo

lucassmacedo/gist:785fb9853c41f995baa3af34ddf10495

Created May 7, 2026 12:16
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save lucassmacedo/785fb9853c41f995baa3af34ddf10495 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save lucassmacedo/785fb9853c41f995baa3af34ddf10495 to your computer and use it in GitHub Desktop.
Download ZIP
V_TITULOS_V2.SQL
Raw
gistfile1.sql
SELECT PCPREST.NUMTRANSVENDA || '/' || PCPREST.PREST AS codigo,
PCPREST.NUMTRANSVENDA,
--PCUSUARI.tipovend,
PCCLIENT.TIPOFJ AS tipoCliente,
REGEXP_REPLACE(PCCLIENT.CGCENT, '[^0-9]', '') AS documentoCliente,
'DISDAL' AS nomeOrigem,
NULL AS token,
DTEMISSAO,
PCPREST.DTVENC,
PCPREST.CODCOB,
JSON_OBJECT(
'tipoRepresentante' VALUE PCCLIENT.TIPOFJ,
'documentoRepresentante' VALUE REGEXP_REPLACE(DECODE(PCUSUARI.tipovend,
'J', PCUSUARI.CGC,
'F', PCUSUARI.CPF,
'E', PCUSUARI.CODUSUR
), '[^0-9]', ''),
'documentoTitulo' VALUE TO_CHAR(PCPREST.DUPLIC),
'dataEmissao' VALUE PCPREST.DTEMISSAO,
'dataVencimentoProrrogado' VALUE PCPREST.DTVENC,
'dataVencimento' VALUE PCPREST.DTVENC,
'motivoProrrogacao' VALUE NULL,
'parcela' VALUE TO_CHAR(PCPREST.PREST),
'valor' VALUE TO_NUMBER(PCPREST.VALOR),
'observacao' VALUE PCPREST.OBSTITULO,
'numeroBoleto' VALUE PCPREST.NOSSONUMBCO,
'observacao1' VALUE PCPREST.OBS,
'observacao2' VALUE PCPREST.OBS2,
'observacao3' VALUE NULL,
'tipoCedente' VALUE PCCLIENT.TIPOFJ,
'documentoCedente' VALUE REGEXP_REPLACE(PCCLIENT.CGCENT, '[^0-9]', ''),
'codigoMoeda' VALUE 'BRL'
) AS titulo,
JSON_ARRAY(
JSON_OBJECT(
'desassociar' VALUE NULL,
'codigo' VALUE PCPREST.NUMPED
)
) AS pedidosRelacionados,
JSON_ARRAY(
JSON_OBJECT(
'desassociar' VALUE NULL,
'documento' VALUE NULL,
'origem' VALUE NULL,
'tipoCliente' VALUE NULL,
'tipoRelacao' VALUE NULL
)
) AS clientesRelacionados,
JSON_ARRAY(
JSON_OBJECT(
'desassociar' VALUE NULL,
'codigoSistemaGestao' VALUE NULL,
'valor' VALUE NULL
)
) AS divisorLimite,
JSON_ARRAY(
JSON_OBJECT(
'desassociar' VALUE 'N',
'codigoSistemaGestao' VALUE 'COB-' || PCPREST.CODCOB,
'nomeCategoria' VALUE 'Cobranca',
'Nome' VALUE 'COB-' || PCPREST.CODCOB
),
JSON_OBJECT(
'desassociar' VALUE 'N',
'codigoSistemaGestao' VALUE 'FIL-' || PCPREST.CODFILIAL,
'nomeCategoria' VALUE 'Filial',
'Nome' VALUE PCFILIAL.RAZAOSOCIAL
)
) AS parametrizacoesCategorias,
JSON_ARRAY(
JSON_OBJECT(
'desassociar' VALUE NULL,
'numero' VALUE NULL,
'valorUtilizado' VALUE NULL
)
) AS cheques,
JSON_ARRAY(
JSON_OBJECT(
'desassociar' VALUE NULL,
'codigo' VALUE PCPREST.DUPLIC
)
) AS notas,
JSON_ARRAY(
JSON_OBJECT(
'desassociar' VALUE NULL,
'codigo' VALUE NULL
)
) AS notasComplementares,
JSON_ARRAY(
CASE
WHEN PCPREST.DTDEVOL IS NULL AND PCPREST.DTPAG IS NULL THEN NULL
ELSE JSON_OBJECT(
'codigoTipoBaixa' VALUE 'Pagamento',
'nomeTipoBaixa' VALUE 'Pagamento',
'documento' VALUE PCPREST.NUMTRANSVENDA || '/' || PCPREST.PREST,
'dataBaixa' VALUE CASE
WHEN PCPREST.DTDEVOL IS NOT NULL THEN PCPREST.DTDEVOL
WHEN PCPREST.DTPAG IS NOT NULL THEN PCPREST.DTPAG
ELSE NULL
END,
'valor' VALUE CASE
WHEN NVL(PCPREST.VPAGO, 0) > 0 THEN PCPREST.VPAGO
END
)
END
) AS movimentosBaixas,
JSON_ARRAY(
JSON_OBJECT(
'codigoTipoMovimento' VALUE 'Saldo',
'nomeTipoMovimento' VALUE 'Saldo',
'documento' VALUE PCPREST.NUMTRANSVENDA || '/' || PCPREST.PREST,
'dataMovimento' VALUE NVL(CASE
WHEN PCPREST.DTDEVOL IS NOT NULL THEN PCPREST.DTDEVOL
ELSE NVL(PCPREST.DTPAG, PCPREST.DTEMISSAO)
END
, SYSDATE),
'valor' VALUE CASE
WHEN NVL(PCPREST.VPAGO, 0) = 0 THEN TO_NUMBER(PCPREST.VALOR)
WHEN NVL(PCPREST.VALOR, 0) <= (NVL(PCPREST.VPAGO, 0) + NVL(PCPREST.TXPERM, 0) + NVL(PCPREST.VALORMULTA, 0) + NVL(PCPREST.VLROUTROSACRESC, 0) + NVL(PCPREST.VLRDESPBANCARIAS, 0) + NVL(PCPREST.VLRDESPCARTORAIS, 0) + NVL(PCPREST.VALORDESC, 0)) THEN 0
WHEN TO_NUMBER((NVL(PCPREST.VALOR, 0) + NVL(PCPREST.TXPERM, 0) + NVL(PCPREST.VALORMULTA, 0) + NVL(PCPREST.VLROUTROSACRESC, 0) + NVL(PCPREST.VLRDESPBANCARIAS, 0) + NVL(PCPREST.VLRDESPCARTORAIS, 0)) - NVL(PCPREST.VPAGO, 0) - NVL(PCPREST.VALORDESC, 0)) < 0 THEN 0
ELSE TO_NUMBER((NVL(PCPREST.VALOR, 0) + NVL(PCPREST.TXPERM, 0) + NVL(PCPREST.VALORMULTA, 0) + NVL(PCPREST.VLROUTROSACRESC, 0) + NVL(PCPREST.VLRDESPBANCARIAS, 0) + NVL(PCPREST.VLRDESPCARTORAIS, 0)) - NVL(PCPREST.VPAGO, 0) - NVL(PCPREST.VALORDESC, 0))
END
)
) AS movimentosGerais,
JSON_OBJECT(
'desassociar' VALUE NULL,
'codigoNegociacao' VALUE NULL,
'numeroParcela' VALUE NULL
) AS recuperacaoNegociacao,
PCPREST.DTULTALTER,
PCPREST.DTPAG
FROM PCPREST
JOIN PCCLIENT ON PCCLIENT.CODCLI = PCPREST.CODCLI
JOIN PCCOB ON PCCOB.CODCOB = PCCLIENT.CODCOB
JOIN PCUSUARI ON PCUSUARI.CODUSUR = PCCLIENT.CODUSUR1
JOIN PCFILIAL ON PCFILIAL.CODIGO = PCPREST.CODFILIAL
WHERE PCPREST.VALOR > 0
--AND PCPREST.CODCOB NOT IN ('DESD') -- comentado para enviar desdobramento 28/08/2024 - Flavio Borges - Reunião Disdal/Deps/Integra.do
AND SUBSTR(REPLACE(REPLACE(REPLACE(TRIM(REPLACE(PCCLIENT.CGCENT, ' ', '')), '-', ''), '.', ''), '/', ''), 1, 8) NOT IN ('00000000', '11111111', '22222222', '33333333', '44444444', '55555555', '66666666', '77777777', '88888888', '99999999')
UNION ALL
SELECT PCHISTPREST.NUMTRANSVENDA || '/' || PCHISTPREST.PREST AS codigo,
PCHISTPREST.NUMTRANSVENDA,
PCCLIENT.TIPOFJ AS tipoCliente,
REGEXP_REPLACE(PCCLIENT.CGCENT, '[^0-9]', '') AS documentoCliente,
'DISDAL' AS nomeOrigem,
NULL AS token,
PCHISTPREST.DTEMISSAO,
PCHISTPREST.DTVENC,
'CANC' AS CODCOB,
JSON_OBJECT(
'tipoRepresentante' VALUE PCCLIENT.TIPOFJ,
'documentoRepresentante' VALUE REGEXP_REPLACE(DECODE(PCUSUARI.tipovend,
'J', PCUSUARI.CGC,
'F', PCUSUARI.CPF,
'E', PCUSUARI.CODUSUR
), '[^0-9]', ''),
'documentoTitulo' VALUE TO_CHAR(PCHISTPREST.DUPLIC),
'dataEmissao' VALUE PCHISTPREST.DTEMISSAO,
'dataVencimentoProrrogado' VALUE PCHISTPREST.DTVENC,
'dataVencimento' VALUE PCHISTPREST.DTVENC,
'motivoProrrogacao' VALUE 'TITULO CANCELADO',
'parcela' VALUE TO_CHAR(PCHISTPREST.PREST),
'valor' VALUE TO_NUMBER(PCHISTPREST.VALOR),
'observacao' VALUE 'TITULO CANCELADO',
'numeroBoleto' VALUE NULL,
'observacao1' VALUE NULL,
'observacao2' VALUE NULL,
'observacao3' VALUE NULL,
'tipoCedente' VALUE PCCLIENT.TIPOFJ,
'documentoCedente' VALUE REGEXP_REPLACE(PCCLIENT.CGCENT, '[^0-9]', ''),
'codigoMoeda' VALUE 'BRL'
) AS titulo,
JSON_ARRAY(
JSON_OBJECT(
'desassociar' VALUE NULL,
'codigo' VALUE PCPEDC.NUMPED
)
) AS pedidosRelacionados,
JSON_ARRAY(
JSON_OBJECT(
'desassociar' VALUE NULL,
'documento' VALUE NULL,
'origem' VALUE NULL,
'tipoCliente' VALUE NULL,
'tipoRelacao' VALUE NULL
)
) AS clientesRelacionados,
JSON_ARRAY(
JSON_OBJECT(
'desassociar' VALUE NULL,
'codigoSistemaGestao' VALUE NULL,
'valor' VALUE NULL
)
) AS divisorLimite,
JSON_ARRAY(
JSON_OBJECT(
'desassociar' VALUE 'N',
'codigoSistemaGestao' VALUE 'COB-CANC',
'nomeCategoria' VALUE 'Cobranca',
'Nome' VALUE 'COB-CANC'
),
JSON_OBJECT(
'desassociar' VALUE 'N',
'codigoSistemaGestao' VALUE 'FIL-' || PCHISTPREST.CODFILIAL,
'nomeCategoria' VALUE 'Filial',
'Nome' VALUE PCFILIAL.RAZAOSOCIAL
)
) AS parametrizacoesCategorias,
JSON_ARRAY(
JSON_OBJECT(
'desassociar' VALUE NULL,
'numero' VALUE NULL,
'valorUtilizado' VALUE NULL
)
) AS cheques,
JSON_ARRAY(
JSON_OBJECT(
'desassociar' VALUE NULL,
'codigo' VALUE PCHISTPREST.DUPLIC
)
) AS notas,
JSON_ARRAY(
JSON_OBJECT(
'desassociar' VALUE NULL,
'codigo' VALUE NULL
)
) AS notasComplementares,
JSON_ARRAY(
JSON_OBJECT(
'codigoTipoBaixa' VALUE 'Cancelamento',
'nomeTipoBaixa' VALUE 'Cancelamento',
'documento' VALUE PCHISTPREST.NUMTRANSVENDA || '/' || PCHISTPREST.PREST,
'dataBaixa' VALUE PCHISTPREST.DTEMISSAO,
'valor' VALUE TO_NUMBER(PCHISTPREST.VALOR)
)
) AS movimentosBaixas,
JSON_ARRAY(
JSON_OBJECT(
'codigoTipoMovimento' VALUE 'Cancelamento',
'nomeTipoMovimento' VALUE 'Cancelamento',
'documento' VALUE PCHISTPREST.NUMTRANSVENDA || '/' || PCHISTPREST.PREST,
'dataMovimento' VALUE NVL(PCHISTPREST.DTEMISSAO, SYSDATE),
'valor' VALUE 0
)
) AS movimentosGerais,
JSON_OBJECT(
'desassociar' VALUE NULL,
'codigoNegociacao' VALUE NULL,
'numeroParcela' VALUE NULL
) AS recuperacaoNegociacao,
PCHISTPREST.DTEMISSAO,
NULL AS DTPAG
FROM PCHISTPREST
JOIN PCPEDC ON PCPEDC.NUMTRANSVENDA = PCHISTPREST.NUMTRANSVENDA
JOIN PCCLIENT ON PCCLIENT.CODCLI = PCHISTPREST.CODCLI
JOIN PCUSUARI ON PCUSUARI.CODUSUR = PCCLIENT.CODUSUR1
JOIN PCFILIAL ON PCFILIAL.CODIGO = PCHISTPREST.CODFILIAL
WHERE PCHISTPREST.VALOR > 0
AND SUBSTR(REPLACE(REPLACE(REPLACE(TRIM(REPLACE(PCCLIENT.CGCENT, ' ', '')), '-', ''), '.', ''), '/', ''), 1, 8)
NOT IN ('00000000', '11111111', '22222222', '33333333', '44444444',
'55555555', '66666666', '77777777', '88888888', '99999999')
AND NOT EXISTS (SELECT 1
FROM PCPREST R
WHERE R.NUMTRANSVENDA = PCHISTPREST.NUMTRANSVENDA
AND R.PREST = PCHISTPREST.PREST
AND R.DUPLIC = PCHISTPREST.DUPLIC)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment