Noetix 6.0.1 to 6.4.1 Noetix Views Migration ~ Running a Seeded Regeneration

I have been busy working on my Noetix 6.0.1 to 6.4.1 Noetix Views migration and I am supposed to crank through this project pretty quickly. I have been setting up my test environment.  My approach to setting-up my 6.4.1 Noetix View schema is as follows:

• Run a 6.4.1 Noetix Views regeneration “out of the box”on an existing Noetix schema (6.0.1 to be exact).
• Run a 6.4.1 Noetix Views regeneration with all my xu2 hook scripts.
• Run a 6.4.1 Noetix Views regeneration with all my xu5 hook scripts.

With the “out of the box” regeneration, I noticed that popview.sql and ycrcal.sql appear to have bugs (Magnitude supplied me with a corrected popview.sql script).

Here is the error for the ycrcal.sql:

6 insert into noetix_calendar
7 ( effective_date,
8 month,
9 day,
10 year,
11 week_of_year,
12 quarter,
13 julian_day,
14 month_of_year,
15 week_of_month,
16 day_of_month,
17 day_of_week )
18 values
19 ( l_effective_date,
20 TRIM(to_char(l_effective_date,'MONTH')),
21 TRIM(to_char(l_effective_date,'DAY')),
22 to_number(to_char(l_effective_date,'YYYY')),
23 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'WW')),
24 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'Q')),
25 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'J')),
26 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'MM')),
27 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'W')),
28 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'DD')),
29 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'D')));
30 l_effective_date := l_effective_date + 1;
31
32 exit when l_effective_date = to_date('01-JAN-2100','DD-MON-YYYY') ;
33 end loop;
34 commit;
35 end;
36 /
declare
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 6

This install script is taking l_effective_date, which is a date and casting it as date (I have not seen that done successfully nor is this a great idea).

Here is a reproduction of that error in a more trivial example:

NOETIX_SYS@erp>select to_date(sysdate,'dd-mon-yyyy')
2 from
3 dual;
select to_date(sysdate,'dd-mon-yyyy')
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

When I correct this casting of a date as a date issue, the script looks like this:

6 insert into noetix_calendar
7 ( effective_date,
8 month,
9 day,
10 year,
11 week_of_year,
12 quarter,
13 julian_day,
14 month_of_year,
15 week_of_month,
16 day_of_month,
17 day_of_week )
18 values
19 (l_effective_date,
20 to_char(l_effective_date,'MONTH'),
21 to_char(l_effective_date,'DAY'),
22 to_number(to_char(l_effective_date,'YYYY')),
23 to_number(to_char(l_effective_date,'WW')),
24 to_number(to_char(l_effective_date,'Q')),
25 to_number(to_char(l_effective_date,'J')),
26 to_number(to_char(l_effective_date,'MM')),
27 to_number(to_char(l_effective_date,'W')),
28 to_number(to_char(l_effective_date,'DD')),
29 to_number(to_char(l_effective_date,'D')));

Presently, I am waiting for Magnitude to acknowledge the problem and fix it.