Noetix: Problems with INV_Reservations Template Missing Records

As we've just (at the beginning of the year) moved to R12 we've only just started exploiting the Reservations functionality and, as is often the case, when we started using the new functionality we wanted to report on it.

Noetix provides a view (descended from the INV_Reservations) template that seemed to meet most of our needs and we produced a few reports based on it. Yesterday one of our users reported an issue whereby only certain reservations were being included in the total reserved;

Item Reservations Window (Release 12)

 As you can see we have five Reservation amounts (1, 1, 2, 2, .25), now the total being reported by the Noetix view was 3.25 (actual total is 6.25).

After a bit of investigation it became clear that what the view was adding up the distinct values (1 + 2 + .25 = 3.25) and I'm sure you'll spot that the most likely reason behind this is the use of a UNION rather than a UNION ALL in the SQL.

Looking at the SQL behind the view it shows that both the INV_Reservations and the INV_Reservations_Base views have UNIONS and in order to fix the issue you need to replace them with UNION ALL's. This issue has been raised with Noetix and is bug 29598.

The script to fix it is;

UPDATE n_view_query_templates
   SET union_minus_intersection = 'UNION ALL',
       last_update_date = TO_DATE('17-APR-2012'),
       last_updated_by = 'B29598'
 WHERE view_label like 'INV_Reservations_Base'
   AND union_minus_intersection = 'UNION'
;

UPDATE n_view_query_templates
   SET union_minus_intersection = 'UNION ALL',
       last_update_date = TO_DATE('17-APR-2012'),
       last_updated_by = 'B29598'
 WHERE view_label like 'INV_Reservations'
   AND union_minus_intersection = 'UNION'
;

You'll need to call this from XU2.