sql_viasoft_contas.sql

SELECT
  'PDUPREC' AS "TABELA",
  PDUPREC.MLK_IDREFINT,
  VDUPREC.EMPRESA,
  VDUPREC.CLIENTE AS NUMEROCM,
  CONTAMOV.NOME AS CONTAMOV_NOME,
  NULL AS CONTAMOVLAN_TIPO,
  NULL AS CONTAMOVLAN_SEQCM,
  VDUPREC.DUPREC,
  VDUPREC.HISTORICO,
  VDUPREC.DTEMISSAO,
  VDUPREC.DTVENCTO,
  CONTAMOV.MLK_IDREFINT AS CONTAMOV_MLK_IDREFINT,
  VDUPREC.SALDO AS VALOR
FROM VIASOFT.VDUPREC
LEFT JOIN VIASOFT.CONTAMOV ON (CONTAMOV.NUMEROCM = VDUPREC.CLIENTE)
INNER JOIN VIASOFT.PRODUTOR_LEITE ON (PRODUTOR_LEITE.NUMEROCM = VDUPREC.CLIENTE)
LEFT JOIN VIASOFT.PDUPREC ON (PDUPREC.EMPRESA = VDUPREC.EMPRESA AND PDUPREC.DUPREC = VDUPREC.DUPREC)
WHERE
  VDUPREC.REPRESENT IN (9105,7024,7784)
    AND
  VDUPREC.DTVENCTO BETWEEN to_date('DATA_INICIAL', 'DD/MM/YYYY') AND to_date('DATA_FINAL', 'DD/MM/YYYY')
    AND
  VDUPREC.SALDO > 0
UNION ALL
SELECT
  'CONTAMOVLAN' AS "TABELA",
  CONTAMOVLAN.MLK_IDREFINT,
  CONTAMOVLAN.ESTAB,
  CONTAMOV.NUMEROCM,
  CONTAMOV.NOME AS CONTAMOV_NOME,
  CONTAMOVLAN.TIPO AS CONTAMOVLAN_TIPO,
  CONTAMOVLAN.SEQCM AS CONTAMOVLAN_SEQCM,
  CONTAMOVLAN.TIPO||' - '||CONTAMOVLAN.SEQCM AS DUPREC,
  CONTAMOVLAN.HISTORICO,
  CONTAMOVLAN.DTMOVTO,
  CONTAMOVLAN.VENCIMENTO,
  CONTAMOV.MLK_IDREFINT AS CONTAMOV_MLK_IDREFINT,
  (
    SELECT
      (VIASOFT.PSALDOCONTAMOVLAN(CONTAMOV.NUMEROCM, CONTAMOVLAN.ESTAB, CONTAMOVLAN.SEQCM, CURRENT_DATE)) AS NSALDO
    FROM DUAL
  ) AS VALOR
FROM VIASOFT.CONTAMOV
INNER JOIN VIASOFT.PRODUTOR_LEITE ON (PRODUTOR_LEITE.NUMEROCM = CONTAMOV.NUMEROCM)
INNER JOIN VIASOFT.CONTAMOVLAN ON (CONTAMOVLAN.NUMEROCM = CONTAMOV.NUMEROCM AND CONTAMOVLAN.TIPO IN ('AP','APGF'))
WHERE
  (SELECT (VIASOFT.PSALDOCONTAMOVLAN(CONTAMOV.NUMEROCM, CONTAMOVLAN.ESTAB, CONTAMOVLAN.SEQCM, CURRENT_DATE)) AS NSALDO FROM DUAL) > 0
    AND
  CONTAMOVLAN.VENCIMENTO BETWEEN to_date('DATA_INICIAL', 'DD/MM/YYYY') AND to_date('DATA_FINAL', 'DD/MM/YYYY')