RSF_LEAD record ACCEPTED_DT, REFERRED DT fields

RSF_LEAD header record has ACCEPTED_DT, REFERRED DT fields. They get populated when the status of the Lead changes.
I am on CRM 9.0 bundle 31 and peopletools 8.49
I don't see those values getting updated correctly which could be useful when building any sql query around it.

The place where you can add business logic for this is:
RSF:CDMInterface - application class
Method: ConvertLeadToOppy
This is the code snippet -


&rLead.OPPORTUNITY_ID.Value = &ciOpp.OPPORTUNITY_ID;
&rLead.LEAD_STATUS.Value = "CO";
==============custom code can be placed here ==========================
If None(&rLead.ACCEPTED_DT.Value) Then
&rLead.ACCEPTED_DT.Value = %Date;
End-If;
If None(&rLead.REFERRED_DT.Value) Then
&rLead.REFERRED_DT.Value = %Date;
End-If;
==============custom code can be placed here ==========================
/* ice 578544000 */
&rLead.LEAD_REJECT_CD.Value = "";

"Maintain Account" team on company page

CRM 8.9

PeopleBook says:
You must have one of these roles to maintain account teams:

Account Administrator

Account Manager

UPG_ALLPAGES

When I saw the code behind it:
Make sure one of your roles is tied to membership lists which in turn is a part of security profile in which the following Functional Option is also present:

/** Functional Option to Administer Account Team **/
"RSP_ADM_ACCT_TEAM"

CRM Sales - imp tables

RSF_ACC_SUSER – flattened table populated by RSF_ACCESS app engine
This is based on the territory tree and associated child nodes.

RSF_SUSER stores the defaults for all the sales users.

RSF_TR_TEAM – Territory definition and sales users are tied to it.

Code to add a new BO Role in BO_ROLE table

import RB_BORM:BO:*;
import RB_CDM:ObjectCollection;

&oObjectCollection = create RB_CDM:ObjectCollection();
&oBO = &oObjectCollection.getBO(&inBOID);

/*Check to see if ROLE already exists*/
SQLExec("SELECT count(*) FROM PS_BO_ROLE R WHERE R.ROLE_END_DT > GETDATE() AND R.BO_ID = :1 AND R.ROLE_TYPE_ID =:2", &inBOID, &nRoleTypeId, &RoleExists);

If &RoleExists < 1 Then &oBO.addBORole(&nRoleTypeId);
End-If;

Code to create a new BO relationship in BO_REL table

import RB_BORM:BO:*;
import RB_CDM:ObjectCollection;

&oObjectCollection = create RB_CDM:ObjectCollection();
&oBO = &oObjectCollection.getBO(RD_CALLRPT_CONT.BO_ID_CONTACT);

/*Check to see if REL already exists*/
SQLExec("SELECT 'Yes' FROM PS_BO_REL WHERE BO_ID_1=:1 AND BO_ID_2=:2 AND ROLE_TYPE_ID_1=:3 AND ROLE_TYPE_ID_2=:4 AND END_DT>GETDATE()", RD_CALLRPT_CONT.BO_ID_CONTACT, RD_CALLRPT_CONT.BO_ID_CUST, 8, 2, &l_RelExists);

If &l_RelExists <> "Yes" Then
&dtEndDt = Date3(2999, 12, 31);
&nRelID = &oBO.addBORelationship(10, RD_CALLRPT_CONT.BO_ID_CONTACT, 8, RD_CALLRPT_CONT.BO_ID_CUST, 2, %Date, &dtEndDt);
&oBO.save();

End-If;

Queries for some more data issues related to address/phone/emails

BORM / CDM Table data issues

--Active companies with multiple primary Addresses

SELECT A.BO_ID,BC.COMPANYID,COUNT(1) FROM PS_BO_SRCH_ADD A,PS_RD_COMPANY BC,PS_BC B
WHERE A.PRIMARY_IND='Y'
AND A.BO_CM_END_DT>GETDATE()
AND A.CM_USE_END_DT>GETDATE()
AND A.BO_REL_ID = 0
AND A.ROLE_TYPE_ID = 2
AND BC.BO_ID = A.BO_ID AND BC.BO_ID = B.BO_ID AND B.CUST_STATUS='A'
GROUP BY A.BO_ID,BC.COMPANYID
HAVING COUNT(1)>1


--Active companies with multiple primary phones

SELECT A.BO_ID,BC.COMPANYID,COUNT(1) FROM PS_BO_SRCH_PHN A,PS_RD_COMPANY BC,PS_BC B
WHERE A.PRIMARY_IND='Y'
AND A.BO_CM_END_DT>GETDATE()
AND A.CM_USE_END_DT>GETDATE()
AND A.BO_REL_ID = 0
AND BC.BO_ID = A.BO_ID AND BC.BO_ID = B.BO_ID AND B.CUST_STATUS='A'
GROUP BY A.BO_ID,BC.COMPANYID
HAVING COUNT(1)>1


--Active companies with multiple primary Emails

SELECT A.BO_ID,BC.COMPANYID,COUNT(1) FROM PS_BO_SRCH_EML A,PS_RD_COMPANY BC,PS_BC B
WHERE A.PRIMARY_IND='Y'
AND A.BO_CM_END_DT>GETDATE()
AND A.CM_USE_END_DT>GETDATE()
AND A.BO_REL_ID = 0
AND BC.BO_ID = A.BO_ID AND BC.BO_ID = B.BO_ID AND B.CUST_STATUS='A'
GROUP BY A.BO_ID,BC.COMPANYID
HAVING COUNT(1)>1

Cloning functionality in CRM

CRM 9.0
Tools 8.50

Out of the box,
Cloning of commonly used objects like opportunity,lead, etc. is provided.
The code behind the scene is fairly neat and usually does not need any modification.
If you need to pull additional scrolls in the cloning process, please take a look at the additional points mentioned below.

On the cloning setup page
Component RB_CLONE_DEFN
Menu RSF_SETUP_TABLES
This component is delivered 'Hidden' so you have to find it how to get there :-)

I would consider the examle of Opportunity cloning.

First, open the RSF_OPP_CI in app designer and add all the required scrolls, scroll fields to the right hand side pane and save.

Go to the front end and select RSF_OPP_CI and go to the second tab for field selection (Define Clone Fields). Click the 'reload' button and select 'Yes'.
It retains the old field selection and now select all that you want to get cloned.

Select the check box 'Prompted' if you want user to select whether to clone that scroll or not at runtime, i.e. on the actual opportunity cloning transaction page.
If you want a particular scroll to be always clone, implicitly, then do not select this option.

That's all and you are all set!

Text tray in CRM 9.0

To have access to Text tray functionality you need to have access to the permission list:
CRRB1810
It is hardcoded in the peoplecode.

ERMS imp tables

CRM 9.0

When an email is read by RB_MAIL_READ process, then take a look at the data in this table.

SELECT * FROM PS_MCFEM_MAIL_DSCR

This table actually stores the CC email address but it does not store the BCC address !

========================================

Also regarding Auto acknowledgement:
Once the auto-acknowledgement is sent the information is stored in this table:

SELECT * FROM PS_RBC_RECIPIENT

This is the main correspondence management table.

As of CRM 9.0 Auto-ack is sent only to the Email_From person and not to anyone in CC/BCC.

AAF cache issue

If you are having trouble with the application server or AAF cache in your program then try this:

Import these classes:
import EOCF_CLF_DL:Runtime:Resolution:*;


declare:
Local EOCF_CLF_DL:Runtime:Resolution:DataLibrarySession &oDataLibrarySession;


Call:
&oDataLibrarySession = create EOCF_CLF_DL:Runtime:Resolution:DataLibrarySession();
&oDataLibrarySession.clearCache(" ");


I am sure it will work in all cases.

Verify Primary contact for Active companies

SELECT REL.BO_ID_2,COUNT(1) FROM PS_BO_REL REL,PS_BC COMP
WHERE REL.END_DT>GETDATE() AND REL.REL_TYPE_ID=9
AND REL.BO_ID_2 = COMP.BO_ID AND COMP.CUST_STATUS='A'
GROUP BY REL.BO_ID_2
HAVING COUNT(1)>1

Quick notes for opportunity data set rules and roles

Opps as Owner:
Primary SLSREP or person id in the RSF_OPPORTUNITY table

Opps as Manager: (manager of primary sls team member)
return person_id_manager from RSF_aCC_SUSER
person id from RSF_OPPORTUNITY = person id from RSF_ACC_SUSER

opps as team member:
SLSREP table for a given opportunity

Opps as Team Member Manager: (manager of any of the sls team member)
return person_id_manager from RSF_aCC_SUSER
person id from SLSREP = person id from RSF_ACC_SUSER

Opps as Task Assignee:
Task owner from RB_TSK WHERE
RB_TSK_TXN table with RB_TRANSACTION_ID = 'OPPRT' and sync id from RB_TSK and RSF_OPPORTUNITY is same
AND RB_TSK.TASK_ID=RB_TSK_TXN.TASK_ID

Opps as Temporary Assignee
if the opport owner (PERSON_ID in RSF_OPPORTUNITY tble) is temporarily assigned someone elses job based on the RSF_TR_TEAM table setup in the given date range
RSF_TR_TEAM.PERSON_ID_ASSIGNEE = RSF_OPPORTUNITY.PERSON_ID

Unassigned Opps
No sales team is assigned

Opps as Account Owner
BO_REL.BO_ID_2 = RSF_OPPORTUNITY.BO_ID_CUST
and REL_TYPE_ID IN 31 OR 101

SQL - All Active addresses for the customer

SELECT A.CUST_ID,A.BO_ID ,D.CM_ID,D.CM_CONCAT
FROM PS_BC A, PS_BO_CM_USE B , PS_BO_CM C, PS_CM D
WHERE A.BO_ID=B.BO_ID
AND B.BO_ID=C.BO_ID
AND C.CM_ID=D.CM_ID
AND C.CM_TYPE_ID=1
AND B.PROFILE_CM_SEQ=C.PROFILE_CM_SEQ
AND B.CM_USE_END_DT>=GETDATE()

Sold to / Bill to / Ship to flags on the company

CRM 9.0
Tools 8.50

Add a new company
BO_ROLE will have roles 2 and 40
If you mark the company to be 'sold to' then an additional role
42 would also be created.

Once you add a contact to the above company following relationships would be created:
Relationship Type 74 is the contact for Sold to organization(Role 42)
Relationship Type 75 is the 'Primary contact' for Sold to organization(Role 42)

9 and 10 for Primary contact and contact for the company(Role 2)

Once you add an address for the above company:
BO_CM will have 1 row with appropriate CM_PURPOSE_TYPE and CM_TYPE
CM will have 1 row with appropriate CM_PURPOSE_TYPE and CM_TYPE

BO_CM_USE will have 2 rows with same PROFILE_CM_SEQ but different Roles 2 and 42 with appropriate PRIMARY_IND


The similar rows would be present when you select 'Bill to' and 'Ship to' flags on the company.

RC_SOLN_USAG application engine in PeopleSoft

Application Engine program for Updating Solution Usage Counts:
This is for CRM 9.0

Navigation ->
Home>Solutions>Update Solution Usage Counts

A case can have product or Category/Type/Detail (CTD) values for easy classification.
When a solution is used to resolve a case, an entry is created in RC_RESOLUTION table.
After this if we run the application engine RC_SOLN_USAG from the above navigation the entries would be created in RC_SOLN_CTD and RC_SOLN_PRODUCT tables depending on what values are present on the case.

We can then index these tables in the search collection for better % accuracy of the search results.

Personal data integration between HR 9.0 and CRM 9.0

Scenario 1
Add a new employee with eff dt (sysdate-3) – back dated row
Result 1
One row each in
BO_CM – start date as (sysdate-3) and end date 12/31/2999
BO_CM_USE - start date as (sysdate-3) and end date 12/31/2999 and for role 4 and primary as ‘Y’
CM
BO_NAME
Two rows in BO_ROLE - Role 1 and 4

Scenario 2
Add a new row with eff dt (sysdate) for name change – today’s date
Result 2
BO_NAME has two rows with sysdate as seq nbr 2 and primary as ‘Y’

Scenario 3
Add a new row with eff dt (sysdate+3) for name change
Result 3
BO_NAME has two rows with 04/23 as seq nbr 3 and primary as ‘N’

Scenario 4
Add a row for phone number – Type Main – Preferred check box ‘Y’
Result 4
It creates one row each in: (There is no Effdt for phone grid, by default it takes system date)
BO_CM – start date as (sysdate) and end date 12/31/2999
BO_CM_USE - start date as (sysdate) and end date 12/31/2999 and for role 4 and primary as ‘Y’
CM

Scenario 5
Add a row for phone number – Type Work- Preferred check box ‘N’
Result 5
It creates one row each in: (There is no Effdt for phone grid, by default it takes system date)BO_CM – start date as (sysdate) and end date 12/31/2999
BO_CM_USE - start date as (sysdate) and end date 12/31/2999 and for role 4 and primary as ‘N’
CM

Scenario 6
Change the row for phone number – Type Work- Preferred check box ‘Y’
Result 6
Updated successfully in CRM

Scenario 7
Add one more effective dated row (sysdate-1) under Home Address type
Result 7
One row is inserted in BO_CM without changing the end date of the previous effective row (!)
In BO_CM_USE the end date of the previous effective row is changed to system date
Also new PROFILE_CM_SEQ is also created

Scenario 8
In Correct History Mode change effective dated row (sysdate-3) under Home Address type – make status as ‘I’
Result 8
In BO_CM the end date of the row is changed to system date
In BO_CM_USE the end date of the row is changed to system date

Scenario 9
In Correct History Mode change effective dated row (sysdate-2) under Home Address type
Result 9
In BO_CM the end date of the row is NOT changed to the new date but is row is updated with the latest timestamp
In BO_CM_USE the end date of the row is changed to the changed date and one more row is inserted for the same profile cm seq number with end date as 12/31/2999

Notes:
When just a person is created BO_CM_USE has Role 1
When a person is created with job data BO_CM_USE has Role 4 only and no role 1

CRM :: Masking the email addresses in non-production environment

Important CRM tables to be updated for masking the email addresses in non-production environment.This is crucial when we want to test the email functionality in peoplesoft as we donot want the emails from DEV/TEST environments to go to the real email addresses.

update ps_bo_srch_eml set cm_concat = 'DevEmailTesting@abc.com', email_addr = 'devemailtesting@abc.com',cm_concat_upper = 'DEVEMAILTESTING@ABC.COM';
update ps_rf_provider_grp set email_addr = 'DevEmailTesting@abc.com'
update ps_rc_case set rc_contact_info mailto:= where rc_contact_info like '%@%';
update ps_cm set email_addr= 'DevEmailTesting@abc.com' where cm_type_id=4;
update ps_rf_provider_grp set email_addr= 'DevEmailTesting@abc.com';
update psuseremail set emailid= 'DevEmailTesting@abc.com';
update ps_rolexlatopr set emailid= 'DevEmailTesting@abc.com';
update psoprdefn set emailid= 'DevEmailTesting@abc.com';
update ps_rb_in_email set reply_to_address= 'DevEmailTesting@abc.com';
update ps_rbc_recipient set email_addr= 'DevEmailTesting@abc.com';
update ps_mcfem_mail_main set mcf_email_from= 'DevEmailTesting@abc.com';
update ps_RB_EMAIL_ORGL set email_from= 'DevEmailTesting@abc.com' , Email_bcc= 'DevEmailTesting@abc.com';

There are other ways to do it also. This is just data masking.
We can achieve this objective by writing a wrapper code around sendmail() function in peoplesoft.This wrapper can modify the 'To email address' to dummy ID when we are dealing with non-production environments.
In higher peopletools version 8.47, we would need to write a similar wrapper around javamail API function call also.

Some CRM security thoughts

CRM security is not just at the row level. We can implement basic row security either by user ID or by permission list.
In either case, we can define which BUs and tablesets are accessible for a given user or for a given permission list.

1)Over and above these security levels, CRM has something called Dataset Roles and Dataset Rules, to further restrict the access. These are all configurable once you build the appropriate SQL views in app designer.
Most of the CRM search pages are 'Configurable Search' pages which are controlled by a huge chunk of setup data. That is the place where we can setup these dataset rules.

2)Another place to look for security for the functional folks is setting up 'functional options'. This specific security level is not very well described or documented. E.g. There may be few people in the organization who can setup and modify the company information, but only those who have sepcific 'functional option' which is mainly derived from PeopleTools roles, can add account team members for that company and not anyone else.
Instead of using delivered roles, we can build the memberships and view lists for customers or for other CRM roles

3) Secured worker role is for restricting the access to the sensitive worker information like DOB,address,phone,job data, etc.
There is a separate setup to allow specific roles to view/modify the CRM Worker data.This is again one level above row level security

Auto numbering

CRM 8.9 / 9.0

To setup Auto numbering for Site, company and person IDs:

1. Select the appropriate SETID
2. Num Type should be 'CUST', i.e. Customer ID

ERMS message in any email reply

When responding to this email, please perform a reply with history so that the following conversational identifier "[THREAD_ID:%1]" is included in your response.
message catalog - 17834
message set - 8024

Declared in app class: ERMS:ConstantsLib

Called from ERMS:ContextTag
Method:GetResolvedExplain

Field name is CONTEXT_TAG_FLAG in RBC_REQUEST table, but the component built over it is hidden and looks like this is a transaction page and not the setup page.