SQL - Títulos a receber para vendas | Portal de Documentação

Aviso: Este artigo ainda é um rascunho!

Tratar duplicidades via SUBSER

select * from tt_trc where (CODFIL,CODSER,NUMNOT) in (select CODFIL,CODSER,NUMNOT from tt_trc where flgest <> 'C' group by CODFIL,CODSER,NUMNOT having count(*) > 1)

Migrar títulos a receber e suas parcelas (TT_TRC/TT_PTR) para TT_VEN/TT_REC

INSERT INTO TT_VEN (CODFIL, NUMECF, CODSER, NUMNOT, FILCLI, CODCLI, DATHOR, FLGEST, ATU_EM, FILUSU, CODUSU, FILVEN, CODVEN, FILPGT, CODPGT, VLRNOT, VLRLIQ, VINCUL, COMSOB, CODNAT, OBSERV, UFLOJA, FLGTIP, PERCOM, PERENT, PRAMED, PROPGT, NROIMP, DESCON, FILFAT, ENDFAT, FILENT, ENDENT, FILCOB, ENDCOB, FILGUI, CODGUI, FINANC, TRIBUT, LANVEN, TIPNOT, BASUBS, SUBSTT, RETIDO, CONSFI, FILPRE, CODPRE, DUMMY) (select codfil,'001', case when codser = ' 1' then '01' when codser = ' 2' then '02' when codser = ' 3' then '03' else codser end, SUBSTR(numnot,3,8), filpar, codpar, datope, flgest, atu_em, filusu, codusu, filusu, codusu, '001', ' 1',vlrtot, vlrliq, 'F', 0, -3, 'null', 'T', 0, 0, 0, 0, agora(), 0,0, null,null,null,null,null,null,null,null,'T','T', 'T','A',0, 0, 0,'T', '001', 1,'I' FROM TT_TRC WHERE FLGEST NOT IN ('C','Q'))

INSERT INTO TT_VEN (CODFIL, NUMECF, CODSER, NUMNOT, FILCLI, CODCLI, DATHOR, FLGEST, ATU_EM, FILUSU, CODUSU, FILVEN, CODVEN, FILPGT, CODPGT, VLRNOT, VLRLIQ, VINCUL, COMSOB, CODNAT, OBSERV, UFLOJA, FLGTIP, PERCOM, PERENT, PRAMED, PROPGT, NROIMP, DESCON, FILFAT, ENDFAT, FILENT, ENDENT, FILCOB, ENDCOB, FILGUI, CODGUI, FINANC, TRIBUT, LANVEN, TIPNOT, BASUBS, SUBSTT, RETIDO, CONSFI, FILPRE, CODPRE, DUMMY) (select codfil,'000', case when codser = ' 1' then '04' when codser = ' 2' then '02' when codser = ' 3' then '03' else codser end, SUBSTR(numnot,3,8), filpar, codpar, datope, flgest, atu_em, filusu, codusu, filusu, codusu, '001', ' 1',vlrtot, vlrliq, 'F', 0, -3, 'null', 'T', 0, 0, 0, 0, agora(), 0,0, null,null,null,null,null,null,null,null,'T','T', 'T','A',0, 0, 0,'T', '001', 1,'I' FROM TT_TRC WHERE FLGEST NOT IN ('C','Q'))

SELECT (select SEQUEN from tt_ven ven where VEN.CODFIL = TRC.CODFIL AND VEN.NUMECF = '001' AND VEN.CODSER = (case when codser = ' 1' then '01' when codser = ' 2' then '02' when codser = ' 3' then '03' else codser end) AND VEN.NUMNOT = SUBSTR(TRC.NUMNOT,3,8)) as SEQUEN, PTR.codfil, PTR.numpar, PTR.subpar, PTR.flgest, null, PTR.DATVEN, PTR.atu_em, null,null,
PTR.vlrpar, PTR.filusu, PTR.codusu, 0, 0, 1, null, '001', PTR.codfil, null, null FROM tt_trc trc inner join tt_ptr ptr on trc.codfil=ptr.codfil and trc.sequen=ptr.sequen where trc.flgest not in ('C','P')

INSERT INTO TT_REC (SEQUEN,CODFIL, NUMPAR, SUBPAR, FLGEST, DATVEN, ATU_EM, VLRPAR, FILUSU, CODUSU, PORTAD, CODMOE, SIGNAT, NUMECF) (SELECT (select SEQUEN from tt_ven ven where VEN.CODFIL = TRC.CODFIL AND VEN.NUMECF = '001' AND VEN.CODSER = (case when codser = ' 1' then '01' when codser = ' 2' then '02' when codser = ' 3' then '03' else codser end) AND VEN.NUMNOT = SUBSTR(TRC.NUMNOT,3,8)) as SEQUEN, PTR.codfil, PTR.numpar, PTR.subpar, PTR.flgest, PTR.DATVEN, PTR.atu_em, PTR.vlrpar, PTR.filusu, PTR.codusu, 0, 0, 1, '001' FROM tt_trc trc inner join tt_ptr ptr on trc.codfil=ptr.codfil and trc.sequen=ptr.sequen where trc.flgest not in ('C','Q') and ptr.flgest not in ('C','P'))

INSERT INTO TT_REC (SEQUEN,CODFIL, NUMPAR, SUBPAR, FLGEST, DATVEN, ATU_EM, VLRPAR, FILUSU, CODUSU, PORTAD, CODMOE, SIGNAT, NUMECF) (SELECT (select SEQUEN from tt_ven ven where VEN.CODFIL = TRC.CODFIL AND VEN.NUMECF = '000' AND VEN.CODSER = (case when TRC.codser = ' 1' then '04' when trc.codser = ' 2' then '02' when trc.codser = ' 3' then '03' else trc.codser end) AND VEN.NUMNOT = SUBSTR(TRC.NUMNOT,3,8)) as SEQUEN, PTR.codfil, PTR.numpar, PTR.subpar, PTR.flgest, PTR.DATVEN, PTR.atu_em, PTR.vlrpar, PTR.filusu, PTR.codusu, 0, 0, 1, '000' FROM tt_trc trc inner join tt_ptr ptr on trc.codfil=ptr.codfil and trc.sequen=ptr.sequen where trc.flgest not in ('C','Q') and ptr.flgest not in ('C','P'))