This discussion is archived
1 Reply Latest reply: Nov 30, 2012 12:15 AM by AlexAnd RSS

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

vishm8 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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'
    )

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points