1 Reply Latest reply: Nov 30, 2012 2:15 AM by AlexAnd RSS

    No output for XML Publisher Report using CASE/DECODE in Where Clause

    vishm8
      Hi,

      I've a business requirement to modify an existing report which has two input parameters,
      -> p_statcode (Closed Status) which can have values 'Y' or 'N'
      -> p_overdue (Overdue Flag) which can have values 'Y' or 'N'

      The Overdue Flag is an evaluated column having values of Y/N and it is evaluated as follows,
      ONTF_MOD_VAL(NVL (
                                           (TRUNC (SYSDATE)
                                            - (TO_DATE (oe_order_lines.attribute18,
                                                        'DD-MON-RRRR')
                                               + TO_NUMBER (fnd_lookup_values.meaning))),
                                           0
                                        ))
                              overdue_flag
      The user requirement now is they needs to be a third option for parameter p_overdue called ALL,
      passing which the output should include records having
      p_statcode is Y ELSE p_statcode is N AND p_overdue is Y OR p_overdue is N

      In other words records having both Y and N vlaues for Overdue Flag have to be returned irrespective of the value given to Closed Status.

      Original where clause in the Data Definition file is as follows,
      WHERE Closed_Status = nvl(:p_statcode,Closed_Status)
                         AND overdue_flag = nvl(:p_overdue,overdue_flag)
      My modified code is as follows,
      WHERE   Closed_Status = NVL (:p_statcode, Closed_Status)
               AND overdue_flag = (CASE
               WHEN :p_overdue = 'Y' THEN 'Y'
               WHEN :p_overdue = 'N' THEN 'N'
               ELSE overdue_flag
               END)
      OR
      WHERE   Closed_Status = NVL (:p_statcode, Closed_Status)
               AND overdue_flag = DECODE (:p_overdue, 'Y', 'Y', 'N', 'N',overdue_flag)
      Both approaches have the same problem.
      The output is in EXCEL format. The modified query works fine for p_overdue as Y or N but when p_overdue is passed as ALL it returns an empty EXCEL sheet with just the report output column headers.
      Any help as to why this is the case ?? What is wrong in my approach ?

      Regards,
      Vishal
        • 1. Re: No output for XML Publisher Report using CASE/DECODE in Where Clause
          AlexAnd
          not clear about p_overdue = ALL
          which values needed for p_overdue = ALL ?

          try smth like
          WHERE   Closed_Status = NVL (:p_statcode, Closed_Status)
          AND (
             overdue_flag = DECODE (:p_overdue, 'Y', 'Y', 'N', 'N',overdue_flag) and :p_overdue != 'ALL'
             or
            :p_overdue = 'ALL' and (overdue_flag = 'Y' or overdue_flag = 'N')
          )
          for overdue_flag which has more then 'Y', 'N' values
          if overdue_flag only in ('Y','N') then
          WHERE   Closed_Status = NVL (:p_statcode, Closed_Status)
          AND (
             overdue_flag = DECODE (:p_overdue, 'Y', 'Y', 'N', 'N',overdue_flag) and :p_overdue != 'ALL'
             or
            :p_overdue = 'ALL'
          )