Here are some tips and tricks I've started documenting on the Sage software. A lot of this stuff came from a painful upgrade from v6.0 to v6.2.

Note that these tips apply to the Oracle installed version of Sage CRM. These may or may not hold true for other databases.
Use these tips at your own risk!

cuvi_options:

bit5: 1= keyword search
bit4: 1= group view
bit3: 1= report view
bit2&1: 00= core view, 10= core view, 01= system view, 11= user view

These are the only numbers in my database (so far):

0 core view
1 system view
3 user view
4 core view, report view
5 system view, report view
7 user view, report view
9 system view, group view
15 user view, report view, group view
17 system view, keyword
25 system view, group, keyword

An example of using bitand in a query to find cuvi_options with bit 4 set (this is the query that CRM 6.2 uses to find candidate group views):

SELECT DISTINCT CuVi_ViewName
FROM Custom_Views, ALL_COL_COMMENTS
WHERE OWNER = 'SA'
AND CONVERT(TABLE_NAME, 'UTF8', 'UTF8') = CONVERT(UPPER(CuVi_ViewName), 'UTF8', 'UTF8')
AND BITAND(CuVi_Options,8) <> 0
and lower(CuVi_ViewName) = lower('vCompanyMailCampaign')
ORDER BY CuVi_ViewName

If you mark a view as a group view, then you'd expect it to be available in the list of views for creating a view, right? Wrong.

The view must pass another step: It cannot have any duplicate keys in it. For example, a company view cannot have duplicate comp_companyId's in the views result set. If it does, it will not be listed as a view from which to create a group.

update custom_reportbands set reba_viewname = 'name of new view'
  where lower(reba_viewname) = lower('name of old view') ;

update Custom_ReportSearches set RESE_SQLTEXT = REGEXP_REPLACE(RESE_SQLTEXT, lower('name of old view'), 'name of new view', 1, 0, 'i')
  WHERE lower(RESE_SQLTEXT) like lower('%name of old view%');

If you have a repo_category that is the name of a CRM entity and not a report category, then this thing is a group, not a report.

CRM uses the following query to show groups:

select REPO_REPORTID, REPO_CATEGORY, REPO_NAME
  from vTargetLists
  WHERE  UPPER(repo_Category) = N'COMPANY'
  AND BITAND(Repo_Options,128) <> 0
  AND  (repo_privateUserID=  8057 OR repo_privateUserID=-1 OR repo_privateUserID=0)
  ORDER BY Repo_Name, Repo_ReportID

Based on this query, we can assume that if the bit8 is set in repo_options, then the item is a group.

CRM upgrades sometimes mess with these types of views, so do yourself a favor and uncheck the “Reports View” option for any system or core view to prevent users from creating reports using them.

This query will find all reports that use system or core views that are marked as a “Reports View”. These reports may cause issues on an upgrade.

select repo_category, repo_name, reba_viewname
  from custom_reportbands b, custom_reports r
  where reba_reportid = repo_reportid
    and lower(reba_viewname) in (select lower(CUVI_VIEWNAME) from custom_views where cuvi_options in (4,5))
  order by repo_category, reba_viewname;

Seems that beginning in crm v6.2, CRM will append a predicate to your where clause that checks all the *_secterr columns in all the CRM tables that your view uses. This is a change from v.6.0, where it would only check the secterr column from the entity under which your view was created. This can cause problems if you don't include these columns in your view. You will get errors like: ORA-00904: “COMP_SECTERR”: invalid identifier (at least in Oracle).

So, when building a view, be sure to include these columns. If your view is based on other views, then you need to include these territory columns from those other views too….CRM knows! What to do if you don't or can't include these columns?

For example, assume you have the two following views:

CREATE VIEW vPersonCount AS
select pers_companyId, count(*) as personCount
  from Person
  group by pers_companyId

CREATE VIEW vCompanyNumberPeople AS
select comp_companyId, comp_secterr, personCount
  from Company, vPersonCount 
  where comp_companyId = pers_companyId(+)

If you attempt to create a report using vCompanyNumberPeople, you will get the error:

ORA-00904: "COMP_SECTERR": invalid identifier

Some solutions:

1. You could group by pers_companyId and pers_secterr in vPersonCount, then you can include pers_secterr in the view definition, and consequently in vCompanyNumberPeople.
2. A quick and easy solution is to change the vCompanyNumberPeople to look like the following. This is effective especially if your views are very complex, and/or you don't have time to change a bunch of views.

   • CREATE VIEW vCompanyNumberPeople AS
     select comp_companyId, comp_secterr, comp_secterr as pers_secterr, personCount
       from Company, vPersonCount 
       where comp_companyId = pers_companyId(+)
     

Sage uses oracle sequences to control it's primary keys. This is a good thing, and actually surprising. A lot of cross database applications roll their own key management system so as to simplify deployment and support on each platform. I applaud Sage for using sequences. There are, however, some wierdnesses. The major one is that occasionally, and inexplicably, the CRM software will drop and recreate a sequence. Not even Sage support can say why this happens. Our VAR even ran it up the pole to development, and we never got a correct answer back on how to prevent it.

Normally this isn't a problem for Sage, the sequence will return back a number the next time it's asked for, no big deal right? Well, consider if you have some stored procedures that are using these sequences. Guess what happens when the sequence is dropped? Your stored procedure is invalidated. Guess what happens when that stored procedure is called again? Yeah, you get ora-04048 or one of it's cousins. And because CRM is using a thread pool, the same end user could retry, get a different connection, and get the same error again. Now imagine this happening at random times throughout the day, to random sequences. Lots of unhappy people.

The fix is one of:

1. Don't use CRM sequences in stored procedures. Only works if you don't have a need to insert into the CRM tables directly.
2. Wrap the call to the sequence in dynamic SQL. Easy to do. We use something like:

   1. /*--------------------------------------------------------------------------------------------------------------*/
      
      function get_next_sequence_val(p_sequence_name in varchar2) return number is
        v_seq_value number;
      begin
        execute immediate 'select '||p_sequence_name||'.nextval from dual' into v_seq_value;
        return v_seq_value;
      end;
      
      /*--------------------------------------------------------------------------------------------------------------*/