Script produto.sql

SELECT * FROM (
SELECT PF.CODPROD,
       P.DESCRICAO,
       R.REGIAO,
       T.PTABELA,
       PF.PRECOFIXO,
       TRUNC(E.QTESTGER, 0) QTGERAL,
       TRUNC(E.QTESTGER - E.QTBLOQUEADA) QTDISPONIVEL,
       F.FORNECEDOR,
       PF.DTINICIOVIGENCIA,
       PF.DTFIMVIGENCIA,
       CASE
         WHEN P.CODPROD IN
              (SELECT DISTINCT CODPROD
                 FROM PCPRECOPROM
                WHERE PCPRECOPROM.DTINICIOVIGENCIA <= TRUNC(SYSDATE)
                  AND PCPRECOPROM.DTFIMVIGENCIA >= TRUNC(SYSDATE))
         THEN
          'Ativa'
         ELSE
          'Inativa'
       END AS ATIVO,
       :POLITICA RESPOSTA,
       P.MODULO || ' - ' || P.RUA || ' - ' || TRUNC(P.NUMERO) || ' - ' ||
       P.APTO ENDERECO
  FROM 
       PCPRECOPROM PF,
       PCPRODUT P,
       PCEST E,
       PCFORNEC F,
       PCREGIAO R,
       PCTABPR T
 WHERE 1 + 1 = 2
   AND PF.CODPROD = P.CODPROD
   AND PF.NUMREGIAO = R.NUMREGIAO
   AND P.CODFORNEC = F.CODFORNEC
   AND P.CODPROD = E.CODPROD
   AND T.CODPROD = PF.CODPROD
   AND T.CODPROD = P.CODPROD
   AND T.CODPROD = E.CODPROD
   AND PF.DTINICIOVIGENCIA >= :DATA_INICIO_PROMOCAO
   AND PF.DTFIMVIGENCIA <= :DATA_FIM_PROMOCAO
   AND PF.NUMREGIAO IN (1)
   AND T.NUMREGIAO = 1
   AND PF.CODPROD NOT IN (SELECT CODPROD
                            FROM PCEST
                            WHERE CODPROD IN (SELECT CODPROD
                                              FROM PCPRODUT
                                              WHERE OBS2 = 'FL')
                            AND QTEST = 0)
   ORDER BY CASE WHEN :ORDEM = 'DT' THEN PF.DTINICIOVIGENCIA END
   ) WHERE ATIVO = RESPOSTA OR RESPOSTA = 'Todas'
   ORDER BY CASE WHEN :ORDEM = 'PD' THEN DESCRICAO END