Updating Preferred Contact Method on Legacy Values
In this release of Oracle Utilities Digital Asset Management, a feature is introduced that some system processes use to determine if person phone and email or person contacts is being used.
If moving to using person contacts, some contact method values on case and customer contact are suppressed and existing records show as <invalid value>.
These scripts update the preferred contact method on cases and customer contacts from legacy values to person contact.
1. Update Case Preferred Contact Method from Email to Primary Email Person Contact:
UPDATE CI_CASE X
SET X.CONTACT_METH_FLG = 'C1PC',
X.C1_CONTACT_ID =
(SELECT B.C1_CONTACT_ID
FROM CI_CASE A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'EM'
AND A.CONTACT_PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'EMAIL'
AND X.CASE_ID = A.CASE_ID
)
WHERE X.CASE_ID IN
(SELECT A.CASE_ID
FROM CI_CASE A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'EM'
AND A.CONTACT_PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'EMAIL'
)
--AND X.CASE_ID BETWEEN '0000000000' AND '9999999999';
AND X.CASE_ID BETWEEN '0000000000' AND '0000000000';
2. Update Case Preferred Contact Method from Fax to Primary Fax Person Contact.
UPDATE CI_CASE X
SET X.CONTACT_METH_FLG = 'C1PC',
X.C1_CONTACT_ID =
(SELECT B.C1_CONTACT_ID
FROM CI_CASE A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'FAX'
AND A.CONTACT_PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'FAX'
AND X.CASE_ID = A.CASE_ID
)
WHERE X.CASE_ID IN
(SELECT A.CASE_ID
FROM CI_CASE A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'FAX'
AND A.CONTACT_PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'FAX'
)
--AND X.CASE_ID BETWEEN '0000000000' AND '9999999999';
AND X.CASE_ID BETWEEN '0000000000' AND '0000000000';
3. Update Case Preferred Contact Method from Phone to Primary Phone Person Contact.
UPDATE CI_CASE X
SET X.CONTACT_METH_FLG = 'C1PC',
X.C1_CONTACT_ID =
(SELECT B.C1_CONTACT_ID
FROM CI_CASE A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'PH'
AND A.CONTACT_PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'PHONE'
AND X.CASE_ID = A.CASE_ID
)
WHERE X.CASE_ID IN
(SELECT A.CASE_ID
FROM CI_CASE A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'PH'
AND A.CONTACT_PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'PHONE'
)
--AND X.CASE_ID BETWEEN '0000000000' AND '9999999999';
AND X.CASE_ID BETWEEN '0000000000' AND '0000000000';
4. Update Customer Contact Preferred Contact Method from Email to Primary Email Person Contact.
UPDATE CI_CC X
SET X.CONTACT_METH_FLG = 'C1PC',
X.C1_CONTACT_ID =
(SELECT B.C1_CONTACT_ID
FROM CI_CC A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'EM'
AND A.PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'EMAIL'
AND X.CC_ID = A.CC_ID
)
WHERE X.CC_ID IN
(SELECT A.CC_ID
FROM CI_CC A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'EM'
AND A.PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'EMAIL'
)
--AND X.CC_ID BETWEEN '0000000000' AND '9999999999';
AND X.CC_ID BETWEEN '0000000000' AND '0000000000';
5. Update Customer Contact Preferred Contact Method from Fax to Primary Fax Person Contact.
UPDATE CI_CC X
SET X.CONTACT_METH_FLG = 'C1PC',
X.C1_CONTACT_ID =
(SELECT B.C1_CONTACT_ID
FROM CI_CC A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'FAX'
AND A.PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'FAX'
AND X.CC_ID = A.CC_ID
)
WHERE X.CC_ID IN
(SELECT A.CC_ID
FROM CI_CC A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'FAX'
AND A.PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'FAX'
)
--AND X.CC_ID BETWEEN '0000000000' AND '9999999999';
AND X.CC_ID BETWEEN '0000000000' AND '0000000000';
6. Update Customer Contact Preferred Contact Method from Phone to Primary Phone Person Contact.
UPDATE CI_CC X
SET X.CONTACT_METH_FLG = 'C1PC',
X.C1_CONTACT_ID =
(SELECT B.C1_CONTACT_ID
FROM CI_CC A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'PH'
AND A.PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'PHONE'
AND X.CC_ID = A.CC_ID
)
WHERE X.CC_ID IN
(SELECT A.CC_ID
FROM CI_CC A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'PH'
AND A.PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'PHONE'
)
--AND X.CC_ID BETWEEN '0000000000' AND '9999999999';
AND X.CC_ID BETWEEN '0000000000' AND '0000000000';
7. The following shows remaining cases that need to be investigated manually:
SELECT *
FROM CI_CASE
WHERE CONTACT_METH_FLG <> ' '
AND CONTACT_METH_FLG NOT IN ('N/A','POST','C1PC');
8. The following shows remaining Customer Contacts that need to be investigated manually:
SELECT *
FROM CI_CC
WHERE CONTACT_METH_FLG <> ' '
AND CONTACT_METH_FLG NOT IN ('N/A','POST','C1PC');