Cliente Cheque Devolvido

SELECT TIPRES as "Tipo", RESTRI as "Restricao", SUM(DIVIDA) + 100 as "Divida", SUM(OCORRE) as "Ocorrencias", MIN(COALESCE(LIMITE,0)) as "Limite" FROM (-- Restrições originárias de cheques nos CPFs/CNPJs SELECT R2.TIPRES, R2.RESTRI, R2.DIVIDA, R2.OCORRE, R2.LIMITE FROM TV_RES_002_CK4G R2, (select cast('78620739000193' as varchar(14)) as chave from dual) c WHERE R2.NUMDOC like c.Chave /*<opcional>*/ -- Restrições a partir de lançamentos dos clientes UNION ALL SELECT Rn.TIPRES, Rn.RESTRI, Rn.DIVIDA, Rn.OCORRE, Rn.LIMITE FROM (SELECT * FROM TV_RES_001_CK4G R1 UNION ALL SELECT * FROM TV_RES_003_CK4G R3 UNION ALL SELECT * FROM TV_RES_004_CK4G R4 UNION ALL SELECT * FROM TV_RES_006_CK4G R6) Rn WHERE (Rn.FILCLI, Rn.CODCLI ) in (('001',' 4846')) /*</opcional>*/ ) RES GROUP BY TIPRES, RESTRI HAVING (TIPRES <> 1 or MAX(COALESCE(LIMITE,0)) < SUM(DIVIDA) + 100)

Clientes por Tipo e UF

select cli.*, tend.endere from tt_cli cli inner join tt_end tend on tend.filcli = cli.filcli and tend.codcli = cli.codcli where cli.isento = 'T' and cli.flgtip = '0' and tend.SIGEST = 'SC'

Previsão de Pagamento e Recebimento

SELECT TAB.*, UTL.DATOPE FROM ( SELECT SUB.CODFIL, SUB.FILPGT, SUB.CODPGT, CAST(NULL AS CHAR(3)) AS FILMAR, CAST(NULL AS CHAR(6)) AS CODMAR, CAST(NULL AS NUMERIC(4)) AS ITEMAR, FluxoMediaRecebimentos(SUB.CODFIL, SUB.FILPGT, SUB.CODPGT, TO_DATE('18/06/2016','DD/MM/YYYY'), TO_DATE('28/06/2016 23:59:59','DD/MM/YYYY HH24:MI:SS')) AS MEDIA_A, FluxoMediaRecebimentos(SUB.CODFIL, SUB.FILPGT, SUB.CODPGT, TO_DATE('19/06/2015','DD/MM/YYYY'), TO_DATE('29/06/2015 23:59:59','DD/MM/YYYY HH24:MI:SS')) AS MEDIA_B, FluxoMediaRecebimentos(SUB.CODFIL, SUB.FILPGT, SUB.CODPGT, TO_DATE('29/06/2015','DD/MM/YYYY'), TO_DATE('13/08/2015 23:59:59','DD/MM/YYYY HH24:MI:SS')) AS MEDIA_N, SUB.PERIOD, SUB.PRAIDE, SUB.PRIIDE, SUB.PARIDE, CAST('PRE' AS CHAR(3)) AS TABORI, -- Previsão de Recebimento CAST('R' AS CHAR(1)) AS TIPLAN -- A Receber FROM (SELECT FIL.CODFIL, PLA.FILPGT, PLA.CODPGT, PLA.PERIOD, PLA.PRAIDE, PLA.PRIIDE, PLA.PARIDE FROM TT_PLA PLA CROSS JOIN TD_FIL FIL WHERE FIL.CODFIL = COALESCE('001', FIL.CODFIL) ORDER BY FIL.CODFIL, PLA.FILPGT, PLA.CODPGT) SUB UNION ALL SELECT PAG.CODFIL, PAG.FILPGT, PAG.CODPGT, PAG.FILMAR, PAG.CODMAR, PAG.ITEMAR, PAG.MEDIA_A, PAG.MEDIA_B, PAG.MEDIA_N, 30 AS PERIOD, 0 AS PRAIDE, 100/PARCELA_MEDIA AS PRIIDE, PARCELA_MEDIA AS PARIDE, PAG.TABORI, PAG.TIPLAN FROM ( SELECT SUB.CODFIL, CAST(NULL AS CHAR(3)) AS FILPGT, CAST(NULL AS CHAR(3)) AS CODPGT, SUB.FILMAR, SUB.CODMAR, SUB.ITEMAR, FluxoMediaPagamentos(SUB.CODFIL, SUB.FILMAR, SUB.CODMAR, SUB.ITEMAR, TO_DATE('18/06/2016','DD/MM/YYYY'), TO_DATE('28/06/2016 23:59:59','DD/MM/YYYY HH24:MI:SS')) AS MEDIA_A, FluxoMediaPagamentos(SUB.CODFIL, SUB.FILMAR, SUB.CODMAR, SUB.ITEMAR, TO_DATE('19/06/2015','DD/MM/YYYY'), TO_DATE('29/06/2015 23:59:59','DD/MM/YYYY HH24:MI:SS')) AS MEDIA_B, FluxoMediaPagamentos(SUB.CODFIL, SUB.FILMAR, SUB.CODMAR, SUB.ITEMAR, TO_DATE('29/06/2015','DD/MM/YYYY'), TO_DATE('13/08/2015 23:59:59','DD/MM/YYYY HH24:MI:SS')) - FluxoMediaPedidos(SUB.CODFIL, SUB.FILMAR, SUB.CODMAR, SUB.ITEMAR, TO_DATE('29/06/2016','DD/MM/YYYY'), TO_DATE('12/08/2016 23:59:59','DD/MM/YYYY HH24:MI:SS')) AS MEDIA_N, FluxoMediaPrazoPagamentos(SUB.CODFIL, SUB.FILMAR, SUB.CODMAR, SUB.ITEMAR, TO_DATE('18/06/2016','DD/MM/YYYY'), TO_DATE('28/06/2016 23:59:59','DD/MM/YYYY HH24:MI:SS')) AS PARCELA_MEDIA, CAST('PPA' AS CHAR(3)) AS TABORI, -- Previsão de Pagamentos CAST('P' AS CHAR(1)) AS TIPLAN -- A Pagar FROM (SELECT FIL.CODFIL, MAR.FILMAR, MAR.CODMAR, MAR.ITEMAR FROM TT_MAR MAR CROSS JOIN TD_FIL FIL WHERE FIL.CODFIL = COALESCE('001', FIL.CODFIL) ORDER BY FIL.CODFIL, MAR.FILMAR, MAR.CODMAR, MAR.ITEMAR) SUB ) PAG ) TAB INNER JOIN TT_UTL UTL ON UTL.CODFIL = TAB.CODFIL WHERE UTL.DATOPE BETWEEN TRUNC(TO_DATE('29/06/2016','DD/MM/YYYY')) AND TO_DATE('12/08/2016 23:59:59','DD/MM/YYYY HH24:MI:SS') AND 1 = CASE WHEN 'F' = 'T' THEN 1 ELSE CASE WHEN UTL.FERABE = 'F' THEN 1 ELSE 0 END END ORDER BY TAB.CODFIL, TAB.FILPGT, TAB.CODPGT, TAB.FILMAR, TAB.CODMAR, TAB.ITEMAR, UTL.DATOPE

Vendas e Planos

select pla.apepgt, sum(ven.vlrliq) as vlrliq from tt_rec rec inner join tt_ven ven on ven.codfil = rec.codfil and ven.sequen = rec.sequen inner join tt_pla pla on pla.filpgt = ven.filpgt and pla.codpgt = ven.codpgt
where rec.datven between add_day(TO_DATE('28/06/2016','DD/MM/YYYY'),-10) and TO_DATE('28/06/2016','DD/MM/YYYY') group by pla.apepgt order by pla.apepgt

Embalagens com Fator de Conversão

select ipr.filmat, ipr.codmat, ipr.codcor, ipr.codtam, ipr.refbas, ipr.refpro,
sub.nomite as embalagem, sub_sal.nomite as unidade, sub.fatcon from tv_ipr_fast ipr inner join tt_sub sub on ipr.codtam = sub.codite inner join td_sub dsub on dsub.codtab = sub.codsub inner join tt_sub sub_sal on sub_sal.codite = dsub.codsal where dsub.codsal is not null
order by ipr.filmat, ipr.codmat, ipr.codcor, sub.fatcon

Produto ST com IPI

SELECT PRO.DESMAT, PRO.ESPMAT, IUF.* FROM TT_IUF IUF INNER JOIN TT_PRO PRO ON PRO.FILMAT = IUF.FILMAT AND PRO.CODMAT = IUF.CODMAT WHERE IUF.SUBTRI = '3' AND PRO.SITIPI = '0'

Produtos em Trânsito

SELECT COALESCE(SUM(IVE.QTDMOV),0) FROM TT_TRA TRA INNER JOIN TT_VEN VEN ON VEN.CODFIL = TRA.FILIVE AND VEN.SEQUEN = TRA.SEQIVE INNER JOIN TT_IVE IVE ON IVE.CODFIL = TRA.FILIVE AND IVE.SEQUEN = TRA.SEQIVE AND IVE.NUMITE = TRA.NUMIVE WHERE TRA.NUMICO IS NULL AND TRA.FILMAT = pFilMat AND TRA.CODMAT = pCodMat AND TRA.CODCOR = pCodCor AND TRA.CODTAM = pCodTam AND VEN.FLGEST <> 'C' AND INSTR(COALESCE(PedidosDeFilial, VEN.FILCLI)||';', VEN.FILCLI||';') > 0 AND VEN.CODCLI = LPAD('0', 6, ' ')